Audit trail retention and query

An audit trail is only evidence if it is both tamper-proof and reachable years after the fact. Retention and query is the discipline of storing the immutable drift and evidence record for the full window a framework demands — often far longer than operational data lives — while keeping it fast to answer an auditor’s questions. That means an append-only storage model where rows are written once and never mutated, a retention policy that survives partition churn and legal holds, and indexes shaped for the queries auditors actually run, not for the writes that fill the table.

The failure scenario is discovering, mid-audit, that the trail cannot answer the question asked. Either the rows for the period in scope were rotated out by an operational retention job that never knew about the six-year HIPAA window, or they exist but a full scan of a billion-row history times out before it can prove who held DELETE on a schema last spring. Both are avoidable with a storage model designed around retention and query from the first row. This guide sits within Compliance Evidence & Audit Reporting and stores the output of Continuous Control Monitoring and Rule-Based Drift Scoring.

Prerequisites and scope

Retention and query is an infrastructure concern layered under the evidence producers. It assumes the records arriving are already correct and tamper-evident; its job is to keep them, unaltered, for as long as the framework requires and to serve them quickly. Before building it, confirm:

• PostgreSQL 14+ for declarative range partitioning with reliable partition pruning and ALTER TABLE ... DETACH PARTITION CONCURRENTLY. The examples use BRIN indexes, tstzrange, and identity columns; MySQL 8 differences are called out in the child guide.
• A content hash per record. Each drift or evidence event must arrive with a stable content hash so ingestion can deduplicate idempotently. That hash is produced upstream by Evidence Integrity and Signing, which also chains the records so the trail is tamper-evident, not merely append-only.
• A separate, least-privilege writer role. The ingesting role holds INSERT and SELECT on the history table and nothing else — no UPDATE, no DELETE, no TRUNCATE. Append-only is enforced by the grant model first and a trigger second.
• Documented retention windows per framework. SOC 2 examinations cover the audit period plus history; PCI DSS Requirement 10 mandates at least one year with three months immediately available; HIPAA requires six years for its documentation. Retention is set to the longest obligation that touches the data.

The scope stops at storage and retrieval. It does not decide what a record means — that is Continuous Control Monitoring — and the specific auditor query recipes live in its child guide, querying immutable drift history for auditors.

Core implementation walkthrough

Three steps establish the store: an append-only schema partitioned by time, indexes shaped for auditor reads, and an enforced retention policy.

Step 1 — Define the append-only, time-partitioned schema

Every state change is a new row. A grant observed and later remediated is two events, not an updated one, so the table never needs UPDATE. Partitioning by recorded_at keeps each partition small enough to index and detach independently.

CREATE TABLE drift_history (
    event_id     bigint GENERATED ALWAYS AS IDENTITY,
    event_type   text NOT NULL
                 CHECK (event_type IN ('observed', 'remediated', 'whitelisted')),
    principal    text NOT NULL,
    object_id    text NOT NULL,
    privilege    text NOT NULL,
    control_id   text NOT NULL,
    content_hash bytea NOT NULL,
    recorded_at  timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (event_id, recorded_at)
) PARTITION BY RANGE (recorded_at);

-- One partition per month; create the next month ahead of time in a scheduled job.
CREATE TABLE drift_history_2026_07 PARTITION OF drift_history
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE drift_history_2026_08 PARTITION OF drift_history
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Ingestion is deduplicated by content hash, so replaying the same drift batch is a no-op rather than a source of duplicate evidence:

CREATE UNIQUE INDEX drift_history_dedup
    ON drift_history (content_hash, recorded_at);

INSERT INTO drift_history (event_type, principal, object_id, privilege, control_id, content_hash)
VALUES ('observed', 'analytics_ro', 'prod.claims', 'UPDATE', 'CC6.1', decode($1, 'hex'))
ON CONFLICT DO NOTHING;

Step 2 — Index for the queries auditors run

Auditors read by time range and by principal or object, never by the identity column. A BRIN index on recorded_at is tiny and ideal for the append-ordered timestamp, while a btree on the lookup keys serves point questions:

-- BRIN: a few kilobytes even at a billion rows, perfect for range scans on an
-- append-ordered column.
CREATE INDEX drift_history_recorded_brin
    ON drift_history USING brin (recorded_at) WITH (pages_per_range = 32);

-- btree: "everything principal X touched on object Y".
CREATE INDEX drift_history_principal_object
    ON drift_history (principal, object_id, privilege, recorded_at);

Partition pruning does the coarse filtering: a query bounded by recorded_at only touches the partitions overlapping the range, and the BRIN index skips the blocks inside them that fall outside it. That combination is what keeps an auditor’s date-bounded question fast against a multi-year table.

Step 3 — Enforce append-only and drive retention by partition

Grants are the primary defence; a trigger is the backstop that makes tampering fail loudly even if a role is misconfigured:

REVOKE UPDATE, DELETE, TRUNCATE ON drift_history FROM evidence_writer;
GRANT INSERT, SELECT ON drift_history TO evidence_writer;

CREATE FUNCTION deny_mutation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION 'drift_history is append-only: % rejected', TG_OP;
END;
$$;

CREATE TRIGGER drift_history_immutable
    BEFORE UPDATE OR DELETE ON drift_history
    FOR EACH ROW EXECUTE FUNCTION deny_mutation();

Retention then works at the partition level. Once a month passes the retention window and carries no legal hold, its partition is detached and moved to archive storage — never deleted row by row:

-- Retire a partition past the retention window; archive, don't drop, while any
-- framework window or legal hold still covers it.
ALTER TABLE drift_history DETACH PARTITION drift_history_2025_01 CONCURRENTLY;

Idempotency and safety contract

The store is safe to write to continuously and impossible to corrupt through ordinary operation:

• Write-once rows. No code path issues UPDATE or DELETE. State changes append new events, so the history of any grant is the ordered list of its rows — nothing is ever overwritten.
• Idempotent ingestion. The unique index on content_hash plus ON CONFLICT DO NOTHING means re-running a drift batch inserts each event at most once. A crashed-and-retried pipeline converges to the same table.
• Retention is detach, not delete. Aging a partition out of the live table detaches and archives it; the rows survive in cold storage for the full obligation. Deletion happens only when the longest applicable window has fully elapsed and no hold applies.
• Holds fail closed. A legal hold is checked before any detach. If the hold registry is unreachable, detachment is skipped, not forced — the trail errs toward keeping evidence, never toward losing it.

Compliance alignment

Retention windows are a direct control requirement, and the partition model produces the evidence that the requirement is met.

The append-only property is what makes the retained data admissible as evidence: an auditor can trust that a record present today is byte-for-byte the record written when the event occurred. Chaining and signing that guarantee is the job of Evidence Integrity and Signing; the retained trail then feeds the packaged deliverables in Audit Report Artifact Formats.

Troubleshooting matrix

Retention and query problems tend to surface at the worst moment — during an audit. Each row pairs the symptom with its cause and fix.

Correct storage is only half the job; turning the retained history into answers an auditor accepts is covered next.

Related

• Querying immutable drift history for auditors — the SQL and Python recipes that answer common auditor questions over this table.
• Continuous Control Monitoring — the control-effectiveness series this store retains.
• Evidence Integrity and Signing — hash-chaining and signing that make the append-only rows tamper-evident.
• Audit Report Artifact Formats — packaging retained evidence into auditor deliverables.
• Rule-Based Drift Scoring — the scored records that become history rows.

Up: Compliance Evidence & Audit Reporting