db

29 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 idx_audit_log_metadata (the jsonb_path_ops GIN on audit_log.metadata, shipped by the audit_log_metadata_gin_index migration); 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 the fact hashes derived from data (string values that are exactly a fact hash, derive_cell_refs) by application code on every data 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.

• 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, and the list reads apply the view predicate (db/cell_view_sql.ts) to the far end before LIMIT — a page is up to limit edges whose far end the caller can view.

  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_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, and the list reads apply the view predicate (db/cell_view_sql.ts) to the far end before LIMIT — a page is up to limit edges whose far end the caller can view.

  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_moderation.ts

  The cell.moderation read rules, in pure and SQL form side by side.

  moderation is free TEXT with no CHECK, so each rule is an allowlist over the literals: a marker this module doesn't name (a typo, a future state) admits nothing.

  Audience admission — cell_moderation_admits_audience and cell_moderation_admits_audience_sql: unmoderated (null) or 'approved'. A cell's effective audience is its visibility (or grants) and its admission, so the public and grant read arms of can_view_cell require it, as do the editor grant arm of can_edit_cell and the matching arms of query_cell_list (general and shared_with shapes). The owner and global-admin arms don't: an author keeps reading and editing their own pending or rejected contribution, and an admin reads everything.

  Stewardship — cell_moderation_is_stewardable and cell_moderation_is_stewardable_sql: exactly 'pending' or 'rejected', the states in which the governing root's manager reads a contribution its audience can't (can_steward_cell). An unknown marker is neither admitted nor stewardable, so only its owner and admins read it.

  The pure and SQL forms are written once each here, next to each other, over the same literal constants; every predicate and query composes them rather than restating the rule. Twin of the Rust fuz_cell::cell_moderation.

• 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 derived from data on every create and update via derive_cell_refs (depth-first walk for string values that are exactly a fact hash). Callers never pass refs directly — the column is a derived projection of data, what fact serving admits a read through and what cell ref admission (auth/cell_ref_admission.ts) gates on a write.

  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/cell_view_sql.ts

  can_view_cell in SQL — the view predicate a query applies to a cell row before LIMIT, so a page holds only rows the caller may view.

  can_view_cell_sql is the one SQL form of the predicate: query_cell_list embeds it (adding the steward arm beside it), and the relation lists apply it to the cell at the other end of each edge. It binds the caller through a CellViewer (auth/cell_authorize.ts's cell_viewer_from_auth builds one from a request context), and the role-shaped grant arm reads the caller_role_grants CTE caller_role_grants_cte defines. Twin of the Rust fuz_cell::cell_view_sql.

• 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 @fuzdev/pglet-wasm (in-memory)

  pg, @electric-sql/pglite and @fuzdev/pglet-wasm are optional peer dependencies, dynamically imported only when needed. For direct driver construction without auto-detection, use db/db_pg.ts, db/db_pglite.ts or db/db_pglet.ts — an in-memory PGlite is create_pglite_db(new PGlite()).

• db/db_checkout_error.ts

  DbCheckoutError — a failed pool checkout, kept apart from a failed query.

  A request that cannot check a connection out of the pool (the wait timed out, a new connection's connect timed out or was refused, the pool is closed) finds the database unavailable; every request surface answers it with a 503 (service_unavailable), where a query that fails on a connection already checked out keeps its 500. The pool built by create_pg_pool (db/db_pg.ts) throws one from its connect, which per-statement pool.query and each transaction's pool.connect() both run through. to_thrown_jsonrpc_error (http/jsonrpc_errors.ts) maps it, so the dispatcher, the route catch, and the server's onError all answer it the same way. Twin of the Rust spine's pool.get() failures, which answer fuz_http::db_unavailable / db_unavailable_response.

  PGlite and pglet have no pool, so neither ever throws one.

• db/db_pg.ts

  PostgreSQL driver adapter for Db.

  Provides create_pg_pool() to build the pool with a bounded checkout, and create_pg_db() to construct a Db backed by it.

  Checkout timeout

  Every checkout from a create_pg_pool pool — the wait for a free client, or a new client's connect — gives up after checkout_timeout_ms (connectionTimeoutMillis; default DEFAULT_DB_CHECKOUT_TIMEOUT_MS, 5 s) and throws a DbCheckoutError (db/db_checkout_error.ts), which every request surface answers with a 503. A query that fails on a client already checked out throws the driver's error as before (a 500). Per-statement pool.query and each transaction's pool.connect() both check out through the pool's connect, so both are bounded. The wait and the connect are bounded one by one, so a checkout that waits and is then handed a slot to connect in can take about twice the bound. Db.with_checkout_timeout gives a caller its own bound on the same pool — the audit emitter's pool writes wait DEFAULT_DB_AUDIT_CHECKOUT_TIMEOUT_MS (30 s), so a pool saturated enough to shed requests does not also drop the failure rows recording them. A request that awaits its audit row — a failed login does — can therefore wait up to that bound, not the request's. Twin of the Rust fuz_db::create_pool / PoolOptions.

  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_pglet.ts

  pglet driver adapter for Db.

  Provides create_pglet_instance(), which loads @fuzdev/pglet-wasm on demand and creates an in-memory instance with PGLET_COERCION, the value coercion every fuz_app pglet instance is created with, and create_pglet_db_driver() to construct a Db backed by one. The package is imported dynamically, so it stays an optional peer.

• 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. pg.Pool, @electric-sql/pglite and @fuzdev/pglet-wasm all satisfy this interface.

  Transaction safety is provided by an injected transaction callback — the driver adapters (db/db_pg.ts, db/db_pglite.ts, db/db_pglet.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 + possession PG schema.

  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.
  • fact_holder — which actors hold which facts: one row per (actor, fact) the actor has put, the possession record cell ref admission reads.

  fact_holder.actor_id references the auth schema's actor table, so the namespace runs after fuz_auth.

  The released chain is frozen — every schema change ships as an appended migration. Migrations are tracked by (namespace, name) with no checksum, so editing a released entry in place never reruns on a database that already recorded it. Never edit, rename, reorder, or re-purpose an entry in FACT_MIGRATIONS; append a new one with idempotent DDL. Each entry is twinned with the Rust fuz_fact migration of the same name and position, statement for statement (the _testing_migration_tracker and schema-parity gates compare the two).

• db/fact_disk_storage.ts

  Filesystem CAS for fact bytes stored outside Postgres — 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). The temp is renamed over the final path even when a file is already there: the temp's bytes are the ones just hashed, so the rename is a no-op for a sound file and replaces a corrupt one (a torn write, bit rot) with bytes that match their name — a re-upload heals the CAS. .tmp/ is a sibling of <shard>/ under the same facts_dir so rename is always same-filesystem (no EXDEV).

• db/fact_holder_queries.ts

  Raw queries against fact_holder — the possession record.

  A fact_holder row says an actor has put a fact's bytes. PgFactStore writes one on every put that names a holder (HeldFactPutOptions.holder), deduplicated puts included: identical bytes put again by a second actor make that actor a holder too. Cell ref admission (auth/cell_ref_admission.ts) reads it back — a write may introduce a fact ref only for a fact the acting actor holds.

  Same discipline as db/fact_queries.ts: deps: QueryDeps first (the caller owns the transaction), no audit side effects, idempotent writes.

• 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, 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 HeldFactStore implementation.

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

  • sync hash on put; put_stream hashes as it streams
  • idempotent insert (ON CONFLICT DO NOTHING in the queries layer)
  • refs are exactly the caller's options.refs — the bytes are never scanned for hashes, whatever the content_type
  • verify-on-read for disk-backed content; embedded reads skip verify because PG storage IS the hash table
  • mismatched disk 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 as it streams, spill past the threshold, enforce max_bytes / ENOSPC). put needs disk_root + fs (the runtime/*Deps) for the over-threshold path only: without them the store has nowhere to put bytes it cannot embed, and an oversize put throws. put_stream needs fs for a body of any size — with none it throws before reading the stream — and disk_root for the over-threshold spill: with none, a body that passes the threshold throws FactPayloadTooLargeError.

  Every external_url the store writes is a file:<shard>/<rest> URL into its own disk CAS, and that CAS is the only place get reads external bytes from — the store makes no network request. The column is free text (query_put_fact inserts what it is given), so get treats a row whose external_url has any other shape, and any external row on a store with no disk CAS configured, as unavailable: a warning and null. A file: URL naming another fact's file (file_fact_url_addresses) is refused the same way, logged as an error, before the file is read.

  Holders

  Every put names its holder (HeldFactPutOptions.holder): the actor whose request carried the bytes, or null for a system put (seeds, server-side imports). A named holder gets a fact_holder row on every put — a deduplicated put included, since bringing identical bytes is possession. The fact row, its refs, and the holder row commit in one transaction. Cell ref admission (auth/cell_ref_admission.ts) reads the holder rows. The holder is a spine concept, so PgFactStore implements HeldFactStore — the @fuzdev/fuz_util FactStore reads plus puts that take HeldFactPutOptions — rather than the backend-agnostic FactStore, whose puts name no actor.

• db/file_fact_url.ts

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

  external_url on the fact row is free text (string | null), but the only shape PgFactStore writes, reads, or serves is the filesystem-minted one — any other reads as unavailable. Those 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 checks (shape, then file_fact_url_addresses), and the disk read (read_fact_bytes_from_disk) 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 (pglet) 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_columns.ts

  Helpers for the named-column projection consts (ACCOUNT_COLUMNS, CELL_COLUMNS, …) — per-table column-name arrays that every table read projects through so a dropped or leftover column fails loud instead of vanishing under SELECT *.

  Each query module exports its table's const and derives every projection it needs from it at the read site — columns_sql for single-table reads, qualify_columns for reads that alias the table, omit_columns for a client-safe subset — rather than spelling a second column list, so the drift guard on the base const (assert_columns_match_live in testing/db.ts) covers every derived projection too. Twin of the Rust spine's *_COLUMNS consts + fuz_db::qualify_columns / omit_columns.

  A read that needs a column rendered as something other than the bare reference passes a ColumnExpr — the expr override, twin of the third argument to fuz_db::qualify_columns. Every timestamp that reaches the wire projects through iso8601_timestamp_column so both spines serialize the same instant to the same bytes; each query module builds its override with iso8601_timestamp_expr over its table const. An override costs one rule — a projected ORDER BY must qualify its column — stated on qualify_columns.

  Placement rule: a const lives in its table's query module. When two query modules project the same table (the revoke cascades in auth/role_grant_queries.ts supersede role_grant_offer rows while the offer module's accept path writes role_grant rows), it lives in the table's *_ddl.ts instead — the module both already import — so the query modules stay acyclic.

  Column arrays are literal consts; the alias passed to qualify_columns is the only input these helpers treat as dynamic (and validate). Each const also carries satisfies ReadonlyArray<keyof Row> so a column name the row type doesn't know is a compile error.

• db/sql_identifier.ts

  SQL identifier validation for the places an identifier must be interpolated rather than parameterized.

  PostgreSQL parameterizes values only — table and column names in DDL (DROP TABLE, TRUNCATE, ALTER) and the alias qualify_columns prefixes onto a projection (db/sql_columns.ts) have to be string-interpolated. This validator ensures such identifiers are safe to interpolate.

• 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.