Functional Requirements — AI Software Factory
Exhaustive per-domain functional requirements. Every requirement carries the canonical ID from product-requirements.md (this file does not mint new IDs — it expands them). Section references
PS§ncitePRODUCT-SPEC.md; schema references citedatabase-architecture.md; visual/interaction details defer to ux-requirements.md.Notation — "MUST/SHOULD/MAY" per RFC 2119. "Actor" defaults to an authenticated member with the minimum level stated. All permissions are re-validated server-side (PR-115).
1. Authentication (PS§2.1, PS§24)
| ID | Requirement |
|---|---|
| FR-PR-001 | Registration POSTs {email, password, name}. Email normalized to lowercase. Password MUST be ≥ 10 chars with ≥ 2 of {lower, upper, digit, symbol}; server re-validates with the shared Zod schema (packages/shared). On success: user created with status=ACTIVE, emailVerification=PENDING, personal workspace created (PR-013), verification email queued. Duplicate email → 409 EMAIL_TAKEN (no account-existence oracle beyond that). |
| FR-PR-002 | Verification email contains a single-use token (stored hashed, 24 h TTL). Clicking verifies (emailVerification=VERIFIED, emailVerifiedAt set). Resending is rate-limited (≤ 3/hour/email). Unverified users may sign in but the client pins a "verify your email" banner on every page and blocks invites/exports. |
| FR-PR-003 | Login POSTs {email, password} → 200 {accessToken (JWT, 15 min), refreshToken (opaque, 30 d)}. Access token claims: sub (user id), ws (workspace id, when bound), role, jti, iat, exp. Refresh token stored as SHA-256 hash in refresh_tokens; the plaintext token travels in an HttpOnly; Secure; SameSite=Strict cookie (factory_rt) and is never returned in the body. |
| FR-PR-004 | Refresh rotation: POST /auth/refresh with a valid token hashes it, looks it up, marks revokedAt + sets replacedBy, and issues a new token (same family id). If the presented token was already rotated (replay), the whole family is revoked and the user is signed out everywhere, with audit event auth.refresh.replayed. |
| FR-PR-005 | POST /auth/logout revokes the presented refresh token + session. GET /auth/sessions lists active sessions (UA, IP, created, last seen); DELETE /auth/sessions/:id revokes one; "sign out everywhere" revokes all. |
| FR-PR-006 | Password reset: POST /auth/password/forgot {email} always returns 200 (no oracle) but emails a 1 h single-use token when the account exists. POST /auth/password/reset {token, password} sets the new hash, marks token USED, revokes all refresh tokens + sessions, emits user.password.reset. |
| FR-PR-007 | Hashing uses bcrypt with cost from BCRYPT_ROUNDS (default 12). Password comparison is the library's constant-time compare. Passwords are never logged, never in audit metadata, never in URLs. |
| FR-PR-008 | Profile: GET/PATCH /users/me for name, displayName, avatarUrl, locale, timezone, theme. Avatar upload goes through the file pipeline (PR-105) with a 5 MB cap and image-only mime allow-list. |
| FR-PR-009 | Auth endpoints sit behind the strictest rate-limit tier: 5/min per IP for login, 3/min per IP for forgot-password, 3/day per IP for registration (see security-architecture §10 for the full tier table). |
| FR-PR-010 | Audit events (mandatory): user.login.success, user.login.failed (with reason: bad_password, suspended, not_verified), user.logout, user.registered, user.password.reset, auth.refresh.replayed, session.revoked. Each carries actor, IP, UA, timestamp (schema: audit_logs). |
| FR-PR-011 | OIDC: GET /sso/oidc/:orgSlug/start → authorization-code + PKCE flow → callback validates id_token signature/nonce → maps to user via sso_identities. SAML: ACS endpoint validates assertion signature, NameID → sso_identities.externalId. When sso_provider.enforce = true, password login for the org returns 403 SSO_REQUIRED. |
| FR-PR-012 | SSO linking rules: email match → link identities to existing user; no match → create user with status=INVITED and deliver invitation email. De-provisioning (SCIM deactivate) sets User.status=SUSPENDED and revokes all sessions/refresh tokens. |
2. Workspaces & tenancy (PS§1)
| ID | Requirement |
|---|---|
| FR-PR-013 | Registration transaction creates: users row, workspaces row (isPersonal=true, slug personal-<userSlug>, ownerId), and workspace_members row with role=OWNER. Failure of any step rolls back the whole registration. |
| FR-PR-014 | POST /workspaces {name, slug, iconUrl?}. Slug: lowercase kebab, 3–40 chars, unique platform-wide; on conflict the API suggests alternatives. Owner becomes OWNER member. |
| FR-PR-015 | Delete (owner only) requires typed confirmation ("DELETE <name>"); workspace enters soft-delete (deletedAt); after 30 days a purge job removes workspaces, pages, blocks, files (object keys deleted), search docs, and orphans audit rows set workspaceId=NULL (kept for forensics). |
| FR-PR-016 | Role model per product-requirements §4. Role is stored per membership (workspace_members.role, team_members.role inherits workspace role as ceiling). |
| FR-PR-017 | Role change rules: only OWNER/ADMIN may change roles; an ADMIN may not grant/strip OWNER; the last active OWNER cannot be demoted or removed (API error 409 LAST_OWNER). Every change → audit workspace.role.changed. |
| FR-PR-018 | Invitation lifecycle: create (POST /workspaces/:id/invitations {email, role, expiresAt}) → email with signed token link (token hashed in invitations) → accept (must be a registered user or register first) creates membership → decline / revoke / expire (job marks EXPIRED after expiresAt). Unique constraint (workspaceId, email, role) on PENDING invitations. |
| FR-PR-019 | Teams: POST /workspaces/:id/teams {name, slug, color, iconUrl}; members added by workspace member+; isPrivate teams visible only to their members. Team position is fractional (a0, a1, …) and reorderable by drag. |
| FR-PR-020 | Sidebar data: one query returns Home (workspace root page), Inbox (unread notification count), Search, Favorites (user favorites), Teamspaces (teams ordered by position), Private (user's private root), Shared (pages shared with user), Trash. Page tree is virtualized and lazy-loaded per folder. |
| FR-PR-021 | Settings routes: /settings/profile, /settings/members, /settings/teams, /settings/security (sessions, API keys, 2FA placeholder), /settings/billing (plan display, limits), /settings/integrations (placeholder list). Each section gated by role. |
| FR-PR-022 | Organization: POST /organizations {name, slug}; workspaces may be attached (organizationId); org members get org-level role. Org policy JSON keys: sso.enforce, sharing.publicAllowed, password.minLength, retention.*. |
| FR-PR-023 | SCIM endpoints (/scim/v2/Users, /scim/v2/Groups) behind a SCIM bearer token: create (→ user INVITED or link), update (name, email, active flag), deactivate (suspend), group sync → team membership. |
| FR-PR-024 | Usage limits enforced in repositories: membership count before invite/accept; storage bytes before file READY; AI tokens before gateway call (budget reset monthly); API rps from workspace.usageLimits.apiRps (default from plan). Over-limit → 402 LIMIT_EXCEEDED with the limit name. |
3. Pages (PS§3)
| ID | Requirement |
|---|---|
| FR-PR-025 | Page CRUD under /workspaces/:ws/pages. Create accepts {parentId?, title, icon?, coverUrl?, description?, teamId?, isPrivate?}. parentId MUST be in the same workspace (server-validated) — cross-tenant parent links are impossible. |
| FR-PR-026 | Sibling ordering: new page at index i between siblings with positions p_a and p_b gets position = mid(p_a, p_b) (fractional algorithm, database-architecture §7). Root-level pages of a workspace: parentId = null. |
| FR-PR-027 | Page surface (see PS§3): icon (emoji or file id), cover (file id/URL), title (required, ≤ 200 chars), description (≤ 500), blocks, comments, shares/permissions, versions, child pages. |
| FR-PR-028 | Trash: delete sets trashedAt (blocks remain, deletedAt unset). Restore clears trashedAt and, if the parent was deleted, reparents to the parent's former parent. Purge (owner/admin, or any user for their own after policy) hard-deletes page + blocks + comments + share rows; file object keys deleted asynchronously. |
| FR-PR-029 | Archive: archivedAt set → hidden from sidebar tree and search (until unarchived); trash and archive are mutually exclusive states. |
| FR-PR-030 | Favorites: POST /pages/:id/favorite (position = fractional append); sidebar "Favorites" sorts by position; unfavorite removes the row. Guests may favorite pages they can view. |
| FR-PR-031 | Versions: an explicit "save version" (page menu) plus automatic snapshots when title or structure (block tree shape) changes, max 100 versions kept per page (oldest dropped). GET /pages/:id/versions?limit=, GET .../:version, POST .../restore {versionId} — restore writes a new version (non-destructive). |
| FR-PR-032 | Private pages: isPrivate=true means only the creator and explicit grants may view (workspace members see nothing in search/sidebar). Toggling private on a page with children cascades the flag to children unless they have their own grants. |
| FR-PR-033 | Tree performance: sidebar fetches depth-limited subtrees (?depth=2 default) with nextCursor; full tree never fetched client-side. Target: 100k-block workspace tree interactive in < 2 s (NFR-NF1). |
4. Blocks & editor (PS§4, PS§5)
4.1 Block model (PR-034)
Block (schema blocks): id (cuid, stable), pageId, workspaceId,
parentId (self-referencing), type (BlockType enum), position
(fractional), content JSONB (type-specific), properties JSONB (colors,
alignment, width, checkbox state), isSynced + syncedSourceId,
createdBy/updatedBy, version (optimistic concurrency), deletedAt.
4.2 All 28 block types
| # | Type | content shape |
properties shape |
Behavior |
|---|---|---|---|---|
| 1 | paragraph |
{text: RichText} |
{align?, color?} |
Plain text; empty paragraph shows placeholder "Type '/' for commands…" |
| 2 | heading_1 |
{text: RichText} |
{align?, color?, showAnchor?} |
1.875× title scale; anchor link on hover when showAnchor |
| 3 | heading_2 |
same | same | 1.5× scale |
| 4 | heading_3 |
same | same | 1.25× scale |
| 5 | quote |
{text: RichText} |
{borderColor?} |
Left-border accent, muted text |
| 6 | callout |
{text: RichText} |
{icon: emoji, background: token color} |
Bordered card, icon left, tinted background |
| 7 | bulleted_list |
{text: RichText} |
{level} |
Indent by level; Enter continues list |
| 8 | numbered_list |
{text: RichText} |
{level} |
Numbering computed from consecutive same-level siblings; unnumbering on gap |
| 9 | todo |
{text: RichText} |
{checked: bool, level} |
Checkbox toggles strikethrough; "x" prefix on paste converts |
| 10 | toggle |
{text: RichText} |
{open: bool, level} |
Collapsible; children are blocks with parentId = toggle.id; chevron + ⋮⋮ handle; Enter inside opens |
| 11 | image |
{fileId, alt?, caption?} |
{width?: %, align?} |
Renders file thumbnail; width drag handle; caption below; loading skeleton while file.status=UPLOADING |
| 12 | video |
{fileId? | url} |
{align?, autoplay?} |
Native player for files; embed for URLs; poster = thumbnail |
| 13 | audio |
{fileId} |
{loop?} |
Custom minimal player (play/seek/time) |
| 14 | file |
{fileId} |
— | Card: icon by mime, name, size, download (signed URL), preview link |
| 15 | bookmark |
{url, title?, description?} |
— | Card with favicon, domain, title, description; fetches OpenGraph on insert (cached in file metadata) |
| 16 | embed |
{url, provider} |
{height?} |
iframe sandbox (sandbox="allow-scripts allow-same-origin", no allow-top-navigation); provider presets (Loom, Figma, Airtable, generic) |
| 17 | code |
{code: string, language} |
{language, showLineNumbers: true} |
40+ languages, syntax highlight, copy button, overflow-x scroll; no line wrapping by default |
| 18 | equation |
{latex: string} |
— | Rendered via KaTeX; block mode; invalid LaTeX shows error styling + raw text |
| 19 | column_list |
— | {widths: number[] (sum=1)} |
Container; children are column blocks; resizable via drag handle between columns; stacks below 768 px |
| 20 | column |
— | {width?: inherited} |
Layout child of column_list; holds arbitrary blocks |
| 21 | divider |
— | {color?} |
Horizontal rule, 16 px vertical margin |
| 22 | spacer |
— | `{height: 8 | 16 |
| 23 | database |
{databaseId} |
{viewId? (initial view)} |
Inline database render (all 9 view types); properties/views owned by the databases row |
| 24 | linked_database |
{databaseId, viewId?, filter?} |
— | Read/query projection of another database (not a copy); edits flow through the same engine |
| 25 | table |
{rows: [[Cell]], cols: int} |
{colWidths?} |
Static table; cell editing; add/remove rows/cols; not queryable (use database) |
| 26 | chart |
{chartId} |
— | Renders charts row (see §12) |
| 27 | form |
{formId} |
— | Renders published form inline; submissions create records in the form's database |
| 28 | synced_block |
{masterId | null} |
— | Master renders own content; copies render master's content live; "synced" badge; breaking sync makes the copy independent |
| — | child_page |
{pageId} |
— | Sidebar-row-style block in the page body; click opens the child page (used by templates & TOCs) |
| — | child_database |
{databaseId} |
— | Row that opens a linked database's record list |
child_page/child_databaseare listed in theBlockTypeenum and the slash menu even though they are not in the PS§4 table grouping; the enum is authoritative (30 values; 28 "authoring" types + 2 structural rows).
4.3 Editor interactions
Mouse (PR-042):
| Interaction | Behavior |
|---|---|
| Hover block | Left gutter shows ⋮⋮ drag handle + "+" add-below button (appears at ≥ 150 ms hover, no animation jank) |
| Click handle, drag | Drag preview follows cursor; drop indicator: line (sibling) or box (into toggle/column/child); valid targets highlight |
| Click block | Places caret; selects text on second click, triple-click selects block content |
| Shift+click block | Extends multi-selection (contiguous) |
| Right-click block / selected blocks | Block context menu (§4.5) |
| Drag image edge | Resizes width (snaps at 25/50/66/75/100 %) |
| Drag column divider | Resizes column widths (live, min 10 %) |
| Drag OS file onto editor | Upload → insert image/file/video/audio/code (.txt/.md) at drop point |
Keyboard (PR-041) — full master table in ux-requirements §10; the load-bearing rules:
| Key | Effect |
|---|---|
Enter |
Split block: new empty block of same list type below; empty list item on Enter exits list; in toggle: opens if empty, else new child |
Shift+Enter |
Soft line break (line break node) inside the block |
Backspace at block start |
Merge with previous block (inherit its type); if previous is a toggle heading, un-indent instead |
Delete at block end |
Merge next block upward |
Tab / Shift+Tab |
Indent (make child of previous list/toggle) / un-indent; in table cells move focus |
↑ / ↓ |
Move caret across blocks; at edge, scroll; inside columns moves between columns at column edges |
Cmd/Ctrl+A |
1st: select block content; 2nd: select whole block; 3rd: all blocks |
Cmd/Ctrl+C/X/V |
Internal: copy block JSON (content + properties + subtree); external: copy rendered markdown/HTML; paste: detect markdown/HTML/RTF → convert to blocks (plain text → split on newlines) |
Cmd/Ctrl+Z / Shift+Z / Cmd+Y |
Undo / redo (block-operation history, in-memory 100 steps; versions are the durable history) |
Cmd/Ctrl+B/I/U |
Bold / italic / underline on selection (also: Ctrl+Shift+S strikethrough, Ctrl+E code) |
Cmd/Ctrl+K |
Command palette (global) |
Cmd/Ctrl+Shift+L |
Link on selection |
Cmd/Ctrl+Shift+1/2/3 |
Convert to H1/H2/H3; Cmd+Shift+0 → paragraph |
Slash commands (PR-045…PR-047): typing / at a block start (or / mid-text with the word selected) opens the menu; fuzzy-matches command names + synonyms ("h1", "heading one", "bulleted"); grouped Text / Lists / Media / Code / Data / Layout / Embeds; ↑↓ + Enter selects; Esc closes; results with database targets open the property-picker dialog before insertion.
Context menus (PR-048…PR-049):
Block menu (right-click / ⋮⋮ menu): Turn into ▸ (grouped list of convertible types) · Duplicate · Copy · Copy link (deep #blockId) · Move to ▸ (page picker) · Delete · ── · Comment · Ask AI ▸ (summarize/rewrite/improve/translate — present only when AI enabled) · ── · Color ▸ (text color palette) · Background ▸ (background palette) · ── · Convert to ▸ (heading 1–3 / quote / callout / todo — quick path) · ── · More ▸ (save as template, permissions (if page root), duplicate as synced block).
Page menu (sidebar row / page top-bar ⋯): Open · Open in new tab · Duplicate · Rename · Move ▸ (page picker) · Copy link · Favorite (toggle) · Archive · Delete (→ trash) · Export ▸ (Markdown/HTML/PDF) · Share (share dialog) · Permissions (grant editor).
Drag & drop (PR-050…PR-056): dnd-kit sensors (pointer + keyboard); drop targets: sibling position, toggle children, column, other page (sidebar row, child_page block, or page mentioned in "Move to"); drop of a block onto a database creates a record from the block (markdown → title + first property); multi-block drag preserves relative order and nesting; auto-scroll when dragging near viewport edges; all drops write fractional positions in one API batch (single transaction) — partial failure rolls back the whole move.
5. Databases (PS§9)
5.1 Property types (24) — PR-058
| # | Type | config JSON |
Value JSON (record_properties.value) |
Editor widget |
|---|---|---|---|---|
| 1 | TITLE |
— | not stored (page title) | Page title input (exactly one, required, isTitle=true) |
| 2 | TEXT |
— | "plain string" |
Inline single-line |
| 3 | RICH_TEXT |
— | RichText (markdown source) |
Multi-line markdown |
| 4 | NUMBER |
{format: int|float, precision, prefix?, suffix?, currency?} |
42 | 19.99 | null |
Right-aligned numeric, format-aware display |
| 5 | SELECT |
{options: [{id, name, color}]} |
"opt-id" |
Dropdown with colored pill |
| 6 | MULTI_SELECT |
same | ["opt-a","opt-b"] |
Multi-pick popover, colored pills |
| 7 | STATUS |
{options: [{id, name, color}]} (ordered) |
"opt-id" |
Dropdown; option order = board column order; per-option color |
| 8 | DATE |
{format?} |
ISO 8601 2025-09-30T00:00:00Z |
Date picker (+ optional time) |
| 9 | DATE_RANGE |
— | {start: ISO, end: ISO} |
Dual date picker; calendar/timeline render span |
| 10 | PERSON |
{multiple?: bool} |
"userId" | ["userId"] |
User picker (workspace members) |
| 11 | EMAIL |
— | "a@b.co" |
Text + mailto link + client validation |
| 12 | PHONE |
— | "+1 555…" |
Text + tel link |
| 13 | URL |
— | "https://…" |
Link field, favicon preview |
| 14 | CHECKBOX |
— | true | false |
Toggle |
| 15 | FILES |
{maxCount?: 10} |
["fileId"] |
Upload button + chips, thumbnails |
| 16 | CREATED_TIME |
— | read-only ISO (auto) | Timestamp, not editable |
| 17 | CREATED_BY |
— | read-only "userId" |
Avatar + name, not editable |
| 18 | LAST_EDITED_TIME |
— | read-only ISO (auto) | Timestamp, not editable |
| 19 | LAST_EDITED_BY |
— | read-only "userId" |
Avatar, not editable |
| 20 | RELATION |
{relationId, multiple} |
["recordId"] |
Multi-select popover of linked records (search) |
| 21 | ROLLUP |
{rollupId} |
computed: number/number[]/text/date | Read-only computed badge |
| 22 | FORMULA |
{formulaId} |
computed value | Read-only; error state shows formula parse error |
| + | BUTTON |
{buttonId} |
n/a | Action button (see §5.6) |
| + | UNIQUE_ID |
{format: sequential|uuid, prefix?} |
"PRJ-0001" |
Auto, read-only |
The
PropertyTypeenum inschema.prismais the source of truth (24 values, TITLE … UNIQUE_ID, incl. BUTTON and UNIQUE_ID); this table lists all 24. The table's#column numbers the 22 "stored/computed" types and marks BUTTON and UNIQUE_ID with+— the implemented set is the full enum.
5.2 Records (PR-060, PR-061)
- Create: inline "New row" (table), card "Add a card" (board), day click
(calendar), API
POST /databases/:id/records, form submission, automation action. Transaction:pagesrow (title = TITLE value) +database_recordsrow (pageId1:1) +record_propertiesrows for provided values. - Update: cell edit →
PATCHwith property id + value; server validates value against property type (Zod per-type schemas); auto properties (created/last-edited) are read-only (client-sent values ignored); last-edited updated server-side. - Archive:
archivedAtset; excluded from views by default; recoverable. - Duplicate: copies title, property values (relations/rollups/formulas not copied — recomputed), creates new page.
- Delete record: moves its page to trash (one object, one trash flow).
5.3 Views (9) — PR-062…PR-070
Each DatabaseView row keeps: name, type, position, config
(visible property ids + order, column widths, card layout, color, density),
filter (tree), sort ([{propertyId, direction}]), group
([{propertyId, direction}]), pageSize.
| View | UX rules (full spec in ux-requirements §6) |
|---|---|
TABLE |
Virtualized rows (100k+), virtualized columns, sticky title column + first visible column, inline editing, row hover actions (open page, duplicate, archive, add comment), multi-select (shift-click) with bulk toolbar (archive, duplicate, assign, delete), row height from density, column width drag (min 80 px), "show more columns" chevron |
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 |
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 |
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 |
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 |
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 |
CHART |
Embeds a chart (§12) bound to this database; config picker = chart config |
DASHBOARD |
Embeds a dashboard widget grid (§12) |
FORM |
Renders the form (§10) bound to this database |
View rules: 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).
5.4 Filters, sort, group (PR-071…PR-073)
- Filter 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; file:hasFiles, hasNoFiles.valuespecial tokens:"me"(current user),"today","now". Evaluation is server-side inpackages/database-engine(shared with the client for optimistic UI) — same code path, property-tested against golden datasets. - Sort: applied after filter; null ordering option per key (default: nulls last); stable sort by record
positionas final tiebreaker. - Group: applied after sort; groups collapsible; group headers show count; multi-level (≤ 3); boards use group[0] as columns.
5.5 Relations, rollups, formulas (PR-075…PR-083)
- Relations: create from property picker ("Relation to… database");
Relationrow stores both property ids (or creates the reverse property on the target when bidirectional); cardinality enforced at link time (ONE_TO_ONE: linking a second target on the "one" side unlinks the previous — API returns the replaced link). Links stored inrelation_links((databaseId, recordId, targetDatabaseId, targetRecordId)unique). Deleting a record removes its links (cascade); deleting a relation property removes all its links. - Rollups:
Rolluprow{relationId, targetPropertyId, aggregate, filter?}; value computed lazily on read and eagerly refreshed via events (record.updated,relation.changed) for the affected records; PERCENT = fraction of matching (filtered) related records (0–1, displayed %); UNIQUE = deduped value list (or count when display is "count"); result type inferred (SUM/AVG → number; MIN/MAX → type of target; COUNT/PERCENT → number; UNIQUE → array). - Formulas: expression text → parser (tokens: identifiers, strings,
numbers, booleans, dates,
prop("Name"), function calls, arithmetic, comparison, logical, ternary) → AST (stored informulas.ast) → evaluator with a fixed function table (PR-080). No recursion depth > 16, no string eval, no access to host objects, evaluation budget 10 000 steps. Errors: parse error / type error surface in the property cell as a red badge with the message; formula is still saved (broken formulas don't block the DB).now()re-evaluates on each read (documented nondeterminism); all other inputs → deterministic output.dateAdd(date, amount, unit),dateBetween(a, b)→ days,contains(hay, needle),replace(s, from, to),concat(a, b, …),length(x),if(cond, a, b),and/or/not(...),sum/average/min/max(list)over arrays (relation/rollup values). - Buttons:
DatabaseButtonwith orderedactions[]; each action{type, config}from:set_property {propertyId, value},create_page {title, templateId?},update_page {pageRef: "current", title?, properties},archive_page {pageRef: "current"},duplicate_page {pageRef: "current"},send_webhook {webhookId},trigger_automation {automationId}. Runs synchronously in one transaction (webhook/automation enqueue asynchronously); executes with the caller's permissions (button cannot escalate); run is auditeddatabase.button.executed.
6. Automation (PS§10)
Triggers (PR-135) — AutomationTrigger {type, config}:
| Type | config |
Fires when |
|---|---|---|
RECORD_CREATED |
{databaseId} |
a record created in the database |
RECORD_UPDATED |
{databaseId} |
any property of a record changed |
PROPERTY_CHANGED |
{databaseId, propertyId, toValue?} |
that property changed (optionally to a value) |
PAGE_CREATED |
{teamId? | null} |
a page created (optionally inside a team) |
FORM_SUBMITTED |
{formId} |
that form receives a submission |
SCHEDULE |
{cron: "0 9 * * 1-5", timezone?} |
cron match (server TZ default; per-trigger override) |
WEBHOOK |
{path: "auto-xyz"} |
POST /api/v1/webhooks/inbound/:path (HMAC or token-validated) |
MANUAL |
— | "Run now" in UI or API |
Conditions (PR-139): AND/OR/NOT tree over predicates
{propertyId|field, operator, value} (same operator table as §5.4, plus
field ∈ {createdBy, createdAt, lastEditedBy, lastEditedTime}); evaluated
against the trigger event payload; empty conditions = always true.
Actions (PR-136) — ordered, each {type, config, retries=3, timeoutMs=30000}:
| Type | config |
Effect |
|---|---|---|
UPDATE_RECORD |
{recordRef: "trigger", propertyId, value} |
Patch the triggering record (or a looked-up record) |
CREATE_PAGE |
{title, parentId?, templateId?, teamId?} |
Create a page (template optional) |
SEND_EMAIL |
{to: userRef[] | email, template, vars} |
Transactional email via SMTP worker |
SEND_WEBHOOK |
{webhookId | url, secretRef?} |
Enqueue webhook delivery (signed) |
CREATE_NOTIFICATION |
{to: userRef[] | team, type, title, body, link} |
In-app (+email per preferences) notification |
ASSIGN_USER |
{propertyId, to: userRef} |
Set a PERSON property |
CHANGE_STATUS |
{propertyId, toValue} |
Set a STATUS property |
CALL_INTEGRATION |
{integrationId, action, params} |
Provider call (Slack/GitHub/custom) with encrypted credentials |
RUN_AI_ACTION |
{prompt, context: "record"} |
AI gateway call (summarize/extract), result → property or email |
Runtime: trigger match on domain events (Redis stream consumer); run row
created (PENDING → RUNNING → SUCCEEDED/FAILED); actions execute
sequentially in a BullMQ job; failure of action k retries up to
action.retries with exponential backoff (1 s, 2 s, 4 s…); exhausted retries
→ action FAILED, run DEAD_LETTER (visible, re-runnable manually); every
step appends automation_logs (info/warn/error + structured data).
Concurrency: one run per automation at a time (dedup key) — re-triggers while
running are queued, not dropped. Paused/archived automations are skipped by
the matcher. All runs carry the triggering actor (or SYSTEM for schedules)
and its permissions are used by the actions (no privilege escalation).
7. Forms (PS§11) — PR-127…PR-130
- Builder: add question (10 types: SHORT_TEXT, LONG_TEXT, NUMBER, EMAIL, DATE,
CHECKBOX, SINGLE_SELECT, MULTI_SELECT, RATING (1–5), FILE), each with label,
description, required, validation (
{min, max, pattern, options, minRating}),propertyIdmapping (required for non-FILE questions at publish time), order (fractional). - Publish:
Form.statusDRAFT → PUBLISHED (assignsslug, unique); PUBLISHED → CLOSED (submissions rejected with410 FORM_CLOSED). - Public page
/forms/:slug: no auth; renders questions with intro + branding (logo, accent color); client validation mirrors server Zod; submissionPOST /public/forms/:slug/submissions {answers}→ validates, creates recordFormSubmission(IP captured), emitsform.submitted; success screen with configurable message. Rate limit: 10 submissions/15 min/IP.
- Responses view (form page "Responses"): submissions table (date, answers preview, record link); open as record; delete submission (cascades record → trash).
8. Dashboards & charts (PS§12) — PR-131…PR-133
- Chart (
charts):{chartType: bar|line|area|pie|donut|number, config: {xAxis: propertyId, yAxis: propertyId, groupBy?: propertyId, aggregation: COUNT|SUM|AVERAGE|MIN|MAX|UNIQUE, color?: token, legend: bool}, filter?}. Data computed server-side (aggregation over filtered records, capped 10k input records with a warning badge). Number = single KPI (aggregation of one property). - Dashboard (
dashboards):layout= grid of widgets{widgetType: table|board|chart|number|calendar|timeline|text|linked_database, sourceRef: {type: database|chart|view|text, id?}, x, y, w (cols 1–12), h (rows), config, filter?}; widgets render live queries (read-only in dashboard context: navigation yes, editing no); add widget via picker; drag-resize (dnd-kit); dashboard can be public (read-only share link).
9. Collaboration & realtime (PS§13) — PR-084…PR-090
- Socket auth:
ws://host/ws?token=<access JWT>; server validates + binds the connection to (userId, workspaceId); roomsdoc:{ws}:{pageId|databaseId}andpresence:{ws}:{pageId}. - Document protocol: Yjs binary updates over the socket (server applies to an
authoritative
Y.Doc; on update → debounced (500 ms) persistence of the derived block tree to Postgres + domain events). On join: server sends state vector diff (or full doc from Postgres hydration) — client never trusts its own cache as source of truth. - Codec:
Y.Textper text block,Y.Mapper block metadata/properties,Y.Arrayper parent's children (ordered by fractional position); the codec is unit-tested property-based against the block tree (round-trip invariants). - Presence: awareness state
{user, name, color, cursor: {blockId, pos}, selection}; server heartbeats 30 s, prunes after 90 s silence; presence fan-out capped at 50 peers/room (beyond that, avatars only, no cursors). - Realtime comments: comment create/reply/resolve events fan out to the page room; notification fan-out uses the same socket when the user is connected, else queued for in-app on next connect + email per preferences.
- Reconnect: client keeps Yjs doc in IndexedDB; on reconnect sends updates since last ack; server keeps an in-Redis update log per doc (truncated after successful Postgres persistence) so a briefly-offline client resumes without full state.
- Offline: all editor edits apply locally (Yjs local-first); API calls queue
in
offlineQueue(Zustand + IndexedDB); on reconnect: Yjs sync (CRDT merge) then queued mutations replayed with idempotency keys; conflicts on non-CRDT fields (title, properties) resolve last-writer-wins with a toast.
10. Comments, mentions, notifications (PS§14) — PR-091…PR-097
- Attachment targets (exactly one of):
pageId,blockId(→ page implicit),selection(blockId +{anchor, focus}offsets into the block's text),recordId. A page's comment list = comments wherepageId = X(any block under it) or record-of-that-page. - Thread operations: create (body, mentions), reply (flat per thread), edit
(own, before resolve), delete (own or admin; deletes thread if root),
resolve/reopen (any participant; sets
resolvedAt/By,status), reactions (≤ 1 per user per emoji per target; toggle). - Mention syntax:
@triggers popup (users by name, teams, pages, databases — scoped to the workspace, max 20 results, fuzzy); stored asmentionIds(users) /pageMentionIds(resources); notification created per mentioned user (mention_notifications) and team (per member, deduped per (user, target)). - Notification types → sources: MENTION (comment/reply mention), COMMENT (reply in a thread you participate in), ASSIGNMENT (PERSON property set to you), PAGE_SHARED (grant added), TASK_ASSIGNED (record assigned via automation/button), DATABASE_CHANGE (subscribed to a database + property changed — subscription = "watch" on database), AUTOMATION (automation targeted at you), INTEGRATION (integration event mentioning you).
- Channels: IN_APP always; EMAIL per-type user preference (batched digest for COMMENT/DATABASE_CHANGE every 1 h, immediate for MENTION/ASSIGNMENT); WEBHOOK (workspace-level subscription, same dispatcher as PR-151).
- Inbox:
GET /notifications?unread=true&cursor=; mark read (single/all); deep link{type, id}→ navigates and auto-resolves the badge.
11. Search (PS§15) — PR-098…PR-104
- Indexing: domain events (
page.updated,block.updated,record.updated,comment.created,file.uploaded,user.updated, …) → BullMQsearch:indexjob → buildSearchDocument(type, refId, title, bodyplain-text extracted from blocks,permissionsvisibility snapshot:{ownerUserId, workspaceId, teamIds, grantUserIds, grantTeamIds, public: bool, scope}) → upsertsearch_documents+ OpenSearch doc (with optional embedding vector for RAG). Reconciliation: nightly full re-index job + on-demand "reindex" admin action. Index lag SLO: < 30 s p95. - Query:
POST /search {q, types?, teamId?, createdBy?, updatedAfter?, updatedBefore?, cursor, size≤50}→ OpenSearch bool query: (multi_match title^3 + body, prefix + fuzzyAUTO) AND permission filter (terms onpermissions.*for the caller's user/teams, ORpublic=true, OR share-token session). Response:{results: [{type, refId, title, snippet, score, updatedAt, creator}], cursor}. - Snippets: OpenSearch highlight (± 40 chars,
<mark>), client strips tags and re-renders (no raw HTML injection). - Suggestions:
GET /search/suggest?q=→ prefix on title field, ≤ 10. - Recent searches: per-user, client-persisted (local), max 10, editable.
- Graceful degradation: OpenSearch down → API returns
503 SEARCH_UNAVAILABLE→ UI shows inline "Search is unavailable" empty state; app remains fully usable (NFR-NR2).
12. Files (PS§16) — PR-105…PR-111
- Upload flow:
POST /files:prepare {name, size, mimeType, blockId?/recordId?}→FilerowUPLOADING+ presigned PUT (multipart for size > 8 MB) → client PUTs to S3 →POST /files/:id:complete→ worker job (ClamAV scan → metadata (mime sniff via magic bytes, image dimensions, video duration, pdf page count, SHA-256) → thumbnail (images: 512 px + 128 px; video: poster frame; pdf: first page) →READY). - Limits: per-file max 500 MB (plan-configurable); images 25 MB; total
workspace storage from plan; per-mime-type allow-list (deny: executables,
.exe/.msi/.bat/.shblocked regardless of plan); MIME determined by magic bytes, never by extension (extension mismatch →FAILED+ report). - Security: private buckets; reads via signed URLs (15 min TTL); virus
verdict
VIRUS_DETECTED→ object retained in quarantine prefix, never served, admin report shows signature; per-file permission override (permissionsJSON: stricter-than-page grants only — file grants can remove access, never add it). - Versions: re-upload to the same file (same key) creates
FileVersion(old keys retained per retention policy);?version=on signed URLs. - Trash:
deletedAtset; objects deleted by purge job after 30 days. - Use sites: editor image/file/video/audio blocks; Files property; form FILE answers; avatars; covers; icons.
13. Permissions & sharing (PS§17, PS§18) — PR-112…PR-120
- Level semantics: NONE = no access (explicit deny); VIEW = read page/blocks/ properties (no comment); COMMENT = VIEW + comment/mention; EDIT = COMMENT + edit blocks/records/properties; FULL_ACCESS = EDIT + manage permissions + delete/share/export.
- Evaluation (algorithm in security-architecture §4):
role base level per role (Owner=FULL_ACCESS, Admin=FULL_ACCESS on most
resources, Member=EDIT on workspace content, Guest=NONE at workspace root);
explicit grants (
permissionsrows) collected for grantee = {user, teams user belongs to, role} along the path workspace → team → ancestors → resource;effective = max(...), with an explicit NONE on any closer ancestor overriding inheritance (deny wins at its level). - Grant UI: share dialog (scope: workspace / team / specific users / public
link), level picker per grantee, inherit indicator ("Inherited from
<parent>"), copy link, revoke. Changes audit
permission.changed(before/after in metadata). - Public sharing:
Sharerow (token,scope=PUBLIC,allowComments,allowDuplicate,allowSearchIndexing,passwordHash?,expiresAt?); public route/share/:tokenrenders a read-only page (no sidebar, no settings, no AI); password prompt when set (session cookie 1 h); expired → "link expired" page; duplicate (if allowed) copies into the viewer's own workspace with attribution. - Server-side: every repository query for content includes the permission
predicate; mutations check the required level (read=VIEW, comment=COMMENT,
write=EDIT, manage=FULL_ACCESS) before and after (optimistic); denial →
403 PERMISSION_DENIED+ audit.
14. Templates (PS§19) — PR-121…PR-123
- Create from: page ("Save as template" — snapshots page + block tree + databases inside, new ids), database ("Save as template" — schema + views + records), team (structure), workspace (skeleton of teamspaces + pages).
snapshotJSON = frozen structure; instantiation:POST /templates/:id/ instantiate {targetParentId}→ new ids, relations re-wired within the snapshot, external references dropped,createdBy = caller; source untouched.- Builtin templates (
isBuiltin=true, shipped with seed): "Project" (Overview/Goals/Requirements/Tasks database/Timeline/Meeting Notes/ Documents), "Meeting Notes", "Bug Report", "Weekly Update", "OKR", "CRM Contacts" (database), "Content Calendar" (database).
15. Import / export (PS§20) — PR-124…PR-126
- Import (job-based,
POST /imports {fileId}): parsers per format — Markdown (ATX headings, lists, task lists, tables, images, code fences, callout HTML comment syntax), HTML ( sanitized import, §security), CSV (→ database: first row = property names, type inference), JSON (our export format — lossless round-trip), Notion zip (export bundle: markdown +manifest.json+ attachments). Pipeline: upload → parse → normalize to block tree / property schema → validate (Zod) → import (transaction per page) → report ({pagesCreated, blocksCreated, databases, errors[]}). - Export (
POST /exports {scope: page|database|team, format, includeFiles}): Markdown (page → md tree; database → md table +record.mdper record), HTML (single file, inline CSS), PDF (headless renderer, A4, page per section), CSV (database rows + properties), JSON (full structure incl. relations — the import format). Exports are jobs with progress + signed download URL (1 h). - Both audited (
import.completed/export.completedwith counts).
16. AI (PS§21) — PR-141…PR-147
- Gateway: one interface
{chat(messages, {model, tools, temperature, maxTokens}), embed(texts), listModels()}; providers: OpenAI, Anthropic, Google (Gemini), OSS (vLLM/Ollama via OpenAI-compatible base URL), custom. Per-workspace provider config (workspace.settings.ai) with platform default fallback. Failover: provider timeout (60 s) →503 AI_UNAVAILABLE(no silent provider swap mid-conversation). - Ask AI: chat panel (page or global) with context chips (current page,
selected blocks, database); history in
ai_conversations/ai_messages(roles incl.tool); streaming responses (SSE). - Writing tools (selection or block): summarize, rewrite (tone param), improve (grammar/clarity), translate (target language) — applied as a preview diff the user accepts/rejects (never in-place overwrite).
- Database AI (TASK-0124): "generate database" (description → properties +
page-backed sample records, ≤ 10, created through the normal database
services — no partial writes on failure); "extract data" (pasted
text/markdown, ≤ 50k chars → ≤ 200 records into an existing database,
unmatched columns skipped and reported, live mapping preview before
applying); "generate formula" (natural language → formula expression,
validated by the safe formula engine before the column is saved, sample
rows evaluated). Generated-property allowlist: the model may only
CREATE property types from
GENERATABLE_PROPERTY_TYPES(packages/ai/src/schemas/database.ts) — allPropertyTypevalues exceptTITLE(reserved: a database seeds exactly one),RELATIONandROLLUP(require a target/relation — created separately),FORMULA(the dedicated formula tool), andBUTTON(requires view-action config). A rejected type is fed back for one retry, then the call fails422 ai_invalid_structurewith nothing written. The spec's "22 types minus relation/rollup/button/formula/extraction" phrasing maps onto the real 24-value enum as recorded inNON_GENERATABLE_PROPERTY_TYPES(there is noEXTRACTIONmember;TITLEis additionally non-generatable). - RAG search: "search workspace" query → hybrid retrieval (BM25 top-50 +
vector top-50 → RRF → top-12, permission-filtered) → LLM answer with inline
citations
[1] page title — section→ answer + sources panel. Chunks: ~800 tokens, 15 % overlap, chunk boundaries on headings/blocks; embeddings inai_embeddings+ vector store (OpenSearch kNN). - Generation via tool calls: the LLM is given a structured tool set
(
create_page {title, parentId, blocks[]},create_database {title, properties[], views[], records[]},create_template {...}); block payloads are Zod-validated server-side; a rejected tool result is fed back for retry (max 3). Never raw SQL, never direct Prisma from AI code. - Metering: every call logs provider/model/tokens/latency/cost to
ai_messages; per-workspace monthly token budget (usageLimits.aiMonthlyTokens, Phase-12 field name — supersedesaiTokenshere; unset/0 = unlimited) enforced at the gateway submit time (no spend on an over-budget workspace):402 AI_BUDGET_EXCEEDED{reason, used, limit}+ a throttled admin notification (one per workspace per 24 h, in-app + email) + theai.budget_exceededaudit event; a per-workspace rate window (default 60 req/min) →429 RATE_LIMITED; per-user 30 msg/min cap on the chat endpoints (TASK-0122).
17. Public API & webhooks (PS§22) — PR-148…PR-153
- Surface (all under
/api/v1, workspace-scoped):pagesCRUD +children,databasesGET/PATCH,POST /databases/:id/query({filter?, sort?, group?, properties?, pageSize?, cursor?}→ same engine as the UI),POST /databases/:id/records(batch ≤ 100),recordsGET/PATCH/DELETE,search POST /search,files POST /files(metadata + presigned),comments,workspaces(read),forms POST /forms/:id/ submissions(with key),automations(run),webhooks(manage). - Auth:
Authorization: Bearer fk_live_…(API key) or OAuth2 access token. Key = prefix (fk_live_ab12) + 32 random bytes; only SHA-256 stored. Scopes READ/WRITE/ADMIN gate methods; key auth bindsactorType=API_KEY. - Webhooks (outbound): subscribe per workspace to the event list
(
page.created|updated|deleted,database.created|updated,record.created|updated,comment.created,form.submitted,automation.executed); dispatcher (worker) POSTs{id, type, workspaceId, createdAt, data}with headersX-Factor-Signature: t=<ts>,v1=<HMAC-SHA256(secret, ts.body)>,X-Factor-Event: <type>; success = 2xx within 10 s; retries: 8 attempts, backoff 1 m → 2 h (exponential, jittered); each attempt logged inwebhook_deliveries; endpoint URL must be HTTPS (http allowed only for loopback in dev); SSRF guard: private IP ranges / link-local rejected. - OpenAPI: spec generated from DTO metadata; CI contract test spins the
API and diffs the live
/api/v1/openapi.jsonagainst the committed spec.
18. Audit (PS§23) — PR-010, PR-154 (+ per-feature audit rows)
- Mandatory catalog (minimum):
user.registered,user.login.success,user.login.failed,user.logout,user.password.reset,session.revoked,auth.refresh.replayed,workspace.created,workspace.role.changed,workspace.invitation.sent|accepted|revoked,page.created|updated|deleted|shared,page.restored,permission.changed,database.created,database.property.changed,record.created|updated|deleted,file.uploaded,file.downloaded(sampled),file.virus_detected,comment.created,automation.created,automation.executed,api_key.created|revoked,webhook.created,form.published,import.completed,export.completed,ai.generation.completed,sso.login,scim.user.updated|deactivated,retention.purged. - Row:
actorType(USER/API_KEY/SYSTEM/INTEGRATION),actorId,workspaceId,pageId(when relevant),resourceType,resourceId,action(dot-notation above),ipAddress,userAgent,metadata(JSON: before/after for permission changes, counts for bulk ops),createdAt. - Writes happen in the same transaction as the operation when possible, else synchronously via the event bus with at-least-once delivery; audit write failure fails the operation (no silent loss for security events).
- Viewer (enterprise): filter by action/resource/actor/time; export CSV/JSON (paginated); retention per policy (default: keep 400 days, then purge to a cold copy).
19. Offline (PS§25) — PR-090 (details in §9 above)
- Scope (v1): editor content (Yjs), local UI state, queued API mutations. Read cache (TanStack Query) persists in IndexedDB so last-opened pages render offline.
- Rules: offline edits never show as "saved" — an "Offline — N unsynced changes" indicator is pinned in the top bar; sync engine retries with exponential backoff; a mutation rejected after reconnect (permission changed, record deleted) is surfaced as a conflict toast with keep-discard choice (CRDT fields auto-merge silently).