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 max_ttl REAL CHECK (max_ttl IS NULL OR (max_ttl > 0 AND max_ttl <= 3650)),10 require_password INTEGER NOT NULL DEFAULT 0 CHECK (require_password IN (0, 1)),11 default_password_hash TEXT,12 created_at INTEGER NOT NULL13);1415INSERT OR IGNORE INTO vaults (slot, name, description, default_ttl, created_at)16VALUES (0, 'personal', 'Personal and temporary shares', 14, unixepoch());17INSERT OR IGNORE INTO vaults (slot, name, description, default_ttl, created_at)18VALUES (15, 'public', 'Durable public app origins', 0, unixepoch());1920CREATE TABLE IF NOT EXISTS vault_defaults (21 lifecycle TEXT PRIMARY KEY CHECK (lifecycle IN ('temporary', 'permanent')),22 vault_slot INTEGER NOT NULL REFERENCES vaults(slot)23);24INSERT OR IGNORE INTO vault_defaults (lifecycle, vault_slot)25SELECT 'temporary', slot FROM vaults WHERE slot = 0 AND name = 'personal';26INSERT OR IGNORE INTO vault_defaults (lifecycle, vault_slot)27SELECT 'permanent', slot FROM vaults WHERE slot = 15 AND name = 'public';2829CREATE TABLE IF NOT EXISTS sites (30 address INTEGER PRIMARY KEY CHECK (address BETWEEN 0 AND 65535),31 vault_slot INTEGER NOT NULL REFERENCES vaults(slot),32 alias TEXT,33 current_manifest TEXT NOT NULL,34 created_at INTEGER NOT NULL,35 updated_at INTEGER NOT NULL,36 expires_at INTEGER, -- unix seconds; NULL = permanent37 password_hash TEXT, -- PBKDF2 password verifier; NULL = public38 content_security_policy TEXT, -- app build CSP mirrored as a response header39 auth_version INTEGER NOT NULL DEFAULT 1, -- increment to revoke unlock cookies40 feedback INTEGER NOT NULL DEFAULT 0 CHECK (feedback IN (0, 1)), -- visitor notes; needs a password41 feedback_requested_at INTEGER, -- unix seconds of the latest owner feedback request42 feedback_submitted_at INTEGER, -- unix seconds of the latest visitor submit since that request43 feedback_callback_url TEXT, -- one-shot HTTPS callback fired on the next submit, then cleared44 UNIQUE (vault_slot, alias)45);46CREATE INDEX IF NOT EXISTS idx_sites_expiry ON sites(expires_at) WHERE expires_at IS NOT NULL;4748-- App addresses are permanent identity tombstones. Content may disappear from49-- sites, but a reservation row is never deleted or returned to the allocator.50CREATE TABLE IF NOT EXISTS app_reservations (51 address INTEGER PRIMARY KEY CHECK (address BETWEEN 0 AND 65535),52 vault_slot INTEGER NOT NULL REFERENCES vaults(slot),53 alias TEXT NOT NULL,54 created_at INTEGER NOT NULL,55 retired_at INTEGER,56 UNIQUE (vault_slot, alias)57);5859CREATE TABLE IF NOT EXISTS pushes (60 address INTEGER NOT NULL REFERENCES sites(address) ON DELETE CASCADE,61 seq INTEGER NOT NULL,62 manifest_hash TEXT NOT NULL,63 pushed_at INTEGER NOT NULL,64 note TEXT,65 PRIMARY KEY (address, seq)66);6768-- Visitor notes keyed by a page-chosen item. Cascades with the site so a reused69-- address never inherits another site's feedback.70CREATE TABLE IF NOT EXISTS site_feedback (71 address INTEGER NOT NULL REFERENCES sites(address) ON DELETE CASCADE,72 item TEXT NOT NULL,73 text TEXT NOT NULL,74 seq INTEGER NOT NULL, -- site push sequence current when the note was saved75 updated_at INTEGER NOT NULL,76 PRIMARY KEY (address, item)77);7879CREATE TABLE IF NOT EXISTS security_probes (80 bucket INTEGER NOT NULL,81 scanner_id TEXT NOT NULL,82 address INTEGER NOT NULL CHECK (address BETWEEN 0 AND 65535),83 vault_slot INTEGER NOT NULL CHECK (vault_slot BETWEEN 0 AND 15),84 is_live INTEGER NOT NULL CHECK (is_live IN (0, 1)),85 country TEXT,86 asn INTEGER,87 PRIMARY KEY (bucket, scanner_id, address)88);8990CREATE TABLE IF NOT EXISTS security_signals (91 bucket INTEGER NOT NULL,92 scanner_id TEXT NOT NULL,93 kind TEXT NOT NULL,94 PRIMARY KEY (bucket, scanner_id, kind)95);9697CREATE TABLE IF NOT EXISTS security_incidents (98 name TEXT PRIMARY KEY,99 status TEXT NOT NULL CHECK (status IN ('open', 'closed')),100 severity INTEGER NOT NULL,101 opened_at INTEGER NOT NULL,102 last_seen_at INTEGER NOT NULL,103 last_alerted_at INTEGER NOT NULL,104 quiet_windows INTEGER NOT NULL,105 peak_addresses INTEGER NOT NULL,106 total_addresses INTEGER NOT NULL,107 total_live_hits INTEGER NOT NULL,108 total_rate_limited INTEGER NOT NULL,109 vault_mask INTEGER NOT NULL110);111112CREATE TABLE IF NOT EXISTS security_notifications (113 id TEXT PRIMARY KEY,114 incident_name TEXT NOT NULL,115 action TEXT NOT NULL,116 window_bucket INTEGER NOT NULL,117 subject TEXT NOT NULL,118 text TEXT NOT NULL,119 html TEXT,120 created_at INTEGER NOT NULL,121 sent_at INTEGER,122 attempts INTEGER NOT NULL DEFAULT 0,123 last_error TEXT,124 lease_owner TEXT,125 lease_expires_at INTEGER126);127128CREATE INDEX IF NOT EXISTS idx_security_notifications_pending129 ON security_notifications(created_at)130 WHERE sent_at IS NULL;131132CREATE TABLE IF NOT EXISTS notification_devices (133 id TEXT PRIMARY KEY,134 pairing_code_hash TEXT UNIQUE,135 claim_hash TEXT UNIQUE,136 name TEXT,137 status TEXT NOT NULL CHECK (138 status IN ('pending', 'approved', 'active', 'disabled', 'revoked', 'expired')139 ),140 user_agent_summary TEXT,141 device_class TEXT,142 country TEXT,143 region TEXT,144 asn INTEGER,145 endpoint TEXT,146 endpoint_hash TEXT UNIQUE,147 p256dh TEXT,148 auth TEXT,149 created_at INTEGER NOT NULL,150 pairing_expires_at INTEGER,151 claim_expires_at INTEGER,152 approved_at INTEGER,153 active_at INTEGER,154 last_attached_at INTEGER,155 last_seen_at INTEGER,156 last_success_at INTEGER,157 last_error TEXT158);159160CREATE INDEX IF NOT EXISTS idx_notification_devices_status161 ON notification_devices(status);162163CREATE TABLE IF NOT EXISTS notification_events (164 id TEXT PRIMARY KEY,165 title TEXT NOT NULL,166 body TEXT NOT NULL,167 path TEXT,168 expected_manifest_hash TEXT,169 tag TEXT,170 created_at INTEGER NOT NULL171);172173CREATE INDEX IF NOT EXISTS idx_notification_events_created174 ON notification_events(created_at);175176CREATE TABLE IF NOT EXISTS notification_deliveries (177 event_id TEXT NOT NULL REFERENCES notification_events(id) ON DELETE CASCADE,178 device_id TEXT NOT NULL REFERENCES notification_devices(id) ON DELETE CASCADE,179 status TEXT NOT NULL CHECK (status IN ('pending', 'sending', 'retry', 'sent', 'failed')),180 attempts INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),181 next_attempt_at INTEGER,182 lease_owner TEXT,183 lease_expires_at INTEGER,184 sent_at INTEGER,185 last_error TEXT,186 PRIMARY KEY (event_id, device_id)187);188189CREATE INDEX IF NOT EXISTS idx_notification_deliveries_due190 ON notification_deliveries(status, next_attempt_at);191192-- These guards also cover concurrent API operations and non-CLI writers.193-- Bodies use SELECT RAISE(...) WHERE ... rather than CASE ... END: D1's remote statement splitter194-- ends a trigger at the first END it sees, so a CASE expression inside a body breaks195-- `wrangler d1 migrations apply --remote` with "incomplete input". Keep BEGIN uppercase and LF endings.196CREATE TRIGGER IF NOT EXISTS sites_vault_policy_insert197BEFORE INSERT ON sites BEGIN198 SELECT RAISE(ABORT, 'vault requires password protection')199 WHERE (SELECT require_password FROM vaults WHERE slot = NEW.vault_slot) = 1200 AND NEW.password_hash IS NULL;201 SELECT RAISE(ABORT, 'site expiry exceeds vault maximum TTL')202 WHERE EXISTS (SELECT 1 FROM vaults WHERE slot = NEW.vault_slot AND max_ttl IS NOT NULL203 AND (NEW.expires_at IS NULL OR NEW.expires_at > unixepoch() + round(max_ttl * 86400)));204END;205206CREATE TRIGGER IF NOT EXISTS sites_vault_policy_update207BEFORE UPDATE ON sites BEGIN208 SELECT RAISE(ABORT, 'vault requires password protection')209 WHERE (SELECT require_password FROM vaults WHERE slot = NEW.vault_slot) = 1210 AND NEW.password_hash IS NULL;211 SELECT RAISE(ABORT, 'site expiry exceeds vault maximum TTL')212 WHERE EXISTS (SELECT 1 FROM vaults WHERE slot = NEW.vault_slot AND max_ttl IS NOT NULL213 AND (NEW.expires_at IS NULL OR NEW.expires_at > unixepoch() + round(max_ttl * 86400)));214END;215216CREATE TRIGGER IF NOT EXISTS vault_policy_insert217BEFORE INSERT ON vaults BEGIN218 SELECT RAISE(ABORT, 'vault default TTL exceeds maximum TTL')219 WHERE NEW.max_ttl IS NOT NULL220 AND (COALESCE(NEW.default_ttl, 14) = 0 OR COALESCE(NEW.default_ttl, 14) > NEW.max_ttl);221END;222223CREATE TRIGGER IF NOT EXISTS vault_policy_update224BEFORE UPDATE ON vaults BEGIN225 SELECT RAISE(ABORT, 'vault default TTL exceeds maximum TTL')226 WHERE NEW.max_ttl IS NOT NULL227 AND (COALESCE(NEW.default_ttl, 14) = 0 OR COALESCE(NEW.default_ttl, 14) > NEW.max_ttl);228 SELECT RAISE(ABORT, 'protect existing sites before requiring vault passwords')229 WHERE NEW.require_password = 1230 AND EXISTS (SELECT 1 FROM sites WHERE vault_slot = OLD.slot AND password_hash IS NULL);231 SELECT RAISE(ABORT, 'shorten existing site expiry before tightening vault maximum TTL')232 WHERE NEW.max_ttl IS NOT NULL233 AND EXISTS (SELECT 1 FROM sites WHERE vault_slot = OLD.slot AND234 (expires_at IS NULL OR expires_at > unixepoch() + round(NEW.max_ttl * 86400)));235END;236237-- Defense in depth for concurrent writers. Bodies use SELECT RAISE(...) WHERE ... (no CASE/END)238-- so D1's remote statement splitter keeps each trigger whole. Keep BEGIN uppercase and LF endings.239CREATE TRIGGER IF NOT EXISTS sites_feedback_policy_insert240BEFORE INSERT ON sites BEGIN241 SELECT RAISE(ABORT, 'feedback requires password protection')242 WHERE NEW.feedback = 1 AND NEW.password_hash IS NULL;243END;244245CREATE TRIGGER IF NOT EXISTS sites_feedback_policy_update246BEFORE UPDATE ON sites BEGIN247 SELECT RAISE(ABORT, 'feedback requires password protection')248 WHERE NEW.feedback = 1 AND NEW.password_hash IS NULL;249END;250251CREATE TRIGGER IF NOT EXISTS site_feedback_item_cap252BEFORE INSERT ON site_feedback BEGIN253 SELECT RAISE(ABORT, 'feedback item limit reached')254 WHERE (SELECT COUNT(*) FROM site_feedback WHERE address = NEW.address) >= 200255 AND NOT EXISTS (SELECT 1 FROM site_feedback WHERE address = NEW.address AND item = NEW.item);256END;