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

view source

Declarations
#

13 declarations

CELL_DROP_TABLES
#

db/cell_ddl.ts view source

readonly ["cell_field", "cell_item", "cell_grant", "cell"] import {CELL_DROP_TABLES} from '@fuzdev/fuz_app/db/cell_ddl.js';

Tables created by CELL_MIGRATION_NS, in drop order (children first).

CELL_FIELD_INDEXES
#

db/cell_ddl.ts view source

string[] import {CELL_FIELD_INDEXES} from '@fuzdev/fuz_app/db/cell_ddl.js';

cell_field indexes.

PK on (source_id, name) covers forward lookup ("what does this cell point to via field X?") and the per-source fields list.
idx_cell_field_target covers reverse lookup ("which cells link to this target?").

Soft-delete is filtered by JOIN at the read boundary; no partial indexes here on deleted_at (would force index churn on cell soft-delete toggles, and the join filter is sufficient).

CELL_FIELD_SCHEMA
#

db/cell_ddl.ts view source

"\nCREATE TABLE IF NOT EXISTS cell_field (\n\tsource_id UUID NOT NULL REFERENCES cell(id) ON DELETE CASCADE,\n\tname TEXT NOT NULL,\n\ttarget_id UUID NOT NULL REFERENCES cell(id) ON DELETE CASCADE,\n\tcreated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n\tPRIMARY KEY (source_id, name)\n)"

import {CELL_FIELD_SCHEMA} from '@fuzdev/fuz_app/db/cell_ddl.js';

cell_field table — named relation ((source_id, name) → target_id). One target per name per source — JSON-object keys are unique. Multiplicity is expressed by composition (foo.tags = collection_cell whose items[] are the tags), not by allowing duplicate (source_id, name) rows.

CELL_GRANT_INDEXES
#

db/cell_ddl.ts view source

string[] import {CELL_GRANT_INDEXES} from '@fuzdev/fuz_app/db/cell_ddl.js';

cell_grant indexes.

idx_cell_grant_cell: forward lookup ("who has access to this cell?").
idx_cell_grant_actor: reverse lookup ("which cells does this actor have access to?").
idx_cell_grant_role_scope: reverse lookup for role-shaped principals.
idx_cell_grant_unique_actor: prevents duplicate actor-shaped grants for the same cell. Re-granting updates level via UPSERT on this index.
idx_cell_grant_unique_role_scope: same, for role-shaped grants. NULLS NOT DISTINCT so two rows with the same (cell_id, role) and scope_id IS NULL collide — without it, default NULL-distinct semantics would let duplicate null-scope role grants slip past the re-share UPSERT path. Requires PostgreSQL 15+ (pglite tracks PG 16).

CELL_GRANT_SCHEMA
#

db/cell_ddl.ts view source

"\nCREATE TABLE IF NOT EXISTS cell_grant (\n\tid UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n\tcell_id UUID NOT NULL REFERENCES cell(id) ON DELETE CASCADE,\n\tlevel TEXT NOT NULL CHECK (level IN ('viewer', 'editor')),\n\tactor_id UUID REFERENCES actor(id) ON DELETE CASCADE,\n\trole TEXT,\n\tscope_id UUID,\n\tgrante...

import {CELL_GRANT_SCHEMA} from '@fuzdev/fuz_app/db/cell_ddl.js';

cell_grant table — resource-side ACL for cells. Each row admits a principal (actor or (role, scope_id)) at a level (viewer or editor). Owner is implicit (cell.created_by); the table never carries owner rows.

The single-principal arm is actor-grain (actor_id FK); the other arm is role-shaped ((role, scope_id)). The CHECK enforces exactly one arm.

CELL_INDEXES
#

db/cell_ddl.ts view source

string[] import {CELL_INDEXES} from '@fuzdev/fuz_app/db/cell_ddl.js';

Cell indexes — all active-only, partial on deleted_at IS NULL.

idx_cell_active: active-cell list/scan ordered by creation.
idx_cell_path_unique: global path uniqueness + read-side path lookup. Partial on path + active so reused paths after soft delete are allowed.
idx_cell_kind: the cell_list kind filter (cell.kind = ?) and kind-scoped scans. Active-only.
idx_cell_data: shape-driven queries (`data

CELL_ITEM_INDEXES
#

db/cell_ddl.ts view source

string[] import {CELL_ITEM_INDEXES} from '@fuzdev/fuz_app/db/cell_ddl.js';

cell_item indexes.

PK on (parent_id, position) covers ordered scans for the per-parent items list (SELECT ... ORDER BY position).
idx_cell_item_child covers reverse lookup ("which parents contain this child?").

CELL_ITEM_SCHEMA
#

db/cell_ddl.ts view source

"\nCREATE TABLE IF NOT EXISTS cell_item (\n\tparent_id UUID NOT NULL REFERENCES cell(id) ON DELETE CASCADE,\n\tposition TEXT NOT NULL,\n\tchild_id UUID NOT NULL REFERENCES cell(id) ON DELETE CASCADE,\n\tcreated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n\tPRIMARY KEY (parent_id, position)\n)"

import {CELL_ITEM_SCHEMA} from '@fuzdev/fuz_app/db/cell_ddl.js';

cell_item table — ordered child membership keyed by an opaque fractional-indexing string. (parent_id, position) PK enforces one cell per slot; the same child_id may appear at multiple positions (the primitive is JSON-array-shaped — ordered multiset, not set). Domain dedup rules ride on top in helpers.

CELL_MIGRATION_NAMESPACE
#

db/cell_ddl.ts view source

"fuz_cell" import {CELL_MIGRATION_NAMESPACE} from '@fuzdev/fuz_app/db/cell_ddl.js';

Namespace identifier for cell migrations.

CELL_MIGRATION_NS
#

db/cell_ddl.ts view source

MigrationNamespace import {CELL_MIGRATION_NS} from '@fuzdev/fuz_app/db/cell_ddl.js';

Migration namespace consumed by run_migrations.

CELL_MIGRATIONS
#

db/cell_ddl.ts view source

Migration[] import {CELL_MIGRATIONS} from '@fuzdev/fuz_app/db/cell_ddl.js';

Cell migrations.

CELL_SCHEMA
#

db/cell_ddl.ts view source

"\nCREATE TABLE IF NOT EXISTS cell (\n\tid UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n\tdata JSONB NOT NULL,\n\tkind TEXT,\n\tvisibility cell_visibility NOT NULL DEFAULT 'private',\n\tpath TEXT,\n\trefs TEXT[],\n\tparent_id UUID REFERENCES cell(id) ON DELETE SET NULL,\n\troot_id UUID REFERENCES cell(id) ON DELETE ...

import {CELL_SCHEMA} from '@fuzdev/fuz_app/db/cell_ddl.js';

cell table — universal content primitive: identity + content only. Parent→child membership lives in cell_item; named relations live in cell_field. Includes the created_by / updated_by ownership columns.

visibility is the access-control axis — private (default) is restricted to admin / owner / cell_grant-admitted callers; public admits everyone, including unauthenticated visitors. Lives as a top-level column so the auth predicate reads off the row directly rather than reaching into data.

path is the global namespace axis (no tenant/hub scoping) — globally unique on active rows via idx_cell_path_unique.

kind is the capability / identity axis — a nullable top-level column (peer to visibility / path), not a field inside data. It is the discriminator a creation authorizer gates on (see auth/cell_actions.ts CellCreateAuthorize) and is write-once: set at INSERT and carried on no update path, so a cell's kind is fixed at birth. Content stays duck-typed in data; kind is a capability tag, not a content-type.

parent_id / root_id are the directory tree (containment): parent_id is the immediate container (nullable self-FK; NULL = a root), root_id is the governing root denormalized for flat-subtree queries (`root_id = parent.root_id ?? parent.id, so a root has NULL`). Both are set once at create and immutable in v1 (carried on no update path). moderation (nullable text — pending / approved / rejected; NULL = unmoderated) is the approval-lifecycle marker, peer to visibility (a control field with a non-author writer — see auth/cell_actions.ts), never inside data.

CELL_VISIBILITY_TYPE
#

db/cell_ddl.ts view source

"\nDO $$ BEGIN\n\tCREATE TYPE cell_visibility AS ENUM ('private', 'public');\nEXCEPTION WHEN duplicate_object THEN NULL;\nEND $$"

import {CELL_VISIBILITY_TYPE} from '@fuzdev/fuz_app/db/cell_ddl.js';

cell_visibility enum — access-control axis for a cell. Lives as a top-level column (not inside data) because visibility is access control, not content metadata. cell_grant is the other ACL surface; keeping visibility as a peer column (not a JSON field) co-locates access-control state and lets the planner reason about it directly.

Ships with two states ('private', 'public'); a third (unlisted / public-link) folds in via ALTER TYPE when public-link sharing lands.

Wrapped in a DO block so the migration can replay idempotently — CREATE TYPE has no IF NOT EXISTS variant in PostgreSQL.