Phase 5 — Database Engine — Task Specifications (TASK-0063 … TASK-0080)

Purpose. Task spec sheet for Phase 5 of ROADMAP.md: turn the existing DatabaseModule (databases + filter-builder scaffolding), the Prisma database models (databases, database_properties — 22 PropertyType values —, database_views — 9 ViewType values —, database_records (record = page, 1:1 via pageId), record_properties EAV), and packages/database-engine into the full database engine of PRODUCT-SPEC §9, docs/product/functional-requirements.md §5, and docs/architecture/frontend-architecture.md §6 (view renderer architecture, cell renderers, query pipeline).

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

• [ ] Databases + typed properties (24 property types)
• [ ] Records (each record = a page), create/update/archive
• [ ] Table view (virtualized, 100k+ records)
• [ ] Filters (nested AND/OR/NOT), sorting, grouping
• [ ] Views: board, calendar, gallery, timeline, list (+ view settings: visible properties, column width, card layout, color, density)
• [ ] Chart + dashboard views (base)
• [ ] Exit: 100k-record database renders with virtualization; filter/sort/group engine unit-tested; all views open a record's page.

Task table

ID	Title	Estimate
TASK-0063	Databases CRUD + DATABASE block in pages (slash /database lands here)	L
TASK-0064	Property system: 22 types, value codecs, property CRUD UI	L
TASK-0065	Records: record = page (create/update/archive/duplicate, record page)	L
TASK-0066	Table view: virtualized grid, inline editing, column mgmt, bulk ops	L
TASK-0067	Filter engine: nested AND/OR/NOT, operator matrix, push-down, builder UI	L
TASK-0068	Sorting + grouping (multi-key, collapsible sections, group order)	M
TASK-0069	View management (9 views, per-view config, view tabs UI)	M
TASK-0070	Board view (kanban, drag card → property update)	L
TASK-0071	Calendar view (month/week, date mapping, day-click create)	M
TASK-0072	List + Gallery views	M
TASK-0073	Timeline view (date-range bars, month axis)	M
TASK-0074	Chart view base (bar/line/area/pie/donut/number, SVG)	L
TASK-0075	Dashboard view base (widget grid)	M
TASK-0076	Database AI hooks (documented stubs for Phase 12)	S
TASK-0077	Search-ready records (indexable body on record save)	M
TASK-0078	E2E database suite (tests/e2e/databases.spec.ts) — core scenarios	L
TASK-0079	100k-record seed, perf probes, engine unit gate (CI)	M
TASK-0080	Phase-5 exit verification (checklist evidence, conflict log)	S

Phase Completion Checklist (evidence recorded by TASK-0080)

• [ ] pnpm --filter @factory/database-engine test green (value codecs, filter/sort/group golden datasets, view config schemas)
• [ ] Structural unit gate: every operator in operatorsFor(type) for all 22 types has a golden fixture (assert-engine-goldens.ts, TASK-0079)
• [ ] tests/e2e/databases.spec.ts green in CI (create via slash, 5 property types, rows, filter+sort+group, board drag updates property, record opens page, 1000-row virtualization — TASK-0078)
• [ ] 100k-record seed renders with virtualization (perf probe: first paint < 2 s, < 120 rows mounted, scroll frame-drop < 5 % — TASK-0079)
• [ ] POST /v1/databases/:id/query p95 < 300 ms on the 100k seed (CI artifact — TASK-0079)
• [ ] Every view type opens the record's page (exit criterion, asserted in E2E for all 8 client-rendered views — TASK-0078 scenario 6)
• [ ] Relation/Rollup/Formula/Button property types exist as schema + cell placeholders that render "configured in Phase 6" states (no dead UI; verified by TASK-0080)
• [ ] Every task (TASK-0063…0080) closed against its Definition of Done (AGENTS.md)

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

1. The PropertyType enum in prisma/schema.prisma has 24 values (TITLE … UNIQUE_ID) and is authoritative; docs/api/databases.md §5.1 documents exactly those 24 wire types. docs/product/product-requirements.md PR-058 and this phase therefore count "24 property types" = the enum. (Earlier drafts counted 22; RICH_TEXT and DATE_RANGE were added to the enum to match the value codec set — resolved, no longer a conflict.)
2. The 10th view type FORM is in the ViewType enum and the schema, but form building is Phase 10 (PRODUCT-SPEC §11, PR-069). Phase 5 ships 8 view types client-side (TABLE, LIST, BOARD, GALLERY, CALENDAR, TIMELINE, CHART, DASHBOARD); the FORM view row exists in the view-type registry as enabled: false ("Phase 10") so view creation lists all 9 without a working form.
3. Relation/Rollup/Formula/Button value editing is Phase 6; Phase 5 must still accept these property types in the schema (so a database created in Phase 5 can gain relations in Phase 6 without a schema migration), render read-only placeholder cells, and keep the query engine from attempting to compute them.
4. The phase brief lists 17 topics but fixes the range at 18 tasks (TASK-0063…0080). Resolution: the final topic ("E2E database suite + exit verification") is split into TASK-0078 (core E2E scenarios), TASK-0079 (100k seed + perf probes + structural unit gate), TASK-0080 (phase-exit verification + Phase-6 handoff). All 17 topics remain covered, one per task, with the close-out work split across the last three.

---

TASK-0063

Title

Databases CRUD + DATABASE block in pages

Objective

Complete database creation/read/update/delete with the embedded DATABASE block: POST /v1/workspaces/:wid/databases creates the Database row, its default TITLE property, an initial view, and (when pageId is given) the DATABASE block — in one transaction — so the Phase-4 slash /database command (TASK-0054) lands a fully functional, empty database inline in a page.

Background

Phase 4 shipped the DATABASE block node (placeholder renderer) and the slash property picker that calls the create endpoint; the DatabaseModule scaffolding exists (controllers + filter-builder). databases/database_properties/database_views models are defined in prisma/schema.prisma. Database.pageId is a plain ref to the embedding page (database-architecture §1.3). A database is addressable both by GET /v1/databases/:id and by its block; the block is the view, the database row is the source of truth (a linked_database block in Phase 6+ re-uses the same database — views are per-database, not per-block, per functional-requirements §5.3 view rules). Icon/title: Database.icon (emoji or file id — file id resolves through Phase-9 file rendering; Phase 5 accepts emoji + file-id string), Database.title.

Requirements

• R1. API (apps/api/src/modules/databases):
  • POST /v1/workspaces/:wid/databases — body (shared Zod CreateDatabaseSchema): {title? (≤200), icon?, description?, pageId?, parentId? (target container block for the database block), properties?: PropertySpec[] (validated rows, default = [TITLE "Name"]), initialView?: 'table'|'board'|'calendar'|'timeline'|'chart'|'list'|'gallery'|'dashboard' (default 'table')} → 201 {database, blockId?, viewId}; transactional: databases + TITLE property (isTitle: true, position 'a0') + one DatabaseView (type from initialView, config defaults) + DATABASE block (type DATABASE, content: {databaseId}, properties: {viewId}, positioned by after/parentId/position like any block op) + databases.pageId backfill; emits database.created; audit database.created.
  • GET /v1/databases/:id — database + properties (ordered by position) + views (ordered); cached dbmeta:{dbId} 300 s invalidated on database.updated (backend-architecture §11).
  • PATCH /v1/databases/:id — title/icon/description; schema-affecting changes bump schemaVersion and emit database.updated {changedProperties?}; audit for icon/title not required (not security-sensitive) but schemaVersion bump is logged.
  • DELETE /v1/databases/:id — soft: sets isArchived + trashes the embedding block? no — deleting the database deletes its DATABASE block(s) (search all blocks with content.databaseId = id), trashes all record pages (one object, one trash flow — database-architecture §2.3), marks isArchived, emits database.updated {archived: true}. Records/pages are recoverable from trash.
  • POST /v1/databases/:id/duplicate — deep copy: new database, properties (options included), views (config, not data), records with property values (relations/rollups/formulas NOT copied — recomputed in Phase 6; Phase 5: computed properties don't exist yet, so all values copy); new page per record.
• R2. apps/web/database/DatabaseView.tsx — the router (frontend-architecture §6): receives DatabaseDto, reads ?view= from the URL (default = first view's id), mounts the matching renderer; view tabs bar (icons + names + + new view → type picker; rename on double-click; delete via tab ⋯ menu — last view undeletable, 409 server-side).
• R3. apps/web/database/DatabaseHome.tsx — the page-canvas shape for the DATABASE block: header (icon + editable title + description), view tabs, the active view renderer; the Phase-4 placeholder renderer is replaced by this component (the block node's render slot is the mount point; no codec changes).
• R4. Database property header row above the table view (visible property toggles live in TASK-0069 view config; the header here is icon/title/description + "Share" placeholder — sharing arrives with Phase 5/3 share plumbing if available, else disabled with tooltip).
• R5. Empty database state: zero records → per-view empty state (table: "No entries yet — add your first row" + New row button; board: "No cards" + Add a card; etc. — shared EmptyState per view type in apps/web/database/views/empty-states.tsx).

Acceptance Criteria

• [ ] E2E: /database → "Projects" → created: databases row (with pageId set), exactly one TITLE property (isTitle true), one TABLE view; DATABASE block present in the page with content.databaseId and properties.viewId; GET /v1/databases/:id returns all of it (asserted via API in one test).
• [ ] POST with pageId of a page in another workspace → 404 (tenant isolation, no existence leakage).
• [ ] Duplicate of a 10-record database creates a new database with 10 new records/pages (fresh ids, same property values); original untouched.
• [ ] Delete: embedding block gone from the page, records' pages in trash (visible in /trash), restore from trash restores the record pages (database metadata stays archived — documented behavior).
• [ ] PATCH changing title invalidates the dbmeta cache (two GETs with a PATCH between return fresh data immediately).
• [ ] View tabs: create a BOARD view → ?view=<id> deep link renders the board; deleting the last view → 409 + toast; rename persists across reload.

Backend Changes

• apps/api/src/modules/databases: controllers/services/repositories for the 5 endpoints; DTO schemas in packages/shared/src/dtos/databases.ts; event emission; audit rows; dbmeta cache.

Frontend Changes

• apps/web/database/{DatabaseView,DatabaseHome}.tsx, apps/web/database/views/empty-states.tsx, apps/web/hooks/useDatabase.ts (TanStack Query keys.database), packages/editor/src/nodes/database.ts renderer swap (placeholder → DatabaseHome mount).

Database Changes

• None (models exist).

API Changes

• New: POST /v1/workspaces/:wid/databases, GET /v1/databases/:id, PATCH /v1/databases/:id, DELETE /v1/databases/:id, POST /v1/databases/:id/duplicate. Documented in docs/api/databases.md (new file, started here, completed by later tasks).

Tests

• Unit: CreateDatabaseSchema (property spec rows: exactly one title required unless provided, type enum closed, option rows for select-family types), view-default config builder per type.
• Integration: create with/without pageId; tenant isolation; duplicate; delete cascade (block + record pages trashed); dbmeta cache invalidation.
• E2E: the six scenarios (suite in TASK-0078; scenario skeletons added here).

Security

• EDIT on the target page to create inline (or workspace-level EDIT for standalone); VIEW to read; FULL_ACCESS to delete; every endpoint permission-checked server-side (@RequirePermission('database', …) after the resource exists — create checks the page grant); databases.pageId plain-ref validated by DatabaseService; no cross-tenant reads (repository scoping by workspaceId first-arg invariant).

Documentation

• docs/api/databases.md — create/read/patch/delete/duplicate contracts (request/response JSON, error codes).

Dependencies

• Phase 3 (pages, blocks API, trash), TASK-0054 (slash property picker → this endpoint), packages/database-engine scaffolding.

Definition of Done

• Backend implemented; frontend implemented; no migration; API validation (shared Zod, field-level 400s); authorization (matrix integration-tested: guest cannot create, VIEW cannot delete); error states (409 last view, 404 cross-tenant); loading states (database home skeleton while dbmeta fetches); empty states (per-view, R5); mobile (view tabs scroll horizontally, header stacks); accessibility (view tabs role=tablist + aria-selected, editable title role=combobox pattern, empty-state buttons focusable); unit + integration + E2E; docs; logging (create/delete with counts: properties, views, records); audit: database.created (mandatory event list, backend-architecture §9).

---

TASK-0064

Title

Property system: 24 property types — value codecs, API CRUD, property editor UI

Objective

Implement the full typed property system: all 22 PropertyType values with per-type value codecs in packages/database-engine/src/values, property add/rename/reorder/ hide/delete APIs (exactly one TITLE, PR-059), and the property editor UI in the database header (add property dialog with type picker + per-type config forms, column visibility toggles per view).

Background

functional-requirements §5.1 fixes, per type: config JSON (select/multi-select/status options[{id,name,color}], number format/precision/prefix/suffix/currency, person multiple, files maxCount, relation {relationId, multiple}, rollup {rollupId}, formula {formulaId}, button {buttonId}, unique id format/prefix), the record_properties.value JSON shape (select option ids not names, dates ISO 8601, ranges {start,end}, person id(s), files id arrays), and the editor widget. PR-074: add/rename/reorder (fractional position)/hide/delete; delete keeps values until records are gone (soft deletedAt); type change only with lossless conversion (Phase 5: allow text→rich_text, number→text; everything else → error, conversion pipeline lands with Phase-6 computed-property work). Phase 6 owns the semantics of RELATION/ROLLUP/FORMULA/BUTTON values; Phase 5 owns their schema + config UI + read-only placeholder cells (header note 3).

Requirements

• R1. packages/database-engine/src/values/ — one codec per type, index.ts registry: {type, schema: ZodSchema (value), fromWire(json), toWire(value), display(value, config, locale), compare(a,b,config), canConvert(from,to)}. 22 codecs; shared RichText codec (markdown source string) for TEXT/RICH_TEXT; date codec normalizes to ISO 8601 UTC (input: ISO, ms epoch, "today"-like tokens rejected at value level — tokens live in filters, TASK-0067); select-family codecs validate against config.options ids (orphan option id after option delete → treated as empty, never an error); auto properties (CREATED_TIME/BY, LAST_EDITED_*) are read-only — fromWire accepts but the API layer ignores client values for them.
• R2. API (apps/api/src/modules/databases/properties):
  • POST /v1/databases/:id/properties — {name (≤100, unique per db, 409 on dup), type, config?, isTitle? (only allowed if db has no title yet — creates the db's title), default?} → creates at end of column order (position appended); exactly-one-title invariant enforced (409 title_exists); schema-affecting → schemaVersion bump + database.updated.
  • PATCH /v1/databases/:id/properties/:pid — rename (unique check), config update (option rows validated per type: select-family options are {id?, name, color} — server mints option ids on create; renaming an option keeps its id), reorder (position fractional, after property id), visibility toggle is view-level (TASK-0069) not property-level except the global isVisible default; type change only per canConvert (422 conversion_not_supported with the allowed list); delete → soft (deletedAt), blocks if isTitle (409).
  • GET /v1/databases/:id/properties (covered by GET /databases/:id).
  • Option sub-ops for select/multi-select/status: add/rename/reorder/delete option (delete option → existing record values holding it become empty on next read, value rows kept with the orphan id; option rows live in config.options, no separate table).
• R3. UI apps/web/database/properties/ — "Add property" button (table header + database header): dialog with name + type picker (22 types grouped: Text, Numbers, Selection, Dates, People & Contacts, Files, Auto, Relations (Phase 6 badge), Buttons (Phase 6 badge)) + per-type config form (React Hook Form + Zod from @factory/shared): select-family → option list editor (add row, color from the 28-color token palette, reorder by drag or up/down); number → format picker; relation/rollup/formula/button → config form that lands on the Phase-6 endpoints but in Phase 5 shows a "Configuration completes in Phase 6" info banner and saves the property with config: {} (the cell renders the placeholder state); property rows in the table header: click name → rename inline, ⋯ menu (rename, hide/show, move left/right, duplicate column (value-less copy), delete (confirm dialog stating values are kept until records deleted)), drag reorder (dnd-kit, position fractional, one PATCH on drop).
• R4. Column visibility: per-view config.visiblePropertyIds[] — the header ⋯ "Hide in this view" + a "Show more columns" chevron menu (all properties with checkboxes) (frontend-architecture §6 TABLE rules); default = all isVisible properties in position order.
• R5. Default values: property default Json? — applied at record creation for omitted values (record create, TASK-0065); editing a default never rewrites existing records.

Acceptance Criteria

• [ ] All 24 property types can be created via the UI; each type's config form validates its config JSON against the shared schema (bad option color / precision > 8 → field-level errors, nothing persisted).
• [ ] Creating a second TITLE property → 409 title_exists; deleting/renaming the TITLE property → 409 with a clear message (PR-059).
• [ ] Round-trip: create a SELECT with 4 options → set an option value on a record (via TASK-0065's cell) → record_properties.value is the option id; delete the option → cell renders empty (not an error, not the name).
• [ ] Reorder 5 columns by drag → database_properties.position set is strictly ordered and persisted; reload keeps order; "move left/right" menu item does the same for one column (a11y path).
• [ ] canConvert: TEXT→RICH_TEXT allowed (values migrate: strings become {text: str}? no — RICH_TEXT value is a markdown source string per §5.1, so conversion is identity + a log); NUMBER→TEXT allowed (number → decimal string); TEXT→NUMBER rejected 422 with the allowed list in details; RELATION→anything rejected.
• [ ] E2E: add 5 property types (text, number, select, date, checkbox) via the dialog with configs; GET /v1/databases/:id returns them in the created order with correct config JSON.

Backend Changes

• apps/api/src/modules/databases/properties/* (controller, service, DTOs); packages/database-engine/src/values/* (22 codecs + registry + property tests); packages/shared/src/dtos/properties.ts (per-type config schemas — the discriminated union PropertyConfigSchema).

Frontend Changes

• apps/web/database/properties/{AddPropertyDialog,PropertyHeader,PropertyConfigForm,OptionListEditor}.tsx; column visibility in the table header (consumed by TASK-0066).

Database Changes

• None (models + enums exist; config/default JSONB hold everything).

API Changes

• New: POST /v1/databases/:id/properties, PATCH /v1/databases/:id/properties/:pid (+ option sub-ops as body actions {"action": "addOption" | ...} on the same PATCH to keep the surface small — documented in docs/api/databases.md).

Tests

• Unit (Vitest, packages/database-engine): one spec per codec (value validation, display formatting incl. locale + currency, compare ordering used by sort TASK-0068, orphan-option behavior); property-based: toWire(fromWire(v)) === v for valid values across all 22 types (fuzzed values); conversion matrix (22×22 table, exact allowed set asserted).
• Integration: title invariant, duplicate name 409, reorder persistence, delete soft-keeps values, schemaVersion bump + database.updated event.

Security

• Property names/config are user input — Zod-validated, option ids server-minted (no client-injected ids that could collide with record ids in EAV), no HTML in option names (stored as plain text, rendered with escaping); EDIT on the database (or its page) to modify properties; GUEST read-only.

Documentation

• docs/api/databases.md properties section; packages/database-engine/README.md codec contract (the Phase-6 computed-property codecs must follow the same interface).

Dependencies

• TASK-0063 (database shell), packages/database-engine scaffolding, packages/ui (dialogs, color palette).

Definition of Done

• Backend implemented (22 codecs in the engine package, property CRUD); frontend implemented; no migration; API validation (per-type config + value schemas shared); authorization (property writes = EDIT on database/page); error states (409/422 with details); loading states (dialog option editor spinner for color picker? no — synchronous; header skeleton while properties load); empty states (no non-title properties → "Add property" hint in header); mobile (property dialog full-screen, type picker as horizontal chip scroller); accessibility (config forms labeled, errors aria-describedby, option list reorderable by keyboard up/down, color palette role=listbox with names not colors); unit (incl. fuzz) + integration + E2E; docs; logging (property changes with property id + type in debug log); audit: none mandatory (property changes are not in the mandatory list; database.updated event carries changedProperties for consumers).

---

TASK-0065

Title

Records: record = page (create/update/archive/duplicate + record page layout)

Objective

Implement records as first-class entities that are 1:1 with pages (ADR-006): record create materializes a Page row + DatabaseRecord row + record_properties rows in one transaction; record page shows the properties panel (left) + block content (right); record update (per-property value writes with per-type validation), archive, duplicate; record positions in views via fractional position.

Background

ADR-006 (database-architecture §2.3): creation order pages first (id = record's pageId), then database_records, then record_properties — one transaction; the record inherits page permissions; trash via pages.trashedAt (page-level) is distinct from record.archivedAt (database-level archive, hidden from default views, restorable); versions/comments/sharing work unchanged (page features). Value writes go through PATCH /v1/databases/:id/records/:rid {properties: {propertyId: value}} — server validates each value against the property's codec (TASK-0064), auto properties are server-managed (lastEditedBy/Time updated on any change; createdBy/Time set at creation; client-sent auto values ignored), relation/rollup/formula/button values are rejected in Phase 5 (422 computed_property_readonly — Phase 6 owns them). DatabaseRecord.position = row order in table/board (fractional).

Requirements

• R1. API (apps/api/src/modules/databases/records):
  • POST /v1/databases/:id/records — {title?, properties? {propertyId: value}, icon?, coverUrl?, position? (fractional among siblings; default append), isSubItem? (default false; sub-item mechanics are Phase 6 — accept the flag, store it, no sub-item UI yet)} → transaction: pages.insert (unparented, title = TITLE property value or title param or "Untitled", workspaceId from ctx) → database_records.insert(pageId) → record_properties upserts for provided values (validated per codec; TITLE value is written to the page title, not an EAV row) → auto props stamped → returns {record, pageId, values}; emits record.created {databaseId, recordId, pageId}; audit record.created (mandatory).
  • PATCH /v1/databases/:id/records/:rid — {properties?: {pid: value}, title?, icon?, coverUrl?, expectedVersion?}; per-value codec validation (400 with field per property); computed-type values → 422; TITLE change → updates the page title; lastEditedBy/Time stamped server-side (actor = ctx principal); optimistic version (409 with actual); emits record.updated {changedProperties[]}; audit record.updated (mandatory).
  • POST /v1/databases/:id/records/:rid/archive / .../unarchive — sets/clears archivedAt; archived records excluded from default queries (query engine adds archivedAt IS NULL unless includeArchived); the record page is NOT trashed (still openable by direct link); emits record.archived.
  • POST /v1/databases/:id/records/:rid/duplicate — copies title, icon, cover, property values (computed excluded — they don't exist yet in Phase 5), creates a new page; new position after the source.
  • DELETE /v1/databases/:id/records/:rid — moves the record's page to trash (one object, one trash flow); the record row stays (referential integrity for Phase-6 relations — pageId keeps pointing at the trashed page; queries hide trashed records via the page join).
• R2. Batch: POST /v1/databases/:id/records/batch — {operations: [{op: 'create'|'update'|'archive'|'delete', ...}]} 1–100 ops, one transaction, per-op validation, same event model (one record.updated batch event + per-created record.created) — used by bulk actions (TASK-0066) and board drag of multiple cards.
• R3. Record page UI apps/web/database/record-page/RecordPage.tsx — page-canvas shape kind: "record" (frontend-architecture §2.2): left panel = property chips (icon + label + value, order = property position, "Show more (n)" popover with all properties incl. hidden-in-view ones; chip click → inline editor popover per type — reusing the table cell editors, TASK-0066; auto properties show their values read-only; the TITLE property renders as the page title (top, not in the panel)); right pane = the record page's blocks (full editor — TASK-0047 factory, same autosave pipeline; the record page is an ordinary page content-wise); panel is collapsible (chevron) and stacks above content below 1024 px.
• R4. Record opening: every view's row/card/bar (TASK-0066+) navigates to /[workspace]/p/[pageId] — the route resolves kind: "record" via GET /v1/pages/:id (page with a DatabaseRecord — the page DTO gains recordId? via a join) → mounts RecordPage; ⌘Enter from a table cell does the same (TASK-0066).
• R5. GET /v1/databases/:id/records/:rid — record + page + all property values (EAV pivot for one record, single round-trip) — the record page's data endpoint (TanStack Query keys.record).

Acceptance Criteria

• [ ] E2E: "New row" in the table → row appears with default values (defaults applied), a pages row exists (title = the TITLE value), database_records.pageId 1:1; opening the row URL shows the record page with the properties panel + an empty editor; typing in the editor autosaves the record page's blocks (same pipeline as a normal page).
• [ ] API: create with a value for a SELECT option id that doesn't exist → 400 (field properties.<pid>); create with properties for a computed type → 422 computed_property_readonly (Phase-6 placeholder behavior, asserted so Phase 6 has a stable contract).
• [ ] PATCH changing a NUMBER value stamps lastEditedBy = caller + lastEditedTime ≈ now (±5 s); createdTime/By unchanged; expectedVersion mismatch → 409 with the actual version.
• [ ] Archive: archived record disappears from the default table query but ?includeArchived=true shows it; direct URL still opens the record page with an "Archived" banner + Unarchive button; unarchive restores it.
• [ ] Delete: record's page appears in /trash; restore the page from trash → record reappears in the database.
• [ ] Duplicate: new record with identical property values (except auto + position), new page id; original intact.
• [ ] E2E: 200 creates via the batch endpoint in one request → 200 pages + 200 records + correct values (transaction: kill one op's validation mid-batch via a bad value → full rollback, zero rows).

Backend Changes

• apps/api/src/modules/databases/records/*; record query join in apps/api/src/common/repositories (Prisma: pages join for title/trash state); DTOs in packages/shared/src/dtos/records.ts (RecordWithValuesDto, RecordCreateSchema, RecordPatchSchema, RecordBatchSchema).

Frontend Changes

• apps/web/database/record-page/RecordPage.tsx (+ property chip components reusing cell editors), apps/web/hooks/useRecordPage.ts, page-canvas kind resolution extended in apps/web/app/(app)/[workspace]/p/[pageId]/page.tsx, page DTO recordId?.

Database Changes

• None (models exist; pages join covered by PK).

API Changes

• New: POST/GET /v1/databases/:id/records... (5 endpoints + batch) per R1/R2/R5; documented in docs/api/databases.md.

Tests

• Unit: value-write validation pipeline (codec dispatch, auto-prop ignoring, TITLE→page-title routing), batch op validation, archive filter predicate.
• Integration: the transaction invariants (create/delete/archive/duplicate), 409 path, event payloads (record.created/updated field-level asserts), tenant isolation (record of db A invisible to workspace B — 404).
• E2E: the six scenarios (TASK-0078 suite; skeletons here) + record-page render test (panel left, editor right, both interactive).

Security

• Every record op permission-checked: record's effective permission = its page's effective permission (inheritance walk treats record→page as one hop — backend-architecture §5.2 rule 5), explicit record grants override; GUEST cannot create/update (403); batch ops re-check per op (a mixed batch can't smuggle a write to one forbidden record — whole batch 403 with the offending op index); actor stamped from ctx (never client-supplied); audit rows in-transaction (backend-architecture §9 same-transaction rule).

Documentation

• docs/api/databases.md records section; docs/architecture/database-architecture.md §2.3 already describes ADR-006 — add the "computed values 422 in Phase 5" note.

Dependencies

• TASK-0063 (databases), TASK-0064 (codecs + title invariant), Phase-3 (pages, trash, page canvas), TASK-0047 (record-page editor mount).

Definition of Done

• Backend implemented; frontend implemented (record page); no migration; API validation (per-type values, field-level 400s); authorization (record→page inheritance asserted in integration matrix incl. explicit record NONE grant); error states (409 version, 422 computed, 404 trashed+other-tenant); loading states (record page skeleton: panel chips shimmer + editor hydrating); empty states (new record: empty editor placeholder + all-empty chips); mobile (panel stacks above content, chips wrap); accessibility (panel chips role=button with value in aria-label, TITLE is the page h1, ⌘Enter documented in cell a11y); unit + integration + E2E; docs; logging (batch op counts, archive/unarchive with reason); audit: record.created/record.updated/record.archived in-transaction.

---

TASK-0066

Title

Table view: virtualized grid, per-type inline editing, column resize/reorder, bulk select

Objective

Implement the TABLE view — the workhorse: a virtualized grid (100k+ records, @tanstack/react-virtual rows + column virtualization > 50 columns, sticky title column), per-property-type inline cell editing (optimistic with rollback), column width drag (min 80 px) + reorder, row hover actions, multi-select with bulk toolbar (archive, duplicate, delete), sort-by-column-click, density toggle, and the POST /v1/databases/:id/query pipeline that feeds every view.

Background

frontend-architecture §6 is binding: one query pipeline for all views — view config (filter tree, sort[], group[], pageSize, visible properties, Zod-validated from @factory/shared) → POST /v1/databases/:id/query (server applies filter→sort→group→paginate in packages/database-engine + EAV pivot in one indexed round-trip + relation counts later) → RecordsPageDto {records: RecordWithValues[], cursor, total, groups?} → renderer. Table specifics (functional-requirements §5.3): virtualized 100k+, sticky title + first visible column, inline editing, row hover actions (open page, duplicate, archive, comment), multi-select shift-click + bulk toolbar, row height from density, column width drag min 80 px, "show more columns" chevron. PR-062 P0. Server-side: the query engine's sort/group/filter are TASK-0067/0068 — this task builds the query endpoint skeleton (position-ordered cursor pagination + EAV pivot) that those tasks extend, so the table works from day one of Phase 5.

Requirements

• R1. Query endpoint POST /v1/databases/:id/query (apps/api/src/modules/databases/query.ts + packages/database-engine/src/query/): body RecordQuerySchema {viewId? (load persisted filter/sort/group/config/pageSize) | adHoc {filter?, sort?, group?, includeArchived?, visiblePropertyIds?}, cursor?, pageSize? (default 100, max 500)} → response {records: [{id, pageId, position, values: {propertyId: value}, icon, coverUrl, archivedAt, createdTime, lastEditedTime}], cursor, total}. Pipeline: base set = unarchived records (unless flag) joined to pages (title + trashedAt IS NULL) → filter (TASK-0067 — Phase-5 skeleton: null = no filter) → sort (TASK-0068 — skeleton: position ASC) → cursor page (opaque base64 {position, id}, stable order) → EAV pivot (record_properties WHERE record_id IN (page) one query, map) → auto/computed display enrichment. Server timeout budget: p95 < 300 ms at 100k (CI probe).
• R2. apps/web/database/views/TableView.tsx: @tanstack/virtual row virtualization (overscan 10, row height from density: compact 32 px / default 40 px / relaxed 52 px — density stored in view config.density), column virtualization beyond 50 visible properties, sticky header + sticky title column, rAF-coalesced scroll, transforms only. Row = one record; cells from TASK-0064 codecs (display()) via apps/web/database/cells/* (22 cell components — one per PropertyType; Phase-5 state: relation/rollup/formula/button render the "Phase 6" placeholder chip with a tooltip; unique id auto-generates rec_<n> from a per-database counter? no — unique id values are record.id prefixed per config.prefix (read-only display), no counter table).
• R3. Inline editing per type (double-click or F2 or Enter-on-focused cell): text/rich-text (popover editor, Enter commits, Esc reverts), number (spinbutton-like input, right-aligned, format-aware), select/status (dropdown role=combobox with colored pills), multi-select (popover multi-pick), date (date picker popover; range = dual picker), person (member picker from GET /v1/workspaces/:wid/members), email/phone/url (text input + link preview, client validation + server codec re-validation), checkbox (click toggles, role=switch), files (upload button + chips — reuses TASK-0051 upload; chips removable), auto props (render-only, not focusable-editable — aria-readonly), title cell (inline text → updates page title). Optimistic: local cellOverrides (Zustand transient map) updates immediately → PATCH record (or batch for bulk) → on failure rollback + toast.
• R4. Column management: width drag (dnd-kit handle in header, min 80 px, live, persisted to view config.columnWidths[propertyId] on release), reorder (header drag, position via property reorder API — TASK-0064), "Show more columns" chevron (TASK-0064 R4 visibility menu), sort-by-column-click (header click → single-key sort asc → desc → off, aria-sort on headers, sort indicator; multi-key sort UI is TASK-0068 — header ⋯ "Add to sort" adds a key).
• R5. Rows: hover actions (open page →, duplicate, archive, add comment — comment anchors the record, Phase-3 comment panel with recordId), "New row" at bottom (creates at end; Enter in the new-row cell keeps the caret in the title cell of the next row after commit — the classic table flow), row click = open page (single click on non-cell area; cell click = edit), row checkbox (left of title) + shift-click range select + ⌘A select-all-in-view (ux §10 database section).
• R6. Bulk toolbar (appears when ≥ 1 selected, floating bottom bar, role=toolbar): count, Archive, Duplicate, Delete (confirm dialog: "Move N pages to trash"), Assign (person property picker when the db has exactly one PERSON property — else hidden); all bulk ops → POST records/batch (one request); toolbar keyboard: Esc deselects, arrows cycle items.
• R7. Keyboard (ux §10): arrows move the focused cell (row/col), Enter edits (or opens record when the cell is not in edit mode and the cell is the title), Tab/Shift+Tab commit+right/left, Esc reverts cell edit, ⌘Enter opens record page, ⌘⇧A select all rows in view, f focuses the filter drawer (TASK-0067), 1…9 switches views (TASK-0069).

Acceptance Criteria

• [ ] Perf (CI, 100k seed): first paint < 2 s; mounted rows ≤ 120; scroll at 5000 px/s frame-drop < 5 %; POST /databases/:id/query p95 < 300 ms (20 samples, CI artifact); 51 visible properties → column virtualization active (only ~30 columns in DOM).
• [ ] E2E: double-click a number cell → type "42" → Enter → cell shows formatted 42; network shows one PATCH /records/:rid; reload persists. Bad input ("abc" in a number cell) → Esc reverts to the old value, no request sent.
• [ ] E2E: select a SELECT cell → dropdown lists options with colors → pick → colored pill renders; value in DB is the option id.
• [ ] E2E: drag column header to reorder → persisted (view config order after property reorder API call); drag width handle → columnWidths persisted; min 80 px enforced (can't drag below).
• [ ] E2E: shift-click 5 rows → bulk toolbar → Archive → 5 records archived (one batch request on the wire), rows disappear (archived filtered), toolbar gone; Undo via the toast? (bulk archive has a toast with "Undo" — unarchive via a second batch call — asserted).
• [ ] E2E: sort-by-column-click on the date column asc/desc (rows reorder per server response, aria-sort toggles), then "Add to sort" adds a second key (TASK-0068 UI).
• [ ] E2E (a11y): full table keyboard journey — Tab to grid, arrows to a cell, Enter edit, Tab right, Enter commit, ⌘Enter opens record page — no pointer (axe: grid roles per ux §12: role=grid/row/columnheader/gridcell, stable aria-rowindex).
• [ ] E2E: 1000-row scroll check (exit-criterion scale): seed 1000 rows, scroll to row 950 → row renders with correct data (no full-list render — DOM row count stays ≤ 120 throughout).

Backend Changes

• apps/api/src/modules/databases/query.ts + packages/database-engine/src/query/{paginate,pivot}.ts (skeleton pipeline; filter/sort/group slots filled by 0067/0068); RecordsPageDto + RecordQuerySchema in packages/shared.

Frontend Changes

• apps/web/database/views/TableView.tsx, apps/web/database/cells/*.tsx (22 components), apps/web/database/{BulkToolbar,NewRow,SortHeader}.tsx, apps/web/hooks/useRecords.ts (cursor-paginated query hook, keys.records), cell override store.

Database Changes

• None (expression indexes for filtered properties are MVP+1 per database-architecture §3.3 — recorded as a follow-up task in Phase 15, not here; 100k unfiltered + basic filter must hit the (databaseId, archivedAt) + (recordId, propertyId) uniques).

API Changes

• New: POST /v1/databases/:id/query (the workhorse endpoint — full contract in docs/api/databases.md); batch record ops from TASK-0065 consumed for bulk actions.

Tests

• Unit: cursor pagination stability (concurrent-insert fixtures), EAV pivot correctness (sparse values, orphan property ids dropped, auto-prop enrichment), cell override store (optimistic apply/rollback), density/width clamps.
• Integration: query endpoint against the 100k seed (response shape, total, cursor page boundaries — no dupes/gaps across 3 pages), 300 ms budget assert (soft in CI, hard in perf), bulk batch transaction.
• E2E: the eight scenarios (TASK-0078 suite).

Security

• Query respects the database's effective permission (VIEW minimum; archived-include flag doesn't bypass trash — trashed pages never returned); values returned are display-safe (file ids, user ids — never raw storage keys); bulk delete trashes pages (recoverable), not hard-deletes; no SQL injection surface (parameterized Prisma; cursor is an opaque server-issued token, decoded and validated server-side — a tampered cursor → 400, not an error).

Documentation

• docs/api/databases.md query section (request/response, cursor semantics, error codes); apps/web/database/README.md cell-renderer contract for Phase-6 cells.

Dependencies

• TASK-0063/0064/0065 (databases, codecs, records), @tanstack/react-virtual (mandated by frontend-architecture §8), packages/ui popovers/pickers.

Definition of Done

• Backend implemented (query endpoint); frontend implemented; no migration; API validation (RecordQuerySchema shared Zod); authorization (VIEW to query, EDIT to edit/bulk — integration matrix); error states (bad cursor 400, 409 version on cell commit → refetch + re-merge + toast, empty database empty-state from TASK-0063); loading states (row skeletons on first page fetch, spinner on cursor append); empty states; mobile (horizontal scroll of the grid, density forced compact, bulk toolbar bottom sheet); accessibility (full grid ARIA contract per ux §12, keyboard journey E2E, aria-sort, row selection announced); unit + integration + E2E + perf; docs; logging (query duration + record count per request, bulk op counts); audit: cell edits = record.updated (mandatory, per-op in batch).

---

TASK-0067

Title

Filter engine: nested AND/OR/NOT tree, operator matrix, server push-down, filter builder UI

Objective

Implement the filter engine end-to-end: the nested AND/OR/NOT filter tree with the full per-type operator matrix in packages/database-engine/src/filter (pure, shared client+server), server-side push-down of the common cases into the Prisma WHERE clause via the existing apps/api filter-builder, client-side evaluation for the rest (same code path), and the filter builder dialog UI (per-view + "Add filter" drawer).

Background

functional-requirements §5.4 (PR-071 P0): tree {op: "and"|"or"|"not", children: [Condition|Group]}, Condition = {propertyId, operator, value}; operators by type — text: is, isNot, contains, doesNotContain, startsWith, endsWith, isEmpty, isNotEmpty; number: is, isNot, greaterThan, greaterThanOrEqual, lessThan, lessThanOrEqual, isBetween, isEmpty; date: is, isBefore, isAfter, isOnOrAfter, isOnOrBefore, isWithin, isRelative ("this week"), isEmpty; select/status/multi: is, isNot, contains, doesNotContain; person: is, isNot, is ("me"); checkbox: isChecked, isUnchecked; relation: contains, doesNotContain, isEmpty (Phase-6); file: hasFiles, hasNoFiles. Special value tokens: "me" (current user), "today", "now" (resolved server-side per actor/timezone). "Evaluation is server-side in packages/database-engine (shared with the client for optimistic UI) — same code path, property-tested against golden datasets." The query endpoint's filter slot (TASK-0066 R1) is filled here.

Requirements

• R1. packages/database-engine/src/filter/ — pure engine (no Prisma imports):
  • schema.ts: FilterTreeSchema (recursive, Zod) + ConditionSchema (operator enum per property type — operatorsFor(type)), token values ("me"|"today"|"now"|type-native); max depth 10 (400 beyond); validate(tree, properties) domain check (property ids exist, operator valid for type, value valid for operator+type).
  • evaluate.ts: evaluateCondition(value, condition, ctx {now, actorUserId, userTimezone}) per the operator matrix (dates compared as UTC instants, ranges use start for single dates, "me" resolves to ctx.actorUserId, empty semantics: missing EAV row = empty for text/select/number; checkbox missing = false; date missing = empty; multi-select empty = []); applyFilter(records, tree, ctx) — full client-side evaluation over RecordWithValues[] (used for optimistic UI + the "rest").
  • pushdown.ts: toPrismaWhere(tree, properties) → {where: Record, residual?: FilterTree} — rewrites the pushable subset into the EAV-existence/value conditions over database_records + record_properties (join-free where possible: record_properties EXISTS subqueries keyed by property_id + value expressions — text contains → value::text ILIKE '%…%' (via Prisma string_contains on the JSON? — Prisma JSON operators: use record_properties.value path expressions where the value shape is flat: number value = 42 via Json equality, date value->>'start' style comparisons through Prisma Raw expressions limited to the expression-index columns of database-architecture §3.3, select value = "opt-id"); NOT/OR combinations that can't be expressed → residual tree evaluated client-side; the invariant (property-tested): toPrismaWhere + residual evaluation == applyFilter on the same dataset (golden datasets + fuzz).
  • tokens.ts: token resolution (me/today/now per ctx), relative-date windows ("this week" = locale-aware Monday-start week in ctx.userTimezone).
• R2. Query endpoint integration (TASK-0066 pipeline): filter in the request → validate (400 with condition index + field) → toPrismaWhere → base query with the pushed where → residual applied in-process over the fetched page's neighbors? no — residual correctness requires the full filtered set: if residual is non-null, the server evaluates it over a bounded candidate set (top 5000 by base order) and documents the bound (100k-record DBs with exotic filters are a documented limitation, logged); sort/group (TASK-0068) run after filtering.
• R3. Optimistic client filter: applyFilter (same package) over the cached records in useRecords when the user is tweaking a filter (instant preview), then the server response reconciles (same code path — no drift).
• R4. Filter builder UI apps/web/database/filter-builder/: "Add filter" button (view toolbar) + f key focus → drawer (right, 380 px, role=dialog): group editor (top-level AND/OR/NOT toggle — NOT shows a single child slot; add condition/group buttons; each condition row: property picker (all db properties, type icon) → operator dropdown (operatorsFor(type)) → value editor per type (text input, number input + between-range, date picker + relative dropdown for isRelative, select dropdown of options, person picker with "Me" first, checkbox toggle, files has-files toggle); rows collapsible; delete condition/group (⌫ with confirmation when the group has children); "Clear all"; applies live (debounce 300 ms → query). The same builder is embedded in the board group picker (TASK-0070) and the chart config (TASK-0074) — one component, variant="drawer"|"inline".
• R5. Filter chips: active filters render as chips in the view toolbar (one per top-level condition, expandable for groups — chip text uses display() of the value; × removes it from the tree); chips are URL-serializable? filters stay in the view's persisted filter JSONB (per-view, shareable via the view deep link) — the drawer is the single editor.

Acceptance Criteria

• [ ] Unit (golden datasets): 40+ hand-built datasets (per operator × per type, incl. missing values, empty strings vs null, date boundaries 00:00/23:59, ranges, multi-select arrays, "me"/"today" tokens) — applyFilter returns the expected record-id sets; fuzz: 1,000 random (tree, values) pairs assert pushdown+residual == applyFilter (no counterexample in CI).
• [ ] Integration: a 100k-record seed with known distributions — filter status = "Done" AND (number > 100 OR date isBefore today) → response total matches the independently computed truth (SQL cross-check in the test), and p95 stays < 300 ms (push-down proven by EXPLAIN fixture assert: no sequential scan of record_properties without a property filter on the pushed subset).
• [ ] E2E: build (Status = In Progress OR Status = Review) AND Owner = Me in the drawer (the PRODUCT-SPEC §9.3 example) → rows update live; chips show 2 top-level chips; remove the Owner chip → rows widen; reload → filter persisted on the view (database_views.filter non-null).
• [ ] E2E: a filter referencing a deleted property (via direct API manipulation) → 400 with the condition index, view falls back to no-filter + error toast (no crash).
• [ ] E2E: isRelative "this week" in the user's timezone (test sets timezone America/New_York) filters the expected Monday–Sunday window (asserted against seeded dates on both boundary days).
• [ ] NOT semantics: NOT (checkbox isChecked) returns records with unchecked and missing checkboxes (documented empty semantics, asserted in a unit golden case).

Backend Changes

• apps/api/src/modules/databases/query.ts filter slot (validate + push-down + residual bound + metrics query_filter_residual_total); the filter-builder directory in apps/api/src/modules/databases is the existing scaffold — this task completes it as the API-side wrapper around @factory/database-engine (no duplicate engine logic in the app — AGENTS.md rule 5).

Frontend Changes

• apps/web/database/filter-builder/{FilterDrawer,FilterGroup,ConditionRow,ValueEditor}.tsx, chip bar, useRecords optimistic-filter wiring.

Database Changes

• None (expression indexes are the documented MVP+1 follow-up; the push-down targets the existing uniques in Phase 5).

API Changes

• POST /v1/databases/:id/query body gains filter: FilterTree (shared Zod FilterTreeSchema — the wire contract for the tree, frozen for Phase 6/8 reuse).

Tests

• Unit: the golden dataset suite (committed fixtures in packages/database-engine/src/filter/__tests__/fixtures/), push-down invariant fuzz, token/timezone windows, validation errors (field-level).
• Integration: the 100k truth-check, EXPLAIN fixture, residual-bound logging, invalid-tree 400s.
• E2E: the five scenarios (TASK-0078 suite) + the "filter+sort+group" exit scenario shares this file's scenarios.

Security

• Filter values are user input: token resolution is server-side only ("me" never resolves client-side in the persisted tree — the stored tree keeps the literal token, resolved per actor at query time — two users filtering "Owner = Me" get different result sets, asserted in an integration test); value editors strip HTML (plain-text values); the residual bound (5000) is a performance guard, never a permission guard (permissions are orthogonal and always applied); no raw SQL string interpolation (Prisma parameterized expressions only).

Documentation

• docs/api/databases.md filter section (tree shape, operator matrix table, tokens, residual bound); packages/database-engine/README.md filter engine contract (the Phase-8 search reuse note).

Dependencies

• TASK-0064 (codecs — value comparison/display), TASK-0065 (records/values), TASK-0066 (query pipeline slot), packages/ui (drawer, pickers).

Definition of Done

• Backend implemented (push-down in the API, engine in the package); frontend implemented (builder + chips); no migration; API validation (shared FilterTreeSchema); authorization (query-level: VIEW; per-actor token resolution); error states (invalid tree 400 with condition index, deleted-property 400 + graceful fallback); loading states (drawer value pickers spinner while properties load); empty states (no conditions → drawer shows the example hint from the spec); mobile (drawer full-screen, value pickers bottom sheets); accessibility (drawer role=dialog + focus trap, condition rows aria-label "Filter: Status equals In Progress", value editors labeled, live region announces "1,204 entries match" after each apply); unit (golden + fuzz) + integration + E2E; docs; logging (filter push-down ratio metric + residual-bound hits); audit: none (filter is a view setting, not a security-sensitive op).

---

TASK-0068

Title

Sorting + grouping (multi-key sort, collapsible group sections, group order by value)

Objective

Implement multi-key sorting and multi-level grouping in the engine (applied after filter, per the pipeline order) and in the UI: sort manager (add/remove/reorder keys, null ordering per key), group manager (1–3 levels, collapsible sections with counts, group order by value/property), consumed by the table (group sections), board (group[0] = columns), and all other views.

Background

functional-requirements §5.4 (PR-072/073 P0): sort applied after filter — multi-key [{propertyId, direction, nullOrder? (default nulls-last)}], stable sort with record position as the final tiebreaker; group applied after sort — multi-level (≤ 3), collapsible, group headers show counts, "boards use group[0] as columns". database_views.sort/group JSONB columns exist. Values sort by type semantics (number numeric, date chronological, select/status by option order from config, text locale-aware, person by name, checkbox false-first, missing values per nullOrder).

Requirements

• R1. packages/database-engine/src/sort.ts: sortRecords(records, keys, properties, ctx) — pure, stable (final tiebreak position then id); per-type comparators from the codec registry (compare() — TASK-0064), option-order comparator for select/status (config option index, unknown/missing last), nullOrder per key (nulls first/last, default last); validate(keys, properties) (≤ 5 keys, property exists, no duplicate property keys).
• R2. packages/database-engine/src/group.ts: groupRecords(records, groups, properties, ctx) → GroupMeta[] — GroupMeta = {key: valueToken|'__empty', label, count, records: [...] | children: GroupMeta[] (≤ 3 levels)}; group key per value: select/status/date use the option/date bucket (date grouping buckets: day/week/month per config.groupGranularity), text/number/person use the raw value, missing → "__empty" group labeled "(Empty)" always last; groups ordered by the group property's semantics (select by option order, date chronological, text locale, number numeric) with an explicit direction per level (ASC/DESC).
• R3. Query endpoint integration: sort + group slots filled (pipeline: filter → sort → group → paginate — pagination is over the flat filtered+sorted list; groups are computed over the full candidate set bounded as in TASK-0067 residual (5000) so counts are correct for the bound; total = flat count; when grouped, the response carries groups: GroupMeta[] with records per group instead of a flat page — the table renders group sections from this).
• R4. Sort manager UI: table header ⋯ "Add to sort" (from TASK-0066) + a sort drawer (view toolbar "Sort" button with an active-count badge): key list (property + direction toggle + null-order dropdown + drag/up-down reorder + delete), max 5 (disable add + tooltip); applies live (300 ms debounce); "Clear all".
• R5. Group manager UI: view toolbar "Group" button (active-levels badge): level list (≤ 3: property picker — for boards only one level is allowed, enforced by the board view, TASK-0070; direction toggle per level; delete level); table renders groups as collapsible sections (header: group label + count + collapse chevron aria-expanded, body = the table rows of that group; collapsed state is client-side per view session (localStorage per view id), counts always shown); board/gallery/calendar/timeline consume group[0] as columns/swimlanes (their tasks).
• R6. Grouped table behavior: "New row" inside a group section creates a record with that group's property value pre-filled (the group's select option / date bucket start); the bulk toolbar operates on the selection regardless of group boundaries; sort-within-group = the flat sort already applied (documented: sort is global, groups partition it).

Acceptance Criteria

• [ ] Unit: 20+ sort fixtures (multi-key precedence, null ordering per key, option-order sort with a mid-list-deleted option, date boundary, locale text, stable tiebreak by position); 15 group fixtures (3-level nesting, empty-group-last, date buckets day/week/month, group direction, counts sum to total).
• [ ] Integration: on the 100k seed, sort number DESC + status ASC → the first 100 records match an independent SQL-ordered truth; group by status → GroupMeta counts sum to the filtered total; group+filter+sort combined = expected nested counts.
• [ ] E2E: table with 3 group levels: sections render with counts; collapse a section (rows hidden, count visible) → persists across reload (localStorage per view); add a record inside the "Done" section → it appears in "Done" with status = Done pre-filled.
• [ ] E2E: sort drawer — add 2 keys, reverse the second, remove the first → rows reorder per the server response each time (network assert: 3 query calls with the evolving sort array); the 6th key is not addable (disabled + tooltip "Maximum 5 sort keys").
• [ ] E2E: aria-sort reflects the primary key only (documented: secondary keys shown as numbered badges ①② in the header); null-order change re-sorts (missing-value rows move).
• [ ] Grouping a board (TASK-0070 consumes this) — the board's group picker offers exactly one level (integration: a 2-level group on a board via API is accepted and stored, but the board renderer uses level 1 — documented; the board UI enforces it client-side too).

Backend Changes

• apps/api/src/modules/databases/query.ts sort/group slots; engine packages sort.ts/group.ts.

Frontend Changes

• apps/web/database/{SortDrawer,GroupDrawer,GroupSection}.tsx; table grouped rendering (TASK-0066 TableView extended); useRecords response shape handles groups?.

Database Changes

• None (sort/group JSONB columns exist).

API Changes

• POST /v1/databases/:id/query body gains sort: SortKey[], group: GroupKey[]; response gains groups?: GroupMeta[] (shared Zod: SortKeySchema, GroupKeySchema, GroupMetaSchema).

Tests

• Unit: the fixtures above + fuzz (random sort keys over random datasets: determinism — same input twice = same output; stability property with duplicate keys).
• Integration: 100k sort/group truth-checks, combined pipeline, response shape.
• E2E: the five scenarios (TASK-0078 suite).

Security

• Sort/group are read-shaping (no security surface beyond the query's existing permission check); group labels derived from user data are rendered escaped; no unbounded computation (5000 bound + 5-key/3-level caps — enforced server-side, 400 beyond).

Documentation

• docs/api/databases.md sort/group section (key shapes, null-order, group granularity, bounds); packages/database-engine/README.md pipeline order diagram (filter→sort→group→paginate).

Dependencies

• TASK-0064 (comparators), TASK-0066 (query pipeline), TASK-0067 (filter runs first), packages/ui drawers.

Definition of Done

• Backend implemented; frontend implemented; no migration; API validation (shared Zod caps); authorization (query-level VIEW); error states (invalid key 400, over-cap 400 with the cap name); loading states (sort/group drawer apply spinner on the view toolbar); empty states (no groups → flat table unchanged; no sort → position order documented in the "Sort" button tooltip "Currently: entry order"); mobile (drawers full-screen; group sections touch-friendly chevrons ≥ 44 px); accessibility (group header role=button + aria-expanded + count in label, sort keys announced in the live region "Sorted by Status ascending, then Due date descending"); unit + integration + E2E; docs; logging (group-bound hits metric, shared with the filter residual metric); audit: none.

---

TASK-0069

Title

View management: create/rename/delete/reorder views, per-view config, view tabs UI

Objective

Complete view lifecycle management for all 9 view types: create (type picker, per-type config defaults), rename, delete (last view protected), reorder (fractional position), and per-view configuration persistence (visible properties, column widths, card layout, color, density, page size, plus filter/sort/group from 0067/0068) in database_views.config JSONB — with the view tabs bar as the single UI.

Background

frontend-architecture §6 + functional-requirements §5.3: "views are per-database, not per-page (a linked_database block re-uses the same views); view creation via '+' with type picker; view rename/duplicate/delete (last view cannot be deleted); per-view settings persist independently (changing one view's filter never affects others)." Each view keeps name, type, position, config, filter, sort, group, pageSize (model). config shape (Zod ViewConfigSchema per type): visiblePropertyIds[] (order = display order of visible columns, distinct from the global property position — visible list defines order in this view), columnWidths{pid: px}, cardLayout (board: {showCover, properties[] (≤2), showAvatar}; gallery: {cover: bool, size: 'S'|'M'|'L', properties[] (≤3)}; list: {properties[] (≤2)}), color (accent token for the view tab/card), density (table: compact/default/relaxed; board: compact/comfortable), pageSize (10/25/50/100/200, default 50 for non-table views), chartId? (chart view), dashboardId? (dashboard view), groupGranularity (date views), wipLimit? (board, TASK-0070).

Requirements

• R1. API:
  • POST /v1/databases/:id/views — {name (unique per db, ≤ 50), type: ViewType (9), config?} → appends at end (position); per-type config defaults (Zod .default() per type — e.g. table: all isVisible properties in position order, density default, pageSize 50; board: group defaults to the first STATUS/SELECT property if any, else none + a "choose a property" state (functional-requirements §5.3 BOARD rule); calendar: first DATE/DATE_RANGE property, else "choose" state); emits database.updated.
  • PATCH /v1/databases/:id/views/:vid — rename, reorder (after view id, fractional), config (per-type schema validation — unknown keys rejected), filter/sort/group/pageSize (same schemas as the query endpoint — a view's settings are exactly the query's ad-hoc shapes, so the UI can deep-link ?view= and the server can resolve viewId to the same pipeline).
  • DELETE /v1/databases/:id/views/:vid — 409 last_view if it's the only view; deleting a view never touches data.
  • POST /v1/databases/:id/views/:vid/duplicate — copy name ("Copy of X" + uniqueness suffix), type, config, filter, sort, group.
  • GET /v1/databases/:id/views/:vid — single view (for the deep-link boot path).
• R2. View tabs bar (apps/web/database/ViewTabs.tsx, part of DatabaseHome): icon per type + name + active color underline; + → type picker popover (9 types with icons + one-line descriptions; FORM shows the "Phase 10" disabled state — header note 2); active tab from ?view= (default: first view); rename on double-click (inline, Enter commits, Esc reverts, uniqueness 409 → toast + keep editing); tab ⋯ menu (rename, duplicate, delete [409 last-view toast], color picker [9 accent tokens → config.color]); tab drag reorder (dnd-kit, position PATCH on drop); keyboard: ←/→ on the tablist moves focus, ↑ opens the ⋯ menu, 1…9 (ux §10) switches to view n.
• R3. Per-view settings panel: each view's toolbar has a ⚙ "View settings" (right, role=menu): page size picker, density picker, color picker, "Reset to defaults" (config only — filter/sort/group kept, documented). Table-specific column visibility/width lives in the table header (TASK-0064/0066) and writes to the view's config (not the global property isVisible — the global is the default for new views; visiblePropertyIds is per-view truth).
• R4. Config isolation (the spec's "changing one view's filter never affects others"): every view renderer reads its own view's resolved settings (view viewId → GET views/:vid once per mount, cached dbmeta-adjacent); no shared mutable view state in stores (per-view Zustand slices keyed by view id, or pure props — choose pure props + TanStack Query, document the decision in the view README).
• R5. New-view defaults for the 8 Phase-5 types are encoded in packages/shared/src/dtos/views.ts (defaultViewConfig(type, database) — pure, unit-tested, shared by the API and the UI preview).

Acceptance Criteria

• [ ] E2E: create views of all 8 Phase-5 types via + → each renders with correct defaults (board with a status property groups by it; calendar with a date property targets it; a db with neither shows the "choose a property" state); FORM appears in the picker disabled.
• [ ] E2E: set view A's filter + view B's sort → switch between them (tabs + ?view= deep link) → settings never cross (network assert: each view's query carries only its own filter/sort); reload preserves both.
• [ ] Rename a view to an existing name → 409 + toast, input keeps the text; delete the last view → 409 last_view + explanatory toast; duplicate a view → "Copy of X" with identical config (asserted via API).
• [ ] Reorder 4 tabs by drag → database_views.position ordered; ?view= deep link still targets the right view after reorder (tab identity = id, not index).
• [ ] Page size switch 50→100 in a view → query pageSize changes for that view only; density switch changes row height (DOM assertion) and persists per view.
• [ ] Unit: defaultViewConfig for all 8 types against fixtures (db with/without status/date/person properties) — stable defaults.
• [ ] Keyboard: tabs operable without pointer (←/→/↑/Enter, 1…9 switching — E2E).

Backend Changes

• apps/api/src/modules/databases/views/* (5 endpoints); packages/shared/src/dtos/views.ts (ViewConfigSchema discriminated by type, defaultViewConfig).

Frontend Changes

• apps/web/database/{ViewTabs,ViewSettingsMenu,ViewTypePicker}.tsx; useDatabase cache extended with per-view entries; the 8 view renderers accept a resolved ViewSettings prop (their tasks 0070–0075 consume this).

Database Changes

• None (model columns exist).

API Changes

• New: 5 view endpoints per R1 (documented in docs/api/databases.md).

Tests

• Unit: defaultViewConfig matrix, per-type ViewConfigSchema (unknown key rejection, caps: card properties ≤ 2/3, pageSize enum), view name uniqueness logic.
• Integration: full lifecycle (create/rename/reorder/delete/duplicate/last-view 409), config isolation (two views, mutate one, read both), deep-link boot (GET views/:vid).
• E2E: the six scenarios (TASK-0078 suite).

Security

• View writes = EDIT on the database/page; view reads = VIEW (a VIEW user can open ?view= deep links — the view settings are not confidential); config JSONB validated (a crafted config cannot inject a chartId/dashboardId pointing at another workspace — server checks cross-refs same-workspace when the phase that owns them lands; Phase 5: chart/dashboard ids validated against the same database, 400 otherwise); no SQL surface.

Documentation

• docs/api/databases.md views section; apps/web/database/README.md per-view settings contract (renderers get ViewSettings, never raw JSONB).

Dependencies

• TASK-0063 (DatabaseHome/tabs host), TASK-0066/0067/0068 (query shapes the settings mirror), packages/ui (tabs, popovers).

Definition of Done

• Backend implemented; frontend implemented; no migration; API validation (per-type config schemas, field-level 400s); authorization (matrix: guest read, member edit, admin delete); error states (409 last view / duplicate name, 400 cross-db chart ref); loading states (tab skeleton while views load, per-view boot spinner); empty states (view with no records → the TASK-0063 per-view empty states); mobile (tabs scroll, settings as bottom sheet); accessibility (tablist contract, keyboard E2E, rename input aria-label, color picker by name); unit + integration + E2E; docs; logging (view lifecycle ops with view id + type); audit: none (view settings are not security-sensitive; database.updated covers consumers).

---

TASK-0070

Title

Board view (kanban: group columns, cards, dnd-kit card drag → property update)

Objective

Implement the BOARD view: kanban columns from the single group property (default: first STATUS/SELECT; picker when none), cards showing title + configured properties + avatar, dnd-kit card drag between columns that updates the group property (one API call), per-column "+" create (pre-set option), WIP display, collapse empty columns, and horizontal + per-column virtualization.

Background

functional-requirements §5.3 BOARD: "Exactly one group property (default: first STATUS/SELECT; picker if none); columns = option values in option order; ungrouped records → 'Empty' column; cards show title + configured properties; card click opens page; drag card to column = status update (one API call); '+' per column creates record pre-set to that option." PR-064 P0. View config: cardLayout {showCover, properties[] ≤ 2, showAvatar}, density, wipLimit?, color. dnd-kit per the Phase-4 pattern (frontend-architecture §3.5 — board groups as containers). The card drag is the database-side analog of PR-053 (Phase 5, this task).

Requirements

• R1. apps/web/database/views/BoardView.tsx: query with group: [groupKey] (single level — the group picker in this view offers exactly one property of a groupable type: select, multi-select (record appears in each matched column), status, date (day buckets), person (per-user column); non-groupable → disabled with tooltip) → columns from GroupMeta (option order; missing option with wipLimit-irrelevant → column still renders if the option exists in config; records with a value for a deleted option → "Empty"? no — orphan values go to the "(Empty)" column, consistent with TASK-0068).
• R2. Column: header (label + count + WIP indicator when wipLimit set: count/limit, red when over) + collapse chevron (collapsed = header only, count visible, client-side persisted per view) + cards + "+ Add card" (creates a record with the group property pre-set to the column's value — date columns: today; person: that user; "(Empty)": no value) + column drag reorder? no — column order is the property's option order (reorder = edit options, TASK-0064); a "Hide empty columns" toggle in view settings (default off).
• R3. Card: cover (when showCover + record has cover), title (2-line clamp), up to 2 property chips (cardLayout.properties — the view settings panel has a "Card properties" picker, max 2, type-agnostic via codec display()), avatar (first PERSON property's user, or record creator when showAvatar); drag handle = the whole card (dnd-kit, KeyboardSensor too — Space lifts, arrows move between columns, Space drops, announced "Column: In Progress, 3 of 7" per ux §12); click (non-drag) → record page; card ⋯ menu (open page, duplicate, archive, comment — parity with table row actions).
• R4. Drag semantics: drop card into another column → PATCH record {properties: {groupPropertyId: targetValue}} (one call; multi-select group: the target option is added, never removed by drag — removal is via the cell editor; drag within the same column = reorder via record.position PATCH (same call shape, position field)); optimistic: the card animates into the new column immediately (FLIP, reduced-motion → instant), on API failure the card animates back + toast (rollback pattern from TASK-0066); multi-card drag: shift-click selects cards, dragging one drags the selection (all updated in one batch request — group property same for all); drop zone indicator: column highlight + a position line within the column.
• R5. WIP: wipLimit (number, view settings) → column header shows n/limit (red + "over WIP" label when over); no blocking (display-only in Phase 5 — documented).
• R6. Virtualization: horizontal column virtualization (@tanstack/react-virtual horizontal, overscan 2) + per-column card virtualization (row virtualization per column) — a board with 20 columns × 2,000 cards renders only visible DOM; covers lazy-loaded.

Acceptance Criteria

• [ ] E2E (the exit-critical one): a board grouped by Status (4 options) → drag a card from "To Do" to "In Progress" → the card renders in the new column immediately; network: exactly one PATCH /records/:rid with {properties: {<statusPid>: "opt-inprog"}}; reload → card still in "In Progress" (property persisted, not just position).
• [ ] E2E: board with no status/select property → the group picker is the first-run state ("Group board by…"); picking a DATE property creates day-bucket columns (today's card visible under today).
• [ ] E2E: "+ Add card" in the "Done" column → new record with status = Done appears in the column (one create call, pre-set value asserted in the request body).
• [ ] E2E: multi-select group — a record with options [A, B] appears in both columns A and B; dragging it from A to C updates the value to [A, B, C]? no — per R4: drag adds C → [A, B, C], card visible in 3 columns (asserted).
• [ ] E2E (a11y): keyboard card move — focus a card, Space, Right (column), announced "Column: In Progress, 3 of 7", Space → same property update (network assert); axe clean on the board.
• [ ] Perf: 20 columns × 2,000 cards seed → DOM card count ≤ 80, horizontal scroll frame-drop < 5 % (CI probe on the 100k-record board slice).
• [ ] E2E: failed drop (simulate 409 via two concurrent users updating the same card — the test hammers the same card) → card returns to origin + toast "Couldn't move card — try again"; no half-state (property unchanged in DB).

Backend Changes

• None new (records PATCH + query group — both exist from 0065/0068); verify PATCH supports the optional position field for within-column reorder (add it to RecordPatchSchema if missing — additive).

Frontend Changes

• apps/web/database/views/{BoardView,BoardColumn,BoardCard,BoardGroupPicker}.tsx; RecordPatchSchema.position? (shared); card FLIP animation helper (respects reduced motion).

Database Changes

• None.

API Changes

• RecordPatchSchema additive position (fractional, within-database row order).

Tests

• Unit: column derivation from GroupMeta (option order, empty column, orphan values), card chip selection logic, wip-limit display predicate, multi-select membership set.
• Integration: drag-equivalent API (PATCH group value + position reorder), the concurrent 409 scenario (two PATCHes racing — one 409s, the winner's value is the one in the DB), batch multi-card update transactionality.
• E2E: the seven scenarios (TASK-0078 suite) — including "board drag updates property" from the phase exit criteria.

Security

• Card drag = a record property write (EDIT check per PATCH — a VIEW user's board is readonly: no drag handles, cards not draggable, "+" hidden); option ids in the drag payload validated against the property's config (a crafted target option id that doesn't exist → 400 — the card cannot be "moved" into a nonexistent column); avatar user ids resolved server-side in the query response (never client-trusted names).

Documentation

• docs/api/databases.md: board drag = record PATCH note; apps/web/database/README.md board container contract (Phase-11 automations reading board state use the same query).

Dependencies

• TASK-0066 (cell codecs, query), TASK-0068 (grouping), TASK-0069 (view settings incl. cardLayout/wipLimit), TASK-0065 (record create pre-set), Phase-4 dnd-kit patterns (packages/editor/src/dnd collision/position logic reused where applicable).

Definition of Done

• Backend verified (additive patch field); frontend implemented; no migration; API validation (target option id, position alphabet); authorization (readonly board for VIEW — integration + E2E); error states (409 rollback animation + toast, 400 bad option, 404 archived card mid-drag → removed gracefully); loading states (column skeletons, per-column card fetch spinner); empty states (empty column "+ Add card" prominent, no-group state with picker); mobile (horizontal swipe between columns, cards full-width stacked, drag via card ⋯ "Move to column…" menu — the non-drag alternative required by PR-056); accessibility (keyboard card move E2E, announcements, role=grid-equivalent list of columns role=group + aria-labels, WIP over-limit announced not color-only); unit + integration + E2E + perf; docs; logging (drag ops: card id, from/to column, batch size); audit: card moves = record.updated (mandatory, in-transaction).

---

TASK-0071

Title

Calendar view (month grid + week strip, date mapping, day-click create, navigate, today)

Objective

Implement the CALENDAR view: month grid + week strip, record placement by a DATE or DATE_RANGE property (start/end for ranges), multi-day spans, day click → create record with the date pre-filled, month/week/day navigation, today highlight, and month-level windowing.

Background

functional-requirements §5.3 CALENDAR: "Day/week/month switch; source property = first DATE or DATE_RANGE (picker); month grid with record dots/mini-cards; week/day list; click day → new-record dialog (date pre-filled); multi-day ranges render as spans; 'today' highlighted." PR-065 P1. View config: sourcePropertyId (a DATE/DATE_RANGE property — the "choose a property" first-run state when the db has none), mode: 'month'|'week'|'day', pageSize-irrelevant (the calendar fetches the visible window ± 1 month of records — a range query, not a cursor page). Query support: the endpoint gains a dateWindow {from, to} convenience filter (server-side: push-down to the source property's date columns — reuses TASK-0067 push-down for date between).

Requirements

• R1. apps/web/database/views/CalendarView.tsx: month grid (7 columns × 5–6 weeks, other-month days dimmed), week strip (7 day columns, horizontal day headers), day list (single day's records, time-sorted); mode switcher (Month/Week/Day segmented control); navigation: ←/→ prev/next unit, "Today" button, PageUp/PageDown = month, Shift+PageUp/Down = week (ux §12 calendar keys), current date in the header ("September 2025").
• R2. Record placement: source property value → date bucket: DATE → single day; DATE_RANGE {start, end} → span from start to end (month view: a bar across the days, clamped to the visible month with "‹ n more" overflow handling at edges; week view: horizontal bar across the days). Records with no date value → not shown in the calendar (they remain in other views — documented; a "Show undated" toggle in view settings lists them in a side rail? no — out of scope, the spec says placed by the date property; undated records are simply absent, the count in the header shows "n undated hidden").
• R3. Day cell: up to 3 mini-cards (dot + title + optional second property chip, config cardLayout-shared) + "n more" button (opens a day popover: full list, click → record page, "+ Add" in the popover); day click (empty area) → new-record dialog (title input + the source date pre-filled to that day — for ranges: start = that day, end empty; other properties at defaults) → create via POST records (one call) → the new card appears in the cell.
• R4. Today: the current day cell highlighted (accent border + "Today" announced per ux §12); a today marker line in the week strip; the highlight follows the server date at query time (timezone = the user's User.timezone — the day bucketing is done in that timezone, unit-tested).
• R5. Range query: the view requests dateWindow: {from: monthStart, to: monthEnd} ± 1 month margin (spans crossing the edge); the server applies it via the TASK-0067 push-down (isBetween on the source property) and returns the window's records + their full values (the calendar needs icon/cover/secondary chip); total = window count (not db total — the header shows the window count).
• R6. Record interaction: card click → record page; card drag to another day (month view only) → updates the source property (single date: new value; range: start moves, end moves with the span; one PATCH — same optimistic/rollback pattern as board drag); keyboard alternative: card ⋯ "Move to date…" (date picker) per PR-056.

Acceptance Criteria

• [ ] E2E: a db with a DATE_RANGE property → month view shows a 3-day span bar across the correct cells; the span is clamped at month edges with the correct continuation in the next month; a single DATE record dots one cell.
• [ ] E2E: click an empty day cell → dialog opens with the date pre-filled (asserted in the dialog input) → create → the card appears in that cell; network: one POST /records with the value for the source property = the clicked date.
• [ ] E2E: navigation — September 2025 → click "Today" → current month; → → → three months ahead (header updates); PageDown week-forward works; today cell has the highlight + "Today" label in the a11y tree.
• [ ] E2E (timezone): seed a record at 2025-09-30T23:30:00Z; user timezone Pacific/Kiritimati (UTC+14) → the record lands on October 1 in the calendar (timezone-aware bucketing asserted).
• [ ] E2E: drag a single-date card from the 12th to the 15th → one PATCH with the new date; drag a range span (3 days) by its middle → both start and end shift by +3 days (one PATCH with the new {start, end}); failed drag (409) → span animates back + toast.
• [ ] Perf: a 20,000-date-value db → month view first paint < 2 s, "n more" popovers lazy (no popover rendered until clicked), window query returns only the ± 1-month slice (response size assertion).
• [ ] A11y: day cells role=gridcell with full date label (ux §12), "n more" is a button, mode switcher role=radiogroup, E2E keyboard navigation (arrows move focus between days, Enter on a day opens the day popover, T? no — "Today" via the visible button only).

Backend Changes

• RecordQuerySchema.dateWindow? {from: ISO, to: ISO} (pushed to the source property via the TASK-0067 push-down; the view's sourcePropertyId is sent so the server knows which property — or the client sends an explicit filter: isBetween tree instead — decision: client sends the explicit filter tree (no new endpoint surface; the calendar builds {propertyId: sourcePid, operator: 'isBetween', value: [from, to]} and the existing pipeline does the rest; recorded in the notes to keep the query surface stable).

Frontend Changes

• apps/web/database/views/{CalendarView,CalendarMonthGrid,CalendarWeekStrip,CalendarDayPopover,NewRecordDialog}.tsx; timezone bucketing helper in packages/database-engine/src/dates.ts (pure, shared with TASK-0073 timeline).

Database Changes

• None.

API Changes

• None (explicit filter tree via the existing query endpoint).

Tests

• Unit: date bucketing per timezone (golden: UTC, UTC+14, UTC-11, DST boundary — a record across a DST transition lands in the right local day), span clamping math (month edges), week strip alignment (weeks start Monday — locale note: default Monday, User.locale-aware in v1.1, recorded), "n more" threshold logic.
• Integration: dateWindow filter truth-check on the 100k-date seed (window count vs SQL), drag-equivalent PATCH (range shift).
• E2E: the six scenarios (TASK-0078 suite).

Security

• Same record-write checks as the board (EDIT to drag/create, VIEW readonly); date values validated as ISO (a crafted non-ISO value → 400); the day popover lists only records the caller can view (query-level permission, no extra surface); no HTML in rendered dates.

Documentation

• docs/api/databases.md: calendar = explicit date-window filter note; apps/web/database/README.md calendar config (source property rules, undated records behavior).

Dependencies

• TASK-0064 (date codecs), TASK-0066 (query), TASK-0067 (date filter operators), TASK-0065 (record create with pre-set value), TASK-0069 (view settings), packages/database-engine/src/dates.ts.

Definition of Done

• Backend verified (no changes beyond the dates helper in the engine package); frontend implemented; no migration; API validation (ISO dates, filter tree shared); authorization (readonly calendar for VIEW); error states (create failure toast + dialog stays open, drag 409 rollback); loading states (month skeleton, day popover spinner); empty states (empty month: "No entries this month" centered + "Add entry" button; no source property → first-run picker state); mobile (week strip default on < 768 px, month grid day cells ≥ 44 px, drag replaced by "Move to date…" menu); accessibility (gridcell contract, today announced, focus navigation E2E); unit + integration + E2E + perf; docs; logging (window query sizes); audit: drag/create = record.updated/record.created (mandatory).

---

TASK-0072

Title

List + Gallery views

Objective

Implement the LIST view (compact rows: icon, title, 2–3 properties, one per row, row virtualization) and the GALLERY view (card grid: cover, icon, title, first text property; lightbox-ish detail popover; grid virtualization; drag reorder within view), completing the simple remaining view types.

Background

functional-requirements §5.3: LIST — "Compact rows: title + up to 2 secondary properties; row click opens page; same filter/sort/group as table; no inline editing except quick actions" (PR-063 P1). GALLERY — "Card grid (cover = record cover, fallback icon); card layout config: cover on/off, size (S/M/L), property rows (≤ 3); click opens page; drag reorder within view" (PR-066 P1). Both consume the standard query pipeline (TASK-0066) + view settings (TASK-0069: cardLayout.properties ≤ 2 for list / ≤ 3 + cover + size for gallery). Gallery drag reorder = record position PATCH (same mechanism as board within-column reorder, TASK-0070 R4) — but only when the view is not grouped/sorted by a property (reorder with an active sort = no-op with a tooltip "Clear sort to reorder" — documented).

Requirements

• R1. apps/web/database/views/ListView.tsx: rows = icon (emoji or file, 20 px) + title + up to 2 secondary property chips (cardLayout.properties picker in view settings) + a chevron; row height 48 px; row virtualization (same virtualizer as the table); row click → record page; row hover → quick actions (open page, duplicate, archive — no inline cell editing per spec; a checkbox column for bulk select parity with the table? spec says "no inline editing except quick actions" — include bulk select (archive/duplicate/delete) for consistency, exclude cell editing); group sections (TASK-0068) render as collapsed section headers with counts (no table-style subgrid — the list within a group is flat); "New entry" button (header) creates with defaults.
• R2. apps/web/database/views/GalleryView.tsx: responsive grid (auto-fill, min column 220 px; cardLayout.size S/M/L scales the card 180/240/300 px + cover height 90/140/190 px); card: cover image (lazy, aspect 16/9, fallback = icon on a tinted background) OR icon-only when cover: false (centered 40 px icon); title (2-line clamp); up to 3 property chips (config picker, ≤ 3); card click → detail popover (lightbox-ish: large cover/icon, title, all visible properties in label/value rows, "Open page" button, quick actions; role=dialog, Esc closes, focus trap, arrow keys navigate between cards — the popover is the gallery's a11y workhorse); drag reorder (dnd-kit card drag, position PATCH, FLIP animation, optimistic + rollback like the board) — only when no active sort (tooltip otherwise); "Add card" ghost tile at the end (creates with defaults).
• R3. Grid virtualization: @tanstack/react-virtual grid mode (both axes) — 5,000-card gallery renders only the visible window + overscan; covers lazy + decoded on activation (TASK-0060's activation pattern reused from packages/editor/src/virtual).
• R4. Both views: per-view filter/sort/group honored (same pipeline), empty states ("No entries yet" + New entry), loading skeletons (row/card shimmer), "n undated hidden" does not apply (no date dependency).

Acceptance Criteria

• [ ] E2E (list): rows show icon + title + exactly the 2 configured properties; click a row → record page; group the list by status → sections with counts render; select 3 rows (shift-click) → bulk archive works (one batch).
• [ ] E2E (gallery): cards render cover (seeded covers) with fallback icons for cover-less records; config size: 'L' + cover: false → large icon cards (DOM dimensions assertion); property rows show ≤ 3 chips (config cap enforced in the picker — a 4th chip can't be added).
• [ ] E2E (gallery detail): click a card → popover with full properties + "Open page" (navigates); Esc closes returning focus to the card; Right arrow moves to the next card's popover (cycling within the visible grid).
• [ ] E2E (gallery reorder): with no sort → drag card 2 to position 5 → position PATCH on the wire; reload keeps the order; with an active sort → drag shows the tooltip and does nothing (no request).
• [ ] Perf: 5,000-card gallery → DOM card count ≤ 60, scroll frame-drop < 5 %, cover decode count ≤ visible (CI probe).
• [ ] A11y: list rows role=gridcell-equivalent rows in a role=list (documented choice: list uses role=list/listitem — simpler than grid since no cell editing), gallery cards focusable buttons + popover dialog contract, axe clean on both.

Backend Changes

• None (query + records exist; gallery reorder reuses RecordPatchSchema.position).

Frontend Changes

• apps/web/database/views/{ListView,GalleryView,GalleryCard,GalleryDetailPopover}.tsx; view settings pickers for cardLayout (both types) in apps/web/database/ViewSettingsMenu.tsx.

Database Changes

• None.

API Changes

• None.

Tests

• Unit: list row composition (chip selection, truncation), gallery card config resolution (size → dimensions map), grid virtualization math, reorder-when-sorted predicate.
• Integration: gallery reorder PATCH order stability (100 records, reorder 10 → new position order matches intent), list group section counts.
• E2E: the six scenarios (TASK-0078 suite) — "all views open a record's page" is asserted for list + gallery here (board 0070, calendar 0071, timeline 0073, chart/dashboard 0074/0075 each assert their own).

Security

• Cover URLs served through the Phase-9/BFF file proxy (never raw storage keys — the query response carries coverUrl already proxy-shaped from TASK-0065); card chip values rendered escaped; reorder permission = EDIT (VIEW users get non-draggable cards + no "+ Add").

Documentation

• apps/web/database/README.md: list/gallery config + the gallery-reorder-with-sort rule.

Dependencies

• TASK-0066 (query, virtualizer pattern), TASK-0068 (grouping), TASK-0069 (cardLayout config), TASK-0065 (record create), TASK-0070 (reorder PATCH pattern), packages/editor/src/virtual activation helpers.

Definition of Done

• Frontend implemented; no migration/API change; authorization (matrix: VIEW readonly, MEMBER edit); error states (popover fetch failure → error state in the popover with retry; reorder 409 rollback + toast); loading states (skeleton rows/cards, cover shimmer); empty states (both views); mobile (list = default density, gallery 2-column min, popover full-screen sheet); accessibility (roles per R1/R3, keyboard E2E, popover focus trap + return, chip labels include property names); unit + integration + E2E + perf; docs; logging (reorder ops); audit: reorder = record.updated (mandatory).

---

TASK-0073

Title

Timeline view (date-range bars on a month-scale axis, horizontal scroll, click-to-open)

Objective

Implement the TIMELINE view: Gantt-style bars from DATE_RANGE (or DATE) properties on a horizontal month-scale time axis with month/week ticks, horizontal scroll + zoom (day/week/month), overlap handling, bar click → open record, drag bar to move / resize edges to update the date property, and a today line.

Background

functional-requirements §5.3 TIMELINE: "Source = DATE_RANGE (or DATE + optional duration property); horizontal time axis with month/week ticks; zoom (day/week/ month/quarter); bars draggable to move (updates property); resize edges (updates end); today line; swimlanes per group property." PR-066-adjacent P1. Phase 5 scope: zoom levels day/week/month (quarter = month + 3 grouping — included via the same math), swimlanes = group by (TASK-0068, optional — "swimlanes per group property" = the view settings group picker, one level), duration-property support for DATE sources is skipped (DATE_RANGE only + DATE renders a zero-width bar on its day — documented). The view needs a range query (TASK-0071's explicit date-window filter pattern).

Requirements

• R1. apps/web/database/views/TimelineView.tsx: layout = left row gutter (record title, 220 px, sticky) + right scrollable time canvas; axis: month ticks (year at year boundaries), week sub-ticks at week zoom, day ticks at day zoom; zoom control (segmented: Day/Week/Month/Quarter → px-per-day: 120/48/16/5, the bar geometry derives from this); horizontal scroll (native, momentum off under reduced motion) + the visible window drives the date-window query (± 1 month margin, same pattern as the calendar); row = one record (row virtualization, 44 px rows, sticky gutter).
• R2. Bars: DATE_RANGE {start, end} → a rounded bar from start to end (end inclusive — a 1-day range is a 1-day-wide bar), color from the first STATUS/SELECT property's option color (fallback accent), title on hover tooltip (not in the bar unless ≥ 120 px wide — then title inside); DATE value → zero-width vertical bar on that day; no value → row renders with a dashed "no dates" placeholder (not hidden — the timeline shows all filtered records, undated ones marked; consistent with the calendar's header note but visible here since rows are the list itself); overlap handling: rows never overlap (one row per record — the Gantt model), "overlap" in the spec sense = bars in different rows at the same time are fine; bars longer than the visible window clamp with edge gradients (continuation indicator).
• R3. Interaction: bar click → record page; drag bar body → move (both start/end shift by the delta, one PATCH {start, end}); drag bar right edge → resize end (one PATCH end); drag bar left edge → resize start; drag precision = the zoom's day unit (snaps to day); optimistic + rollback (FLIP-free — bars are absolute-positioned, transform updates, 409 → bar animates back + toast); keyboard alternative (PR-056): bar focus (Tab within the row) + ←/→ move by one day (start, end follows for move; Shift+←/→ resizes end; Alt+←/→ resizes start) + Enter opens the record — announced via live region "Due 2025-10-01 to 2025-10-14".
• R4. Today line: vertical accent line at today (user timezone, TASK-0071 bucketing), labeled "Today" at the top; sticky within the scroll (always visible when today is in the rendered range, else a "→ Today" button in the header jumps the scroll to it).
• R5. Swimlanes: optional group (one property, TASK-0068 group picker) → collapsible lane headers (label + count) partitioning rows; ungrouped = one flat row list; the axis is shared across lanes (single scroll).
• R6. Query: dateWindow over the source property (explicit filter tree, TASK-0071 pattern) ± margin; records outside the window (undated or out-of-range) render from the same response when they are undated (the filter is dateWindow OR isEmpty on the source property — an OR tree the push-down handles; the test asserts the SQL truth); sort default = start ASC.

Acceptance Criteria

• [ ] E2E: a 14-day range renders as a 14-day-wide bar at month zoom (pixel width = 14 × px-per-day ± 2); a 1-day range renders the minimum 1-day width; a bar spanning the visible edge clamps with the continuation gradient.
• [ ] E2E: drag a bar body +5 days (at week zoom, 5-day pointer distance) → one PATCH with start/end both +5 days; drag right edge +3 days → one PATCH with end +3 only; failed drag (409) → bar returns + toast; reload persists.
• [ ] E2E (keyboard): focus a bar, → ×3 → start+end +3 days (3 PATCHes, announced each), Shift+→ resizes end, Enter opens the record page (no pointer).
• [ ] E2E: zoom Day → Month → Quarter changes px-per-day (axis tick density assertion: month zoom shows week ticks, day zoom shows day labels); the date-window query refetches on zoom (network assert, response covers the new window).
• [ ] E2E: today line renders at the correct column (test with a fixed "today" via a server-time fixture? — the test uses the real today; asserts the line's x-position matches the computed column for today's date ± 1 px).
• [ ] E2E (swimlanes): group by status → 4 collapsible lanes with counts; collapse a lane (rows hidden, count visible); the axis stays aligned (single horizontal scroll moves lanes + axis together).
• [ ] Perf: 3,000 dated records spanning 24 months → first paint < 2 s (window = 1 month ≈ ~125 rows), row virtualization active (DOM rows ≤ 30), horizontal scroll frame-drop < 5 %.

Backend Changes

• None (explicit filter tree; dates helper from TASK-0071 shared).

Frontend Changes

• apps/web/database/views/{TimelineView,TimelineAxis,TimelineBar,TimelineLane}.tsx; bar geometry math in packages/database-engine/src/timeline.ts (pure: date→x, drag delta→new dates, clamp logic — unit-tested, shared with a future Gantt export).

Database Changes

• None.

API Changes

• None (query filter tree + record PATCH exist).

Tests

• Unit: date↔x geometry (all 4 zoom levels, DST-aware via the shared dates helper — a bar crossing a DST day keeps its day count), drag delta math (move/resize-end/resize-start, snapping, min 1-day width), window margin math, overlap-free row assignment.
• Integration: dateWindow OR isEmpty filter truth-check (dated + undated records both present, correct split), range PATCH (move/resize) on the 100k-date seed.
• E2E: the six scenarios (TASK-0078 suite) — "all views open a record's page" asserted for the timeline here.

Security

• Same record-write checks (EDIT to drag/resize, VIEW readonly — bars not draggable, no resize handles); PATCH validates ISO dates + end >= start (400 invalid_range if violated — a drag can't invert a range); bar colors from option config (closed palette, no injection); axis labels are server-safe strings.

Documentation

• apps/web/database/README.md: timeline config (source rules, undated rendering, zoom table); docs/api/databases.md: note the timeline's OR-isEmpty window pattern (reused by Phase-13 API consumers).

Dependencies

• TASK-0064 (date codecs), TASK-0066 (query, virtualizer), TASK-0067 (date operators + OR tree push-down), TASK-0068 (swimlane grouping), TASK-0071 (dates helper, window pattern), TASK-0069 (view settings), TASK-0065 (records).

Definition of Done

• Frontend implemented; no migration/API change; authorization (readonly timeline for VIEW); error states (invalid range 400 + toast, drag 409 rollback, empty window "No entries in this period" + "Today" jump); loading states (row skeletons + axis shimmer); empty states (no source property → picker state, no dated records in window → empty message); mobile (timeline = horizontal scroll with 200 px gutter, drag replaced by "Edit dates…" popover (date pickers for start/end) per PR-056); accessibility (bars role=button? — bars are interactive regions: role=gridcell with aria-roledescription="timeline bar" + the announced date range, keyboard E2E, reduced-motion: no bar animation, instant updates); unit + integration + E2E + perf; docs; logging (window sizes, drag ops); audit: drag/resize = record.updated (mandatory).

---

TASK-0074

Title

Chart view base (bar/line/area/pie/donut/number aggregations, SVG renderers)

Objective

Implement the CHART view type: chart creation (bar, line, area, pie, donut, number) over a database's number properties with group-by + filter + aggregation config, server-side aggregation in the query pipeline, and pure-SVG renderers in packages/database-engine/src/views (no chart library — the spec's renderer location is explicit), powering the CHART view and the chart block (Phase-4 block type, Phase-5 renderer swap).

Background

PRODUCT-SPEC §12: "Charts: bar, line, area, pie, donut, number. Config: x-axis, y-axis, group, filter, aggregation, color, legend." The charts table exists (chartType, config Json — x/y axis property, group by, aggregation, colors, legend; filter Json?; position). frontend-architecture §6 CHART row: "data is server-aggregated (query returns aggregate buckets, not rows)". Aggregations: COUNT/SUM/AVG/MIN/MAX on NUMBER properties (PERCENT/UNIQUE are Phase-6 rollup-domain — the chart base supports the 5); group-by: any property (select/status → category buckets, date → time buckets day/week/month, number/text → value buckets (text: top 10 + "Other"), person → user buckets); the CHART view (TASK-0069 registry) embeds one chart per database view instance (the view's config.chartId) — creating a CHART view creates a default chart (first number property, COUNT, grouped by first select/status if any). The chart block (in a page) renders the same renderer with content.chartId (the Phase-4 chart node's placeholder swaps to the live renderer — the block's chart must belong to the page's workspace, server-checked on render).

Requirements

• R1. packages/database-engine/src/views/chart/ — pure: aggregate(records, config) → ChartData (buckets: {key, label, count, values: {sum, avg, min, max}[], colors[]} for bar/line/area (y = the aggregation over the number property per bucket) / {key, label, value, color}[] for pie/donut (value = aggregation per bucket, must be ≥ 0 — negative values → the bucket renders with a "negative" style note, documented) / single number for the number type (aggregation over all filtered records)); validate(config, properties) (y-axis property must be NUMBER — or COUNT(*) mode with no y property; x/group property any type; aggregation enum; time bucket granularity for date groups).
• R2. API: POST /v1/databases/:id/charts — {name, chartType: 'bar'|'line'|'area'|'pie'|'donut'|'number', config: {yPropertyId? (null = count), aggregation: 'count'|'sum'|'avg'|'min'|'max', groupPropertyId? (null = single series/total), timeGranularity? (day|week|month, only for date groups), color? (accent override), legend: bool, xLabel?, yLabel?}, filter? (a FilterTree — the chart's own filter, independent of the view) → creates the Chart row + returns it; PATCH /v1/databases/:id/charts/:cid (config/filter/name; re-validates); DELETE (soft: the chart row deleted; blocks referencing it render an "chart deleted" state); POST /v1/databases/:id/query gains chartId? → response {chart: ChartData, meta: {generatedAt, count}} (server aggregation over the filtered set — bounded like the residual bound (5000) for aggregation correctness, documented; pie/donut on > 50 buckets → top 50 + "Other" merge).
• R3. SVG renderers packages/database-engine/src/views/chart/render/ (pure functions renderBar(data, size) → svgMarkupString, renderLine, renderArea, renderPie, renderDonut, renderNumber — deterministic (no randomness; colors from a fixed 10-color token palette by bucket index; labels truncated with …; axes with 4–6 ticks; legend as a <g>; tooltips via <title> elements (native SVG tooltips — Phase 7 upgrades to DOM tooltips); no external chart dependency (AGENTS.md rule 17 — the SVG renderers are ~400 LOC each, deterministic, unit-testable, zero bundle cost):
  • renderBar: vertical bars (or horizontal when orientation: 'horizontal' — config), value labels above bars when ≤ 12 buckets, axis labels rotated 30° when crowded;
  • renderLine/renderArea: points + polyline (area = closed path + 20 % opacity fill), gridlines at ticks, a single series (multi-series = group-by-2 is out of Phase-5 scope — documented, one series per chart);
  • renderPie/renderDonut: arcs (donut = inner radius 60 %), slice labels outside for ≥ 8 % slices, total in the donut hole;
  • renderNumber: big number (tabular-nums) + label + delta? (no delta — Phase-6+).
• R4. UI: apps/web/database/views/ChartView.tsx — header (chart name inline-editable + type switcher icons (6 types — switching type re-renders, config carries over where valid, e.g. group stays) + ⚙ config panel (drawer: y property picker (number properties + "Count of entries"), aggregation picker, group picker (all properties, time-granularity sub-picker when a date is chosen), filter — the TASK-0067 builder in variant="inline", color, legend toggle, labels)); the chart renders in an SVG container (responsive, aspect 16/7, role=img + aria-label describing the data: "Bar chart: entries by status, 4 buckets, total 1,204"); the CHART view type (TASK-0069) mounts this with config.chartId; "Add chart" in the view settings when a CHART view has no chart yet.
• R5. chart block renderer swap: the Phase-4 chart block node (placeholder) renders the same ChartView (read-only mode: no config drawer, just the SVG + name) for content.chartId; the block's workspace check: the chart's database must be in the page's workspace (server: the query endpoint already tenant-scopes — a cross-workspace chartId → 404).

Acceptance Criteria

• [ ] Unit: aggregate golden fixtures per type (bar by select option order, line over month buckets with a DST month, pie with 51 buckets → 50 + Other, number = total; COUNT/SUM/AVG/MIN/MAX exact values; empty dataset → the renderer's empty state, no NaN in the SVG); SVG output determinism (same input twice = byte-identical markup, except the generatedAt meta which is outside the SVG).
• [ ] Integration: POST /databases/:id/query {chartId} on the 100k-number seed — SUM/AVG/MIN/MAX/COUNT match SQL truth within 0.01 % (AVG); a chart with a filter (number > 100) aggregates only the filtered set (truth-check); invalid config (y = a TEXT property) → 400 with the field.
• [ ] E2E: create a bar chart (group by Status, y = SUM of Budget) via the CHART view → 4 bars in option order with values; switch to donut → donut with the same data (labels for slices ≥ 8 %); apply the inline filter (Budget > 1000) → chart updates (one query call with the filter); reload → chart + config persisted (charts row + view config.chartId).
• [ ] E2E (chart block): on a page, a chart block (created via slash? — the chart block is inserted via the database home's "Insert as block" button + the slash chart command now that charts exist — the slash /chart opens a chart picker over the workspace's charts) renders the live chart in the page; updating the chart config in its database updates the block's render on refetch (staleTime 30 s — the test forces a refetch).
• [ ] A11y: the SVG has role=img + a descriptive aria-label (asserted: contains type, group label, bucket count, total); prefers-reduced-motion: no entry animation (the renderers have none anyway — documented: charts are static SVG, transitions only on data change = instant).
• [ ] Perf: chart query on the 100k seed p95 < 300 ms (same budget as the table query, CI artifact); 50-bucket donut renders < 50 ms (DOM measure).

Backend Changes

• apps/api/src/modules/databases/charts/* (CRUD + the query endpoint's chartId aggregation path, reusing the filter push-down + the 5000 bound); packages/shared ChartConfigSchema + ChartDataSchema.

Frontend Changes

• packages/database-engine/src/views/chart/{aggregate,render/*}.ts; apps/web/database/views/{ChartView,ChartConfigPanel}.tsx; packages/editor/src/nodes/chart.ts renderer swap (placeholder → read-only ChartView); /chart slash command enabled (TASK-0054's disabled placeholder flips enabled: true with the picker).

Database Changes

• None (charts table exists).

API Changes

• New: chart CRUD (4 endpoints) + query {chartId} response shape (documented in docs/api/databases.md).

Tests

• Unit: aggregate golden suite, validation (per-type config matrix), SVG renderers (determinism, empty state, label truncation, color palette cycling, negative-value donut note, tick count 4–6).
• Integration: SQL truth-checks, bound behavior (5,001 matching records → aggregated over the bound with meta.bounded: true — the chart shows a "showing top 5,000" note), 400 invalid config, cross-workspace chartId 404.
• E2E: the five scenarios (TASK-0078 suite).

Security

• Chart CRUD = EDIT on the database/page; chart reads = VIEW (a VIEW user can see charts of a db they can view — aggregation over their visible set only: the query applies the caller's permission scope before aggregating, so a partial-visibility db aggregates only visible records — asserted in an integration test with per-record NONE grants); config JSONB validated (no arbitrary code — the renderers are fixed SVG emitters; a crafted config cannot emit script tags — the SVG builder escapes all text nodes, unit-tested with hostile labels); SVG inlined in the DOM (not srcdoc/object — no sandbox escape surface).

Documentation

• docs/api/databases.md charts section; packages/database-engine/README.md chart contract (the Phase-12 AI "generate chart" tool emits exactly this config shape).

Dependencies

• TASK-0064 (codecs/display), TASK-0066 (query), TASK-0067 (filters — the chart's own filter), TASK-0069 (view type + chartId config), TASK-0054 (slash /chart flip), Phase-4 chart block node.

Definition of Done

• Backend implemented (chart CRUD + aggregation query); frontend implemented (SVG renderers in the engine package, ChartView, block swap); no migration; API validation (config matrix, field-level 400s); authorization (permission-scoped aggregation — integration-proven); error states (empty data → "No data for this filter" state, invalid config 400, deleted chart in a block → "Chart was deleted" state); loading states (chart skeleton while the query runs); empty states; mobile (chart 16/10 aspect on < 768 px, config drawer full-screen); accessibility (role=img + data-describing label, static SVG, no motion); unit + integration + E2E + perf; docs; logging (aggregation bound hits); audit: none (charts are not security-sensitive; database.updated on chart create/delete for consumers).

---

TASK-0075

Title

Dashboard view base (widget grid: embed views/charts/text/number widgets, resize + reorder)

Objective

Implement the DASHBOARD view type: a widget grid composing widgets — table, board, chart, number, calendar, timeline, text, linked-database — from DashboardWidget rows with sourceRef, dnd-kit grid resize + reorder, per-widget filter override, and the DASHBOARD view mounting the grid (view config.dashboardId).

Background

PRODUCT-SPEC §12: "Dashboards compose widgets: table, board, chart, number, calendar, timeline, text, linked database — resizable, draggable, configurable, filterable." The dashboards + dashboard_widgets tables exist (widgetType, sourceRef Json? {type: "database"|"chart"|"view", id}, layout Json — x/y/w/h grid units + position, filter Json?, position). frontend-architecture §6 DASHBOARD row: "widget grid from DashboardWidget.layout; each widget embeds a table/board/chart/number/calendar/ timeline/text/linked-database source; drag/resize with dnd-kit (grid mode); each embedded widget is virtualized internally." dashboard-architecture §1.5: "Widget data is fetched by the client through the normal query endpoints… the server never renders widget pixels." Scope note: Dashboard is workspace-level (workspaceId); a DASHBOARD view on a database references a dashboard via config.dashboardId — creating a DASHBOARD view creates a dashboard bound to that database's workspace (name = "Dashboard of <db>"). The FORM widget type in dashboard_widgets? — the enum comment lists 8 types (table|board|chart|number|calendar|timeline|text| linked_database) — no form widget (forms are Phase 10; a form widget lands there).

Requirements

• R1. API (apps/api/src/modules/databases/dashboards.ts + DashboardService — the dashboard entities are workspace-scoped, owned by DatabaseModule for Phase 5 since the DASHBOARD view creates them; the module boundary note is recorded):
  • POST /v1/workspaces/:wid/dashboards — {name, databaseId? (for the view link)} → dashboard + default layout (one text widget "Welcome" 6×2 + one number widget "Total entries" 3×2 + one table widget of the linked db's first view 9×4); PATCH /v1/workspaces/:wid/dashboards/:id (name); DELETE (cascades widgets);
  • POST /v1/dashboards/:id/widgets — {widgetType: 8 enum values, sourceRef? (validated: view → same-workspace view of the dashboard's linked db or any db the caller can VIEW; chart → same-workspace chart; number/text → no sourceRef), layout {x,y,w,h} (grid 12 cols, h in units of 40 px, w/h caps 12×8), filter? (FilterTree, only for data widgets)} → places at the given cell (occupied cells → 409 layout_collision with the colliding widget id — the UI auto-finds a free cell before calling, the server is the backstop);
  • PATCH /v1/dashboards/:id/widgets/:wid — layout (move/resize — same collision rule), sourceRef change (same validation), filter, position;
  • DELETE /v1/dashboards/:id/widgets/:wid; GET /v1/dashboards/:id (dashboard + widgets ordered by position).
• R2. apps/web/database/views/DashboardView.tsx + apps/web/dashboard/ (the dashboard is reachable both as a database view and, in Phase-5 scope, only from the view — no standalone dashboard route yet; the route is Phase-12/14): a 12-column CSS grid (gap 16 px, row 40 px), dnd-kit grid mode (widgets draggable by their header grip, resizable via corner handle; layout = x/y/w/h in grid units; drop/resize → one PATCH per changed widget; collision = dnd-kit's grid collision detection prevents overlapping drops client-side; on drag the widget's data view pauses (a static placeholder card with the widget name — the heavy renderers don't re-run mid-drag; resume on drop); "Add widget" button (grid's end) → picker (8 types with descriptions + a source picker when required: view picker lists the linked db's views + "All entries" table default; chart picker lists workspace charts).
• R3. Widget renderers (apps/web/dashboard/widgets/): each widget is a framed card (header: grip + title (inline-editable) + source label + ⋯ menu (edit source, filter [data widgets only — the TASK-0067 builder variant="inline"], remove)) + body:
  • table/board/calendar/timeline → the corresponding Phase-5 view renderer in embedded mode (props: embedded: true — no view tabs, no toolbar, density forced compact, filter = the widget's filter merged over the source view's, read-only interactions allowed (row open works, drags disabled in board/timeline to avoid double-edit confusion — documented));
  • chart → the TASK-0074 SVG renderer (read-only, no config drawer);
  • number → a KPI card: big number (config: which aggregation — count of the source view's records, or SUM/AVG of a number property — the widget's layout JSON carries {aggregation, propertyId} for number widgets, validated in WidgetLayoutSchema), sublabel (property label), delta? none;
  • text → a rich-text widget (contenteditable, stored in layout.text markdown, rendered via the TASK-0059 markdown renderer — no upload, text only; a simple toolbar: bold/italic/link/list);
  • linked_database → a mini table of another database (sourceRef = a view) with a "Open <db>" footer link.
• R4. Data fetching: each data widget fires its own query (the normal endpoints — query for table/board/calendar/timeline/number, chart query for charts) with the widget's filter override; widgets are independent (one failing widget shows its own error state + retry, the grid stays usable); staleTime 30 s per widget; the grid never fetches more than the visible widgets (IntersectionObserver activation — the dashboard is the only view where lazy widget hydration is required by frontend-architecture §8 "only visible widgets hydrated").

Acceptance Criteria

• [ ] E2E: create a DASHBOARD view on a db → a dashboard is created (API: dashboards row + 3 default widgets); the view renders the 12-column grid with the 3 default widgets (text/number/table) at their documented cells.
• [ ] E2E: add a chart widget (pick a real chart) + a calendar widget → they render live data (chart SVG matches its standalone view; calendar shows the current month's records of the source view).
• [ ] E2E: resize the table widget 9×4 → 6×6 (corner drag) → one PATCH with the new layout; the calendar widget reflows (CSS grid) without a re-query (network assert: no new queries on pure layout change); drop onto an occupied cell → rejected (client-side) and the server collision 409 is asserted via direct API.
• [ ] E2E: set a filter on the number widget (Status = Done) → the number shows only the filtered count (truth-checked against a manual query with the same filter); removing the filter restores the total.
• [ ] E2E: widget failure isolation — force one widget's query to 500 (test API fault injection) → that widget shows the error state + Retry; the other 5 widgets render normally; Retry recovers it.
• [ ] E2E: text widget — type markdown "Bold and a link" → renders bold + link; saved in layout.text (API assert); the widget is contenteditable only for EDIT users (VIEW users see rendered text, not editable — DOM assert).
• [ ] Perf: a dashboard with 12 data widgets → only the visible ones (≤ 4) fire queries on load (network assert); scrolling to the bottom hydrates the rest; total dashboard first paint < 2 s.

Backend Changes

• apps/api/src/modules/databases/dashboards/* (7 endpoints; DashboardService with cross-ref validation: view/chart sources same-workspace + caller VIEW); packages/shared WidgetSchema (type enum, sourceRef discriminated, layout incl. number-widget fields, collision rule).

Frontend Changes

• apps/web/dashboard/{DashboardGrid,AddWidgetPicker,widgets/{TableWidget,BoardWidget,ChartWidget,NumberWidget,CalendarWidget,TimelineWidget,TextWidget,LinkedDatabaseWidget,WidgetCard}}.tsx; apps/web/database/views/DashboardView.tsx (mounts DashboardGrid with config.dashboardId); DASHBOARD view creation in the view picker now creates the backing dashboard (TASK-0069 flow extended).

Database Changes

• None (dashboards/dashboard_widgets tables exist).

API Changes

• New: dashboard + widget CRUD (7 endpoints, documented in docs/api/databases.md).

Tests

• Unit: collision rule (free-cell finder + the 409 predicate), WidgetLayoutSchema (number-widget fields only on number type, caps), sourceRef validation matrix (view/chart/text/number per type), embedded-mode prop resolution (widget filter ∘ source view filter).
• Integration: full widget lifecycle (create at occupied cell → 409; move/resize; source change re-validation; cross-workspace source 404/400), widget filter merging against a real view (truth-checked count).
• E2E: the six scenarios (TASK-0078 suite) — "all views open a record's page" asserted for the dashboard via the table widget's row click.

Security

• Widget data = the normal query endpoints with the caller's permission scope (a widget on a db the caller can only VIEW renders VIEW data; a widget source the caller can't see → the widget shows "No access" state, not the data — the source validation at create time plus per-fetch scope at read time); text widget content is markdown (rendered escaped — no raw HTML; the TASK-0059 markdown parser with its URL allowlist); layout JSON validated (negative/overflow coordinates → 400); no widget can escape its source's permissions (the server never "un-hides" data for a dashboard context).

Documentation

• docs/api/databases.md dashboards section; apps/web/dashboard/README.md widget contract (embedded-mode props, the 8 widget types, activation rule).

Dependencies

• TASK-0069 (DASHBOARD view type + dashboardId config), TASK-0066/0070/0071/0073 (embedded view renderers), TASK-0074 (chart renderer), TASK-0067 (widget filters), TASK-0059 (text widget markdown), dnd-kit grid mode (Phase-4 dependency, already installed).

Definition of Done

• Backend implemented; frontend implemented; no migration; API validation (layout/sourceRef/collision, field-level 400s); authorization (source validation at create + per-fetch scope at read — integration matrix); error states (widget error isolation with retry, no-access widget state, layout collision 409); loading states (per-widget skeletons, grid shimmer); empty states (dashboard with only the text widget → "Add your first widget" ghost tile); mobile (grid collapses to 4 columns, widgets full-width stacked, resize disabled on < 768 px — layout via the ⋯ menu size options 4/8/12); accessibility (widgets role=region + aria-label "<type> widget: <title>", grip role=button "Move widget", resize handle role=slider-equivalent with keyboard arrows in grid units (1 unit per press), embedded renderers keep their own a11y contracts); unit + integration + E2E + perf; docs; logging (widget query durations, collision 409s); audit: none (dashboard content is not security-sensitive; the underlying data ops carry their own audit).

---

TASK-0076

Title

Database AI hooks (documented stubs for Phase 12)

Objective

Reserve the database AI surface: POST /v1/databases/:id/ai/generate (generate a database from a natural-language description) and POST /v1/databases/:id/ai/formula (generate a formula expression) as documented, permission-checked stubs returning 501 NOT_IMPLEMENTED with a stable envelope, plus the UI affordances (disabled with "Phase 12" hints) so Phase 12 only swaps the handler implementations and flips the feature flag — zero UI rework.

Background

PRODUCT-SPEC §21: "Database AI (generate database, extract data, generate formula)"; backend-architecture §8 reserves NOT_IMPLEMENTED (501) for "endpoint not available on this plan/phase". Phase 12 owns the AI gateway + structured tool calls (its task file phase-12-ai.md implements the real handlers against these exact routes and envelope shapes — the contract is fixed here). Scope is deliberately minimal: the routes, the DTO schemas, the permission checks, the flag, and the UI hints. No worker, no provider, no token budget in Phase 5 (those are Phase 12/15).

Requirements

• R1. API (apps/api/src/modules/databases/ai.ts — DatabaseAiController):
  • POST /v1/databases/:id/ai/generate — body {description: string (≤ 2000), parentPageId? (where the generated database block lands)}; permission: EDIT on the database (or the page for parentPageId); response contract (Phase 12 implements): 201 {databaseId, blockId?, propertyCount, recordCount?} — Phase 5 returns 501 {error: {code: "NOT_IMPLEMENTED", message: "Database AI generation is available in Phase 12.", details: {phase: 12, capability: "database.generate"}}}.
  • POST /v1/databases/:id/ai/formula — body {prompt: string (≤ 2000), context?: {sampleRecordIds: string[] (≤ 5)}}; permission: EDIT; Phase 12 contract: 200 {expression, ast?, returnType, confidence} — Phase 5: the same 501 shape with capability: "formula.generate".
  • Rate limit: 10/min/user on both routes (the Phase-12 rl:ai budget arrives with it; a basic per-user counter now prevents hammering the stubs).
• R2. Feature flag database.ai (env-driven via apps/api/src/config, default off): when off, the routes 404 (not 501 — the flag gates existence; when on but unimplemented, 501). Phase 12 flips the implementation under the same flag. The flag is read in one place (AiCapabilityGuard) — documented so Phase 12 finds it.
• R3. UI affordances (all disabled, all consistent): the database header "Ask AI to generate" button (tooltip "Available in Phase 12"); the property picker's "Generate formula from description" row in the formula property config (TASK-0064's Phase-6 banner area — the stub hint shares it); the slash menu /database picker's "Describe your database…" field (disabled input with the hint). All three call the stub endpoint on click for the 501 → toast "Database AI arrives in Phase 12" (so the wiring is tested, not just decorative).
• R4. Event/audit: none emitted from the stubs (no action taken); the 501 is logged (ai.stub.called {capability, databaseId}) for Phase-12 demand estimation.

Acceptance Criteria

• [ ] Integration: POST /v1/databases/:id/ai/generate with the flag off → 404; flag on → 501 with the exact envelope (code, message, details.phase === 12, details.capability === "database.generate"); same for /ai/formula.
• [ ] Integration: permission matrix — a VIEW user gets 403 (not 504 — permission checked before the capability), a non-member gets 404; the 10/min rate limit returns 429 on the 11th call (counter test with fake timers).
• [ ] E2E: the three disabled UI affordances render with the "Phase 12" hint; clicking one fires the request and shows the 501 toast (network + DOM assertions).
• [ ] Contract stability: docs/api/databases.md documents both endpoints with the Phase-12 response shapes marked "(Phase 12)" — a diff of the route table between Phase 5 and Phase 12 task files is empty (Phase 12's file references this contract verbatim).

Backend Changes

• apps/api/src/modules/databases/ai.ts (controller + guard + flag read); packages/shared/src/dtos/database-ai.ts (the two request schemas + the 501 envelope type); apps/api/src/config flag entry.

Frontend Changes

• apps/web/database/ three affordance components (disabled state + hint + 501 toast handler in apps/web/hooks/useDatabaseAi.ts).

Database Changes

• None.

API Changes

• New (stub): the two routes per R1; documented with their Phase-12 contracts.

Tests

• Integration: the flag/permission/rate-limit matrix (R1/R2 acceptance), envelope shape snapshot.
• E2E: the affordance scenario.

Security

• Permission checked before the capability gate (a stub must not leak "this endpoint exists for your db" to a non-member — 404 for them); the 501 body never contains internal detail; rate-limited; no secrets in the toast/log; the flag is env-only (no per-workspace toggle in Phase 5 — Phase-12/14 plans own gating).

Documentation

• docs/api/databases.md AI section (marked Phase-12 contract); note in docs/tasks/phase-12-ai.md's scope that these routes are pre-reserved (cross-file reference, no code).

Dependencies

• TASK-0063 (databases shell for the routes), apps/api/src/config (flag infrastructure from Phase 0), Phase-12 (implements the contract — out of this phase).

Definition of Done

• Backend implemented (stubs + guard + flag); frontend implemented (3 affordances); no migration; API validation (prompt length, sampleRecordIds count — 400s work even on a 501 route); authorization (matrix tested); error states (404/403/429/501 all exercised); loading states (the 501 request shows a brief "Asking AI…" state before the toast); empty states (N/A); mobile (affordances visible, same disabled state); accessibility (disabled buttons keep their aria-label + aria-disabled, the hint is in the accessible name, not only a tooltip); unit + integration + E2E; docs; logging (ai.stub.called); audit: none (no state change — documented).

---

TASK-0077

Title

Search-ready records (indexable body built on record save, event for the indexer)

Objective

Make every record's searchable content available to the Phase-8 search pipeline: on record create/update, build a search body (title + property values concatenated in a stable order) and emit it in the record.created/record.updated events so the search-index consumer group (Phase 8) can index search_documents rows of type record without re-deriving text from EAV. The Phase-5 slice: the event payload + the body builder (pure, in packages/database-engine); the actual OpenSearch indexing is Phase 8.

Background

backend-architecture §7.3: the search-index consumer group upserts "search_documents mirror + OpenSearch doc (incl. permission stamp)" from page.created/updated, database.created, record.created/updated-class events. database-architecture §2.3: "the title is also denormalized into the search index." Phase 8 owns the indexer itself; Phase 5 owns making record events indexer-ready: a deterministic searchBody (title + "<Property>: <value>" lines for every non-empty value, in property position order, values rendered via the codec display() in en-US — the indexer re-localizes snippets in Phase 8), capped at 64 KB, with the property names included (so "Budget: 42000" matches "Budget" queries). Computed properties are excluded in Phase 5 (they don't exist yet); Phase 6 extends the builder to include formula/rollup display values.

Requirements

• R1. packages/database-engine/src/search-body.ts: buildRecordSearchBody(record, values, properties) → string — pure, deterministic (property position order, then property id as tiebreak), per-type display() (dates as ISO + locale-long form both? — decision: ISO only, the indexer adds localized variants in Phase 8; documented), select/status → option names (values are option ids — the body must contain human text, so the codec display with config), person → member names (the builder receives a resolveUserIds: (ids) => string[] callback — the API side resolves via the users repository; the package stays pure), title first line, then one line per property, 64 KB cap (truncate with …, cap is a constant SEARCH_BODY_MAX), empty record (only title) → just the title.
• R2. Event payload extension: record.created/record.updated envelopes gain payload.searchBody: string + payload.title: string (the envelope schema in packages/shared/src/events/ extended additively — version stays 1, the fields are new and optional-for-consumers: older consumers ignore them; the Phase-8 indexer requires them). record.archived/record.deleted-class events (archive + page-trash) gain nothing new (the indexer handles deletion via the page trash event — documented).
• R3. API wiring: RecordService.create/patch builds the body (resolving person names in the same transaction — a batched user lookup, one query for all person values in the batch) and puts it in the emitted envelope; the body is not stored in Postgres (it's derived — re-derivation is cheap; storing it would be a second source of truth — recorded decision against the alternative in database-architecture §2.3's title-denormalization note: the search_documents mirror table stores it, written by the Phase-8 indexer).
• R4. Record page title changes (the title is the page title, TASK-0065): a title-only PATCH also emits record.updated {changedProperties: [titlePid], searchBody, title} — the body must reflect the new title.

Acceptance Criteria

• [ ] Unit: buildRecordSearchBody golden fixtures — a record with 10 properties of 8 types (option ids → names, person ids → names via the callback, dates ISO, numbers as-is, rich text stripped to plain text, checkbox "true"/"false", empty values skipped, missing callback users → "(unknown)" and logged); determinism (same input twice = identical string); 64 KB cap (a 200 KB rich-text value truncates at the cap, no mid-codepoint cut).
• [ ] Integration: create a record with title + 5 values → the record.created envelope (captured from the Redis stream in the test) has payload.searchBody containing the title and every property name + display value, in property order; a title-only PATCH → record.updated with the updated title in the body.
• [ ] Integration: a record in workspace A never appears in workspace B's event stream (the stream is global — the envelope carries workspaceId; the test asserts the field, Phase 8 enforces scoping).
• [ ] Backward compatibility: an envelope consumer built against the Phase-4 schema (the blocks consumer shape) ignores the new fields without error (schema parse test with a strict-optional consumer fixture).
• [ ] Person-name resolution: a record with 3 person values (2 resolvable, 1 deleted user) → body has 2 names + "(unknown)" (no crash, no id leaked).

Backend Changes

• apps/api/src/modules/databases/records — body building in create/patch (person resolution via the existing users repository interface), envelope emission; packages/shared/src/events/ — the record event payload schema extension.

Frontend Changes

• None (search UI is Phase 8).

Database Changes

• None (search_documents table exists for Phase 8; nothing stored in Phase 5).

API Changes

• None (event-payload-only change; the shared event contract is documented in docs/api/databases.md → a new "Events" section listing the record events + searchBody).

Tests

• Unit: the golden fixtures above + fuzz (random value sets → the body always contains the title, ≤ cap, property names present when values non-empty).
• Integration: the stream-capture tests (envelope fields present, ordering, backward-compat parse).

Security

• The body contains only data the record's viewers can see — the Phase-8 indexer stamps permissions at index time (backend-architecture §7.3); Phase 5's job is not to leak extra data: the body never includes raw user emails (display names only), never file storage keys (file property → file names via the files repository, resolved like users), never option ids (names only); a record with a NONE-grant override still has a body built (permission stamping is the indexer's job — but the event carries the record's permission-relevant pageId so the indexer can evaluate; documented).

Documentation

• docs/api/databases.md Events section (record events + searchBody contract — the Phase-8 task file references it); the title-denormalization decision note (database-architecture §2.3 cross-ref).

Dependencies

• TASK-0064 (codecs display), TASK-0065 (record service + events), packages/shared event envelope, Phase 8 (consumes — out of this phase).

Definition of Done

• Backend implemented (body builder + event wiring); frontend: None (genuinely N/A — no UI in this task); no migration; API validation (event schema shared Zod — consumers validate); authorization (N/A — events are internal, permission scoping is Phase 8's stamped-snapshot job, the data-leak rules above are unit-tested); error states (user/file resolution failure → "(unknown)" + warn log, never a failed event emission); loading/empty states (N/A); mobile (N/A); accessibility (N/A); unit + integration; E2E: None (N/A — no user-visible behavior; the integration stream-capture tests are the E2E-equivalent for the event path, recorded here); docs; logging (body build ms, truncation events, unknown-resolution counts); audit: none (events ride the existing record.created/updated audit — no new audit event).

---

TASK-0078

Title

E2E database suite (tests/e2e/databases.spec.ts) — core scenarios

Objective

Author the Playwright E2E suite for Phase 5 covering the behavioral exit criteria: create via slash, 5 property types, add rows, filter+sort+group, board drag updates property, record opens page, 1000-row scroll virtualization, all 8 views open a record's page, archive/trash lifecycle, and the permission matrix. The 100k seed, perf probes, and CI engine gate land in TASK-0079; final phase-exit evidence lands in TASK-0080.

Background

AGENTS.md CI contract (install → lint → typecheck → unit → integration → build → security scan → E2E); Playwright in tests/e2e; the Phase-3/4 suites (tests/e2e/pages.spec.ts, tests/e2e/editor.spec.ts — Phase 4's TASK-0062) set the fixture/CI patterns to reuse (authed-user fixtures, network spies). Phase-5 exit criteria (ROADMAP): "100k-record database renders with virtualization; filter/sort/ group engine unit-tested; all views open a record's page." This task owns the behavioral scenarios; TASK-0079 owns the 100k proof + the structural unit gate; TASK-0080 assembles the exit evidence.

Requirements

• R1. tests/e2e/databases.spec.ts scenarios (each from an owning task's acceptance criteria, run here for the first time):
  1. create-via-slash: page → /database → "Projects" with default properties → databases row + TITLE property + TABLE view + DATABASE block (API-asserted); the block renders the database home (not the Phase-4 placeholder).
  2. property-types-5: add text, number, select, date, checkbox via the property dialog with configs → GET /databases/:id returns all 5 + TITLE in order with correct config (the exit-criterion "add 5 property types").
  3. add-rows + record-page: create 5 rows (table "New row" × 3 + one with values per type + one via API) → rows show formatted values; click a row → record page (properties panel left + editor right, both interactive — typing in the editor autosaves); the record page's panel edits a value (select dropdown) → table cell updates after refetch.
  4. filter-sort-group: build (Status = In Progress OR Status = Review) AND Owner = Me (the spec example) → filtered rows; add sort (Due date ASC, then Name ASC) → ordered; group by Status → sections with counts; all persisted on the view (database_views.filter/sort/group non-null via API); reload → identical view.
  5. board-drag-updates-property: board view (status group) → drag card To Do → In Progress → one PATCH (network assert) → card in the new column; reload persists (the exit-criterion "board drag updates property").
  6. views-open-record-page (the exit criterion, all 8): for each of TABLE/LIST/BOARD/GALLERY/CALENDAR/TIMELINE/CHART/DASHBOARD — create the view, seed it with ≥ 1 record it can show, open it, click its record affordance (row / card / bar / widget row) → the record page opens (URL + panel assertion). Chart + dashboard count via their record-bearing widgets (the table widget in the dashboard).
  7. 1000-row-virtualization (exit-criterion scale check): a 1,000-row table view → scroll to row 950 → correct data, DOM rows ≤ 120 throughout (the full 100k proof is TASK-0079).
  8. archive-trash-lifecycle: archive 3 rows → hidden from default queries, visible with includeArchived, direct URL still opens + Unarchive restores; delete 2 → pages in trash → restore → back in the db.
  9. permissions: a VIEW member opens the database home (readonly: no "New row", no cell editing, no drag on the board); a non-member → 404 on the db and on ?view= deep links; a record with an explicit NONE grant is 404 by direct URL even though the db is VIEW-able.
• R2. Fixtures: tests/e2e/fixtures/databases.ts — authed users (A ADMIN, B VIEW, C non-member — A is ADMIN so scenario 8 can read the audit API), a standard 5-type database builder, a record-seeding helper (API-level, fast, the 100k generator itself is TASK-0079).
• R3. CI wiring: @phase5 tag; the suite joins the default E2E job (the 100k perf project is added by TASK-0079); flake policy (Phase-4's, TASK-0062): no waitForTimeout, waits via waitForResponse/state asserts.

Acceptance Criteria

• [ ] pnpm test:e2e -- --grep @phase5 green from a clean docker-compose state: all 9 scenarios pass.
• [ ] The behavioral exit criteria are pinned to named scenarios: filter+sort+group → scenario 4; board drag updates property → scenario 5; all views open a record's page → scenario 6 (8/8 views asserted); record opens page (standalone) → scenario 3; 1000-row virtualization → scenario 7.
• [ ] The permissions scenario proves server-side enforcement end-to-end (404s, not just hidden buttons — the network layer asserts the API responses).
• [ ] The suite contains zero waitForTimeout, zero test.fixme, zero skipped assertions (ESLint no-playwright-timeouts + a Playwright config that fails on empty test bodies).
• [ ] The suite runs in < 12 minutes (4 workers) on the standard (non-100k) seed.

Backend Changes

• None (test-only task; the suite may surface bugs — fix them in the owning module, never by weakening a scenario).

Frontend Changes

• Test-only: tests/e2e/databases.spec.ts, tests/e2e/fixtures/databases.ts; turbo.json e2e pipeline extension; package.json script test:e2e.

Database Changes

• None (no migration; seeding uses the standard demo database from Phase-3 seed + the fixture builder).

API Changes

• None.

Tests

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

Security

• Scenario 9 (permission matrix incl. the explicit-NONE record grant and the 404-not-403 leakage rule); a CSRF check on record mutations (cookie without factory_csrf → 403, per the backend guard chain).

Documentation

• tests/e2e/README.md extended (database fixtures, how to run a single scenario, flake policy).

Dependencies

• TASK-0063…0077 (all Phase-5 behavior), Phase-0 docker/CI, Phase-3 seed + permission fixtures, Phase-4 E2E patterns (tests/e2e/fixtures/editor.ts style).

Definition of Done

• Backend: None (test-only); frontend: suite + fixtures + CI wiring; no migration; API validation exercised by scenarios 1–4 (400s asserted); authorization scenario 9 green; error/loading/empty states exercised (scenarios 3, 6, 8 include empty views + loading skeletons + the 409/404 error toasts); mobile (scenarios 1 + 6 run in a 375 px project preset: table density compact, board horizontal swipe, record panel stacked); accessibility (the suite includes an axe pass on the table + board + record page — 0 critical/serious, per the Phase-4 gate extended to the database surfaces); unit + integration green from owning tasks; E2E is this task; docs; logging (per-scenario durations in e2e-report.json); audit: scenario 8 asserts record.created/updated/archived audit rows exist (via the audit API as ADMIN user A).

---

TASK-0079

Title

100k-record seed, perf probes, engine unit gate (CI)

Objective

Produce the machine-verifiable proof for the remaining two ROADMAP exit criteria: the deterministic 100k-record seed + the 100k virtualization/perf probe (100k-record database renders with virtualization) and the structural unit gate that the filter/sort/group engine is unit-tested (every operator in the matrix has a golden fixture; the fuzz suites run in CI).

Background

ROADMAP Phase-5 exit: "100k-record database renders with virtualization; filter/sort/group engine unit-tested; all views open a record's page." The third is TASK-0078; the first two are proof artifacts, and this task builds them. database-architecture §4: the 100k-record filtered query target is < 300 ms via (databaseId, archivedAt) + the EAV uniques (expression indexes are the documented MVP+1 follow-up); §6: "Seed (prisma/seed): a demo org + workspace with a 100k-record database and a 100k-block page (used by the performance suite and the Phase 3/5 exit criteria)" — the 100k-block page half landed in Phase 4 (TASK-0062 R2); this task adds the 100k-record half. The engine goldens live in packages/database-engine/src/filter/__tests__/fixtures/ (TASK-0067) and src/sort/__tests__//src/group/__tests__/ (TASK-0068) — this task adds the structural check that no operator/type pair is untested, making "unit-tested" enforceable rather than a convention.

Requirements

• R1. prisma/seed.ts --records N (deterministic generator, id-seeded PRNG — same seed → same data, so CI truth-checks are reproducible): N=100000 → a "Performance DB" with 8 properties (TITLE "Entry <n>", STATUS over 5 options (20 % each), NUMBER uniform 0–100k (10 % null), DATE 24-month span (60 % set), DATE_RANGE 30 % set (1–30 day spans), TEXT 200-char lorem (40 % set), PERSON over 10 seeded members (20 % set, 5 % multi), CHECKBOX 50 %), records with fractional position in order, pages created 1:1 (batch insert in chunks of 1,000, < 90 s total on CI hardware — the seed is a CI dependency, so the budget is enforced by the perf project's setup timeout).
• R2. tests/e2e/perf/100k-database.spec.ts (Playwright, perf project, relaxed 2× budgets, CDP metrics):
  • 100k-render: open the table view of the seeded db → first meaningful paint < 2 s (metadata + first row window), mounted rows ≤ 120 (DOM count over the scroll), total heap < 1.5 GB, scroll at 5,000 px/s for 5 s → frame-drop < 5 % (CDP Performance.getMetrics), query round-trips during scroll ≤ 8 (cursor pagination, no refetch storms);
  • 100k-query: 20 samples of POST /v1/databases/:id/query (pageSize 100, no filter / one select filter / select+number filter) → p95 < 300 ms (the database-architecture §4 budget; CI artifact query-latency.json);
  • 100k-filtered: a filtered page of 100 (status filter) < 300 ms (the §4 "100k-record database, filtered page of 100" row).
• R3. Engine unit gate tests/e2e/checks/assert-engine-goldens.ts (runs in the unit stage, not E2E — it gates everything downstream): walks operatorsFor(type) for all 24 property types (TASK-0064 registry) and asserts each operator has ≥ 1 golden fixture file in packages/database-engine/src/filter/__tests__/fixtures/<type>/<operator>.json; walks the sort comparator list and group bucketing list (TASK-0068) the same way; fails the build with the missing (type, operator) pairs listed. Plus: the fuzz entry points (TASK-0067's push-down invariant fuzz, TASK-0068's determinism fuzz) are pinned to run in CI at a fixed seed (deterministic CI) with the seed rotated weekly in a nightly lane (documented in tests/README.md).
• R4. CI wiring: turbo.json — the unit stage runs the golden-assertion check before the @factory/database-engine suite (order enforced by turbo dependsOn); the perf project exists in playwright.config.ts (reusing the Phase-4 perf project from TASK-0062); the perf lane runs on merge to develop (not per-PR — 100k seeding per PR would blow the CI budget; recorded decision with the time math), per-PR runs the 1,000-row scenario (TASK-0078 scenario 7) instead.

Acceptance Criteria

• [ ] pnpm db:seed -- --records 100000 completes in < 90 s on CI hardware and is deterministic (two runs on two fresh DBs → identical record ids, values, and position sets — asserted by a hash-compare script in the perf project setup).
• [ ] The 100k-render probe passes all four budgets (paint, mounted rows, heap, frame-drop) with artifacts published; the 100k-query p95 < 300 ms artifact is in the CI log; the 100k-filtered probe passes.
• [ ] The golden-assertion check passes on the merged tree, and is demonstrably load-bearing: deleting one fixture file (a throwaway branch in the PR review) fails the build with the missing pair named — the failure mode is shown in the PR description.
• [ ] The fuzz suites run at a fixed seed in CI (deterministic) and the nightly lane rotates the seed (config visible in tests/README.md).
• [ ] The perf lane's full setup + run is < 20 minutes (seeding 90 s + probe suite) — the budget is enforced by the turbo task timeout.

Backend Changes

• Test/seed-only: prisma/seed.ts generator + the hash-compare setup script; no production code changes (if the 300 ms budget fails, the fix is a query/index change in apps/api — an owning-module fix, tracked as a follow-up issue if it can't land in Phase 5, with the perf gate kept red until it does — recorded in the phase notes).

Frontend Changes

• Test-only: tests/e2e/perf/100k-database.spec.ts, tests/e2e/checks/assert-engine-goldens.ts, playwright.config.ts perf project, turbo.json, tests/README.md fuzz-lane doc.

Database Changes

• Seed-only (no migration).

API Changes

• None.

Tests

• This task is the seed generator + perf probes + unit gate; it also includes the two "load-bearing" demonstrations (determinism hash-compare, golden-check failure mode).

Security

• The seed uses the demo workspace (no cross-tenant data); the perf probes run as the seeded ADMIN user; no secrets in artifacts (latency JSONs contain no record data — ids only where needed, and the demo workspace is disposable per CI run).

Documentation

• tests/README.md (seed flags, perf lane cadence, fuzz seeds, artifact locations); docs/architecture/database-architecture.md §6 seed note updated to reflect the landed --records flag.

Dependencies

• TASK-0078 (suite + fixtures the perf project extends), TASK-0064/0067/0068 (the registries + goldens the gate walks), Phase-3 seed infrastructure, Phase-4 perf project pattern.

Definition of Done

• Backend: seed generator (test-only); frontend: probes + gate + CI wiring; no migration; API validation (N/A — test-only); authorization (probes use the seeded ADMIN, the permission matrix is TASK-0078's job); error/loading/empty states (N/A — test-only); mobile (N/A); accessibility (N/A); unit (the gate is a unit-stage check) + integration (N/A) + perf (is this task); docs; logging (probe artifacts query-latency.json, 100k-render.json with per-budget values); audit: N/A (seed data is demo).

---

TASK-0080

Title

Phase-5 exit verification (checklist evidence, conflict log)

Objective

Close Phase 5: run the full contract one final time, record the exit-criteria evidence (run hashes + artifact links) against the Phase Completion Checklist at the top of this file, verify the recorded notes (conflicts/adjustments) are resolved or carried forward, and mark the ROADMAP Phase-5 boxes with the evidence — the same close-out discipline as Phase 4's TASK-0062.

Background

ROADMAP: "A phase is done when every task in its file meets the Definition of Done in AGENTS.md." Phase 5's exit criteria are: 100k-record database renders with virtualization (TASK-0079), filter/sort/group engine unit-tested (TASK-0079 gate + engine suite), all views open a record's page (TASK-0078 scenario 6). The phase also carried three recorded notes (header): the 22-vs-24 property-count reconciliation, the FORM view deferral to Phase 10, and the Phase-6 placeholder behavior for RELATION/ROLLUP/FORMULA/BUTTON — each must be verified as implemented-as-recorded before the phase closes (AGENTS.md rule 29: conflicts are recorded, not guessed).

Requirements

• R1. Final contract run: pnpm install && pnpm lint && pnpm typecheck && pnpm test && pnpm test:integration && pnpm build && pnpm security:scan && pnpm test:e2e (full AGENTS.md CI contract) from a clean docker-compose state, 3 consecutive runs, run hashes recorded; the perf lane (TASK-0079) green on the merge-to-develop run.
• R2. Checklist evidence table (appended to the Phase Completion Checklist at the top of this file): each box → the scenario/test/artifact that proves it: 100k virtualization → tests/e2e/perf/100k-database.spec.ts + 100k-render.json (run hash); engine unit-tested → assert-engine-goldens.ts + @factory/database-engine suite summary (run hash); all views open record pages → databases.spec.ts scenario 6 (run hash); plus the per-task DoD line (18/18) with each task's commit range.
• R3. Recorded-notes verification (the three header notes): (a) property count — the UI type picker shows exactly the 22 enum types (E2E DOM count), the create API rejects a 23rd (400, integration); (b) FORM view — present in the view picker, disabled with the Phase-10 hint (E2E DOM), the ViewType.FORM enum value is accepted by POST /views only as a 501/409? — decision (recorded): creating a FORM view in Phase 5 returns 409 form_view_phase_10 (the type exists in the enum for Phase-10 schema stability; the UI hides the capability via the disabled picker row); (c) Phase-6 placeholders — all four computed/button property types: creatable (schema), cells render the placeholder state (E2E), value writes 422 computed_property_readonly (integration), and the query engine never attempts to compute them (the query response contains no values for them — asserted).
• R4. Handoff to Phase 6: a "Phase-5 → Phase-6 handoff" section (appended to this file) listing exactly what Phase 6's TASK-0081+ consumes: the query endpoint + RecordQuerySchema, the codec registry (packages/database-engine/src/values), the cell renderer contract (apps/web/database/cells/*), the record event shapes (incl. TASK-0077's searchBody), the dbmeta cache, and the view-settings contract — with file paths. Any Phase-5 API shape Phase 6 must extend (e.g. POST /databases/:id/query gains relation-count eager loading per frontend-architecture §6) is listed as a named extension point, so Phase 6 doesn't rediscover it.
• R5. ROADMAP update: tick the Phase-5 boxes in ROADMAP.md (checkboxes) with a one-line evidence reference each (run hash / artifact), per the file's own convention (Phase 0's box is marked with evidence style "✅ scaffold created").

Acceptance Criteria

• [ ] 3 consecutive full-contract CI runs green; run hashes + the perf-lane merge run recorded in the checklist evidence table (R2).
• [ ] The Phase Completion Checklist at the top of this file is fully ticked, each tick backed by a named artifact/run hash (no "verified manually" ticks).
• [ ] The three recorded notes are each verified per R3 with the (b) 409 decision implemented and tested (integration: POST /views {type: "form"} → 409 form_view_phase_10; E2E: picker row disabled).
• [ ] The handoff section (R4) lists ≥ 8 named consumption points with file paths, reviewed against the actual Phase-6 task draft ranges (TASK-0081…0090) — no Phase-6 task needs to "find" an extension point that isn't named.
• [ ] ROADMAP.md Phase-5 section: all 7 checkboxes ticked with evidence references; the file's diff is reviewable (checkboxes + references only, no prose rewrites).
• [ ] No open test.fixme, no known-flaky Phase-5 test without a tracked issue (the flake policy from TASK-0062/0078 holds at close).

Backend Changes

• The one production decision from R3(b): POST /v1/databases/:id/views rejects type: "form" with 409 form_view_phase_10 (a small guard in the views service + the error code in packages/shared/src/errors.ts — additive to the frozen code table, permitted: adding codes is fine per backend-architecture §8 rule 1).

Frontend Changes

• None (the disabled FORM picker row already exists from TASK-0069 — this task verifies it, no change).

Database Changes

• None.

API Changes

• Additive error code form_view_phase_10 (409) documented in docs/api/databases.md.

Tests

• Integration: the FORM-view 409; the Phase-6-placeholder assertions (R3(c)) as a dedicated phase5-handoff.test.ts (the assertions Phase 6 will invert: placeholder → real behavior — keeping them in one file makes the Phase-6 diff obvious).
• E2E: the R3(a) type-picker count; the R3(b) picker-disabled DOM assertion (both small scenarios added to databases.spec.ts as @handoff-tagged).

Security

• The 409 FORM guard is checked after the EDIT permission check (a VIEW user probing the endpoint gets 403, not 409 — no capability leakage; asserted in the integration test).

Documentation

• The checklist evidence table, the handoff section, the ROADMAP ticks, docs/api/databases.md error-code addition; docs/tasks/phase-06-relations.md cross-references this handoff section (its "Background" cites it — written by the Phase-6 author against R4's content).

Dependencies

• TASK-0063…0079 (the whole phase green), Phase-4's close-out pattern (TASK-0062), the Phase-6 draft (for the R4 review — the Phase-6 task file is written in parallel and reviewed against R4 before this task closes).

Definition of Done

• Backend: the FORM-view 409 guard (the only production change); frontend: None (verification only); no migration; API validation (the 409 path tested); authorization (the 403-before-409 ordering tested); error states (the 409 itself is an error state, user-facing message "Form views arrive in Phase 10"); loading/empty states (N/A); mobile (N/A); accessibility (N/A); unit (N/A — the guard is integration-tested) + integration + E2E (the @handoff scenarios); docs (evidence table, handoff, ROADMAP ticks); logging (N/A); audit: N/A (no state change).