DR-121

Orphan-but-filter soft-delete pattern

consistency · persistence · · Enforced at gate-time, runtime

Origin

Established during Phase 11 of the rebuild (2026-04-07) after the revised Dispatch 83 framing. Earlier drafts framed certain children as “FK CASCADE exceptions”; that framing conflated the application layer with the FK layer. The revised rule separates the two layers and documents each independently.

Rule Text

When a parent entity is soft-deleted (sets deletedAt via softDeleteSet() per PER-1), no cascade UPDATE runs against any child entity. Each child retains its independent deletedAt state at the moment the parent was removed. Every subsequent read on a child entity joins through the parent with notDeleted(parent.deletedAt), so children whose parent has been soft-deleted become unreachable via every application code path: list, getById, search, aggregate.

Two layers operate independently:

  1. Application layer (universal). The soft-delete sets only parent.deletedAt. No cascade UPDATE runs against any child table. Reads filter via parent JOIN plus notDeleted(parent). This is the pattern every domain enforces.

  2. FK layer (per-table). Most child tables declare onDelete: cascade on the FK as a defense-in-depth safety net for the hard-delete case that is separately forbidden. A small number declare onDelete: set null because the FK is nullable and the child is designed to survive parent removal. Because hard delete is forbidden on soft-deletable tables, neither FK clause ever fires in production code. The clauses exist for auditors reading the schema, not for the application.

The audit log is the single exception. Audit entries are preserved for legal retention (seven years) and are NOT orphan-filtered. An audit entry’s entityId still points at the soft-deleted entity’s UUID, and audit reads display the entry verbatim. Cascading or filtering the audit log would erase the forensic record of what happened, which is the opposite of what an audit log is for.

Testable Assertion

// Soft-deleting the parent leaves every child's deletedAt untouched:
const before = await rawDb.select().from(childTable).where(eq(childTable.parentId, parentId));
await softDeleteParent(parentId);
const after = await rawDb.select().from(childTable).where(eq(childTable.parentId, parentId));
expect(after.map((r) => r.deletedAt)).toEqual(before.map((r) => r.deletedAt));

// Every child read path returns zero rows for orphaned children:
expect(await caller.children.list({ parentId })).toEqual({ items: [], nextCursor: null });

// Audit log entries for the soft-deleted parent remain visible:
const audits = await caller.activity.byEntity({ entityId: parentId });
expect(audits.length).toBeGreaterThan(0);

Enforcement

  • Gate-time — An ast-grep rule forbids cascade-UPDATE patterns on child tables during a parent soft-delete (e.g., a child softDeleteSet() inside the same transaction as the parent softDeleteSet()). The pattern is supposed to never appear; if an agent writes it, the gate rejects the commit.
  • Runtime — Every child list, getById, and aggregate procedure joins through the parent with notDeleted(parent) via the established middleware pattern. If an agent drops the join (which would expose orphaned children), the broader consistency rules catch it: no child read procedure omits the parent join.

Violation Closed

Cascade-soft-deletion was the intuitive implementation. It did not work. Three failure modes closed by orphan-but-filter:

  1. Partial cascades. A cascade-UPDATE over 18 child tables in a single transaction is fragile. One failure leaves the parent soft-deleted with some children cascaded and others not. The orphan-but-filter pattern has no multi-row cascade; the parent UPDATE is atomic, and orphaned children are invisible regardless of their row state.

  2. Restore complexity. Restoring a soft-deleted parent under cascade requires knowing which children to un-cascade. Under orphan-but-filter, restoration is a single UPDATE on the parent. Every child is already visible again through the join.

  3. Audit leakage. Cascade-updating the audit log would erase the record of what happened. Leaving the audit log untouched, and explicitly NOT orphan-filtering it, preserves the forensic history.

The rule turns a class of distributed-mutation bug into a single-row write with a universal read-side filter.

Last updated