Phase 6 — Relations — Task Specifications (TASK-0081 … TASK-0090)

Purpose. Task spec sheet for Phase 6 of ROADMAP.md: relations (1:1 / 1:N / M:N, bidirectional), rollups (COUNT/SUM/AVERAGE/MIN/MAX/PERCENT/UNIQUE), the safe formula engine (parser → AST → evaluator, zero eval), formula/rollup properties with event-driven recompute, database buttons, and sub-items — per PRODUCT-SPEC §9.4–9.7, docs/product/functional-requirements.md §5.5, and the Phase-5 handoff contract recorded in docs/tasks/phase-05-databases.md (TASK-0080 R4: query endpoint, codec registry, cell renderers, record event shapes, dbmeta cache, view-settings contract).

Phase exit criteria (verbatim from ROADMAP.md, Phase 6):

• [ ] Relations (1:1, 1:N, M:N, bidirectional) + relation links
• [ ] Rollups (COUNT, SUM, AVERAGE, MIN, MAX, PERCENT, UNIQUE)
• [ ] Formula engine (safe AST evaluator; if/and/or/not/sum/average/min/max/ dateAdd/dateBetween/now/concat/replace/contains/length + prop())
• [ ] Database buttons (set property, create/update/archive/duplicate page, webhook, trigger automation)
• [ ] Sub-items & dependency tracking for computed properties
• [ ] Exit: formula engine 100% function coverage, zero eval; rollups recompute on relation change (event-driven); button actions E2E-tested.

Task table

ID	Title	Estimate
TASK-0081	Relations API + model (cardinality, bidirectional, link maintenance, cascade)	L
TASK-0082	Relation property editor (multi-select dialog, bidirectional side)	M
TASK-0083	Rollups (7 aggregates, on-read compute + Redis cache, event invalidation)	L
TASK-0084	Formula engine (tokenizer → Pratt parser → AST → safe evaluator, fuzz)	L
TASK-0085	Formula property (config, AST storage, editor UI with autocomplete + preview)	M
TASK-0086	Formula/rollup evaluation pipeline (worker recompute, depth limit, cycle guard)	L
TASK-0087	Database buttons (7 action types, execution endpoint, button UI)	L
TASK-0088	Sub-items (isSubItem, child list in parent page, rollup dependency across sub-items)	M
TASK-0089	E2E relations suite (tests/e2e/relations.spec.ts)	L
TASK-0090	Formula engine coverage + phase-exit verification (zero-eval CI gate)	M

Phase Completion Checklist (evidence recorded by TASK-0090)

• [ ] Relation CRUD + links integration-tested for all 3 cardinalities × bidirectional on/off (TASK-0081)
• [ ] Rollup values correct for all 7 aggregates (golden datasets) and recompute within 5 s of a linked change (event-driven, TASK-0083/0086, E2E-verified in TASK-0089)
• [ ] Formula engine: 100% function coverage (every function has golden tests), 100-expression fuzz + type-matrix tests green, zero eval/new Function in packages/database-engine (CI grep gate)
• [ ] Formula recompute on dependency change (event-driven, TASK-0086)
• [ ] Button actions E2E-tested (set property, create page, update page, archive page, duplicate page; webhook/automation = documented stubs calling the Phase-11/13 contracts)
• [ ] Sub-items render in the parent record's page; rollups count across sub-items (TASK-0088)
• [ ] Every task (TASK-0081…0090) closed against its Definition of Done (AGENTS.md)

Recorded notes (conflicts/adjustments, per AGENTS.md):

1. The phase brief's button action list names send_webhook (stub Phase 13) and trigger_automation (stub Phase 11); functional-requirements §5.5 has both as real actions "enqueued asynchronously." Resolution: Phase 6 implements the execution plumbing (the action type is accepted, validated, and executed as an enqueue against the Phase-11/13 queue names — automation BullMQ queue and webhook-deliveries flow) with the worker-side executors stubbed to NOT_IMPLEMENTED-marked jobs that log and no-op until their phases land; the button result report shows "queued" (not "executed") for those two types until then. This keeps the execution endpoint's contract stable for Phase 11/13.
2. rollups.recompute: the brief says "computed on read with Redis cache (TTL 60 s) + invalidation via domain events"; database-architecture §1.4 says computed values are "computed at query time for small sets, event-driven precomputed into a materialized cache for large ones." Resolution (both, by dataset size): on-read compute is the default path; when a database exceeds ROLLUP_MATERIALIZE_THRESHOLD (5,000 records with ≥ 1 rollup, env-tunable), the TASK-0086 worker precomputes into record_properties rows (the schema already supports it — record_properties.value for a ROLLUP property) and queries read the materialized rows; invalidation events flush both the Redis cache and the materialized rows. Recorded so the two docs agree.
3. Formula now() is documented nondeterministic (re-evaluated on each read) per functional-requirements §5.5; the materialized cache therefore excludes formulas containing now() (they always compute on read) — the dependency scanner flags the AST, not just the dependencies list.
4. Relation values are NOT stored in record_properties — links live in relation_links (database-architecture §1.4, uniform M:N join table; 1:1/1:N are service-layer count assertions). Phase 5's "computed values 422" contract for the RELATION property is lifted in this phase: PATCH with a relation value is now accepted (it maps to link operations) — the 422 applies only to ROLLUP/FORMULA.

---

TASK-0081

Title

Relations API + model (cardinality, bidirectional reverse property, link maintenance, cascade)

Objective

Implement relation creation and link management end-to-end: POST /v1/databases/:id/relations creates the Relation row (+ the RELATION property on both databases when bidirectional), cardinality enforcement (1:1 replacement semantics, 1:N single-side assertion), the relation_links join table maintenance (add/remove/replace in one transaction), reverse-link maintenance (setting links from either side keeps both sides consistent by construction — one row, two reads), and cascade rules (record delete → links removed; relation delete → links removed + properties soft-deleted).

Background

PR-075/076 P0. Schema (already present): relations (databaseId → targetDatabaseId, cardinality ONE_TO_ONE/ONE_TO_MANY/MANY_TO_MANY, bidirectional, propertyId? this side, reversePropertyId? counterpart, name/targetName per-side display names); relation_links (uniform M:N join: databaseId × recordId × targetDatabaseId × targetRecordId unique, plus the reverse-lookup index). database-architecture §1.4: "Cardinality as policy, not constraint… 1:1 ('exactly one target') and 1:N ('many on one side') are validated in RelationService.setLinks (transactional delete-then-insert with count asserts)." functional-requirements §5.5: "ONE_TO_ONE: linking a second target on the 'one' side unlinks the previous — API returns the replaced link"; "Deleting a record removes its links (cascade); deleting a relation property removes all its links." Events: relation.linked / relation.unlinked (backend-architecture §7.2 — consumed by TASK-0086 recompute and Phase-13 webhooks). Phase-5 handoff (TASK-0080 R4): the RELATION property type exists in the schema with config: {} placeholder; this task fills config with {relationId, multiple} and wires the cell (TASK-0082).

Requirements

• R1. API (apps/api/src/modules/relations/ — the RelationModule, per the module table #8):
  • POST /v1/databases/:id/relations — body {name (≤ 100, unique per db), targetDatabaseId (same workspace, ≠ self — 400 self_relation), targetName? (display name on the other side, default = this db's title), cardinality (default MANY_TO_MANY), bidirectional (default true)} → transaction: this side's RELATION property (config: {relationId, multiple: cardinality !== ONE_TO_ONE}, position appended) → when bidirectional: the reverse RELATION property on the target db (name = targetName ?? <this db title>, config: {relationId, multiple: per the mirrored cardinality}) → the Relation row (propertyId, reversePropertyId) → schemaVersion bump on both databases + database.updated × 2. Validation: the target db must not already have a relation to this db with the same name (409); a db pair may have multiple relations with different names (allowed — Notion parity).
  • PATCH /v1/databases/:id/relations/:rid — rename either side's property name (unique check per side), change bidirectional off→on (creates the reverse property + backfills? no — creating the reverse property is enough; existing links are shared by construction, no backfill needed; on→off: removes the reverse property, keeps the links — documented), cardinality change is not allowed live (409 recreate_required — the user deletes + recreates; changing cardinality in place would orphan/under-enforce existing links — recorded decision).
  • DELETE /v1/databases/:id/relations/:rid — transaction: delete all relation_links for the relation (both directions — the join table stores one row per (db, record, targetDb, targetRecord) so "both directions" = the rows where (databaseId, targetDatabaseId) is either ordering), soft-delete both properties, delete the Relation row; schemaVersion bump × 2; emits database.updated × 2. (There is no relation.deleted event in the frozen catalog — database.updated with changedProperties: [relationPropertyId] is the signal; Phase-13 webhooks subscribe to that. Recorded.)
  • PUT /v1/databases/:id/relations/:rid/links?recordId=… — setLinks (the backend-architecture §1 module-table endpoint): body {targetRecordIds: string[]} (absolute set, not delta — the client sends the full desired set; the server diffs): transaction per cardinality — M:N: delete removed + insert added (existing rows untouched, one upsert); 1:N: if this side is the "one" side (record belongs to the databaseId db and the relation is ONE side here): targetRecordIds.length > 1 → 400; the single target's other "one-side" records that link it are replaced (their links to this record removed — the "one" side is exclusive per target); if this side is the "many" side: unlimited targets; 1:1: both sides exclusive — linking a second target unlinks the previous (response replaced: [oldTargetRecordId]), and if the target already has a different one-side partner, that partner is unlinked (replaced includes it, documented bidirectional replacement); all target records must exist in targetDatabaseId and be untrashed (400 record_not_found listing the bad ids); emits relation.linked/relation.unlinked per changed pair (batched into ≤ 20 ops per event, one event per changed pair-group — the catalog event carries recordId singular; the batch sends one event per pair, ≤ 20 pairs per request, more → the diff is chunked into multiple events — recorded against the "one event per request" block-batch convention: relation events are finer-grained because rollup invalidation is per-pair).
  • GET /v1/databases/:id/relations/:rid/links?recordId=…&cursor= — a record's links (page of {targetRecordId, title, icon} — titles joined from the target pages) — the cell editor's data source (TASK-0082) + the reverse side (the same endpoint called with the reverse property's relation id returns the inverse automatically, because relation_links rows carry both directions' lookup via the two indexes — the reverse read uses the (targetDatabaseId, targetRecordId) index with databaseId = targetDb swapped — a direction: 'forward' | 'reverse' query param selects the index path).
• R2. Query integration (Phase-5 handoff point): POST /databases/:id/query response gains relationCounts: {propertyId: n} (frontend-architecture §6: "+ relation counts (for relation cells)") — one EXISTS-count batch for the page's records across all RELATION properties of the db (single IN subquery, indexed); the relation values themselves are not inlined into the query response (the cell editor fetches them lazily via the links endpoint — keeps the query payload small at 100k scale; recorded decision against inlining, matching the frontend-architecture "relation counts (for relation cells)" wording).
• R3. Cascade rules (all in-transaction, all tested): record delete (page trash, Phase-5 path) → its relation_links rows removed (the page-trash service calls RelationService.onRecordTrashed — a same-module service call, not an event: the trash flow must be atomic with the link removal, and the relation.unlinked events are emitted post-commit); relation delete (R1) → links + properties; database delete (Phase-5 path) → all its relations' links on both sides + the reverse properties on the target databases (the Phase-5 delete service calls RelationService.onDatabaseDeleted — recorded extension to the Phase-5 delete path, the smallest possible diff); target record trashed while linked → the link stays (the links endpoint's lister filters trashed targets from display; the cell shows "n linked (m unavailable)" — a trashed-then-restored target's links survive the round-trip, which is the Notion behavior; recorded).
• R4. Permissions: relation CRUD = EDIT on the owning database (creating a bidirectional relation also requires EDIT on the target database — both grants checked, the weaker one wins the 403); link operations = EDIT on the source database (the record's db) — writing links from the reverse side is the same op (the endpoint is symmetric); reading links = VIEW on the source database.

Acceptance Criteria

• [ ] Integration (M:N): create a bidirectional M:N relation between two seeded dbs → both databases have a RELATION property with the right name/config (asserted via GET /databases/:id); set 3 links from db A's record R → GET …/links?recordId=R returns 3; the same 3 appear when reading R's targets' reverse side (index-path assertion — the reverse query returns them without a new row).
• [ ] Integration (1:1): linking target T2 while T1 is linked → response replaced: [T1]; T1's reverse side no longer shows the record; linking from T2's side to a record already partnered with T3 → T3 replaced (bidirectional replacement, both directions asserted).
• [ ] Integration (1:N): the "one" side rejects a 2-target set (400 with the field); the "many" side accepts 5 targets; the target's other one-side partners are displaced (replaced list correct).
• [ ] Integration (cascade): trash a linked record → its links gone (both sides); delete the relation → links gone + both properties soft-deleted + schemaVersion bumped on both dbs; delete a database → the reverse properties on the surviving target db are removed and the surviving db's GET shows no dangling RELATION properties (the Phase-5 delete-path extension works).
• [ ] Integration (events): a 3-link set emits exactly the changed pairs' relation.linked/relation.unlinked (stream capture: 2 linked + 1 unlinked for a set that adds 2 and removes 1 of 3).
• [ ] POST /databases/:id/query on a db with a RELATION property returns relationCounts matching the links table truth (100-record fixture with known link counts).
• [ ] Permissions: a user with EDIT on db A but only VIEW on db B cannot create a bidirectional relation A→B (403, the B grant is the blocker — asserted); the same user can create a one-directional A→B (allowed — only A's grant is needed for the one-way property creation? — decision, recorded: one-directional creation also requires EDIT on the target db, because it mutates the target's schema surface only when bidirectional… final: one-directional = EDIT on A only; bidirectional = EDIT on both. The test asserts both.)

Backend Changes

• apps/api/src/modules/relations/{controller,service,repositories}; RelationService with createRelation/setLinks/deleteRelation/onRecordTrashed/onDatabaseDeleted; the relation_counts batch in the query pipeline (Phase-5 handoff point, TASK-0080 R4); extension hooks in apps/api/src/modules/pages (trash → onRecordTrashed) and apps/api/src/modules/databases (delete → onDatabaseDeleted) — two small service-injected call sites; packages/shared/src/dtos/relations.ts (request/response schemas + relation.linked payload schema).

Frontend Changes

• None (the cell UI is TASK-0082; this task is API + engine + cascade).

Database Changes

• None (relations/relation_links tables + indexes exist per prisma/schema.prisma; the reverse-lookup index (targetDatabaseId, targetRecordId) is already present).

API Changes

• New: relation CRUD + PUT/GET …/links (5 endpoints); POST /databases/:id/query response gains relationCounts (additive, documented in docs/api/databases.md + a new docs/api/relations.md for the 5 endpoints).

Tests

• Unit: cardinality diff algorithm (set → added/removed/replaced per the 3 cardinalities × direction, table-driven 24 cases), the replacement-queue logic for 1:1 (chained replacements), event chunking (≤ 20 pairs).
• Integration: the 7 acceptance scenarios above against real Postgres (incl. the stream-capture event test and the two cascade paths); tenant isolation (relation across workspaces → 404/400).

Security

• Both-database grant check for bidirectional create (a relation is a schema change visible to both tenants-of-record — the target db's members must be able to consent via their EDIT grant); target record validation (no cross-db id smuggling — a targetRecordId that belongs to a third db → 400, not silently inserted, because the unique tuple includes targetDatabaseId); no raw SQL (Prisma parameterized); the links endpoint never returns records the caller can't view on the target side? — recorded decision: link reads are scoped by the source db's permission only (the target record's title is a page title — the caller with VIEW on the source db can see the linked titles; restricting by target-side grants would make the reverse side asymmetric and is a documented Phase-14 "advanced permissions" refinement).

Documentation

• docs/api/relations.md (new: the 5 endpoints, cardinality semantics table, replacement rules, cascade rules, event payloads); docs/architecture/database-architecture.md §1.4 note on the reverse-read index path.

Dependencies

• Phase-5 (databases, records, query pipeline, the RELATION property placeholder — the TASK-0080 handoff contract), packages/shared event catalog, Phase-11/13 (consumers of relation.linked — out of this phase).

Definition of Done

• Backend implemented; frontend: None (genuinely N/A — the UI is TASK-0082, deliberately split); no migration; API validation (cardinality enum, self-relation 400, target existence, batch size caps — 400s asserted); authorization (the 4-grant matrix incl. the bidirectional both-dbs rule); error states (409 duplicate name / recreate_required, 400 bad target ids, 404 cross-tenant); loading/empty states (N/A — no UI); mobile (N/A); accessibility (N/A); unit + integration (no E2E for this task — the user-facing proof is TASK-0082/0089, recorded); docs; logging (setLinks with added/removed/replaced counts, cascade deletions with row counts); audit: none mandatory (relation ops are not in the mandatory list; database.updated + relation.linked cover consumers — recorded).

---

TASK-0082

Title

Relation property editor (multi-select dialog with search, bidirectional side, relation cell)

Objective

Implement the RELATION property's user surface: the relation cell (chips of linked record titles, count, + to link), the multi-select dialog (search records in the target database, select/deselect with a live count, pre-populated from the current links), and the bidirectional experience (editing from either side updates the same link set; the reverse property's cell shows the same links with its own column name).

Background

PR-076 P0: "Relation cells show linked record titles (multi-select popover, search); reverse side updates automatically." functional-requirements §5.1 row 20: value ["recordId"], editor "Multi-select popover of linked records (search)". The Phase-5 cell placeholder (apps/web/database/cells/RelationCell.tsx — "Phase 6" chip) is replaced here. The data path: cell click → dialog → GET …/links (current)

• search via POST /databases/:id/query on the target db ({q: …}? — no: searching target records by title uses the Phase-5 query endpoint with a filter on the target's TITLE property: contains over title — the title is a page field, not an EAV value; the query endpoint gains a titleSearch convenience field for this (join on pages.title ILIKE — the one new query field this task adds, recorded); the dialog applies the diff → PUT …/links (absolute set). Optimistic chip updates + rollback (the TASK-0066 pattern).

Requirements

• R1. Relation cell (apps/web/database/cells/RelationCell.tsx, replacing the Phase-5 placeholder): read state — up to 2 linked-title chips (codec-free: title + icon) + +n overflow chip; the chip is a link (click → target record's page, in a new tab? no — same-tab navigation, the standard record-open behavior); a count badge from the query's relationCounts (no per-cell fetch for the count); + affordance (opens the dialog); the 1:1 variant shows a single chip + × (unlink) inline (1:1 has no multi-select — the "dialog" is a picker of exactly one, selecting replaces); empty cell = muted "Add link" (click → dialog). Read-only for VIEW users (chips render, no +).
• R2. Link dialog (apps/web/database/relations/LinkDialog.tsx): role=dialog, header "<this prop name> — <target db title>" + live count "3 of 128 linked"; search input (debounce 250 ms → POST /databases/:targetId/query {titleSearch, pageSize: 20, cursor} — the target db must be VIEW-able by the caller, else the dialog shows "You can't see this database's records" state); results list (title + icon + one secondary property chip) with checkboxes; the current links are pre-checked (fetched via GET …/links on open, with their titles — the links endpoint returns titles, so no second fetch); select/deselect updates the count live; "Done" (primary, disabled when nothing changed) → PUT …/links {targetRecordIds: fullSet} → chips update optimistically on send, rollback on 409/400 (with the server's truth re-fetched into the dialog on 409 — a concurrent edit re-merges visibly, never silent); Esc closes without saving (the standard overlay contract); keyboard: / focuses search (when the dialog is open, / is not the slash menu — overlay owns it, the TASK-0057 rule), ↑/↓ + Space toggles results, ⌘Enter = Done.
• R3. Bidirectional side: the reverse property (created by TASK-0081) renders the same RelationCell component with its own name — the links endpoint's direction: reverse makes the data correct; editing from the reverse side calls PUT …/links on the reverse property's relation (same relationId — the endpoint is keyed by relation, and recordId + targetRecordIds are interpreted in the calling direction — the server normalizes to the canonical (db, targetDb) ordering; a link added from either side is one row, verified by an integration test).
• R4. Table integration: the relation column in the table view (TASK-0066) uses the cell; column width default 200 px; the cell is not sortable in Phase 6 (sorting by relation = sort by linked titles — a multi-valued key; deferred, the header sort click is a no-op with a tooltip "Sorting by relation is coming" — recorded); filtering by relation (contains/doesNotContain/isEmpty per the TASK-0067 operator matrix) is enabled: the filter builder's relation operator takes a target-record picker (a compact version of the dialog, single-select) — the push-down: relation_links EXISTS subquery (added to TASK-0067's pushdown.ts — the extension is recorded in that task's file header notes? no — this task edits pushdown.ts and documents the addition in docs/api/databases.md).
• R5. Record page: the relation property in the record-page left panel (TASK-0065) renders the same cell + dialog (panel chip click → dialog, consistent with the table).

Acceptance Criteria

• [ ] E2E (link flow): open a relation cell → dialog pre-populated with the current 2 links (checked) → search "alpha" (target title search) → check 2 results → Done → chips show 4 (2 + 2, count badge 4); network: one PUT …/links with the 4-id set; the reverse property's cell on a linked target now shows this record (E2E opens the target's page and asserts the chip).
• [ ] E2E (1:1): the cell shows one chip + ×; the dialog is single-select (checking a second result unchecks the first — radio behavior asserted); Done replaces the link (replaced in the response); the old target's reverse cell no longer shows the record.
• [ ] E2E (optimistic + rollback): two users link the same record concurrently (test hammers the same cell with two contexts) → one 409 → the losing dialog re-opens with the server truth (the winning user's links checked) → the loser picks + Done → final state = union, no lost link (the CRDT-free "re-merge" path, asserted in the DB).
• [ ] E2E (filter): filter the table by relation contains <target> → only records linking that target show (truth-checked); isEmpty → records with no links.
• [ ] Integration: the titleSearch query field (case-insensitive, prefix-friendly, paginated, scoped to the target db + untrashed pages; a cross-workspace target → 404).
• [ ] A11y E2E: dialog opened from a keyboard-focused cell (Enter), searched with /, results toggled with arrows + Space, Done with ⌘Enter — no pointer; role=dialog + focus trap + focus return; axe clean on the cell + dialog.

Backend Changes

• POST /databases/:id/query gains titleSearch?: string (≤ 100 chars, pages.title ILIKE %…% on the joined pages — parameterized; the 100k seed perf budget re-verified with this field in a filtered query — recorded in the TASK-0079 artifact comparison); pushdown.ts gains the relation EXISTS conditions (contains → EXISTS with targetRecordId IN (…); isEmpty → NOT EXISTS) — the fuzz invariant (pushdown+residual == apply) is extended with relation fixtures.

Frontend Changes

• apps/web/database/cells/RelationCell.tsx (replace placeholder), apps/web/database/relations/LinkDialog.tsx, filter builder relation value editor (compact picker), record-page panel wiring (TASK-0065 panel reuses the cell — no change there if the cell is drop-in, which the task verifies).

Database Changes

• None.

API Changes

• Additive: titleSearch on the query endpoint; relation filter operators now implemented (the TASK-0067 matrix already declared them — the residual-bound behavior for deep relation filters is inherited).

Tests

• Unit: cell chip truncation (2 + +n), dialog diff (current set + toggles → final set, 1:1 radio logic), the pushdown relation conditions vs applyFilter (golden relation fixtures — 12 cases: contains-one/many, doesNotContain, isEmpty, with archived targets).
• Integration: titleSearch (the 5 assertions above), the direction normalization (link from the reverse side = same row — unique-tuple assertion), the 409 concurrent-link re-merge.
• E2E: the 5 scenarios (TASK-0089 suite hosts them; skeletons here).

Security

• The dialog requires VIEW on the target db (a caller with EDIT on the source but no grant on the target sees the "can't see" state — no enumeration of target record ids/titles); PUT …/links re-validates every target id against the target db (400 on foreign ids — TASK-0081's check, re-exercised through the UI); titleSearch is a plain ILIKE (escaped %/_ in user input — the escape is unit-tested; no LIKE-injection surface); optimistic updates never render uncommitted data as truth (rollback on any error, the TASK-0066 pattern).

Documentation

• docs/api/relations.md: the cell/dialog data-flow note + titleSearch; apps/web/database/README.md: RelationCell contract (TASK-0083/0085 computed cells follow the same placeholder→real pattern).

Dependencies

• TASK-0081 (the API + links endpoint), Phase-5 (cells, query, filter builder, record page), packages/ui dialog primitives.

Definition of Done

• Backend implemented (titleSearch + push-down relation conditions); frontend implemented (cell + dialog + filter editor + panel wiring); no migration; API validation (titleSearch length/escaping, filter relation value = a valid target record id — 400s); authorization (the source-EDIT/target-VIEW matrix E2E-asserted); error states (409 re-merge dialog, target-db no-access state, 400 foreign ids toast); loading states (dialog results spinner, cell count badge while the query loads); empty states (no links "Add link", no search results "No entries match '…'"); mobile (dialog full-screen, chips wrap); accessibility (the full keyboard E2E + axe, focus return, aria-label on chips "Linked: <title>", 1:1 × button labeled "Unlink <title>"); unit + integration + E2E; docs; logging (link-dialog saves with added/removed counts, titleSearch latency); audit: link changes = relation.linked/unlinked events (consumer-facing; the mandatory audit list is unchanged — recorded).

---

TASK-0083

Title

Rollups (7 aggregates, on-read compute + Redis cache, event invalidation)

Objective

Implement ROLLUP properties: POST /v1/databases/:id/rollups (relation + target property + aggregate + optional filter), on-read computation with a 60 s Redis cache, event-driven invalidation (record.updated/relation.linked|unlinked on the linked database → cache flush), correct results for all 7 aggregates (COUNT, SUM, AVERAGE, MIN, MAX, PERCENT, UNIQUE), and the read-only ROLLUP cell.

Background

PR-077/078 P1. Schema: rollups (relationId, targetPropertyId, aggregate, filter Json?, propertyId? = the ROLLUP property it feeds). functional-requirements §5.5: "value computed lazily on read and eagerly refreshed via events (record.updated, relation.changed) for the affected records; PERCENT = fraction of matching (filtered) related records (0–1, displayed %); UNIQUE = deduped value list (or count when display is 'count'); result type inferred (SUM/AVG → number; MIN/MAX → type of target; COUNT/PERCENT → number; UNIQUE → array)." Header note 2: on-read is the default; the materialized path (worker precompute into record_properties for large dbs) is TASK-0086's job — this task builds the pure aggregate engine + the cache + the invalidation, and the query integration reads either path through one interface (ComputedValueProvider — on-read here, materialized swap-in in TASK-0086).

Requirements

• R1. packages/database-engine/src/rollups/ — pure (no Prisma, no Redis):
  • aggregate.ts: aggregateRollup(values: PropertyValue[], config: {aggregate, targetPropertyType, filter?}, ctx) → RollupValue — input = the linked records' values for targetPropertyId (already filtered by the rollup's filter tree via the TASK-0067 engine — the rollup filter filters the related records, e.g. "status = Done" before counting — the spec's "Completed = 3" example); semantics per aggregate: COUNT = count of linked records matching the filter (not non-null values — COUNT of rows; a rollup with no filter counts all links); SUM/AVERAGE = over the numeric values of matching records (non-numeric/missing excluded; AVERAGE of zero numerics = null, not NaN — the empty-set rule: SUM = 0? decision, recorded: SUM over zero matching numeric values = null (no values), COUNT over zero matching records = 0 (a count is defined on an empty set — the distinction is asserted in goldens)); MIN/MAX = over numeric or date values (mixed-type target → 400 at config time — the target property must be NUMBER or DATE for MIN/MAX); PERCENT = (matching records) / (all linked records) as a 0–1 float (filter present; without a filter PERCENT = 1.0 — degenerate, allowed, the UI warns); UNIQUE = deduped value array (numbers/strings/dates; option values → option ids, displayed as names by the cell; config.display: 'list' | 'count' (default list) — count mode returns the array length as a number); result-type inference function inferredType(aggregate, targetType) → 'number' | 'date' | 'array' (stored nowhere — computed at read; the cell and the Phase-8 search body use it).
  • collect.ts: collectLinkedValues(links, targetValues, filterTree, properties, ctx) — the join: for each linked target record, its record_properties value for targetPropertyId (the caller fetches: the query pipeline batch-fetches target values for the page's links in one IN query — two indexed round-trips total for a page of 100 records with ≤ 50 links each, budgeted in the 300 ms query target).
• R2. API (apps/api/src/modules/relations/rollups.ts):
  • POST /v1/databases/:id/rollups — {name, relationId (must belong to this db), targetPropertyId (must belong to the relation's **target** db — 400 wrong_database), aggregate, filter? (a FilterTree over the target db's properties — validated with the target's property set), display? ('list'|'count', UNIQUE only)} → the ROLLUP property on this db (config: {rollupId}, position appended) + the Rollup row (propertyId); target type validation (SUM/AVERAGE → target NUMBER; MIN/MAX → NUMBER|DATE; COUNT/PERCENT/UNIQUE → any type); schemaVersion bump + database.updated; emits nothing extra (the property is a schema change — database.updated covers it).
  • PATCH /v1/databases/:id/rollups/:rid — rename, filter change, display change (re-validates); aggregate/targetPropertyId/relationId changes → 409 recreate_required (same cardinality-change precedent, TASK-0081).
  • DELETE /v1/databases/:id/rollups/:rid — soft-delete the property + the rollup row; schemaVersion bump.
  • GET /v1/databases/:id/rollups/:rid/preview?recordId= — the computed value for one record (the formula/rollup editor's live-preview endpoint, shared with TASK-0085's pattern — one endpoint shape for both computed families, recorded).
• R3. Read path (query integration, the Phase-5 handoff point): POST /databases/:id/query response values now includes ROLLUP property values — ComputedValueProvider.read(pageRecords) → for each rollup property: (a) Redis cache lookup rollup:{dbId}:{propId}:{recordId} (TTL 60 s — header-note-2's "computed on read with Redis cache (TTL 60s)"); (b) miss → collectLinkedValues (the batch link+value fetch) → aggregateRollup → cache write; the provider is an interface (OnReadRollupProvider here; MaterializedRollupProvider swap-in in TASK-0086 behind the same interface + a size-based selector — the query code doesn't change in TASK-0086, recorded).
• R4. Invalidation (event-driven, PR-0078 "no polling"): an in-process event subscriber in apps/api (the api-side consumer bootstrap from backend-architecture §7 — the api links into the Redis streams for its own cache invalidations; the worker consumers for heavy work are TASK-0086) on: record.updated / relation.linked / relation.unlinked where payload.databaseId = a rollup's target db (or the rollup's own db, for COUNT-of-self? no — a rollup always crosses to the target db via the relation; invalidation triggers on the target db's record changes) → DEL the Redis keys for the affected records: the affected set = records in the source db that link the changed target record (the reverse lookup: relation_links WHERE targetDatabaseId = X AND targetRecordId IN (changedIds) → source record ids, one indexed query) + records whose own values changed (a record.updated on the target db with changedProperties containing targetPropertyId → its partners' rollups are stale). Cache-only in this task (materialized-row flush is TASK-0086). Bounded: > 500 affected records → a prefix-scan DEL of rollup:{dbId}:* (the bounded fallback, logged — a fan-out cap against a hub record with 10k links).
• R5. ROLLUP cell (apps/web/database/cells/RollupCell.tsx, replacing the Phase-5 placeholder): read-only badge — number formatting per the inferred type (PERCENT → 75 % with a small progress bar when the property is a STATUS-adjacent context? no — PERCENT always renders 75 % text + an optional 4 px progress underline when config.showBar (default false); UNIQUE list → up to 3 chips + +n (chips use the target property's display); UNIQUE count → a number; null → muted "—"; an "error" state is not possible in Phase 6 (aggregate errors are config-time 400s — runtime over bad data yields the empty-set rule, never an error — recorded against the formula's error state, which is different: formulas can fail at runtime, rollups can't, because their inputs are type-checked at config time).

Acceptance Criteria

• [ ] Unit (goldens, the "rollup golden" suite): 40+ cases — every aggregate × (empty set, one value, mixed missing/typed values, PERCENT with filter 3/4 = 0.75 (the spec's "Completion = 75%" example), UNIQUE dedupe with option ids, MIN/MAX over dates, AVERAGE-of-zero-numerics = null, SUM-of-zero = null, COUNT-with-filter vs COUNT-all) — exact values + inferredType.
• [ ] Integration (on-read): a Projects→Tasks M:N (4 tasks, 3 with status Done, a Number "Estimate" 5/10/15/null) → rollups COUNT (4), COUNT+filter Done (3 — the spec example "Completed = 3"), SUM Estimate (30), AVERAGE (10), PERCENT Done (0.75), MIN (5), MAX (15), UNIQUE (the 3 status ids deduped) — all 8 correct via POST /query (one response, all rollup values in values).
• [ ] Integration (cache): the same query twice → the second is a cache hit (the test asserts via a Redis GET spy: first call writes, second reads, no recompute); TTL 60 s respected (fake-time advance → recomputes).
• [ ] Integration (invalidation — PR-0078's "recompute on relation change, event-driven"): update a linked task's Estimate (5 → 7) via the API → record.updated → the project's rollup cache key is DELed (stream + Redis spy) → the next query returns SUM 32 (not 30) without waiting for the TTL; link a 5th task → relation.linked → COUNT reads 5 on the next query. The E2E-observable latency (< 5 s, TASK-0089) is the same path.
• [ ] Integration (cross-db guard): a rollup whose targetPropertyId belongs to a third db → 400 wrong_database; a rollup on a relation pointing at a db in another workspace → 404.
• [ ] E2E (preview): the rollup editor's preview (GET …/preview?recordId) shows the live value as the editor's filter changes (TASK-0085 builds the editor; the endpoint is tested here with a curl-style call from the E2E context).
• [ ] Fallback: a hub record with 600 links → invalidation uses the prefix DEL (logged rollup.invalidate.fallback, the 600 affected ids are NOT individually DELed — asserted via the Redis command log).

Backend Changes

• apps/api/src/modules/relations/rollups.ts (CRUD + preview), ComputedValueProvider interface + OnReadRollupProvider in apps/api/src/modules/databases/computed.ts (the query integration point), the api-side invalidation subscriber in apps/api/src/events (a new consumer group rollup-cache-invalidate? — recorded decision: cache invalidation is cheap and belongs with the cache; it runs in the api process as a direct subscriber (the api already links into the streams per backend-architecture §7) — no new worker consumer in this task; TASK-0086 adds the worker consumer for materialized recompute under a different group rollup-recompute); Redis cache keys rollup:{dbId}:{propId}:{recordId} (TTL 60 s) + rollup:{dbId}:* prefix; packages/shared DTOs (RollupCreateSchema, RollupValueSchema).

Frontend Changes

• apps/web/database/cells/RollupCell.tsx (replace placeholder); the rollup editor dialog is TASK-0085's (this task exposes the preview endpoint it consumes); the property picker's ROLLUP type row flips from the "Phase 6" banner to an active config form (relation picker [this db's relations] → target property picker [target db's properties, typed] → aggregate picker [the type-filtered list] → filter builder inline [TASK-0067, target property set] → display toggle for UNIQUE) — the form component apps/web/database/properties/RollupConfigForm.tsx.

Database Changes

• None (rollups table exists; record_properties materialized rows are TASK-0086).

API Changes

• New: rollup CRUD + preview (4 endpoints, docs/api/relations.md); POST /databases/:id/query values now populated for ROLLUP properties (the Phase-5 422 for rollup writes stands — writes remain 422 computed_property_readonly, only reads compute).

Tests

• Unit: the 40+ golden aggregate cases, collectLinkedValues join fixtures (link sets × sparse values), inferredType matrix, the invalidation affected-set computation (reverse lookup fixtures incl. the 500-cap fallback).
• Integration: the 7 scenarios above (Redis + stream spies), the cross-db guards, the query integration (rollup values present + correct + cached).
• E2E: the preview scenario (TASK-0089 hosts the user-facing rollup scenario: create rollup via the property picker → value renders in the table → linked change → value updates < 5 s without manual refresh — the phase exit criterion "rollups recompute on relation change").

Security

• Rollup CRUD = EDIT on the owning db (the target db needs no grant — reading the target's values through the rollup is scoped by the caller's VIEW on the source db at query time; the target's records are accessed server-side as system for the aggregate — recorded: a rollup can surface aggregate data the caller couldn't see row-by-row in the target db (e.g. a SUM over records a Guest can't open); this is Notion-parity behavior and is a documented Phase-14 "advanced permissions" refinement, flagged in the handoff note for Phase 14, not a Phase-6 gap); filter trees validated against the target property set (no cross-db property smuggling); cache keys tenant-scoped (dbId is unique per workspace by construction — the key prefix is the tenant boundary); no SQL in the engine (pure functions over fetched values).

Documentation

• docs/api/relations.md rollups section (semantics table incl. the empty-set rules + PERCENT definition + UNIQUE display modes); docs/architecture/database-architecture.md §1.4 update: the on-read/materialized split (header note 2 made normative); the Phase-14 permissions refinement flag.

Dependencies

• TASK-0081 (relations + links + events), TASK-0067 (filter engine for the rollup filter), Phase-5 (query pipeline, property CRUD, cell contract), Redis (Phase 0), TASK-0086 (materialized path — consumes this task's provider interface).

Definition of Done

• Backend implemented; frontend implemented (cell + config form); no migration; API validation (type-allowed aggregates per target type, target-db guards — 400s asserted); authorization (the matrix incl. the target-value scoping decision, integration-tested); error states (config 400s, the cell "—" null state); loading states (cell skeleton while the first on-read compute runs — a page with 20 rollups computes in parallel, capped at 5 concurrent computes per request, the rest queued — recorded perf guard); empty states (no links → COUNT 0 / others null, rendered per the rules); mobile (cell chips wrap, config form bottom sheet); accessibility (cell role=note? — read-only computed values: aria-label "Total tasks: 4" (value in the label), config form labeled, errors aria-describedby); unit (goldens) + integration + E2E; docs; logging (cache hit ratio per rollup property, fallback invalidations, compute durations p95); audit: none mandatory (rollup ops ride database.updated — recorded).

---

TASK-0084

Title

Formula engine (tokenizer → Pratt parser → AST → safe evaluator, fuzz)

Objective

Implement the formula engine in packages/database-engine/src/formula: a tokenizer, a Pratt parser producing a JSON AST, and a safe AST evaluator with the fixed function set (if, and, or, not, sum, average, min, max, dateAdd, dateBetween, now, concat, replace, contains, length + prop(name)), type inference, a structured FormulaError {PARSE|TYPE|RUNTIME|UNKNOWN_FUNCTION}, per-evaluation step limits, and zero eval/new Function — the engine is pure TypeScript over the AST only, with 100 % function coverage via golden tests and 100-expression fuzzing.

Background

PR-079/080/081 P1; the Phase-6 exit criterion: "formula engine 100 % function coverage, zero eval." database-architecture §1.4: "The source is parsed at save time by the safe parser in packages/database-engine (lexer → Pratt parser → AST with the fixed function set … + prop()); an invalid formula is rejected with VALIDATION_ERROR (never stored). Evaluation runs the AST only, with per-evaluation step limits (10k steps, 1 MB output) so a hostile formula cannot DoS the query path. dependencies is extracted from the AST — the recompute worker uses it, not string matching." functional-requirements §5.5: tokens (identifiers, strings, numbers, booleans, dates, prop("Name"), function calls, arithmetic, comparison, logical, ternary); "No recursion depth > 16, no string eval, no access to host objects, evaluation budget 10 000 steps"; errors surface in the cell as a red badge; now() re-evaluates on each read (documented nondeterminism); dateAdd(date, amount, unit), dateBetween(a, b) → days, contains(hay, needle), replace(s, from, to), concat(a, b, …), length(x), if(cond, a, b), and/or/not(...), sum/average/min/max(list) over arrays (relation/rollup values). The Formula model already stores expression + ast Json + returnType + dependencies String[]. This task is engine-only: the property/API/UI are TASK-0085; the recompute pipeline is TASK-0086.

Requirements

• R1. packages/database-engine/src/formula/lexer.ts: tokens — numbers (int/float, no exponents? — allow 1e5, documented), strings (double-quoted, \" escape; single-quoted accepted for user-friendliness, both produce the same token — recorded), booleans (true/false), dates (ISO 2025-03-01 and 2025-03-01T10:00:00Z literals), identifiers ([a-z][a-z0-9_]* case-sensitive, matching property names — property lookup is case-sensitive but the parser's prop() accepts the name as-is and the resolver reports unknown properties as TYPE errors with the available list), operators (+ - * / % == != < <= > >= and or not ( ) , ? : .), comments // (line, for user readability — stripped, recorded). Unknown char → FormulaError {code: 'PARSE', pos, message} with a caret-annotated snippet (the editor's error highlighting consumes pos).
• R2. parser.ts — Pratt (precedence climbing): literal → unary (-x, not x) → binary (multiplicative > additive > comparison > and > or; comparison is non-associative? — left-assoc, a < b < c allowed (chained, left-assoc, documented)) → ternary (cond ? a : b as sugar for if(cond, a, b) — both forms parse to the same AST node If — the ternary is syntactic sugar, recorded) → call (name(args) — variadic arity checked against the function table, 0-arg now() allowed). Max nesting depth 16 (PARSE error beyond — the functional-requirements "no recursion depth > 16"). The AST is JSON-serializable (the formulas.ast JSONB): {type: 'Literal'|'Prop'|'Call'|'BinaryOp'|'UnaryOp'|'If', ...} — a discriminated union in packages/shared (the editor's autocomplete + the Phase-12 AI "generate formula" tool consume the same AST type).
• R3. functions.ts — the fixed table (name → {arity: [min, max], argTypes: (…| 'any')[], returns: Type, impl(ctx, args): Value}), exactly: if ([3,3], any/any/any, type = the two branches must unify (number/number, string/string, date/date, bool/bool; mismatch → TYPE), lazy evaluation (only the taken branch — asserted: if(true, 1, 1/0) does not error)), and ([2,∞] all bool → bool, lazy), or ([2,∞] lazy), not (1, bool), sum ([1,1], array-of-number → number (empty → null per the TASK-0083 empty-set rule? — decision, recorded: sum([]) = 0 (a function over an explicit list — the user passed a list, 0 is the identity), average([]) = null, min/max([]) = null — the asymmetry is asserted in goldens; rollup SUM-null is the no-matching-records case, a different path — the two empty-semantics are documented side by side)), average (1, array-of-number → number|null), min/max (1, array-of-number|date → same type), dateAdd ([3,3], (date, number, 'days'|'weeks'|'months'|'years') → date; month/year overflow clamps to month-end (2025-01-31 + 1 month = 2025-02-28 — asserted), negative amounts allowed), dateBetween ([2,2], (date, date) → number of days (b − a, negative allowed, DST-safe via UTC-day math — the TASK-0071 dates helper reused), now ([0,0] → date (the evaluator's ctx.now — injectable for tests; the documented nondeterminism), concat ([2,∞] all stringify → string (numbers as-is, dates as ISO, null → empty string, arrays → comma-joined — documented stringify rules)), replace ([3,3] (string, string, string) → string (all occurrences, literal not regex — recorded), contains ([2,2] (string, string) → bool (case-sensitive; the case-insensitive variant is containsLower? no — single function, case-sensitive, documented)), length (1, string → its length | array → its length | number/date/bool → 1? — decision, recorded: length on non-string/array → TYPE error (no surprise 1s); null → 0).
• R4. prop() is a call, not syntax: prop("Status") → AST {type: 'Prop', name} — the resolver at eval time receives ctx.properties: {name: value} (property names, not ids — the user writes names; the parser keeps the name; TASK-0085 maps names → ids for dependencies); an unknown name → FormulaError {code: 'TYPE', message: "Unknown property 'Statu'. Did you mean 'Status'?" (Levenshtein ≤ 2 suggestion)}.
• R5. evaluator.ts: walks the AST (iterative where possible; the depth cap makes recursion safe — 16), step counter (each node visit = 1 step; limit 10 000 → FormulaError {code: 'RUNTIME', message: 'Evaluation limit exceeded (10000 steps)'}), output-size guard (any string > 1 MB → RUNTIME), ctx: {now: Date, properties: Record<string, Value>} (the ONLY host access — the evaluator has no closures, no this, no imports of host objects; the function table is a closed const — a static-analysis assertion in CI greps the file for eval(, new Function, setTimeout, require, import( — recorded as the zero-eval gate's engine-side half, the repo-wide grep is TASK-0090). Type rules: arithmetic on non-numbers → TYPE; comparison across types (number < date) → TYPE (string < string and date < date allowed — lexicographic/chronological); null semantics: null in arithmetic → null propagation (not an error — null + 1 = null, documented; null == null = true, null == 0 = false); if/and/or laziness; date arithmetic only via dateAdd (no date + 1 — that's a TYPE error with a "use dateAdd" hint in the message).
• R6. inferType(ast, properties) → Type (static, no evaluation): walks the AST with the function table's returns + the property types (from the db's property set) → returnType for formulas.returnType; uninferable (e.g. if with divergent branches that can't unify) → the unification error at save time (400) — a formula that can't infer a type is rejected at save (never stored, per the architecture doc); extractDependencies(ast) → propertyNames[] (every Prop node's name, deduped, sorted — the recompute worker's input; the now() flag is a separate boolean usesNow — header note 3).
• R7. index.ts public API: parseFormula(src) → Ast | FormulaError, validateFormula(src, propertyNames) → {ast, returnType, dependencies, usesNow} | FormulaError (parse + infer + name resolution — the save-time entry, TASK-0085 calls it), evaluateFormula(ast, ctx) → Value | FormulaError (the read-time entry), FormulaError with {code: 'PARSE'|'TYPE'|'RUNTIME'|'UNKNOWN_FUNCTION', message, pos?, property?}.

Acceptance Criteria

• [ ] 100 % function coverage: a coverage report (Vitest, function-level) shows 100 % of functions.ts's impls executed by the golden suite; a CI assertion fails the build if any function name lacks a golden test file (__tests__/goldens/<fn>.json — the structural gate pattern from TASK-0079, applied per function).
• [ ] Golden tests: ≥ 8 cases per function (15 functions + prop + literals + operators ≥ 130 total) — exact values incl. the recorded decisions: sum([]) = 0, average([]) = null, dateAdd month clamping, replace literal semantics, contains case-sensitivity, length TYPE errors, if/and/or laziness (the 1/0-in-untaken-branch case), null propagation matrix (16 null×operator cases), the spec's example if(prop("Status") == "Done", 100, 0) → 100/0 for both inputs.
• [ ] Error classification: 25+ hostile inputs each produce the right FormulaError.code — foo( (PARSE), 1 + "a" (TYPE), "a" < 1 (TYPE), unknownFn(1) (UNKNOWN_FUNCTION), 17-deep nesting (PARSE), a 20k-step loop? — no loops exist (no while in the language) — the step limit is reached via a deeply nested concat chain (the PARSE depth cap catches 17; the step limit is hit by a 10k+ node AST that parses (≤ 16 depth) but walks 10k+ nodes — a 16-deep × wide tree fixture — RUNTIME asserted), length(5) (TYPE).
• [ ] Fuzz: 100 random expressions (seeded, generated by a random-AST→source generator over the grammar — the generator is committed, __tests__/fuzz/generate.ts) — for each: parseFormula(src) either (a) parses and evaluateFormula either returns a value or a RUNTIME/TYPE FormulaError (never throws, never hangs — a 1 s watchdog per case in CI), or (b) a PARSE/UNKNOWN_FUNCTION error; invariant: the engine never throws an uncaught exception and never exceeds 1 s — 100/100 pass, the fuzz log (expression + outcome per case) is a CI artifact; the nightly lane rotates the seed (the TASK-0079 pattern).
• [ ] Type matrix: 4×4 (number/string/date/bool × number/string/date/bool) for +, ==, < → 48 expected outcomes asserted (the TYPE/OK matrix is a golden table).
• [ ] inferType + extractDependencies: the spec example infers number and ['Status']; concat(prop("A"), prop("B")) infers string, deps ['A','B'] sorted; now() sets usesNow: true (header note 3's flag).
• [ ] Zero-eval (engine half): the CI grep on packages/database-engine/src/formula/** finds zero matches for eval(, new Function, Function(, setTimeout, require(, import( (the grep command is committed in tests/ci/no-eval.sh — TASK-0090 runs it repo-wide).

Backend Changes

• None (pure package — AGENTS.md rule 23/24: domain logic independent of UI and Postgres; no NestJS decorators in the package).

Frontend Changes

• None (the editor UI is TASK-0085; it imports parseFormula for live validation).

Database Changes

• None (formulas table exists).

API Changes

• None (the save-time validation endpoint is TASK-0085; this task is the package).

Tests

• This task is the test suite: the golden suite (≥ 130 cases, per-function files), the error-classification suite, the fuzz harness + generator + watchdog, the type matrix, the coverage + structural-coverage assertions, the no-eval grep (engine scope). Unit-only by design (the engine has no I/O — integration happens through TASK-0085/0086).

Security

• The engine IS the security control (AGENTS.md rule 18: "Never use arbitrary JavaScript execution for formulas"): no eval/Function/timers/host imports (grep-gated), closed function table (no dynamic dispatch — a name not in the table is UNKNOWN_FUNCTION at parse time, never looked up in globalThis), step + output limits (DoS guard — a hostile concat-chain formula can't burn the query path), the ctx is the only input (no prototype access — values are plain primitives/arrays from the codec registry, never host objects — the evaluator asserts argument shapes at the function boundary and TYPE-errors on mismatch), the AST JSON is schema-validated on read from formulas.ast (a corrupted/hand-edited AST → VALIDATION_ERROR, re-parse from expression is the repair path, TASK-0085).

Documentation

• packages/database-engine/src/formula/README.md — the language reference (tokens, precedence table, the full function table with arity/types/semantics, the null matrix, the empty-set rules, the recorded decisions) — this file is the single source TASK-0085's autocomplete and the Phase-12 "generate formula" tool prompt cite.

Dependencies

• packages/database-engine scaffolding (Phase-5), the TASK-0071 dates helper (UTC-day math), packages/shared (the AST discriminated union — the editor + Phase-12 share it), no other phase code.

Definition of Done

• Backend: the engine (pure package, no I/O); frontend: None (N/A — UI is TASK-0085); no migration; API validation (N/A — no endpoint); authorization (N/A — pure engine); error states (the 4-code FormulaError contract is the deliverable); loading/empty states (N/A); mobile (N/A); accessibility (N/A); unit (is this task — goldens + fuzz + matrix + coverage gate) + integration (N/A by design) + E2E (N/A by design — the user-facing formula E2E is TASK-0089); docs (the language reference); logging (N/A — the engine is pure; TASK-0085/0086 log around it); audit: N/A.

---

TASK-0085

Title

Formula property (config, AST storage, editor UI with autocomplete + live preview)

Objective

Make formulas usable: POST /v1/databases/:id/formulas (save-time validation via the TASK-0084 engine — parse + type inference + dependencies/usesNow extraction → formulas row + FORMULA property), the formula editor UI (input with function + prop() autocomplete, live error highlighting from the tokenizer's pos, live preview over a sample record), and the read-only formula cell with the red error badge (runtime FormulaError per record, e.g. a TYPE error that only manifests for a subset of records' values).

Background

PR-082 P1. formulas model: expression (source, the user's text), ast Json (pre-parsed, schema-validated on read), returnType, dependencies String[] (property names, extracted by extractDependencies), propertyId? (the FORMULA property it feeds). The Phase-5 placeholder cell (FORMULA = "Phase 6" chip) is replaced. Save-time contract (database-architecture §1.4): "an invalid formula is rejected with VALIDATION_ERROR (never stored)" — i.e. PARSE/TYPE (unknown property)/uninference errors → 400; RUNTIME errors are not save-time (they depend on data) — a formula that parses + infers is stored, and per-record runtime failures surface as the cell's error state. Read path: the query pipeline's ComputedValueProvider (TASK-0083's interface) gains OnReadFormulaProvider — evaluateFormula(ast, {now, properties: record's values by name}) per record, with the same Redis cache as rollups except formulas with usesNow (header note 3 — they bypass the cache: now() re-evaluates each read, the documented nondeterminism); the materialized path (TASK-0086) skips usesNow formulas the same way. The editor (functional-requirements §5.5): "expression input with function autocomplete; live preview evaluates against a sample record (first record of the db, or an empty-record fallback); errors highlighted inline (pos → caret + red squiggle); the return type badge (number/text/date/checkbox) updates as the user types."

Requirements

• R1. API (apps/api/src/modules/relations/formulas.ts):
  • POST /v1/databases/:id/formulas — {name (≤ 100, unique per db), expression (≤ 5000 chars)} → validateFormula(expression, thisDbPropertyNames) (TASK-0084 R7 — property names of this db; a reference to another db's property is a TYPE error with the suggestion list — cross-db property references are not supported in Phase 6, recorded; the relation's target values come in via rollups, not prop()) → 400 VALIDATION_ERROR with {code, message, pos?} on failure → on success: the FORMULA property (config: {formulaId}, position appended) + the Formula row (ast = the JSON AST, returnType, dependencies, + usesNow? — the model has no usesNow column; the flag is re-derived from the AST at read time (extractUsesNow(ast) — a cheap walk; recorded decision: don't widen the schema, derive it), propertyId) → schemaVersion bump + database.updated.
  • PATCH /v1/databases/:id/formulas/:fid — rename, or a new expression (full re-validation; the ast/returnType/dependencies are recomputed atomically with the property rename — a rename that violates uniqueness → 409); changing the expression of a formula whose dependencies shrink is safe (the recompute worker re-reads dependencies fresh — no migration of subscriptions, recorded).
  • DELETE /v1/databases/:id/formulas/:fid — soft-delete property + formula row; schemaVersion bump.
  • GET /v1/databases/:id/formulas/:fid/preview?recordId= — the shared preview endpoint (same shape as TASK-0083's rollup preview): evaluates against that record's values (the caller must be able to view the record — VIEW on the db suffices, the record is a page) → {value, error?} (a per-record FormulaError is data here — the preview shows the same red state the cell would; the preview never 500s on a RUNTIME error — it's returned in error).
• R2. Editor UI (apps/web/database/formula/FormulaEditor.tsx — a dialog from the property picker / the property ⋯ menu "Edit formula"): a monospace text input (a plain <textarea> + an overlaid syntax-highlighted <pre> from a tiny tokenizer re-use of the engine's lexer — the highlighter maps token types to token colors; no CodeMirror dependency (rule 17: the formula language is small; a textarea+overlay is ~150 LOC; recorded decision, upgraded to CodeMirror only if the a11y/review pass demands it)); autocomplete: typing ( after a word, or prop, shows a completion popup (function list with signatures from the engine's function table — the single source, imported, not re-typed; prop("…") completes against this db's property names, fuzzy, max 12); Tab accepts a completion (inserts name( with the caret inside); Esc dismisses (overlay contract); live validation: on every change (debounce 200 ms) run validateFormula client-side (the engine is a pure package — it ships to the web bundle; the bundle cost is recorded: ~15 KB gzip, acceptable for the core feature) → error → red underline at pos (the overlay renders the squiggle span) + the message below the input; success → the returnType badge (number/text/date/checkbox — the 4 display types; array renders as "list") + a green check; live preview: a "Preview" pane (right of the input, dialog ≥ 720 px; below on mobile) showing the value over the sample record (the db's first record by position — fetched once when the dialog opens; if the db is empty, the preview shows "(empty record)" with null values — the formula must still evaluate, e.g. if(prop("X"), 1, 0) → 0; a "Use record: …" picker (max 10 recent records) lets the user preview against a specific record — the GET …/preview?recordId= endpoint); the preview value renders via the return type's codec display().
• R3. Save flow: "Save" disabled until the last validation succeeded (PARSE/TYPE clean); save → POST/PATCH → success closes the dialog; a server 400 (a client/server validation drift — shouldn't happen, same package) → the server's error message replaces the client's (the server is authoritative, recorded).
• R4. Formula cell (apps/web/database/cells/FormulaCell.tsx, replacing the placeholder): read-only, formatted per returnType (number formatting, date display, bool → checkbox glyph); error state = a red badge ! with the FormulaError.message in the title/aria-label (per-record runtime failure — e.g. a TYPE that only hits when the record's value is null and the formula does length(prop("X")) — null → 0 is defined in TASK-0084, so the realistic runtime error is a RUNTIME limit or a type mismatch on a specific value shape — the badge shows the code: "Type error" / "Runtime limit"); the cell is not editable (double-click → opens the FormulaEditor, the one edit path — the column ⋯ menu too).
• R5. Query integration: ComputedValueProvider now resolves FORMULA values: cache key formula:{dbId}:{propId}:{recordId} (TTL 60 s, skipped when usesNow — header note 3), evaluation batch (one pass over the page's records, sharing the already-fetched EAV values — no extra DB round-trip; the step-limit budget is per-record, a page of 100 records × 10k steps max = 1M steps worst case — the per-request guard: 200k steps total per query, 422? no — the over-budget records render the error badge and the query returns; a 500 is never correct here; recorded), values.formulaPid populated.

Acceptance Criteria

• [ ] E2E (save): create a FORMULA property with if(prop("Status") == "Done", 100, 0) (the spec example) → saved; formulas row has the AST JSON (asserted via API), returnType: "number", dependencies: ["Status"]; the table cell shows 100/0 per record status.
• [ ] E2E (editor): typing if(prop("Statu") shows the autocomplete suggestion "Status" (fuzzy) and the live error (before completion) "Unknown property 'Statu'. Did you mean 'Status'?" with the red underline at the right offset; Tab-accepting the completion clears the error; the returnType badge shows "number"; the preview pane shows the value for the first record.
• [ ] E2E (error badge): save length(prop("Tags")) where Tags is a TEXT property (not an array) → per TASK-0084, length on a string is its length (valid!) — use a true runtime case instead: a record whose value shape breaks the formula at eval (e.g. formula prop("N") / 0 where N = 0 on one record → division by zero = RUNTIME "Division by zero" (the engine adds explicit zero-division detection — TASK-0084's + arithmetic: x / 0 → RUNTIME, recorded as an engine detail this task verifies is implemented; if TASK-0084 left it as IEEE Infinity, this task adds the 2-line guard + golden and notes it) → that one record shows the red ! badge, the other records show values.
• [ ] Integration (usesNow): a formula dateBetween(prop("Created"), now()) → usesNow derived from the AST → the query response is not cached for it (two queries 1 s apart return different values as now advances — the test freezes/advances time); a non-now formula is cached (second query = cache hit, Redis spy).
• [ ] Integration (save-time rejection): prop("Missing") + 1 → 400 VALIDATION_ERROR with the suggestion; 1 + → 400 PARSE with pos; nothing stored (formulas count unchanged); a 5001-char expression → 400 length.
• [ ] E2E (preview picker): switch the preview to a specific record (the 5th of 10) → the preview value matches GET …/preview?recordId= for that record (network + DOM parity).
• [ ] A11y: the editor input is a real textarea (aria-label="Formula expression"), errors announced via aria-live=polite (the message region), the autocomplete popup is role=listbox with arrow navigation, the preview pane has aria-label="Formula preview: <record title>"; full keyboard E2E (type, Tab-complete, Esc, save — no pointer).

Backend Changes

• apps/api/src/modules/relations/formulas.ts (CRUD + preview), OnReadFormulaProvider in apps/api/src/modules/databases/computed.ts (the TASK-0083 interface's second implementation + the per-request step-budget guard), packages/shared DTOs (FormulaCreateSchema, FormulaPreviewSchema).

Frontend Changes

• apps/web/database/formula/{FormulaEditor,FormulaHighlighter,CompletionPopup}.tsx, apps/web/database/cells/FormulaCell.tsx (replace placeholder), the property picker's FORMULA row flips to the active config (name + opens the editor), apps/web/database/properties/FormulaConfigForm.tsx.

Database Changes

• None (formulas table exists; usesNow derived, not stored — recorded).

API Changes

• New: formula CRUD + preview (4 endpoints, docs/api/relations.md); POST /databases/:id/query values populated for FORMULA properties (writes remain 422 computed_property_readonly — only the RELATION write path was lifted in TASK-0081 header note 4).

Tests

• Unit: the save-time validation matrix (the 5 rejection cases + success shapes), the usesNow derivation (AST fixtures incl. nested now() inside if — still flagged), the per-request step-budget guard (a page fixture where 3 of 100 records blow the budget → those 3 get error values, 97 get values, no exception).
• Integration: the 4 scenarios above (Redis spies, time freezing via a test clock in the provider's ctx.now), the preview endpoint's per-record evaluation (a record the caller can't view? — the preview requires db VIEW only, the record is a page in the same db — a NONE-grant record → the preview 404s it, asserted).
• E2E: the 5 scenarios (TASK-0089 hosts the full "formula recompute on dependency change" — the recompute itself is TASK-0086; this task's E2E proves the authoring + read path).

Security

• The expression is untrusted input: it never executes (AST-only, TASK-0084); the ast stored in JSONB is re-validated against the shared AST schema at every read (a tampered row — direct DB write — yields VALIDATION_ERROR and the record's cell shows the error state, never code execution — the "corrupted AST" path is integration-tested); the preview endpoint evaluates as the caller (VIEW-scoped record visibility); autocomplete data (property names) is non-sensitive; the client-side validateFormula is a UX convenience only (the server re-validates — AGENTS.md rule 19); no secrets in error messages (the pos snippet is the user's own expression, fine to echo).

Documentation

• docs/api/relations.md formulas section (save-time vs runtime error split — the table of which FormulaError.code appears where: 400 at save vs cell badge at read); packages/database-engine/src/formula/README.md cross-ref from the editor.

Dependencies

• TASK-0084 (the engine — the single source for autocomplete/validation/evaluation), TASK-0083 (the ComputedValueProvider interface + cache pattern), Phase-5 (property CRUD, cells, the property picker), packages/ui dialog primitives.

Definition of Done

• Backend implemented; frontend implemented (editor + cell + config form); no migration; API validation (save-time 400s with pos/suggestions, length cap); authorization (EDIT to author, VIEW to preview/query — matrix tested); error states (the red badge + its a11y, the 400 messages, the corrupted-AST read path); loading states (editor validation spinner (≤ 200 ms debounce, no spinner under 100 ms — a subtle border pulse instead), preview "Evaluating…"); empty states (preview on an empty db — the "(empty record)" state); mobile (editor full-screen, preview below the input, completion popup above the input); accessibility (the full contract in R2/R4 — E2E keyboard pass + axe); unit + integration + E2E; docs; logging (formula evaluations: per-request step usage, error counts by code, cache bypass count for usesNow); audit: none mandatory (formula ops ride database.updated — recorded).

---

TASK-0086

Title

Formula/rollup evaluation pipeline (worker recompute, depth limit 5, cycle guard, materialization)

Objective

Make computed properties stay correct as data changes: the event-driven recompute pipeline — record.updated / relation.linked|unlinked → find the computed properties whose dependencies (formulas) or relation-target values (rollups) were affected → enqueue recompute jobs (BullMQ) with a depth limit of 5 and a cycle guard (formula → rollup → formula chains terminate) → recompute the affected records' values → write results (Redis cache invalidation on the small-dataset path; record_properties materialized rows on the large-dataset path per header note 2) → emit record.updated {changedProperties: [computed…], source: 'computed'} so downstream consumers (search, Phase-11 automations) see the final values.

Background

Header note 2 (on-read default + materialized above ROLLUP_MATERIALIZE_THRESHOLD = 5,000 records with ≥ 1 computed property, env-tunable) is implemented here: MaterializedRollupProvider/MaterializedFormulaProvider (the TASK-0083 interface's second path) read record_properties rows for computed properties when the db is materialized. The dependency graph: formulas depend on properties (their dependencies names) — a record.updated with changedProperties intersecting dependencies invalidates the record's formula values; rollups depend on the linked records' values + the link set itself (relation.linked/unlinked); a formula can reference a rollup property? — decision, recorded (scope): prop() resolves to direct properties only (text/number/select/…/relation) — a formula referencing a rollup or another formula's property is a save-time TYPE error in Phase 6 ("computed properties cannot be referenced by formulas; use rollups to combine relation data"). This makes the dependency graph acyclic by construction (formulas → direct properties; rollups → relation → target db's direct values), and the depth-5/cycle guard is a defense-in-depth assertion (it can never fire on a valid graph — it fires on data corruption or a Phase-7+ extension, and must be tested with a hand-crafted cyclic fixture to prove the guard works, not merely that the graph is acyclic). Phase-7+ (multi-db formula references) lifts the restriction and the guard becomes load-bearing — recorded for that phase's task file.

Requirements

• R1. Event subscription (apps/worker/src/jobs/computed-recompute.ts — a BullMQ consumer, a new consumer group computed-recompute per the backend-architecture §7.3 group model — distinct from TASK-0083's api-process cache invalidation; both run: the api process flushes Redis fast-path, the worker does the heavy recompute/materialization):
  • on record.updated {databaseId, recordId, changedProperties[]}: for each formula in the db whose dependencies ∩ changedProperties ≠ ∅ → mark (dbId, recordId, formulaPropertyIds) dirty; for each rollup whose relation's target db = this db and whose targetPropertyId ∈ changedProperties → mark the affected source records (the reverse-link lookup, one indexed query — the TASK-0083 R4 affected-set, reused) dirty;
  • on relation.linked/relation.unlinked {recordId, targetRecordId, databaseId, targetDatabaseId}: the linked record's rollups (target = the relation's target db, if this event's direction matches) + the unlinked target's ex-partners (both directions of the pair — relation.unlinked carries both recordId and targetRecordId, the event's payload from TASK-0081) → mark dirty;
  • dirty sets are coalesced per (dbId, recordId) with a 1 s debounce window (BullMQ removeOnComplete + a per-db delayed job — the job payload is {dbId, recordIds[], computedPropertyIds[]}; a burst of 1,000 record.updated in 1 s = ≤ 10 jobs of 100 records, not 1,000 jobs — the debounce is a delayed-queue re-enqueue with a unique job key recompute:{dbId}:{recordId} (BullMQ removeOnFail: 3, attempts: 5, exponential backoff — the dead-letter after 5, logged computed.recompute.dead).
• R2. Recompute job body (in packages/database-engine where pure — the "plan" function; the I/O in the worker): planRecompute(dirty, graph) → {evaluations: [{recordId, propertyIds, inputs}], depth: n} — depth limit 5: the planner walks the dependency graph from the dirty records (formula: record → its formula values; rollup: record → linked target values → source rollups); each hop = +1 depth; a plan deeper than 5 → the job logs computed.recompute.depth_limited (with the db + the truncated chain) and computes only the first 5 hops (the deeper effects are picked up by the next event — eventual consistency, recorded) — in a valid Phase-6 graph the depth is ≤ 2 (record → formula; record → link → rollup), so the limit is headroom, not a constraint (asserted in tests on the real graph shapes); cycle guard: the planner keeps a visited set of (recordId, propertyId) per plan; a revisit → computed.recompute.cycle_detected + the cycle is broken at the revisit (that property is skipped for this pass, the event is re-queued once for the next pass — the self-healing path; a hand-crafted cyclic fixture proves the guard fires and terminates — the test writes a formula + rollup that reference each other directly in the DB (bypassing the save-time validator) to simulate the Phase-7+ world, and asserts the worker terminates in ≤ 2 passes with no infinite loop (a 5 s watchdog in the test)).
• R3. Evaluation + write: for each (recordId, propertyIds) in the plan: fetch the record's direct values (one batched EAV query) + the rollup inputs (the TASK-0083 collectLinkedValues batch) → evaluateFormula/aggregateRollup (the TASK-0084/0083 engines — no new evaluation logic, AGENTS.md rule 5) → write: (a) always DEL the Redis on-read cache keys (so the on-read path never serves a stale value even on materialized dbs — the cache is a pure speed layer, the materialized row is truth for reads on large dbs); (b) if the db is materialized (the threshold check, computed once per job from a dbmeta field — computedCount cached): upsert record_properties rows (recordId, propertyId, value) (the computed property's EAV row — the schema supports it; value is the aggregate/eval result, the codec toWire shape); (c) emit record.updated {databaseId, recordId, changedProperties: [computedPids], source: 'computed'} — with source: 'computed' (a new optional field on the envelope payload — additive; consumers can skip re-triggering: the worker's own subscription ignores source: 'computed' events for the recompute trigger — but not for rollup invalidation: a computed value changing can change a rollup that sums it? — Phase 6 scope: rollups sum direct properties only (a rollup's targetPropertyId is validated to be a direct type at create — TASK-0083's type validation extended to reject FORMULA/ROLLUP targets — recorded); so source: 'computed' events are safe to ignore wholesale by the recompute trigger; the search indexer (Phase 8) still consumes them — the searchBody rebuild on a computed change is the TASK-0077 pipeline's job, flagged for Phase 8's task file).
• R4. Read-path swap: the query pipeline's ComputedValueProvider selector: db materialized? → MaterializedProvider (reads the EAV rows; a missing row = not-yet-recomputed → fall through to on-read compute for that record and populate the row lazily — the lazy-fill makes a fresh materialization (a db crossing the threshold after data already existed) correct without a backfill job; the backfill job itself: POST /v1/databases/:id/computed/backfill (admin/EDIT, the migration tool — enqueues a full recompute of all records in pages of 100; the threshold-crossing detection (the dbmeta count check) auto-triggers the backfill once, guarded by a materializedAt stamp so it runs once per crossing — recorded) → OnReadProvider (the TASK-0083 path).
• R5. Observability: per job: duration, records evaluated, evaluations, errors by FormulaError.code, depth reached, cache DELs, materialized upserts; Prometheus counters computed_recompute_jobs_total{outcome}, computed_recompute_evaluations_total, computed_recompute_errors_total{code}, computed_recompute_depth_max (gauge); the p95 job duration budget: < 2 s for a 100-record job (CI perf fixture).

Acceptance Criteria

• [ ] Integration (the exit-critical path — "rollups recompute on relation change < 5 s"): a materialized db (seeded above the threshold, the test sets the env var to 100) — update a linked task's Number value → record.updated → the worker job runs (< 2 s) → the source project's rollup record_properties row is upserted → POST /query (MaterializedProvider) returns the new value without a cache (the materialized row is the answer); total event→fresh-read latency < 5 s measured (the E2E in TASK-0089 measures the same path end-to-end).
• [ ] Integration (formula dependency): a formula prop("A") + prop("B") (dependencies [A, B]); a record.updated changing only C → the job does not recompute the formula (the plan is empty — the job is a no-op with 0 evaluations, asserted via the job payload log); changing B → the formula recomputes and the row upserts (materialized db).
• [ ] Integration (depth + cycle): the real graph shapes reach depth ≤ 2 (asserted on the fixtures); the hand-crafted cyclic fixture (direct-DB-write formula↔rollup) → cycle_detected logged, the worker terminates in ≤ 2 passes (5 s watchdog), no job storm (≤ 3 jobs total for the fixture, the re-queue-once rule asserted).
• [ ] Integration (coalescing): 1,000 record.updated events for 1,000 distinct records of one db in 1 s → ≤ 10 recompute jobs (100-record pages), all 1,000 rows eventually consistent (a final query after 10 s returns all updated values — eventual-consistency assertion with a bounded wait).
• [ ] Integration (threshold + backfill): a db at 4,999 records (on-read) gets 2 more records (crosses 5,000 with a computed property) → materializedAt stamped, one backfill job runs (exactly one — the guard), all 5,001 rows materialized (spot-check 100), reads switch to the MaterializedProvider (the dbmeta flag + a row-presence assert).
• [ ] Integration (lazy fill): on a materialized db, a record created after the last recompute (a new record with formula inputs) → the first query computes it on-read and populates the materialized row (the second query reads the row — Redis/DB spies assert both steps).
• [ ] Perf: a 100-record recompute job (50 formulas + 50 rollups, real engine) completes < 2 s (p95 of 10 runs, CI artifact).

Backend Changes

• apps/worker/src/jobs/computed-recompute.ts (consumer + job body), packages/database-engine/src/computed/{plan,graph}.ts (the pure planner: dirty → plan, depth + cycle guard — unit-testable standalone, AGENTS.md rule 23), apps/api/src/modules/databases/computed.ts (the provider selector + the MaterializedProviders + the dbmeta computedCount/materializedAt fields + the backfill endpoint POST /v1/databases/:id/computed/backfill), the source: 'computed' envelope field in packages/shared/src/events (additive), the rollup targetPropertyId direct-type validation in TASK-0083's endpoint (a 2-line guard, recorded as this task's fix).

Frontend Changes

• None (the read path is transparent — the cells from TASK-0082/0083/0085 render whatever the query returns; this task is pipeline-only).

Database Changes

• None (the materialized rows use the existing record_properties table; dbmeta fields are database_views-adjacent? no — they live in the Redis dbmeta:{dbId} cache object (never a schema change — computedCount is derived by a COUNT with a cache, materializedAt is a Redis stamp; a restart re-derives both — recorded: no new columns, the stamp is re-established by the first post-restart threshold check, which re-runs the backfill only if no materialized rows exist — the idempotent guard)).

API Changes

• New (admin-facing): POST /v1/databases/:id/computed/backfill (EDIT/FULL_ACCESS; 202 {jobId}; the job is observable via the worker's queue API — the Phase-0 ops surface).

Tests

• Unit (packages/database-engine/src/computed/__tests__/): the planner (the dirty→plan fixtures: formula intersection, rollup reverse-lookup, the depth-2 real shapes, the depth-limit truncation fixture at 6, the cycle fixture termination), the coalescing key scheme, the threshold-crossing predicate.
• Integration: the 6 scenarios above (worker + Redis + Postgres + stream capture; the cycle fixture's direct-DB-write is explicitly marked as a corruption-simulation in the test comment).
• Perf: the 100-record job budget (CI artifact).

Security

• The worker consumes events with the actor context (the envelope's actor — recomputation writes are system-attributed with the original actor in the job metadata for audit trails — the record_properties upserts carry no user identity beyond the envelope; audit: computed writes do not emit record.updated audit rows for the computed change? — recorded decision: they do emit the event (consumers need it) but the audit row is tagged source: 'computed' and the audit UI (Phase 14) filters it from "user actions" — the event's source field is the marker; the Phase-14 task file references this); the backfill endpoint is FULL_ACCESS-gated (it rewrites mass data); no event can trigger a recompute in another workspace (the job is keyed by dbId, tenant-scoped — a cross-tenant event + dbId pair is impossible by the event's construction, and the job re-verifies the db's workspace at run time — a 404-style job abort if mismatched, defense in depth).

Documentation

• docs/architecture/database-architecture.md §1.4 — the materialization decision made normative (header note 2); apps/worker/README.md (the new queue + consumer group, the job payload schema, the dead-letter behavior); docs/api/relations.md — the backfill endpoint + the source: 'computed' event field.

Dependencies

• TASK-0081 (links + the relation events), TASK-0083 (rollup engine + the provider interface + the affected-set lookup), TASK-0084/0085 (formula engine + the dependencies/usesNow data), Phase-5 (query pipeline, dbmeta cache), Phase-0 (BullMQ infrastructure, worker scaffolding), Phase-8 (consumes source: 'computed' — out of this phase), Phase-7+ (multi-db formula references lift the acyclicity-by-construction — the guard becomes load-bearing; flagged).

Definition of Done

• Backend implemented (worker + planner + providers + backfill); frontend: None (genuinely N/A — pipeline-only, recorded); no migration (the no-new-columns decision is recorded + tested); API validation (the backfill endpoint's auth + idempotency); authorization (FULL_ACCESS backfill, the matrix tested); error states (dead-letter + the cycle guard + the depth-limit — all with the logged artifacts the acceptance criteria assert); loading/empty states (N/A — no UI); mobile (N/A); accessibility (N/A); unit (planner, standalone) + integration (6 scenarios) + perf (job budget); E2E: the user-facing proof is TASK-0089 (this task's integration tests are the pipeline's E2E-equivalent — recorded); docs; logging + metrics (the R5 set, all wired to the Phase-0 observability stack); audit: the source: 'computed' tagging (the Phase-14 filter contract, recorded).

---

TASK-0087

Title

Database buttons (7 action types, execution endpoint, row + panel button UI)

Objective

Implement BUTTON properties: POST /v1/databases/:id/buttons (config: name, icon, color, actions[]), the 7 action types (set_property, create_page, update_page, archive_page, duplicate_page, send_webhook, trigger_automation), the execution endpoint POST /v1/databases/:id/records/:rid/buttons/:pid/run (sequential execution with a per-action result report, caller's permissions, audited), and the button UI (row + record-page panel, with the per-action config forms).

Background

PR-083 P1; the Phase-6 exit criterion: "button actions E2E-tested." functional-requirements §5.5: "BUTTON: config {name, icon, color, actions[]}; action types: set_property, create_page, update_page, archive_page, duplicate_page, send_webhook (async), trigger_automation (async); executes with the caller's permissions (the button cannot escalate); runs are audited (database.button.executed)." Header note 1 (webhook/automation = Phase-13/11 contracts, Phase-6 stubs the worker side, the button reports "queued"). The buttons table exists (config Json, propertyId?). database.button.executed is not in the backend-architecture §7.2 frozen event catalog — it's an audit event (the §9 mandatory-list "automation runs" analog — audit rows, not stream events; recorded resolution: the execution writes an AuditLog row per the AGENTS.md mandatory rule, type database.button.executed, in-transaction with the run's effects where they're transactional, best-effort after the async enqueues).

Requirements

• R1. Action config schema (packages/shared/src/dtos/buttons.ts — ButtonActionSchema discriminated union):
  • set_property {propertyId (a direct property of this db — a computed one → 400), value (codec-validated per the property type — the TASK-0064 codec), setTo? ('value' | 'empty')} — applies to the record the button runs on;
  • create_page {titleTemplate? (supports {Title}/{<prop>}tokens — resolved from the record's values; default "Untitled"), icon?, description?, properties? (pre-sets on the created page? no — create_page creates a plain **page** (not a record) unlessdatabaseIdis given:databaseId?(a db in the same workspace, EDIT required) → creates a **record** in that db withpropertiespre-set (same codec validation);parentPageId? (the page to nest under — EDIT required there)};
  • update_page {pageId? (fixed) | 'self' (the record's own page), patch: {title?, description?, icon?, coverUrl?}} — the self target is the common case ("rename me to Done: {Title}");
  • archive_page {target: 'self' | pageId} — moves the page to trash (the Phase-3 trash flow — recoverable);
  • duplicate_page {target: 'self' | pageId, titleSuffix? (default " (copy)")} — the Phase-3 page duplicate (deep copy: blocks + child pages; for a record page, the record's property values too — the record duplicate semantics from TASK-0065);
  • send_webhook {webhookId (an existing workspace webhook — the Phase-13 webhookstable? the webhooks module is Phase 13; Phase 6:webhookIdis a **string reference** validated only for workspace ownership when the Phase-13 table exists — resolution (header note 1): Phase 6 storeswebhookIdas an opaque string + apayloadTemplate?(a{token}-templated JSON string, the tokens = the record's values), and the execution enqueues a BullMQ webhook-deliveriesjob{webhookId, payload}— the worker-side dispatcher is the Phase-13 consumer; until then the job is a **stub consumer** (this task ships it: it validates the payload, logswebhook.delivery.stub, no-ops — the queue name + payload shape are frozen for Phase 13)};
  • trigger_automation {automationId (an existing Automationrow — the Phase-11 table exists in the schema; Phase 11 owns the engine. Phase-6 resolution (header note 1): the execution callsAutomationService.triggerManual(automationId, {source: 'button', recordId, actor})— Phase 11 provides the method; Phase 6 ships the **interface + stub**: aManualTriggerPortinterface inpackages/sharedwith aStubManualTriggerimplementation (logsautomation.trigger.stub {automationId, recordId}and returns{status: 'stub'}) that Phase 11 replaces by DI swap — the port is the frozen contract; a non-existent automationId→ the run reports that action aserror: 'NOT_FOUND' (the button doesn't fail the whole run — per-action results)}.
  • Button config overall: {name (≤ 50, unique per db), icon (emoji, default "▶️"), color (the 9-button token palette — the packages/ui button colors), actions: ButtonAction[] (1–5, ordered)} — 5 actions max (the UI + the validation; a longer chain is a Phase-11 automation, documented).
• R2. API (apps/api/src/modules/relations/buttons.ts):
  • POST /v1/databases/:id/buttons — create (validates every action: property existence + codec value, page/database existence + the caller's current EDIT grant on the target — a button can pre-encode an action on a page the caller can edit now; the grant is re-checked at run time too — a button cannot outlive a revoked grant (AGENTS.md rule 7 — the run-time check is authoritative, the create-time check is UX), the databaseId record-create's property validation) → the BUTTON property (config: {buttonId}) + the Button row → schemaVersion bump.
  • PATCH /v1/databases/:id/buttons/:bid — rename/icon/color/actions (full re-validation); DELETE — soft-delete property + button.
  • POST /v1/databases/:id/records/:rid/buttons/:pid/run — the execution endpoint: permission = EDIT on the record's db (the button runs as the caller — no escalation: every action's per-target grant is re-checked with the caller's token; a denied action → that action's result is {status: 'denied', reason} and the run continues (sequential, the spec's "runs actions sequentially with result report") — the whole endpoint is 200 with the report (a 403 would be for the endpoint-level EDIT grant only); execution: actions in order, synchronous actions (set_property → the record PATCH path (in-process service call, same codec validation — a value the caller could also have set directly; the button adds no power), create_page/update_page/archive_page/duplicate_page → the PageService/record services (in-transaction per action, each action = its own transaction — a mid-run failure (action 3 of 5) leaves actions 1–2 applied (documented: sequential, no cross-action rollback — the report says which succeeded) — recorded decision against an all-or-nothing transaction: page operations are not committable in one transaction across the modular-monolith's service boundaries without a saga, and the spec's "result report" implies per-action outcomes); async actions (send_webhook/trigger_automation) → enqueue → {status: 'queued'}; response {executedAt, results: [{actionIndex, type, status: 'ok'|'denied'|'error'|'queued', detail?}]}; audit row database.button.executed (in-transaction for the sync effects, best-effort after the enqueues — the backend-architecture §9 rule); emits no domain event of its own (the actions' own events fire: record.updated, page.created, … — consumers see the effects normally; recorded).
• R3. Button UI:
  • Row (table view): the BUTTON property renders as a compact button chip (icon + name, the color palette, role=button, aria-label "Run <name> on <record title>") — click → a confirm popover (the action list rendered as plain rows: "Set Status to Done", "Create page '…'", "Send webhook" — the user sees what will happen before clicking; "Run" (primary) / "Cancel"; the popover is the standard role=dialog; on run → the results: a toast per failed action, the cell re-fetches the record (the changed properties update the row optimistically from the report's set_property results));
  • Record-page panel: the button renders as a full-width row button (icon + name + a chevron showing the action count); same confirm + run flow; after a run with create_page, the result report's created page id → the toast has an "Open" action (deep link);
  • Config form (the property picker's BUTTON row, now active): name/icon/color + an action list editor (add action → the type picker (7) → the per-type config form: set_property → property picker (direct properties) + the type's value editor (the TASK-0066 cell editors, reused); create_page → title template input (with a token-completion list: the db's property names) + optional target-db/page pickers (search over the workspace); update_page → target picker (self / a page search) + the patch fields (title/description with token completion); archive/duplicate → target picker; send_webhook → webhook id text input (Phase-6: free-form, the Phase-13 webhook picker replaces it — the input shows a "webhooks arrive in Phase 13; the id is stored as-is" hint, recorded) + payload template (a JSON textarea with token completion + JSON validation); trigger_automation → automation picker (search over GET /automations — the Phase-11 module's read endpoint? the automations module exists in the schema + module table; if its list endpoint isn't built yet in Phase 6, the picker is a free-form id input with the same hint — the task checks the module state and uses the endpoint if present, else the input, recorded either way in the implementation); delete/move up/down per action; the Save validates the whole config (the server 400s map to field errors).
  • Read-only (VIEW): the button renders disabled with a tooltip "You need edit access to run buttons" (a button is an action, not data — VIEW users don't see the action list, only the disabled chip — no action enumeration for non-editors, recorded).

Acceptance Criteria

• [ ] E2E (the exit criterion — "button sets property + creates page"): a button "Close out" with actions [set_property Status=Done, create_page {titleTemplate: "Retrospective: {Title}", parentPageId: the db's page}] → run on a record → the report has 2 × ok with the created page id; the record's Status cell shows Done (one record.updated); the child page exists with the resolved title (the token replaced with the record's title — asserted via API); a second run → the page is not duplicated (create_page has no idempotency in Phase 6 — a second run creates a second page; the E2E asserts the documented behavior: 2 runs = 2 pages, recorded).
• [ ] E2E (per-action results): a button [set_property (valid), update_page (a page the user lost EDIT on — the test revokes mid-test), archive_page (self)] → run → report [ok, denied, ok] — the archive happened (the page is trashed), the update didn't (the page unchanged), the set_property applied; the denied toast shows the reason.
• [ ] E2E (async stubs): [send_webhook, trigger_automation] → report [queued, queued]; the BullMQ queue has the 2 jobs (the queue API test); the stub consumers ran (the webhook.delivery.stub/automation.trigger.stub log lines asserted via the test log capture); no crash, the run completed in < 1 s.
• [ ] Integration (escalation guard): a user with EDIT on the db but no grant on page P; a button (created while they had the grant, then the grant revoked) with update_page {pageId: P} → run → that action denied (the run-time re-check — the create-time grant is not cached authority).
• [ ] Integration (validation): an action's set_property value failing the codec (a string into a NUMBER) → 400 at button create (field actions[0].value); 6 actions → 400 (the 5-max); a set_property targeting a ROLLUP property → 400 computed_property_readonly.
• [ ] A11y: the row chip is keyboard-operable (Enter runs → the confirm popover opens, role=dialog, Tab cycles Run/Cancel, Esc returns focus to the chip, Enter on Run); the action list in the popover is read-only text (not focusable — it's a description); the disabled (VIEW) chip has aria-disabled + the reason in the accessible name; E2E keyboard-only run (no pointer) + axe clean.
• [ ] Audit: after a run, the audit log (as ADMIN) has the database.button.executed row with the button id, the record id, the action count, and the caller id.

Backend Changes

• apps/api/src/modules/relations/buttons.ts (CRUD + the run endpoint), the run-time grant re-checks (PermissionService calls per action target — the packages/permissions engine, the same one as the guards), the ManualTriggerPort interface + StubManualTrigger in packages/shared (the Phase-11 swap point), the stub webhook-deliveries consumer in apps/worker/src/jobs/webhook-stub.ts (the Phase-13 swap point), the audit row type database.button.executed in packages/shared/src/errors.ts's audit catalog.

Frontend Changes

• apps/web/database/buttons/{ButtonCell,ButtonConfirmPopover,ButtonConfigForm,ActionForm}.tsx (replacing the Phase-5 placeholder cell), the record-page panel wiring (drop-in cell reuse), the toast "Open" action for created pages.

Database Changes

• None (buttons table exists; no new tables — the webhook/automation references are opaque ids by the header-note-1 resolution).

API Changes

• New: button CRUD + run (5 endpoints, docs/api/relations.md buttons section — the run report schema is frozen for the Phase-11/13 consumers).

Tests

• Unit: the action schema validation matrix (per type × bad value, the 5-max, the computed-target 400), the token template resolver ({Title}/{<prop>} escaping — a title containing { must not be re-interpreted: the resolver substitutes in one pass, unit-tested with hostile titles), the report builder (sequential outcomes).
• Integration: the 4 scenarios (escalation guard, the per-action-denied run, the stub enqueues + the stub consumers' log lines, the audit row), the run endpoint's 403 (no EDIT on the db) vs 200-with-denied-actions (EDIT on the db, no grant on a target).
• E2E: the 3 scenarios + a11y (TASK-0089 hosts them).

Security

• The button is a stored procedure over the user's current grants: create-time validation is UX, run-time re-check is law (the escalation guard is an explicit acceptance criterion); the button cannot set computed values (400), cannot reference another workspace's pages/records (the pickers + the server both scope to the workspace — a cross-workspace pageId → 404 at run time, the action is error: 'NOT_FOUND'); the webhook payload template is a string the stub logs — no outbound HTTP in Phase 6 (the Phase-13 dispatcher adds the egress controls: allowlists, signing — flagged for that task file); trigger_automation goes through the port (Phase 11's engine owns the conditions/actions validation — the Phase-6 stub doesn't execute automation logic); audit is in-transaction where possible (a crashed run mid-action 3 has audit rows for actions 1–2 — the partial-run audit is the documented behavior, consistent with the no-cross-action-rollback decision); the run report is returned to the caller only (no cross-user report access — there's no report history endpoint in Phase 6; the audit log is the durable record).

Documentation

• docs/api/relations.md buttons section (the run report schema, the sequential/no-rollback decision, the stub contracts for Phase 11/13 with the swap points named); apps/web/database/README.md button cell contract.

Dependencies

• TASK-0065 (record create/duplicate/archive — the action targets), Phase-3 (PageService duplicate/trash, page create), TASK-0064 (codecs for set_property), Phase-11 (ManualTriggerPort swap — out of this phase), Phase-13 (webhook dispatcher swap — out of this phase), Phase-0 (BullMQ, the stub consumer's queue), packages/permissions (the run-time checks).

Definition of Done

• Backend implemented (CRUD + run + stubs + port); frontend implemented (cell + popover + config form + panel wiring); no migration; API validation (per-action 400s with field paths, the 5-max, the computed-target guard); authorization (the create-time AND run-time matrix — both tested, the escalation guard is the headline); error states (the per-action denied/error results + their toasts, the disabled VIEW chip with reason); loading states (the run button spins during execution — sync runs < 1 s typical, the spinner is honest); empty states (a button with 0 actions? — the schema requires ≥ 1, so no empty state; a db with no buttons → the property picker's BUTTON row is the only surface, no empty state needed); mobile (the row chip ≥ 44 px, the confirm popover a bottom sheet); accessibility (the full keyboard run + axe, the R3 contract); unit + integration + E2E; docs; logging (every run: button id, record id, per-action status + duration, the stub consumer logs); audit: database.button.executed (AGENTS.md mandatory — the AGENTS.md rule 27 "automation runs" analog, recorded as a mandatory event by this task).

---

TASK-0088

Title

Sub-items (isSubItem records, child list in the parent page, navigation up, rollup dependencies across sub-items)

Objective

Implement sub-items: records with isSubItem = true that render inside their parent record's page (a "Sub-items" child list with inline create), an up-navigation back to the parent, sub-item creation (a record in the same db, flagged), and the dependency tracking that makes rollups computed over sub-items stay correct (a sub-item's record.updated invalidates the parent's rollups too — the dependency edge is record-level, not just database-level).

Background

functional-requirements §5.2: "Sub-items: records that are children of another record (isSubItem flag + parentRecordId? — schema check: database_records has isSubItem but the parent link is via a relation: the standard Notion pattern is a relation from the db to itself (self-relation) + the sub-item flag. The database_records model in prisma/schema.prisma carries isSubItem (flag) — the parent is modeled as a self-relation (the db's relation to itself), and "sub-items of R" = R's links via the self-relation where the linked record has isSubItem. This task therefore: (a) makes the self-relation first-class in the relation UI (the relation picker allows targetDatabaseId = the same db — TASK-0081's self_relation 400 is lifted for the sub-item use case only: the create endpoint gains purpose: 'sub_items'? — a self relation with that purpose is the db's sub-item relation, unique per db (409 if one exists), its property is hidden from the normal column list (isVisible: false, the cell renders as the sub-item list, not a link chip), recorded resolution against TASK-0081's 400 (the 400 stands for self-relations without the purpose); (b) the record-page sub-item list (the children of the record via the self-relation, filtered to isSubItem records); (c) the inline create (a new record, isSubItem: true, linked to the parent, the TITLE prefilled from the row input); (d) navigation up (a "Sub-item of: <parent title>" breadcrumb above the sub-item's record-page panel, click → the parent); (e) dependency tracking (TASK-0086's planner gains the sub-item edge: a sub-item's change invalidates the parent's rollups that roll up the self-relation — the rollup's link set includes the sub-item, so the rollup is already correct on recompute; the edge needed is: a sub-item's change must recompute the parent's formula values? no — formulas reference the parent's own direct properties only; the edge is: a sub-item's change recomputes the parent's rollups (already covered by the link-based invalidation — the sub-item is a linked record) AND recompute the sub-item's own rollups (its own relation data) — the genuinely new edge is the transitive one flagged in the roadmap line "dependency tracking for rollups across sub-items": a grandparent's rollup over a relation that targets the sub-item's db- self-links? — Phase-6 scope (recorded, bounded): invalidation is one hop (the TASK-0086 depth model — a sub-item change invalidates the direct parent's rollups; a chain of sub-items (sub-of-sub) invalidates each level in the next pass — the re-queue-once mechanism already in the planner handles it; the E2E asserts a 2-level sub-item chain converges in ≤ 2 passes).

Requirements

• R1. Self-relation lift (TASK-0081 extension): POST /v1/databases/:id/relations gains purpose?: 'sub_items' — with it: targetDatabaseId = the same db is allowed (the self_relation 400 fires only without the purpose), bidirectional: false forced (a sub-item relation is one-directional: parent → children; the "children" side is read via the same links endpoint with direction: reverse — no reverse property is created), the property is created with isVisible: false + config: {relationId, multiple: true, purpose: 'sub_items'} (the cell + the column list + the filter builder all treat purpose: 'sub_items' properties specially: hidden from the normal column list, excluded from the filter/sort pickers, the cell renders the sub-item list — one property type, a purpose-flagged variant, recorded), uniqueness: one sub-item relation per db (409 sub_items_relation_exists); the relation's name is auto "Sub-items" (not user-editable via the normal rename — the rename endpoint 409s for purpose relations, recorded).
• R2. Record-page sub-item list (apps/web/database/record-page/SubItemList.tsx): rendered in the record page's right pane (the blocks area), as a dedicated block-like section above the page's blocks (it's not a page block — it's database chrome, pinned, like the property panel's sibling; recorded decision against a real blocks row: a sub-item list is a view over the relation, not content — it survives block reordering, it's not draggable, it appears on every device; the page's own blocks remain fully editable below it): header "Sub-items (n)" + a "+ Add" row (an inline input: type a title, Enter → creates the sub-item record — POST /records {title, isSubItem: true, properties: {…defaults}} + the link op PUT …/links (the parent's self-relation links) in sequence — two calls, or the records endpoint gains subItemOf?: recordId which does both in one transaction — decision, recorded: the one-call form, subItemOf, added to RecordCreateSchema (the server creates the record + the link atomically; the property defaults apply as normal)); each sub-item row: checkbox-style indent, title (click → the sub-item's record page), a status-ish chip (the first SELECT/STATUS property's value, like a board card), a ⋯ (open, duplicate, archive — the record actions, no unlink (unlinking a sub-item = removing it from the parent via the link dialog — available in the row ⋯ as "Unlink from <parent>" → the link op; the record itself survives, the flag stays — a "linked but not sub-item"? no — the flag is on the record; unlinking keeps isSubItem: true (the record is a sub-item, just not of this parent — the list shows only linked-and-flagged; recorded edge behavior + toast "Unlinked — the entry remains a sub-item type"), delete (trashes the record, the link cascade from TASK-0081 removes the link)); empty state: "No sub-items yet — add one below" + the Add row; collapsed state? — the list is always visible (it's short by nature; no collapse in Phase 6, recorded).
• R3. Navigation up: a sub-item's record page (a page whose DatabaseRecord.isSubItem = true and that has ≥ 1 link on a purpose: 'sub_items' relation) shows a breadcrumb above the panel: "← Sub-item of: <parent title>" (click → the parent's page; the parent title is fetched via the links endpoint's reverse read, one call, cached in useRecordPage); if the sub-item is linked to multiple parents (multi-select self-relation — allowed!), the breadcrumb lists up to 2 + "+n more" (each clickable); if isSubItem but linked to no parent (orphaned by an unlink) → no breadcrumb (the flag alone doesn't create a parent — the link does, recorded).
• R4. isSubItem lifecycle: the flag is set at create (subItemOf implies it, or an explicit isSubItem: true on a standalone create — allowed, the record is sub-item-typed before it's linked — the breadcrumb is absent until linked, the table view shows it like any record (sub-items are records; the table's default query does not filter them out — a database shows all its records; the sub-item list is the filtered view; a table view can filter by… no sub-item filter operator exists — sub-items in the table are indistinguishable from records in Phase 6, documented limitation, the isSubItem column is not a property (it's the record's own field — it can't be a column without a schema change to properties; recorded as a Phase-14 refinement)); clearing the flag: the record ⋯ menu (the record page panel's ⋯) "Remove sub-item flag" → PATCH record {isSubItem: false} (a new field on RecordPatchSchema — additive) → the record leaves the parent's list (the list filters on flag + link).
• R5. Dependency tracking (TASK-0086 extension): the planner's dirty-set computation gains the sub-item edge: a record.updated on a sub-item record S also marks S's parents dirty for rollup purposes (the parents' rollups over the self-relation include S — the link-based invalidation already covers the value change; the flag/link changes need the explicit edge: a relation.linked/unlinked on the self-relation marks the parent dirty (covered by the relation event path) — so the genuinely new planner work is: the reverse-link lookup for a self-relation must include the parent side (the existing lookup is symmetric — verified, not new) + the 2-level chain convergence (sub-of-sub: a grandchild change → the child's rollups (pass 1) → the grandparent's rollups over the child? no — the grandparent's rollup is over its self-relation links (the children), and a child's value changing doesn't change the grandparent's rollup unless the rollup's target property is the child's — which is exactly the value-change path (pass 1 covers the child's own rollups; the grandparent's rollup input = the child's value for the target property → the child's record.updated (fired by pass 1's recompute if the child has a formula/rollup on that property) triggers pass 2 — the event chain is already there; the task's work = prove it with the 2-level E2E + add the planner unit fixtures for the self-relation shapes).
• R6. Sub-item creation defaults + permissions: subItemOf requires EDIT on the db (same as record create) + the parent record must be untrashed (400); the sub-item inherits nothing special (it's a record — page permissions = the db's page inheritance as always); the sub-item's page is not nested under the parent's page in the page tree (sub-item ≠ child page — the page tree and the record sub-item list are orthogonal; the page remains a top-level unparented page like every record page — recorded, matches ADR-006).

Acceptance Criteria

• [ ] E2E (create + render): a db with the sub-item relation (created via the property picker's "Sub-items" special row — the picker shows it as a distinct, once-only option) → open a record's page → the Sub-items section renders (empty state) → type "Task 1" in the Add row + Enter → the row appears (flag + link set — API-asserted: database_records.isSubItem = true, a relation_links row on the self-relation); the table view now shows the new record too.
• [ ] E2E (navigation): open the sub-item's page → the breadcrumb "← Sub-item of: <parent title>" renders → click → the parent's page (URL assert); an orphaned sub-item (unlinked via the row ⋯) → no breadcrumb on its page, it leaves the parent's list, the toast shows the documented message.
• [ ] E2E (rollup across sub-items — the roadmap line): the db has a rollup "Total sub-item estimates" (self-relation, SUM of the sub-items' Number) → the parent's cell shows the sum of its 3 sub-items (10 + 5 + 3 = 18) → update sub-item 2's value (5 → 7) via the table → the parent's rollup shows 20 within 5 s (the event-driven path — the sub-item's record.updated → the parent marked dirty (link-based) → recompute); a 2-level chain (sub-item 1 has its own sub-item, value 2; the rollup targets only the direct links — the 2-level convergence test: change the grandchild → the child's rollup updates (pass 1) → if the child has its own rollup, its value changes → the parent's rollup (over the child's value, if the target property is a rollup? — Phase-6 rollup targets are direct properties only (TASK-0086's guard) → the parent's rollup is unaffected by the grandchild (asserted: the 2-level change converges with the parent's value unchanged, the child's updated — the documented scoping: rollups are one link-hop; "across sub-items" = the direct children of the record, exactly) — the acceptance is: the documented behavior is what happens, asserted).
• [ ] Integration (subItemOf atomicity): a failed create (a bad property value) → no record, no link, no half-state (one transaction — the rollback asserted).
• [ ] Integration (self-relation guards): a second purpose: 'sub_items' self-relation → 409 sub_items_relation_exists; a self-relation without the purpose → 400 self_relation (the TASK-0081 guard intact); renaming a purpose relation → 409; the purpose property is absent from GET /databases/:id's visible list (isVisible: false) and from the filter/sort picker payload (the UI fetches isVisible properties only — the API test asserts the flag, the UI test asserts the absence from the pickers).
• [ ] A11y: the Sub-items section is role=list + role=listitem rows, the Add row is a labeled input (aria-label="Add sub-item title"), the breadcrumb is a role=navigation with a "Back to parent" link (the link text includes the parent title), keyboard: Enter on the Add row creates (E2E, no pointer), the row ⋯ menus are keyboard-reachable; axe clean on the record page with the section.

Backend Changes

• apps/api/src/modules/relations — the purpose: 'sub_items' self-relation path (the 400 lift + uniqueness + the forced settings), RecordCreateSchema.subItemOf + RecordPatchSchema.isSubItem (additive, packages/shared), the record service's atomic create+link for subItemOf, the links endpoint's direction: reverse reused for the breadcrumb fetch (no new endpoint); the planner's self-relation fixtures are TASK-0086's code (this task extends its test files, recorded).

Frontend Changes

• apps/web/database/record-page/{SubItemList,SubItemBreadcrumb}.tsx, useRecordPage (the flag + reverse-link fetch), the property picker's "Sub-items" special row (once-only, the 409 → "already added" toast), the record-page panel ⋯ "Remove sub-item flag", the row ⋯ "Unlink from <parent>" in the sub-item list.

Database Changes

• None (the flag exists; the self-relation uses the existing relations/relation_links; no migration).

API Changes

• Additive: purpose on relation create, subItemOf on record create, isSubItem on record patch; documented in docs/api/relations.md (a Sub-items section).

Tests

• Unit: the self-relation validation matrix (purpose × self/other × bidirectional), the sub-item list derivation (flag ∧ link, multi-parent, orphaned), the breadcrumb resolution (0/1/2/n parents), the planner self-relation fixtures (the TASK-0086 test files extended).
• Integration: the 2 scenarios (atomicity, the guards) + the 2-level convergence (the event-chain fixture).
• E2E: the 4 scenarios (TASK-0089 hosts them).

Security

• Sub-item ops are record ops (the permission model is unchanged — EDIT on the db, the parent's page grant is not an extra gate: the sub-item list is visible to anyone who can view the db, the sub-items are pages in the same db with the same inheritance — no new leak: a user who can't view a sub-item's page can't open its row link (the 404 on the page) but can see its title in the parent's list — the title is page-title data at the db's visibility level; recorded decision: titles in the sub-item list are shown to db-VIEW users (parity with the table view showing all records' titles); a Phase-14 per-record grant refinement would filter the list then — flagged); the subItemOf target must be a record in the same db (a cross-db id → 400 — sub-items are db-local by definition); the purpose property can't be used as a rollup/relation target property in the normal sense (the type is RELATION — rollups over it work normally (that's the whole point) — no restriction needed).

Documentation

• docs/api/relations.md Sub-items section (the purpose-relation contract, subItemOf, the flag lifecycle, the documented scoping: rollups are one link-hop, the table-view indistinguishability limitation, the Phase-14 refinement flags); docs/architecture/database-architecture.md §1.3 note (sub-items = flagged records + self-relation, the page-tree orthogonality).

Dependencies

• TASK-0081 (relations + the self-relation guard being lifted), TASK-0083/0086 (rollups + the planner — the cross-sub-item invalidation), Phase-5 (record create/patch, the record page, the property picker), Phase-3 (pages — the sub-item pages are ordinary pages).

Definition of Done

• Backend implemented (purpose relation + subItemOf + the patch field); frontend implemented (list + breadcrumb + picker row + the two menu items); no migration; API validation (the guards, the same-db check — 400/409s asserted); authorization (record-op matrix + the title-visibility decision tested); error states (409s with toasts, the orphaned-sub-item no-breadcrumb state, the unlink toast); loading states (the list skeleton while the links fetch, the Add row create spinner); empty states (the "No sub-items yet" state); mobile (the list full-width, the Add row a full-width input, the breadcrumb a sticky top row under the title); accessibility (the R6 contract — E2E keyboard create + axe); unit + integration + E2E; docs; logging (sub-item creates with parent id, unlinks, flag clears); audit: sub-item ops ride the record audit (record.created/updated — the subItemOf is in the create event payload, no new mandatory event — recorded).

---

TASK-0089

Title

E2E relations suite (tests/e2e/relations.spec.ts) — Phase-6 behavioral exit

Objective

Author the Playwright E2E suite for Phase 6 covering the behavioral exit criteria: create two dbs + a relation both directions, link/unlink, rollup COUNT updates on a linked change < 5 s, formula recompute on dependency change, a button that sets a property + creates a page, sub-items rendering in the parent page — plus the permission matrix and the a11y passes. The engine coverage + zero-eval gate is TASK-0090.

Background

ROADMAP Phase-6 exit: "formula engine 100 % function coverage, zero eval (the TASK-0090 gate); rollups recompute on relation change (event-driven) — the < 5 s E2E; button actions E2E-tested." The behavioral scenarios below are the user-facing half; TASK-0090 is the engine-coverage half. The Phase-5 suite patterns (tests/e2e/databases.spec.ts, fixtures tests/e2e/fixtures/databases.ts) are reused (authed users, network spies, the record-seeding helper); the fixtures gain two standard databases ("Projects" with STATUS/NUMBER/DATE + a "Tasks" db with STATUS/ NUMBER + a self-relation) and the relation/rollup/formula/button builders (API-level, fast).

Requirements

• R1. tests/e2e/relations.spec.ts scenarios (each from an owning task's acceptance criteria, run here for the first time):
  1. relation-both-directions: create Projects + Tasks (via slash, the Phase-5 path) → create a bidirectional M:N relation "Tasks" (Projects side) / "Project" (Tasks side) → both dbs show the new column (the cell renders "Add link"); link a task to a project from both sides (link the task→project in Tasks, then open the project's page and verify its Tasks cell shows the task — and vice versa); unlink from the reverse side → the forward side updates (no stale chip after refetch).
  2. relation-11: a 1:1 relation (Projects ↔ "Owner profile" db) → the radio dialog (checking a second unchecks the first), replacement from either side, the reverse cell single-chip + × unlink.
  3. rollup-recompute-5s (the exit criterion): the Tasks db has a rollup? no — the rollup lives on Projects (COUNT of Tasks, filter Status = Done — the spec's "Completed = 3" example): seed 4 tasks (3 Done) → the project's rollup cell = 3 → update task #4's status to Done (the table cell, the Phase-5 editor) → without reloading the project's row, the cell shows 4 within 5 s (the test polls the cell DOM with a 5 s budget — the event → invalidation → refetch path: the table's TanStack Query staleTime is 0 for computed columns? — the record page + the table both refetch on record.updated? no — the Phase-5 table refetches on local edits optimistically; a remote change (the test edits in the same session, so it's local) — the test edits via the API in a second context to force the remote path: context A views the project's rollup, context B (same user, different browser context) updates the task via the API → context A's cell must update < 5 s — context A polls (its TanStack Query is revalidated on the Phase-5 "records changed" signal? — Phase 5 has no realtime (Phase 7): the honest Phase-6 behavior is that context A updates on its next fetch (a user action or a staleTime expiry — staleTime 30 s default) — decision, recorded (the < 5 s contract): the < 5 s exit criterion is met for the same session (the user who made the change sees the rollup update immediately — the local edit triggers the optimistic + refetch, and the refetch's computed value is fresh because the invalidation already flushed the cache — the E2E asserts the same-session path: edit the task's status in the table → the project row's rollup cell (same view? the rollup is on the Projects db, the edit is on the Tasks db — cross-db same-session: the Projects table must refetch. Mechanism (shipped in this task): the link dialog + the cell + the table subscribe to a lightweight in-app "computed values changed" signal (a BroadcastChannel per workspace — same-browser cross-tab + same-tab) emitted by the worker? no — the worker can't reach the browser. The honest mechanism: the api emits nothing to the browser in Phase 6 (realtime is Phase 7). So the same-session cross-db update appears on the Projects view's next fetch. The E2E therefore asserts: (a) same-db same-session (the edit is in the same db as the rollup display — e.g. the rollup cell is edited into view via the record page, which refetches on panel interaction — the record page's panel "Show more" refetch): < 5 s; (b) cross-db: the Projects table refetches within the staleTime (30 s) — asserted with a 35 s budget, documented as the Phase-7 gap (realtime push makes it instant). The < 5 s exit criterion is pinned to (a) + the integration-level proof (TASK-0086: event → fresh cache read < 5 s, which is the server-side truth; the browser's freshness in Phase 6 is bounded by the poll-free fetch model — recorded, and Phase 7's realtime task file inherits the "make it instant" follow-up). This decision is the load-bearing one in the task — it is recorded in full here and must be mirrored in the Phase Completion Checklist.
  4. formula-recompute: a formula "Estimate × 2" (prop("Estimate") * 2) on Tasks → a row shows the doubled value → edit Estimate (10 → 20) in the table → the formula cell shows 40 on the same-session refetch (the record page path, the < 5 s contract as in scenario 3(a)); a formula referencing a changed property that the record's row didn't display (the user edits via the record page panel) → the table cell updates on refetch.
  5. button-set-and-create (the exit criterion): the "Close out" button (set Status=Done + create child page with a token title) → run from the table row chip → the confirm popover (the actions listed) → Run → the Status cell = Done, the child page exists with the resolved title (the API assert), the report toast; the audit row (as ADMIN) has database.button.executed.
  6. button-denied-mid-run: the 3-action button with the revoked-grant middle action → the per-action results (ok/denied/ok) — the denied toast, the applied effects (asserted in the DB).
  7. subitems-parent-page: the sub-item flow — the property picker's "Sub-items" row (once), the record page's section, inline create (Enter on the Add row), the breadcrumb navigation back, the rollup over sub-items (the spec's SUM example: 18 → 20 after a value edit, same-session).
  8. permissions: a VIEW user: relation cells read-only (no +, no link dialog), the button chip disabled with the reason, the formula/rollup cells render values (data, readable), the sub-item Add row hidden; a non-member: 404 on both dbs (the network assert).
  9. a11y-passes: axe on the link dialog, the formula editor, the button confirm popover, the sub-item section (0 critical/serious); the keyboard-only runs: link a record (dialog keyboard flow), run a button (chip → Enter → popover → Run), create a sub-item (Add row Enter) — all no-pointer.
• R2. Fixtures: tests/e2e/fixtures/relations.ts — the two-db builder (the properties above), the relation/rollup/formula/button creation helpers (API-level, reusing TASK-0081/0083/0085/0087 endpoints), a second-context helper (the cross-context test for scenario 3(b)), the admin user for audit reads.
• R3. CI wiring: @phase6 tag in the default E2E job; scenario 3's (b) 35 s assertion and scenario 4's polling use the state-wait pattern (no waitForTimeout — a page.waitForFunction on the cell text with a 35 s timeout, the flake policy from Phase 4/5); the suite < 12 minutes.

Acceptance Criteria

• [ ] pnpm test:e2e -- --grep @phase6 green from a clean docker-compose state: all 9 scenarios.
• [ ] The exit criteria are pinned: rollups recompute on relation change → scenario 3 (the < 5 s same-session + the recorded cross-db bounded-freshness decision, both asserted); formula recompute on dependency change → scenario 4; button actions → scenarios 5 + 6; sub-items in the parent page → scenario 7; relations both directions → scenario 1 (+ 2 for 1:1).
• [ ] The scenario-3 decision is mirrored in the Phase Completion Checklist's rollup line (the checklist line says "same-session < 5 s; cross-db bounded by staleTime until Phase-7 realtime — recorded in TASK-0089 R1.3").
• [ ] Zero waitForTimeout, zero test.fixme; 3 consecutive green runs recorded (the close-out is TASK-0090).
• [ ] The permission scenario asserts at the network layer (the 404s/403s), not just the DOM.

Backend Changes

• None (test-only; bugs surface in owning modules — the scenario-3(b) finding is a documented Phase-7 gap, not a bug: no code change).

Frontend Changes

• Test-only: tests/e2e/relations.spec.ts, tests/e2e/fixtures/relations.ts; the no-playwright-timeouts lint extends to the new spec.

Database Changes

• None.

API Changes

• None.

Tests

• This task is the suite (9 scenarios) + fixtures.

Security

• Scenario 8 (the matrix, network-asserted) + the audit-row assertion (scenario 5) + a CSRF check on the button run endpoint (the 403 without the token).

Documentation

• The scenario-3 freshness decision (recorded here in full, mirrored in the checklist, cross-referenced from the Phase-7 task file's Background when it's written); tests/e2e/README.md extended.

Dependencies

• TASK-0081…0088 (all Phase-6 behavior), Phase-5 E2E patterns + fixtures, Phase-3 (pages for the button's create_page, the trash for archive), the audit API (ADMIN reads).

Definition of Done

• Backend: None (test-only); frontend: suite + fixtures + CI wiring; no migration; API validation exercised (the 400/409 paths via the negative cases in scenarios 2/5); authorization scenario 8 green (network layer); error/loading/empty states exercised (the denied toast, the button run spinner, the sub-item empty state, the orphaned-breadcrumb absence); mobile (scenarios 1 + 7 in the 375 px preset: the link dialog full-screen, the sub-item list full-width, the button chip ≥ 44 px); accessibility (scenario 9 — axe + the three keyboard runs); unit + integration green from owning tasks; E2E is this task; docs (the freshness decision); logging (per-scenario durations in e2e-report.json); audit (scenario 5's row asserted).

---

TASK-0090

Title

Formula engine coverage + phase-exit verification (zero-eval CI gate)

Objective

Close Phase 6: prove the engine exit criteria (100 % function coverage with the structural gate, the fuzz suite, the type matrix — all green in CI), install the repo-wide zero-eval grep as a hard CI stage, run the full contract 3×, record the checklist evidence, and write the Phase-7 handoff note (the computed-value freshness follow-up + the multi-db formula-reference flag).

Background

ROADMAP Phase-6 exit: "formula engine 100 % function coverage, zero eval; rollups recompute on relation change (event-driven); button actions E2E-tested." The first is this task's gate; the second is TASK-0086 (integration) + TASK-0089 scenario 3 (E2E); the third is TASK-0089 scenarios 5–6. AGENTS.md rule 18 ("Never use arbitrary JavaScript execution for formulas") and rule 30 (no task complete until acceptance criteria are verified) make the zero-eval proof a CI stage, not a code-review promise: tests/ci/no-eval.sh greps the whole repo (source + packages, excluding node_modules, fixtures that legitimately contain the string as test data — the exclusion list is in the script, each exclusion justified in a comment) for eval(, new Function, Function(, and fails the build on a hit. The engine's own scope gate (TASK-0084's grep on src/formula/**) is subsumed by the repo-wide gate (kept as a faster pre-stage). Phase-5's close-out pattern (TASK-0080) is the template.

Requirements

• R1. tests/ci/no-eval.sh (committed, executable, shellcheck-clean): the repo-wide grep (the 3 patterns, plus setTimeout in packages/database-engine only — the engine package must be timer-free: it's called from the query hot path and the worker; a timer in the package would leak handles — a narrower second rule, recorded); exits non-zero on any hit with the file:line context; the exclusion file (.ci/no-eval-allowlist) lists test fixtures that contain the patterns as data (e.g. the TASK-0084 hostile-input fixture "eval(1+1)" as a string literal — the fixture is test data for the lexer, not code — each allowlisted line annotated with the reason; the allowlist is reviewed in the PR that touches it — a governance rule, recorded).
• R2. Coverage gate: the Vitest config for packages/database-engine requires 100 % function coverage on src/formula/functions.ts (the coverage.thresholds.functions: 100 scoped via include to that file — the rest of the package keeps its normal thresholds); the structural per-function golden check (TASK-0084's assertion: every function in the table has a __tests__/goldens/<fn>.json) runs in the unit stage; the fuzz (100 expressions, fixed seed in CI, rotating nightly) + the type matrix are part of the unit stage (already, from TASK-0084 — this task asserts they're green in the contract run + the artifact (the fuzz log) is uploaded).
• R3. Final contract run: the full AGENTS.md CI (install → lint → typecheck → unit → integration → build → security scan → E2E) from a clean docker-compose state, 3 consecutive runs, run hashes; the E2E includes @phase6 (TASK-0089); the security scan's dependency audit confirms no new runtime dependency was added by Phase 6 (the formula engine is dependency-free — the bundle-cost note from TASK-0085 is the only weight).
• R4. Checklist evidence (the Phase Completion Checklist at the top of this file): each line → the artifact/run hash: relations (TASK-0081 integration summary + TASK-0089 scenario 1), rollups (TASK-0083 goldens + TASK-0086 integration + TASK-0089 scenario 3 with the recorded freshness decision), formulas (the coverage report + the fuzz artifact + the no-eval.sh pass — all three in the run log), buttons (TASK-0087 integration + TASK-0089 scenarios 5–6), sub-items (TASK-0088 integration + TASK-0089 scenario 7), 18? no — 10/10 tasks closed (each with its commit range).
• R5. Phase-7 handoff note (appended to this file, "Phase-6 → Phase-7 handoff"): (a) the computed-value freshness gap (TASK-0089 R1.3's decision — the cross-db/cross-tab staleness bounded by staleTime until realtime; Phase 7's Yjs/presence work should push record.updated-class signals over the socket so the rollup/formula cells go instant — the named extension point: the realtime room broadcast for the database:{id} room, which already exists in the realtime architecture doc §1 — Phase 7 wires the computed-value events into it); (b) the multi-db formula reference flag (the prop() cross-db restriction — Phase 7+ lifts it; the TASK-0086 cycle guard becomes load-bearing; the flag is named for the formula task file's future); (c) the Phase-11/13 swap points (the ManualTriggerPort + the webhook-deliveries stub consumer — named, with the contract files); (d) the Phase-8 search note (the source: 'computed' events + the searchBody rebuild on computed changes — TASK-0077's pipeline, named for the search task file).
• R6. ROADMAP update: tick the Phase-6 boxes with evidence references (the TASK-0080 pattern).

Acceptance Criteria

• [ ] tests/ci/no-eval.sh passes on the merged tree and is demonstrably load-bearing: a throwaway branch adding eval("1") to any source file fails the CI stage (the failure shown in the PR description); the allowlist is non-empty and every entry annotated (a fixture from TASK-0084).
• [ ] The coverage report in the CI artifact shows functions.ts at 100 % functions (and the structural golden check passes: 15/15 functions + prop + operators have goldens).
• [ ] The fuzz log artifact (100 expressions, per-case outcomes, zero uncaught exceptions, zero > 1 s) is in the run; the type-matrix suite is green (48 outcomes).
• [ ] 3 consecutive full-contract CI runs green (run hashes); the @phase6 E2E green in all three; the security scan shows zero new runtime dependencies for Phase 6.
• [ ] The Phase Completion Checklist is fully ticked, each tick artifact-backed, the rollup line carrying the recorded freshness decision (the TASK-0089 mirror).
• [ ] The handoff note (R5) has the 4 named items, each with a file/contract reference; the ROADMAP Phase-6 boxes are ticked with evidence.

Backend Changes

• None (the gate is test infrastructure; if the contract run surfaces a Phase-6 bug, it's fixed in the owning module first — the gate stays red until it's fixed).

Frontend Changes

• Test-only: tests/ci/no-eval.sh, .ci/no-eval-allowlist, the Vitest coverage threshold config, turbo.json (the no-eval stage between lint and typecheck — fast, ~5 s, runs on every PR).

Database Changes

• None.

API Changes

• None.

Tests

• This task is the gate + the coverage wiring + the close-out; the "load-bearing" demonstration (the failing branch) is part of the PR evidence.

Security

• The zero-eval gate is the security deliverable for AGENTS.md rule 18's verifiability; the allowlist governance (annotated entries, PR review on changes) prevents allowlist drift; the security scan stage confirms the dependency surface.

Documentation

• The checklist evidence, the handoff note, the ROADMAP ticks, tests/README.md (the gate's usage + the allowlist rules); docs/architecture/database-architecture.md §1.4 final state (materialization normative from TASK-0086, the freshness gap cross-referenced).

Dependencies

• TASK-0081…0089 (the whole phase green), Phase-5's close-out pattern (TASK-0080), the Phase-7 task file draft (R5(a) is reviewed against it before close, the Phase-6↔7 parallel-writing pattern from TASK-0080 R4).

Definition of Done

• Backend: None (test infra only); frontend: the gate + coverage config + CI wiring; no migration; API validation (N/A); authorization (N/A); error states (the gate's failure modes — a hit, an unannotated allowlist entry — both fail loudly, both demonstrated); loading/empty states (N/A); mobile (N/A); accessibility (N/A); unit (the coverage gate runs the unit stage) + integration (the contract run) + E2E (the contract run); docs (evidence + handoff + ROADMAP); logging (the fuzz artifact, the coverage artifact); audit: N/A (no state change — the close-out is a verification task).