V1__create_payments_and_idempotency.sql

CREATE TABLE payments (
    id UUID PRIMARY KEY,
    status VARCHAR(32) NOT NULL,
    card_number_last_four INTEGER NOT NULL,
    expiry_month INTEGER NOT NULL,
    expiry_year INTEGER NOT NULL,
    currency VARCHAR(3) NOT NULL,
    amount INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_payments_created_at ON payments (created_at);

CREATE TABLE idempotency_requests (
    idempotency_key VARCHAR(255) PRIMARY KEY,
    request_fingerprint CHAR(64) NOT NULL,
    http_status INTEGER NOT NULL,
    response_json TEXT NOT NULL,
    payment_id UUID REFERENCES payments (id),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_idempotency_created_at ON idempotency_requests (created_at);