// --------------------------------------------------------------------------- // AI Software Factory — Prisma schema (PostgreSQL) // // Conventions: // - Every tenant-scoped table carries workspaceId (and organizationId where // relevant). All queries are tenant-scoped in the repository layer. // - Ordering uses fractional positions (LexoRank-style strings), NEVER indexes. // - Flexible payloads (block content, property values, view settings, // formula ASTs) are JSONB. // - Records are pages: database_records.pageId is 1:1 with pages.id. // - Polymorphic references (SearchDocument.refId, Permission.resourceId, // Share.recordId, Comment.pageMentionIds) are plain strings — the // application validates referential integrity, Prisma does not model // multi-way FKs on shared columns. // --------------------------------------------------------------------------- generator client { provider = "prisma-client-js" // TASK-0135 (observability): the `tracing` preview feature (the generated // client's real tracing helper, delegating to // globalThis.PRISMA_INSTRUMENTATION at query time) was promoted to GA in // Prisma 6.1 — no previewFeatures entry is needed for @prisma/instrumentation // spans (db_query_duration_seconds) to be emitted (T-190b). } datasource db { provider = "postgresql" url = env("DATABASE_URL") } // ============ ENUMS ============ enum WorkspaceRole { OWNER ADMIN MEMBER GUEST } enum PermissionLevel { NONE VIEW COMMENT EDIT FULL_ACCESS } enum ShareScope { PRIVATE WORKSPACE TEAM USERS PUBLIC } enum 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 // T-147: table-of-contents block (content generated from headings at render). TOC } enum 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 } enum ViewType { TABLE LIST BOARD GALLERY CALENDAR TIMELINE CHART DASHBOARD FORM } enum RelationCardinality { ONE_TO_ONE ONE_TO_MANY MANY_TO_MANY } enum RollupAggregate { COUNT SUM AVERAGE MIN MAX PERCENT UNIQUE } enum AutomationTriggerType { RECORD_CREATED RECORD_UPDATED PROPERTY_CHANGED PAGE_CREATED FORM_SUBMITTED SCHEDULE WEBHOOK MANUAL } enum AutomationActionType { UPDATE_RECORD CREATE_PAGE SEND_EMAIL SEND_WEBHOOK CREATE_NOTIFICATION ASSIGN_USER CHANGE_STATUS CALL_INTEGRATION RUN_AI_ACTION } enum AutomationStatus { DRAFT ACTIVE PAUSED ARCHIVED } enum AutomationRunStatus { PENDING RUNNING SUCCEEDED FAILED DEAD_LETTER } enum NotificationChannel { IN_APP EMAIL WEBHOOK } enum NotificationType { MENTION FORM_SUBMITTED COMMENT ASSIGNMENT PAGE_SHARED TASK_ASSIGNED DATABASE_CHANGE AUTOMATION INTEGRATION // Confluence expansion (T-163/T-157/T-160): REVIEW_REQUESTED // page submitted for review → reviewers APPROVAL_DECISION // approved / changes requested → editor + other reviewers WATCH_UPDATE // watched page changed → watchers (canView rechecked at fan-out) TASK_DUE // task due-date reminder → assignees } enum FileStatus { UPLOADING SCANNING READY VIRUS_DETECTED FAILED } enum FormQuestionType { SHORT_TEXT LONG_TEXT NUMBER EMAIL DATE CHECKBOX SINGLE_SELECT MULTI_SELECT RATING FILE } enum FormStatus { DRAFT PUBLISHED CLOSED } enum AuditActorType { USER API_KEY SYSTEM INTEGRATION } enum ApiKeyScope { READ WRITE ADMIN } enum WebhookDeliveryStatus { PENDING DELIVERED FAILED REJECTED /// TASK-0130 — terminal after the final retry (8 attempts) fails: the /// delivery is dead-lettered (kept in `webhook_deliveries` for the /// inspector; resubmit re-queues it). DEAD_LETTER } enum UserStatus { ACTIVE INVITED SUSPENDED } enum InvitationStatus { PENDING ACCEPTED DECLINED REVOKED EXPIRED } enum SessionStatus { ACTIVE REVOKED EXPIRED } enum EmailVerificationStatus { UNVERIFIED PENDING VERIFIED } enum PasswordResetStatus { PENDING USED EXPIRED REVOKED } enum SSOProviderType { SAML OIDC } enum RetentionPolicyType { PURGE HOLD } // ============ IDENTITY & TENANCY ============ model User { id String @id @default(cuid()) email String @unique emailVerifiedAt DateTime? emailVerification EmailVerificationStatus @default(UNVERIFIED) passwordHash String? name String displayName String? avatarUrl String? status UserStatus @default(ACTIVE) locale String @default("en-US") timezone String @default("UTC") theme String @default("system") // Per-type EMAIL-channel notification preferences (TASK-0094 R6): a // partial map NotificationType -> "all"|"instant"|"digest"|"off" // (IN_APP is always on). The shape is the shared // `@factory/shared/realtime/notification-prefs` schema; absent types use // the defaults there. Additive, default "{}" (no data loss). notificationPrefs Json @default("{}") // TASK-0133 — org `require2FA` policy. The TOTP seed is stored AES-256-GCM // encrypted (base64 envelope, same scheme as SSO/webhook secrets); `null` = // 2FA not enrolled. The raw seed is revealed once (enrollment QR/secret). twoFactorSecret String? lastLoginAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt memberships WorkspaceMember[] ownedWorkspaces Workspace[] @relation("WorkspaceOwner") ownedOrganizations Organization[] @relation("OrgOwner") orgMemberships OrganizationMember[] // Confluence expansion (T-157/T-153/T-154): pageWatches PageWatch[] recentItems UserRecentItem[] savedSearches SavedSearch[] comments Comment[] commentReplies CommentReply[] notifications Notification[] favorites PageFavorite[] pageVersions PageVersion[] blockVersions BlockVersion[] fileVersions FileVersion[] sessions Session[] refreshTokens RefreshToken[] passwordResets PasswordReset[] apiKeys ApiKey[] automations Automation[] @relation("AutomationCreator") aiConversations AiConversation[] mentionNotifications MentionNotification[] @@map("users") } model RefreshToken { id String @id @default(cuid()) tokenHash String @unique userId String user User @relation(fields: [userId], references: [id], onDelete: Cascade) sessionAgent String? createdAt DateTime @default(now()) expiresAt DateTime revokedAt DateTime? replacedBy String? @@map("refresh_tokens") } model Session { id String @id @default(cuid()) userId String user User @relation(fields: [userId], references: [id], onDelete: Cascade) tokenHash String @unique userAgent String? ipAddress String? status SessionStatus @default(ACTIVE) createdAt DateTime @default(now()) expiresAt DateTime revokedAt DateTime? @@index([userId, status]) @@map("sessions") } model EmailVerification { id String @id @default(cuid()) userId String tokenHash String @unique expiresAt DateTime createdAt DateTime @default(now()) @@index([userId]) @@map("email_verifications") } model PasswordReset { id String @id @default(cuid()) userId String user User @relation(fields: [userId], references: [id], onDelete: Cascade) tokenHash String @unique status PasswordResetStatus @default(PENDING) expiresAt DateTime usedAt DateTime? createdAt DateTime @default(now()) @@index([userId]) @@map("password_resets") } model Organization { id String @id @default(cuid()) name String slug String @unique logoUrl String? ownerId String owner User @relation("OrgOwner", fields: [ownerId], references: [id]) policy Json @default("{}") // org policies: security, retention, SSO enforcement ssoConfig Json? scimConfig Json? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt members OrganizationMember[] workspaces Workspace[] ssoProvider SsoProvider? retentionPolicies RetentionPolicy[] auditExports AuditExport[] @@map("organizations") } // TASK-0133 — async audit CSV exports (the >10k-row path; `exports/{orgId}/ // {exportId}.csv` in the files bucket, retained 7 d). `status` is an app- // level String (processing|ready|failed — no enum: the worker + API share the // literals, mirroring the other app-level status fields). `downloadUsed` is // the single-use flag (the presigned URL is minted at most once; the // `?redownload=1` poll mints one more). model AuditExport { id String @id // the exportId (uuid, created by the API) organizationId String organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade) status String @default("processing") // processing | ready | failed totalRows Int capRows Int rows Int @default(0) truncated Boolean @default(false) // true when capRows < totalRows filters Json @default("{}") error String? downloadUrlAt DateTime? downloadUsed Boolean @default(false) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt completedAt DateTime? @@index([organizationId, status]) @@index([organizationId, createdAt]) @@map("audit_exports") } model OrganizationMember { id String @id @default(cuid()) organizationId String userId String role WorkspaceRole @default(MEMBER) createdAt DateTime @default(now()) organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade) user User @relation(fields: [userId], references: [id], onDelete: Cascade) @@unique([organizationId, userId]) @@map("organization_members") } model SsoProvider { id String @id @default(cuid()) organizationId String @unique type SSOProviderType name String config Json // provider config (entityId, certificate ref, issuer, client id) enforce Boolean @default(false) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade) identityLinks SsoIdentity[] @@map("sso_providers") } model SsoIdentity { id String @id @default(cuid()) providerId String externalId String userId String createdAt DateTime @default(now()) provider SsoProvider @relation(fields: [providerId], references: [id], onDelete: Cascade) @@unique([providerId, externalId]) @@map("sso_identities") } model RetentionPolicy { id String @id @default(cuid()) organizationId String type RetentionPolicyType scope String // "comments" | "trash" | "audit_logs" | "files" ... retainDays Int? config Json @default("{}") createdAt DateTime @default(now()) updatedAt DateTime @updatedAt organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade) // TASK-0133 — one policy per (org, scope) (retention engine + PATCH upsert). @@unique([organizationId, scope]) @@map("retention_policies") } model Workspace { id String @id @default(cuid()) organizationId String? name String slug String @unique iconUrl String? logoUrl String? description String? publicUrl String? // e.g. https://acme.factory.app /// Phase 12 (TASK-0121): `settings.ai` — per-workspace AI config /// (`{enabled?, provider?, chatModel?, embedModel?, budgets?, endpoint?}` — /// the `@factory/ai` `AiConfigSchema` shape, per-field fallback onto the /// env platform defaults `AI_PROVIDER`/`AI_BASE_URL`/`AI_API_KEY`/ /// `AI_MODEL`). Provider selection is configuration-only; swapping the /// provider never changes application code (ROADMAP Phase 12 exit). settings Json @default("{}") /// Confluence expansion (T-136): space type — "team" | "project" | "kb" | /// "custom". Legacy workspaces read as "team" (default). Drives the space /// directory filter; no permission semantics attached. type String @default("team") /// Confluence expansion (T-136): soft archive (distinct from `deletedAt` /// removal). Archived spaces are hidden from the directory; content and /// membership are preserved; restore clears the flag. archivedAt DateTime? isPersonal Boolean @default(false) ownerId String? owner User? @relation("WorkspaceOwner", fields: [ownerId], references: [id], onDelete: SetNull) billingPlan String @default("free") /// Phase 12 (TASK-0121): `usageLimits.aiMonthlyTokens` — the per-workspace /// monthly AI token budget (Int; unset or 0 = unlimited, plan default). /// Enforced by the AI gateway at submit time → `AI_BUDGET_EXCEEDED` (402) /// + a throttled admin notification (one per workspace per 24 h). usageLimits Json @default("{}") /// TASK-0104 §4: materialized storage-quota counter (bytes of non-deleted /// files). Updated transactionally by the file service (complete +size, /// purge / replace-old-versions −size; restore = no change) so session /// pre-checks are O(1) — never a live sum. BigInt (PG bigint): 10 GB /// workspaces overflow Int. usageStorageBytes BigInt @default(0) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt deletedAt DateTime? organization Organization? @relation(fields: [organizationId], references: [id], onDelete: SetNull) members WorkspaceMember[] invitations Invitation[] teams Team[] pages Page[] databases Database[] files File[] automations Automation[] integrations Integration[] webhooks Webhook[] apiKeys ApiKey[] shares Share[] templates Template[] notifications Notification[] auditLogs AuditLog[] searchDocuments SearchDocument[] aiConversations AiConversation[] formSubmissions FormSubmission[] dashboards Dashboard[] comments Comment[] @@index([organizationId]) @@map("workspaces") } model WorkspaceMember { id String @id @default(cuid()) workspaceId String userId String role WorkspaceRole @default(MEMBER) invitedBy String? joinedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) user User @relation(fields: [userId], references: [id], onDelete: Cascade) teamMemberships TeamMember[] @@unique([workspaceId, userId]) @@index([userId]) @@map("workspace_members") } model Invitation { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) email String role WorkspaceRole @default(MEMBER) invitedById String tokenHash String @unique status InvitationStatus @default(PENDING) expiresAt DateTime acceptedAt DateTime? revokedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@unique([workspaceId, email, role]) @@index([workspaceId, status]) @@map("invitations") } model Team { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) name String slug String description String? color String? iconUrl String? position String @default("a0") // fractional ordering in sidebar isPrivate Boolean @default(false) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt members TeamMember[] pages Page[] comments Comment[] shares Share[] @@unique([workspaceId, slug]) @@map("teams") } model TeamMember { id String @id @default(cuid()) teamId String workspaceMemberId String userId String role WorkspaceRole @default(MEMBER) createdAt DateTime @default(now()) team Team @relation(fields: [teamId], references: [id], onDelete: Cascade) workspaceMember WorkspaceMember @relation(fields: [workspaceMemberId], references: [id], onDelete: Cascade) @@unique([teamId, userId]) @@map("team_members") } // ============ PAGES & BLOCKS ============ model Page { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) teamId String? team Team? @relation(fields: [teamId], references: [id], onDelete: SetNull) parentId String? parent Page? @relation("PageTree", fields: [parentId], references: [id], onDelete: SetNull) children Page[] @relation("PageTree") grandparentId String? // denormalized parent's parent for fast sibling queries position String @default("a0") // fractional ordering title String @default("") icon String? // emoji or file id coverUrl String? description String? /// Confluence expansion (T-144/A-001/A-006): content-type discriminator — /// "page" | "whiteboard" | "task". Legacy pages read as "page" (default); /// the page tree, permissions, versions, trash, and search stay unified. /// Whiteboards carry their canvas in the 1:1 `whiteboards` sidecar; tasks /// carry queryable columns in the 1:1 `tasks` sidecar. kind String @default("page") /// Confluence expansion (T-162): review workflow state — "draft" | /// "in_review" | "approved" | "published" | "changes_requested" | /// "archived". Legacy pages read as "published" (default = no behavior /// change). Transitions are validated server-side in the review service. reviewState String @default("published") isPrivate Boolean @default(false) isFavorite Boolean @default(false) archivedAt DateTime? deletedAt DateTime? trashedAt DateTime? restoredAt DateTime? createdBy String? updatedBy String? version Int @default(1) // optimistic concurrency createdAt DateTime @default(now()) updatedAt DateTime @updatedAt blocks Block[] favorites PageFavorite[] versions PageVersion[] comments Comment[] shares Share[] record DatabaseRecord? auditLogs AuditLog[] reviews Review[] watch PageWatch[] whiteboard Whiteboard? task Task? @@index([workspaceId, parentId, position]) @@index([workspaceId, deletedAt]) @@index([teamId]) @@map("pages") } model PageFavorite { id String @id @default(cuid()) workspaceId String userId String pageId String position String @default("a0") createdAt DateTime @default(now()) user User @relation(fields: [userId], references: [id], onDelete: Cascade) page Page @relation(fields: [pageId], references: [id], onDelete: Cascade) @@unique([workspaceId, userId, pageId]) @@map("page_favorites") } model PageVersion { id String @id @default(cuid()) pageId String version Int snapshot Json // page + block tree snapshot createdBy String? note String? createdAt DateTime @default(now()) page Page @relation(fields: [pageId], references: [id], onDelete: Cascade) user User? @relation(fields: [createdBy], references: [id], onDelete: SetNull) @@unique([pageId, version]) @@map("page_versions") } model Block { id String @id @default(cuid()) pageId String workspaceId String parentId String? type BlockType position String @default("a0") // fractional ordering, NEVER array indexes content Json @default("{}") // type-specific content (text, media refs, code, ...) properties Json @default("{}") // colors, alignment, column width, checkbox state, ... isSynced Boolean @default(false) syncedSourceId String? // for SYNCED_BLOCK: source block id createdBy String? updatedBy String? deletedAt DateTime? version Int @default(1) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt page Page @relation(fields: [pageId], references: [id], onDelete: Cascade) versions BlockVersion[] comments Comment[] files File[] @@index([pageId, parentId, position]) @@index([pageId, position]) // TASK-0135: tree depth=all load (EXPLAIN-proven) @@index([workspaceId, pageId]) @@map("blocks") } model BlockVersion { id String @id @default(cuid()) blockId String version Int content Json properties Json createdBy String? createdAt DateTime @default(now()) block Block @relation(fields: [blockId], references: [id], onDelete: Cascade) user User? @relation(fields: [createdBy], references: [id], onDelete: SetNull) @@unique([blockId, version]) @@map("block_versions") } // ============ DATABASES ============ model Database { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) pageId String? // page the database block is embedded in (plain ref) title String @default("") icon String? description String? schemaVersion Int @default(1) isArchived Boolean @default(false) createdBy String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt properties DatabaseProperty[] views DatabaseView[] records DatabaseRecord[] relations Relation[] relationLinks RelationLink[] rollups Rollup[] formulas Formula[] buttons DatabaseButton[] forms Form[] charts Chart[] @@index([workspaceId]) @@map("databases") } model DatabaseProperty { id String @id @default(cuid()) databaseId String name String type PropertyType position String @default("a0") isTitle Boolean @default(false) isVisible Boolean @default(true) required Boolean @default(false) config Json @default("{}") // options[], format, relation ref, formula ref, button config default Json? deletedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) values RecordProperty[] @@unique([databaseId, name]) @@index([databaseId, position]) @@map("database_properties") } model DatabaseView { id String @id @default(cuid()) databaseId String name String type ViewType position String @default("a0") config Json @default("{}") // visible properties, column widths, card layout, color, density filter Json? // nested AND/OR/NOT filter tree sort Json? // [{propertyId, direction}] group Json? // [{propertyId, direction}] pageSize Int? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) @@index([databaseId, position]) @@map("database_views") } model DatabaseRecord { id String @id @default(cuid()) workspaceId String databaseId String pageId String? @unique // each record IS a page coverUrl String? icon String? archivedAt DateTime? isSubItem Boolean @default(false) position String @default("a0") // ordering inside table/board views version Int @default(1) createdBy String? createdTime DateTime @default(now()) lastEditedBy String? lastEditedTime DateTime @default(now()) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) page Page? @relation(fields: [pageId], references: [id], onDelete: SetNull) properties RecordProperty[] relationLinks RelationLink[] formSubmissions FormSubmission[] @@index([workspaceId, databaseId, position]) @@index([databaseId, archivedAt]) @@index([databaseId, position]) // TASK-0135: 100k-record table list (EXPLAIN-proven) @@map("database_records") } model RecordProperty { id String @id @default(cuid()) recordId String propertyId String value Json? // typed value per property.type (select option ids, file ids, user ids) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt record DatabaseRecord @relation(fields: [recordId], references: [id], onDelete: Cascade) property DatabaseProperty @relation(fields: [propertyId], references: [id], onDelete: Cascade) @@unique([recordId, propertyId]) @@map("record_properties") } // ============ RELATIONS / ROLLUPS / FORMULAS / BUTTONS ============ model Relation { id String @id @default(cuid()) databaseId String targetDatabaseId String propertyId String? // relation property on this database reversePropertyId String? // bidirectional counterpart name String targetName String? cardinality RelationCardinality @default(MANY_TO_MANY) bidirectional Boolean @default(true) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) @@index([databaseId]) @@index([targetDatabaseId]) @@map("relations") } model RelationLink { id String @id @default(cuid()) databaseId String recordId String targetRecordId String targetDatabaseId String createdAt DateTime @default(now()) database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) record DatabaseRecord @relation(fields: [recordId], references: [id], onDelete: Cascade) @@unique([databaseId, recordId, targetDatabaseId, targetRecordId]) @@index([targetDatabaseId, targetRecordId]) @@map("relation_links") } model Rollup { id String @id @default(cuid()) databaseId String propertyId String? // the ROLLUP property on this database relationId String targetPropertyId String aggregate RollupAggregate filter Json? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) @@index([databaseId]) @@map("rollups") } model Formula { id String @id @default(cuid()) databaseId String propertyId String? expression String // source text, e.g. if(prop("Status") == "Done", 100, 0) ast Json // parsed AST (safe evaluator input) returnType String // inferred return type dependencies String[] // property ids this formula references createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) @@index([databaseId]) @@map("formulas") } model DatabaseButton { id String @id @default(cuid()) databaseId String propertyId String? name String icon String? color String? actions Json // [{type, config}] — set property, create page, webhook, automation createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) @@index([databaseId]) @@map("database_buttons") } // ============ CHARTS & DASHBOARDS ============ model Chart { id String @id @default(cuid()) workspaceId String databaseId String name String chartType String // bar | line | area | pie | donut | number config Json // x axis, y axis, group, aggregation, color, legend filter Json? position String @default("a0") createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) @@index([workspaceId, databaseId]) @@map("charts") } model Dashboard { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) name String icon String? layout Json @default("{}") // widget grid: [{widgetType, sourceRef, x, y, w, h, config}] isPublic Boolean @default(false) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt widgets DashboardWidget[] @@index([workspaceId]) @@map("dashboards") } model DashboardWidget { id String @id @default(cuid()) dashboardId String widgetType String // table | board | chart | number | calendar | timeline | text | linked_database sourceRef Json? // {type: "database"|"chart"|"view", id} position String @default("a0") layout Json @default("{}") filter Json? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt dashboard Dashboard @relation(fields: [dashboardId], references: [id], onDelete: Cascade) @@index([dashboardId, position]) @@map("dashboard_widgets") } // ============ COMMENTS & NOTIFICATIONS ============ model Comment { id String @id @default(cuid()) workspaceId String teamId String? pageId String? blockId String? recordId String? // database record (plain ref) selection Json? // text selection anchor/offset body String mentionIds String[] @default([]) // user ids mentioned pageMentionIds String[] @default([]) // pages/databases mentioned (plain refs) resolvedAt DateTime? resolvedBy String? status String @default("open") // open | resolved | reopened createdBy String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) team Team? @relation(fields: [teamId], references: [id], onDelete: SetNull) page Page? @relation(fields: [pageId], references: [id], onDelete: Cascade) block Block? @relation(fields: [blockId], references: [id], onDelete: Cascade) author User @relation(fields: [createdBy], references: [id], onDelete: Cascade) replies CommentReply[] reactions CommentReaction[] @@index([workspaceId, pageId]) @@index([workspaceId, blockId]) @@index([workspaceId, recordId]) @@map("comments") } model CommentReply { id String @id @default(cuid()) commentId String body String mentionIds String[] @default([]) createdBy String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt comment Comment @relation(fields: [commentId], references: [id], onDelete: Cascade) author User @relation(fields: [createdBy], references: [id], onDelete: Cascade) reactions CommentReaction[] @@index([commentId]) @@map("comment_replies") } model CommentReaction { id String @id @default(cuid()) emoji String userId String commentId String? replyId String? createdAt DateTime @default(now()) comment Comment? @relation(fields: [commentId], references: [id], onDelete: Cascade) reply CommentReply? @relation(fields: [replyId], references: [id], onDelete: Cascade) @@unique([commentId, replyId, userId, emoji]) @@map("comment_reactions") } model Notification { id String @id @default(cuid()) workspaceId String userId String type NotificationType channel NotificationChannel @default(IN_APP) title String body String? link Json? // deep link {type, id} readAt DateTime? // The acting user (the notification's author, e.g. the mentioner / the // person who replied). Used for: the self-actor email suppression // (TASK-0094 R6: actorId == userId → no email) + the bell's actor label. actorId String? // EMAIL idempotency (TASK-0094 R6): set by the worker when the email // job succeeds; a double-enqueue (the BullMQ job-key race) is guarded by // the unique job key `email_{notificationId}` + this flag. (Colon-free: // BullMQ 5.x rejects custom jobIds containing `:` unless 3-segment — the // old `email:{id}` threw on every enqueue; fixed 2026-10-12.) emailSentAt DateTime? createdAt DateTime @default(now()) workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) user User @relation(fields: [userId], references: [id], onDelete: Cascade) @@index([userId, readAt]) @@index([workspaceId, userId]) @@map("notifications") } model MentionNotification { id String @id @default(cuid()) userId String user User @relation(fields: [userId], references: [id], onDelete: Cascade) mentionerId String targetType String // comment | reply | page | record targetId String context Json? readAt DateTime? createdAt DateTime @default(now()) @@index([userId, readAt]) @@map("mention_notifications") } // ============ FILES ============ model File { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) blockId String? pageId String? // plain ref name String key String @unique // object storage key url String? // public/proxy URL size Int mimeType String status FileStatus @default(UPLOADING) metadata Json @default("{}") // dimensions, duration, page count, hashes thumbnailKey String? thumbnailUrl String? scanReport Json? // virus scan result uploadedBy String permissions Json? // file-level permission overrides deletedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt block Block? @relation(fields: [blockId], references: [id], onDelete: SetNull) versions FileVersion[] @@index([workspaceId, deletedAt]) @@index([blockId]) // TASK-0135 — workspace file listing (ORDER BY createdAt DESC), EXPLAIN-proven. @@index([workspaceId, createdAt(sort: Desc)]) @@map("files") } model FileVersion { id String @id @default(cuid()) fileId String version Int key String size Int createdBy String? createdAt DateTime @default(now()) file File @relation(fields: [fileId], references: [id], onDelete: Cascade) user User? @relation(fields: [createdBy], references: [id], onDelete: SetNull) @@unique([fileId, version]) @@map("file_versions") } // ============ PERMISSIONS & SHARING ============ model Permission { id String @id @default(cuid()) workspaceId String granteeType String // "user" | "team" | "role" granteeId String // userId | teamId | role name (plain refs) resourceType String // "workspace" | "teamspace" | "page" | "database" | "record" | "file" resourceId String level PermissionLevel inheritsFrom String? // parent grant id for inheritance chains createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@unique([workspaceId, granteeType, granteeId, resourceType, resourceId]) @@index([workspaceId, granteeId]) @@index([resourceType, resourceId]) @@map("permissions") } model Share { id String @id @default(cuid()) workspaceId String pageId String? teamId String? recordId String? // plain ref token String @unique scope ShareScope @default(PRIVATE) allowComments Boolean @default(true) allowDuplicate Boolean @default(false) allowSearchIndexing Boolean @default(false) passwordHash String? expiresAt DateTime? createdBy String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) page Page? @relation(fields: [pageId], references: [id], onDelete: Cascade) team Team? @relation(fields: [teamId], references: [id], onDelete: Cascade) @@index([workspaceId, pageId]) @@index([token]) @@map("shares") } // ============ TEMPLATES ============ model Template { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) name String icon String? type String // "workspace" | "database" | "page" | "team" sourceRef Json? // {type, id} of the template source (plain refs) sourceDatabaseId String? // denormalized, plain ref snapshot Json // frozen page/database structure for instantiation isBuiltin Boolean @default(false) /// Confluence expansion (T-141/T-142): categories/descriptions drive the /// directory grouping + search; `variables` is the typed variable /// definition list for form-based instantiation (plain-text substitution /// only — never interpreted as code/HTML); `defaultFor` marks the /// workspace default template for new content ("page" | "database" | null). category String? description String? variables Json? defaultFor String? createdBy String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt versions TemplateVersion[] @@index([workspaceId, type]) @@map("templates") } model TemplateVersion { id String @id @default(cuid()) templateId String version Int snapshot Json name String? description String? variables Json? note String? createdBy String? createdAt DateTime @default(now()) template Template @relation(fields: [templateId], references: [id], onDelete: Cascade) @@unique([templateId, version]) @@map("template_versions") } // ============ AUTOMATION ============ model Automation { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) name String description String? status AutomationStatus @default(DRAFT) databaseId String? // scoped database for record triggers (plain ref) createdById String createdBy User @relation("AutomationCreator", fields: [createdById], references: [id]) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt triggers AutomationTrigger[] conditions AutomationCondition[] actions AutomationAction[] runs AutomationRun[] @@index([workspaceId, status]) @@map("automations") } model AutomationTrigger { id String @id @default(cuid()) automationId String type AutomationTriggerType config Json // property, value, cron expression, webhook id... position Int @default(0) automation Automation @relation(fields: [automationId], references: [id], onDelete: Cascade) @@index([automationId]) @@map("automation_triggers") } model AutomationCondition { id String @id @default(cuid()) automationId String expression Json // condition tree (AND/OR/NOT over property predicates) position Int @default(0) automation Automation @relation(fields: [automationId], references: [id], onDelete: Cascade) @@index([automationId]) @@map("automation_conditions") } model AutomationAction { id String @id @default(cuid()) automationId String type AutomationActionType config Json // target property, page template, email template, webhook id... position Int retries Int @default(3) timeoutMs Int @default(30000) automation Automation @relation(fields: [automationId], references: [id], onDelete: Cascade) runs AutomationRun[] @@index([automationId, position]) @@map("automation_actions") } model AutomationRun { id String @id @default(cuid()) workspaceId String automationId String actionId String? status AutomationRunStatus @default(PENDING) triggerEvent Json? input Json? output Json? error Json? startedAt DateTime? finishedAt DateTime? retryCount Int @default(0) createdAt DateTime @default(now()) automation Automation @relation(fields: [automationId], references: [id], onDelete: Cascade) action AutomationAction? @relation(fields: [actionId], references: [id], onDelete: SetNull) logs AutomationLog[] @@index([workspaceId, automationId, createdAt]) @@index([status]) @@map("automation_runs") } model AutomationLog { id String @id @default(cuid()) runId String level String // info | warn | error message String data Json? createdAt DateTime @default(now()) run AutomationRun @relation(fields: [runId], references: [id], onDelete: Cascade) @@index([runId]) @@map("automation_logs") } // ============ INTEGRATIONS / API / WEBHOOKS ============ model Integration { id String @id @default(cuid()) workspaceId String name String provider String // "slack" | "github" | "custom"... config Json // encrypted connection settings (tokens) status String @default("active") createdById String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) @@index([workspaceId]) @@map("integrations") } model ApiKey { id String @id @default(cuid()) workspaceId String name String keyHash String @unique // only hash stored; raw key shown once at creation prefix String // e.g. "fk_live_ab12..." for identification scopes ApiKeyScope[] rateLimit Int? lastUsedAt DateTime? expiresAt DateTime? revokedAt DateTime? createdById String createdAt DateTime @default(now()) workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) createdBy User @relation(fields: [createdById], references: [id], onDelete: Cascade) @@index([workspaceId]) @@map("api_keys") } model Webhook { id String @id @default(cuid()) workspaceId String workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) url String /// SHA-256 (hex) of the signing secret — bookkeeping + the incoming- /// verification path. NEVER returned by the API. TASK-0130: for legacy /// rows (pre-`secretEncrypted`) this may instead hold the AES-GCM /// ciphertext or a bare hash — see `resolveSigningSecret` (C-13.8a). secretHash String /// TASK-0130 — the RAW signing secret, AES-256-GCM-encrypted with /// MASTER_ENCRYPTION_KEY, base64(iv:tag:ciphertext). Present on every /// webhook created after this column landed (and backfilled for the /// Phase-11 rows, whose secret lived in `secretHash`). secretEncrypted Bytes? events String[] // subscribed event types active Boolean @default(true) createdById String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt deliveries WebhookDelivery[] @@index([workspaceId]) @@map("webhooks") } model WebhookDelivery { id String @id @default(cuid()) webhookId String event String payload Json status WebhookDeliveryStatus @default(PENDING) attempts Int @default(0) statusCode Int? error String? deliveredAt DateTime? nextRetryAt DateTime? createdAt DateTime @default(now()) /// TASK-0130 — wall-clock ms of the LAST delivery attempt (docs §6 rows /// carry `latencyMs`; null until the first attempt completes). latencyMs Int? /// TASK-0130 — set when this delivery was created by a RESUBMIT: the id of /// the original delivery it re-delivers (resubmit resets the attempt /// counter on the new row; the original stays put for the log). originDeliveryId String? webhook Webhook @relation(fields: [webhookId], references: [id], onDelete: Cascade) @@index([webhookId, status]) @@index([status, nextRetryAt]) /// TASK-0130 — inspector pagination (newest-first per webhook). @@index([webhookId, createdAt]) @@map("webhook_deliveries") } // ============ AUDIT ============ model AuditLog { id String @id @default(cuid()) actorType AuditActorType actorId String // plain ref: userId, apiKey id, "system" or integration id workspaceId String? pageId String? resourceType String resourceId String action String // "page.created", "permission.changed", "user.login", ... ipAddress String? userAgent String? metadata Json @default("{}") createdAt DateTime @default(now()) workspace Workspace? @relation(fields: [workspaceId], references: [id], onDelete: SetNull) page Page? @relation(fields: [pageId], references: [id], onDelete: SetNull) @@index([workspaceId, action, createdAt]) @@index([resourceType, resourceId, createdAt]) @@index([actorType, actorId, createdAt]) // TASK-0135 — workspace-wide audit listing (ORDER BY createdAt DESC), EXPLAIN-proven. @@index([workspaceId, createdAt(sort: Desc)]) @@map("audit_logs") } // ============ SEARCH ============ model SearchDocument { id String @id @default(cuid()) workspaceId String type String // "page" | "block" | "database" | "record" | "comment" | "file" | "user" refId String title String body String? permissions Json // visibility snapshot for query-time permission filtering metadata Json @default("{}") indexedAt DateTime @default(now()) // SHA-1(title + "\n" + body + "\n" + permissions + "\n" + metadata) of the // SOURCE content at index time — the reconciliation anchor (search §1.3/§7.3). contentHash String? workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) @@unique([type, refId]) @@index([workspaceId, type]) @@map("search_documents") } // ============ AI ============ model AiConversation { id String @id @default(cuid()) workspaceId String userId String title String? context Json? // {pageId, blockId, databaseId, ...} /// Phase 12 (TASK-0122): soft delete — the conversation hides from the /// list immediately and the maintenance purge job hard-deletes after 30 d /// (the existing trash convention). deletedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) user User @relation(fields: [userId], references: [id], onDelete: Cascade) messages AiMessage[] @@index([workspaceId, userId]) @@map("ai_conversations") } model AiMessage { id String @id @default(cuid()) conversationId String role String // user | assistant | system | tool content String toolCalls Json? toolResults Json? /// Phase 12 (TASK-0121/0123+): per-message metadata — the feature tag /// for hidden per-call conversations (`{feature:"writing"|"rag"|"generate"…}`), /// the truncated flag on aborted streams (`{truncated:true}`), … metadata Json? model String? tokensIn Int? tokensOut Int? latencyMs Int? createdAt DateTime @default(now()) conversation AiConversation @relation(fields: [conversationId], references: [id], onDelete: Cascade) /// Phase 12 (TASK-0121): `ai_messages` is the usage ledger — the monthly /// token rollup sums `tokensIn + tokensOut` per workspace since month- /// start, so the rollup's range scan needs the createdAt index below. @@index([conversationId]) @@index([createdAt]) @@index([conversationId, createdAt]) @@map("ai_messages") } model AiEmbedding { id String @id @default(cuid()) workspaceId String chunkRef String // {type, id} of the source chunk (plain ref) chunkText String vectorId String // vector store document id model String dimension Int metadata Json @default("{}") createdAt DateTime @default(now()) /// Phase 12 (TASK-0125): the nightly re-embed walk scans per model — /// `@@index([workspaceId, model])` keeps that walk indexed. @@unique([workspaceId, chunkRef]) @@index([workspaceId]) @@index([workspaceId, model]) @@map("ai_embeddings") } // ============ FORMS ============ model Form { id String @id @default(cuid()) workspaceId String databaseId String name String slug String @unique description String? status FormStatus @default(DRAFT) config Json @default("{}") // branding, intro, success message, closed message createdById String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt database Database @relation(fields: [databaseId], references: [id], onDelete: Cascade) questions FormQuestion[] submissions FormSubmission[] @@index([workspaceId, databaseId]) @@map("forms") } model FormQuestion { id String @id @default(cuid()) formId String label String description String? type FormQuestionType required Boolean @default(false) validation Json? // min/max, pattern, options... propertyId String? // maps to database property placeholder String? position String @default("a0") createdAt DateTime @default(now()) updatedAt DateTime @updatedAt form Form @relation(fields: [formId], references: [id], onDelete: Cascade) @@index([formId, position]) @@map("form_questions") } model FormSubmission { id String @id @default(cuid()) workspaceId String formId String recordId String? // created database record answers Json // {questionId: value} submittedBy String? // null for anonymous public submissions ipAddress String? createdAt DateTime @default(now()) workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) form Form @relation(fields: [formId], references: [id], onDelete: Cascade) record DatabaseRecord? @relation(fields: [recordId], references: [id], onDelete: SetNull) @@index([workspaceId, formId, createdAt]) @@map("form_submissions") } // ============ CONFLUENCE EXPANSION (T-144, additive) ============ // See docs/architecture/DATABASE_IMPACT.md + docs/implementation/DECISIONS.md // (A-001 page-kind whiteboard, A-006 page-backed tasks, A-007 audit-projection // activity feeds — so NO activity table here). model Whiteboard { id String @id @default(cuid()) pageId String @unique canvas Json // versioned canvas document ({schemaVersion, viewport, objects[]}) schemaVersion Int @default(1) updatedBy String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt page Page @relation(fields: [pageId], references: [id], onDelete: Cascade) @@map("whiteboards") } model Review { id String @id @default(cuid()) pageId String state String // mirrors page.reviewState while open (denormalized for queries) reviewerIds String[] @default([]) // stable user ids (A-003: explicit lists, not roles) requestedBy String comment String? deadline DateTime? decision String? // approved | changes_requested (set on close) decidedAt DateTime? decidedBy String? openedAt DateTime @default(now()) closedAt DateTime? page Page @relation(fields: [pageId], references: [id], onDelete: Cascade) @@index([pageId, state]) @@index([reviewerIds]) @@map("reviews") } model Task { id String @id @default(cuid()) workspaceId String pageId String @unique // 1:1 with pages.kind='task' (A-006): page carries blocks/comments/permissions title String assigneeIds String[] @default([]) // stable user ids createdBy String status String @default("todo") // todo | in_progress | done | cancelled dueAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt page Page @relation(fields: [pageId], references: [id], onDelete: Cascade) @@index([workspaceId, status]) @@index([assigneeIds]) @@index([workspaceId, dueAt]) @@map("tasks") } model PageWatch { id String @id @default(cuid()) pageId String userId String createdAt DateTime @default(now()) page Page @relation(fields: [pageId], references: [id], onDelete: Cascade) user User @relation(fields: [userId], references: [id], onDelete: Cascade) @@unique([pageId, userId]) @@index([userId]) @@map("page_watches") } model UserRecentItem { id String @id @default(cuid()) userId String itemType String // "page" | "database" | "whiteboard" | "task" refId String at DateTime @default(now()) user User @relation(fields: [userId], references: [id], onDelete: Cascade) @@unique([userId, itemType, refId]) @@index([userId, at]) @@map("user_recent_items") } model SavedSearch { id String @id @default(cuid()) userId String workspaceId String? // null = cross-workspace (member-scoped at query time) name String queryJson Json // the search filter/sort query (same shape as POST /search body) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt user User @relation(fields: [userId], references: [id], onDelete: Cascade) @@index([userId]) @@map("saved_searches") }