ADR-001: Postgres as the OLTP operational store¶
| Status | Accepted |
| Date | 2026-04-10 |
| Deciders | CTO, Head of Platform, Chief Architect |
| Affects repos | bank-core, bank-kyc, bank-aml, bank-payments, bank-credit, bank-risk-platform, bank-platform, bank-app |
Status¶
Accepted — 2026-04-10
Context¶
The bank requires a transactional database for the core banking ledger, account management, KYC records, payment processing, and all operational state. The choice must support ACID transactions, event sourcing (append-only ledger), sub-10ms write latency, and reliable CDC to Snowflake (see ADR-003). Neon (serverless Postgres) is being evaluated as an operational variant offering database branching for safe schema migration testing and serverless scaling.
Decision¶
All OLTP workloads use Postgres. Neon is the preferred deployment target for its serverless scaling and branching capability. No operational source-of-truth data lives in Snowflake — Snowflake is the analytics and compute layer only. Snowflake-computed values that need to be read on the transaction path are written back to Postgres first via the write-back API.
Consequences¶
Positive - Strong ACID guarantees for double-entry ledger integrity - CDC pipeline (EventBridge Scheduler → Lambda → Kinesis Firehose → S3 Iceberg) provides reliable WAL-based replication to Snowflake (ADR-003) - Neon branching enables safe schema migration testing against production data snapshots - Mature ecosystem — well-understood operational model
Negative / trade-offs - Connection pooling required at scale (PgBouncer or Neon built-in pooler) - Not suitable for analytical queries — all reporting goes via Snowflake - Horizontal sharding adds complexity if write throughput exceeds single-node limits
Constraints this creates for all repos¶
- No repo may call Snowflake inline on a payment or account transaction path
- All Snowflake-computed values used operationally must be written back to Postgres first
ledger_entriestable is append-only — no UPDATE or DELETE, enforced by trigger- Target: write latency p99 ≤ 10ms, read latency p99 ≤ 5ms (see NFR-012, NFR-013)
Principles alignment¶
| Principle | Assessment | Notes |
|---|---|---|
| AP-001 KISS | ✓ | Single well-understood OLTP store; no polyglot persistence on the transaction path |
| AP-002 Data governance | ✓ | Schema-as-contract; append-only ledger; clear ownership per table |
| AP-006 Cost effective | ✓ | Neon consumption pricing; no always-on over-provisioning |
| AP-007 Evolution | ✓ | Neon branching enables safe schema evolution; CDC decouples consumers |
| AP-009 Robust and serviceable | ✓ | Mature tooling; well-understood failure modes; Neon managed availability |
Perspectives¶
| Perspective | Assessment | Notes |
|---|---|---|
| Performance & Scalability | ✓ | Sub-10ms writes at current scale; Neon autoscaling; sharding path exists if needed |
| Resilience & Availability | ✓ | Neon managed HA; point-in-time recovery; RPO ≤ 1h achievable |
| Evolution | ✓ | Neon branching is the key enabler — schema changes tested on branch before production |
| Integration | ✓ | CDC via logical replication slot + Lambda (ADR-003); standard Postgres WAL interface |
| Support & Maintenance | ✓ | Neon managed service reduces operational burden significantly |
| Cost | ✓ | Consumption-based; cost scales with actual usage |
| Security | ✓ | Column-level encryption for PII; row-level security for tenant isolation |
| Regulatory | ✓ | Data residency in AWS ap-southeast-2/4; NZ/AU compliance maintained |
See perspectives.md for how to use these evaluation lenses.
Relevant viewpoints¶
- System viewpoint — Postgres as the central OLTP node; CDC via ADR-003 pipeline to Snowflake; Tier 2 decisions returned via ADR-036 decision inbox
- Information viewpoint — Schema ownership, append-only constraints, PII classification per table
- Deployment viewpoint — Neon serverless; connection pooler configuration; migration pipeline
- Operational viewpoint — Connection pool monitoring; replication lag alerts; slow query detection
See viewpoints.md for guidance on producing these viewpoints.
API architecture and the Snowflake write-back pattern¶
Postgres is the operational store. But Snowflake is where most analytics are calculated — including risk assessments, sanctions checks, AML scores, credit models, and capital/liquidity positions. Understanding how these two systems interact on the transaction path is critical to getting the architecture right.
The two paths¶
Every bank operation travels one of two paths:
The synchronous path (Postgres only): A customer initiates a payment. The API gateway routes the request to the payments service. The payments service reads the pre-computed fraud score and sanctions status from Postgres — both written there in advance by Snowflake. If all checks pass, the ledger entry is written to Postgres. The response returns in milliseconds. Snowflake is never called inline.
The asynchronous path (CDC → Snowflake → decision inbox): An EventBridge Scheduler triggers the CDC Lambda every 60 seconds per domain database. The Lambda calls pg_logical_slot_get_changes() on the Postgres WAL, advances the replication slot, and ships committed changes to Kinesis Firehose. Firehose writes Apache Iceberg files to S3. Snowflake External Iceberg Tables read those files; Dynamic Tables refresh the risk models, AML scores, and customer intelligence. Approved operational decisions are returned to Postgres via the ADR-036 decision inbox (not a direct write-back). The next transaction reads the freshest available score. Full CDC pipeline detail is in ADR-003.
What Snowflake computes¶
Snowflake is never called on the transaction hot path. It is the system of record and computation engine for everything that does not need to block a payment:
| Assessment | Mode | Latency target | How it reaches Postgres |
|---|---|---|---|
| Transaction fraud score | Write-back | ≤60s from event | Written to risk_scores table |
| Customer AML typology score | Write-back | ≤60s from event | Written to customer_risk table |
| CDD tier reassignment | Write-back | ≤60s from event | Written to customers table |
| Credit pre-approval | Batch nightly | Daily | Written to credit_offers table |
| Capital adequacy (RWA) | Batch daily | End of day | Snowflake only — Tier 3 (ADR-038) |
| LCR / NSFR | Batch daily | End of day | Snowflake only — Tier 3 (ADR-038) |
| Customer health / churn | Batch weekly | Weekly | Snowflake only — Tier 3 (ADR-038) |
What stays in Postgres (synchronous, blocks the transaction)¶
Some checks must be current at the exact moment of the transaction and cannot tolerate any lag:
| Check | Source | Latency |
|---|---|---|
| Balance sufficiency | Postgres accounts — live balance |
<1ms |
| Daily limit | Postgres payment_limits — running total |
<1ms |
| Fraud score gate | Postgres risk_scores — pre-computed by Snowflake |
<1ms read |
| Sanctions status | Postgres sanctions_status — pre-computed, updated on list change |
<1ms read |
| Account state | Postgres accounts.status — Active/Restricted/Frozen |
<1ms |
Sanctions screening design¶
For hard sanctions screening on the synchronous path, the materialised approach is used: a sanctions_status field on the customer record is kept current by the sanctions screening engine, which re-screens all customers whenever a list is updated. When a payment is initiated, the service reads the pre-computed status — it is a fast Postgres index scan, not a live call to Snowflake or an external API.
This gives sub-millisecond performance on the payment path and deterministic behaviour under load, while ensuring the bank's sanctions exposure is always evaluated against the current list.
The write-back API¶
Snowflake writes operational scores back to Postgres via a lightweight internal write-back API. This API: - Accepts a batch of score updates from Snowflake - Validates the format and content - Writes to the target Postgres table using an upsert - Logs the update with timestamp and model version
The write-back API is not on the customer-facing path. It is an internal service called only by Snowflake-side pipelines. It has its own rate limiting and circuit breaker to prevent a Snowflake pipeline failure from propagating to the operational database.
API gateway design¶
Two distinct internal routes are maintained at the API gateway, even if they share the same underlying infrastructure:
Customer-scoped gateway: All requests are scoped to the authenticated customer's own data, enforced at the gateway. A customer JWT cannot call any endpoint that returns another customer's data, regardless of what the client-side code requests. This is the security boundary, not the UI permission check.
Internal/back office gateway: Broader data scope with RBAC enforced per role matrix. Agents, compliance officers, risk managers, and admins each have a different scope. A misconfiguration in the customer gateway cannot expose data through the internal gateway — they are separate gateway policies.
EventBridge domain events¶
Inter-Lambda async communication uses EventBridge (ADR-029, superseded by ADR-051; see ADR-051 for current EventBridge bus naming convention). Domain event names follow the pattern bank.<domain>.<event> — for example bank.transactions.authorised, bank.accounts.status_changed, bank.kyc.verification_completed. This allows domain consumers (enrichment Lambda, rules engine, notification service) to subscribe to the events relevant to them without coupling to the CDC pipeline schema.
End-to-end CDC latency target: Postgres commit → Snowflake table ≤ 5 minutes p99 (NFR-015). Write-back of Tier 2 decisions via ADR-036 decision inbox targets ≤ 60 seconds from Snowflake computation to Postgres application (NFR-014).
Signoff record¶
| Date | Name | Role | Status |
|---|---|---|---|
| 2026-04-10 | Ross Millen | CTO | Approved |
| 2026-04-10 | Ross Millen | Head of Architecture | Approved |
| 2026-04-10 | Ross Millen | Head of Data | Approved |
Capabilities¶
| Capability | Description | Relationship |
|---|---|---|
| CAP-001 | NZ bank account (NZD) | enabled — account state, balance, and ledger entries live in Postgres |
| CAP-002 | AU bank account (AUD) | enabled — same Postgres ledger structure supports AUD accounts |
| CAP-011 | Real-time balance & transaction feed | enabled — balances served sub-millisecond from Postgres on the hot path |
| CAP-015 | Spending limits & card controls | enabled — daily limit and velocity tracking held in Postgres |
| CAP-016 | Instant card freeze / unfreeze | enabled — account status field in Postgres gates card authorisations |
| CAP-029 | Immutable audit log | enabled — ledger_entries append-only constraint enforced by trigger |
| CAP-038 | Real-time fraud scoring & block | enabled — pre-computed fraud score held in Postgres risk_scores table |
| CAP-062 | CDD automatic tier assignment | enabled — CDD tier written back to Postgres via decision inbox |
| CAP-065 | Pre-approval engine (nightly eligibility scoring) | enabled — credit decision existence flag held in Postgres |
| CAP-109 | Session lifecycle management | enabled — active session and device trust held in Postgres |
| CAP-125 | Snowflake write-back API (internal) | enabled — write-back API is the governed path for Snowflake decisions into Postgres |
Related decisions¶
| ADR | Title | Relationship |
|---|---|---|
| ADR-002 | Snowflake as the analytics and risk compute platform | complementary — Postgres is OLTP; Snowflake is analytics |
| ADR-003 | CDC pipeline — Neon Postgres to Snowflake via Firehose and Apache Iceberg | downstream — CDC reads the Postgres WAL |
| ADR-024 | Database hosting — Neon serverless Postgres | implements this decision as the hosting choice |
| ADR-036 | Decision result publication — governed Snowflake → Neon write-back contract | return path — Snowflake decisions written back to Postgres |
| ADR-038 | Data access tier policy — Snowflake as the reporting and insight layer | formalises which Postgres tables are Tier 1 |
All ADRs
Compiled 2026-05-22 from source/entities/adrs/ADR-001.yaml