GL/infrastructure/postgres/migrations/000001_init.up.sql

CREATE TABLE IF NOT EXISTS ledger_schema_migrations (
    version bigint PRIMARY KEY,
    applied_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE ledger_accounts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    class text NOT NULL CHECK (class IN (
        'USER_AVAILABLE',
        'USER_FROZEN',
        'EXTERNAL_BLOCKCHAIN',
        'TREASURY',
        'MARKET_CLEARING',
        'IPG_CLEARING',
        'COMMISSION_REVENUE'
    )),
    owner_type text NOT NULL DEFAULT '',
    owner_id text NOT NULL DEFAULT '',
    asset_id bigint NOT NULL CHECK (asset_id > 0),
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    UNIQUE (class, owner_type, owner_id, asset_id),
    UNIQUE (id, asset_id),
    CHECK (
        class NOT IN ('USER_AVAILABLE', 'USER_FROZEN')
        OR (owner_type <> '' AND owner_id <> '')
    )
);

CREATE TABLE journals (
    id uuid PRIMARY KEY,
    source_service text NOT NULL CHECK (source_service <> ''),
    idempotency_key text NOT NULL UNIQUE CHECK (idempotency_key <> ''),
    source_transaction_id text NOT NULL CHECK (source_transaction_id <> ''),
    tracking_code text NOT NULL DEFAULT '',
    effect_kind text NOT NULL CHECK (effect_kind <> ''),
    event_version integer NOT NULL CHECK (event_version > 0),
    reversal_of_journal_id uuid REFERENCES journals (id),
    occurred_at timestamptz NOT NULL,
    recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    sealed_at timestamptz,
    correlation_id text NOT NULL DEFAULT '',
    actor_id text NOT NULL DEFAULT '',
    blockchain_network text NOT NULL DEFAULT '',
    blockchain_transaction_hash text NOT NULL DEFAULT '',
    blockchain_ledger_sequence text NOT NULL DEFAULT '',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(metadata) = 'object'),
    payload_hash char(64) NOT NULL CHECK (payload_hash ~ '^[0-9a-f]{64}$'),
    CHECK (reversal_of_journal_id IS NULL OR reversal_of_journal_id <> id)
);

CREATE INDEX journals_source_transaction_idx
    ON journals (source_service, source_transaction_id);

CREATE INDEX journals_recorded_at_idx ON journals (recorded_at, id);

CREATE UNIQUE INDEX journals_one_reversal_idx
    ON journals (reversal_of_journal_id)
    WHERE reversal_of_journal_id IS NOT NULL;

CREATE TABLE journal_entries (
    journal_id uuid NOT NULL REFERENCES journals (id),
    line_number integer NOT NULL CHECK (line_number > 0),
    account_id bigint NOT NULL,
    asset_id bigint NOT NULL CHECK (asset_id > 0),
    amount numeric(38, 18) NOT NULL CHECK (amount <> 0),
    description text NOT NULL DEFAULT '',
    PRIMARY KEY (journal_id, line_number),
    FOREIGN KEY (account_id, asset_id) REFERENCES ledger_accounts (id, asset_id)
);

CREATE INDEX journal_entries_account_idx
    ON journal_entries (account_id, journal_id, line_number);

CREATE TABLE transaction_events (
    id uuid PRIMARY KEY,
    source_service text NOT NULL CHECK (source_service <> ''),
    idempotency_key text NOT NULL UNIQUE CHECK (idempotency_key <> ''),
    source_transaction_id text NOT NULL CHECK (source_transaction_id <> ''),
    tracking_code text NOT NULL DEFAULT '',
    event_version integer NOT NULL CHECK (event_version > 0),
    state text NOT NULL CHECK (state IN (
        'CREATED',
        'PENDING_TRANSACTION',
        'PENDING_ADMIN',
        'SUCCESSFUL',
        'FAILED',
        'SUSPENDED'
    )),
    error_code text NOT NULL DEFAULT '',
    error_message text NOT NULL DEFAULT '',
    occurred_at timestamptz NOT NULL,
    recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    correlation_id text NOT NULL DEFAULT '',
    actor_id text NOT NULL DEFAULT '',
    blockchain_network text NOT NULL DEFAULT '',
    blockchain_transaction_hash text NOT NULL DEFAULT '',
    blockchain_ledger_sequence text NOT NULL DEFAULT '',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(metadata) = 'object'),
    payload_hash char(64) NOT NULL CHECK (payload_hash ~ '^[0-9a-f]{64}$')
);

CREATE INDEX transaction_events_source_idx
    ON transaction_events (source_service, source_transaction_id, event_version);

CREATE FUNCTION reject_ledger_mutation() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION '% is append-only', TG_TABLE_NAME
        USING ERRCODE = '55000';
END;
$$;

CREATE FUNCTION guard_journal_seal() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.sealed_at IS NOT NULL
        OR NEW.sealed_at IS NULL
        OR (to_jsonb(NEW) - 'sealed_at') IS DISTINCT FROM (to_jsonb(OLD) - 'sealed_at') THEN
        RAISE EXCEPTION 'journals are immutable except for initial sealing'
            USING ERRCODE = '55000';
    END IF;

    IF (SELECT count(*) FROM journal_entries WHERE journal_id = NEW.id) < 2 THEN
        RAISE EXCEPTION 'journal requires at least two entries'
            USING ERRCODE = '23514';
    END IF;

    IF EXISTS (
        SELECT asset_id
        FROM journal_entries
        WHERE journal_id = NEW.id
        GROUP BY asset_id
        HAVING sum(amount) <> 0
    ) THEN
        RAISE EXCEPTION 'journal is not balanced per asset'
            USING ERRCODE = '23514';
    END IF;

    RETURN NEW;
END;
$$;

CREATE FUNCTION guard_entry_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    IF (SELECT sealed_at IS NOT NULL FROM journals WHERE id = NEW.journal_id) THEN
        RAISE EXCEPTION 'cannot append to a sealed journal'
            USING ERRCODE = '55000';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER journals_guard_update
BEFORE UPDATE ON journals
FOR EACH ROW EXECUTE FUNCTION guard_journal_seal();

CREATE TRIGGER journals_reject_delete
BEFORE DELETE ON journals
FOR EACH ROW EXECUTE FUNCTION reject_ledger_mutation();

CREATE TRIGGER entries_guard_insert
BEFORE INSERT ON journal_entries
FOR EACH ROW EXECUTE FUNCTION guard_entry_insert();

CREATE TRIGGER entries_reject_update_or_delete
BEFORE UPDATE OR DELETE ON journal_entries
FOR EACH ROW EXECUTE FUNCTION reject_ledger_mutation();

CREATE TRIGGER accounts_reject_update_or_delete
BEFORE UPDATE OR DELETE ON ledger_accounts
FOR EACH ROW EXECUTE FUNCTION reject_ledger_mutation();

CREATE TRIGGER transaction_events_reject_update_or_delete
BEFORE UPDATE OR DELETE ON transaction_events
FOR EACH ROW EXECUTE FUNCTION reject_ledger_mutation();