migrate-to-sqlite

name	migrate-to-sqlite
description	Migrate a TinyBase table to SQLite. Use when asked to move a data domain (e.g. templates, vocabs) from the TinyBase store to the app SQLite database.
metadata	{"internal":true}

Status

Keep up to date as each PR lands. Outer box = fully done across both phases. Sub-bullets track sub-states where relevant.

This migration targets the TinyBase main store only. The separate TinyBase settings store is out of scope unless the plan is explicitly expanded to include it.

• templates — already Drizzle, no Phase 0 needed
• calendars
  • Phase 0 reads (PR 2: useCalendar, useEnabledCalendars)
  • Phase 0 writes — services/calendar/ctx.ts has a cross-domain calendars+events transaction; lands with events PR
  • Phase 1 — Rust migration + ops exist
• events
  • Phase 0
  • Phase 1 — Rust migration + ops exist
• sessions
• transcripts
• humans
• organizations
• enhanced_notes
  • Phase 0 reads — session/hooks/useEnhancedNotes.ts
  • Phase 0 writes
  • Phase 1
• mapping_session_participant
• mapping_tag_session
• mapping_mention
• tags
• chat_groups
• chat_messages
• session_key_facts
• tasks
• daily_notes
  • Phase 0 writes (partial) — chat/store/*
  • Phase 0 reads
  • Phase 1
• tasks
• memories
  • Phase 0 writes — settings/memory/custom-vocabulary.tsx
  • Phase 0 reads
  • Phase 1
• daily_notes

Strategy

Two-phase, per-domain migration. Each phase is many small PRs.

Phase 0 — Decouple consumers

Before any storage swap, move every TinyBase call behind a domain hook living in apps/desktop/src/<domain>/hooks.ts (or <domain>/hooks/*). Hook return shapes are plain TypeScript; no TinyBase types leak out. Consumer code stops importing ~/store/tinybase/store/main.

Scope note: this applies to consumers of ~/store/tinybase/store/main and the legacy ~/store/tinybase/hooks barrel. Do not start wrapping settings.UI.* usage behind new hooks as part of this effort; ~/store/tinybase/store/settings is intentionally out of scope for this migration.

Why: one storage-swap PR per domain touches 1 file (the hook module), not 20–50 consumer files.

Enforced by anlg/no-raw-tinybase in eslint-plugin-anlg.mjs. .oxlintrc.json keeps a TINYBASE_MIGRATION_PENDING override that shrinks as each domain is cleaned. CI gates this via .github/workflows/lint.yaml.

Phase 1 — Swap storage per domain

1. Rust migration + ops + Drizzle schema (steps below).
2. Flip writes in the hook module: db.insert()/update()/delete().
3. Shadow-hydrate TinyBase from SQLite so read hooks that haven't moved yet keep working. A small adapter subscribes to db-live-query and mirrors rows into the in-memory TinyBase store. One-way only: SQLite is the source of truth.
4. Swap read hooks to useDrizzleLiveQuery one at a time. Consumers untouched because hook signatures are stable.
5. Once no TinyBase consumers remain for the domain, remove the shadow adapter, the TinyBase schema entries, and the persister.

Skip the shadow bridge only for leaf-clean domains (no cross-table indexes/queries into or out of the domain, and <10 consumer sites).

Architecture

• Schema source of truth: Rust migration in crates/db-app/migrations/
• Drizzle mirror: packages/db/src/schema.ts (typed TS query interface, not schema management)
• Reads (reactive): useDrizzleLiveQuery — calls .toSQL() on a Drizzle query, feeds {sql, params} to the underlying useLiveQuery which uses subscribe() from @anlg/plugin-db
• Reads (imperative): db.select()... through the Drizzle sqlite-proxy driver
• Writes: db.insert(), db.update(), db.delete() through the Drizzle sqlite-proxy driver, wrapped in useMutation from tanstack-query
• Reactivity loop: write via execute → SQLite change → Rust db-live-query notifies subscribers → useLiveQuery fires onData → React re-renders. No manual invalidation needed.

Package layers

The DB stack uses a factory/DI pattern across four packages:

1. @anlg/db-runtime (packages/db-runtime/) — type contracts only: LiveQueryClient, DrizzleProxyClient, shared row/query types.
2. @anlg/db (packages/db/) — Drizzle schema (schema.ts) + createDb(client) factory using drizzle-orm/sqlite-proxy. Re-exports Drizzle operators (eq, and, sql, etc.).
3. @anlg/db-tauri (packages/db-tauri/) — Tauri-specific client that binds execute/executeProxy/subscribe from @anlg/plugin-db to the db-runtime types.
4. @anlg/db-react (packages/db-react/) — createUseLiveQuery(client) and createUseDrizzleLiveQuery(client) factories.

These are wired together in apps/desktop/src/db/index.ts, which exports db, useLiveQuery, and useDrizzleLiveQuery. Consumer code imports from ~/db, not directly from the packages.

Per-domain steps (Phase 1)

Assumes Phase 0 already landed for this domain — consumers go through <domain>/hooks.ts, not raw UI.*.

1. Rust migration

Add a new timestamped .sql file in crates/db-app/migrations/. Convention: YYYYMMDDHHMMSS_name.sql.

Do NOT include user_id columns — it was a TinyBase-era pattern with a hardcoded default. It will be redesigned when multi-device/team support lands.

2. Rust ops (optional but recommended)

Add <domain>_types.rs and <domain>_ops.rs in crates/db-app/src/ with typed sqlx::FromRow structs and CRUD functions. Export from lib.rs. These are used by other Rust code and legacy import; the TS side uses Drizzle instead.

3. Legacy data import

If the domain had a TinyBase JSON persister file (e.g. templates.json), add an import function in plugins/db/src/migrate.rs that reads the old file and upserts rows. Call it from plugins/db/src/runtime.rs during startup. Guard with an "already imported" check (e.g. table non-empty).

4. Drizzle schema

Add the table definition to packages/db/src/schema.ts mirroring the migration. Use { mode: "json" } for JSON text columns, { mode: "boolean" } for integer boolean columns. Re-export from packages/db/src/index.ts if adding new operator re-exports.

5. Swap hook internals

Replace the hook module's TinyBase calls with Drizzle. Hook signatures stay the same, so consumer code doesn't change.

• useDrizzleLiveQuery(db.select()...) for reactive reads
• db.select()... for imperative reads (returns parsed objects via proxy driver)
• db.insert(), db.update(), db.delete() for writes, wrapped in useMutation

Import db and useDrizzleLiveQuery from ~/db, and schema tables/operators from @anlg/db.

Live query results come from Rust subscribe as raw objects (not through the Drizzle driver), so mapRows must handle two things:

• JSON parsing for JSON text columns (e.g. sections_json, targets_json).
• snake_case → camelCase mapping. Live rows use the raw SQLite column names (pin_order, targets_json), while Drizzle's $inferSelect uses camelCase (pinOrder, targetsJson). Define a separate <Domain>LiveRow type with snake_case keys for mapRows, distinct from the Drizzle inferred type. See TemplateLiveRow in apps/desktop/src/templates/queries.ts for the pattern.

6. Shadow bridge (skip for leaf-clean domains)

Add a small module that hydrates the TinyBase <domain> table from SQLite on startup and subscribes to db-live-query to mirror subsequent changes. One-way: SQLite → TinyBase. Retire once all consumers have swapped.

7. Remove TinyBase artifacts

• Table definition from packages/store/src/tinybase.ts
• Query definitions from store/tinybase/store/main.ts (both QUERIES object and _QueryResultRows type)
• Persister files (e.g. store/tinybase/persister/<domain>/)
• Persister registration from store/tinybase/store/persisters.ts
• Hooks from store/tinybase/hooks/ if they existed
• Shadow-bridge module if one was added
• Associated tests and test wrapper setup

8. Verify

• cargo check and cargo test -p db-app -p tauri-plugin-db
• pnpm -F @anlg/desktop typecheck
• pnpm -F @anlg/desktop test
• npx oxlint --quiet apps/desktop/src/ (the anlg/no-raw-tinybase CI gate)
• pnpm exec dprint fmt