Designing a FERPA Audit-Log Schema in Postgres

FERPA obliges an institution to answer a specific question on demand: who accessed which student’s records, when, and for what purpose? Scattered application logs cannot answer it credibly, because they can be edited, rotated away, or simply never written. This guide designs the durable alternative — an append-only audit log in Postgres that records every access to protected data as an immutable, tamper-evident row keyed to a tokenized subject. It is the audit mechanism that the FERPA-Compliant PII Handling design calls for, and it records subjects using the same stu_… tokens produced by salted SHA-256 tokenization so the log never itself stores raw PII.

The task: a table that (1) captures who/what/when/purpose plus the tokenized subject and source job, (2) is append-only — no UPDATE or DELETE reaches it through the application role, (3) is tamper-evident via a hash chain, and (4) has a retention story that survives audits without unbounded growth.

FERPA append-only audit-log schema and write flow A principal issues a data-access request through a guarded access layer, which returns the record and simultaneously appends one audit row. The ferpa_audit_log table shows its columns; UPDATE and DELETE are revoked for the application role. Each row_hash chains to the previous row's hash to make tampering detectable, and rows are partitioned by month for retention. Principal actor + purpose access request Guarded access read + append audit row INSERT only ferpa_audit_log id bigserial PK occurred_at timestamptz actor_token text action text purpose text subject_token text † source_job text row_hash text prev_hash text † tokenized subject — no raw PII partition by month (retention) Append-only REVOKE UPDATE REVOKE DELETE trigger blocks mutation Hash chain row_hash = H(prev_hash + row fields) tamper-evident each row_hash links to the prior prev_hash — a break reveals tampering row n-1 row n row n+1 row n+2

Prerequisites

Confirm each before running the procedure — the script assumes all of them.

Step-by-step implementation

1. Model the row around who/what/when/purpose. The four FERPA questions become four required columns — actor_token, action, occurred_at, purpose — plus the subject_token whose record was touched and the source_job that did it. Everything an auditor needs is on one row; no join to a mutable table is required to interpret it.

2. Make the table append-only at the database, not just in code. Application-layer discipline is not enough; revoke UPDATE and DELETE from the application role and add a trigger that raises on any mutation attempt. Append-only must be a property Postgres enforces, so a compromised or careless app path cannot rewrite history.

3. Chain each row to the last for tamper-evidence. Compute row_hash = sha256(prev_hash || canonical_row_fields). Any later edit to a row changes its hash and breaks the chain at that point, so tampering is detectable by re-walking the chain — even by someone with direct database access who bypassed the trigger.

4. Partition by month for retention. Declarative range partitioning on occurred_at lets you drop an entire expired month with DROP TABLE (instant, no bloat) instead of a giant DELETE — and dropping a partition is the one deletion the retention policy legitimately permits.

5. Write through a single guarded helper. All audit writes go through one Python function that fetches the previous hash, computes the new one, and inserts — so the chain is always continuous and no code path invents its own row format.

The audit table DDL

sql
-- Parent: declarative range partitioning by month enables cheap retention drops.
CREATE TABLE ferpa_audit_log (
    id            bigserial,
    occurred_at   timestamptz  NOT NULL DEFAULT now(),
    actor_token   text         NOT NULL,   -- tokenized principal, never a raw name
    action        text         NOT NULL,   -- 'read' | 'export' | 'reverse_token' ...
    purpose       text         NOT NULL,   -- stated FERPA purpose for the access
    subject_token text         NOT NULL,   -- tokenized student, never raw PII
    source_job    text         NOT NULL,   -- job / request id for traceability
    row_hash      text         NOT NULL,   -- sha256(prev_hash || row fields)
    prev_hash     text         NOT NULL,   -- links to the previous row's row_hash
    PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE ferpa_audit_log_2026_07 PARTITION OF ferpa_audit_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- Block mutation of history at the database level.
CREATE OR REPLACE FUNCTION ferpa_audit_no_mutate() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'ferpa_audit_log is append-only: % denied', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_no_update BEFORE UPDATE OR DELETE ON ferpa_audit_log
    FOR EACH ROW EXECUTE FUNCTION ferpa_audit_no_mutate();

-- Least privilege: the app can only append.
REVOKE UPDATE, DELETE, TRUNCATE ON ferpa_audit_log FROM etl_app;
GRANT INSERT, SELECT ON ferpa_audit_log TO etl_app;

The immutable-write helper

python
import hashlib
import json
import os
from datetime import datetime, timezone

import psycopg

GENESIS = "0" * 64  # prev_hash for the very first row


def _row_hash(prev_hash: str, occurred_at: str, actor: str, action: str,
              purpose: str, subject: str, source_job: str) -> str:
    """Chain hash: any later edit to these fields breaks the chain."""
    payload = json.dumps(
        [prev_hash, occurred_at, actor, action, purpose, subject, source_job],
        separators=(",", ":"), ensure_ascii=False,
    )
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()


def write_audit(conn: psycopg.Connection, *, actor_token: str, action: str,
                purpose: str, subject_token: str, source_job: str) -> str:
    """Append one immutable, hash-chained audit row. Returns its row_hash."""
    occurred_at = datetime.now(timezone.utc).isoformat()
    with conn.cursor() as cur:
        cur.execute("SELECT row_hash FROM ferpa_audit_log "
                    "ORDER BY id DESC LIMIT 1")
        prev = cur.fetchone()
        prev_hash = prev[0] if prev else GENESIS
        rh = _row_hash(prev_hash, occurred_at, actor_token, action,
                       purpose, subject_token, source_job)
        cur.execute(
            "INSERT INTO ferpa_audit_log "
            "(occurred_at, actor_token, action, purpose, subject_token, "
            " source_job, row_hash, prev_hash) "
            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
            (occurred_at, actor_token, action, purpose, subject_token,
             source_job, rh, prev_hash),
        )
    conn.commit()
    return rh


def access_record(conn: psycopg.Connection, *, actor_token: str,
                  subject_token: str, purpose: str, source_job: str) -> dict:
    """Example: wrap a data access so every read leaves an audit trail."""
    record = {"subject_token": subject_token, "score": 87.5}   # placeholder read
    write_audit(conn, actor_token=actor_token, action="read", purpose=purpose,
                subject_token=subject_token, source_job=source_job)
    return record


def verify_chain(conn: psycopg.Connection) -> bool:
    """Re-walk the chain; a mismatch means a row was tampered with."""
    with conn.cursor() as cur:
        cur.execute("SELECT occurred_at, actor_token, action, purpose, "
                    "subject_token, source_job, row_hash, prev_hash "
                    "FROM ferpa_audit_log ORDER BY id ASC")
        prev_hash = GENESIS
        for oa, actor, action, purpose, subject, job, stored, phash in cur:
            if phash != prev_hash:
                return False
            expected = _row_hash(phash, oa.isoformat() if hasattr(oa, "isoformat")
                                 else str(oa), actor, action, purpose, subject, job)
            if expected != stored:
                return False
            prev_hash = stored
    return True


if __name__ == "__main__":
    with psycopg.connect(os.environ["AUDIT_DB_DSN"]) as conn:
        access_record(conn, actor_token="stu_v1_actor_9f3a",
                      subject_token="stu_v1_subj_4c1b",
                      purpose="nightly_grade_sync", source_job="job_2026_07_15_01")
        print("chain intact:", verify_chain(conn))

Verification and output validation

  • Append-only is enforced. UPDATE ferpa_audit_log SET purpose='x' WHERE id=1; must raise ferpa_audit_log is append-only: UPDATE denied. If it succeeds, the trigger or the grants are missing.
  • No raw PII in the log. Confirm actor_token and subject_token match the stu_… shape: SELECT count(*) FROM ferpa_audit_log WHERE subject_token !~ '^stu_'; must return 0.
  • Chain integrity. verify_chain(conn) returns True on an untouched log. Force a mismatch (temporarily, on a throwaway table) to confirm it returns False when a field is altered.
  • Every access wrote a row. After a batch of N accesses, SELECT count(*) FROM ferpa_audit_log increased by exactly N — no access bypassed the helper.
  • Retention drop is clean. DROP TABLE ferpa_audit_log_2026_07; removes an expired month instantly, and the parent still queries the remaining partitions without error.

Troubleshooting

  • UPDATE/DELETE unexpectedly succeeds. The trigger was created on the parent but a new partition was attached without it, or etl_app retained a grant. Recreate the trigger per partition (or use a parent-level trigger on PG 14+) and re-run the REVOKE.
  • verify_chain returns False on a clean log. The hash inputs differ between write and verify — usually a timestamp serialized differently (occurred_at stored as timestamptz but hashed from a Python str). Serialize occurred_at identically in both paths, as the helper does with isoformat().
  • Rows insert but prev_hash is always the genesis value. The SELECT ... ORDER BY id DESC LIMIT 1 runs in a transaction that cannot see prior committed rows, or concurrent writers race. Serialize audit writes (a single writer, or SELECT ... FOR UPDATE on a chain-head row) so each row chains to the true predecessor.
  • Partition-missing error on insert (no partition of relation ... found). A row’s occurred_at falls outside every defined partition. Pre-create the next month’s partition on a schedule, or add a DEFAULT partition to catch stragglers.
  • Audit writes slow the ingestion job. The synchronous SELECT for the previous hash serializes throughput. Batch accesses under one job and write a single summarizing row per subject, or move audit writes onto an append queue — but never drop the row; a missing row is the audit gap called out in FERPA-Compliant PII Handling.
  • Token reversal is not showing in the log. A vault reversal happened outside the guarded helper. Route every reversal from the tokenization vault through write_audit with action='reverse_token'.

Part of: FERPA-Compliant PII Handling