1-- nzip D1 schema. Idempotent; apply with:2-- wrangler d1 execute nzip --remote --file schema.sql34CREATE TABLE IF NOT EXISTS vaults (5 slot INTEGER PRIMARY KEY CHECK (slot BETWEEN 0 AND 15),6 name TEXT NOT NULL UNIQUE,7 description TEXT,8 default_ttl INTEGER CHECK (default_ttl IS NULL OR default_ttl BETWEEN 0 AND 3650),9 created_at INTEGER NOT NULL10);1112INSERT OR IGNORE INTO vaults (slot, name, description, default_ttl, created_at)13VALUES (0, 'personal', 'Personal and temporary shares', 14, unixepoch());14INSERT OR IGNORE INTO vaults (slot, name, description, default_ttl, created_at)15VALUES (15, 'public', 'Durable public app origins', 0, unixepoch());1617CREATE TABLE IF NOT EXISTS vault_defaults (18 lifecycle TEXT PRIMARY KEY CHECK (lifecycle IN ('temporary', 'permanent')),19 vault_slot INTEGER NOT NULL REFERENCES vaults(slot)20);21INSERT OR IGNORE INTO vault_defaults (lifecycle, vault_slot)22SELECT 'temporary', slot FROM vaults WHERE slot = 0 AND name = 'personal';23INSERT OR IGNORE INTO vault_defaults (lifecycle, vault_slot)24SELECT 'permanent', slot FROM vaults WHERE slot = 15 AND name = 'public';2526CREATE TABLE IF NOT EXISTS sites (27 address INTEGER PRIMARY KEY CHECK (address BETWEEN 0 AND 65535),28 vault_slot INTEGER NOT NULL REFERENCES vaults(slot),29 alias TEXT,30 current_manifest TEXT NOT NULL,31 created_at INTEGER NOT NULL,32 updated_at INTEGER NOT NULL,33 expires_at INTEGER, -- unix seconds; NULL = permanent34 password_hash TEXT, -- PBKDF2 password verifier; NULL = public35 content_security_policy TEXT, -- app build CSP mirrored as a response header36 auth_version INTEGER NOT NULL DEFAULT 1, -- increment to revoke unlock cookies37 UNIQUE (vault_slot, alias)38);39CREATE INDEX IF NOT EXISTS idx_sites_expiry ON sites(expires_at) WHERE expires_at IS NOT NULL;4041-- App addresses are permanent identity tombstones. Content may disappear from42-- sites, but a reservation row is never deleted or returned to the allocator.43CREATE TABLE IF NOT EXISTS app_reservations (44 address INTEGER PRIMARY KEY CHECK (address BETWEEN 0 AND 65535),45 vault_slot INTEGER NOT NULL REFERENCES vaults(slot),46 alias TEXT NOT NULL,47 created_at INTEGER NOT NULL,48 retired_at INTEGER,49 UNIQUE (vault_slot, alias)50);5152CREATE TABLE IF NOT EXISTS pushes (53 address INTEGER NOT NULL REFERENCES sites(address) ON DELETE CASCADE,54 seq INTEGER NOT NULL,55 manifest_hash TEXT NOT NULL,56 pushed_at INTEGER NOT NULL,57 note TEXT,58 PRIMARY KEY (address, seq)59);6061CREATE TABLE IF NOT EXISTS security_probes (62 bucket INTEGER NOT NULL,63 scanner_id TEXT NOT NULL,64 address INTEGER NOT NULL CHECK (address BETWEEN 0 AND 65535),65 vault_slot INTEGER NOT NULL CHECK (vault_slot BETWEEN 0 AND 15),66 is_live INTEGER NOT NULL CHECK (is_live IN (0, 1)),67 country TEXT,68 asn INTEGER,69 PRIMARY KEY (bucket, scanner_id, address)70);7172CREATE TABLE IF NOT EXISTS security_signals (73 bucket INTEGER NOT NULL,74 scanner_id TEXT NOT NULL,75 kind TEXT NOT NULL,76 PRIMARY KEY (bucket, scanner_id, kind)77);7879CREATE TABLE IF NOT EXISTS security_incidents (80 name TEXT PRIMARY KEY,81 status TEXT NOT NULL CHECK (status IN ('open', 'closed')),82 severity INTEGER NOT NULL,83 opened_at INTEGER NOT NULL,84 last_seen_at INTEGER NOT NULL,85 last_alerted_at INTEGER NOT NULL,86 quiet_windows INTEGER NOT NULL,87 peak_addresses INTEGER NOT NULL,88 total_addresses INTEGER NOT NULL,89 total_live_hits INTEGER NOT NULL,90 total_rate_limited INTEGER NOT NULL,91 vault_mask INTEGER NOT NULL92);9394CREATE TABLE IF NOT EXISTS security_notifications (95 id TEXT PRIMARY KEY,96 incident_name TEXT NOT NULL,97 action TEXT NOT NULL,98 window_bucket INTEGER NOT NULL,99 subject TEXT NOT NULL,100 text TEXT NOT NULL,101 html TEXT,102 created_at INTEGER NOT NULL,103 sent_at INTEGER,104 attempts INTEGER NOT NULL DEFAULT 0,105 last_error TEXT106);107108CREATE INDEX IF NOT EXISTS idx_security_notifications_pending109 ON security_notifications(created_at)110 WHERE sent_at IS NULL;111112CREATE TABLE IF NOT EXISTS notification_devices (113 id TEXT PRIMARY KEY,114 pairing_code_hash TEXT UNIQUE,115 claim_hash TEXT UNIQUE,116 name TEXT,117 status TEXT NOT NULL CHECK (118 status IN ('pending', 'approved', 'active', 'disabled', 'revoked', 'expired')119 ),120 user_agent_summary TEXT,121 device_class TEXT,122 country TEXT,123 region TEXT,124 asn INTEGER,125 endpoint TEXT,126 endpoint_hash TEXT UNIQUE,127 p256dh TEXT,128 auth TEXT,129 created_at INTEGER NOT NULL,130 pairing_expires_at INTEGER,131 claim_expires_at INTEGER,132 approved_at INTEGER,133 active_at INTEGER,134 last_attached_at INTEGER,135 last_seen_at INTEGER,136 last_success_at INTEGER,137 last_error TEXT138);139140CREATE INDEX IF NOT EXISTS idx_notification_devices_status141 ON notification_devices(status);142143CREATE TABLE IF NOT EXISTS notification_events (144 id TEXT PRIMARY KEY,145 title TEXT NOT NULL,146 body TEXT NOT NULL,147 path TEXT,148 expected_manifest_hash TEXT,149 tag TEXT,150 created_at INTEGER NOT NULL151);152153CREATE INDEX IF NOT EXISTS idx_notification_events_created154 ON notification_events(created_at);155156CREATE TABLE IF NOT EXISTS notification_deliveries (157 event_id TEXT NOT NULL REFERENCES notification_events(id) ON DELETE CASCADE,158 device_id TEXT NOT NULL REFERENCES notification_devices(id) ON DELETE CASCADE,159 status TEXT NOT NULL CHECK (status IN ('pending', 'sending', 'retry', 'sent', 'failed')),160 attempts INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),161 next_attempt_at INTEGER,162 lease_owner TEXT,163 lease_expires_at INTEGER,164 sent_at INTEGER,165 last_error TEXT,166 PRIMARY KEY (event_id, device_id)167);168169CREATE INDEX IF NOT EXISTS idx_notification_deliveries_due170 ON notification_deliveries(status, next_attempt_at);