init.sql

CREATE TABLE IF NOT EXISTS transactions (
    correlation_id UUID PRIMARY KEY,
    amount         NUMERIC(19, 4) NOT NULL,
    requested_at   TIMESTAMPTZ    NOT NULL,
    processed_at   TIMESTAMPTZ    NOT NULL,
    processor      VARCHAR(16)    NOT NULL,

    CONSTRAINT transactions_processor_chk CHECK (processor IN ('default', 'fallback'))
);

DROP INDEX IF EXISTS idx_transactions_default_processed_at;
DROP INDEX IF EXISTS idx_transactions_fallback_processed_at;

CREATE INDEX IF NOT EXISTS idx_transactions_default_requested_at
ON transactions (requested_at)
WHERE processor = 'default';

CREATE INDEX IF NOT EXISTS idx_transactions_fallback_requested_at
ON transactions (requested_at)
WHERE processor = 'fallback';

CREATE TABLE IF NOT EXISTS retry_jobs (
    correlation_id   UUID PRIMARY KEY,
    amount           NUMERIC(19, 4) NOT NULL,
    requested_at     TIMESTAMPTZ    NOT NULL,
    target_processor VARCHAR(16)    NOT NULL,
    attempts         INT            NOT NULL,
    next_retry_at    TIMESTAMPTZ    NOT NULL,
    last_error       TEXT,

    CONSTRAINT retry_jobs_processor_chk CHECK (target_processor IN ('default', 'fallback'))
);

CREATE INDEX IF NOT EXISTS idx_retry_jobs_next_retry_at
ON retry_jobs (next_retry_at);

CREATE TABLE IF NOT EXISTS processor_health (
    processor         VARCHAR(16) PRIMARY KEY,
    failing           BOOLEAN      NOT NULL,
    min_response_time INT          NOT NULL,
    observed_at       TIMESTAMPTZ  NOT NULL,
    updated_by        TEXT         NOT NULL,

    CONSTRAINT processor_health_processor_chk CHECK (processor IN ('default', 'fallback'))
);

CREATE TABLE IF NOT EXISTS processor_health_leases (
    lease_key   TEXT PRIMARY KEY,
    holder_id   TEXT        NOT NULL,
    lease_until TIMESTAMPTZ NOT NULL
);