Database Architecture

PostgreSQL + Prisma, single schema (prisma/schema.prisma), ~50 models. Sibling docs: system · backend · realtime · search.

Conventions (from the schema header, normative):

1. Every tenant-scoped table carries workspaceId (and organizationId where relevant). All queries are tenant-scoped in the repository layer.
2. Ordering uses fractional positions (LexoRank-style strings), never indexes.
3. Flexible payloads (block content, property values, view settings, formula ASTs) are JSONB.
4. Records are pages: database_records.pageId is 1:1 with pages.id.
5. Polymorphic references (search_documents.refId, permissions.resourceId, shares.recordId, comments.pageMentionIds, …) are plain strings — the application validates referential integrity; Prisma does not model multi-way FKs on shared columns.

---

1. Model Map by Domain

1.1 Identity & Tenancy

Table	Model	Purpose & key columns
users	User	Global identity. email (unique), passwordHash (bcrypt; NULL for SSO-only users), emailVerification (UNVERIFIED/PENDING/VERIFIED), status (ACTIVE/INVITED/SUSPENDED), prefs (locale, timezone, theme), lastLoginAt
refresh_tokens	RefreshToken	Opaque rotating refresh tokens. tokenHash (SHA-256, unique — raw token never stored), sessionAgent, expiresAt, revokedAt, replacedBy (rotation chain for reuse detection)
sessions	Session	Login sessions (device list). tokenHash (unique), userAgent, ipAddress, status (ACTIVE/REVOKED/EXPIRED)
email_verifications	EmailVerification	tokenHash (unique), expiresAt; one per pending verification
password_resets	PasswordReset	tokenHash (unique), status (PENDING/USED/EXPIRED/REVOKED), usedAt; single-use, 1 h expiry
organizations	Organization	Enterprise tenant above workspaces. slug (unique), policy Json (security/retention/SSO-enforcement org policies), ssoConfig Json?, scimConfig Json?, ownerId
organization_members	OrganizationMember	org × user × role (unique [organizationId, userId])
sso_providers	SsoProvider	Per-org SSO: type (SAML/OIDC), config Json (entityId/cert ref/issuer/clientId — secrets referenced, not inlined), enforce
sso_identities	SsoIdentity	IdP → local user link: unique [providerId, externalId]
retention_policies	RetentionPolicy	type (PURGE/HOLD), scope (comments/trash/audit_logs/files/…), retainDays, config Json
workspaces	Workspace	The tenant. slug (unique), organizationId?, isPersonal (auto-created at registration), ownerId?, billingPlan, usageLimits Json (file size caps, AI budgets, seat counts), settings Json, deletedAt (soft)
workspace_members	WorkspaceMember	workspace × user × role (unique), invitedBy, joinedAt; role ∈ OWNER/ADMIN/MEMBER/GUEST
invitations	Invitation	tokenHash (unique), email, role, status (PENDING/ACCEPTED/DECLINED/REVOKED/EXPIRED), expiresAt; unique [workspaceId, email, role]
teams	Team	Teamspaces: slug (unique per workspace), position (fractional sidebar order), isPrivate
team_members	TeamMember	team × workspaceMember × user (unique) — links team membership to an existing workspace membership

Design notes.

• Workspace is the tenant boundary, not user. A user belongs to many workspaces; every tenant-scoped table scopes by workspaceId, so cross-workspace queries are a repository-layer bug, not a SQL accident.
• Organization is optional. Personal workspaces and small teams have organizationId = NULL; enterprise features (SSO/SCIM/policies/retention) activate when an org exists.
• Tokens are stored as hashes. Refresh tokens, session tokens, invitations, password resets, email verifications, and share passwords are all hashed at rest (SHA-256 for server-generated opaque tokens, bcrypt for user-chosen share passwords) — a DB leak does not yield usable credentials. See security, §8.

1.2 Pages & Blocks

Table	Model	Purpose & key columns
pages	Page	Content container (and record carrier). Tree: parentId (self-FK PageTree), grandparentId (denormalized grandparent for fast "sibling subtree" queries), position (fractional). Metadata: title, icon (emoji or file id), coverUrl, description. Lifecycle: isPrivate, archivedAt, trashedAt, deletedAt, restoredAt. createdBy/updatedBy, version (optimistic concurrency), teamId?
page_favorites	PageFavorite	Per-user star list, position fractional (user can order favorites), unique [workspaceId, userId, pageId]
page_versions	PageVersion	Named/scheduled snapshots: version Int, snapshot Json (page + block tree), unique [pageId, version]
blocks	Block	The core content table. Tree within a page: pageId, parentId (nullable = top level of the page), position (fractional), type (BlockType enum, 30 values), content Json (type-specific: text runs, media fileIds, code, table rows), properties Json (colors, alignment, column widths, checkbox state, …), isSynced + syncedSourceId (synced blocks), createdBy/updatedBy, deletedAt, version
block_versions	BlockVersion	Per-block history: version Int, content Json, properties Json, unique [blockId, version]

Design notes.

• Block tree, not document. Blocks form a tree per page (parentId), ordered by fractional position among siblings — this is ADR-002 (system, §5). COLUMN_LIST → COLUMN → blocks nesting is expressed with parentId, so columns are just container blocks, no special schema.
• content vs properties. content is the semantic payload (what the block says: text, file refs, code, latex). properties is presentation (color, alignment, width). Splitting them lets sync blocks mirror content while keeping local presentation, lets the AI layer rewrite content without touching layout, and lets the search indexer index content only.
• grandparentId denormalization: sidebar expansion and "children of sibling X" queries would otherwise need a self-join per level; the column is maintained in the same transaction as parentId writes (PageService/BlockService own the invariant).
• Trash semantics: trashedAt (recoverable, shown in /trash), deletedAt (purged; FK children cascade or SetNull per relation), archivedAt (hidden from tree, still addressable). A page is "live" when trashedAt IS NULL AND deletedAt IS NULL.

1.3 Databases

Table	Model	Purpose & key columns
databases	Database	The database entity (a DATABASE/CHILD_DATABASE block embeds it). pageId? (plain ref — the page the embedding block lives in; set on create, cleared if the block is deleted elsewhere), title, icon, schemaVersion (bumped on property schema changes; used by clients to revalidate), isArchived
database_properties	DatabaseProperty	Typed columns. type (PropertyType enum, 22 values), position fractional, isTitle (exactly one per database; the title property is the record page title), isVisible, required, config Json (select/multi-select/status options[] with colors, number format, relation ref {relationId}, rollup ref, button config), default Json?, soft deletedAt; unique [databaseId, name]
database_views	DatabaseView	9 view types (ViewType). Per-view config Json (visible property ids + order, column widths, card layout, color, density), filter Json? (nested AND/OR/NOT tree), sort Json? ([{propertyId, direction}]), group Json?, pageSize, position fractional
database_records	DatabaseRecord	A row = a page. pageId unique, 1:1 with pages (ADR-006), position fractional (row order in table/board), archivedAt, isSubItem (sub-item records under a parent record), version, createdBy/createdTime, lastEditedBy/lastEditedTime, coverUrl, icon
record_properties	RecordProperty	EAV values: recordId × propertyId (unique) → value Json? typed per property.type (select option ids, file ids, user ids, relation handled separately in relation_links). Only rows with a value exist — sparse by construction

Design notes.

• Record = page (ADR-006). Record creation is one transaction: pages.insert (unparented, title from the title property) + database_records.insert (pageId) + record_properties rows for provided values. The record inherits page permissions (no separate record grant unless an explicit permissions row exists), comments attach via comments.recordId, versions via the page, trash via pages.trashedAt (the record's archivedAt is the database-level archive, a separate concept: archived = hidden from default views, not deleted).
• EAV over wide columns. 24 property types × user-defined columns makes a fixed-wide table impossible. EAV (record_properties) keeps the schema stable; the query engine pivots a page of records (WHERE record_id IN (…) → map) in one indexed round-trip. Filterable properties get expression indexes (§4) so server-side filtering stays fast.
• Computed properties are NOT stored per record. FORMULA/ROLLUP values are computed in packages/database-engine at query time for small sets, and event-driven precomputed into a materialized cache for large ones: record_properties rows for computed properties are written by the rollup-recompute worker (invalidated on record.updated/relation.linked of dependencies — Formula.dependencies lists the exact property ids). This keeps the 100k-record query path a simple pivot read.
• database_properties.config.relation stores {relationId, targetDatabaseId}; the canonical relation direction lives in relations — the property is the view of the relation on this database (bidirectional counterpart via relations.reversePropertyId).

1.4 Relations & Computed

Table	Model	Purpose & key columns
relations	Relation	databaseId → targetDatabaseId, cardinality (1:1 / 1:N / M:N), bidirectional, propertyId? (this side's property), reversePropertyId? (counterpart), name/targetName (display names each side sees)
relation_links	RelationLink	The m2m join: databaseId × recordId × targetDatabaseId × targetRecordId (unique tuple), plus [targetDatabaseId, targetRecordId] index for reverse lookups. 1:1/1:N are enforced in the service layer (count check on insert — the table itself is uniform M:N storage)
rollups	Rollup	relationId + targetPropertyId + aggregate (COUNT/SUM/AVERAGE/MIN/MAX/PERCENT/UNIQUE) + optional filter Json?; propertyId? = the ROLLUP property it feeds
formulas	Formula	expression (source text, kept for display/edit), ast Json (parsed AST — the evaluator's only input; never eval), returnType (inferred), dependencies String[] (property ids — drives recompute), propertyId?
database_buttons	DatabaseButton	actions Json = [{type, config}] (set_property, create_page, update_page, archive_page, duplicate_page, send_webhook, trigger_automation)

Design notes.

• Cardinality as policy, not constraint. Storing every link in one uniform join table keeps link operations identical; 1:1 ("exactly one target") and 1:N ("many on one side") are validated in RelationService.setLinks (transactional delete-then-insert with count asserts) and violated rows are impossible by construction. This also makes a later cardinality change a data check, not a migration.
• Formula safety. The source is parsed at save time by the safe parser in packages/database-engine (lexer → Pratt parser → AST with the fixed function set if/and/or/not/sum/average/min/max/dateAdd/dateBetween/now/concat/replace/contains/length
  • prop()); an invalid formula is rejected with VALIDATION_ERROR (never stored). Evaluation runs the AST only, with per-evaluation step limits (10k steps, 1 MB output) so a hostile formula cannot DoS the query path. dependencies is extracted from the AST — the recompute worker uses it, not string matching.

1.5 Charts & Dashboards

Table	Model	Purpose & key columns
charts	Chart	chartType (bar/line/area/pie/donut/number), config Json (x/y axis property, group by, aggregation, colors, legend), filter Json?, position
dashboards	Dashboard	layout Json (widget grid: [{widgetType, sourceRef, x, y, w, h, config}]), isPublic
dashboard_widgets	DashboardWidget	widgetType (table/board/chart/number/calendar/timeline/text/linked_database), sourceRef Json? (`{type: "database"

Widget data is fetched by the client through the normal query endpoints (a table widget = POST /databases/:id/query with the widget's sourceRef + filter); the server never renders widget pixels.

1.6 Comments & Notifications

Table	Model	Purpose & key columns
comments	Comment	Anchors: pageId? / blockId? / recordId? (plain ref) / selection Json? (text-selection anchor/offsets); exactly one anchor kind set. body, mentionIds String[] (user ids), pageMentionIds String[] (plain refs to mentioned pages/databases), status (open/resolved/reopened), resolvedAt/By, teamId? (mention of a team)
comment_replies	CommentReply	commentId, body, mentionIds, createdBy (flat reply list under a thread — no nested threads)
comment_reactions	CommentReaction	emoji × user on comment or reply (nullable commentId/replyId), unique [commentId, replyId, userId, emoji]
notifications	Notification	In-app feed: type (MENTION/COMMENT/ASSIGNMENT/PAGE_SHARED/TASK_ASSIGNED/DATABASE_CHANGE/AUTOMATION/INTEGRATION), channel (IN_APP/EMAIL/WEBHOOK), title/body, link Json? (deep link {type, id}), readAt
mention_notifications	MentionNotification	Per-mention delivery rows (a comment mentioning 5 users creates 5 rows) with targetType/targetId + context Json?

Design notes. Mentions are stored as id arrays on the comment row (cheap, indexed by GIN @@index only where needed — mention lookups go through mention_notifications, which has the [userId, readAt] index). The fan-out to notifications/mention_notifications happens in the notifications consumer group, never in the comment-create transaction (keeps comment creation at single-digit ms).

1.7 Files

Table	Model	Purpose & key columns
files	File	key (S3 object key, unique), name, size, mimeType, status (UPLOADING/SCANNING/READY/VIRUS_DETECTED/FAILED), metadata Json (dimensions/duration/page count/hashes), thumbnailKey/Url, scanReport Json? (ClamAV result), blockId? (attaching block), pageId? (plain ref), permissions Json? (file-level overrides), uploadedBy, deletedAt
file_versions	FileVersion	version Int, key, size, unique [fileId, version] — re-uploads keep history

The object itself lives in S3; Postgres holds metadata only. status drives the UI (skeleton until READY, "virus detected" state never served) and the pipeline worker. Downloads are never a static URL — the API mints a short-lived signed URL after a permission check (see security, §6).

1.8 Permissions & Sharing

Table	Model	Purpose & key columns
permissions	Permission	Grant rows: granteeType (user/team/role), granteeId (plain ref), resourceType (workspace/teamspace/page/database/record/file), resourceId, level (NONE/VIEW/COMMENT/EDIT/FULL_ACCESS), inheritsFrom (parent grant id for auditability of inheritance chains); unique [workspaceId, granteeType, granteeId, resourceType, resourceId]
shares	Share	Public/shared links: token (unique, 32-byte random), scope (PRIVATE/WORKSPACE/TEAM/USERS/PUBLIC), pageId?/teamId?/recordId? (plain ref), allowComments, allowDuplicate, allowSearchIndexing, passwordHash? (bcrypt), expiresAt?
templates	Template	type (workspace/database/page/team), sourceRef Json?, sourceDatabaseId? (denormalized plain ref), snapshot Json (frozen structure for instantiation — the template is a copy-on-create snapshot, not a live view), isBuiltin

Design notes.

• RBAC floor + grant rows. Workspace roles (in workspace_members) provide the default level for workspace-level resources; permissions rows add or override at any level (user/team/role × resource). The evaluation algorithm (inheritance walk + explicit-NONE veto + role floor) is in backend, §5 and is shared with the search indexer so query-time permission filtering matches the guard exactly.
• inheritsFrom records which parent grant a row was derived from when a grant is copied down (e.g. "grant Edit to Team X on this page, inherited from its parent page grant") — it's informational for the permissions UI and the permission.changed audit; evaluation never walks inheritsFrom (it walks the resource tree).
• Share is separate from Permission. A Share row is a capability token (/share/:token) with its own policy (comments/duplicate/search indexing/password/ expiry); it is not a permissions grant. Password is bcrypt-hashed; the token is random 256-bit and unique-indexed (lookup is the hot path of public pages — served with a 30 s cache, invalidated on share update).

1.9 Automation

Table	Model	Purpose & key columns
automations	Automation	status (DRAFT/ACTIVE/PAUSED/ARCHIVED), databaseId? (scope for record triggers), createdById
automation_triggers	AutomationTrigger	type (RECORD_CREATED/RECORD_UPDATED/PROPERTY_CHANGED/PAGE_CREATED/FORM_SUBMITTED/SCHEDULE/WEBHOOK/MANUAL), config Json (property + value predicates, cron expression, webhook id), position
automation_conditions	AutomationCondition	expression Json (AND/OR/NOT tree over property predicates), position
automation_actions	AutomationAction	type (UPDATE_RECORD/CREATE_PAGE/SEND_EMAIL/SEND_WEBHOOK/CREATE_NOTIFICATION/ASSIGN_USER/CHANGE_STATUS/CALL_INTEGRATION/RUN_AI_ACTION), config Json, position (execution order), retries (default 3), timeoutMs (default 30000)
automation_runs	AutomationRun	One row per run (per action step): status (PENDING/RUNNING/SUCCEEDED/FAILED/DEAD_LETTER), triggerEvent Json?, input/output/error Json?, startedAt/finishedAt, retryCount
automation_logs	AutomationLog	level (info/warn/error), message, data Json? per run

Runs are the source of truth for the history UI; a run's triggerEvent stores the envelope that fired it (replay/debug). Action executors live in the worker and call the same application services as the REST API — automations get identical permission checks (run as the automation's creator) and audit rows.

1.10 Integrations / API / Webhooks

Table	Model	Purpose & key columns
integrations	Integration	provider (slack/github/custom…), config Json (envelope-encrypted connection tokens — see security, §8), status, createdById
api_keys	ApiKey	keyHash (SHA-256, unique — raw key fk_live_… shown once at creation), prefix (first 12 chars, for identification in UI/logs), scopes (READ/WRITE/ADMIN array), rateLimit? (per-key override), lastUsedAt, expiresAt?, revokedAt?
webhooks	Webhook	url, secretHash (HMAC signing secret, hashed — the raw secret is returned once at creation; deliveries are signed with a server-side copy kept in the encrypted config), events String[] (subscribed event types), active
webhook_deliveries	WebhookDelivery	event, payload Json (full envelope, retained for replay UI), status (PENDING/DELIVERED/FAILED/REJECTED), attempts, statusCode, error, deliveredAt, nextRetryAt — indexed [webhookId, status] and [status, nextRetryAt] (the worker polls the retry set)

1.11 Audit

Table	Model	Purpose & key columns
audit_logs	AuditLog	Append-only. actorType (USER/API_KEY/SYSTEM/INTEGRATION), actorId, workspaceId?, pageId?, resourceType, resourceId, action (e.g. page.created, permission.changed, user.login), ipAddress, userAgent, metadata Json (before/after diffs), createdAt

Indexes: [workspaceId, action, createdAt] (audit viewer filter), [resourceType, resourceId, createdAt] (resource history), [actorType, actorId, createdAt] (actor history). Written in-transaction with the audited mutation where possible (backend, §9).

1.12 Search

Table	Model	Purpose & key columns
search_documents	SearchDocument	Mirror table of what is indexed in OpenSearch: type (page/block/database/record/comment/file/user), refId (plain ref), title, body (extracted text), permissions Json (visibility snapshot for query-time permission filtering — the same grant evaluation the guard uses, stamped at index time), metadata Json, indexedAt; unique [type, refId]

Postgres is the source of truth; OpenSearch is a derived cache. search_documents exists for: (1) reconciliation (compare indexedAt/hash against source rows to find drift), (2) repair (re-emit indexing events from here without touching source tables), (3) permission audit (what visibility was stamped when). Full pipeline: search.

1.13 AI

Table	Model	Purpose & key columns
ai_conversations	AiConversation	Chat threads: workspaceId, userId, title, context Json? ({pageId, blockId, databaseId, …})
ai_messages	AiMessage	role (user/assistant/system/tool), content, toolCalls Json?, toolResults Json?, cost observability: model, tokensIn, tokensOut, latencyMs
ai_embeddings	AiEmbedding	Bookkeeping for vectors (the vectors themselves live in OpenSearch kNN): chunkRef ({type, id} plain ref — page/block/record), chunkText (stored for re-embed and display), vectorId (OpenSearch doc id), model, dimension, metadata Json; unique [workspaceId, chunkRef]

1.14 Forms

Table	Model	Purpose & key columns
forms	Form	databaseId (submissions create records here), slug (unique — public URL /forms/:slug), status (DRAFT/PUBLISHED/CLOSED), config Json (branding, intro, success/closed messages)
form_questions	FormQuestion	type (SHORT_TEXT/LONG_TEXT/NUMBER/EMAIL/DATE/CHECKBOX/SINGLE_SELECT/MULTI_SELECT/RATING/FILE), required, validation Json? (min/max/pattern/options), propertyId? (maps to a database property), position fractional
form_submissions	FormSubmission	recordId? (the created record — plain ref to database_records), answers Json ({questionId: value}), submittedBy? (NULL = anonymous public), ipAddress

A public submission is the one write path that works without authentication: the API validates the form is PUBLISHED, validates answers against each question's Zod schema (from @factory/shared), creates the record with system actor + the submitter's email where present, and emits form.submitted (drives automations + notifications).

---

2. Key Design Decisions (deep dive)

2.1 Fractional positions (ADR-002)

Every ordered sibling list — blocks, pages, favorites, properties, records, views, questions, widgets, teams — stores position TEXT with a LexoRank-style value:

• Alphabet: a0 … aZ (36 chars), values are base-36-ish strings like "a0", "a0V", "a1". Midpoint: mid(before, after) = next string strictly between; if the gap shrinks below 32 chars, the affected sibling range is re-batched (deterministic renumbering "a0", "a1", …) — re-batch is a background op, rare.
• Move cost is O(1) rows (one UPDATE), regardless of list size. Two concurrent moves into the same gap produce distinct midpoints (different client clocks/ids) — under CRDT the Yjs doc is authoritative; at REST level a CONFLICT (version bump) triggers client rebase.
• Storage: position TEXT (never a numeric index). Comparison is byte-wise (TEXT with default C-compatible collation — @@collate note: positions are generated ASCII-only so en_US vs C never diverges).
• The same algorithm lives in packages/editor/src/positions.ts (client, synchronous, for < 16 ms local moves) and packages/database-engine/src/positions.ts (server) — one spec, two builds, cross-tested with the same fixture corpus.

2.2 JSONB usage

Column	JSONB	Rationale
blocks.content / blocks.properties	✅	30 block types × type-specific shapes; a per-type table would need 30 schemas. Content is validated by BlockContentSchema (Zod discriminated union) on write; indexed fields (text for search) are extracted at index time, not JSONB-queried at read time
record_properties.value	✅	24 property types share one EAV table; value shape = f(property.type)
database_views.filter/sort/group/config	✅	View settings are user-shaped, read whole, never filtered by column
formulas.ast	✅	Parsed AST is the evaluator input; the shape is internal
automation_triggers/conditions/actions.config	✅	Per-type config; executor validates with Zod before execution
integration.config	✅ (encrypted)	Provider-specific secrets — envelope-encrypted string inside JSONB
page_versions.snapshot / templates.snapshot	✅	Frozen structures — written and read whole, never queried by field
search_documents.permissions	✅	Grant snapshot stamped at index time (array of {granteeType, granteeId, level})

Rules: (1) JSONB columns always have a Zod schema in @factory/shared (write-time validation + client types); (2) JSONB is never the target of list/filter queries on large tables (if a JSONB field becomes queryable, it graduates to a column + index); (3) GIN indexes on JSONB are the exception (none in MVP; blocks.content text search goes through OpenSearch, not jsonb_path_ops).

2.3 Record = page (ADR-006)

Consequences made concrete:

• Creation order: pages row first (its id = the record's pageId), then database_records, then record_properties — one transaction.
• Permissions: a record's effective permission = its page's effective permission (the inheritance walk in the permission engine treats record → page as one hop); an explicit permissions row with resourceType = 'record' overrides.
• Tree: record pages are unparented by default; a "sub-item" record (isSubItem = true) parents its page under the parent record's page for the sub-item tree in the record UI.
• Trash: pages.trashedAt hides the record from views (the query engine adds page.trashedAt IS NULL); record.archivedAt is the lighter "archive" (hidden from default views, restorable from the Archived filter).
• Versions/comments/sharing all work unchanged because they're page features.
• Cost: the records query joins pages (for title/trash state) — covered by the (databaseId, position) index on records + PK join on pages; the title is also denormalized into the search index and the sidebar never lists record pages directly.

2.4 Polymorphic plain-ref columns + application-level integrity

Prisma/Postgres cannot express "this string is one of N tables' ids" with FKs. We declare, per column, who validates (checked in integration tests):

Column	Candidate targets	Validator
search_documents.refId	page/block/database/record/comment/file/user by type	search indexer (reads the real row before indexing; deletes on *.deleted events)
permissions.granteeId / resourceId	user/team/role and workspace/teamspace/page/database/record/file	PermissionService (grant write validates existence + same-workspace)
shares.recordId	database_records.id	ShareService
comments.recordId, comments.pageMentionIds, comments.mentionIds	records, pages, users	CommentService (mention extraction resolves ids against real users/pages)
databases.pageId, files.pageId, automation.databaseId	pages.id, databases.id	respective services
ai_embeddings.chunkRef / vectorId	page/block/record + OpenSearch doc	ai-indexer
templates.sourceRef / sourceDatabaseId	page/database/team	TemplateService
dashboard_widgets.sourceRef	database/chart/view	DashboardService
form_submissions.recordId	database_records.id	form submission handler

Invariant (CI-tested, tests/integrity): a nightly job + a post-migration check scans plain-ref columns against their candidate tables and fails the build on orphans; deletion paths cascade/soft-delete so orphans appear only from bugs, and the scanner catches them before users do.

2.5 Multi-tenancy scoping

• Column-level: every tenant-scoped table has workspaceId (and organizationId where the entity is org-level). No tenant-scoped table lacks it (schema lint: @@index([workspaceId…]) presence checked in CI).
• Query-level: the repository layer is the only place Prisma is imported; every repository method takes workspaceId as its first argument (compile-time enforcement via interface signatures — you cannot forget the tenant because the method signature demands it). A TenantGuard populates ctx.workspaceId on every request; services pass ctx.workspaceId into repositories.
• Cross-tenant reads are therefore structurally impossible without a deliberate interface change; the integration suite includes a "tenant A can never see tenant B" property test (random ops across two workspaces, assert zero cross-reads).
• Isolation beyond columns: no shared global tables hold tenant data (users is global identity, intentionally — sessions/tokens reference users, not workspaces, which is what multi-workspace login requires).

---

3. Index Strategy

3.1 Index principles

1. Every hot read path is covered by a composite index in query order (equality columns first, range/order column last).
2. Fractional-position orderings are the trailing column of their composite ((parent, position)).
3. Soft-delete columns join the composite only where the live-filter is universal (deletedAt on pages/files); otherwise trashed rows are few and the filter is cheap.
4. Unique constraints double as indexes (@@unique) — identity lookups use them.
5. No speculative indexes: each index below traces to a named query (tracked in docs/database/indexes.md as they ship).

3.2 Index inventory (from the schema, with query rationale)

Table	Index	Serves
pages	(workspaceId, parentId, position)	page tree: "children of X in order" (sidebar, canvas, import)
pages	(workspaceId, deletedAt)	workspace trash listing / purge job
pages	(teamId)	teamspace page lists
blocks	(pageId, parentId, position)	the block tree read (canvas, Yjs hydration, export) — range scan over siblings
blocks	(workspaceId, pageId)	tenant-scoped block ops (delete-all on page delete, search hydration)
database_records	(workspaceId, databaseId, position)	the records read: page of rows in order (table/board views)
database_records	(databaseId, archivedAt)	default view filter (unarchived rows)
database_properties	(databaseId, position)	column order in table header
database_views	(databaseId, position)	view tabs order
relation_links	(databaseId, recordId, targetDatabaseId, targetRecordId) unique	link identity + "links of record R"
relation_links	(targetDatabaseId, targetRecordId)	reverse lookup ("who links to T") — the m2m inverse
comments	(workspaceId, pageId) / (workspaceId, blockId) / (workspaceId, recordId)	comment threads per anchor
notifications	(userId, readAt)	inbox (unread first = readAt IS NULL prefix)
mention_notifications	(userId, readAt)	mentions tab
files	(workspaceId, deletedAt)	file browser
api_keys	(workspaceId)	key listing; keyHash unique = auth lookup
webhook_deliveries	(webhookId, status)	delivery log per hook; (status, nextRetryAt)
automation_runs	(workspaceId, automationId, createdAt)	run history; (status)
audit_logs	(workspaceId, action, createdAt)	audit viewer
search_documents	(type, refId) unique	indexer upsert; (workspaceId, type)
ai_embeddings	(workspaceId, chunkRef) unique	re-embed idempotency
form_submissions	(workspaceId, formId, createdAt)	responses view
sessions	(userId, status)	session list for "active devices"

3.3 Expression indexes (MVP+1, gated on query profiles)

• Filterable property values (the EAV pain point): for properties a workspace marks "filterable" (select/status/date/number — the ones users actually filter on), the indexer maintains:

  CREATE INDEX record_properties_select_idx
    ON record_properties (property_id, (value->>'optionId'))   -- selects/status
  CREATE INDEX record_properties_date_idx
    ON record_properties (property_id, (value->>'start'))      -- dates
  CREATE INDEX record_properties_number_idx
    ON record_properties (property_id, (value->>'n')::float8)  -- numbers
  

  (One combined expression index per property type family; the query engine rewrites filter: {property, op, value} to the matching indexed expression. This is what makes 100k-record filtered queries hit an index instead of a sequential pivot.)

• blocks.content->>'text' is not indexed — full-text search is OpenSearch's job; Postgres only serves exact block fetches by id/parent.

---

4. Performance Targets & Query Budgets

Targets from PRODUCT-SPEC §26 and the Phase 15 load tests:

Scenario	Target	How it's met
Page open (metadata + first block window)	< 300 ms API p95	pages PK + (pageId, parentId, position) range for top-level blocks; blocks beyond the first 200 are fetched as the canvas scrolls (cursor over position)
100k-block page tree load	< 2 s total (virtualized)	metadata-only query (id/type/position/title, no content payload) for the tree; content lazy per viewport window; OpenSearch never on this path
100k-record database, filtered page of 100	< 300 ms	(databaseId, archivedAt) + expression indexes on filtered properties; pivot record_properties in one IN (100 record ids) batch; computed values from the precomputed cache
Record page open	< 300 ms	record PK → page PK (1:1) → blocks window
Sidebar tree (whole workspace, 50k pages)	< 500 ms	cached 120 s (tree:{wid}:{userId}); cold: 2-level lazy load
Search	< 500 ms p95	OpenSearch (see search, §6)
Realtime persist (debounced)	< 200 ms per flush of ≤ 500 block ops	batched INSERT … ON CONFLICT (bulk upsert) per page, single tx, no per-block round-trip
Audit write	< 5 ms added to the audited op	single insert in-transaction
Connection pool	20/api replica (default)	PgBouncer (transaction mode) in production; Prisma connection_limit tuned per pod; pool exhaustion → 503 SEARCH_UNAVAILABLE-class degradation, never hangs

Capacity model (planning):

Table	Rows @ 1 workspace (medium)	Rows @ platform (10k workspaces)	Notes
blocks	500k	5B	largest table → partitioned (§5)
database_records	1M	10B	second largest → partition candidate
record_properties	10M (≈10 values/record)	100B	sparse EAV; pruned with records
audit_logs	5M/yr	50B/yr	append-only → partitioned (§5)
blocks versions (block_versions)	5M	50B	partition with blocks or TTL-purged by retention

---

5. Future Partitioning Plan

Partitioning is deferred until the tables above their planning rows (premature partitioning hurts: bigger catalog, no local PKs, Prisma raw-SQL for writes). The plan, ready to execute as a migration sequence:

5.1 blocks (range by workspace_id hash, then time)

Phase A (≈ 500M rows): do nothing — composite indexes above suffice.
Phase B:  CREATE TABLE blocks_part (LIKE blocks INCLUDING ALL) PARTITION BY HASH (workspace_id);
          CREATE 64 hash partitions;  -- co-locates a workspace's blocks (tenant ops stay local)
Phase C:  each partition is itself RANGE-partitioned by month on created_at
          (two-level partitioning); old month partitions → DETACH + COPY to cold storage
          (S3-backed PG or archived table) per retention policy.

Prisma stays the write path (it writes to the parent table; PG routes to the leaf). Migrations that add columns must ADD PARTITION for every new leaf — a CI check partitions the migration template. The (pageId, parentId, position) index becomes a partition-local index (each leaf holds it) — page reads never cross partitions because pageId implies workspaceId.

5.2 audit_logs (range by month)

audit_logs PARTITION BY RANGE (created_at);
monthly partitions; DETACH + drop after retention (or per org policy HOLD);
"viewer" queries always filter created_at range → partition pruning;
resource-history queries ([resourceType, resourceId, createdAt]) are bounded by the
UI's time filter (max 1 year window) → ≤ 12 partitions scanned.

Audit is append-only and time-bounded by definition — the ideal partitioning citizen; it ships in Phase 15 (production hardening) alongside the retention jobs.

5.3 database_records + record_properties (postponed)

Partition by database_id hash only if a single workspace exceeds 10M records; otherwise the (databaseId, …) composites keep scans local. record_properties partitions with its parent (same hash key on record_id's database) to keep the pivot join intra-partition.

5.4 Migration discipline

• Partition conversions are expanding migrations: build the partitioned twin, pg_repack-style backfill in batches, swap via rename in a maintenance window (< 5 min cutover for 100 GB), keep the old table 7 days.
• No in-place ALTER TABLE … PARTITION (Postgres can't) — twins + swap only.
• Every partitioning migration is paired with the index-inventory update in §3 and a tests/performance scenario proving the hot queries didn't regress.

---

6. Migrations, Seeds, Data Safety

• Migrations are the only schema change path (prisma migrate dev / migrate deploy); the CI contract runs migrate deploy against a fresh container + migrate diff against the last release to prove drift-free deploys.
• Expand-and-contract for breaking column changes: add new column (m1) → backfill + dual-write (code) → switch reads (code) → drop old column (m2, next release).
• Destructive changes require a data-safety review: column drops, type changes, FK changes — each must state the rollback and the affected data; AGENTS.md rule 15.
• 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).