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. A run comes from the service’s scheduler, 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 and a copy in the archive table, 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; for an entity with archiveto, <agefield> < cutoff on the archive table	Hard-deletes the row, or the archived copy

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.

An entity that declares archiveto keeps its primary rows until they are archived: its purge phase deletes archived copies from the archive table, never primary rows, and a copy only once archive_delete has removed its original. For a same-database target that is the companion table; a target outside the database holds its copies and ages them out itself, so purge has nothing to do there.

The phases of one entity build on each other, so a failed phase ends that entity’s run: its later phases are reported as skipped and wait for the next run. Every other entity 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	Accepted and validated at load; not applied at purge time
noautoindex	bool	false	Nothing. No retention indexes are auto-emitted
batchsize	int	1000	Rows per statement for this entity’s anonymise, archive and purge passes; a run’s batch_size overrides it

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. A negative age fails the load. 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

An entity takes part in a run when it sets ttl or purge, sets action to something other than none, or sets both active and archiveto. The last is an archive-only policy: it gets the archive and archive_delete phases.

Keys the runner needs

Every retention statement picks its rows by a single primary-key column. A retention: block fails the load on an entity whose primary key has more than one column, and on an entity with type2 or type6 history, whose versions share one key. A type4 or typeaudit entity keeps its history in a side table and takes retention.

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

A retention: block written in an older vocabulary, policy:, after:, archive_to:, condition:, the shape still present in several bundled example schemas, fails the load with the unknown key named. A block that loads is one the runner reads; its marker columns are then in the DDL for a reason. 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.

The pass also treats the copies of the entity’s rows that are already archived. For a same-database target, the companion table’s rows past the anonymise age get the same values and their own anonymized_at. An external target holds its copies outside the database; anonymise them in your pipeline.

The entity’s history side tables are treated with the rows: a type-4 history table gets the same redactions for the anonymised rows, live or archived, and an audit table has old_value and new_value nulled for the anonymised fields of those rows. Each batch treats the side tables before it sets anonymized_at, so a run that stops partway leaves the batch unmarked, and the next run treats it in full.

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 (…) over at most one batch of primary keys, looped until a batch finds no more 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 SELECT of primary keys, UPDATE of the history side tables, then UPDATE of the rows, 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 and the copy is in the companion table	Primary table
archive_delete (other targets)	Batched DELETE of confirmed queue rows, then finalize	Primary table
purge	Batched DELETE on the age predicate; for an entity with archiveto, of the copies whose original has left the primary table	Primary table, or the companion table for an entity with archiveto

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.

The companion table carries no retention config of its own. Its rows age out through the primary entity’s purge (or ttl) threshold: the purge phase of an entity with archiveto deletes the archived copies whose agefield is past that age and whose original has left the primary table. A row archived past its purge age keeps its copy through the grace window, and the copy goes in the first run after archive_delete removes the original. The retention.archive duration is stored and not read by any phase.

archive_delete removes a primary row only once its copy is in the companion table. A row marked archived_at with no copy there, for example one queued for an external target whose type was later changed to same_db_table, stays in the primary table. Drain the handoff queue before changing a target’s type.

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 takes pending rows, and claimed rows whose claim is more than an hour old; 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	The claim: the pipeline’s claimer_id followed by # and a token unique to the claim call, and its 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",
      "claim":       "datapipe-job-2026-05-13-002#01HVK80ABC..."
    }
  ]
}

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 each row goes to one claimer: the response lists only the rows this call holds.

A claim holds its rows for an hour. A row still claimed an hour later, because the pipeline that took it stopped, is claimable again, so a crashed task’s rows are picked up by the next one.

POST /admin/data/retention/handoff/confirm
{
  "datastore": "primary",
  "confirmations": [
    {
      "handoff_id":      "01HVK7XYZ...",
      "claim":           "datapipe-job-2026-05-13-002#01HVK80ABC...",
      "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. A confirmation that carries the row’s claim counts only while that claim still holds the row, so a task whose claim expired and was taken over cannot confirm over the task that holds it now; send it back as the claim response gave it. 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 rebound to the backend’s placeholders

Every retention statement, the eraser, the handoff queue, the same-database archive target and the preview count, is written with ? placeholders and rebound to the backend’s style before it runs: $1, $2, … on PostgreSQL, ? elsewhere. The functional suite runs a full retention pass (preview, dry run, run, the per-row outcomes) against PostgreSQL.

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

The service’s scheduler runs a pass for every tenant on the interval set in scheduler.retention.every, see Configuration. Runs also 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:dev:shop:main -f bootstrap.yaml
data.svc data retention preview --tenant acme:dev:shop:main --entity orders
data.svc data retention preview --tenant acme:dev:shop:main --phase purge --phase archive
data.svc data retention apply --tenant acme:dev:shop:main --dry-run
data.svc data retention apply --tenant acme:dev:shop:main --entity audit_events --phase purge
data.svc data retention apply --tenant acme:dev:shop:main --grace-hours 24
data.svc data retention archives --tenant acme:dev:shop:main
data.svc data retention entities --tenant acme:dev:shop:main
data.svc data retention pending --tenant acme:dev:shop: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 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.

batch_size (and --batch-size on the CLI) sets the rows per statement for that run and wins over the entity’s retention.batchsize; unset, the entity’s value applies, then the runner’s default of 1000. max_rows_per_pass and --max-rows are carried into the run options and never read: there is no cap on rows per pass.

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.

A run that changes rows invalidates the entity cache for each entity it changed, the entities its cache.invalidaterelated names, and its archive companion.

The audit trail

A non-dry run opens a row in data_schema_upgrade (status: running, max_risk: high, plus the trigger label and principal), writes one data_schema_upgrade_step 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 the run’s id, so one run reconstructs end to end:

SELECT 'plan' AS source, NULL::text AS entity, status, started_at AS at
FROM data_schema_upgrade WHERE upgrade_id = 'RR-...'
UNION ALL
SELECT 'step', entity, status, finished_at
FROM data_schema_upgrade_step WHERE upgrade_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. The archive table ages out on the purge threshold
retention.onfkconstraint	Validated at load; not applied at purge time. 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
max_rows_per_pass, --max-rows	Carried into run options and never read

Restoring an archived row

POST /admin/data/retention/restore is the reverse of the archive and archive-delete pair, per row and by primary key:

curl -X POST https://api.example.com/data/admin/data/retention/restore \
  -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -H "X-Customer: acme" -H "X-Product: erp" -H "X-Env: prod" -H "X-Tenant: main" \
  -d '{"entity":"event_log","ids":["01J...","01K..."]}'

{ "entity": "event_log",
  "results": [{"id": "01J...", "outcome": "restored"}, {"id": "01K...", "outcome": "already_live"}],
  "counts": {"restored": 1, "already_live": 1} }

Outcome	Meaning
restored	The primary row was gone; the archive copy has been written back
unmarked	The primary row was still there within the grace window; its archived_at is cleared
already_live	The primary row is there and unmarked. Nothing was written: a live row is never overwritten with an older copy
not_found	Neither the primary nor the companion table has that id

Each row restores in its own transaction, and the companion copy is removed once the row is back, so the two tables never both claim it. ids is required: restoring everything ever archived is not something this route will infer. An entity that declares no archiveto, or one whose archive is an external target rather than a companion table, is refused naming the reason. Rows that left through the handoff queue come back through that pipeline.

A restored row is subject to the same retention: declaration as before. If it still matches the age predicate, the next run archives it again: restoring buys you the row, not a change of policy.

Recovering from an external target is still your own pipeline against that 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