Retention and erasure across sinks

Two obligations sit on stored conversations and they are frequently confused. A retention policy is a promise about how long content is kept in the ordinary course, usually implemented as a scheduled sweep. An erasure request is an out-of-band demand about one data subject — the person the content is about, which is the term the regulation uses, and the reason subject_id rather than user_id is the right key on the tables below, since the obligation follows the human even when the account is gone. Retention runs on a clock you set; erasure runs on a clock somebody else started, typically 72 hours wide. That 72 hours is an operator's commitment rather than a universal constant, since the statutory deadline depends on which regime you are under and on who is asking; none of the engineering below changes if your own number is different, only the arithmetic.

Content written once is read by several systems that each keep their own copy, deliberately and usually for good reasons, and a delete statement against the source table has no effect on any of them.

Six sinks, one of which you were thinking about#

Take a realistic stack: Postgres for messages, S3 for attachments, a nightly ELT sync into Snowflake for product analytics — extract from the source, load the raw rows into the warehouse, transform them there into the tables analysts query — an LLM trace store holding prompts and completions, 35-day point-in-time-recovery backups, and the model provider's own request logs.

A TTL, in a Postgres shop, is rarely a feature of the database at all; it is a scheduled job that deletes rows whose created_at is older than the retention window, and it reaches exactly as far as the tables somebody named in it. A 30-day TTL on the messages table satisfies the first row of that table and only the first row, and it does not even satisfy that one completely. The tables built up over this topic each hold content: a message_parts table carries tool payloads and thinking text, an attachments table carries filenames, a generations table carries bound_variables which are often a retrieved document or a summary of the user's own words, a conversation_head_log carries no content but reveals behaviour, and abandoned branches carry everything a live path does with no user action that would ever remove them. A TTL that names one table leaves five neighbours full.

The sweep is also on the wrong clock for the other obligation, which is easy to miss because a green run in the scheduler looks like compliance. A TTL fires on a timetable you chose and deletes by age, so a conversation that is a week old when the erasure request arrives is not old enough for the sweep to touch and will not become old enough for another three weeks. The sweep is a bulk operation keyed on created_at and an erasure is a targeted one keyed on a person, and neither query can be written as the other.

Reading down the last two columns of the sink table gives something more useful than six memorised rows, because the same two properties decide how any sink you have not met yet will behave. An erasure can reach a copy only if the copy can be located by subject, since a deletion you cannot address is one you cannot issue, and only if the sink supports removing one record without destroying what it is for. Postgres has both, which is why its row is measured in seconds. S3 has the second unconditionally and the first only where the object key, a prefix or a tag carries the subject. The trace store usually has the second and fails the first, since a span is filed under a trace id the SDK minted rather than under the person whose conversation it holds. Backups have the first in principle and fail the second outright, which is what the expiry_only row in the table is recording.

The latency column repays the same treatment, and adding it against the deadline decides whether an arrangement is feasible before anyone argues about whether it is elegant. A request that arrives an hour after the nightly sync has run waits for the next one, which is up to 24 hours away, and 24 hours rather than the average is the figure to plan against. Where the S3 path is a prefix lifecycle rule, its objects go within about 48 hours. Those two sinks are independent — neither waits for the other — so the elapsed time is the larger of the two rather than their sum, which is the 48 hours and leaves a day of the 72 for everything that fails and has to be retried. Make them serial instead, by having the warehouse merge wait until a lifecycle rule has been observed to apply, and 24 plus 48 consumes the entire commitment with nothing held back.

Delete the row, keep the copy#

The reason none of the other five sinks felt the delete is a property of what a delete is. A DELETE in Postgres, with foreign keys cascading from it, reaches every dependent row the constraint graph names, and that reach is genuinely complete: the engine walks its own catalogue inside one transaction, and no row it can see survives. The completeness is what makes the wrong conclusion tempting, because "the delete cascaded, so it is gone" is true of the database and says nothing about the other five places. A cascade is complete over a graph built from constraints that one engine enforces over its own tables, and there is no foreign key from a Postgres row to an S3 object or to a Snowflake row, since nothing exists that could enforce one. Every copy in another system was made by a separate write that carried the values across, and all that survives of the relationship is that two systems hold the same identifier. Removing the copy is therefore a second operation you have to issue, with its own failure mode and its own evidence.

Two of those sinks fail in ways that are worth understanding mechanically, because both look like they should work.

The warehouse fails because of how ELT is usually written. A nightly job selects rows where updated_at > last_watermark and appends them to a target table, which is efficient, restartable and blind to deletion by construction: a row that no longer exists produces no record, so the sync has nothing to carry.

That blindness is worth deriving rather than accepting, since it is a property of the query and not of the tool. A SELECT returns rows that exist, while a deletion is the absence of a row rather than a row in some particular state. An absent row has no updated_at for the predicate to compare, because there is no row left to carry the column, so no query of that shape can return it under any circumstances. Inserts and updates are the whole vocabulary available to the pipeline, and deletion is not an event it handles badly. It is not an event.

Every deleted conversation stays in Snowflake forever, along with every dbt model, aggregate and dashboard extract built on top of it. Fixing it means making deletion a thing that flows rather than a thing that is absent, which is a modelling change and not a configuration one:

CREATE TABLE erasure_tombstones (
  subject_id  uuid        NOT NULL,
  scope       text        NOT NULL,       -- 'conversation', 'message', 'attachment'
  entity_id   uuid        NOT NULL,
  emitted_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (scope, entity_id)
);

The tombstone table is synced by the same incremental job, because it is append-only and has an emitted_at watermark like everything else. Downstream, each model gains a MERGE that deletes the matching keys before it rebuilds, and the erasure is confirmed only when that merge has run and the derived tables have been refreshed. Change-data-capture achieves the same thing more elegantly where it is available, since a CDC stream carries delete events natively — the point is that something has to represent the absence, and an incremental append cannot.

What makes a tombstone work is that it converts the one event the pipeline cannot see into the one it can always see. A deletion in the source becomes an insertion into erasure_tombstones, and an insertion is exactly what an append-only load was built to transport. Nothing about the sync has to change; the fact changes shape until it fits the pipe that already exists. The rebuild step at the far end is there for a related reason. A derived table is a copy that was computed once and then stored, so the content in it does not vanish when its inputs do, and only a recomputation removes it. That is why the confirmation waits for the refresh rather than for the merge, and why a dashboard extract nobody rebuilds is a sink in its own right.

The trace store fails for a different reason: nobody chose its retention. Tracing is instrumented by whoever is debugging, the default retention on most vendors is somewhere between 14 and 90 days, the spans carry whatever the SDK captured, and the SDK for a model client captures the request body, which is the entire conversation.

Two properties of tracing are worth slowing down on, because together they explain how a whole conversation ends up in a second database with nobody having decided to store conversations there. A tracing SDK instruments a call by recording that call's arguments and its return value as attributes on a span, which is a general rule that knows nothing about what the arguments contain. The argument to a model client is the entire assembled request, so a general instrumentation rule and the shape of one API combine into a full copy of the prompt sitting in a store the deletion job has never heard of. The second property is the awkward one, because a span is filed under a trace id the SDK minted: unless somebody recorded the subject as an attribute, the store holds the content and holds nothing you can search it by. A vendor delete API keyed by trace id, as the sink table has it, is that limitation showing through rather than an implementation detail.

There are two workable postures. Either the tracing layer redacts content at emit time — record token counts, model, latency, prompt_version_id, tool_call_id, and a message id, so the span is a pointer into a system that does have a deletion path — or the trace store is treated as a first-class content sink with a per-subject deletion path and a retention window shorter than your commitment. The first is much cheaper and loses very little, because the ids in the span are enough to join back to the real content for as long as the real content exists.

Making propagation something the schema can enforce#

The general failure here is that "delete everywhere" lives in a runbook, and a runbook is a list that goes stale the week someone adds a sink. Two structures move it into the database.

The first is a per-request fan-out with explicit acknowledgement, so an erasure request is not a job that logs success but a state machine that cannot close while any target is outstanding:

CREATE TABLE erasure_requests (
  id           uuid PRIMARY KEY,
  subject_id   uuid NOT NULL,
  requested_at timestamptz NOT NULL DEFAULT now(),
  due_at       timestamptz NOT NULL,
  closed_at    timestamptz
);

CREATE TABLE erasure_targets (
  request_id   uuid NOT NULL REFERENCES erasure_requests(id) ON DELETE CASCADE,
  sink         text NOT NULL,
  state        text NOT NULL CHECK (state IN
                 ('pending','submitted','confirmed','not_applicable','expiry_only')),
  evidence     text,          -- job id, S3 delete-marker version, MERGE query id
  confirmed_at timestamptz,
  PRIMARY KEY (request_id, sink)
);

evidence is the column that turns the table from a checklist into an audit record: a delete-marker version id from S3, a Snowflake query id, a trace-store deletion receipt. expiry_only is an honest state rather than a euphemism — it is what backups get, and having a distinct value for it means a report can say precisely which subjects have content sitting in an immutable copy and until when.

What the two tables buy over a runbook is that completion stops being something a person asserts and becomes something the database computes. A runbook is finished when whoever ran it says so, and a step that was never written on the list leaves no trace of having been skipped, since the absence of a line is invisible. Under the schema above a request cannot be closed while any row beneath it is still pending, and a sink nobody has built a path for is a pending row that a query can find and a due date can page on. The failure moves from a silent omission to a visible outstanding item, which is the change that matters on the day the person who knew the list has left.

The second structure is a completeness check that fails in CI rather than in an audit. Every table that holds content gets a column or a foreign-key path leading to subject_id, and a test walks the information schema, lists every table with a text or jsonb column, and asserts that each one is either in the deletion graph or in an explicit allow-list with a written reason. A new table added on a Thursday either joins the graph or breaks the build, which is the only mechanism that reliably survives team turnover. It costs an afternoon to write and it is the difference between a deletion policy and a deletion intention.

That check has a boundary worth stating, because it is the sort of thing an audit finds rather than a test. It walks your own information schema, so it sees the tables in your database and nothing beyond them. S3, the warehouse, the trace store and the provider are not in that catalogue and never will be, so they stay in the erasure_targets list by hand and the enumeration of external sinks remains a human decision that needs reviewing like any other. What the check removes is the largest and fastest-growing source of new content, which is your own schema; what it leaves is a short list somebody has to keep honest.

What you tell legal about the backups#

Point-in-time recovery behaves the way it does under a deletion because of how it is built, and taking it apart is what stops the behaviour looking like a bug somebody could fix. A PITR system keeps a base copy of the database plus the write-ahead log — the ordered record of every change, which the engine writes before it applies anything — and a restore replays that log forward from the base copy up to whichever instant you name. A row deleted on Tuesday is present in the log's version of Monday, and a restore to Monday replays Monday's changes and stops before Tuesday's delete ever runs. Reproducing the deleted row is the design working rather than leaking, since a copy that could not reproduce the database as it stood on Monday would not be a backup. That is also why expiry_only is an accurate state name and not a euphemism: content leaves the window by the window's start moving past it, and the only lever anyone has on that is how wide the window is.

Setting the two windows against each other on the chapter's own figures gives a longer answer than most people expect. A message written today sits inside the retention promise for thirty days, and the sweep removes it from the live database at the end of those. The backup taken the day before that sweep still contains it, and that backup stays inside the recovery window for the full 35 days after it was taken. Laying the two stretches end to end puts the last moment the message can be brought back a little over two months from the day it was written, which is more than twice the number in the promise. No step in that chain is wrong on its own; each window is doing the job it was configured for, and the total is the part a customer reading the promise has no way to predict.

A 35-day point-in-time-recovery window and a 30-day retention promise are in direct contradiction, and no amount of tooling resolves it, because deleting one subject's rows from a physical backup destroys the backup's usefulness as a backup. Three honest responses exist and they are all trade-offs somebody senior has to sign.

Shortening the PITR window to 25 days makes the promise literally true and costs you recovery range, which is a real operational risk that the person promising 30 days is unlikely to have priced. Writing the exception into the commitment — content is removed from all live systems within 72 hours and from backups within the rolling 35-day window, with any restore re-applying outstanding erasures — is the arrangement most organisations actually run, and it requires the erasure log to survive a restore and be replayed against the restored database, which means that log cannot itself be inside the data being erased. The third option is encrypting each subject's content under a per-subject key and destroying the key on erasure, so the ciphertext in every backup becomes unreadable within seconds. That works, and its costs are usually understated: encrypted columns stop being queryable and joinable, key management becomes an availability dependency for reading any conversation at all, and a lost key is an unrecoverable customer rather than a restorable one.

The provider's own logs are the sink with the fewest options, and the one most often omitted from the diagram entirely. Model providers typically retain request and response payloads for a bounded period for abuse monitoring, on the order of 30 days, and that retention is a term in a contract rather than a knob in a console. What you can do about it is contractual and architectural: check whether zero-retention or reduced-retention terms are available on your plan, and keep the amount of identifying content in the request lower than it needs to be by default, since a system prompt that carries a customer's full record because it was convenient is a copy you have chosen to send. Record the provider as a sink in erasure_targets with state expiry_only and a documented window, so that a subject-access answer is complete rather than quietly missing a row.

One thing this design should not do is delete the tool_effects ledger. Financial records generally have their own lawful basis and their own statutory retention — commonly six or seven years — which sits alongside an erasure right rather than under it. How far that carries in your own case depends on the regime you are under and on which of your records are financial in the sense the rule means, so the step to insist on during a review is being shown the written basis for that specific table rather than being told that finance data is exempt. That conflict is resolvable precisely because the ledger holds a tool call identifier, an amount, a currency, an external reference and a state, and no conversation text. Minimised records survive an erasure request intact; the same facts recorded as a copy of the assistant's message would not. The same distinction cuts the other way for TTLs, since a scheduled sweep that has not fired yet is not a defence, and an erasure path that only knows how to run in bulk will miss its 72 hours: the two obligations need separate machinery because they run on different clocks.

Deletion is a property of a graph of systems, not of a table, and the only version of it that stays true is one the database can be asked about. That means a row per sink per request with an evidence column, an honest state for the copies you cannot surgically reach, and a build-time check that a new table carrying text has joined the graph. Everything else — the runbook, the quarterly review, the engineer who remembers that traces exist — degrades at exactly the rate the team changes.

Everything from the parts table onward can be adopted incrementally, and the order that tends to work is the order these chapters ran in. Move tool calls out of prose first, because it is the migration that becomes impossible later once the prose has accumulated. Keep the bytes and the thinking blocks next, since they cost almost nothing and cannot be reconstructed. Add the generation record when the first regression investigation stalls, add parent_id before the regenerate button ships rather than after, and put the effects ledger in place the first time a tool touches money. The deletion graph is the one to build while the number of sinks is still small enough to enumerate on a whiteboard, which is the only week it will ever be easy.