[RFC] Local-First Storage — Run Hexus Without an External Postgres (sqlite-vec backend)

[codenamekt]

Issue 0001: Local-First Storage (Make Hexus Run Without an External Postgres)

Status: Draft / Design Phase
Branch: fix/hexus-offline
Author: @codenamekt
Created: 2026-08-30
Affects: hexus/store.py (refactor only), hexus/__init__.py, deployment story

---

TL;DR

Today, hexus requires a running Postgres + pgvector to do anything useful. That makes hexus a server-class tool — every laptop, every air-gapped machine, every Omarchy install has to bring its own Postgres (or talk to a homelab). The embedder is already local (MiniLM-L6-v2, 7.4ms per call, CPU-only). The storage isn't.

This issue proposes adding a SqliteBackend (sqlite-vec + FTS5) behind a small MemoryBackend protocol, so hexus runs as a single file at ~/.local/share/hexus/hexus.db on a laptop with zero daemons, zero docker, zero network. Postgres stays as the production / homelab backend — additive, not breaking.

---

Motivation

The laptop-grade use case is real

Hexus is being adopted for Omarchy (DHH's Arch+Hyprland distro) as the OS-level memory substrate for AI coding agents. Omarchy runs on laptops. Laptops:

• Go offline (planes, cafes, travel, conferences)
• Move between networks (home wifi → tether → corporate VPN)
• Boot cold — first omarchy memory recall after a fresh install must "just work"
• Crash (laptop sleep, OOM, lid close) — and the memory substrate can't be the thing that takes 30s to recover

None of that works against a remote Postgres.

The homelab use case stays

Existing deployments (the hexus:homelab docker image, multi-agent shared-knowledge setups, hermes-agent fleets) all keep working unchanged. The Postgres backend becomes a PostgresBackend implementation; the new SQLite path is opt-in via config.

The dogfood angle

The hexus maintainer's own workflow is exactly this case: homelab Postgres → laptop with no Postgres → optional sync when on the same network. If we get this right for ourselves, we get it right for everyone.

---

Goals

• ✅ Hexus runs standalone on a laptop with no daemon, no docker, no Postgres binary
• ✅ All existing MCP tools work (recall_memory, recall_conversation, recall_delegation, entity_graph, graph_walk, common_topics, confirm_memory, reject_memory, summarize_session, headroom_retrieve, memory_stats)
• ✅ Postgres deployments keep working — same API, same SQL semantics where they exist in Postgres
• ✅ Optional bidirectional sync between local SQLite and remote Postgres
• ✅ Migration path: hexus migrate --from postgres --to sqlite for one-shot moves
• ✅ Schema version stamped on both backends so hexus-mcp serve auto-migrates on startup
• ✅ All existing tests pass on both backends (a BACKEND=sqlite|postgres test matrix in CI)

Non-Goals (for v1)

• ❌ Replacing HNSW with a different ANN algorithm — sqlite-vec brute-force KNN is fine to ~100k vectors, and laptops won't realistically hit that
• ❌ halfvec / binary_quantize precision modes — drop for v1; can re-add later if memory pressure matters
• ❌ Postgres LISTEN/NOTIFY real-time events — not portable; defer
• ❌ DuckDB / LanceDB / pg_embedded — evaluated, not picked (see "Why SQLite" below)

---

Why SQLite + sqlite-vec (and not the alternatives)

Option	Verdict	Why
Embedded Postgres (pg_embedded, user-mode postgres binary)	� no	~150MB RAM, slow startup, complex teardown, WAL recovery on every laptop sleep — brings the daemon problem onto the laptop
SQLite + sqlite-vec + FTS5	✅ pick this	Zero daemon, crash-safe WAL, ~30MB RAM, single file at ~/.local/share/hexus/hexus.db, built into Python, battle-tested on every mobile device
DuckDB + VSS extension	🤷 later	HNSW support, columnar analytics — wrong default, useful if/when we ship "memory analytics"
LanceDB	❌ no	Different paradigm (columnar/PyArrow); would force a full rewrite of store.py's SQL — not worth it

---

Proposed Design

1. MemoryBackend Protocol

A thin contract, not an ORM. Lives in a new hexus/backends/ package:

# hexus/backends/base.py
from typing import Protocol, runtime_checkable

@runtime_checkable
class MemoryBackend(Protocol):
    def upsert(
        self,
        scope: str,
        target: str,
        content: str,
        embedding: list[float],
        metadata: dict,
    ) -> int: ...

    def recall(
        self,
        scope: str,
        query_embedding: list[float],
        limit: int,
        bm25_query: str | None = None,
        isolation: str = "shared",
    ) -> list[dict]: ...

    def remove(self, scope: str, memory_id: int) -> bool: ...
    def confirm(self, scope: str, memory_id: int) -> None: ...
    def reject(self, scope: str, memory_id: int) -> None: ...
    def stats(self) -> dict: ...
    def sync_from(self, other: "MemoryBackend") -> None: ...
    def close(self) -> None: ...

Five methods cover ~95% of store.py's actual call sites. The remaining 5% (observability, vector-precision migrations, cross-encoder reranking) stay backend-specific and live in the implementation classes, not the protocol.

2. PostgresBackend (renamed from store.py)

The current hexus/store.py becomes hexus/backends/postgres.py. No behavior changes — pure rename + import path adjustments. CI must still pass against Postgres exactly as today.

3. SqliteBackend (new)

Lives at hexus/backends/sqlite.py. Covers the protocol. Key implementation notes:

Hexus today (pgvector)	SQLite + sqlite-vec
embedding <=> %s::vector	vec_distance_cosine(embedding, ?)
to_tsvector('english', content)	FTS5 content column with unicode61 tokenizer + porter stemmer
ts_rank(tsv, q)	bm25(fts)
HNSW (embedding vector_cosine_ops)	sqlite-vec vec0 virtual table (brute-force KNN; fine to ~100k)
halfvec / binary_quantize	drop for v1
JSONB	SQLite JSON1 extension (built in)
psycopg_pool.ConnectionPool	sqlite3.Connection + threading.Lock mirroring the existing writer-thread pattern
LISTEN/NOTIFY (if used)	drop — not portable

Schema versioning lives in a hexus_meta(key TEXT PRIMARY KEY, value TEXT) table on both backends. hexus-mcp serve runs migrations on startup, same way the current Postgres migrations do.

4. MemoryStore orchestrator

The existing MemoryStore (currently a wrapper around psycopg3) becomes the backend-agnostic orchestrator. It accepts a backend in its constructor and delegates all SQL to it. The async-writer thread pattern, the cross-encoder rerank, the entity extractor — all stay at this layer, untouched.

5. Sync daemon

A new hexus-sync subcommand that runs in the background when both backends are configured:

# laptop (sqlite) ↔ homelab (postgres)
hexus-sync \
  --local-backend sqlite --local-path ~/.local/share/hexus/hexus.db \
  --remote-dsn "dbname=hexus user=postgres password=... host=homelab.lan" \
  --interval 300 \
  --mode bidirectional    # or upload_only / download_only

Sync uses the existing metadata->>'updated_at' field for last-write-wins. Mutations (confirm_memory, reject_memory, remove) are scoped to the calling agent in both modes (mirroring the existing HEXUS_MEMORY_ISOLATION semantics).

6. CLI / config changes

# Today
HEXUS_DSN="dbname=hexus user=postgres password=... host=localhost"
HEXUS_TRANSPORT=http

# Tomorrow (local mode)
HEXUS_BACKEND=sqlite
HEXUS_DB_PATH=~/.local/share/hexus/hexus.db
HEXUS_TRANSPORT=stdio
hexus-mcp serve    # no docker, no daemon

# Tomorrow (hybrid)
HEXUS_BACKEND=sqlite
HEXUS_DB_PATH=~/.local/share/hexus/hexus.db
HEXUS_SYNC_REMOTE_DSN="dbname=hexus user=postgres password=... host=homelab.lan"
HEXUS_SYNC_INTERVAL=300
HEXUS_SYNC_MODE=bidirectional
hexus-mcp serve

HEXUS_BACKEND defaults to postgres (preserves every existing deployment). Setting HEXUS_BACKEND=sqlite is opt-in.

---

What does NOT change

• embedder.py — already local (MiniLM-L6-v2)
• entity_extractor.py
• pipeline/router.py
• webhook/dispatcher.py
• ccr/cache.py
• All MCP tool schemas — backend-agnostic by design
• The hermes plugin entry point
• The Docker images (the homelab use case is unchanged)

The Postgres coupling is concentrated in store.py. That's the whole refactor surface.

---

Phased Ship Plan

Phase	What	Effort	Risk
0	Spike: prove sqlite-vec + FTS5 match pgvector recall quality on a 1k-row test set using existing benchmarks/	3 days	low
1	backends/ package + MemoryBackend Protocol + SqliteBackend covering upsert/recall/remove/confirm/stats (the 5 most-used paths in store.py)	2 weeks	medium
2	Refactor store.py → backends/postgres.py (no behavior change; rename only)	1 week	low
3	hexus migrate --from postgres --to sqlite	3 days	low
4	hexus-sync daemon (bidirectional, WAL-based, conflict resolution by (scope, updated_at))	2 weeks	medium
5	CI matrix: every test runs against both backends; SQLite tests use a tmp file in /tmp	3 days	low
6	Docs: README "Local Mode" section + docs/LOCAL_MODE.md	2 days	low

Total: ~7 weeks. Each phase ships as its own PR.

---

Why This Is Uniquely Well-Positioned

• Single maintainer. Only one piece (hexus/store.py) needs to change; no upstream coordination needed (sqlite-vec is stable, FTS5 is in SQLite core).
• The homelab stays untouched. Existing Postgres deployments keep working byte-for-byte. SQLite is purely additive.
• Dogfood. Maintainer's own workflow is exactly the use case most users will hit.
• Omarchy integration. First-class laptop-grade RAG as part of an opinionated distro is genuinely novel — no other distro has this.
• Category expansion. Today hexus is "Postgres vector memory." Tomorrow it's "Postgres vector memory or portable vector memory." The second category is ~100× the addressable market (every laptop, every air-gapped machine, every CLI agent that doesn't want to manage a DB).

---

Open Questions

1. Vector precision: drop halfvec / binary_quantize for v1? Or include them via sqlite-vec quantization? My recommendation: drop for v1, re-add only if memory pressure matters in practice.
2. Concurrent writers: SQLite's WAL allows one writer + many readers. Does the existing async-writer pattern fit, or do we need a write queue? My read: it fits — the writer thread is already serialized.
3. FTS5 stemming: porter is English-biased. Should we ship per-language tokenizers (unicode61 remove_diacritics 2) as a config? My recommendation: yes, one config knob, defaults to unicode61.
4. Sync conflict UX: last-write-wins is fine for memories (humans don't multi-edit; agents serialize via confirm_memory / reject_memory). But what if the same fact is reworded on both sides? Should we run a small LLM merge, or just take the latest? My recommendation: latest for v1, optional hexus-sync --merge-via <model> later.
5. Read-compat with Postgres schema: do we expose a hexus export --format pg-dump so users can round-trip? My recommendation: yes — it's cheap and the migration story is the gating risk for adoption.

---

Out of Scope

• Replacing the embedder (already local)
• Adding a web UI (hexus stays MCP-server-first)
• Multi-user permission model (hexus's agent_identity scope is already the right primitive)
• Replacing the hermes plugin's X-Hermes-Session-Key auth (backend-agnostic by design)

---

References

• sqlite-vec — SQLite vector search, KNN + vec0 virtual table
• SQLite FTS5 — built-in full-text search with bm25() ranking
• Omarchy — the consumer of this design (DHH's Arch+Hyprland distro)
• PR feat: add pi-agent extension for hexus vector memory #37 — the merged pi-extension work that this issue extends

---

License: BSD 3-Clause (matches repo)