Tenant Audit Log for SOC 2 and HIPAA Without the Compliance Drag

The architecture is one append-only Postgres table, written in the same transaction as the side effect it records, partitioned for retention and query speed, with a per-tenant export path that enforces isolation at the database. The work happens up front in the schema and the write path; the SOC 2 and HIPAA evidence falls out of the design instead of being assembled by hand later.

Narrow audit_events schema

Start with the table. The schema is intentionally narrow — every column either appears in evidence or supports a query that does.

CREATE TABLE audit_events (
  id              uuid        NOT NULL DEFAULT gen_random_uuid(),
  tenant_id       uuid        NOT NULL,
  actor_id        uuid,                -- nullable: system events have no actor
  actor_type      text        NOT NULL,-- 'user' | 'service' | 'system'
  event_type      text        NOT NULL,-- 'auth.login' | 'phi.read' | 'admin.role_grant' | ...
  resource_type   text,                -- 'patient' | 'invoice' | 'api_key' | ...
  resource_id     text,                -- record id, NOT record contents
  action          text        NOT NULL,-- 'read' | 'list' | 'export' | 'create' | 'update' | 'delete'
  outcome         text        NOT NULL,-- 'success' | 'failure'
  request_ip      inet,
  request_id      text,                -- correlation id, ties to app logs
  context         jsonb       NOT NULL DEFAULT '{}'::jsonb,
  event_time      timestamptz NOT NULL DEFAULT now(),
  prev_hash       bytea,               -- hash chain for integrity (HIPAA 164.312(c))
  row_hash        bytea       NOT NULL,
  PRIMARY KEY (event_time, id)
) PARTITION BY RANGE (event_time);

Column intent

The columns are deliberate. tenant_id is the first filter on every query, so it lives outside context to keep the index narrow. actor_id and actor_type together answer "who," with the type column letting you distinguish a service account from a human and a system job from both. event_type is your taxonomy — design it once, hold the line, and the same query answers "every login for this tenant" or "every PHI read by this user." resource_id is a string id only, not the record itself; this is the rule that keeps HIPAA disclosures from multiplying through your audit table. context is a jsonb for the per-event-type detail (the export format, the new role, the changed permission) and is the only freeform column.

Hash-chain integrity columns

The prev_hash and row_hash columns are the integrity chain. Each row's hash is computed over the row's content concatenated with the prior row's hash within the same tenant. That gives you the HIPAA §164.312(c) "corroborate that ePHI has not been altered" property for the audit trail itself: an auditor can pick two boundary rows and prove no row between them was inserted or removed.

Monthly range partitioning

Partition by event_time on a monthly range. With seven years of retention, that is 84 partitions per tenant boundary — well within Postgres's comfort zone, and partition pruning means a query for "this tenant in March 2026" reads exactly one partition.

CREATE TABLE audit_events_2026_01 PARTITION OF audit_events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE INDEX audit_events_2026_01_tenant_time
  ON audit_events_2026_01 (tenant_id, event_time DESC);

CREATE INDEX audit_events_2026_01_tenant_event_type
  ON audit_events_2026_01 (tenant_id, event_type, event_time DESC);

Automate the next-month partition creation with a small cron — running out of partitions is the kind of outage you discover at 00:00 on the first of the month.

Write path in the same transaction as the side effect

The write path is one function called from the same transaction as the side effect. The application does not write an audit row in a separate transaction, does not write it after the response is sent, and does not write it from a background job that runs "soon." If the audit insert fails, the side effect fails. The audit row is the receipt, and you do not perform an action you cannot record.

import { createHash } from "node:crypto";
import type { Transaction } from "./db";

type AuditEventInput = {
  tenantId: string;
  actorId: string | null;
  actorType: "user" | "service" | "system";
  eventType: string;          // e.g. "phi.read"
  resourceType?: string;
  resourceId?: string;
  action: "read" | "list" | "export" | "create" | "update" | "delete";
  outcome: "success" | "failure";
  requestIp?: string;
  requestId?: string;
  context?: Record<string, unknown>;
};

export async function writeAuditEvent(
  tx: Transaction,
  input: AuditEventInput,
): Promise<void> {
  // Hash chain: read the most recent row_hash for this tenant inside the
  // same transaction so concurrent writers serialise on it.
  const prev = await tx.queryOne<{ row_hash: Buffer | null }>(
    `SELECT row_hash
       FROM audit_events
      WHERE tenant_id = $1
      ORDER BY event_time DESC, id DESC
      LIMIT 1
      FOR UPDATE`,
    [input.tenantId],
  );
  const prevHash = prev?.row_hash ?? Buffer.alloc(32); // zero-hash genesis row

  const payload = JSON.stringify({
    tenant_id: input.tenantId,
    actor_id: input.actorId,
    actor_type: input.actorType,
    event_type: input.eventType,
    resource_type: input.resourceType ?? null,
    resource_id: input.resourceId ?? null,
    action: input.action,
    outcome: input.outcome,
    context: input.context ?? {},
  });

  const rowHash = createHash("sha256")
    .update(prevHash)
    .update(payload)
    .digest();

  await tx.query(
    `INSERT INTO audit_events
       (tenant_id, actor_id, actor_type, event_type, resource_type,
        resource_id, action, outcome, request_ip, request_id,
        context, prev_hash, row_hash)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)`,
    [
      input.tenantId,
      input.actorId,
      input.actorType,
      input.eventType,
      input.resourceType ?? null,
      input.resourceId ?? null,
      input.action,
      input.outcome,
      input.requestIp ?? null,
      input.requestId ?? null,
      input.context ?? {},
      prevHash,
      rowHash,
    ],
  );
}

The FOR UPDATE on the previous row is what makes the hash chain safe under concurrent writes for the same tenant. Two simultaneous PHI reads will serialise on the tenant's last row, both will hash the same prior, and you will not get two rows with the same prev_hash (which is what a tampering check looks for).

Lock down the table at the database level. The application role that runs day-to-day traffic only needs INSERT and SELECT.

REVOKE UPDATE, DELETE, TRUNCATE ON audit_events FROM app_role;
GRANT INSERT, SELECT ON audit_events TO app_role;

That is the second layer of "append-only." Even if a code path goes wrong and tries to issue an UPDATE on an audit row, the database refuses. The only role that can purge old partitions is the maintenance role, which runs on a schedule and detaches partitions older than the retention window — it does not delete individual rows. 3)`, [ input.tenantId, input.actorId, input.actorType, input.eventType, input.resourceType ?? null, input.resourceId ?? null, input.action, input.outcome, input.requestIp ?? null, input.requestId ?? null, input.context ?? {}, prevHash, rowHash, ], ); }

The `FOR UPDATE` on the previous row is what makes the hash chain safe under concurrent writes for the same tenant. Two simultaneous PHI reads will serialise on the tenant's last row, both will hash the same prior, and you will not get two rows with the same `prev_hash` (which is what a tampering check looks for).

### Database-level append-only lockdown

Lock down the table at the database level. The application role that runs day-to-day traffic only needs INSERT and SELECT.

sql REVOKE UPDATE, DELETE, TRUNCATE ON audit_events FROM app_role; GRANT INSERT, SELECT ON audit_events TO app_role;

That is the second layer of "append-only." Even if a code path goes wrong and tries to issue an UPDATE on an audit row, the database refuses. The only role that can purge old partitions is the maintenance role, which runs on a schedule and detaches partitions older than the retention window — it does not delete individual rows.

![A confident CTO in a calm office, viewing a monitor with a clean, color-blocked dashboard of organized tenant audit log data.](https://startups.boostframe.io/service-page-photos/patterns/tenant-audit-log-for-soc2-hipaa/03-inline.jpg)

## What gets shipped

The deliverable is the table and partitioning DDL, the write helper above wired through the application's transaction layer, an event taxonomy document that lists every `event_type` the application emits (and what `context` shape each carries), a partition-rotation cron that creates the next month and detaches partitions beyond the retention horizon, a customer-facing audit export endpoint with tenant filtering enforced at the database, and a SIEM forwarder that selects the security-relevant subset and ships it to whatever the security team already uses (Splunk, Datadog SIEM, Panther, Wazuh — the forwarder does not care).

The event taxonomy is the piece teams underestimate. Without it, three engineers invent three different `event_type` strings for the same action — `"login"`, `"auth.login"`, and `"user.signin"` all show up in the same table, and the auditor's query for "all logins for tenant X" misses two thirds of them. Bill writes the taxonomy as a TypeScript union type in the audit module so the compiler refuses any string that is not in the list.

typescript export type AuditEventType = // Auth | "auth.login" | "auth.logout" | "auth.mfa_challenge" | "auth.mfa_failure" | "auth.password_reset_request" | "auth.password_reset_complete" // Tenant access | "tenant.member_invite" | "tenant.member_join" | "tenant.member_remove" | "tenant.role_grant" | "tenant.role_revoke" // Regulated record access (HIPAA-relevant if PHI) | "record.read" | "record.list" | "record.export" | "record.create" | "record.update" | "record.delete" // Admin actions | "admin.config_change" | "admin.api_key_create" | "admin.api_key_revoke" | "admin.webhook_endpoint_change" // Data export | "export.csv_download" | "export.api_bulk_read" | "export.report_generate"; ```

The customer export endpoint resolves the tenant id from the caller's JWT — not from a URL parameter, not from a request body field — and then issues a query that includes WHERE tenant_id = $1 at the database. If the application uses Postgres RLS for tenancy elsewhere, the same RLS policy applies to the audit table for the export role. The signed CSV is streamed (not built in memory) so a customer with millions of audit rows does not blow the export server's heap.