Blog · August 2, 2026

code_ref_update could set a PR's sha to any value you wanted — except null. That one, coalesce quietly refused.

code_ref_update patches a code reference's four mutable fields — sha, title, url, pr_state — from a JSON object. Send {"sha": "abc1234"} and only sha changes; the other three stay whatever they were. That's the entire contract, and it worked, for every value except one: there was no way to ever clear a field back to null once it had been set.

The bug was in how "unchanged" got decided

The original update used coalesce(nullif(p_patch->>'field',''), c.field) for each column. Read outside-in: nullif turns an empty string into SQL null, then coalesce falls back to the existing column value whenever the right-hand side is null. That's a compact way to say "empty or absent means leave it alone" — and it does, correctly, for the common case of an omitted key. The problem is what it does with an explicit null. p_patch->>'field' on a JSON key whose value is literally null also evaluates to SQL null. So {"sha": null} and {} produced the identical right-hand side, and coalesce couldn't tell them apart. A caller trying to say "this PR reference no longer has a sha, clear it" got silently ignored — the field stayed exactly what it was before, no error, no signal that the write did nothing.

task_update had already solved this, for one field. Its reviewer_actor_id column doesn't ask "is the new value present," it asks "is the key present" — p_patch ? 'field' instead of value-based coalesce. Key absent: untouched. Key present, value null: clear it. That's the distinction code_ref_update needed and didn't have, on all four of its fields.

Two representations of "nothing," one bit of information lost

The underlying issue isn't Postgres-specific — it's the general shape of a partial-update ("PATCH") API expressed as JSON. A JSON object has two ways to say a field is empty: omit the key, or include it with value null. Those are semantically different requests ("don't touch this" vs. "unset this") but they collapse to the same thing the moment your update logic asks "what's the new value?" instead of "was a new value supplied?" coalesce-on-value is the natural first draft precisely because it reads clean and passes every test that never tries to clear a field — which, for a young RPC, is most of them.

What shipped

Migration 0106 rewrites each column's set clause as a case on p_patch ? 'field' instead of a value-based coalesce — same signature, same return shape, no public-schema drift, so it shipped as a direct push rather than the two-phase dev-first dance a contract-changing migration needs. The TS SDK's codeRefPatch zod schema widened from .optional() to .nullish() on all four fields, because the old schema would have stripped an explicit null before it ever reached the RPC — fixing the SQL alone wouldn't have been reachable from any real caller. Empty string stays rejected client-side (min(1)); null is now the one sanctioned way to clear a field, not an accidental synonym for "unchanged." pgTAP covers both the explicit-null and explicit-empty-string clear paths against a live Postgres; three new validation unit tests pin the SDK-layer distinction between an omitted key and an explicit null.

Filed as a feature request from a defect audit of the code_ref_update family, not from a customer report — the bug never had a chance to surface as a support ticket, because nothing about a silently-ignored write throws.

Start at ledgenter.com.