Flow
Saving a plan and handling a rejected insert
The lookups run after the failed transaction has rolled back.
1Insert both rows
Plan row and key row in one transaction.
2Database picks a winner
The loser gets a uniqueness error and rolls back completely.
3Look up the key
Same request hash replays the receipt. A different hash is refused.
4Look up the plan
No key row but a matching maker, run and hash means already received.
5Neither found
The original error goes back up.
Flow
Ways to deal with a blank in a unique rule
I went with requiring it at the door.
1Require it at the door
Simple, and the sender learns what it forgot. Other writers are not covered.
2Make the column not null
Covers every writer. Old blank rows need a decision first.
3Let blanks match in the index
Stops the copies, but unrelated plans start blocking each other.
4Remove the skip in code
Needed either way: a lookup that skips blanks repeats the hole.
By the end of the day, my guard against storing the same plan twice had a hole one empty field wide. I put it in two places: the database and my own code.
The ordering system gets its plans from a separate planning service now. Its job is to check each plan, keep one copy and send back a receipt. Approving the plan comes later. This was only about the receipt.
The front door
The sender's permissions come from its login, worked out on my side. If the plan claims extra permissions, that's ignored. Each plan must list the approved inputs it was built from. The old shortcut that wrote plans straight into the table is gone.
Two rows or none
Taking a plan writes two rows in one transaction. One is the plan and its receipt. The other is a key row with the sender's retry key, a hash of the request and a link to the plan. Both save or neither does.
The key row is unique, and that rule settles races. If you check first and insert second, two requests can both see nothing and both write. So both insert, the database keeps one, and the loser rolls back. The loser then reads the winner's key row. Same request hash: send back the stored receipt. Different hash: the key was reused for different content, so refuse it.
My tests cover a create, a replay, a conflict and a race. They all use a fake database.
When the run was left blank
A retry key tracks an attempt, not the content. The same plan sent with a new key looks new. So I added a second unique rule on the plan: who made it, which run, and a hash of the content. If that matches, the sender hears the plan was already received.
The run was optional, and in Postgres two blanks never count as equal:
insert into plans values ('planner', null, 'abc');
insert into plans values ('planner', null, 'abc'); -- also goes in
So plans with no run slipped past the rule. My own lookup also gave up early when the run was missing. A plan without a run got no protection at all. A fake database can't show you this, because it only errors when the test says so.
Making the run required
Later that day I made the run required when a plan comes in, and removed the early exit. A test sends a plan with no run and expects it to be turned away.
Other ways exist. A not-null column covers every writer, but old blank rows need sorting out first. Telling the index that blanks match makes unrelated plans block each other. Turning the plan away at the door has one real upside: the sender learns exactly what it forgot.
Two migration traps
Migrations run in name order. Mine would have built an index before its column existed on a fresh database, so I renamed the file. Postgres also shortens long names, so I pinned the short name to match my schema file.
The blank the table still takes
I haven't seen the blank case tried on a real database. My notes say to prove it on a throwaway copy before a real planner connects. The table can also still store a blank run, because only the front door checks. Anything that writes plans around that door brings the hole straight back.