Querying immutable drift history for auditors

Q: How does the query handle a grant that is still active?

The lead lookahead finds no remediated event, so the interval's upper bound is null and the range type treats it as unbounded above. Range overlap against any date range still works, and upper_inf on the live window flags the grant as open in the results.

Q: Is this efficient on a very large history table?

Yes, when the query carries a recorded_at predicate so partition pruning and the BRIN index limit the scan to the relevant months. The window function then runs only over the surviving partitions, keeping even multi-year questions fast.

Q: How do I answer the same question on MySQL?

Use LEAD to build granted_at and remediated_at columns, then replace the range-overlap operator with an explicit predicate: the grant was live during the window if it started before the window ended and was not remediated before the window began.

This guide gives the SQL and Python query patterns that turn an append-only drift-history table into answers auditors accept: who held a given privilege on an object between two dates, and when a specific excess grant was remediated — reconstructed from immutable events, never from mutable current state.

An auditor’s questions are almost always temporal and object-scoped, but an append-only table stores discrete events, not standing intervals. The skill is reconstructing the interval a grant was live from its observed and remediated events with a window function, then filtering by range overlap. These queries read the store defined in Audit Trail Retention and Query.

When to use this — and when not to

Reach for these patterns when:

An auditor asks a point-in-time or interval question — “who could read cust.cards in Q1” — and you must answer from the immutable trail, not the live catalog.
Your history is genuinely append-only, so a grant’s life is a sequence of events you reconstruct rather than a row you read.
You need the answer to be reproducible: the same query over the same rows must return the same evidence months later.

Do not use them when:

You only need the current privilege set. Query the live catalog directly — reconstructing intervals to answer “who has access now” is wasted work.
Your table is mutated in place. If rows are updated on remediation, interval reconstruction is unnecessary and the trail is not audit-grade; fix the storage model first.
The question is “should this access exist,” not “did it exist.” That is a scoring and mapping question — see Rule-Based Drift Scoring.

Figure 1 — A grant's live window is reconstructed from its observed and remediated events, then overlapped with the auditor's date range; the dashed region is what the query returns.

Step-by-step implementation

Step 1 — Reconstruct live intervals from paired events

The core move: within each (principal, object_id, privilege) group, lead() looks ahead to the next event. When that next event is a remediated, it closes the interval; when there is none (or it is a re-observed), the upper bound is NULL, which a tstzrange treats as unbounded — the grant is still live.

WITH grant_intervals AS (
    SELECT
        principal, object_id, privilege, control_id, event_type,
        recorded_at AS granted_at,
        tstzrange(
            recorded_at,
            CASE WHEN lead(event_type) OVER w = 'remediated'
                 THEN lead(recorded_at) OVER w END,   -- NULL upper => still live
            '[)'
        ) AS live_window
    FROM drift_history
    WHERE event_type IN ('observed', 'remediated')
    WINDOW w AS (
        PARTITION BY principal, object_id, privilege
        ORDER BY recorded_at
    )
)
SELECT principal, object_id, privilege, granted_at, live_window
FROM grant_intervals
WHERE event_type = 'observed'
ORDER BY object_id, principal, granted_at;

Verify that an open grant yields an unbounded upper bound:

-- upper_inf() is true when the grant has no remediation event yet.
SELECT principal, upper_inf(live_window) AS still_live
FROM grant_intervals
WHERE event_type = 'observed' AND object_id = 'prod.ledger';

Step 2 — Answer “who had privilege X on object Y between two dates”

With intervals reconstructed, the interval question is one range-overlap predicate. && returns true whenever the grant’s live window and the auditor’s date range share any instant, and it works correctly against the unbounded upper bound of a still-live grant:

WITH grant_intervals AS ( /* the CTE from Step 1 */ )
SELECT principal, granted_at, live_window
FROM grant_intervals
WHERE event_type = 'observed'
  AND object_id = 'sales.orders'
  AND privilege = 'SELECT'
  AND live_window && tstzrange('2026-01-01', '2026-04-01', '[)')
ORDER BY granted_at;

Keep an explicit recorded_at bound in the outer query when the range is narrow, so partition pruning limits the scan to the months in scope.

Step 3 — Answer “when was this excess grant remediated”

Given the content hash of a specific observed event, find the first matching remediated event for the same principal, object, and privilege that occurred after it:

SELECT
    o.principal, o.object_id, o.privilege,
    o.recorded_at AS observed_at,
    min(r.recorded_at) FILTER (WHERE r.recorded_at > o.recorded_at) AS remediated_at
FROM drift_history AS o
LEFT JOIN drift_history AS r
       ON  r.principal  = o.principal
       AND r.object_id  = o.object_id
       AND r.privilege  = o.privilege
       AND r.event_type = 'remediated'
WHERE o.event_type = 'observed'
  AND o.content_hash = decode($1, 'hex')
GROUP BY o.principal, o.object_id, o.privilege, o.recorded_at;

A NULL remediated_at is itself evidence: the excess grant was never cleared as of the query time.

Step 4 — Wrap the query in parameterized Python

Auditor tooling should never interpolate identifiers or dates into SQL. psycopg v3 binds every value as a parameter and returns typed rows:

import psycopg
from psycopg.rows import dict_row

WHO_HAD = """
WITH grant_intervals AS (
    SELECT principal, object_id, privilege, event_type,
           recorded_at AS granted_at,
           tstzrange(recorded_at,
                     CASE WHEN lead(event_type) OVER w = 'remediated'
                          THEN lead(recorded_at) OVER w END, '[)') AS live_window
    FROM drift_history
    WHERE event_type IN ('observed', 'remediated')
    WINDOW w AS (PARTITION BY principal, object_id, privilege ORDER BY recorded_at)
)
SELECT principal, granted_at, live_window
FROM grant_intervals
WHERE event_type = 'observed'
  AND object_id = %(obj)s AND privilege = %(priv)s
  AND live_window && tstzrange(%(start)s, %(end)s, '[)')
ORDER BY granted_at;
"""

def who_had(conn: psycopg.Connection, obj: str, priv: str, start: str, end: str):
    with conn.cursor(row_factory=dict_row) as cur:
        cur.execute(WHO_HAD, {"obj": obj, "priv": priv, "start": start, "end": end})
        return cur.fetchall()

Worked example: PostgreSQL 15, one contested object

Scenario: PostgreSQL 15. An auditor asks who could SELECT from sales.orders in the first quarter of 2026. The table holds three events for the role analytics_ro on that object:

event_type   principal      privilege   recorded_at
observed     analytics_ro   SELECT      2026-01-05 09:14:00+00
remediated   analytics_ro   SELECT      2026-04-02 16:40:00+00
observed     analytics_ro   SELECT      2026-05-10 11:00:00+00

Running who_had(conn, "sales.orders", "SELECT", "2026-01-01", "2026-04-01") reconstructs two intervals — a closed one from January to April and an open one from May — and overlaps them with the Q1 range:

 principal    | granted_at             | live_window
--------------+------------------------+-----------------------------------------------
 analytics_ro | 2026-01-05 09:14:00+00 | ["2026-01-05 09:14:00+00","2026-04-02 16:40:00+00")

Only the first interval overlaps Q1, so exactly one evidence row returns; the May grant is correctly excluded because it began after the range closed. The auditor gets the precise window analytics_ro held the privilege, sourced entirely from immutable events.

Gotchas and engine-specific notes

A NULL upper bound is “still live,” not “empty.” tstzrange(t, NULL, '[)') is unbounded above and overlaps any later range, which is the behaviour you want for an open grant. Do not coerce the NULL to now() — that silently closes an interval that is still open and understates exposure.

Order ties need a tiebreaker. clock_timestamp() is granular, but bulk backfills can land two events in the same microsecond. Add event_id as a secondary key in the WINDOW clause (ORDER BY recorded_at, event_id) so lead() pairs events deterministically.

Partition pruning needs a key predicate. The range-overlap on live_window does not prune partitions by itself. For narrow date questions, add AND recorded_at >= %(start)s::timestamptz - interval '1 year' so the planner skips partitions that cannot contain a relevant observed event, per the PostgreSQL partition pruning rules.

MySQL 8 has no range type. There is no tstzrange or &&. Reconstruct intervals with LEAD(recorded_at) OVER (...) into two columns and express overlap explicitly: granted_at < :end AND (remediated_at IS NULL OR remediated_at > :start). The window function is identical; only the overlap test changes.

Compliance note

These queries produce the operating-effectiveness evidence an examiner samples: for any object and period, an exact list of who held which privilege and for how long, plus the remediation timestamp that closed each finding. Because every answer is derived from an append-only trail by a deterministic, parameterized query, the examiner can re-run it and obtain byte-identical results — satisfying SOC 2 CC6.1 access-restriction evidence and PCI DSS Requirement 10’s demand that access history be reviewable. The reconstructed live_window and remediated_at are the artifact: a defensible, replayable statement of historical access that no in-place-mutated table could produce.

Frequently asked questions

Why reconstruct intervals instead of storing a start and end column? Because the trail is append-only — remediation is a new event, not an update to an existing row. Storing an end column would require mutating history, which destroys the tamper-evidence the audit trail exists to provide. Reconstruction keeps every row write-once.

How does the query handle a grant that is still active? The lead() lookahead finds no remediated event, so the interval’s upper bound is NULL and tstzrange treats it as unbounded above. Range overlap against any date range still works, and upper_inf(live_window) flags the grant as open in the results.

Is this efficient on a very large history table? Yes, when the query carries a recorded_at predicate so partition pruning and the BRIN index limit the scan to the relevant months. The window function then runs only over the surviving partitions, keeping even multi-year questions fast.

How do I answer the same question on MySQL? Use LEAD() to build granted_at and remediated_at columns, then replace the range-overlap operator with an explicit predicate: the grant was live during the window if it started before the window ended and was not remediated before the window began.

Audit Trail Retention and Query — the append-only, partitioned store these queries read.
Continuous Control Monitoring — the control verdicts these historical answers substantiate.
Evidence Integrity and Signing — why a row returned today matches the event first recorded.

Up: Audit Trail Retention and Query