Index: src/config/database.js =================================================================== --- src/config/database.js (revision 2165b6d3d57ceaa8b5d1f70491868482149470cf) +++ src/config/database.js (revision 7bc636b391c66ac399c33e54f7173a022c6a3cbd) @@ -17,35 +17,4 @@ db.pragma('journal_mode = WAL'); db.pragma('foreign_keys = ON'); -// With WAL + several concurrent writers (request handlers, the delivery worker, the -// background thread-crawler) a short write-lock should retry rather than throw SQLITE_BUSY. -db.pragma('busy_timeout = 5000'); // wait up to 5s for a lock instead of failing immediately -db.pragma('synchronous = NORMAL'); // safe with WAL (no torn writes); fewer fsyncs = faster writes - -/** - * Tijdstempels in EEN spelling (shaer-a937). - * - * Deze database draagt twee vormen door elkaar: '2026-08-06 09:02:01' van - * CURRENT_TIMESTAMP en '2026-08-06T02:08:01.000Z' van toISOString(). SQLite - * vergelijkt ze als TEKST, en op positie 10 staat een 'T' (0x54) tegenover een - * spatie (0x20) -- dus binnen dezelfde dag wint de ISO-vorm altijd, hoe laat hij - * ook is. Een antwoord van 02:08 kwam zo boven een like van 09:02 te staan. - * - * `NU_ISO` is wat je SCHRIJFT, `isoSql()` is waarmee je VERGELIJKT of SORTEERT. - * De twee horen bij elkaar: het eerste zorgt dat er niets nieuws bijkomt, het - * tweede dat wat er al staat toch goed op volgorde komt. - * - * isoSql valt met COALESCE terug op de RAUWE waarde: strftime geeft NULL op iets - * dat het niet als tijd herkent, en zonder die terugval zou zo'n rij uit de - * sortering vallen -- of erger, als hij ook geSELECTeerd wordt (de cursor in de - * gesprekslezing) zou de client een lege stempel terugkrijgen en zijn plek - * kwijtraken. Waar zo'n onleesbare waarde dan LANDT is onbepaald: hij wordt als - * tekst vergeleken en 'geen datum' staat nu eenmaal boven '2026-...'. Dat is de - * juiste ruil -- data die je niet begrijpt bewaar je, je gooit hem niet weg. - * - * Ze staan HIER en niet in een dienst omdat ze over de opslag gaan: elke plek - * die sorteert importeert `db` toch al uit dit bestand. - */ -export const NU_ISO = "strftime('%Y-%m-%dT%H:%M:%SZ','now')"; -export const isoSql = (expr) => `COALESCE(strftime('%Y-%m-%dT%H:%M:%SZ', ${expr}), ${expr})`; export function initializeDatabase() { @@ -65,151 +34,13 @@ // SQLite throws if the column already exists; we swallow that. ensureColumn('sites', 'enable_audio_player', 'INTEGER DEFAULT 1'); - // Eigenaarspoort (Robins wens, 18-8-2026): volgers niet automatisch - // accepteren maar door de eigenaar laten beslissen, op z'n fediverse. - // STANDAARD AAN (ook Robins wens, zelfde dag): een nieuwe of bijgewerkte - // klonkt beschermt zijn eigenaar meteen; uitzetten is de bewuste keuze. - ensureColumn('sites', 'approve_followers', 'INTEGER DEFAULT 1'); - // (Verwijderd 31-7-2026: sites.guardian_only en ap_guardian_invites hoorden - // bij de guardian-lite accounts. Bestaande installaties houden kolom en tabel - // ongebruikt; nieuwe krijgen ze niet meer.) - // FEP-633c §5.3: follows targeting a ward are held pending until its - // guardians approve (Guardian 2). Gating applies only to ward-actors. - db.exec(`CREATE TABLE IF NOT EXISTS ap_pending_follows ( - id TEXT PRIMARY KEY, - ward_slug TEXT NOT NULL, - follower_uri TEXT NOT NULL, - follower_inbox TEXT, - follower_shared_inbox TEXT, - follower_name TEXT, - follower_handle TEXT, - follower_icon TEXT, - activity_json TEXT, - quorum TEXT DEFAULT 'any', - status TEXT DEFAULT 'pending', - created_at TEXT DEFAULT CURRENT_TIMESTAMP - )`); - db.exec(`CREATE TABLE IF NOT EXISTS ap_pending_follow_approvals ( - follow_id TEXT NOT NULL, - guardian_uri TEXT NOT NULL, - decision TEXT NOT NULL, - created_at TEXT DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (follow_id, guardian_uri) - )`); - // FEP-633c §5.3, the OTHER direction (shaer-p729): a ward's own follow is - // held until its guardians approve. Deliberately not ap_pending_follows — - // that table is keyed with the ward as the TARGET ("who wants to follow me"), - // and adding a direction column would make every existing query ambiguous. - db.exec(`CREATE TABLE IF NOT EXISTS ap_pending_outgoing_follows ( - id TEXT PRIMARY KEY, - ward_slug TEXT NOT NULL, - target_uri TEXT NOT NULL, - target_inbox TEXT, - target_name TEXT, - target_handle TEXT, - target_icon TEXT, - quorum TEXT DEFAULT 'any', - status TEXT DEFAULT 'pending', - created_at TEXT DEFAULT CURRENT_TIMESTAMP - )`); - db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_ap_outgoing_follows_target - ON ap_pending_outgoing_follows(ward_slug, target_uri)`); - // Wat er gebeurd is, en waarom (shaer-p729, §4.2). Guardianship-events waren - // vluchtig: onGuardianshipEvent wekte de long-poll en stuurde eventueel een - // push, en de rest van de gebeurtenis loste op. Een weigering droeg - // `reason: 'not_a_teapot'` tot in die functie en verder niet -- de ward en - // zijn guardians hoorden het alleen doordat het aanbod uit de wachtrij - // verdween. §4.2 eist dat ze het TE HOREN krijgen, met de reden erbij. - // - // Een logboek, geen wachtrij: hier staat niets dat om een antwoord vraagt. - // Daarom hoort het ook ingeklapt te staan -- naast wat nog wel wacht, maakt - // afgelopen nieuws de open vraag onleesbaar. - db.exec(`CREATE TABLE IF NOT EXISTS ap_guardian_events ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, - kind TEXT NOT NULL, - payload TEXT, - created_at TEXT DEFAULT CURRENT_TIMESTAMP - )`); - db.exec('CREATE INDEX IF NOT EXISTS idx_ap_guardian_events_slug ON ap_guardian_events(slug, id DESC)'); - db.exec(`CREATE TABLE IF NOT EXISTS ap_outgoing_follow_approvals ( - follow_id TEXT NOT NULL, - guardian_uri TEXT NOT NULL, - decision TEXT NOT NULL, - created_at TEXT DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (follow_id, guardian_uri) - )`); - // Cross-instance follow-approval (modelled on the guardian offer): the - // guardian-side COPY of a gated follow on a REMOTE ward, forwarded here by - // the ward's server as an Offer(Follow). The decision is sent back to the - // ward's inbox. (Local wards use ap_pending_follows directly.) - db.exec(`CREATE TABLE IF NOT EXISTS ap_follow_reviews ( - id TEXT NOT NULL, - guardian_slug TEXT NOT NULL, - ward_uri TEXT NOT NULL, - ward_inbox TEXT, - follower_uri TEXT NOT NULL, - follower_handle TEXT, - follower_icon TEXT, - follow_json TEXT, - status TEXT DEFAULT 'pending', - created_at TEXT DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (guardian_slug, id) - )`); - // Guardianship Fase 2 (shaer-jdb): een doorgestuurde follow-goedkeuring draagt - // een RICHTING. Bij een inkomende is de follower iemand anders en de ward het - // doel; bij een uitgaande is de ward zelf de follower en staat het doel in het - // Follow-object. Zonder deze twee kolommen werd een uitgaande opgeslagen als - // "deze ward wil deze ward volgen" en viel het doel weg -- dan valt er niets - // zinnigs te tonen, hoe je de wachtrij ook vult. - ensureColumn('ap_follow_reviews', 'direction', "TEXT DEFAULT 'incoming'"); - ensureColumn('ap_follow_reviews', 'target_uri', 'TEXT'); - ensureColumn('ap_follow_reviews', 'target_handle', 'TEXT'); ensureColumn('sites', 'profile_photo', 'TEXT'); - // De MusicBrainz-koppeling van de artiest (shaer-mbz). Een MBID is een - // verwijzing naar hun register, geen kopie ervan -- de naam staat erbij zodat - // het beheerscherm kan tonen WAT er gekoppeld is zonder ervoor te moeten - // netwerken, en zodat een verkeerde koppeling opvalt. - ensureColumn('sites', 'mb_artist_id', 'TEXT'); - ensureColumn('sites', 'mb_artist_name', 'TEXT'); - // Een UITGAVE heeft twee dingen die een afspeellijst niet heeft (shaer-756s). - // - // release_date en niet `year`: die kolom bestaat al en blijft, maar hun - // AlbumSerializer leest `released` als een DateField. Een jaartal als - // 2024-01-01 versturen is een dag verzinnen, en dat is precies wat we bij - // artiesten en albums niet doen. Volledige datum of niets. - // - // mb_release_id is de tegenhanger van sites.mb_artist_id: dezelfde soort - // verwijzing naar MusicBrainz, een niveau lager. - // - // Ze horen ALLEEN bij kind='album'. PlaylistService dwingt dat af bij het - // opslaan -- zie daar waarom dat niet alleen in het scherm mag zitten. - ensureColumn('playlists', 'release_date', 'TEXT'); - ensureColumn('playlists', 'mb_release_id', 'TEXT'); ensureColumn('audio_tracks', 'cover_url', 'TEXT'); ensureColumn('audio_tracks', 'album', 'TEXT'); ensureColumn('users', 'reset_token', 'TEXT'); ensureColumn('users', 'reset_token_expires', 'DATETIME'); - // Google OAuth: link a Google account to a user (login via Google). - ensureColumn('users', 'google_sub', 'TEXT'); - // Read-only/viewer account: can view everything but make no changes. - ensureColumn('users', 'readonly', 'INTEGER DEFAULT 0'); - // Personal interface language (nl|en|de). Null = follow the default (site/env/browser). - ensureColumn('users', 'lang', 'TEXT'); // Site-level moderation toggle. 'trust' = auto-approve, 'moderate' = pending until reviewed. - // Circles: whether this site may appear in other sites' circles (surfacing opt-out). - ensureColumn('sites', 'allow_circle', 'INTEGER DEFAULT 1'); - - // One EXPLICIT primary/main site (= the company/label site in hub mode, - // the only site in solo) instead of the fragile "oldest = main" convention - // that was duplicated in 4 places. Backfill: mark the oldest if no primary - // site exists yet, so existing behaviour is preserved exactly. - ensureColumn('sites', 'is_primary', 'INTEGER DEFAULT 0'); - try { - const hasPrimary = db.prepare('SELECT 1 FROM sites WHERE is_primary = 1 LIMIT 1').get(); - if (!hasPrimary) { - const oldest = db.prepare('SELECT id FROM sites ORDER BY created_at ASC LIMIT 1').get(); - if (oldest) db.prepare('UPDATE sites SET is_primary = 1 WHERE id = ?').run(oldest.id); - } - } catch (e) { /* sites table still empty/absent on fresh init — ensurePrimarySite handles it */ } + ensureColumn('sites', 'comments_moderation_mode', "TEXT DEFAULT 'trust'"); + // Per-site Prutter toggle: when off, DM endpoints/UI are hidden for that site. + ensureColumn('sites', 'enable_prutter', 'INTEGER DEFAULT 1'); // v9 audit additions ————————————————————————————————————————— @@ -225,89 +56,12 @@ ensureColumn('sites', 'profile_bio', 'TEXT'); // short bio for header ensureColumn('sites', 'profile_links', 'TEXT'); // JSON array [{platform, url}] - ensureColumn('sites', 'feed_view_default', "TEXT DEFAULT 'grid'"); // timeline | grid + ensureColumn('sites', 'feed_view_default', "TEXT DEFAULT 'timeline'"); // timeline | grid ensureColumn('sites', 'feed_view_switch', 'INTEGER DEFAULT 1'); // show switcher - // Welke TWEEDE weergave deze site aanbiedt naast Grid. - // 'reader' Lezen, hele berichten, een per scherm - // 'timeline' de chronologische lijst - // 'auto' Lezen op mobiel, Tijdlijn op desktop (grens 768px) - // Lezen verving Tijdlijn, maar de tijdlijn is nooit weggehaald -- home.ejs - // rendert alle drie de secties en de CSS kiest. Dit maakt er weer een keuze - // van in plaats van een besluit voor iedereen. 'auto' kost geen JavaScript: - // het is een mediaquery, dus geen resize-afhandeling en geen flikkering. - ensureColumn('sites', 'feed_alt_view', "TEXT DEFAULT 'reader'"); // reader | timeline | auto - // Krijgt elk bericht in Lezen zijn EIGEN scherm? Aan betekent min-height:100svh - // per bericht: een kort bericht vult dan het scherm en je ziet het volgende - // pas na een snap. Uit is de stroom zoals hij nu is, bericht na bericht zonder - // lege ruimte. Stond eerst vast op AAN, is toen vast op UIT gezet omdat een - // kort bericht een halve pagina leegte gaf; nu is het een keuze. - ensureColumn('sites', 'reader_full_page', 'INTEGER DEFAULT 0'); ensureColumn('sites', 'show_search', 'INTEGER DEFAULT 1'); ensureColumn('sites', 'show_archive_link', 'INTEGER DEFAULT 1'); - // Gated feature (FEP-633c): may external (non-fediverse) embeds be shown to - // this account? NULL = auto, which means OFF for a ward and ON for anyone - // else. The guardians flip it; the gate itself lives server-side, so a ward - // never even receives the thumbnail it is not allowed to see. - ensureColumn('sites', 'external_embeds', 'INTEGER'); - // De rest van de gate-familie (shaer-ahy.1, "maak ze allemaal functioneel", - // Barts opdracht 8-8). Zelfde drietal als external_embeds: NULL is de - // automatiek (dicht voor een ward, open voor de rest), 0/1 is een besluit - // van de guardians en wint van de automatiek. - ensureColumn('sites', 'external_threads', 'INTEGER'); // replies van vreemden onder een post (shaer-9y2) - ensureColumn('sites', 'gate_replies', 'INTEGER'); // zelf antwoorden in een gesprek (shaer-r4c) - ensureColumn('sites', 'gate_images', 'INTEGER'); // afbeeldingsbijlagen (shaer-6p5) - ensureColumn('sites', 'gate_messages', 'INTEGER'); // heel Messages (shaer-3ow) - ensureColumn('sites', 'gate_compose', 'INTEGER'); // zelf posten, de (+) kaart (shaer-qgev) - ensureColumn('sites', 'gate_music', 'INTEGER'); // audiobijlagen (shaer-rmz) - ensureColumn('sites', 'gate_quote_cards', 'INTEGER'); // ingebedde quote-kaarten (shaer-mls) - ensureColumn('sites', 'gate_custom_emoji', 'INTEGER'); // FEP-9098 emoji-plaatjes (shaer-ytw) - ensureColumn('sites', 'gate_account_move', 'INTEGER'); // FEP-7628 Move (shaer-tge) - // Wie de ward ZELF mag volgen (shaer-p729): de tegenhanger van shaer:follows, - // dat over de andere richting gaat. §5.3 schrijft alleen het doorsturen voor - // van een Follow NAAR een ward; wat je verder gated is een keuze van de - // implementatie, en dit is die keuze. Verstelbaar, anders dan de inkomende - // kant: een kind dat ouder wordt hoort niet eeuwig te blijven vragen. - ensureColumn('sites', 'gate_following', 'INTEGER'); // zelf iemand volgen (shaer-p729) - // The heavier sibling (FEP-633c 5.6): may a player from outside this app run - // INSIDE it? A preview is a picture; playback hands the screen to a third - // party's engine, recommendations and all. Two settings, so the guardians can - // allow the one without the other. NULL = auto, which means off for a ward. - ensureColumn('sites', 'external_playback', 'INTEGER'); - ensureColumn('sites', 'og_theme', 'TEXT'); // OG share-card variant: NULL=auto (follow site theme) | 'light' | 'dark' - // FEP-7628: former identities this actor claims (JSON array of actor URIs). - // Publishing them as alsoKnownAs is what lets the OLD server approve a Move - // of its followers to this account — the claim must be visible on OUR side. - ensureColumn('sites', 'ap_aliases', 'TEXT'); - ensureColumn('sites', 'moved_to', 'TEXT'); // FEP-7628 slice 2: waarheen dit account vertrok - // FEP-1580: staat de ingest-routine nog open? 1 = klaar (en dat is ook de - // stand van een site die nooit iets gemigreerd heeft, want er hangt niets). - // Derden pollen op deze vlag, dus hij moet ook "er valt niets te wachten" - // kunnen zeggen. - ensureColumn('sites', 'migration_complete', 'INTEGER DEFAULT 1'); // Per-post noindex + type ensureColumn('posts', 'noindex', 'INTEGER DEFAULT 0'); - ensureColumn('posts', 'publish_at', 'DATETIME'); // release planning (premium #3): scheduled go-live - ensureColumn('posts', 'fan_only', 'INTEGER DEFAULT 0'); // fan-only preview (premium #3) - ensureColumn('posts', 'nsfw', 'INTEGER DEFAULT 0'); // sensitive content → blur + click-to-reveal; fediverse sensitive - ensureColumn('posts', 'cover_video_url', 'TEXT'); // muted loop MP4 for an animated cover (Safari-smooth) - ensureColumn('posts', 'cover_alt', 'TEXT'); // alt text / description for the cover (a11y → AS2 attachment `name`) - ensureColumn('posts', 'language', 'TEXT'); // BCP-47 content language → federates as AS2 contentMap (Mastodon language filter/translate) - ensureColumn('posts', 'content_warning', 'TEXT'); // custom CW label (empty = default "Gevoelige inhoud") ensureColumn('posts', 'type', "TEXT DEFAULT 'post'"); // post | foto | video | audio - ensureColumn('posts', 'poll_json', 'TEXT'); // a poll WE host → federates as AS2 Question: {multiple,options[{name}],endTime,closed} - - // Statistics (premium module) — bare counters, cookie-free. - ensureColumn('posts', 'view_count', 'INTEGER DEFAULT 0'); // views per post - ensureColumn('audio_tracks', 'play_count', 'INTEGER DEFAULT 0'); // plays per track - ensureColumn('audio_tracks', 'downloadable', 'INTEGER DEFAULT 0'); // download-for-email (premium #2) - ensureColumn('audio_tracks', 'credit', 'TEXT'); // owner/credit (copyright holder) - ensureColumn('audio_tracks', 'license', 'TEXT'); // license (e.g. "CC BY 4.0", "All rights reserved") - ensureColumn('audio_tracks', 'link_spotify', 'TEXT'); // "open in" links per track - ensureColumn('audio_tracks', 'link_youtube', 'TEXT'); - ensureColumn('audio_tracks', 'link_soundcloud', 'TEXT'); - // Per-track: federate the actual audio file as an AS2 Audio attachment so it plays inline - // in EVERY fediverse client (incl. the Mastodon apps). Default 0 = gated (web player only, - // file not exposed). Opt-in 1 = the file is served ungated + shared on the fediverse. - ensureColumn('audio_tracks', 'fedi_open', 'INTEGER DEFAULT 0'); // Playlists (v9 feature) — first-class entity. CREATE IF NOT EXISTS is @@ -337,832 +91,4 @@ ON playlist_tracks(playlist_id, position); `); - - // Global app settings (key/value singleton). One instance is one owner, so - // there is no tenancy mode here anymore — see SettingsService. - // Oudere installaties dragen nog een dode rij key='tenancy' ('solo' of - // 'circle'). Niets leest hem; bewust laten staan (shaer-x7c0) in plaats van - // er opruimcode voor te schrijven die na één ronde zelf dood is. - db.exec(` - CREATE TABLE IF NOT EXISTS app_settings ( - key TEXT PRIMARY KEY, - value TEXT, - updated_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - `); - - // ── Statistics (premium) — cookie-free ────────────────────── - // stat_daily: pageview count per day per site (bare counter). - // stat_visitor_day: one row per UNIQUE visitor hash per day per site - // (sha256 of IP+UA+day-salt; the salt rotates daily and is never stored - // → no persistent identifier, no cookie, no consent required). - db.exec(` - CREATE TABLE IF NOT EXISTS stat_daily ( - site_id TEXT NOT NULL, - day TEXT NOT NULL, - pageviews INTEGER NOT NULL DEFAULT 0, - PRIMARY KEY (site_id, day) - ); - CREATE TABLE IF NOT EXISTS stat_visitor_day ( - site_id TEXT NOT NULL, - day TEXT NOT NULL, - visitor_hash TEXT NOT NULL, - PRIMARY KEY (site_id, day, visitor_hash) - ); - CREATE INDEX IF NOT EXISTS idx_stat_visitor_day ON stat_visitor_day(site_id, day); - CREATE TABLE IF NOT EXISTS stat_referrer ( - site_id TEXT NOT NULL, - host TEXT NOT NULL, - count INTEGER NOT NULL DEFAULT 0, - PRIMARY KEY (site_id, host) - ); - `); - - // Newsletter / mailing list (premium). Subscribers per site; double opt-in when SMTP - // is configured (status 'pending' until confirmed), otherwise single opt-in ('confirmed'). - // 'unsub' = unsubscribed. token = confirm/unsubscribe key (used in email links). - db.exec(` - CREATE TABLE IF NOT EXISTS subscribers ( - id TEXT PRIMARY KEY, - site_id TEXT NOT NULL, - email TEXT NOT NULL, - status TEXT NOT NULL DEFAULT 'pending', - source TEXT DEFAULT 'widget', - token TEXT NOT NULL, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - confirmed_at DATETIME, - UNIQUE(site_id, email) - ); - CREATE INDEX IF NOT EXISTS idx_subscribers_site_status ON subscribers(site_id, status); - `); - - // Sent newsletters (history + counts). - db.exec(` - CREATE TABLE IF NOT EXISTS newsletters ( - id TEXT PRIMARY KEY, - site_id TEXT NOT NULL, - subject TEXT NOT NULL, - body TEXT NOT NULL, - sent_at DATETIME DEFAULT CURRENT_TIMESTAMP, - recipient_count INTEGER DEFAULT 0 - ); - `); - - // Show agenda (premium #8): tour dates / gigs per site. - db.exec(` - CREATE TABLE IF NOT EXISTS shows ( - id TEXT PRIMARY KEY, - site_id TEXT NOT NULL, - date TEXT NOT NULL, - time TEXT, - city TEXT NOT NULL, - venue TEXT, - country TEXT, - ticket_url TEXT, - notes TEXT, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE INDEX IF NOT EXISTS idx_shows_site_date ON shows(site_id, date); - `); - - // Link-in-bio click statistics (premium #6). One counter per (site, url); the - // link-in-bio page links via /links/go/:i which counts the click and redirects. - db.exec(` - CREATE TABLE IF NOT EXISTS link_clicks ( - site_id TEXT NOT NULL, - url TEXT NOT NULL, - clicks INTEGER DEFAULT 0, - updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (site_id, url) - ); - `); - - - // ── ActivityPub (fediverse bridge) ────────────────────────── - // RSA keypair per actor (Mastodon-compatible HTTP Signatures; separate from - // the Cirkels Ed25519 keys). ap_followers = remote AP actors following us. - db.exec(` - CREATE TABLE IF NOT EXISTS ap_keys ( - slug TEXT PRIMARY KEY, - public_pem TEXT NOT NULL, - private_pem TEXT NOT NULL, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); -- LUISTERAARS (shaer-0nh). Wie de BIBLIOTHEEK volgt, niet de actor. - -- - -- Een eigen tabel en niet een vlag op ap_followers, en dat is met opzet: - -- deze accounts horen onze gewone posts NIET te krijgen. Zolang ze in een - -- andere tabel staan kan een bezorging ze niet per ongeluk meenemen -- een - -- vlag die iemand vergeet te filteren zou dat wel doen, en dan komt de - -- Krant van een site bij mensen die alleen muziek wilden. - CREATE TABLE IF NOT EXISTS ap_library_followers ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, - actor_uri TEXT NOT NULL, - inbox TEXT, - shared_inbox TEXT, - name TEXT, - handle TEXT, - icon TEXT, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - last_delivery_at DATETIME, - last_error_at DATETIME, - UNIQUE (slug, actor_uri) - ); - - -- OpenWebAuth (FEP-61cf): eenmalige tokens waarmee een BEZOEKER van elders - -- bewijst wie hij is. Klonkt is hier de 'target instance': we hebben nooit - -- iemands prive-sleutel nodig, alleen zijn publieke -- dus staat hier ook - -- geen geheim van een ander in. - -- - -- Het token is kort houdbaar (minuten, zie OpenWebAuthService) en gaat na - -- inwisselen meteen weg: eenmalig is de hele bedoeling. De FEP noemt de - -- opruiming expliciet als DoS-verdediging -- zonder vervaltijd vult iemand - -- deze tabel met tokens die hij nooit inwisselt. - CREATE TABLE IF NOT EXISTS owa_tokens ( - token TEXT PRIMARY KEY, - actor_uri TEXT NOT NULL, - created_at INTEGER NOT NULL -- ms sinds epoch - ); - CREATE INDEX IF NOT EXISTS idx_owa_tokens_created ON owa_tokens(created_at); - - CREATE TABLE IF NOT EXISTS ap_followers ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, - actor_uri TEXT NOT NULL, - inbox TEXT, - shared_inbox TEXT, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(slug, actor_uri) - ); - CREATE INDEX IF NOT EXISTS idx_ap_followers_slug ON ap_followers(slug); - CREATE TABLE IF NOT EXISTS ap_interactions ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - kind TEXT NOT NULL, -- 'reply' | 'like' | 'announce' - post_id TEXT NOT NULL, - object_uri TEXT NOT NULL DEFAULT '', -- remote note id (reply) or '' (like/announce) - actor_uri TEXT NOT NULL, - actor_name TEXT, - actor_handle TEXT, - actor_url TEXT, - actor_icon TEXT, - content TEXT, -- sanitized HTML (reply) - published TEXT, - parent_uri TEXT, -- the note this reply replies to (for nesting) - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(kind, post_id, actor_uri, object_uri) - ); - CREATE INDEX IF NOT EXISTS idx_ap_inter_post ON ap_interactions(post_id, kind); - -- Moderation tombstones: object URIs the site owner removed. Checked at ingest - -- (handleInbox) AND by the thread-crawler, so a removed reply never comes back - -- via thread-filling. Private notes can't be flagged via authorize_interaction - -- (their fetch 401s), so owner moderation acts on the locally stored copy. - CREATE TABLE IF NOT EXISTS ap_rejected_objects ( - object_uri TEXT PRIMARY KEY, - post_id TEXT, - reason TEXT, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - -- ActivityPub C2S (client-to-server): OAuth 2.0 for native/web clients (Shaer). - -- Public clients + PKCE (RFC 8252); tokens stored hashed; token is per user+site. - CREATE TABLE IF NOT EXISTS oauth_clients ( - client_id TEXT PRIMARY KEY, - client_name TEXT, - redirect_uris TEXT NOT NULL, -- JSON array - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE TABLE IF NOT EXISTS oauth_codes ( - code TEXT PRIMARY KEY, - client_id TEXT NOT NULL, - user_id TEXT NOT NULL, - site_slug TEXT NOT NULL, - redirect_uri TEXT NOT NULL, - code_challenge TEXT, -- PKCE S256 (verplicht voor public clients) - scope TEXT, - expires_at DATETIME NOT NULL, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE TABLE IF NOT EXISTS oauth_tokens ( - token_hash TEXT PRIMARY KEY, -- sha256(bearer); het token zelf slaan we nooit op - client_id TEXT NOT NULL, - user_id TEXT NOT NULL, - site_slug TEXT NOT NULL, - scope TEXT, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - last_used_at DATETIME - ); - -- Paid posts (klonkt-demo-aki): the site owner's own Patreon campaign. - -- Secrets are encrypted at rest (CryptoBox). Never reuses the instance-level - -- patreon_* settings, which are Klonkt Premium's separate license flow. - CREATE TABLE IF NOT EXISTS paid_patreon ( - site_id TEXT PRIMARY KEY, - client_id TEXT, - client_secret_enc TEXT, - campaign_id TEXT, - access_token_enc TEXT, - refresh_token_enc TEXT, - token_exp INTEGER, -- unix seconds - default_min_cents INTEGER DEFAULT 0, - updated_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - -- One row per passkey. NO patron identity is stored (design decision): - -- {passkey, site, proven cents, expiry}. Not traceable to a person. - CREATE TABLE IF NOT EXISTS paid_entitlements ( - credential_id TEXT PRIMARY KEY, -- WebAuthn credential id (opaque, base64url) - site_id TEXT NOT NULL, - public_key TEXT NOT NULL, -- COSE public key, base64url - counter INTEGER DEFAULT 0, - transports TEXT, - min_cents INTEGER DEFAULT 0, -- the amount proven at link time - expires_at INTEGER NOT NULL, -- unix seconds; re-link after - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - -- Web Push (docs/webpush-design.md): one row per browser/device the owner - -- enabled notifications on. Payloads are encrypted to p256dh/auth (RFC 8291). - CREATE TABLE IF NOT EXISTS push_subscriptions ( - endpoint TEXT PRIMARY KEY, -- push-service URL for this device - user_id TEXT NOT NULL, - p256dh TEXT NOT NULL, -- client public key - auth TEXT NOT NULL, -- client auth secret - alert_types TEXT, -- JSON {follow,reply,like,boost,dm} - ua_label TEXT, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - last_ok_at DATETIME - ); - CREATE TABLE IF NOT EXISTS ap_outbox ( - id TEXT PRIMARY KEY, -- note path segment (uuid) → /ap/notes/ - site_slug TEXT NOT NULL, - post_id TEXT NOT NULL, - post_slug TEXT, - in_reply_to TEXT, -- remote status uri we reply to - to_actor TEXT, -- remote actor uri (mentioned) - to_handle TEXT, - content TEXT NOT NULL, -- sanitized HTML of our reply - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE INDEX IF NOT EXISTS idx_ap_outbox_post ON ap_outbox(post_id); - -- Your like/boost state on a REMOTE post (the interact page), so those become toggles. - CREATE TABLE IF NOT EXISTS ap_my_reactions ( - site_slug TEXT NOT NULL, - target_uri TEXT NOT NULL, - kind TEXT NOT NULL, -- 'like' | 'boost' - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(site_slug, target_uri, kind) - ); - `); - ensureColumn('ap_interactions', 'parent_uri', 'TEXT'); // nesting (existing DBs) - ensureColumn('ap_interactions', 'acted_boost', 'INTEGER DEFAULT 0'); // owner boosted this comment (🔁) → can undo - ensureColumn('ap_interactions', 'acted_like', 'INTEGER DEFAULT 0'); // owner liked this comment (⭐) → can undo - - // Fediverse CLIENT: accounts WE follow (outbound) + the home timeline of their posts. - db.exec(` - CREATE TABLE IF NOT EXISTS ap_following ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, -- our site that follows - actor_uri TEXT NOT NULL, -- the followed account's actor id - handle TEXT, name TEXT, icon TEXT, url TEXT, - inbox TEXT, -- their inbox (for Create delivery / Undo) - follow_id TEXT, -- the Follow activity id we sent (Accept matching) - status TEXT DEFAULT 'pending', -- pending | accepted - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(slug, actor_uri) - ); - -- Antwoorden van accounts die we volgen komen gewoon binnen, ondertekend - -- door de schrijver, maar horen niet in de Krant (belongsInTimeline) en - -- werden daarna nergens bewaard. Kwam er later een doorgestuurd antwoord OP - -- zo'n bericht, dan kenden we de ouder niet en wezen we het af (shaer-e9g). - -- Alleen de URI, geen inhoud: dit voedt uitsluitend de vraag "kennen wij dit - -- bericht?". Wordt na 30 dagen gesnoeid; doorsturen gebeurt kort na het - -- antwoord, dus langer bewaren levert niets op. - CREATE TABLE IF NOT EXISTS ap_seen_notes ( - uri TEXT PRIMARY KEY, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE INDEX IF NOT EXISTS idx_ap_seen_notes_age ON ap_seen_notes(created_at); - CREATE TABLE IF NOT EXISTS ap_timeline ( - id TEXT NOT NULL, -- the remote note's AP id - slug TEXT NOT NULL, -- whose home timeline (our site) - author_uri TEXT, author_name TEXT, author_handle TEXT, author_icon TEXT, author_url TEXT, - content TEXT, url TEXT, published TEXT, media_json TEXT, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(slug, id) - ); - CREATE INDEX IF NOT EXISTS idx_ap_timeline_slug ON ap_timeline(slug, published); - -- canonicalReactionUri herleidt een permalink naar het object-id door op (slug, url) - -- te zoeken. Zonder deze index viel dat terug op idx_ap_timeline_slug, dus een scan - -- van elke rij van die slug. Dat gebeurt PER REACTIE in getInteractions, en de - -- reactie-migratie erft het in haar re-key-join, die synchroon vóór listen draait: - -- de opstartkosten waren reacties maal tijdlijnrijen. - CREATE INDEX IF NOT EXISTS idx_ap_timeline_url ON ap_timeline(slug, url); - -- FEP-1580: de vertaaltabel van een verhuizing. Per gemigreerd object waar - -- het VANDAAN kwam en welke URI het HIER kreeg. Derden lezen deze mapping en - -- werken er hun eigen inReplyTo/Like-verwijzingen mee bij; zonder deze tabel - -- is "de berichten krijgen nieuwe adressen" een permanent kapotte draad. - -- - -- De spec eist omgekeerd-chronologisch op het moment dat de kopie HIER is - -- aangemaakt (niet de oorspronkelijke publicatiedatum). Daarom sorteren we - -- op de autoincrement-id en niet op created_at: die heeft secondeprecisie, - -- en een ingest zet er tientallen per seconde in. - CREATE TABLE IF NOT EXISTS ap_migration ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, -- onze site, de DOELkant van de verhuizing - origin TEXT NOT NULL, -- object-URI op de broninstantie - target TEXT NOT NULL, -- de URI die het object hier kreeg - source_actor TEXT NOT NULL DEFAULT '', - is_public INTEGER NOT NULL DEFAULT 1, -- niet-publieke items horen niet in een publieke pagina - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(slug, origin) - ); - CREATE INDEX IF NOT EXISTS idx_ap_migration_slug ON ap_migration(slug, id DESC); - -- De Move-activities zelf, gededupliceerd. FEP-1580 wil dat deze collectie - -- een op zichzelf staand bewijs vormt voor de items in ap_migration, met een - -- FEP-8b32 integrity proof van de bron-actor plus een kopie van diens - -- actor-document. Wij bewaren allebei die stukken al (activity_json en - -- actor_json), maar Klonkt kent 8b32 nog niet: zie shaer-j1v0. Zolang dat - -- open staat is deze collectie structureel goed en niet verifieerbaar. - CREATE TABLE IF NOT EXISTS ap_moves ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, - move_id TEXT NOT NULL, -- id van de Move-activity, tevens dedup-sleutel - source_actor TEXT NOT NULL, - target_actor TEXT NOT NULL, - activity_json TEXT NOT NULL, - actor_json TEXT, -- inline kopie van het bron-actordocument - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(slug, move_id) - ); - CREATE TABLE IF NOT EXISTS ap_blocks ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, -- our site that set the block - target TEXT NOT NULL, -- actor URI (actor block) or domain (domain block) - kind TEXT NOT NULL, -- 'actor' | 'domain' - label TEXT, -- display (@handle or domain) - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(slug, target) - ); - CREATE INDEX IF NOT EXISTS idx_ap_blocks_target ON ap_blocks(target); - -- Committed guardian ↔ ward relations, one row per local side. role - -- 'ward' = the local slug is a ward of other_uri; 'guardian' = the local - -- slug guards other_uri. status is always 'accepted' here now: PENDING - -- offers live in ap_guardian_offers below (FEP-633c multi-party handshake). - CREATE TABLE IF NOT EXISTS ap_guardianships ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, -- our local site in this relation (guardianship module) - role TEXT NOT NULL, -- 'guardian' (slug guards other) | 'ward' (other guards slug) - other_uri TEXT NOT NULL, -- the counterpart actor URI (local or remote) - other_handle TEXT, -- cached @user@host for display - status TEXT NOT NULL, -- 'offered' (legacy) | 'accepted' - offer_id TEXT, -- the Offer activity id (FEP-633c section 3) - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(slug, role, other_uri) - ); - CREATE INDEX IF NOT EXISTS idx_ap_guardianships_slug ON ap_guardianships(slug, role, status); - -- The multi-party handshake (FEP-633c section 3), one row per offer this - -- instance is a party to. Mirrors the Shaer test daemon's Handshake: - -- accepts accumulate in ap_guardian_offer_accepts, and the offer commits - -- only when the candidate returns the handle after ward + candidate + at - -- least one existing guardian have accepted. - CREATE TABLE IF NOT EXISTS ap_guardian_offers ( - offer_id TEXT NOT NULL, -- the Offer activity id (minted by the candidate) - slug TEXT NOT NULL, -- the local site tracking this handshake (each party keeps its own copy) - ward_uri TEXT NOT NULL, -- the ward-to-be - candidate_uri TEXT NOT NULL, -- the guardian-candidate (fixed initiator) - existing_guardians TEXT NOT NULL DEFAULT '[]', -- JSON array of the ward's current guardian URIs - status TEXT NOT NULL DEFAULT 'pending', -- 'pending' | 'committed' | 'void' - handle TEXT, -- the escalation handle returned at commit (section 6) - ward_handle TEXT, -- cached @ward@host for display - candidate_handle TEXT, -- cached @candidate@host for display - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (slug, offer_id) - ); - CREATE INDEX IF NOT EXISTS idx_ap_guardian_offers_slug ON ap_guardian_offers(slug, status); - CREATE TABLE IF NOT EXISTS ap_guardian_offer_accepts ( - offer_id TEXT NOT NULL, -- FK to ap_guardian_offers - slug TEXT NOT NULL, -- the local site's copy of the tally - party_uri TEXT NOT NULL, -- the party who accepted (ward | candidate | an existing guardian) - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (slug, offer_id, party_uri) - ); - -- FEP-633c §5.6: a gated setting a ward's guardians decide together, which - -- has to work when they live on other servers (the ordinary case). One row - -- per guardian answer; the ward's server tallies (§3.5) and enforces. - -- The proposals themselves, so an Accept that only references the offer - -- id can still be resolved to "which feature, which value". - CREATE TABLE IF NOT EXISTS ap_gated_offers ( - offer_id TEXT PRIMARY KEY, - slug TEXT NOT NULL, -- the ward, on this server - feature TEXT NOT NULL, - value INTEGER NOT NULL, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - -- The guardian-side COPY of a gated-setting proposal on a ward, forwarded - -- here by the WARD's server (the same shape ap_follow_reviews has for a - -- gated follow). Without it a guardian on another server never learns a - -- proposal exists and can never answer it, so a threshold of two can never - -- be reached and every proposal expires. The answer goes back to the - -- ward's inbox, which tallies (5.6). - CREATE TABLE IF NOT EXISTS ap_gated_reviews ( - id TEXT NOT NULL, -- the offer id, as minted by the proposer - guardian_slug TEXT NOT NULL, -- us, one of the ward's guardians - ward_uri TEXT NOT NULL, - ward_inbox TEXT, - proposer TEXT, -- who opened it (for display) - feature TEXT NOT NULL, - value INTEGER NOT NULL, - created_at TEXT DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (guardian_slug, id) - ); - -- The PROPOSER's own record of a gated proposal it sent (5.6). Without it - -- a guardian clicks "propose", the ward's server tallies somewhere else, - -- and the proposer has nowhere to even see that something is running: the - -- status was a button caption that did not survive a page refresh. The - -- ward's server answers the Offer once the decision settles (Accept when - -- it settled on the proposed value, Reject otherwise); that answer lands - -- in status. An open row past the decision window renders as expired. - CREATE TABLE IF NOT EXISTS ap_gated_sent ( - offer_id TEXT PRIMARY KEY, -- as minted by us, the proposer - guardian_slug TEXT NOT NULL, -- us - ward_uri TEXT NOT NULL, - feature TEXT NOT NULL, - value INTEGER NOT NULL, -- what we proposed - status TEXT NOT NULL DEFAULT 'open', -- 'open' | 'accepted' | 'rejected' - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE TABLE IF NOT EXISTS ap_gated_votes ( - slug TEXT NOT NULL, -- the WARD, on this server - feature TEXT NOT NULL, -- e.g. 'shaer:externalEmbeds' - guardian_uri TEXT NOT NULL, -- who answered (must be a committed guardian) - value INTEGER NOT NULL, -- the value they voted for (0/1) - opened_at DATETIME NOT NULL, -- when this decision opened (the window start) - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (slug, feature, guardian_uri) - ); - -- Guardian availability (FEP-633c 3.6): one guardian's attention as seen - -- from one ward on this server. Never public; the ward reads it via the - -- owner-only guardians queue. One rule above all: one answer restores - -- everything, so every row here is one answer away from disappearing. - CREATE TABLE IF NOT EXISTS ap_guardian_attention ( - ward_slug TEXT NOT NULL, - guardian_uri TEXT NOT NULL, - state TEXT NOT NULL DEFAULT 'active', -- 'active' | 'away' | 'dormant' - away_until INTEGER, -- epoch ms while declared away - PRIMARY KEY (ward_slug, guardian_uri) - ); - -- The ONLY admissible dormancy evidence (3.6.2): directly addressed - -- requests that went unanswered. Calendar time alone never counts. - CREATE TABLE IF NOT EXISTS ap_attention_requests ( - ward_slug TEXT NOT NULL, - guardian_uri TEXT NOT NULL, - request_id TEXT NOT NULL, - asked_at INTEGER NOT NULL, -- epoch ms - PRIMARY KEY (ward_slug, guardian_uri, request_id) - ); - -- A lapse (3.6.3): the available co-guardians deciding to release a - -- dormant one. Irreversible, so the window always runs in full; any sign - -- of life from the target cancels it outright. - CREATE TABLE IF NOT EXISTS ap_lapses ( - id TEXT PRIMARY KEY, - ward_slug TEXT NOT NULL, - ward_uri TEXT NOT NULL, - target_uri TEXT NOT NULL, - opened_by TEXT NOT NULL, - set_json TEXT NOT NULL, -- the available set at open, target excluded - accepts_json TEXT NOT NULL DEFAULT '[]', - rejects_json TEXT NOT NULL DEFAULT '[]', - opened_at INTEGER NOT NULL, -- epoch ms - window_ms INTEGER NOT NULL, - cancelled INTEGER NOT NULL DEFAULT 0, - applied INTEGER NOT NULL DEFAULT 0, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE TABLE IF NOT EXISTS ap_delivery ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, -- our site/actor that signs the delivery - inbox TEXT NOT NULL, -- recipient inbox URL - body TEXT NOT NULL, -- the activity JSON to POST - attempts INTEGER NOT NULL DEFAULT 0, - next_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE INDEX IF NOT EXISTS idx_ap_delivery_due ON ap_delivery(next_at); - CREATE TABLE IF NOT EXISTS poll_votes ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - post_id INTEGER NOT NULL, -- our local poll post (posts.id) - actor_uri TEXT NOT NULL, -- the remote voter's AP actor URI - choice TEXT NOT NULL, -- the chosen option's name (matches poll_json options[].name) - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(post_id, actor_uri, choice) - ); - CREATE INDEX IF NOT EXISTS idx_poll_votes_post ON poll_votes(post_id); - CREATE TABLE IF NOT EXISTS ap_mentions ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, -- our mentioned site/actor - object_uri TEXT NOT NULL, -- the remote note that mentions us - note_url TEXT, -- its human URL (open/interact) - actor_uri TEXT, actor_name TEXT, actor_handle TEXT, actor_icon TEXT, actor_url TEXT, - content TEXT, -- sanitized HTML snippet of the mentioning note - published TEXT, - in_reply_to TEXT, -- de note waarop dit een antwoord is (AS2 inReplyTo) - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(slug, object_uri) - ); - CREATE INDEX IF NOT EXISTS idx_ap_mentions_slug ON ap_mentions(slug, created_at); - CREATE TABLE IF NOT EXISTS ap_reports ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, -- our site the report is about (its owner moderates) - actor_uri TEXT, -- the reporter's actor URI - actor_name TEXT, actor_handle TEXT, actor_icon TEXT, - content TEXT, -- the reason (plain text) - objects TEXT, -- JSON array of reported object URIs (our actor + statuses) - seen INTEGER DEFAULT 0, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP - ); - CREATE INDEX IF NOT EXISTS idx_ap_reports_slug ON ap_reports(slug, created_at); - `); - // "Feature" a followed account: its posts show in the local Cirkel. Heet in de - // UI "Uitgelicht" / "Featured" (tl.autoboost), en zo heet de kolom ook in - // following.csv. Niet te verwarren met `featured` op de ACTOR: dat is de - // collectie vastgezette POSTS (toot:featured), iets heel anders. - ensureColumn('ap_following', 'auto_boost', 'INTEGER DEFAULT 0'); - // A timeline post you boosted (🔁) — also shown in the Cirkel (mixed by date). - ensureColumn('ap_timeline', 'boosted', 'INTEGER DEFAULT 0'); - ensureColumn('ap_timeline', 'liked', 'INTEGER DEFAULT 0'); // a feed post you liked (⭐) → toggle - ensureColumn('ap_timeline', 'nsfw', 'INTEGER DEFAULT 0'); // remote sensitive post → blur in the Cirkel - ensureColumn('ap_timeline', 'cw', 'TEXT'); // remote content-warning text - ensureColumn('ap_timeline', 'emoji_json', 'TEXT'); // FEP-9098 custom emoji Emoji tags from the inbound note, served back as `tag` - ensureColumn('ap_timeline', 'link_json', 'TEXT'); // FEP-e232 object-link (quote/ref) tags from the inbound note, served back as `tag` - ensureColumn('ap_timeline', 'quote_json', 'TEXT'); // FEP-044f resolved quoted-post snapshot (author + content), for the embedded quote card - // FEP-044f: the fediverse object THIS post quotes, resolved once at publish - // time so buildNote (sync, also used by the outbox) needs no network. - ensureColumn('posts', 'quote_uri', 'TEXT'); // the quoted object's id - // De kaart op je EIGEN post (shaer-k3f): dezelfde snapshots die ap_timeline - // voor binnenkomende posts draagt, maar dan voor wat je zelf publiceert -- - // zonder deze twee kan de app een eigen post nooit als kaart tonen. - ensureColumn('posts', 'quote_json', 'TEXT'); // FEP-044f resolved quote snapshot - ensureColumn('posts', 'embed_json', 'TEXT'); // externe linkkaart (oEmbed/OG), thumbnail-only - ensureColumn('posts', 'quote_actor', 'TEXT'); // its author, so we can address them - ensureColumn('ap_timeline', 'embed_json', 'TEXT'); // resolved EXTERNAL embed (oEmbed/provider), thumbnail-only; gated per site (sites.external_embeds) - ensureColumn('ap_timeline', 'author_emoji_json', 'TEXT'); // FEP-9098 custom emojis in the author's display name (shaer:author.emojis) - ensureColumn('ap_timeline', 'reblog_emoji_json', 'TEXT'); // FEP-9098 custom emojis in the booster's display name (shaer:booster.emojis) - ensureColumn('ap_timeline', 'reblog_name', 'TEXT'); // a followed account boosted this → "X boosted" - ensureColumn('ap_timeline', 'reblog_handle', 'TEXT'); // the booster's @handle - ensureColumn('ap_timeline', 'reblog_icon', 'TEXT'); // the booster's avatar - ensureColumn('ap_timeline', 'poll_json', 'TEXT'); // a Question (poll): {multiple,options[{name,count}],endTime,closed,voters,voted} - - // Delivery health per follower → surface dead accounts for manual cleanup. - ensureColumn('ap_followers', 'last_delivery_at', 'DATETIME'); // last SUCCESSFUL delivery to this follower's inbox - ensureColumn('ap_followers', 'last_error_at', 'DATETIME'); // last time a delivery to it gave up (max retries) - - // ActivityPub `source` model: content_rendered = baked display HTML (#hashtags / URLs / - // @mentions linkified once at save). `content` stays the raw source used for editing and - // re-rendering. NULL on old posts → the render route bakes on the fly as a fallback. - ensureColumn('posts', 'content_rendered', 'TEXT'); - - // AP addressing of an incoming interaction: 'public' | 'unlisted' | 'followers' | 'direct', - // derived from the note's to/cc at ingest. The public post page only renders public/unlisted - // replies; followers/direct replies surface in notifications (and later Messages) with post - // context instead. Existing rows default to 'public' (historically almost all were). - ensureColumn('ap_interactions', 'visibility', "TEXT DEFAULT 'public'"); - ensureColumn('ap_interactions', 'emoji_json', 'TEXT'); // FEP-9098 custom emojis in a reply's content (messages + thread) - ensureColumn('ap_interactions', 'actor_emoji_json', 'TEXT'); // FEP-9098 custom emojis in the reply author's display name - // Rich replies: the reply's language (BCP47 code) → contentMap on the outgoing Note. - ensureColumn('ap_outbox', 'language', 'TEXT'); - // Rich replies: JSON array [{url, mediaType, name}] → `attachment` on the Note. - ensureColumn('ap_outbox', 'attachments', 'TEXT'); - ensureColumn('posts', 'ap_visibility', 'TEXT'); // public|quiet|friends|direct (C2S addressing, shaer-60b) - ensureColumn('posts', 'paid', 'INTEGER DEFAULT 0'); // paid post (klonkt-demo-aki) - ensureColumn('posts', 'paid_min_cents', 'INTEGER'); // required support; null = owner default - ensureColumn('paid_patreon', 'patreon_url', 'TEXT'); // owner's public Patreon page → "Word supporter" link (klonkt-demo-aki) - ensureColumn('ap_outbox', 'visibility', 'TEXT'); // 'direct' = private mention, never Public (shaer-tqc) - ensureColumn('ap_outbox', 'to_actors', 'TEXT'); // JSON array of recipient actor URIs for direct notes - ensureColumn('ap_outbox', 'help_request', 'INTEGER'); // FEP-633c shaer:helpRequest (ward's call for help) - // Wie er op een hulpvraag af is, en wanneer hij is afgesloten (shaer-lgo). - // Los van ap_mentions, want dit is GEDEELDE staat: elke guardian van dit kind - // heeft er een kopie van, en die komt binnen als bericht van een ander. Een - // kolom op de mention zou alleen over onszelf gaan. - // - // OPGEPIKT mag stapelen: twee mensen die tegelijk reageren op een kind dat om - // hulp vraagt is geen probleem. Twee mensen die allebei niets doen omdat de - // ander het "geclaimd" had, wel. - // - // AFGEHANDELD kent geen terugdraai. Sluiten gebeurt met een stevige - // bevestiging, en leeft de vraag daarna nog, dan wordt hij opnieuw gesteld -- - // een nieuwe hulpvraag. Zo blijft het verslag eerlijk: er wordt niets - // herschreven, er wordt toegevoegd. - db.exec(`CREATE TABLE IF NOT EXISTS ap_help_state ( - note_uri TEXT NOT NULL, - guardian_uri TEXT NOT NULL, - kind TEXT NOT NULL, -- pickup | handled - guardian_handle TEXT, - created_at TEXT DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (note_uri, guardian_uri, kind) - )`); - db.exec('CREATE INDEX IF NOT EXISTS idx_ap_help_state_note ON ap_help_state(note_uri)'); - // Een kind dat zelf om een poort vraagt (shaer-8ru). Een VRAAG, geen stem: - // pas als een guardian hem oppakt wordt het een voorstel dat langs de tally - // gaat. handled_at in plaats van verwijderen -- wat een kind gevraagd heeft - // hoort terug te vinden te zijn, ook als het antwoord nee was. - db.exec(`CREATE TABLE IF NOT EXISTS ap_gate_requests ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - slug TEXT NOT NULL, -- de guardian die hem ontving - ward_uri TEXT NOT NULL, - feature TEXT NOT NULL, - note_uri TEXT, - created_at TEXT DEFAULT CURRENT_TIMESTAMP, - handled_at TEXT, - UNIQUE (slug, ward_uri, feature, handled_at) - )`); - db.exec('CREATE INDEX IF NOT EXISTS idx_ap_gate_requests_slug ON ap_gate_requests(slug, handled_at)'); - ensureColumn('ap_mentions', 'help_request', 'INTEGER'); // inbound ward call-for-help (Guardian PWA message centre) - ensureColumn('ap_outbox', 'wave', 'INTEGER'); // FEP-633c shaer:wave (guardian -> ward nudge) - ensureColumn('ap_outbox', 'away_until', 'INTEGER'); // FEP-633c 3.6.1 shaer:away + endTime (epoch ms) - ensureColumn('ap_gated_offers', 'proposer', 'TEXT'); // who proposed (5.6): the settle-answer goes back to them - // Zou JOUW antwoord het besluit afmaken (shaer-8vt)? De telling loopt op de - // server van het kind; zonder dit veld kan een guardian elders niet weten dat - // hij de doorslag geeft. Ontbreekt hij, dan waarschuwen we -- bij twijfel. - ensureColumn('ap_gated_reviews', 'decisive', 'INTEGER'); - // Did a guardian actually say yes to this follower? That is what makes the - // mutual shortcut sound: a ward may follow back anyone its guardians already - // admitted, without asking the same question twice. Only follows that came - // through the §5.3 gate carry the mark; a free actor's followers never faced - // one. Everyone already following when this column arrives is grandfathered - // in (Barts besluit, 3-8): the rule is exact from that moment forward rather - // than retroactively suspicious of relationships that already exist. - { - const had = db.prepare("SELECT COUNT(*) AS n FROM pragma_table_info('ap_followers') WHERE name = 'gate_approved'").get(); - ensureColumn('ap_followers', 'gate_approved', 'INTEGER DEFAULT 0'); - if (!had || !had.n) { - try { db.prepare('UPDATE ap_followers SET gate_approved = 1').run(); } catch { /* table still empty on a fresh init */ } - } - } - ensureColumn('posts', 'c2s_attachments', 'TEXT'); // media a C2S Note carried (JSON [{url,mediaType,name}]); buildNote federates them - // 30-7: C2S posts briefly got their content media copied onto the cover, - // which showed the same video twice on the post page. Clear the covers that - // duplicate their own content; idempotent, only ever touches those. - try { - db.prepare("UPDATE posts SET cover_video_url = NULL WHERE cover_video_url LIKE '/media/reply-media/%' AND instr(content, cover_video_url) > 0").run(); - db.prepare("UPDATE posts SET cover_image_url = NULL WHERE cover_image_url LIKE '/media/reply-media/%' AND instr(content, cover_image_url) > 0").run(); - } catch { /* posts table absent on fresh init */ } - // Bestaande rijen naar EEN spelling (shaer-a937). De schrijfwegen leveren - // sinds deze release ISO; dit haalt na wat er in SQL-notatie is blijven - // staan, zodat de sortering ook zonder de isoSql-wikkel klopt. - // - // Alleen rijen met een spatie op positie 11 en geen 'T': dat is precies de - // CURRENT_TIMESTAMP-vorm. Idempotent -- een tweede keer draaien vindt niets - // meer -- en het raakt een ISO-stempel nooit aan. - // - // strftime geeft NULL op iets dat het niet als tijd herkent; de WHERE laat - // zulke rijen met rust, want een onleesbare stempel vervangen door NULL is - // gegevens weggooien. Ze blijven staan zoals ze stonden. - { - const kolommen = [ - ['ap_timeline', 'created_at'], ['ap_timeline', 'published'], - ['ap_mentions', 'created_at'], ['ap_mentions', 'published'], - ['ap_interactions', 'created_at'], ['ap_interactions', 'published'], - ['ap_outbox', 'created_at'], - ]; - let veranderd = 0; - for (const [tabel, kolom] of kolommen) { - try { - const r = db.prepare( - `UPDATE ${tabel} SET ${kolom} = strftime('%Y-%m-%dT%H:%M:%SZ', ${kolom}) - WHERE ${kolom} IS NOT NULL AND ${kolom} LIKE '____-__-__ %' - AND strftime('%Y-%m-%dT%H:%M:%SZ', ${kolom}) IS NOT NULL`).run(); - veranderd += r.changes; - } catch { /* tabel bestaat nog niet op een verse installatie */ } - } - if (veranderd) console.log(`🕒 tijdstempels genormaliseerd: ${veranderd} rijen`); - } - ensureColumn('ap_mentions', 'wave', 'INTEGER'); // inbound guardian wave - // FEP-633c §2.2: object hint that the author is a ward. Register-only for now; - // used later at reddings-boei / escalation routing. - ensureColumn('ap_timeline', 'has_guardians', 'INTEGER'); - ensureColumn('ap_mentions', 'has_guardians', 'INTEGER'); - // Berichten and de Krant render a post the same way, so a mention or a reply - // needs the same trimmings a timeline row already has: custom emojis, the - // media the note carried, and the quote / link-preview card. - ensureColumn('ap_mentions', 'emoji_json', 'TEXT'); // FEP-9098, in the content - ensureColumn('ap_mentions', 'actor_emoji_json', 'TEXT'); // FEP-9098, in the display name - ensureColumn('ap_mentions', 'media_json', 'TEXT'); - ensureColumn('ap_mentions', 'quote_json', 'TEXT'); // FEP-044f quoted post - ensureColumn('ap_mentions', 'embed_json', 'TEXT'); // external link preview - // AS2 inReplyTo op een vermelding/bericht. Bestond hier niet, en daarmee ging - // de ouder bij het opslaan verloren: elk ANTWOORD in een gesprek kwam bij de - // client aan alsof het een gesprek begon (Robins melding, 26-8). Bestaande - // rijen blijven leeg -- die ouder is niet meer te achterhalen zonder hem - // opnieuw op te halen, en een verzonnen ouder is erger dan geen. - ensureColumn('ap_mentions', 'in_reply_to', 'TEXT'); - ensureColumn('ap_interactions', 'media_json', 'TEXT'); - ensureColumn('ap_interactions', 'quote_json', 'TEXT'); - ensureColumn('ap_interactions', 'embed_json', 'TEXT'); - ensureColumn('ap_followers', 'name', 'TEXT'); // cached display name (shaer-aa3) - ensureColumn('ap_followers', 'handle', 'TEXT'); // @user@host - ensureColumn('ap_followers', 'icon', 'TEXT'); // avatar URL - feedStateTriggers(); -} - -/** - * Wat er met een tijdlijn gebeurd is, op één plek (shaer-n05). - * - * De inbox-lezing voegt vier bronnen samen. De vraag "is er iets veranderd" werd - * eerst beantwoord met MAX(rowid) over die vier -- een TOEVALLIGE eigenschap van - * de tabellen, geen feit dat ergens is opgeschreven. Dat gaf precies de gebreken - * die je van zo'n afleiding verwacht: bewerkingen en verwijderingen bewogen hem - * niet, en hij kon achteruit lopen. Dezelfde fout als reacties uitlezen uit - * ap_timeline.liked (shaer-9e9). - * - * Nu één rij per bericht per tijdlijn, met een oplopende `rev` en `kind`. Dat - * beantwoordt drie vragen die anders drie eigen oplossingen zouden krijgen: - * is er iets veranderd sinds N, wát is er veranderd, en is dit bericht bewerkt. - * - * Bijgehouden door TRIGGERS en niet door de aanroepende code, om dezelfde reden - * dat er geen gebeurtenis-emitter is: een trigger zit in de database, dus geen - * enkel codepad kan hem vergeten. De prijs is onzichtbare logica -- wie alleen de - * JavaScript leest ziet niet waarom deze tabel vult. Vandaar dat ze hier staan, - * bij de tabel, en niet verspreid. - * - * Let op de `UPDATE OF`-kolomlijsten: die zijn niet decoratief. Een like schrijft - * ap_timeline.liked en een 🔁 schrijft .boosted; zonder die afbakening zou je - * eigen like het bericht als BEWERKT merken en elke wachtende client wekken. - */ -function feedStateTriggers() { - try { - db.exec(` - -- Tot waar jij een gesprek gelezen hebt (shaer-frontend-3tx). - -- - -- Een MARKERING, geen teller: het aantal ongelezen berichten is een - -- COUNT over de berichten die na deze cursor komen. Een opgeslagen - -- getal zou opgehoogd, verlaagd en gerepareerd moeten worden, en zou - -- blijven staan als er iets verwijderd wordt -- badge zegt 3, er is - -- niets. - -- - -- De cursor is samengesteld ('|'), dezelfde vorm als de - -- gesprekspaginering en om dezelfde reden: twee berichten in dezelfde - -- seconde is bij DM's een gesprek, geen randgeval. - CREATE TABLE IF NOT EXISTS ap_read_markers ( - slug TEXT NOT NULL, - other TEXT NOT NULL, -- de tegenpartij (actor uri) - cursor TEXT NOT NULL, - at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (slug, other) - ); - CREATE TABLE IF NOT EXISTS ap_feed_state ( - slug TEXT NOT NULL, - object_uri TEXT NOT NULL, - rev INTEGER NOT NULL, - kind TEXT NOT NULL, -- new | updated | deleted - at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (slug, object_uri) - ); - CREATE INDEX IF NOT EXISTS idx_ap_feed_state_rev ON ap_feed_state(slug, rev); - -- Eén doorlopende teller voor de hele instance. Bewust niet MAX(rev) uit de - -- tabel zelf: verdwijnt de hoogste rij, dan zou die teruglopen en denkt een - -- client dat er niets gebeurd is. - CREATE TABLE IF NOT EXISTS ap_feed_rev (n INTEGER NOT NULL); - `); - if (!db.prepare('SELECT COUNT(*) AS n FROM ap_feed_rev').get().n) { - db.prepare('INSERT INTO ap_feed_rev (n) VALUES (0)').run(); - } - // slug + object_uri verschillen per bron; de rest is voor alle vier gelijk. - const zet = (naam, gebeurtenis, tabel, slug, uri, kind, extra = '', wanneer = '') => ` - DROP TRIGGER IF EXISTS ${naam}; - CREATE TRIGGER ${naam} AFTER ${gebeurtenis} ON ${tabel}${wanneer ? ` WHEN ${wanneer}` : ''} BEGIN - UPDATE ap_feed_rev SET n = n + 1; - INSERT INTO ap_feed_state (slug, object_uri, rev, kind) - ${extra || `VALUES (${slug}, ${uri}, (SELECT n FROM ap_feed_rev), '${kind}')`} - ON CONFLICT(slug, object_uri) DO UPDATE - SET rev = excluded.rev, kind = excluded.kind, at = CURRENT_TIMESTAMP; - END;`; - const joinPosts = (uri, kind) => ` - SELECT s.slug, ${uri}, (SELECT n FROM ap_feed_rev), '${kind}' - FROM posts p JOIN sites s ON s.id = p.site_id`; - db.exec([ - zet('trg_feed_tl_ins', 'INSERT', 'ap_timeline', 'NEW.slug', 'NEW.id', 'new'), - zet('trg_feed_tl_upd', 'UPDATE OF content, media_json, nsfw, cw, url, poll_json, quote_json, embed_json', 'ap_timeline', 'NEW.slug', 'NEW.id', 'updated'), - zet('trg_feed_tl_del', 'DELETE', 'ap_timeline', 'OLD.slug', 'OLD.id', 'deleted'), - zet('trg_feed_mn_ins', 'INSERT', 'ap_mentions', 'NEW.slug', 'NEW.object_uri', 'new'), - zet('trg_feed_mn_upd', 'UPDATE OF content, media_json, quote_json, embed_json', 'ap_mentions', 'NEW.slug', 'NEW.object_uri', 'updated'), - zet('trg_feed_mn_del', 'DELETE', 'ap_mentions', 'OLD.slug', 'OLD.object_uri', 'deleted'), - zet('trg_feed_ob_ins', 'INSERT', 'ap_outbox', 'NEW.site_slug', 'NEW.id', 'new'), - zet('trg_feed_ob_upd', 'UPDATE OF content, attachments', 'ap_outbox', 'NEW.site_slug', 'NEW.id', 'updated'), - zet('trg_feed_ob_del', 'DELETE', 'ap_outbox', 'OLD.site_slug', 'OLD.id', 'deleted'), - // ap_interactions draagt geen slug: die hangt aan de POST. Vandaar de join, - // en vandaar dat deze drie niet in de gewone vorm passen. - // - // De WHEN op kind='reply' is nodig omdat deze tabel ook likes en announces - // draagt, en die schrijven object_uri = '' (zie recordInteraction). Zonder de - // WHEN bumpte elke inkomende like de rev, werd elke wachter gewekt en kreeg - // die de hele collectie opnieuw terwijl er niets aan veranderd was: precies de - // kosten die de 304 moest wegnemen. Bovendien belandde er dan een rij op de - // lege string in ap_feed_state, die feedChangesSince vervolgens uitdeelt. - // De oude cursor filterde hier wel op kind; bij ap_timeline is dit ook gedaan - // (de UPDATE OF sluit liked/boosted uit) en één tabel verder vergeten. - zet('trg_feed_ia_ins', 'INSERT', 'ap_interactions', '', '', '', `${joinPosts('NEW.object_uri', 'new')} WHERE p.id = NEW.post_id`, "NEW.kind = 'reply'"), - zet('trg_feed_ia_upd', 'UPDATE OF content, media_json, quote_json, embed_json', 'ap_interactions', '', '', '', `${joinPosts('NEW.object_uri', 'updated')} WHERE p.id = NEW.post_id`, "NEW.kind = 'reply'"), - zet('trg_feed_ia_del', 'DELETE', 'ap_interactions', '', '', '', `${joinPosts('OLD.object_uri', 'deleted')} WHERE p.id = OLD.post_id`, "OLD.kind = 'reply'"), - ].join('\n')); - } catch (e) { - // Niet fataal: zonder deze tabel valt het wachten terug op "altijd de tijd - // volmaken", en dat is traag maar niet stuk. - console.error('❌ feed-state triggers:', e.message); - } }