Skip to content

QMD Migration: better-sqlite3 + Turso → libSQL Self-hosted

Timeline

  • Date: 2026-06-16 → 2026-06-17
  • Scope: QMD database layer, cloud sync, deployment configs

Infrastructure Lessons

Know your environment FIRST

  • Check hostname + tailscale ip before assuming which server is which. Development server (100.126.172.96) IS the local machine where the devcontainer runs.
  • Don't SSH to yourself. ssh root@100.126.172.96 fails (can't SSH to own Tailscale IP). If SSH fails on a known Tailscale IP, check if it's local.
  • Use km-exec -s <server> --host when SSH fails. Komodo can execute commands on any server via the periphery API without SSH.

Server topology (verified)

  • Physical host: Lenovo ThinkCentre M910X running ESXi
  • Development VM (100.126.172.96): Debian 13, local ext4 /mnt/user/appdata/ (391GB LVM)
  • Unraid VM (100.68.251.84): Ubuntu, unraid array /mnt/user/appdata/
  • Both peripheries are Docker containers (komodo-periphery:2.2), NOT systemd services
  • Both use local filesystem, NOT NFS. Appdata paths are identical but independent.
  • Komodo Core runs as Docker container on development server

Don't guess — verify

  • Wrong: "development uses NFS" — assumed because /mnt/user/appdata path matches unraid convention
  • Right: km-exec -s development --host "df -hT /mnt/user/appdata" → ext4 (local)
  • Wrong: "development periphery is systemd" — assumed because SSH failed
  • Right: km list servers + Komodo API shows both are Docker containers

Phase 1: Initial Setup (Merge-based Cloud Sync)

Changes

  • Rewrote src/cloud/push.ts with merge-based push strategy for multi-instance Turso sync
  • Added test/cloud-push-merge.test.ts — 9 test cases
  • Collection-aware tables: DELETE WHERE collection IN (local) + INSERT OR REPLACE
  • Hash-keyed tables: INSERT OR IGNORE (shared across instances)
  • Documents.id excluded from INSERT (autoincrement)

Lessons

  • Merge push strategy: Different table types need different merge strategies. Collection-aware (documents, store_collections) need scoped DELETE. Hash-keyed (content, content_vectors, llm_cache) are shared — never DELETE, only INSERT OR IGNORE.
  • Test data matters: Test DB must include store_collections rows, otherwise getLocalCollectionNames() returns empty and DELETE path is skipped silently.
  • FTS5 rowid divergence: Multi-instance autoincrement causes FTS rowids to not match document IDs. FTS rebuild is needed after cross-instance sync.

Phase 2: Cross-Instance Collection Visibility

Changes

  • collections.ts: Added setDbCollections() + dbExtraCollections fallback in getCollection() and listCollections()
  • store.ts: syncConfigToDb() preserves cloud-synced collections (only deletes local-owned)
  • cli/qmd.ts: getStore() injects DB collections via setDbCollections() on startup
  • Contexts always sourced from DB table, not YAML config

Lessons

  • Two createStore functions: QMD has TWO createStore — one in store.ts (sync, CLI) and one in index.ts (async, SDK). CLI uses the sync version. Changes to DB init must go in BOTH.
  • Module state injection: collections.ts can't import store.ts (circular dependency). Use setter injection pattern: setDbCollections() called by CLI before any command.
  • Context DB-source: YAML config's global_context overwrites DB value on every syncConfigToDb(). Remove context from sync to preserve cloud-synced contexts.

Phase 3: libSQL Driver Migration (better-sqlite3 → libsql)

Changes

  • src/db.ts: Replaced better-sqlite3 with libsql (sync API, drop-in compatible)
  • Native vector support: FLOAT32(N) + libsql_vector_idx() replaces vec0 extension
  • Vector search: vector_top_k('idx', ?, N) + vector_distance_cos() replaces MATCH ? AND k=N
  • Vector insert: INSERT OR REPLACE replaces DELETE+INSERT (vec0 didn't support OR REPLACE)
  • Migration script: scripts/migrate-vectors.mjs (vec0 → FLOAT32, 9521 vectors)
  • src/cloud/push.ts, src/cloud/pull.ts, src/cloud/auto-sync.ts deleted (replaced by embedded replica / direct remote)

Lessons

  • libsql sync API is identical to better-sqlite3: prepare().get()/.all()/.run(), exec(), transaction() — zero changes needed in 165 DB call sites.
  • vector_top_k doesn't return distance: Unlike vec0's MATCH ... k=N, libSQL's vector_top_k() returns only id. Use vector_distance_cos() in JOIN to get distance.
  • Float32Array accepted directly: libsql accepts Float32Array for FLOAT32 columns. No need for vector32('[...]') string conversion. BUT only via the native libsql package — @libsql/client HTTP API requires string format.
  • libsql package vs @libsql/client: libsql (npm) = sync API, better-sqlite3-compatible, native binary. @libsql/client = async HTTP client. Use libsql for local/embedded, @libsql/client for remote HTTP queries.
  • ca-certificates required: libsql TLS connections need ca-certificates installed in Docker image. Alpine/slim images may not have it.

Phase 4: Embedded Replica → Self-hosted Direct Remote

Changes

  • Embedded replica mode attempted first: new Database(path, {syncUrl, authToken, syncPeriod})
  • Abandoned due to: Turso free tier 403 (read quota exceeded), WalConflict on local writes, FTS rowid mismatch after sync, corrupted DB on failed syncs
  • Final approach: direct remote connection new Database(url, {authToken})
  • Connection auto-reconnect wrapper in db.ts (STREAM_EXPIRED after 30s inactivity)

Lessons

  • Turso free tier is very limited: Read operations blocked after quota. Embedded replica syncs every 60s × 2 instances = rapid read consumption. Use self-hosted for unlimited.
  • Embedded replica ≠ magic: Multi-writer conflicts (WalConflict), FTS rowid divergence, DB corruption on failed syncs. Not production-ready for QMD's use case.
  • Self-hosted libSQL stream expiry: HTTP connections expire after ~30s inactivity. Wrap prepare()/exec() with retry-on-error pattern.
  • DROP TABLE + CREATE on replica causes WalConflict: Schema DDL on replica DB conflicts with background sync. Skip initializeDatabase() for replicas.

Phase 5: FTS Auto-Sync via SQL Triggers

Changes

  • Created documents_fts_ai/ad/au triggers on libSQL server DB file
  • INSERT on documents → auto-insert FTS row
  • DELETE on documents → auto-delete FTS row
  • UPDATE on documents → auto-replace FTS row
  • Removed all manual FTS rebuild code from QMD startup

Lessons

  • FTS rebuild over HTTP is impractical: Self-hosted libSQL HTTP API has response size limits and connection timeouts. INSERT...SELECT for 671 docs crashes the server.
  • Direct file access for heavy operations: Stop libsql container → run sqlite3 via Python container on the data file → restart. This is the only reliable way for heavy DB operations.
  • SQL triggers are the right solution: One-time trigger creation = FTS stays in sync forever. No rebuild needed. Zero runtime overhead.

Phase 6: Deployment

Changes

  • Docker compose: LIBSQL_URL=http://100.68.251.84:8091, LIBSQL_AUTH from Komodo variable
  • Dockerfile: added ca-certificates for TLS
  • file_paths in Komodo TOML: only ["compose.yaml"] (non-compose files mounted via volumes, not -f overrides)
  • Volume mounts: absolute host paths (/mnt/user/appdata/qmd/...) + pre_deploy copy scripts

Lessons

  • Komodo file_paths = docker compose -f flags: Don't put non-compose files in file_paths. They get passed as -f overrides, causing "models.generate must be a mapping" errors.
  • Docker-in-Docker volume paths: Komodo periphery runs compose inside its container. Relative paths (./config/index.yml) resolve to periphery container paths, not host paths. Use absolute host paths + pre_deploy copy.
  • Resource busy error: Komodo stack state can get stuck as "deploying". Restart periphery container to clear.

Current Architecture

Nanobot → LiteLLM MCP (4001) → QMD MCP (8183) → libSQL (8091)
                                                         ↓
                                                   data.db (direct file)
                                                   FTS triggers (auto-sync)
  • Local QMD: stdio MCP, connects to same libSQL server
  • Remote QMD: HTTP MCP, connects to same libSQL server
  • libSQL server: self-hosted on unraid, 969 docs, 12367 vectors, FTS auto-synced
  • No cloud sync needed: both instances use same DB server directly

Key Files Modified

  • src/db.ts — libsql driver, remote connection wrapper, ReplicaOptions
  • src/store.ts — vector schema (FLOAT32), remote mode init, FTS rebuild function
  • src/cli/qmd.ts — LIBSQL_URL env var wiring, DB fallback collections
  • src/collections.ts — setDbCollections/setDbGlobalContext for cross-instance
  • src/index.ts — replica options in createStore
  • src/mcp/server.ts — removed auto-sync startup
  • scripts/migrate-vectors.mjs — one-time vec0 → FLOAT32 migration
  • Dockerfile — ca-certificates
  • stacks/llm/qmd/compose.yaml — LIBSQL_URL, volume mounts
  • komodo/stacks/llm/qmd.toml — file_paths, pre_deploy

Commits

  • a564554 — merge-based cloud push
  • 7b2a958 — cross-instance collection visibility
  • da33fd7 — context from DB table
  • 9bbb536 — libsql driver + vector migration + embedded replica
  • 64eed35 — remove cloud push/pull code
  • fe420e6 — self-hosted libSQL direct remote
  • 52974a4 — auto-reconnect on stream expiry
  • 1c3d82c — remove FTS auto-rebuild (triggers handle it)