- architecture/: overview, technology stack, auth (OIDC + built-in IndieAuth), syndication (POSSE) rewritten; new database.md, deployment.md and indieweb-protocols.md; legacy stack marked historical - decisions/: reconstructed ADR-060 and ADR-063; 31 status corrections (superseded auth ADRs, proposed -> accepted); INDEX rebuilt - design/: reconstructed the v1.6.0 documents the webmention code cites; reports for 18 v2 releases without a design folder; INDEX rebuilt - projectplan/: BACKLOG rewritten (shipped items removed, open issues from the reviews added); RELEASE.md for v2.0.0-v2.10.1; INDEX - standards/: development setup (uv, tests, DEV_MODE), testing (CI test stage), git and release flow, versioning, coding standards, cookies - README, CLAUDE.md, .env.example (every setting the code reads, with defaults) brought up to date - 137 broken links repaired (old absolute paths, moved files); stale ADR references in code comments corrected (comments only) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
4.8 KiB
v1.6.0 Design: Webmention Database Schema
Reconstructed on 2026-11-03 from the implementation; the original v1.6.0 document was lost. Later changes: v2.6.8–v2.7.2 (see CHANGELOG). Schema from
migrations/010_add_webmentions.sqlplus014_webmention_source_details.sql(v2.8.0). The original document's story numbering is only known for Story 2.4 (reply context), cited instarpunk/reply_context.py.
Related: ADR-063, background-worker.md, display-moderation.md.
Migration 010 (v1.6.0)
notes.in_reply_to TEXT
URL the note replies to. Later migrations added like_of, repost_of
(013, v2.5.0) and bookmark_of (015, v2.9.0); Note.reacts_to is the first
of these that is set.
webmentions (received)
| Column | Notes |
|---|---|
id |
PK |
source_url, target_url |
NOT NULL; UNIQUE(source_url, target_url); target stored without fragment (v2.7.0) |
target_note_id |
FK notes(id) ON DELETE SET NULL; always set for new rows since v2.7.0 (only notes accept webmentions) |
mention_type |
reply, like, repost, bookmark, mention (default mention) |
author_name, author_url, author_photo |
From the authorship algorithm; URLs http(s) only |
content_snippet |
Sanitised HTML, ~500 chars max |
validation_status |
pending (default), valid, invalid, error |
moderation_status |
pending (default), approved, hidden, deleted |
validation_error |
Last error text, NULL when valid |
validated_at |
Last validation attempt; with validation_error IS NULL it means "was valid before" |
received_at, updated_at |
Timestamps |
source_post_url, source_published |
Migration 014 (v2.8.0): the source h-entry's u-url and dt-published (UTC) |
Indexes: target_note_id, moderation_status, validation_status,
received_at DESC.
Notes:
moderation_status = 'deleted'is allowed by the CHECK but unused: admin delete removes the row (DELETE). Validation never overwriteshiddenordeleted.- A row is shown on a note only if
validandapproved.
webmention_queue (sent)
| Column | Notes |
|---|---|
source_url |
Our note's permalink (built from SITE_URL) |
target_url |
UNIQUE(source_url, target_url) |
source_note_id |
FK notes(id) ON DELETE CASCADE; cleared (detach) before a hard delete so the re-sent rows survive |
endpoint_url |
Endpoint found by the current attempt; reset on every re-queue (no cross-attempt cache since v2.7.0) |
status |
pending, sending, sent, failed, no_endpoint |
retry_count, next_retry_at, last_error |
Retry schedule 1 h / 6 h / 24 h, failed at 4 |
created_at, started_at, sent_at |
started_at drives orphan cleanup (>5 min in sending) |
Indexes: status, next_retry_at, source_note_id.
reply_contexts (cache of what a note responds to)
| Column | Notes |
|---|---|
url |
UNIQUE; the in_reply_to / like_of / repost_of / bookmark_of URL |
title |
h-entry p-name, max 200 chars |
content_snippet |
Via extract_content |
published_at |
h-entry dt-published parsed with dateutil |
author_name, author_url, author_photo |
Via extract_author |
site_name |
First top-level h-card's name |
fetch_status |
pending, fetched, error |
fetch_error, fetched_at, created_at |
Story 2.4: reply context
starpunk/reply_context.py, called synchronously from notes.create_note
(for any response URL) and notes.update_note (when in_reply_to is set),
best-effort:
- If a row for the URL exists, return it (never refreshed).
- Fetch with
webmention_processor.fetch_source(SSRF-safe). - Non-HTML: store an
errorrow ("Not HTML content"). - Parse mf2; take the first top-level h-entry's name, content and
published time, and the author; store as
fetched(INSERT OR REPLACE). - Any failure stores an
errorrow; note creation is never affected.
Read side: webmention_queries.get_reply_context(url) for the note page
(in_reply_to h-cite), routes.public.attach_contexts for likes/reposts
(title and author lines, v2.5.0) and syndication._target_title for
crosspost text.
Known defect (found during reconstruction)
Since v2.7.0, fetch_source() returns (content, content_type, final_url),
but fetch_reply_context() still unpacks two values. The resulting
ValueError is caught by the generic handler, so every new URL gets an
error row ("Unexpected error: too many values to unpack") and no context.
Rows fetched before v2.7.0 are unaffected (they are never refreshed).
Tests replace fetch_reply_context with a stub, so this is not covered.
Later schema touching webmentions
- 012 (v2.1.0)
syndications: used to reject a note's own crossposts as sources (v2.8.0). - 013 / 015: response URL columns on
notes, which become the first outgoing webmention target.