Log schema versioned for regulatory changes

When a regulator examines records from 5 years ago, they need to understand what the log schema meant at that time — whether status meant HTTP status or business status, whether amount was in cents or dollars, whether a column that now exists was absent in earlier records. Without schema versioning, there is no authoritative answer to those questions. NIST 800-53 AU-3 requires audit records to contain sufficient information to establish what happened, which extends to the schema definitions that give field values their meaning. SOX §404 internal control assessments assume that the design of a control is stable and documented — an undocumented schema change is a gap in that documentation. FINRA Rule 4511 requires that books and records be available in a form that permits examination; records whose column semantics have shifted without documentation cannot be reliably examined.

Create an audit_schema_versions table and insert a record for every migration that modifies transaction_logs, in db/migrations/ or supabase/migrations/:

CREATE TABLE audit_schema_versions (
  version       INTEGER PRIMARY KEY,
  applied_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  changelog     TEXT NOT NULL,
  regulatory_reason TEXT
);

INSERT INTO audit_schema_versions (version, changelog, regulatory_reason) VALUES
  (1, 'Initial: id, timestamp, user_id, operation_type, amount, currency',
      'SOX §404 baseline — AU-3 required fields'),
  (2, 'Added balance_before, balance_after',
      'FINRA 4511 — reconciliation support'),
  (3, 'Added entry_hash, previous_hash',
      'PCI-DSS 4.0 Req-10.3.3 — tamper evidence');

ALTER TABLE transaction_logs ADD COLUMN schema_version INTEGER NOT NULL DEFAULT 3;

When applying future migrations, always include an INSERT INTO audit_schema_versions as part of the same migration file so version tracking stays in sync with schema state. Add schema_version as a column in the exported audit log (see finserv-audit-trail.retention-compliance.export-completeness) so regulators can identify which schema applies to each record.

• ID: finserv-audit-trail.tamper-evidence.schema-versioning

• Severity: info

• What to look for: Count all migration files that modify the audit log table. Look for a schema_version column or a separate version tracking table. Quote the actual version number or migration sequence found. Enumerate all schema changes documented in migration files with their dates.

• Pass criteria: Audit log schema has at least 1 explicit version tracking mechanism (a schema_version column, a version tracking table, or numbered migration files with documented changes). At least 1 migration file or changelog entry documents a schema change with date and description. Report the count even on pass (e.g., "3 migrations found for transaction_logs table, schema_version column at v3").

• Fail criteria: No schema versioning (0 version tracking mechanisms) or no documentation of how the log schema has evolved.

• Skip (N/A) when: The audit log table has existed for fewer than 30 days and has never been modified (no schema changes to track yet) — cite the actual creation date found.

• Detail on fail: "No schema versioning — 0 version columns, 0 version tracking tables, 0 documented schema changes.".

• Remediation: Version your audit log schema (in db/migrations/ or supabase/migrations/):

  -- Add schema version metadata
  CREATE TABLE audit_schema_versions (
    version INTEGER PRIMARY KEY,
    changelog TEXT,
    appliedAt TIMESTAMP DEFAULT NOW(),
    regulatory_reason TEXT
  );
  
  INSERT INTO audit_schema_versions (version, changelog, regulatory_reason) VALUES
    (1, 'Initial: id, timestamp, user_id, operation_type, amount, currency', 'Baseline SOX compliance'),
    (2, 'Added balance_before, balance_after columns', 'Enhanced reconciliation support'),
    (3, 'Added entry_hash column for tamper detection', 'GLBA requirement update 2024');
  
  ALTER TABLE transaction_logs ADD COLUMN schema_version INTEGER DEFAULT 3;
  
  -- In migrations:
  INSERT INTO audit_schema_versions (version, changelog, regulatory_reason)
    VALUES (4, 'Added approval_id column for correction tracking', 'Policy update for correction governance');
  ALTER TABLE transaction_logs ADD COLUMN approval_id UUID;
  UPDATE transaction_logs SET schema_version = 4 WHERE schema_version < 4;