db/cell_queries.ts

Input for query_cell_create. refs is derived from data. kind is the write-once capability axis (cell.kind column) — set here at INSERT and absent from CellUpdatePatch, so it can never change post-create. parent_id / root_id / moderation are the directory-tree + lifecycle columns the handler derives (parent containment, governing root, the create-authorizer verdict) — likewise write-once / control-gated, never on CellUpdatePatch.

Common pagination + tombstone-visibility options for list queries.

Parameters for query_cell_list. All filter dimensions are optional.

Row shape returned by cell SELECTs. data is typed as CellData — the storage layer trusts the wire validation; the row is what was written, and the wire validates CellData on every write.

Parent↔child membership and named relations live in the cell_item / cell_field sibling tables (see db/cell_item_queries.ts / db/cell_field_queries.ts). The cell row carries identity + content only.

grant_count is a derived projection (correlated subquery against cell_grant keyed by cell_id, served by idx_cell_grant_cell) — not a table column. New cells naturally land at 0.

Patch for query_cell_update. Fields left undefined are unchanged; path may be explicitly set to null to clear. refs is re-derived from data whenever data is updated.

Insert a cell row, deriving refs from data.

updated_by is left NULL on insert — same convention as updated_at (NULL until first update). The "last modifier" stamp is meaningful only after a real edit; copying the creator's id into updated_by at create time would make a no-op update by a different actor look authored by the creator.

Soft-delete a cell. Sets deleted_at = NOW(), updated_at = NOW(), and updated_by = options.deleted_by (or NULL). No-op when the row is already deleted.

Fetch a cell by id. Excludes soft-deleted rows by default.

Fetch a cell by path. Excludes soft-deleted rows; the global partial unique index on path WHERE deleted_at IS NULL guarantees at most one result.

Filterable list query for the generic cell_list RPC.

Takes a flat filter shape (single optional clause per dimension; the cell_list API explicitly does NOT support OR'd alternatives within a dimension — keep it simple) plus an optional viewer-aware visibility predicate.

The visibility predicate mirrors can_view_cell in SQL form:

The grants branch closes parity with can_view_cell: a SQL EXISTS over cell_grant, parameterized by the caller's actor_id and the parallel (role[], scope_id[]) projection of auth.role_grants. The caller's role_grants are materialized once via a caller_role_grants CTE so the role-grant unnest isn't re-scanned per outer row. Empty role_grant arrays are fine: the CTE yields zero rows, the inner EXISTS returns false, and the actor-grant branch still fires for actor-shaped grants.

shared_with_caller_only: true (shared_with: 'me' at the wire layer) takes a different SQL shape: instead of layering an extra conjunction on the cell-driven scan, it semi-joins through cell_grant, letting the planner drive from the (typically tiny) admitted-grant set via idx_cell_grant_actor / idx_cell_grant_role_scope rather than scanning every cell row. For a sharee with N grants over a table of M cells, the cost drops from O(M) to O(N + matched-cells). Owner-is-implicit (a cell's owner never appears as a grant principal) means the grants branch is itself owner-excluding, but the explicit created_by IS DISTINCT FROM caller guards against any future deviation. The shared_with branch does NOT bypass for admin: an admin asking "what's shared with me" wants their own grant footprint, not every cell.

Soft-deleted rows are excluded by default; opt-in via include_deleted.

List active cells created by an actor, newest first. Backed by the idx_cell_created_by partial index.

List active cells with the given kind, newest first. Uses the idx_cell_kind index (cell.kind = ?).

Bulk-load active cell rows by id, no visibility filter applied. Used by the strict relation-read filter (auth/cell_relation_visibility.ts's filter_visible_target_ids), which runs can_view_cell per row in memory rather than in SQL. Soft-deleted rows are excluded so relations to tombstones never surface.

Transition a contribution's moderation marker (the cell_moderate verb's write). On set_visibility_public (an approval) it also flips visibility to 'public' so the approved contribution goes live; a rejection leaves visibility untouched (stays private). updated_at / updated_by are stamped.

moderation is deliberately not writable through query_cell_update (it's absent from CellUpdatePatch) — gating the transition on a dedicated query (with the manage-tier authority check in the handler) is what stops an author self-approving their own pending cell.

Update a cell. Fields left undefined in the patch keep their existing value; explicit null writes NULL. refs is re-derived from data whenever the patch updates data. updated_at is bumped to NOW() on every successful update.