ADR-048: Database-enforced invariants — Postgres constraints and triggers as a first-class defence layer

Status	Accepted
Date	2026-05-01
Deciders	CTO, Head of Architecture, Head of Engineering
Affects repos	bank-core, bank-kyc, bank-aml, bank-payments, bank-credit, bank-platform

---

Status: Accepted — 2026-05-01

Context

The current implicit model treats Neon Postgres as a passive store and Lambda as the sole validator of business rules. Lambda reads state, applies rules, and writes results. This works, but it has a structural weakness: any path that bypasses the Lambda — a migration script, a direct psql session, a future module that writes to the same table, a hot-fix executed under pressure — also bypasses the business rule. The invariant exists in application code only; it is not encoded in the data.

This weakness is most visible at accounts.postings. The double-entry balance equation (sum(DEBIT amounts) = sum(CREDIT amounts) per currency per transaction_id) is a mathematical invariant of the ledger. If it is violated, the ledger is wrong. Enforcing it only in Lambda means a single bug, a concurrent write, or an operator INSERT can silently corrupt the ledger. The same argument applies to immutability constraints on audit tables, status-domain constraints, and temporal consistency on rate tables.

A second argument is performance. For hot paths like posting, validating that an account is not CLOSED or DORMANT requires a Lambda round-trip to the database for a SELECT, then a second round-trip for the INSERT. If the DB enforces the rule, the guard fires inside the INSERT transaction — one round-trip, zero additional latency.

A third argument is defence in depth. Even if Lambda validation is correct today, DB-level constraints survive refactors, framework migrations, and module replacements. They are the hard floor.

This ADR establishes a classification framework and mandates DB-level enforcement for two categories of rule. It does not change the role of Lambda — Lambda retains full responsibility for orchestration, event emission, external state lookups, and config-driven thresholds.

Decision

Rule classification

Rules are classified into three categories. Category determines where enforcement lives.

Category 1 — Structural invariants

Rules that are mathematically or logically true about the data, independent of context, configuration, or external state. Examples: an amount is positive; a status value is in the permitted set; a posting row is never updated. These are always enforced at the DB layer via CHECK constraints, NOT NULL, UNIQUE, domain types, or immutability triggers. Lambda may also check them, but the DB check is authoritative. If they conflict, the DB wins and raises an exception.

Category 2 — Data-local business rules

Rules that require joining tables to enforce, but all the relevant data lives in the same Neon instance. Examples: a posting to a CLOSED account is rejected; debit legs balance credit legs within a transaction; a rate's effective_to is later than its effective_from. These are enforced at the DB layer via triggers when all three of the following hold:

1. All data required to evaluate the rule is in the same Neon database.
2. The rule is deterministic — same inputs, same outcome, always.
3. The rule has no side effects — no event emission, no external API call, no config read.

If any condition fails, the rule stays in Lambda.

Category 3 — Cross-service and config-driven rules

Rules that require external state (another service domain, EventBridge, AppConfig, SSM), or that have side effects (event emission, notification dispatch). These always live in Lambda. Examples: KYC gate (requires kyc status from a different database); SAR threshold check (AppConfig-configurable); dormancy reactivation logic (depends on customer_initiated metadata that arrives via event).

Implementation standards

1. All DB-level constraints and triggers are implemented in numbered Flyway migrations (V0NN__describe.sql). No constraint is applied outside a migration.
2. Triggers must be SECURITY DEFINER with the executing role restricted to bank_core_app_user (or equivalent per-domain app user). They must not perform DML on other tables — only RAISE EXCEPTION or RETURN NEW/OLD.
3. The double-entry balance trigger on accounts.postings uses a DEFERRED constraint trigger (DEFERRABLE INITIALLY DEFERRED) so that both legs of the transaction are visible when the check fires at COMMIT time.
4. Every DB-level constraint and trigger is documented in the relevant system data model page under a DB-enforced invariants section. Agents and modules must not duplicate the enforcement in Lambda without documenting the redundancy as intentional (defence in depth).
5. Migration tests must include a negative test that proves the constraint fires: INSERT a row that violates the invariant inside a test transaction, assert the expected exception, ROLLBACK.

Canonical implementations

The following are the reference implementations that demonstrate the pattern. Each system domain's data model page is the authoritative register of what is actually deployed.

Immutability trigger (append-only tables)

CREATE FUNCTION fn_immutable_row() RETURNS trigger
  LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  RAISE EXCEPTION 'Table % is append-only: UPDATE and DELETE are not permitted',
    TG_TABLE_NAME;
END;
$$;

CREATE TRIGGER trg_postings_immutable
  BEFORE UPDATE OR DELETE ON accounts.postings
  FOR EACH ROW EXECUTE FUNCTION fn_immutable_row();

Account status gate (posting validation)

CREATE FUNCTION fn_check_posting_account_status() RETURNS trigger
  LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  acct_status text;
BEGIN
  SELECT status INTO acct_status
    FROM accounts.accounts WHERE id = NEW.account_id;
  IF acct_status IN ('CLOSED', 'DORMANT') THEN
    RAISE EXCEPTION
      'Cannot post to account % with status %: account must be ACTIVE or RESTRICTED',
      NEW.account_id, acct_status;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_posting_account_status
  BEFORE INSERT ON accounts.postings
  FOR EACH ROW EXECUTE FUNCTION fn_check_posting_account_status();

Double-entry balance (deferred constraint trigger)

CREATE FUNCTION fn_check_double_entry() RETURNS trigger
  LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  IF EXISTS (
    SELECT currency FROM accounts.postings
    WHERE transaction_id = NEW.transaction_id
    GROUP BY currency
    HAVING
      SUM(CASE WHEN entry_type = 'DEBIT'  THEN amount ELSE 0 END) <>
      SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END)
  ) THEN
    RAISE EXCEPTION
      'Double-entry imbalance for transaction_id %: debits do not equal credits per currency',
      NEW.transaction_id;
  END IF;
  RETURN NEW;
END;
$$;

CREATE CONSTRAINT TRIGGER trg_postings_double_entry
  AFTER INSERT ON accounts.postings
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION fn_check_double_entry();

This trigger fires per-currency. For a standard 2-leg same-currency transaction it reduces to debit = credit. For MOD-004's 4-leg multi-currency conversion it checks balance independently for each currency involved, which is mathematically correct.

Consequences

accounts.postings gains three DB-enforced guards: immutability trigger, account-status INSERT gate, and the deferred double-entry balance trigger. Lambda (MOD-001) continues to validate these conditions before attempting the INSERT — the DB check is defence in depth, not a replacement.

Audit tables across all domains (aml.case_events, kyc.kyc_audit_events, accounts.fx_rates, accounts.fx_conversions, accounts.daily_balance_snapshots, accounts.daily_trial_balance) should already have immutability triggers. Each domain's data model page is the authoritative register of what is deployed.

Rate and schedule tables gain CHECK (effective_to IS NULL OR effective_to > effective_from) constraints. accounts.interest_rates gains CHECK (annual_rate >= 0). core.fee_schedule gains CHECK (notice_days >= 14) (CON-005 floor). These are simple CHECK constraints added by each module's next migration.

Lambda validation is not removed. Lambda checks remain as the user-facing error path: they run before the DB call, produce structured error responses (HTTP 422 with error codes), and handle config-driven thresholds that the DB cannot evaluate. The DB constraint is the guarantee that no bypass path corrupts the data; the Lambda check is the user experience.

Testing requirement. Every constraint and trigger introduced under this ADR requires a negative migration test. The test transaction must attempt the violating INSERT/UPDATE/DELETE, catch the expected exception, and ROLLBACK. This test must be in the module's tests/integration/ directory alongside the positive path tests.

---

All ADRs Compiled 2026-05-22 from source/entities/adrs/ADR-048.yaml