Files
PhilandClaude Opus 5.5 55ce6a1fdd feat: backfeed of responses to crossposts (v2.13.0)
Likes, reposts, quotes and replies to a note's GoToSocial, Bluesky and
Threads copies are read back from the silos and stored as webmentions
(ADR-073): age-based schedule to 90 days, pruning of withdrawn responses,
#nobridge/#nobot opt-outs, admin backfill and Check now, Threads counts,
a Quotes group, local avatar copies, and delete tombstones.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-10 12:19:57 -07:00

67 lines
3.1 KiB
SQL

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