Retention and Legal Hold for Location Audit Data

← Back to Audit Logging for Location Data Access

This page covers the two halves of an audit-retention policy that has to survive scrutiny: expiring records on schedule, and reliably not expiring the ones an open investigation depends on.

# Context & When to Use

An audit trail of location access is personal data about the people whose locations were read and about the staff who read them. Keeping it forever is a liability; deleting it too early destroys the ability to investigate. So the policy has two commitments — “we keep it for 24 months” and “we delete it after 24 months” — and both need to be demonstrably true.

The complication is holds. When an incident is under investigation, or a regulator has asked a question, the records relevant to it must survive past their expiry date. A retention job that does not know about holds will cheerfully destroy evidence on schedule, and the deletion will look deliberate afterwards no matter how automatic it was.

The design below keeps the policy as data, keeps holds in a registry the job consults, and records the fact of each expiry so the policy’s execution is provable after the underlying rows are gone. It builds on the partitioned audit table from Audit Logging for Location Data Access and the rollover mechanics in Automating Partition Rollover and Retention.

# Runnable Implementation

-- The policy, as data the job reads rather than a constant in a script
CREATE TABLE retention_policy (
    dataset        text PRIMARY KEY,
    retain_months  int  NOT NULL CHECK (retain_months BETWEEN 1 AND 120),
    approved_by    text NOT NULL,
    approved_at    timestamptz NOT NULL DEFAULT now()
);
INSERT INTO retention_policy VALUES ('access_audit', 24, 'dpo@example.com');

-- Active holds. A hold is a time range plus, optionally, a subject.
CREATE TABLE legal_hold (
    id            bigserial PRIMARY KEY,
    reason        text        NOT NULL,
    subject_id    text,                       -- NULL = every subject
    covers        tstzrange   NOT NULL,
    placed_by     text        NOT NULL,
    placed_at     timestamptz NOT NULL DEFAULT now(),
    expected_release date,
    released_at   timestamptz
);
CREATE INDEX legal_hold_active ON legal_hold USING GIST (covers)
    WHERE released_at IS NULL;

-- Tombstones: what was expired, proving the policy ran
CREATE TABLE retention_log (
    id             bigserial PRIMARY KEY,
    dataset        text        NOT NULL,
    partition_name text        NOT NULL,
    covers         tstzrange   NOT NULL,
    rows_removed   bigint      NOT NULL,
    archived_to    text,
    executed_at    timestamptz NOT NULL DEFAULT now()
);

The retention job then becomes a straightforward loop with one extra condition — does any unreleased hold overlap this partition’s range?

CREATE OR REPLACE FUNCTION expire_audit_partitions()
RETURNS TABLE (partition_name text, outcome text)
LANGUAGE plpgsql AS $$
DECLARE
    keep_months int;
    horizon     timestamptz;
    part        record;
    n           bigint;
BEGIN
    SELECT retain_months INTO STRICT keep_months
    FROM   retention_policy WHERE dataset = 'access_audit';
    horizon := date_trunc('month', now()) - (keep_months || ' month')::interval;

    FOR part IN
        SELECT c.relname,
               tstzrange((regexp_replace(c.relname, '^access_audit_', '') || '_01')::date,
                         ((regexp_replace(c.relname, '^access_audit_', '') || '_01')::date
                          + interval '1 month')) AS covers
        FROM   pg_class c
        JOIN   pg_inherits i ON i.inhrelid = c.oid
        WHERE  i.inhparent = 'access_audit'::regclass
          AND  c.relname ~ '^access_audit_\d{4}_\d{2}$'
    LOOP
        CONTINUE WHEN upper(part.covers) > horizon;

        -- The one extra condition that makes this safe
        IF EXISTS (SELECT 1 FROM legal_hold h
                   WHERE h.released_at IS NULL AND h.covers && part.covers) THEN
            partition_name := part.relname; outcome := 'held';
            RETURN NEXT; CONTINUE;
        END IF;

        EXECUTE format('SELECT count(*) FROM %I', part.relname) INTO n;
        EXECUTE format('ALTER TABLE access_audit DETACH PARTITION %I CONCURRENTLY',
                       part.relname);
        INSERT INTO retention_log (dataset, partition_name, covers, rows_removed,
                                   archived_to)
        VALUES ('access_audit', part.relname, part.covers, n,
                's3://audit-archive/' || part.relname || '.dump');
        EXECUTE format('DROP TABLE %I', part.relname);

        partition_name := part.relname; outcome := 'expired';
        RETURN NEXT;
    END LOOP;
END $$;

# Key Parameters & Options

# Holds that never get released

The predictable failure of a hold registry is not that holds are missed — it is that they are never lifted. A hold placed during an incident outlives the incident, nobody remembers it exists, and two years later the “24-month” trail quietly contains five years of data.

# Gotchas & Failure Modes

• A hold placed after the partition was dropped. Retention runs on a schedule; an investigation opened on Monday cannot protect data expired on Sunday. Keep the archive copy long enough to restore from, and record where it went in the tombstone.
• STRICT on the policy lookup. If the policy row is missing, SELECT … INTO STRICT raises rather than defaulting to zero months. That failure mode — job errors loudly — is much better than the alternative, which deletes everything.
• Holds without a subject filter treated as narrow. A hold with subject_id IS NULL covers every subject in the range. Make the registry display that explicitly, or someone will place a broad hold thinking it was narrow.
• Deleting rows instead of partitions when held data is mixed in. Deleting the unheld rows from a held partition breaks the append-only guarantee and destroys the tamper-evidence argument. Skip the whole partition instead.
• The tombstone table growing unbounded. It is small — one row per partition — but it must never be expired itself, or the proof of deletion disappears with the data.
• Archive not verified. Confirm the dump is readable before dropping, the same discipline as in Automating Partition Rollover and Retention.

# Proving the policy ran

The awkward question in any review is not “what is your retention period” — it is “show me that it happened”. Once the rows are gone, the only evidence is what you wrote down at the time, which is why the tombstone table is part of the design rather than an afterthought.

Three artefacts together make the case: the policy row with its approver and date, one tombstone per expired partition, and the hold registry showing why any apparent exception exists. A reviewer can reconcile them without access to a single audit record, and therefore without any additional exposure of the personal data the review is about.

# Verification Snippet

-- Does the trail match the stated policy right now?
SELECT (SELECT retain_months FROM retention_policy WHERE dataset = 'access_audit')
         AS policy_months,
       round(extract(epoch FROM now() - min(occurred_at)) / 2629746)::int
         AS actual_months_held,
       (SELECT count(*) FROM legal_hold WHERE released_at IS NULL) AS active_holds
FROM   access_audit;
--  policy_months | actual_months_held | active_holds
-- ---------------+--------------------+--------------
--             24 |                 26 |            1     ← explained by the hold

-- Holds past their expected release
SELECT id, reason, placed_by, expected_release,
       (current_date - expected_release) AS days_overdue
FROM   legal_hold
WHERE  released_at IS NULL AND expected_release < current_date
ORDER  BY days_overdue DESC;

# Related

• Audit Logging for Location Data Access — the trail this policy governs
• Automating Partition Rollover and Retention — the detach-and-drop mechanics
• Row-Level Security for Multi-Tenant PostGIS — restricting who can read the trail at all

← Back to Audit Logging for Location Data Access