Let the Database Reject Impossible State

Two workers asked the same reasonable question: does this owner already have an active record? Both received the same honest answer: no. Each worker then created one.

Nothing in either request was obviously careless. The application checked first. The insert happened inside a transaction. Tests which ran one request at a time stayed green. The defect lived in the interval between the check and the write, where both transactions were allowed to be correct about a state which had already become stale.

The repair wasn’t a faster check or a bigger transaction around the same idea. Every active record received a deterministic owner key, and the database made that key unique. A second writer could still race. It could no longer commit an impossible result.

Validation describes the rule; a constraint survives the race

Application validation is still useful. It can say “an active record already exists” instead of exposing a storage error, reject incomplete input before opening a transaction, and keep an ordinary conflict off the expensive path. What it cannot do is make a check-then-insert sequence atomic:

worker A: SELECT active WHERE owner = 42  -> none
worker B: SELECT active WHERE owner = 42  -> none
worker A: INSERT active(owner = 42)       -> succeeds
worker B: INSERT active(owner = 42)       -> succeeds

Both reads can be valid when they happen. The combined state is invalid when both writes commit.

This is a time-of-check to time-of-use problem, but the lesson that matters is ownership. If every writer shares one database, that database is the last place where the invariant can see all competing writes. A constraint there does not depend on a controller, command, queue worker, import, or some future code path remembering the rule. The application should explain the invariant. The database should make it inescapable.

Encode the state which must be unique

The design had several active states and several terminal states. Historical rows had to remain, while only one active row per owner was allowed. A unique owner column by itself would have prohibited history as well as duplicates.

The representation was conditional rather than clever:

active record:
    owner_key = deterministic key for its owner

terminal record:
    owner_key = null

A unique index on owner_key then rejects a second active record. Databases which permit several null values in a unique index still retain any number of terminal rows.

The key has to represent the actual identity boundary. A raw concatenation such as tenant + "|" + user + "|" + session looks convenient until a missing value and an empty value mean different things. The implementation length-prefixes present values, marks absent values explicitly, and hashes the resulting tuple. The hash is an index representation, not the source of identity; the original owner fields remain authoritative.

This design has a consequence worth stating plainly: changing the meaning of “owner” changes the invariant. If an owner may have one active record per currency, region, or workflow, that scope belongs in the key. A compact key cannot rescue a badly named rule.

A unique key needs a state agreement

Uniqueness answers only one question: can two rows carry this key? It does not prove that the correct rows carry it.

Without another guard, these states remain possible:

active row with no owner key
terminal row which still occupies the owner key
active row with neither a user nor a session identity

The first quietly bypasses uniqueness. The second prevents a legitimate new record. The third hashes an absence which has no domain meaning.

The migration therefore adds checks around the unique index. Active rows must have an owner identity and a derived key. Terminal rows release the key. Database-side derivation protects older or lower-level writers which do not pass through the current application model.

That last detail can look redundant. It isn’t. A durable constraint is only as strong as its least disciplined write path. If a direct import can insert an active row without the key, we’ve moved the race rather than closed it.

The rules have different jobs:

Calling all of this “database validation” hides the point. Each rule closes a different hole.

Existing data gets a vote

Adding a constraint to an empty table is easy. Adding one to a table which already violates it is a business decision disguised as schema work.

The migration found duplicate active sets in existing data. It could have chosen the oldest row, the newest row, or the row with the most fields and deleted the rest. None of those rules was known to preserve the right business state.

Instead, the migration refused to continue until each duplicate set had an explicit canonical resolution. The chosen row received the owner key. Other rows remained as historical evidence with a narrow legacy exemption.

unresolved duplicate set
    -> migration stops

explicit canonical decision
    -> canonical row receives the key
    -> other rows remain visible as remediated history
    -> future duplicates are rejected

Stopping the migration feels less automated than choosing a winner. It is also more honest. Schema code cannot infer which record corresponds to a remote effect, a payment, a user-visible result, or later work merely because one timestamp is newer.

The exemption is debt, not an escape hatch for inconvenient writes. It exists for identified legacy rows which cannot be made historically false. New writes do not receive it. If ordinary application code can opt out, we’re back to hoping every caller behaves.

A conflict is a normal result of correct enforcement

Once the database owns the invariant, concurrent creation has two possible outcomes:

one writer creates the active record
another writer discovers that record already won

The second outcome is not necessarily an application failure. The acquisition path catches only the named unique-key conflict, reads the winner, and returns it. Unrelated database errors still escape, as they should.

That narrow catch matters. Converting every integrity error into “already exists” would hide missing foreign keys, invalid states, truncated values, and other defects. Catching the database exception broadly may look robust; in practice it turns real failures into lies. Translate the one rejection the application knows how to reconcile.

There is also a small interval where the losing transaction has received a conflict but the winning row is not yet visible under its current isolation and connection timing. A bounded retry can cover that visibility interval. The bound must remain finite, and it must not turn arbitrary database failures into retry traffic.

Application code still improves the common path:

find an existing active record
if found, return it
attempt creation
if the named uniqueness constraint rejects it, fetch the winner
otherwise propagate the error

The early lookup is an optimization. The constraint is the correctness boundary.

Locks are useful when they lock something real

A transaction does not magically serialize a predicate which currently matches no rows. Asking to lock “the active record” cannot lock a row which does not exist.

Other designs can be correct:

• lock a stable parent row which always exists;
• use an advisory lock derived from the owner;
• run the operation at a serialization level which detects the conflict;
• allocate active ownership in a dedicated table; or
• send all commands for an owner through one ordered processor.

Each has a legitimate place. A parent-row lock works well when all writers share the transaction and the parent is not a contention hotspot. Advisory locks can express a missing-row predicate, but every writer must use the same key and release behavior. Serializable transactions protect broader predicates, usually with retry and throughput costs. Ordered processing helps when ownership already lives in a stream, but the ordering boundary must cover every writer.

For one equality invariant in one relational database, a unique key is often the smallest reliable mechanism. It uses the write boundary the data already shares and gives the losing writer an unambiguous signal. There is no prize for introducing distributed coordination when one index already owns the truth.

The reproduction makes both interleavings visible

The DATA-01 fixture is a deterministic model, not a database benchmark. It runs the naive sequence with two successful observations and two accepted inserts, then sends the same competing writes through the modeled constraint and accepts exactly one.

It also exercises the less dramatic parts of the repair:

• terminal transition releases the key;
• a later active record can then acquire it;
• an ownerless active write is rejected;
• an unresolved duplicate set blocks migration;
• explicit resolution keeps both legacy rows while naming one canonical row; and
• new writes cannot request the legacy exemption.

The fixture cannot prove how a particular database engine, isolation level, trigger, migration, or private implementation behaves. The application tests are the evidence for that implementation. The public model exists so the argument can be inspected without pretending a JavaScript array is PostgreSQL.

Constraints should make the impossible expensive to reintroduce

A database constraint is not automatically the right answer. It cannot enforce an invariant spread across independent databases. It may be too rigid during a deliberate compatibility window. Some rules depend on remote facts which the database cannot know. Some are expensive predicates which need a different model rather than a clever check.

The decision turns on three questions:

Is this state invalid regardless of which writer produced it?
Can the shared write boundary observe every competing transition?
Can a rejected writer recover or report the conflict deliberately?

If the first answer changes, revise the invariant. If writers move across independent stores, introduce coordination at the new shared boundary. If a migration requires temporary invalid state, make that window explicit and remove the escape path afterward.

The property we need is not mathematical impossibility. Operators can disable constraints, defects can live outside their scope, and future migrations can change the rules. What matters is much more practical: ordinary application paths can no longer create the known contradiction by accident.

Two workers may still observe the same empty state. With the invariant at the shared write boundary, only one of them gets to make that observation obsolete.