The unique index only one of your two services declares
The declaration is in the repository, it is correct, and it is applied at startup. Whether it ever took effect was decided months ago by which of two containers happened to start first, and the service that owns it has been logging success ever since.
On this page
TL;DR: A unique index declared in one service's model file and absent from the other's is not a guarantee, it is a race between two deploys that finished months ago. The declaration is applied at startup, and the database checks for duplicates at creation time — so if a duplicate landed before the declaring service first booted, the index was never created and never can be. That service has been logging
indexes syncedat every boot ever since.
The declaration was in the repository the whole time. It was correct. It was applied on startup by the model layer, the way that has been done for years. Nobody had written it wrong.
What nobody had written was the other copy. One collection, two services that both write to it, two model definitions that began as the same file and were copied when the second service was carved out. One of them says the collection is unique on a source id. The other says nothing about it, and creates the collection if it is missing — because it was written to be able to start against an empty database, which is a reasonable thing for a service to be able to do.
Neither file is wrong on its own. What is wrong is that a property of the data is stated in a place that only one of its two writers imports.
The sentence that decides it is in the database's manual
Applying a uniqueness declaration is not a declaration at all; it is an operation that can fail.
PostgreSQL's CREATE INDEX documentation
puts it in one line: UNIQUE "causes the
system to check for duplicate values in the table when the index is created (if data already exist)
and each time data is added."
Read that with two services in mind. The check runs when the index is created, against whatever is already there. So the declaring service's boot step succeeds if it happens to run before any duplicate exists, and fails if it does not — and which of those happened is a fact about the order two containers started in, once, at some point in the past.
That is not a race in the usual sense. There is no window, no interleaving, nothing that could go either way on a retry. The outcome was decided by a deploy and then frozen.
- Both services existthe collection does not
- Declaring service firstindex created — guarantee holds
- The other firstit creates the collection
- A duplicate landsbefore the declaration is applied
- Index creation refusedand it can never succeed
Two identical deployments, two opposite states
We built a harness for this rather than argue about it: plain SQL modelling exactly two things per service — the boot step a model layer performs, and the write path — five arms, each starting from an empty database.
| Arm | Duplicated ids | Unique index |
|---|---|---|
| The declaring service boots first, then the other | 0 | present |
| The other boots first, declaring service arrives before any duplicate | 0 | present |
| The other boots first, a duplicate lands first | 1 | ABSENT |
| …then restart the declaring service twice | 1 | ABSENT |
The first and third rows are the finding. Same code, same two model files, same database engine, opposite outcomes — and nothing in either repository distinguishes them. If you diffed the two deployments you would find nothing, because the difference is not in them.
The fourth row is the operational instinct and it is the wrong one. Restarting the service that owns the declaration does not help, because its boot step is not what failed: the data is. The index cannot be created while the duplicate is there, and the duplicate cannot be removed by anything that runs at boot. The ordering did not delay the guarantee. It foreclosed it.
What the declaring service logged the whole time
This is the part that explains why it survived. Three boots of the service that owns the uniqueness declaration, with the index absent throughout:
A | schema ready
A | indexes synced
A | schema ready
A | indexes synced
A | schema ready
A | indexes syncedindexes synced is emitted whether the index was created or its creation was refused, because the
boot step catches its own error and logs one line either way. That is not carelessness; it is what you
write when you want a service to be able to start. A boot step that raised would take the container
down and somebody would look at it. Ours came up healthy, reported success, and served traffic.
The write path has the same shape. An insert that the database refuses as a duplicate is caught and treated as a no-op, which is correct — a duplicate arriving twice is genuinely fine. But the identical line is emitted when the row is written, when it is refused, and when it is written as a duplicate because no index exists to refuse it. Three different states, one message.
So there is no arm of any of this that is distinguishable in a log. That is why the answer to "how long has this been broken" is always the same and always unsatisfying: as long as the collection has had duplicates in it, which nobody was counting.
Ask the database, not the repository
How would you know if this were true of you?
We did not find it from an error, because there wasn't one. It came from a support question about a record appearing twice in an export — the kind of thing that gets fixed by hand once and filed under "weird".
The check is three queries and needs no tooling:
-- 1. does the index actually exist, in the database, right now?
SELECT indexname FROM pg_indexes WHERE tablename = 'receipts';
-- 2. if it does not, why not — is there something in the way?
SELECT source_id, count(*) FROM receipts GROUP BY source_id HAVING count(*) > 1;
-- 3. and the question the first two are really asking
SELECT count(*) FROM receipts;The point of the first query is that it asks the database, not the repository. Every other way of answering "do we have a unique index on this" — reading the model file, grepping the migrations, remembering — answers a different question, which is whether somebody declared one.
Where the declaration has to live instead
Two rules came out of this, and both are about ownership rather than about indexes.
A property of the data belongs to the schema, not to a service. The moment a second writer exists, a declaration inside one service's model layer is a statement that service is making about somebody else's data, and nothing makes the other one hear it. Schema changes belong in migrations that run as their own deploy step, owned by neither service and applied exactly once — which also means a failure is a failed deploy rather than a log line.
A boot step that cannot fail cannot enforce anything. If applying the declaration is going to stay
at startup, then it has to be able to stop the startup. The version of this that would have caught it
on day one is not clever: attempt the index, and if it cannot be created, refuse to serve. That is a
one-line change to a catch block, and it converts a silent permanent defect into a container that
will not start and says why.
FAQ
Wouldn't a migration have prevented this? Yes, and that is the actual fix. A migration runs once, as its own step, and a failure fails the deploy. The reason it was a boot step instead is ordinary: applying indexes on startup is convenient, it works while there is one service, and nothing announces the day a second writer appears.
Can we just add the index now? Not until the duplicates are gone, which is the whole problem: you have to decide what a duplicate means before you can delete one, and that is a product question. Once the table is clean the index creates normally — and the same boot step that has been quietly failing will succeed on the next restart without anybody noticing that either.
Is this a database problem or an application problem? Neither, which is why it lasted. The database did exactly what it documents. Both services did what their own files said. The defect lives in the space between two repositories, and no single-repository tool — linter, test suite, review — is looking there.
Do we need the second service to import the first one's model? That is the tempting fix and it is the wrong shape: it makes one service depend on another's internals to stay correct, and the next copy re-creates the problem. Move the declaration out of both.
The three things worth taking away
Ask the database, not the repository. "Do we have a unique index" and "did somebody declare a unique index" are different questions with different answers, and only one of them is about production.
A uniqueness declaration is an operation that can fail, and it fails against data. It is checked when the index is created, so a declaration applied at startup is conditional on everything that happened before that startup — which means on a deploy order nobody recorded.
A catch block around a boot step is a decision, not a formality. Ours decided that the service
should start without the guarantee it exists to provide, and then said indexes synced about it, at
every boot, for as long as anyone cares to look.
Behind a market feed, the same split sits among the failures that raise no error at all, collected on market feeds that reconnect without their symbols.
If two of your services write one table and only one of them declares what makes a row unique, that is a cheap thing to have looked at.
