source: Klonkt/src/config/database.js@ 859b1707

import Database from 'better-sqlite3';
import path from 'path';
import { fileURLToPath } from 'url';
import fs from 'fs';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const dbPath = process.env.DATABASE_PATH || path.join(__dirname, '../../storage/database.sqlite');

// Ensure storage directory exists
const storageDir = path.dirname(dbPath);
if (!fs.existsSync(storageDir)) {
  fs.mkdirSync(storageDir, { recursive: true });
}

// Initialize database
const db = new Database(dbPath);
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

export function initializeDatabase() {
  const tableExists = db.prepare(`
    SELECT name FROM sqlite_master WHERE type='table' AND name='users'
  `).get();

  if (!tableExists) {
    console.log('🔧 Initializing database schema...');
    const schemaPath = path.join(__dirname, '..', 'db', 'migrations', '001-init.sql');
    const schema = fs.readFileSync(schemaPath, 'utf-8');
    db.exec(schema);
    console.log('✅ Database initialized with v9-soul schema');
  }

  // Additive column migrations — safe to run every boot.
  // SQLite throws if the column already exists; we swallow that.
  ensureColumn('sites', 'enable_audio_player', 'INTEGER DEFAULT 1');
  // Guardian 2: losse guardians. Een guardian-only account is user + minimale
  // site (alleen de actor telt); de vlag houdt CMS/listings erbuiten.
  ensureColumn('sites', 'guardian_only', 'INTEGER DEFAULT 0');
  db.exec(`CREATE TABLE IF NOT EXISTS ap_guardian_invites (
    token TEXT PRIMARY KEY,
    created_by TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    used_by TEXT,
    used_at TEXT
  )`);
  // 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)
  )`);
  // 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)
  )`);
  ensureColumn('sites', 'profile_photo', '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 */ }

  // v9 audit additions —————————————————————————————————————————
  // SEO/social columns the v9 template uses (most live in 001-init.sql already
  // for fresh DBs but ensureColumn is idempotent for existing DBs).
  ensureColumn('sites', 'twitter',         'TEXT');     // @handle (with @)
  ensureColumn('sites', 'schema_type',     "TEXT DEFAULT 'Person'"); // Person|Organization
  ensureColumn('sites', 'publisher_name',  'TEXT');
  ensureColumn('sites', 'publisher_url',   'TEXT');
  ensureColumn('sites', 'publisher_logo',  'TEXT');
  ensureColumn('sites', 'profile_enabled', 'INTEGER DEFAULT 1');
  ensureColumn('sites', 'profile_name',    'TEXT');     // display name (falls back to title)
  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_switch',  'INTEGER DEFAULT 1');       // show switcher
  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');
  // 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'

  // 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
  // idempotent so it's safe to run on every boot regardless of DB age.
  db.exec(`
    CREATE TABLE IF NOT EXISTS playlists (
      id TEXT PRIMARY KEY,
      site_id TEXT NOT NULL,
      title TEXT NOT NULL,
      artist TEXT,
      year INTEGER,
      cover_url TEXT,
      kind TEXT DEFAULT 'album',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (site_id) REFERENCES sites(id)
    );
    CREATE TABLE IF NOT EXISTS playlist_tracks (
      playlist_id TEXT NOT NULL,
      track_id TEXT NOT NULL,
      position INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (playlist_id, track_id),
      FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
      FOREIGN KEY (track_id) REFERENCES audio_tracks(id) ON DELETE CASCADE
    );
    CREATE INDEX IF NOT EXISTS idx_playlist_tracks_pos
      ON playlist_tracks(playlist_id, position);
  `);

  // Global app settings (key/value singleton). Includes the tenancy mode
  // (solo = one site, hub = company site + /user/). Default = solo.
  db.exec(`
    CREATE TABLE IF NOT EXISTS app_settings (
      key TEXT PRIMARY KEY,
      value TEXT,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
  `);
  db.prepare("INSERT OR IGNORE INTO app_settings (key, value) VALUES ('tenancy', 'solo')").run();

  // ── 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
    );
    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/<id>
      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)
    );
    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);
    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,
      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.
  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
  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)
  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
  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 */ }
  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
  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
}

function ensureColumn(table, column, definition) {
  try {
    db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
    console.log(`🔧 Added column ${table}.${column}`);
  } catch (e) {
    // "duplicate column name" → already there. Anything else, surface it.
    if (!/duplicate column/i.test(e.message)) {
      console.error(`❌ ensureColumn(${table}.${column}):`, e.message);
    }
  }
}

export default db;