Blog · August 4, 2026

A foreign key rejects a row from the wrong tenant. It has no opinion on a row from the right tenant that's been soft-deleted.

Six RPCs, one bug class, six migrations, six pull requests, over eleven days. Not six separate discoveries — one finding, and then a mechanical sweep to check whether it was really contained to the RPC where it was first noticed. It wasn't.

Where it started

task_link lets a task declare a dependency or a parent — p_task_id, plus arrays of ids to add or remove. Every mutating branch was already correctly tenant-scoped: the dependency insert and the parent-id update both carry a composite foreign key, (tenant_id, task_id) -> tasks, so a wrong-tenant id or a typo'd uuid gets rejected with Postgres error 23503. That part worked. What it didn't check was whether p_task_id itself — the task being linked from — actually resolved to a live row at all. Call it with a stale id after the task was deleted, or a copy-pasted id from the wrong task, and every branch fell through as a no-op. The RPC still returned {"ok":true,"cycle_rejected":[]}. A caller bug that should have surfaced as an error surfaced as silent success instead.

The fix, shipped as migration 0107, was one existence check: select ... into v_found where tenant_id = v_tenant and id = p_task_id, raising P0002 if nothing came back. Small, obvious in hindsight, and worth asking: is this shape anywhere else?

The shape that kept recurring

It was. repo_link (0108), code_ref_add (0109), and task_create (0110) all had the identical gap — an RPC that inserts or mutates a row referencing some other id purely through a composite FK, with no explicit check that the referenced row still exists and belongs to the caller. Four RPCs into the sweep, the pattern looked closed.

Then decision_log got a new parameter. Task #54 wired up decision-to-task linkage — p_related_task_ids, added the same day as a defect-audit pass that covered everything except decision_log, because that pass had scoped itself to the task_*/repo_*/code_ref_* families and decision_log wasn't in any of them. The new parameter inherited the gap on day one, on code that had never been through a defect-audit pass at all. Worse: decision_log's existing p_project_id had the same gap and had simply never been caught, because the earlier sweep's boundary didn't include it either.

That's the part worth sitting with. A composite FK is real protection — it closes off cross-tenant writes and nonexistent ids completely, 23503 every time. But (tenant_id, project_id) -> projects says nothing about deleted_at. A soft-deleted project in the caller's own tenant still satisfies that FK, because the row is still there — just marked gone. The FK can't distinguish "this project doesn't exist" from "this project existed, and someone deleted it five minutes ago." Only application code that explicitly filters deleted_at is null can make that distinction, and nothing did.

Migration 0113 added that check to decision_log, on both p_project_id and the new p_related_task_ids (a set-based anti-join, since it's an array). p_supersedes_decision_id was deliberately left alone — decisions are append-only, no deleted_at column, no delete path, so there's no soft-delete state for that particular FK to ever miss.

Closing it out

One RPC remained with the identical shape: knowledge_write, filed as its own task while fixing decision_log rather than folded into the same PR, to keep each migration scoped to one function. Migration 0114 added the same check, same pattern, same test shape — reusing the soft-deleted-project fixture that later sections of the isolation suite already seeded, upgrading a pre-existing cross-tenant assertion from 23503 to P0002 in the same pass so the two error codes stay documented at the same call site instead of drifting apart.

Six RPCs now share the identical guard: select ... into v_found where tenant_id = v_tenant and id = ... and deleted_at is null, raising P0002 before any mutation runs. task_update and task_get had this from the start — they were the reference the whole sweep kept checking new code against — which is a decent way to describe how this class of gap gets closed: not by writing a rule and enforcing it everywhere at once, but by having one correct RPC on file and asking, every time something new touches a foreign key, "does this actually match the pattern, or does it just look like it does."

Start at ledgenter.com.