ai-partner: conversation persistence (user.db)

[CraigBuckmaster]

Parent epic: #1446 (Amicus — AI Study Partner v1)
Phase: 2 · Size: S

Persistence layer for Amicus threads and messages in user.db. Local-only (never synced to cloud unless Supabase Phase 14 changes that). Schema migration, queries (read), mutations (write), and hook wrappers.

---

Files to modify

• app/src/db/userDatabase.ts — add migration creating Amicus tables
• app/src/db/userQueries.ts — add read functions
• app/src/db/userMutations.ts — add write functions
• app/src/types/index.ts (or appropriate types file) — add AmicusThread, AmicusMessage, AmicusCitation types

Files to create

• app/src/hooks/useAmicusThreads.ts — hook wrapping thread list queries
• app/src/hooks/useAmicusThread.ts — already referenced in ai-partner: streaming chat UI with citation pills #1455; provide read side here (sendMessage comes from chat.ts)

---

Migration (append to MIGRATIONS array in userDatabase.ts)

{
  version: /* next number after #1452's migration */,
  description: 'Amicus — threads and messages',
  sql: `
    CREATE TABLE IF NOT EXISTS amicus_threads (
      thread_id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      chapter_ref TEXT,                       -- "romans:9" format, nullable
      pinned INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      last_message_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE INDEX IF NOT EXISTS idx_amicus_threads_pinned_last
      ON amicus_threads(pinned DESC, last_message_at DESC);

    CREATE TABLE IF NOT EXISTS amicus_messages (
      message_id TEXT PRIMARY KEY,
      thread_id TEXT NOT NULL REFERENCES amicus_threads(thread_id) ON DELETE CASCADE,
      role TEXT NOT NULL CHECK (role IN ('user', 'assistant')),
      content TEXT NOT NULL,
      citations_json TEXT,                    -- JSON: [{chunk_id, source_type, display_label, scholar_id?}]
      follow_ups_json TEXT,                   -- JSON: [string, string, string]
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE INDEX IF NOT EXISTS idx_amicus_messages_thread
      ON amicus_messages(thread_id, created_at);

    CREATE TABLE IF NOT EXISTS amicus_usage (
      day TEXT PRIMARY KEY,                   -- 'YYYY-MM-DD'
      query_count INTEGER NOT NULL DEFAULT 0
    );
  `,
}

Follow the append-only pattern — don't modify existing migrations.

---

Types (app/src/types/)

export interface AmicusThread {
  thread_id: string;
  title: string;
  chapter_ref: string | null;      // e.g. "romans:9"
  pinned: boolean;
  created_at: string;
  last_message_at: string;
}

export interface AmicusCitation {
  chunk_id: string;
  source_type: string;
  display_label: string;
  scholar_id?: string;
}

export interface AmicusMessage {
  message_id: string;
  thread_id: string;
  role: 'user' | 'assistant';
  content: string;
  citations: AmicusCitation[];
  follow_ups: string[];
  created_at: string;
}

---

Read queries (userQueries.ts)

Follow the existing file conventions (getUserDb().getAllAsync etc.):

export async function listAmicusThreads(limit = 50, offset = 0): Promise<AmicusThread[]>;
export async function getAmicusThread(threadId: string): Promise<AmicusThread | null>;
export async function listAmicusMessages(threadId: string): Promise<AmicusMessage[]>;
export async function getAmicusUsageToday(): Promise<number>;
export async function getAmicusUsageThisMonth(): Promise<number>;

Ordering

• Threads: pinned first, then by last_message_at DESC
• Messages: created_at ASC within a thread

JSON hydration

Citations and follow_ups columns are JSON-serialized; hydrate in the query function so callers receive typed objects (never raw JSON strings).

---

Write mutations (userMutations.ts)

export async function createAmicusThread(params: {
  threadId: string;                    // caller generates UUID (client-side, not DB autoincrement)
  title: string;
  chapterRef?: string | null;
}): Promise<AmicusThread>;

export async function appendAmicusMessage(params: {
  messageId: string;                   // caller generates UUID
  threadId: string;
  role: 'user' | 'assistant';
  content: string;
  citations?: AmicusCitation[];
  followUps?: string[];
}): Promise<AmicusMessage>;

export async function updateThreadTitle(threadId: string, title: string): Promise<void>;
export async function toggleThreadPin(threadId: string): Promise<boolean>;   // returns new pinned state
export async function deleteAmicusThread(threadId: string): Promise<void>;   // CASCADE handles messages

export async function incrementAmicusUsage(): Promise<void>;  // upserts current day's row

Invariants

• appendAmicusMessage updates the parent thread's last_message_at in the same transaction
• Thread deletion cascades to messages (schema-enforced)
• incrementAmicusUsage uses INSERT OR REPLACE pattern keyed on today's date

---

Hook: useAmicusThreads

export function useAmicusThreads(): {
  threads: AmicusThread[];
  isLoading: boolean;
  refresh: () => Promise<void>;
  actions: {
    pin: (threadId: string) => Promise<void>;
    unpin: (threadId: string) => Promise<void>;
    remove: (threadId: string) => Promise<void>;
    rename: (threadId: string, title: string) => Promise<void>;
  };
};

Implementation: React state + pull from queries on mount; expose a refresh function; action functions call mutations + refresh locally (no global cache needed for v1 — simple enough).

---

Conventions to follow

• Match userQueries.ts / userMutations.ts existing file structure
• Use getUserDb() for all access (singleton already set up)
• Generate IDs client-side (UUIDs) — do NOT use autoincrement for thread/message IDs; this matters if/when we ever sync
• Transactions: wrap multi-statement mutations in db.withTransactionAsync
• Logger, not console.log
• Strict TS; no any

---

Acceptance criteria

• Migration adds tables cleanly on fresh install; idempotent re-run
• Migration adds tables to existing user.db without wiping other user data
• listAmicusThreads returns pinned-first ordering
• listAmicusMessages returns oldest-first
• appendAmicusMessage updates last_message_at atomically
• deleteAmicusThread cascades to messages
• toggleThreadPin returns new state correctly
• getAmicusUsageThisMonth counts queries across current month only
• JSON columns hydrate to typed objects
• Unit tests cover: create + append + list round-trip, pin toggle, delete cascade, usage counters
• No any types; lint clean

Out of scope

• Any Supabase sync — local only
• Usage limits enforcement — that's in ai-partner: premium gating via RevenueCat #1460 (premium gating reads from getAmicusUsageThisMonth)
• UI (ai-partner: thread management UI #1454, ai-partner: streaming chat UI with citation pills #1455)