Files

glm-5.1 6aa2fcc6ff Architect storage around SQLite+Honker: remove PG, add multi-tenant identity, scoping

Reorient @alkdev/storage around a single SQLite database host with Honker
for pub/sub, event streams, and task queues. PostgreSQL is removed as a
target (ADR-038), eliminating dual schema maintenance and infrastructure
complexity. Honker provides DB + pubsub + queues in one .db file (ADR-039).

Add system/tenant DB model (ADR-040): identity tables in system.db, all
graph data in tenant-{orgId}.db files. Identity tables move from the hub
into storage (ADR-041). Scoping columns (ownerId, projectId) added to
graphs table (ADR-042). Graph types get scope (system/tenant/user) to
protect infrastructure schemas (ADR-043).

Define Drizzle-Honker session adapter (ADR-044): ~100-line adapter enabling
Drizzle typed queries and Honker pubsub/queue on a single connection with
transactional consistency.

Resolve OQ-03, OQ-04, OQ-19, OQ-21, OQ-22, OQ-23, OQ-24. Add new
open questions OQ-26 through OQ-29 for Honker integration specifics.

New docs: honker-integration.md (adapter, event patterns, migration).
Scrub all PG/jsonb/libsql references from existing spec docs.

2026-05-31 15:41:41 +00:00

2.5 KiB

Raw Permalink Blame History

ADR-038: SQLite-First, Postgres Removed

Status

Accepted

Context

The original architecture specified two database hosts: SQLite for spokes (local/embedded) and PostgreSQL for the hub (central service). This required:

Maintaining two sets of Drizzle table definitions (sqliteTable and pgTable) with the same logical shapes
Two client factories (createSqliteDatabase, createPostgresDatabase)
Two repository layer implementations or a host-agnostic abstraction
Separate test suites for each host
A PostgreSQL server as infrastructure dependency for any hub deployment

The dual-host model came from the @ade POC, which was single-tenant and didn't account for multi-tenant deployment concerns. For the actual use case — small teams of developers and AI agents sharing compute — PostgreSQL is operational overhead without proportional benefit.

Decision

@alkdev/storage is SQLite-only. The pg/ subpath export is removed. The package provides one database host: SQLite via the Honker extension (see ADR-039).

This eliminates:

All pgTable definitions and the src/pg/ directory
The PostgreSQL porting notes in every spec document
Dual schema maintenance, dual testing, dual repository implementations
PostgreSQL and Redis as infrastructure dependencies

Consequences

Positive:

Single set of table definitions, one client factory, one test suite
No PostgreSQL server to install, configure, secure, and maintain
No Redis for pub/sub — Honker provides durable pub/sub within SQLite
Simpler deployment: a single .db file per database
The hub's domain tables can coexist with metagraph tables in the same SQLite file
WAL mode with Honker's reader pool provides sufficient concurrency for the expected workload

Negative:

SQLite is single-machine — no horizontal scaling, no read replicas, no cross-server queries
No native jsonb type with GIN indexes — JSON attributes rely on json_extract() queries
No built-in full-text search on JSON attributes (SQLite FTS5 works but requires manual setup)
Some ecosystem tools expect PostgreSQL (migration tools, monitoring dashboards)
If a future deployment genuinely needs PostgreSQL scale, a migration path would need to be rebuilt

References

ADR-039: Honker as SQLite extension and pub/sub transport
ADR-040: System DB + tenant DB separation
ADR-018 (superseded): dbtype integration was partly motivated by PG/SQLite dual maintenance; with PG removed, this pressure is reduced

2.5 KiB Raw Permalink Blame History