Retention and archives

A retention: block on an entity declares how old a row may get before the block anonymises, archives, or deletes it. A run is a walk over every retention-enabled entity in one datastore, in four fixed phases. Nothing schedules that walk — you trigger it from the CLI, the admin API, or your own cron.

The four phases

Each entity is taken through all four in this order, so a row that ages past two thresholds in one run still lands cleanly.

Order	Phase	Fires when	Row predicate	Effect
1	anonymize	action: anonymize and anonymizeafter (else active) is set	<agefield> < cutoff AND anonymized_at IS NULL	Overwrites compliance-tagged fields in place; sets anonymized_at
2	archive	archiveto is set and active is set	<agefield> < cutoff AND archived_at IS NULL	Copies rows to the archive target, or queues them; sets archived_at
3	archive_delete	archiveto is set	archived_at IS NOT NULL AND archived_at < now − grace, or the confirmed queue rows	Hard-deletes the archived rows from the primary table
4	purge	ttl if set, else purge	<agefield> < cutoff AND archived_at IS NULL	Hard-deletes the row

Thresholds are absolute ages measured from agefield (default createdon), never increments between phases. Purge skips rows with a non-NULL archived_at — those belong to archive_delete, which owns its own grace window.

A failure on one (entity, phase) pair does not stop the run. Every other pair still gets its turn and the failure count comes back in the result.

Declaring a policy

retention: sits on the entity, alongside history: and storage: — see Entities for where it fits in the entity block.

Key	Type	Default	Drives
active	duration	—	The archive threshold; fallback threshold for anonymize
purge	duration	—	The purge threshold
ttl	duration	—	The purge threshold; wins over purge when non-zero
archiveto	string	—	Names a target in the top-level archives: block
action	none | anonymize | delete	none	Whether the anonymise phase runs at all
anonymizeafter	duration	falls back to active	The anonymize threshold
agefield	string	createdon	The column every phase reads as the row’s clock
archive	duration	—	Nothing. Parses and is echoed by the discovery surfaces; no phase reads it
onfkconstraint	fail | cascade | anonymize_refs	fail	Nothing. Validated at load, never enforced
noautoindex	bool	false	Nothing. No retention indexes are auto-emitted

An unknown action or onfkconstraint value fails the load with the allowed set in the message. There is no such check on archives[].type — that one fails at run time.

Duration grammar

One number, one unit. Decimals are allowed.

Unit	Meaning
y	years, each exactly 365.25 days
d	days
h	hours
m	minutes
s	seconds
ms / us / ns	sub-second, for tests

90d, 7y, 30m and 1.5y are all valid. An empty string parses to zero without error, so an unset key is never a failure. Compound forms such as 1h30m are not the documented shape but fall through to the standard Go duration parser and are accepted. Anything neither parser recognises fails the schema load, naming the offending key and value.

Archive targets

Targets are declared once at the top level of the schema and referenced by name.

Key	Type	Default	Meaning
archives[].name	string	—	Name referenced by retention.archiveto
archives[].type	string	same_db_table	same_db_table, datastore, s3, gcs, azblob
archives[].datastore	string	—	Target datastore name, for type: datastore
archives[].suffix	string	_archive	Companion-table suffix, for type: same_db_table
archives[].settings	map	—	Free-form string map, echoed verbatim on the discovery endpoint

settings is a policy map — bucket names, prefix templates, compression hints. It is not a secret store; credentials belong in Vault.

archives:
  - name: cold
    type: same_db_table
    suffix: _archive

entities:
  - name: session
    retention:
      active: 90d
      purge: 10y
      archiveto: cold
      action: anonymize
      agefield: last_used_at

A policy with no purge, ttl or action never runs

An entity is considered active only when ttl > 0, purge > 0, or action is not none. active and archiveto are not part of that test. An entity declaring only active: and archiveto: is skipped entirely — no archive, no marking, no ledger row, and it does not even appear in preview output. To get an archive phase, set purge (or ttl, or action: anonymize) as well.

What the loader adds to your tables

Marker columns

Any entity carrying a retention: block gets two nullable timestamp columns appended to the end of its field list:

Column	Non-NULL means
archived_at	The row has been copied to the archive target, or queued for it
anonymized_at	The row’s compliance-tagged fields have been overwritten

These are the run’s idempotency mechanism: a row whose marker is already set is skipped, so an interrupted run resumes where it stopped. They are real DDL columns, they appear in the migration plan as ordinary add-column operations, and they appear in the API row shape.

Any retention: block injects them, including an inert one

The loader tolerates unknown keys. A block written in an older vocabulary — policy:, after:, archive_to:, condition:, the shape still present in several bundled example schemas — decodes to an all-zero config that is non-nil. You get both marker columns in the DDL and no retention behaviour whatsoever, permanently and silently. Check the key spelling against the table above.

The archive companion table

When archiveto points at a same_db_table target, the loader synthesises a whole sibling entity named <entity><suffix>, engine-managed, created at migrate time next to the primary. It copies every field from the primary with three adjustments:

• defaults dropped — the archive only ever receives INSERTs
• unique flags dropped — the same logical id can be archived more than once
• computed columns materialised — the value is stored as it was at archive time and never recomputed

Then it appends four columns:

Column	Contents
archived_at	Copy timestamp. Required
archived_by	Plan id of the run. Never populated on the same-database path
archived_reason	active_expired on every shipping write; subject_request:… and manual are reserved
archived_from_table	Qualified source table name

The primary key is reshaped to (<primary key columns…>, archived_at) so one logical id can hold many archived generations, and two indexes are created — <name>_archived_at_idx and <name>_archived_by_idx. Indexes declared on the primary are not mirrored.

Two load-time refusals apply here: an entity with retention and no primary key (declare one before enabling retention), and a companion name that collides with a declared entity (rename the entity or change the archive Suffix).

No companion is synthesised for the non-same_db_table types. Your ETL pipeline defines their destination.

Anonymisation

The pass targets exactly the fields carrying a pii, phi, pci or gdpr compliance modifier — see Field protection for how those are declared. Matching is case-insensitive; fields are processed in name order. The anonymized_at marker is set in the same UPDATE. The written value follows the field’s logical type.

Field shape	Written value
Nullable and not required	NULL
NOT NULL string, text	[redacted]
NOT NULL int, bigint	0
NOT NULL double, decimal, money	0.0
NOT NULL boolean	false
NOT NULL timestamp, date, time	Unix epoch, 1970-01-01
NOT NULL uuid, ulid	00000000-0000-0000-0000-000000000000
jsonb, array, bytes, enum, state, file	NULL, regardless of nullability

An entity with action: anonymize and no compliance-tagged fields is a silent no-op: zero rows, no error.

NOT NULL jsonb, array, bytes, enum, state and file columns fail the phase

There is no zero value for those types, so the pass emits a NULL literal even when the column is NOT NULL. The database rejects the UPDATE and the whole anonymise phase for that entity is recorded as failed. Preview does not warn about it. Make such a column nullable before tagging it.

Deletion semantics

Every destructive statement is a batched DELETE/UPDATE … WHERE <pk> IN (SELECT <pk> … LIMIT <batch>), looped until a pass affects zero rows. Each pass is its own transaction — multi-batch is deliberately not wrapped in an outer transaction, because incremental, resumable progress is the point. Batch size is 1000 rows with a 100 ms sleep between batches.

Phase	Statement	Deletes from
anonymize	Batched UPDATE, sets redaction values plus anonymized_at	—
archive (same-database)	Batched SELECT, multi-row INSERT into the companion, then UPDATE archived_at	—
archive (other targets)	Batched SELECT of primary keys, UPDATE archived_at, INSERT into the handoff queue	—
archive_delete (same-database)	Batched DELETE where archived_at is older than the grace window	Primary table
archive_delete (other targets)	Batched DELETE of confirmed queue rows, then finalize	Primary table
purge	Batched DELETE on the age predicate	Primary table

Rows therefore exist in both the primary and the archive for at least one grace window. The window is a run option, not a schema key: it defaults to one hour, and the CLI and HTTP API expose it in whole hours only (--grace-hours, grace_hours). Two operators running with different values on the same tenant get different behaviour.

Nothing ever deletes from an archive table

The companion carries no retention config of its own and the runner explicitly skips archive companions when building its work list. purge runs against the primary table and ignores rows whose archived_at is set. Archive tables grow without bound — prune them with your own SQL. The retention.archive duration, which reads like the archive’s own retention window, is stored and never consulted.

Retention on an entity with a composite primary key uses only the first key column, because the batched IN-subquery needs a single column. If that column is not unique on its own, a batch matches — and deletes or anonymises — more rows than intended. Give such an entity a single-column unique identity before enabling retention.

Archive targets that are not the same database

For datastore, s3, gcs and azblob, the block moves no data. It marks the primary rows and enqueues one row per primary row into the system table data_archive_handoff. Your pipeline claims a batch, reads the rows itself, writes them wherever the target says, and confirms back. The next run’s archive_delete phase reads the confirmed rows, deletes the primary rows, and finalizes the queue rows.

pending ──claim──► claimed ──confirm──► confirmed ──archive_delete──► finalized
                      │
                      └──report failure──► failed

Every transition is conditional on the current status, so re-runs and retried calls are no-ops: enqueue skips rows already in pending, claimed or confirmed; claim only takes pending; confirm only moves claimed; finalize only moves confirmed.

data_archive_handoff columns:

Column	Contents
id	Queue row id (primary key)
plan_id	Retention run that marked the row
entity, row_pk	Source entity and stringified primary key
target_name, target_type	The archive target this row is destined for
status	pending | claimed | confirmed | failed | finalized
marked_at	When the row was enqueued
claimed_by, claimed_at	Claiming pipeline’s label and time
confirmed_at	When the confirm landed
confirmed_by	Column exists; confirm carries no confirmer field, so it is never written
target_location	Storage URI reported on confirm. Audit-only
checksum	Integrity hash reported on confirm
error	Failure reason when status = failed
attempt	Retry counter

Three indexes are created — (status, target_name), (status, claimed_by) and (entity, status). The table also carries the four audit columns every entity gets.

Integration endpoints

POST /admin/data/retention/handoff/claim
{
  "datastore":   "primary",
  "target_name": "cold_storage",
  "entity":      "orders",
  "limit":       1000,
  "claimer_id":  "datapipe-job-2026-05-13-002"
}

200 OK
{
  "claimed": [
    {
      "handoff_id":  "01HVK7XYZ...",
      "entity":      "orders",
      "row_pk":      "ord_42",
      "target_name": "cold_storage",
      "target_type": "s3"
    }
  ]
}

datastore, target_name and entity are optional filters; claimer_id is required and a 400 without it. limit defaults to 1000. Claims are ordered oldest-marked-first and two claimers cannot take the same row.

POST /admin/data/retention/handoff/confirm
{
  "datastore": "primary",
  "confirmations": [
    {
      "handoff_id":      "01HVK7XYZ...",
      "target_location": "s3://bucket/acme/orders/2026-05/01HVK.parquet",
      "checksum":        "sha256:abc..."
    },
    { "handoff_id": "01HVK7YYY...", "failed": true, "error": "upload timed out" }
  ]
}

200 OK
{ "confirmed": 1, "failed": 1 }

Rows not currently in claimed are ignored, which is what makes a retried confirm safe. An empty confirmations array is a 400.

Two facts to design your pipeline around:

• The queue stores (entity, row_pk), never the payload. Your pipeline reads the row at pickup time, so it archives the value as of pickup — not a snapshot as of marking. An update that lands after the pipeline’s read and before the cleanup DELETE is lost: the archive holds the older value and the primary row is gone.
• Marking and enqueueing are separate statements. A crash between them leaves a row marked but not queued, or queued but not marked. The next run re-evaluates from primary state and tops the queue up, so a marked row can sit with nothing queued to move it until then.

The duplicate-prevention guarantee is application-level. The partial unique index the design calls for is not expressible in the index model, so nothing in the database blocks a duplicate queue row — the enqueue statement’s existence check and the run lock do.

Backend support

Retention statements are emitted with ? placeholders and never rebound

Every retention statement — the eraser, the handoff queue, the same-database archive target, and the preview count — is built with ? placeholders. Unlike the migration ledger, lock manager and version store, none of them rebind to the backend’s placeholder style. PostgreSQL uses $1 (see the placeholder row of the backend matrix) and is the only backend reachable from tenant configuration, so on a deployed tenant the driver rejects these statements: apply records every phase that reaches the database as a failed step, and preview reports 0 because it swallows count errors. The retention test suite runs against the embedded analytical backend, which accepts ?, so nothing in CI catches it. Treat retention as unenforced on PostgreSQL until this is fixed.

Everything else on this page — the loader surface, the marker columns, the companion table, the discovery endpoints, and the queue’s state machine — is backend-independent and behaves as described.

Running a pass

There is no in-process scheduler. Runs come from the CLI, the admin API, or whatever you already use for scheduling (a Kubernetes CronJob, a pipeline DAG).

CLI

data.svc data retention preview --tenant acme:shop:dev:main -f bootstrap.yaml
data.svc data retention preview --tenant acme:shop:dev:main --entity orders
data.svc data retention preview --tenant acme:shop:dev:main --phase purge --phase archive
data.svc data retention apply --tenant acme:shop:dev:main --dry-run
data.svc data retention apply --tenant acme:shop:dev:main --entity audit_events --phase purge
data.svc data retention apply --tenant acme:shop:dev:main --grace-hours 24
data.svc data retention archives --tenant acme:shop:dev:main
data.svc data retention entities --tenant acme:shop:dev:main
data.svc data retention pending --tenant acme:shop:dev:main --status failed

The registered subcommands are exactly preview, apply, pending, archives and entities. There is no status subcommand — use data plan show and data applied list for run history. --tenant and --datastore are shared by every data subcommand; --entity and --phase repeat.

HTTP

Method	Path	Purpose
POST	/admin/data/retention/runs	Execute a pass. Synchronous — blocks until the run finishes
GET	/admin/data/retention/preview	Eligible row counts per (entity, phase). Writes nothing, takes no lock
POST	/admin/data/retention/handoff/claim	Pipeline claims a batch of queued rows
POST	/admin/data/retention/handoff/confirm	Pipeline reports per-row outcome
GET	/admin/data/retention/handoff/pending	Queue depth and age by (entity, target, status)
GET	/admin/data/retention/archives	Declared archive targets. Pure schema read
GET	/admin/data/retention/entities	Entities with an active policy, their thresholds, and their compliance-tagged fields in pii_fields[]

All seven require a JWT. The four read routes take the admin role; the three destructive ones — runs, handoff/claim and handoff/confirm — require superadmin in data.svc, so a tenant admin calling them gets 403 v2.requires_superadmin. claimer_id is a tracking label, not a credential.

POST /admin/data/retention/runs takes an all-optional body — datastore, entities, phases, dry_run, batch_size, max_rows_per_pass, grace_hours — and an empty body is valid. Unknown phase names are dropped silently; an empty phases list means all four. It answers 200, or 207 Multi-Status when any pair failed, with per-entity counts and, on a dry run, the eligible map.

Two body fields are inert: batch_size and max_rows_per_pass are carried into the run options and never read. Batching is fixed at 1000 rows and there is no cap on rows per pass. The same applies to --batch-size and --max-rows on the CLI.

GET /admin/data/retention/entities renders durations with the standard Go duration formatter, so a week comes back as 168h0m0s rather than in the y/d/h grammar the loader accepts. Its pii_fields[] matches the compliance tag case-sensitively while the anonymise pass matches case-insensitively, so a field tagged PII is overwritten by a run but missing from this listing. Write the tags lower-case.

Concurrency

A non-dry run takes a per-(tenant, datastore) advisory lock for its duration, shared with migrations and seed runs, and releases it on exit. A concurrent trigger fails fast rather than queueing. Dry runs skip the lock entirely.

The audit trail

A non-dry run opens a row in data_plans (status: running, max_risk: high, plus the trigger label and principal), writes one data_applied_changes step for each (entity, phase) pair that has work — kind: retention, the phase name as the schema op — and closes the plan with applied or failed and the aggregate counts. A phase an entity has no threshold for is skipped silently and writes no step, so the plan’s total_steps — entities × phases, fixed at open time — is an upper bound, not a count. All three tables carry plan_id, so one run reconstructs end to end:

SELECT 'plan' AS source, NULL::text AS entity, status, started_at AS at
FROM data_plans WHERE plan_id = 'RR-...'
UNION ALL
SELECT 'step', entity, status, finished_at
FROM data_applied_changes WHERE plan_id = 'RR-...'
UNION ALL
SELECT 'handoff', entity, status, confirmed_at
FROM data_archive_handoff WHERE plan_id = 'RR-...'
ORDER BY at;

A dry run and the preview endpoint write nothing to the ledger.

A preview of zero is not proof of a no-op

The eligible-row count returns 0 both when nothing is eligible and when the count query fails — the error is swallowed so a preview cannot crash a run. It also returns 0 whenever the wired eraser is not the SQL implementation. Read a zero preview as “no information”, not as “the apply is safe”.

Declared and inert

Everything in this table parses cleanly and changes nothing at run time. None of it is a substitute for the keys that do work.

Declaration	Reality
retention.archive	Echoed by data retention entities and the discovery endpoint; no phase reads it. Nothing purges an archive table
retention.onfkconstraint	Validated at load, never enforced. A purge that violates a foreign key fails with the driver’s error and is recorded as a failed step; the FK-blocked counter is always zero
retention.noautoindex	Never read. No retention indexes are auto-emitted — index agefield, archived_at and anonymized_at yourself
action: delete	Accepted, but no phase branches on it. Alone it makes an entity look active and produces zero work. Use ttl: or purge:
compliance.retentiondays, compliance.retentionaction	Legacy entity-level keys. The runner reads only retention:
retention as a field-level compliance modifier	Resolves into the field’s posture; no per-field timer exists
batch_size, max_rows_per_pass, --batch-size, --max-rows	Carried into run options and never read

There is no restore-from-archive command or endpoint. Recovering an archived row is your own SQL against the companion table, or your own pipeline against the external target.

Related

For	See
Where retention: sits among the other entity keys	Entities
Declaring the pii/phi/pci/gdpr modifiers the anonymise pass reads	Field protection
Backends, placeholder styles, and schema qualification on shared pools	Datastores
Every route, parameter and status code	Data API reference