Regulatory Compliance Schemas: Designing Databases That Pass Audits

Robinhood's $70 Million Fine

In 2021, FINRA fined Robinhood $70 million for systemic failures including: inaccurate information displayed to customers, system outages during extreme market volatility, and failure to exercise due diligence. The technical root: systems that could not reconstruct historical state, prove what information was shown to users, or demonstrate proper controls. Compliance is not a checkbox - it is an architecture decision that must be built in from day one.

The Immutable Audit Trail Pattern

Immutable Financial Records:

  RULE: Never UPDATE or DELETE financial records.
  Instead: append corrections with references.

  -- The audit trail schema
  CREATE TABLE journal_entries (
      id              UUID PRIMARY KEY,
      transaction_id  UUID NOT NULL,
      account_id      UUID NOT NULL,
      amount          BIGINT NOT NULL,
      direction       VARCHAR(6) CHECK (direction IN ('DEBIT','CREDIT')),
      description     TEXT,
      correction_of   UUID REFERENCES journal_entries(id),  -- NULL if original
      created_by      UUID NOT NULL,     -- which user/service
      source_ip       INET,              -- request origin
      idempotency_key UUID,              -- deduplication
      correlation_id  UUID,              -- links to request traces
      created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
  );

  -- Prevent mutation at the database level
  CREATE RULE no_update AS ON UPDATE TO journal_entries
      DO INSTEAD NOTHING;
  CREATE RULE no_delete AS ON DELETE FROM journal_entries
      DO INSTEAD NOTHING;

  -- Correction example (wrong amount charged):
  -- Original: Entry #1001, Debit $150 from Customer
  -- Step 1: Reverse original
  INSERT INTO journal_entries (correction_of, amount, direction, ...)
  VALUES ('entry_1001', 15000, 'CREDIT', ...);  -- undo debit
  -- Step 2: Create correct entry
  INSERT INTO journal_entries (amount, direction, ...)
  VALUES (10000, 'DEBIT', ...);  -- correct amount

Point-in-Time Reconstruction

Regulators Ask: 'What was this account's balance on March 15 at 2:30 PM?'

  With mutable balances (UPDATE balance SET...):  IMPOSSIBLE.
  The old value is gone forever.

  With immutable journal entries:  TRIVIAL.

  SELECT SUM(
    CASE WHEN direction = 'CREDIT' THEN amount
         ELSE -amount END
  )
  FROM journal_entries
  WHERE account_id = $1
    AND created_at <= '2024-03-15 14:30:00+00'

  Result: exact balance at that moment, derived from history.

  This also enables:
  - Dispute resolution (what happened exactly?)
  - Forensic investigation (was there unauthorized access?)
  - Regulatory reporting (monthly/quarterly aggregate reports)

Data Retention Tiers

Tiered Storage for Regulatory Compliance:

  Tier      Age       Storage          Query Speed   Cost/TB/mo
  --------  --------  ---------------  -----------   ----------
  Hot       0-1 yr    PostgreSQL       Milliseconds  $200+
  Warm      1-5 yr    Athena+Parquet   Seconds       $23
  Cold      5-7 yr    S3 Glacier       Hours         $4
  Archive   7+ yr     S3 Deep Archive  12-48 hours   $1

  Regulatory minimums:
  - SEC Rule 17a-4: 6 years for broker-dealers
  - GDPR: delete on request (conflicts with SEC!)
  - SOX: 7 years for financial records
  - PCI DSS: 1 year of audit logs

  Migration pipeline:
  [PostgreSQL] --after 1 year--> [Export to Parquet in S3]
       |                              |
  DELETE from PG              [Query with Athena]
  (only after verified                |
   export + checksum match)    --after 5 years-->
                               [Move to Glacier]
                               [Verify checksums]

Interview Tip

When designing financial systems, say: 'All financial records are immutable and append-only. I enforce this with database rules that prevent UPDATE and DELETE on journal tables. Every entry captures who created it, from what IP, with what correlation ID, and at what timestamp. Corrections create reversal entries referencing the original. Account balances at any historical point can be derived by summing the journal up to that timestamp. Data retention follows regulatory requirements: hot in PostgreSQL for 1 year, warm in Parquet/Athena for 5 years, cold in S3 Glacier for 7+ years.'

<

/blockquote>

Key Takeaway

Compliance-ready schemas: never delete or overwrite financial records, capture complete audit metadata on every entry, and implement tiered retention matching regulatory requirements. Build these patterns from day one - retrofitting immutability into a system that was built for mutability is extremely painful.