lifecycle retention and history

why

whether a fact is active, recoverable, auditable, or gone is a business promise. an implicit deleted_at flag makes every query, unique key, foreign key, and aggregate guess that promise independently — so they eventually disagree. an explicit lifecycle contract makes restore windows, purge schedules, and audit evidence owned and provable instead of folklore, and keeps compliance retention and privacy deletion honest: each row's history answers "who removed this, when, and can it come back" from durable state.

rule

model whether a fact is active, archived, deleted, retained, or immutable as an explicit lifecycle contract. choose one source of historical truth before adding deleted_at, an audit log, or a copy table.

choose history by the question it must answer

need	durable shape
current fact may disappear permanently	hard delete after a declared retention/export boundary
current fact must be recoverable briefly	explicit archived/deleted state with restore owner and expiry job
prove who changed a sensitive fact	append-only audit event with actor, time, subject, before/after-safe fields
reconstruct business transitions	immutable domain events plus a current projection
retain evidence for compliance or recovery	retention class, purge schedule, legal hold rule, restore proof

a soft-delete timestamp is not a universal safety switch: it changes every unique key, foreign-key policy, query, aggregate, export, and access check. use it only when restore is an approved product behavior with an owner.

lifecycle invariants

state the transition authority and allowed next states
archive ≠ delete; archive remains a readable product fact
make purge idempotent; derived projections and object storage follow the same retention decision
never edit historical events to repair current state — write a corrective event or update the current projection

good and bad

bad

-- schema change: add a soft-delete flag
alter table customer add column deleted_at text null;

// queries must now remember to filter
const activeCustomers = await db.select().from(customers).where(isNull(customers.deleted_at));

// and some forget
const customerById = await db.select().from(customers).where(eq(customers.id, customerId));
// ← missing the deleted_at filter!

failures: (1) two reads disagree on whether a customer exists; (2) unique(email) collides when two deleted customers share an email, forcing unique(email, deleted_at); (3) foreign keys break — can you still reference a deleted customer?; (4) aggregates lie — count(customers) is ambiguous; (5) recovery window undefined — deleted rows pile up until someone manually purges; (6) no audit — no record of who deleted or when restore expires.

good

the lifecycle is now explicit: schema enforces states, unique constraints work, every read states which statuses it includes, the restore window is durable (purge_after), the audit log records who deleted and when, and the purge job is ownership-clear, debuggable, replayable.