Database Schema

PostgreSQL schemas for bopen are defined as versioned SQL scripts under database/scripts/. All application tables live in the bopen schema on RDS. The design follows a consent-vs-execution model inspired by the Open Banking UK specification.

Design philosophy

The schema is built on three principles:

Immutable audit trail — payment_events is append-only, enforced at the trigger layer and the role layer. No application bug can silently mutate the audit log.
Snapshot-at-execution-time — Fee percentages and flat fees are copied onto each payment_intent row at creation. Changing a merchant's fee schedule never retroactively alters settled transactions.
VRP upgrade path — The payment_consents.control_parameters JSONB column is the extension slot for Variable Recurring Payments. V1 code ignores it; V2 code populates and enforces periodic spending caps without a schema change.

Entity relationship

erDiagram
    merchants ||--o{ merchant_bank_accounts : "settles to"
    merchants ||--o{ payment_consents : "owns"
    merchants ||--o{ webhook_deliveries : "receives"
    payment_consents ||--o{ payment_intents : "authorises"
    payment_intents ||--o{ payment_events : "audits"

    merchants {
        uuid id PK
        varchar cognito_client_id UK
        varchar legal_name
        varchar trading_name
        varchar name
        kyb_status kyb_status
        numeric fee_percentage
        numeric fee_flat
        varchar webhook_url
        boolean is_active
    }

    merchant_bank_accounts {
        uuid id PK
        uuid merchant_id FK
        varchar account_holder_name
        text encrypted_account_number
        text encrypted_sort_code
        text encrypted_iban
        boolean is_active
    }

    payment_consents {
        uuid id PK
        uuid merchant_id FK
        consent_type consent_type
        consent_status status
        varchar bank_consent_reference UK
        numeric amount
        char currency
        jsonb control_parameters
        timestamptz expires_at
    }

    payment_intents {
        uuid id PK
        uuid merchant_id FK
        uuid consent_id FK
        varchar idempotency_key
        numeric amount
        numeric gross_amount
        numeric fee_amount
        numeric net_amount
        char currency
        payment_status status
        varchar end_to_end_id UK
        varchar bank_consent_id
        varchar bank_reference
    }

    payment_events {
        bigserial ordinal PK
        uuid id UK
        uuid payment_intent_id FK
        payment_status previous_status
        payment_status new_status
        varchar changed_by
        jsonb raw_payload
        char raw_payload_sha256
    }

    webhook_deliveries {
        uuid id PK
        uuid merchant_id FK
        uuid payment_intent_id FK
        varchar event_id UK
        varchar event_type
        jsonb payload
        webhook_status status
        smallint attempt_count
        timestamptz next_retry_at
        smallint last_http_status
        timestamptz delivered_at
    }

Two data domains

Domain	Migrations	Tables	Purpose
Information	`001`	`customers`, `accounts`, `transactions`	Account Information API (AIS) — balances, booked transactions
B2B Payments	`003–007`	`merchants`, `merchant_bank_accounts`, `payment_consents`, `payment_intents`, `payment_events`	Acquirer payment initiation (PIS) — A2A payment lifecycle

B2B payment tables

`merchants`

Corporate tenants (acquirers). Each merchant maps to an AWS Cognito app client.

Column	Type	Purpose
`cognito_client_id`	`VARCHAR UNIQUE`	Tenant lookup from the authenticated API client
`legal_name`	`VARCHAR NOT NULL`	Registered company name
`trading_name`	`VARCHAR NOT NULL`	Brand / DBA name
`kyb_status`	`kyb_status` enum	KYB gate — only `APPROVED` merchants can initiate payments
`fee_percentage`	`NUMERIC(5,4)`	e.g. `0.0100` = 1.00%
`fee_flat`	`NUMERIC(10,2)`	Flat fee in GBP, e.g. `0.20` = £0.20
`webhook_url`	`VARCHAR`	Endpoint for asynchronous payment status events
`is_active`	`BOOLEAN`	Soft enable/disable without deleting tenant data

KYB state machine:

PENDING → APPROVED → SUSPENDED

`merchant_bank_accounts`

Encrypted settlement destination accounts for merchant payouts.

Column	Purpose
`encrypted_account_number`	AES-encrypted at application layer before storage
`encrypted_sort_code`	AES-encrypted at application layer before storage
`encrypted_iban`	Optional; used for SEPA payouts
`is_active`	Multiple accounts can exist; only active accounts receive payouts

Account number and sort code are encrypted at the application layer before being written to the database. The portal UI masks them as ****{last4}.

`payment_consents`

The authorisation grant from the bank (ASPSP). Decoupled from execution.

Column	Type	Purpose
`consent_type`	`consent_type` enum	`SINGLE_IMMEDIATE` or `VARIABLE_RECURRING`
`status`	`consent_status` enum	Consent state machine
`bank_consent_reference`	`VARCHAR UNIQUE`	Reference string returned by the ASPSP
`amount`	`NUMERIC(19,4)`	Fixed amount for `SINGLE_IMMEDIATE`; NULL for VRP
`control_parameters`	`JSONB`	VRP extension slot — spending limits, periodic caps
`expires_at`	`TIMESTAMPTZ NOT NULL`	Legal consent expiry (typically 90 minutes for single-immediate)

Consent status state machine:

AWAITING_AUTHORISATION → AUTHORISED → (consent remains active for VRP)
        ↓                    ↓
    REJECTED              REVOKED
        ↓
     EXPIRED

Payer context fields (OBL / PSD2 PIS compliance, migration 011):

Column	Type	Regulatory purpose
`payer_name`	`VARCHAR(140)`	AML match confirmation against bank account holder profile. Injected by merchant API.
`debtor_account_scheme`	`debtor_account_scheme` enum	Routing format: `UK.OBIE.SortCodeAccountNumber` or `UK.OBIE.IBAN`
`debtor_identification`	`VARCHAR(34)`	Sort code + account number or IBAN
`remittance_reference`	`VARCHAR(18)`	Statement reference visible in payer's bank ledger
`debtor_bank_id`	`VARCHAR(50)`	ASPSP identifier for the payer's chosen bank (e.g. `"monzo"`)

When debtor_account_scheme + debtor_identification are pre-supplied by the merchant, the checkout widget skips ST-01 (consent) and ST-02 (bank selection) entirely, routing the payer directly to ST-03.

VRP control_parameters schema (v2):

{
  "max_amount_per_payment": 50.00,
  "periodic_limits": [
    { "period": "MONTH", "max_amount": 200.00 }
  ]
}

V1 code ignores this column entirely. A NULL value means SINGLE_IMMEDIATE.

`payment_intents`

The physical money movement. One row per payment execution instruction.

Column	Type	Purpose
`consent_id`	`UUID FK`	Links to the authorising consent
`idempotency_key`	`VARCHAR`	Unique per `(merchant_id, idempotency_key)` — safe retries
`end_to_end_id`	`VARCHAR(35) UNIQUE`	ISO 20022 reference injected into the interbank network
`gross_amount`	`NUMERIC(19,4)`	Full payment amount
`fee_amount`	`NUMERIC(19,4)`	`(gross × fee_%) + flat` — snapshotted at creation
`net_amount`	`NUMERIC(19,4)`	`gross − fee` — amount remitted to merchant
`bank_consent_id`	`VARCHAR`	OAuth state token used in the SCA redirect
`bank_reference`	`VARCHAR`	Bank-assigned settlement reference after authorisation

end_to_end_id format: BOP prefix + 32-char UUID hex (no hyphens) = 35 chars (BACS/FPS field length limit).

Payment status state machine:

Initiated → AwaitingAuthorisation → Authorised → Settled
                  ↓                     ↓
                Failed               Failed

`payment_events`

Append-only audit ledger. Every status transition inserts a row here. No updates, no deletes — ever.

Column	Type	Purpose
`ordinal`	`BIGSERIAL`	Monotonically increasing — use for `ORDER BY`, not `created_at` (avoids timestamp ties)
`previous_status`	`payment_status`	NULL for the initial creation event
`new_status`	`payment_status`	Transition target
`changed_by`	`VARCHAR(100)`	Actor: `MERCHANT_API`, `BANK_CALLBACK`, `BATCH_JOB`
`raw_payload`	`JSONB`	Full webhook/callback body for non-repudiation and debug

| raw_payload_sha256 | CHAR(64) | Hex-encoded SHA-256 of raw_payload::text, computed by PostgreSQL at insert time: encode(sha256($payload::text::bytea), 'hex'). Enables independent cryptographic verification that the stored bank response was not altered after receipt. |

Immutability enforcement (three layers):

Role grants — bopen_app has SELECT, INSERT only (migration 003)
BEFORE UPDATE/DELETE trigger — raises an exception, rolls back the transaction (migration 004)
BEFORE TRUNCATE trigger — statement-level, blocks privileged wipe (migration 004)
REVOKE — UPDATE, DELETE, TRUNCATE revoked from bopen_migrator on this table (migration 004)

`webhook_deliveries`

Transactional outbox and full delivery audit log for outbound merchant webhook events. The background worker dispatches rows with status IN ('PENDING','FAILED') AND next_retry_at <= NOW() using FOR UPDATE SKIP LOCKED to prevent concurrent dispatch across ECS tasks.

Column	Type	Purpose
`event_id`	`VARCHAR(64) UNIQUE`	Globally unique event identifier (`evt_{ms}_{suffix}`). Merchants use this as an idempotency key.
`event_type`	`VARCHAR(64)`	`payment.initiated`, `payment.authorized`, `payment.settled`, `payment.failed`, `payment.refunded`
`payload`	`JSONB`	Full JSON envelope (eventId, eventType, apiVersion, createdAt, data)
`status`	`webhook_status`	`PENDING → DELIVERED` or `PENDING → FAILED → DEAD`
`attempt_count`	`SMALLINT`	Number of dispatch attempts made so far
`next_retry_at`	`TIMESTAMPTZ`	Worker dispatches when `<= NOW()`. Set to `NOW()` on insert for immediate first attempt.
`last_http_status`	`SMALLINT`	HTTP response code from the merchant's endpoint
`last_error`	`TEXT`	Error detail for non-2xx or timeout responses
`delivered_at`	`TIMESTAMPTZ`	Set when status transitions to DELIVERED

Retry schedule (exponential backoff with jitter):

Attempt	Delay
1	Immediate
2	1 minute
3	5 minutes
4	30 minutes
5	2 hours
6	6 hours (final)

After 6 failures the status transitions to DEAD. Merchants can trigger a manual replay via POST /api/v1/portal/webhooks/:id/replay, which resets status = PENDING and attempt_count = 0.

New `merchants` columns (migrations 011–012)

Column	Purpose
`realm_slug`	Keycloak realm identifier (e.g. `acme-corp`). Set at self-registration. Used to scope portal API calls.
`webhook_signing_secret`	64-char hex HMAC-SHA256 signing key. Auto-generated; rotatable via portal.

Information model

Table	Purpose
`customers`	End-customer identity (`external_customer_id`, email)
`accounts`	Bank accounts linked to a customer; `balance_minor` in minor units
`transactions`	Booked ledger entries per account (`amount_minor` in minor units)

Amounts in the information schema use minor units (BIGINT). The B2B payment schema uses decimal amounts (NUMERIC(19,4)) to match Open Banking payment initiation payloads.

Script execution order

 roles/001_app_roles.sql
 init/001_extensions.sql
 migrations/001_initial_schema.sql
 migrations/002_permissions.sql
 migrations/003_b2b_payments.sql
 migrations/004_audit_enforcement.sql
 migrations/005_merchant_enrichment.sql
 migrations/006_payment_consents.sql
 migrations/007_audit_ledger_enrichment.sql
migrations/009_rename_client_id.sql
migrations/010_schema_completions.sql
migrations/011_payer_data_fields.sql
migrations/012_webhook_deliveries.sql
migrations/013_disputes_and_platform.sql
seeds/001_dev_seed.sql               (dev/test only)
seeds/002_dev_b2b_seed.sql           (dev/test only)
seeds/003_dev_merchant_enrichment_seed.sql (dev/test only)

export DATABASE_URL="postgres://<user>:<password>@<host>:5432/bopen"

for f in \
  database/scripts/roles/001_app_roles.sql \
  database/scripts/init/001_extensions.sql \
  database/scripts/migrations/001_initial_schema.sql \
  database/scripts/migrations/002_permissions.sql \
  database/scripts/migrations/003_b2b_payments.sql \
  database/scripts/migrations/004_audit_enforcement.sql \
  database/scripts/migrations/005_merchant_enrichment.sql \
  database/scripts/migrations/006_payment_consents.sql \
  database/scripts/migrations/007_audit_ledger_enrichment.sql \
  database/scripts/migrations/009_rename_client_id.sql \
  database/scripts/migrations/010_schema_completions.sql \
  database/scripts/migrations/011_payer_data_fields.sql \
  database/scripts/migrations/012_webhook_deliveries.sql \
  database/scripts/migrations/013_disputes_and_platform.sql \
  database/scripts/seeds/001_dev_seed.sql \
  database/scripts/seeds/002_dev_b2b_seed.sql \
  database/scripts/seeds/003_dev_merchant_enrichment_seed.sql
do
  psql "$DATABASE_URL" -f "$f"
done

Production must skip seed scripts. Migration 008_keycloak_setup.sql is intentionally excluded from this list — it requires RDS admin privileges the app role doesn't have and must be run separately, once, by an operator (see Keycloak Setup).

No schema-version tracking — every file replays on every boot

There is no schema_migrations table. payment-service's migration runner (RUN_MIGRATIONS=true) batch-executes every file in this list, unconditionally, on every single container start — not just the first time. This means every statement in every migration must remain valid forever, even after a later migration changes the schema it depends on.

This bit twice in practice: migration 003 unconditionally ran CREATE INDEX ... ON bopen.merchants(cognito_client_id), and migration 009 later renamed that column — so on the very next container restart, migration 003's index creation (and migration 009's own rename statement) both failed permanently, because the column no longer existed under its old name. Both are now guarded with information_schema existence checks before the DDL runs. Separately, INSERT ... ON CONFLICT DO UPDATE seed statements were found to fail with NOT NULL violations on columns added by later migrations, even when the row already existed and the update branch would never touch that column — PostgreSQL validates the full candidate tuple before checking for a conflict.

Before adding any migration that renames, drops, or retypes a column, grep for existing unconditional references to it across the whole directory:

grep -rn "column_name_here" database/scripts/migrations/ database/scripts/seeds/

Full writeup: Keycloak Setup — issues #11 and #12.

Migration summary

Migration	Purpose
`001_initial_schema.sql`	`customers`, `accounts`, `transactions` (AIS read model)
`002_permissions.sql`	Schema grants for `bopen_app` and `bopen_migrator`
`003_b2b_payments.sql`	`merchants`, `payment_intents`, `payment_events`; `payment_status` enum; `updated_at` triggers
`004_audit_enforcement.sql`	Immutable ledger: BEFORE UPDATE/DELETE/TRUNCATE triggers + REVOKE on `payment_events`
`005_merchant_enrichment.sql`	`kyb_status` enum; `legal_name`, `trading_name`, `fee_percentage`, `fee_flat` on merchants; `merchant_bank_accounts` table
`006_payment_consents.sql`	`consent_type`, `consent_status` enums; `payment_consents` table; `consent_id`, `end_to_end_id`, `gross/fee/net_amount` on `payment_intents`
`007_audit_ledger_enrichment.sql`	`changed_by` actor column; `ordinal BIGSERIAL` for deterministic audit ordering
`008_keycloak_setup.sql`	Operator-only: `keycloak_core` schema + `keycloak_user` role (run as RDS admin)
`009_rename_client_id.sql`	Renames `cognito_client_id` → `client_id` on `merchants`
`010_schema_completions.sql`	`payment_consents.debtor_bank_id`; `payment_intents.failure_code`; `payment_status += 'Refunded'`; `payment_events.raw_payload_sha256`
`011_payer_data_fields.sql`	`debtor_account_scheme` enum; `payer_name`, `debtor_account_scheme`, `debtor_identification`, `remittance_reference` on `payment_consents`
`012_webhook_deliveries.sql`	`merchants.realm_slug`; `merchants.webhook_signing_secret`; `webhook_status` enum; `webhook_deliveries` table
`013_disputes_and_platform.sql`	`dispute_reason`, `dispute_status`, `dispute_opener`, `platform_role` enums; `disputes` table; `payer_tokens` table (magic-link SHA-256 hashes); `platform_users` table (internal staff OWNER/OPS/SUPPORT); `merchants.branding_config` JSONB + payout hold columns; `payment_intents.refunded_amount`

Disputes table

The disputes table tracks the lifecycle of customer complaints and refund requests under the OBL Dispute Management System framework:

Column	Type	Notes
`id`	UUID	Primary key
`payment_intent_id`	UUID	FK → payment_intents
`merchant_id`	UUID	FK → merchants
`opened_by`	enum	`PAYER \| MERCHANT \| PLATFORM`
`reason`	enum	`ITEM_NOT_RECEIVED \| DUPLICATE_PAYMENT \| INCORRECT_AMOUNT \| UNAUTHORISED \| OTHER`
`status`	enum	`OPEN → UNDER_REVIEW → RESOLVED_MERCHANT_FAVOUR / RESOLVED_PAYER_FAVOUR / ESCALATED`
`payer_statement`	TEXT	Consumer's description of the issue
`merchant_evidence`	TEXT	Merchant's fulfilment proof
`resolution_note`	TEXT	Platform operator's resolution reason

Webhooks fire on key transitions: dispute.opened (notifies merchant) and dispute.resolved (notifies merchant of outcome).

Roles and security

Role	Purpose
`bopen_migrator`	Runs migrations; full DDL/DML on `bopen` schema (except audit table mutations — revoked)
`bopen_app`	Runtime services; least-privilege DML; INSERT-only on `payment_events`

Deployed environments retrieve database credentials from AWS Secrets Manager (KMS-encrypted, auto-rotated). The payment_service binary applies migrations at startup when RUN_MIGRATIONS=true.

API to database mapping

API endpoint	Primary tables
`POST /api/v1/payments`	`payment_consents`, `payment_intents`, `payment_events`, `webhook_deliveries`
`GET /api/v1/payments/:id`	`payment_intents`, `payment_events`
`POST /api/v1/payments/:id/sca-complete`	`payment_intents`, `payment_consents`, `payment_events`, `webhook_deliveries`
`POST /api/v1/payments/:id/refund`	`payment_intents` (status → Refunded, refunded_amount), `payment_events`, `webhook_deliveries`
`POST /api/v1/payments/:id/dispute`	`disputes`, `webhook_deliveries`
`GET /api/v1/portal/disputes`	`disputes` (scoped by merchant)
`PUT /api/v1/portal/disputes/:id`	`disputes.merchant_evidence`, status → UNDER_REVIEW
`GET/PUT /api/v1/portal/branding`	`merchants.branding_config`
`GET /api/v1/portal/webhooks`	`webhook_deliveries` (scoped by `merchants.realm_slug`)
`POST /api/v1/portal/webhooks/:id/replay`	`webhook_deliveries` (resets DEAD → PENDING)
`GET/POST /api/v1/portal/webhook-secret`	`merchants.webhook_signing_secret`
`GET /api/v1/admin/disputes`	`disputes` (all merchants)
`PUT /api/v1/admin/disputes/:id/resolve`	`disputes.status`, `resolution_note`, `webhook_deliveries`
`GET /api/v1/admin/merchants`	`merchants`
`PUT /api/v1/admin/merchants/:id/kyb`	`merchants.kyb_status`
`PUT /api/v1/admin/merchants/:id/hold`	`merchants.payout_held`, `payout_held_at`, `payout_held_by`
`POST /api/v1/payer/auth/request-link`	`payer_tokens` (stores SHA-256 hash, 10-min expiry)
`POST /api/v1/payer/auth/verify`	`payer_tokens` (marks used, returns HMAC session JWT)
`GET /api/v1/payer/payments`	`payment_intents`, `payment_consents`, `merchants` (by payer identity)
`GET /api/v1/payer/consents`	`payment_consents` (active VRP mandates for this payer)
`DELETE /api/v1/payer/consents/:id`	`payment_consents.status` → REVOKED
`POST /api/v1/payer/payments/:id/dispute`	`disputes`, `webhook_deliveries`

Design philosophy​

Entity relationship​

Two data domains​

B2B payment tables​

merchants​

merchant_bank_accounts​

payment_consents​

payment_intents​

payment_events​

webhook_deliveries​

New merchants columns (migrations 011–012)​

Information model​

Script execution order​

Migration summary​

Disputes table​

Roles and security​

API to database mapping​