A policy says customer records are deleted seven years after the relationship ends. A team implements it literally and still fails the audit. What actually starts the clock, and what does that force into the design?

What the clock is attached to

The retention period is measured from a business event, not from when a row was written. End of relationship, last transaction, closure of a claim, expiry of a warranty, the year-end after a tax event. That event usually lives in a different table from the data it governs, and sometimes in a different system.

A created_at column is the wrong hook because the record that matters is often updated for years after it is created. Delete by row age and you destroy live records while keeping dead ones.

Why literal implementation fails the audit

Three mechanisms, in the order auditors find them:

• Partition-level retention deletes by partition age. Object-lock and lifecycle rules on an object store operate on objects, so a daily partition of 30M rows is deleted when the partition is seven years old, which is up to a year late for every record in it. Late deletion is a finding.
• The clock moves. A late-arriving event (a reopened claim, a payment nine years after the fact) resets the period. If the deletion date was computed once and stored, it is now wrong and nobody will notice, because nothing errors.
• Copies do not inherit the rule. The warehouse, the search index, the analytics extract and the vendor's copy each kept their own retention, usually "forever" by default.

What the design needs

1. Retention as a derived value, recomputed from the governing event each time the job runs, rather than a delete_after column written at insert.
2. A record class on every dataset with its period and its governing event. Five classes is usually enough; twenty means nobody maintains it.
3. Deletion that is idempotent and evidenced. The job records what it deleted and why, because the audit question is "prove it happened", not "show me the policy".
4. Crypto-shredding where per-record deletion is impractical. Encrypt per subject or per record class and destroy the key. This is how you satisfy deletion inside an append-only store or a partition you cannot rewrite, and the trade is that the data is unreadable rather than absent, which most regulators accept and some do not. Confirm which one you are dealing with before designing around it.

When not to build any of this

If every record in a dataset shares one class and the volume is small, a dated partition plus a lifecycle rule is correct and cheap, and a retention service costs more than the risk it removes. The threshold is mixed classes in one store: as soon as two retention periods share a table, the simple scheme is a compliance defect waiting for an auditor. Prefer splitting the table by record class over building a per-row engine — separating the classes in production is usually the cheaper half of the work, and it makes the lifecycle rule correct again.