DATABASE_IMPACT.mddocs/architecture/DATABASE_IMPACT.md← architecture6,315 B← Back to the app

DATABASE_IMPACT.md — Schema & Data Changes (Confluence expansion, Phase 0)

All changes are additive and backward-compatible (spec §2.8). No table is renamed, dropped, or had a column's type changed. Existing identifiers, data, and API contracts are preserved. Verified against prisma/schema.prisma (74 models) as of Phase 0.

1. New columns (existing tables)

Table	Column	Type / default	Rationale	Migration risk
`workspaces`	`type`	String, default `'team'`	Space types (A)	None — default covers existing rows
`workspaces`	`archivedAt`	DateTime?	Space archive/restore (A)	None — nullable
`pages`	`kind`	String, default `'page'` (`page` \| `whiteboard` \| `task`)	Content-type discriminator (I, J); page tree/permissions/search stay unified	None — default `'page'`; legacy pages untouched
`pages`	`reviewState`	String, default `'published'`	Review workflow (J); legacy pages read as published	None — default preserves current semantics
`templates`	`category`	String?	Template categories (C)	None
`templates`	`description`	String?	Template descriptions (C)	None
`templates`	`variables`	Json?	Typed variable definitions (C)	None
`templates`	`defaultFor`	String?	Default template for new pages (C)	None
`search_documents` (index metadata)	`labels` (in `metadata` Json)	—	Label filter (G)	Index mapping update + reindex
`notifications` (enum)	`REVIEW_REQUESTED`, `APPROVAL_DECISION`, `WATCH_UPDATE`, `TASK_DUE`	enum add	New event types (J, K)	None — PG enum `ALTER TYPE … ADD VALUE`

2. New tables

Table	Key columns	Indexes	Purpose
`whiteboards`	id, pageId (unique), canvas Json, schemaVersion Int default 1, updatedBy, timestamps	(pageId unique)	Whiteboard canvas, 1:1 with page (I)
`template_versions`	id, templateId, version, snapshot Json, note?, createdBy, createdAt	(templateId, version) unique	Template version history (C)
`reviews`	id, pageId, state, reviewerIds String[], requestedBy, comment?, deadline?, decision?, decidedAt?, decidedBy?, timestamps	(pageId, state), (reviewerIds GIN)	Review workflow instances (J)
`tasks`	id, workspaceId, pageId (unique, 1:1 with kind=task page), title, assigneeIds String[], createdBy, status default 'todo', dueAt?, timestamps	(workspaceId, status), (assigneeIds GIN), (workspaceId, dueAt)	Task sidecar over page-backed tasks (J)
`page_watches`	id, pageId, userId, createdAt	(pageId, userId) unique, (userId)	Watchlist (K)
`user_recent_items`	id, userId, itemType, refId, at	(userId, at desc), unique (userId, itemType, refId)	Recently viewed (G)
`saved_searches`	id, userId, workspaceId?, name, queryJson, createdAt	(userId)	Saved searches (G)

Notes:

No separate backlinks table (Ponytail, see TARGET_ARCHITECTURE §2.2): computed by indexed scan + cache. Upgrade trigger: p95 > 100 ms under the 100k load profile → add maintained page_backlinks table.
No new event/activity table: activity feeds are permission-scoped projections of the existing audit_logs (which already records every domain event's actor/action/resource/time).
Tasks are page-backed (pages.kind='task' + tasks sidecar): checklists are todo blocks, comments/permissions/search come free; the sidecar holds only the columns that need indexed queries (assignee/status/due).

3. Data integrity

Foreign keys: whiteboards.pageId → pages (Cascade), template_versions.templateId → templates (Cascade), reviews.pageId → pages (Cascade), tasks.pageId → pages (Cascade, unique), page_watches.pageId → pages (Cascade), user_recent_items refs are plain ids (no FK — cache-like, GC'd FIFO), saved_searches.userId → users (Cascade).
Uniqueness: whiteboards.pageId, template_versions (templateId, version), reviews open-per-page enforced in service (one open review per page), tasks.pageId, page_watches (pageId, userId).
Transactions: review transitions + notification enqueue in one tx (event after-commit, existing pattern); CSV import batch in one tx with per-row validation errors collected; bulk actions per-item tx, results aggregated.
Soft delete: whiteboards/tasks follow their page (trashedAt cascades); reviews/recent-items hard-delete with the page (no retention value).
Serialized schemas versioned: whiteboards.schemaVersion (v1 defined in packages/whiteboard), block content schema stays versioned by existing Zod BLOCK_CONTENT_SCHEMAS (additive .optional() fields only).

4. Migration plan

Single migration 20261011000000_confluence_expansion (prisma migrate dev), steps: add columns → add enums → create tables → add indexes → (no data rewrites).
Seed extension (prisma/seed.ts): idempotent per-workspace built-in template seeding (upsert by name + category + isBuiltin).
OpenSearch: index template/mapping update for new metadata fields; admin reindex endpoint triggers full rescan after deploy.
Rollback: prisma migrate revert-equivalent SQL documented per table (all additive); feature-flag not needed because defaults preserve legacy behavior (kind='page', reviewState='published', type='team').
Upgrade testing: run the existing Testcontainers integration suite (33 suites) + E2E final-flow against a copy of the current dev database post-migration (spec §18: test upgrades against representative data).

5. Performance impact

New hot queries: task inbox (GIN on assigneeIds — indexed), backlinks scan (covered by blocks type/parent indexes; cached), activity feed (audit_logs workspace/time indexes — existing perf indexes 20261003/20261004 migrations).
Write amplification: one user_recent_items upsert per page open (debounced 30 s in client); one page_watches row per explicit watch; review notifications reuse existing channel.
Storage: whiteboard canvases are JSONB (typical < 1 MB); template snapshots ≤ 256 KB (validated); no new partitioning requirement at dev scale (recorded; revisit if canvas > 10 MB — then object storage + ref).