-- Migration 016: WebSub hub (v2.10.0, ADR-071) -- Active and pending subscriptions to this site's topics CREATE TABLE IF NOT EXISTS websub_subscriptions ( id INTEGER PRIMARY KEY AUTOINCREMENT, topic TEXT NOT NULL, callback TEXT NOT NULL, secret TEXT, lease_seconds INTEGER NOT NULL, expires_at TIMESTAMP NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, verified_at TIMESTAMP, last_success_at TIMESTAMP, last_error TEXT, UNIQUE(topic, callback) ); CREATE INDEX IF NOT EXISTS idx_websub_subscriptions_topic ON websub_subscriptions(topic); -- Subscribe/unsubscribe requests waiting for verification of intent CREATE TABLE IF NOT EXISTS websub_intents ( id INTEGER PRIMARY KEY AUTOINCREMENT, topic TEXT NOT NULL, callback TEXT NOT NULL, mode TEXT NOT NULL CHECK (mode IN ('subscribe', 'unsubscribe')), secret TEXT, lease_seconds INTEGER, attempts INTEGER NOT NULL DEFAULT 0, next_attempt_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(topic, callback) ); -- Content to push: one row per subscription (built-in hub) or per topic -- (ping to an external hub, subscription_id NULL) CREATE TABLE IF NOT EXISTS websub_deliveries ( id INTEGER PRIMARY KEY AUTOINCREMENT, subscription_id INTEGER REFERENCES websub_subscriptions(id) ON DELETE CASCADE, topic TEXT NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, next_attempt_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, last_error TEXT, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE UNIQUE INDEX IF NOT EXISTS idx_websub_deliveries_subscription ON websub_deliveries(subscription_id) WHERE subscription_id IS NOT NULL; CREATE UNIQUE INDEX IF NOT EXISTS idx_websub_deliveries_ping ON websub_deliveries(topic) WHERE subscription_id IS NULL; -- Site-wide values. content_version changes whenever a public note does, so -- every gunicorn process knows its cached feed note list is stale. CREATE TABLE IF NOT EXISTS site_state ( key TEXT PRIMARY KEY, value TEXT NOT NULL ); INSERT OR IGNORE INTO site_state (key, value) VALUES ('content_version', '0');