Duplicate Key Value Violates Unique Constraint, Permanently

The short version

Implement publishing a new contract-template version as two separate writes — demote the current row’s is_current to false, then INSERT the new one with is_current = true — and two concurrent publishes leave one demotion stranded, after which every publish computes the same version number and fails permanently with duplicate key value violates unique constraint. The cause is expressing the state “the current version” through two non-atomic writes spanning multiple rows. The fix is to drop the mutable flag entirely and derive the current version each time from the maximum of the version column. Make publishing a single INSERT of one new row, and a numbering collision is simply rejected on the spot by the unique constraint — retry and you get the right number.

What it looks like — found in code review

A code review revisiting the contract-template publishing feature flagged this design. At the time it was flagged the table was still empty, with zero actual publishes. So no “can never publish again” incident actually occurred in production; the defect was caught as a design flaw before it could.

The finding was this. Publishing was built in these steps.

1. Read the current version (the row with is_current = true)
2. UPDATE that row to is_current = false (demote)
3. INSERT the new version with version = current + 1 and is_current = true

Steps 2 and 3 are separate SQL statements, with no single transaction binding them. Run two publishes concurrently and it breaks like this.

• Both publishes read the same “current version” and compute the same “next version number”
• One publish’s INSERT succeeds first and consumes that number
• The other INSERT aims at the same version number, is rejected by the unique(kind, version) constraint, and fails with duplicate key value violates unique constraint
• If it stopped there it would only be “an error, the publish failed”. But if the demotion in step 2 had already gone through, there is now no row at all with is_current = true for that kind. Anything reading the body in that gap falls back to the baseline v1
• And the next publish request computes the same version number again — because the current version is missing, or because it computes from a broken one — so it repeats the same collision. Retrying by hand does not fix it unless the number shifts

Expressing one single state, “the current version”, through two independent writes — an UPDATE and an INSERT — is the entrance to this failure.

Why

The root cause is managing “which one is current right now” with a mutable flag (is_current). A flag-based design is consistent only when both of these succeed.

• The old version’s flag becomes false
• The new version is INSERTed with its flag true

Unless those two are wrapped in one transaction, a moment where only one has happened is inevitable. Here the computation of “the next version number” was itself anchored on that flag, so when the flag broke, the numbering broke with it. And once the numbering is broken, the same number is recomputed every time — so a retry cannot recover.

Fixing it

A migration dated 2026-07-24 dropped the is_current column itself. The current version is now derived each time as “the row with the maximum version for that kind”, and publishing became a single INSERT of one new row. With no UPDATE at all, there is no gap in which the state can be split.

If a version number collides, the unique(kind, version) constraint simply rejects that INSERT, so re-running the publish (re-reading the current version and recomputing) inserts at the right number. The path where “the same number is computed forever and you are stuck” is gone from the design.

A version >= 2 CHECK constraint was added at the same time. This version table holds v2 onward only, on the premise that v1 is the baseline bundled in the code and is authoritative — so slipping a v1 into this table would let the code’s baseline win and the row be silently ignored. The constraint prevents that separate accident structurally.

Preventing a repeat

What this migration actually changed is the design principle itself: do not rely on a mutable flag. The old design’s comments record exactly why demote-then-insert is dangerous. Whether anywhere else expresses “the current state” through two writes spanning multiple rows is now the first thing to suspect when designing the next table that carries versioning or a state transition.