Files
StarPunk/docs/architecture/database.md
T
PhilandClaude Opus 5.5 742e2e3ec3 Remove the metrics dashboard and monitoring package (v2.12.0)
The in-memory metrics system covered one gunicorn process at a time, was
lost on restart and was never used. Remove /admin/metrics-dashboard,
/admin/metrics, starpunk/monitoring, the HTTP/query timing, memory monitor,
feed counters, METRICS_* settings and psutil. Keep /health, /admin/health
(without its metrics block), pool stats and the feed cache. ADR-072.

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

12 KiB
Raw Permalink Blame History

Database Architecture

Version: 2.11.0 (2026-11-03) Status: Current. Verified against a database created by create_app() with migrations 001–019.

StarPunk uses one SQLite file, starpunk.db under DATA_PATH (/data in the container), in WAL mode. Note content is not in the database: it lives in Markdown files (notes/YYYY/MM/<slug>.md) and the notes table points to them. See overview.md for how the rest of the system uses it.

How the schema is built

On every start, init_db() (starpunk/database/init.py) does two things:

  1. Runs INITIAL_SCHEMA_SQL (starpunk/database/schema.py). This is the frozen v1.0.0 baseline, written with CREATE … IF NOT EXISTS: notes, sessions, tokens, authorization_codes, auth_state and their indexes. It must not be changed; every later change is a migration.
  2. Runs run_migrations() (starpunk/migrations.py) over the SQL files in migrations/, in file-name order.

Migration runner

  • Applied migrations are recorded in schema_migrations (migration_name, applied_at).
  • Locking: all four gunicorn processes run create_app() at the same time. Each opens a direct (unpooled) connection with a 30-second busy timeout and runs BEGIN IMMEDIATE, which takes SQLite's write lock. The whole run (creating schema_migrations, checking what is pending, applying every pending migration and recording it) happens in that one transaction and is committed at the end, so a migration is applied once and atomically. Statements are executed one by one with conn.execute() (split with sqlite3.complete_statement, so trigger bodies stay whole), never with executescript(), which would commit and drop the lock part-way (the cause of the v2.8.0 deploy failure fixed in v2.8.1).
  • A process that finds the database locked retries with exponential backoff (up to 10 attempts, 120 seconds in total), then finds everything already applied and returns.
  • A failing migration rolls the whole run back and raises MigrationError, which stops the app from starting.
  • Fresh databases: when schema_migrations is empty, the runner checks each of the early migrations (001–003) against the actual schema and only records those whose effect is already present; migration 002 is reduced to creating its indexes. All later migrations run normally.

Background: ADR-031, ADR-032, ADR-033, ADR-037, ADR-041.

Migrations

File Version Change
001_add_code_verifier_to_auth_state.sql 0.8 auth_state.code_verifier (removed again by 003)
002_secure_tokens_and_authorization_codes.sql 1.0.0-rc Hashed tokens and authorization_codes (dropped by 004)
003_remove_code_verifier_from_auth_state.sql 1.0 Drops auth_state.code_verifier
004_drop_token_tables.sql 1.0 Drops tokens and authorization_codes
005_add_fts5_search.sql 1.1 notes_fts (FTS5) and the notes_fts_delete trigger
006_add_author_profile.sql 1.2 author_profile (h-card cache)
007_add_media_support.sql 1.2 media, note_media
008_add_tags.sql 1.3 tags, note_tags
009_add_media_variants.sql 1.4 media_variants
010_add_webmentions.sql 1.6 notes.in_reply_to, webmentions, webmention_queue, reply_contexts
011_oidc_login_and_indieauth_server.sql 2.0 oidc_login_state, indieauth_codes, indieauth_tokens
012_add_syndications.sql 2.1 syndications
013_add_likes_reposts.sql 2.5 notes.like_of, notes.repost_of
014_webmention_source_details.sql 2.8 webmentions.source_post_url, webmentions.source_published
015_bookmarks_and_content_format.sql 2.9 notes.bookmark_of, notes.content_format
016_websub.sql 2.10.0 websub_subscriptions, websub_intents, websub_deliveries, site_state
017_websub_hardening.sql 2.10.1 websub_intents.request_id, requested_topic, callback_ip; websub_subscriptions.callback_ip
018_foreign_key_cleanup.sql 2.11.0 Removes rows orphaned while foreign keys weren't enforced
019_shared_rate_limits.sql 2.11.0 rate_limits (counters shared by all processes)

New migrations are numbered NNN_description.sql, must be safe to run inside one transaction, and should use IF NOT EXISTS where SQLite allows it.

Tables

Content

notes: one row per note; the content is in the file at file_path.

Column Purpose
id, slug (unique), file_path (unique) Identity and location of the Markdown file (relative to DATA_PATH)
published 0 = draft, 1 = public
created_at, updated_at Timestamps (UTC)
deleted_at Set on soft delete (file moved to .trash/); cleared by undelete
content_hash SHA-256 of the file content
in_reply_to, like_of, repost_of, bookmark_of URL the note responds to; set for replies, likes, reposts, bookmarks
content_format 'html' when a Micropub client sent HTML (so q=source returns HTML); NULL means Markdown

Indexes on created_at DESC, published, slug, deleted_at, like_of.

notes_fts: FTS5 virtual table (slug unindexed, title, content; porter unicode61 tokenizer), with its shadow tables notes_fts_config, _content, _data, _docsize, _idx. Rows use the note id as rowid. Inserts and updates are done by starpunk/search.py (the database cannot read note files); the trigger notes_fts_delete removes the row when a note row is deleted. The index is rebuilt at start-up if it is empty.

tags (name normalised and unique, display_name as written) and note_tags (note_id, tag_id, unique pair, cascading deletes).

media: an uploaded image: original filename, stored_filename (UUID), path (YYYY/MM/<uuid>.<ext> under media/), mime_type, size, width, height, uploaded_at.

media_variants: resized copies per image (variant_type one of thumb, small, medium, large, original; path, dimensions, size); unique per (media_id, variant_type), deleted with the image.

note_media: attaches media to notes with display_order and caption (alt text); unique per (note_id, media_id).

Authentication

sessions: admin sessions. session_token_hash (SHA-256 of the cookie value), me, created_at, expires_at, last_used_at, user_agent, ip_address.

oidc_login_state: one row per OIDC login in progress: state (primary key), nonce, code_verifier (PKCE), next_url, expires_at. Consumed once on callback.

indieauth_codes: authorization codes issued to Micropub clients: code_hash, client_id, redirect_uri, scope, code_challenge, expires_at, used_at (single use).

indieauth_tokens: access tokens: token_hash, client_id, scope, created_at, expires_at, last_used_at, revoked_at. Listed and revoked in /admin/tokens.

Legacy tables, unused: auth_state (v1 IndieLogin login state), tokens and authorization_codes (v1 token tables). No code reads or writes them. Migration 004 drops tokens and authorization_codes, but INITIAL_SCHEMA_SQL recreates them (empty) on the next start, so a running database normally contains them. They are harmless; v2 auth uses the tables above.

IndieWeb interactions

webmentions: received webmentions, one per (source_url, target_url).

Column Purpose
target_note_id The note mentioned (SET NULL if the note is deleted)
mention_type reply, like, repost, bookmark, mention
author_name, author_url, author_photo, content_snippet From the source's mf2 (content sanitised)
source_post_url, source_published The source entry's own u-url and dt-published
validation_status pending, valid, invalid, error (set by the worker)
moderation_status pending, approved, hidden, deleted (auto-approved when WEBMENTION_AUTO_APPROVE)
validation_error, validated_at, received_at, updated_at Bookkeeping

webmention_queue: outgoing webmentions, one per (source_url, target_url): source_note_id, discovered endpoint_url, status (pending, sending, sent, failed, no_endpoint), retry_count, next_retry_at, last_error, started_at, sent_at.

reply_contexts: cache of the posts that replies (and other responses) point to: url (unique), title, content_snippet, published_at, author fields, site_name, fetch_status (pending, fetched, error), fetch_error, fetched_at.

author_profile: cache of the owner's h-card discovered from ADMIN_ME: me, name, photo, url, note, rel_me_links (JSON), discovered_at, cached_until (24 hours).

Crossposting

syndications: one row per (note_id, target), where target is a configured target id (GoToSocial, Bluesky, Threads). enabled says whether the note should be on that target; remote_id and remote_url identify the remote post. state (pending, synced, failed), version (bumped on every note change, so an edit made during a send is not lost), attempts, last_error, next_attempt_at and claimed_until (a 5-minute lease that lets any gunicorn process take the row exclusively) drive the worker. See syndication-architecture.md.

WebSub

websub_subscriptions: verified subscriptions to this site's topics: topic, callback (unique pair), secret, lease_seconds, expires_at, verified_at, last_success_at, last_error, callback_ip (the address the callback resolved to, for per-address limits).

websub_intents: subscribe and unsubscribe requests waiting for verification of intent: topic, callback (unique pair), mode, secret, lease_seconds, attempts, next_attempt_at, request_id (so verifying an older request cannot consume a newer one), requested_topic (the topic as sent, echoed in verification), callback_ip.

websub_deliveries: content waiting to be pushed: one row per subscription (built-in hub) or one per topic with subscription_id NULL (a publish ping to an external hub); partial unique indexes keep at most one pending row of each kind. attempts, next_attempt_at, last_error.

site_state: key/value pairs. content_version is bumped whenever a public note changes; every gunicorn process compares it to decide whether its cached feed data is stale.

Bookkeeping

schema_migrations: applied migrations (see above).

rate_limits (v2.11.0): request counts for /webmention and /websub, one row per key (<limiter>:<ip or domain>) and time window, so every server process enforces one shared limit. Old windows are pruned as it goes.

Connections

  • Request connections: starpunk/database/pool.py. init_pool() creates one ConnectionPool per process (up to 5 idle connections, 10-second busy timeout; DB_POOL_SIZE and DB_TIMEOUT can be set only through the create_app() config dict). Connections use sqlite3.Row rows, check_same_thread=False, PRAGMA journal_mode=WAL and (since v2.11.0) PRAGMA foreign_keys=ON, so ON DELETE CASCADE/SET NULL run. get_db() stores one connection in flask.g per request or app context; on teardown it goes back to the pool, rolled back first if a transaction was left open.
  • Direct connections: the migration runner, the FTS start-up check, the webmention worker and sender, reply-context fetching and search helpers open their own sqlite3.connect(db_path) connections.
  • WAL mode lets readers carry on while one writer writes. The four gunicorn processes and the worker threads share the one file; writes are short, so contention is low.

Backups

The application does not back itself up. A consistent copy needs the database (use SQLite's online backup, e.g. sqlite3 starpunk.db ".backup …", because of WAL) together with notes/ and media/; see deployment.md.


Last Updated: 2026-11-03 (v2.10.1)