Files
StarPunk/migrations/012_add_syndications.sql
PhilandClaude Opus 5.5 57b02ad6c7 feat: Crosspost notes to GoToSocial (v2.1.0)
Published notes are copied to a GoToSocial (Mastodon-API) account and
edits, unpublishing and deletes follow them (ADR-065). On by default for
new notes, untickable in the editor and selectable from Micropub via
mp-syndicate-to. A background worker reconciles each (note, target)
row with atomic claims, an idempotency key and a version so edits during
a send are not lost. Synced copies are linked with u-syndication.

Also fixes the Micropub Location header and search result links, which
pointed at /notes/<slug> (404), and the nested delete form on the edit
page.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-31 12:46:26 -06:00

30 lines
1.1 KiB
SQL

-- Migration 012: Crossposting (POSSE) to syndication targets
-- Version: 2.1.0
-- Per ADR-065: Direct syndication to GoToSocial / Mastodon-API servers
--
-- One row per (note, target). The worker reconciles the remote post with the
-- note: create, update, or delete as needed. note_id has no foreign key on
-- purpose: a row must outlive a hard-deleted note so the remote post can be
-- deleted too.
CREATE TABLE IF NOT EXISTS syndications (
id INTEGER PRIMARY KEY AUTOINCREMENT,
note_id INTEGER NOT NULL,
target TEXT NOT NULL,
enabled INTEGER NOT NULL DEFAULT 1,
remote_id TEXT,
remote_url TEXT,
state TEXT NOT NULL DEFAULT 'pending'
CHECK (state IN ('pending', 'synced', 'failed')),
version INTEGER NOT NULL DEFAULT 1,
attempts INTEGER NOT NULL DEFAULT 0,
last_error TEXT,
next_attempt_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
claimed_until TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE (note_id, target)
);
CREATE INDEX IF NOT EXISTS idx_syndications_due ON syndications(state, next_attempt_at);