f8cd37ac8c
Includes: API (Express/TypeScript), Astro site, Python workers, document generators, FCC compliance tools, Canada CRTC formation, Ansible infrastructure, and deployment scripts. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
44 lines
2.3 KiB
SQL
44 lines
2.3 KiB
SQL
-- 064: Cold wallet config + sweep audit trail
|
|
--
|
|
-- Hardware / multisig wallet already exists off-platform. We only need:
|
|
-- (a) a place to record the expected address + checksum per coin so
|
|
-- the sweeper can abort on startup if env vars drift to an
|
|
-- attacker-controlled address
|
|
-- (b) audit rows for every sweep we initiate (with approval state)
|
|
--
|
|
-- Seed data: rows are populated at worker startup from
|
|
-- COLD_WALLET_<COIN>_ADDR env vars; startup validates address format
|
|
-- per coin (BIP-173 for BTC, EIP-55 for ETH/MATIC/BNB, base58 for
|
|
-- LTC/DOGE, bech32 for TRX).
|
|
|
|
CREATE TABLE IF NOT EXISTS cold_wallet_config (
|
|
coin TEXT PRIMARY KEY,
|
|
cold_address TEXT NOT NULL,
|
|
address_checksum TEXT NOT NULL, -- SHA-256 of the normalized address; validated at startup
|
|
hot_float_usd_cents BIGINT NOT NULL DEFAULT 50000, -- keep ~$500 hot per coin
|
|
auto_sweep_ceiling_usd_cents BIGINT NOT NULL DEFAULT 500000, -- auto-sweep ≤ $5,000
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS cold_wallet_sweeps (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
coin TEXT NOT NULL,
|
|
amount_coin NUMERIC(36,18) NOT NULL,
|
|
amount_usd_cents BIGINT,
|
|
cold_address TEXT NOT NULL,
|
|
shkeeper_withdraw_id TEXT, -- ID returned by SHKeeper's payout API
|
|
tx_hash TEXT, -- on-chain tx hash once broadcast
|
|
requires_approval BOOLEAN NOT NULL, -- TRUE when amount_usd > auto_sweep_ceiling
|
|
approved_by TEXT,
|
|
approved_at TIMESTAMPTZ,
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending','approved','broadcast','confirmed','failed')),
|
|
failure_reason TEXT,
|
|
ledger_entry_id BIGINT REFERENCES crypto_payment_ledger(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_cws_status ON cold_wallet_sweeps (status) WHERE status IN ('pending','approved','broadcast');
|
|
CREATE INDEX IF NOT EXISTS idx_cws_coin ON cold_wallet_sweeps (coin, created_at DESC);
|