RemitFlow

-- Phase 0 foundation: extensions and the shared updated_at trigger function.
-- Money convention: bigint minor units + ISO-4217 char(3). Ids: uuid via pgcrypto.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

COMMENT ON FUNCTION set_updated_at() IS
    'Phase 0 helper: attach as a BEFORE UPDATE trigger so updated_at is never set by hand.';

-- Phase 1 (Identity) schema only. No JPA entities yet, so Hibernate validate has
-- nothing to compare against until you add them in Phase 1.

CREATE TABLE users (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email         text        NOT NULL,
    password_hash text        NOT NULL,
    full_name     text        NOT NULL,
    country       char(2)     NOT NULL,
    status        text        NOT NULL DEFAULT 'active'
                              CHECK (status IN ('active', 'locked', 'closed')),
    created_at    timestamptz NOT NULL DEFAULT now(),
    updated_at    timestamptz NOT NULL DEFAULT now()
);

-- case-insensitive uniqueness without needing citext
CREATE UNIQUE INDEX users_email_lower_uidx ON users (lower(email));

CREATE TRIGGER users_set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE user_sessions (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    refresh_hash  text        NOT NULL,
    expires_at    timestamptz NOT NULL,
    revoked_at    timestamptz,
    created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX user_sessions_user_idx ON user_sessions (user_id, expires_at DESC);

-- Phase 2 (KYC) schema only. Documents store a storage reference and a hash,
-- never the document bytes.

CREATE TABLE kyc_checks (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id      uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    provider     text        NOT NULL DEFAULT 'sandbox',
    status       text        NOT NULL DEFAULT 'pending'
                             CHECK (status IN ('pending', 'in_review', 'approved', 'rejected', 'expired')),
    reason       text,
    submitted_at timestamptz NOT NULL DEFAULT now(),
    decided_at   timestamptz,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX kyc_checks_user_idx ON kyc_checks (user_id, submitted_at DESC);

CREATE TRIGGER kyc_checks_set_updated_at
    BEFORE UPDATE ON kyc_checks
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE kyc_documents (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    kyc_check_id uuid        NOT NULL REFERENCES kyc_checks(id) ON DELETE CASCADE,
    doc_type     text        NOT NULL CHECK (doc_type IN ('passport', 'national_id', 'drivers_licence', 'proof_of_address')),
    storage_ref  text        NOT NULL,
    sha256       char(64)    NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX kyc_documents_check_idx ON kyc_documents (kyc_check_id);

-- Phase 2 (beneficiaries). account_ref is a sandbox placeholder -- never a real
-- account number.

CREATE TABLE beneficiaries (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id      uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    display_name text        NOT NULL,
    account_ref  text        NOT NULL,
    country      char(2)     NOT NULL,
    currency     char(3)     NOT NULL,
    status       text        NOT NULL DEFAULT 'active'
                             CHECK (status IN ('active', 'archived', 'blocked')),
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, account_ref, currency)
);

CREATE INDEX beneficiaries_user_idx ON beneficiaries (user_id, status);

CREATE TRIGGER beneficiaries_set_updated_at
    BEFORE UPDATE ON beneficiaries
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Phase 3 (FX). minor_units drives formatting everywhere: 2 for USD, 0 for JPY.

CREATE TABLE currencies (
    code        char(3)     PRIMARY KEY,
    name        text        NOT NULL,
    minor_units smallint    NOT NULL CHECK (minor_units BETWEEN 0 AND 3),
    enabled     boolean     NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO currencies (code, name, minor_units) VALUES
    ('USD', 'US dollar',       2),
    ('EUR', 'Euro',            2),
    ('GBP', 'Pound sterling',  2),
    ('INR', 'Indian rupee',    2),
    ('AED', 'UAE dirham',      2),
    ('PHP', 'Philippine peso', 2),
    ('NGN', 'Nigerian naira',  2)
ON CONFLICT (code) DO NOTHING;

CREATE TABLE fx_rates (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    base_code  char(3)        NOT NULL REFERENCES currencies(code),
    quote_code char(3)        NOT NULL REFERENCES currencies(code),
    rate       numeric(24,10) NOT NULL CHECK (rate > 0),
    source     text           NOT NULL,
    fetched_at timestamptz    NOT NULL DEFAULT now(),
    created_at timestamptz    NOT NULL DEFAULT now(),
    CHECK (base_code <> quote_code)
);

CREATE INDEX fx_rates_lookup_idx ON fx_rates (base_code, quote_code, fetched_at DESC);

-- Phase 3 (fees). Charge = fixed_minor + percent_bps of the send amount.
-- 1 basis point = 0.01%.

CREATE TABLE fee_rules (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    corridor_from  char(2)     NOT NULL,
    corridor_to    char(2)     NOT NULL,
    currency       char(3)     NOT NULL,
    fixed_minor    bigint      NOT NULL DEFAULT 0 CHECK (fixed_minor >= 0),
    percent_bps    integer     NOT NULL DEFAULT 0 CHECK (percent_bps BETWEEN 0 AND 5000),
    active         boolean     NOT NULL DEFAULT true,
    effective_from timestamptz NOT NULL DEFAULT now(),
    created_at     timestamptz NOT NULL DEFAULT now(),
    CHECK (corridor_from <> corridor_to)
);

CREATE UNIQUE INDEX fee_rules_effective_uidx
    ON fee_rules (corridor_from, corridor_to, currency, effective_from);

CREATE INDEX fee_rules_lookup_idx
    ON fee_rules (corridor_from, corridor_to, currency, effective_from DESC)
    WHERE active;

-- Phase 4 (transfers + provider adapter). Status changes are append-only: every
-- transition also writes a transfer_events row.

CREATE TABLE transfers (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    public_ref           text        NOT NULL UNIQUE,
    user_id              uuid        NOT NULL REFERENCES users(id),
    beneficiary_id       uuid        NOT NULL REFERENCES beneficiaries(id),
    send_currency        char(3)     NOT NULL REFERENCES currencies(code),
    receive_currency     char(3)     NOT NULL REFERENCES currencies(code),
    send_amount_minor    bigint      NOT NULL CHECK (send_amount_minor > 0),
    fee_minor            bigint      NOT NULL DEFAULT 0 CHECK (fee_minor >= 0),
    fx_rate              numeric(24,10) CHECK (fx_rate IS NULL OR fx_rate > 0),
    receive_amount_minor bigint      CHECK (receive_amount_minor IS NULL OR receive_amount_minor >= 0),
    status               text        NOT NULL DEFAULT 'created'
                                     CHECK (status IN ('created', 'quoted', 'awaiting_funds', 'funded',
                                                       'submitted', 'paid_out', 'failed', 'cancelled', 'refunded')),
    provider             text        NOT NULL DEFAULT 'sandbox',
    provider_ref         text,
    idempotency_key      text,
    risk_score           numeric(5,4) CHECK (risk_score IS NULL OR (risk_score >= 0 AND risk_score <= 1)),
    failure_reason       text,
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX transfers_user_idx ON transfers (user_id, created_at DESC);
CREATE INDEX transfers_status_idx ON transfers (status, created_at DESC);
CREATE INDEX transfers_provider_ref_idx ON transfers (provider, provider_ref);

-- Retrying a POST with the same key must never create a second transfer.
CREATE UNIQUE INDEX transfers_idempotency_uidx
    ON transfers (user_id, idempotency_key)
    WHERE idempotency_key IS NOT NULL;

CREATE TRIGGER transfers_set_updated_at
    BEFORE UPDATE ON transfers
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE transfer_events (
    id          bigserial   PRIMARY KEY,
    transfer_id uuid        NOT NULL REFERENCES transfers(id) ON DELETE CASCADE,
    event_type  text        NOT NULL,
    payload     jsonb       NOT NULL DEFAULT '{}'::jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX transfer_events_transfer_idx ON transfer_events (transfer_id, id);