schema.sql

-- OverSync coordinator persistence schema.
-- This DB is treated as a CACHE — the source of truth is always the
-- on-chain state of the HTLC contracts on both chains. The coordinator
-- can rebuild this DB by re-reading events from both chains.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS orders (
    id                    INTEGER PRIMARY KEY AUTOINCREMENT,
    -- Globally unique id used by clients. We use a UUID-shaped hex
    -- string generated by the coordinator at announce time.
    public_id             TEXT    NOT NULL UNIQUE,

    direction             TEXT    NOT NULL CHECK (direction IN ('eth_to_xlm', 'xlm_to_eth')),
    status                TEXT    NOT NULL CHECK (status IN ('announced', 'src_locked', 'dst_locked', 'secret_revealed', 'completed', 'refunded', 'failed', 'expired')),

    -- Cross-chain link.
    hashlock              TEXT    NOT NULL,    -- 0x-prefixed 32-byte hex.

    -- Source chain (where the user locks first).
    src_chain             TEXT    NOT NULL CHECK (src_chain IN ('ethereum', 'stellar')),
    src_address           TEXT    NOT NULL,    -- user's address on src.
    src_asset             TEXT    NOT NULL,    -- "native" or contract id.
    src_amount            TEXT    NOT NULL,    -- decimal string (atomic units).
    src_safety_deposit    TEXT    NOT NULL,    -- decimal string.
    src_order_id          TEXT,                -- on-chain order id once locked.
    src_lock_tx           TEXT,
    src_lock_block        INTEGER,
    src_timelock          INTEGER,             -- unix seconds (absolute).

    -- Destination chain (where the user wants to receive).
    dst_chain             TEXT    NOT NULL CHECK (dst_chain IN ('ethereum', 'stellar')),
    dst_address           TEXT    NOT NULL,
    dst_asset             TEXT    NOT NULL,
    dst_amount            TEXT    NOT NULL,
    dst_order_id          TEXT,
    dst_lock_tx           TEXT,
    dst_lock_block        INTEGER,
    dst_timelock          INTEGER,

    -- Secret tracking. preimage stays NULL until revealed.
    preimage              TEXT,
    secret_revealed_tx    TEXT,

    -- Resolver that filled the destination side (if any).
    resolver_address      TEXT,

    -- Opt-in demo-fixture flag. Issue #161 requires a path to reset
    -- these rows; see OrdersRepository.removeFixtures(). Always 0
    -- unless COORDINATOR_DEMO_FIXTURES=true has seeded fixtures.
    fixture               INTEGER NOT NULL DEFAULT 0 CHECK (fixture IN (0, 1)),

    created_at            INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER)),
    updated_at            INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
);

CREATE INDEX IF NOT EXISTS idx_orders_hashlock         ON orders (hashlock);
CREATE INDEX IF NOT EXISTS idx_orders_src_address      ON orders (src_address);
CREATE INDEX IF NOT EXISTS idx_orders_dst_address      ON orders (dst_address);
CREATE INDEX IF NOT EXISTS idx_orders_status           ON orders (status);
CREATE INDEX IF NOT EXISTS idx_orders_src_order_id     ON orders (src_chain, src_order_id);
CREATE INDEX IF NOT EXISTS idx_orders_dst_order_id     ON orders (dst_chain, dst_order_id);

CREATE TABLE IF NOT EXISTS order_events (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id      INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    event_type    TEXT    NOT NULL,
    payload_json  TEXT    NOT NULL,
    created_at    INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
);

CREATE INDEX IF NOT EXISTS idx_order_events_order ON order_events (order_id, created_at);

CREATE TABLE IF NOT EXISTS resolver_heartbeats (
    address     TEXT PRIMARY KEY,
    chain       TEXT NOT NULL CHECK (chain IN ('ethereum', 'stellar')),
    last_seen   INTEGER NOT NULL
);

-- Last chain position (Ethereum block / Soroban ledger) the coordinator
-- has fully processed, per chain. `network_id` pins the cursor to a
-- specific network so a cursor from testnet can never be replayed against
-- mainnet (startup rejects a mismatch).
CREATE TABLE IF NOT EXISTS chain_cursors (
    chain       TEXT    PRIMARY KEY CHECK (chain IN ('ethereum', 'soroban')),
    network_id  TEXT    NOT NULL,
    position    INTEGER NOT NULL,
    cursor      TEXT,
    updated_at  INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
);

-- Chain events already applied to order state. Redelivered events (after a
-- restart or a crash between detecting an event and persisting the cursor)
-- are skipped, so a claim or refund is never applied twice.
CREATE TABLE IF NOT EXISTS processed_chain_events (
    event_key   TEXT    PRIMARY KEY,
    chain       TEXT    NOT NULL,
    kind        TEXT    NOT NULL,
    position    INTEGER NOT NULL,
    created_at  INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
);