Blog · July 24, 2026

Three sibling RPCs, three ways to say "that's not yours." code_ref_update is the one that doesn't say it at all.

Closing the pgTAP coverage sweep's last few gaps put three cross-tenant-write RPCs side by side in one week: task_link, handoff_create, code_ref_update. Same job — reject a write against a row you don't own — three different failure shapes, and the difference isn't a bug in any of them. It's a direct readout of how each function is allowed to find the row in the first place.

The other two already leak a "yes, but"

task_link's set_parent_task_id and handoff_create's related-task/related-project fields both go through composite foreign keys — (tenant_id, target_id), not just target_id. Point one at a row from another tenant and Postgres doesn't have to look anything up to know it's wrong: the FK simply doesn't resolve, and it throws 23503. handoff_create's recipient list is checked even earlier, with an explicit 23514 before the insert even runs. Either way, the caller gets an error that's specific to this row, this constraint — which, if you already suspected the row exists somewhere, quietly confirms it does.

code_ref_update has no FK to violate

update public.code_refs c set
  pr_state = coalesce(v_state, c.pr_state), ...
where c.tenant_id = v_tenant and c.id = v_id and c.deleted_at is null
returning c.object_type, c.object_id, c.pr_state into v_otype, v_oid, v_state;

if v_otype is null then raise exception 'code_ref not found' using errcode = 'P0002'; end if;

code_refs is addressed by primary key, and tenant_id = v_tenant is just another predicate in the same where clause — not a separate constraint the database enforces on your behalf. So a cross-tenant code_ref_id, a typo'd code_ref_id, and a code_ref_id that belonged to your own tenant until someone soft-deleted it all produce the identical outcome: zero rows match, v_otype stays null, and the function raises the same 'code_ref not found' / P0002 every time. There's no version of this error that tells you which of the three happened.

That's not a gap, it's the safer shape

A composite-FK throw and a P0002 not-found end up doing opposite jobs. The FK violation is honest about the fact that a row exists — it just isn't yours. The not-found error refuses to make that distinction at all: existing-but-not-yours and never-existed are indistinguishable from the caller's side, on purpose or not. For an internal RPC where every caller is already scoped to one tenant, that's not something we're actively defending — nobody's running an enumeration attack against their own code_ref_ids. But it's the shape you'd want for anything closer to a public boundary, and it's worth naming as its own pattern rather than something that happens to fall out of "we didn't add a foreign key here."

What shipped: code_ref_update now has pgTAP proving the own-tenant partial-patch (per-field coalesce, not whole-row replace), the pr_state-to-activity-verb mapping (merged emits pr.merged against the cited object), the 23514 on an invalid pr_state before the update runs, and the cross-tenant P0002 — the third distinct "not yours" shape this sweep has now pinned by test, next to task_link's silent decline and handoff_create's hard FK throw.

Start at ledgenter.com.