Most firms building a screening agent get the logging right and the calendar wrong. Every score, every prompt, every human override goes into a table, and then nothing ever leaves it. Four years later the firm is holding demographic self-ID rows from candidates who applied once in 2026, and a request to delete them turns into a week of manual SQL.
Retention is the unglamorous half of an audit record. This walkthrough builds it: one rules table, partitioned logs, a hold mechanism, and a purge job that writes down what it did.
A note before the code. Retention periods are a legal question, not an engineering one. New York City's Local Law 144 sets its own recordkeeping expectations for automated employment decision tools; Illinois HB 3773 took effect at the start of 2026; California's civil rights regulations on automated decision systems push employment records, including the data an automated system used, into a multi-year retention window. The numbers in this post are placeholders. Get the actual periods from your employment counsel, then encode them once, here, instead of scattering them across six cron jobs.
Step one: classify the records, not the tables
A screening agent produces at least four kinds of record, and they do not share a clock:
| Class | Example rows | Why the clock differs |
|---|---|---|
| Decision record | Score per criterion, rationale text, rubric version, human actor and outcome | This is the audit artifact. Longest retention. |
| Model input | The resume text and structured fields fed to the scorer | Sensitive, larger, and reproducible from the ATS. Often shorter. |
| Self-ID data | Voluntary race and sex responses used for selection-rate maths | Kept apart from the scorer, retained for audit maths, deleted on its own schedule. |
| Operational telemetry | Latency, token counts, retries, error payloads | Days or weeks. Nobody audits a timeout. |
Write the classes down before writing DDL. Half the retention arguments in a scoping meeting are really two people using the word "logs" for different things.
Step two: one rules table, read by every job
Put the periods in data, not in code:
create table retention_rules (
record_class text primary key,
retain_months int not null,
clock_start text not null, -- 'decided_at' | 'application_closed_at'
purge_mode text not null, -- 'delete' | 'redact'
authority text not null, -- free text: who set this and when
updated_at timestamptz not null default now()
);
insert into retention_rules values
('decision_record', 48, 'decided_at', 'delete',
'counsel memo 2026-02-11'),
('model_input', 18, 'decided_at', 'redact',
'counsel memo 2026-02-11'),
('self_id', 48, 'application_closed_at', 'delete',
'counsel memo 2026-02-11'),
('telemetry', 1, 'decided_at', 'delete',
'engineering default');
The authority column earns its keep the first time someone asks why model inputs go at eighteen months. redact versus delete matters too: a decision record whose resume text has been nulled out still supports a selection-rate query, which a deleted row does not.
Step three: partition the decision log by month
Deleting millions of rows one predicate at a time is slow and leaves bloat. Partitioning turns a purge into a drop table. PostgreSQL's declarative partitioning is documented in the official manual.
create table decision_records (
decision_id bigserial,
candidate_id bigint not null,
job_id bigint,
rubric_version text not null,
criterion text not null,
score numeric not null,
rationale text not null,
human_actor text,
human_outcome text,
decided_at timestamptz not null,
primary key (decision_id, decided_at)
) partition by range (decided_at);
create table decision_records_2026_09 partition of decision_records
for values from ('2026-09-01') to ('2026-10-01');
Create next month's partition on a schedule, a month ahead. An agent that cannot write its decision because the partition is missing will either crash or, worse, score without logging.
Step four: holds beat the clock
The purge job must lose to an open charge, a client audit or litigation. Model that explicitly rather than by disabling cron and hoping someone remembers:
create table retention_holds (
hold_id bigserial primary key,
scope_type text not null, -- 'candidate' | 'job' | 'all'
scope_id bigint,
reason text not null,
opened_by text not null,
opened_at timestamptz not null default now(),
released_at timestamptz
);
And a predicate the purge consults every run:
create or replace function is_held(p_candidate_id bigint, p_job_id bigint)
returns boolean language sql stable as $$
select exists (
select 1 from retention_holds
where released_at is null
and (scope_type = 'all'
or (scope_type = 'candidate' and scope_id = p_candidate_id)
or (scope_type = 'job' and scope_id = p_job_id))
);
$$;
One rule, no exceptions: a hold is opened by a named person with a reason, and only a named person releases it. If a hold is open on scope all, the purge does nothing and says so.
Step five: purge, and log the purge
A deletion you cannot evidence is indistinguishable from a bug. Every purge run writes a row before it writes anything else:
create table purge_runs (
run_id bigserial primary key,
record_class text not null,
cutoff date not null,
rows_affected bigint,
mode text not null,
started_at timestamptz not null default now(),
finished_at timestamptz,
operator text not null -- service account name
);
The redaction pass for model inputs, cutoff read from the rules table, holds respected:
with rule as (
select retain_months from retention_rules
where record_class = 'model_input'
)
update model_inputs mi
set payload = null,
redacted_at = now()
from rule
where mi.redacted_at is null
and mi.decided_at < current_date
- make_interval(months => rule.retain_months)
and not is_held(mi.candidate_id, mi.job_id);
For the partitioned decision log, prefer dropping whole partitions once every row inside is past its period and nothing in it is held. Check first:
select count(*) as held_rows
from decision_records_2022_08 d
where is_held(d.candidate_id, d.job_id);
Zero, and the partition is older than the retained window? drop table decision_records_2022_08; in the same transaction that stamps purge_runs.finished_at. Non-zero, and you fall back to row-level deletes for the unheld rows and leave the partition in place.
Run the whole thing monthly, not nightly. A monthly cadence gives a human a chance to notice a wrong cutoff before four years of records go.
Step six: the dry run nobody skips twice
Ship the purge with a --dry-run flag that runs the same predicates and reports counts per class, and make the first three months of production runs dry only. Print it as a small table someone actually reads:
class cutoff eligible held would_delete
decision_record 2022-09-01 41,208 112 41,096
model_input 2025-03-01 158,940 340 158,600
self_id 2022-09-01 9,731 0 9,731
telemetry 2026-08-01 2,004,552 0 2,004,552
If held is zero for every class in every month, your hold mechanism is probably not wired up. That has been true more often than we would like.
What this gets you
An auditor or a client's counsel asks two questions: what did the system record about this candidate, and what happened to the records you no longer hold. With rules in a table, holds in a table and purge runs in a table, both answers are queries rather than an archaeology project. The retention schedule is a decision your counsel makes; keeping the schedule honest is the part we build.
This is the same records plumbing that sits under our Rubric Screener, and the LL144 selection-rate maths depends on it: you cannot compute a rate over rows you quietly deleted last spring. If you are wiring retention into a screening workflow on Loxo, Crelate, JobAdder, Vincere or Recruiterflow, contact us and name your ATS and your retention periods.