glm-5.1
6aa2fcc6ff
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.
56 lines
3.4 KiB
Markdown
56 lines
3.4 KiB
Markdown
# ADR-041: Identity Tables in Storage Package
|
|
|
|
## Status
|
|
|
|
Accepted
|
|
|
|
## Context
|
|
|
|
The hub currently defines identity tables (`accounts`, `organizations`, `api_keys`, `audit_logs`, `organization_members`) in its own `src/storage/tables/` directory. The storage package provides only the 6 metagraph tables.
|
|
|
|
This separation creates problems:
|
|
|
|
1. The system DB (ADR-040) needs identity tables, but `@alkdev/storage` doesn't provide them
|
|
2. The hub has to maintain its own Drizzle table definitions separately, duplicating the common columns pattern and drizzlebox integration
|
|
3. The scoping columns on the metagraph tables (ADR-042) logically reference identity table rows, but those tables are defined elsewhere
|
|
4. Any consumer that wants multi-tenant graph storage needs the identity tables too — they're not hub-specific, they're infrastructure
|
|
|
|
The identity tables are NOT graph-shaped (ADR-002 established the metagraph for graph-shaped data). They are relational records with fixed schemas, indexed lookups (email uniqueness, key hash lookup), and FK constraints. But they ARE required by any deployment that uses the system/tenant DB model.
|
|
|
|
## Decision
|
|
|
|
The identity tables move into `@alkdev/storage/sqlite`:
|
|
|
|
- `accounts` — hub-local identity records
|
|
- `organizations` — top-level grouping for multi-tenancy
|
|
- `organization_members` — account/org membership (note: also modeled as `BelongsToEdge` in ACL graphs, but the SQL table provides fast indexed lookups that graph traversal cannot match)
|
|
- `api_keys` — keypal-managed API key storage
|
|
- `audit_logs` — append-only security event trail
|
|
|
|
These tables are in the storage package because they are **database infrastructure**, not hub business logic. The hub consumes them; it does not own their schema.
|
|
|
|
`organization_members` remains a SQL table despite `BelongsToEdge` existing in the ACL graph (ADR-034). The SQL table provides O(1) lookups for "list all members of org X" and FK constraints for cascading behavior. The ACL graph provides traversal-based evaluation for permission resolution. Both are needed — the SQL table is authoritative for membership state; the ACL graph edge is derived (OQ-23 resolved as "derived").
|
|
|
|
## Consequences
|
|
|
|
**Positive:**
|
|
|
|
- Single package provides everything needed for a multi-tenant graph database: metagraph tables + identity tables
|
|
- The hub doesn't duplicate table definitions or common column patterns
|
|
- `createSystemDatabase(client)` returns a fully-typed Drizzle instance with identity tables
|
|
- Other consumers (spokes, tools, standalone services) get identity tables without depending on the hub
|
|
- Referential integrity within the system DB is consistent — FK constraints work
|
|
|
|
**Negative:**
|
|
|
|
- `@alkdev/storage` grows in scope — it's no longer just "graph storage" but also "identity infrastructure"
|
|
- The hub loses ownership of its table schemas — changes require coordination with storage
|
|
- `organization_members` existing as both a SQL table and ACL graph edges requires a dual-write contract (the SQL table is authoritative; the ACL edge is derived)
|
|
- `api_keys` is tightly coupled to keypal's `Storage` interface — storage must not import keypal directly; the hub provides the adapter
|
|
|
|
## References
|
|
|
|
- ADR-040: System DB + tenant DB separation
|
|
- ADR-034: ACL as metagraph
|
|
- ADR-002: Metagraph over domain-specific tables
|
|
- Hub identity tables: `/workspace/@alkdev/hub/docs/architecture/storage/identity.md` |