Database Architecture — AI Software Factory

Authoritative model reference. Every model, field, constraint, index, and enum in prisma/schema.prisma is documented here. Schema changes: edit the Prisma schema, migrate (development-guide §4.4), then update this document in the same PR (coding-standards DOC1). Requirements mapping: functional-requirements.md; permissions semantics: security-architecture §4.

---

1. Conventions (repeated from the schema header — binding)

1. Tenancy: every tenant-scoped table carries workspaceId (plus organizationId where relevant). All queries are tenant-scoped in the repository layer (coding-standards §2.2); there is no client-supplied workspace bypass.
2. Ordering: fractional positions (LexoRank-style strings) for pages, blocks, properties, records, views, teams, favorites, form questions, dashboard widgets, charts. Array indexes are never persisted as ordering (§7 explains the algorithm).
3. JSONB: flexible payloads — block content/properties, property config/default/value, view filter/sort/group/config, formula ast, button actions, automation config/expression, chart/dashboard layout, search permissions snapshot, org policy, AI metadata. JSONB shape contracts are Zod schemas in packages/shared (V4) — the DB stores what the schema accepts.
4. Records are pages: database_records.pageId is a 1:1 join to pages.id (§8.1).
5. Polymorphic plain-ref columns: SearchDocument.refId, Permission.resourceId (+resourceType), Share.recordId, Comment.recordId/pageMentionIds, File.pageId, Template.sourceRef, Chart/DashboardWidget.sourceRef, FormSubmission.recordId (FK), AiEmbedding.chunkRef, Automation.databaseId — plain strings; referential integrity is enforced by the application (Prisma cannot model multi-way FKs on shared columns). Where a typed FK exists (e.g. Comment.pageId), it is used; plain refs are reserved for the genuinely polymorphic cases listed in §10.3.
6. Ids: cuid (@default(cuid())); some seeds use prefixed literal ids (demo-db-*) — prefixes are display/telemetry only, never logic (coding-standards TS7).
7. Soft delete: deletedAt/trashedAt/archivedAt/revokedAt patterns per entity (§9 lifecycle). Hard delete only by purge jobs.
8. Concurrency: version Int optimistic-concurrency columns on pages, blocks, database_records — write paths updateMany with version = expected and surface CONFLICT on 0 rows affected.

2. Model map by group

Group	Tables
Identity & tenancy	users, refresh_tokens, sessions, email_verifications, password_resets, organizations, organization_members, sso_providers, sso_identities, retention_policies, workspaces, workspace_members, invitations, teams, team_members
Pages & blocks	pages, page_favorites, page_versions, blocks, block_versions
Databases	databases, database_properties, database_views, database_records, record_properties
Relations / computed / buttons	relations, relation_links, rollups, formulas, database_buttons
Charts & dashboards	charts, dashboards, dashboard_widgets
Comments & notifications	comments, comment_replies, comment_reactions, notifications, mention_notifications
Files	files, file_versions
Permissions & sharing	permissions, shares
Templates	templates
Automation	automations, automation_triggers, automation_conditions, automation_actions, automation_runs, automation_logs
Integrations / API / webhooks	integrations, api_keys, webhooks, webhook_deliveries
Audit	audit_logs
Search	search_documents
AI	ai_conversations, ai_messages, ai_embeddings
Forms	forms, form_questions, form_submissions

Total: 61 tables, 30 enums (below, §6).

3. Field reference

Column legend: T = type, C = constraints, N = notes. ? suffix = nullable. "FK → x" = foreign key. JSONB shapes cited by their shared schema name.

3.1 Identity & tenancy

users — the account. Platform-global (a user belongs to many workspaces).

Field	T	C	N
id	String	PK, cuid
email	String	unique, lowercase-normalized on write	login id; verified via emailVerification
emailVerifiedAt	DateTime?		set when VERIFIED
emailVerification	EmailVerificationStatus	default UNVERIFIED	UNVERIFIED→PENDING (token issued)→VERIFIED
passwordHash	String?	nullable	nullable because SSO-only orgs can have no password (PR-011); bcrypt cost from BCRYPT_ROUNDS
name	String		legal/real name (used in audit)
displayName	String?		shown in UI; falls back to name
avatarUrl	String?		file id or URL (pipeline-processed)
status	UserStatus	default ACTIVE	ACTIVE / INVITED (SSO/invite, no login yet) / SUSPENDED (SCIM deactivate, admin)
locale	String	default en-US	BCP-47; drives Intl formatting (NFR-I2)
timezone	String	default UTC	IANA name; date rendering (NFR-I2)
theme	String	default system	light/dark/system (PR-164)
lastLoginAt	DateTime?
createdAt / updatedAt	DateTime	default now / @updatedAt

refresh_tokens — opaque rotating refresh credentials.

Field	T	C	N
id	String	PK, cuid
tokenHash	String	unique	SHA-256 of the opaque token; plaintext never stored
userId	String	FK → users, cascade
sessionAgent	String?		browser/device UA fingerprint (display in session list)
createdAt	DateTime	default now
expiresAt	DateTime		createdAt + REFRESH_TOKEN_TTL_DAYS (30 d)
revokedAt	DateTime?		set on logout/rotation
replacedBy	String?	plain ref → refresh_tokens.id	rotation chain: the token that replaced this one; replay detection = a revoked token is presented again → revoke the whole family (FR-PR-004)

sessions — the device/session record (distinct from refresh tokens: a session groups one login episode; revoking a session revokes its tokens).

Field	T	C	N
id	String	PK, cuid
userId	String	FK → users, cascade
tokenHash	String	unique	hash of the session id token (used by DELETE /auth/sessions/:id)
userAgent	String?		parsed UA (browser, OS, device)
ipAddress	String?		last-seen IP (stored as text; not indexed — query via audit)
status	SessionStatus	default ACTIVE	ACTIVE / REVOKED / EXPIRED (job)
createdAt	DateTime	default now
expiresAt	DateTime		sliding 30 d
revokedAt	DateTime?
index		[userId, status]	session listing

email_verifications

Field	T	C	N
id	String	PK, cuid
userId	String	(index [userId]; no FK — user deletion orphans are pruned with tokens)
tokenHash	String	unique	single-use
expiresAt	DateTime		24 h
createdAt	DateTime	default now

password_resets

Field	T	C	N
id	String	PK, cuid
userId	String	FK → users, cascade
tokenHash	String	unique	single-use
status	PasswordResetStatus	default PENDING	PENDING → USED (consumed) / EXPIRED (job) / REVOKED (new request supersedes)
expiresAt	DateTime		1 h
usedAt	DateTime?
createdAt	DateTime	default now
index		[userId]

organizations

Field	T	C	N
id	String	PK, cuid
name	String
slug	String	unique
logoUrl	String?
ownerId	String	FK → users	the founding user (OrgOwner relation)
policy	JSONB	default {}	org policies: sso.enforce, sharing.publicAllowed, password.minLength, retention.* (Zod OrgPolicySchema) — PR-155
ssoConfig	JSONB?		legacy pointer; live config is sso_providers
scimConfig	JSONB?		SCIM bearer-token hash + base path + enabled flag
createdAt / updatedAt	DateTime

organization_members

Field	T	C	N
id	String	PK, cuid
organizationId	String	FK → organizations, cascade
userId	String	FK → users, cascade
role	WorkspaceRole	default MEMBER	reuses the workspace role enum (org-level semantics per product-requirements §4)
createdAt	DateTime	default now
unique		[organizationId, userId]

sso_providers (PR-011)

Field	T	C	N
id	String	PK, cuid
organizationId	String	unique, FK → organizations cascade	one provider per org (v1; multiple post-v1)
type	SSOProviderType		SAML / OIDC
name	String		display name
config	JSONB		provider config: OIDC {issuer, clientId, clientSecretRef, scopes, redirectUri} / SAML {entityId, ssoUrl, certificateRef, nameIdFormat, acsUrl}; secrets by reference to the secret store, never inline (security §11)
enforce	Boolean	default false	true → password login blocked for the org
createdAt / updatedAt	DateTime

sso_identities (PR-012)

Field	T	C	N
id	String	PK, cuid
providerId	String	FK → sso_providers, cascade
externalId	String		provider's subject (SAML NameID / OIDC sub)
userId	String	(no FK — plain ref; user deletion handled by session revocation + GC)
createdAt	DateTime	default now
unique		[providerId, externalId]	one identity link per (provider, subject)

retention_policies (PR-156)

Field	T	C	N
id	String	PK, cuid
organizationId	String	FK → organizations, cascade
type	RetentionPolicyType		PURGE (auto-delete after period) / HOLD (litigation hold: block purge)
scope	String		"comments" | "trash" | "audit_logs" | "files" | "notifications"
retainDays	Int?		null = keep forever (or default platform retention, NFR-P3)
config	JSONB	default {}	e.g. {exceptShared: true}
createdAt / updatedAt	DateTime

workspaces — the tenant root.

Field	T	C	N
id	String	PK, cuid
organizationId	String?	FK → organizations, set-null	personal workspaces have none
name	String
slug	String	unique (platform-wide)	URL segment /w/<slug>
iconUrl	String?
logoUrl	String?		public-share branding
description	String?
publicUrl	String?		custom domain for public shares (https://acme.factory.app)
settings	JSONB	default {}	ai.provider (per-workspace AI, PR-141), onboarding, UI prefs
isPersonal	Boolean	default false	true = auto-created at registration (PR-013); delete restricted
ownerId	String?	FK → users, set-null	null after owner leaves/dies (workspace transferable)
billingPlan	String	default free	free/pro/team/enterprise (string, plan catalog in shared)
usageLimits	JSONB	default {}	{members, storageMb, aiTokensMonthly, apiRps} — enforced (PR-024)
createdAt / updatedAt	DateTime
deletedAt	DateTime?		soft delete; 30 d → purge job
index		[organizationId]

workspace_members

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
userId	String	FK → users, cascade
role	WorkspaceRole	default MEMBER	OWNER/ADMIN/MEMBER/GUEST (product-req §4 matrix)
invitedBy	String?	plain ref → users.id
joinedAt	DateTime?		null until invitation accepted
createdAt / updatedAt	DateTime
unique		[workspaceId, userId]
index		[userId]	"my workspaces" query

invitations

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
email	String	lowercase	invitee
role	WorkspaceRole	default MEMBER	role granted on accept
invitedById	String	(plain ref → users.id)
tokenHash	String	unique	email link token (hashed)
status	InvitationStatus	default PENDING	PENDING / ACCEPTED / DECLINED / REVOKED / EXPIRED (job)
expiresAt	DateTime		≤ 30 d from creation
acceptedAt	DateTime?
revokedAt	DateTime?
createdAt / updatedAt	DateTime
unique		[workspaceId, email, role]	one pending invite per (ws, email, role)
index		[workspaceId, status]

teams (teamspaces)

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
name	String
slug	String		unique within workspace (see unique below)
description	String?
color	String?		token color name (blue, …)
iconUrl	String?
position	String	default a0	fractional order in sidebar
isPrivate	Boolean	default false	private team: only members see it
createdAt / updatedAt	DateTime
unique		[workspaceId, slug]

team_members

Field	T	C	N
id	String	PK, cuid
teamId	String	FK → teams, cascade
workspaceMemberId	String	FK → workspace_members, cascade	the membership row this team membership extends (denormalized join so a user leaving the workspace loses team membership)
userId	String	(denormalized, no FK)	for cheap queries; kept in sync by the service
role	WorkspaceRole	default MEMBER	team-level role; ceilinged by the workspace role
createdAt	DateTime	default now
unique		[teamId, userId]

3.2 Pages & blocks

pages — the content container (PS§3). Also the record-page half of the record=page join (§8.1).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade	tenant root — every page query scopes this
teamId	String?	FK → teams, set-null	team membership (null = workspace-level)
parentId	String?	self-FK (PageTree), set-null	nesting; null = root of the tree
grandparentId	String?	plain self-ref	denormalized parent-of-parent: makes "list a subtree of depth 2" a single index seek without a join (sidebar lazy load, FR-PR-033)
position	String	default a0	fractional sibling order (§7)
title	String	default ""	≤ 200 (app-enforced); empty = "Untitled"
icon	String?		emoji or file id (discriminated in the UI; schema stays string)
coverUrl	String?		file id or URL
description	String?		≤ 500
isPrivate	Boolean	default false	PR-032
isFavorite	Boolean	default false	denormalized favorite flag (denormalized for sidebar speed; the source of truth for ordering is page_favorites)
archivedAt	DateTime?		PR-029 (mutually exclusive with trashedAt at the service layer)
deletedAt	DateTime?		hard-delete marker (purge) — distinct from trashedAt: trashed = user-visible trash; deletedAt = pre-purge tombstone used by GC
trashedAt	DateTime?		trash entry (PR-028); restore clears it
restoredAt	DateTime?		last restore time (audit-friendly)
createdBy	String?	plain ref → users.id
updatedBy	String?	plain ref → users.id	last writer (coarse; fine-grained = audit)
version	Int	default 1	optimistic concurrency (§1.8)
createdAt / updatedAt	DateTime
index		[workspaceId, parentId, position]	the sibling-list query (covers tree rendering)
index		[workspaceId, deletedAt]	trash listing / purge job
index		[teamId]	team tree

page_favorites

Field	T	C	N
id	String	PK, cuid
workspaceId	String	(denormalized tenant scope; no FK)
userId	String	FK → users, cascade
pageId	String	FK → pages, cascade
position	String	default a0	user-controlled order in the Favorites section
createdAt	DateTime	default now
unique		[workspaceId, userId, pageId]

page_versions (PR-031)

Field	T	C	N
id	String	PK, cuid
pageId	String	FK → pages, cascade
version	Int		monotonically increasing per page
snapshot	JSONB		{title, icon, coverUrl, description, blocks: BlockDTO[]} — full page+block-tree snapshot (Zod PageVersionSchema)
createdBy	String?	FK → users, set-null
note	String?		user-provided ("before Q3 restructure")
createdAt	DateTime	default now
unique		[pageId, version]
retention			max 100/page (oldest dropped by the version service)

blocks — the editor's unit (PS§4). See §8.2 for the tree invariants.

Field	T	C	N
id	String	PK, cuid	stable; deep links #<id>
pageId	String	FK → pages, cascade	owning page
workspaceId	String	(denormalized, no FK)	tenant scope without a join (block queries always have it)
parentId	String?	self-FK, no enforced cascade (blocks are managed by the service; page delete cascades via pageId)	null = top-level block of the page
type	BlockType		30-value enum (§6); content shape depends on type
position	String	default a0	fractional sibling order (§7) — never an index
content	JSONB	default {}	type-specific payload (Zod BlockContentSchema.<type>): {text} for text blocks, {fileId, alt, caption} for image, {code, language} for code, {databaseId} for database, …
properties	JSONB	default {}	presentation + state: {align, color, background, width, checked, open, level, showLineNumbers, options…}
isSynced	Boolean	default false	true for SYNCED_BLOCK copies
syncedSourceId	String?	plain ref → blocks.id	the master block (null on masters)
createdBy / updatedBy	String?	plain refs → users.id
deletedAt	DateTime?		soft delete (undo window + GC)
version	Int	default 1	optimistic concurrency for batch block ops
createdAt / updatedAt	DateTime
index		[pageId, parentId, position]	the child-list query (page render)
index		[workspaceId, pageId]	page bulk ops (delete, snapshot, export)

block_versions

Field	T	C	N
id	String	PK, cuid
blockId	String	FK → blocks, cascade
version	Int		per-block counter
content	JSONB		snapshot
properties	JSONB		snapshot
createdBy	String?	FK → users, set-null
createdAt	DateTime	default now
unique		[blockId, version]
retention			capped per block (50); created on explicit "save version" and structural edits

3.3 Databases

databases

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
pageId	String?	plain ref → pages.id	the page whose body holds the database block (a database can be created without a page — standalone; the block's content.databaseId is the back-link). Plain (not FK) because a database may outlive its page (page trashed, database unlinked)
title	String	default ""
icon	String?		emoji or file id
description	String?
schemaVersion	Int	default 1	bumped on property schema changes (migration-safe clients)
isArchived	Boolean	default false
createdBy	String?	plain ref → users.id
createdAt / updatedAt	DateTime
index		[workspaceId]

database_properties — the typed schema (22 types, FR-PR-058).

Field	T	C	N
id	String	PK, cuid	referenced by views' filter/sort/group, record values, relations/rollups/formulas
databaseId	String	FK → databases, cascade
name	String		unique within the database (below)
type	PropertyType		enum (§6)
position	String	default a0	fractional column order
isTitle	Boolean	default false	exactly one per database (service-enforced; TITLE type)
isVisible	Boolean	default true	default visibility in views
required	Boolean	default false	record validation
config	JSONB	default {}	per type: select/multi/status {options:[{id,name,color}]}; number {format, precision, prefix, suffix, currency}; relation {relationId, multiple}; rollup {rollupId}; formula {formulaId}; button {buttonId}; unique_id {format, prefix} (Zod PropertyConfigSchema)
default	JSONB?		default value (new records)
deletedAt	DateTime?		soft delete — property disappears from schema, existing record_properties values stay until records are purged (re-adding a same-name property does not resurrect old values)
createdAt / updatedAt	DateTime
unique		[databaseId, name]
index		[databaseId, position]	schema render

database_views (9 types, FR-PR-062…070).

Field	T	C	N
id	String	PK, cuid
databaseId	String	FK → databases, cascade
name	String
type	ViewType		TABLE/LIST/BOARD/GALLERY/CALENDAR/TIMELINE/CHART/DASHBOARD/FORM
position	String	default a0	tab order
config	JSONB	default {}	{visibleProperties: PropertyId[], columnWidths: Record<PropertyId, number>, cardLayout: {cover, size, propertyRows}, color, density, xProperty, dateProperty} (Zod ViewConfigSchema)
filter	JSONB?		nested AND/OR/NOT tree (Zod FilterTreeSchema) — FR-PR-071
sort	JSONB?		`[{propertyId, direction: ASC
group	JSONB?		[{propertyId, direction}] (≤ 3)
pageSize	Int?		pagination for the query API (default 100)
createdAt / updatedAt	DateTime
index		[databaseId, position]

database_records — each row is a page (§8.1).

Field	T	C	N
id	String	PK, cuid	the "record id" in APIs
workspaceId	String	(denormalized, no FK)	tenant scope
databaseId	String	FK → databases, cascade
pageId	String?	unique, FK → pages, set-null	the 1:1 join; null only in the brief window of a failed/rolled-back creation or after the page was purged while the record row lingers for GC
coverUrl	String?		record cover (gallery/board)
icon	String?
archivedAt	DateTime?
isSubItem	Boolean	default false	sub-item (dependency tracking, Phase 6: tasks-within-tasks)
position	String	default a0	ordering inside table/board views (drag)
version	Int	default 1	optimistic concurrency (cell edits)
createdBy	String?	plain ref → users.id	feeds the CREATED_BY property
createdTime	DateTime	default now	feeds CREATED_TIME
lastEditedBy	String?	plain ref → users.id	feeds LAST_EDITED_BY; updated by the record-update path
lastEditedTime	DateTime	default now	feeds LAST_EDITED_TIME
createdAt / updatedAt	DateTime
index		[workspaceId, databaseId, position]	the view-row query (table/board order)
index		[databaseId, archivedAt]	active-records scan / archive jobs

record_properties — EAV value store.

Field	T	C	N
id	String	PK, cuid
recordId	String	FK → database_records, cascade
propertyId	String	FK → database_properties, cascade
value	JSONB?		typed per property type (functional §5.1 value column): string / number / bool / ISO date / {start,end} / option id / option id[] / user id / user id[] / file id[] / record id[] (relations are not here — they live in relation_links)
createdAt / updatedAt	DateTime
unique		[recordId, propertyId]	one value per (record, property)

Why EAV instead of JSONB-per-record: typed property columns get real rows → per-property indexes are possible at scale, record.updated events can name the property, rollups/formula dependency tracking is a join, and property delete is a cascade. Cost: multi-property reads are a pivot (one query, IN on property ids) — fine because a view renders a handful of properties at a time.

3.4 Relations, rollups, formulas, buttons

relations (FR-PR-075).

Field	T	C	N
id	String	PK, cuid	referenced by database_properties.config.relationId (both sides)
databaseId	String	FK → databases, cascade	the "source" database
targetDatabaseId	String	(plain ref → databases.id; same-workspace enforced by service)	cross-database links are within-workspace only (tenant invariant)
propertyId	String?	plain ref → database_properties.id	the RELATION property on databaseId
reversePropertyId	String?	plain ref → database_properties.id	the RELATION property on targetDatabaseId (set when bidirectional)
name	String		label on the source side
targetName	String?		label on the target side
cardinality	RelationCardinality	default MANY_TO_MANY	ONE_TO_ONE / ONE_TO_MANY / MANY_TO_MANY
bidirectional	Boolean	default true	false = one-way link (reverse column hidden)
createdAt / updatedAt	DateTime
index		[databaseId], [targetDatabaseId]	both-direction scans

relation_links — the m2m join table (works for all cardinalities; O(1)/O(N) enforced at write time, §8.4).

Field	T	C	N
id	String	PK, cuid
databaseId	String	FK → databases, cascade	the source database of the relation (denormalized so "links of relation R" is one seek)
recordId	String	FK → database_records, cascade	source record
targetRecordId	String	(plain ref → database_records.id — cross-database, cannot be a Prisma FK)
targetDatabaseId	String	(denormalized, plain ref)
createdAt	DateTime	default now
unique		[databaseId, recordId, targetDatabaseId, targetRecordId]	no duplicate links
index		[targetDatabaseId, targetRecordId]	reverse scan ("what links to this record?")

rollups (FR-PR-077).

Field	T	C	N
id	String	PK, cuid	referenced by database_properties.config.rollupId
databaseId	String	FK → databases, cascade	where the ROLLUP property lives
propertyId	String?	plain ref → database_properties.id	the ROLLUP property
relationId	String	(plain ref → relations.id)	over which relation
targetPropertyId	String	(plain ref → database_properties.id on the target database)	which related property to aggregate
aggregate	RollupAggregate		COUNT/SUM/AVERAGE/MIN/MAX/PERCENT/UNIQUE
filter	JSONB?		optional FilterTree applied to the related set (e.g. PERCENT of done tasks)
createdAt / updatedAt	DateTime
index		[databaseId]

formulas (FR-PR-079…081).

Field	T	C	N
id	String	PK, cuid	referenced by database_properties.config.formulaId
databaseId	String	FK → databases, cascade
propertyId	String?	plain ref → database_properties.id	the FORMULA property
expression	String		source text (display/edit), e.g. if(prop("Status") == "Done", 100, 0)
ast	JSONB		parsed AST (Zod FormulaAstSchema) — the only thing the evaluator consumes; never eval (AGENTS.md 18)
returnType	String		inferred: number | text | boolean | date | array | error
dependencies	String[]		property ids referenced (the recompute trigger set; derived by the parser, verified by CI against the AST)
createdAt / updatedAt	DateTime
index		[databaseId]

database_buttons (FR-PR-082).

Field	T	C	N
id	String	PK, cuid	referenced by database_properties.config.buttonId
databaseId	String	FK → databases, cascade
propertyId	String?	plain ref → database_properties.id	the BUTTON property
name	String		label
icon	String?		emoji
color	String?		token color
actions	JSONB		ordered [{type, config}] (Zod ButtonActionsSchema): set_property / create_page / update_page / archive_page / duplicate_page / send_webhook / trigger_automation
createdAt / updatedAt	DateTime
index		[databaseId]

3.5 Charts & dashboards

charts (FR-PR-131).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	(denormalized, no FK)	tenant scope
databaseId	String	FK → databases, cascade	data source
name	String
chartType	String		bar | line | area | pie | donut | number (string, not enum — chart catalog is app-extensible)
config	JSONB		`{xAxis: PropertyId, yAxis: PropertyId, groupBy?: PropertyId, aggregation: COUNT
filter	JSONB?		FilterTree
position	String	default a0	order in the database's chart list
createdAt / updatedAt	DateTime
index		[workspaceId, databaseId]

dashboards (FR-PR-132).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
name	String
icon	String?
layout	JSONB	default {}	legacy/summary grid (authoritative layout is dashboard_widgets); kept for the "export layout" round-trip
isPublic	Boolean	default false	public read-only share (FR-PR-133)
createdAt / updatedAt	DateTime
index		[workspaceId]

dashboard_widgets

Field	T	C	N
id	String	PK, cuid
dashboardId	String	FK → dashboards, cascade
widgetType	String		table | board | chart | number | calendar | timeline | text | linked_database
sourceRef	JSONB?		`{type: "database"
position	String	default a0	z/reading order
layout	JSONB	default {}	{x, y, w (1–12), h} grid cell
filter	JSONB?		per-widget FilterTree (overrides the source view's)
createdAt / updatedAt	DateTime
index		[dashboardId, position]

3.6 Comments & notifications

comments (FR-PR-091).

Field	T	C	N
id	String	PK, cuid	thread id (replies reference it)
workspaceId	String	FK → workspaces, cascade
teamId	String?	FK → teams, set-null	for team-scoped notification fan-out
pageId	String?	FK → pages, cascade	typed FK when the target is a page/block
blockId	String?	FK → blocks, cascade	block-level comment; implies pageId via the block (kept both, service-ensured consistent)
recordId	String?	plain ref → database_records.id (no FK)	record comments: the record's page is the page, but comments on the record itself (properties) don't target a block; plain ref because a comment may target a record whose page row is optional mid-transaction
selection	JSONB?		{anchor: {blockId, offset}, focus: {blockId, offset}} — text-selection comment (offsets into the block's plain text)
body	String		markdown source (mentions resolved to ids on write)
mentionIds	String[]	default []	user ids (→ mention_notifications on write)
pageMentionIds	String[]	default []	plain refs to pages/databases (polymorphic: a mention can be a page or a database; discriminator stored in the markdown)
resolvedAt	DateTime?
resolvedBy	String?	plain ref → users.id
status	String	default open	open | resolved | reopened (string: the status machine is app-owned)
createdBy	String	FK → users, cascade	author
createdAt / updatedAt	DateTime
index		[workspaceId, pageId]	page comment list
index		[workspaceId, blockId]	block anchor badge
index		[workspaceId, recordId]	record comment list

comment_replies

Field	T	C	N
id	String	PK, cuid
commentId	String	FK → comments, cascade	parent thread
body	String
mentionIds	String[]	default []
createdBy	String	FK → users, cascade
createdAt / updatedAt	DateTime
index		[commentId]

comment_reactions (FR-PR-093).

Field	T	C	N
id	String	PK, cuid
emoji	String		single emoji (normalized to NFC)
userId	String	(no FK — user delete GCs via job)
commentId	String?	FK → comments, cascade	exactly one of commentId/replyId non-null (service-enforced)
replyId	String?	FK → comment_replies, cascade
createdAt	DateTime	default now
unique		[commentId, replyId, userId, emoji]	one reaction per (target, user, emoji); NULLs in the pair are distinguished by Postgres unique semantics (NULLs distinct) — the service enforces the XOR and stores a '' sentinel for the absent side to keep the unique meaningful (see §10.4 note)

notifications (FR-PR-095/096).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
userId	String	FK → users, cascade	recipient
type	NotificationType		MENTION/COMMENT/ASSIGNMENT/PAGE_SHARED/TASK_ASSIGNED/DATABASE_CHANGE/AUTOMATION/INTEGRATION
channel	NotificationChannel	default IN_APP	IN_APP / EMAIL / WEBHOOK (one row per channel sent)
title	String		short line (inbox)
body	String?
link	JSONB?		deep link `{type: "page"
readAt	DateTime?		null = unread (inbox badge = count where readAt IS NULL)
createdAt	DateTime	default now
index		[userId, readAt]	inbox (unread first)
index		[workspaceId, userId]	per-workspace inbox filter

mention_notifications (FR-PR-097 — separate read state).

Field	T	C	N
id	String	PK, cuid
userId	String	FK → users, cascade	mentioned user
mentionerId	String	(plain ref → users.id)	who mentioned
targetType	String		comment | reply | page | record
targetId	String	(plain ref)	the comment/reply/page/record id
context	JSONB?		{pageId, recordId, snippet} for the UI
readAt	DateTime?
createdAt	DateTime	default now
index		[userId, readAt]

3.7 Files

files (FR-PR-105…111).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
blockId	String?	FK → blocks, set-null	when attached to a block (image/file/video/audio block)
pageId	String?	plain ref → pages.id	page-attached files (covers, icons) that aren't blocks
name	String		user-visible name (sanitized display; not the storage key)
key	String	unique	object storage key: <workspaceId>/<fileId>/<version> — server-generated
url	String?		proxy/preview URL (never a long-lived public URL; downloads = signed URLs)
size	Int		bytes
mimeType	String		magic-byte sniff result (authoritative over extension)
status	FileStatus	default UPLOADING	UPLOADING → SCANNING → READY / VIRUS_DETECTED / FAILED (pipeline state machine)
metadata	JSONB	default {}	{dimensions?, duration?, pageCount?, sha256, title?}
thumbnailKey	String?		object key of the generated thumbnail
thumbnailUrl	String?
scanReport	JSONB?		ClamAV result {engine, signature?, scannedAt}
uploadedBy	String	(plain ref → users.id)
permissions	JSONB?		per-file permission overrides — can only restrict, never grant beyond the parent page (Zod FilePermissionsSchema); security §9
deletedAt	DateTime?		trash; objects purged after 30 d
createdAt / updatedAt	DateTime
index		[workspaceId, deletedAt]
index		[blockId]	block → file lookup

file_versions

Field	T	C	N
id	String	PK, cuid
fileId	String	FK → files, cascade
version	Int		1-based
key	String		object key of this version (…/<version>)
size	Int
createdBy	String?	FK → users, set-null
createdAt	DateTime	default now
unique		[fileId, version]

3.8 Permissions & sharing

permissions — explicit grants (FR-PR-113/114). The permission engine composes these with role baselines; see security-architecture §4.

Field	T	C	N
id	String	PK, cuid
workspaceId	String	(denormalized tenant scope)
granteeType	String		"user" | "team" | "role" (role = all members with that workspace role)
granteeId	String	plain ref (userId | teamId | role-name)	polymorphic grantee — §10.3
resourceType	String		"workspace" | "teamspace" | "page" | "database" | "record" | "file"
resourceId	String	plain ref	the resource id (matches resourceType)
level	PermissionLevel		NONE (explicit deny override!) / VIEW / COMMENT / EDIT / FULL_ACCESS
inheritsFrom	String?	plain ref → permissions.id	when a grant was created as an explicit copy of an inherited one (UI shows "inherited"; chains are shallow, ≤ 2 hops)
createdAt / updatedAt	DateTime
unique		[workspaceId, granteeType, granteeId, resourceType, resourceId]	one grant per tuple
index		[workspaceId, granteeId]	"grants visible to this user/team"
index		[resourceType, resourceId]	"grants on this resource"

shares — share links incl. public (FR-PR-117/118).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
pageId	String?	FK → pages, cascade	the shared page (typed FK)
teamId	String?	FK → teams, cascade	share a whole team
recordId	String?	plain ref → database_records.id	share a single record
token	String	unique	the URL secret: /share/<token> (32 bytes, URL-safe; shown once)
scope	ShareScope	default PRIVATE	PRIVATE / WORKSPACE / TEAM / USERS (specific users — list in metadata convention) / PUBLIC
allowComments	Boolean	default true	public-share option
allowDuplicate	Boolean	default false
allowSearchIndexing	Boolean	default false
passwordHash	String?		bcrypt of the share password (optional gate)
expiresAt	DateTime?		null = never
createdBy	String	(plain ref → users.id)
createdAt / updatedAt	DateTime
index		[workspaceId, pageId]
index		[token]	(also unique) lookup by token

3.9 Templates

templates (FR-PR-121…123).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
name	String
icon	String?
type	String		"workspace" | "database" | "page" | "team"
sourceRef	JSONB?		{type, id} of the template source (plain refs inside)
sourceDatabaseId	String?	plain ref → databases.id	denormalized for database templates (cheap picker queries)
snapshot	JSONB		frozen structure (Zod TemplateSnapshotSchema): for page templates {title, icon, blocks[]}; for database templates {title, properties[], views[], records[]} — instantiation maps ids fresh
isBuiltin	Boolean	default false	builtin (seed) templates
createdBy	String?	plain ref → users.id
createdAt / updatedAt	DateTime
index		[workspaceId, type]	template browser

3.10 Automation

automations (FR-PR-134).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
name	String
description	String?
status	AutomationStatus	default DRAFT	DRAFT / ACTIVE / PAUSED / ARCHIVED
databaseId	String?	plain ref → databases.id	scoping for record triggers (null = workspace-wide)
createdById	String	FK → users (relation AutomationCreator)
createdAt / updatedAt	DateTime
index		[workspaceId, status]	active-scan (trigger matcher)

automation_triggers (FR-PR-135).

Field	T	C	N
id	String	PK, cuid
automationId	String	FK → automations, cascade
type	AutomationTriggerType		RECORD_CREATED / RECORD_UPDATED / PROPERTY_CHANGED / PAGE_CREATED / FORM_SUBMITTED / SCHEDULE / WEBHOOK / MANUAL
config	JSONB		per type: {databaseId, propertyId, toValue?} / {cron, timezone} / {webhookPath} / {formId} (Zod TriggerConfigSchema)
position	Int	default 0	multiple triggers: OR-semantics (any trigger fires)
index		[automationId]

automation_conditions (FR-PR-139).

Field	T	C	N
id	String	PK, cuid
automationId	String	FK → automations, cascade
expression	JSONB		AND/OR/NOT tree over `{propertyId
position	Int	default 0	multiple conditions: AND-semantics
index		[automationId]

automation_actions (FR-PR-136).

Field	T	C	N
id	String	PK, cuid
automationId	String	FK → automations, cascade
type	AutomationActionType		UPDATE_RECORD / CREATE_PAGE / SEND_EMAIL / SEND_WEBHOOK / CREATE_NOTIFICATION / ASSIGN_USER / CHANGE_STATUS / CALL_INTEGRATION / RUN_AI_ACTION
config	JSONB		per type (Zod ActionConfigSchema)
position	Int		execution order
retries	Int	default 3	per-action retry budget
timeoutMs	Int	default 30000	per-action timeout
index		[automationId, position]	ordered execution

automation_runs (FR-PR-137/138).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	(denormalized)
automationId	String	FK → automations, cascade
actionId	String?	FK → automation_actions, set-null	which action this run row tracks (a run creates one row per action attempt batch; the first row's actionId is null = the run itself)
status	AutomationRunStatus	default PENDING	PENDING / RUNNING / SUCCEEDED / FAILED / DEAD_LETTER
triggerEvent	JSONB?		the event payload that fired it (for replay/debug)
input	JSONB?		resolved action input
output	JSONB?		result
error	JSONB?		{code, message} (stack in logs, not here)
startedAt	DateTime?
finishedAt	DateTime?
retryCount	Int	default 0
createdAt	DateTime	default now
index		[workspaceId, automationId, createdAt]	history UI
index		[status]	worker scans (RUNNING recovery, DLQ review)

automation_logs

Field	T	C	N
id	String	PK, cuid
runId	String	FK → automation_runs, cascade
level	String		info | warn | error
message	String
data	JSONB?		structured context
createdAt	DateTime	default now
index		[runId]

3.11 Integrations / API / webhooks

integrations (FR-PR-136 CALL_INTEGRATION).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
name	String
provider	String		"slack" | "github" | "custom" | … (catalog in shared)
config	JSONB		encrypted at rest (envelope encryption, security §11): {tokenEnc, webhookUrl, team…} — the only table where JSONB holds secrets, and it is always ciphertext
status	String	default active	active | disconnected | error
createdById	String	(plain ref → users.id)
createdAt / updatedAt	DateTime
index		[workspaceId]

api_keys (FR-PR-149).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade	keys are workspace-scoped
name	String		human label
keyHash	String	unique	SHA-256 of the full key — only the hash is stored
prefix	String		fk_live_ab12… (first 12 chars) — shown in the UI for identification
scopes	ApiKeyScope[]		READ / WRITE / ADMIN (array, ≥ 1)
rateLimit	Int?		per-key rps override (null = plan default)
lastUsedAt	DateTime?		updated on use (throttled writes: at most 1/min per key)
expiresAt	DateTime?
revokedAt	DateTime?
createdById	String	FK → users, cascade
createdAt	DateTime	default now
index		[workspaceId]

webhooks (FR-PR-151).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
url	String		HTTPS (prod); SSRF-validated at create + dispatch
secretHash	String		SHA-256 of the per-webhook HMAC signing secret (integrity/rotation check). The raw signing secret is shown once at creation and stored envelope-encrypted, keyed by webhook id (secret store / encrypted column per deployment), because the dispatcher must compute v1 = HMAC(secret, t.body) for outbound delivery — same show-once model as API keys (security §11)
events	String[]		subscribed event types (page.created, record.updated, …)
active	Boolean	default true
createdById	String	(plain ref → users.id)
createdAt / updatedAt	DateTime
index		[workspaceId]

webhook_deliveries

Field	T	C	N
id	String	PK, cuid
webhookId	String	FK → webhooks, cascade
event	String		the event type delivered
payload	JSONB		the exact body sent (retained 90 d, NFR-P3)
status	WebhookDeliveryStatus	default PENDING	PENDING / DELIVERED / FAILED (retries exhausted) / REJECTED (4xx final)
attempts	Int	default 0
statusCode	Int?		last HTTP status
error	String?		transport error (timeout, DNS)
deliveredAt	DateTime?
nextRetryAt	DateTime?		backoff schedule (1 m → 2 h, 8 attempts, jittered)
createdAt	DateTime	default now
index		[webhookId, status]	delivery log UI
index		[status, nextRetryAt]	retry scanner (worker's main seek)

3.12 Audit

audit_logs (FR-PR-010/154; catalog in security-architecture §12).

Field	T	C	N
id	String	PK, cuid
actorType	AuditActorType		USER / API_KEY / SYSTEM (jobs) / INTEGRATION
actorId	String	plain ref (userId | apiKeyId | system:<job>)	polymorphic actor — §10.3
workspaceId	String?	FK → workspaces, set-null	null for platform-level events (login before workspace context); kept after workspace purge (forensics)
pageId	String?	FK → pages, set-null	when the event is page-centric
resourceType	String		user, workspace, page, database, record, file, permission, api_key, webhook, automation, form, sso, …
resourceId	String	plain ref
action	String		dot-notation event name (page.created, permission.changed, …) — the catalog is the contract (security §12)
ipAddress	String?
userAgent	String?
metadata	JSONB	default {}	before/after (permission changes), counts (bulk ops), extra context — no secrets/PII (NFR-P2)
createdAt	DateTime	default now	immutable (no updatedAt — audit rows are append-only)
index		[workspaceId, action, createdAt]	"what happened in ws X" (viewer)
index		[resourceType, resourceId, createdAt]	"history of this object"
index		[actorType, actorId, createdAt]	"what did this actor do"

3.13 Search

search_documents — the Postgres mirror of the OpenSearch index (FR-PR-102/103; source of truth for reconciliation).

Field	T	C	N
id	String	PK, cuid	(≠ the OpenSearch _id, which is <type>:<refId>)
workspaceId	String	FK → workspaces, cascade
type	String		page | block | database | record | comment | file | user
refId	String	plain ref → the typed table by type	polymorphic — §10.3; @@unique([type, refId]) makes (type, refId) the logical key
title	String		indexed text
body	String?		extracted plain text (blocks → text, records → property render, …)
permissions	JSONB		visibility snapshot for query-time permission filtering: {ownerUserId, teamIds[], grantUserIds[], grantTeamIds[], public, scope} (Zod SearchPermissionsSchema) — refreshed on every permission change event
metadata	JSONB	default {}	{createdBy, updatedAt, icon, databaseId?, recordId?} for result rendering + filters
indexedAt	DateTime	default now	lag measurement (SLO < 30 s)
unique		[type, refId]
index		[workspaceId, type]

3.14 AI

ai_conversations (FR-PR-142).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
userId	String	FK → users, cascade	conversations are personal
title	String?		auto after 2 turns
context	JSONB?		{pageId?, blockId?, databaseId?, selection?} — what the chat is "about"
createdAt / updatedAt	DateTime
index		[workspaceId, userId]

ai_messages

Field	T	C	N
id	String	PK, cuid
conversationId	String	FK → ai_conversations, cascade
role	String		user | assistant | system | tool
content	String		text (tool messages: the tool-call JSON as text)
toolCalls	JSONB?		[{id, name, arguments}] (Zod-validated at generation)
toolResults	JSONB?		[{id, result, ok}]
model	String?		provider model id
tokensIn	Int?		metering (NFR §7.2 AI dashboard)
tokensOut	Int?
latencyMs	Int?
createdAt	DateTime	default now
index		[conversationId]

ai_embeddings (FR-PR-145 — the chunk registry; vectors live in the vector store).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	(denormalized)
chunkRef	String	plain ref {type, id} stringified (page:pg_x:block:blk_y)	the source chunk
chunkText	String		~800-token chunk (15 % overlap) — stored so re-embedding a model change doesn't re-read content
vectorId	String		the document id in the vector store (OpenSearch kNN)
model	String		embedding model id (model-swap = re-embed)
dimension	Int		vector dimension (model property)
metadata	JSONB	default {}	{pageId, databaseId?, headings, position}
createdAt	DateTime	default now
unique		[workspaceId, chunkRef]	one vector per chunk
index		[workspaceId]

3.15 Forms

forms (FR-PR-127…130).

Field	T	C	N
id	String	PK, cuid
workspaceId	String	(denormalized; FK via database)
databaseId	String	FK → databases, cascade	the target database (submissions create records here)
name	String
slug	String	unique (platform-wide)	public URL /forms/<slug>
description	String?
status	FormStatus	default DRAFT	DRAFT / PUBLISHED / CLOSED
config	JSONB	default {}	branding: {intro, logoUrl, accentColor, successMessage, closedMessage, allowDuplicate} (Zod FormConfigSchema)
createdById	String	(plain ref → users.id)
createdAt / updatedAt	DateTime
index		[workspaceId, databaseId]

form_questions

Field	T	C	N
id	String	PK, cuid
formId	String	FK → forms, cascade
label	String
description	String?
type	FormQuestionType		SHORT_TEXT / LONG_TEXT / NUMBER / EMAIL / DATE / CHECKBOX / SINGLE_SELECT / MULTI_SELECT / RATING / FILE
required	Boolean	default false
validation	JSONB?		{min?, max?, pattern?, options?, minRating?} (Zod QuestionValidationSchema)
propertyId	String?	plain ref → database_properties.id	the record property this question writes to (required at publish for non-FILE questions)
placeholder	String?
position	String	default a0	fractional question order
createdAt / updatedAt	DateTime
index		[formId, position]

form_submissions

Field	T	C	N
id	String	PK, cuid
workspaceId	String	FK → workspaces, cascade
formId	String	FK → forms, cascade
recordId	String?	FK → database_records, set-null	the created record (set-null so deleting the record keeps the submission for audit)
answers	JSONB		{questionId: value} — the raw submission (Zod SubmissionAnswersSchema per question types)
submittedBy	String?	plain ref → users.id	null for anonymous public submissions (the only null-actor content row by design)
ipAddress	String?		rate limiting + abuse forensics
createdAt	DateTime	default now
index		[workspaceId, formId, createdAt]	responses view

4. Relationship diagrams

4.1 Identity & tenancy

erDiagram
    USER ||--o{ REFRESH_TOKEN : "rotates"
    USER ||--o{ SESSION : "has"
    USER ||--o{ EMAIL_VERIFICATION : "verifies"
    USER ||--o{ PASSWORD_RESET : "resets"
    USER ||--o{ ORGANIZATION : "owns"
    USER ||--o{ ORGANIZATION_MEMBER : "member of"
    USER ||--o{ WORKSPACE_MEMBER : "member of"
    ORGANIZATION ||--o{ ORGANIZATION_MEMBER : ""
    ORGANIZATION ||--o{ WORKSPACE : "groups"
    ORGANIZATION ||--o| SSO_PROVIDER : "configures"
    SSO_PROVIDER ||--o{ SSO_IDENTITY : "links"
    ORGANIZATION ||--o{ RETENTION_POLICY : "defines"
    WORKSPACE ||--o{ WORKSPACE_MEMBER : "has"
    WORKSPACE ||--o{ INVITATION : "sends"
    WORKSPACE ||--o{ TEAM : "contains"
    TEAM ||--o{ TEAM_MEMBER : "has"
    WORKSPACE_MEMBER ||--o{ TEAM_MEMBER : "extends"

4.2 Pages & blocks

erDiagram
    WORKSPACE ||--o{ PAGE : "contains"
    TEAM ||--o{ PAGE : "owns"
    PAGE ||--o{ PAGE : "parent (PageTree)"
    PAGE ||--o{ BLOCK : "contains"
    BLOCK ||--o{ BLOCK : "parent (children)"
    PAGE ||--o{ PAGE_FAVORITE : "favorited by"
    USER ||--o{ PAGE_FAVORITE : "favorites"
    PAGE ||--o{ PAGE_VERSION : "history"
    BLOCK ||--o{ BLOCK_VERSION : "history"
    BLOCK ||--o{ SYNCED_BLOCK : "master of (syncedSourceId, plain ref)"

4.3 Databases & computed properties

erDiagram
    WORKSPACE ||--o{ DATABASE : "contains"
    PAGE |o--o| DATABASE : "embeds (pageId plain ref, 1:1 in practice)"
    DATABASE ||--o{ DATABASE_PROPERTY : "schema"
    DATABASE ||--o{ DATABASE_VIEW : "views"
    DATABASE ||--o{ DATABASE_RECORD : "rows"
    DATABASE_RECORD |o--o| PAGE : "record IS a page (pageId unique)"
    DATABASE_RECORD ||--o{ RECORD_PROPERTY : "values (EAV)"
    DATABASE_PROPERTY ||--o{ RECORD_PROPERTY : ""
    DATABASE ||--o{ RELATION : "source of"
    DATABASE_RECORD ||--o{ RELATION_LINK : "links"
    RELATION_LINK }o--|| DATABASE : "targetDatabaseId (plain ref)"
    DATABASE ||--o{ ROLLUP : ""
    DATABASE ||--o{ FORMULA : ""
    DATABASE ||--o{ DATABASE_BUTTON : ""
    DATABASE ||--o{ CHART : ""
    DATABASE ||--o{ FORM : ""

4.4 Automation & events

erDiagram
    WORKSPACE ||--o{ AUTOMATION : "defines"
    AUTOMATION ||--o{ AUTOMATION_TRIGGER : "fires on (OR)"
    AUTOMATION ||--o{ AUTOMATION_CONDITION : "gated by (AND)"
    AUTOMATION ||--o{ AUTOMATION_ACTION : "executes (ordered)"
    AUTOMATION ||--o{ AUTOMATION_RUN : "runs"
    AUTOMATION_ACTION ||--o{ AUTOMATION_RUN : "attempted in"
    AUTOMATION_RUN ||--o{ AUTOMATION_LOG : "logs"
    WORKSPACE ||--o{ WEBHOOK : "subscribes"
    WEBHOOK ||--o{ WEBHOOK_DELIVERY : "deliveries"
    WORKSPACE ||--o{ API_KEY : "issues"
    WORKSPACE ||--o{ INTEGRATION : "connects"

5. Unique constraints & indexes (consolidated)

5.1 Uniques (integrity-critical)

Table	Unique	Protects
users	email	one account per email
workspaces / organizations	slug	platform-unique URL segment
teams	(workspaceId, slug)	team URL within workspace
workspace_members / organization_members / team_members	(ws/org/team, userId)	one membership per (scope, user)
invitations	(workspaceId, email, role)	no duplicate pending invites
refresh_tokens, sessions, email_verifications, password_resets	tokenHash	single-use credential lookup
sso_providers	organizationId	one provider per org (v1)
sso_identities	(providerId, externalId)	one identity link per subject
page_favorites	(workspaceId, userId, pageId)	no double favorite
page_versions / block_versions / file_versions	(parent, version)	version monotonicity
database_properties	(databaseId, name)	schema name integrity
record_properties	(recordId, propertyId)	EAV single-value
relation_links	(databaseId, recordId, targetDatabaseId, targetRecordId)	no duplicate links
permissions	(workspaceId, granteeType, granteeId, resourceType, resourceId)	one grant per tuple
shares	token	share URL secret
search_documents	(type, refId)	one doc per entity
api_keys	keyHash	key lookup
files	key	object key integrity
forms	slug	public form URL platform-unique
ai_embeddings	(workspaceId, chunkRef)	one vector per chunk
comment_reactions	(commentId, replyId, userId, emoji)	one reaction per (target, user, emoji) — see §10.4 null-sentinel note

5.2 Indexes (query-shape driven)

Index (table: columns)	Serves
pages: (workspaceId, parentId, position)	sibling listing — the hottest read in the app (sidebar + page render)
pages: (workspaceId, deletedAt)	trash view, purge job
blocks: (pageId, parentId, position)	child listing — page render, virtualization window
blocks: (workspaceId, pageId)	page bulk ops (delete, snapshot, export, GC)
database_records: (workspaceId, databaseId, position)	table/board row window (virtualized reads)
database_records: (databaseId, archivedAt)	active scan, archive jobs
relation_links: (targetDatabaseId, targetRecordId)	reverse relation scan ("who links to me")
database_properties: (databaseId, position)	schema render
database_views: (databaseId, position)	view tabs
comments: (workspaceId, pageId) / (workspaceId, blockId) / (workspaceId, recordId)	comment lists per target kind
permissions: (workspaceId, granteeId)	effective-grant collection (engine)
permissions: (resourceType, resourceId)	resource grant listing (share dialog)
notifications: (userId, readAt)	inbox (unread first)
mention_notifications: (userId, readAt)	mention inbox
webhook_deliveries: (status, nextRetryAt)	retry scanner seek
automation_runs: (workspaceId, automationId, createdAt)	run history
audit_logs: (workspaceId, action, createdAt) / (resourceType, resourceId, createdAt) / (actorType, actorId, createdAt)	the three audit viewer filters
search_documents: (workspaceId, type)	reconciliation scans
sessions: (userId, status)	session list
invitations: (workspaceId, status)	invitation management
files: (workspaceId, deletedAt), files: (blockId)	file listings, block→file
ai_conversations: (workspaceId, userId)	chat history
ai_messages: (conversationId)	message loading
form_submissions: (workspaceId, formId, createdAt)	responses view
automation_actions: (automationId, position)	ordered action load
automation_triggers/conditions: (automationId)	matcher load
rollups/formulas/database_buttons/relations/charts: (databaseId)	per-database computed-property load
teams: (workspaceId, slug) unique doubles as the lookup index	team by slug
teams: (teamId) on pages	team tree
workspaces: (organizationId)	org → workspaces

Missing-by-design: no index on audit_logs.workspaceId alone (the composite covers it), no index on blocks.parentId alone (child listing is always per-page), no index on JSONB (filters on JSONB fields go through packages/database-engine in SQL via record_properties pivots, not JSONB operators — at 100k records the pivot is cheaper than GIN scans; revisit in Phase 15 with real query plans, §13).

6. Enum catalog (30)

Enum	Values	Used by
WorkspaceRole	OWNER, ADMIN, MEMBER, GUEST	workspace_members, organization_members, team_members, invitations
PermissionLevel	NONE, VIEW, COMMENT, EDIT, FULL_ACCESS	permissions
ShareScope	PRIVATE, WORKSPACE, TEAM, USERS, PUBLIC	shares
BlockType	PARAGRAPH, HEADING_1, HEADING_2, HEADING_3, QUOTE, CALLOUT, BULLETED_LIST, NUMBERED_LIST, TODO, TOGGLE, IMAGE, VIDEO, AUDIO, FILE, BOOKMARK, EMBED, CODE, EQUATION, COLUMN, COLUMN_LIST, DIVIDER, SPACER, DATABASE, LINKED_DATABASE, TABLE, CHART, FORM, SYNCED_BLOCK, CHILD_PAGE, CHILD_DATABASE (30)	blocks
PropertyType	TITLE, TEXT, RICH_TEXT, NUMBER, SELECT, MULTI_SELECT, STATUS, DATE, DATE_RANGE, PERSON, EMAIL, PHONE, URL, CHECKBOX, FILES, CREATED_TIME, CREATED_BY, LAST_EDITED_TIME, LAST_EDITED_BY, RELATION, ROLLUP, FORMULA, BUTTON, UNIQUE_ID (24)	database_properties
ViewType	TABLE, LIST, BOARD, GALLERY, CALENDAR, TIMELINE, CHART, DASHBOARD, FORM (9)	database_views
RelationCardinality	ONE_TO_ONE, ONE_TO_MANY, MANY_TO_MANY	relations
RollupAggregate	COUNT, SUM, AVERAGE, MIN, MAX, PERCENT, UNIQUE	rollups
AutomationTriggerType	RECORD_CREATED, RECORD_UPDATED, PROPERTY_CHANGED, PAGE_CREATED, FORM_SUBMITTED, SCHEDULE, WEBHOOK, MANUAL	automation_triggers
AutomationActionType	UPDATE_RECORD, CREATE_PAGE, SEND_EMAIL, SEND_WEBHOOK, CREATE_NOTIFICATION, ASSIGN_USER, CHANGE_STATUS, CALL_INTEGRATION, RUN_AI_ACTION	automation_actions
AutomationStatus	DRAFT, ACTIVE, PAUSED, ARCHIVED	automations
AutomationRunStatus	PENDING, RUNNING, SUCCEEDED, FAILED, DEAD_LETTER	automation_runs
NotificationChannel	IN_APP, EMAIL, WEBHOOK	notifications
NotificationType	MENTION, COMMENT, ASSIGNMENT, PAGE_SHARED, TASK_ASSIGNED, DATABASE_CHANGE, AUTOMATION, INTEGRATION	notifications
FileStatus	UPLOADING, SCANNING, READY, VIRUS_DETECTED, FAILED	files
FormQuestionType	SHORT_TEXT, LONG_TEXT, NUMBER, EMAIL, DATE, CHECKBOX, SINGLE_SELECT, MULTI_SELECT, RATING, FILE	form_questions
FormStatus	DRAFT, PUBLISHED, CLOSED	forms
AuditActorType	USER, API_KEY, SYSTEM, INTEGRATION	audit_logs
ApiKeyScope	READ, WRITE, ADMIN	api_keys (array)
WebhookDeliveryStatus	PENDING, DELIVERED, FAILED, REJECTED	webhook_deliveries
UserStatus	ACTIVE, INVITED, SUSPENDED	users
InvitationStatus	PENDING, ACCEPTED, DECLINED, REVOKED, EXPIRED	invitations
SessionStatus	ACTIVE, REVOKED, EXPIRED	sessions
EmailVerificationStatus	UNVERIFIED, PENDING, VERIFIED	users
PasswordResetStatus	PENDING, USED, EXPIRED, REVOKED	password_resets
SSOProviderType	SAML, OIDC	sso_providers
RetentionPolicyType	PURGE, HOLD	retention_policies

PropertyType has 24 values (PS§9.1's "22" headline counted Title + Unique ID differently — the enum is authoritative; functional-requirements §5.1 documents the mapping). BlockType has 30 (PS§4's "28" excludes the two structural rows child_page/child_database — functional §4.2).

7. Fractional positions — algorithm & rationale

7.1 Why not array indexes

An array index means every insert renumbers every later sibling — O(n) writes per drag on a 10k-block page, and two concurrent drags collide (lost updates). Fractional positions make an insert one row write and make concurrent inserts commute (each client writes a different position string; the total order converges on sort).

7.2 The encoding

Positions are strings over the alphabet A–Z a–z 0–9 (base-62, uppercase before lowercase by design: the alphabet is ordered 0-9 < A-Z < a-z, so "a0" < "a0V" < "a0Vg" < "a1"). A position is read as a base-62 fraction: "a0" = 1/62, "a0V" = 1/62 + (21·…)/62³, etc. The leading "a" is a "grouping digit" (all siblings of a fresh page/block start in the a0…a1 band); longer positions are sub-fractions, not larger numbers.

Midpoint (mid(a, b), a < b in string order):

1. If len(a) == len(b) and they differ at some digit d: the midpoint inserts the digit mid(a[d], b[d]) at d and copies a's suffix. mid("a0", "a1") → digits '0' and '1' → mid digit '0' + 0.5·gap → "a0V" (the half-step in base-62 between 0 and 1 is the first fractional digit, V = 21 in the ordering… concretely the library computes the average in base-62 fixed-point and emits the shortest string strictly between — "a0V" is the canonical output).
2. If b is the "successor boundary" (e.g. a = "a1", b is the next group), the midpoint extends a: mid("a0V", "a1") = "a0Vg".
3. Each extension adds one digit; precision is therefore logarithmic: k digits give 62^k distinct positions between two neighbors.

Worked example (inserting between siblings p1="a0" and p2="a1" repeatedly, always at the front of the gap):

start:        a0  a1
insert A:     a0  a0V  a1          mid("a0","a1") = "a0V"
insert B:     a0  a0Vg a0V a1      mid("a0","a0V") = "a0Vg"
insert C:     a0  a0Vg a0V… a0V a1 (each mid extends or splits)

After ~50 consecutive same-gap inserts the strings approach the cap (implementation cap: 512 chars ≈ 512 digits of headroom — far beyond any human editing session; the cap exists to bound memory, not practice).

7.3 Rebalancing (when strings get long)

When mid(a, b) cannot produce a representable midpoint (the two strings are "adjacent" in the 512-digit space), the positions module returns NEEDS_REBALANCE. The caller (service, in the same transaction) then:

1. Collects the affected sibling run (the maximal run of siblings between the two stable anchors — or the whole parent if the parent is small).
2. Reassigns fresh evenly-spaced positions to the run: a0, a1, a2, … (or re-grouped a0, a1, … per band) — O(run) writes.
3. Emits the new positions in the batch response so clients resync.

Invariants (property-tested — testing.md §3.3): after rebalance, positions are distinct, sort-consistent, and the relative order is unchanged. Rebalance is rare (≈ never in practice; it is the correctness backstop, not a performance path).

7.4 Where positions are used

pages.position (sibling order in tree), blocks.position (sibling order in page/column/toggle), database_properties.position (column order), database_records.position (row order per view), database_views.position (tabs), teams.position (sidebar), page_favorites.position, form_questions.position, dashboard_widgets.position (z-order), charts.position. Everywhere ordering is user-visible, it is fractional. The one exception: automation_triggers/conditions/actions.position are Int — those are small ordered lists edited in a builder (≤ 20 items), where integer renumbering is trivial and simpler to reason about; they are renumbered atomically on edit.

8. Cross-cutting design decisions

8.1 The record = page join

databases 1 ──── * database_records * ──── 1 pages
                      (pageId unique)

• Creating a record = one transaction: pages.insert (title = the TITLE property value, workspaceId, teamId inherited from the database's parent page) + database_records.insert(pageId) + record_properties rows. The two rows are one object: the record page's sidebar entry, its block content, its comments, and its permissions all live on the pages row; the structured data lives on the database_records + record_properties rows.
• Deleting a record moves its page to trash (one trash flow, PR-028); purging the page cascades the record row (FK pageId set-null on page hard-delete leaves an orphaned record row only if the record was deleted first — the GC job reaps database_records with pageId IS NULL older than 7 d).
• The pages.record relation is 1:1 (DatabaseRecord? on Page); a plain page has record = null. This is what makes "open the record" a page navigation (deep link /w/<ws>/<pageId> works for records and pages identically).

8.2 Block tree invariants

• blocks.parentId = null ⇔ top-level block of blocks.pageId.
• A toggle/column_list/column's children are the blocks with that parentId; the tree is rendered in position order per parent.
• pageId is immutable on a block (moving between pages = delete + insert in the target, within the DnD transaction — keeps workspaceId and the tree indexes honest).
• Synced blocks: the master has isSynced = false; copies have isSynced = true + syncedSourceId; edits to a copy are redirected to the master by the editor (the API rejects writes to a copy's content).
• blocks.deletedAt gives the undo window; the GC job purges soft-deleted blocks > 7 d old (orphan children cascade in the service at delete time, not via FK — parentId is intentionally FK-less so the service controls subtree deletion order + audit).

8.3 JSONB usage rules (V4)

1. JSONB only for: (a) type-discriminated payloads whose per-type shape varies (block content, property config/value, automation configs, view filter trees), (b) snapshots (versions, templates, search permission snapshots), (c) open catalogs (org policy, workspace settings, chart config).
2. Every JSONB column has a named Zod schema in packages/shared; repositories validate on write (the API never stores schema-invalid JSONB) and trust on read (schema change = a data migration, done by a job, never a runtime "fix").
3. No JSONB field is a query key (no WHERE content->>'x' = … in repositories) — anything queried gets a real column or the record_properties pivot. Exception: metadata.updatedAt-style search filters go through OpenSearch, not Postgres.
4. JSONB columns default to {} (never null) except where "absent" is meaningful (filter, sort, group, default, scanReport, selection, link, context, sourceRef).

8.4 Cardinality enforcement (relations)

relation_links is a plain join table; cardinality is a write-time constraint in the relation service (the engine, unit-tested):

• MANY_TO_MANY: insert the link; idempotent on the unique.
• ONE_TO_MANY (source record → many targets): insert; on the target side, linking target T to a second source S₂ is rejected with CONFLICT ("already linked to …").
• ONE_TO_ONE: linking unlinks any existing partner on either side; the response includes the replaced link (client can offer "keep both" → creates a new record).
• Deleting a record cascades its links (FK); deleting a relation removes relation_links where databaseId = <relation.source> (service query)
  • the two properties (soft).

9. Data lifecycle

9.1 Trash / restore / purge

 live ──delete──► trashed (trashedAt set) ──restore──► live (trashedAt, restoredAt)
   │                                        │
   │ delete                                  │ delete again → trashed
   ▼                                         ▼
 (records: archivedAt ⇄ live, independent of trash)
 trashed ──30 d purge job / owner purge──► hard delete

Entity	Soft delete	Restore	Purge (hard)
Page	trashedAt	clear trashedAt, set restoredAt, reparent if parent gone	delete page → FK cascades blocks, versions, comments, favorites, shares; record row orphans → GC; file objects deleted async (queue job, logged)
Block	deletedAt (undo window 7 d)	clear deletedAt (undo)	GC after 7 d (or page purge)
Record	archivedAt (views) and/or page trash	unarchive / page restore	page purge
File	deletedAt	clear	30 d → row + object keys (all versions) deleted
Workspace	deletedAt	— (owner-only, 30 d)	30 d → full cascade (files objects async); audit rows kept with workspaceId nulled
Session/token/invite/reset	status transitions	—	job expiry sweeps (EXPIRED)

Purge rules: owner/admin (workspace purge), owner (page/file purge), or the retention job (enterprise policies, NFR-P3/P4). Every purge is audited (*.purged) and logged.

9.2 Versioning

• Pages: explicit (menu → "Save version") + automatic on title/structure change; snapshot = page + full block tree; 100/page cap; restore = new version (non-destructive, page.versions immutable).
• Blocks: per-block version on structural edits (50/block cap).
• Files: re-upload creates a file_versions row (old object keys retained; S3 versioning is a second, storage-level safety net).
• Records/pages: there is no per-record version table — record history = the page's versions (the record page snapshot includes property values in the snapshot JSON, so version restore restores properties too).

9.3 Immutables

audit_logs (append-only; no UPDATE path in the repository — the interface literally has no update method), automation_runs after finishedAt (re-runs create new rows), webhook_deliveries after status = DELIVERED, page_versions/block_versions/file_versions (snapshots), search_documents.indexedAt (per doc row).

10. Multi-tenancy & integrity

10.1 Scoping rule (binding)

Every repository method takes a RequestContext {workspaceId, userId, role, permissions} and adds workspaceId to its where clause. There is no repository method that accepts a bare resource id without a context (except: token lookups — shares.token, refresh_tokens.tokenHash, api_keys.keyHash, forms.slug — which are single-row secret lookups that then immediately re-scope by the resolved workspaceId).

Query patterns (the only shapes repositories emit):

// list siblings
where: { workspaceId: ctx.workspaceId, parentId, deletedAt: null }
orderBy: { position: "asc" }

// fetch one (existence + tenant in one check)
findFirst({ where: { id, workspaceId: ctx.workspaceId } })   // NOT findUnique(id)

// cross-tenant is impossible: no findUnique-by-id alone exists in
// the repository interfaces for tenant-scoped tables

The cross-tenant integration suite (testing.md §4.3, NFR-P1) is the enforcement proof: 50 random (actor, other-workspace-resource) pairs must all 404/403.

10.2 Why application-enforced (not RLS)

Postgres Row-Level Security was evaluated and rejected for v1: (a) Prisma

• RLS adds a connection-per-tenant or SET-per-transaction complexity that interacts badly with connection pooling; (b) the repository-context rule gives the same guarantee with the same code review surface; (c) it keeps the option open. The cross-tenant suite makes a regression visible in CI, not in production. Revisit if we add a second query path (e.g. read replicas with direct SQL).

10.3 Polymorphic plain-ref columns — integrity map

Column	Discriminator	Integrity enforced where
search_documents.refId	type	indexer service (builds docs only for existing rows; reconciliation job deletes docs whose source row is gone)
permissions.resourceId (+resourceType)	resourceType	permission service: grant create validates the resource exists (typed lookup per resourceType); GC sweep drops grants whose resource vanished
permissions.granteeId (+granteeType)	granteeType	same service (user/team/role must exist)
shares.recordId	— (context: pageId/teamId null)	share service (record must exist in the workspace)
comment.recordId	— (pageId/blockId null)	comment service
comment.pageMentionIds	markdown context	mention resolver on write (ids validated; dead mentions render as "deleted page" — acceptable, mentions are historical)
file.pageId	—	file service
template.sourceRef / sourceDatabaseId	type in JSON	template service (snapshot is frozen; source may later be deleted — template keeps working off the snapshot)
chart/dashboard_widget.sourceRef	type in JSON	dashboard service (widget with dead source renders an error card, not a crash)
ai_embeddings.chunkRef	stringified {type,id}	RAG indexer (rebuild on model change re-reads sources; dead chunks pruned by reconciliation)
automation.databaseId	—	automation service (trigger matcher skips automations whose database is gone + flags them PAUSED in the UI)
audit_logs.actorId/resourceId	actorType/resourceType	deliberately not enforced — audit must survive deletion of the actor/resource (forensic value); rows keep the id strings
sso_identities.userId	—	SSO service (link only to existing users; user deletion → GC job drops identities)
form_questions.propertyId	—	form publish validation (property must exist; property deletion → form publish re-validates, question marked broken)
relation_links.targetRecordId	targetDatabaseId	relation service (link target must exist; target record delete cascades via GC on the target side — the reverse index makes the sweep a single query)
mention_notifications.targetId	targetType	mention service
form_submissions.recordId	—	FK (set-null) — the one plain-ref that is a real FK (single typed target)

Rule of thumb: when the target is one typed table and the read pattern is a join, use a real FK; when the target varies (or must survive deletion), use a plain ref + a service-enforced invariant + a GC/reconciliation job. Every plain ref is listed above — an unlisted plain ref is a review defect.

10.4 The comment_reactions null-unique note

Postgres treats NULLs as distinct in unique constraints, so (commentId, replyId, userId, emoji) would allow (NULL, reply1, u, 👍) twice. The service stores commentId = '' (empty string sentinel) when the reaction targets a reply, and replyId = '' when it targets a comment — making the unique constraint total. The API never exposes the sentinel. (Alternative considered: a targetType + targetId pair — rejected: this table is 3 columns of churn, the sentinel is cheaper.)

11. Data flow: who writes what (ownership)

Table(s)	Written by	Notes
users, sessions, refresh_tokens, email_verifications, password_resets	api/auth	only module that may write identity
workspaces, members, invitations, teams	api/workspaces
organizations, sso_*, retention_policies, organization_members	api/organizations (Phase 14)
pages, page_favorites, page_versions	api/pages (+ worker for purge jobs)	page writes always emit page.* events
blocks, block_versions	api/blocks (REST) and realtime (Yjs-persisted, debounced) — both call the same BlockRepository unit-of-work	the realtime path is the hot path; REST is for cross-device/non-collab edits
databases, properties, views, records, record_properties	api/databases	record writes emit record.* + database.updated
relations, relation_links, rollups, formulas, database_buttons	api/relations	link writes emit relation.changed (rollup/formula recompute triggers)
charts, dashboards, dashboard_widgets	api/databases (charts) / api/dashboards
comments, comment_replies, comment_reactions, notifications, mention_notifications	api/comments + worker (notification fan-out/email)	realtime fans out over the socket; rows are written by api
files, file_versions	api/files (metadata) + worker (pipeline stages: status, metadata, thumbnails, scanReport)	workers never write name/uploadedBy
permissions, shares	api/pages (share dialog) + api/workspaces (scope grants)	every change → permission.changed/page.shared + search permission-snapshot refresh event
templates	api/templates
automations, triggers, conditions, actions	api/automations	runs/logs written by worker only
integrations, api_keys, webhooks	api/integrations, api/api-keys, api/webhooks	deliveries written by worker
audit_logs	any module via AuditService (in-transaction)	append-only
search_documents	worker (indexer) only	api never writes search docs
ai_conversations, ai_messages	api/ai	ai_embeddings by worker (RAG indexer)
forms, form_questions	api/forms	submissions: api/forms (public endpoint)

12. Query patterns & performance (watch list)

Query	Index	Pattern notes
Sibling page list	pages(workspaceId, parentId, position)	single seek + range scan; virtualized (limit 200 + cursor)
Page block window	blocks(pageId, parentId, position)	limit/cursor per parent; a 100k-block page = ~500 parent-level seeks total across a full scroll (each < 1 ms)
Table row window	database_records(workspaceId, databaseId, position) + record_properties pivot (WHERE recordId IN (window) AND propertyId IN (visible))	the pivot is the hot path at 100k; EXPLAIN must show Index Scan on the record index + Bitmap/Index on record_properties(recordId) (add a partial index if the plan degrades — Phase 15)
Board columns	same + GROUP BY in SQL on the group property value	groups computed in SQL (record_properties.value for the one group property), rows paged per group
Reverse relations	relation_links(targetDatabaseId, targetRecordId)	single seek
Inbox	notifications(userId, readAt)	readAt IS NULL first (NULLs sort first with the asc+nulls-first modifier)
Audit viewer	the three audit composites	always with createdAt range + limit (no unbounded scans)
Webhook retry scan	webhook_deliveries(status, nextRetryAt)	WHERE status='PENDING' AND nextRetryAt <= now() — the worker's tight loop; keep this index lean
Permission collection (engine)	permissions(workspaceId, granteeId)	returns all grants for the user's grantee set (user + teams + role), then the engine walks the tree in memory (grants are few per workspace — typically < 500; the walk is O(grants × tree-depth))

Covering-index discipline: the three list indexes above are chosen to be covering for their projections (the list queries select only indexed columns + id), so the hot reads never heap-fetch. If a projection grows (e.g. adding a column to the table-row fetch), either add the column to a new index or accept the heap fetch and re-measure (Phase 15 query-plan review is mandatory before adding columns to hot tables).

13. Partitioning plan (scale path — Phase 15)

Trigger-based, not preemptive: partition when a table exceeds the threshold and queries show the plan degrading.

Table	Threshold	Plan
blocks	> 50 M rows (a large multi-tenant deployment)	Range partition by createdAt (monthly) + keep (pageId, parentId, position) as a local index per partition; the hot query (per-page window) stays within 1–2 partitions (pages are young); old partitions demote to cold storage (pg_dump to S3 + detach). Alternative considered: hash by workspaceId — rejected (queries are per-page, not per-workspace; and tenant isolation is app-level anyway)
audit_logs	> 100 M rows	Range partition by createdAt (monthly) — the three composite indexes become local; retention (NFR-P3: 400 d) maps cleanly to partition detach (detach = instant retention enforcement + archive to S3). This is the first table to be partitioned if any (audit grows monotonically and is append-only)
automation_runs / automation_logs	> 50 M	range by createdAt (monthly), same retention mapping (180 d, NFR-P3)
webhook_deliveries	> 50 M	range by createdAt; 90 d retention (NFR-P3)
search_documents	—	not partitioned (bounded by content size; OpenSearch holds the hot copy)
notifications	> 100 M	range by createdAt, 180 d default retention

Partitioning rules: the partition key is always the retention key (createdAt); hot queries must touch ≤ 2 partitions (verified by the query-plan review); partition maintenance (create next month, detach expired

• archive) is a scheduled worker job (db:partition-maintain), audited. Until Phase 15, no partitioning is deployed — the index set above is designed so the single-table plans hold to the NFR-S thresholds (100k records/db, 1M records/workspace, 10M search docs).

14. Change management

1. Schema change → edit prisma/schema.prisma → pnpm db:migrate (dev) → review the generated SQL → update this document (field tables + index tables + enum catalog) in the same PR → the integration suite (which applies migrations fresh) is the canary.
2. JSONB shape change → change the Zod schema in packages/shared first (it is the contract) → a migration job (worker) rewrites stored rows if the change is not backward-compatible → deploy (expand) → run job → (next release) drop old readers (contract).
3. Enum value add: safe (expand) — add to Prisma enum + shared union + this §6. Enum value removal: never; deprecate in docs, keep the value (rows may reference it), mark the value @deprecated in the schema comment.
4. Destructive DDL: expand/migrate/contract (§7 of the deployment doc), task-file record, review ×2.