Files
PhilandClaude Opus 5.5 b520b8b4d3 feat: WebSub with a built-in hub (v2.10.0)
ADR-071. Topics (/, /feed.atom, /feed.rss, /feed.json, /likes, /tag/<name>)
advertise the hub and themselves in Link headers and embedded links. Note
changes bump a content version (fresh feeds in every process) and queue
deliveries. /websub accepts subscriptions for these topics only; the worker
verifies intent, delivers signed content with retries, and prunes expired
leases, all through the SSRF-safe pinned fetcher. WEBSUB_HUB_URL switches
to an external hub (publish pings). Also: the memory monitor no longer
stops after the first request; RSS self link is /feed.rss.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-03 10:33:41 -07:00

56 lines
2.2 KiB
SQL

-- 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');