1CREATE TABLE IF NOT EXISTS notification_devices (2 id TEXT PRIMARY KEY,3 pairing_code_hash TEXT UNIQUE,4 claim_hash TEXT UNIQUE,5 name TEXT,6 status TEXT NOT NULL CHECK (7 status IN ('pending', 'approved', 'active', 'disabled', 'revoked', 'expired')8 ),9 user_agent_summary TEXT,10 device_class TEXT,11 country TEXT,12 region TEXT,13 asn INTEGER,14 endpoint TEXT,15 endpoint_hash TEXT UNIQUE,16 p256dh TEXT,17 auth TEXT,18 created_at INTEGER NOT NULL,19 pairing_expires_at INTEGER,20 claim_expires_at INTEGER,21 approved_at INTEGER,22 active_at INTEGER,23 last_attached_at INTEGER,24 last_seen_at INTEGER,25 last_success_at INTEGER,26 last_error TEXT27);2829CREATE INDEX IF NOT EXISTS idx_notification_devices_status30 ON notification_devices(status);3132CREATE TABLE IF NOT EXISTS notification_events (33 id TEXT PRIMARY KEY,34 title TEXT NOT NULL,35 body TEXT NOT NULL,36 path TEXT,37 expected_manifest_hash TEXT,38 tag TEXT,39 created_at INTEGER NOT NULL40);4142CREATE INDEX IF NOT EXISTS idx_notification_events_created43 ON notification_events(created_at);4445CREATE TABLE IF NOT EXISTS notification_deliveries (46 event_id TEXT NOT NULL REFERENCES notification_events(id) ON DELETE CASCADE,47 device_id TEXT NOT NULL REFERENCES notification_devices(id) ON DELETE CASCADE,48 status TEXT NOT NULL CHECK (status IN ('pending', 'sending', 'retry', 'sent', 'failed')),49 attempts INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),50 next_attempt_at INTEGER,51 lease_owner TEXT,52 lease_expires_at INTEGER,53 sent_at INTEGER,54 last_error TEXT,55 PRIMARY KEY (event_id, device_id)56);5758CREATE INDEX IF NOT EXISTS idx_notification_deliveries_due59 ON notification_deliveries(status, next_attempt_at);