Enforce optimistic concurrency version checks at the DB layer (remove the version=0 bypass)

Table of Contents

This page is a capture in the next bucket of the product backlog — a pre-sprint idea, not yet pulled into a sprint as a story.

What

Every codegen'd entity's insert trigger (sql_schema_domain_entity_create.mustache) treats a caller-supplied version = 0 as "skip the optimistic-concurrency check, accept regardless of the row's current version" — a sentinel silently overloaded across four different write intents (interactive create, DQ publish/upsert, synthetic generation, revert-to-history). Introduce an explicit, separate signal (protocol-level force_version: bool, or a dedicated upsert path distinct from the plain insert) for the three intentional-upsert use cases, then remove the version = 0 bypass from the trigger entirely so a genuine duplicate-key create is rejected — at the DB layer, for every client (Qt, shell, HTTP, future UIs), not just wherever someone remembered to add a client-side pre-check.

Why

Currently a genuine create-with-colliding-key silently overwrites an unrelated existing row instead of being rejected — found and fixed for currency_pair specifically via a client-side pre-check (see Creating a Currency Pair with an already-used pair_code silently versions the existing row), but that fix only covers one entity and one client; every other codegen'd entity with a Qt create flow has the same latent hole, exploitable by any client that skips the (currently nonexistent, DB-level) check.

References

  • projects/ores.codegen/library/templates/sql_schema_domain_entity_create.mustache — the shared insert-trigger template to change.
  • projects/ores.sql/create/refdata/refdata_publish_from_dq_create.sql — a batch-upsert consumer that needs the new explicit force signal.
  • projects/ores.codegen/library/templates/cpp_domain_type_generator.cpp.mustache — synthetic generators, another version = 0 consumer.

See also

Emacs 29.3 (Org mode 9.6.15)