How a stranger implements this.

CREATE TABLE handshakes (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  handshake_uid   TEXT UNIQUE NOT NULL,        -- H-2026-05-00417
  type            TEXT NOT NULL,               -- 'INTENT'|'ACK'|'INTAKE'|'SETTLEMENT'|'OUTCOME'
  chain_seq       INTEGER NOT NULL,            -- 1..5
  referrer_id     INTEGER NOT NULL,
  recipient_id    INTEGER,
  client_id       INTEGER,
  vertical_id     INTEGER NOT NULL,
  product_id      INTEGER NOT NULL,
  gross_cents     INTEGER,                     -- populated at H4
  signature       TEXT NOT NULL,               -- ECDSA P-256 over canonical payload
  signature_alg   TEXT NOT NULL DEFAULT 'ES256',
  signing_key_id  TEXT NOT NULL,               -- which device key
  payload_hash    TEXT NOT NULL,               -- SHA-256 of canonical JSON
  hash_prev       TEXT,                        -- previous handshake hash (chain link)
  hash_self       TEXT UNIQUE NOT NULL,        -- SHA-256(payload_hash || hash_prev)
  block_id        INTEGER,                     -- which sealed block this row was anchored into
  idempotency_key TEXT UNIQUE,                 -- request-supplied or derived
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_id) REFERENCES contacts(id),
  FOREIGN KEY (recipient_id) REFERENCES contacts(id),
  FOREIGN KEY (client_id) REFERENCES contacts(id),
  FOREIGN KEY (vertical_id) REFERENCES verticals(id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (block_id) REFERENCES chain_blocks(id)
);

CREATE INDEX idx_handshakes_uid ON handshakes(handshake_uid);
CREATE INDEX idx_handshakes_referrer ON handshakes(referrer_id);
CREATE INDEX idx_handshakes_recipient ON handshakes(recipient_id);
CREATE INDEX idx_handshakes_chain ON handshakes(handshake_uid, chain_seq);

-- App-layer enforcement: INSERT-only. No UPDATE, no DELETE.
-- Enforced via wrangler binding policy + code review checklist.

CREATE TABLE chain_blocks (
  id                      INTEGER PRIMARY KEY AUTOINCREMENT,
  block_height            INTEGER UNIQUE NOT NULL,
  block_hash              TEXT UNIQUE NOT NULL,
  prev_block_hash         TEXT,                    -- NULL only on genesis
  merkle_root             TEXT NOT NULL,
  event_count             INTEGER NOT NULL,
  period_start            DATETIME NOT NULL,
  period_end              DATETIME NOT NULL,
  sealed_at               DATETIME DEFAULT CURRENT_TIMESTAMP,
  external_anchor_status  TEXT NOT NULL DEFAULT 'PENDING',  -- PENDING|ANCHORED|FAILED
  external_anchor_method  TEXT,                              -- 'NOTARY_HTTP'|'BTC_OPRETURN'|'ETH_L2'
  external_anchor_ref     TEXT,
  external_anchored_at    DATETIME
);

CREATE TABLE commission_intents (
  id                 INTEGER PRIMARY KEY AUTOINCREMENT,
  intent_uid         TEXT UNIQUE NOT NULL,            -- CI-2026-00417
  handshake_uid      TEXT NOT NULL,                   -- link to H4
  rate_card_version  INTEGER NOT NULL,
  gross_cents        INTEGER NOT NULL,
  referrer_cents     INTEGER NOT NULL,
  recipient_cents    INTEGER NOT NULL,
  platform_cents     INTEGER NOT NULL,
  status             TEXT NOT NULL DEFAULT 'PENDING', -- PENDING|PAID|DISPUTED|VOID
  created_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (handshake_uid) REFERENCES handshakes(handshake_uid)
);

CREATE TABLE payouts (
  id                          INTEGER PRIMARY KEY AUTOINCREMENT,
  payout_uid                  TEXT UNIQUE NOT NULL,        -- PO-2026-00417
  commission_intent_id        INTEGER UNIQUE NOT NULL,     -- one-to-one — idempotent
  payee_member_id             INTEGER NOT NULL,
  amount_cents                INTEGER NOT NULL,
  currency                    TEXT NOT NULL DEFAULT 'AUD',
  stripe_transfer_id          TEXT,
  stripe_application_fee_id   TEXT,
  status                      TEXT NOT NULL DEFAULT 'QUEUED', -- QUEUED|SENT|PAID|FAILED|REVERSED
  initiated_at                DATETIME,
  arrived_at                  DATETIME,
  rcti_pdf_path               TEXT,
  created_at                  DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (commission_intent_id) REFERENCES commission_intents(id)
);

CREATE TABLE rate_cards (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  version         INTEGER UNIQUE NOT NULL,
  vertical_id     INTEGER NOT NULL,
  product_id      INTEGER,                          -- NULL = applies to whole vertical
  referrer_bps    INTEGER NOT NULL,                 -- basis points (10 = 0.10%)
  recipient_bps   INTEGER NOT NULL,
  platform_bps    INTEGER NOT NULL,
  effective_from  DATETIME NOT NULL,
  effective_to    DATETIME,                         -- NULL = currently active
  notes           TEXT,
  created_by      INTEGER,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (vertical_id) REFERENCES verticals(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE audit_log (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  actor_id        INTEGER NOT NULL,
  actor_role      TEXT NOT NULL,
  action          TEXT NOT NULL,
  target_table    TEXT NOT NULL,
  target_id       INTEGER,
  payload_json    TEXT,
  ip              TEXT,
  user_agent      TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
);