LLBBL Blog

LLBBL Blog

Adding a Column With a Default Broke Every Idempotency Key

Adding a nullable column, or a column with a default, is the textbook example of a safe migration. Old rows get filled in, nothing breaks, no downtime. I’ve repeated that advice to other people.

Then I added one to a table whose rows are addressed by a hash of the request that created them, and the safe migration quietly invalidated every idempotency key already in the database.

How the replay works

The API is replay-first. A client sends an Idempotency-Key header with a job submission. If that same principal and key already exist, and the incoming request is equivalent to the stored one, the API returns the persisted job instead of doing the work twice. If the key matches but the request is different, that’s a 409, because the client reused a key for something else and that’s a bug on their side.

“Equivalent” is the interesting word. It’s implemented as a SHA-256 over the canonical JSON encoding of the normalized request:

func digestJSON(data []byte) string {
	sum := sha256.Sum256(data)
	return hex.EncodeToString(sum[:])
}

Normalization is what makes this defensible. You don’t hash the raw bytes off the wire, because then whitespace and key order would make two identical requests look different. You hash a struct that’s been round-tripped through your own types, with a few explicit fixups applied first.

One of those fixups is the problem.

The migration

I added agent routing to the job table. Two columns:

ALTER TABLE jobs
	ADD COLUMN requested_agent_id text NOT NULL DEFAULT 'auto',
	ADD COLUMN resolved_agent_id text;

ALTER TABLE jobs
	ADD CONSTRAINT jobs_requested_agent_id_valid CHECK (
		requested_agent_id = 'auto' OR requested_agent_id ~ '^[a-z][a-z0-9-]{0,62}$'
	);

Textbook. Every existing row gets 'auto', which is exactly what those jobs did behave like, since automatic selection was the only behavior that existed when they ran. The column is honest about history.

At the same time, the request normalizer picked up a matching rule: if requested_agent_id is missing or blank, set it to auto before hashing.

Also reasonable. Now {}, {"requested_agent_id": ""}, and {"requested_agent_id": "auto"} all hash the same, which is what you want.

Put those two reasonable things together and here’s what happens to a client that submitted a job last month and retries it today with the same key and a byte-identical body:

  • The stored digest was computed when the field didn’t exist. The canonical JSON had no requested_agent_id key at all, because the struct field is omitempty and the value was empty.
  • The new digest is computed with normalization applied. The canonical JSON now contains "requested_agent_id":"auto".
  • Different bytes. Different SHA-256. The digests don’t match.
  • The API concludes the client reused an idempotency key for a different request, and returns 409.

The client did nothing wrong. It sent the identical request, which is the exact scenario idempotency keys exist to make safe, and it got told it was conflicting with itself. The server changed its mind about what canonical form means, and every historical row was addressed under the old definition.

The fix is a compatibility set

The repair is to accept that a stored digest is written in whatever dialect was current when it was written, and to teach the reader more than one dialect:

out := requestDigests{
	canonical: json.RawMessage(canonical),
	primary:   digestJSON(canonical),
}
if normalized.RequestedAgentID == agentregistry.AutoID {
	legacy := normalized
	legacy.RequestedAgentID = ""
	legacyDigest := digestJSON(legacyCanonical)
	if legacyDigest != out.primary {
		out.compatible = append(out.compatible, legacyDigest)
	}
}

When the normalized request resolves to auto, compute a second digest with the field cleared back to empty, which reproduces the pre-migration encoding exactly. Then the comparison accepts a hit on either one.

The detail I’m happiest with is that the compatible digests are never stored. There’s no second column, no array of historical hashes on the row, and no backfill pass. They’re recomputed on each incoming request and compared against the single value already on the row.

That matters, because the alternative is rewriting the digest of every historical row to the new canonical form. To do that you’d have to re-marshal request bodies you accepted under older validation rules, and any row you can’t faithfully reconstruct becomes a row whose idempotency key silently stops working. A migration that can corrupt the thing it’s repairing isn’t much of a repair. Leaving old rows exactly as they are and making the reader bilingual has no failure mode worse than a cache miss.

Sorting the list would have been the bug

A few days later I added preferred_agent_ids, a bounded ordered list of agents to try, valid only when no explicit agent is requested. Capped at 16.

There’s a strong instinct, when you’re putting a list into a hash key, to sort it first. Sorting gives you stable digests regardless of client-side ordering, and for something like a set of tags it’s the correct move.

Here it would have been a bug, because the order is the request. ["hermes","antigravity"] means try Hermes first. ["antigravity","hermes"] means try Antigravity first. Those are different instructions that produce different work, so they have to produce different digests and they must never replay each other’s results. The list goes into the canonical JSON in exactly the order the caller sent it.

The general rule I pulled out of this: sort a collection before hashing only when the collection is actually a set. If order changes behavior, order is data.

The rest of that feature is deliberately fail-closed, in ways that are easy to get backwards:

  • An empty preferred_agent_ids: [] is a 422, not “no preference.” An empty list is a caller mistake, and treating it as a default would silently route work somewhere the caller didn’t ask for.
  • Preferences do not fall back outside the list. If none of the named agents are ready, the request fails with no_ready_agents, even when some other configured agent is sitting there ready. A preference list is a boundary, not a hint.
  • Duplicates, unknown IDs, and the reserved value auto inside the list are all rejected at submission time rather than at routing time.
  • The default priority order applies only when the list is omitted entirely. Hermes at 10, Antigravity at 20, the fake adapter at 100, lowest first.

What I’d tell past me

Your idempotency digest is a wire format. It’s not an implementation detail, even though it lives entirely inside your process and never appears in a response body, because rows in your database are addressed by it and those rows outlive your assumptions. Adding a field with a default is backward compatible for the table. It is not automatically backward compatible for anything you hashed.

So: write down what goes into the digest, treat that document as versioned, and before any migration touches a field that feeds it, work out what happens to a client replaying a request from before the change. If the answer is a 409 on an identical retry, you need a compatibility digest, and you need it in the same commit as the migration rather than after someone’s retry storm finds it for you.

I got lucky on the ordering question. I nearly typed sort.Strings.

Sources

I’d appreciate a follow. You can subscribe with your email below. The emails go out once a week, or you can find me on Mastodon at @[email protected].

api Go Postgres