vector_core/db/

schema.rs

//! Database schema and migrations.

pub const SQL_SCHEMA: &str = r#"
-- Profiles table (plaintext - public data)
CREATE TABLE IF NOT EXISTS profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    npub TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL DEFAULT '',
    display_name TEXT NOT NULL DEFAULT '',
    nickname TEXT NOT NULL DEFAULT '',
    lud06 TEXT NOT NULL DEFAULT '',
    lud16 TEXT NOT NULL DEFAULT '',
    banner TEXT NOT NULL DEFAULT '',
    avatar TEXT NOT NULL DEFAULT '',
    about TEXT NOT NULL DEFAULT '',
    website TEXT NOT NULL DEFAULT '',
    nip05 TEXT NOT NULL DEFAULT '',
    status_content TEXT NOT NULL DEFAULT '',
    status_url TEXT NOT NULL DEFAULT '',
    muted INTEGER NOT NULL DEFAULT 0,
    bot INTEGER NOT NULL DEFAULT 0,
    avatar_cached TEXT NOT NULL DEFAULT '',
    banner_cached TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_profiles_npub ON profiles(npub);
CREATE INDEX IF NOT EXISTS idx_profiles_name ON profiles(name);

-- Chats table (plaintext - metadata)
CREATE TABLE IF NOT EXISTS chats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_identifier TEXT UNIQUE NOT NULL,
    chat_type INTEGER NOT NULL,
    participants TEXT NOT NULL,
    last_read TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL,
    metadata TEXT NOT NULL DEFAULT '{}',
    muted INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_chats_identifier ON chats(chat_identifier);
CREATE INDEX IF NOT EXISTS idx_chats_created ON chats(created_at DESC);


-- Settings table (key-value pairs)
CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

-- Events table: flat, protocol-aligned storage for all Nostr events
CREATE TABLE IF NOT EXISTS events (
    id TEXT PRIMARY KEY,
    kind INTEGER NOT NULL,
    chat_id INTEGER NOT NULL,
    user_id INTEGER,
    content TEXT NOT NULL,
    tags TEXT NOT NULL DEFAULT '[]',
    reference_id TEXT,
    created_at INTEGER NOT NULL,
    received_at INTEGER NOT NULL,
    mine INTEGER NOT NULL DEFAULT 0,
    pending INTEGER NOT NULL DEFAULT 0,
    failed INTEGER NOT NULL DEFAULT 0,
    wrapper_event_id TEXT,
    npub TEXT,
    preview_metadata TEXT,
    FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES profiles(id) ON DELETE SET NULL
);
-- Pages a chat newest-first with no sort step, rejecting other kinds without reading the row.
CREATE INDEX IF NOT EXISTS idx_events_chat_page ON events(chat_id, created_at DESC, received_at DESC, kind);
CREATE INDEX IF NOT EXISTS idx_events_kind ON events(kind);
-- Covers the community member fold (authors + newest activity per channel) without touching rows.
CREATE INDEX IF NOT EXISTS idx_events_chat_kind_npub ON events(chat_id, kind, npub, created_at);
CREATE INDEX IF NOT EXISTS idx_events_reference ON events(reference_id) WHERE reference_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_events_wrapper ON events(wrapper_event_id) WHERE wrapper_event_id IS NOT NULL;

-- PIVX Promos table
CREATE TABLE IF NOT EXISTS pivx_promos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    gift_code TEXT NOT NULL UNIQUE,
    address TEXT NOT NULL,
    privkey_encrypted TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    claimed_at INTEGER,
    amount_piv REAL,
    status TEXT NOT NULL DEFAULT 'active'
);
CREATE INDEX IF NOT EXISTS idx_pivx_promos_code ON pivx_promos(gift_code);
CREATE INDEX IF NOT EXISTS idx_pivx_promos_address ON pivx_promos(address);
CREATE INDEX IF NOT EXISTS idx_pivx_promos_status ON pivx_promos(status);

-- Mini Apps history table
CREATE TABLE IF NOT EXISTS miniapps_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    src_url TEXT NOT NULL,
    attachment_ref TEXT,
    open_count INTEGER DEFAULT 1,
    last_opened_at INTEGER NOT NULL,
    is_favorite INTEGER NOT NULL DEFAULT 0,
    categories TEXT NOT NULL DEFAULT '',
    marketplace_id TEXT DEFAULT NULL,
    installed_version TEXT DEFAULT NULL
);

-- Mini App permissions table
CREATE TABLE IF NOT EXISTS miniapp_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    file_hash TEXT NOT NULL,
    permission TEXT NOT NULL,
    granted INTEGER NOT NULL DEFAULT 0,
    granted_at INTEGER,
    UNIQUE(file_hash, permission)
);
CREATE INDEX IF NOT EXISTS idx_miniapp_permissions_hash ON miniapp_permissions(file_hash);

-- Processed wrappers table (NIP-59 gift wrap dedup + NIP-77 negentropy)
-- Universal outer-event ledger across transports. The `transport` discriminator
-- (0 = nip17 gift-wrap, 1 = concord channel envelope, …) is added by migration 42 so the
-- dedup is shared but NIP-77 negentropy only fingerprints the nip17 (0) subset.
CREATE TABLE IF NOT EXISTS processed_wrappers (
    wrapper_id BLOB PRIMARY KEY,
    wrapper_created_at INTEGER NOT NULL DEFAULT 0
);

-- The nip17_wrap_keys vault is introduced by migration 21. The legacy MLS
-- tables (mls_wrap_keys / mls_pending_events from migrations 22/23) are dropped
-- by migration 41, so on a fresh DB they're created in order and then removed.

-- Schema migrations tracking table
CREATE TABLE IF NOT EXISTS schema_migrations (
    id INTEGER PRIMARY KEY,
    applied_at INTEGER NOT NULL
);
"#;

/// Highest migration id this build knows how to apply.
///
/// **Bump this whenever you add a migration below.** A DB carrying anything
/// above it was written by a newer Vector, so its schema holds changes this
/// build cannot see and opening it corrupts data.
///
/// Leaving it behind is the one way the guard misfires: the new migration
/// applies on first run, then this build reads its own database as newer and
/// refuses to open it. The `debug_assert` in [`run_atomic_migration`] and
/// `highest_migration_id_matches_the_runner` both catch that before release.
pub const HIGHEST_MIGRATION_ID: u32 = 94;

/// Highest migration id recorded in this DB; 0 for a fresh or pre-tracking one.
///
/// Nothing else reads the high-water mark: `schema_migrations` is a *set* of
/// applied ids and every migration probes its own id, which is exactly why an
/// older build slides past newer schema without noticing it exists.
pub fn applied_migration_high_water(conn: &rusqlite::Connection) -> u32 {
    conn.query_row("SELECT MAX(id) FROM schema_migrations", [], |row| {
        row.get::<_, Option<u32>>(0)
    })
    .ok()
    .flatten()
    .unwrap_or(0)
}

/// Check if a specific migration has already been applied
pub fn migration_applied(conn: &rusqlite::Connection, migration_id: u32) -> bool {
    conn.query_row(
        "SELECT 1 FROM schema_migrations WHERE id = ?1",
        rusqlite::params![migration_id],
        |_| Ok(())
    ).is_ok()
}

/// Mark a migration as applied (within a transaction)
pub fn mark_migration_applied(tx: &rusqlite::Transaction, migration_id: u32) -> Result<(), String> {
    let now = web_time::SystemTime::now()
        .duration_since(web_time::UNIX_EPOCH)
        .unwrap()
        .as_secs() as i64;

    tx.execute(
        "INSERT INTO schema_migrations (id, applied_at) VALUES (?1, ?2)",
        rusqlite::params![migration_id, now],
    ).map_err(|e| format!("[DB] Migration {}: Failed to record: {}", migration_id, e))?;

    Ok(())
}

/// Run a single migration atomically within a transaction.
///
/// GUARANTEES:
/// - If the migration succeeds: all changes are committed, migration is marked as applied
/// - If the migration fails: ALL changes are rolled back, database is unchanged
/// - No partial state is ever possible
///
/// This is the ONLY way migrations should be run.
fn run_atomic_migration<F>(
    conn: &mut rusqlite::Connection,
    id: u32,
    name: &str,
    migrate: F,
) -> Result<(), String>
where
    F: FnOnce(&rusqlite::Transaction) -> Result<(), String>,
{
    // A migration above the constant would apply fine on first run, then be
    // read as a downgrade on the next one and lock the user out of their own
    // account. Fires on any debug run, so it lands long before a release even
    // if nobody ran the test suite.
    debug_assert!(
        id <= HIGHEST_MIGRATION_ID,
        "migration {id} exceeds HIGHEST_MIGRATION_ID ({HIGHEST_MIGRATION_ID}); bump the constant \
         or this build will refuse the database it just wrote"
    );

    // Check if this specific migration was already applied.
    if migration_applied(conn, id) {
        return Ok(());
    }

    crate::log_info!("[DB] Migration {}: {}...", id, name);

    // Start transaction - this is the atomicity boundary
    let tx = conn.transaction()
        .map_err(|e| format!("[DB] Migration {}: Failed to start transaction: {}", id, e))?;

    // Run the migration within the transaction
    match migrate(&tx) {
        Ok(()) => {
            // Mark as applied WITHIN the same transaction
            mark_migration_applied(&tx, id)?;

            // Commit - if this fails, everything rolls back
            tx.commit()
                .map_err(|e| format!("[DB] Migration {}: Failed to commit: {}", id, e))?;

            crate::log_info!("[DB] Migration {} complete", id);
            Ok(())
        }
        Err(e) => {
            // Transaction automatically rolls back on drop
            eprintln!("[DB] Migration {} FAILED: {} - rolling back", id, e);
            Err(e)
        }
    }
}

/// Ensure a column exists on a table, adding it if missing.
/// This is a safety net for cases where ALTER TABLE inside a WAL-mode
/// transaction silently fails (e.g., other connections hold read locks).
#[allow(dead_code)]
fn ensure_column_exists(
    conn: &mut rusqlite::Connection,
    table: &str,
    column: &str,
    col_type: &str,
) -> Result<(), String> {
    let exists: bool = conn.query_row(
        &format!("SELECT COUNT(*) FROM pragma_table_info('{}') WHERE name='{}'", table, column),
        [],
        |row| row.get::<_, i32>(0),
    ).map(|c| c > 0).unwrap_or(false);

    if !exists {
        crate::log_warn!("[DB] Safety net: adding missing column {}.{}", table, column);
        conn.execute(
            &format!("ALTER TABLE {} ADD COLUMN {} {}", table, column, col_type),
            [],
        ).map_err(|e| format!("[DB] Failed to add column {}.{}: {}", table, column, e))?;
    }
    Ok(())
}

/// Run database migrations for schema updates
///
/// GUARANTEES:
/// - Each migration runs in a transaction (atomic - all or nothing)
/// - If any migration fails, changes are rolled back - no partial state
/// - Migrations are tracked in schema_migrations table (idempotent - safe to re-run)
/// - All errors are logged with [DB] prefix and propagated (no silent failures)
pub fn run_migrations(conn: &mut rusqlite::Connection) -> Result<(), String> {
    // Ensure schema_migrations table exists (bootstrap - must succeed before any migrations)
    conn.execute(
        "CREATE TABLE IF NOT EXISTS schema_migrations (
            id INTEGER PRIMARY KEY,
            applied_at INTEGER NOT NULL
        )",
        [],
    ).map_err(|e| format!("[DB] Failed to create schema_migrations table: {}", e))?;

    // =========================================================================
    // Migration 19: Create marketplace_cache table for persistent Nexus cache
    // =========================================================================
    // Caches marketplace app listings in SQLite so they survive restarts.
    // On login, the cache is loaded into MARKETPLACE_STATE immediately (so
    // permission checks work before the user visits the Nexus tab), then a
    // background network fetch refreshes the data.
    run_atomic_migration(conn, 19, "Create marketplace_cache table", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS marketplace_cache (
                id TEXT PRIMARY KEY,
                data TEXT NOT NULL,
                fetched_at INTEGER NOT NULL
            );"
        ).map_err(|e| format!("Failed to create marketplace_cache table: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 20: Add is_blocked column to profiles table
    // =========================================================================
    // Supports user blocking: blocked profiles have DM events dropped after
    // decrypt (wrapper kept for negentropy), group messages filtered in UI.
    run_atomic_migration(conn, 20, "Add is_blocked column to profiles", |tx| {
        tx.execute_batch(
            "ALTER TABLE profiles ADD COLUMN is_blocked INTEGER NOT NULL DEFAULT 0;"
        ).map_err(|e| format!("Failed to add is_blocked column: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 21: NIP-17 ephemeral wrap-key vault for deletable DMs
    // =========================================================================
    // Stores the ephemeral secp256k1 secret used to sign each kind-1059
    // gift-wrap so the user can later publish a NIP-09 deletion against
    // the wrap event ID — actually removing the message from inbox relays.
    // Encryption-at-rest is handled by Vector's per-account database
    // envelope (ChaCha20 if the account has a password; plaintext otherwise).
    // One row per published wrap; deletion uses (wrap_event_id, secret,
    // relay_urls) to issue an author-signed NIP-09 to the same relay set.
    run_atomic_migration(conn, 21, "Create nip17_wrap_keys table", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS nip17_wrap_keys (
                wrap_event_id    TEXT PRIMARY KEY,
                rumor_id         TEXT NOT NULL,
                recipient_pubkey TEXT NOT NULL,
                role             INTEGER NOT NULL,
                secret           BLOB NOT NULL,
                relay_urls       TEXT NOT NULL,
                created_at       INTEGER NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_nip17_wrap_keys_rumor ON nip17_wrap_keys(rumor_id);"
        ).map_err(|e| format!("Failed to create nip17_wrap_keys table: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 22: MLS ephemeral wrap-key vault for deletable group messages
    // =========================================================================
    // Sibling of nip17_wrap_keys: every kind-445 MLS wrapper is signed by an
    // ephemeral keypair that MDK normally discards. With our `create_message_retained`
    // patch the sender retains the secret so a later NIP-09 deletion against
    // the kind-445 event id is valid (NIP-09 requires `event.pubkey ==
    // deletion.pubkey`). One row per published wrapper; retries write new rows.
    run_atomic_migration(conn, 22, "Create mls_wrap_keys table", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS mls_wrap_keys (
                wrap_event_id TEXT PRIMARY KEY,
                message_id    TEXT NOT NULL,
                group_id      TEXT NOT NULL,
                secret        BLOB NOT NULL,
                relay_urls    TEXT NOT NULL,
                created_at    INTEGER NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_mls_wrap_keys_message ON mls_wrap_keys(message_id);
            CREATE INDEX IF NOT EXISTS idx_mls_wrap_keys_group ON mls_wrap_keys(group_id);"
        ).map_err(|e| format!("Failed to create mls_wrap_keys table: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 23: MLS pending event queue for cross-sync retry
    // =========================================================================
    // When MDK can't process an MLS event because its prerequisite commit
    // hasn't arrived, we previously marked it "processed" and advanced the
    // cursor past it — losing it forever. This table persists such events
    // so subsequent syncs can retry once the prerequisite shows up (possibly
    // from a different relay, days or weeks later). Pruned at 90 days.
    run_atomic_migration(conn, 23, "Create mls_pending_events table", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS mls_pending_events (
                event_id      TEXT PRIMARY KEY,
                group_id      TEXT NOT NULL,
                event_json    TEXT NOT NULL,
                first_seen_at INTEGER NOT NULL,
                last_retry_at INTEGER NOT NULL,
                retry_count   INTEGER NOT NULL DEFAULT 0
            );
            CREATE INDEX IF NOT EXISTS idx_mls_pending_events_group ON mls_pending_events(group_id);
            CREATE INDEX IF NOT EXISTS idx_mls_pending_events_first_seen ON mls_pending_events(first_seen_at);"
        ).map_err(|e| format!("Failed to create mls_pending_events table: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 24: Blossom capability cache — drives smart upload routing.
    // =========================================================================
    run_atomic_migration(conn, 24, "Create blossom_server_capabilities table", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS blossom_server_capabilities (
                server_url        TEXT    NOT NULL,
                mime_type         TEXT    NOT NULL,
                outcome           INTEGER NOT NULL,
                max_accepted_size INTEGER NOT NULL DEFAULT 0,
                updated_at        INTEGER NOT NULL,
                PRIMARY KEY (server_url, mime_type)
            );"
        ).map_err(|e| format!("Failed to create blossom_server_capabilities table: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 25: Add `min_rejected_size` (smallest observed 413).
    // =========================================================================
    run_atomic_migration(conn, 25, "Add min_rejected_size to blossom_server_capabilities", |tx| {
        tx.execute_batch(
            "ALTER TABLE blossom_server_capabilities ADD COLUMN min_rejected_size INTEGER;"
        ).map_err(|e| format!("Failed to add min_rejected_size column: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 26: Split capability rows by encrypted vs plaintext context.
    // Same wire MIME means different things for ciphertext vs real bytes;
    // pre-migration rows didn't track the distinction so they're dropped.
    // =========================================================================
    run_atomic_migration(conn, 26, "Add is_encrypted to capability cache PK", |tx| {
        tx.execute_batch(
            "DROP TABLE IF EXISTS blossom_server_capabilities;
             CREATE TABLE blossom_server_capabilities (
                server_url        TEXT    NOT NULL,
                mime_type         TEXT    NOT NULL,
                is_encrypted      INTEGER NOT NULL DEFAULT 0,
                outcome           INTEGER NOT NULL,
                max_accepted_size INTEGER NOT NULL DEFAULT 0,
                min_rejected_size INTEGER,
                updated_at        INTEGER NOT NULL,
                PRIMARY KEY (server_url, mime_type, is_encrypted)
             );"
        ).map_err(|e| format!("Failed to recreate blossom_server_capabilities: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 27: Mark NIP-46 remote-signer support landed.
    //
    // Settings is a KV — no schema change needed for the three new keys
    // (`signer_type`, `bunker_url`, `bunker_remote_pubkey`). Pre-bunker
    // accounts have no `signer_type` row at all; the loader treats missing
    // as `local`. We backfill an explicit `signer_type='local'` row so every
    // account has a discriminator on disk after this point — makes the
    // discriminator query a clean `=` instead of a NULL-coalesce.
    // =========================================================================
    run_atomic_migration(conn, 27, "Backfill signer_type=local for pre-NIP-46 accounts", |tx| {
        tx.execute(
            "INSERT OR IGNORE INTO settings (key, value) VALUES ('signer_type', 'local')",
            [],
        ).map_err(|e| format!("Failed to backfill signer_type: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 28: NIP-30 / NIP-51 custom emoji packs
    // =========================================================================
    // `emoji_packs`           — kind 30030 sets (own + subscribed), one row per addr.
    // `emoji_pack_items`      — flattened emoji rows per pack; CASCADE deletes follow.
    // `emoji_pack_subscriptions` — local mirror of kind 10030 `a` tags; fast startup
    //                              read without re-fetching from relays.
    run_atomic_migration(conn, 28, "Create emoji pack tables", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS emoji_packs (
                addr        TEXT PRIMARY KEY,
                pubkey      TEXT NOT NULL,
                identifier  TEXT NOT NULL,
                title       TEXT NOT NULL DEFAULT '',
                image_url   TEXT NOT NULL DEFAULT '',
                description TEXT NOT NULL DEFAULT '',
                is_own      INTEGER NOT NULL DEFAULT 0,
                updated_at  INTEGER NOT NULL,
                raw_event   TEXT NOT NULL DEFAULT ''
            );
            CREATE INDEX IF NOT EXISTS idx_emoji_packs_pubkey ON emoji_packs(pubkey);
            CREATE INDEX IF NOT EXISTS idx_emoji_packs_is_own ON emoji_packs(is_own);

            CREATE TABLE IF NOT EXISTS emoji_pack_items (
                pack_addr  TEXT NOT NULL,
                shortcode  TEXT NOT NULL,
                url        TEXT NOT NULL,
                sha256     TEXT,
                position   INTEGER NOT NULL DEFAULT 0,
                PRIMARY KEY (pack_addr, shortcode),
                FOREIGN KEY (pack_addr) REFERENCES emoji_packs(addr) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_emoji_pack_items_pack ON emoji_pack_items(pack_addr, position);

            CREATE TABLE IF NOT EXISTS emoji_pack_subscriptions (
                addr           TEXT PRIMARY KEY,
                subscribed_at  INTEGER NOT NULL
            );"
        ).map_err(|e| format!("Failed to create emoji pack tables: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 29: Add per-DM wallpaper columns to chats
    // =========================================================================
    // Wallpaper is the local cached file path (decrypted from the Blossom
    // attachment carried by the most recent kind-30078 d=vector-wallpaper rumor
    // for this chat). wallpaper_ts is the rumor created_at that produced it,
    // used for latest-write-wins on concurrent sets.
    run_atomic_migration(conn, 29, "Add wallpaper columns to chats", |tx| {
        tx.execute_batch(
            "ALTER TABLE chats ADD COLUMN wallpaper_path TEXT NOT NULL DEFAULT '';
             ALTER TABLE chats ADD COLUMN wallpaper_ts INTEGER NOT NULL DEFAULT 0;"
        ).map_err(|e| format!("Failed to add wallpaper columns: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 30: Wallpaper customisation knobs (blur + brightness)
    // =========================================================================
    // blur: integer pixels, 0..=30 (0 = no blur).
    // dim:  integer percent, 0..=100 (100 = no darkening, 0 = fully black).
    // Defaults match the values applied when a rumor arrives without the
    // optional tags — keeps older clients interoperable.
    run_atomic_migration(conn, 30, "Add wallpaper blur/dim columns to chats", |tx| {
        tx.execute_batch(
            "ALTER TABLE chats ADD COLUMN wallpaper_blur INTEGER NOT NULL DEFAULT 0;
             ALTER TABLE chats ADD COLUMN wallpaper_dim INTEGER NOT NULL DEFAULT 50;"
        ).map_err(|e| format!("Failed to add wallpaper blur/dim columns: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 31: Track wallpaper Blossom URL + uploader pubkey
    // =========================================================================
    // wallpaper_url is the Blossom blob URL of the current wallpaper.
    // wallpaper_uploader is the npub (bech32) of whoever uploaded it. Together
    // they let us DELETE the previous blob from Blossom when we (or another
    // device of ours) replace the wallpaper — only the original uploader's
    // signature satisfies the server's auth challenge.
    run_atomic_migration(conn, 31, "Add wallpaper url/uploader columns to chats", |tx| {
        tx.execute_batch(
            "ALTER TABLE chats ADD COLUMN wallpaper_url TEXT NOT NULL DEFAULT '';
             ALTER TABLE chats ADD COLUMN wallpaper_uploader TEXT NOT NULL DEFAULT '';"
        ).map_err(|e| format!("Failed to add wallpaper url/uploader columns: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 32: Drop mls_event_cursors — superseded by Total Negentropy
    // =========================================================================
    // MLS sync no longer tracks a per-group cursor. Possession of an event
    // (mls_processed_events ∪ mls_pending_events) is the negentropy fingerprint
    // set, and reconciliation derives the missing set directly. The cursor was
    // a pre-negentropy resume mechanism that could only disagree with it.
    run_atomic_migration(conn, 32, "Drop mls_event_cursors table", |tx| {
        tx.execute_batch("DROP TABLE IF EXISTS mls_event_cursors;")
            .map_err(|e| format!("Failed to drop mls_event_cursors: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // GAP: migration ids 33-39 are PERMANENTLY BURNED — do not reuse.
    // =========================================================================
    // The distributed v0.4.0 "MLS edition" shipped MLS migrations in the 33-39 range that
    // never made it into committed history (its release branch was later squashed to max 32).
    // Migrations are tracked per-id (`schema_migrations`), not by a monotonic counter, so an
    // MLS-edition DB has 33-39 recorded and would SKIP any new migration reusing those ids,
    // silently never creating the table. Community state therefore starts at 40. Never fill
    // the 33-39 gap, even though it looks tidy — those ids are spent forever.
    //
    // Migration 40: Community (Concord) protocol local state
    // =========================================================================
    // Per-account (the DB itself is account-scoped via account_dir(npub)). Holds the
    // owner/member's held secrets (server-root key, epoch-tagged channel keys), the folded
    // control-plane state, and local invite/dedup bookkeeping. Ids are hex. Authority is
    // keyless: real-npub control editions + the owner attestation, never a shared secret.
    run_atomic_migration(conn, 40, "Create community tables", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS communities (
                community_id          TEXT PRIMARY KEY,
                server_root_key       BLOB NOT NULL,
                name                  TEXT NOT NULL,
                relays                TEXT NOT NULL,
                created_at            INTEGER NOT NULL,
                description           TEXT,
                icon                  TEXT,
                banner                TEXT,
                banlist               TEXT NOT NULL DEFAULT '[]',
                banlist_at            INTEGER NOT NULL DEFAULT 0,
                owner_attestation     TEXT,
                roles                 TEXT NOT NULL DEFAULT '{}',
                roles_at              INTEGER NOT NULL DEFAULT 0,
                server_root_epoch     INTEGER NOT NULL DEFAULT 0,
                invite_registry       TEXT NOT NULL DEFAULT '[]',
                read_cut_pending      INTEGER NOT NULL DEFAULT 0,
                read_cut_target_epoch INTEGER NOT NULL DEFAULT 0,
                dissolved             INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE IF NOT EXISTS community_channels (
                channel_id              TEXT PRIMARY KEY,
                community_id            TEXT NOT NULL,
                channel_key             BLOB NOT NULL,
                epoch                   INTEGER NOT NULL,
                name                    TEXT NOT NULL,
                created_at              INTEGER NOT NULL,
                rekeyed_at_server_epoch INTEGER NOT NULL DEFAULT 0
            );
            CREATE INDEX IF NOT EXISTS idx_community_channels_community
                ON community_channels(community_id);
            CREATE TABLE IF NOT EXISTS community_message_keys (
                outer_event_id   TEXT PRIMARY KEY,
                ephemeral_secret BLOB NOT NULL,
                relays           TEXT NOT NULL,
                created_at       INTEGER NOT NULL,
                message_id       TEXT
            );
            CREATE INDEX IF NOT EXISTS idx_cmk_message_id
                ON community_message_keys(message_id);
            CREATE TABLE IF NOT EXISTS pending_community_invites (
                community_id TEXT PRIMARY KEY,
                bundle_json  TEXT NOT NULL,
                inviter_npub TEXT NOT NULL,
                received_at  INTEGER NOT NULL
            );
            CREATE TABLE IF NOT EXISTS community_public_invites (
                token        TEXT PRIMARY KEY,
                community_id TEXT NOT NULL,
                url          TEXT NOT NULL,
                expires_at   INTEGER,
                created_at   INTEGER NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_public_invites_community
                ON community_public_invites(community_id);
            CREATE TABLE IF NOT EXISTS community_edition_heads (
                community_id TEXT NOT NULL,
                entity_id    TEXT NOT NULL,
                version      INTEGER NOT NULL,
                self_hash    BLOB NOT NULL,
                inner_id     BLOB,
                epoch        INTEGER NOT NULL DEFAULT 0,
                PRIMARY KEY (community_id, entity_id)
            );
            CREATE TABLE IF NOT EXISTS community_epoch_keys (
                community_id TEXT NOT NULL,
                scope_id     TEXT NOT NULL,
                epoch        INTEGER NOT NULL,
                key          BLOB NOT NULL,
                created_at   INTEGER NOT NULL,
                PRIMARY KEY (community_id, scope_id, epoch)
            );
            CREATE TABLE IF NOT EXISTS community_invite_link_sets (
                community_id TEXT NOT NULL,
                creator      TEXT NOT NULL,
                locators     TEXT NOT NULL DEFAULT '[]',
                version      INTEGER NOT NULL DEFAULT 0,
                PRIMARY KEY (community_id, creator)
            );",
        )
        .map_err(|e| format!("Failed to create community tables: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 41: Purge legacy MLS data (MLS is fully removed)
    // =========================================================================
    // Drop the retired chat_type=1 (MlsGroup) chats + their events, then the MLS-only
    // storage tables. chat_type 2 (Community) is untouched. Runs for accounts upgrading
    // from an MLS build; a no-op on a fresh DB.
    run_atomic_migration(conn, 41, "Purge legacy MLS data", |tx| {
        tx.execute_batch(
            "DELETE FROM events WHERE chat_id IN (SELECT id FROM chats WHERE chat_type = 1);
             DELETE FROM chats WHERE chat_type = 1;
             DROP TABLE IF EXISTS mls_groups;
             DROP TABLE IF EXISTS mls_keypackages;
             DROP TABLE IF EXISTS mls_processed_events;
             DROP TABLE IF EXISTS mls_wrap_keys;
             DROP TABLE IF EXISTS mls_pending_events;",
        )
        .map_err(|e| format!("Failed to purge legacy MLS data: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 42: Make processed_wrappers a cross-transport dedup ledger
    // =========================================================================
    // A `transport` discriminator so every transport (NIP-17 DMs, Concord) shares ONE
    // outer-event dedup store, while NIP-77 negentropy keeps fingerprinting only the 'nip17'
    // subset. Existing rows are gift-wraps, so the default 0 ('nip17') is correct.
    run_atomic_migration(conn, 42, "Add transport discriminator to processed_wrappers", |tx| {
        tx.execute_batch("ALTER TABLE processed_wrappers ADD COLUMN transport INTEGER NOT NULL DEFAULT 0;")
            .map_err(|e| format!("Failed to add transport column: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 43: Persist the optional label on a minted public invite
    // =========================================================================
    // The label set at mint time rides in the relay-published bundle (join attribution) but wasn't
    // stored locally, so the owner's invite-links list had no label to show. Encrypted-at-rest like
    // the sibling columns; NULL when no label was set.
    run_atomic_migration(conn, 43, "Add label to community_public_invites", |tx| {
        tx.execute_batch("ALTER TABLE community_public_invites ADD COLUMN label TEXT;")
            .map_err(|e| format!("Failed to add label column: {}", e))?;
        Ok(())
    })?;

    // Migration 44: Per-account emoji "frecency" (most-used) table.
    // =========================================================================
    // `score` is a time-weighted log-space value: each use adds
    // 2^((t-EPOCH)/half_life), so ranking is a plain `ORDER BY score DESC` (the
    // uniform decay factor cancels) — no per-row decay math at read time. `kind`:
    // 0=unicode, 1=custom. WITHOUT ROWID + (kind,id) PK so a reuse is an in-place
    // upsert (one row per emoji), not an append.
    run_atomic_migration(conn, 44, "Create emoji_usage table", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS emoji_usage (
                kind      INTEGER NOT NULL,
                id        TEXT    NOT NULL,
                url       TEXT,
                score     REAL    NOT NULL,
                last_used INTEGER NOT NULL,
                PRIMARY KEY (kind, id)
            ) WITHOUT ROWID;
            CREATE INDEX IF NOT EXISTS idx_emoji_usage_score
                ON emoji_usage(score DESC);",
        )
        .map_err(|e| format!("Failed to create emoji_usage table: {}", e))?;
        Ok(())
    })?;

    // Migration 62: Repair — guarantee `label` exists on community_public_invites. Id 43 (which adds it)
    // was burned on DBs created from an older baseline: recorded as applied without the ALTER ever landing,
    // so `label` is silently absent and list_all_public_invites errors. Use a fresh id past every recorded
    // one (DBs already hold up to 61) and add the column only if missing, so it's a no-op where 43 worked.
    run_atomic_migration(conn, 62, "Repair: ensure label column on community_public_invites", |tx| {
        let has_label: i64 = tx
            .query_row(
                "SELECT COUNT(*) FROM pragma_table_info('community_public_invites') WHERE name = 'label'",
                [],
                |r| r.get(0),
            )
            .map_err(|e| format!("Failed to inspect community_public_invites columns: {}", e))?;
        if has_label == 0 {
            tx.execute_batch("ALTER TABLE community_public_invites ADD COLUMN label TEXT;")
                .map_err(|e| format!("Failed to add label column: {}", e))?;
        }
        Ok(())
    })?;

    // =========================================================================
    // Migration 63: Emoji pack health (revocation / durable-absence tracking)
    // =========================================================================
    // `status`: 0 = active, 1 = revoked (a deterministic tombstone was seen: an
    // empty kind-30030 replacement, or an author-signed kind-5 deletion),
    // 2 = missing (absent across enough clean relay sweeps). The miss columns
    // drive the promotion gauntlet in `emoji_packs::apply_pack_health`; a live
    // fetch resets everything back to active.
    run_atomic_migration(conn, 63, "Add health columns to emoji_packs", |tx| {
        tx.execute_batch(
            "ALTER TABLE emoji_packs ADD COLUMN status INTEGER NOT NULL DEFAULT 0;
             ALTER TABLE emoji_packs ADD COLUMN miss_count INTEGER NOT NULL DEFAULT 0;
             ALTER TABLE emoji_packs ADD COLUMN first_missed_at INTEGER NOT NULL DEFAULT 0;
             ALTER TABLE emoji_packs ADD COLUMN last_miss_counted_at INTEGER NOT NULL DEFAULT 0;
             ALTER TABLE emoji_packs ADD COLUMN status_changed_at INTEGER NOT NULL DEFAULT 0;",
        )
        .map_err(|e| format!("Failed to add emoji pack health columns: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 64: Drop orphaned pending-id event rows
    // =========================================================================
    // Mid-flight persists could land a row under a message's optimistic
    // "pending-…" id; the finalized message then saved under its REAL id,
    // orphaning the pending-keyed row as a ghost duplicate that renders on
    // reload. Rows still flagged pending/failed are live send-state (the
    // retry UI needs them) and stay.
    run_atomic_migration(conn, 64, "Drop orphaned pending-id event rows", |tx| {
        tx.execute(
            "DELETE FROM events WHERE id LIKE 'pending-%' AND pending = 0 AND failed = 0",
            [],
        )
        .map_err(|e| format!("Failed to drop orphaned pending rows: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 65: Add position to emoji pack subscriptions
    // =========================================================================
    // `subscribed_at` alone can't hold a user-defined order — save_subscriptions
    // rewrites every row with the same `now`, so ties are unordered. `position`
    // is the authoritative display order (cross-device synced via kind 10030).
    // Backfill preserves the current rowid order so existing installs don't
    // reshuffle on first launch.
    run_atomic_migration(conn, 65, "Add position to emoji pack subscriptions", |tx| {
        tx.execute_batch(
            "ALTER TABLE emoji_pack_subscriptions ADD COLUMN position INTEGER NOT NULL DEFAULT 0;
             UPDATE emoji_pack_subscriptions SET position = (
                 SELECT COUNT(*) FROM emoji_pack_subscriptions s2
                 WHERE s2.rowid < emoji_pack_subscriptions.rowid
             );",
        )
        .map_err(|e| format!("Failed to add position column: {}", e))?;
        Ok(())
    })?;

    // Migration 66: Concord v2 dual-stack columns. A community is v1 (the shipped
    // protocol) or v2 (the self-certifying-id CORD stack); the two coexist per
    // account. Existing rows default to v1. v2 stores the owner commitment inputs
    // (owner_pubkey + owner_salt reproduce the community_id) in place of v1's
    // owner_attestation; server_root_key/server_root_epoch carry the v2
    // community_root/root_epoch (same base-key role, reused columns). A channel's
    // `private` flag selects v2 keying: public channels derive from the root (no
    // stored key), private ones carry an independent key.
    run_atomic_migration(conn, 66, "Concord v2 dual-stack columns", |tx| {
        for (table, col, ddl) in [
            ("communities", "protocol", "INTEGER NOT NULL DEFAULT 1"),
            ("communities", "owner_pubkey", "TEXT"),
            ("communities", "owner_salt", "TEXT"),
            ("community_channels", "private", "INTEGER NOT NULL DEFAULT 0"),
        ] {
            // ADD COLUMN is not idempotent; tolerate a re-run (duplicate column).
            let sql = format!("ALTER TABLE {table} ADD COLUMN {col} {ddl}");
            if let Err(e) = tx.execute(&sql, []) {
                let msg = e.to_string();
                if !msg.contains("duplicate column name") {
                    return Err(format!("add {table}.{col}: {msg}"));
                }
            }
        }
        Ok(())
    })?;

    // Migration 67: the persisted v2 Guestbook — the RAW membership events (one
    // encrypted JSON blob per community; kick/snapshot validity is judged at fold
    // time against CURRENT authority, so raw events are the correct stored form)
    // plus the newest-seen cursor, so boot catches the plane up incrementally and
    // the memberlist becomes a local read.
    run_atomic_migration(conn, 67, "v2 guestbook store", |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS community_guestbook (
                community_id TEXT PRIMARY KEY,
                events TEXT NOT NULL,
                cursor_secs INTEGER NOT NULL DEFAULT 0
            )",
            [],
        )
        .map_err(|e| format!("create community_guestbook: {e}"))?;
        Ok(())
    })?;

    // Migration 68: the CORD-02 §6 preservation stash — vsk fields Vector doesn't
    // drive (voice, client `custom`, unknown `extra`) persist beside the entity so
    // our own editions republish the FULL document instead of wiping them.
    run_atomic_migration(conn, 68, "v2 metadata preservation stash", |tx| {
        for (table, col) in [("communities", "meta_extra"), ("community_channels", "meta_extra")] {
            let sql = format!("ALTER TABLE {table} ADD COLUMN {col} TEXT");
            if let Err(e) = tx.execute(&sql, []) {
                let msg = e.to_string();
                if !msg.contains("duplicate column name") {
                    return Err(format!("add {table}.{col}: {msg}"));
                }
            }
        }
        Ok(())
    })?;

    // Migration 69: last-known bot manifests (kind 10304) so the `/` command
    // picker serves instantly from boot; a background refetch replaces a row
    // only with a newer edition. Manifests are PUBLIC replaceable events, so
    // rows are plaintext (unlike membership/community state).
    run_atomic_migration(conn, 69, "bot manifest store", |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS bot_manifests (
                pubkey TEXT PRIMARY KEY,
                manifest TEXT NOT NULL,
                event_created_at INTEGER NOT NULL,
                fetched_at INTEGER NOT NULL
            )",
            [],
        )
        .map_err(|e| format!("create bot_manifests: {e}"))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 70: Retained gift-wrap body for idempotent manual retry
    // =========================================================================
    // A failed (red) DM whose wrap silently landed would double-post on manual
    // Retry, because Retry rebuilt a fresh wrap with a new outer id. Retaining
    // the exact recipient wrap event (+ its rumor, + the local pending id to
    // look it up by) lets Retry republish the byte-identical event: relays
    // no-op the duplicate, so duplication is impossible regardless of client.
    // The body columns are nulled the instant the send is confirmed (a relay
    // OK), so steady-state they are NULL — only unsent messages carry a body.
    run_atomic_migration(conn, 70, "Retained gift-wrap body for idempotent retry", |tx| {
        tx.execute_batch(
            "ALTER TABLE nip17_wrap_keys ADD COLUMN wrap_json  TEXT;
             ALTER TABLE nip17_wrap_keys ADD COLUMN rumor_json TEXT;
             ALTER TABLE nip17_wrap_keys ADD COLUMN pending_id TEXT;
             CREATE INDEX IF NOT EXISTS idx_nip17_wrap_keys_pending ON nip17_wrap_keys(pending_id);"
        ).map_err(|e| format!("Failed to add resend-payload columns: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 71: Covering index for the unread-count query
    // =========================================================================
    // Column order = (chat_id, mine, kind) equality then a created_at range; the four columns
    // cover both the per-chat anchor MAX and the count, so neither touches the table.
    run_atomic_migration(conn, 71, "Covering index for unread counts", |tx| {
        tx.execute_batch(
            "CREATE INDEX IF NOT EXISTS idx_events_unread ON events(chat_id, mine, kind, created_at);"
        ).map_err(|e| format!("Failed to create unread covering index: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 72: Drop the unused events(user_id) index
    // =========================================================================
    // No query filters, joins, or orders by events.user_id, so the index only
    // cost a b-tree write on every event insert. Authors resolve via the
    // denormalized npub column instead.
    run_atomic_migration(conn, 72, "Drop unused events user_id index", |tx| {
        tx.execute_batch("DROP INDEX IF EXISTS idx_events_user;")
            .map_err(|e| format!("Failed to drop idx_events_user: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 73: Drop the legacy `messages` table
    // =========================================================================
    // Superseded by `events` at v0.3.1 (its data + attachment metadata were
    // copied over then). The public app has shipped on `events` since v0.4.0,
    // so no live account writes or reads `messages`. DROP takes its indexes too.
    run_atomic_migration(conn, 73, "Drop legacy messages table", |tx| {
        tx.execute_batch("DROP TABLE IF EXISTS messages;")
            .map_err(|e| format!("Failed to drop legacy messages table: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 74: Dedicated attachments table + backfill from event tags
    // =========================================================================
    // Attachments lived as a `["attachments", <json>]` entry inside events.tags,
    // making dedup a LIKE scan, the integrity check a per-event JSON parse, and
    // every download flip a read-modify-write of the whole tags blob. Normalize
    // into one row per attachment, keyed to its event (cascade on delete) and
    // indexed by content hash. Backfill from the existing tags in this same
    // transaction; the original tag is LEFT IN PLACE as a safety net (the table
    // is authoritative, but no data is destroyed) until a later release strips it.
    // Tags are plaintext at rest (only content is encrypted), so no decrypt here.
    run_atomic_migration(conn, 74, "Attachments table + backfill", |tx| {
        tx.execute_batch(
            "CREATE TABLE IF NOT EXISTS attachments (
                id           INTEGER PRIMARY KEY,
                event_id     TEXT NOT NULL,
                att_index    INTEGER NOT NULL,
                hash         TEXT NOT NULL,
                key          TEXT NOT NULL DEFAULT '',
                nonce        TEXT NOT NULL DEFAULT '',
                extension    TEXT NOT NULL DEFAULT '',
                name         TEXT NOT NULL DEFAULT '',
                url          TEXT NOT NULL DEFAULT '',
                path         TEXT NOT NULL DEFAULT '',
                size         INTEGER NOT NULL DEFAULT 0,
                img_meta     TEXT,
                downloaded   INTEGER NOT NULL DEFAULT 0,
                webxdc_topic TEXT, group_id TEXT, original_hash TEXT, scheme_version TEXT, mls_filename TEXT,
                UNIQUE(event_id, att_index),
                FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_attachments_hash       ON attachments(hash);
            CREATE INDEX IF NOT EXISTS idx_attachments_downloaded ON attachments(downloaded) WHERE downloaded = 1;"
        ).map_err(|e| format!("Failed to create attachments table: {}", e))?;

        // Backfill: parse each event's attachments tag and insert one row per attachment.
        let events: Vec<(String, String)> = {
            let mut stmt = tx.prepare("SELECT id, tags FROM events WHERE tags LIKE '%attachments%'")
                .map_err(|e| format!("prepare attachment backfill: {}", e))?;
            let mapped = stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, String>(1)?)))
                .map_err(|e| format!("query attachment backfill: {}", e))?;
            mapped.filter_map(|r| r.ok()).collect()
        };
        for (event_id, tags_json) in events {
            let tags: Vec<Vec<String>> = match serde_json::from_str(&tags_json) {
                Ok(t) => t,
                Err(_) => continue,
            };
            let att_json = tags.iter()
                .find(|t| t.first().map(|s| s.as_str()) == Some("attachments"))
                .and_then(|t| t.get(1));
            let Some(att_json) = att_json else { continue };
            let atts: Vec<crate::types::Attachment> = match serde_json::from_str(att_json) {
                Ok(a) => a,
                Err(_) => continue,
            };
            for (i, a) in atts.iter().enumerate() {
                let img_meta_json = a.img_meta.as_ref().and_then(|m| serde_json::to_string(m).ok());
                tx.execute(
                    "INSERT INTO attachments (event_id, att_index, hash, key, nonce, extension, name, url, \
                     path, size, img_meta, downloaded, webxdc_topic, group_id, original_hash) \
                     VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15)",
                    rusqlite::params![
                        event_id, i as i64, a.id, a.key, a.nonce, a.extension, a.name, a.url,
                        a.path, a.size as i64, img_meta_json, a.downloaded as i64,
                        a.webxdc_topic, a.group_id, a.original_hash,
                    ],
                ).map_err(|e| format!("insert backfilled attachment: {}", e))?;
            }
        }
        Ok(())
    })?;

    // =========================================================================
    // Migration 75: Strip the vestigial `attachments` tag from backfilled events
    // =========================================================================
    // Migration 74 copied the attachments into the table but left the source tag
    // in place as a fallback. Now reclaim that dead JSON from every event whose
    // attachments are PROVABLY in the table — 74's backfill is all-or-nothing per
    // event, so a matching table row means the whole vec was copied. A tag that 74
    // could not parse (no row) keeps its raw bytes, so this is lossless. Smaller
    // event rows also mean the message-load queries (which read `tags`) touch fewer
    // bytes. The read fallback stays for any un-backfilled remnants.
    run_atomic_migration(conn, 75, "Strip backfilled attachment tags", |tx| {
        let events: Vec<(String, String)> = {
            let mut stmt = tx.prepare(
                "SELECT id, tags FROM events WHERE tags LIKE '%attachments%' \
                 AND id IN (SELECT DISTINCT event_id FROM attachments)"
            ).map_err(|e| format!("prepare tag strip: {}", e))?;
            let mapped = stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, String>(1)?)))
                .map_err(|e| format!("query tag strip: {}", e))?;
            mapped.flatten().collect()
        };
        for (id, tags_json) in events {
            let Ok(mut tags) = serde_json::from_str::<Vec<Vec<String>>>(&tags_json) else { continue };
            let before = tags.len();
            tags.retain(|t| t.first().map(|s| s.as_str()) != Some("attachments"));
            if tags.len() == before {
                continue; // false-positive LIKE match; no actual attachments tag
            }
            let new_tags = serde_json::to_string(&tags).unwrap_or(tags_json);
            tx.execute("UPDATE events SET tags=?1 WHERE id=?2", rusqlite::params![new_tags, id])
                .map_err(|e| format!("strip attachments tag: {}", e))?;
        }
        Ok(())
    })?;

    // Drop the two attachment columns that never carried production data: `mls_filename`
    // (a vestige of the removed MLS feature) and `scheme_version` (unused MIP-04 plumbing).
    // Neither is read anywhere; both were always NULL.
    run_atomic_migration(conn, 76, "Drop dead attachment columns (mls_filename, scheme_version)", |tx| {
        tx.execute("ALTER TABLE attachments DROP COLUMN scheme_version", [])
            .map_err(|e| format!("drop scheme_version: {}", e))?;
        tx.execute("ALTER TABLE attachments DROP COLUMN mls_filename", [])
            .map_err(|e| format!("drop mls_filename: {}", e))?;
        Ok(())
    })?;

    // v1→v2 community migration (task #10). `migrated_to` is the terminal per-community
    // fence: set inside the flip transaction, checked by every v1 write path. `migration_pointer`
    // persists the extracted dissolution payload (signpost + sealed key material) so the flip
    // survives restarts; `migration_checked` stops the boot sweep re-probing a community whose
    // tombstone turned out to be a plain payload-less dissolution. `community_migrations` is the
    // owner wizard's resumable ledger — `twin` carries the created channel key material because
    // the pre-flip v2 twin has zero channel rows locally (the hijack guard skips v1-owned rows).
    run_atomic_migration(conn, 77, "v1->v2 migration: pointer columns + wizard ledger", |tx| {
        tx.execute_batch(
            "ALTER TABLE communities ADD COLUMN migrated_to TEXT;
             ALTER TABLE communities ADD COLUMN migration_pointer TEXT;
             ALTER TABLE communities ADD COLUMN migration_checked INTEGER NOT NULL DEFAULT 0;
             CREATE TABLE IF NOT EXISTS community_migrations (
                 community_id    TEXT PRIMARY KEY,
                 v2_community_id TEXT NOT NULL,
                 phase           INTEGER NOT NULL DEFAULT 0,
                 twin            TEXT NOT NULL DEFAULT '',
                 updated_at      INTEGER NOT NULL DEFAULT 0
             );",
        )
        .map_err(|e| format!("migration 77: {}", e))?;
        Ok(())
    })?;

    // Direct Invites carry a NIP-40 24h expiry. Relay support for NIP-40 is optional, so the
    // recipient enforces it locally too: the sender's declared expiry is persisted per parked
    // invite and filtered on read. 0 = no expiry declared (a pre-expiry sender), which stays
    // permanent — an invite whose sender never promised a deadline isn't ours to revoke.
    run_atomic_migration(conn, 78, "Pending invite expiry (NIP-40)", |tx| {
        tx.execute(
            "ALTER TABLE pending_community_invites ADD COLUMN expires_at INTEGER NOT NULL DEFAULT 0",
            [],
        )
        .map_err(|e| format!("migration 78: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 79: Per-npub ban history for phantom-member suppression
    // =========================================================================
    // CORD-02 §5 counts observation FORWARD of a member's latest Leave, Kick OR
    // BAN. The banlist alone is a timeless set, so lifting it let a pre-ban Join
    // (or old message) resurrect the npub as a member of a community they hold no
    // key to. Armada folds this from live control history; Vector caches the
    // banlist, so the per-npub mark has to persist alongside it.
    run_atomic_migration(conn, 79, "Per-npub ban marks (phantom-member suppression)", |tx| {
        // Presence-checked, not blind: a build that briefly carried this column in the
        // CREATE TABLE too would leave a DB holding the column with the migration rolled
        // back, and a bare ALTER then fails on every boot forever with no way out.
        let present: i32 = tx
            .query_row("SELECT COUNT(*) FROM pragma_table_info('communities') WHERE name='banlist_marks'", [], |r| r.get(0))
            .map_err(|e| format!("migration 79: {}", e))?;
        if present == 0 {
            tx.execute(
                "ALTER TABLE communities ADD COLUMN banlist_marks TEXT NOT NULL DEFAULT '{}'",
                [],
            )
            .map_err(|e| format!("migration 79: {}", e))?;
        }
        Ok(())
    })?;

    // =========================================================================
    // Migration 80: Attachment mirror URLs (BUD-04 fallbacks)
    // =========================================================================
    // NIP-17 / imeta `fallback` sources: the same ciphertext mirrored on other
    // Blossom servers, tried in order when the primary URL dies. Space-joined
    // (URLs cannot contain spaces); empty = no mirrors.
    run_atomic_migration(conn, 80, "Attachment fallback URLs (Blossom mirrors)", |tx| {
        tx.execute(
            "ALTER TABLE attachments ADD COLUMN fallback_urls TEXT NOT NULL DEFAULT ''",
            [],
        )
        .map_err(|e| format!("migration 80: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 81: window index for the dedup-cache preload
    // =========================================================================
    // The preload reads processed_wrappers bounded by the reconcile cursors;
    // without this index the bounded query still scans the full ledger.
    run_atomic_migration(conn, 81, "processed_wrappers window index", |tx| {
        tx.execute(
            "CREATE INDEX IF NOT EXISTS idx_processed_wrappers_window \
             ON processed_wrappers(transport, wrapper_created_at)",
            [],
        )
        .map_err(|e| format!("migration 81: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 82: parked Private-Channel key vends (CORD-03/05 §6)
    // =========================================================================
    // A grant's key vend can arrive before the control fold that proves the
    // grant, so it parks here and is re-judged after every control follow.
    // Durable rather than in-RAM by necessity: the vend rides a 24h NIP-40 wrap
    // that relays delete, so a restart before the fold catches up would lose the
    // only copy. One row per (community, channel) — the newest epoch wins, and a
    // vend at or below the held epoch is superseded.
    run_atomic_migration(conn, 82, "Parked private-channel key vends", |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS pending_channel_keys (
                community_id TEXT NOT NULL,
                channel_id   TEXT NOT NULL,
                epoch        INTEGER NOT NULL,
                channel_key  BLOB NOT NULL,
                sender       TEXT NOT NULL,
                received_at  INTEGER NOT NULL,
                PRIMARY KEY (community_id, channel_id)
            )",
            [],
        )
        .map_err(|e| format!("migration 82: {}", e))?;
        Ok(())
    })?;

    // =========================================================================
    // Migration 83: parked vends become CANDIDATES, not a single slot
    // =========================================================================
    // 82 keyed the table on (community, channel) and only replaced on a higher
    // epoch. Parking is reachable by any npub that can gift-wrap us (the bundle
    // self-certifies, and its inputs are public for a public community), so a
    // stranger could pre-park a high-epoch row and make the genuine vend a silent
    // no-op — the member simply stays keyless with no retry.
    //
    // Now every vend is its own row and the judge tries them all, so an
    // unprovable row can never displace a provable one. Caps bound what an
    // arbitrary sender can make us store (and decrypt on every follow pass).
    run_atomic_migration(conn, 83, "Parked channel-key vends as candidates", |tx| {
        tx.execute("DROP TABLE IF EXISTS pending_channel_keys", [])
            .map_err(|e| format!("migration 83: {}", e))?;
        tx.execute(
            "CREATE TABLE pending_channel_keys (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                community_id TEXT NOT NULL,
                channel_id   TEXT NOT NULL,
                epoch        INTEGER NOT NULL,
                channel_key  BLOB NOT NULL,
                sender       TEXT NOT NULL,
                received_at  INTEGER NOT NULL
            )",
            [],
        )
        .map_err(|e| format!("migration 83: {}", e))?;
        tx.execute(
            "CREATE INDEX IF NOT EXISTS idx_pending_channel_keys_scope \
             ON pending_channel_keys(community_id, channel_id)",
            [],
        )
        .map_err(|e| format!("migration 83: {}", e))?;
        Ok(())
    })?;

    // CORD-04 §7: one Pin List per channel — the folded head's RAW content
    // (both self-describing forms), never a re-serialization, so republishing
    // carries the exact bytes and the byte cap judges what the wire carried.
    run_atomic_migration(conn, 84, "Per-channel pin lists", |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS community_pins (
                community_id TEXT NOT NULL,
                channel_id   TEXT NOT NULL,
                content      TEXT NOT NULL,
                version      INTEGER NOT NULL,
                PRIMARY KEY (community_id, channel_id)
            )",
            [],
        )
        .map_err(|e| format!("migration 84: {}", e))?;
        Ok(())
    })?;

    // Durable delete tombstones: a deleted message's row is GONE, so the ingest
    // dedup can't refuse a wrap a relay re-serves after ignoring our NIP-09 —
    // across a restart the in-session tombstone set is empty and the message
    // resurrects. One row per deleted message id, forever.
    run_atomic_migration(conn, 85, "Deleted-message tombstones", |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS deleted_messages (
                event_id   TEXT PRIMARY KEY,
                deleted_at INTEGER NOT NULL
            )",
            [],
        )
        .map_err(|e| format!("migration 85: {}", e))?;
        Ok(())
    })?;

    // CORD-02 §2 control_root split: the Control Plane's held address
    // (control_pk, encrypted hex) and the staff write secret (control_root,
    // encrypted blob) per v2 community. Nullable — a legacy epoch has neither,
    // a non-staff member holds only the address, and the v1 writer never
    // names these columns.
    run_atomic_migration(conn, 86, "Concord v2 control_root split columns", |tx| {
        for (col, ddl) in [("control_pk", "TEXT"), ("control_root", "BLOB")] {
            // ADD COLUMN is not idempotent; tolerate a re-run (duplicate column).
            let sql = format!("ALTER TABLE communities ADD COLUMN {col} {ddl}");
            if let Err(e) = tx.execute(&sql, []) {
                let msg = e.to_string();
                if !msg.contains("duplicate column name") {
                    return Err(format!("add communities.{col}: {msg}"));
                }
            }
        }
        Ok(())
    })?;

    // The NIP-77 fingerprint read selects `wrapper_id` for a whole transport,
    // which `idx_processed_wrappers_window` can locate but not supply — so every
    // row costs a table lookup, and asking for sorted rows costs a temp B-tree
    // on top. Carrying `wrapper_id` in the index makes the read a covering scan
    // that arrives in negentropy's own sort order, which is most of the cost of
    // sealing a storage on a device that isn't a desktop.
    run_atomic_migration(conn, 87, "Covering index for negentropy fingerprints", |tx| {
        tx.execute(
            "CREATE INDEX IF NOT EXISTS idx_processed_wrappers_neg \
             ON processed_wrappers(transport, wrapper_created_at, wrapper_id)",
            [],
        )
        .map_err(|e| format!("migration 87: {}", e))?;

        // `idx_processed_wrappers_window` is a strict prefix of the index above,
        // and every reader of this table also wants `wrapper_id` — the one
        // column it lacks. Keeping it would cost a second b-tree write per
        // ingested wrapper to serve queries the wider index already covers.
        tx.execute("DROP INDEX IF EXISTS idx_processed_wrappers_window", [])
            .map_err(|e| format!("migration 87 (drop superseded index): {}", e))?;
        Ok(())
    })?;

    run_atomic_migration(conn, 88, "Persist status custom-emoji tags", |tx| {
        // Without this column the shortcode->URL pairs live only in memory,
        // so every boot renders raw :shortcodes: until a relay answers the
        // next status fetch — even when the emoji is cached on disk.
        tx.execute(
            "ALTER TABLE profiles ADD COLUMN status_emoji_tags TEXT NOT NULL DEFAULT ''",
            [],
        )
        .map_err(|e| format!("migration 88: {}", e))?;
        Ok(())
    })?;

    // Migration 89: the policy store. Bytes, not columns — a policy is hashed
    // over the EXACT bytes it arrived as, and one day those same bytes ride a
    // control-plane edition to every device and client. Normalising it into
    // columns would mean re-serialising to evaluate, which changes the hash and
    // breaks the only identity the wire has.
    run_atomic_migration(conn, 89, "Community policy store", |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS community_policies (
                community_id TEXT NOT NULL,
                policy_id    TEXT NOT NULL,
                bytes        TEXT NOT NULL,
                hash         TEXT NOT NULL,
                enabled      INTEGER NOT NULL DEFAULT 1,
                updated_at   INTEGER NOT NULL DEFAULT 0,
                PRIMARY KEY (community_id, policy_id)
            )",
            [],
        )
        .map_err(|e| format!("create community_policies: {e}"))?;
        Ok(())
    })?;

    // The banlist's queryable home. It lived only as an encrypted blob on the
    // communities row, which SQL cannot compare against — so a banned author's
    // messages could not be excluded before LIMIT, and filtering them after it
    // returned short pages and broke backscroll.
    //
    // Plaintext by necessity: `events.npub` is plaintext, and a join needs both
    // sides in the same form. Deliberate — every message author is already stored
    // in the clear, so the list mainly hides members who never posted, and the
    // column it retires is one fewer for the at-rest sweep to forget.
    run_atomic_migration(conn, 90, "Queryable community banlist", |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS community_bans (
                community_id TEXT NOT NULL,
                npub         TEXT NOT NULL,
                PRIMARY KEY (community_id, npub)
            )",
            [],
        )
        .map_err(|e| format!("create community_bans: {e}"))?;
        // The hot path is 'is THIS author banned in THIS community', which the
        // primary key already serves; this one serves 'list the bans' cheaply.
        tx.execute(
            "CREATE INDEX IF NOT EXISTS idx_community_bans_community ON community_bans(community_id)",
            [],
        )
        .map_err(|e| format!("index community_bans: {e}"))?;
        Ok(())
    })?;

    // A chat row needs an identifier to ever be opened; one without is a phantom
    // that loads into the list and can never be addressed or removed.
    run_atomic_migration(conn, 91, "Drop chats with an empty identifier", |tx| {
        tx.execute(
            "DELETE FROM events WHERE chat_id IN (SELECT id FROM chats WHERE chat_identifier = '')",
            [],
        )
        .map_err(|e| format!("drop phantom chat events: {e}"))?;
        tx.execute("DELETE FROM chats WHERE chat_identifier = ''", [])
            .map_err(|e| format!("drop phantom chats: {e}"))?;
        Ok(())
    })?;

    run_atomic_migration(conn, 92, "Create blossom_server_stats table", |tx| {
        crate::blossom_stats::migrate(tx)
    })?;

    // Keyed by scope id rather than hung off a chat row: a community has no chat
    // row of its own, only its primary channel's, and storing it there is what
    // made "Mute Community" silence one channel.
    run_atomic_migration(conn, 93, "Per-scope notification preferences", |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS notify_prefs (
                scope_id          TEXT PRIMARY KEY,
                level             INTEGER,
                mute_until        INTEGER NOT NULL DEFAULT 0,
                suppress_everyone INTEGER
            )",
            [],
        )
        .map_err(|e| format!("create notify_prefs: {e}"))?;
        // Existing mutes carry over as indefinite ones, so nobody's rooms get
        // loud on upgrade.
        tx.execute(
            "INSERT OR REPLACE INTO notify_prefs (scope_id, level, mute_until, suppress_everyone)
             SELECT chat_identifier, NULL, -1, NULL FROM chats WHERE muted = 1 AND chat_identifier != ''",
            [],
        )
        .map_err(|e| format!("seed notify_prefs from mutes: {e}"))?;
        Ok(())
    })?;

    // The page query skipped rows by reading each one for its kind, then sorted ties on
    // received_at; the wider index answers both from the index alone.
    run_atomic_migration(conn, 94, "Chat page index covers received_at and kind", |tx| {
        tx.execute_batch(
            "DROP INDEX IF EXISTS idx_events_chat_time;
             CREATE INDEX IF NOT EXISTS idx_events_chat_page ON events(chat_id, created_at DESC, received_at DESC, kind);",
        )
        .map_err(|e| format!("rebuild chat page index: {e}"))
    })?;

    Ok(())
}

#[cfg(test)]
mod tests {
    use super::HIGHEST_MIGRATION_ID;

    /// Parses this very file so the constant cannot drift from `run_migrations`.
    /// Without it, adding a migration and forgetting the bump would silently
    /// re-open the downgrade hole the constant exists to close.
    #[test]
    fn highest_migration_id_matches_the_runner() {
        let src = include_str!("schema.rs");
        let mut highest = 0u32;
        let mut seen = 0usize;

        for (at, _) in src.match_indices("run_atomic_migration(") {
            let tail = src[at + "run_atomic_migration(".len()..].trim_start();
            // Skips this test's own mention of the name, which is not a call.
            let Some(args) = tail.strip_prefix("conn,") else {
                continue;
            };
            let id: String = args
                .trim_start()
                .chars()
                .take_while(char::is_ascii_digit)
                .collect();
            if let Ok(id) = id.parse::<u32>() {
                seen += 1;
                highest = highest.max(id);
            }
        }

        assert!(seen > 0, "parsed no migrations; the call shape must have changed");
        assert_eq!(
            HIGHEST_MIGRATION_ID, highest,
            "bump HIGHEST_MIGRATION_ID to {highest} when adding a migration"
        );
    }
}