ADR-042: Data retention — four-state lifecycle, derived state via views, forward-trigger enforcement

Context

Spectral needs a retention model that survives three context boundaries (core, worlds, platform), aligns with the ContentClass taxonomy fixed in ADR-036 D10 (PLATFORM = customer-facing data in the platform context; WORLDS = worlds-context operator-authored data; OPERATIONS = operations-context Spectral-operated reasoning; SYNTHETIC = test-agent), and reconciles with ADR-040’s DR posture (Supabase-native managed backups + PITR; no self-run pg_dump pipeline). SPEC-289 had deferred “formal retention policy” to post-alpha; this ADR partially supersedes that deferral.

Research progression during disposition:

1. Landscape survey — market-default retention (14–30d entry tier, 90d mid-tier; Langfuse / Datadog / Braintrust / Logfire); FTC Safeguards Rule (does not bind Spectral directly; flows through partner contracts); GDPR A17 “put beyond use” backup conventions; pg_cron native on Supabase; partitioning not warranted at alpha.
2. Adversarial — argued for full deferral (“keep forever is correct for eval-platform signal extraction; documented policy without enforcement is a liability; spectral.core.retention constants violate ADR-065 admission discipline”). Directionally right that time-based TTL alone is wrong but over-indexed on deferral.
3. Founder reframe (load-bearing): retention is a reference-graph problem, not a time problem. Audit-chain records cited by published world-model versions cannot be deleted by age — that breaks auditability + regression baselines + signal replay. State-based vocabulary emerged.
4. Second reframe: an initial five-state draft (with PENDING as a pre-scan state) leaked context-specific concerns into a vocabulary shared across contexts. Refined to four context-agnostic states.
5. Third research pass — pattern research on the four-state model. Each state maps to established literature (Cassandra tombstones, generational-GC weak hypothesis, eDiscovery legal-hold). Derived state via views (no stored retention_state column) is the DDD-consistent approach. deleted_at timestamptz NULL is the one stored bit.

Decision

D1 — Four-state universal retention vocabulary

RetentionState = {ACTIVE, REFERENCED, ORPHANED, TOMBSTONED} defined once in spectral.core.retention.states. Context-agnostic. Consumed by worlds, platform, and core retention logic.

• ACTIVE — default state on ingestion. Governed by baseline TTL per RetentionPolicy; None opts out.
• REFERENCED — cited by one or more live downstream artifacts (applied change sets, active rules, unexpired windows). Retained indefinitely while referenced. Pattern lineage: generational-GC weak hypothesis (Bacon/Cheng/Rajan; Oracle HotSpot) plus eDiscovery legal-hold preservation.
• ORPHANED — integrity anomaly: a row was REFERENCED but lost its upstream sponsor in a way schema discipline should have prevented. Detection runs as a reconciliation job; any non-zero result is an operator alert, never auto-cleanup.
• TOMBSTONED — soft-deleted via deleted_at, in the grace window pending hard-delete cascade. Cascades transitively at state-computation time.

D2 — State is derived, not stored

No retention_state column anywhere. Per-context SQL views compute state from the reference graph plus deleted_at at query time. Drift-free by construction. DDD-consistent: cross-aggregate state computed from identity, not stored.

D3 — deleted_at timestamptz NULL convention

Every domain-scoped table carries this column. Migration convention; post-alpha lint enforces. The one stored bit required by the state model.

D4 — RetentionPolicy shape ships now; values arrive with enforcement

Typed pydantic model keyed by (entity_type, content_class). Shape lands in spectral.core.retention.policy; POLICY_REGISTRY lands empty. Entries land alongside the enforcement code that consumes them — matching the TA-review pattern of “contract artifacts now, values + mechanism later.”

D5 — Alpha default active_ttl_days = 365

Deliberate cost-control posture from day 1; expected to reduce per entity type as data-window research identifies shorter useful windows. tombstoned_grace_days = 30. disposal = HARD_DELETE. Ships as DEFAULT_POLICY. Reducible via per-entity POLICY_REGISTRY overrides.

D6 — REFERENCED ⇌ ACTIVE transition uses last_referenced_at + grace

When a row loses its last reference, ACTIVE TTL countdown restarts from the de-reference moment + grace, not from original creation. Prevents premature expiry of historically-referenced rows.

D7 — Three disposal postures

HARD_DELETE / STRIP_PAYLOAD (preserve FKs + metadata) / RETAIN_METADATA (audit stub). Ships as DisposalPosture enum; STRIP_PAYLOAD enforcement deferred to a named trigger.

D8 — TOMBSTONED cascades transitively at state-computation time

Ancestor tombstone implies descendant effective-tombstone in the view logic. Hard-delete cascade runs at grace expiry (operator-scripted at alpha; template documented).

D9 — Retention inheritance is within-context

OPERATIONS/worlds data must NOT persistently reference PLATFORM data — that is a data-segregation violation forbidden by ADR-032 D2 and by the architecture validator. Operations tooling queries platform data at read-time but does not create persistent cross-schema reference links. Customer-offboarding cascade therefore propagates through the platform-context reference graph only; OPERATIONS and worlds data are unaffected by design. This is much simpler than a “most-restrictive-class-wins” legal-hold cascade rule — the problem does not arise because the architecture forbids it.

D10 — Late-corruption floor = the managed-backup / PITR recovery window

Disaster recovery is Supabase-native managed backups + PITR (ADR-040 D1) — there is no self-run nightly pg_dump. The late-corruption floor is therefore the active backup retention window (daily snapshots pre-PITR; the PITR retention window once Pro is adopted), not a dump-lifecycle rule. The retention principle still binds: a domain-scoped entity’s active_ttl_days + tombstoned_grace_days must not fall below that recovery window, or hard-delete can outrun the ability to recover late-discovered corruption. If any PLATFORM TTL is tightened below it, either lengthen the window (PITR retention) or hold the TTL. Codified in docs/runbooks/disaster-recovery.md.

D11 — Forward triggers (named and measurable)

• core.llm_usage > 100 MB on disk → implement ACTIVE-expiry cron for PLATFORM-class rows
• Any platform-context table > 1 GB → implement ACTIVE-expiry plus consider declarative partitioning
• First design-partner contract signed → graduate the TOMBSTONED cascade script template to tested code
• First partner with Safeguards Rule flow-down → codify a 2-year PLATFORM hard cap in POLICY_REGISTRY
• First ORPHANED integrity-job run returning non-zero → P3 ticket; root-cause the schema/application violation
• First domain-scoped table landing a migration without deleted_at → post-alpha lint activation

D12 — User-reference columns carry uuid without core.users FK

Worlds- and platform-context tables that record an operator identity (created_by, retired_by, last_modified_by, operator_id, user_id) carry uuid not null columns without a foreign key to core.users(id). App-layer JWT verification is the source of truth for operator identity at write time; the database-level FK would block right-to-be-forgotten deletion of core.users rows downstream (with ON DELETE NO ACTION semantics) without protecting any load-bearing invariant.

Intra-context FKs (e.g., worlds.rules.revision_of → worlds.rules.id, worlds.rule_relationship.source_rule_id → worlds.rules(id, world_id)) are unaffected — the rule applies only to user-reference columns where right-to-be-forgotten is the governing semantic.

The architecture validator does not flag cross-schema FKs in migrations (cross-context coupling rules govern code-layer access via apps/api / DI, not declarative constraints). Per ADR-098 D1, worlds.world_models carries no platform tenancy columns and the domain↔world link is a soft uuid (platform.domains.world_id, no FK) — so there is no worlds.world_models → platform cross-schema FK to retain. User-reference columns follow the same no-cross-context-FK convention (above).

Alternatives considered

Full retention table with hard TTL numbers per entity × class (the pre-reframe Option A). Rejected. Would delete REFERENCED rows by age, breaking audit + regression + signal replay. Wrong by construction for an eval platform.

Defer TA-4 entirely (pure adversarial position). Rejected. SPEC-289 had already partially deferred, but ORPHANED-as-error-case, TOMBSTONED cascade direction, TA-2 reconciliation floor, and the RetentionState vocabulary are genuine decisions that land here. Pure defer loses them.

Stored retention_state column updated by triggers. Rejected per D2. Drift risk, DDD-inconsistent, and requires trigger coordination across contexts that violates the architecture validator.

Event-driven REFERENCED propagation. Rejected at alpha. Events are a cache, not truth; a nightly reconciliation job is needed anyway; the read-path cost of view-based derivation is tractable at alpha scale.

Five-state model with PENDING. Rejected after the founder reframe. Leaked context-specific concerns (scan cadence) into a vocabulary shared across contexts. Baseline ACTIVE TTL handles pre-publication state without a dedicated state.

active_ttl_days = None alpha default (indefinite). Rejected per founder direction. Near-term data-centric business needs cost control shipped, not deferred. 365 days chosen as the concrete ship value.

Inter-context legal-hold cascade (most-restrictive-class-wins across schemas). Mooted by D9. The architecture forbids the references between contexts that would make it necessary.

Datadog-style “retention filters” terminology. Considered and rejected. Descriptive but less precise than state vocabulary; does not capture ORPHANED or TOMBSTONED.

Consequences

• Zero additional alpha runtime cost. Vocabulary-only land; no new services, no active enforcement.
• spectral.core.retention is the contract surface across contexts. Any change to the enum values ripples to every per-context view that computes state — contract test pins the strings.
• deleted_at timestamptz NULL convention applies prospectively. No migration required for existing tables (none yet have domain-scope without deleted_at); enforcement lands as a post-alpha lint when the first domain-scoped table ships without it.
• Per-context view templates land per migration. Each context’s first domain-scoped migration ships a v_<entity>_retention_state view computing the four-state vocabulary.
• Customer offboarding at alpha is scripted operator work, not automated. Cascade template documented; graduates to tested code on first partner contract.
• ORPHANED integrity-job lands disabled. Schedule activates when the first domain-scoped tables ship in any context. Non-zero returns are always P3 tickets; never auto-reconciled.
• The previously-deferred retention concern is addressed by this ADR. The four-state retention vocabulary and per-context view templates land here.
• The late-corruption floor (D10) binds retention to the DR recovery window. DR is Supabase-native managed backups + PITR (ADR-040); a PLATFORM TTL must not drop below the active backup / PITR retention window, or hard-delete outruns recovery.
• No FKs introduced between contexts. Reference checks across schemas use EXISTS in single-DB views (allowed by ADR-032) rather than enforced constraints.
• POLICY_REGISTRY is empty at alpha. All (entity, class) pairs resolve to DEFAULT_POLICY via RetentionPolicy.resolve() fallback. Entries land alongside enforcement.

References

• ADR-065 — spectral.core admission discipline (the new retention surface ships under core)
• ADR-031 — single-library structure
• ADR-032 — schema topology; FKs between contexts forbidden
• ADR-036 — ContentClass taxonomy that RetentionPolicy keys on
• ADR-040 — late-corruption floor reconciliation (D10)
• ADR-043 — TA-14 retention integration
• ADR-058 — TA-12 persistent-tier RetentionPolicy registration
• ADR-058 — universal agent-memory lifecycle + audit-posture (transient-tier scope inheritance)
• TA-4 disposition — SPEC-307 comment 43f73c9f
• TA-4 verification — SPEC-307 comment d252e19d
• src/spectral/core/retention/ (commit 1504f0c) — landed contract surface
• Codex system-design/foundations/data-retention.mdx — close-pass new page