Skip to content
Crosswalker
🚧 Early alpha — building the foundation. See the roadmap →

v0.1.5 — Tier 2 sqlite-wasm sidecar projector

Updated

Goal

Section titled “Goal”

Implement the Tier 2 sidecar projector — the deletable, recoverable SQLite projection of Tier 1 frontmatter. Auto-projects on vault load; lazy closure cache for transitive crosswalk queries; if .crosswalker.sqlite is missing/corrupted/stale, reproject from canonical Tier 1 on next load. Per Ch 24 substrate synthesis (sqlite-wasm + sqlite-vec; libSQL/Turso/Limbo rejected).

Status

Section titled “Status”

✅ Done (2026-05-06). All 6 phases complete; sqlite-vec deferred via WASM-A pivot with calendar-anchored 2026-11-06 revisit. Tier 2 sidecar projector + query API + closure cache + plugin integration all green; realistic-framework integration tests pass for NIST 800-53 / NIST CSF 2.0 / ISO 27001:2022 / MITRE ATT&CK with cross-framework closure queries. See delivery log for the full delivery summary + system-design integration diagram.

Dependencies

Section titled “Dependencies”

Blocks: v0.1.6 (Bases query layer reads from sidecar for transitive queries); v0.1.7 (exporters use sidecar for join-heavy queries)

WASM bundling option-space (decided 2026-05-05)

Section titled “WASM bundling option-space (decided 2026-05-05)”

The substrate commitment from Ch 24 §4 is @sqlite.org/sqlite-wasm + sqlite-vec (canonical, foundation-governed). The schema spec §7 line 733 clarifies vector embeddings are a “v0.1+ optional add-on” — concrete DDL deferred until the embedding feature ships.

Critical constraint (per Ch 24 deliverable §2 + §4.4): WASM SQLite does not support dynamic extension loading (no sqlite3_load_extension over a host filesystem from a WASM sandbox). To use sqlite-vec from any WASM SQLite, the extension must be statically compiled in at build time. Neither @sqlite.org/sqlite-wasm nor sqlite-vec as-shipped on npm composes them; runtime extension loading is unavailable in WASM.

This collapses the practical option space to three:

#Approachsqlite-vec support?EffortBundleStatus
WASM-APlain @sqlite.org/sqlite-wasm (defer sqlite-vec to later milestone)❌ deferredNone — bun add @sqlite.org/sqlite-wasm~600 KBConsidered, rejected for v0.1.5 — postpones substrate commitment unnecessarily.
WASM-BVendor sqlite-vec-wasm-demo pre-built artifact (Alex Garcia’s static-linked WASM) into the plugin✅ NowNone — vendor .wasm file~600 KB✅ CHOSEN for v0.1.5. Substrate commitment satisfied immediately; rollback path is cheap; migration to WASM (npm) trigger documented.
WASM (npm-installable)Upstream-shipped sqlite-vec-wasm npm package that composes cleanly with sqlite-wasm✅ NowNone — bun add both~600 KBDoesn’t exist as of 2026-05-05. Migration target — see “Migration trigger” below.
WASM-CCustom static-link build via emcc + autotools (the formal Ch 24 §4 lock interpretation)✅ NowHigh — build script + CI choreography~600 KBReserved for v1.0+ if WASM-B doesn’t satisfy a concrete need (custom flags, patches, size trim).

Path history — WASM-B attempted, reverted to WASM-A (2026-05-05 → 2026-05-06)

Section titled “Path history — WASM-B attempted, reverted to WASM-A (2026-05-05 → 2026-05-06)”

2026-05-05 user decision: ship WASM-B (bun add sqlite-vec-wasm-demo) with vec from day 1. Acknowledged size overrun (~2 MB compressed vs Ch 24 §2.2 ~600 KB target).

2026-05-06 integration discovery + revert to WASM-A: during Phase 1 substrate scaffolding, the WASM-B artifact (sqlite-vec-wasm-demo v0.1.9) hit a chain of 5 emscripten env-detection issues in succession when loaded inside Obsidian’s Electron renderer:

  1. Build target mismatch: artifact uses BigInt literals (ES2020+); esbuild was set to ES2018 → bumped to ES2020
  2. wdio service file copy: obsidian-launcher hardcodes copying manifest.json + main.js + styles.css only → added a wdio before hook to copy .wasm + .mjs
  3. Env-detection throw: artifact built with -sENVIRONMENT=web; checks typeof process !== 'undefined' && process.versions?.node and throws “not compiled for this environment”. Obsidian’s Electron renderer exposes Node integration → patched the .mjs string to bypass
  4. new URL('.', blobURL): artifact’s import.meta.url becomes a Blob URL when loaded via Blob; Chromium rejects relative URL parent computation → patched import.meta.url references to a placeholder HTTPS URL
  5. Module.postRun already processed: emscripten init order issue surfaced after the prior fixes — final unresolved blocker

The sqlite-vec-wasm-demo is fundamentally a demo build for plain web browsers, not Electron’s hybrid window+process renderer. Each fix revealed the next layer of mismatch. The user-approved “we revert later” trigger fired sooner than expected.

Reverted to WASM-A: plain @sqlite.org/sqlite-wasm (the official build by the SQLite team) loads cleanly in Obsidian via the Blob URL workaround — no env-detection traps, well-established Obsidian-plugin precedent. All 6 Phase 1 smoke tests pass on the WASM-A path.

ChangeBefore (WASM-B attempt)After (WASM-A revert)
Substrate packagesqlite-vec-wasm-demo@0.1.9@sqlite.org/sqlite-wasm@3.53.0-build1
sqlite-vecCompiled in (didn’t load)Deferred — see “Revisit checkpoint” below
Bundle size (.wasm gzipped)1.8 MB~400 KB
Plugin total compressed~2 MB (over Ch 24 §2.2 budget)~700 KB (within budget)
Phase 1 smoke tests2/6 (4 blocked by emscripten issues)6/6

Revisit checkpoint — 2026-11-06 (6 months out) or earlier signal

Section titled “Revisit checkpoint — 2026-11-06 (6 months out) or earlier signal”

Hard checkpoint: re-evaluate sqlite-vec integration by 2026-11-06 (6 months from the WASM-A pivot). This is in addition to (not a replacement for) the Ch 24 §5 Q4 trigger conditions. Even if no signal fires, we re-check the upstream tooling state on that date.

What “re-evaluate” looks like at the checkpoint:

  • Has @sqlite.org/sqlite-wasm shipped sqlite-vec compiled in?
  • Has Alex Garcia (sqlite-vec maintainer) shipped a production-quality sqlite-vec-wasm (not the demo build)?
  • Has anyone in the Obsidian-plugin ecosystem shipped a hardened wrapper?
  • Has @sqlite.org/sqlite-wasm enabled runtime extension loading?

If any are yes → sequence the migration into the next milestone. If all are no → push the checkpoint another 6 months OR commit to WASM-C (custom build).

This checkpoint is calendar-anchored, not just trigger-anchored, so it can’t be silently postponed indefinitely. Anchored in:

  • This milestone page (“Revisit checkpoint” section above)
  • Ch 24 synthesis log §5 Q4
  • Project memory (project_wasm_vendoring_migration_trigger.md)
  • v0.1.5 delivery log (when written) — must include the checkpoint in its Related section

Reasoning:

  • Substrate commitment satisfied immediately — Ch 24 §4 locks @sqlite.org/sqlite-wasm + sqlite-vec. WASM-B ships both starting v0.1.5; no two-stage migration.
  • Decoupling is preserved — vector layer (sqlite-vec) is portable across substrates by design (Ch 24 §3). Vendoring a static-linked artifact doesn’t violate the modularity property — it’s still sqlite-vec data, portable to any future substrate.
  • WASM-B IS the formal Ch 24 §4 lock — the synthesis text says “Custom static-link build of @sqlite.org/sqlite-wasm with sqlite-vec compiled in.” Vendoring sqlite-vec-wasm-demo IS a static-linked build; we’ve delegated the build step to upstream rather than running emcc ourselves. The result is identical (statically-linked WASM artifact). Commitment satisfied.
  • Rollback is cheap (see below) — vec layer is purely additive in the schema; concepts / mappings / junction_notes don’t depend on it. Rip-out is ~30 min of code if needed.
  • Forward migration is locked-in (see migration trigger below) — when upstream ships an npm-installable build OR sqlite-wasm gains runtime extension loading, we migrate from vendored binary to npm dep.

Migration trigger — when to move from sqlite-vec-wasm-demo to a smaller / better-packaged build

Section titled “Migration trigger — when to move from sqlite-vec-wasm-demo to a smaller / better-packaged build”

This is the 6th migration trigger in addition to the 5 already locked in Ch 24 §5. Re-evaluate the WASM bundling approach when ANY of:

  1. Bundle-size pressure surfaces — community plugin reviewers push back on plugin size, mobile load times degrade noticeably, OR users report sub-1MB plugins as preferable. This is the trigger most likely to fire. v0.1.5 ships at ~2 MB compressed (vs Ch 24 §2.2’s ~600 KB target); reducing this is high-value when the demand is concrete.
  2. sqlite-vec upstream ships a production-quality npm-installable WASM package — size-optimized (debug symbols stripped, -Os + LTO, feature trim), distinct from the demo artifact
  3. @sqlite.org/sqlite-wasm gains runtime extension loading support in WASM (currently disabled for browser security; if upstream adds it, we can bun add both packages and load vec at runtime)
  4. Concrete need for a custom build that sqlite-vec-wasm-demo doesn’t satisfy:
    • Custom compile flags (e.g., trim unused SQLite features for size)
    • Patches to upstream SQLite or sqlite-vec
    • Reproducible builds for compliance environments
    • SQLite version mismatch with what the demo artifact ships
  5. Demo artifact is abandoned upstream — no release for 12+ months AND no community fork has gained traction

Any one of those signals → migrate to WASM-C (custom build) or whatever the new upstream offering is. The npm sqlite-vec-wasm-demo package is the v0.1 default, NOT a permanent commitment.

Migration target: most likely WASM-C custom build (size-optimized: -Os + LTO + feature trim — target ~600 KB compressed per Ch 24 §2.2 original commitment). Real engineering investment in a build script + CI integration, but unavoidable for production-quality vec packaging since upstream ships a demo build.

Rollback path — if we have to pull sqlite-vec entirely

Section titled “Rollback path — if we have to pull sqlite-vec entirely”

If sqlite-vec proves problematic (mobile incompatibility surfaces, vendor abandonment, security CVE without timely fix, etc.), removing it is straightforward because vec is purely additive in the schema:

StepWhat changes
1Delete src/tier2/vendor/sqlite-vec.wasm
2Swap import to plain @sqlite.org/sqlite-wasm from npm (bun add @sqlite.org/sqlite-wasm)
3Remove CREATE VIRTUAL TABLE ... USING vec0(...) from migrations + remove vec-specific query helpers
4Bump schema_meta.schema_version from tier2-sqlite-v1 to tier2-sqlite-v1-no-vec; existing sidecars auto-reproject without vec tables
5Update _crosswalker_settings.enableVectorEmbeddings toggle to default false; UI surfaces “vector search unavailable”

Total estimated work: ~30 minutes of code, full reversibility, zero data loss for non-vector tables (concepts, mappings, junction_notes are unaffected). Tier 1 vault is canonical and complete; Tier 2 reprojects.

This rollback property is what makes vendoring acceptable. We’re not betting v0.1 on a single binary’s stability; we’re using it for convenience with a clean exit.

Scope

Section titled “Scope”

In:

  • Vendor sqlite-vec-wasm-demo artifact (WASM-B path) into src/tier2/vendor/sqlite-vec.wasm — substrate is sqlite-wasm + sqlite-vec statically linked from day 1 of v0.1.5
  • Project Tier 1 frontmatter on vault load — populate ontologies, controls, mappings, junction_notes tables (DDL per v0.1 schema spec §7)
  • closure_cache table created (empty until first transitive query)
  • control_embeddings virtual table via vec0 — DDL present but unused until vector-embedding feature lands (UI/embedding-generation deferred to a later milestone)
  • Recovery on missing/stale sidecar — reproject from Tier 1; abort + warn if Tier 1 itself is malformed
  • OPFS persistence via opfs-sahpool VFS (works on Capacitor without COOP/COEP — the mobile-portable path)
  • Runs on main thread with cooperative yielding (Web Workers unreliable per Ch 23 §9.5)
  • Platform.isDesktopApp gate where needed
  • clear-sidecar command in palette
  • Lazy closure cache materialization (recursive CTE on first transitive query; mtime-based invalidation)
  • Streaming projector — walks vault file-by-file via the v0.1.4.5 streaming foundation (no full-vault-in-RAM accumulation)
  • VENDORING readme/notice in src/tier2/vendor/README.md documenting the artifact source, version pin, license, and migration trigger

Out (deferred to later milestones):

  • Vector-search UI — query input, similarity threshold tuning, results rendering (when first vector query use case is concrete)
  • Vector embedding generation — local embedding model integration; OpenAI/Anthropic/Ollama API wiring (when first user requests semantic search)
  • DuckDB-WASM / Oxigraph / Nemo (researched back-pocket per v0.1 stack pivot)
  • Tier 3 server (deployment-guide concern; v2.0+)
  • libSQL / Turso / Limbo (rejected per Ch 24 synthesis; 5 migration triggers locked)
  • Custom emcc build (WASM-C) — reserved for v1.0+ if WASM-B doesn’t satisfy a concrete need

Phased execution plan

Section titled “Phased execution plan”

Six phases, each commit-able independently. Streaming foundation from v0.1.4.5 is leveraged so the projector iterates vault files lazily.

Phase 1: Substrate scaffolding (WASM-B path via npm)

Section titled “Phase 1: Substrate scaffolding (WASM-B path via npm)”
  • bun add sqlite-vec-wasm-demo@0.1.9 — pin to a specific version
  • Update esbuild.config.mjs to copy node_modules/sqlite-vec-wasm-demo/sqlite3.wasm into the plugin distribution at build time
  • New src/tier2/sidecar.ts — openSidecar(app) — initializes sqlite-wasm with opfs-sahpool VFS via the npm package; opens .crosswalker.sqlite at vault root; returns handle with db + close()
  • New src/tier2/schema.sql — DDL per v0.1 schema spec §7: schema_meta, ontologies, controls, mappings, junction_notes, closure_cache + indexes + junction_notes_with_freshness view + (deferred-until-feature) control_embeddings virtual table commented out
  • New src/tier2/migrations.ts — applyMigrations(db, schemaVersion); if version mismatch or missing, drop tables + recreate
  • Smoke test: open sidecar in test-vault, run SELECT 1, run SELECT vec_version() to confirm sqlite-vec is loaded, verify success
  • Bundle-size measurement (before/after — record actual size; documented as ~2 MB compressed in v0.1.5; flag for the 6th migration trigger when reduction becomes priority)

Phase 2: Projector ✅ Done (2026-05-06)

Section titled “Phase 2: Projector ✅ Done (2026-05-06)”
  • New src/tier2/projector.ts — projectFromTier1(app, db, options?) walks app.vault.getMarkdownFiles() lazily; cooperative yield every 50 files (await new Promise(setTimeout))
  • Per-file: read frontmatter via app.metadataCache.getFileCache(file)?.frontmatter; dispatch by kind (default concept → concepts table; junction-note → junction_notes; crosswalk-edge → mappings)
  • Ontology population: kind-aware — concept-notes register their own ontology (from fm.curie prefix); crosswalk-edges register both subject + object ontologies (from subject_id and object_id prefixes). Recipe-discovery walk deferred to a future milestone (placeholder name/version/recipe_id for now)
  • Idempotent upsert (INSERT OR REPLACE) keyed on vault_path for concepts/junctions; source_path UNIQUE for mappings
  • Source-hash via FNV-1a non-cryptographic hash (cryptographic hashes for audit trail are v0.1.8 concern); excludes _crosswalker.produced_at so re-imports of unchanged source produce stable hashes
  • Skips files without _crosswalker block (silently, counted as skipped)
  • Closure cache invalidated after any mappings write
  • plugin.runProjection() exposed — primary E2E entry; cached sidecar handle reused
  • E2E: tests/e2e/sidecar-phase-2-projection.spec.ts — 7 tests, all pass (counts; concept projection; crosswalk projection; junction projection; ontology upsert; idempotency; skip-non-crosswalker)

Phase 3: Query API ✅ Done (2026-05-06)

Section titled “Phase 3: Query API ✅ Done (2026-05-06)”
  • New src/tier2/queries.ts — typed helpers:
    • getConceptsByOntology(db, ontologyId) — flat list of concepts in the given ontology, ordered by curie
    • crosswalkBetween(db, subjectOntology, objectOntology, predicateId?) — direct edges between two ontologies, optionally filtered by STRM predicate
    • closureFromConcept(db, startCurie, predicateId?, maxDepth?) — transitive closure via recursive CTE per Ch 18 §2 deliverable patterns: path-string anti-join cycle detection + MIN(depth) aggregation + predicate filter in both base + recursive arms
    • findCoverageGaps(ontologyId) — deferred (requires wikilink resolution against junction_notes.subject string-matching; future milestone)
  • Closure cache lazy materialization (Ch 18 §2.5):
    • First call computes the recursive CTE, populates closure_cache (subject_id=start_curie, predicate_id=predicate_filter, object_id=target_curie, shortest_depth=N)
    • Subsequent calls hit the cache directly via WHERE subject_id = $start AND predicate_id = $pred
    • Projector clears closure_cache after any mappings change (DELETE FROM closure_cache in projector.ts)
  • plugin.queryConcepts(ontologyId) + plugin.queryCrosswalk(subj, obj, pred?) + plugin.queryClosure(start, pred?, maxDepth?) exposed as instance handles
  • E2E: tests/e2e/sidecar-phase-3-queries.spec.ts — 9 tests, all pass:
    1. queryConcepts returns all concepts in ontology
    2. queryCrosswalk returns direct edges (no predicate filter)
    3. queryCrosswalk with predicate filter returns only matching edges
    4. queryClosure (no filter) computes full reachability — verifies shortest-path semantics with multiple competing paths
    5. queryClosure with predicate filter — verifies filter applied in both base + recursive arms; verifies shortest path correctly skips filtered-out edges
    6. closure_cache empty after fresh projection
    7. closure_cache populated after first closure call
    8. Second closure call returns cached rows (same shape as first)
    9. runProjection invalidates closure_cache (mappings-change → cache cleared)

Phase 4: Plugin integration ✅ Done (2026-05-06)

Section titled “Phase 4: Plugin integration ✅ Done (2026-05-06)”
  • src/main.ts — app.workspace.onLayoutReady() triggers autoProjectOnLayoutReady() which lazy-runs projectFromTier1. Per Ch 24 §2 recovery property — onLayoutReady is the canonical entry point that makes “if .crosswalker.sqlite is missing, the projector reprojects” real
  • Settings-toggleable via enableTier2Projection (default true); auto-projection skipped silently if disabled
  • Errors during auto-projection don’t block plugin lifecycle — Tier 1 vault is still functional; user sees a Notice if errors > 0
  • plugin.openTier2() + plugin.runProjection() + plugin.queryConcepts/Crosswalk/Closure all exposed as instance handles (shipped in Phases 1-3; verified usable in Phase 4)
  • Palette command crosswalker:clear-tier-2-sidecar — closes handle, deletes sidecar, next access reprojects
  • Settings tab: new “Tier 2 sidecar” heading section with enableTier2Projection toggle + tier2SidecarPath text input (defaults .crosswalker.sqlite)
  • openTier2() now respects settings.tier2SidecarPath; clearSidecar() uses the same path
  • E2E: tests/e2e/sidecar-phase-4-integration.spec.ts — 5/5 tests pass:
    1. Settings include tier2 fields with sane defaults
    2. clear-tier-2-sidecar command is registered
    3. openTier2() respects settings.tier2SidecarPath
    4. clear-sidecar palette command closes handle + reprojects (recovery property verified)
    5. enableTier2Projection=false setting doesn’t disable manual runProjection (only controls auto-trigger on vault load)

Phase 5: Tests

Section titled “Phase 5: Tests”
  • Unit tests/tier2-projection.test.ts — round-trip projection (project a fixture vault → query → assert rows match Tier 1 facts)
  • Unit tests/tier2-recovery.test.ts — delete sidecar handle, reproject, verify same data
  • E2E tests/e2e/sidecar.spec.ts (milestone gate):
    1. Plugin opens sidecar in test-vault
    2. Run plugin.runProjection() → verify controls, mappings, junction_notes populated
    3. Query API: getControlsByOntology, crosswalkBetween return expected rows
    4. Delete .crosswalker.sqlite via vault adapter → re-project → verify same results
    5. clear-sidecar command works

Phase 6: Delivery log + milestone status flip + commit

Section titled “Phase 6: Delivery log + milestone status flip + commit”
  • Write docs/src/content/docs/agent-context/zz-log/2026-05-NN-v0-1-5-tier-2-sidecar-shipped.mdx with system-design integration diagram
  • Flip milestone status to ✅
  • Note WASM-A → WASM-B sequencing for the future vec milestone
  • Commit + push

Success criteria

Section titled “Success criteria”
  • Sidecar opens cleanly in Electron-based desktop Obsidian via opfs-sahpool VFS
  • sqlite-vec is loaded and queryable (SELECT vec_version() succeeds)
  • Projector handles a 100-row test vault in under 5 seconds with cooperative yielding (no UI freeze)
  • Deleting .crosswalker.sqlite and reloading the plugin cleanly reprojects without user action
  • Recursive-CTE transitive closure query returns results consistent with Tier 1 facts
  • Bundle size measured + documented (expected ~2 MB compressed; over Ch 24 §2.2 budget; tracked for 6th migration trigger)
  • All v0.1.4.5 prior tests still pass (153 tests across 8 spec files)
  • New sidecar.spec.ts E2E passes (milestone gate)
  • 6th migration trigger documented in Ch 24 synthesis log §5 — when to migrate from sqlite-vec-wasm-demo to a smaller / better-packaged build

Files to touch

Section titled “Files to touch”
  • src/tier2/sidecar.ts — NEW (lifecycle + OPFS sahpool VFS init + sqlite-vec load via npm package)
  • src/tier2/schema.sql — NEW (DDL per spec §7; vec0 virtual table commented out until vec feature lands)
  • src/tier2/migrations.ts — NEW (schema-version + drop-and-recreate)
  • src/tier2/projector.ts — NEW (Tier 1 → sidecar table mapping; streaming-friendly)
  • src/tier2/queries.ts — NEW (typed query helpers; closure cache logic)
  • src/main.ts — wire onLayoutReady projection trigger + tier2 instance handle + clear-sidecar command
  • src/settings/settings-data.ts + src/settings/settings-tab.ts — enableTier2Projection toggle
  • package.json — add sqlite-vec-wasm-demo@0.1.9 dep (provides sqlite-wasm + sqlite-vec statically linked from upstream)
  • esbuild.config.mjs — WASM asset copy config
  • tests/tier2-projection.test.ts — NEW
  • tests/tier2-recovery.test.ts — NEW
  • tests/e2e/sidecar.spec.ts — NEW (milestone gate)

Open questions

Section titled “Open questions”
  • Which OPFS VFS variant — opfs (requires SharedArrayBuffer/COOP-COEP) or opfs-sahpool (works without)? Ch 24 §2.3 says opfs-sahpool is the pragmatic path
  • Closure cache invalidation — file-mtime tracking, recipe-hash tracking, or full reproject on any vault change?
  • Should we expose a clear-sidecar command in the command palette for users? (Useful for debugging; minimal cost.)
Section titled “Related”

Concept pages:

Agent context:

  • v0.1 schema spec — §7 Tier 2 SQL DDL (mappings, junction_notes, concepts, ontologies tables)
  • Vision — runtime-agnostic recipe schema; Tier 2 is one consumer
  • Tradeoffs — embedded substrate vs. portability constraints

Design decisions (synthesis logs):

Research deliverables:

External references:

Other milestones: