Data Model Payments

Data Model - Payments Schema

Part of the Data Model reference. Schema: payments. Companion to Domain-Model, Architecture-Overview.

---

Tables in this schema

Table	Role
payments.payments	Payment lifecycle record (mutable, versioned)
payments.payment_events	Append-only event log per payment
payments.processed_webhooks	Inbound webhook deduplication

---

Table: payments

CREATE TABLE IF NOT EXISTS payments.payments (
    id                         UUID            PRIMARY KEY,
    idempotency_key            VARCHAR(255)    NOT NULL,
    from_account_id            UUID,
    to_account_id              UUID,
    counterparty_name          VARCHAR(255),
    counterparty_iban          VARCHAR(34),
    counterparty_bic           VARCHAR(11),
    counterparty_account       VARCHAR(64),
    counterparty_country       CHAR(2),
    amount                     NUMERIC(38, 18) NOT NULL,
    currency                   CHAR(3)         NOT NULL,
    state                      VARCHAR(32)     NOT NULL,
    ledger_transaction_id      UUID,
    provider_id                VARCHAR(64),
    provider_ref               VARCHAR(255),
    attempts                   INT             NOT NULL DEFAULT 0,
    max_attempts               INT             NOT NULL DEFAULT 6,
    retryable                  BOOLEAN         NOT NULL DEFAULT true,
    next_retry_at              TIMESTAMPTZ,
    last_error                 TEXT,
    reject_reason              VARCHAR(64),
    compliance_case_id         UUID,
    metadata                   JSONB           NOT NULL DEFAULT '{}',
    version                    BIGINT          NOT NULL DEFAULT 0,
    created_at                 TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    created_by                 VARCHAR(64)     NOT NULL,
    updated_at                 TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_by                 VARCHAR(64)     NOT NULL,

    CONSTRAINT uq_payments_idempotency_key  UNIQUE (idempotency_key),
    CONSTRAINT uq_payments_provider_ref     UNIQUE (provider_id, provider_ref),
    CONSTRAINT ck_payments_state CHECK (state IN (
        'CREATED','PROCESSING','COMPLETED','FAILED',
        'PERMANENTLY_FAILED','REJECTED','PENDING_REVIEW','CANCELLED'
    )),
    CONSTRAINT ck_payments_amount_positive  CHECK (amount > 0),
    CONSTRAINT ck_payments_currency         CHECK (currency ~ '^[A-Z]{3}$'),
    CONSTRAINT ck_payments_counterparty     CHECK (
        (to_account_id IS NOT NULL AND counterparty_name IS NULL)
        OR (to_account_id IS NULL  AND counterparty_name IS NOT NULL)
    ),
    CONSTRAINT ck_payments_attempts CHECK (attempts >= 0 AND attempts <= max_attempts)
);

CREATE INDEX IF NOT EXISTS idx_payments_state
    ON payments.payments(state);
CREATE INDEX IF NOT EXISTS idx_payments_from_account
    ON payments.payments(from_account_id) WHERE from_account_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_payments_to_account
    ON payments.payments(to_account_id)   WHERE to_account_id   IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_payments_created
    ON payments.payments(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_payments_retry_pending
    ON payments.payments(next_retry_at)
    WHERE state = 'FAILED' AND retryable = true;
CREATE INDEX IF NOT EXISTS idx_payments_compliance_case
    ON payments.payments(compliance_case_id) WHERE compliance_case_id IS NOT NULL;

CREATE OR REPLACE TRIGGER trg_payments_updated_at
    BEFORE UPDATE ON payments.payments
    FOR EACH ROW EXECUTE FUNCTION shared.set_updated_at();

Query patterns:

• Get payment by ID: PK lookup, p99 < 5ms
• List payments with status filter: idx_payments_state + idx_payments_created
• Retry scheduler poll: idx_payments_retry_pending (partial, PENDING rows only)
• Compliance case lookup: idx_payments_compliance_case (partial)
• Provider webhook correlation: uq_payments_provider_ref

Notes:

• id is stored as UUID and exposed at the API as pay_<crockford-base32> (see ADR-0010).
• version supports optimistic locking (@Version) on the mutable payment aggregate.
• idempotency_key is the client-supplied key (cross-referenced with platform.idempotency_keys). The unique constraint here guards at the payments level; the idempotency_keys table guards at the HTTP request level.
• No cross-schema foreign keys: ledger_transaction_id and compliance_case_id are stored as plain UUIDs without FK constraints, because services may be split to separate databases in later phases.

---

Table: payment_events (append-only event log)

CREATE TABLE IF NOT EXISTS payments.payment_events (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    payment_id      UUID        NOT NULL,
    type            VARCHAR(64) NOT NULL,
    payload         JSONB       NOT NULL,
    occurred_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    actor_type      VARCHAR(16) NOT NULL,
    actor_id        VARCHAR(64) NOT NULL,
    correlation_id  UUID,

    CONSTRAINT fk_payment_events_payment FOREIGN KEY (payment_id)
        REFERENCES payments.payments(id) ON DELETE RESTRICT,
    CONSTRAINT ck_payment_events_actor_type CHECK (actor_type IN ('USER','SERVICE','SYSTEM')),
    CONSTRAINT ck_payment_events_type CHECK (type IN (
        'CREATED','DECISION_APPROVED','DECISION_REJECTED','DECISION_REVIEW_REQUIRED',
        'LEDGER_POSTED','SENT_TO_PROVIDER','PROVIDER_ACK','PROVIDER_TIMEOUT',
        'COMPLETED','FAILED','RETRY_SCHEDULED','PERMANENTLY_FAILED',
        'REVERSED','MANUAL_OVERRIDE','CANCELLED'
    ))
);

CREATE INDEX IF NOT EXISTS idx_payment_events_payment_occurred
    ON payments.payment_events(payment_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_payment_events_correlation
    ON payments.payment_events(correlation_id) WHERE correlation_id IS NOT NULL;

Query patterns:

• Load event history for a payment: idx_payment_events_payment_occurred
• Correlation trace: idx_payment_events_correlation (partial, non-null only)

Notes:

• Append-only. No updates, no deletes, no @Version.
• payload stores the full event data snapshot as JSONB for audit replay.

---

Table: processed_webhooks (inbound deduplication)

CREATE TABLE IF NOT EXISTS payments.processed_webhooks (
    provider_id        VARCHAR(64)  NOT NULL,
    provider_event_id  VARCHAR(255) NOT NULL,
    received_at        TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    payment_id         UUID,
    PRIMARY KEY (provider_id, provider_event_id)
);

CREATE INDEX IF NOT EXISTS idx_processed_webhooks_received
    ON payments.processed_webhooks(received_at DESC);

Query patterns:

• Dedup check on inbound webhook: PK lookup (provider_id, provider_event_id), O(1)
• Cleanup: DELETE WHERE received_at < NOW() - INTERVAL '30 days', uses idx_processed_webhooks_received

Notes:

• provider_id is a logical provider identifier (e.g., sandbox, generic-bank). No real provider names are embedded in the DDL.
• Combined with platform.outbox_events on the outbound side, this table closes the at-least-once delivery loop for inbound webhooks.

---

Liquibase changelogs for this schema

v0.1/
  020-payments-payments.xml
  021-payments-events.xml
  022-payments-processed-webhooks.xml

---

Related

• Data-Model - conventions, ERD, cross-cutting sections
• Data-Model-Ledger - ledger schema (accounts, transactions, entries)
• Data-Model-Platform - outbox, idempotency, webhook subscriptions
• Domain-Model - Payment aggregate definition