AUDIT LOG

WHAT THIS PATTERN TEACHES

How to resolve the conflict between tenant-scoped audit tables and system-scope events without silent IntegrityErrors. The two valid patterns (drop NOT NULL or sentinel + tag) and the four integrity properties any audit pipeline must hold.

WHEN TO USE THIS

Any time an audit_log or events table must record BOTH tenant-scoped actions AND system events. The trap: `org_id INTEGER NOT NULL DEFAULT 1` rejects explicit NULL inserts, so spec-vs-code drift silently loses rows.

AT A GLANCE

// Pattern 2: sentinel + JSONB tag
await writeSystemAudit(db, {
  action: 'retention.sweep',
  resource_type: 'job',
  resource_id: jobId,
  decisions: { reason: 'scheduled' },
});

FRAMEWORK IMPLEMENTATIONS

// Pattern 2: sentinel + tag (the cheap, reversible resolution)
// Schema: org_id INTEGER NOT NULL DEFAULT 1, decisions JSONB
// System events: org_id = 1 (sentinel) + decisions.system_event = true

export type AuditEntry = {
  org_id: number;
  user_id: string | null;
  action: string;
  resource_type: string;
  resource_id: string | null;
  decisions: AuditDecisions;
  occurred_at: Date;
};

export type AuditDecisions = {
  system_event?: true;
  reason?: string;
  actor_role?: string;
  [key: string]: unknown;
};