Skip to content

Voice-to-text dictation app with local (Nvidia Parakeet/Whisper) and cloud models (BYOK). Privacy-first and available cross-platform.

316 KB
Raw Blame
const Database = require("better-sqlite3");
const {
  NOTE_TEXT_FIELDS,
  SUMMARY_CLEARED_METADATA,
  SUMMARY_METADATA,
  clearedNoteFields,
  cloudTextUpdates,
  hasNoteRevision,
  hasPendingNoteClear,
} = require("./noteFieldSync");
const path = require("path");
const fs = require("fs");
const { randomUUID } = require("crypto");
const { isDeepStrictEqual } = require("util");
const noteUndo = require("./noteUndo");
const debugLogger = require("./debugLogger");
const { buildNoteSearchQuery } = require("./noteSearch");
const { normalizeStoredSpeakerCount } = require("./speakerCount");
const { parseEventTime } = require("./calendarAvailability");
// An explicit zone marks an instant this app captured at dictation time. A
// naive timestamp may instead be a sync artifact: upsertTranscriptionFromCloud
// keeps the cloud created_at but lets timestamp default to the local pull, so
// a naive value must never outrank created_at when dating a historical row.
const { hasExplicitTimeZone, parseDbTimestamp, toDbTimestamp } = require("./dbTimestamp");
const {
  BUILTIN_ACTIONS,
  GENERATE_NOTES_KEY,
  LEGACY_DETAILED_NOTES,
  NOTE_ACTION_LIMITS,
} = require("./builtinActions");
const { normalizeSections } = require("./templatePrompts");
const {
  ANALYTICS_COUNTER_VERSION,
  ANALYTICS_HISTORY_BACKFILL_VERSION,
  ANALYTICS_HISTORICAL_COUNTER_VERSION,
  countSpokenWords,
  inferHistoricalAnalyticsMode,
  localDateKey,
  summarizeAnalyticsDays,
} = require("./analytics");
const { app } = require("electron");

// Server-enforced trigger cap (openwhispr-api); enforced here so one oversized
// trigger can't 400 the whole sync batch.
const MAX_SNIPPET_TRIGGER_LENGTH = 100;

// Text a local writer creates is the user's: recording it as a set lets
// conflict Keep tell it from text this device merely pulled. A blank stays
// unmarked, as it never clears anything.
function typedTextOperation(text) {
  return typeof text === "string" && text.trim() ? "set" : null;
}

// Every local field a note create (POST) carries; the acknowledgement compares
// these atomically against the pushed snapshot. Must mirror NotePushSnapshot
// in src/types/electron.ts.
const NOTE_CREATE_ACK_FIELDS = [
  "client_note_id",
  "title",
  "content",
  "enhanced_content",
  "content_sync_operation",
  "enhanced_content_sync_operation",
  "content_edit_generation",
  "enhanced_content_edit_generation",
  "enhancement_prompt",
  "enhancement_template_id",
  "enhanced_at_content_hash",
  "note_type",
  "source_file",
  "audio_duration_seconds",
  "folder_id",
  "space_id",
  "transcript",
  "calendar_event_id",
  "participants",
  "diarization_enabled",
  "expected_speaker_count",
  "created_at",
  "updated_at",
  "sync_status",
  "deleted_at",
];
// A PATCH additionally pins the server base and any pending scope retraction.
const NOTE_PATCH_ACK_FIELDS = [
  ...NOTE_CREATE_ACK_FIELDS,
  "cloud_updated_at",
  "cloud_revision",
  "left_team",
];
// Must mirror FolderPushSnapshot in src/types/electron.ts.
const FOLDER_ACK_FIELDS = [
  "client_folder_id",
  "name",
  "is_default",
  "sort_order",
  "space_id",
  "created_at",
  "updated_at",
  "sync_status",
  "deleted_at",
  "left_team",
];

// Validates and normalizes the editable fields of a note template or action.
function resolveActionFields(kind, fields) {
  const name = (fields.name || "").trim();
  const description = (fields.description || "").trim();
  const prompt = (fields.prompt || "").trim();
  const sections = kind === "template" ? normalizeSections(fields.sections) : [];
  const output = kind === "action" ? fields.output || "chat" : null;
  if (!name) return { error: "Name is required" };
  if (kind === "action" && !prompt) return { error: "Action prompt is required" };
  if (kind === "template" && !prompt && sections.length === 0) {
    return { error: "A template needs instructions or a section" };
  }
  if (output !== null && output !== "summary" && output !== "chat") {
    return { error: "Unknown action output" };
  }
  const limits = NOTE_ACTION_LIMITS;
  if (
    name.length > limits.name ||
    description.length > limits.description ||
    prompt.length > limits.prompt ||
    sections.length > limits.sections ||
    sections.some(
      (section) =>
        section.heading.length > limits.heading || section.instruction.length > limits.instruction
    )
  ) {
    return { error: "Too long" };
  }
  return {
    name,
    description,
    prompt,
    sections: sections.length > 0 ? JSON.stringify(sections) : null,
    output,
  };
}

function toActionItem(row) {
  if (!row) return null;
  let sections = null;
  try {
    sections = row.sections ? JSON.parse(row.sections) : null;
  } catch {
    sections = null;
  }
  // Destinations are fixed by built-in identity, never by editable copy or stored output.
  const builtin = BUILTIN_ACTIONS.find((action) => action.translationKey === row.translation_key);
  const output =
    row.kind === "template" ? null : builtin?.output === "summary" ? "summary" : "chat";
  return { ...row, sections, output };
}

function rowMatchesSnapshot(row, snapshot, fields) {
  return fields.every((field) => {
    const fallback = field.endsWith("_edit_generation") ? 0 : null;
    const expected = snapshot[field] === undefined ? fallback : snapshot[field];
    return row[field] === expected;
  });
}

function noteTextMatchesSnapshot(row, snapshot, field) {
  return rowMatchesSnapshot(row, snapshot, [
    field,
    `${field}_sync_operation`,
    `${field}_edit_generation`,
    ...(field === "enhanced_content" ? SUMMARY_METADATA : []),
  ]);
}

// An applied create acknowledges the submitted sets, even if the server kept
// a clear instead. Later edits and undelivered clears still belong to this device.
function acknowledgedCreateTextUpdates(current, snapshot, cloudNote) {
  const fields = NOTE_TEXT_FIELDS.filter(
    (field) =>
      snapshot[`${field}_sync_operation`] !== "clear" &&
      noteTextMatchesSnapshot(current, snapshot, field)
  );
  return {
    ...cloudTextUpdates(current, cloudNote, fields),
    ...Object.fromEntries(fields.map((field) => [`${field}_sync_operation`, null])),
  };
}

// An optimistically deleted folder still holds its server-side name until the
// DELETE is confirmed, so it must keep blocking reuse of that name.
const FOLDER_NAME_TAKEN_FILTER = `(deleted_at IS NULL OR EXISTS (
  SELECT 1 FROM optimistic_folder_delete_rows r
  WHERE r.folder_id = folders.id AND r.entity_type = 'folder'
))`;

// A meeting synced by both a REST provider (Google/Microsoft) and Apple
// (Calendar.app mirrors the same accounts) would double-fire reminders and
// duplicate UI rows; suppress the Apple copy when a REST row occupies the same
// time slot + title (REST rows have richer conference data). datetime()
// normalizes the providers' timestamp formats (Google stores offset-form
// RFC3339, Apple/Microsoft store UTC "Z" form). All-day rows pair by local
// days instead: REST providers store plain dates (the end exclusive) while the
// Apple helper writes local instants, ending at either the next midnight or
// the last second of the final day — one second later is the exclusive end
// day in both cases. REST rows are never collapsed — Google and Microsoft are
// never mirrors of each other.
const MIRROR_SLOT = `CASE WHEN is_all_day THEN 'day:' || CASE WHEN length(start_time) = 10
  THEN start_time || '|' || end_time
  ELSE date(start_time, 'localtime') || '|' || date(end_time, '+1 second', 'localtime') END
  ELSE datetime(start_time) || '|' || datetime(end_time) END`;

function dedupedEventsQuery(where) {
  return `SELECT * FROM (
    SELECT *, MAX(provider != 'apple') OVER (
      PARTITION BY ${MIRROR_SLOT}, COALESCE(summary, '')
    ) AS has_synced
    FROM calendar_events
    WHERE ${where}
  ) WHERE provider != 'apple' OR has_synced = 0 ORDER BY datetime(start_time) ASC`;
}

function stripDedupeColumn({ has_synced: _hasSynced, ...event }) {
  return event;
}

// Whitelist for provider-scoped SQL against the per-provider calendars tables.
const CALENDARS_TABLE_BY_PROVIDER = {
  google: "google_calendars",
  microsoft: "microsoft_calendars",
};

const AVAILABILITY_PROVIDERS = new Set(["google", "microsoft", "apple"]);
const SELECTED_CALENDAR_EVENT_FILTER = `(
  (provider = 'google' AND EXISTS (
    SELECT 1 FROM google_calendars WHERE google_calendars.id = calendar_events.calendar_id
      AND google_calendars.is_selected = 1
  )) OR
  (provider = 'microsoft' AND EXISTS (
    SELECT 1 FROM microsoft_calendars WHERE microsoft_calendars.id = calendar_events.calendar_id
      AND microsoft_calendars.is_selected = 1
  )) OR
  (provider = 'apple' AND EXISTS (
    SELECT 1 FROM apple_calendars WHERE apple_calendars.id = calendar_events.calendar_id
  ))
)`;

// A contacts row's source: "google:<account email>", "microsoft:<account
// email>", "apple", or "manual" (added by hand to a note).
function contactSource(provider, accountEmail = null) {
  return accountEmail ? `${provider}:${accountEmail}` : provider;
}

class DatabaseManager {
  constructor() {
    this.db = null;
    this.activeAccountId = null;
    this.initDatabase();
  }

  setActiveAccountId(accountId) {
    this.activeAccountId =
      typeof accountId === "string" && accountId.trim().length > 0 ? accountId.trim() : null;
  }

  _accountScopeCondition(tableName) {
    return {
      sql: `((${tableName}.account_id IS NULL AND EXISTS (
        SELECT 1 FROM spaces account_scope_space
        WHERE account_scope_space.id = ${tableName}.space_id
          AND account_scope_space.kind = 'private'
      )) OR ${tableName}.account_id = ? OR EXISTS (
        SELECT 1
        FROM spaces account_scope_space
        JOIN space_accounts account_scope_membership
          ON account_scope_membership.space_id = account_scope_space.id
        WHERE account_scope_space.id = ${tableName}.space_id
          AND account_scope_space.kind = 'team'
          AND account_scope_membership.account_id = ?
      ))`,
      params: [this.activeAccountId, this.activeAccountId],
    };
  }

  _accountIdForSpace(spaceId) {
    const space = this.db.prepare("SELECT kind FROM spaces WHERE id = ?").get(spaceId);
    return space?.kind === "team" ? null : this.activeAccountId;
  }

  _getFolderInAccountScope(id) {
    const accountScope = this._accountScopeCondition("folders");
    return (
      this.db
        .prepare(`SELECT * FROM folders WHERE id = ? AND ${accountScope.sql}`)
        .get(id, ...accountScope.params) || null
    );
  }

  // Child notes owned by another local account are invisible to the active
  // scope, so a folder removal must release them to the space root — never
  // delete them (or their conversations, speaker rows, or cloud tombstones)
  // with the folder. Run this before any statement that targets the folder's
  // children, so plain `folder_id = ?` filters only ever see in-scope rows.
  _releaseOutOfScopeChildNotes(folderId) {
    const accountScope = this._accountScopeCondition("notes");
    const outOfScopeIds = this.db
      .prepare(
        `SELECT id FROM notes
         WHERE folder_id = ? AND id NOT IN (
           SELECT id FROM notes WHERE folder_id = ? AND ${accountScope.sql}
         )`
      )
      .all(folderId, folderId, ...accountScope.params)
      .map((row) => row.id);
    if (outOfScopeIds.length === 0) return [];
    const placeholders = outOfScopeIds.map(() => "?").join(", ");
    this.db
      .prepare(
        `UPDATE notes
         SET folder_id = NULL, sync_status = 'pending', updated_at = datetime('now')
         WHERE id IN (${placeholders})`
      )
      .run(...outOfScopeIds);
    return this.db
      .prepare(`SELECT * FROM notes WHERE id IN (${placeholders})`)
      .all(...outOfScopeIds);
  }

  _releaseActiveSpaceMembershipIfShared(spaceId) {
    if (!this.activeAccountId) return false;
    const otherMembership = this.db
      .prepare(
        `SELECT 1 FROM space_accounts
         WHERE space_id = ? AND account_id != ?
         LIMIT 1`
      )
      .get(spaceId, this.activeAccountId);
    if (!otherMembership) return false;
    this.db
      .prepare("DELETE FROM space_accounts WHERE space_id = ? AND account_id = ?")
      .run(spaceId, this.activeAccountId);
    return true;
  }

  initDatabase() {
    try {
      const dbFileName =
        process.env.NODE_ENV === "development" ? "transcriptions-dev.db" : "transcriptions.db";

      const dbPath = path.join(app.getPath("userData"), dbFileName);

      this.db = new Database(dbPath);
      this.db.pragma("journal_mode = WAL");

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS transcriptions (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          text TEXT NOT NULL,
          timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Audio retention columns
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN raw_text TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN has_audio INTEGER NOT NULL DEFAULT 0");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN audio_duration_ms INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN provider TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN model TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec(
          "ALTER TABLE transcriptions ADD COLUMN status TEXT NOT NULL DEFAULT 'completed'"
        );
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN error_message TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN error_code TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      // Records the dictation intent (e.g. "translation") so retry/recover re-runs the same route.
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN route_kind TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS custom_dictionary (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          word TEXT NOT NULL UNIQUE,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS snippets (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          trigger TEXT NOT NULL,
          replacement TEXT NOT NULL,
          client_snippet_id TEXT,
          cloud_id TEXT,
          sync_status TEXT DEFAULT 'pending',
          deleted_at TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS notes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL DEFAULT 'Untitled Note',
          content TEXT NOT NULL DEFAULT '',
          note_type TEXT NOT NULL DEFAULT 'personal',
          source_file TEXT,
          audio_duration_seconds REAL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN enhanced_content TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN enhancement_prompt TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN enhanced_at_content_hash TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN enhancement_template_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      let addedSyncOperations = false;
      for (const definition of [
        "cloud_revision INTEGER",
        "cloud_create_rejected INTEGER NOT NULL DEFAULT 0",
        "cloud_create_pending TEXT",
        "content_sync_operation TEXT",
        "enhanced_content_sync_operation TEXT",
        "content_edit_generation INTEGER NOT NULL DEFAULT 0",
        "enhanced_content_edit_generation INTEGER NOT NULL DEFAULT 0",
      ]) {
        try {
          this.db.exec(`ALTER TABLE notes ADD COLUMN ${definition}`);
          if (definition.startsWith("content_sync_operation")) addedSyncOperations = true;
        } catch (err) {
          if (!err.message.includes("duplicate column")) throw err;
        }
      }
      if (addedSyncOperations) this._markUnsyncedNoteTextAsSet();
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN cloud_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      this.db.exec(`
        CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
          title,
          content,
          enhanced_content,
          content='notes',
          content_rowid='id'
        )
      `);

      this.db.exec(`
        CREATE TRIGGER IF NOT EXISTS notes_fts_insert AFTER INSERT ON notes BEGIN
          INSERT INTO notes_fts(rowid, title, content, enhanced_content)
          VALUES (new.id, new.title, new.content, new.enhanced_content);
        END
      `);

      this.db.exec(`
        CREATE TRIGGER IF NOT EXISTS notes_fts_update AFTER UPDATE ON notes BEGIN
          INSERT INTO notes_fts(notes_fts, rowid, title, content, enhanced_content)
          VALUES ('delete', old.id, old.title, old.content, old.enhanced_content);
          INSERT INTO notes_fts(rowid, title, content, enhanced_content)
          VALUES (new.id, new.title, new.content, new.enhanced_content);
        END
      `);

      this.db.exec(`
        CREATE TRIGGER IF NOT EXISTS notes_fts_delete AFTER DELETE ON notes BEGIN
          INSERT INTO notes_fts(notes_fts, rowid, title, content, enhanced_content)
          VALUES ('delete', old.id, old.title, old.content, old.enhanced_content);
        END
      `);

      this.db
        .prepare(
          `
        INSERT OR IGNORE INTO notes_fts(rowid, title, content, enhanced_content)
        SELECT id, COALESCE(title, ''), COALESCE(content, ''), COALESCE(enhanced_content, '')
        FROM notes
      `
        )
        .run();

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS folders (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL UNIQUE,
          is_default INTEGER NOT NULL DEFAULT 0,
          sort_order INTEGER NOT NULL DEFAULT 0,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      const folderCount = this.db.prepare("SELECT COUNT(*) as count FROM folders").get();
      if (folderCount.count === 0) {
        const seedFolder = this.db.prepare(
          "INSERT INTO folders (name, is_default, sort_order) VALUES (?, 1, ?)"
        );
        seedFolder.run("Personal", 0);
        seedFolder.run("Meetings", 1);
        seedFolder.run("Videos", 2);
      }

      // Backfill folder_id only when the column is first added: on later
      // launches a NULL folder_id is a legitimate space-root note, not a
      // pre-folders row.
      let folderColumnAdded = true;
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN folder_id INTEGER REFERENCES folders(id)");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
        folderColumnAdded = false;
      }

      if (folderColumnAdded) {
        const personalFolder = this.db
          .prepare("SELECT id FROM folders WHERE name = 'Personal' AND is_default = 1")
          .get();
        if (personalFolder) {
          this.db
            .prepare("UPDATE notes SET folder_id = ? WHERE folder_id IS NULL")
            .run(personalFolder.id);
        }
      }

      // One-time seed (user_version 1): a pre-existing user-created "Videos"
      // folder stays untouched (never promoted to default); URL downloads route
      // to it by name. Guarded so a later delete/rename doesn't resurrect it as
      // an undeletable default on the next launch.
      if (this.db.pragma("user_version", { simple: true }) < 1) {
        const videosFolder = this.db.prepare("SELECT id FROM folders WHERE name = 'Videos'").get();
        if (!videosFolder) {
          const maxOrder = this.db.prepare("SELECT MAX(sort_order) as m FROM folders").get();
          this.db
            .prepare(
              "INSERT OR IGNORE INTO folders (name, is_default, sort_order) VALUES ('Videos', 1, ?)"
            )
            .run((maxOrder?.m ?? 1) + 1);
        }
        this.db.pragma("user_version = 1");
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS actions (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          description TEXT NOT NULL DEFAULT '',
          prompt TEXT NOT NULL,
          icon TEXT NOT NULL DEFAULT 'sparkles',
          is_builtin INTEGER NOT NULL DEFAULT 0,
          sort_order INTEGER NOT NULL DEFAULT 0,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      try {
        this.db.exec("ALTER TABLE actions ADD COLUMN translation_key TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      // Every row before these columns rewrote the AI summary from the note's
      // material, which is what a template does, so 'template' is the default.
      for (const column of [
        "client_id TEXT",
        "kind TEXT NOT NULL DEFAULT 'template'",
        "sections TEXT",
        "output TEXT",
      ]) {
        try {
          this.db.exec(`ALTER TABLE actions ADD COLUMN ${column}`);
        } catch (err) {
          if (!err.message.includes("duplicate column")) throw err;
        }
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS agent_conversations (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL DEFAULT 'Untitled',
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS agent_messages (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          conversation_id INTEGER NOT NULL REFERENCES agent_conversations(id) ON DELETE CASCADE,
          role TEXT NOT NULL CHECK(role IN ('user', 'assistant', 'system')),
          content TEXT NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_agent_messages_conversation ON agent_messages(conversation_id)"
      );

      try {
        this.db.exec("ALTER TABLE agent_messages ADD COLUMN metadata TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE agent_conversations ADD COLUMN archived_at DATETIME");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      // Receipts for agent connector actions. Never holds message content:
      // destination labels, states and result links only.
      this.db.exec(`
        CREATE TABLE IF NOT EXISTS connector_actions (
          id TEXT PRIMARY KEY,
          account_id TEXT,
          connector TEXT NOT NULL,
          action TEXT NOT NULL,
          kind TEXT NOT NULL,
          destination_label TEXT,
          state TEXT NOT NULL,
          result_url TEXT,
          error_code TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_connector_actions_connector ON connector_actions(connector, created_at)"
      );
      // Destination labels name channels and people in someone's workspace,
      // so a receipt belongs to the OpenWhispr account that made it. Tables
      // created before the column existed gain it here.
      try {
        this.db.exec("ALTER TABLE connector_actions ADD COLUMN account_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_connector_actions_account ON connector_actions(account_id, connector, created_at)"
      );
      try {
        this.db.exec("ALTER TABLE agent_conversations ADD COLUMN cloud_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE agent_conversations ADD COLUMN note_id INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_agent_conversations_note ON agent_conversations(note_id)"
      );
      try {
        this.db.exec("ALTER TABLE agent_conversations ADD COLUMN space_id INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE agent_conversations ADD COLUMN folder_id INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_agent_conversations_container ON agent_conversations(space_id, folder_id)"
      );

      const builtinKeys = [
        ...BUILTIN_ACTIONS.map((action) => action.translationKey),
        LEGACY_DETAILED_NOTES.translationKey,
      ];
      this.db.transaction(() => {
        // Older builds rename unfamiliar keys. Recover even the retired key
        // before the unknown-key fallback can turn it into a second AI Summary.
        this.db
          .prepare(
            `UPDATE actions SET translation_key = client_id WHERE is_builtin = 1 AND client_id IN (${builtinKeys.map(() => "?").join(", ")}) AND translation_key IS NOT client_id`
          )
          .run(...builtinKeys);

        const hasStockContent = (row, action) => {
          if (row.sections === null) return action.previousPrompts.includes(row.prompt);
          if (row.prompt !== action.prompt) return false;
          try {
            return isDeepStrictEqual(JSON.parse(row.sections), action.sections);
          } catch {
            // Unrecognized data may be a customization; keep it.
            return false;
          }
        };
        // A customized AI Summary isn't the detailed template, so Detailed Notes stays.
        const summaryRow = this.db
          .prepare(
            "SELECT prompt, sections FROM actions WHERE is_builtin = 1 AND translation_key = ?"
          )
          .get(GENERATE_NOTES_KEY);
        const summaryIsStock =
          !summaryRow ||
          hasStockContent(
            summaryRow,
            BUILTIN_ACTIONS.find((action) => action.translationKey === GENERATE_NOTES_KEY)
          );

        const legacyRows = this.db
          .prepare("SELECT * FROM actions WHERE is_builtin = 1 AND translation_key = ?")
          .all(LEGACY_DETAILED_NOTES.translationKey);
        const deleteLegacy = this.db.prepare("DELETE FROM actions WHERE id = ?");
        const settleLegacy = this.db.prepare(
          "UPDATE OR IGNORE actions SET client_id = COALESCE(client_id, ?), sort_order = ?, prompt = ?, sections = ? WHERE id = ?"
        );
        for (const row of legacyRows) {
          const stockContent = hasStockContent(row, LEGACY_DETAILED_NOTES);
          if (
            summaryIsStock &&
            stockContent &&
            row.name === LEGACY_DETAILED_NOTES.name &&
            row.description === LEGACY_DETAILED_NOTES.description &&
            row.icon === LEGACY_DETAILED_NOTES.icon
          ) {
            deleteLegacy.run(row.id);
          } else {
            // A previous flat default still moves to sections, keeping the user's name.
            const upgradesPrompt = stockContent && row.sections === null;
            settleLegacy.run(
              LEGACY_DETAILED_NOTES.translationKey,
              LEGACY_DETAILED_NOTES.sortOrder,
              upgradesPrompt ? LEGACY_DETAILED_NOTES.prompt : row.prompt,
              upgradesPrompt ? JSON.stringify(LEGACY_DETAILED_NOTES.sections) : row.sections,
              row.id
            );
          }
        }

        // Pre-2026 installs carry the default under an older key.
        this.db
          .prepare(
            `UPDATE actions SET translation_key = ? WHERE is_builtin = 1 AND (translation_key IS NULL OR translation_key NOT IN (${builtinKeys.map(() => "?").join(", ")}))`
          )
          .run(GENERATE_NOTES_KEY, ...builtinKeys);
      })();

      // Built-ins: insert any that are missing, and roll a new default out to rows
      // that are still a previous flat default (never a user edit). A built-in's
      // kind and destination are fixed, regardless of prompt edits.
      const selectBuiltin = this.db.prepare(
        "SELECT id, name, description, prompt, sections FROM actions WHERE is_builtin = 1 AND translation_key = ?"
      );
      const insertBuiltin = this.db.prepare(
        "INSERT INTO actions (name, description, prompt, icon, is_builtin, sort_order, translation_key, client_id, kind, sections, output) VALUES (?, ?, ?, ?, 1, ?, ?, ?, ?, ?, ?)"
      );
      const upgradeBuiltin = this.db.prepare(
        "UPDATE actions SET prompt = ?, sections = ? WHERE id = ?"
      );
      const settleBuiltin = this.db.prepare(
        "UPDATE actions SET client_id = ?, kind = ?, sort_order = ?, output = ?, name = ?, description = ?, icon = ? WHERE id = ?"
      );
      for (const action of BUILTIN_ACTIONS) {
        const sections = action.sections ? JSON.stringify(action.sections) : null;
        const existing = selectBuiltin.get(action.translationKey);
        if (!existing) {
          insertBuiltin.run(
            action.name,
            action.description,
            action.prompt,
            action.icon,
            action.sortOrder,
            action.translationKey,
            action.translationKey,
            action.kind,
            sections,
            action.output
          );
          continue;
        }
        const upgradesPrompt =
          existing.sections === null && action.previousPrompts.includes(existing.prompt);
        if (upgradesPrompt) {
          upgradeBuiltin.run(action.prompt, sections, existing.id);
        }
        settleBuiltin.run(
          action.translationKey,
          action.kind,
          action.sortOrder,
          action.output,
          action.previousNames?.includes(existing.name) ? action.name : existing.name,
          (
            action.previousDescriptions?.length
              ? action.previousDescriptions.includes(existing.description)
              : upgradesPrompt
          )
            ? action.description
            : existing.description,
          action.icon,
          existing.id
        );
      }

      const actionsWithoutClientId = this.db
        .prepare("SELECT id FROM actions WHERE client_id IS NULL")
        .all();
      const setActionClientId = this.db.prepare("UPDATE actions SET client_id = ? WHERE id = ?");
      for (const row of actionsWithoutClientId) setActionClientId.run(randomUUID(), row.id);
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_actions_client_id ON actions(client_id) WHERE client_id IS NOT NULL"
      );

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS google_calendar_tokens (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          google_email TEXT NOT NULL UNIQUE,
          access_token TEXT NOT NULL,
          refresh_token TEXT NOT NULL,
          expires_at INTEGER NOT NULL,
          scope TEXT NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Migration: add UNIQUE constraint to google_email if table already existed without it
      try {
        const tableInfo = this.db.pragma("index_list('google_calendar_tokens')");
        const hasUniqueEmail = tableInfo.some((idx) => {
          if (!idx.unique) return false;
          const cols = this.db.pragma(`index_info('${idx.name}')`);
          return cols.length === 1 && cols[0].name === "google_email";
        });
        if (!hasUniqueEmail) {
          this.db.exec(
            "CREATE UNIQUE INDEX IF NOT EXISTS idx_google_calendar_tokens_email ON google_calendar_tokens(google_email)"
          );
        }
      } catch (err) {
        debugLogger.error(
          "Migration: google_email unique index",
          { error: err.message },
          "database"
        );
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS google_calendars (
          id TEXT PRIMARY KEY,
          summary TEXT NOT NULL,
          description TEXT,
          background_color TEXT,
          is_selected INTEGER NOT NULL DEFAULT 1,
          sync_token TEXT,
          sync_token_expires_at INTEGER,
          account_email TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      try {
        this.db.exec("ALTER TABLE google_calendars ADD COLUMN account_email TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      try {
        this.db.exec("ALTER TABLE google_calendars ADD COLUMN sync_token_expires_at INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      try {
        this.db.exec(
          "ALTER TABLE google_calendars ADD COLUMN is_primary INTEGER NOT NULL DEFAULT 0"
        );
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS microsoft_calendar_tokens (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          microsoft_email TEXT NOT NULL UNIQUE,
          access_token TEXT NOT NULL,
          refresh_token TEXT NOT NULL,
          expires_at INTEGER NOT NULL,
          scope TEXT NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      try {
        this.db.exec("ALTER TABLE microsoft_calendar_tokens ADD COLUMN tenant_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE microsoft_calendar_tokens ADD COLUMN own_addresses TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS microsoft_calendars (
          id TEXT PRIMARY KEY,
          summary TEXT NOT NULL,
          background_color TEXT,
          is_selected INTEGER NOT NULL DEFAULT 1,
          is_primary INTEGER NOT NULL DEFAULT 0,
          sync_token TEXT,
          sync_token_expires_at INTEGER,
          account_email TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // One-time reset (user_version 2): pre-fix builds stored recurring
      // occurrences untitled when the series-master fetch failed, and delta
      // never re-delivers them; a forced full sync re-fetches them fixed.
      if (this.db.pragma("user_version", { simple: true }) < 2) {
        this.db.exec(
          "UPDATE microsoft_calendars SET sync_token = NULL, sync_token_expires_at = NULL"
        );
        this.db.pragma("user_version = 2");
      }

      // One-time reset (user_version 3): older builds stored rooms without a
      // resource flag, and incremental syncs never resend unchanged events; a
      // forced full sync stores them flagged, purges the rooms those builds
      // wrote to contacts and tags the contacts it sees with their source.
      if (this.db.pragma("user_version", { simple: true }) < 3) {
        this.db.exec("UPDATE google_calendars SET sync_token = NULL, sync_token_expires_at = NULL");
        this.db.exec(
          "UPDATE microsoft_calendars SET sync_token = NULL, sync_token_expires_at = NULL"
        );
        this.db.pragma("user_version = 3");
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS calendar_events (
          id TEXT PRIMARY KEY,
          calendar_id TEXT NOT NULL,
          summary TEXT,
          start_time TEXT NOT NULL,
          end_time TEXT NOT NULL,
          is_all_day INTEGER NOT NULL DEFAULT 0,
          status TEXT NOT NULL DEFAULT 'confirmed',
          availability_status TEXT NOT NULL DEFAULT 'unknown',
          self_response_status TEXT NOT NULL DEFAULT 'unknown',
          hangout_link TEXT,
          conference_data TEXT,
          organizer_email TEXT,
          attendees_count INTEGER DEFAULT 0,
          synced_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      try {
        this.db.exec(
          "ALTER TABLE calendar_events ADD COLUMN provider TEXT NOT NULL DEFAULT 'google'"
        );
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      let availabilitySchemaChanged = false;
      for (const column of ["availability_status", "self_response_status"]) {
        try {
          this.db.exec(
            `ALTER TABLE calendar_events ADD COLUMN ${column} TEXT NOT NULL DEFAULT 'unknown'`
          );
          availabilitySchemaChanged = true;
        } catch (err) {
          if (!err.message.includes("duplicate column")) throw err;
        }
      }
      if (availabilitySchemaChanged) {
        // Existing incremental tokens will not resend unchanged free/declined
        // events, so rebuild both REST caches once with the new semantics.
        this.db
          .prepare("UPDATE google_calendars SET sync_token = NULL, sync_token_expires_at = NULL")
          .run();
        this.db
          .prepare("UPDATE microsoft_calendars SET sync_token = NULL, sync_token_expires_at = NULL")
          .run();
      }

      // One-time reset (user_version 4): older builds took a shared Google
      // calendar owner's RSVP as the connected user's; clear those cached
      // responses and force a full sync of those calendars.
      if (this.db.pragma("user_version", { simple: true }) < 4) {
        this.db.exec(`
          UPDATE calendar_events SET self_response_status = 'unknown'
          WHERE provider = 'google' AND NOT EXISTS (
            SELECT 1 FROM google_calendars c
            WHERE c.id = calendar_events.calendar_id
              AND (c.is_primary = 1 OR c.id = c.account_email)
          );
          UPDATE google_calendars SET sync_token = NULL, sync_token_expires_at = NULL
          WHERE is_primary = 0 AND (account_email IS NULL OR id != account_email);
        `);
        this.db.pragma("user_version = 4");
      }

      // One-time reset (user_version 5): the stored lookback widened to
      // CALENDAR_LOOKBACK_DAYS so notes can link recent past events, and REST
      // sync tokens pin the window they were created with. The full sync also
      // fills calendar_events.web_link.
      if (this.db.pragma("user_version", { simple: true }) < 5) {
        this.db.exec(`
          UPDATE google_calendars SET sync_token = NULL, sync_token_expires_at = NULL;
          UPDATE microsoft_calendars SET sync_token = NULL, sync_token_expires_at = NULL;
        `);
        this.db.pragma("user_version = 5");
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS apple_calendars (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          color TEXT,
          source_name TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN transcript TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN calendar_event_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      try {
        this.db.exec("ALTER TABLE calendar_events ADD COLUMN attendees TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      // The event's page in Google Calendar or Outlook on the web; Apple has
      // none. The user_version 5 full sync fills it for rows in the sync window.
      try {
        this.db.exec("ALTER TABLE calendar_events ADD COLUMN web_link TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN participants TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN diarization_enabled INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN expected_speaker_count INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS contacts (
          email TEXT PRIMARY KEY,
          display_name TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Every source (see contactSource) that has seen a contact, so a
      // disconnect only removes people no other source still has. Rows older
      // builds stored have none.
      this.db.exec(`
        CREATE TABLE IF NOT EXISTS contact_sources (
          email TEXT NOT NULL REFERENCES contacts(email) ON DELETE CASCADE,
          source TEXT NOT NULL,
          PRIMARY KEY (email, source)
        )
      `);
      // Pre-release builds kept a single source in contacts.source.
      if (this.db.pragma("table_info(contacts)").some((column) => column.name === "source")) {
        this.db.exec(
          "INSERT OR IGNORE INTO contact_sources (email, source) SELECT email, source FROM contacts WHERE source IS NOT NULL"
        );
        this.db.exec("UPDATE contacts SET source = NULL WHERE source IS NOT NULL");
      }

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS speaker_profiles (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          display_name TEXT NOT NULL,
          email TEXT,
          embedding BLOB NOT NULL,
          sample_count INTEGER DEFAULT 1,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS speaker_mappings (
          note_id INTEGER NOT NULL,
          speaker_id TEXT NOT NULL,
          profile_id INTEGER,
          display_name TEXT NOT NULL,
          PRIMARY KEY (note_id, speaker_id),
          FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
          FOREIGN KEY (profile_id) REFERENCES speaker_profiles(id) ON DELETE SET NULL
        )
      `);

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS note_speaker_embeddings (
          note_id INTEGER NOT NULL,
          speaker_id TEXT NOT NULL,
          embedding BLOB NOT NULL,
          PRIMARY KEY (note_id, speaker_id),
          FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE
        )
      `);

      // A cloud folder delete is optimistic until the API answers. Keep the
      // exact pre-delete row state in a durable journal so a permission denial
      // can revive the same folder, notes, speakers, and conversations even
      // after an app restart. Content stays in its owning tables; the journal
      // contains only identity and sync metadata.
      this.db.exec(`
        CREATE TABLE IF NOT EXISTS optimistic_folder_delete_rows (
          folder_id INTEGER NOT NULL,
          entity_type TEXT NOT NULL,
          entity_id INTEGER NOT NULL,
          original_sync_status TEXT,
          original_deleted_at TEXT,
          original_updated_at TEXT,
          PRIMARY KEY (folder_id, entity_type, entity_id)
        )
      `);
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_optimistic_folder_delete_entity ON optimistic_folder_delete_rows(entity_type, entity_id)"
      );

      // Sync columns for notes
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN client_note_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN sync_status TEXT DEFAULT 'pending'");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN deleted_at TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN is_shared INTEGER NOT NULL DEFAULT 0");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN share_token TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Sync columns for folders
      try {
        this.db.exec("ALTER TABLE folders ADD COLUMN client_folder_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE folders ADD COLUMN cloud_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE folders ADD COLUMN sync_status TEXT DEFAULT 'pending'");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE folders ADD COLUMN deleted_at TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE folders ADD COLUMN updated_at DATETIME");
        this.db.exec("UPDATE folders SET updated_at = created_at WHERE updated_at IS NULL");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Sync columns for agent_conversations
      try {
        this.db.exec("ALTER TABLE agent_conversations ADD COLUMN client_conversation_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec(
          "ALTER TABLE agent_conversations ADD COLUMN sync_status TEXT DEFAULT 'pending'"
        );
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE agent_conversations ADD COLUMN deleted_at TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Sync columns for transcriptions
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN client_transcription_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN cloud_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN sync_status TEXT DEFAULT 'pending'");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE transcriptions ADD COLUMN deleted_at TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Sync columns for custom_dictionary
      try {
        this.db.exec("ALTER TABLE custom_dictionary ADD COLUMN client_dict_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE custom_dictionary ADD COLUMN cloud_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec(
          "ALTER TABLE custom_dictionary ADD COLUMN source TEXT NOT NULL DEFAULT 'manual'"
        );
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE custom_dictionary ADD COLUMN sync_status TEXT DEFAULT 'pending'");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE custom_dictionary ADD COLUMN deleted_at TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE custom_dictionary ADD COLUMN updated_at DATETIME");
        this.db.exec(
          "UPDATE custom_dictionary SET updated_at = created_at WHERE updated_at IS NULL"
        );
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Backfill client IDs for existing rows
      const syncTables = [
        { table: "notes", col: "client_note_id" },
        { table: "folders", col: "client_folder_id" },
        { table: "agent_conversations", col: "client_conversation_id" },
        { table: "transcriptions", col: "client_transcription_id" },
        { table: "custom_dictionary", col: "client_dict_id" },
        { table: "snippets", col: "client_snippet_id" },
      ];
      for (const { table, col } of syncTables) {
        const rows = this.db.prepare(`SELECT id FROM ${table} WHERE ${col} IS NULL`).all();
        const stmt = this.db.prepare(`UPDATE ${table} SET ${col} = ? WHERE id = ?`);
        for (const row of rows) {
          stmt.run(randomUUID(), row.id);
        }
      }

      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_notes_client_note_id ON notes(client_note_id)"
      );
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_folders_client_folder_id ON folders(client_folder_id)"
      );
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_conversations_client_id ON agent_conversations(client_conversation_id)"
      );
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_transcriptions_client_id ON transcriptions(client_transcription_id)"
      );

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS analytics_events (
          event_id TEXT PRIMARY KEY,
          account_id TEXT,
          occurred_at TEXT NOT NULL,
          local_date TEXT NOT NULL,
          word_count INTEGER NOT NULL CHECK (word_count > 0),
          spoken_duration_ms INTEGER,
          mode TEXT NOT NULL,
          provider TEXT,
          model TEXT,
          counter_version INTEGER NOT NULL DEFAULT 1,
          sync_status TEXT NOT NULL DEFAULT 'pending',
          deleted_at TEXT,
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE INDEX IF NOT EXISTS idx_analytics_events_account_date
          ON analytics_events(account_id, local_date);
        CREATE TABLE IF NOT EXISTS analytics_clear_requests (
          account_id TEXT PRIMARY KEY,
          cleared_through TEXT NOT NULL,
          synced INTEGER NOT NULL DEFAULT 0
        );
        CREATE TABLE IF NOT EXISTS analytics_device_clear_state (
          id INTEGER PRIMARY KEY CHECK (id = 1),
          cleared_through TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS analytics_history_backfill_state (
          id INTEGER PRIMARY KEY CHECK (id = 1),
          version INTEGER NOT NULL,
          scanned_through_transcription_id INTEGER NOT NULL DEFAULT 0
            CHECK (scanned_through_transcription_id >= 0)
        );
      `);
      // Repair databases created before analytics deletion tombstones were
      // introduced. SQLite has no ADD COLUMN IF NOT EXISTS syntax.
      try {
        this.db.exec("ALTER TABLE analytics_events ADD COLUMN deleted_at TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_custom_dictionary_client_id ON custom_dictionary(client_dict_id)"
      );
      // Cloud batch-create matches responses by client_dict_id, so a row
      // without one can never be marked synced and re-uploads every pass. Rows
      // written straight to SQLite don't set it (#1295), so the schema does.
      this.db.exec(`
        CREATE TRIGGER IF NOT EXISTS custom_dictionary_client_id_default
        AFTER INSERT ON custom_dictionary
        WHEN new.client_dict_id IS NULL
        BEGIN
          UPDATE custom_dictionary SET client_dict_id =
            lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' ||
            substr(lower(hex(randomblob(2))), 2) || '-' ||
            substr('89ab', abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))), 2) ||
            '-' || lower(hex(randomblob(6)))
          WHERE id = new.id;
        END
      `);
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_snippets_client_id ON snippets(client_snippet_id) WHERE client_snippet_id IS NOT NULL"
      );
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_snippets_trigger_lower_active ON snippets(lower(trigger)) WHERE deleted_at IS NULL"
      );
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_snippets_pending_sync ON snippets(sync_status) WHERE sync_status = 'pending'"
      );

      this.db.exec(`
        CREATE TABLE IF NOT EXISTS spaces (
          id              INTEGER PRIMARY KEY AUTOINCREMENT,
          client_space_id TEXT,
          cloud_team_id   TEXT,
          workspace_id    TEXT,
          kind            TEXT NOT NULL DEFAULT 'team' CHECK (kind IN ('private','team')),
          name            TEXT NOT NULL,
          emoji           TEXT,
          sort_order      INTEGER NOT NULL DEFAULT 0,
          my_role         TEXT,
          member_count    INTEGER,
          sync_status     TEXT NOT NULL DEFAULT 'pending',
          deleted_at      TEXT,
          created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_spaces_client_space_id ON spaces(client_space_id)"
      );
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_spaces_cloud_team_id ON spaces(cloud_team_id) WHERE cloud_team_id IS NOT NULL"
      );

      // First-class spaces: a space mirrors a cloud space with one or more
      // assigned teams. cloud_team_id survives only so pre-spaces rows can be
      // adopted by upsertSpaceFromCloud (matched via the space's single
      // backfilled team).
      try {
        this.db.exec("ALTER TABLE spaces ADD COLUMN cloud_space_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        // JSON array of { id, name, my_role } mirrored from GET /api/me/spaces.
        this.db.exec("ALTER TABLE spaces ADD COLUMN teams TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        // Direct space_members grant ('admin' | 'member'), distinct from the
        // effective my_role: it decides whether the user can leave the space.
        this.db.exec("ALTER TABLE spaces ADD COLUMN my_direct_role TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_spaces_cloud_space_id ON spaces(cloud_space_id) WHERE cloud_space_id IS NOT NULL"
      );

      const privateSpaceCount = this.db
        .prepare("SELECT COUNT(*) as count FROM spaces WHERE kind = 'private'")
        .get();
      if (privateSpaceCount.count === 0) {
        this.db
          .prepare(
            "INSERT INTO spaces (client_space_id, kind, name, sort_order, sync_status) VALUES (?, 'private', 'Personal', 0, 'synced')"
          )
          .run(randomUUID());
      }

      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN space_id INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE folders ADD COLUMN space_id INTEGER");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Rebuild folders to drop the table-level UNIQUE(name); per-space name
      // uniqueness is enforced by idx_folders_space_name below.
      // better-sqlite3 enables foreign_keys by default, so DROP TABLE folders
      // would fail while notes.folder_id rows reference it; the pragma is a
      // no-op inside a transaction, so toggle it around the rebuild.
      const foldersTable = this.db
        .prepare("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'folders'")
        .get();
      if (foldersTable?.sql.includes("UNIQUE")) {
        const foreignKeysWereOn = this.db.pragma("foreign_keys", { simple: true }) === 1;
        this.db.pragma("foreign_keys = OFF");
        try {
          this.db.transaction(() => {
            this.db.exec(`
            CREATE TABLE folders_new (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name TEXT NOT NULL,
              is_default INTEGER NOT NULL DEFAULT 0,
              sort_order INTEGER NOT NULL DEFAULT 0,
              created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
              updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
              client_folder_id TEXT,
              cloud_id TEXT,
              sync_status TEXT DEFAULT 'pending',
              deleted_at TEXT,
              space_id INTEGER
            )
          `);
            this.db.exec(`
            INSERT INTO folders_new (id, name, is_default, sort_order, created_at, updated_at,
              client_folder_id, cloud_id, sync_status, deleted_at, space_id)
            SELECT id, name, is_default, sort_order, created_at, updated_at,
              client_folder_id, cloud_id, sync_status, deleted_at, space_id
            FROM folders
          `);
            this.db.exec("DROP TABLE folders");
            this.db.exec("ALTER TABLE folders_new RENAME TO folders");
            this.db.exec(
              "CREATE UNIQUE INDEX IF NOT EXISTS idx_folders_client_folder_id ON folders(client_folder_id)"
            );
          })();
        } finally {
          if (foreignKeysWereOn) this.db.pragma("foreign_keys = ON");
        }
      }
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_folders_space_name ON folders(space_id, name) WHERE deleted_at IS NULL"
      );

      const privateSpace = this.db.prepare("SELECT id FROM spaces WHERE kind = 'private'").get();
      this.db
        .prepare("UPDATE folders SET space_id = ? WHERE space_id IS NULL")
        .run(privateSpace.id);
      this.db.prepare("UPDATE notes SET space_id = ? WHERE space_id IS NULL").run(privateSpace.id);

      this.db.exec("CREATE INDEX IF NOT EXISTS idx_notes_folder_id ON notes(folder_id)");
      this.db.exec("CREATE INDEX IF NOT EXISTS idx_notes_updated_at ON notes(updated_at)");
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_notes_space_updated ON notes(space_id, updated_at)"
      );
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_folders_space_sort ON folders(space_id, sort_order)"
      );

      // Account attribution is intentionally nullable. Existing rows remain
      // device-owned legacy content; workspace rows are never attributed to a
      // personal account and therefore cannot be erased with that account.
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN account_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE folders ADD COLUMN account_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      this.db.exec("CREATE INDEX IF NOT EXISTS idx_notes_account_id ON notes(account_id)");
      this.db.exec("CREATE INDEX IF NOT EXISTS idx_folders_account_id ON folders(account_id)");
      this.db.exec("DROP INDEX IF EXISTS idx_folders_space_name");
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_folders_space_legacy_name ON folders(space_id, name) WHERE deleted_at IS NULL AND account_id IS NULL"
      );
      this.db.exec(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_folders_space_account_name ON folders(space_id, account_id, name) WHERE deleted_at IS NULL AND account_id IS NOT NULL"
      );

      // A cloud space is workspace-owned, while visibility is account-specific.
      // Keep the many-to-many membership separate so signing out one local
      // account cannot delete or expose another account's workspace cache.
      this.db.exec(`
        CREATE TABLE IF NOT EXISTS space_accounts (
          space_id INTEGER NOT NULL REFERENCES spaces(id) ON DELETE CASCADE,
          account_id TEXT NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (space_id, account_id)
        )
      `);
      this.db.exec(
        "CREATE INDEX IF NOT EXISTS idx_space_accounts_account_id ON space_accounts(account_id)"
      );

      // Cloud-backed rows that just LEFT a team must keep pushing their scope
      // retraction (D6) even in the backup-off team-only pass, where the
      // pending queues otherwise filter on the row's CURRENT space kind.
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN left_team INTEGER NOT NULL DEFAULT 0");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE folders ADD COLUMN left_team INTEGER NOT NULL DEFAULT 0");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Last cloud editor (CloudNote.updated_by_user_id); only populated on
      // cloud pull — local edits don't set it. Resolved to a display name via
      // the team roster when rendering note authorship.
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN updated_by_user_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Server updated_at this device last acked (push response or pull),
      // echoed verbatim as base_updated_at on the next PATCH so the server can
      // 409 a stale overwrite. Local edits never touch it; NULL means the note
      // predates the guard and pushes last-write-wins once.
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN cloud_updated_at TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // The note's owner (CloudNote.user_id) — who created it, not who last
      // edited it (updated_by_user_id). Drives the client-side delete/scope
      // permission checks; NULL until a cloud pull or push response fills it.
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN owner_user_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }
      try {
        this.db.exec("ALTER TABLE notes ADD COLUMN created_by_user_id TEXT");
      } catch (err) {
        if (!err.message.includes("duplicate column")) throw err;
      }

      // Space vector purges owed to Qdrant while the sidecar was down/booting;
      // drained on the next semantic search activation.
      this.db.exec(`
        CREATE TABLE IF NOT EXISTS pending_vector_purges (
          space_id   INTEGER PRIMARY KEY,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);
      this._initVectorChangeJournal();
      try {
        noteUndo.initializeNoteUndo(this.db);
        this.noteUndoReady = true;
      } catch (error) {
        // Undo is a convenience: it must never keep the notes database from
        // opening. Without it, assistant edits save as plain updates.
        debugLogger.error("Assistant note Undo unavailable", { error: error.message }, "database");
      }

      return true;
    } catch (error) {
      debugLogger.error("Database initialization failed", { error: error.message }, "database");
      throw error;
    }
  }

  saveTranscription(
    text,
    rawText = null,
    {
      status = "completed",
      errorMessage = null,
      errorCode = null,
      routeKind = null,
      clientTranscriptionId = randomUUID(),
      analyticsOccurredAt = null,
    } = {}
  ) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      // With an occurrence time this column carries when the dictation was
      // spoken rather than when the row was written -- earlier by the length
      // of the recording plus transcription. History reads it through
      // normalizeDbDate, which already branches on a trailing zone.
      // Keep the existing SQLite-friendly separator so mixed old/new rows
      // continue to sort chronologically, while the trailing Z marks this as
      // an exact client-captured instant for clear-state reconciliation.
      const occurredAt = toDbTimestamp(analyticsOccurredAt);
      const stmt = this.db.prepare(
        `INSERT INTO transcriptions (
           text, raw_text, status, error_message, error_code, route_kind,
           client_transcription_id, timestamp
         ) VALUES (?, ?, ?, ?, ?, ?, ?, COALESCE(?, CURRENT_TIMESTAMP))`
      );
      const result = stmt.run(
        text,
        rawText,
        status,
        errorMessage,
        errorCode,
        routeKind,
        clientTranscriptionId,
        occurredAt
      );

      const fetchStmt = this.db.prepare("SELECT * FROM transcriptions WHERE id = ?");
      const transcription = fetchStmt.get(result.lastInsertRowid);

      return { id: result.lastInsertRowid, success: true, transcription };
    } catch (error) {
      debugLogger.error("Error saving transcription", { error: error.message }, "database");
      throw error;
    }
  }

  _ensureAnalyticsHistoryBackfillState(version) {
    this.db
      .prepare(
        `INSERT INTO analytics_history_backfill_state (
           id, version, scanned_through_transcription_id
         ) VALUES (1, ?, 0)
         ON CONFLICT(id) DO UPDATE SET
           version = excluded.version,
           scanned_through_transcription_id = 0
         WHERE analytics_history_backfill_state.version <> excluded.version`
      )
      .run(version);
    return this.db
      .prepare(
        `SELECT version, scanned_through_transcription_id
         FROM analytics_history_backfill_state WHERE id = 1`
      )
      .get();
  }

  getAnalyticsHistoryBackfillState(version = ANALYTICS_HISTORY_BACKFILL_VERSION) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const safeVersion = Math.max(1, Math.trunc(Number(version)) || 1);
      return this.db.transaction(() => {
        const state = this._ensureAnalyticsHistoryBackfillState(safeVersion);
        const target = this.db
          .prepare("SELECT COALESCE(MAX(id), 0) AS id FROM transcriptions")
          .get();
        return {
          version: safeVersion,
          scannedThroughId: Number(state.scanned_through_transcription_id),
          targetId: Number(target.id),
        };
      })();
    } catch (error) {
      debugLogger.error(
        "Error reading analytics history backfill state",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  _invalidateAnalyticsHistoryFromTranscription(id) {
    const resumeBeforeId = Math.max(0, Math.trunc(Number(id)) - 1);
    this.db
      .prepare(
        `INSERT INTO analytics_history_backfill_state (
           id, version, scanned_through_transcription_id
         ) VALUES (1, ?, 0)
         ON CONFLICT(id) DO UPDATE SET
           version = excluded.version,
           scanned_through_transcription_id = CASE
             WHEN analytics_history_backfill_state.version = excluded.version
             THEN MIN(
               analytics_history_backfill_state.scanned_through_transcription_id,
               ?
             )
             ELSE 0
           END`
      )
      .run(ANALYTICS_HISTORY_BACKFILL_VERSION, resumeBeforeId);
  }

  backfillAnalyticsHistoryBatch({
    afterId = 0,
    throughId = null,
    checkpointVersion = null,
    limit = 250,
  } = {}) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const safeLimit = Math.max(1, Math.min(Math.trunc(Number(limit)) || 250, 1_000));
      const safeAfterId = Math.max(0, Math.trunc(Number(afterId)) || 0);
      const safeThroughId =
        throughId === null || throughId === undefined
          ? null
          : Math.max(0, Math.trunc(Number(throughId)) || 0);
      const safeCheckpointVersion =
        checkpointVersion === null || checkpointVersion === undefined
          ? null
          : Math.max(1, Math.trunc(Number(checkpointVersion)) || 1);

      return this.db.transaction(() => {
        const checkpoint =
          safeCheckpointVersion === null
            ? null
            : this._ensureAnalyticsHistoryBackfillState(safeCheckpointVersion);
        const effectiveAfterId = checkpoint
          ? Number(checkpoint.scanned_through_transcription_id)
          : safeAfterId;
        if (safeThroughId !== null && effectiveAfterId >= safeThroughId) {
          return {
            complete: true,
            nextCursor: effectiveAfterId,
            scanned: 0,
            inserted: 0,
            skipped: 0,
          };
        }

        const clearState = this.db
          .prepare("SELECT cleared_through FROM analytics_device_clear_state WHERE id = 1")
          .get();
        // Legacy SQLite timestamps are completion times without an offset. Once
        // the user has cleared Insights, only a client-captured occurrence time
        // can prove that a historical row happened afterward, so an ambiguous
        // legacy row stays out rather than reviving a cleared counter. That is
        // the eligibility rule below; the boundary on the instant actually
        // written is enforced in the loop, where the chosen value is known.
        const rows = this.db
          .prepare(
            `SELECT transcription.id, transcription.client_transcription_id,
                    transcription.text, transcription.raw_text, transcription.timestamp,
                    transcription.created_at,
                    audio_duration_ms, provider, model
             FROM transcriptions transcription
             WHERE transcription.id > ?
               AND (? IS NULL OR transcription.id <= ?)
               AND transcription.deleted_at IS NULL
               AND transcription.status = 'completed'
               AND TRIM(COALESCE(NULLIF(TRIM(transcription.raw_text), ''), transcription.text, '')) != ''
               AND NOT EXISTS (
                 SELECT 1 FROM analytics_events event
                 WHERE event.event_id = TRIM(transcription.client_transcription_id)
               )
               AND (
                 ? IS NULL
                 OR (
                   (TRIM(transcription.timestamp) LIKE '%Z'
                    OR SUBSTR(TRIM(transcription.timestamp), -6, 1) IN ('+', '-'))
                   AND JULIANDAY(transcription.timestamp) > JULIANDAY(?)
                 )
               )
             ORDER BY transcription.id ASC
             LIMIT ?`
          )
          .all(
            effectiveAfterId,
            safeThroughId,
            safeThroughId,
            clearState?.cleared_through ?? null,
            clearState?.cleared_through ?? null,
            safeLimit
          );

        let inserted = 0;
        let skipped = 0;
        const clearedThrough = clearState ? Date.parse(clearState.cleared_through) : null;
        const insert = this.db.prepare(
          `INSERT INTO analytics_events (
             event_id, account_id, occurred_at, local_date, word_count,
             spoken_duration_ms, mode, provider, model, counter_version, created_at
           ) VALUES (?, NULL, ?, ?, ?, ?, ?, ?, ?, ?, DATETIME(?))
           ON CONFLICT(event_id) DO NOTHING`
        );
        const assignClientId = this.db.prepare(
          `UPDATE transcriptions SET client_transcription_id = ?
           WHERE id = ? AND (client_transcription_id IS NULL OR TRIM(client_transcription_id) = '')`
        );

        for (const row of rows) {
          const sourceText = row.raw_text?.trim() ? row.raw_text : row.text;
          const wordCount = countSpokenWords(sourceText);
          if (wordCount === 0) {
            skipped += 1;
            continue;
          }

          const createdAt = parseDbTimestamp(row.created_at);
          // A naive timestamp can be a sync artifact rather than an occurrence
          // time, so it is never the answer: created_at carries the cloud row's
          // own instant, while timestamp defaulted to the moment of the pull.
          const occurredAt =
            (hasExplicitTimeZone(row.timestamp) ? parseDbTimestamp(row.timestamp) : null) ??
            createdAt;
          // Guessing a date would put an old dictation on today, inflating
          // today's counters and manufacturing a current streak out of a row
          // whose age we could not read. It stays out instead.
          if (!occurredAt) {
            skipped += 1;
            continue;
          }
          // Not a restatement of the query's clear filter: that one decides
          // eligibility from transcription.timestamp, while this guards the
          // instant actually chosen, which may be created_at. It also catches
          // what the SQL shape test cannot -- a bare YYYY-MM-DD reads as zoned
          // there, its day hyphen sitting six characters from the end.
          if (clearedThrough !== null && occurredAt.getTime() <= clearedThrough) {
            skipped += 1;
            continue;
          }

          const eventId = row.client_transcription_id?.trim() || randomUUID();
          if (!row.client_transcription_id?.trim()) assignClientId.run(eventId, row.id);
          const result = insert.run(
            eventId,
            occurredAt.toISOString(),
            localDateKey(occurredAt),
            wordCount,
            Number(row.audio_duration_ms) > 0 ? Number(row.audio_duration_ms) : null,
            inferHistoricalAnalyticsMode(row.provider),
            row.provider || null,
            row.model || null,
            ANALYTICS_HISTORICAL_COUNTER_VERSION,
            (createdAt ?? occurredAt).toISOString()
          );
          if (result.changes > 0) inserted += 1;
          else skipped += 1;
        }

        const complete = rows.length < safeLimit;
        const lastCandidateId =
          rows.length > 0 ? Number(rows[rows.length - 1].id) : effectiveAfterId;
        const nextCursor = complete && safeThroughId !== null ? safeThroughId : lastCandidateId;
        if (checkpoint) {
          this.db
            .prepare(
              `UPDATE analytics_history_backfill_state
               SET scanned_through_transcription_id = ?
               WHERE id = 1 AND version = ?`
            )
            .run(nextCursor, safeCheckpointVersion);
        }

        return {
          complete,
          nextCursor,
          scanned: rows.length,
          inserted,
          skipped,
        };
      })();
    } catch (error) {
      debugLogger.error(
        "Error backfilling analytics history",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  recordAnalyticsEvent({
    eventId,
    wordCount,
    occurredAt,
    localDate,
    spokenDurationMs = null,
    mode = "unknown",
    provider = null,
    model = null,
  }) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (wordCount === 0) return { success: true, ignored: true };
      // Clear History is device-wide, so an in-flight recording must stay
      // cleared even if its account changes before this late write lands.
      const cleared = this.db
        .prepare(
          `SELECT 1 FROM analytics_device_clear_state
           WHERE id = 1 AND ? <= cleared_through`
        )
        .get(occurredAt);
      if (cleared) return { success: true, ignored: true };
      this.db
        .prepare(
          `INSERT INTO analytics_events (
             event_id, account_id, occurred_at, local_date, word_count,
             spoken_duration_ms, mode, provider, model, counter_version
           ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
           ON CONFLICT(event_id) DO UPDATE SET
             account_id = COALESCE(analytics_events.account_id, excluded.account_id),
             occurred_at = excluded.occurred_at,
             local_date = excluded.local_date,
             word_count = excluded.word_count,
             spoken_duration_ms = COALESCE(
               excluded.spoken_duration_ms,
               analytics_events.spoken_duration_ms
             ),
             mode = excluded.mode,
             provider = COALESCE(excluded.provider, analytics_events.provider),
             model = COALESCE(excluded.model, analytics_events.model),
             counter_version = excluded.counter_version,
             sync_status = 'pending'
           WHERE analytics_events.deleted_at IS NULL`
        )
        .run(
          eventId,
          this.activeAccountId,
          occurredAt,
          localDate,
          wordCount,
          Number(spokenDurationMs) > 0 ? Number(spokenDurationMs) : null,
          mode,
          provider,
          model,
          ANALYTICS_COUNTER_VERSION
        );
      return { success: true, eventId };
    } catch (error) {
      debugLogger.error("Error recording analytics event", { error: error.message }, "database");
      throw error;
    }
  }

  getAnalyticsSummary() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      // Grouped in SQL so the row count is bounded by distinct days rather than
      // by dictations; summarizeAnalyticsDays still owns every derived figure.
      // Device-scoped by design: account_id only attributes rows for cloud
      // sync, so filtering on it here would blank the view on every sign-out.
      const days = this.db
        .prepare(
          `SELECT local_date AS date,
                  SUM(word_count) AS words,
                  COUNT(*) AS dictations,
                  SUM(CASE WHEN spoken_duration_ms > 0 THEN spoken_duration_ms ELSE 0 END)
                    AS spokenDurationMs,
                  SUM(CASE WHEN spoken_duration_ms > 0 THEN word_count ELSE 0 END)
                    AS coveredWords
           FROM analytics_events
           WHERE deleted_at IS NULL
           GROUP BY local_date`
        )
        .all();
      return summarizeAnalyticsDays(days);
    } catch (error) {
      debugLogger.error("Error reading analytics summary", { error: error.message }, "database");
      throw error;
    }
  }

  analyticsAccountId(expectedAccountId) {
    if (expectedAccountId == null) return this.activeAccountId;
    if (expectedAccountId !== this.activeAccountId) {
      throw Object.assign(new Error("Analytics account context changed"), {
        code: "AUTH_CONTEXT_CHANGED",
      });
    }
    return expectedAccountId;
  }

  getPendingAnalyticsEvents(limit = 200, expectedAccountId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountId = this.analyticsAccountId(expectedAccountId);
      if (!accountId) return [];
      const safeLimit = Math.max(1, Math.min(Number(limit) || 200, 200));
      // The projection is the wire shape: AnalyticsService posts these rows
      // verbatim, so every column here has to satisfy the batch endpoint's
      // event schema -- occurred_at included, which that schema requires
      // alongside local_date. Exact events go first so rejected historical
      // rows cannot block current activity during an API rollback.
      return this.db
        .prepare(
          `SELECT event_id, occurred_at, local_date, word_count, spoken_duration_ms,
                  mode, provider, model, counter_version
           FROM analytics_events
           WHERE account_id = ? AND sync_status = 'pending' AND deleted_at IS NULL
           ORDER BY (counter_version = 0) ASC, occurred_at ASC LIMIT ?`
        )
        .all(accountId, safeLimit);
    } catch (error) {
      debugLogger.error("Error reading pending analytics", { error: error.message }, "database");
      throw error;
    }
  }

  markAnalyticsEventsSynced(eventIds, expectedAccountId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountId = this.analyticsAccountId(expectedAccountId);
      if (!accountId || !Array.isArray(eventIds) || eventIds.length === 0) {
        return { success: true, updated: 0 };
      }
      const placeholders = eventIds.map(() => "?").join(", ");
      const result = this.db
        .prepare(
          `UPDATE analytics_events SET sync_status = 'synced'
           WHERE account_id = ? AND deleted_at IS NULL
             AND event_id IN (${placeholders})`
        )
        .run(accountId, ...eventIds);
      return { success: true, updated: result.changes };
    } catch (error) {
      debugLogger.error("Error marking analytics synced", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingAnalyticsDeletes(limit = 200, expectedAccountId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountId = this.analyticsAccountId(expectedAccountId);
      if (!accountId) return [];
      const safeLimit = Math.max(1, Math.min(Number(limit) || 200, 200));
      return this.db
        .prepare(
          `SELECT event_id FROM analytics_events
           WHERE account_id = ? AND deleted_at IS NOT NULL AND sync_status = 'pending'
           ORDER BY occurred_at ASC LIMIT ?`
        )
        .all(accountId, safeLimit);
    } catch (error) {
      debugLogger.error(
        "Error reading pending analytics deletes",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  hardDeleteAnalyticsEvents(eventIds, expectedAccountId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountId = this.analyticsAccountId(expectedAccountId);
      if (!accountId || !Array.isArray(eventIds) || eventIds.length === 0) {
        return { success: true, deleted: 0 };
      }
      const placeholders = eventIds.map(() => "?").join(", ");
      const result = this.db
        .prepare(
          `DELETE FROM analytics_events
           WHERE account_id = ? AND deleted_at IS NOT NULL
             AND event_id IN (${placeholders})`
        )
        .run(accountId, ...eventIds);
      return { success: true, deleted: result.changes };
    } catch (error) {
      debugLogger.error(
        "Error deleting synced analytics tombstones",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getPendingAnalyticsClear(expectedAccountId) {
    if (!this.db) throw new Error("Database not initialized");
    const accountId = this.analyticsAccountId(expectedAccountId);
    if (!accountId) return null;
    return (
      this.db
        .prepare(
          `SELECT cleared_through FROM analytics_clear_requests
           WHERE account_id = ? AND synced = 0`
        )
        .get(accountId) ?? null
    );
  }

  completeAnalyticsClear(clearedThrough, expectedAccountId) {
    if (!this.db) throw new Error("Database not initialized");
    const accountId = this.analyticsAccountId(expectedAccountId);
    if (!accountId || typeof clearedThrough !== "string") {
      return { success: false, deleted: 0 };
    }

    const complete = this.db.transaction(() => {
      const request = this.db
        .prepare(
          `UPDATE analytics_clear_requests SET synced = 1
           WHERE account_id = ? AND cleared_through = ? AND synced = 0`
        )
        .run(accountId, clearedThrough);
      if (request.changes === 0) return 0;
      return this.db
        .prepare(
          `DELETE FROM analytics_events
           WHERE account_id = ? AND occurred_at <= ?`
        )
        .run(accountId, clearedThrough).changes;
    });
    return { success: true, deleted: complete() };
  }

  countUnclaimedAnalyticsEvents() {
    if (!this.db) throw new Error("Database not initialized");
    return this.db
      .prepare(
        "SELECT COUNT(*) AS count FROM analytics_events WHERE account_id IS NULL AND deleted_at IS NULL"
      )
      .get().count;
  }

  // Everything turning Insights sync on would upload: this account's queued
  // rows plus the pre-sign-in ones the prompt offers to claim.
  //
  // The claim count alone is not that number and badly understates it. Every
  // dictation made while signed in is already attributed to the account, so a
  // user who had been signed in for months had nothing "unclaimed" — the
  // consent prompt never opened, and flipping the toggle uploaded their entire
  // history in one pass.
  countAnalyticsEventsAwaitingUpload(expectedAccountId) {
    if (!this.db) throw new Error("Database not initialized");
    const accountId = this.analyticsAccountId(expectedAccountId);
    return this.db
      .prepare(
        `SELECT COUNT(*) AS count FROM analytics_events
         WHERE deleted_at IS NULL AND sync_status <> 'synced'
           AND (account_id IS NULL OR account_id = ?)`
      )
      .get(accountId).count;
  }

  // Device-local rows stay unattributed until the signed-in user explicitly
  // asks for them, so signing in never silently adopts someone else's history.
  claimAnonymousAnalyticsEvents(expectedAccountId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountId =
        typeof expectedAccountId === "string" && expectedAccountId.trim().length > 0
          ? expectedAccountId.trim()
          : null;
      if (!accountId || accountId !== this.activeAccountId) {
        return { success: false, claimed: 0 };
      }
      const result = this.db
        .prepare(
          "UPDATE analytics_events SET account_id = ? WHERE account_id IS NULL AND deleted_at IS NULL"
        )
        .run(accountId);
      return { success: true, claimed: result.changes };
    } catch (error) {
      debugLogger.error("Error claiming analytics events", { error: error.message }, "database");
      throw error;
    }
  }

  getTranscriptions(limit = 50, { includeDiscarded = false } = {}) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const statusFilter = includeDiscarded ? "" : " AND status != 'discarded'";
      const stmt = this.db.prepare(
        `SELECT * FROM transcriptions WHERE deleted_at IS NULL${statusFilter} ORDER BY timestamp DESC LIMIT ?`
      );
      const transcriptions = stmt.all(limit);
      return transcriptions;
    } catch (error) {
      debugLogger.error("Error getting transcriptions", { error: error.message }, "database");
      throw error;
    }
  }

  clearTranscriptions() {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const tombstone = this.db.prepare(
        "UPDATE transcriptions SET deleted_at = datetime('now'), sync_status = 'pending' WHERE cloud_id IS NOT NULL AND deleted_at IS NULL"
      );
      const hardDelete = this.db.prepare("DELETE FROM transcriptions WHERE cloud_id IS NULL");
      // One rule decides every row: a counter the cloud never received is
      // erased outright, and only a counter it did receive leaves a tombstone
      // behind for the delete pusher.
      //
      // It matters in both directions. Tombstoning a row that was never
      // uploaded sent its event id to the server on the next pass — for an
      // account that never turned Insights sync on, that was the only
      // analytics traffic it ever produced, and the server stores a row per id
      // it is asked to delete. Hard-deleting a row that *was* uploaded stranded
      // it in the cloud with nothing left on the device to erase it, which is
      // what signing out before clearing used to do.
      //
      // Scope follows the credentials: only the active account can be erased
      // remotely, so another account's synced rows keep their tombstones until
      // that account signs in here again.
      const hardDeleteLocalAnalytics = this.db.prepare(
        `DELETE FROM analytics_events
         WHERE account_id IS NULL OR (sync_status <> 'synced' AND deleted_at IS NULL)`
      );
      const tombstoneSyncedAnalytics = this.db.prepare(
        `UPDATE analytics_events
         SET deleted_at = ?, sync_status = 'pending'
         WHERE sync_status = 'synced' AND deleted_at IS NULL`
      );
      const countSyncedAnalytics = this.db.prepare(
        "SELECT COUNT(*) AS count FROM analytics_events WHERE account_id = ? AND sync_status = 'synced'"
      );
      const queueAnalyticsClear = this.db.prepare(
        `INSERT INTO analytics_clear_requests (account_id, cleared_through, synced)
         VALUES (?, ?, 0)
         ON CONFLICT(account_id) DO UPDATE SET
           cleared_through = MAX(analytics_clear_requests.cleared_through, excluded.cleared_through),
           synced = 0`
      );
      const updateDeviceClearState = this.db.prepare(
        `INSERT INTO analytics_device_clear_state (id, cleared_through)
         VALUES (1, ?)
         ON CONFLICT(id) DO UPDATE SET
           cleared_through = MAX(analytics_device_clear_state.cleared_through, excluded.cleared_through)`
      );
      const clearedThrough = new Date().toISOString();
      const clearAll = this.db.transaction(() => {
        const cleared = tombstone.run().changes + hardDelete.run().changes;
        updateDeviceClearState.run(clearedThrough);
        // The account-wide cutoff is what erases rows this device no longer
        // has — another device's uploads. It is only meaningful once this
        // account has actually put something in the cloud; queueing it for an
        // account that never synced would be a bare request to the analytics
        // API from a user who never opted in.
        const hasSyncedRows =
          this.activeAccountId && countSyncedAnalytics.get(this.activeAccountId).count > 0;
        tombstoneSyncedAnalytics.run(clearedThrough);
        hardDeleteLocalAnalytics.run();
        if (hasSyncedRows) {
          queueAnalyticsClear.run(this.activeAccountId, clearedThrough);
        }
        return cleared;
      });
      return { cleared: clearAll(), success: true };
    } catch (error) {
      debugLogger.error("Error clearing transcriptions", { error: error.message }, "database");
      throw error;
    }
  }

  /** Purges transcriptions and their Insights counters older than the retention window.
   *  Returns the affected transcription ids so callers can drop the matching audio files.
   *  Runs even with no expired transcriptions: counters outlive tombstoned rows. */
  deleteTranscriptionsExpiredBefore(retentionDays) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      // Resolve the cutoff once so the ids we report are exactly the rows we purge.
      const cutoff = this.db
        .prepare("SELECT datetime('now', ?) AS cutoff")
        .get(`-${retentionDays} days`).cutoff;
      const expired = this.db
        .prepare("SELECT id FROM transcriptions WHERE deleted_at IS NULL AND created_at < ?")
        .all(cutoff)
        .map((row) => row.id);

      const tombstone = this.db.prepare(
        "UPDATE transcriptions SET deleted_at = datetime('now'), sync_status = 'pending' WHERE cloud_id IS NOT NULL AND deleted_at IS NULL AND created_at < ?"
      );
      const hardDelete = this.db.prepare(
        "DELETE FROM transcriptions WHERE cloud_id IS NULL AND created_at < ?"
      );
      // Counters follow the transcripts they describe, on the same cutoff and
      // in the same transaction. Matched on created_at, never occurred_at:
      // created_at uses the same SQLite timestamp format as the cutoff.
      //
      // Same rule as clearTranscriptions: only a row the cloud actually holds
      // leaves a tombstone. Attribution alone is not enough — every dictation
      // made while signed in carries an account_id whether or not Insights
      // sync was ever turned on, so tombstoning on that basis shipped the event
      // ids of a user who never opted in.
      const tombstoneSyncedAnalytics = this.db.prepare(
        `UPDATE analytics_events
         SET deleted_at = datetime('now'), sync_status = 'pending'
         WHERE sync_status = 'synced' AND deleted_at IS NULL AND created_at < ?`
      );
      const purgeUnsyncedAnalytics = this.db.prepare(
        `DELETE FROM analytics_events
         WHERE (account_id IS NULL OR sync_status <> 'synced')
           AND deleted_at IS NULL AND created_at < ?`
      );
      let analyticsPurged = 0;
      this.db.transaction(() => {
        tombstone.run(cutoff);
        hardDelete.run(cutoff);
        analyticsPurged =
          tombstoneSyncedAnalytics.run(cutoff).changes + purgeUnsyncedAnalytics.run(cutoff).changes;
      })();
      return { ids: expired, analyticsPurged };
    } catch (error) {
      debugLogger.error(
        "Error purging expired transcriptions",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  deleteTranscription(id) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const row = this.db
        .prepare("SELECT cloud_id, deleted_at FROM transcriptions WHERE id = ?")
        .get(id);
      if (!row || row.deleted_at) return { success: false, id };
      const stmt = row.cloud_id
        ? this.db.prepare(
            "UPDATE transcriptions SET deleted_at = datetime('now'), sync_status = 'pending' WHERE id = ? AND deleted_at IS NULL"
          )
        : this.db.prepare("DELETE FROM transcriptions WHERE id = ?");
      const result = stmt.run(id);
      return { success: result.changes > 0, id };
    } catch (error) {
      debugLogger.error("Error deleting transcription", { error: error.message }, "database");
      throw error;
    }
  }

  updateTranscriptionAudio(id, { hasAudio, audioDurationMs, provider, model }) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare(
        "UPDATE transcriptions SET has_audio = ?, audio_duration_ms = ?, provider = ?, model = ? WHERE id = ?"
      );
      stmt.run(hasAudio, audioDurationMs, provider, model, id);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error updating transcription audio", { error: error.message }, "database");
      throw error;
    }
  }

  updateTranscriptionText(id, text, rawText) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare("UPDATE transcriptions SET text = ?, raw_text = ? WHERE id = ?");
      this.db.transaction(() => {
        const existing = this.db
          .prepare("SELECT text, raw_text FROM transcriptions WHERE id = ?")
          .get(id);
        if (existing && (existing.text !== text || existing.raw_text !== rawText)) {
          this._invalidateAnalyticsHistoryFromTranscription(id);
        }
        stmt.run(text, rawText, id);
      })();
      return { success: true };
    } catch (error) {
      debugLogger.error("Error updating transcription text", { error: error.message }, "database");
      throw error;
    }
  }

  updateTranscriptionStatus(id, status, errorMessage = null, errorCode = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare(
        "UPDATE transcriptions SET status = ?, error_message = ?, error_code = ? WHERE id = ?"
      );
      this.db.transaction(() => {
        const existing = this.db.prepare("SELECT status FROM transcriptions WHERE id = ?").get(id);
        if (existing && existing.status !== status && status === "completed") {
          this._invalidateAnalyticsHistoryFromTranscription(id);
        }
        stmt.run(status, errorMessage, errorCode, id);
      })();
      return { success: true };
    } catch (error) {
      debugLogger.error(
        "Error updating transcription status",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getTranscriptionById(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare("SELECT * FROM transcriptions WHERE id = ?");
      return stmt.get(id) || null;
    } catch (error) {
      debugLogger.error("Error getting transcription by id", { error: error.message }, "database");
      throw error;
    }
  }

  clearAudioFlags(ids) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!ids || ids.length === 0) return { success: true };
      const transaction = this.db.transaction((idList) => {
        const stmt = this.db.prepare("UPDATE transcriptions SET has_audio = 0 WHERE id = ?");
        for (const id of idList) {
          stmt.run(id);
        }
      });
      transaction(ids);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error clearing audio flags", { error: error.message }, "database");
      throw error;
    }
  }

  getDictionary() {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const rows = this.db
        .prepare("SELECT word FROM custom_dictionary WHERE deleted_at IS NULL ORDER BY id ASC")
        .all();
      return rows.map((row) => row.word);
    } catch (error) {
      debugLogger.error("Error getting dictionary", { error: error.message }, "database");
      throw error;
    }
  }

  // Every dictionary mutation rule lives here once, so the whole-list and
  // delta write paths cannot drift apart.
  _dictionaryWriteStatements() {
    return {
      tombstone: this.db.prepare(
        "UPDATE custom_dictionary SET deleted_at = datetime('now'), updated_at = datetime('now'), sync_status = 'pending' WHERE id = ? AND deleted_at IS NULL"
      ),
      hardDelete: this.db.prepare(
        "DELETE FROM custom_dictionary WHERE id = ? AND cloud_id IS NULL"
      ),
      restore: this.db.prepare(
        "UPDATE custom_dictionary SET deleted_at = NULL, source = CASE WHEN source = 'learned' AND ? = 'manual' THEN 'manual' ELSE source END, word = ?, updated_at = datetime('now'), sync_status = 'pending' WHERE id = ?"
      ),
      promoteSource: this.db.prepare(
        "UPDATE custom_dictionary SET word = ?, source = 'manual', updated_at = datetime('now'), sync_status = 'pending' WHERE id = ? AND source = 'learned'"
      ),
      // Guarded on word != ? so an unchanged row keeps its sync_status.
      updateWord: this.db.prepare(
        "UPDATE custom_dictionary SET word = ?, updated_at = datetime('now'), sync_status = 'pending' WHERE id = ? AND word != ?"
      ),
      // INSERT OR IGNORE in case a legacy case-variant row collides on the
      // case-sensitive UNIQUE(word) that the lowercase index didn't catch.
      insert: this.db.prepare(
        "INSERT OR IGNORE INTO custom_dictionary (word, source, client_dict_id, sync_status, updated_at) VALUES (?, ?, ?, 'pending', datetime('now'))"
      ),
    };
  }

  // Dedupe by lower(word), keeping the first occurrence's casing, so no caller
  // can present two spellings of the same word to a write loop.
  _normalizeDictionaryWords(words) {
    const byLower = new Map();
    for (const raw of Array.isArray(words) ? words : []) {
      if (typeof raw !== "string") continue;
      const trimmed = raw.trim();
      if (!trimmed) continue;
      const lower = trimmed.toLowerCase();
      if (!byLower.has(lower)) byLower.set(lower, trimmed);
    }
    return byLower;
  }

  _dictionaryRows() {
    const rows = this.db
      .prepare("SELECT id, word, source, deleted_at FROM custom_dictionary")
      .all();
    return { rows, byLower: new Map(rows.map((r) => [r.word.toLowerCase(), r])) };
  }

  // Returns true when the word became present, so callers can report how many
  // words they actually added rather than how many they asked for.
  _upsertDictionaryWord(stmts, word, existing, source) {
    if (!existing) {
      return stmts.insert.run(word, source, randomUUID()).changes > 0;
    }
    if (existing.deleted_at) {
      stmts.restore.run(source, word, existing.id);
      return true;
    }
    if (source === "manual" && existing.source === "learned") {
      stmts.promoteSource.run(word, existing.id);
    } else {
      stmts.updateWord.run(word, existing.id, word);
    }
    return false;
  }

  // Hard-delete when the row never reached the cloud, else tombstone so the
  // next push tells the server about the deletion.
  _deleteDictionaryRow(stmts, existing) {
    if (!existing || existing.deleted_at) return false;
    const hardResult = stmts.hardDelete.run(existing.id);
    if (hardResult.changes === 0) stmts.tombstone.run(existing.id);
    return true;
  }

  // Add and/or remove specific words, leaving every other row untouched.
  // Prefer this over setDictionary, which deletes whatever the caller omitted
  // and so lets a stale snapshot destroy the rest (#1295).
  // `source` tags additions ('manual' for user-typed, 'learned' for auto-learn).
  applyDictionaryChanges({ add = [], remove = [] } = {}, source = "manual") {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const additions = this._normalizeDictionaryWords(add);
      const removals = this._normalizeDictionaryWords(remove);
      // A word on both sides is a rename to itself; adding wins.
      for (const lower of additions.keys()) removals.delete(lower);
      if (additions.size === 0 && removals.size === 0) {
        return { success: true, added: 0, removed: 0 };
      }

      const { byLower } = this._dictionaryRows();
      const stmts = this._dictionaryWriteStatements();
      let added = 0;
      let removed = 0;

      this.db.transaction(() => {
        for (const lower of removals.keys()) {
          if (this._deleteDictionaryRow(stmts, byLower.get(lower))) removed += 1;
        }
        for (const [lower, word] of additions) {
          if (this._upsertDictionaryWord(stmts, word, byLower.get(lower), source)) added += 1;
        }
      })();

      return { success: true, added, removed };
    } catch (error) {
      debugLogger.error("Error applying dictionary changes", { error: error.message }, "database");
      throw error;
    }
  }

  // Replace the entire dictionary: anything absent from `words` is deleted.
  // Only for deliberate replace-everything callers (settings restore, clear
  // all, first write into an empty database). Everything else wants
  // applyDictionaryChanges.
  //
  // Diff-based so unchanged rows keep their source/created_at/cloud_id.
  setDictionary(words, sourceForNewWords = "manual") {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const incomingByLower = this._normalizeDictionaryWords(words);
      const { rows, byLower } = this._dictionaryRows();
      const stmts = this._dictionaryWriteStatements();

      this.db.transaction(() => {
        for (const existing of rows) {
          if (incomingByLower.has(existing.word.toLowerCase())) continue;
          this._deleteDictionaryRow(stmts, existing);
        }
        for (const [lower, word] of incomingByLower) {
          this._upsertDictionaryWord(stmts, word, byLower.get(lower), sourceForNewWords);
        }
      })();

      return { success: true };
    } catch (error) {
      debugLogger.error("Error setting dictionary", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingDictionary() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          "SELECT * FROM custom_dictionary WHERE sync_status = 'pending' AND deleted_at IS NULL"
        )
        .all();
    } catch (error) {
      debugLogger.error("Error getting pending dictionary", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingDictionaryDeletes() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          "SELECT * FROM custom_dictionary WHERE deleted_at IS NOT NULL AND cloud_id IS NOT NULL AND sync_status = 'pending'"
        )
        .all();
    } catch (error) {
      debugLogger.error(
        "Error getting pending dictionary deletes",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  hardDeleteDictionaryEntry(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db.prepare("DELETE FROM custom_dictionary WHERE id = ?").run(id);
      return { success: result.changes > 0, id };
    } catch (error) {
      debugLogger.error(
        "Error hard deleting dictionary entry",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getDictionaryEntryByClientId(clientDictId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return (
        this.db
          .prepare("SELECT * FROM custom_dictionary WHERE client_dict_id = ?")
          .get(clientDictId) || null
      );
    } catch (error) {
      debugLogger.error(
        "Error getting dictionary entry by client id",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  upsertDictionaryFromCloud(cloudEntry) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      // Reject incomplete payloads rather than corrupt a row with defaults.
      if (!cloudEntry || typeof cloudEntry !== "object") return null;
      if (typeof cloudEntry.id !== "string" || !cloudEntry.id) return null;

      const word = typeof cloudEntry.word === "string" ? cloudEntry.word.trim() : "";
      if (!word) return null;

      const clientDictId =
        typeof cloudEntry.client_dict_id === "string" && cloudEntry.client_dict_id
          ? cloudEntry.client_dict_id
          : randomUUID();
      const incomingSource = cloudEntry.source === "learned" ? "learned" : "manual";
      const updatedAt =
        typeof cloudEntry.updated_at === "string" && cloudEntry.updated_at
          ? cloudEntry.updated_at
          : typeof cloudEntry.created_at === "string" && cloudEntry.created_at
            ? cloudEntry.created_at
            : new Date().toISOString();
      const createdAt =
        typeof cloudEntry.created_at === "string" && cloudEntry.created_at
          ? cloudEntry.created_at
          : updatedAt;

      // Resolve the local row deterministically: client_dict_id, then cloud_id,
      // then word.
      const byClient = this.db
        .prepare("SELECT * FROM custom_dictionary WHERE client_dict_id = ? LIMIT 1")
        .get(clientDictId);
      const byCloud =
        byClient ||
        this.db
          .prepare("SELECT * FROM custom_dictionary WHERE cloud_id = ? LIMIT 1")
          .get(cloudEntry.id);
      const existing =
        byCloud ||
        this.db
          .prepare("SELECT * FROM custom_dictionary WHERE lower(word) = lower(?) LIMIT 1")
          .get(word);

      if (existing) {
        // Manual is sticky — a pull never demotes a local manual row to learned.
        const mergedSource =
          existing.source === "manual" || incomingSource === "manual" ? "manual" : "learned";
        // A local edit can follow the push ACK within the same SQLite second.
        // Its timestamp cannot safely order it against the server's echo.
        if (existing.sync_status === "pending") {
          this.db
            .prepare(
              "UPDATE custom_dictionary SET cloud_id = COALESCE(cloud_id, ?), source = ? WHERE id = ?"
            )
            .run(cloudEntry.id, mergedSource, existing.id);
          return this.db.prepare("SELECT * FROM custom_dictionary WHERE id = ?").get(existing.id);
        }
        this.db
          .prepare(
            `UPDATE custom_dictionary
             SET cloud_id = ?, client_dict_id = ?, word = ?, source = ?,
                 sync_status = 'synced', deleted_at = NULL, updated_at = ?
             WHERE id = ?`
          )
          .run(cloudEntry.id, clientDictId, word, mergedSource, updatedAt, existing.id);
        return this.db.prepare("SELECT * FROM custom_dictionary WHERE id = ?").get(existing.id);
      }

      this.db
        .prepare(
          `INSERT INTO custom_dictionary
             (word, source, client_dict_id, cloud_id, sync_status, created_at, updated_at)
           VALUES (?, ?, ?, ?, 'synced', ?, ?)`
        )
        .run(word, incomingSource, clientDictId, cloudEntry.id, createdAt, updatedAt);
      return this.db
        .prepare("SELECT * FROM custom_dictionary WHERE client_dict_id = ?")
        .get(clientDictId);
    } catch (error) {
      debugLogger.error(
        "Error upserting dictionary entry from cloud",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  markDictionaryEntrySynced(
    id,
    cloudId,
    serverUpdatedAt = null,
    expectedWord = null,
    expectedSource = null,
    serverSource = null
  ) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.transaction(() => {
        const current = this.db.prepare("SELECT * FROM custom_dictionary WHERE id = ?").get(id);
        if (!current || current.deleted_at) return { success: false, changes: 0 };

        const unchanged =
          (expectedWord === null || current.word === expectedWord) &&
          (expectedSource === null || current.source === expectedSource);
        // Edited rows still adopt the cloud identity, so they can PATCH next
        // pass. Only missing/deleted rows authorize orphan cleanup (changes=0).
        const result = this.db
          .prepare(
            `UPDATE custom_dictionary
             SET cloud_id = ?, sync_status = ?, updated_at = COALESCE(?, updated_at),
                 source = CASE WHEN ? = 'manual' THEN 'manual' ELSE source END
             WHERE id = ?`
          )
          .run(
            cloudId,
            unchanged ? "synced" : "pending",
            unchanged ? serverUpdatedAt : null,
            serverSource,
            id
          );
        return { success: result.changes > 0, changes: result.changes };
      })();
    } catch (error) {
      debugLogger.error(
        "Error marking dictionary entry synced",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // Clears cloud_id after a 404 so the next push re-creates the row via
  // batchCreate instead of retrying the dead PATCH.
  clearDictionaryCloudId(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db
        .prepare(
          "UPDATE custom_dictionary SET cloud_id = NULL, sync_status = 'pending' WHERE id = ?"
        )
        .run(id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error("Error clearing dictionary cloud_id", { error: error.message }, "database");
      throw error;
    }
  }

  getSnippets() {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      return this.db
        .prepare(
          "SELECT trigger, replacement FROM snippets WHERE deleted_at IS NULL ORDER BY id ASC"
        )
        .all();
    } catch (error) {
      debugLogger.error("Error getting snippets", { error: error.message }, "database");
      throw error;
    }
  }

  setSnippets(snippets) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }

      const incomingByLower = new Map();
      for (const raw of Array.isArray(snippets) ? snippets : []) {
        if (!raw || typeof raw !== "object") continue;
        const trigger = typeof raw.trigger === "string" ? raw.trigger.trim() : "";
        const replacement = typeof raw.replacement === "string" ? raw.replacement.trim() : "";
        if (!trigger || !replacement) continue;
        if (trigger.length > MAX_SNIPPET_TRIGGER_LENGTH) continue;
        const lower = trigger.toLowerCase();
        if (!incomingByLower.has(lower)) incomingByLower.set(lower, { trigger, replacement });
      }
      const cleaned = Array.from(incomingByLower.values());
      const incomingLower = new Set(incomingByLower.keys());

      const existingRows = this.db.prepare("SELECT * FROM snippets").all();
      const existingByLower = new Map();
      for (const row of existingRows) {
        const lower = row.trigger.toLowerCase();
        const current = existingByLower.get(lower);
        if (!current || (current.deleted_at && !row.deleted_at)) existingByLower.set(lower, row);
      }

      const tombstone = this.db.prepare(
        "UPDATE snippets SET deleted_at = datetime('now'), updated_at = datetime('now'), sync_status = 'pending' WHERE id = ? AND deleted_at IS NULL"
      );
      const hardDelete = this.db.prepare("DELETE FROM snippets WHERE id = ? AND cloud_id IS NULL");
      const restore = this.db.prepare(
        "UPDATE snippets SET deleted_at = NULL, trigger = ?, replacement = ?, updated_at = datetime('now'), sync_status = 'pending' WHERE id = ?"
      );
      const updateActive = this.db.prepare(
        "UPDATE snippets SET trigger = ?, replacement = ?, updated_at = datetime('now'), sync_status = 'pending' WHERE id = ? AND (trigger != ? OR replacement != ?)"
      );
      const insert = this.db.prepare(
        "INSERT OR IGNORE INTO snippets (trigger, replacement, client_snippet_id, sync_status, updated_at) VALUES (?, ?, ?, 'pending', datetime('now'))"
      );

      this.db.transaction(() => {
        for (const existing of existingRows) {
          if (incomingLower.has(existing.trigger.toLowerCase())) continue;
          if (existing.deleted_at) continue;
          const hardResult = hardDelete.run(existing.id);
          if (hardResult.changes === 0) tombstone.run(existing.id);
        }

        for (const snippet of cleaned) {
          const existing = existingByLower.get(snippet.trigger.toLowerCase());
          if (existing) {
            if (existing.deleted_at) {
              restore.run(snippet.trigger, snippet.replacement, existing.id);
            } else {
              updateActive.run(
                snippet.trigger,
                snippet.replacement,
                existing.id,
                snippet.trigger,
                snippet.replacement
              );
            }
            continue;
          }
          insert.run(snippet.trigger, snippet.replacement, randomUUID());
        }
      })();

      return { success: true };
    } catch (error) {
      debugLogger.error("Error setting snippets", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingSnippets() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare("SELECT * FROM snippets WHERE sync_status = 'pending' AND deleted_at IS NULL")
        .all();
    } catch (error) {
      debugLogger.error("Error getting pending snippets", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingSnippetDeletes() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          "SELECT * FROM snippets WHERE deleted_at IS NOT NULL AND cloud_id IS NOT NULL AND sync_status = 'pending'"
        )
        .all();
    } catch (error) {
      debugLogger.error(
        "Error getting pending snippet deletes",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  hardDeleteSnippet(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db.prepare("DELETE FROM snippets WHERE id = ?").run(id);
      return { success: result.changes > 0, id };
    } catch (error) {
      debugLogger.error("Error hard deleting snippet", { error: error.message }, "database");
      throw error;
    }
  }

  getSnippetForCloudMerge(cloudEntry) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!cloudEntry || typeof cloudEntry !== "object") return null;

      const clientSnippetId =
        typeof cloudEntry.client_snippet_id === "string" && cloudEntry.client_snippet_id
          ? cloudEntry.client_snippet_id
          : "";
      if (clientSnippetId) {
        const byClient = this.db
          .prepare("SELECT * FROM snippets WHERE client_snippet_id = ? LIMIT 1")
          .get(clientSnippetId);
        if (byClient) return byClient;
      }

      if (typeof cloudEntry.id === "string" && cloudEntry.id) {
        const byCloud = this.db
          .prepare("SELECT * FROM snippets WHERE cloud_id = ? LIMIT 1")
          .get(cloudEntry.id);
        if (byCloud) return byCloud;
      }

      const trigger = typeof cloudEntry.trigger === "string" ? cloudEntry.trigger.trim() : "";
      if (!trigger) return null;
      const byActiveTrigger = this.db
        .prepare(
          "SELECT * FROM snippets WHERE lower(trigger) = lower(?) AND deleted_at IS NULL LIMIT 1"
        )
        .get(trigger);
      if (byActiveTrigger) return byActiveTrigger;
      return (
        this.db
          .prepare(
            "SELECT * FROM snippets WHERE lower(trigger) = lower(?) AND deleted_at IS NOT NULL LIMIT 1"
          )
          .get(trigger) || null
      );
    } catch (error) {
      debugLogger.error(
        "Error getting snippet for cloud merge",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  upsertSnippetFromCloud(cloudEntry) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!cloudEntry || typeof cloudEntry !== "object") return null;
      if (typeof cloudEntry.id !== "string" || !cloudEntry.id) return null;

      const trigger = typeof cloudEntry.trigger === "string" ? cloudEntry.trigger.trim() : "";
      const replacement =
        typeof cloudEntry.replacement === "string" ? cloudEntry.replacement.trim() : "";
      if (!trigger || !replacement) return null;

      const clientSnippetId =
        typeof cloudEntry.client_snippet_id === "string" && cloudEntry.client_snippet_id
          ? cloudEntry.client_snippet_id
          : randomUUID();
      const updatedAt =
        typeof cloudEntry.updated_at === "string" && cloudEntry.updated_at
          ? cloudEntry.updated_at
          : typeof cloudEntry.created_at === "string" && cloudEntry.created_at
            ? cloudEntry.created_at
            : new Date().toISOString();
      const createdAt =
        typeof cloudEntry.created_at === "string" && cloudEntry.created_at
          ? cloudEntry.created_at
          : updatedAt;

      const existing = this.getSnippetForCloudMerge({
        ...cloudEntry,
        client_snippet_id: clientSnippetId,
        trigger,
      });

      if (existing) {
        // A different active row may already hold this trigger (cross-device
        // rename); it must yield first or the UPDATE trips the active-trigger
        // unique index and aborts the pull.
        const collidingActive = this.db
          .prepare(
            "SELECT * FROM snippets WHERE lower(trigger) = lower(?) AND deleted_at IS NULL AND id != ? LIMIT 1"
          )
          .get(trigger, existing.id);
        // Tombstone existing → keep the active collider; else keep existing and
        // drop the stale collider.
        const target = existing.deleted_at && collidingActive ? collidingActive : existing;
        const orphanId = target.id === existing.id ? collidingActive?.id : existing.id;
        if (orphanId) {
          this.db.prepare("DELETE FROM snippets WHERE id = ?").run(orphanId);
        }
        this.db
          .prepare(
            `UPDATE snippets
             SET cloud_id = ?, client_snippet_id = ?, trigger = ?, replacement = ?,
                 sync_status = 'synced', deleted_at = NULL, updated_at = ?
             WHERE id = ?`
          )
          .run(cloudEntry.id, clientSnippetId, trigger, replacement, updatedAt, target.id);
        return this.db.prepare("SELECT * FROM snippets WHERE id = ?").get(target.id);
      }

      this.db
        .prepare(
          `INSERT INTO snippets
             (trigger, replacement, client_snippet_id, cloud_id, sync_status, created_at, updated_at)
           VALUES (?, ?, ?, ?, 'synced', ?, ?)`
        )
        .run(trigger, replacement, clientSnippetId, cloudEntry.id, createdAt, updatedAt);
      return this.db
        .prepare("SELECT * FROM snippets WHERE client_snippet_id = ?")
        .get(clientSnippetId);
    } catch (error) {
      debugLogger.error("Error upserting snippet from cloud", { error: error.message }, "database");
      throw error;
    }
  }

  markSnippetSynced(
    id,
    cloudId,
    serverUpdatedAt = null,
    expectedTrigger = null,
    expectedReplacement = null
  ) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      // If a user edit landed between push and ack, the row no longer matches
      // what was pushed — leave it 'pending' so the next sync re-pushes it.
      const result = this.db
        .prepare(
          `UPDATE snippets
           SET sync_status = 'synced',
               cloud_id = ?,
               updated_at = COALESCE(?, updated_at)
           WHERE id = ? AND deleted_at IS NULL
             AND (? IS NULL OR trigger = ?)
             AND (? IS NULL OR replacement = ?)`
        )
        .run(
          cloudId,
          serverUpdatedAt,
          id,
          expectedTrigger,
          expectedTrigger,
          expectedReplacement,
          expectedReplacement
        );
      return { success: result.changes > 0, changes: result.changes };
    } catch (error) {
      debugLogger.error("Error marking snippet synced", { error: error.message }, "database");
      throw error;
    }
  }

  clearSnippetCloudId(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db
        .prepare("UPDATE snippets SET cloud_id = NULL, sync_status = 'pending' WHERE id = ?")
        .run(id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error("Error clearing snippet cloud_id", { error: error.message }, "database");
      throw error;
    }
  }

  saveNote(
    title,
    content,
    noteType = "personal",
    sourceFile = null,
    audioDuration = null,
    folderId = null,
    spaceId = null,
    transcript = null
  ) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      if (folderId) {
        // D2: a note's space always follows its folder's space.
        const folder = this._getFolderInAccountScope(folderId);
        if (!folder) throw new Error("Folder not found in the active account scope");
        spaceId = folder.space_id;
      } else {
        if (spaceId == null) spaceId = this.getPrivateSpaceId();
        if (!this.getSpace(spaceId)) throw new Error("Space not found in the active account scope");
        const defaultFolderName = noteType === "meeting" ? "Meetings" : "Personal";
        const folderScope = this._accountScopeCondition("folders");
        const defaultFolder = this.db
          .prepare(
            `SELECT id FROM folders
             WHERE name = ? AND is_default = 1 AND space_id = ? AND ${folderScope.sql}`
          )
          .get(defaultFolderName, spaceId, ...folderScope.params);
        folderId = defaultFolder?.id || null;
      }
      const clientNoteId = randomUUID();
      const accountId = this._accountIdForSpace(spaceId);
      const stmt = this.db.prepare(
        "INSERT INTO notes (title, content, content_sync_operation, note_type, source_file, audio_duration_seconds, folder_id, space_id, client_note_id, account_id, transcript) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      );
      const result = stmt.run(
        title,
        content,
        typedTextOperation(content),
        noteType,
        sourceFile,
        audioDuration,
        folderId,
        spaceId,
        clientNoteId,
        accountId,
        transcript
      );

      const fetchStmt = this.db.prepare("SELECT * FROM notes WHERE id = ?");
      const note = fetchStmt.get(result.lastInsertRowid);

      return { success: true, note };
    } catch (error) {
      debugLogger.error("Error saving note", { error: error.message }, "notes");
      throw error;
    }
  }

  /**
   * Bulk-insert externally imported notes (e.g. a Granola CSV export).
   * Unlike saveNote, rows carry their own client_note_id and original
   * created_at/updated_at; the UNIQUE client_note_id index makes re-imports
   * idempotent (duplicates are skipped, never overwritten).
   */
  importNotes(rows, { noteType = "meeting", folderName = "Imported" } = {}) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const spaceId = this.getPrivateSpaceId();
      const accountId = this._accountIdForSpace(spaceId);

      let folderId = null;
      try {
        const folderScope = this._accountScopeCondition("folders");
        const existing = this.db
          .prepare(
            `SELECT id FROM folders
             WHERE name = ? AND space_id = ? AND deleted_at IS NULL AND ${folderScope.sql}`
          )
          .get(folderName, spaceId, ...folderScope.params);
        folderId = existing?.id ?? this.createFolder(folderName, spaceId)?.folder?.id ?? null;
      } catch (folderError) {
        debugLogger.error(
          "Import folder resolution failed; importing without a folder",
          { error: folderError.message },
          "notes"
        );
      }

      const insert = this.db.prepare(`
        INSERT INTO notes (client_note_id, title, content, content_sync_operation, note_type, source_file,
          folder_id, space_id, account_id, transcript, participants, sync_status, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending',
          COALESCE(?, datetime('now')), COALESCE(?, datetime('now')))
        ON CONFLICT(client_note_id) DO NOTHING
      `);

      let imported = 0;
      let skipped = 0;
      const noteIds = [];
      const errors = [];
      this.db.transaction(() => {
        for (const row of rows) {
          try {
            const result = insert.run(
              row.clientNoteId,
              row.title,
              row.content,
              typedTextOperation(row.content),
              noteType,
              row.sourceFile,
              folderId,
              spaceId,
              accountId,
              row.transcript,
              row.participants,
              row.createdAt,
              row.createdAt
            );
            if (result.changes === 1) {
              imported++;
              noteIds.push(Number(result.lastInsertRowid));
            } else {
              skipped++;
            }
          } catch (rowError) {
            errors.push({ clientNoteId: row.clientNoteId, error: rowError.message });
          }
        }
      })();

      return { success: true, imported, skipped, folderId, noteIds, errors };
    } catch (error) {
      debugLogger.error("Error importing notes", { error: error.message }, "notes");
      throw error;
    }
  }

  getExistingClientNoteIds(clientNoteIds) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const existing = [];
      for (let i = 0; i < clientNoteIds.length; i += 500) {
        const chunk = clientNoteIds.slice(i, i + 500);
        const placeholders = chunk.map(() => "?").join(",");
        const found = this.db
          .prepare(`SELECT client_note_id FROM notes WHERE client_note_id IN (${placeholders})`)
          .all(...chunk);
        existing.push(...found.map((row) => row.client_note_id));
      }
      return existing;
    } catch (error) {
      debugLogger.error("Error checking client note ids", { error: error.message }, "notes");
      throw error;
    }
  }

  getNote(id) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const accountScope = this._accountScopeCondition("notes");
      const stmt = this.db.prepare(`SELECT * FROM notes WHERE id = ? AND ${accountScope.sql}`);
      return stmt.get(id, ...accountScope.params) || null;
    } catch (error) {
      debugLogger.error("Error getting note", { error: error.message }, "notes");
      throw error;
    }
  }

  getNoteByCloudId(cloudId) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const accountScope = this._accountScopeCondition("notes");
      const stmt = this.db.prepare(
        `SELECT * FROM notes
         WHERE cloud_id = ? AND deleted_at IS NULL AND ${accountScope.sql}
         LIMIT 1`
      );
      return stmt.get(cloudId, ...accountScope.params) || null;
    } catch (error) {
      debugLogger.error("Error getting note by cloud_id", { error: error.message }, "notes");
      throw error;
    }
  }

  getNotes(noteType = null, limit = 100, folderId = null, spaceId = null) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const conditions = ["deleted_at IS NULL"];
      const params = [];
      const accountScope = this._accountScopeCondition("notes");
      conditions.push(accountScope.sql);
      params.push(...accountScope.params);
      if (noteType) {
        conditions.push("note_type = ?");
        params.push(noteType);
      }
      if (folderId != null) {
        conditions.push("folder_id = ?");
        params.push(folderId);
      } else if (spaceId != null) {
        // spaceId without folderId lists a space's root: folderless notes only.
        conditions.push("folder_id IS NULL");
      }
      if (spaceId != null) {
        conditions.push("space_id = ?");
        params.push(spaceId);
      }
      const where = `WHERE ${conditions.join(" AND ")}`;
      const stmt = this.db.prepare(`SELECT * FROM notes ${where} ORDER BY updated_at DESC LIMIT ?`);
      params.push(limit);
      return stmt.all(...params);
    } catch (error) {
      debugLogger.error("Error getting notes", { error: error.message }, "notes");
      throw error;
    }
  }

  // Unlike getNotes(null, limit, null, spaceId) — which is root-only — this
  // lists every note in the space, foldered or not (space overview list).
  getNotesForSpace(spaceId, limit = 50) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("notes");
      return this.db
        .prepare(
          `SELECT * FROM notes
           WHERE space_id = ? AND deleted_at IS NULL AND ${accountScope.sql}
           ORDER BY updated_at DESC LIMIT ?`
        )
        .all(spaceId, ...accountScope.params, limit);
    } catch (error) {
      debugLogger.error("Error getting notes for space", { error: error.message }, "notes");
      throw error;
    }
  }

  getNoteIdsInFolder(folderId) {
    return this.getNoteIdsInScope(null, folderId);
  }

  // Authoritative scope membership for semantic-search candidates. Qdrant
  // payload writes are asynchronous/best-effort, so its filters are only an
  // optimization and must not decide which space or folder a hit belongs to.
  getNoteIdsInScope(spaceId = null, folderId = null, candidateIds = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (candidateIds && candidateIds.length === 0) return [];
      const conditions = ["deleted_at IS NULL"];
      const params = [];
      const accountScope = this._accountScopeCondition("notes");
      conditions.push(accountScope.sql);
      params.push(...accountScope.params);
      if (candidateIds) {
        conditions.push(`id IN (${candidateIds.map(() => "?").join(", ")})`);
        params.push(...candidateIds);
      }
      if (spaceId != null) {
        conditions.push("space_id = ?");
        params.push(spaceId);
      }
      if (folderId != null) {
        conditions.push("folder_id = ?");
        params.push(folderId);
      }
      return this.db
        .prepare(`SELECT id FROM notes WHERE ${conditions.join(" AND ")}`)
        .all(...params)
        .map((row) => row.id);
    } catch (error) {
      debugLogger.error("Error getting scoped note ids", { error: error.message }, "notes");
      throw error;
    }
  }

  getNoteUndos() {
    return this.noteUndoReady ? noteUndo.getNoteUndos(this) : [];
  }

  claimNoteUndo(token) {
    return this.noteUndoReady && noteUndo.claimNoteUndo(this, token);
  }

  undoNoteUpdate(token) {
    if (!this.noteUndoReady) return { success: false, error: "note_changed" };
    return noteUndo.undoNoteUpdate(this, token);
  }

  discardNoteUndo(id, token) {
    if (this.noteUndoReady) noteUndo.discardNoteUndo(this, id, token);
  }

  updateNote(id, updates, options) {
    if (options?.undoable && this.noteUndoReady) {
      return noteUndo.updateNoteWithUndo(this, id, updates, options.expected, options.turn);
    }
    try {
      if (!this.db) throw new Error("Database not initialized");
      const previous = this.getNote(id);
      if (!previous) return { success: false, error: "Note not found" };
      updates = { ...updates };
      // An unlink is stored as "" so it syncs: the cloud keeps a NULL as "unchanged".
      if (updates.calendar_event_id === null && previous.calendar_event_id != null) {
        updates.calendar_event_id = "";
      }
      const clears = updates.clear_fields ?? [];
      if (!Array.isArray(clears) || clears.some((field) => !NOTE_TEXT_FIELDS.includes(field))) {
        return { success: false, error: "Invalid note clear fields" };
      }
      for (const field of NOTE_TEXT_FIELDS) {
        delete updates[`${field}_sync_operation`];
        delete updates[`${field}_edit_generation`];
        if (clears.includes(field)) {
          if (
            updates[field] != null &&
            (typeof updates[field] !== "string" || updates[field].trim())
          ) {
            return { success: false, error: "A clear cannot include nonempty text" };
          }
          updates[field] = field === "content" ? "" : null;
          updates[`${field}_sync_operation`] = "clear";
          if (field === "enhanced_content") {
            for (const key of SUMMARY_CLEARED_METADATA) updates[key] = null;
          }
        } else if (
          typeof updates[field] === "string" &&
          updates[field].trim() &&
          updates[field] !== previous[field]
        ) {
          updates[`${field}_sync_operation`] = "set";
        } else if (updates[field] !== undefined && !updates[field]?.trim()) {
          // Preserve the existing local save contract, but never turn an
          // untrusted blank into a sync operation.
          updates[`${field}_sync_operation`] =
            !previous[field]?.trim() && previous[`${field}_sync_operation`] === "clear"
              ? "clear"
              : null;
        }
      }
      if (
        !clears.includes("enhanced_content") &&
        (updates.enhanced_content === undefined || updates.enhanced_content?.trim()) &&
        (updates.enhanced_content ?? previous.enhanced_content)?.trim() &&
        SUMMARY_METADATA.some((key) => updates[key] !== undefined && updates[key] !== previous[key])
      ) {
        updates.enhanced_content_sync_operation = "set";
      }
      delete updates.clear_fields;
      delete updates.account_id;
      delete updates.cloud_revision;
      delete updates.cloud_create_rejected;
      delete updates.cloud_create_pending;
      if (
        updates.cloud_id === null ||
        (updates.client_note_id && updates.client_note_id !== previous.client_note_id)
      ) {
        updates.cloud_revision = null;
        updates.cloud_create_rejected = 0;
        updates.cloud_create_pending = null;
      }
      if (updates.folder_id != null) {
        // D2: a note's space always follows its folder's space.
        const folder = this._getFolderInAccountScope(updates.folder_id);
        if (folder) updates = { ...updates, space_id: folder.space_id };
        else return { success: false, error: "Folder not found" };
      }
      if (updates.space_id !== undefined) {
        if (!this.getSpace(updates.space_id)) {
          return { success: false, error: "Space not found" };
        }
        updates.account_id = this._accountIdForSpace(updates.space_id);
        // D6: a cloud-backed note leaving a team must keep pushing until the
        // scope retraction lands, even when cloud backup is off (left_team
        // keeps it in the team-only pending queue). Identity forks null the
        // cloud_id — nothing to retract, so they never set the flag.
        const current = this.db
          .prepare(
            "SELECT n.space_id, n.cloud_id, s.kind AS space_kind FROM notes n LEFT JOIN spaces s ON s.id = n.space_id WHERE n.id = ?"
          )
          .get(id);
        if (current && current.space_id !== updates.space_id) {
          const newKind = this.db
            .prepare("SELECT kind FROM spaces WHERE id = ?")
            .get(updates.space_id)?.kind;
          const keepsCloudId =
            updates.cloud_id === undefined ? current.cloud_id != null : updates.cloud_id != null;
          if (current.space_kind === "team" && newKind === "private" && keepsCloudId) {
            updates = { ...updates, left_team: 1 };
          } else if (newKind === "team") {
            updates = { ...updates, left_team: 0 };
          }
        }
      }
      const allowedFields = [
        "content_sync_operation",
        "enhanced_content_sync_operation",
        "cloud_revision",
        "cloud_create_rejected",
        "cloud_create_pending",
        "title",
        "content",
        "enhanced_content",
        "enhancement_prompt",
        "enhancement_template_id",
        "enhanced_at_content_hash",
        "folder_id",
        "space_id",
        "transcript",
        "calendar_event_id",
        "participants",
        "diarization_enabled",
        "expected_speaker_count",
        "sync_status",
        "deleted_at",
        "client_note_id",
        "cloud_id",
        "cloud_updated_at",
        "owner_user_id",
        "updated_by_user_id",
        "left_team",
        "account_id",
      ];
      const fields = [];
      const values = [];
      for (const [key, value] of Object.entries(updates)) {
        if (allowedFields.includes(key) && value !== undefined) {
          fields.push(`${key} = ?`);
          values.push(value);
        }
      }
      if (fields.length === 0) return { success: false };
      // Undo can return to the submitted text within the same SQLite second.
      // Keep that newer intent distinct from the in-flight request snapshot.
      for (const field of NOTE_TEXT_FIELDS) {
        const keys = [
          field,
          `${field}_sync_operation`,
          ...(field === "enhanced_content" ? SUMMARY_METADATA : []),
        ];
        if (
          clears.includes(field) ||
          keys.some((key) => updates[key] !== undefined && updates[key] !== previous[key])
        ) {
          fields.push(`${field}_edit_generation = ${field}_edit_generation + 1`);
        }
      }
      // Re-queue for cloud sync on any local edit, so post-sync field changes aren't
      // left local-only and overwritten by a later pull.
      if (!("sync_status" in updates)) {
        fields.push("sync_status = 'pending'");
      }
      fields.push("updated_at = CURRENT_TIMESTAMP");
      values.push(id);
      const accountScope = this._accountScopeCondition("notes");
      const stmt = this.db.prepare(
        `UPDATE notes SET ${fields.join(", ")} WHERE id = ? AND ${accountScope.sql}`
      );
      const result = stmt.run(...values, ...accountScope.params);
      if (result.changes === 0) return { success: false, error: "Note not found" };
      const fetchStmt = this.db.prepare("SELECT * FROM notes WHERE id = ?");
      const note = fetchStmt.get(id);
      return { success: true, note };
    } catch (error) {
      debugLogger.error("Error updating note", { error: error.message }, "notes");
      throw error;
    }
  }

  getFolders(spaceId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const conditions = ["deleted_at IS NULL"];
      const params = [];
      const accountScope = this._accountScopeCondition("folders");
      conditions.push(accountScope.sql);
      params.push(...accountScope.params);
      if (spaceId != null) {
        conditions.push("space_id = ?");
        params.push(spaceId);
      }
      return this.db
        .prepare(
          `SELECT * FROM folders WHERE ${conditions.join(" AND ")} ORDER BY sort_order ASC, created_at ASC`
        )
        .all(...params);
    } catch (error) {
      debugLogger.error("Error getting folders", { error: error.message }, "notes");
      throw error;
    }
  }

  createFolder(name, spaceId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const trimmed = (name || "").trim();
      if (!trimmed) return { success: false, error: "Folder name is required" };
      if (spaceId == null) spaceId = this.getPrivateSpaceId();
      if (!this.getSpace(spaceId)) {
        return { success: false, error: "Space not found" };
      }
      const accountScope = this._accountScopeCondition("folders");
      const existing = this.db
        .prepare(
          `SELECT id FROM folders
           WHERE name = ? AND space_id = ? AND ${FOLDER_NAME_TAKEN_FILTER}
             AND ${accountScope.sql}`
        )
        .get(trimmed, spaceId, ...accountScope.params);
      if (existing) return { success: false, error: "A folder with that name already exists" };
      const maxOrder = this.db
        .prepare("SELECT MAX(sort_order) as max_order FROM folders WHERE space_id = ?")
        .get(spaceId);
      const sortOrder = (maxOrder?.max_order ?? 0) + 1;
      const clientFolderId = randomUUID();
      const accountId = this._accountIdForSpace(spaceId);
      const result = this.db
        .prepare(
          "INSERT INTO folders (name, sort_order, space_id, client_folder_id, account_id) VALUES (?, ?, ?, ?, ?)"
        )
        .run(trimmed, sortOrder, spaceId, clientFolderId, accountId);
      const folder = this.db
        .prepare("SELECT * FROM folders WHERE id = ?")
        .get(result.lastInsertRowid);
      return { success: true, folder };
    } catch (error) {
      debugLogger.error("Error creating folder", { error: error.message }, "notes");
      throw error;
    }
  }

  deleteAccountData(accountId) {
    if (!this.db) throw new Error("Database not initialized");
    if (!accountId || accountId !== this.activeAccountId) {
      throw new Error("Account deletion must match the active account scope");
    }

    const deletedNoteIds = this.db
      .prepare(
        `SELECT notes.id
         FROM notes
         JOIN spaces ON spaces.id = notes.space_id
         WHERE notes.account_id = ? AND spaces.kind = 'private'
         ORDER BY notes.id`
      )
      .all(accountId)
      .map((row) => row.id);
    const deletedFolderIds = this.db
      .prepare(
        `SELECT folders.id
         FROM folders
         JOIN spaces ON spaces.id = folders.space_id
         WHERE folders.account_id = ? AND spaces.kind = 'private'
         ORDER BY folders.id`
      )
      .all(accountId)
      .map((row) => row.id);

    const notePlaceholders = deletedNoteIds.map(() => "?").join(", ");
    const folderPlaceholders = deletedFolderIds.map(() => "?").join(", ");
    const deleteRows = () => {
      const conversationConditions = [];
      const conversationParams = [];
      if (deletedNoteIds.length > 0) {
        conversationConditions.push(`note_id IN (${notePlaceholders})`);
        conversationParams.push(...deletedNoteIds);
      }
      if (deletedFolderIds.length > 0) {
        conversationConditions.push(`folder_id IN (${folderPlaceholders})`);
        conversationParams.push(...deletedFolderIds);
      }
      if (conversationConditions.length > 0) {
        const conversationIds = this.db
          .prepare(
            `SELECT id FROM agent_conversations WHERE ${conversationConditions.join(" OR ")}`
          )
          .all(...conversationParams)
          .map((row) => row.id);
        if (conversationIds.length > 0) {
          const conversationPlaceholders = conversationIds.map(() => "?").join(", ");
          this.db
            .prepare(
              `DELETE FROM agent_messages WHERE conversation_id IN (${conversationPlaceholders})`
            )
            .run(...conversationIds);
          this.db
            .prepare(`DELETE FROM agent_conversations WHERE id IN (${conversationPlaceholders})`)
            .run(...conversationIds);
        }
      }

      if (deletedNoteIds.length > 0) {
        this.db
          .prepare(`DELETE FROM speaker_mappings WHERE note_id IN (${notePlaceholders})`)
          .run(...deletedNoteIds);
        this.db
          .prepare(`DELETE FROM note_speaker_embeddings WHERE note_id IN (${notePlaceholders})`)
          .run(...deletedNoteIds);
        this.db
          .prepare(`DELETE FROM notes WHERE id IN (${notePlaceholders})`)
          .run(...deletedNoteIds);
      }
      if (deletedFolderIds.length > 0) {
        this.db
          .prepare(
            `DELETE FROM optimistic_folder_delete_rows WHERE folder_id IN (${folderPlaceholders})`
          )
          .run(...deletedFolderIds);
        this.db
          .prepare(`DELETE FROM folders WHERE id IN (${folderPlaceholders})`)
          .run(...deletedFolderIds);
      }
      this.db.prepare("DELETE FROM analytics_events WHERE account_id = ?").run(accountId);
      this.db.prepare("DELETE FROM analytics_clear_requests WHERE account_id = ?").run(accountId);
      this.db.prepare("DELETE FROM connector_actions WHERE account_id = ?").run(accountId);
      this.db.prepare("DELETE FROM space_accounts WHERE account_id = ?").run(accountId);
    };

    if (typeof this.db.transaction === "function") {
      this.db.transaction(deleteRows)();
    } else {
      this.db.exec("BEGIN");
      try {
        deleteRows();
        this.db.exec("COMMIT");
      } catch (error) {
        this.db.exec("ROLLBACK");
        throw error;
      }
    }

    return { deletedNoteIds, deletedFolderIds };
  }

  deleteFolder(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const folder = this._getFolderInAccountScope(id);
      if (folder?.deleted_at) return { success: false, error: "Folder not found" };
      if (!folder) return { success: false, error: "Folder not found" };
      if (folder.is_default) return { success: false, error: "Cannot delete default folders" };
      const allChildNotes = "SELECT id FROM notes WHERE folder_id = ?";
      const childNotes = `${allChildNotes} AND deleted_at IS NULL`;
      const accountScope = this._accountScopeCondition("notes");
      const noteIds = this.db
        .prepare(`${childNotes} AND ${accountScope.sql}`)
        .all(id, ...accountScope.params)
        .map((row) => row.id);
      let relocatedNotes = [];
      this.db.transaction(() => {
        relocatedNotes = this._releaseOutOfScopeChildNotes(id);
        if (!folder.cloud_id) {
          // There is no server operation to deny. Local-only folders can
          // finalize immediately, including their local-only child content.
          this._retireConversationsWhere(`note_id IN (${allChildNotes})`, [id], {
            scrubSyncedMessages: true,
          });
          this._deleteSpeakerRowsForNotes(allChildNotes, id);
          this.db.prepare("DELETE FROM notes WHERE folder_id = ?").run(id);
          this._retireConversationsWhere("folder_id = ?", [id], {
            scrubSyncedMessages: true,
          });
          this.db.prepare("DELETE FROM folders WHERE id = ?").run(id);
          return;
        }

        const journal = this.db.prepare(
          `INSERT OR IGNORE INTO optimistic_folder_delete_rows
             (folder_id, entity_type, entity_id, original_sync_status,
              original_deleted_at, original_updated_at)
           VALUES (?, ?, ?, ?, ?, ?)`
        );
        journal.run(
          id,
          "folder",
          id,
          folder.sync_status ?? "synced",
          folder.deleted_at ?? null,
          folder.updated_at ?? null
        );

        const activeNotes = this.db.prepare(
          "SELECT id, sync_status, deleted_at, updated_at FROM notes WHERE folder_id = ? AND deleted_at IS NULL"
        );
        for (const note of activeNotes.all(id)) {
          journal.run(
            id,
            "note",
            note.id,
            note.sync_status ?? "pending",
            note.deleted_at ?? null,
            note.updated_at ?? null
          );
        }

        // Only live conversations belong to this rollback. A tombstone that
        // predates the folder action is an independent user-requested delete
        // and must remain pending on both denial and confirmation.
        const activeConversations = this.db
          .prepare(
            `SELECT id, sync_status, deleted_at, updated_at
             FROM agent_conversations
             WHERE deleted_at IS NULL
               AND (folder_id = ? OR note_id IN (${childNotes}))`
          )
          .all(id, id);
        for (const conversation of activeConversations) {
          journal.run(
            id,
            "conversation",
            conversation.id,
            conversation.sync_status ?? "pending",
            conversation.deleted_at ?? null,
            conversation.updated_at ?? null
          );
        }

        // Keep every child row and message body in place while hiding them
        // from normal readers and all per-note/per-conversation sync queues.
        this.db
          .prepare(
            `UPDATE notes
             SET deleted_at = datetime('now'), sync_status = 'folder_delete_pending',
                 updated_at = datetime('now')
             WHERE id IN (
               SELECT entity_id FROM optimistic_folder_delete_rows
               WHERE folder_id = ? AND entity_type = 'note'
             )`
          )
          .run(id);
        this.db
          .prepare(
            `UPDATE agent_conversations
             SET deleted_at = datetime('now'), sync_status = 'folder_delete_pending',
                 updated_at = datetime('now')
             WHERE id IN (
               SELECT entity_id FROM optimistic_folder_delete_rows
               WHERE folder_id = ? AND entity_type = 'conversation'
             )`
          )
          .run(id);
        this.db
          .prepare(
            `UPDATE folders
             SET deleted_at = datetime('now'), updated_at = datetime('now'),
                 sync_status = 'pending'
             WHERE id = ?`
          )
          .run(id);
      })();
      return { success: true, id, noteIds, relocatedNotes };
    } catch (error) {
      debugLogger.error("Error deleting folder", { error: error.message }, "notes");
      throw error;
    }
  }

  renameFolder(id, name) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const folder = this._getFolderInAccountScope(id);
      if (folder?.deleted_at) return { success: false, error: "Folder not found" };
      if (!folder) return { success: false, error: "Folder not found" };
      if (folder.is_default) return { success: false, error: "Cannot rename default folders" };
      const trimmed = (name || "").trim();
      if (!trimmed) return { success: false, error: "Folder name is required" };
      const folderScope = this._accountScopeCondition("folders");
      const existing = this.db
        .prepare(
          `SELECT id FROM folders
           WHERE name = ? AND space_id = ? AND id != ? AND ${FOLDER_NAME_TAKEN_FILTER}
             AND ${folderScope.sql}`
        )
        .get(trimmed, folder.space_id, id, ...folderScope.params);
      if (existing) return { success: false, error: "A folder with that name already exists" };
      this.db
        .prepare(
          "UPDATE folders SET name = ?, sync_status = 'pending', updated_at = datetime('now') WHERE id = ?"
        )
        .run(trimmed, id);
      const updated = this.db.prepare("SELECT * FROM folders WHERE id = ?").get(id);
      return { success: true, folder: updated };
    } catch (error) {
      debugLogger.error("Error renaming folder", { error: error.message }, "notes");
      throw error;
    }
  }

  moveFolderToSpace(id, spaceId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const folder = this._getFolderInAccountScope(id);
      if (folder?.deleted_at) return { success: false, error: "Folder not found" };
      if (!folder) return { success: false, error: "Folder not found" };
      if (folder.is_default) return { success: false, error: "Cannot move default folders" };
      const space = this.getSpace(spaceId);
      if (!space) return { success: false, error: "Space not found" };
      if (folder.space_id === spaceId) return { success: true, folder, notes: [] };
      const folderScope = this._accountScopeCondition("folders");
      const existing = this.db
        .prepare(
          `SELECT id FROM folders
           WHERE name = ? AND space_id = ? AND id != ? AND ${FOLDER_NAME_TAKEN_FILTER}
             AND ${folderScope.sql}`
        )
        .get(folder.name, spaceId, id, ...folderScope.params);
      if (existing) return { success: false, error: "A folder with that name already exists" };
      // D6: cloud-backed rows leaving a team must keep pushing their scope
      // retraction even in the backup-off team-only pass (left_team).
      const oldKind = this.db
        .prepare("SELECT kind FROM spaces WHERE id = ?")
        .get(folder.space_id)?.kind;
      const leftTeam = oldKind === "team" && space.kind === "private" ? 1 : 0;
      const nextAccountId = this._accountIdForSpace(spaceId);
      const notes = this.db.transaction(() => {
        this.db
          .prepare(
            "UPDATE folders SET space_id = ?, account_id = ?, sync_status = 'pending', updated_at = datetime('now'), left_team = ? WHERE id = ?"
          )
          .run(spaceId, nextAccountId, leftTeam && folder.cloud_id ? 1 : 0, id);
        this.db
          .prepare(
            "UPDATE notes SET space_id = ?, account_id = ?, sync_status = 'pending', updated_at = datetime('now'), left_team = (CASE WHEN ? = 1 AND cloud_id IS NOT NULL THEN 1 ELSE 0 END) WHERE folder_id = ? AND deleted_at IS NULL"
          )
          .run(spaceId, nextAccountId, leftTeam, id);
        this.db
          .prepare("UPDATE agent_conversations SET space_id = ? WHERE folder_id = ?")
          .run(spaceId, id);
        return this.db
          .prepare("SELECT * FROM notes WHERE folder_id = ? AND deleted_at IS NULL")
          .all(id);
      })();
      const updated = this.db.prepare("SELECT * FROM folders WHERE id = ?").get(id);
      return { success: true, folder: updated, notes };
    } catch (error) {
      debugLogger.error("Error moving folder to space", { error: error.message }, "spaces");
      throw error;
    }
  }

  getFolderNoteCounts() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      // folder_id NULL rows are space-root notes; grouping by space_id too
      // attributes them per space so the tree shows true space totals.
      const accountScope = this._accountScopeCondition("notes");
      return this.db
        .prepare(
          `SELECT space_id, folder_id, COUNT(*) as count
           FROM notes
           WHERE deleted_at IS NULL AND ${accountScope.sql}
           GROUP BY space_id, folder_id`
        )
        .all(...accountScope.params);
    } catch (error) {
      debugLogger.error("Error getting folder note counts", { error: error.message }, "notes");
      throw error;
    }
  }

  getPrivateSpaceId() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.prepare("SELECT id FROM spaces WHERE kind = 'private'").get()?.id ?? null;
    } catch (error) {
      debugLogger.error("Error getting private space id", { error: error.message }, "spaces");
      throw error;
    }
  }

  // Parses the teams JSON mirror so renderer consumers only ever see an array.
  _spaceRow(row) {
    if (!row) return row;
    let teams = [];
    if (row.teams) {
      try {
        teams = JSON.parse(row.teams);
      } catch {
        teams = [];
      }
    }
    return { ...row, teams };
  }

  getSpaces() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          `SELECT * FROM spaces
           WHERE deleted_at IS NULL
             AND (kind = 'private' OR EXISTS (
               SELECT 1 FROM space_accounts
               WHERE space_accounts.space_id = spaces.id
                 AND space_accounts.account_id = ?
             ))
           ORDER BY CASE WHEN kind = 'private' THEN 0 ELSE 1 END, sort_order ASC, name ASC`
        )
        .all(this.activeAccountId)
        .map((row) => this._spaceRow(row));
    } catch (error) {
      debugLogger.error("Error getting spaces", { error: error.message }, "spaces");
      throw error;
    }
  }

  getSpace(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const row = this.db
        .prepare(
          `SELECT * FROM spaces
           WHERE id = ? AND deleted_at IS NULL
             AND (kind = 'private' OR EXISTS (
               SELECT 1 FROM space_accounts
               WHERE space_accounts.space_id = spaces.id
                 AND space_accounts.account_id = ?
             ))`
        )
        .get(id, this.activeAccountId);
      return row ? this._spaceRow(row) : null;
    } catch (error) {
      debugLogger.error("Error getting space", { error: error.message }, "spaces");
      throw error;
    }
  }

  updateSpace(id, { name, emoji } = {}) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const space = this.getSpace(id);
      if (!space) return { success: false, error: "Space not found" };
      const fields = [];
      const values = [];
      if (name !== undefined) {
        if (space.kind === "private") {
          return { success: false, error: "Cannot rename the private space" };
        }
        const trimmed = (name || "").trim();
        if (!trimmed) return { success: false, error: "Space name is required" };
        fields.push("name = ?");
        values.push(trimmed);
      }
      if (emoji !== undefined) {
        fields.push("emoji = ?");
        values.push(emoji);
      }
      if (fields.length === 0) return { success: false };
      fields.push("sync_status = 'pending'", "updated_at = datetime('now')");
      values.push(id);
      this.db.prepare(`UPDATE spaces SET ${fields.join(", ")} WHERE id = ?`).run(...values);
      const updated = this._spaceRow(this.db.prepare("SELECT * FROM spaces WHERE id = ?").get(id));
      return { success: true, space: updated };
    } catch (error) {
      debugLogger.error("Error updating space", { error: error.message }, "spaces");
      throw error;
    }
  }

  setSpaceSyncStatus(id, status) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getSpace(id)) return { success: false, space: null };
      const result = this.db
        .prepare("UPDATE spaces SET sync_status = ? WHERE id = ? AND deleted_at IS NULL")
        .run(status, id);
      const success = result.changes > 0;
      return { success, space: success ? this.getSpace(id) : null };
    } catch (error) {
      debugLogger.error("Error setting space sync status", { error: error.message }, "spaces");
      throw error;
    }
  }

  getSpaceByCloudSpaceId(cloudSpaceId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const row = this.db
        .prepare("SELECT * FROM spaces WHERE cloud_space_id = ?")
        .get(cloudSpaceId);
      return row ? this._spaceRow(row) : null;
    } catch (error) {
      debugLogger.error(
        "Error getting space by cloud space id",
        { error: error.message },
        "spaces"
      );
      throw error;
    }
  }

  upsertSpaceFromCloud(space) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.activeAccountId) {
        throw new Error("Cannot cache a cloud space without an active account scope");
      }
      const updatedAt = space.updated_at || space.created_at || new Date().toISOString();
      const teams = Array.isArray(space.teams) ? space.teams : [];
      const teamsJson = JSON.stringify(teams);
      let existing = this.db.prepare("SELECT * FROM spaces WHERE cloud_space_id = ?").get(space.id);
      if (!existing && teams.length === 1) {
        // Adopt a pre-spaces row: the server backfilled one space per legacy
        // team, so a single-team space claims the local row that mirrored that
        // team. Keeps local ids alive for chats, vector payloads and tree state.
        existing = this.db
          .prepare("SELECT * FROM spaces WHERE cloud_space_id IS NULL AND cloud_team_id = ?")
          .get(teams[0].id);
      }
      if (existing) {
        this.db
          .prepare(
            `UPDATE spaces SET cloud_space_id = ?, workspace_id = ?, name = ?, emoji = ?, my_role = ?,
               my_direct_role = ?, member_count = ?, teams = ?, deleted_at = NULL, updated_at = ?
             WHERE id = ?`
          )
          .run(
            space.id,
            space.workspace_id ?? null,
            space.name,
            space.emoji ?? null,
            space.my_role ?? null,
            space.my_direct_role ?? null,
            space.member_count ?? null,
            teamsJson,
            updatedAt,
            existing.id
          );
        this.db
          .prepare("INSERT OR IGNORE INTO space_accounts (space_id, account_id) VALUES (?, ?)")
          .run(existing.id, this.activeAccountId);
        return this._spaceRow(
          this.db.prepare("SELECT * FROM spaces WHERE id = ?").get(existing.id)
        );
      }
      const maxOrder = this.db.prepare("SELECT MAX(sort_order) as max_order FROM spaces").get();
      // New spaces insert as 'pending' (skeletons until the content backfill
      // completes); updates never touch sync_status, so an interrupted
      // backfill's 'pending' survives the next mirror pass and re-runs.
      const result = this.db
        .prepare(
          `INSERT INTO spaces (client_space_id, cloud_space_id, workspace_id, kind, name, emoji,
             sort_order, my_role, my_direct_role, member_count, teams, sync_status, created_at,
             updated_at)
           VALUES (?, ?, ?, 'team', ?, ?, ?, ?, ?, ?, ?, 'pending', ?, ?)`
        )
        .run(
          randomUUID(),
          space.id,
          space.workspace_id ?? null,
          space.name,
          space.emoji ?? null,
          (maxOrder?.max_order ?? 0) + 1,
          space.my_role ?? null,
          space.my_direct_role ?? null,
          space.member_count ?? null,
          teamsJson,
          space.created_at || updatedAt,
          updatedAt
        );
      this.db
        .prepare("INSERT OR IGNORE INTO space_accounts (space_id, account_id) VALUES (?, ?)")
        .run(result.lastInsertRowid, this.activeAccountId);
      return this._spaceRow(
        this.db.prepare("SELECT * FROM spaces WHERE id = ?").get(result.lastInsertRowid)
      );
    } catch (error) {
      debugLogger.error("Error upserting space from cloud", { error: error.message }, "spaces");
      throw error;
    }
  }

  // Speaker rows cascade on note delete (ON DELETE CASCADE), but callers that
  // only tombstone notes still need the explicit cleanup.
  _deleteSpeakerRowsForNotes(noteIdSubquery, param) {
    this.db.prepare(`DELETE FROM speaker_mappings WHERE note_id IN (${noteIdSubquery})`).run(param);
    this.db
      .prepare(`DELETE FROM note_speaker_embeddings WHERE note_id IN (${noteIdSubquery})`)
      .run(param);
  }

  // Conversations whose note or container is being removed must not survive
  // as global chats. Synced rows tombstone (like deleteAgentConversation) so
  // the next push retires the cloud copy; a hard local delete would let the
  // next pull resurrect the conversation. Irreversible purge/revocation
  // callers also scrub their messages, while an optimistic ordinary delete
  // retains synced messages until the server accepts it. Never-synced rows
  // hard-delete: there is no server row to retire, and a bare tombstone would
  // linger forever (getPendingConversationDeletes requires a cloud_id).
  _retireConversationsWhere(
    filter,
    params,
    { scrubSyncedMessages = false, syncedTombstoneStatus = "pending" } = {}
  ) {
    const messageFilter = scrubSyncedMessages ? filter : `cloud_id IS NULL AND (${filter})`;
    this.db
      .prepare(
        `DELETE FROM agent_messages WHERE conversation_id IN (SELECT id FROM agent_conversations WHERE ${messageFilter})`
      )
      .run(...params);
    this.db
      .prepare(`DELETE FROM agent_conversations WHERE cloud_id IS NULL AND (${filter})`)
      .run(...params);
    this.db
      .prepare(
        `UPDATE agent_conversations SET deleted_at = datetime('now'), sync_status = ?, updated_at = datetime('now') WHERE cloud_id IS NOT NULL AND deleted_at IS NULL AND (${filter})`
      )
      .run(syncedTombstoneStatus, ...params);
  }

  // Account transitions are a local privacy boundary, not a cloud mutation:
  // no old-account conversation row (including a pending cloud tombstone)
  // may remain for the next account to see or push.
  _hardDeleteConversationsWhere(filter, params) {
    this.db
      .prepare(
        `DELETE FROM agent_messages WHERE conversation_id IN (SELECT id FROM agent_conversations WHERE ${filter})`
      )
      .run(...params);
    this.db.prepare(`DELETE FROM agent_conversations WHERE ${filter}`).run(...params);
  }

  purgeSpace(localSpaceId, options = {}) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const mode = options?.mode ?? "preserve-dirty";
      if (mode !== "preserve-dirty" && mode !== "destructive") {
        return { success: false, error: "Invalid purge mode" };
      }
      const destructive = mode === "destructive";
      const space = this.getSpace(localSpaceId);
      if (!space) return { success: false, error: "Space not found" };
      if (space.kind === "private") {
        return { success: false, error: "Cannot purge the private space" };
      }
      if (this._releaseActiveSpaceMembershipIfShared(localSpaceId)) {
        return {
          success: true,
          noteIds: [],
          folderNames: [],
          spaceId: localSpaceId,
          relocatedNotes: [],
          relocatedCount: 0,
          relocatedTitles: [],
          preservedForOtherAccounts: true,
        };
      }
      if (!destructive) {
        // Space revocation supersedes any unresolved folder delete. Recover
        // the held rows first so dirty/local-only children are classified by
        // their real pre-delete state and can still relocate to Personal.
        const heldFolderIds = this.db
          .prepare(
            `SELECT DISTINCT r.folder_id
             FROM optimistic_folder_delete_rows r
             JOIN folders f ON f.id = r.folder_id
             WHERE r.entity_type = 'folder' AND f.space_id = ?`
          )
          .all(localSpaceId)
          .map((row) => row.folder_id);
        for (const folderId of heldFolderIds) {
          const rollback = this.restoreFolderAfterDeniedDelete(folderId);
          if (!rollback.success) return rollback;
        }
      }
      const privateSpaceId = this.getPrivateSpaceId();
      const { noteIds, folderNames, relocatedNotes } = this.db.transaction(() => {
        // Dirty or never-synced notes are the only surviving content (plan
        // §7.2, matching relocateRevokedFolder): they move to the private
        // space root with forked identities — the server row (if any) stays
        // the team's, so the next push re-creates them as personal notes.
        let relocated = [];
        if (!destructive && privateSpaceId != null) {
          const preservedIds = this.db
            .prepare(
              "SELECT id FROM notes WHERE space_id = ? AND deleted_at IS NULL AND (sync_status != 'synced' OR cloud_id IS NULL)"
            )
            .all(localSpaceId)
            .map((row) => row.id);
          if (preservedIds.length > 0) {
            const relocateNote = this.db.prepare(
              "UPDATE notes SET space_id = ?, folder_id = NULL, client_note_id = ?, cloud_id = NULL, cloud_updated_at = NULL, cloud_revision = NULL, cloud_create_rejected = 0, cloud_create_pending = NULL, owner_user_id = NULL, updated_by_user_id = NULL, sync_status = 'pending', left_team = 0, is_shared = 0, share_token = NULL, updated_at = datetime('now') WHERE id = ?"
            );
            const detachNoteConversation = this.db.prepare(
              "UPDATE agent_conversations SET space_id = NULL, folder_id = NULL WHERE note_id = ?"
            );
            for (const noteId of preservedIds) {
              relocateNote.run(privateSpaceId, randomUUID(), noteId);
              // A note chat follows the dirty note fork into Personal. Clear
              // any redundant team-container scope so the container cleanup
              // below cannot retire a conversation whose note survived.
              detachNoteConversation.run(noteId);
            }
            const getNote = this.db.prepare("SELECT * FROM notes WHERE id = ?");
            relocated = preservedIds.map((id) => getNote.get(id));
          }
        }
        const ids = this.db
          .prepare("SELECT id FROM notes WHERE space_id = ?")
          .all(localSpaceId)
          .map((row) => row.id);
        const names = this.db
          .prepare("SELECT name FROM folders WHERE space_id = ?")
          .all(localSpaceId)
          .map((row) => row.name);
        // Note chats normally carry only note_id, so container cleanup alone
        // cannot see them. Retire them while the doomed note rows still
        // identify the space; relocated dirty-note chats were moved above.
        if (destructive) {
          // Account-boundary cleanup must leave neither visible chats nor
          // cloud-delete tombstones. Match note-only chats and both kinds of
          // container scope before deleting their parent rows.
          this._hardDeleteConversationsWhere(
            `note_id IN (SELECT id FROM notes WHERE space_id = ?)
             OR space_id = ?
             OR folder_id IN (SELECT id FROM folders WHERE space_id = ?)`,
            [localSpaceId, localSpaceId, localSpaceId]
          );
          this.db
            .prepare(
              `DELETE FROM optimistic_folder_delete_rows
               WHERE folder_id IN (SELECT id FROM folders WHERE space_id = ?)`
            )
            .run(localSpaceId);
        } else {
          this._retireConversationsWhere(
            "note_id IN (SELECT id FROM notes WHERE space_id = ?)",
            [localSpaceId],
            { scrubSyncedMessages: true }
          );
        }
        this._deleteSpeakerRowsForNotes("SELECT id FROM notes WHERE space_id = ?", localSpaceId);
        this.db.prepare("DELETE FROM notes WHERE space_id = ?").run(localSpaceId);
        // Deleted-note tombstones in other spaces can still reference folders
        // in this space (folder moved across spaces after the delete); clear
        // the refs or the folder delete below aborts on the FK.
        this.db
          .prepare(
            "UPDATE notes SET folder_id = NULL WHERE space_id != ? AND folder_id IN (SELECT id FROM folders WHERE space_id = ?)"
          )
          .run(localSpaceId, localSpaceId);
        if (!destructive) {
          this._retireConversationsWhere(
            "space_id = ? OR folder_id IN (SELECT id FROM folders WHERE space_id = ?)",
            [localSpaceId, localSpaceId],
            { scrubSyncedMessages: true }
          );
        }
        this.db.prepare("DELETE FROM folders WHERE space_id = ?").run(localSpaceId);
        this.db.prepare("DELETE FROM spaces WHERE id = ?").run(localSpaceId);
        return { noteIds: ids, folderNames: names, relocatedNotes: relocated };
      })();
      return {
        success: true,
        noteIds,
        folderNames,
        spaceId: localSpaceId,
        relocatedNotes,
        relocatedCount: relocatedNotes.length,
        relocatedTitles: relocatedNotes.slice(0, 3).map((note) => note.title),
      };
    } catch (error) {
      debugLogger.error("Error purging space", { error: error.message }, "spaces");
      throw error;
    }
  }

  _initVectorChangeJournal() {
    this.db.transaction(() => {
      const journalExists = this.db
        .prepare(
          "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'pending_vector_changes'"
        )
        .get();
      // AUTOINCREMENT keeps acknowledgements unique even after the queue is
      // emptied or a deleted note id is reused while indexing is in flight.
      this.db.exec(`
        CREATE TABLE IF NOT EXISTS pending_vector_changes (
          revision INTEGER PRIMARY KEY AUTOINCREMENT,
          note_id INTEGER NOT NULL UNIQUE
        );
        CREATE TRIGGER IF NOT EXISTS notes_vector_insert AFTER INSERT ON notes BEGIN
          INSERT INTO pending_vector_changes (note_id) VALUES (NEW.id)
          ON CONFLICT(note_id) DO UPDATE SET revision = excluded.revision;
        END;
        CREATE TRIGGER IF NOT EXISTS notes_vector_update
        AFTER UPDATE OF title, content, enhanced_content, space_id, folder_id, deleted_at ON notes
        WHEN OLD.title IS NOT NEW.title OR OLD.content IS NOT NEW.content
          OR OLD.enhanced_content IS NOT NEW.enhanced_content
          OR OLD.space_id IS NOT NEW.space_id OR OLD.folder_id IS NOT NEW.folder_id
          OR OLD.deleted_at IS NOT NEW.deleted_at
        BEGIN
          INSERT INTO pending_vector_changes (note_id) VALUES (NEW.id)
          ON CONFLICT(note_id) DO UPDATE SET revision = excluded.revision;
        END;
        CREATE TRIGGER IF NOT EXISTS notes_vector_delete AFTER DELETE ON notes BEGIN
          INSERT INTO pending_vector_changes (note_id) VALUES (OLD.id)
          ON CONFLICT(note_id) DO UPDATE SET revision = excluded.revision;
        END;
      `);
      if (!journalExists) this.enqueueAllVectorChanges();
    })();
  }

  getPendingVectorChanges(limit = 50, afterRevision = 0) {
    if (!this.db) throw new Error("Database not initialized");
    return this.db
      .prepare(
        "SELECT note_id, revision FROM pending_vector_changes WHERE revision > ? ORDER BY revision LIMIT ?"
      )
      .all(afterRevision, limit);
  }

  clearPendingVectorChange(noteId, revision) {
    if (!this.db) throw new Error("Database not initialized");
    const result = this.db
      .prepare("DELETE FROM pending_vector_changes WHERE note_id = ? AND revision = ?")
      .run(noteId, revision);
    return { success: true, changes: result.changes };
  }

  enqueueAllVectorChanges() {
    if (!this.db) throw new Error("Database not initialized");
    this.db.exec(`
      INSERT INTO pending_vector_changes (note_id) SELECT id FROM notes WHERE TRUE
      ON CONFLICT(note_id) DO UPDATE SET revision = excluded.revision
    `);
    return { success: true };
  }

  getNoteForVectorIndex(noteId) {
    if (!this.db) throw new Error("Database not initialized");
    // The durable index spans accounts; renderer/search reads still use the
    // scoped getNote API before exposing any indexed result.
    return this.db.prepare("SELECT * FROM notes WHERE id = ?").get(noteId) || null;
  }

  addPendingVectorPurge(spaceId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db
        .prepare("INSERT OR IGNORE INTO pending_vector_purges (space_id) VALUES (?)")
        .run(spaceId);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error adding pending vector purge", { error: error.message }, "spaces");
      throw error;
    }
  }

  getPendingVectorPurges() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.prepare("SELECT space_id FROM pending_vector_purges").all();
    } catch (error) {
      debugLogger.error("Error getting pending vector purges", { error: error.message }, "spaces");
      throw error;
    }
  }

  clearPendingVectorPurge(spaceId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db.prepare("DELETE FROM pending_vector_purges WHERE space_id = ?").run(spaceId);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error clearing pending vector purge", { error: error.message }, "spaces");
      throw error;
    }
  }

  getActions() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare("SELECT * FROM actions ORDER BY sort_order ASC, created_at ASC")
        .all()
        .map(toActionItem);
    } catch (error) {
      debugLogger.error("Error getting actions", { error: error.message }, "notes");
      throw error;
    }
  }

  getAction(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return toActionItem(this.db.prepare("SELECT * FROM actions WHERE id = ?").get(id));
    } catch (error) {
      debugLogger.error("Error getting action", { error: error.message }, "notes");
      throw error;
    }
  }

  createAction(
    name,
    description,
    prompt,
    icon = "sparkles",
    { kind = "template", sections, output } = {}
  ) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (kind !== "template" && kind !== "action") {
        return { success: false, error: "Unknown action kind" };
      }
      const fields = resolveActionFields(kind, { name, description, prompt, sections, output });
      if (fields.error) return { success: false, error: fields.error };
      const maxOrder = this.db.prepare("SELECT MAX(sort_order) as max_order FROM actions").get();
      const sortOrder = (maxOrder?.max_order ?? 0) + 1;
      const result = this.db
        .prepare(
          "INSERT INTO actions (name, description, prompt, icon, sort_order, client_id, kind, sections, output) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .run(
          fields.name,
          fields.description,
          fields.prompt,
          icon || "sparkles",
          sortOrder,
          randomUUID(),
          kind,
          fields.sections,
          fields.output
        );
      const action = toActionItem(
        this.db.prepare("SELECT * FROM actions WHERE id = ?").get(result.lastInsertRowid)
      );
      return { success: true, action };
    } catch (error) {
      debugLogger.error("Error creating action", { error: error.message }, "notes");
      throw error;
    }
  }

  // A row's kind and client id never change; every other field is replaced by
  // the update or kept, then validated as a whole.
  updateAction(id, updates) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const current = this.getAction(id);
      if (!current) return { success: false, error: "Action not found" };
      const merged = { ...current };
      for (const key of [
        "name",
        "description",
        "prompt",
        "icon",
        "sort_order",
        "sections",
        "output",
      ]) {
        if (updates[key] !== undefined) merged[key] = updates[key];
      }
      const fields = resolveActionFields(current.kind, merged);
      if (fields.error) return { success: false, error: fields.error };
      this.db
        .prepare(
          "UPDATE actions SET name = ?, description = ?, prompt = ?, icon = ?, sort_order = ?, sections = ?, output = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?"
        )
        .run(
          fields.name,
          fields.description,
          fields.prompt,
          merged.icon,
          merged.sort_order,
          fields.sections,
          fields.output,
          id
        );
      return { success: true, action: this.getAction(id) };
    } catch (error) {
      debugLogger.error("Error updating action", { error: error.message }, "notes");
      throw error;
    }
  }

  deleteAction(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const action = this.db.prepare("SELECT * FROM actions WHERE id = ?").get(id);
      if (!action) return { success: false, error: "Action not found" };
      if (action.is_builtin) return { success: false, error: "Cannot delete built-in actions" };
      this.db.prepare("DELETE FROM actions WHERE id = ?").run(id);
      return { success: true, id };
    } catch (error) {
      debugLogger.error("Error deleting action", { error: error.message }, "notes");
      throw error;
    }
  }

  deleteNote(id) {
    try {
      if (!this.db) {
        throw new Error("Database not initialized");
      }
      const accountScope = this._accountScopeCondition("notes");
      const stmt = this.db.prepare(
        `UPDATE notes
         SET deleted_at = datetime('now'), sync_status = 'pending', updated_at = datetime('now')
         WHERE id = ? AND deleted_at IS NULL AND ${accountScope.sql}`
      );
      const result = stmt.run(id, ...accountScope.params);
      return { success: result.changes > 0, id };
    } catch (error) {
      debugLogger.error("Error deleting note", { error: error.message }, "notes");
      throw error;
    }
  }

  createAgentConversation(title = "Untitled", noteId = null, spaceId = null, folderId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.transaction(() => {
        let note = null;
        let space = null;
        let folder = null;

        if (noteId != null) {
          note = this.getNote(noteId);
          if (!note || note.deleted_at) return null;
          if (note.folder_id != null) {
            const noteFolder = this._getFolderInAccountScope(note.folder_id);
            if (!noteFolder || noteFolder.deleted_at || noteFolder.space_id !== note.space_id) {
              return null;
            }
          }
        }
        if (spaceId != null) {
          space = this.getSpace(spaceId);
          if (!space) return null;
        }
        if (folderId != null) {
          folder = this._getFolderInAccountScope(folderId);
          if (!folder || folder.deleted_at || !this.getSpace(folder.space_id)) return null;
        }
        if (folder && spaceId != null && folder.space_id !== spaceId) return null;
        if (note && spaceId != null && note.space_id !== spaceId) return null;
        if (note && folderId != null && note.folder_id !== folderId) return null;

        const clientConversationId = randomUUID();
        const result = this.db
          .prepare(
            "INSERT INTO agent_conversations (title, note_id, space_id, folder_id, client_conversation_id) VALUES (?, ?, ?, ?, ?)"
          )
          .run(title, noteId, spaceId, folderId, clientConversationId);
        return this.db
          .prepare("SELECT * FROM agent_conversations WHERE id = ?")
          .get(result.lastInsertRowid);
      })();
    } catch (error) {
      debugLogger.error("Error creating agent conversation", { error: error.message }, "database");
      throw error;
    }
  }

  getConversationsForNote(noteId, limit = 20) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(noteId)) return [];
      return this.db
        .prepare(
          `SELECT c.id, c.title, c.created_at, c.updated_at,
            COUNT(m.id) AS message_count
          FROM agent_conversations c
          LEFT JOIN agent_messages m ON m.conversation_id = c.id
          WHERE c.note_id = ? AND c.deleted_at IS NULL
          GROUP BY c.id
          ORDER BY c.updated_at DESC
          LIMIT ?`
        )
        .all(noteId, limit);
    } catch (error) {
      debugLogger.error(
        "Error getting conversations for note",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // Space-root scope (folderId null) intentionally excludes folder-scoped
  // conversations — each container surfaces only its own chats.
  getConversationsForContainer(spaceId, folderId = null, limit = 20) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (folderId != null) {
        const folder = this._getFolderInAccountScope(folderId);
        if (!folder || folder.deleted_at) return [];
      } else if (!this.getSpace(spaceId)) {
        return [];
      }
      const scopeFilter =
        folderId != null ? "c.folder_id = ?" : "c.space_id = ? AND c.folder_id IS NULL";
      const params = folderId != null ? [folderId, limit] : [spaceId, limit];
      return this.db
        .prepare(
          `SELECT c.id, c.title, c.created_at, c.updated_at,
            COUNT(m.id) AS message_count
          FROM agent_conversations c
          LEFT JOIN agent_messages m ON m.conversation_id = c.id
          WHERE ${scopeFilter} AND c.deleted_at IS NULL
          GROUP BY c.id
          ORDER BY c.updated_at DESC
          LIMIT ?`
        )
        .all(...params);
    } catch (error) {
      debugLogger.error(
        "Error getting conversations for container",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getAgentConversations(limit = 50) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          "SELECT * FROM agent_conversations WHERE deleted_at IS NULL AND space_id IS NULL AND folder_id IS NULL ORDER BY updated_at DESC LIMIT ?"
        )
        .all(limit);
    } catch (error) {
      debugLogger.error("Error getting agent conversations", { error: error.message }, "database");
      throw error;
    }
  }

  getAgentConversation(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const conversation = this.db
        .prepare("SELECT * FROM agent_conversations WHERE id = ? AND deleted_at IS NULL")
        .get(id);
      if (!conversation) return null;
      const messages = this.db
        .prepare("SELECT * FROM agent_messages WHERE conversation_id = ? ORDER BY created_at ASC")
        .all(id);
      return { ...conversation, messages };
    } catch (error) {
      debugLogger.error("Error getting agent conversation", { error: error.message }, "database");
      throw error;
    }
  }

  deleteAgentConversation(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db
        .prepare(
          "UPDATE agent_conversations SET deleted_at = datetime('now'), sync_status = 'pending', updated_at = datetime('now') WHERE id = ?"
        )
        .run(id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error("Error deleting agent conversation", { error: error.message }, "database");
      throw error;
    }
  }

  updateAgentConversationTitle(id, title) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db
        .prepare(
          "UPDATE agent_conversations SET title = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND deleted_at IS NULL"
        )
        .run(title, id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error(
        "Error updating agent conversation title",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  saveGoogleTokens(tokens) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare(
        `INSERT INTO google_calendar_tokens (google_email, access_token, refresh_token, expires_at, scope)
         VALUES (?, ?, ?, ?, ?)
         ON CONFLICT(google_email) DO UPDATE SET
           access_token = excluded.access_token,
           refresh_token = excluded.refresh_token,
           expires_at = excluded.expires_at,
           scope = excluded.scope,
           updated_at = CURRENT_TIMESTAMP`
      );
      stmt.run(
        tokens.google_email,
        tokens.access_token,
        tokens.refresh_token,
        tokens.expires_at,
        tokens.scope
      );
      return { success: true };
    } catch (error) {
      debugLogger.error("Error saving Google tokens", { error: error.message }, "gcal");
      throw error;
    }
  }

  getGoogleTokens() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.prepare("SELECT * FROM google_calendar_tokens LIMIT 1").get() || null;
    } catch (error) {
      debugLogger.error("Error getting Google tokens", { error: error.message }, "gcal");
      throw error;
    }
  }

  getGoogleTokensByEmail(email) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return (
        this.db.prepare("SELECT * FROM google_calendar_tokens WHERE google_email = ?").get(email) ||
        null
      );
    } catch (error) {
      debugLogger.error("Error getting Google tokens by email", { error: error.message }, "gcal");
      throw error;
    }
  }

  addAgentMessage(conversationId, role, content, metadata) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.transaction(() => {
        const conversation = this.db
          .prepare("SELECT id FROM agent_conversations WHERE id = ? AND deleted_at IS NULL")
          .get(conversationId);
        if (!conversation) return null;

        const metadataStr = metadata ? JSON.stringify(metadata) : null;
        const result = this.db
          .prepare(
            "INSERT INTO agent_messages (conversation_id, role, content, metadata) VALUES (?, ?, ?, ?)"
          )
          .run(conversationId, role, content, metadataStr);
        this.db
          .prepare(
            "UPDATE agent_conversations SET updated_at = CURRENT_TIMESTAMP WHERE id = ? AND deleted_at IS NULL"
          )
          .run(conversationId);
        return this.db
          .prepare("SELECT * FROM agent_messages WHERE id = ?")
          .get(result.lastInsertRowid);
      })();
    } catch (error) {
      debugLogger.error("Error adding agent message", { error: error.message }, "database");
      throw error;
    }
  }

  getAllGoogleTokens() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.prepare("SELECT * FROM google_calendar_tokens").all();
    } catch (error) {
      debugLogger.error("Error getting all Google tokens", { error: error.message }, "gcal");
      throw error;
    }
  }

  getGoogleAccounts() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare("SELECT google_email AS email FROM google_calendar_tokens ORDER BY created_at ASC")
        .all();
    } catch (error) {
      debugLogger.error("Error getting Google accounts", { error: error.message }, "gcal");
      throw error;
    }
  }

  removeGoogleAccount(email) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction(() => {
        const calendarIds = this.db
          .prepare("SELECT id FROM google_calendars WHERE account_email = ?")
          .all(email)
          .map((c) => c.id);
        if (calendarIds.length > 0) {
          const placeholders = calendarIds.map(() => "?").join(", ");
          this.db
            .prepare(
              `DELETE FROM calendar_events WHERE provider = 'google' AND calendar_id IN (${placeholders})`
            )
            .run(...calendarIds);
        }
        this.db.prepare("DELETE FROM google_calendars WHERE account_email = ?").run(email);
        this.db.prepare("DELETE FROM google_calendar_tokens WHERE google_email = ?").run(email);
        this._removeContactSources("source = ?", contactSource("google", email));
      });
      transaction();
      return { success: true };
    } catch (error) {
      debugLogger.error("Error removing Google account", { error: error.message }, "gcal");
      throw error;
    }
  }

  deleteGoogleTokens() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db.prepare("DELETE FROM google_calendar_tokens").run();
      return { success: true };
    } catch (error) {
      debugLogger.error("Error deleting Google tokens", { error: error.message }, "gcal");
      throw error;
    }
  }

  saveGoogleCalendars(calendars, accountEmail = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare(
        `INSERT INTO google_calendars (id, summary, description, background_color, account_email, is_primary)
         VALUES (?, ?, ?, ?, ?, ?)
         ON CONFLICT(id) DO UPDATE SET
           summary = excluded.summary,
           description = excluded.description,
           background_color = excluded.background_color,
           account_email = excluded.account_email,
           is_primary = excluded.is_primary`
      );
      for (const cal of calendars) {
        stmt.run(
          cal.id,
          cal.summary,
          cal.description || null,
          cal.background_color || null,
          accountEmail,
          cal.is_primary ? 1 : 0
        );
      }
      return { success: true };
    } catch (error) {
      debugLogger.error("Error saving Google calendars", { error: error.message }, "gcal");
      throw error;
    }
  }

  applyPrimaryOnlyToSelection(primaryOnly) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db
        .prepare(
          "UPDATE google_calendars SET is_selected = CASE WHEN ? = 1 THEN is_primary ELSE 1 END"
        )
        .run(primaryOnly ? 1 : 0);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error applying primary-only selection", { error: error.message }, "gcal");
      throw error;
    }
  }

  getGoogleCalendars(accountEmail = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (accountEmail) {
        return this.db
          .prepare("SELECT * FROM google_calendars WHERE account_email = ?")
          .all(accountEmail);
      }
      return this.db.prepare("SELECT * FROM google_calendars").all();
    } catch (error) {
      debugLogger.error("Error getting Google calendars", { error: error.message }, "gcal");
      throw error;
    }
  }

  updateCalendarSelection(calendarId, isSelected) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db
        .prepare("UPDATE google_calendars SET is_selected = ? WHERE id = ?")
        .run(isSelected ? 1 : 0, calendarId);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error updating calendar selection", { error: error.message }, "gcal");
      throw error;
    }
  }

  getAgentMessages(conversationId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare("SELECT * FROM agent_messages WHERE conversation_id = ? ORDER BY created_at ASC")
        .all(conversationId);
    } catch (error) {
      debugLogger.error("Error getting agent messages", { error: error.message }, "database");
      throw error;
    }
  }

  getSelectedCalendars(accountEmail = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (accountEmail) {
        return this.db
          .prepare("SELECT * FROM google_calendars WHERE is_selected = 1 AND account_email = ?")
          .all(accountEmail);
      }
      return this.db.prepare("SELECT * FROM google_calendars WHERE is_selected = 1").all();
    } catch (error) {
      debugLogger.error("Error getting selected calendars", { error: error.message }, "gcal");
      throw error;
    }
  }

  upsertCalendarEvents(events) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction((eventList) => {
        const stmt = this.db.prepare(
          "INSERT OR REPLACE INTO calendar_events (id, calendar_id, provider, summary, start_time, end_time, is_all_day, status, availability_status, self_response_status, hangout_link, conference_data, organizer_email, attendees_count, attendees, web_link, synced_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)"
        );
        for (const e of eventList) {
          stmt.run(
            e.id,
            e.calendar_id,
            e.provider || "google",
            e.summary || null,
            e.start_time,
            e.end_time,
            e.is_all_day ? 1 : 0,
            e.status || "confirmed",
            e.availability_status || "unknown",
            e.self_response_status || "unknown",
            e.hangout_link || null,
            e.conference_data || null,
            e.organizer_email || null,
            e.attendees_count || 0,
            e.attendees || null,
            e.web_link || null
          );
        }
      });
      transaction(events);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error upserting calendar events", { error: error.message }, "gcal");
      throw error;
    }
  }

  getActiveEvents() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          dedupedEventsQuery(
            "datetime(start_time) <= datetime('now') AND datetime(end_time) > datetime('now') AND is_all_day = 0 AND status IN ('confirmed', 'tentative')"
          )
        )
        .all()
        .filter((event) => event.self_response_status !== "declined")
        .map(stripDedupeColumn);
    } catch (error) {
      debugLogger.error("Error getting active events", { error: error.message }, "gcal");
      throw error;
    }
  }

  searchNotes(query, limit = 50, spaceId = null, folderId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const ftsQuery = buildNoteSearchQuery(query);
      if (!ftsQuery) return [];
      const accountScope = this._accountScopeCondition("n");
      const params = [ftsQuery, ...accountScope.params];
      let scopeFilter = "";
      if (spaceId != null) {
        scopeFilter += " AND n.space_id = ?";
        params.push(spaceId);
      }
      if (folderId != null) {
        scopeFilter += " AND n.folder_id = ?";
        params.push(folderId);
      }
      params.push(limit);
      return this.db
        .prepare(
          `
        SELECT n.*
        FROM notes n
        JOIN notes_fts ON notes_fts.rowid = n.id
        WHERE notes_fts MATCH ? AND n.deleted_at IS NULL AND ${accountScope.sql}${scopeFilter}
        ORDER BY notes_fts.rank
        LIMIT ?
      `
        )
        .all(...params);
    } catch (error) {
      debugLogger.error("Error searching notes", { error: error.message }, "database");
      throw error;
    }
  }

  getUpcomingEvents(windowMinutes = 1440) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          dedupedEventsQuery(
            "((datetime(start_time) > datetime('now') AND datetime(start_time) <= datetime('now', '+' || ? || ' minutes')) OR (datetime(start_time) <= datetime('now') AND datetime(end_time) > datetime('now'))) AND is_all_day = 0 AND status IN ('confirmed', 'tentative')"
          )
        )
        .all(windowMinutes)
        .filter((event) => event.self_response_status !== "declined")
        .map(stripDedupeColumn);
    } catch (error) {
      debugLogger.error("Error getting upcoming events", { error: error.message }, "gcal");
      throw error;
    }
  }

  getCalendarEventsInRange(start, end, providers) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const rangeStart = Date.parse(start);
      const rangeEnd = Date.parse(end);
      if (!Number.isFinite(rangeStart) || !Number.isFinite(rangeEnd) || rangeEnd <= rangeStart) {
        throw new RangeError("Invalid calendar event range");
      }

      const selectedProviders = [...new Set(providers)].filter((provider) =>
        AVAILABILITY_PROVIDERS.has(provider)
      );
      if (selectedProviders.length === 0) return [];
      const placeholders = selectedProviders.map(() => "?").join(", ");
      const events = this.db
        .prepare(
          dedupedEventsQuery(
            `provider IN (${placeholders}) AND status IN ('confirmed', 'tentative') AND ${SELECTED_CALENDAR_EVENT_FILTER}`
          )
        )
        .all(...selectedProviders)
        .map(stripDedupeColumn);

      return events.filter((event) => {
        const isAllDay = event.is_all_day === true || event.is_all_day === 1;
        const eventStart = parseEventTime(event.start_time, isAllDay);
        const eventEnd = parseEventTime(event.end_time, isAllDay);
        return (
          Number.isFinite(eventStart) &&
          Number.isFinite(eventEnd) &&
          eventStart < rangeEnd &&
          eventEnd > rangeStart
        );
      });
    } catch (error) {
      debugLogger.error(
        "Error getting calendar events in range",
        { error: error.message },
        "calendar"
      );
      throw error;
    }
  }

  // The stored events on the user's selected calendars that a note can link
  // to (not cancelled or declined), with its calendar's colour for the picker.
  // `endsAfter` (ISO) leaves out old rows kept only because a note links them,
  // which would otherwise grow with every linked note.
  getLinkableCalendarEvents({ endsAfter = null } = {}) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const colors = new Map(
        this.db
          .prepare(
            `SELECT 'google' AS provider, id, background_color AS color FROM google_calendars
             UNION ALL
             SELECT 'microsoft', id, background_color FROM microsoft_calendars
             UNION ALL
             SELECT 'apple', id, color FROM apple_calendars`
          )
          .all()
          .map((row) => [`${row.provider}:${row.id}`, row.color])
      );
      return this.db
        .prepare(
          dedupedEventsQuery(
            `status IN ('confirmed', 'tentative') AND ${SELECTED_CALENDAR_EVENT_FILTER}
             AND (? IS NULL OR datetime(end_time) > datetime(?))`
          )
        )
        .all(endsAfter, endsAfter)
        .filter((event) => event.self_response_status !== "declined")
        .map((row) => {
          const event = stripDedupeColumn(row);
          const color = colors.get(`${event.provider}:${event.calendar_id}`);
          return {
            ...event,
            calendar_color: /^#[0-9a-f]{6}$/i.test(color ?? "") ? color : null,
          };
        });
    } catch (error) {
      debugLogger.error(
        "Error getting linkable calendar events",
        { error: error.message },
        "calendar"
      );
      throw error;
    }
  }

  getCalendarEventById(eventId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.prepare("SELECT * FROM calendar_events WHERE id = ?").get(eventId) || null;
    } catch (error) {
      debugLogger.error("Error getting calendar event by id", { error: error.message }, "gcal");
      return null;
    }
  }

  // accountId is the account the action ran under (resolved from its
  // credential by the connector IPC), not this.activeAccountId, which lags a
  // sign-in or account switch.
  insertConnectorAction({
    id,
    accountId = null,
    connector,
    action,
    kind,
    destinationLabel = null,
    state,
    resultUrl = null,
    errorCode = null,
  }) {
    if (!this.db) throw new Error("Database not initialized");
    this.db
      .prepare(
        `INSERT INTO connector_actions
           (id, account_id, connector, action, kind, destination_label, state, result_url, error_code)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
      )
      .run(id, accountId, connector, action, kind, destinationLabel, state, resultUrl, errorCode);
  }

  // With fromState, only a row still in that state moves, so a caller can tell
  // a durable transition (1 row) from a missing or already-moved row (0).
  updateConnectorActionState(
    id,
    { state, destinationLabel = null, resultUrl = null, errorCode = null },
    fromState = null
  ) {
    if (!this.db) throw new Error("Database not initialized");
    const guard = fromState === null ? "" : " AND state = ?";
    const params = [
      state,
      destinationLabel,
      resultUrl,
      errorCode,
      id,
      ...(fromState === null ? [] : [fromState]),
    ];
    return this.db
      .prepare(
        `UPDATE connector_actions
           SET state = ?,
               destination_label = COALESCE(?, destination_label),
               result_url = COALESCE(?, result_url),
               error_code = COALESCE(?, error_code),
               updated_at = CURRENT_TIMESTAMP
         WHERE id = ?${guard}`
      )
      .run(...params).changes;
  }

  // Receipts name the people a user wrote to, so only the account that took
  // the action sees them.
  listRecentConnectorActions(connector, limit = 10, accountId = null) {
    if (!this.db) throw new Error("Database not initialized");
    if (!accountId) return [];
    return this.db
      .prepare(
        `SELECT id, connector, action, kind,
                destination_label AS destinationLabel, state,
                result_url AS resultUrl, error_code AS errorCode,
                created_at AS createdAt
           FROM connector_actions
          WHERE connector = ? AND account_id = ?
          ORDER BY created_at DESC, rowid DESC
          LIMIT ?`
      )
      .all(connector, accountId, limit);
  }

  // A quit mid-send can't tell whether the provider acted, so committing rows
  // become unknown; pending rows were never sent. Rows without an account
  // (only pre-release builds wrote them) are listed to no one and no account
  // deletion reaches them, so they are deleted.
  reconcileInterruptedConnectorActions() {
    if (!this.db) throw new Error("Database not initialized");
    const unknown = this.db
      .prepare(
        "UPDATE connector_actions SET state = 'unknown', error_code = 'app_quit', updated_at = CURRENT_TIMESTAMP WHERE state = 'committing'"
      )
      .run().changes;
    const cancelled = this.db
      .prepare(
        "UPDATE connector_actions SET state = 'cancelled', error_code = 'app_quit', updated_at = CURRENT_TIMESTAMP WHERE state = 'pending'"
      )
      .run().changes;
    const orphaned = this.db
      .prepare("DELETE FROM connector_actions WHERE account_id IS NULL")
      .run().changes;
    return { unknown, cancelled, orphaned };
  }

  // What find_contact searches. calendar_events only holds a sync window
  // (about three days back to a month ahead), so the meetings nearest to now go
  // first, and the contacts table (every synced attendee until its last
  // source is disconnected) covers older ones, most recently seen first. Only
  // the user's own meetings count: not cancelled or declined ones, not a
  // colleague's shared Google calendar (whose people still come through
  // contacts). Contacts come only from hand-added rows and connected
  // accounts; rows older builds stored have no source and are left out.
  // excludedEmails are the user's own addresses and every address a stored
  // event flags as a room or resource.
  getContactLookupSources(meetingLimit = 1000) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const meetings = this.db
        .prepare(
          `SELECT start_time, is_all_day, organizer_email, attendees
             FROM calendar_events
            WHERE (attendees IS NOT NULL OR organizer_email IS NOT NULL)
              AND status IN ('confirmed', 'tentative')
              AND self_response_status != 'declined'
              AND ${SELECTED_CALENDAR_EVENT_FILTER}
              AND (provider != 'google' OR EXISTS (
                SELECT 1 FROM google_calendars
                 WHERE google_calendars.id = calendar_events.calendar_id
                   AND (google_calendars.is_primary = 1 OR google_calendars.id = google_calendars.account_email)
              ))
            ORDER BY ABS(julianday(start_time) - julianday('now')) IS NULL,
                     ABS(julianday(start_time) - julianday('now'))
            LIMIT ?`
        )
        .all(meetingLimit);
      const accounts = this.db
        .prepare(
          `SELECT 'google' AS provider, account_email FROM google_calendars
            WHERE account_email IS NOT NULL
           UNION
           SELECT 'microsoft', account_email FROM microsoft_calendars
            WHERE account_email IS NOT NULL`
        )
        .all();
      const appleConnected = Boolean(this.db.prepare("SELECT 1 FROM apple_calendars").get());
      const sources = [
        contactSource("manual"),
        ...(appleConnected ? [contactSource("apple")] : []),
        ...accounts.map((row) => contactSource(row.provider, row.account_email)),
      ];
      const contacts = this.db
        .prepare(
          `SELECT email, display_name FROM contacts
            WHERE email IN (
              SELECT email FROM contact_sources
               WHERE source IN (${sources.map(() => "?").join(", ")})
            )
            ORDER BY updated_at DESC`
        )
        .all(...sources);
      const microsoftAliases = this.db
        .prepare(
          "SELECT own_addresses FROM microsoft_calendar_tokens WHERE own_addresses IS NOT NULL"
        )
        .all()
        .flatMap((row) => JSON.parse(row.own_addresses));
      const resources = this.db
        .prepare(
          `SELECT DISTINCT attendee.value ->> '$.email' AS email
             FROM calendar_events, json_each(calendar_events.attendees) AS attendee
            WHERE json_valid(calendar_events.attendees)
              AND attendee.value ->> '$.resource' = 1`
        )
        .all()
        .map((row) => row.email);
      const excludedEmails = [
        ...accounts.map((row) => row.account_email),
        ...microsoftAliases,
        ...resources,
      ];
      return { meetings, contacts, excludedEmails };
    } catch (error) {
      debugLogger.error("Error reading contact lookup sources", { error: error.message });
      return { meetings: [], contacts: [], excludedEmails: [] };
    }
  }

  // Join & transcribe resumes this note. Google gives every invitee's copy of an
  // event the same id, so a teammate's synced note for the meeting must never
  // match, or both apps record into one note. Ownership follows ownsNote() in
  // spacePermissions.ts, plus Personal rows synced before owners were recorded.
  getOwnNoteByCalendarEventId(eventId, { throwOnError = false } = {}) {
    // An unlinked note stores "", which must never match.
    if (!eventId) return null;
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("notes");
      return (
        this.db
          .prepare(
            `SELECT notes.* FROM notes
             JOIN spaces ON spaces.id = notes.space_id
             WHERE notes.calendar_event_id = ? AND notes.deleted_at IS NULL
               AND ${accountScope.sql}
               AND (notes.cloud_id IS NULL OR notes.owner_user_id = ?
                 OR (notes.owner_user_id IS NULL AND spaces.kind = 'private'))
             ORDER BY datetime(notes.created_at) DESC, notes.id DESC
             LIMIT 1`
          )
          .get(eventId, ...accountScope.params, this.activeAccountId) || null
      );
    } catch (error) {
      debugLogger.error(
        "Error getting note by calendar event id",
        { error: error.message },
        "notes"
      );
      if (throwOnError) throw error;
      return null;
    }
  }

  createMeetingNoteForNotification({ title, folderId, spaceId, eventId, participants }) {
    return this.db.transaction(() => {
      const existing = eventId
        ? this.getOwnNoteByCalendarEventId(eventId, { throwOnError: true })
        : null;
      if (existing) return { created: false, note: existing };
      const { note } = this.saveNote(title, "", "meeting", null, null, folderId, spaceId);
      if (!note) throw new Error("Meeting note not saved");
      if (!eventId) return { created: true, note };
      const result = this.updateNote(note.id, {
        calendar_event_id: eventId,
        ...(participants ? { participants } : {}),
      });
      if (!result.success || !result.note) throw new Error("Meeting metadata not saved");
      return { created: true, note: result.note };
    })();
  }

  // With a source (see contactSource), records that it has seen these
  // contacts.
  upsertContacts(contacts, source = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction((list) => {
        const upsert = this.db.prepare(
          "INSERT INTO contacts (email, display_name, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP) ON CONFLICT(email) DO UPDATE SET display_name = COALESCE(excluded.display_name, contacts.display_name), updated_at = CURRENT_TIMESTAMP"
        );
        const addSource = this.db.prepare(
          "INSERT OR IGNORE INTO contact_sources (email, source) VALUES (?, ?)"
        );
        for (const c of list) {
          if (!c.email) continue;
          const email = c.email.toLowerCase().trim();
          upsert.run(email, c.displayName || null);
          if (source) addSource.run(email, source);
        }
      });
      transaction(contacts);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error upserting contacts", { error: error.message }, "database");
      throw error;
    }
  }

  // A note participant becomes a hand-added contact only when nothing stored
  // it before: picking a synced contact from autocomplete leaves it to the
  // accounts that synced it.
  addManualContact(contact) {
    if (!this.db) throw new Error("Database not initialized");
    const email = contact.email?.toLowerCase().trim();
    const isNew =
      Boolean(email) && !this.db.prepare("SELECT 1 FROM contacts WHERE email = ?").get(email);
    return this.upsertContacts([contact], isNew ? contactSource("manual") : null);
  }

  // Hand-added contacts stay, whatever a sync says about the address.
  removeContacts(emails) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare(
        "DELETE FROM contacts WHERE email = ? AND email NOT IN (SELECT email FROM contact_sources WHERE source = ?)"
      );
      const transaction = this.db.transaction((list) => {
        for (const email of list) stmt.run(email.toLowerCase().trim(), contactSource("manual"));
      });
      transaction(emails);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error removing contacts", { error: error.message }, "database");
      throw error;
    }
  }

  // Drops the matching sources and deletes the contacts no other source still
  // has. Rows older builds stored have no sources and stay.
  _removeContactSources(match, value) {
    this.db
      .prepare(
        `DELETE FROM contacts
          WHERE email IN (SELECT email FROM contact_sources WHERE ${match})
            AND email NOT IN (SELECT email FROM contact_sources WHERE NOT (${match}))`
      )
      .run(value, value);
    this.db.prepare(`DELETE FROM contact_sources WHERE ${match}`).run(value);
  }

  // A calendar sync's attendees. Rooms and the user's own addresses are
  // deleted rather than stored: older builds stored them, and nothing else
  // prunes this table.
  syncCalendarContacts(provider, accountEmail, contacts, notContacts) {
    if (contacts.length > 0) this.upsertContacts(contacts, contactSource(provider, accountEmail));
    if (notContacts.length > 0) this.removeContacts(notContacts);
  }

  searchContacts(query) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const pattern = `%${query || ""}%`;
      return this.db
        .prepare(
          "SELECT email, display_name, created_at, updated_at FROM contacts WHERE email LIKE ? OR display_name LIKE ? ORDER BY display_name ASC, email ASC LIMIT 20"
        )
        .all(pattern, pattern);
    } catch (error) {
      debugLogger.error("Error searching contacts", { error: error.message }, "database");
      throw error;
    }
  }

  clearGoogleCalendarData() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction(() => {
        this.db.prepare("DELETE FROM calendar_events WHERE provider = 'google'").run();
        this.db.prepare("DELETE FROM google_calendars").run();
        this.db.prepare("DELETE FROM google_calendar_tokens").run();
        this._removeContactSources("source LIKE ?", contactSource("google", "%"));
      });
      transaction();
      return { success: true };
    } catch (error) {
      debugLogger.error("Error clearing calendar data", { error: error.message }, "gcal");
      throw error;
    }
  }

  updateCalendarSyncToken(calendarId, syncToken, expiresAt) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db
        .prepare(
          "UPDATE google_calendars SET sync_token = ?, sync_token_expires_at = ? WHERE id = ?"
        )
        .run(syncToken, expiresAt, calendarId);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error updating sync token", { error: error.message }, "gcal");
      throw error;
    }
  }

  removeCalendarEvents(eventIds) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const placeholders = eventIds.map(() => "?").join(", ");
      this.db.prepare(`DELETE FROM calendar_events WHERE id IN (${placeholders})`).run(...eventIds);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error removing calendar events", { error: error.message }, "gcal");
      throw error;
    }
  }

  // A full (non-incremental) REST sync is authoritative for its calendar's
  // window: rows the provider no longer returns were deleted while no valid
  // sync token existed (e.g. the app was offline past the token TTL), so they
  // would otherwise linger and fire reminders for cancelled meetings. Rows
  // referenced by meeting notes are kept so notes retain calendar metadata.
  removeStaleCalendarEvents(provider, calendarId, freshEventIds) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const placeholders = freshEventIds.map(() => "?").join(", ");
      const freshFilter = freshEventIds.length > 0 ? `AND id NOT IN (${placeholders})` : "";
      this.db
        .prepare(
          `DELETE FROM calendar_events
           WHERE provider = ? AND calendar_id = ? ${freshFilter}
             AND id NOT IN (
               SELECT calendar_event_id
               FROM notes
               WHERE calendar_event_id IS NOT NULL AND deleted_at IS NULL
             )`
        )
        .run(provider, calendarId, ...freshEventIds);
      return { success: true };
    } catch (error) {
      debugLogger.error(
        "Error removing stale calendar events",
        { error: error.message },
        provider === "microsoft" ? "mcal" : "gcal"
      );
      throw error;
    }
  }

  removeEventsFromDeselectedCalendars(provider) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const calendarsTable = CALENDARS_TABLE_BY_PROVIDER[provider];
      if (!calendarsTable) throw new Error(`Unknown calendar provider: ${provider}`);
      this.db
        .prepare(
          `DELETE FROM calendar_events WHERE provider = ? AND calendar_id NOT IN (SELECT id FROM ${calendarsTable} WHERE is_selected = 1)`
        )
        .run(provider);
      return { success: true };
    } catch (error) {
      debugLogger.error(
        "Error removing events from deselected calendars",
        { error: error.message },
        provider === "microsoft" ? "mcal" : "gcal"
      );
      throw error;
    }
  }

  saveMicrosoftTokens(tokens) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare(
        `INSERT INTO microsoft_calendar_tokens (microsoft_email, access_token, refresh_token, expires_at, scope, tenant_id)
         VALUES (?, ?, ?, ?, ?, ?)
         ON CONFLICT(microsoft_email) DO UPDATE SET
           access_token = excluded.access_token,
           refresh_token = excluded.refresh_token,
           expires_at = excluded.expires_at,
           scope = excluded.scope,
           tenant_id = COALESCE(excluded.tenant_id, tenant_id),
           updated_at = CURRENT_TIMESTAMP`
      );
      stmt.run(
        tokens.microsoft_email,
        tokens.access_token,
        tokens.refresh_token,
        tokens.expires_at,
        tokens.scope,
        tokens.tenant_id ?? null
      );
      return { success: true };
    } catch (error) {
      debugLogger.error("Error saving Microsoft tokens", { error: error.message }, "mcal");
      throw error;
    }
  }

  getMicrosoftTokensByEmail(email) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return (
        this.db
          .prepare("SELECT * FROM microsoft_calendar_tokens WHERE microsoft_email = ?")
          .get(email) || null
      );
    } catch (error) {
      debugLogger.error(
        "Error getting Microsoft tokens by email",
        { error: error.message },
        "mcal"
      );
      throw error;
    }
  }

  getMicrosoftAccounts() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          "SELECT microsoft_email AS email, tenant_id AS tenantId FROM microsoft_calendar_tokens ORDER BY created_at ASC"
        )
        .all();
    } catch (error) {
      debugLogger.error("Error getting Microsoft accounts", { error: error.message }, "mcal");
      throw error;
    }
  }

  // The account's other addresses (primary SMTP address, aliases), which
  // attendee lists use instead of the sign-in name.
  saveMicrosoftOwnAddresses(email, addresses) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db
        .prepare("UPDATE microsoft_calendar_tokens SET own_addresses = ? WHERE microsoft_email = ?")
        .run(JSON.stringify(addresses), email);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error saving Microsoft addresses", { error: error.message }, "mcal");
      throw error;
    }
  }

  getMicrosoftOwnAddresses(email) {
    const row = this.getMicrosoftTokensByEmail(email);
    return row?.own_addresses ? JSON.parse(row.own_addresses) : [];
  }

  removeMicrosoftAccount(email) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction(() => {
        const calendarIds = this.db
          .prepare("SELECT id FROM microsoft_calendars WHERE account_email = ?")
          .all(email)
          .map((c) => c.id);
        if (calendarIds.length > 0) {
          const placeholders = calendarIds.map(() => "?").join(", ");
          this.db
            .prepare(
              `DELETE FROM calendar_events WHERE provider = 'microsoft' AND calendar_id IN (${placeholders})`
            )
            .run(...calendarIds);
        }
        this.db.prepare("DELETE FROM microsoft_calendars WHERE account_email = ?").run(email);
        this.db
          .prepare("DELETE FROM microsoft_calendar_tokens WHERE microsoft_email = ?")
          .run(email);
        this._removeContactSources("source = ?", contactSource("microsoft", email));
      });
      transaction();
      return { success: true };
    } catch (error) {
      debugLogger.error("Error removing Microsoft account", { error: error.message }, "mcal");
      throw error;
    }
  }

  saveMicrosoftCalendars(calendars, accountEmail) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const stmt = this.db.prepare(
        `INSERT INTO microsoft_calendars (id, summary, background_color, account_email, is_primary)
         VALUES (?, ?, ?, ?, ?)
         ON CONFLICT(id) DO UPDATE SET
           summary = excluded.summary,
           background_color = excluded.background_color,
           account_email = excluded.account_email,
           is_primary = excluded.is_primary`
      );
      for (const cal of calendars) {
        stmt.run(
          cal.id,
          cal.summary,
          cal.background_color || null,
          accountEmail,
          cal.is_primary ? 1 : 0
        );
      }
      return { success: true };
    } catch (error) {
      debugLogger.error("Error saving Microsoft calendars", { error: error.message }, "mcal");
      throw error;
    }
  }

  applyMicrosoftPrimaryOnlyToSelection(primaryOnly) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db
        .prepare(
          "UPDATE microsoft_calendars SET is_selected = CASE WHEN ? = 1 THEN is_primary ELSE 1 END"
        )
        .run(primaryOnly ? 1 : 0);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error applying primary-only selection", { error: error.message }, "mcal");
      throw error;
    }
  }

  getSelectedMicrosoftCalendars() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.prepare("SELECT * FROM microsoft_calendars WHERE is_selected = 1").all();
    } catch (error) {
      debugLogger.error(
        "Error getting selected Microsoft calendars",
        { error: error.message },
        "mcal"
      );
      throw error;
    }
  }

  updateMicrosoftCalendarSyncToken(calendarId, syncToken, expiresAt) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db
        .prepare(
          "UPDATE microsoft_calendars SET sync_token = ?, sync_token_expires_at = ? WHERE id = ?"
        )
        .run(syncToken, expiresAt, calendarId);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error updating sync token", { error: error.message }, "mcal");
      throw error;
    }
  }

  clearMicrosoftCalendarData() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction(() => {
        this.db.prepare("DELETE FROM calendar_events WHERE provider = 'microsoft'").run();
        this.db.prepare("DELETE FROM microsoft_calendars").run();
        this.db.prepare("DELETE FROM microsoft_calendar_tokens").run();
        this._removeContactSources("source LIKE ?", contactSource("microsoft", "%"));
      });
      transaction();
      return { success: true };
    } catch (error) {
      debugLogger.error("Error clearing calendar data", { error: error.message }, "mcal");
      throw error;
    }
  }

  saveAppleCalendars(calendars) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction((list) => {
        // Snapshots are complete: prune calendars removed from Calendar.app,
        // upsert the rest so created_at survives.
        if (list.length === 0) {
          this.db.prepare("DELETE FROM apple_calendars").run();
          return;
        }
        const placeholders = list.map(() => "?").join(", ");
        this.db
          .prepare(`DELETE FROM apple_calendars WHERE id NOT IN (${placeholders})`)
          .run(...list.map((cal) => cal.id));

        const stmt = this.db.prepare(
          `INSERT INTO apple_calendars (id, title, color, source_name)
           VALUES (?, ?, ?, ?)
           ON CONFLICT(id) DO UPDATE SET
             title = excluded.title,
             color = excluded.color,
             source_name = excluded.source_name`
        );
        for (const cal of list) {
          stmt.run(cal.id, cal.title, cal.color || null, cal.source_name || null);
        }
      });
      transaction(calendars);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error saving Apple calendars", { error: error.message }, "acal");
      throw error;
    }
  }

  getAppleCalendars() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.prepare("SELECT * FROM apple_calendars").all();
    } catch (error) {
      debugLogger.error("Error getting Apple calendars", { error: error.message }, "acal");
      throw error;
    }
  }

  // Snapshots cover the full cache window, so missing unreferenced events can
  // be removed while note-linked history is retained.
  replaceAppleCalendarEvents(events) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction((list) => {
        // The helper snapshot only covers the lookback window and the future.
        // Keep older or rescheduled rows that are still referenced by meeting
        // notes so those notes retain their calendar metadata.
        this.db
          .prepare(
            `DELETE FROM calendar_events
             WHERE provider = 'apple'
               AND id NOT IN (
                 SELECT calendar_event_id
                 FROM notes
                 WHERE calendar_event_id IS NOT NULL AND deleted_at IS NULL
               )`
          )
          .run();
        if (list.length > 0) this.upsertCalendarEvents(list);
      });
      transaction(events);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error replacing Apple calendar events", { error: error.message }, "acal");
      throw error;
    }
  }

  clearAppleCalendarData() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction(() => {
        this.db.prepare("DELETE FROM calendar_events WHERE provider = 'apple'").run();
        this.db.prepare("DELETE FROM apple_calendars").run();
        this._removeContactSources("source = ?", contactSource("apple"));
      });
      transaction();
      return { success: true };
    } catch (error) {
      debugLogger.error("Error clearing Apple calendar data", { error: error.message }, "acal");
      throw error;
    }
  }

  getMeetingsFolder(spaceId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("folders");
      return (
        this.db
          .prepare(
            `SELECT id FROM folders
             WHERE name = 'Meetings' AND is_default = 1 AND space_id = ?
               AND ${accountScope.sql}`
          )
          .get(spaceId ?? this.getPrivateSpaceId(), ...accountScope.params) || null
      );
    } catch (error) {
      debugLogger.error("Error getting meetings folder", { error: error.message }, "gcal");
      throw error;
    }
  }

  updateNoteCloudId(id, cloudId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(id)) return null;
      this.db.prepare("UPDATE notes SET cloud_id = ? WHERE id = ?").run(cloudId, id);
      return this.db.prepare("SELECT * FROM notes WHERE id = ?").get(id);
    } catch (error) {
      debugLogger.error("Error updating note cloud_id", { error: error.message }, "database");
      throw error;
    }
  }

  // Share bookkeeping, not a content edit — must not bump updated_at (which
  // would reorder note lists and churn sync last-write-wins comparisons).
  updateNoteShareState(id, { is_shared, share_token }) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(id)) return null;
      if (share_token !== undefined) {
        this.db
          .prepare("UPDATE notes SET is_shared = ?, share_token = ? WHERE id = ?")
          .run(is_shared, share_token, id);
      } else {
        this.db.prepare("UPDATE notes SET is_shared = ? WHERE id = ?").run(is_shared, id);
      }
      return this.db.prepare("SELECT * FROM notes WHERE id = ?").get(id);
    } catch (error) {
      debugLogger.error("Error updating note share state", { error: error.message }, "database");
      throw error;
    }
  }

  cleanup() {
    try {
      if (this.db) {
        try {
          this.db.close();
        } catch (closeError) {
          debugLogger.error("Error closing database", { error: closeError.message }, "database");
        }
        this.db = null;
      }
      const dbPath = path.join(
        app.getPath("userData"),
        process.env.NODE_ENV === "development" ? "transcriptions-dev.db" : "transcriptions.db"
      );
      if (fs.existsSync(dbPath)) {
        fs.unlinkSync(dbPath);
      }
    } catch (error) {
      debugLogger.error("Error deleting database file", { error: error.message }, "database");
    }
  }
  getAgentConversationsWithPreview(limit = 50, offset = 0, includeArchived = false) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const archiveFilter = includeArchived
        ? "WHERE c.archived_at IS NOT NULL AND c.deleted_at IS NULL AND c.space_id IS NULL AND c.folder_id IS NULL"
        : "WHERE c.archived_at IS NULL AND c.deleted_at IS NULL AND c.space_id IS NULL AND c.folder_id IS NULL";
      return this.db
        .prepare(
          `SELECT c.id, c.title, c.created_at, c.updated_at, c.archived_at, c.cloud_id,
            COUNT(m.id) AS message_count,
            (SELECT content FROM agent_messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) AS last_message,
            (SELECT role FROM agent_messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) AS last_message_role
          FROM agent_conversations c
          LEFT JOIN agent_messages m ON m.conversation_id = c.id
          ${archiveFilter}
          GROUP BY c.id
          ORDER BY c.updated_at DESC
          LIMIT ? OFFSET ?`
        )
        .all(limit, offset);
    } catch (error) {
      debugLogger.error(
        "Error getting agent conversations with preview",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  searchAgentConversations(query, limit = 20) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const pattern = `%${query}%`;
      return this.db
        .prepare(
          `SELECT DISTINCT c.id, c.title, c.created_at, c.updated_at, c.archived_at, c.cloud_id,
            COUNT(m.id) AS message_count,
            (SELECT content FROM agent_messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) AS last_message,
            (SELECT role FROM agent_messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) AS last_message_role
          FROM agent_conversations c
          LEFT JOIN agent_messages m ON m.conversation_id = c.id
          LEFT JOIN agent_messages ms ON ms.conversation_id = c.id
          WHERE c.archived_at IS NULL AND c.deleted_at IS NULL
            AND c.space_id IS NULL AND c.folder_id IS NULL
            AND (c.title LIKE ? OR ms.content LIKE ?)
          GROUP BY c.id
          ORDER BY c.updated_at DESC
          LIMIT ?`
        )
        .all(pattern, pattern, limit);
    } catch (error) {
      debugLogger.error(
        "Error searching agent conversations",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  archiveAgentConversation(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db
        .prepare(
          "UPDATE agent_conversations SET archived_at = CURRENT_TIMESTAMP WHERE id = ? AND deleted_at IS NULL"
        )
        .run(id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error("Error archiving agent conversation", { error: error.message }, "database");
      throw error;
    }
  }

  unarchiveAgentConversation(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db
        .prepare(
          "UPDATE agent_conversations SET archived_at = NULL WHERE id = ? AND deleted_at IS NULL"
        )
        .run(id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error(
        "Error unarchiving agent conversation",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  updateAgentConversationCloudId(id, cloudId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db
        .prepare("UPDATE agent_conversations SET cloud_id = ? WHERE id = ? AND deleted_at IS NULL")
        .run(cloudId, id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error(
        "Error updating agent conversation cloud_id",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  _normalizeEmail(email) {
    const trimmed = (email || "").trim().toLowerCase();
    return trimmed || null;
  }

  _findProfileByEmail(email) {
    const normalized = this._normalizeEmail(email);
    if (!normalized) return null;
    return this.db.prepare("SELECT * FROM speaker_profiles WHERE lower(email) = ?").get(normalized);
  }

  upsertSpeakerProfile(name, email, embeddingBuffer, profileId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const normalizedEmail = this._normalizeEmail(email);
      let existing = profileId
        ? this.db.prepare("SELECT * FROM speaker_profiles WHERE id = ?").get(profileId)
        : null;
      if (!existing && normalizedEmail) {
        existing = this._findProfileByEmail(normalizedEmail);
      }
      if (!existing) {
        existing = this.db
          .prepare("SELECT * FROM speaker_profiles WHERE display_name = ?")
          .get(name);
      }
      if (existing) {
        const stored = new Float32Array(
          existing.embedding.buffer,
          existing.embedding.byteOffset,
          existing.embedding.byteLength / 4
        );
        const incoming = new Float32Array(
          embeddingBuffer.buffer,
          embeddingBuffer.byteOffset,
          embeddingBuffer.byteLength / 4
        );
        const updated = new Float32Array(stored.length);
        for (let i = 0; i < stored.length; i++) {
          updated[i] = 0.3 * incoming[i] + 0.7 * stored[i];
        }
        const updatedBuf = Buffer.from(updated.buffer);
        const finalEmail = normalizedEmail || existing.email || null;
        this.db
          .prepare(
            "UPDATE speaker_profiles SET display_name = ?, email = ?, embedding = ?, sample_count = sample_count + 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?"
          )
          .run(name, finalEmail, updatedBuf, existing.id);
        const resolved = this.db
          .prepare("SELECT * FROM speaker_profiles WHERE id = ?")
          .get(existing.id);
        if (normalizedEmail) {
          const collision = this.db
            .prepare("SELECT * FROM speaker_profiles WHERE lower(email) = ? AND id != ?")
            .get(normalizedEmail, existing.id);
          if (collision) {
            return this.mergeSpeakerProfiles(resolved, collision);
          }
        }
        return resolved;
      }
      const result = this.db
        .prepare("INSERT INTO speaker_profiles (display_name, email, embedding) VALUES (?, ?, ?)")
        .run(name, normalizedEmail, embeddingBuffer);
      return this.db
        .prepare("SELECT * FROM speaker_profiles WHERE id = ?")
        .get(result.lastInsertRowid);
    } catch (error) {
      debugLogger.error("Error upserting speaker profile", { error: error.message }, "database");
      throw error;
    }
  }

  attachEmailToProfile(profileId, email) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const normalizedEmail = this._normalizeEmail(email);
      const profile = this.db.prepare("SELECT * FROM speaker_profiles WHERE id = ?").get(profileId);
      if (!profile) throw new Error(`Speaker profile ${profileId} not found`);

      if (!normalizedEmail) {
        this.db
          .prepare(
            "UPDATE speaker_profiles SET email = NULL, updated_at = CURRENT_TIMESTAMP WHERE id = ?"
          )
          .run(profileId);
        return this.db.prepare("SELECT * FROM speaker_profiles WHERE id = ?").get(profileId);
      }

      const collision = this._findProfileByEmail(normalizedEmail);
      if (collision && collision.id !== profileId) {
        return this.mergeSpeakerProfiles(collision, profile);
      }

      this.db
        .prepare(
          "UPDATE speaker_profiles SET email = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?"
        )
        .run(normalizedEmail, profileId);
      return this.db.prepare("SELECT * FROM speaker_profiles WHERE id = ?").get(profileId);
    } catch (error) {
      debugLogger.error(
        "Error attaching email to speaker profile",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  mergeSpeakerProfiles(a, b) {
    const winner = (a.sample_count || 0) >= (b.sample_count || 0) ? a : b;
    const loser = winner === a ? b : a;

    const winnerEmb = new Float32Array(
      winner.embedding.buffer,
      winner.embedding.byteOffset,
      winner.embedding.byteLength / 4
    );
    const loserEmb = new Float32Array(
      loser.embedding.buffer,
      loser.embedding.byteOffset,
      loser.embedding.byteLength / 4
    );
    const wSamples = winner.sample_count || 1;
    const lSamples = loser.sample_count || 1;
    const total = wSamples + lSamples;
    const blended = new Float32Array(winnerEmb.length);
    for (let i = 0; i < winnerEmb.length; i++) {
      blended[i] = (winnerEmb[i] * wSamples + loserEmb[i] * lSamples) / total;
    }

    const finalEmail = winner.email || loser.email || null;
    const finalName = winner.display_name || loser.display_name;

    const tx = this.db.transaction(() => {
      this.db
        .prepare(
          "UPDATE speaker_profiles SET display_name = ?, email = ?, embedding = ?, sample_count = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?"
        )
        .run(finalName, finalEmail, Buffer.from(blended.buffer), total, winner.id);
      this.db
        .prepare(
          "UPDATE speaker_mappings SET profile_id = ?, display_name = ? WHERE profile_id = ?"
        )
        .run(winner.id, finalName, loser.id);
      this.db.prepare("DELETE FROM speaker_profiles WHERE id = ?").run(loser.id);
    });
    tx();

    return this.db.prepare("SELECT * FROM speaker_profiles WHERE id = ?").get(winner.id);
  }

  getSpeakerProfiles(includeEmbedding = false) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const query = includeEmbedding
        ? "SELECT * FROM speaker_profiles"
        : `SELECT id, display_name, email, sample_count, created_at, updated_at
           FROM speaker_profiles`;
      return this.db.prepare(query).all();
    } catch (error) {
      debugLogger.error("Error getting speaker profiles", { error: error.message }, "database");
      throw error;
    }
  }

  setSpeakerMapping(noteId, speakerId, profileId, displayName) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(noteId)) return { success: false, error: "Note not found" };
      this.db
        .prepare(
          "INSERT OR REPLACE INTO speaker_mappings (note_id, speaker_id, profile_id, display_name) VALUES (?, ?, ?, ?)"
        )
        .run(noteId, speakerId, profileId, displayName);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error setting speaker mapping", { error: error.message }, "database");
      throw error;
    }
  }

  getSpeakerMappings(noteId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(noteId)) return [];
      return this.db.prepare("SELECT * FROM speaker_mappings WHERE note_id = ?").all(noteId);
    } catch (error) {
      debugLogger.error("Error getting speaker mappings", { error: error.message }, "database");
      throw error;
    }
  }

  saveNoteSpeakerEmbeddings(noteId, embeddings) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(noteId)) return { success: false, error: "Note not found" };
      const transaction = this.db.transaction((entries) => {
        const stmt = this.db.prepare(
          "INSERT OR REPLACE INTO note_speaker_embeddings (note_id, speaker_id, embedding) VALUES (?, ?, ?)"
        );
        for (const [speakerId, buffer] of entries) {
          stmt.run(noteId, speakerId, buffer);
        }
      });
      transaction(Object.entries(embeddings));
      return { success: true };
    } catch (error) {
      debugLogger.error(
        "Error saving note speaker embeddings",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getNoteSpeakerEmbeddings(noteId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(noteId)) return [];
      return this.db.prepare("SELECT * FROM note_speaker_embeddings WHERE note_id = ?").all(noteId);
    } catch (error) {
      debugLogger.error(
        "Error getting note speaker embeddings",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getPendingNotes(spaceKind = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("n");
      if (spaceKind != null) {
        // 'team' also returns cloud-backed rows that just LEFT a team: their
        // scope retraction must push even when cloud backup is off (D6).
        const leftTeam =
          spaceKind === "team" ? " OR (n.left_team = 1 AND n.cloud_id IS NOT NULL)" : "";
        return this.db
          .prepare(
            `SELECT n.* FROM notes n JOIN spaces s ON s.id = n.space_id
             WHERE n.sync_status IN ('pending', 'error') AND n.deleted_at IS NULL
               AND ${accountScope.sql} AND (s.kind = ?${leftTeam})`
          )
          .all(...accountScope.params, spaceKind);
      }
      // 'error' rows retry too: a transient failure (e.g. one offline pass)
      // must not strand a note until its next local edit.
      return this.db
        .prepare(
          `SELECT n.* FROM notes n
           WHERE n.sync_status IN ('pending', 'error') AND n.deleted_at IS NULL
             AND ${accountScope.sql}`
        )
        .all(...accountScope.params);
    } catch (error) {
      debugLogger.error("Error getting pending notes", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingNoteDeletes() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("n");
      return this.db
        .prepare(
          `SELECT * FROM notes n
           WHERE deleted_at IS NOT NULL AND cloud_id IS NOT NULL
             AND sync_status = 'pending'
             AND ${accountScope.sql}
             AND NOT EXISTS (
               SELECT 1 FROM optimistic_folder_delete_rows r
               WHERE r.entity_type = 'note' AND r.entity_id = n.id
             )`
        )
        .all(...accountScope.params);
    } catch (error) {
      debugLogger.error("Error getting pending note deletes", { error: error.message }, "database");
      throw error;
    }
  }

  getNoteByClientId(clientNoteId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("n");
      return (
        this.db
          .prepare(
            `SELECT n.*,
               EXISTS (
                 SELECT 1 FROM optimistic_folder_delete_rows r
                 WHERE r.entity_type = 'note' AND r.entity_id = n.id
               ) AS folder_delete_pending
             FROM notes n
             WHERE n.client_note_id = ? AND ${accountScope.sql}`
          )
          .get(clientNoteId, ...accountScope.params) || null
      );
    } catch (error) {
      debugLogger.error("Error getting note by client id", { error: error.message }, "database");
      throw error;
    }
  }

  upsertNoteFromCloud(cloudNote, localFolderId, localSpaceId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const spaceId = localSpaceId ?? this.getPrivateSpaceId();
      const accountId = this._accountIdForSpace(spaceId);
      const existing = this.db
        .prepare("SELECT * FROM notes WHERE client_note_id = ?")
        .get(cloudNote.client_note_id);
      // Rows without an account (created signed out or before attribution) and
      // personal<->team moves are adopted as before; only another account's
      // row or another cloud row is left alone. Never throw: one bad row would
      // abort the whole pull and stall its cursor.
      const otherAccount = existing?.account_id && accountId && existing.account_id !== accountId;
      const otherCloudRow = existing?.cloud_id && existing.cloud_id !== cloudNote.id;
      if (otherAccount || otherCloudRow) {
        debugLogger.warn(
          "Skipping cloud note that belongs to another local row",
          { otherAccount: Boolean(otherAccount), otherCloudRow: Boolean(otherCloudRow) },
          "database"
        );
        // IPC broadcasts every returned note, so a skipped row must stay private.
        return null;
      }
      const versioned = hasNoteRevision(cloudNote.revision);
      // A copy older than the revision this device acknowledged is stale. One
      // without a revision (an API that predates them) applies as before and
      // drops the local revision.
      if (
        versioned &&
        hasNoteRevision(existing?.cloud_revision) &&
        cloudNote.revision < existing.cloud_revision
      )
        return existing;
      const cleared = versioned ? clearedNoteFields(cloudNote) : [];
      const clearsContent = cleared.includes("content");
      const clearsSummary = cleared.includes("enhanced_content");
      cloudNote = { ...cloudNote };
      if (!cloudNote.content?.trim()) cloudNote.content = "";
      if (!cloudNote.enhanced_content?.trim()) cloudNote.enhanced_content = null;
      if (clearsSummary) {
        for (const key of SUMMARY_CLEARED_METADATA) cloudNote[key] = null;
      }
      const hasExplicitCreator = Object.prototype.hasOwnProperty.call(
        cloudNote,
        "created_by_user_id"
      );
      const hasLegacyCreator = Object.prototype.hasOwnProperty.call(cloudNote, "user_id");
      const createdByUserId = hasExplicitCreator ? cloudNote.created_by_user_id : cloudNote.user_id;
      const creatorUpdate =
        hasExplicitCreator || hasLegacyCreator
          ? "excluded.created_by_user_id"
          : "created_by_user_id";
      const hasExplicitUpdater = Object.prototype.hasOwnProperty.call(
        cloudNote,
        "updated_by_user_id"
      );
      const updaterUpdate = hasExplicitUpdater
        ? "excluded.updated_by_user_id"
        : "updated_by_user_id";
      // Sync must never replace non-empty local content/enhanced_content/
      // transcript with an empty cloud value (#1290, the #938 invariant),
      // unless the cloud recorded that empty value as a deliberate clear.
      // The enhancement prompt/hash/template travel with enhanced_content.
      const keepsLocalContent = clearsContent
        ? "0"
        : "COALESCE(excluded.content, '') = '' AND COALESCE(content, '') <> ''";
      const keepsLocalSummary = clearsSummary
        ? "0"
        : "COALESCE(excluded.enhanced_content, '') = '' AND COALESCE(enhanced_content, '') <> ''";
      // An API that predates template ids omits the key; keep the local one.
      const templateIdUpdate = Object.prototype.hasOwnProperty.call(
        cloudNote,
        "enhancement_template_id"
      )
        ? `CASE WHEN ${keepsLocalSummary}
            THEN enhancement_template_id ELSE excluded.enhancement_template_id END`
        : "enhancement_template_id";
      const stmt = this.db.prepare(`
        INSERT INTO notes (client_note_id, cloud_id, title, content, enhanced_content,
          enhancement_prompt, enhancement_template_id, enhanced_at_content_hash, note_type, source_file,
          audio_duration_seconds, transcript, folder_id, space_id, participants, calendar_event_id,
          diarization_enabled, expected_speaker_count, updated_by_user_id, owner_user_id, created_by_user_id, sync_status, created_at, updated_at,
          cloud_updated_at, account_id, cloud_revision)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'synced', ?, ?, ?, ?, ?)
        ON CONFLICT(client_note_id) DO UPDATE SET
          cloud_id = excluded.cloud_id,
          title = excluded.title,
          content = CASE WHEN ${keepsLocalContent}
            THEN content ELSE excluded.content END,
          enhanced_content = CASE WHEN ${keepsLocalSummary}
            THEN enhanced_content ELSE excluded.enhanced_content END,
          enhancement_prompt = CASE WHEN ${keepsLocalSummary}
            THEN enhancement_prompt ELSE excluded.enhancement_prompt END,
          enhancement_template_id = ${templateIdUpdate},
          enhanced_at_content_hash = CASE WHEN ${keepsLocalSummary}
            THEN enhanced_at_content_hash ELSE excluded.enhanced_at_content_hash END,
          transcript = CASE
            WHEN COALESCE(excluded.transcript, '') = '' AND COALESCE(transcript, '') <> ''
            THEN transcript ELSE excluded.transcript END,
          folder_id = excluded.folder_id,
          space_id = excluded.space_id,
          account_id = excluded.account_id,
          participants = COALESCE(excluded.participants, participants),
          calendar_event_id = COALESCE(excluded.calendar_event_id, calendar_event_id),
          diarization_enabled = COALESCE(excluded.diarization_enabled, diarization_enabled),
          expected_speaker_count = COALESCE(excluded.expected_speaker_count, expected_speaker_count),
          updated_by_user_id = ${updaterUpdate},
          owner_user_id = COALESCE(excluded.owner_user_id, owner_user_id),
          created_by_user_id = ${creatorUpdate},
          sync_status = 'synced',
          left_team = 0,
          updated_at = excluded.updated_at,
          cloud_updated_at = excluded.cloud_updated_at,
          cloud_revision = excluded.cloud_revision,
          cloud_create_rejected = 0,
          cloud_create_pending = NULL,
          content_sync_operation = NULL,
          enhanced_content_sync_operation = NULL
      `);
      stmt.run(
        cloudNote.client_note_id,
        cloudNote.id,
        cloudNote.title,
        cloudNote.content,
        cloudNote.enhanced_content || null,
        cloudNote.enhancement_prompt || null,
        cloudNote.enhancement_template_id || null,
        cloudNote.enhanced_at_content_hash || null,
        cloudNote.note_type || "personal",
        cloudNote.source_file || null,
        cloudNote.audio_duration_seconds || null,
        cloudNote.transcript || null,
        localFolderId,
        spaceId,
        cloudNote.participants || null,
        // "" is the cloud's record of a deliberate unlink; NULL means the API
        // omitted the field, which the COALESCE above treats as "keep local".
        cloudNote.calendar_event_id ?? null,
        cloudNote.diarization_enabled ?? null,
        normalizeStoredSpeakerCount(cloudNote.expected_speaker_count),
        cloudNote.updated_by_user_id || null,
        cloudNote.user_id ?? null,
        createdByUserId ?? null,
        cloudNote.created_at,
        cloudNote.updated_at,
        cloudNote.updated_at,
        accountId,
        versioned ? cloudNote.revision : null
      );
      return this.db
        .prepare("SELECT * FROM notes WHERE client_note_id = ?")
        .get(cloudNote.client_note_id);
    } catch (error) {
      debugLogger.error("Error upserting note from cloud", { error: error.message }, "database");
      throw error;
    }
  }

  markNoteSynced(id, cloudId, cloudUpdatedAt = null, ownerUserId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(id)) return { success: false, changes: 0 };
      // cloud_updated_at and owner_user_id are overwritten even with null: a
      // forked row that re-creates under a new cloud_id must not keep the old
      // note's base or its previous owner (a null base settles last-write-wins
      // once; a null owner fails closed until a pull records the real one).
      this.db
        .prepare(
          "UPDATE notes SET sync_status = 'synced', cloud_id = ?, left_team = 0, cloud_updated_at = ?, owner_user_id = ? WHERE id = ?"
        )
        .run(cloudId, cloudUpdatedAt, ownerUserId, id);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error marking note synced", { error: error.message }, "database");
      throw error;
    }
  }

  // A row moved from a team space to Personal while its push was in flight
  // still owes the server a scope retraction for the returned team-side copy.
  _leftTeamDuringPush(snapshotSpaceId, currentSpaceId) {
    const kindOf = this.db.prepare("SELECT kind FROM spaces WHERE id = ?");
    return kindOf.get(snapshotSpaceId)?.kind === "team" &&
      kindOf.get(currentSpaceId)?.kind === "private"
      ? 1
      : 0;
  }

  // A create response arrives after an arbitrary network delay. Adopt it only
  // for the exact local identity that issued the request, and settle only when
  // every pushed field still matches that request's snapshot. A newer edit
  // adopts the cloud identity/base but remains pending. Response metadata is
  // assigned even when null because a fork may still carry the old identity's
  // base/owner. A purge forks the client_note_id, so the relocated Personal
  // row is never mutated. Partial migration creates explicitly opt out of
  // settling so a later full PATCH still delivers fields the POST omitted.
  // writeRejected links a create the server refused without writing it, so
  // the row stays parked until a pull resolves it (cloud_create_rejected).
  acknowledgeNoteCreate(
    id,
    snapshot,
    cloudId,
    cloudUpdatedAt = null,
    ownerUserId = null,
    options = {}
  ) {
    let { settleIfUnchanged = true } = options;
    const {
      cloudRevision = null,
      writeRejected = false,
      cloudNote = null,
      requiresReconciliation = false,
      reconcilePending = false,
    } = options;
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(id)) {
        return { success: true, outcome: "identity-changed", changes: 0 };
      }
      const expectedClientNoteId = snapshot?.client_note_id;
      if (!expectedClientNoteId || !cloudId) {
        return { success: false, outcome: "unresolved" };
      }

      return this.db.transaction(() => {
        const current = this.db.prepare("SELECT * FROM notes WHERE id = ?").get(id);
        if (!current || current.client_note_id !== expectedClientNoteId) {
          // If the same identity exists under an unexpected numeric row, do
          // not mutate it and do not authorize deletion of its cloud result.
          const identityStillExists = this.db
            .prepare("SELECT 1 FROM notes WHERE client_note_id = ?")
            .get(expectedClientNoteId);
          return {
            success: true,
            outcome: identityStillExists ? "unresolved" : "orphaned",
          };
        }

        if (
          current.cloud_id &&
          !(reconcilePending && current.cloud_create_pending && current.cloud_id === cloudId)
        ) {
          // Concurrent creates should be idempotent and return the same id.
          // A different id is ambiguous, though, so never replace the adopted
          // identity or authorize destructive cleanup in that case.
          return {
            success: true,
            outcome: current.cloud_id === cloudId ? "already-linked" : "unresolved",
          };
        }

        if (reconcilePending && current.cloud_create_pending) {
          const pending = JSON.parse(current.cloud_create_pending);
          snapshot = pending.snapshot;
          settleIfUnchanged = pending.settleIfUnchanged;
          if (!cloudNote) return { success: true, outcome: "awaiting-cloud" };
          if (
            hasNoteRevision(pending.revision) &&
            hasNoteRevision(cloudRevision) &&
            cloudRevision < pending.revision
          )
            return { success: true, outcome: "awaiting-cloud" };
          if (
            !hasNoteRevision(pending.revision) &&
            pending.updatedAt &&
            Date.parse(cloudUpdatedAt) < Date.parse(pending.updatedAt)
          )
            return { success: true, outcome: "awaiting-cloud" };
          const sameReceipt = hasNoteRevision(pending.revision)
            ? cloudRevision === pending.revision
            : Boolean(
                pending.updatedAt && Date.parse(pending.updatedAt) === Date.parse(cloudUpdatedAt)
              );
          if (!sameReceipt) {
            // A pull can already contain another device's newer write. Only
            // an unchanged full create can take it without a conflict choice.
            if (
              !settleIfUnchanged ||
              hasPendingNoteClear(snapshot) ||
              !rowMatchesSnapshot(current, snapshot, NOTE_CREATE_ACK_FIELDS) ||
              options.localSpaceId === undefined
            )
              return { success: true, outcome: "conflict" };
            const note = this.upsertNoteFromCloud(
              {
                ...cloudNote,
                id: cloudId,
                client_note_id: expectedClientNoteId,
                updated_at: cloudUpdatedAt,
                revision: cloudRevision,
              },
              options.localFolderId ?? null,
              options.localSpaceId
            );
            return { success: true, outcome: "synced", note };
          }
        }
        const unchanged = rowMatchesSnapshot(current, snapshot, NOTE_CREATE_ACK_FIELDS);
        const leftTeam = this._leftTeamDuringPush(snapshot.space_id, current.space_id);

        // A metadata-only receipt proves identity, not which text survived
        // the upsert. Save its request and keep every push gated until a pull
        // reconciles that exact receipt (including after restart).
        if (requiresReconciliation) {
          this.db
            .prepare(
              `UPDATE notes SET cloud_id = ?, owner_user_id = ?,
              sync_status = CASE WHEN sync_status = 'synced' THEN 'pending' ELSE sync_status END,
              cloud_create_pending = ?,
              left_team = CASE WHEN ? = 1 THEN 1 ELSE left_team END
            WHERE id = ? AND client_note_id = ?`
            )
            .run(
              cloudId,
              ownerUserId,
              JSON.stringify({
                snapshot: {
                  ...snapshot,
                  sync_status: snapshot.sync_status === "synced" ? "pending" : snapshot.sync_status,
                },
                settleIfUnchanged,
                revision: cloudRevision,
                updatedAt: cloudUpdatedAt,
              }),
              leftTeam,
              id,
              expectedClientNoteId
            );
          return { success: true, outcome: "awaiting-cloud" };
        }

        // A create's submitted set may have been ignored by the server. Take
        // the returned copy only for bundles still equal to that submission,
        // and retire those sets even when another field was edited in flight.
        // POST never delivers a deliberate clear; it still owes a PATCH.
        if (cloudNote && !writeRejected) {
          const adopted = acknowledgedCreateTextUpdates(current, snapshot, cloudNote);
          noteUndo.preserveUnchangedUndo(this, id, Object.keys(adopted), () => {
            this._setNoteColumns(id, adopted);
          });
        }

        // A clear still follows as a revisioned PATCH. A team-to-Personal
        // move stays pending for its scope retraction, even with backup off.
        if (unchanged && settleIfUnchanged && !hasPendingNoteClear(snapshot) && !writeRejected) {
          this.db
            .prepare(
              `UPDATE notes
               SET sync_status = 'synced', cloud_id = ?, left_team = 0,
                   cloud_updated_at = ?, cloud_revision = ?,
                   owner_user_id = ?,
                   content_sync_operation = NULL, enhanced_content_sync_operation = NULL,
                   cloud_create_pending = NULL
               WHERE id = ? AND client_note_id = ?`
            )
            .run(
              cloudId,
              cloudUpdatedAt,
              hasNoteRevision(cloudRevision) ? cloudRevision : null,
              ownerUserId,
              id,
              expectedClientNoteId
            );
          return { success: true, outcome: "synced", ...(cloudNote && { note: this.getNote(id) }) };
        }

        this.db
          .prepare(
            `UPDATE notes
             SET cloud_id = ?,
                 cloud_updated_at = ?, cloud_revision = ?,
                 owner_user_id = ?,
                 sync_status = 'pending', cloud_create_rejected = ?, cloud_create_pending = NULL,
                 left_team = CASE WHEN ? = 1 THEN 1 ELSE left_team END
             WHERE id = ? AND client_note_id = ?`
          )
          .run(
            cloudId,
            cloudUpdatedAt,
            hasNoteRevision(cloudRevision) ? cloudRevision : null,
            ownerUserId,
            writeRejected ? 1 : 0,
            leftTeam,
            id,
            expectedClientNoteId
          );
        return { success: true, outcome: "pending", ...(cloudNote && { note: this.getNote(id) }) };
      })();
    } catch (error) {
      debugLogger.error("Error acknowledging note create", { error: error.message }, "database");
      throw error;
    }
  }

  // A PATCH response belongs to both the local client identity and the cloud
  // identity that issued it. Purge/revocation forks in place, so numeric id
  // alone is never sufficient. An exact pushed snapshot settles; newer work
  // on the same identity remains pending while advancing its server base.
  // cloudNote is the response row: a text field this device pushed without
  // an edit and has not touched since takes the server's copy (see
  // cloudTextUpdates), so the ack never pins a revision over text the server
  // no longer holds (a clear this device missed, or a blank it left out).
  markNoteSyncedIfUnchanged(
    id,
    snapshot,
    expectedCloudId,
    cloudUpdatedAt = null,
    ownerUserId = null,
    cloudRevision = null,
    cloudNote = null
  ) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(id)) {
        return { success: true, outcome: "identity-changed", changes: 0 };
      }
      if (!snapshot?.client_note_id || !expectedCloudId) {
        return { success: false, outcome: "identity-changed", changes: 0 };
      }

      return this.db.transaction(() => {
        const current = this.db.prepare("SELECT * FROM notes WHERE id = ?").get(id);
        if (
          !current ||
          current.client_note_id !== snapshot.client_note_id ||
          current.cloud_id !== expectedCloudId
        ) {
          return { success: true, outcome: "identity-changed", changes: 0 };
        }

        if (current.cloud_create_rejected || current.cloud_create_pending)
          return { success: true, outcome: "pending", changes: 0 };
        // An older response arriving out of order must not regress the
        // revision. A response without one (an API that predates revisions)
        // drops it, so pushes fall back to the timestamp base.
        const incomingRevision = hasNoteRevision(cloudRevision) ? cloudRevision : null;
        if (
          incomingRevision !== null &&
          hasNoteRevision(current.cloud_revision) &&
          incomingRevision < current.cloud_revision
        ) {
          return { success: true, outcome: "pending", changes: 0 };
        }
        const unchanged = rowMatchesSnapshot(current, snapshot, NOTE_PATCH_ACK_FIELDS);
        const adopted = cloudNote
          ? cloudTextUpdates(
              current,
              cloudNote,
              NOTE_TEXT_FIELDS.filter(
                (field) =>
                  !current[`${field}_sync_operation`] &&
                  noteTextMatchesSnapshot(current, snapshot, field)
              )
            )
          : {};
        this._setNoteColumns(id, adopted);
        const note = Object.keys(adopted).length > 0 ? this.getNote(id) : undefined;
        const nextCloudUpdatedAt = (() => {
          if (!cloudUpdatedAt) return current.cloud_updated_at;
          if (!current.cloud_updated_at) return cloudUpdatedAt;
          const incomingMs = Date.parse(cloudUpdatedAt);
          const currentMs = Date.parse(current.cloud_updated_at);
          if (Number.isFinite(incomingMs) && Number.isFinite(currentMs)) {
            return incomingMs > currentMs ? cloudUpdatedAt : current.cloud_updated_at;
          }
          return cloudUpdatedAt > current.cloud_updated_at
            ? cloudUpdatedAt
            : current.cloud_updated_at;
        })();

        if (unchanged) {
          const result = this.db
            .prepare(
              `UPDATE notes
               SET sync_status = 'synced', left_team = 0, content_sync_operation = NULL, enhanced_content_sync_operation = NULL,
                   cloud_updated_at = ?, cloud_revision = ?,
                   owner_user_id = COALESCE(?, owner_user_id)
               WHERE id = ? AND client_note_id = ? AND cloud_id = ?`
            )
            .run(
              nextCloudUpdatedAt,
              incomingRevision,
              ownerUserId,
              id,
              snapshot.client_note_id,
              expectedCloudId
            );
          return { success: true, outcome: "synced", changes: result.changes, note };
        }

        // The delivered PATCH still advances this identity's base; otherwise
        // the next push would 409 against this device's own write. An older
        // response arriving out of order must not regress a newer base. Never
        // run this update for an identity/cloud mismatch.
        this.db
          .prepare(
            `UPDATE notes
             SET cloud_updated_at = ?, cloud_revision = ?,
                 owner_user_id = COALESCE(?, owner_user_id)
             WHERE id = ? AND client_note_id = ? AND cloud_id = ?`
          )
          .run(
            nextCloudUpdatedAt,
            incomingRevision,
            ownerUserId,
            id,
            snapshot.client_note_id,
            expectedCloudId
          );
        return { success: true, outcome: "pending", changes: 0, note };
      })();
    } catch (error) {
      debugLogger.error(
        "Error marking note synced if unchanged",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // Copies the cloud owner onto a local row without touching updated_at or
  // sync_status: an unchanged note skips the last-write-wins upsert but must
  // still gain its owner (the owner_user_id backfill relies on this).
  setNoteOwnerFromCloud(id, ownerUserId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(id)) return { success: false };
      this.db.prepare("UPDATE notes SET owner_user_id = ? WHERE id = ?").run(ownerUserId, id);
      return { success: true };
    } catch (error) {
      debugLogger.error(
        "Error setting note owner from cloud",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // Live cloud-backed team notes whose owner is still unknown — the UI fails
  // closed on them, so a snapshot backfill runs while any remain.
  countTeamNotesMissingOwner() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("n");
      const row = this.db
        .prepare(
          `SELECT COUNT(*) AS count FROM notes n
             JOIN spaces s ON s.id = n.space_id
            WHERE s.kind = 'team' AND n.deleted_at IS NULL
              AND n.cloud_id IS NOT NULL AND n.owner_user_id IS NULL
              AND ${accountScope.sql}`
        )
        .get(...accountScope.params);
      return row?.count ?? 0;
    } catch (error) {
      debugLogger.error(
        "Error counting team notes missing owner",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // Clean cloud-backed notes synced before the server kept revisions. Their
  // server rows may never re-enter a delta pull, so a snapshot pull applies
  // them while any remain. spaceKind narrows like getPendingNotes.
  countNotesMissingRevision(spaceKind = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("n");
      const row = this.db
        .prepare(
          `SELECT COUNT(*) AS count FROM notes n
             JOIN spaces s ON s.id = n.space_id
            WHERE n.sync_status = 'synced' AND n.deleted_at IS NULL
              AND n.cloud_id IS NOT NULL AND n.cloud_revision IS NULL
              AND (? IS NULL OR s.kind = ?)
              AND ${accountScope.sql}`
        )
        .get(spaceKind, spaceKind, ...accountScope.params);
      return row?.count ?? 0;
    } catch (error) {
      debugLogger.error(
        "Error counting notes missing a revision",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // Records the server revision the user knowingly overwrites ("Keep editing"
  // on the conflict banner, keepLocal) or that a pull found equal to this
  // device's base. Deliberately leaves updated_at and sync_status alone — a
  // local edit stays pending and pushes with the advanced base. Keep merges
  // per field against the conflicting cloud copy: a field this device edited
  // (it has a sync operation) keeps the local value, and one it never edited
  // takes the cloud's, set or clear, so Keep never pushes a stale copy over
  // another device's edit.
  setNoteCloudBase(id, cloudUpdatedAt, cloudRevision = null, options = {}) {
    const { keepLocal = false, cloudNote = null } = options;
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db.transaction(() => {
        const current = this.getNote(id);
        if (!current) return { success: false };
        const revision = hasNoteRevision(cloudRevision) ? cloudRevision : null;
        if (
          hasNoteRevision(current.cloud_revision) &&
          revision !== null &&
          revision < current.cloud_revision
        )
          return { success: false };
        if (keepLocal && cloudNote) {
          // A deferred create may already have delivered (or had the server
          // discard) these sets. Keep preserves edits made after that POST,
          // not the unchanged submitted text a newer cloud copy cleared.
          const acknowledged = current.cloud_create_pending
            ? acknowledgedCreateTextUpdates(
                current,
                JSON.parse(current.cloud_create_pending).snapshot,
                cloudNote
              )
            : {};
          const merged = { ...current, ...acknowledged };
          const adopted = {
            ...acknowledged,
            ...cloudTextUpdates(
              merged,
              cloudNote,
              NOTE_TEXT_FIELDS.filter((field) => !merged[`${field}_sync_operation`])
            ),
          };
          noteUndo.preserveUnchangedUndo(this, id, Object.keys(adopted), () => {
            this._setNoteColumns(id, adopted);
          });
        }
        this.db
          .prepare(
            `UPDATE notes SET cloud_updated_at = ?, cloud_revision = ?,
               cloud_create_rejected = CASE WHEN ? THEN 0 ELSE cloud_create_rejected END,
               cloud_create_pending = CASE WHEN ? THEN NULL ELSE cloud_create_pending END
             WHERE id = ?`
          )
          .run(cloudUpdatedAt, revision, keepLocal ? 1 : 0, keepLocal ? 1 : 0, id);
        return { success: true, note: this.getNote(id) };
      })();
    } catch (error) {
      debugLogger.error("Error setting note cloud base", { error: error.message }, "database");
      throw error;
    }
  }

  // Runs once, when the sync operation columns are added. Unsynced text from
  // a build without them is the user's own edit, so it is marked as a set:
  // Keep then keeps it rather than taking the cloud's copy, and a
  // revision-aware push sends it. A blank stays unmarked, since a released
  // build's blank is no deliberate clear. A database that predates sync
  // status has never synced any of its notes.
  _markUnsyncedNoteTextAsSet() {
    const hasSyncStatus = this.db
      .prepare("PRAGMA table_info(notes)")
      .all()
      .some((column) => column.name === "sync_status");
    const rows = this.db
      .prepare(
        `SELECT id, content, enhanced_content FROM notes
         ${hasSyncStatus ? "WHERE sync_status IS NOT 'synced'" : ""}`
      )
      .all();
    const mark = this.db.prepare(
      `UPDATE notes SET content_sync_operation = ?, enhanced_content_sync_operation = ?
       WHERE id = ?`
    );
    this.db.transaction(() => {
      for (const row of rows) {
        const content = typedTextOperation(row.content);
        const summary = typedTextOperation(row.enhanced_content);
        if (content || summary) mark.run(content, summary, row.id);
      }
    })();
  }

  // Writes sync-owned columns (from cloudTextUpdates) without touching
  // updated_at or sync_status. Keys come from that fixed set, never input.
  _setNoteColumns(id, columns) {
    const keys = Object.keys(columns);
    if (keys.length === 0) return;
    this.db
      .prepare(`UPDATE notes SET ${keys.map((key) => `${key} = ?`).join(", ")} WHERE id = ?`)
      .run(...Object.values(columns), id);
  }

  markNoteSyncError(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(id)) return { success: false };
      this.db.prepare("UPDATE notes SET sync_status = 'error' WHERE id = ?").run(id);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error marking note sync error", { error: error.message }, "database");
      throw error;
    }
  }

  // A denied optimistic delete leaves the server row untouched. Revive the
  // local tombstone in place so its numeric id, chats and speaker rows survive;
  // the deliberately old timestamp lets the mandatory snapshot pull replace
  // it with the authoritative cloud row.
  restoreNoteAfterDeniedDelete(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("notes");
      const result = this.db
        .prepare(
          `UPDATE notes
           SET deleted_at = NULL, sync_status = 'synced',
               updated_at = '1970-01-01 00:00:00'
           WHERE id = ? AND deleted_at IS NOT NULL AND ${accountScope.sql}`
        )
        .run(id, ...accountScope.params);
      return { success: result.changes > 0, id };
    } catch (error) {
      debugLogger.error(
        "Error restoring note after denied delete",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // Confirmed cloud deletes and access revocation retire note chats; denied
  // deletes use the restore method above instead.
  hardDeleteNote(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(id)) return { success: false, id, error: "Note not found" };
      const result = this.db.transaction(() => {
        this._retireConversationsWhere("note_id = ?", [id], {
          scrubSyncedMessages: true,
        });
        this._deleteSpeakerRowsForNotes("SELECT id FROM notes WHERE id = ?", id);
        return this.db.prepare("DELETE FROM notes WHERE id = ?").run(id);
      })();
      return { success: result.changes > 0, id };
    } catch (error) {
      debugLogger.error("Error hard deleting note", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingFolders(spaceKind = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("f");
      if (spaceKind != null) {
        // 'team' also returns cloud-backed rows that just LEFT a team: their
        // scope retraction must push even when cloud backup is off (D6).
        const leftTeam =
          spaceKind === "team" ? " OR (f.left_team = 1 AND f.cloud_id IS NOT NULL)" : "";
        return this.db
          .prepare(
            `SELECT f.* FROM folders f JOIN spaces s ON s.id = f.space_id
             WHERE f.sync_status = 'pending' AND f.deleted_at IS NULL
               AND ${accountScope.sql} AND (s.kind = ?${leftTeam})
             ORDER BY f.space_id, f.name`
          )
          .all(...accountScope.params, spaceKind);
      }
      return this.db
        .prepare(
          `SELECT f.* FROM folders f
           WHERE f.sync_status = 'pending' AND f.deleted_at IS NULL
             AND ${accountScope.sql}
           ORDER BY f.space_id, f.name`
        )
        .all(...accountScope.params);
    } catch (error) {
      debugLogger.error("Error getting pending folders", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingFolderDeletes() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("f");
      return this.db
        .prepare(
          `SELECT * FROM folders f
           WHERE deleted_at IS NOT NULL AND cloud_id IS NOT NULL
             AND ${accountScope.sql}
             AND (sync_status = 'pending' OR EXISTS (
               SELECT 1 FROM optimistic_folder_delete_rows r
               WHERE r.folder_id = f.id AND r.entity_type = 'folder'
             ))`
        )
        .all(...accountScope.params);
    } catch (error) {
      debugLogger.error(
        "Error getting pending folder deletes",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // A folder DELETE permission denial means the server changed nothing.
  // Restore only rows hidden by that exact optimistic operation; independent
  // note/conversation tombstones were never journaled and remain deleted.
  restoreFolderAfterDeniedDelete(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this._getFolderInAccountScope(id)) {
        return { success: false, id, error: "Folder not found" };
      }
      return this.db.transaction(() => {
        const journalRows = this.db
          .prepare(
            `SELECT * FROM optimistic_folder_delete_rows
             WHERE folder_id = ?
             ORDER BY CASE entity_type
               WHEN 'folder' THEN 0 WHEN 'note' THEN 1 ELSE 2 END, entity_id`
          )
          .all(id);
        const folderState = journalRows.find((row) => row.entity_type === "folder");
        if (!folderState) {
          return { success: false, id, error: "Folder delete rollback not found" };
        }

        const folder = this.db.prepare("SELECT * FROM folders WHERE id = ?").get(id);
        if (!folder) {
          return { success: false, id, error: "Folder row is missing" };
        }
        const collision = this.db
          .prepare(
            "SELECT id FROM folders WHERE name = ? AND space_id = ? AND deleted_at IS NULL AND id != ?"
          )
          .get(folder.name, folder.space_id, id);
        if (collision) {
          return {
            success: false,
            id,
            reason: "name-taken",
            error: "Folder name is no longer available",
          };
        }

        const noteStates = journalRows.filter((row) => row.entity_type === "note");
        const conversationStates = journalRows.filter((row) => row.entity_type === "conversation");
        const noteExists = this.db.prepare("SELECT 1 FROM notes WHERE id = ?");
        const conversationExists = this.db.prepare(
          "SELECT 1 FROM agent_conversations WHERE id = ?"
        );
        if (noteStates.some((row) => !noteExists.get(row.entity_id))) {
          return { success: false, id, error: "A folder note row is missing" };
        }
        if (conversationStates.some((row) => !conversationExists.get(row.entity_id))) {
          return { success: false, id, error: "A folder conversation row is missing" };
        }

        this.db
          .prepare(
            `UPDATE folders
             SET deleted_at = ?, sync_status = ?, updated_at = ?
             WHERE id = ?`
          )
          .run(
            folderState.original_deleted_at,
            folderState.original_sync_status,
            folderState.original_updated_at,
            id
          );
        const restoreNote = this.db.prepare(
          `UPDATE notes
           SET deleted_at = ?, sync_status = ?, updated_at = ?
           WHERE id = ?`
        );
        for (const state of noteStates) {
          restoreNote.run(
            state.original_deleted_at,
            state.original_sync_status,
            state.original_updated_at,
            state.entity_id
          );
        }
        const restoreConversation = this.db.prepare(
          `UPDATE agent_conversations
           SET deleted_at = ?, sync_status = ?, updated_at = ?
           WHERE id = ?`
        );
        for (const state of conversationStates) {
          restoreConversation.run(
            state.original_deleted_at,
            state.original_sync_status,
            state.original_updated_at,
            state.entity_id
          );
        }

        this.db.prepare("DELETE FROM optimistic_folder_delete_rows WHERE folder_id = ?").run(id);
        const getNote = this.db.prepare("SELECT * FROM notes WHERE id = ?");
        return {
          success: true,
          id,
          folder: this.db.prepare("SELECT * FROM folders WHERE id = ?").get(id),
          notes: noteStates.map((state) => getNote.get(state.entity_id)),
          conversationIds: conversationStates.map((state) => state.entity_id),
        };
      })();
    } catch (error) {
      debugLogger.error(
        "Error restoring folder after denied delete",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  hardDeleteFolder(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const folder = this._getFolderInAccountScope(id);
      if (!folder) return { success: false, id, error: "Folder not found" };
      const childNotes = "SELECT id FROM notes WHERE folder_id = ?";
      const heldNotes =
        "SELECT entity_id FROM optimistic_folder_delete_rows WHERE folder_id = ? AND entity_type = 'note'";
      const heldConversations =
        "SELECT entity_id FROM optimistic_folder_delete_rows WHERE folder_id = ? AND entity_type = 'conversation'";
      const accountScope = this._accountScopeCondition("notes");
      const noteIds = this.db
        .prepare(`${childNotes} AND ${accountScope.sql}`)
        .all(id, ...accountScope.params)
        .map((row) => row.id);
      let relocatedNotes = [];
      const result = this.db.transaction(() => {
        relocatedNotes = this._releaseOutOfScopeChildNotes(id);
        // Note chats normally have note_id only. Retire them while the child
        // rows still identify which chats belong to this folder cleanup, then
        // handle independently folder-scoped conversations.
        this._retireConversationsWhere(`note_id IN (${childNotes})`, [id], {
          scrubSyncedMessages: true,
        });
        // The journal is the authoritative ownership record for the
        // optimistic operation. Use it as well as current parent columns so a
        // late stale write cannot strand a held row by changing its scope.
        this.db
          .prepare(
            `DELETE FROM agent_messages
             WHERE conversation_id IN (${heldConversations})`
          )
          .run(id);
        this.db
          .prepare(
            `DELETE FROM agent_conversations
             WHERE cloud_id IS NULL AND id IN (${heldConversations})`
          )
          .run(id);
        this.db
          .prepare(
            `UPDATE agent_conversations
             SET deleted_at = COALESCE(deleted_at, datetime('now')),
                 sync_status = 'pending', updated_at = datetime('now')
             WHERE cloud_id IS NOT NULL AND id IN (${heldConversations})`
          )
          .run(id);
        this._deleteSpeakerRowsForNotes(childNotes, id);
        this._deleteSpeakerRowsForNotes(heldNotes, id);
        this.db.prepare(`DELETE FROM notes WHERE id IN (${heldNotes})`).run(id);
        this.db.prepare(`DELETE FROM notes WHERE id IN (${childNotes})`).run(id);
        this._retireConversationsWhere("folder_id = ?", [id], {
          scrubSyncedMessages: true,
        });
        // Held cloud chats now become ordinary pending cloud deletes. Rows
        // tombstoned before the folder action were never journaled and keep
        // their existing pending state.
        const deleted = this.db.prepare("DELETE FROM folders WHERE id = ?").run(id);
        this.db.prepare("DELETE FROM optimistic_folder_delete_rows WHERE folder_id = ?").run(id);
        return deleted;
      })();
      return {
        success: result.changes > 0,
        id,
        noteIds,
        relocatedNotes,
        name: folder?.name ?? null,
      };
    } catch (error) {
      debugLogger.error("Error hard deleting folder", { error: error.message }, "database");
      throw error;
    }
  }

  // The folder's server row moved into a team this user can't access
  // (access_removed stub or a team_access_revoked push rejection). Clean
  // server-owned child notes are no longer ours to keep — the server retains
  // them; dirty or never-synced children are the only surviving content
  // (plan §7.2): they relocate to the private space with forked identities so
  // the next push re-creates them as personal rows. The folder row itself
  // survives in the private space only when it carries unpushed changes
  // (preserveFolder), renamed "name (2)" on a collision; otherwise it is
  // deleted.
  relocateRevokedFolder(id, privateSpaceId, preserveFolder = false) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this._getFolderInAccountScope(id)) {
        return { success: false, error: "Folder not found" };
      }
      // If access revocation overtakes an optimistic delete, first recover the
      // held rows so the normal dirty-note preservation rules can classify
      // them from their real pre-delete state.
      const held = this.db
        .prepare(
          "SELECT 1 FROM optimistic_folder_delete_rows WHERE folder_id = ? AND entity_type = 'folder'"
        )
        .get(id);
      if (held) {
        const rollback = this.restoreFolderAfterDeniedDelete(id);
        if (!rollback.success) return rollback;
      }

      const folder = this.db.prepare("SELECT * FROM folders WHERE id = ?").get(id);
      if (!folder) return { success: false, error: "Folder not found" };
      const serverOwnedChildren =
        "SELECT id FROM notes WHERE folder_id = ? AND (deleted_at IS NOT NULL OR (sync_status = 'synced' AND cloud_id IS NOT NULL))";
      const result = this.db.transaction(() => {
        const preservedIds = this.db
          .prepare(
            "SELECT id FROM notes WHERE folder_id = ? AND deleted_at IS NULL AND (sync_status != 'synced' OR cloud_id IS NULL)"
          )
          .all(id)
          .map((row) => row.id);
        const deletedNoteIds = this.db
          .prepare(serverOwnedChildren)
          .all(id)
          .map((row) => row.id);
        // Note chats have no folder_id, so retire them before deleting the
        // server-owned notes that prove they belonged to this revoked folder.
        this._retireConversationsWhere(`note_id IN (${serverOwnedChildren})`, [id], {
          scrubSyncedMessages: true,
        });
        this._deleteSpeakerRowsForNotes(serverOwnedChildren, id);
        this.db.prepare(`DELETE FROM notes WHERE id IN (${serverOwnedChildren})`).run(id);
        const relocateNote = this.db.prepare(
          "UPDATE notes SET space_id = ?, folder_id = ?, client_note_id = ?, cloud_id = NULL, cloud_updated_at = NULL, cloud_revision = NULL, cloud_create_rejected = 0, cloud_create_pending = NULL, owner_user_id = NULL, updated_by_user_id = NULL, sync_status = 'pending', left_team = 0, is_shared = 0, share_token = NULL, updated_at = datetime('now') WHERE id = ?"
        );
        const detachNoteConversation = this.db.prepare(
          "UPDATE agent_conversations SET space_id = NULL, folder_id = NULL WHERE note_id = ?"
        );
        for (const noteId of preservedIds) {
          relocateNote.run(privateSpaceId, preserveFolder ? id : null, randomUUID(), noteId);
          // Note-scoped chats follow a preserved dirty note, not the revoked
          // team container. Folder-only chats are handled separately below.
          detachNoteConversation.run(noteId);
        }
        let preservedFolder = null;
        if (preserveFolder) {
          let name = folder.name;
          const taken = this.db.prepare(
            "SELECT 1 FROM folders WHERE name = ? AND space_id = ? AND deleted_at IS NULL AND id != ?"
          );
          for (let n = 2; taken.get(name, privateSpaceId, id); n++) {
            name = `${folder.name} (${n})`;
          }
          this.db
            .prepare(
              "UPDATE folders SET space_id = ?, name = ?, client_folder_id = ?, cloud_id = NULL, sync_status = 'pending', left_team = 0, updated_at = datetime('now') WHERE id = ?"
            )
            .run(privateSpaceId, name, randomUUID(), id);
          preservedFolder = this.db.prepare("SELECT * FROM folders WHERE id = ?").get(id);
          // Folder-scoped chats follow the preserved folder into the private
          // space so their space ref doesn't dangle on the revoked space.
          this.db
            .prepare("UPDATE agent_conversations SET space_id = ? WHERE folder_id = ?")
            .run(privateSpaceId, id);
        } else {
          this._retireConversationsWhere("folder_id = ?", [id], {
            scrubSyncedMessages: true,
          });
          this.db.prepare("DELETE FROM folders WHERE id = ?").run(id);
        }
        const getNote = this.db.prepare("SELECT * FROM notes WHERE id = ?");
        return {
          folder: preservedFolder,
          relocatedNotes: preservedIds.map((noteId) => getNote.get(noteId)),
          deletedNoteIds,
        };
      })();
      return { success: true, folderName: folder.name, ...result };
    } catch (error) {
      debugLogger.error("Error relocating revoked folder", { error: error.message }, "database");
      throw error;
    }
  }

  getFolderByClientId(clientFolderId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("folders");
      return (
        this.db
          .prepare(`SELECT * FROM folders WHERE client_folder_id = ? AND ${accountScope.sql}`)
          .get(clientFolderId, ...accountScope.params) || null
      );
    } catch (error) {
      debugLogger.error("Error getting folder by client id", { error: error.message }, "database");
      throw error;
    }
  }

  upsertFolderFromCloud(cloudFolder, localSpaceId = null) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const spaceId = localSpaceId ?? this.getPrivateSpaceId();
      const accountId = this._accountIdForSpace(spaceId);
      const updatedAt = cloudFolder.updated_at || cloudFolder.created_at;
      const stmt = this.db.prepare(`
        INSERT INTO folders (client_folder_id, cloud_id, name, is_default, sort_order, space_id, account_id, sync_status, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, 'synced', ?, ?)
        ON CONFLICT(client_folder_id) DO UPDATE SET
          cloud_id = excluded.cloud_id,
          name = excluded.name,
          sort_order = excluded.sort_order,
          space_id = excluded.space_id,
          account_id = excluded.account_id,
          sync_status = 'synced',
          left_team = 0,
          updated_at = excluded.updated_at
      `);
      try {
        stmt.run(
          cloudFolder.client_folder_id,
          cloudFolder.id,
          cloudFolder.name,
          cloudFolder.is_default ? 1 : 0,
          cloudFolder.sort_order || 0,
          spaceId,
          accountId,
          cloudFolder.created_at,
          updatedAt
        );
      } catch (err) {
        // A live same-named folder already exists in this space (partial unique
        // index idx_folders_space_name is a different conflict target than the
        // client_folder_id upsert). Converge on the existing folder by adopting
        // the cloud row's identity instead of wedging the pull.
        // Match the error code, not the message text (which SQLite could
        // reformat); the column check keeps client_folder_id collisions on
        // the rethrow path.
        if (err.code !== "SQLITE_CONSTRAINT_UNIQUE" || !err.message.includes("folders.space_id")) {
          throw err;
        }
        const existing = this.db
          .prepare(
            "SELECT id FROM folders WHERE space_id = ? AND name = ? AND account_id IS ? AND deleted_at IS NULL"
          )
          .get(spaceId, cloudFolder.name, accountId);
        if (!existing) throw err;
        this.db.transaction(() => {
          const holder = this.db
            .prepare("SELECT id FROM folders WHERE client_folder_id = ? AND id != ?")
            .get(cloudFolder.client_folder_id, existing.id);
          // A different local row already tracked this cloud folder (rename
          // collision via the DO UPDATE branch) — fork it so the winner can
          // take the cloud identity without violating the client id index.
          if (holder) this.forkFolderIdentity(holder.id);
          this.db
            .prepare(
              "UPDATE folders SET client_folder_id = ?, cloud_id = ?, sort_order = ?, sync_status = 'synced', left_team = 0, updated_at = ? WHERE id = ?"
            )
            .run(
              cloudFolder.client_folder_id,
              cloudFolder.id,
              cloudFolder.sort_order || 0,
              updatedAt,
              existing.id
            );
        })();
      }
      return this.db
        .prepare("SELECT * FROM folders WHERE client_folder_id = ?")
        .get(cloudFolder.client_folder_id);
    } catch (error) {
      debugLogger.error("Error upserting folder from cloud", { error: error.message }, "database");
      throw error;
    }
  }

  markFolderSynced(id, cloudId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this._getFolderInAccountScope(id)) return { success: false };
      this.db
        .prepare(
          "UPDATE folders SET sync_status = 'synced', cloud_id = ?, left_team = 0 WHERE id = ?"
        )
        .run(cloudId, id);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error marking folder synced", { error: error.message }, "database");
      throw error;
    }
  }

  // A folder create or pull-side name adoption may return a canonical client
  // identity different from the local one. Adopt it only while both identities
  // captured before the request still occupy this numeric row. A newer rename
  // or move adopts the cloud identity but remains pending for its follow-up
  // PATCH; an in-place revocation fork is never touched.
  acknowledgeFolderCreate(
    id,
    snapshot,
    expectedCloudId,
    responseClientFolderId,
    cloudId,
    cloudUpdatedAt = null
  ) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this._getFolderInAccountScope(id)) {
        return { success: true, outcome: "identity-changed", changes: 0 };
      }
      if (
        !snapshot?.client_folder_id ||
        (expectedCloudId !== null && typeof expectedCloudId !== "string") ||
        !responseClientFolderId ||
        !cloudId
      ) {
        return { success: false, outcome: "unresolved", changes: 0 };
      }

      return this.db.transaction(() => {
        const current = this.db.prepare("SELECT * FROM folders WHERE id = ?").get(id);
        if (
          !current ||
          current.client_folder_id !== snapshot.client_folder_id ||
          current.cloud_id !== expectedCloudId
        ) {
          return { success: true, outcome: "identity-changed", changes: 0 };
        }
        if (expectedCloudId !== null && expectedCloudId !== cloudId) {
          return { success: true, outcome: "unresolved", changes: 0 };
        }
        const responseIdentityHolder = this.db
          .prepare("SELECT id FROM folders WHERE client_folder_id = ? AND id != ?")
          .get(responseClientFolderId, id);
        if (responseIdentityHolder) {
          return { success: true, outcome: "unresolved", changes: 0 };
        }

        const unchanged = rowMatchesSnapshot(current, snapshot, FOLDER_ACK_FIELDS);
        const leftTeam = this._leftTeamDuringPush(snapshot.space_id, current.space_id);

        if (unchanged) {
          const result = this.db
            .prepare(
              `UPDATE folders
               SET client_folder_id = ?, cloud_id = ?, sync_status = 'synced',
                   left_team = 0, updated_at = COALESCE(?, updated_at)
               WHERE id = ? AND client_folder_id = ? AND cloud_id IS ?`
            )
            .run(
              responseClientFolderId,
              cloudId,
              cloudUpdatedAt,
              id,
              snapshot.client_folder_id,
              expectedCloudId
            );
          return { success: true, outcome: "synced", changes: result.changes };
        }

        const result = this.db
          .prepare(
            `UPDATE folders
             SET client_folder_id = ?, cloud_id = ?, sync_status = 'pending',
                 left_team = CASE WHEN ? = 1 THEN 1 ELSE left_team END
             WHERE id = ? AND client_folder_id = ? AND cloud_id IS ?`
          )
          .run(
            responseClientFolderId,
            cloudId,
            leftTeam,
            id,
            snapshot.client_folder_id,
            expectedCloudId
          );
        return { success: true, outcome: "pending", changes: result.changes };
      })();
    } catch (error) {
      debugLogger.error("Error acknowledging folder create", { error: error.message }, "database");
      throw error;
    }
  }

  // Folder PATCH twin of the guarded note acknowledgement. Bind the response
  // to both client and cloud identity and compare every pushed field so a
  // same-second rename or an in-place revocation fork cannot be settled.
  markFolderSyncedIfUnchanged(id, snapshot, expectedCloudId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this._getFolderInAccountScope(id)) {
        return { success: true, outcome: "identity-changed", changes: 0 };
      }
      if (!snapshot?.client_folder_id || !expectedCloudId) {
        return { success: false, outcome: "identity-changed", changes: 0 };
      }
      return this.db.transaction(() => {
        const current = this.db.prepare("SELECT * FROM folders WHERE id = ?").get(id);
        if (
          !current ||
          current.client_folder_id !== snapshot.client_folder_id ||
          current.cloud_id !== expectedCloudId
        ) {
          return { success: true, outcome: "identity-changed", changes: 0 };
        }
        const unchanged = rowMatchesSnapshot(current, snapshot, FOLDER_ACK_FIELDS);
        if (!unchanged) {
          return { success: true, outcome: "pending", changes: 0 };
        }
        const result = this.db
          .prepare(
            `UPDATE folders
             SET sync_status = 'synced', left_team = 0
             WHERE id = ? AND client_folder_id = ? AND cloud_id = ?`
          )
          .run(id, snapshot.client_folder_id, expectedCloudId);
        return { success: true, outcome: "synced", changes: result.changes };
      })();
    } catch (error) {
      debugLogger.error(
        "Error marking folder synced if unchanged",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  // A folder whose server row moved into a scope this user can no longer
  // write gets a fresh identity, so the next push creates it as a new
  // personal folder instead of PATCHing the inaccessible row forever.
  forkFolderIdentity(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this._getFolderInAccountScope(id)) return { success: false };
      const result = this.db
        .prepare(
          "UPDATE folders SET client_folder_id = ?, cloud_id = NULL, sync_status = 'pending', left_team = 0 WHERE id = ?"
        )
        .run(randomUUID(), id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error("Error forking folder identity", { error: error.message }, "database");
      throw error;
    }
  }

  getFolderIdMap() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("folders");
      return this.db
        .prepare(`SELECT * FROM folders WHERE deleted_at IS NULL AND ${accountScope.sql}`)
        .all(...accountScope.params);
    } catch (error) {
      debugLogger.error("Error getting folder id map", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingConversations() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      // The cloud conversation contract has no space/folder scope yet.
      // Keep container chats local so another device cannot pull them as
      // global chats. Cloud-backed tombstones still use the delete queue.
      return this.db
        .prepare(
          "SELECT * FROM agent_conversations WHERE sync_status = 'pending' AND deleted_at IS NULL AND space_id IS NULL AND folder_id IS NULL"
        )
        .all();
    } catch (error) {
      debugLogger.error(
        "Error getting pending conversations",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getPendingConversationDeletes() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          `SELECT * FROM agent_conversations c
           WHERE deleted_at IS NOT NULL AND cloud_id IS NOT NULL
             AND sync_status = 'pending'
             AND NOT EXISTS (
               SELECT 1 FROM optimistic_folder_delete_rows r
               WHERE r.entity_type = 'conversation' AND r.entity_id = c.id
             )`
        )
        .all();
    } catch (error) {
      debugLogger.error(
        "Error getting pending conversation deletes",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getConversationByClientId(clientId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return (
        this.db
          .prepare(
            `SELECT c.*,
               EXISTS (
                 SELECT 1 FROM optimistic_folder_delete_rows r
                 WHERE r.entity_type = 'conversation' AND r.entity_id = c.id
               ) AS folder_delete_pending
             FROM agent_conversations c
             WHERE c.client_conversation_id = ?`
          )
          .get(clientId) || null
      );
    } catch (error) {
      debugLogger.error(
        "Error getting conversation by client id",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  upsertConversationFromCloud(cloudConv, messages) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const transaction = this.db.transaction(() => {
        // A local tombstone represents an unacknowledged delete. A newer live
        // cloud revision must not cancel that intent or restore message bodies
        // while the delete retries. Match by cloud id as a fallback for legacy
        // rows without a client_conversation_id.
        let existing = null;
        if (cloudConv.client_conversation_id != null) {
          existing = this.db
            .prepare("SELECT * FROM agent_conversations WHERE client_conversation_id = ?")
            .get(cloudConv.client_conversation_id);
        }
        if (!existing && cloudConv.id != null) {
          existing = this.db
            .prepare("SELECT * FROM agent_conversations WHERE cloud_id = ?")
            .get(cloudConv.id);
        }
        if (existing?.deleted_at) return existing;

        const convStmt = this.db.prepare(`
          INSERT INTO agent_conversations (client_conversation_id, cloud_id, title, note_id, sync_status, created_at, updated_at)
          VALUES (?, ?, ?, ?, 'synced', ?, ?)
          ON CONFLICT(client_conversation_id) DO UPDATE SET
            cloud_id = excluded.cloud_id,
            title = excluded.title,
            note_id = excluded.note_id,
            sync_status = 'synced',
            updated_at = excluded.updated_at
        `);
        convStmt.run(
          cloudConv.client_conversation_id ?? null,
          cloudConv.id ?? null,
          cloudConv.title ?? "Untitled",
          cloudConv.note_id ?? null,
          cloudConv.created_at ?? new Date().toISOString(),
          cloudConv.updated_at ?? new Date().toISOString()
        );
        const conv = this.db
          .prepare("SELECT * FROM agent_conversations WHERE client_conversation_id = ?")
          .get(cloudConv.client_conversation_id);
        this.db.prepare("DELETE FROM agent_messages WHERE conversation_id = ?").run(conv.id);
        if (messages && messages.length > 0) {
          const msgStmt = this.db.prepare(
            "INSERT INTO agent_messages (conversation_id, role, content, metadata, created_at) VALUES (?, ?, ?, ?, ?)"
          );
          for (const msg of messages) {
            msgStmt.run(
              conv.id,
              msg.role ?? "user",
              msg.content ?? "",
              msg.metadata ? JSON.stringify(msg.metadata) : null,
              msg.created_at ?? new Date().toISOString()
            );
          }
        }
        return conv;
      });
      return transaction();
    } catch (error) {
      debugLogger.error(
        "Error upserting conversation from cloud",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  markConversationSynced(id, cloudId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db
        .prepare(
          `UPDATE agent_conversations
           SET cloud_id = COALESCE(cloud_id, ?),
               sync_status = CASE WHEN deleted_at IS NULL THEN 'synced' ELSE 'pending' END
           WHERE id = ?`
        )
        .run(cloudId, id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error("Error marking conversation synced", { error: error.message }, "database");
      throw error;
    }
  }

  acknowledgeConversationCreate(id, snapshot, cloudId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!snapshot || !cloudId) {
        return { success: false, outcome: "unresolved", cloud_id: null };
      }

      return this.db.transaction(() => {
        const current = this.db
          .prepare(
            `SELECT c.*, COUNT(m.id) AS message_count
             FROM agent_conversations c
             LEFT JOIN agent_messages m ON m.conversation_id = c.id
             WHERE c.id = ?
             GROUP BY c.id`
          )
          .get(id);
        const expectedClientId = snapshot.client_conversation_id ?? null;

        if (!current || (current.client_conversation_id ?? null) !== expectedClientId) {
          const identityStillExists = expectedClientId
            ? this.db
                .prepare("SELECT 1 FROM agent_conversations WHERE client_conversation_id = ?")
                .get(expectedClientId)
            : null;
          return {
            success: true,
            outcome: identityStillExists ? "unresolved" : "orphaned",
            cloud_id: null,
          };
        }

        if (current.cloud_id) {
          return { success: true, outcome: "already-linked", cloud_id: current.cloud_id };
        }

        if (current.deleted_at) {
          this.db
            .prepare(
              `UPDATE agent_conversations
               SET cloud_id = ?, sync_status = 'pending'
               WHERE id = ? AND cloud_id IS NULL`
            )
            .run(cloudId, id);
          return { success: true, outcome: "delete-pending", cloud_id: cloudId };
        }

        const unchanged =
          current.title === snapshot.title &&
          current.updated_at === snapshot.updated_at &&
          Number(current.message_count) === snapshot.message_count;
        if (!unchanged) {
          return { success: true, outcome: "changed", cloud_id: null };
        }

        this.db
          .prepare(
            `UPDATE agent_conversations
             SET cloud_id = ?, sync_status = 'synced'
             WHERE id = ? AND cloud_id IS NULL`
          )
          .run(cloudId, id);
        return { success: true, outcome: "synced", cloud_id: cloudId };
      })();
    } catch (error) {
      debugLogger.error(
        "Error acknowledging conversation create",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  hardDeleteConversation(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db.prepare("DELETE FROM agent_messages WHERE conversation_id = ?").run(id);
      const result = this.db.prepare("DELETE FROM agent_conversations WHERE id = ?").run(id);
      return { success: result.changes > 0 };
    } catch (error) {
      debugLogger.error("Error hard deleting conversation", { error: error.message }, "database");
      throw error;
    }
  }

  getPendingTranscriptions() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          "SELECT * FROM transcriptions WHERE sync_status = 'pending' AND deleted_at IS NULL"
        )
        .all();
    } catch (error) {
      debugLogger.error(
        "Error getting pending transcriptions",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  getPendingTranscriptionDeletes() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return this.db
        .prepare(
          "SELECT * FROM transcriptions WHERE deleted_at IS NOT NULL AND cloud_id IS NOT NULL AND sync_status = 'pending'"
        )
        .all();
    } catch (error) {
      debugLogger.error(
        "Error getting pending transcription deletes",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  hardDeleteTranscription(id) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const result = this.db.prepare("DELETE FROM transcriptions WHERE id = ?").run(id);
      return { success: result.changes > 0, id };
    } catch (error) {
      debugLogger.error("Error hard deleting transcription", { error: error.message }, "database");
      throw error;
    }
  }

  getTranscriptionByClientId(clientId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      return (
        this.db
          .prepare("SELECT * FROM transcriptions WHERE client_transcription_id = ?")
          .get(clientId) || null
      );
    } catch (error) {
      debugLogger.error(
        "Error getting transcription by client id",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  upsertTranscriptionFromCloud(cloudTranscription) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const text = cloudTranscription.text ?? "";
      const rawText = cloudTranscription.raw_text || null;
      const status = cloudTranscription.status || "completed";
      // timestamp is what the history list sorts and groups on, so it takes the
      // cloud row's own instant rather than defaulting to the moment of the
      // pull -- which would land a whole archive at "now", above everything
      // spoken since. Deliberately not in the conflict update: a row this
      // device recorded already carries the recording's start time, which is
      // more precise than the cloud's creation time for the same dictation.
      //
      // The separator is normalized because that sort is a TEXT comparison and
      // the API sends ISO 8601: "T" (0x54) outranks the space (0x20) every
      // locally written row uses, so a raw cloud value would sort above every
      // local dictation from the same UTC day whatever the hour.
      const cloudOccurredAt = toDbTimestamp(cloudTranscription.created_at);
      const stmt = this.db.prepare(`
        INSERT INTO transcriptions (client_transcription_id, cloud_id, text, raw_text, status, sync_status, created_at, timestamp)
        VALUES (?, ?, ?, ?, ?, 'synced', ?, COALESCE(?, CURRENT_TIMESTAMP))
        ON CONFLICT(client_transcription_id) DO UPDATE SET
          cloud_id = excluded.cloud_id,
          text = excluded.text,
          raw_text = excluded.raw_text,
          status = excluded.status,
          sync_status = 'synced'
      `);
      return this.db.transaction(() => {
        const existing = this.db
          .prepare(
            `SELECT id, text, raw_text, status FROM transcriptions
             WHERE client_transcription_id = ?`
          )
          .get(cloudTranscription.client_transcription_id);
        if (
          existing &&
          (existing.text !== text || existing.raw_text !== rawText || existing.status !== status)
        ) {
          this._invalidateAnalyticsHistoryFromTranscription(existing.id);
        }
        stmt.run(
          cloudTranscription.client_transcription_id,
          cloudTranscription.id,
          text,
          rawText,
          status,
          cloudTranscription.created_at,
          cloudOccurredAt
        );
        return this.db
          .prepare("SELECT * FROM transcriptions WHERE client_transcription_id = ?")
          .get(cloudTranscription.client_transcription_id);
      })();
    } catch (error) {
      debugLogger.error(
        "Error upserting transcription from cloud",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  markTranscriptionSynced(id, cloudId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      this.db
        .prepare("UPDATE transcriptions SET sync_status = 'synced', cloud_id = ? WHERE id = ?")
        .run(cloudId, id);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error marking transcription synced", { error: error.message }, "database");
      throw error;
    }
  }

  getNotesWithUnmappedSpeakers() {
    try {
      if (!this.db) throw new Error("Database not initialized");
      const accountScope = this._accountScopeCondition("notes");
      return this.db
        .prepare(
          `SELECT DISTINCT nse.note_id
          FROM note_speaker_embeddings nse
          JOIN notes ON notes.id = nse.note_id
          LEFT JOIN speaker_mappings sm ON nse.note_id = sm.note_id AND nse.speaker_id = sm.speaker_id
          WHERE sm.note_id IS NULL AND ${accountScope.sql}`
        )
        .all(...accountScope.params)
        .map((row) => row.note_id);
    } catch (error) {
      debugLogger.error(
        "Error getting notes with unmapped speakers",
        { error: error.message },
        "database"
      );
      throw error;
    }
  }

  removeSpeakerMapping(noteId, speakerId) {
    try {
      if (!this.db) throw new Error("Database not initialized");
      if (!this.getNote(noteId)) return { success: false };
      this.db
        .prepare("DELETE FROM speaker_mappings WHERE note_id = ? AND speaker_id = ?")
        .run(noteId, speakerId);
      return { success: true };
    } catch (error) {
      debugLogger.error("Error removing speaker mapping", { error: error.message }, "database");
      throw error;
    }
  }
}

module.exports = DatabaseManager;