db

24 modules

• db/assert_row.ts

  Assertion helper for INSERT RETURNING results.

• db/cell_audit_queries.ts

  Audit-log query for the per-cell timeline. Matches rows whose metadata jsonb names the cell on any of the keys used by cell-domain event envelopes — cell_id (cell mutations + grants), source_id / new_id (clone), source_id / target_id (cell_field events), parent_id / child_id (cell_item events).

  Each metadata @> '{...}'::jsonb clause hits the existing GIN on audit_log.metadata; Postgres bitmap-ORs the index scans together.

• db/cell_ddl.ts

  Cell PG schema.

  The universal content primitive: a single cell table whose data JSONB is interpreted by view shape. Parent→child membership and named relationships live in two sibling tables — cell_item (ordered children, fractional-indexing keyed) and cell_field (named edges). refs text[] carries blake3: fact hashes auto-extracted from data by application code on every write.

  Soft delete via deleted_at. Most indexes are partial on deleted_at IS NULL so active-cell queries skip tombstones.

  path is the global namespace axis — a partial unique index enforces uniqueness across all active rows (PostgreSQL UNIQUE constraints don't support WHERE clauses, so it's expressed as a partial unique index). It additionally filters on deleted_at IS NULL so a soft-deleted cell doesn't block reuse of its path. Path writes are admin-only at the action layer; user-namespaced paths are a future extension.

  Ownership columns (created_by, updated_by) are nullable FKs to actor: NULL = system origin (well-known cells, daemon/agent cells). The non-admin authz path treats NULL created_by as admin-only via an explicit equality check (auth/cell_authorize.ts).

  Timestamp naming (created_at, updated_at) aligns with fuz_app's _at-everywhere convention used by account, actor, audit_log, role_grant, etc.

  Single-migration shape: full_cell_schema creates the canonical cell + cell_grant + cell_field + cell_item layout in one shot from the live exported constants. The dormant cell_history table lives in the separate fuz_cell_history namespace (cell_history_ddl.ts).

• db/cell_field_queries.ts

  Raw queries against the cell_field table.

  Named-relation primitive: each row is (source_id, name) → target_id, a JSON-object-shaped edge from one cell to another. (source_id, name) is unique — one target per name per source. Multiplicity by composition (target a collection cell whose items[] are the multi-valued tags), not by allowing duplicate field rows.

  Reads filter both endpoints by cell.deleted_at IS NULL so relations dangling off a soft-deleted cell don't surface to the live graph.

  query_cell_field_set upserts on the (source_id, name) PK so re-pointing a name updates target_id in place — JSON-object semantics (obj.foo = bar overwrites whatever was there).

• db/cell_grant_queries.ts

  Raw queries against the cell_grant table.

  Resource-side ACL for cells: each row admits a principal at a level (viewer | editor). Principal is discriminated by which columns are set — actor_id (single actor) xor (role, scope_id?) (any holder of a matching role_grant). Owner is implicit on cell.created_by and never appears in this table.

  Convention: deps: QueryDeps first, no audit side effects, mutations return the affected row (or null for not-found).

  query_cell_grant_create upserts on the relevant partial unique index so re-granting the same principal updates level rather than producing duplicate rows. The two principal shapes use different indexes:

  • Actor-shaped: idx_cell_grant_unique_actor on (cell_id, actor_id).
  • Role-shaped: idx_cell_grant_unique_role_scope on (cell_id, role, scope_id) with NULLS NOT DISTINCT so two (role, NULL) grants on the same cell collide.

• db/cell_history_ddl.ts

  Cell history PG schema (schema only).

  Lightweight references to cell state snapshots. Heavy data (serialized cell bytes) lives in the fact store; fact_hash points there. The snapshot lifecycle (when to serialize, hash, store, and record) is deferred to a future iteration — this only stages the table so downstream code can target a stable schema. The table ships present-but-unwritten.

  fact_hash is intentionally not a foreign key to fact(hash) — snapshots may be evicted by GC policy while history rows remain as audit traces, and federation may target facts on another instance.

  Depends on CELL_MIGRATION_NS (FK on cell.id).

• db/cell_item_queries.ts

  Raw queries against the cell_item table.

  Ordered-child membership: each row is (parent_id, position) → child_id. position is opaque text (fractional-indexing key) — lex ordering is the contract. The PK on (parent_id, position) enforces one cell per slot but allows the same child_id to appear at multiple positions (the primitive is JSON-array-shaped — ordered multiset, not set; domain dedup rules ride on top).

  Reads filter both endpoints by cell.deleted_at IS NULL so items dangling off a soft-deleted cell don't surface.

  query_cell_item_insert returns the inserted row OR throws the underlying 23505 (Postgres unique violation) on a `(parent_id, position)` collision. Handlers convert this into the cell_item_position_taken JSON-RPC error so the client retries with a refreshed bracket.

• db/cell_queries.ts

  Raw queries against the cell table.

  Convention: deps: QueryDeps first, no audit side effects, mutations return the affected row (or null for not-found).

  cell.refs is auto-extracted from data on every create and update via fact_hash_extract_refs (depth-first walk for blake3:-prefixed strings). Callers never pass refs directly — the column is a derived projection of data for cells-by-fact discovery, mirroring what a fact store does for JSON facts.

  Soft delete via deleted_at. All get / list queries exclude tombstones by default; include_deleted: true opts in for admin / audit views.

  path uniqueness is global, enforced by idx_cell_path_unique (partial on active rows). Path reuse after soft delete falls out of the partial index — queries do not need special handling.

• db/create_db.ts

  Database initialization with driver auto-detection.

  Selects the appropriate database driver based on database_url:

  • postgres:// or postgresql:// — uses pg (PostgreSQL)
  • file:// — uses @electric-sql/pglite (file-based)
  • memory:// — uses @electric-sql/pglite (in-memory)

  Both pg and @electric-sql/pglite are optional peer dependencies, dynamically imported only when needed. For direct driver construction without auto-detection, use db/db_pg.ts or db/db_pglite.ts.

• db/db_pg.ts

  PostgreSQL driver adapter for Db.

  Provides create_pg_db() to construct a Db backed by a pg.Pool. Statically imports only pg types; the pg runtime is dynamically imported where needed — by callers (e.g., create_db) that construct the Pool, and by register_pg_type_parsers to reach pg.types. pglite-only consumers never reach either path, so pg stays an optional peer dep.

• db/db_pglite.ts

  PGlite driver adapter for Db.

  Provides create_pglite_db() to construct a Db backed by @electric-sql/pglite. Only imports PGlite types — the actual package is dynamically imported by callers (e.g., create_db) that construct the PGlite instance.

• db/db.ts

  Database wrapper with duck-typed interface.

  Accepts any client with a query(text, values) method. Both pg.Pool and @electric-sql/pglite satisfy this interface.

  Transaction safety is provided by an injected transaction callback — the driver adapters (db/db_pg.ts, db/db_pglite.ts) supply the driver-appropriate implementation. Close is handled externally (returned alongside the Db as DbDriverResult), not as a method on this class.

• db/fact_ddl.ts

  Fact + memo PG schema.

  Three tables:

  • fact — content-addressed bytes. hash = 'blake3:<hex64>'. Either embedded (bytes) or referenced (external_url); the CHECK constraint enforces exactly one populated. Idempotent: same bytes always produce the same hash, so INSERT … ON CONFLICT DO NOTHING is the put primitive.
  • fact_ref — declared dependency edges (source fact → target fact). target_hash is intentionally not a foreign key: in federation a reference may target a fact stored on another instance.
  • memo — (fn_id, input_hash) → output_hash for memoized computations.

• db/fact_disk_storage.ts

  Filesystem CAS for externally-stored fact bytes — the disk half of PgFactStore, threaded over the injectable runtime/*Deps rather than raw node:fs, so it runs unchanged under Node, Deno, and a mock runtime.

  Large facts (over the embedded threshold) live on disk at the canonical sharded layout <facts_dir>/<shard>/<rest> — <shard> is the first 2 hex chars of the blake3 digest, <rest> the remaining 62 — with the fact row carrying external_url = file:<shard>/<rest> (disk-root-relative). The layout is single-sourced by fact_disk_path in db/file_fact_url.ts, so the write path here and the URL minted into the row can't drift. The TS twin of the Rust fuz_fact disk CAS.

  Writes land through <facts_dir>/.tmp/<rand>.tmp, are fsynced, then renamed into the content-addressed final path. The rename is atomic on POSIX (a *concurrent reader* observing the path sees either the full content or nothing), but atomicity is not durability — the fsync before the rename is what guards against a *host crash* leaving a torn/zero file at a published CAS path, because the serving path streams the hash-named file without re-hashing it (server/serve_fact_route.ts). This twins the Rust fuz_fact §fsync posture: data-sync before the rename; the parent-dir fsync stays deliberately waived (a lost dirent is regenerable under content addressing). If the final path already exists the temp is dropped instead of renamed over — idempotent dedup (same hash → byte-identical content), mirroring the Rust commit path. .tmp/ is a sibling of <shard>/ under the same facts_dir so rename is always same-filesystem (no EXDEV).

• db/fact_queries.ts

  Raw queries against the fact and fact_ref tables.

  Convention: deps: QueryDeps first, no audit side effects, mutations are idempotent (ON CONFLICT DO NOTHING) so the same hash can be written by two callers without the second observing an error.

  Higher-level lifecycle (verify-on-read, JSON ref auto-extraction, embedded-vs-referenced selection) lives in db/fact_store.ts. Queries here are deliberately mechanical.

• db/fact_store_errors.ts

  Typed errors thrown by PgFactStore.put_stream so a file-store route can map them to the canonical wire responses.

  The Rust twin uses FactError::PayloadTooLarge / ::StorageFull (fuz_fact); these TS classes carry the same two cases so the upload handler can branch identically and return the same status + body shape (413 / 507).

• db/fact_store.ts

  PG-backed FactStore implementation.

  Wraps the raw queries in db/fact_queries.ts with the lifecycle the FactStore interface promises:

  • sync hash on put, stream hash on put_ref (counting bytes against the caller-supplied size)
  • idempotent insert (ON CONFLICT DO NOTHING in the queries layer)
  • JSON ref auto-extraction when content_type signals JSON and the caller didn't pass an explicit refs array
  • verify-on-read for external content; embedded reads skip verify because PG storage IS the hash table
  • mismatched external bytes return null + log warning (treat as unavailable; GC / repair is a separate concern)

  Embedded vs disk split: writes route by size. Bytes <= embedded_threshold land in the PG bytes column; larger bytes go to the disk CAS at <facts_dir>/<shard>/<rest> (db/fact_disk_storage.ts) and the row records a file:<shard>/<rest> external_url. put takes fully-buffered bytes; put_stream is the bounded-memory streaming twin (hash BLAKE3 + SHA-256 in one pass, spill past the threshold, enforce max_bytes / ENOSPC). Both need disk_root + fs (the runtime/*Deps) configured for the over-threshold path; without them, an oversize put throws and the caller must put_ref against an externally-managed URL (federation / stub-fetcher tests).

• db/file_fact_url.ts

  Canonical filesystem-fact URL shape + on-disk layout.

  external_url on the generic fact row is string | null because the FactStore interface stays federation-friendly (future https://... / s3://... shapes). Filesystem-minted URLs are exactly file:<shard>/<rest> where <shard> is the first 2 hex chars of the blake3 digest and <rest> the remaining 62 — files land at <facts_dir>/<shard>/<rest> after the writer atomically temp+renames them in.

  Centralizing the regex + the fact_disk_path split keeps the shape in one place: PgFactStore's disk CAS (db/fact_disk_storage.ts), the serve_fact_route defense-in-depth check, and the file_fact_fetcher resolver all derive the layout here, so the write path and the read path can't drift. The TS twin of the Rust fact_disk_path (fuz_fact).

  Defense-in-depth: a .. segment can't match (. isn't in [0-9a-f]), neither can absolute paths, query strings, or any non-hex character. Used in front of path.join so the resolver never trusts the URL came from a fact row, even though it always does in practice.

• db/migrate.ts

  Identity-tracked database migration runner.

  Migrations are named {name, up} objects in ordered arrays, grouped by namespace. A schema_version table records one row per applied migration — (namespace, name, sequence, applied_at) — and the runner verifies the applied list is a name-prefix of the code's migration array at boot.

  name is descriptive identity, not a version ordinal. The runner orders by the sequence column and matches name by array position — it never parses the name — so name carries *what the migration is* (full_cell_schema, role_grant_offer_and_scoped_role_grants), and the redundant _vN ordinal is avoided (it would only duplicate sequence, and a cell_v0 name tempts editing "v0" in place). name is half the schema_version PK (with namespace), so a rename is an identity change every already-migrated DB sees as name-divergence-at-N. This is also the cross-impl contract: the TS and Rust spines must record byte-identical (namespace, name, sequence) so a consumer can swap backends over one DB.

  Schema is not stabilized yet — append-only is NOT the rule. While fuz_app is pre-stable, migration bodies, names, and positions can change freely between versions; consumers upgrading across a schema change are expected to drop and re-bootstrap their dev/test databases (production deployments are not yet a supported use case). Once the schema is declared stable a hard append-only-after-publish rule will apply and the cliff will be called out in that release's notes; until then, body edits to a published migration slip past the runner (no content hashing) by design — they're the recommended way to evolve the schema.

  Chain-level transactions: All pending migrations in a namespace run in a single transaction. Any failure rolls back every migration in that run — no partial-state recovery. This rules out non-transactional DDL (e.g., CREATE INDEX CONCURRENTLY); run those out of band.

  Chain idempotency, not migration idempotency: the chain-tx wraps every migration replayed in a single boot, so an individual migration may temporarily produce intermediate state that a later migration reverses (e.g. v0's ROLE_GRANT_INDEXES recreates an index that v1 drops; chain-tx hides this from observers). What matters is that the *committed end state* matches; the in-tx steps may not be individually idempotent against an arbitrary mid-chain target.

  Forward-only: No down-migrations. Schema changes are additive.

  Advisory locking: Per-namespace pg_advisory_lock reduces contention in multi-instance deployments — best-effort, not load-bearing. The locks are session-scoped, but Db.query runs against a pool that may check out a different backend per call, so two concurrent boots can both "hold" the lock on different sessions. The real serialization comes from chain- tx atomicity + the (namespace, name) PK on schema_version: the loser's INSERT hits a PK violation, the chain-tx rolls back, and the next boot reads the committed state and proceeds cleanly. Environments without pg_advisory_lock (some PGlite versions) silently fall through.

• db/pg_error.ts

  PostgreSQL error utilities.

  Works with both pg and @electric-sql/pglite — both set .code on error objects using standard PostgreSQL error codes.

• db/query_deps.ts

  Shared query dependency type.

  All query_* functions take deps: QueryDeps as their first argument. Widened per-function when additional capabilities are needed (e.g., log for token validation).

• db/schema_ready.ts

  Readiness probe core: live-DB schema-drift detection.

  /health is a dumb liveness probe (no DB). /ready is the deploy gate — it introspects the live database's column set and compares it against a committed expected column map (what a fresh full migration-chain bootstrap produces). A live DB missing an expected column is exactly the failure mode that silently broke login when the auth schema gained account.deleted_at via an in-place base-DDL edit instead of an appended migration: the deployed code required a column an older bootstrapped DB never got, and a SELECT * + JS deleted_at === null filter rejected every account. The /ready route (http/common_routes.ts) turns that drift into a loud 503 so a deploy poll rolls the release back instead of promoting code that can't authenticate anyone. The discipline that prevents the drift is the frozen append-only migration chain (auth/migrations.ts); this probe is the runtime net for a lapse.

  The check is intentionally column-presence only — not type / constraint / index parity. Column names are DDL-deterministic and engine-portable, so a map generated against PGlite at gen-time compares exactly against a live Postgres at runtime; finer-grained parity would false-positive across the two engines, and a false positive here means a rolled-back deploy — an outage you caused. Full structural parity stays the dev-time cross-backend schema-snapshot suite's job (testing/schema_introspect.ts). In-place *type* changes (a column kept by name, retyped) are out of scope — they rely on the query-time column-named failures instead.

  This module is pure DB introspection + comparison: no HTTP, no filesystem, no fixture-path knowledge. The route factory and the committed-fixture loader live in http/common_routes.ts; the gen-time fixture-regeneration helper lives in testing/schema_ready_fixture.ts.

• db/sql_identifier.ts

  SQL identifier validation for dynamic DDL queries.

  PostgreSQL DDL operations (DROP TABLE, TRUNCATE, ALTER) do not support parameterized table/column names — only values can be parameterized. This validator ensures identifiers are safe for string interpolation in those specific cases.

• db/status.ts

  Database status utility for CLI and dev workflows.

  Queries migration state and table info without a running server. Returns structured data that consumer scripts can print however they like.

  The migration check is name-divergence aware: it name-prefix-verifies the applied migrations against the code's list (mirroring run_migrations), so a divergent history (same count, different names) reports a divergence and renders DIVERGED rather than a false up_to_date.