feat(gl): add immutable postgres ledger storage

2026-08-14 19:00:45 +03:30
parent b1e0b01598
commit 0b3eaa0c3d
14 changed files with 1139 additions and 1 deletions
@@ -0,0 +1,172 @@
+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 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();