XCORE Claims via an Off-Chain Database Layer

Technical design & implementation guide: XEN (Ethereum) snapshot → authenticated off-chain claim → XCORE payout

Conventions

• <<PLACEHOLDER>>: a value the owner must decide or fill in. Never ship one.
• EXAMPLE ONLY: illustrative numbers or formulas. They are not a decision.
• ASSUMPTION: something this document assumes. Confirm it before launch.
• VERIFY: a fact to check against chain data or primary sources before you rely on it.
• No real secrets, keys or live endpoints appear here. All env vars are listed by name only.

Table of Contents

1. Overview & Architecture
2. Snapshot Production
3. Eligibility & Allocation Rules
4. Off-Chain Database Design
5. Wallet Authentication
6. Claim API
7. Payout Fulfillment Options
8. Webpage UX Flow
9. Security Threat Model & Controls
10. Operations
11. Testing & Acceptance Criteria
12. Build Sequence & Open Decisions
13. Alternatives Considered
14. Appendix: Assumptions Register & Glossary

1. Overview & Architecture

1.1 What this system does

1. Snapshot. Records every Ethereum address that held a non-zero XEN balance at one fixed snapshot block (the first block at or after 2026-10-01 00:00:00 UTC, per the ASSUMPTION above).
2. Allocation. Turns each balance into an XCORE allocation using a pluggable, versioned formula. The result is a fixed, hashed allocation table.
3. Database. Loads the table into an off-chain Postgres database, which is the system of record for who can claim and how much.
4. Claim webpage. A holder connects their Ethereum wallet and proves ownership with a SIWE / EIP-4361 signed message. They then bind a destination address on the payout chain (on Solana, signed with Phantom or the X1 wallet) and submit a claim.
5. Payout. A fulfillment worker pays XCORE to the bound destination. It is either a rate-limited backend hot wallet or a batched multisig export. Each claim is paid at most once, enforced by DB constraints plus an on-chain idempotency reference.
6. Audit. Every state change goes to an append-only, hash-chained audit log. A daily reconciliation job compares DB "paid" records with on-chain transfers.

The design is off-chain trust-based: users trust the operator to apply the published snapshot and formula honestly and to pay out. Transparency comes from publishing the snapshot artifacts, their SHA-256 manifest and the allocation formula, so anyone can recompute them. §13 compares this with an on-chain Merkle distributor.

1.2 Architecture diagram

flowchart LR
  subgraph ETH["Ethereum mainnet (read-only)"]
    XEN["XEN ERC-20<br/>0x0645...6Fb8"]
    ARCH["Archive node(s)<br/>2 independent providers"]
  end

  subgraph SNAP["Snapshot pipeline (offline, deterministic)"]
    BLK["Block selector<br/>first block ≥ T_snapshot"]
    SCAN["Transfer log scanner<br/>checkpointed"]
    REC["Balance reconstruction<br/>+ reconciliation"]
    ALLOC["Allocation engine<br/>formula vN"]
    MAN["Snapshot manifest<br/>SHA-256 + signature"]
  end

  subgraph BACK["Backend (Vercel functions + Postgres/Neon)"]
    ETL["ETL loader<br/>idempotent import"]
    DB[("Postgres<br/>allocations, claims,<br/>nonces, jobs, audit")]
    API["Claim API<br/>/auth/* /claims/*"]
    Q["Payout queue<br/>payout_jobs table"]
    W["Fulfillment worker<br/>(hot wallet signer<br/>or batch export)"]
    RECON["Daily reconciliation<br/>DB vs chain"]
    AUD["Append-only audit log<br/>hash-chained"]
  end

  subgraph WEB["Claim webpage"]
    UI["/claim UI"]
    EW["Ethereum wallet<br/>(SIWE sign)"]
    SW["Solana wallet<br/>Phantom / X1 (bind sign)"]
  end

  subgraph PAY["Payout chain (e.g. Solana)"]
    XC["XCORE token / mint"]
  end

  XEN --> ARCH --> BLK --> SCAN --> REC --> ALLOC --> MAN
  MAN --> ETL --> DB
  UI <--> API
  EW -. signature .-> UI
  SW -. signature .-> UI
  API <--> DB
  API --> Q
  Q --> W --> XC
  W --> DB
  XC --> RECON --> DB
  DB --> AUD
  API --> AUD
  W --> AUD

1.3 Components and their trust boundaries

1.4 Non-goals

• No smart contract changes to XEN. XEN is only read.
• No sybil resistance beyond what the snapshot gives (see §3.6).
• No custody of user funds, and no fiat.
• No PII collection (§9.8).

2. Snapshot Production

2.1 Token facts (verify before running)

2.2 Snapshot timestamp and block selection

ASSUMPTION: "Oct 1st" means 2026-10-01 00:00:00 UTC, Unix timestamp T = 1790812800. (Check it: date -u -d '2026-10-01T00:00:00Z' +%s → 1790812800.)

⚠️ Owner action required: confirm the year (2026), timestamp and timezone, then freeze them together with the resulting block number and block hash in snapshot_manifest before any public announcement or launch. If they change after publication, every allocation is invalid.

Rule: the snapshot block B is the first block whose timestamp ≥ T. Balances are taken after all transactions in block B are applied (end-of-block state). That is the state balanceOf(addr, blockTag=B) returns.

Alternative rule: last block with timestamp < T. Pick one and write it in the manifest field block_rule. This document uses first_block_gte_timestamp.

Algorithm (binary search on an archive node):

def find_first_block_gte(rpc, T):
    lo, hi = DEPLOY_BLOCK, rpc.eth_blockNumber()
    assert rpc.get_block(hi).timestamp >= T, "snapshot time not reached yet"
    while lo < hi:
        mid = (lo + hi) // 2
        if rpc.get_block(mid).timestamp >= T:
            hi = mid
        else:
            lo = mid + 1
    B = lo
    assert rpc.get_block(B).timestamp >= T
    assert rpc.get_block(B - 1).timestamp < T
    return B

Recording and verification:

1. Run the search against two independent archive providers (e.g. a self-hosted Erigon/Reth node plus one hosted provider). Both must return the same B and the same block_hash.
2. Wait for finality: only take the snapshot once B is finalized (eth_getBlockByNumber("finalized") ≥ B; post-Merge this is usually about 2 epochs, ~13 minutes). In practice the snapshot runs days later, so this is automatic, but the check still runs.
3. Record block_number, block_hash, block_timestamp, parent_hash and block_rule in the manifest.
4. Anyone can verify with a public explorer or any archive RPC: block B has the recorded hash, ts(B) ≥ T, and ts(B-1) < T.

2.3 Holder enumeration by Transfer-log scan

ERC-20 balances cannot be enumerated on-chain, so the holder set is rebuilt from Transfer events between DEPLOY_BLOCK and B inclusive.

Scan procedure:

CHUNK = 2_000          # adapt per provider log limits; shrink on "too many results"
state = load_checkpoint() or {"next_block": DEPLOY_BLOCK, "balances": {}, "log_count": 0}

while state["next_block"] <= B:
    start = state["next_block"]
    end = min(start + CHUNK - 1, B)
    logs = rpc.eth_getLogs(address=XEN, topics=[TRANSFER_TOPIC],
                           fromBlock=start, toBlock=end)
    logs.sort(key=lambda l: (l.blockNumber, l.logIndex))       # deterministic ordering
    for l in logs:
        if l.removed: raise ReorgError(l)                        # should never happen below finality
        frm = to_checksum("0x" + l.topics[1][-40:])
        to  = to_checksum("0x" + l.topics[2][-40:])
        val = int(l.data, 16)
        if frm != ZERO: state["balances"][frm] = state["balances"].get(frm, 0) - val
        if to  != ZERO: state["balances"][to]  = state["balances"].get(to, 0) + val
        append_raw_log(l)                                        # raw_logs.jsonl, for replay
    state["log_count"] += len(logs)
    state["next_block"] = end + 1
    save_checkpoint(state, chunk_hash=sha256(logs_serialized))  # atomic write: tmp + rename

Implementation notes:

2.4 Special address handling

Classification step: for every address with balance > 0, call eth_getCode(addr, B) and store is_contract. Tag known addresses (pools, CEX, bridges) from a reviewed, versioned labels.csv whose hash goes in the manifest.

2.5 Reconciliation (mandatory before you publish)

If XEN had any non-standard balance change without a Transfer event (it shouldn't; VERIFY in the source), the supply check fails. Never "fix" this by adjusting numbers. Investigate.

2.6 Output artifacts

All files are UTF-8 with LF line endings, sorted by address ascending (lowercase hex compare), no trailing whitespace, and decimal integers in base units (wei-like, 18 decimals).

snapshot_manifest.json (example structure; values are placeholders):

{
  "manifest_version": 1,
  "project": "XCORE claim from XEN snapshot",
  "source_chain_id": 1,
  "token_address": "0x06450dEe7FD2Fb8E39061434BAbCFC05599a6Fb8",
  "token_decimals": 18,
  "deploy_block": "<<VERIFY_DEPLOY_BLOCK>>",
  "snapshot_time_utc": "2026-10-01T00:00:00Z",
  "snapshot_time_unix": 1790812800,
  "block_rule": "first_block_gte_timestamp",
  "block_number": "<<FROZEN_BLOCK_NUMBER>>",
  "block_hash": "<<FROZEN_BLOCK_HASH>>",
  "block_timestamp": "<<TS_OF_BLOCK>>",
  "total_supply_raw_at_block": "<<FROM_totalSupply()>>",
  "holder_count_nonzero": "<<N>>",
  "excluded_count": "<<N>>",
  "eligible_count": "<<N>>",
  "allocation": {
    "formula_id": "<<e.g. pro_rata_capped>>",
    "formula_version": "<<semver>>",
    "params": { "pool_raw": "<<POOL>>", "min_eligible_raw": "<<DUST>>", "cap_raw": "<<CAP>>", "xcore_decimals": "<<9_OR_6_VERIFY>>" },
    "sum_allocation_raw": "<<SUM>>",
    "remainder_raw": "<<REMAINDER>>"
  },
  "providers": ["<<provider A name>>", "<<provider B name>>"],
  "tooling": { "repo_commit": "<<git sha>>", "runtime": "<<python 3.x / node 2x>>" },
  "files": {
    "holders_all.csv": "<<sha256>>",
    "excluded_addresses.csv": "<<sha256>>",
    "allocations.csv": "<<sha256>>",
    "labels.csv": "<<sha256>>",
    "raw_logs.jsonl.zst": "<<sha256>>",
    "reconciliation_report.json": "<<sha256>>"
  },
  "manifest_sha256": "computed over canonical JSON with this field omitted",
  "frozen_at_utc": "<<ISO8601>>",
  "signers": ["<<publishing key fingerprint / address>>"]
}

Canonicalization: compute manifest_sha256 over RFC 8785 JCS (JSON Canonicalization Scheme) serialization with manifest_sha256 removed. The DB stores this hash (§4), and every API allocation response returns it so clients can check which snapshot they're looking at.

3. Eligibility & Allocation Rules

3.1 Principles

• Deterministic. Same inputs and formula version give byte-identical allocations.csv.
• Integer-only. All math in base units with explicit rounding (floor), plus a defined policy for the remainder.
• Versioned and pluggable. formula_id + formula_version + params live in the manifest. Changing any of them means a new manifest, never a change in place.
• Published before claims open. Users can recompute their own allocation.

3.2 Pluggable formula interface

interface AllocationFormula {
  id: string;                 // e.g. "pro_rata_capped"
  version: string;            // semver
  // Input: eligible holders (after exclusions + dust filter), params.
  // Output: allocation per address in XCORE base units; MUST satisfy invariants in §3.5.
  allocate(holders: Array<{ address: string; eligibleBalanceRaw: bigint }>,
           params: Record<string, string>): Map<string, bigint>;
}

3.3 Example formulas (EXAMPLE ONLY, not decisions)

⚠️ EXAMPLE ONLY. All numbers below are made up to show the arithmetic. Pool size, caps, thresholds and the formula itself are open owner decisions (§12.3).

(A) Pure pro-rata. alloc_i = floor(POOL × b_i / Σb)

(B) Pro-rata with per-wallet cap (iterative water-filling). Allocate pro-rata. Any wallet over CAP is set to CAP. Redistribute the excess pro-rata among the uncapped wallets and repeat until nothing is over the cap.

(C) Flat + pro-rata hybrid. alloc_i = FLAT + floor((POOL − N×FLAT) × b_i / Σb)

(D) Tiered / sqrt-weighted (reduces whale concentration): w_i = isqrt(b_i), alloc_i = floor(POOL × w_i / Σw)

Worked example (EXAMPLE ONLY), formula (B): Settings: POOL = 1,000,000 XCORE, CAP = 400,000 XCORE, dust threshold 1,000 XEN. Four holders (balances in whole XEN for readability; the real computation uses raw units):

This example shows why caps need an iterative algorithm and why you have to define what happens if N_eligible × CAP < POOL: the pool can't be fully distributed, and the excess goes to remainder_raw and is handled by the unclaimed/remainder policy.

Worked example (EXAMPLE ONLY), formula (A) with rounding: POOL = 100 base units, balances [1, 1, 1] → each gets floor(100/3) = 33. Sum = 99, remainder_raw = 1.

3.4 Thresholds, caps, rounding

3.5 Pool accounting invariant

These must always hold, and are checked in the ETL, in a DB constraint/trigger, and in reconciliation:

(I1) Σ allocation_raw (all eligible rows)            + remainder_raw          == POOL_raw
(I2) Σ paid_raw + Σ in_flight_raw + Σ unclaimed_raw                           == Σ allocation_raw
(I3) for every claim c: c.amount_raw == allocations[c.eth_address].allocation_raw   (no partial/custom amounts)
(I4) count(claims per eth_address) <= 1
(I5) on-chain XCORE sent by payout wallet(s) tagged with claim refs == Σ paid_raw   (daily recon)
(I6) payout wallet balance + Σ paid_raw (+ fees paid in XCORE, if any)  == initial funding (+ top-ups logged in admin_actions)

3.6 Sybil / multi-wallet note

A balance snapshot gives no sybil resistance:

• One person with 50 wallets gets 50 allocations. Under pure pro-rata that's neutral, since splitting doesn't change their total share. Splitting does game per-wallet caps, flat components (formula C), dust thresholds (more wallets just over the line) and sqrt weighting (formula D).
• Anyone who expected an airdrop could have split balances before Oct 1. After the snapshot, splitting does nothing.
• Mitigations, all optional, each with a cost: favor pro-rata-style formulas that are split-neutral; avoid flat components; cluster analysis (funding-source heuristics) applied before freezing, which is subjective and needs a published rule; KYC (out of scope, conflicts with "no PII").
• Document clearly on the claim page: "Allocation is per Ethereum address at the snapshot block."

4. Off-Chain Database Design

4.1 Platform and roles

• Postgres 15+ (Neon fits the prior Vercel stack; any managed Postgres works).
• Separate DB roles, each with least privilege:

No role has UPDATE/DELETE on allocations, snapshot_manifest or audit_events after load (§4.6).

4.2 Full SQL schema

-- =========================================================
-- Extensions & types
-- =========================================================
CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- gen_random_uuid(), digest()

CREATE TYPE claim_status AS ENUM (
  'reserved',     -- claim row created, destination bound, awaiting queueing
  'queued',       -- payout job enqueued
  'signed',       -- payout tx signed/submitted (signature recorded), awaiting confirmation
  'paid',         -- tx landed (processed/confirmed)
  'confirmed',    -- tx finalized + reconciled
  'failed',       -- terminal failure requiring admin action (no funds moved, verified)
  'voided'        -- admin-voided (e.g. policy exclusion, fraud); allocation not payable
);
-- Note: "unclaimed" is not a claim status; it is the absence of a claims row for an allocation.

CREATE TYPE job_status AS ENUM ('pending','leased','submitted','succeeded','retry_wait','dead');

CREATE TYPE dest_chain AS ENUM ('solana','x1','evm');   -- extend as needed; chain-agnostic

-- =========================================================
-- Snapshot manifest (immutable after insert)
-- =========================================================
CREATE TABLE snapshot_manifest (
  snapshot_id          SMALLINT PRIMARY KEY,                 -- 1 for launch; new ids for any re-issue
  manifest_sha256      CHAR(64) NOT NULL UNIQUE,
  manifest_json        JSONB    NOT NULL,                    -- full canonical manifest
  source_chain_id      INTEGER  NOT NULL CHECK (source_chain_id = 1),
  token_address        CHAR(42) NOT NULL,
  block_number         BIGINT   NOT NULL,
  block_hash           CHAR(66) NOT NULL,
  snapshot_time_utc    TIMESTAMPTZ NOT NULL,
  formula_id           TEXT     NOT NULL,
  formula_version      TEXT     NOT NULL,
  pool_raw             NUMERIC(78,0) NOT NULL CHECK (pool_raw > 0),
  sum_allocation_raw   NUMERIC(78,0) NOT NULL,
  remainder_raw        NUMERIC(78,0) NOT NULL,
  eligible_count       INTEGER  NOT NULL,
  signature            TEXT     NOT NULL,                    -- detached sig over manifest_sha256
  is_active            BOOLEAN  NOT NULL DEFAULT FALSE,      -- exactly one active
  loaded_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (sum_allocation_raw + remainder_raw = pool_raw)      -- invariant I1
);
CREATE UNIQUE INDEX one_active_snapshot ON snapshot_manifest ((is_active)) WHERE is_active;

-- =========================================================
-- Allocations (one row per eligible Ethereum address; immutable)
-- =========================================================
CREATE TABLE allocations (
  snapshot_id          SMALLINT NOT NULL REFERENCES snapshot_manifest(snapshot_id),
  eth_address          CHAR(42) NOT NULL,      -- stored LOWERCASE 0x-hex; checksum applied at display
  balance_raw          NUMERIC(78,0) NOT NULL CHECK (balance_raw > 0),   -- XEN at block B
  eligible_balance_raw NUMERIC(78,0) NOT NULL,
  allocation_raw       NUMERIC(78,0) NOT NULL CHECK (allocation_raw > 0), -- XCORE base units
  is_contract          BOOLEAN  NOT NULL,
  label                TEXT,                   -- e.g. 'cex_suspect', null for normal
  flags                TEXT[]   NOT NULL DEFAULT '{}',   -- e.g. {'requires_manual','eip1271'}
  row_sha256           CHAR(64) NOT NULL,      -- sha256 of canonical CSV line, for tamper checks
  PRIMARY KEY (snapshot_id, eth_address),
  CHECK (eth_address ~ '^0x[0-9a-f]{40}$')
);

-- Excluded addresses kept for transparency/support answers
CREATE TABLE excluded_addresses (
  snapshot_id  SMALLINT NOT NULL REFERENCES snapshot_manifest(snapshot_id),
  eth_address  CHAR(42) NOT NULL CHECK (eth_address ~ '^0x[0-9a-f]{40}$'),
  balance_raw  NUMERIC(78,0) NOT NULL,
  reason       TEXT NOT NULL,      -- 'burn','self','lp_pool','dust','policy_cex','contract_policy'
  PRIMARY KEY (snapshot_id, eth_address)
);

-- =========================================================
-- Auth nonces (SIWE challenges)
-- =========================================================
CREATE TABLE auth_nonces (
  nonce          TEXT PRIMARY KEY,                 -- >= 128-bit random, base58/alnum, >= 16 chars
  eth_address    CHAR(42),                         -- optional pre-binding (lowercase)
  purpose        TEXT NOT NULL CHECK (purpose IN ('siwe_eth','bind_dest')),
  issued_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at     TIMESTAMPTZ NOT NULL,
  consumed_at    TIMESTAMPTZ,                      -- set exactly once; replay => reject
  ip_hash        CHAR(64),                         -- salted hash, for rate limiting only
  CHECK (expires_at > issued_at)
);
CREATE INDEX auth_nonces_expiry ON auth_nonces (expires_at) WHERE consumed_at IS NULL;

-- =========================================================
-- Sessions (server-side record; cookie carries opaque id or JWT with jti)
-- =========================================================
CREATE TABLE sessions (
  session_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),   -- = JWT jti
  eth_address    CHAR(42) NOT NULL,
  siwe_message_sha256 CHAR(64) NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at     TIMESTAMPTZ NOT NULL,
  revoked_at     TIMESTAMPTZ
);
CREATE INDEX sessions_addr ON sessions (eth_address);

-- =========================================================
-- Destination bindings (Ethereum address -> payout address), proof stored
-- =========================================================
CREATE TABLE destination_bindings (
  binding_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  snapshot_id      SMALLINT NOT NULL,
  eth_address      CHAR(42) NOT NULL,
  dest_chain       dest_chain NOT NULL,
  dest_address     TEXT NOT NULL,                  -- base58 for Solana/X1; validated
  eth_sig          TEXT NOT NULL,                  -- EIP-191 sig over binding message
  dest_sig         TEXT,                           -- ed25519 sig from Phantom/X1 (nullable if not provided)
  binding_message  TEXT NOT NULL,                  -- exact signed text
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  superseded_at    TIMESTAMPTZ,                    -- only allowed before claim is queued
  FOREIGN KEY (snapshot_id, eth_address) REFERENCES allocations(snapshot_id, eth_address)
);
CREATE UNIQUE INDEX one_live_binding ON destination_bindings (snapshot_id, eth_address)
  WHERE superseded_at IS NULL;

-- =========================================================
-- Claims (at most ONE per Ethereum address per snapshot)
-- =========================================================
CREATE TABLE claims (
  claim_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  snapshot_id      SMALLINT NOT NULL,
  eth_address      CHAR(42) NOT NULL,
  amount_raw       NUMERIC(78,0) NOT NULL,         -- copied from allocations; enforced by trigger (I3)
  binding_id       UUID NOT NULL REFERENCES destination_bindings(binding_id),
  dest_chain       dest_chain NOT NULL,
  dest_address     TEXT NOT NULL,                  -- denormalized snapshot of binding at claim time
  status           claim_status NOT NULL DEFAULT 'reserved',
  idempotency_key  TEXT NOT NULL,                  -- client-supplied (UUIDv4) for POST /claims
  payout_ref       CHAR(32) NOT NULL UNIQUE,       -- deterministic ref embedded on-chain (memo)
  payout_tx        TEXT UNIQUE,                    -- chain tx signature/hash once submitted
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  version          INTEGER NOT NULL DEFAULT 0,     -- optimistic locking
  CONSTRAINT one_claim_per_address UNIQUE (snapshot_id, eth_address),      -- invariant I4
  CONSTRAINT idem_unique UNIQUE (eth_address, idempotency_key),
  FOREIGN KEY (snapshot_id, eth_address) REFERENCES allocations(snapshot_id, eth_address)
);
CREATE INDEX claims_status ON claims (status, updated_at);
CREATE INDEX claims_dest ON claims (dest_address);

-- payout_ref = first 32 hex chars of sha256(snapshot_id || ':' || eth_address || ':' || PAYOUT_REF_SALT_PUBLIC)
-- It is deterministic per (snapshot, address), so the same claim can never get two different refs.

-- =========================================================
-- Payout jobs (queue) and attempts
-- =========================================================
CREATE TABLE payout_jobs (
  job_id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  claim_id         UUID NOT NULL UNIQUE REFERENCES claims(claim_id),  -- one job per claim, ever
  status           job_status NOT NULL DEFAULT 'pending',
  attempts         INTEGER NOT NULL DEFAULT 0,
  max_attempts     INTEGER NOT NULL DEFAULT 8,
  next_run_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  lease_owner      TEXT,
  lease_expires_at TIMESTAMPTZ,
  batch_id         UUID,                           -- for option (c) batch exports
  last_error       TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX payout_jobs_ready ON payout_jobs (next_run_at) WHERE status IN ('pending','retry_wait');

CREATE TABLE payout_attempts (
  attempt_id       BIGSERIAL PRIMARY KEY,
  job_id           UUID NOT NULL REFERENCES payout_jobs(job_id),
  attempt_no       INTEGER NOT NULL,
  tx_signature     TEXT,                           -- may be set even if later dropped
  recent_blockhash TEXT,                           -- Solana: lets you tell "expired" from "unknown"
  last_valid_block_height BIGINT,
  outcome          TEXT NOT NULL,                  -- 'submitted','confirmed','expired','error'
  detail           JSONB,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (job_id, attempt_no)
);

-- =========================================================
-- Append-only audit log (hash-chained)
-- =========================================================
CREATE TABLE audit_events (
  seq            BIGSERIAL PRIMARY KEY,
  occurred_at    TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  actor_type     TEXT NOT NULL CHECK (actor_type IN ('user','api','worker','admin','system')),
  actor_id       TEXT NOT NULL,                  -- eth address, worker id, admin id
  event_type     TEXT NOT NULL,                  -- 'auth.verified','claim.reserved','payout.submitted',...
  entity_type    TEXT NOT NULL,
  entity_id      TEXT NOT NULL,
  payload        JSONB NOT NULL,
  prev_hash      CHAR(64) NOT NULL,
  row_hash       CHAR(64) NOT NULL               -- sha256(prev_hash || canonical(payload,…))
);

-- =========================================================
-- Admin actions (also mirrored into audit_events)
-- =========================================================
CREATE TABLE admin_actions (
  action_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  admin_id       TEXT NOT NULL,                  -- IdP subject; MFA asserted
  action         TEXT NOT NULL,                  -- 'pause','unpause','retry_job','void_claim','fund_topup','rebind_dest'
  target_id      TEXT,
  reason         TEXT NOT NULL,
  approved_by    TEXT,                           -- second admin for 2-person actions
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (action NOT IN ('void_claim','rebind_dest','unpause') OR approved_by IS NOT NULL),
  CHECK (approved_by IS NULL OR approved_by <> admin_id)
);

-- =========================================================
-- System flags (pause switch etc.)
-- =========================================================
CREATE TABLE system_flags (
  key        TEXT PRIMARY KEY,                   -- 'claims_paused','payouts_paused','claim_window_end'
  value      TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by TEXT NOT NULL
);

-- =========================================================
-- Reconciliation results
-- =========================================================
CREATE TABLE recon_runs (
  run_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  started_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at   TIMESTAMPTZ,
  db_paid_raw   NUMERIC(78,0),
  chain_paid_raw NUMERIC(78,0),
  mismatches    JSONB NOT NULL DEFAULT '[]',
  ok            BOOLEAN
);

4.3 Integrity triggers (sketch)

-- I3: claim amount must equal allocation; claims only for the active snapshot
CREATE FUNCTION trg_claim_amount() RETURNS trigger AS $$
DECLARE a NUMERIC(78,0);
BEGIN
  SELECT allocation_raw INTO a FROM allocations
   WHERE snapshot_id = NEW.snapshot_id AND eth_address = NEW.eth_address;
  IF a IS NULL OR a <> NEW.amount_raw THEN
    RAISE EXCEPTION 'amount mismatch for %', NEW.eth_address;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM snapshot_manifest WHERE snapshot_id = NEW.snapshot_id AND is_active) THEN
    RAISE EXCEPTION 'inactive snapshot';
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER claim_amount BEFORE INSERT OR UPDATE OF amount_raw ON claims
  FOR EACH ROW EXECUTE FUNCTION trg_claim_amount();

-- Immutability of allocations / manifest / audit
CREATE FUNCTION trg_immutable() RETURNS trigger AS $$
BEGIN RAISE EXCEPTION '% is append-only/immutable', TG_TABLE_NAME; END $$ LANGUAGE plpgsql;
CREATE TRIGGER alloc_immutable  BEFORE UPDATE OR DELETE ON allocations      FOR EACH ROW EXECUTE FUNCTION trg_immutable();
CREATE TRIGGER audit_immutable  BEFORE UPDATE OR DELETE ON audit_events     FOR EACH ROW EXECUTE FUNCTION trg_immutable();
CREATE TRIGGER manifest_immut   BEFORE DELETE ON snapshot_manifest          FOR EACH ROW EXECUTE FUNCTION trg_immutable();
-- (manifest UPDATE allowed only for is_active toggle via SECURITY DEFINER admin function)

-- Status transitions enforced (see 4.4)
CREATE FUNCTION trg_claim_transition() RETURNS trigger AS $$
BEGIN
  IF NOT (
     (OLD.status='reserved' AND NEW.status IN ('queued','voided')) OR
     (OLD.status='queued'   AND NEW.status IN ('signed','failed','voided')) OR
     (OLD.status='signed'   AND NEW.status IN ('paid','queued','failed')) OR  -- 'queued' only after proven expiry
     (OLD.status='paid'     AND NEW.status IN ('confirmed')) OR
     (OLD.status='failed'   AND NEW.status IN ('queued','voided')) OR         -- admin retry
     (OLD.status = NEW.status)
  ) THEN
    RAISE EXCEPTION 'illegal transition % -> %', OLD.status, NEW.status;
  END IF;
  NEW.updated_at := now(); NEW.version := OLD.version + 1;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER claim_transition BEFORE UPDATE OF status ON claims
  FOR EACH ROW EXECUTE FUNCTION trg_claim_transition();

Also: the xc_api role cannot set any status beyond queued. Only xc_worker (through a SECURITY DEFINER function) can move a claim to signed/paid/confirmed.

4.4 Claim state machine

stateDiagram-v2
  [*] --> unclaimed : allocation loaded
  unclaimed --> reserved : POST /claims (auth + binding)
  reserved --> queued : payout_job created (same tx)
  queued --> signed : worker signs + submits tx (signature stored BEFORE broadcast)
  signed --> paid : tx observed (confirmed commitment)
  paid --> confirmed : finalized + recon matched
  signed --> queued : tx PROVEN expired/dropped (blockhash expired AND sig not found)
  queued --> failed : max attempts / permanent error
  signed --> failed : permanent error, proven not landed
  failed --> queued : admin retry (2-person, audited)
  reserved --> voided : admin (policy/fraud)
  failed --> voided : admin
  confirmed --> [*]
  voided --> [*]

Critical rule: signed → queued (re-send) is allowed only after the worker proves the earlier tx can no longer land. On Solana, that means the tx's lastValidBlockHeight has passed and getSignatureStatuses (with searchTransactionHistory: true) returns null. Without that proof, you risk paying twice.

4.5 Idempotency keys

4.6 Append-only audit and hash chain

• Every API, worker and admin mutation inserts an audit_events row in the same DB transaction as the change.
• row_hash = sha256(prev_hash || seq || occurred_at || actor_type || actor_id || event_type || entity_type || entity_id || jcs(payload)). The genesis prev_hash is the manifest_sha256.
• Concurrency: compute it in a BEFORE INSERT trigger that takes pg_advisory_xact_lock(<const>), so the chain is serial. The volume is low, so that's fine.
• Anchoring: every hour, publish the latest (seq, row_hash) to an external append-only sink: an object store with object lock/WORM, plus optionally a public post or an on-chain memo. That makes silent rewrites detectable.
• Verification job: recompute the chain nightly and alert on any break.

4.7 Core transactional flow for POST /claims (pseudocode)

BEGIN ISOLATION LEVEL READ COMMITTED;
  -- 0) global flags
  SELECT value FROM system_flags WHERE key='claims_paused';            -- abort if 'true'
  -- 1) allocation must exist for active snapshot
  SELECT allocation_raw, flags FROM allocations a JOIN snapshot_manifest m USING (snapshot_id)
   WHERE m.is_active AND a.eth_address = $addr;
  -- abort if none (NOT_ELIGIBLE) or 'requires_manual' flag (MANUAL_REVIEW)
  -- 2) live binding must exist
  SELECT * FROM destination_bindings WHERE eth_address=$addr AND superseded_at IS NULL;
  -- 3) insert claim; unique constraint does the race-safety
  INSERT INTO claims (snapshot_id, eth_address, amount_raw, binding_id, dest_chain, dest_address,
                      idempotency_key, payout_ref, status)
  VALUES (...,'queued')
  ON CONFLICT (snapshot_id, eth_address) DO NOTHING
  RETURNING claim_id;
  -- if no row returned: SELECT existing claim; if same idempotency_key -> 200 replay; else 409
  -- 4) job + audit in same tx
  INSERT INTO payout_jobs (claim_id) VALUES ($claim_id);
  INSERT INTO audit_events (...) VALUES (... 'claim.queued' ...);
COMMIT;

5. Wallet Authentication

5.1 Goals

1. Prove that the requester controls the private key of the eligible Ethereum address (personal_sign, EIP-191, carrying an EIP-4361 SIWE message).
2. Prove the user intends to send their payout to a specific destination address (Solana/X1). Ideally the user also proves control of that address, so they can't fat-finger or paste a hijacked address.
3. Make every signature single-use, time-boxed and bound to this domain.

5.2 SIWE challenge flow

sequenceDiagram
  participant B as Browser (/claim)
  participant EW as Ethereum wallet
  participant API as Claim API
  participant DB as Postgres
  B->>API: POST /auth/nonce {address}
  API->>DB: INSERT auth_nonces (nonce, expires_at=now()+5m)
  API-->>B: {nonce, issuedAt, expirationTime, domain, uri, chainId:1, statement}
  B->>B: build EIP-4361 message
  B->>EW: personal_sign(message)
  EW-->>B: signature (65 bytes)
  B->>API: POST /auth/verify {message, signature}
  API->>API: parse; check domain, uri, chainId, version, times; recover signer
  API->>DB: UPDATE auth_nonces SET consumed_at=now() WHERE nonce=$n AND consumed_at IS NULL AND expires_at>now()
  alt 1 row updated
    API->>DB: INSERT sessions; INSERT audit_events('auth.verified')
    API-->>B: 200 Set-Cookie: xc_session=...; HttpOnly; Secure; SameSite=Strict
  else 0 rows
    API-->>B: 401 NONCE_INVALID_OR_REPLAYED
  end

5.3 Exact message format (EIP-4361)

<<CLAIM_DOMAIN>> wants you to sign in with your Ethereum account:
0xAbC0000000000000000000000000000000000001

Sign in to check and claim your XCORE allocation from the XEN snapshot (block <<FROZEN_BLOCK_NUMBER>>). This does not authorize any transaction or token approval.

URI: https://<<CLAIM_DOMAIN>>/claim
Version: 1
Chain ID: 1
Nonce: 8f3kQz1LmN0pRt6V
Issued At: 2026-10-20T18:00:00.000Z
Expiration Time: 2026-10-20T18:05:00.000Z
Resources:
- urn:xcore-claim:snapshot:<<MANIFEST_SHA256>>

5.4 Server-side verification checklist

5.5 Address normalization

• Storage: lowercase 0x + 40 hex (CHECK constraint in the schema).
• Input: accept any case. If the input is mixed case, validate the EIP-55 checksum and reject a bad one (ADDRESS_BAD_CHECKSUM). That catches typos. All-lowercase and all-uppercase inputs skip the checksum check.
• Display: always EIP-55 checksummed.
• Solana/X1 addresses: base58 decode must give exactly 32 bytes. Optionally require the address to be on the ed25519 curve (PublicKey.isOnCurve), which rejects PDAs that no wallet controls. Store the canonical base58 string.

5.6 Smart-contract wallets (Safe, etc.)

• EIP-191 ecrecover doesn't work for contract wallets. Support EIP-1271: call isValidSignature(bytes32 hash, bytes sig) at the latest block and require magic value 0x1626ba7e.
• Caveats: the contract's signer set may have changed since the snapshot (that's expected, since the current owners control the funds). Counterfactual (ERC-6492) wallets weren't holders at the snapshot unless deployed. Some Safes need an on-chain signing transaction.
• MVP recommendation: flag is_contract=true allocations as requires_manual. Support EIP-1271 in the hardening phase.

5.7 Sessions

5.8 Destination binding (Solana: Phantom / X1 wallet)

The user binds a payout address with two signatures over the same binding message:

1. Ethereum signature (EIP-191 personal_sign) by the eligible address. It proves the eligible holder chose this destination, and is required.
2. Destination-chain signature by the Solana/X1 key (signMessage in Phantom or the X1 wallet, ed25519). It proves the user controls the destination, which prevents typos or paying an address nobody controls. Strongly recommended; require it unless the wallet can't sign messages.

Recommended wallets: Phantom or the X1 wallet for the Solana/X1 side. Any wallet-standard wallet that supports signMessage works technically, but the UI should feature Phantom and X1.

Binding message (both keys sign the exact same bytes, UTF-8):

XCORE claim destination binding
Domain: <<CLAIM_DOMAIN>>
Snapshot: <<MANIFEST_SHA256>>
Ethereum address: 0xAbC0000000000000000000000000000000000001
Destination chain: solana
Destination address: <<BASE58_PUBKEY>>
Nonce: Zr8pQ2wX4yT7uV1a
Issued At: 2026-10-20T18:01:00.000Z
Expiration Time: 2026-10-20T18:06:00.000Z
I authorize sending my XCORE allocation to the destination address above. This replaces any earlier destination until my claim is queued.

Verification:

• nonce from auth_nonces with purpose='bind_dest', consumed atomically.
• Ethereum signature recovers to the session's address.
• nacl.sign.detached.verify(utf8(message), base58decode(dest_sig), base58decode(dest_address)) is true.
• Store both signatures and the exact message in destination_bindings.

Fallback when the wallet can't sign a message (some hardware wallets/Ledger on Solana): the user sends a tiny self-transfer or a Memo-only transaction from the destination address with memo xcore-bind:<nonce>, and the backend verifies the signer and memo on-chain. It costs a small fee and takes longer. Document it as advanced.

Rebinding: allowed while the claim is reserved or before any claim exists. Once a claim is queued, the destination is locked. Changing it then needs a 2-person admin action, and only for a claim that is failed with funds proven unsent.

5.9 What authentication does NOT prove

6. Claim API

Base path: https://<<CLAIM_DOMAIN>>/api/v1. JSON only (Content-Type: application/json). All amounts are strings in base units (*_raw), with a display string alongside. Times are ISO 8601 UTC.

6.1 Endpoint summary

6.2 Allocation lookup: privacy trade-off

Recommendation: the public lookup returns eligibility and amount only (that data is public once the snapshot is published, which transparency requires). Claim status, destination address and tx come only from authed endpoints. Note that payout transactions are public on-chain anyway, so this is obfuscation, not secrecy.

6.3 Request/response examples

GET /snapshot

200 OK
{
  "snapshotId": 1,
  "manifestSha256": "<<MANIFEST_SHA256>>",
  "sourceChainId": 1,
  "token": "0x06450dEe7FD2Fb8E39061434BAbCFC05599a6Fb8",
  "blockNumber": "<<FROZEN_BLOCK_NUMBER>>",
  "blockHash": "<<FROZEN_BLOCK_HASH>>",
  "snapshotTimeUtc": "2026-10-01T00:00:00Z",
  "formula": { "id": "<<formula_id>>", "version": "<<semver>>" },
  "eligibleCount": "<<N>>",
  "claimWindow": { "opensAt": "<<ISO>>", "closesAt": "<<ISO>>" },
  "artifactsUrl": "<<PUBLIC_ARTIFACTS_URL_PLACEHOLDER>>",
  "claimsPaused": false
}

GET /allocations/0xAbC0000000000000000000000000000000000001

200 OK
{
  "address": "0xAbC0000000000000000000000000000000000001",
  "eligible": true,
  "xenBalanceRaw": "12500000000000000000000000",
  "xenBalanceDisplay": "12,500,000 XEN",
  "allocationRaw": "4200000000000",
  "allocationDisplay": "4,200 XCORE",
  "requiresManualReview": false,
  "manifestSha256": "<<MANIFEST_SHA256>>"
}

Not eligible (address unknown, zero balance, or below dust):

200 OK
{ "address": "0x…", "eligible": false, "reason": "NOT_IN_SNAPSHOT", "manifestSha256": "…" }

reason ∈ NOT_IN_SNAPSHOT | BELOW_DUST | EXCLUDED_POLICY. Excluded addresses return EXCLUDED_POLICY with a short public reason (lp_pool, burn, …).

POST /auth/nonce

// request
{ "address": "0xAbC0000000000000000000000000000000000001" }
// 200
{
  "nonce": "8f3kQz1LmN0pRt6V",
  "domain": "<<CLAIM_DOMAIN>>",
  "uri": "https://<<CLAIM_DOMAIN>>/claim",
  "chainId": 1,
  "version": "1",
  "issuedAt": "2026-10-20T18:00:00.000Z",
  "expirationTime": "2026-10-20T18:05:00.000Z",
  "statement": "Sign in to check and claim your XCORE allocation from the XEN snapshot (block <<FROZEN_BLOCK_NUMBER>>). This does not authorize any transaction or token approval.",
  "resources": ["urn:xcore-claim:snapshot:<<MANIFEST_SHA256>>"]
}

POST /auth/verify

// request
{ "message": "<full EIP-4361 text>", "signature": "0x…130 hex…" }
// 200  (Set-Cookie: xc_session=…; HttpOnly; Secure; SameSite=Strict; Path=/api)
{ "address": "0xAbC0000000000000000000000000000000000001", "expiresAt": "2026-10-20T18:35:00.000Z" }
// 401
{ "error": { "code": "NONCE_INVALID_OR_REPLAYED", "message": "Nonce expired or already used. Request a new one." } }

POST /destination/nonce → { "nonce": "Zr8pQ2wX4yT7uV1a", "message": "<binding message text>", "expiresAt": "…" } (The server builds the canonical message, so the client can't tamper with its format. The client passes destChain and destAddress in the request.)

POST /destination

// request
{
  "destChain": "solana",
  "destAddress": "<<BASE58_PUBKEY>>",
  "message": "<exact binding message>",
  "ethSignature": "0x…",
  "destSignature": "<base58 ed25519 sig>"
}
// 200
{ "bindingId": "b1f…", "destChain": "solana", "destAddress": "<<BASE58_PUBKEY>>", "destVerified": true, "locked": false }
// 409
{ "error": { "code": "DESTINATION_LOCKED", "message": "Your claim is already queued; destination cannot be changed." } }

POST /claims

POST /api/v1/claims
Idempotency-Key: 6c0b8e7e-0f5e-4b8f-9a0a-2a0c7d1d3e11
X-Requested-With: xcore-claim
Cookie: xc_session=…

{ "acceptTermsVersion": "<<TERMS_VERSION>>" }

// 201 Created
{
  "claimId": "c9a…",
  "status": "queued",
  "amountRaw": "4200000000000",
  "amountDisplay": "4,200 XCORE",
  "destChain": "solana",
  "destAddress": "<<BASE58_PUBKEY>>",
  "payoutRef": "3f9c0d…(32 hex)",
  "createdAt": "2026-10-20T18:02:10.000Z"
}
// 200 (same Idempotency-Key replayed) -> identical body to the original 201
// 409
{ "error": { "code": "ALREADY_CLAIMED", "message": "This address has already claimed.", "claimId": "c9a…", "status": "paid" } }

GET /claims/me

200 OK
{
  "claimId": "c9a…",
  "status": "paid",
  "amountRaw": "4200000000000",
  "destChain": "solana",
  "destAddress": "<<BASE58_PUBKEY>>",
  "payoutTx": "<<SOLANA_TX_SIGNATURE>>",
  "explorerUrl": "<<EXPLORER_BASE>>/tx/<<SOLANA_TX_SIGNATURE>>",
  "history": [
    {"status":"queued","at":"2026-10-20T18:02:10Z"},
    {"status":"signed","at":"2026-10-20T18:02:41Z"},
    {"status":"paid","at":"2026-10-20T18:02:44Z"}
  ]
}

404 NO_CLAIM if the user hasn't claimed yet.

6.4 Error codes

Error envelope: { "error": { "code": "…", "message": "…", "requestId": "…" } }. Never echo stack traces or SQL.

6.5 Rate limiting and CORS

• Hash IPs with a rotating salt (IP_HASH_SALT). Don't store raw IPs (§9.8).
• Add bot protection (e.g. Vercel firewall rules / a challenge such as Turnstile) on /auth/nonce if abuse shows up.
• CORS: API and page are same-origin, so send no Access-Control-Allow-Origin (deny cross-origin). If they must be split, allowlist exactly https://<<CLAIM_DOMAIN>> with credentials: true, never *.
• Security headers: Content-Security-Policy (strict, nonce-based scripts; connect-src limited to the API and the RPC endpoints the page uses), Strict-Transport-Security, X-Frame-Options: DENY / frame-ancestors 'none', Referrer-Policy: no-referrer.

6.6 Payout retry rules (POST /claims/me/retry)

The user can't force a re-send. This endpoint only asks the worker to re-check the claim:

Limit: 3 retry requests per claim per hour. Every request goes into the audit log.

7. Payout Fulfillment Options

7.1 Comparison

Recommended hybrid: run (a) for allocations ≤ HOT_WALLET_MAX_PER_CLAIM_RAW with a small, regularly topped-up hot balance. Route larger allocations, contract-wallet holders and failed claims to (c) batches. Fund the hot wallet from a cold multisig treasury, and log every top-up as an admin_actions fund_topup entry.

7.2 Option (a): worker design

def worker_loop(worker_id):
    while True:
        if flag('payouts_paused'): sleep(10); continue
        job = lease_one_job(worker_id, lease_seconds=120)   # SELECT … FOR UPDATE SKIP LOCKED
        if not job: sleep(2); continue
        claim = load_claim(job.claim_id)                     # status must be 'queued'
        try:
            # 1) Pre-flight: has this claim already been paid on-chain? (memo ref search / local index)
            existing = find_onchain_payout(claim.payout_ref)
            if existing: return mark_paid(claim, existing.signature, source='preflight')

            # 2) Limits
            enforce_limits(claim.amount_raw)                 # per-claim, hourly, daily caps
            assert hot_wallet_balance() >= claim.amount_raw + FEE_BUFFER

            # 3) Build tx: (idempotent ATA create) + transferChecked(mint, decimals) + Memo("xcore:1:<payout_ref>")
            tx, blockhash, last_valid = build_transfer(claim.dest_address, claim.amount_raw, memo=ref(claim))
            sig = sign(tx)                                   # signature is deterministic for this tx

            # 4) Persist BEFORE broadcast (crash-safe)
            with db.tx():
                insert_attempt(job, sig, blockhash, last_valid, outcome='submitted')
                set_claim(claim, status='signed', payout_tx=sig)

            send_raw(tx, skip_preflight=False, max_retries=0)  # rebroadcast loop below
            outcome = confirm_or_expire(sig, last_valid, commitment='confirmed')
            if outcome == 'confirmed':
                mark_paid(claim, sig)
            elif outcome == 'expired_not_found':            # PROVEN not landed
                with db.tx():
                    update_attempt(sig, 'expired'); set_claim(claim, status='queued', payout_tx=None)
                    schedule_retry(job, backoff(job.attempts))
            else:                                            # unknown -> do NOT resend; recheck later
                schedule_recheck(job, delay=60)
        except PermanentError as e:
            fail_job(job, claim, e)                          # -> 'failed', alert
        except TransientError as e:
            schedule_retry(job, backoff(job.attempts))

Key rules:

• One worker lease per job (FOR UPDATE SKIP LOCKED + lease expiry). Run a single worker process at first.
• Persist the signature before broadcast. On restart, any signed claim is checked, never re-signed blindly.
• Re-send only after proven expiry (§4.4).
• Use transferChecked with the mint decimals. Create the recipient's associated token account idempotently (the project pays rent, so budget for it). If XCORE uses Token-2022 extensions (transfer fees/hooks), VERIFY amounts and hooks.
• Memo: xcore:<snapshot_id>:<payout_ref>. Index hot-wallet transactions so preflight can search by ref.
• Backoff: min(2^attempts × 15s, 30min) with jitter. After max_attempts → failed + alert.

7.3 Hot-wallet limits and monitoring

7.4 Option (c): batch flow

1. Admin (MFA) runs export_batch(max_rows, max_total_raw). It selects queued jobs, assigns a batch_id, and writes batch_<id>.csv (claim_id,dest,amount_raw,payout_ref) plus its SHA-256 into admin_actions.
2. A second admin independently re-derives the batch from the DB read replica and compares hashes.
3. Multisig signers execute transfers with memos (xcore:1:<payout_ref>), in chunks that fit the tx size limit.
4. The worker ingests the results by scanning the multisig vault's transactions for memos, then moves the claims to paid/confirmed.
5. Rows not found on-chain after the batch window → back to queued (proven not paid) and into the next batch.

7.5 Pause switch

• system_flags.claims_paused → API returns 503 CLAIMS_PAUSED for POST /claims and /destination. Lookups keep working.
• system_flags.payouts_paused → the worker stops leasing.
• One admin can pause. Unpausing needs 2 people. Automatic pause on: unexpected outflow, recon mismatch, audit chain break, or error rate above threshold.

8. Webpage UX Flow

8.1 Flow

flowchart TD
  A[Landing /claim<br/>snapshot info + manifest hash] --> B{Connect Ethereum wallet}
  B --> C[GET /allocations/addr]
  C -->|not eligible| X1[Ineligible screen<br/>reason + how snapshot works]
  C -->|eligible| D[Sign-in: SIWE personal_sign]
  D -->|fail| D
  D --> E[GET /me]
  E -->|claim exists| S[Status screen]
  E -->|manual review| M[Manual review screen<br/>contact path]
  E -->|no claim| F[Connect Solana wallet<br/>Phantom / X1]
  F --> G[Bind destination:<br/>sign with ETH + sign with Phantom/X1]
  G --> H[Review: amount, destination, terms,<br/>'destination locks after claim']
  H --> I[POST /claims w/ Idempotency-Key]
  I -->|201/200| S
  I -->|409 ALREADY_CLAIMED| S
  S --> S2{status}
  S2 -->|queued/signed| P[Pending: poll GET /claims/me every 5–10s]
  S2 -->|paid/confirmed| Q[Paid: tx link + amount]
  S2 -->|failed| R[Delayed: team notified, no action needed]

8.2 Screen states

8.3 Front-end rules

• The page never asks for, handles or stores a seed phrase or private key. The only wallet calls are personal_sign (Ethereum) and signMessage (Solana). No eth_sendTransaction, no token approvals, no Solana transaction signing (except the optional bind-by-memo fallback, which is clearly labeled).
• Show the full message the user is about to sign before you trigger the wallet.
• Wallet connectors: Ethereum through standard injected/WalletConnect providers. Solana through the Wallet Standard adapter, featuring Phantom and X1 wallet as recommended options.
• Show the manifest hash and a link to the artifacts so users can verify independently.
• Generate a fresh Idempotency-Key per claim attempt and keep it in sessionStorage until success, so retrying after a network error reuses it.
• Accessibility: keyboard navigable, readable errors, nothing that relies on color alone.

9. Security Threat Model & Controls

9.1 Assets and adversaries

Assets: the XCORE payout funds (hot wallet + treasury), integrity of the allocation table, the claim → destination mapping, the audit log, user trust. Adversaries: external attackers (API abuse, phishing clones), holders with compromised keys, malicious or compromised insiders/admins, supply-chain compromise (npm deps, CI), infrastructure provider compromise.

9.2 Threat → control matrix

9.3 DB tampering defenses in depth

1. Signed snapshot manifest published publicly. ETL refuses to load unless the artifact SHA-256 matches the manifest and the manifest signature verifies.
2. Immutable tables (triggers + grants). Migrations that touch allocations need a new snapshot_id.
3. Worker-side verification: before signing a payout, the worker checks (eth_address, amount_raw) against an allocation set it loads from the published artifact by hash, not only from the DB.
4. Append-only audit with hash chain + hourly external anchor.
5. Read-replica separation: dashboards, recon and support tooling use the replica / xc_readonly. Only the API and worker hold write roles.
6. Admin MFA + RBAC through an IdP (SSO). No shared DB passwords. Short-lived credentials where the platform supports them.

9.4 Secrets management

Never commit secrets. Run secret scanning in CI. Never print secrets in logs.

9.5 Hot-wallet limits

See §7.3. Caps are enforced in the worker and in the DB (a payout_limits check function sums paid+signed amounts in the last hour/day before leasing).

9.6 Abuse and rate limits

See §6.5. Add anomaly alerts for nonce-to-verify ratio, verify failure rate, and repeated lookups of many addresses from one IP.

9.7 Logging

Structured JSON logs carry the request id, route, status and eth address (public). Never log signatures in full (log a hash), cookies, raw IPs or secrets.

9.8 PII

• Store no PII: no emails, names, or KYC.
• Blockchain addresses are public, pseudonymous data. Linking an Ethereum address to a Solana address creates a new public-ish linkage. Disclose it in the terms/privacy notice, keep binding data out of public endpoints, and know that the payout tx on-chain doesn't reveal the Ethereum address (only the memo ref, which is a salted hash).
• IPs: salted hash only, kept ≤ 30 days for rate limiting.
• If a support channel collects contact details, keep them outside this DB.

9.9 Backup and restore

• Managed Postgres PITR (Neon branching/history or equivalent), plus a nightly logical dump (pg_dump) encrypted to a separate account/bucket with object lock.
• Restore drill before launch and monthly. RPO ≤ 5 min, RTO ≤ 4 h.
• After any restore: pause payouts, rebuild the paid set from on-chain memos for the hot wallet and multisig, reconcile claims (on-chain paid but DB not → mark paid; DB signed but no tx → prove expiry), then unpause with 2 people.

9.10 Incident response and pause

Run a post-incident review and publish a public statement for anything that affects users.

10. Operations

10.1 Environment variables (names only, no values)

10.2 Deployment topology

flowchart LR
  U[User browser] -->|HTTPS| V[Vercel project: claim web + /api/v1 functions]
  V -->|xc_api pooled| N[(Neon Postgres primary)]
  N --> R[(Read replica)]
  W[Payout worker<br/>isolated runtime: container/VM<br/>or separate Vercel project w/ cron] -->|xc_worker| N
  W --> K[KMS signer]
  W --> SOL[Payout chain RPC x2]
  C[Cron: recon, audit-anchor, nonce GC] --> R
  C --> SOL
  C --> ETH[Ethereum RPC x2]
  A[Admin console<br/>SSO+MFA, IP allowlist] --> N

• Web + API: one Vercel project (consistent with the prior stack). Production and Preview environments use separate databases (Neon branches). Preview must never point at production data or payout keys.
• Worker: separate from the web project, so the signer key is never in the same runtime as public HTTP handlers. Prefer a small always-on container (Fly/Render/VM) or a separate Vercel project triggered by cron with no public routes.
• Admin console: separate deployment with SSO, or a CLI that runs only from an allowlisted environment.
• Note: this document makes no changes to wxnt-sale-sim.vercel.app or any existing project. Integrating with it is a future, separately approved step.

10.3 Migrations

• Tool: one of node-pg-migrate, Prisma Migrate, Drizzle Kit, or sqitch. Forward-only, numbered files in git.
• Run from CI with DATABASE_URL_MIGRATOR after review. Apply to a Neon branch first and run the integration suite there.
• Allocation loads are ETL operations, not migrations: etl load --manifest snapshot_manifest.json --artifacts ./out/. That command verifies the signature and hashes, loads in one transaction, checks invariant I1 and counts, and sets is_active only through a separate 2-person admin action.

10.4 Monitoring and alerts

Dashboards: claims by status, cumulative paid vs pool, claims per hour, worker latency p50/p95, RPC error rates.

10.5 Daily reconciliation job

def daily_recon():
    db_paid = sql("SELECT payout_ref, payout_tx, amount_raw, dest_address FROM claims WHERE status IN ('paid','confirmed')")
    chain = index_outflows([HOT_WALLET, MULTISIG], since=LAUNCH)    # parse SPL transfers + memos
    by_ref_chain = group(chain, key=lambda t: t.memo_ref)
    mismatches = []
    for c in db_paid:
        txs = by_ref_chain.get(c.payout_ref, [])
        if len(txs) == 0: mismatches.append(("db_paid_not_onchain", c))
        elif len(txs) > 1: mismatches.append(("DOUBLE_PAY", c, txs))          # SEV1
        elif txs[0].amount != c.amount_raw or txs[0].dest_owner != c.dest_address:
            mismatches.append(("amount_or_dest_mismatch", c, txs[0]))
    for ref, txs in by_ref_chain.items():
        if ref not in {c.payout_ref for c in db_paid}:
            mismatches.append(("onchain_not_db_paid", ref, txs))           # includes 'signed' claims that landed
    for t in chain:
        if t.memo_ref is None and not t.is_known_admin_movement:
            mismatches.append(("UNTAGGED_OUTFLOW", t))                     # SEV1
    totals_ok = sum(db_paid.amount) == sum(t.amount for t in chain if t.memo_ref)
    record_recon_run(db_paid_raw=..., chain_paid_raw=..., mismatches, ok=not mismatches and totals_ok)
    if mismatches: alert(); set_flag('payouts_paused','true')

Also: promote paid → confirmed for matched, finalized txs. Check invariants I1–I6.

10.6 Snapshot re-run determinism test

• CI job (nightly before launch, then weekly): rebuild holders_all.csv, excluded_addresses.csv and allocations.csv from raw_logs.jsonl.zst with no network, then compare SHA-256 to the manifest. Must match exactly.
• Pre-launch (once per provider): full re-scan from both RPC providers, artifact hashes equal.
• Formula property tests: invariants I1, non-negativity, cap respected, dust excluded, deterministic tie-break.
• Platform pinning: pin runtime versions and the lockfile. Sort with byte-wise lowercase-hex comparison, not locale-aware sorting.

10.7 Routine jobs

11. Testing & Acceptance Criteria

11.1 Unit tests

• Block selector: synthetic timestamps; boundary equal to T; T between blocks; T before deploy (error).
• Log decoder: topic parsing, zero address handling, mint/burn, self-transfer (from == to, net zero).
• Balance reconstruction: negative balance detection triggers abort.
• Allocation formulas: invariants I1; cap water-filling; dust; rounding remainder; deterministic tie-break; huge numbers (78-digit).
• Address normalization: lowercase/uppercase accepted, bad mixed-case checksum rejected, Solana base58 32-byte + on-curve.
• SIWE verification: every row of the §5.4 table.
• Ed25519 binding verification.
• State machine trigger: every illegal transition rejected.
• payout_ref derivation stable and unique.

11.2 Integration tests (Neon branch / local Postgres + local chain)

• ETL refuses a tampered artifact (1 byte changed) and a bad manifest signature.
• Concurrent POST /claims (50 parallel, same address, different idempotency keys) → exactly 1 claim, 49 × 409.
• Same idempotency key replay → identical 200 body.
• Worker crash after persisting the signature but before broadcast → restart → no double send. The claim resolves via signature status.
• Worker crash after broadcast before mark_paid → preflight finds the memo → marked paid, no re-send.
• Expired-blockhash path → re-queued with the same ref → paid once.
• Payout caps → worker pauses at the hourly cap.
• Pause flags stop claims and payouts respectively.
• Recon detects: injected double pay, untagged outflow, DB-paid-not-on-chain.
• Audit chain: a manual UPDATE attempt fails; a simulated row edit (superuser) is detected by verify.
• Payout on a Solana local validator/devnet with a test mint (never mainnet in CI).

11.3 End-to-end (Playwright with test wallets)

• Eligible flow: connect → SIWE → bind (Phantom/X1-compatible test signer) → review → claim → pending → paid.
• Ineligible address screen.
• Already-claimed address goes straight to the status screen.
• Paused banner.
• Window closed.
• Network drop during POST /claims → retry reuses the idempotency key → single claim.

11.4 Test vectors

Use a dedicated test snapshot (snapshot_id = 900) built on a fork or synthetic data. These are placeholder fixture addresses, not real holders.

Snapshot test vectors (pre-launch, real chain): pick 20 well-known addresses (top holders, a known burn, a known LP, a random sample) and check the reconstructed balance against balanceOf(addr, B) from both providers. Store them in reconciliation_report.json.

11.5 Acceptance criteria (launch gate)

• Manifest frozen, signed, published. The determinism test passes across two providers.
• Total supply check exact. Sample reconciliation 100%.
• Every §11.1–11.3 test is green in CI.
• Load test: 50 claims/s sustained at the API with p95 < 500 ms. The worker handles the expected peak.
• External security review of the auth, claim and worker code.
• Backup restore drill done, with paid-set rebuild from chain demonstrated on devnet.
• Runbooks: pause, key rotation, restore, recon mismatch.
• All <<PLACEHOLDER>> values resolved. grep '<<' config/ is empty.

12. Build Sequence & Open Decisions

12.1 Phased checklist

Phase 0: Decisions & freeze (owner)

• Confirm the snapshot time (2026-10-01 00:00 UTC?) and block rule
• Decide everything in §12.3
• Choose the payout chain (Solana vs X1) and confirm the XCORE mint + decimals

Phase 1: Snapshot (1–2 weeks)

• Find and verify the deploy block
• Scanner with checkpointing; raw log archive
• Reconstruction + classification + labels review
• Reconciliation report; two-provider match
• Allocation engine + manifest + signature
• Publish artifacts for public review (comment period recommended)

Phase 2: MVP backend + page (2–3 weeks)

• Schema + migrations + roles
• ETL loader with hash/signature verification
• Auth endpoints (SIWE), sessions
• Destination binding (Phantom / X1 signMessage)
• Claims endpoint with idempotency
• Batch export payout (option c) as the first payout path (no hot key yet)
• Claim page states
• Audit log + hash chain

Phase 3: Hardening (1–2 weeks)

• Hot-wallet worker (option a) with KMS, caps, memo, preflight, proven-expiry retries
• Reconciliation job + alerts + auto-pause
• Rate limits, CSP, firewall
• Admin console with SSO/MFA + 2-person actions
• EIP-1271 support (or a documented manual path)
• Backup/restore drill, load test, external review

Phase 4: Launch

• Soft launch: allowlist of test holders / small cap per hour
• Public launch: announce the official URL + manifest hash from verified channels
• Daily recon review. Weekly public status (claimed count / amount).
• Claim window close → apply the unclaimed policy → final report + final audit anchor

12.2 RACI (lightweight)

12.3 Open decisions (owner must decide before freeze)

13. Alternatives Considered

13.1 On-chain Merkle distributor vs off-chain DB

In the Merkle design, the allocation table becomes a Merkle tree and the root is committed to a distributor program/contract on the payout chain. Each user submits a proof to claim. To keep Ethereum-address eligibility, the program has to verify an Ethereum signature (secp256k1 recover; Solana has a secp256k1_recover syscall / secp256k1 program) binding eth_address → solana_recipient, or a relayer submits on the user's behalf.

Verdict: for a small team, the off-chain DB with published artifacts, a hybrid hot-wallet/multisig payout and daily public reconciliation gets to launch fastest with the best UX. The price is more operator trust. If the community wants trust minimization, the snapshot and allocation pipeline here carries over unchanged to a Merkle approach later: the same allocations.csv becomes the Merkle leaves.

13.2 Other alternatives briefly

14. Appendix: Assumptions Register & Glossary

14.1 Assumptions and flags

14.2 Glossary

End of document. Design documentation only: nothing was deployed, no live site was modified, no keys were created or used.