Audit Logging

CREATE TABLE audit_logs (
  seq           BIGINT PRIMARY KEY,  -- chain position, assigned by the trigger
  id            UUID NOT NULL DEFAULT gen_random_uuid(),
  occurred_at   TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  actor_id      TEXT NOT NULL,
  actor_type    TEXT NOT NULL CHECK (actor_type IN ('user','service','admin','system','anonymous')),
  action        TEXT NOT NULL,
  resource_type TEXT NOT NULL,
  resource_id   TEXT NOT NULL,
  result        TEXT NOT NULL CHECK (result IN ('success','failure','denied')),
  ip_address    INET,
  session_id    TEXT,
  reason        TEXT,
  changed_fields TEXT[],
  prev_hash     BYTEA,
  hash          BYTEA NOT NULL
);
CREATE INDEX audit_actor_idx ON audit_logs (actor_id, occurred_at DESC);
CREATE INDEX audit_resource_idx ON audit_logs (resource_type, resource_id, occurred_at DESC);

-- The digest covers every business column, so changing any of them breaks the chain.
CREATE FUNCTION audit_row_digest(r audit_logs) RETURNS BYTEA LANGUAGE sql IMMUTABLE AS $$
  SELECT sha256(convert_to(jsonb_build_array(
    r.id, to_char(r.occurred_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'),
    r.actor_id, r.actor_type, r.action, r.resource_type, r.resource_id, r.result,
    host(r.ip_address), r.session_id, r.reason, r.changed_fields,
    encode(r.prev_hash, 'hex'))::text, 'UTF8'))
$$;

-- Link each new row to the previous one. The advisory lock serialises writers,
-- otherwise two concurrent inserts would share one parent and fork the chain.
-- SECURITY DEFINER: the trigger must read the previous row, which an INSERT-only role cannot do.
CREATE FUNCTION audit_chain() RETURNS trigger LANGUAGE plpgsql
  SECURITY DEFINER SET search_path = pg_catalog, public AS $$
BEGIN
  PERFORM pg_advisory_xact_lock(hashtext('audit_logs_chain'));
  SELECT seq + 1, hash INTO NEW.seq, NEW.prev_hash FROM audit_logs ORDER BY seq DESC LIMIT 1;
  NEW.seq := COALESCE(NEW.seq, 1);
  NEW.hash := audit_row_digest(NEW);
  RETURN NEW;
END $$;
CREATE TRIGGER audit_logs_chain BEFORE INSERT ON audit_logs
  FOR EACH ROW EXECUTE FUNCTION audit_chain();

CREATE FUNCTION audit_forbid() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RAISE EXCEPTION 'audit_logs is append-only'; END $$;
CREATE TRIGGER audit_logs_no_change BEFORE UPDATE OR DELETE ON audit_logs
  FOR EACH ROW EXECUTE FUNCTION audit_forbid();
CREATE TRIGGER audit_logs_no_truncate BEFORE TRUNCATE ON audit_logs
  FOR EACH STATEMENT EXECUTE FUNCTION audit_forbid();

CREATE FUNCTION audit_verify() RETURNS TABLE (broken_seq BIGINT) LANGUAGE sql STABLE AS $$
  SELECT seq FROM (
    SELECT a.*, lag(a.hash) OVER (ORDER BY seq) AS expected_prev FROM audit_logs a
  ) t
  WHERE t.hash IS DISTINCT FROM audit_row_digest(ROW(t.seq,t.id,t.occurred_at,t.actor_id,t.actor_type,
          t.action,t.resource_type,t.resource_id,t.result,t.ip_address,t.session_id,t.reason,
          t.changed_fields,t.prev_hash,t.hash)::audit_logs)
     OR t.prev_hash IS DISTINCT FROM t.expected_prev
  ORDER BY seq LIMIT 1
$$;

SELECT * FROM audit_verify();   -- zero rows = chain intact; otherwise the first broken seq

import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.AUDIT_DATABASE_URL });

export async function audit({ actorId, actorType = 'user', action, resourceType, resourceId,
                              result, ip, sessionId, reason, changedFields }) {
  await pool.query(
    `INSERT INTO audit_logs (actor_id, actor_type, action, resource_type, resource_id,
                             result, ip_address, session_id, reason, changed_fields)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`,
    [actorId, actorType, action, resourceType, resourceId, result, ip, sessionId, reason, changedFields]);
}

// Express: log the access decision where it is made, not every request.
app.get('/patients/:id', requireLogin, async (req, res) => {
  const allowed = await canRead(req.user, req.params.id);
  await audit({ actorId: req.user.id, action: 'read', resourceType: 'patient_record',
                resourceId: req.params.id, result: allowed ? 'success' : 'denied',
                ip: req.ip, sessionId: req.sessionID, reason: req.query.reason });
  if (!allowed) return res.sendStatus(403);
  res.json(await loadPatient(req.params.id));
});

aws s3api create-bucket --bucket acme-audit-archive-2026 --region us-east-1 \
  --object-lock-enabled-for-bucket
aws s3api put-object-lock-configuration --bucket acme-audit-archive-2026 \
  --object-lock-configuration '{"ObjectLockEnabled":"Enabled","Rule":{"DefaultRetention":{"Mode":"GOVERNANCE","Days":365}}}'

aws cloudtrail create-trail --name org-audit-trail --s3-bucket-name acme-cloudtrail-2026 \
  --is-multi-region-trail --include-global-service-events --enable-log-file-validation
aws cloudtrail start-logging --name org-audit-trail
aws cloudtrail validate-logs \
  --trail-arn arn:aws:cloudtrail:us-east-1:111122223333:trail/org-audit-trail \
  --start-time 2026-10-01T00:00:00Z

$ psql "$AUDIT_DATABASE_URL" -c "SELECT * FROM audit_verify();"
 broken_seq
------------
(0 rows)

SELECT occurred_at, actor_id, action, result, ip_address, reason
FROM audit_logs
WHERE resource_type = 'patient_record' AND resource_id = 'patient-456'
ORDER BY occurred_at DESC;

SELECT actor_id, COUNT(*) AS refused, MAX(occurred_at) AS last_attempt
FROM audit_logs
WHERE result IN ('failure','denied') AND occurred_at > now() - INTERVAL '24 hours'
GROUP BY actor_id HAVING COUNT(*) > 5
ORDER BY refused DESC;