V1__init.sql

CREATE TABLE payments (
    id                       UUID PRIMARY KEY,
    merchant_id              VARCHAR(64)  NOT NULL,
    status                   VARCHAR(16)  NOT NULL,
    card_number_last_four    VARCHAR(4)   NOT NULL,
    expiry_month             INTEGER      NOT NULL,
    expiry_year              INTEGER      NOT NULL,
    currency                 CHAR(3)      NOT NULL,
    amount                   INTEGER      NOT NULL,
    auth_code                VARCHAR(32),
    decline_reason           VARCHAR(128),
    created_at               TIMESTAMPTZ  NOT NULL
);

CREATE INDEX payments_by_merchant ON payments (merchant_id, created_at DESC);

CREATE TABLE payment_outbox (
    id            UUID PRIMARY KEY,
    aggregate_id  UUID         NOT NULL,
    topic         VARCHAR(128) NOT NULL,
    key_hint      VARCHAR(128),
    payload       TEXT         NOT NULL,
    created_at    TIMESTAMPTZ  NOT NULL,
    published_at  TIMESTAMPTZ
);

CREATE INDEX payment_outbox_unpublished ON payment_outbox (created_at) WHERE published_at IS NULL;