-- Migration 020: Backfeed of responses to crossposts (v2.13.0, ADR-073) -- -- webmentions: where a response came from (origin, remote_id) and a new -- 'quote' type. SQLite can't change a CHECK constraint, so the table is -- rebuilt (as in migration 003). Nothing references webmentions. -- -- syndications: when each copy's responses were last checked and are next -- due, a lease so one gunicorn worker checks a copy, and Threads counts. CREATE TABLE webmentions_new ( id INTEGER PRIMARY KEY AUTOINCREMENT, source_url TEXT NOT NULL, target_url TEXT NOT NULL, target_note_id INTEGER, mention_type TEXT NOT NULL DEFAULT 'mention' CHECK (mention_type IN ('reply', 'like', 'repost', 'bookmark', 'mention', 'quote')), author_name TEXT, author_url TEXT, author_photo TEXT, content_snippet TEXT, validation_status TEXT NOT NULL DEFAULT 'pending' CHECK (validation_status IN ('pending', 'valid', 'invalid', 'error')), moderation_status TEXT NOT NULL DEFAULT 'pending' CHECK (moderation_status IN ('pending', 'approved', 'hidden', 'deleted')), validation_error TEXT, validated_at TIMESTAMP, received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, source_post_url TEXT, source_published TIMESTAMP, origin TEXT NOT NULL DEFAULT 'webmention', remote_id TEXT, FOREIGN KEY (target_note_id) REFERENCES notes(id) ON DELETE SET NULL, UNIQUE(source_url, target_url) ); INSERT INTO webmentions_new ( id, source_url, target_url, target_note_id, mention_type, author_name, author_url, author_photo, content_snippet, validation_status, moderation_status, validation_error, validated_at, received_at, updated_at, source_post_url, source_published ) SELECT id, source_url, target_url, target_note_id, mention_type, author_name, author_url, author_photo, content_snippet, validation_status, moderation_status, validation_error, validated_at, received_at, updated_at, source_post_url, source_published FROM webmentions; DROP TABLE webmentions; ALTER TABLE webmentions_new RENAME TO webmentions; CREATE INDEX IF NOT EXISTS idx_webmentions_target_note ON webmentions(target_note_id); CREATE INDEX IF NOT EXISTS idx_webmentions_moderation ON webmentions(moderation_status); CREATE INDEX IF NOT EXISTS idx_webmentions_validation ON webmentions(validation_status); CREATE INDEX IF NOT EXISTS idx_webmentions_received ON webmentions(received_at DESC); CREATE INDEX IF NOT EXISTS idx_webmentions_note_origin ON webmentions(target_note_id, origin); ALTER TABLE syndications ADD COLUMN backfeed_next_at TIMESTAMP; ALTER TABLE syndications ADD COLUMN backfeed_checked_at TIMESTAMP; ALTER TABLE syndications ADD COLUMN backfeed_claimed_until TIMESTAMP; ALTER TABLE syndications ADD COLUMN backfeed_attempts INTEGER NOT NULL DEFAULT 0; ALTER TABLE syndications ADD COLUMN backfeed_error TEXT; ALTER TABLE syndications ADD COLUMN backfeed_backfill INTEGER NOT NULL DEFAULT 0; ALTER TABLE syndications ADD COLUMN likes_count INTEGER; ALTER TABLE syndications ADD COLUMN reposts_count INTEGER; ALTER TABLE syndications ADD COLUMN quotes_count INTEGER;