Manage data retention policies for database tables. Use when adding a new table to automated pruning, modifying retention periods, or understanding which tables are excluded.
Manage data retention policies for database tables. Use when adding a new table to automated pruning, modifying retention periods, or understanding which tables are excluded.
argument-hint
<table-name>
Data Retention Policy Management
Manage the automated data retention system that prunes expired/terminal rows from database tables on a Temporal schedule.
Architecture
The retention system has four layers:
Contract registry -- packages/contracts/src/literals.ts exports retentionTableNames, the single source of truth for which tables participate in retention.
Migration seed -- packages/infra/db/drizzle/migrations/0027_system_settings.sql seeds the system_settings table with a retention_settings JSON key containing per-table { enabled, retention_days } config.
Repository prune methods -- Each participating table's repository exposes a prune* method (e.g. pruneExpired, pruneProcessed, pruneTerminal, prunePublished) that deletes rows older than a given date.
Temporal schedule + workflow -- The worker registers a dataRetentionWorkflow on a 24-hour Temporal schedule (ensureDataRetentionSchedule in apps/worker/src/schedules.ts). The workflow reads retention_settings from system_settings at runtime and calls each table's prune activity.
Runtime config
The system_settings table stores retention config as JSONB under the key retention_settings:
Operators can change enabled or retention_days at runtime via a direct UPDATE to system_settings without redeployment.
Enforcement
Rule 11 in scripts/lint/enforce-domain-event-contracts.mjs verifies every table listed in retentionTableNames has a matching "<table_name>" entry in the migration seed. Running pnpm lint will catch mismatches.
Steps -- Adding a New Table to Retention
1. Register the table in contracts
File:packages/contracts/src/literals.ts
Add the table name to the retentionTableNames array:
Edit the JSON in packages/infra/db/drizzle/migrations/0027_system_settings.sql. The seed uses ON CONFLICT (key) DO NOTHING, so it only applies to fresh databases.
The following tables are never pruned and must not be added to retentionTableNames:
Table
Reason
usage_records
Financial audit trail -- required for billing reconciliation and dispute resolution
credit_ledger
Financial audit trail -- immutable ledger of credit transactions
The migration seed description explicitly states: "Tables not listed (usage_records, credit_ledger) are financial audit trails and must never be pruned."
If you need to archive old financial data, implement a separate archive-to-cold-storage strategy rather than deletion.
