1de8985507
Adds PipelineBranchKind (NONE/APPROVED/DECLINED) on stage definitions
so producers can tag the two routes downstream of a FORMAL-approval
stage. The engine then picks exactly one branch per decision:
• Approve → APPROVED-branch children auto-open, DECLINED-branch
siblings auto-SKIPPED (grayed out, unreachable)
• Request Changes → DECLINED-branch children auto-stamped APPROVED
(passive record of the decline), APPROVED-branch siblings auto-
SKIPPED, then the existing rework edge fires as before
Also fixes a quiet bug in pipeline-template-service.addStage where
approvalType was being dropped from new stages (whitelist didn't
include it).
UI: dropdown on the stage edit sheet + branch-kind badges on the
deliverable detail page. SKIPPED rendering already grays things out.
Smoke test extended: 65/65 passing including the user's split-on-
decision case, N-way split, regression assertion that untagged
pipelines still open all children, and an idempotency check.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
1272 lines
41 KiB
Text
1272 lines
41 KiB
Text
generator client {
|
|
provider = "prisma-client"
|
|
output = "../src/generated/prisma"
|
|
}
|
|
|
|
datasource db {
|
|
provider = "postgresql"
|
|
}
|
|
|
|
// ─── Enums ──────────────────────────────────────────────
|
|
|
|
enum Role {
|
|
ADMIN
|
|
PRODUCER
|
|
ARTIST
|
|
CLIENT_VIEWER
|
|
}
|
|
|
|
enum ProjectStatus {
|
|
PIPELINE
|
|
ACTIVE
|
|
ON_HOLD
|
|
COMPLETED
|
|
CANCELED
|
|
ARCHIVED
|
|
}
|
|
|
|
enum BriefStatus {
|
|
PENDING // just arrived
|
|
REVIEWING // producer is triaging
|
|
ACCEPTED // approved but not yet a project
|
|
REJECTED // declined
|
|
CONVERTED // promoted to a Project (see convertedProjectId)
|
|
ARCHIVED
|
|
}
|
|
|
|
enum Priority {
|
|
LOW
|
|
MEDIUM
|
|
HIGH
|
|
URGENT
|
|
}
|
|
|
|
enum DeliverableStatus {
|
|
NOT_STARTED
|
|
IN_PROGRESS
|
|
IN_REVIEW
|
|
APPROVED
|
|
ON_HOLD
|
|
}
|
|
|
|
enum StageStatus {
|
|
BLOCKED
|
|
NOT_STARTED
|
|
IN_PROGRESS
|
|
IN_REVIEW
|
|
CHANGES_REQUESTED
|
|
APPROVED
|
|
DELIVERED
|
|
SKIPPED
|
|
}
|
|
|
|
enum RevisionStatus {
|
|
SUBMITTED
|
|
IN_REVIEW
|
|
CHANGES_REQUESTED
|
|
APPROVED
|
|
}
|
|
|
|
enum NotificationType {
|
|
ASSIGNMENT
|
|
STATUS_CHANGE
|
|
REVISION_SUBMITTED
|
|
REVISION_FEEDBACK
|
|
COMMENT
|
|
DEADLINE_APPROACHING
|
|
DEADLINE_OVERDUE
|
|
STAGE_UNBLOCKED
|
|
BOX_UNMATCHED_FILE
|
|
NEW_FILE_AWAITING_REVIEWER
|
|
}
|
|
|
|
enum BoxPushStatus {
|
|
PENDING
|
|
SUCCESS
|
|
FAILED
|
|
}
|
|
|
|
enum BoxInboundStatus {
|
|
MATCHED
|
|
UNMATCHED
|
|
ERROR
|
|
}
|
|
|
|
enum AssignmentRole {
|
|
LEAD
|
|
SUPPORT
|
|
}
|
|
|
|
enum SkillLevel {
|
|
JUNIOR
|
|
INTERMEDIATE
|
|
SENIOR
|
|
LEAD
|
|
}
|
|
|
|
// ─── RBAC ──────────────────────────────────────────────
|
|
|
|
model OrgRolePermission {
|
|
id String @id @default(cuid())
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
role Role
|
|
permission Permission
|
|
|
|
@@unique([organizationId, role, permission])
|
|
@@index([organizationId])
|
|
@@map("org_role_permissions")
|
|
}
|
|
|
|
// ─── Organization ───────────────────────────────────────
|
|
|
|
model Organization {
|
|
id String @id @default(cuid())
|
|
name String
|
|
domain String @unique
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
users User[]
|
|
projects Project[]
|
|
automationRules AutomationRule[]
|
|
rolePermissions OrgRolePermission[]
|
|
pipelineTemplates PipelineTemplate[]
|
|
deliverables Deliverable[]
|
|
deliverableStages DeliverableStage[]
|
|
invitations Invitation[]
|
|
customFieldDefs CustomFieldDefinition[]
|
|
notificationRules NotificationRule[]
|
|
clientTeams ClientTeam[]
|
|
pods Pod[]
|
|
resourceBookings ResourceBooking[]
|
|
briefs Brief[]
|
|
|
|
@@map("organizations")
|
|
}
|
|
|
|
// ─── Auth.js models ─────────────────────────────────────
|
|
|
|
model User {
|
|
id String @id @default(cuid())
|
|
name String?
|
|
email String @unique
|
|
emailVerified DateTime?
|
|
image String?
|
|
role Role @default(ARTIST)
|
|
department String?
|
|
maxCapacity Int @default(5)
|
|
|
|
// Local auth (MVP) — Entra SSO coexists via nullable passwordHash
|
|
passwordHash String?
|
|
passwordResetToken String? @unique
|
|
passwordResetExpires DateTime?
|
|
mustChangePassword Boolean @default(true)
|
|
lastLoginAt DateTime?
|
|
isExternal Boolean @default(false) // fast filter for CLIENT_VIEWER users
|
|
|
|
organizationId String?
|
|
organization Organization? @relation(fields: [organizationId], references: [id])
|
|
|
|
homePodId String?
|
|
homePod Pod? @relation("PodHome", fields: [homePodId], references: [id])
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
accounts Account[]
|
|
sessions Session[]
|
|
assignments StageAssignment[]
|
|
comments Comment[]
|
|
notifications Notification[]
|
|
skills UserSkill[]
|
|
searchLogs SearchLog[]
|
|
automationRules AutomationRule[] @relation("AutomationCreator")
|
|
chatMessages ChatMessage[]
|
|
invitationsSent Invitation[] @relation("InvitedBy")
|
|
annotations Annotation[]
|
|
clientTeams ClientTeamMembership[]
|
|
podsLed Pod[] @relation("PodLead")
|
|
bookings ResourceBooking[] @relation("BookingResource")
|
|
bookingsCreated ResourceBooking[] @relation("BookingCreator")
|
|
attachmentsCreated DeliverableAttachment[]
|
|
projectsOwned Project[] @relation("ProjectOwner")
|
|
|
|
feedbackCreated FeedbackItem[] @relation("FeedbackCreator")
|
|
feedbackAssigned FeedbackItem[] @relation("FeedbackAssignee")
|
|
feedbackResolved FeedbackItem[] @relation("FeedbackResolver")
|
|
feedbackVerified FeedbackItem[] @relation("FeedbackVerifier")
|
|
reviewSessionsCreated ReviewSession[] @relation("ReviewSessionCreator")
|
|
reviewSessionDecisions ReviewSessionItem[] @relation("ReviewSessionDecider")
|
|
|
|
@@index([homePodId])
|
|
@@index([isExternal])
|
|
@@map("users")
|
|
}
|
|
|
|
model Account {
|
|
id String @id @default(cuid())
|
|
userId String
|
|
type String
|
|
provider String
|
|
providerAccountId String
|
|
refresh_token String? @db.Text
|
|
access_token String? @db.Text
|
|
expires_at Int?
|
|
token_type String?
|
|
scope String?
|
|
id_token String? @db.Text
|
|
session_state String?
|
|
|
|
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([provider, providerAccountId])
|
|
@@map("accounts")
|
|
}
|
|
|
|
model Session {
|
|
id String @id @default(cuid())
|
|
sessionToken String @unique
|
|
userId String
|
|
expires DateTime
|
|
|
|
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
|
|
|
|
@@map("sessions")
|
|
}
|
|
|
|
model VerificationToken {
|
|
identifier String
|
|
token String
|
|
expires DateTime
|
|
|
|
@@unique([identifier, token])
|
|
@@map("verification_tokens")
|
|
}
|
|
|
|
// ─── Pipeline Templates (seed data) ────────────────────
|
|
|
|
model PipelineStageTemplate {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
slug String @unique
|
|
order Int @unique
|
|
isCriticalGate Boolean @default(false)
|
|
isOptional Boolean @default(false)
|
|
description String?
|
|
estimatedDays Float?
|
|
approvalType ApprovalType @default(NONE)
|
|
|
|
dependsOn PipelineStageDependency[] @relation("DependsOnStage")
|
|
dependedBy PipelineStageDependency[] @relation("PrerequisiteStage")
|
|
|
|
deliverableStages DeliverableStage[]
|
|
skillRequirements StageSkillRequirement[]
|
|
|
|
@@map("pipeline_stage_templates")
|
|
}
|
|
|
|
model PipelineStageDependency {
|
|
id String @id @default(cuid())
|
|
stageId String
|
|
prerequisiteId String
|
|
|
|
stage PipelineStageTemplate @relation("DependsOnStage", fields: [stageId], references: [id])
|
|
prerequisite PipelineStageTemplate @relation("PrerequisiteStage", fields: [prerequisiteId], references: [id])
|
|
|
|
@@unique([stageId, prerequisiteId])
|
|
@@map("pipeline_stage_dependencies")
|
|
}
|
|
|
|
// ─── Dynamic Pipeline Templates (org-scoped) ───────────
|
|
|
|
model PipelineTemplate {
|
|
id String @id @default(cuid())
|
|
name String
|
|
description String?
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
isArchived Boolean @default(false)
|
|
isDefault Boolean @default(false)
|
|
|
|
// Regex applied to incoming filenames (Box webhook / API upload / manual
|
|
// upload-by-filename) to extract OMG # + slug + optional version.
|
|
// Null = use the system default
|
|
// `^(\d+)_([a-z0-9-]+)(?:_v(\d+))?(?:\.[a-z0-9]+)?$`. Editable per pipeline
|
|
// from the pipeline editor. See external-delivery-service.parseInboundFileName.
|
|
inboundFilenameRegex String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
stages PipelineStageDefinition[]
|
|
projects Project[]
|
|
|
|
@@unique([organizationId, name])
|
|
@@index([organizationId])
|
|
@@map("pipeline_templates")
|
|
}
|
|
|
|
model PipelineStageDefinition {
|
|
id String @id @default(cuid())
|
|
pipelineId String
|
|
pipeline PipelineTemplate @relation(fields: [pipelineId], references: [id], onDelete: Cascade)
|
|
name String
|
|
slug String
|
|
order Int
|
|
isCriticalGate Boolean @default(false)
|
|
isOptional Boolean @default(false)
|
|
description String?
|
|
estimatedDays Float?
|
|
color String?
|
|
customStatuses Json?
|
|
approvalType ApprovalType @default(NONE)
|
|
// Outcome-branch tag. NONE = normal stage. APPROVED / DECLINED mark
|
|
// this stage as the "approved path" or "declined path" downstream of
|
|
// a FORMAL parent. Only one branch activates per decision; the other
|
|
// is auto-SKIPPED. See dependency-engine.getBranchSplitForParent.
|
|
branchKind PipelineBranchKind @default(NONE)
|
|
|
|
dependsOn PipelineStageDependencyV2[] @relation("DependsOnStageV2")
|
|
dependedBy PipelineStageDependencyV2[] @relation("PrerequisiteStageV2")
|
|
|
|
// Declared "rework" / pushback paths — explicit opt-in backward
|
|
// transitions for rejection handling. Example: from Final Approval you
|
|
// can push back to In Progress Creative when something fails approval.
|
|
// Without an entry here a stage can ONLY move forward through the
|
|
// pipeline (along with the existing dependsOn graph).
|
|
reworkFrom PipelineStageRework[] @relation("ReworkFromStage")
|
|
reworkTo PipelineStageRework[] @relation("ReworkToStage")
|
|
|
|
deliverableStages DeliverableStage[]
|
|
deliverableAttachments DeliverableAttachment[]
|
|
|
|
@@unique([pipelineId, slug])
|
|
@@unique([pipelineId, order])
|
|
@@map("pipeline_stage_definitions")
|
|
}
|
|
|
|
model PipelineStageDependencyV2 {
|
|
id String @id @default(cuid())
|
|
stageId String
|
|
prerequisiteId String
|
|
|
|
stage PipelineStageDefinition @relation("DependsOnStageV2", fields: [stageId], references: [id], onDelete: Cascade)
|
|
prerequisite PipelineStageDefinition @relation("PrerequisiteStageV2", fields: [prerequisiteId], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([stageId, prerequisiteId])
|
|
@@map("pipeline_stage_dependencies_v2")
|
|
}
|
|
|
|
// Explicit backward "rework" transitions on a pipeline template.
|
|
// Distinct from dependsOn (which captures forward prerequisites): this
|
|
// table captures "from stage X you are allowed to push a deliverable
|
|
// back to stage Y". Board drag validates drops against this list.
|
|
model PipelineStageRework {
|
|
id String @id @default(cuid())
|
|
fromStageId String
|
|
toStageId String
|
|
|
|
fromStage PipelineStageDefinition @relation("ReworkFromStage", fields: [fromStageId], references: [id], onDelete: Cascade)
|
|
toStage PipelineStageDefinition @relation("ReworkToStage", fields: [toStageId], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([fromStageId, toStageId])
|
|
@@index([fromStageId])
|
|
@@map("pipeline_stage_reworks")
|
|
}
|
|
|
|
// ─── Project ────────────────────────────────────────────
|
|
|
|
model Project {
|
|
id String @id @default(cuid())
|
|
projectCode String @unique
|
|
name String
|
|
description String?
|
|
status ProjectStatus @default(ACTIVE)
|
|
priority Priority @default(MEDIUM)
|
|
startDate DateTime?
|
|
dueDate DateTime?
|
|
businessUnit String?
|
|
formFactor String?
|
|
codeName String?
|
|
npiOrRefresh String?
|
|
quarter String?
|
|
// Freeform owner name from XLSX/webhook intake. Used as a fallback
|
|
// label when no matching user exists on the system (e.g. the upstream
|
|
// Owner column contains a client-side name). Once we can link it to
|
|
// a real user, populate requestorUserId and display that instead.
|
|
requestor String?
|
|
requestorUserId String?
|
|
workfrontId String?
|
|
omgCode String?
|
|
bmtId String?
|
|
estimatedCost Float?
|
|
actualCost Float?
|
|
agency String?
|
|
|
|
// Dow-specific: upstream OMG job number (canonical key for XLSX + webhook ingest)
|
|
omgJobNumber String? @unique
|
|
|
|
// pgvector embedding for semantic search (raw SQL — Prisma can't query this directly)
|
|
embedding Unsupported("vector(768)")?
|
|
customFields Json?
|
|
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id])
|
|
|
|
clientTeamId String?
|
|
clientTeam ClientTeam? @relation(fields: [clientTeamId], references: [id])
|
|
|
|
requestorUser User? @relation("ProjectOwner", fields: [requestorUserId], references: [id])
|
|
|
|
pipelineTemplateId String?
|
|
pipelineTemplate PipelineTemplate? @relation(fields: [pipelineTemplateId], references: [id])
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
deliverables Deliverable[]
|
|
convertedFrom Brief[] @relation("BriefConvertedProject")
|
|
|
|
@@index([organizationId])
|
|
@@index([clientTeamId])
|
|
@@index([pipelineTemplateId])
|
|
@@index([status])
|
|
@@map("projects")
|
|
}
|
|
|
|
// ─── Deliverable ────────────────────────────────────────
|
|
|
|
model Deliverable {
|
|
id String @id @default(cuid())
|
|
name String
|
|
// OMG's per-deliverable identifier. This is what shows up as the
|
|
// leading digits in the inbound filename convention — e.g.
|
|
// "77777778_homepage-banner_v2.jpg" means OMG deliverable #77777778.
|
|
// Unique per organisation. Optional for legacy / manually-created rows
|
|
// until an OMG number is assigned.
|
|
omgJobNumber String?
|
|
status DeliverableStatus @default(NOT_STARTED)
|
|
priority Priority @default(MEDIUM)
|
|
dueDate DateTime?
|
|
notes String?
|
|
cmfSku String?
|
|
assetCount Int?
|
|
requestedDueDate DateTime?
|
|
plannedDeliveryDate DateTime?
|
|
actualDeliveryDate DateTime?
|
|
wfInputDate DateTime?
|
|
|
|
// pgvector embedding for semantic search (raw SQL — Prisma can't query this directly)
|
|
embedding Unsupported("vector(768)")?
|
|
customFields Json?
|
|
|
|
projectId String
|
|
project Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
|
|
organizationId String?
|
|
organization Organization? @relation(fields: [organizationId], references: [id])
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
stages DeliverableStage[]
|
|
attachments DeliverableAttachment[]
|
|
boxPushLogs BoxPushLog[]
|
|
holdingPenFiles HoldingPenFile[]
|
|
|
|
// Optional override for the inbound matcher slug. Producers can pin an
|
|
// alias if the auto-slugified deliverable name doesn't match what arrives
|
|
// via Box / API. Default null = use slugify(name).
|
|
boxAliasSlug String?
|
|
|
|
@@index([projectId])
|
|
@@index([organizationId])
|
|
@@index([status])
|
|
@@index([omgJobNumber])
|
|
@@unique([organizationId, omgJobNumber])
|
|
@@map("deliverables")
|
|
}
|
|
|
|
// ─── Deliverable Attachments (files + external links) ───
|
|
//
|
|
// Producers upload PDFs, design exports, and other asset files; they
|
|
// also paste Figma / Drive / Dropbox links that should live alongside.
|
|
// Two kinds share one row: file (uploaded to /data/uploads/deliverables)
|
|
// vs link (external URL). An optional stageDefinitionId tags an asset
|
|
// to a specific pipeline stage; null means it applies to the whole
|
|
// deliverable.
|
|
model DeliverableAttachment {
|
|
id String @id @default(cuid())
|
|
deliverableId String
|
|
deliverable Deliverable @relation(fields: [deliverableId], references: [id], onDelete: Cascade)
|
|
|
|
kind String // "file" | "link"
|
|
title String
|
|
url String // local /api/uploads/... or external https://...
|
|
|
|
mimeType String?
|
|
fileSize Int?
|
|
thumbnailUrl String?
|
|
|
|
stageDefinitionId String?
|
|
stageDefinition PipelineStageDefinition? @relation(fields: [stageDefinitionId], references: [id])
|
|
|
|
createdById String
|
|
createdBy User @relation(fields: [createdById], references: [id])
|
|
createdAt DateTime @default(now())
|
|
|
|
@@index([deliverableId])
|
|
@@index([stageDefinitionId])
|
|
@@map("deliverable_attachments")
|
|
}
|
|
|
|
// ─── Deliverable Stage (instance per deliverable) ───────
|
|
|
|
model DeliverableStage {
|
|
id String @id @default(cuid())
|
|
status StageStatus @default(BLOCKED)
|
|
revisionRound Int @default(0)
|
|
startDate DateTime?
|
|
completedDate DateTime?
|
|
dueDate DateTime?
|
|
notes String?
|
|
subStatus String?
|
|
manualSchedule Boolean @default(false)
|
|
scheduleConflict Boolean @default(false)
|
|
scheduleDelta Int?
|
|
|
|
deliverableId String
|
|
deliverable Deliverable @relation(fields: [deliverableId], references: [id], onDelete: Cascade)
|
|
|
|
templateId String
|
|
template PipelineStageTemplate @relation(fields: [templateId], references: [id])
|
|
|
|
stageDefinitionId String?
|
|
stageDefinition PipelineStageDefinition? @relation(fields: [stageDefinitionId], references: [id])
|
|
|
|
organizationId String?
|
|
organization Organization? @relation(fields: [organizationId], references: [id])
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
assignments StageAssignment[]
|
|
revisions Revision[]
|
|
comments Comment[]
|
|
feedbackItems FeedbackItem[]
|
|
reviewSessionItems ReviewSessionItem[]
|
|
suggestedHoldingPenFiles HoldingPenFile[]
|
|
|
|
@@unique([deliverableId, stageDefinitionId])
|
|
@@index([deliverableId])
|
|
@@index([stageDefinitionId])
|
|
@@index([organizationId])
|
|
@@index([status])
|
|
@@map("deliverable_stages")
|
|
}
|
|
|
|
// ─── Stage Assignment ───────────────────────────────────
|
|
|
|
model StageAssignment {
|
|
id String @id @default(cuid())
|
|
role AssignmentRole? @default(LEAD)
|
|
|
|
deliverableStageId String
|
|
deliverableStage DeliverableStage @relation(fields: [deliverableStageId], references: [id], onDelete: Cascade)
|
|
|
|
userId String
|
|
user User @relation(fields: [userId], references: [id])
|
|
|
|
createdAt DateTime @default(now())
|
|
|
|
@@unique([deliverableStageId, userId])
|
|
@@index([userId])
|
|
@@map("stage_assignments")
|
|
}
|
|
|
|
// ─── Revision ───────────────────────────────────────────
|
|
//
|
|
// Versioning model:
|
|
// - `major` starts at 0; bumps to 1 on first "Send to client"; etc.
|
|
// - `minor` counts internal iterations within the current major. Resets to 0
|
|
// on send.
|
|
// - `sentToClient + minor === 0` means this revision IS a client-visible
|
|
// V{major}. Otherwise it's an internal V{major}.{minor}.
|
|
// - One asset per revision — image OR video — stored in the `asset` JSON.
|
|
// Kind auto-detected from MIME at upload time. References, multi-key
|
|
// attachments, and screenshots-as-asset are gone (screenshots remain a
|
|
// separate annotation feature).
|
|
|
|
model Revision {
|
|
id String @id @default(cuid())
|
|
status RevisionStatus @default(SUBMITTED)
|
|
feedbackNotes String?
|
|
internalNotes String?
|
|
|
|
// Per-stage version coordinates. See block comment above for semantics.
|
|
major Int @default(0)
|
|
minor Int @default(1)
|
|
sentToClient Boolean @default(false)
|
|
sentAt DateTime?
|
|
|
|
// Single asset: { kind: "image" | "video", url, mimeType, size }.
|
|
// Null when the row exists but no upload has happened yet.
|
|
asset Json?
|
|
|
|
deliverableStageId String
|
|
deliverableStage DeliverableStage @relation(fields: [deliverableStageId], references: [id], onDelete: Cascade)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
annotations Annotation[]
|
|
feedbackItems FeedbackItem[]
|
|
reviewSessionItems ReviewSessionItem[]
|
|
boxPushLogs BoxPushLog[]
|
|
|
|
// Set when this revision's asset has been pushed to Box (outbound).
|
|
// Format: the Box folder id under which the asset was uploaded.
|
|
boxFolderId String?
|
|
|
|
@@unique([deliverableStageId, major, minor])
|
|
@@index([deliverableStageId])
|
|
@@map("revisions")
|
|
}
|
|
|
|
// ─── Holding Pen ────────────────────────────────────────
|
|
//
|
|
// Files that arrive (via Box webhook / API / manual upload-by-filename) for
|
|
// a deliverable that's currently IN_REVIEW. They don't overwrite the
|
|
// in-flight V{n} — they wait here until the producer manually promotes them
|
|
// to a new revision (creating the next minor in the chain) or discards.
|
|
|
|
enum HoldingPenSource {
|
|
MANUAL
|
|
API
|
|
BOX
|
|
}
|
|
|
|
model HoldingPenFile {
|
|
id String @id @default(cuid())
|
|
deliverableId String
|
|
deliverable Deliverable @relation(fields: [deliverableId], references: [id], onDelete: Cascade)
|
|
|
|
// Best-guess stage at intake time. Producer can override on promote.
|
|
suggestedStageId String?
|
|
suggestedStage DeliverableStage? @relation(fields: [suggestedStageId], references: [id])
|
|
|
|
fileName String
|
|
asset Json // { kind: "image"|"video", url, mimeType, size }
|
|
source HoldingPenSource
|
|
parsedOmgNumber String?
|
|
parsedSlug String?
|
|
parsedVersion Int?
|
|
|
|
receivedAt DateTime @default(now())
|
|
|
|
@@index([deliverableId])
|
|
@@map("holding_pen_files")
|
|
}
|
|
|
|
// ─── Comment ────────────────────────────────────────────
|
|
|
|
model Comment {
|
|
id String @id @default(cuid())
|
|
content String @db.Text
|
|
|
|
deliverableStageId String
|
|
deliverableStage DeliverableStage @relation(fields: [deliverableStageId], references: [id], onDelete: Cascade)
|
|
|
|
authorId String
|
|
author User @relation(fields: [authorId], references: [id])
|
|
|
|
parentId String?
|
|
parent Comment? @relation("CommentThread", fields: [parentId], references: [id])
|
|
replies Comment[] @relation("CommentThread")
|
|
|
|
annotations Annotation[]
|
|
feedbackItems FeedbackItem[]
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([deliverableStageId])
|
|
@@index([parentId])
|
|
@@map("comments")
|
|
}
|
|
|
|
// ─── Notification ───────────────────────────────────────
|
|
|
|
model Notification {
|
|
id String @id @default(cuid())
|
|
type NotificationType
|
|
title String
|
|
message String
|
|
link String?
|
|
isRead Boolean @default(false)
|
|
|
|
userId String
|
|
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
|
|
|
|
createdAt DateTime @default(now())
|
|
|
|
@@index([userId, isRead])
|
|
@@map("notifications")
|
|
}
|
|
|
|
// ─── Skills & Capacity (Phase 6) ────────────────────────
|
|
|
|
model Skill {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
createdAt DateTime @default(now())
|
|
|
|
users UserSkill[]
|
|
stageRequirements StageSkillRequirement[]
|
|
|
|
@@map("skills")
|
|
}
|
|
|
|
model UserSkill {
|
|
userId String
|
|
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
|
|
skillId String
|
|
skill Skill @relation(fields: [skillId], references: [id], onDelete: Cascade)
|
|
level SkillLevel @default(INTERMEDIATE)
|
|
|
|
@@id([userId, skillId])
|
|
@@map("user_skills")
|
|
}
|
|
|
|
model StageSkillRequirement {
|
|
stageTemplateId String
|
|
stageTemplate PipelineStageTemplate @relation(fields: [stageTemplateId], references: [id], onDelete: Cascade)
|
|
skillId String
|
|
skill Skill @relation(fields: [skillId], references: [id], onDelete: Cascade)
|
|
importance Int @default(1) // 1=nice-to-have, 2=important, 3=required
|
|
|
|
@@id([stageTemplateId, skillId])
|
|
@@map("stage_skill_requirements")
|
|
}
|
|
|
|
// ─── Automation Engine (Phase 7.1) ──────────────────────
|
|
|
|
model AutomationRule {
|
|
id String @id @default(cuid())
|
|
name String
|
|
description String?
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id])
|
|
isEnabled Boolean @default(true)
|
|
trigger Json // { event, conditions[] }
|
|
actions Json // [{ type, params }]
|
|
createdById String
|
|
createdBy User @relation("AutomationCreator", fields: [createdById], references: [id])
|
|
executions AutomationExecution[]
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([organizationId])
|
|
@@index([isEnabled])
|
|
@@map("automation_rules")
|
|
}
|
|
|
|
model AutomationExecution {
|
|
id String @id @default(cuid())
|
|
ruleId String
|
|
rule AutomationRule @relation(fields: [ruleId], references: [id], onDelete: Cascade)
|
|
triggeredBy Json // the event payload that triggered execution
|
|
result Json // what actions were taken + outcomes
|
|
status ExecutionStatus
|
|
error String?
|
|
executedAt DateTime @default(now())
|
|
|
|
@@index([ruleId])
|
|
@@index([executedAt])
|
|
@@map("automation_executions")
|
|
}
|
|
|
|
enum ExecutionStatus {
|
|
SUCCESS
|
|
PARTIAL_FAILURE
|
|
FAILURE
|
|
}
|
|
|
|
enum Permission {
|
|
PROJECT_CREATE
|
|
PROJECT_UPDATE
|
|
PROJECT_DELETE
|
|
PROJECT_VIEW
|
|
DELIVERABLE_VIEW
|
|
DELIVERABLE_CREATE
|
|
DELIVERABLE_UPDATE
|
|
DELIVERABLE_DELETE
|
|
STAGE_VIEW
|
|
STAGE_UPDATE
|
|
STAGE_UPDATE_STATUS
|
|
STAGE_ASSIGN
|
|
STAGE_SCHEDULE
|
|
REVISION_CREATE
|
|
REVISION_UPDATE
|
|
REVISION_REVIEW
|
|
COMMENT_CREATE
|
|
COMMENT_DELETE
|
|
COMMENT_DELETE_ANY
|
|
PIPELINE_MANAGE
|
|
USER_MANAGE
|
|
ROLE_MANAGE
|
|
ORG_SETTINGS
|
|
AUTOMATION_MANAGE
|
|
FIELD_CUSTOMIZE
|
|
CLIENT_TEAM_MANAGE
|
|
POD_MANAGE
|
|
}
|
|
|
|
// ─── Chat History (CLI Anything) ────────────────────────
|
|
|
|
model ChatMessage {
|
|
id String @id @default(cuid())
|
|
sessionId String
|
|
role String // "user" | "assistant" | "system"
|
|
content String @db.Text
|
|
toolCalls Json? // tool calls made by assistant
|
|
toolResults Json? // results of tool execution
|
|
metadata Json? // context: active project, etc.
|
|
userId String
|
|
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
|
|
organizationId String
|
|
createdAt DateTime @default(now())
|
|
|
|
@@index([sessionId])
|
|
@@index([userId])
|
|
@@map("chat_messages")
|
|
}
|
|
|
|
// ─── Custom Fields ──────────────────────────────────────
|
|
|
|
model CustomFieldDefinition {
|
|
id String @id @default(cuid())
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
entityType String // "PROJECT" | "DELIVERABLE"
|
|
fieldName String
|
|
fieldType String // "TEXT" | "NUMBER" | "DATE" | "SELECT" | "BOOLEAN"
|
|
fieldOptions Json? // For SELECT type: { options: string[] }
|
|
isRequired Boolean @default(false)
|
|
order Int @default(0)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@unique([organizationId, entityType, fieldName])
|
|
@@index([organizationId])
|
|
@@map("custom_field_definitions")
|
|
}
|
|
|
|
// ─── Notification Rules ─────────────────────────────────
|
|
|
|
model NotificationRule {
|
|
id String @id @default(cuid())
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
name String
|
|
isEnabled Boolean @default(true)
|
|
event String // e.g. "STAGE_STATUS_CHANGE", "DEADLINE_APPROACHING", "REVISION_SUBMITTED"
|
|
conditions Json? // { field: string, operator: string, value: any }[]
|
|
channels Json // ["IN_APP", "EMAIL"]
|
|
recipientRoles Json // ["ADMIN", "PRODUCER"] or ["ASSIGNEE"]
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([organizationId])
|
|
@@index([event])
|
|
@@map("notification_rules")
|
|
}
|
|
|
|
// ─── Invitations ────────────────────────────────────────
|
|
|
|
model Invitation {
|
|
id String @id @default(cuid())
|
|
email String
|
|
role Role @default(ARTIST)
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
invitedById String
|
|
invitedBy User @relation("InvitedBy", fields: [invitedById], references: [id])
|
|
token String @unique @default(cuid())
|
|
expiresAt DateTime
|
|
acceptedAt DateTime?
|
|
|
|
createdAt DateTime @default(now())
|
|
|
|
@@unique([email, organizationId])
|
|
@@index([organizationId])
|
|
@@index([token])
|
|
@@map("invitations")
|
|
}
|
|
|
|
// ─── Semantic Search (Phase 8.4) ────────────────────────
|
|
|
|
model SearchLog {
|
|
id String @id @default(cuid())
|
|
userId String
|
|
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
|
|
query String
|
|
resultCount Int @default(0)
|
|
clickedId String?
|
|
createdAt DateTime @default(now())
|
|
|
|
@@index([userId])
|
|
@@map("search_logs")
|
|
}
|
|
|
|
// ─── Annotations (Visual Review) ────────────────────────
|
|
|
|
enum AnnotationType {
|
|
RECTANGLE
|
|
ELLIPSE
|
|
ARROW
|
|
FREEHAND
|
|
TEXT
|
|
PIN
|
|
SCREENSHOT
|
|
}
|
|
|
|
// ─── Annotation ─────────────────────────────────────────
|
|
|
|
model Annotation {
|
|
id String @id @default(cuid())
|
|
commentId String
|
|
comment Comment @relation(fields: [commentId], references: [id], onDelete: Cascade)
|
|
revisionId String
|
|
revision Revision @relation(fields: [revisionId], references: [id], onDelete: Cascade)
|
|
type AnnotationType
|
|
data Json
|
|
imageX Float
|
|
imageY Float
|
|
timestampSeconds Float? // null for image annotations, set for video annotations
|
|
frameThumbnailUrl String? // cached frame thumbnail for the annotation's moment
|
|
createdById String
|
|
createdBy User @relation(fields: [createdById], references: [id])
|
|
createdAt DateTime @default(now())
|
|
|
|
feedbackItems FeedbackItem[]
|
|
|
|
@@index([commentId])
|
|
@@index([revisionId])
|
|
@@index([revisionId, timestampSeconds])
|
|
@@map("annotations")
|
|
}
|
|
|
|
// ─── Approval Type (per pipeline stage) ─────────────────
|
|
|
|
enum ApprovalType {
|
|
NONE
|
|
SIMPLE
|
|
FORMAL
|
|
}
|
|
|
|
// ─── Branch Kind (outcome routing) ──────────────────────
|
|
|
|
enum PipelineBranchKind {
|
|
NONE
|
|
APPROVED
|
|
DECLINED
|
|
}
|
|
|
|
// ─── Feedback Items ────────────────────────────────────
|
|
|
|
enum FeedbackStatus {
|
|
OPEN
|
|
IN_PROGRESS
|
|
RESOLVED
|
|
VERIFIED
|
|
REOPENED
|
|
}
|
|
|
|
model FeedbackItem {
|
|
id String @id @default(cuid())
|
|
deliverableStageId String
|
|
deliverableStage DeliverableStage @relation(fields: [deliverableStageId], references: [id], onDelete: Cascade)
|
|
revisionId String
|
|
revision Revision @relation(fields: [revisionId], references: [id], onDelete: Cascade)
|
|
annotationId String?
|
|
annotation Annotation? @relation(fields: [annotationId], references: [id], onDelete: SetNull)
|
|
commentId String?
|
|
comment Comment? @relation(fields: [commentId], references: [id], onDelete: SetNull)
|
|
summary String
|
|
isActionItem Boolean @default(true)
|
|
status FeedbackStatus @default(OPEN)
|
|
sortOrder Int @default(0)
|
|
assignedToId String?
|
|
assignedTo User? @relation("FeedbackAssignee", fields: [assignedToId], references: [id], onDelete: SetNull)
|
|
createdById String
|
|
createdBy User @relation("FeedbackCreator", fields: [createdById], references: [id])
|
|
resolvedById String?
|
|
resolvedBy User? @relation("FeedbackResolver", fields: [resolvedById], references: [id], onDelete: SetNull)
|
|
resolvedAt DateTime?
|
|
resolutionNote String?
|
|
verifiedById String?
|
|
verifiedBy User? @relation("FeedbackVerifier", fields: [verifiedById], references: [id], onDelete: SetNull)
|
|
verifiedAt DateTime?
|
|
carriedFromId String?
|
|
carriedFrom FeedbackItem? @relation("FeedbackCarry", fields: [carriedFromId], references: [id], onDelete: SetNull)
|
|
carriedTo FeedbackItem[] @relation("FeedbackCarry")
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([deliverableStageId])
|
|
@@index([revisionId])
|
|
@@index([assignedToId])
|
|
@@index([status])
|
|
@@map("feedback_items")
|
|
}
|
|
|
|
// ─── Review Sessions (batch approval) ───────────────────
|
|
|
|
enum ReviewSessionStatus {
|
|
DRAFT
|
|
IN_PROGRESS
|
|
COMPLETED
|
|
}
|
|
|
|
model ReviewSession {
|
|
id String @id @default(cuid())
|
|
name String
|
|
description String?
|
|
status ReviewSessionStatus @default(DRAFT)
|
|
|
|
createdById String
|
|
createdBy User @relation("ReviewSessionCreator", fields: [createdById], references: [id])
|
|
organizationId String
|
|
|
|
items ReviewSessionItem[]
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([organizationId])
|
|
@@index([status])
|
|
@@map("review_sessions")
|
|
}
|
|
|
|
model ReviewSessionItem {
|
|
id String @id @default(cuid())
|
|
sessionId String
|
|
session ReviewSession @relation(fields: [sessionId], references: [id], onDelete: Cascade)
|
|
deliverableStageId String
|
|
deliverableStage DeliverableStage @relation(fields: [deliverableStageId], references: [id])
|
|
revisionId String?
|
|
revision Revision? @relation(fields: [revisionId], references: [id])
|
|
sortOrder Int
|
|
decision String?
|
|
decisionNote String?
|
|
decidedById String?
|
|
decidedBy User? @relation("ReviewSessionDecider", fields: [decidedById], references: [id])
|
|
decidedAt DateTime?
|
|
|
|
@@index([sessionId])
|
|
@@map("review_session_items")
|
|
}
|
|
|
|
// ─── External API idempotency ──────────────────────────
|
|
//
|
|
// Stores the response body of POSTs that carried an `Idempotency-Key`
|
|
// header so a retried request returns the same response without
|
|
// re-executing. Indexed for fast lookup; createdAt drives the 24-hour
|
|
// TTL sweep.
|
|
|
|
model IdempotencyRecord {
|
|
key String
|
|
route String
|
|
requestHash String
|
|
responseBody Json
|
|
statusCode Int
|
|
createdAt DateTime @default(now())
|
|
|
|
@@id([key, route])
|
|
@@index([createdAt])
|
|
@@map("idempotency_records")
|
|
}
|
|
|
|
// ─── Box integration (bidirectional asset transport) ────
|
|
//
|
|
// Outbound: when a deliverable hits APPROVED, push its latest revision's
|
|
// assets into a Box "In/" sub-folder using a strict naming convention
|
|
// (omgJobNumber_deliverableSlug_v{round}). Inbound: Box notifies us when
|
|
// a new file lands in the watch folder; we match by OMG # + slug and
|
|
// attach as a new revision.
|
|
//
|
|
// Both directions log every attempt. See [[project-box-integration]].
|
|
|
|
model BoxPushLog {
|
|
id String @id @default(cuid())
|
|
deliverableId String
|
|
deliverable Deliverable @relation(fields: [deliverableId], references: [id], onDelete: Cascade)
|
|
revisionId String?
|
|
revision Revision? @relation(fields: [revisionId], references: [id], onDelete: SetNull)
|
|
boxFolderId String?
|
|
status BoxPushStatus @default(PENDING)
|
|
attempt Int @default(1)
|
|
error String?
|
|
sentAt DateTime @default(now())
|
|
|
|
@@index([deliverableId])
|
|
@@index([status])
|
|
@@map("box_push_logs")
|
|
}
|
|
|
|
model BoxInboundLog {
|
|
id String @id @default(cuid())
|
|
boxFileId String
|
|
fileName String
|
|
matchedDeliverableId String?
|
|
matchedProjectId String?
|
|
status BoxInboundStatus
|
|
error String?
|
|
receivedAt DateTime @default(now())
|
|
|
|
@@index([status])
|
|
@@index([receivedAt])
|
|
@@map("box_inbound_logs")
|
|
}
|
|
|
|
// ─── Dow: Client Teams (visibility grouping) ───────────
|
|
|
|
model ClientTeam {
|
|
id String @id @default(cuid())
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
name String
|
|
slug String
|
|
createdAt DateTime @default(now())
|
|
|
|
userMemberships ClientTeamMembership[]
|
|
projects Project[]
|
|
briefs Brief[]
|
|
|
|
@@unique([organizationId, slug])
|
|
@@index([organizationId])
|
|
@@map("client_teams")
|
|
}
|
|
|
|
model ClientTeamMembership {
|
|
userId String
|
|
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
|
|
clientTeamId String
|
|
clientTeam ClientTeam @relation(fields: [clientTeamId], references: [id], onDelete: Cascade)
|
|
isPrimary Boolean @default(false)
|
|
createdAt DateTime @default(now())
|
|
|
|
@@id([userId, clientTeamId])
|
|
@@index([clientTeamId])
|
|
@@map("client_team_memberships")
|
|
}
|
|
|
|
// ─── Dow: Incoming Briefs (pre-project intake) ──────────
|
|
//
|
|
// A Brief is a request that has arrived but hasn't yet been triaged into
|
|
// a Project. Three intake paths all land here: manual create (producer
|
|
// types it in), REST API (`POST /api/briefs`), or webhook
|
|
// (`POST /api/webhooks/briefs`). Webhook payloads are idempotent on the
|
|
// `externalId` column so replays from upstream systems are safe.
|
|
//
|
|
// Once a brief is approved and matched to work, it gets "promoted" to a
|
|
// Project — the status flips to CONVERTED and `convertedProjectId` links
|
|
// back to the new Project. That keeps the audit trail without requiring
|
|
// the Brief row to disappear.
|
|
|
|
model Brief {
|
|
id String @id @default(cuid())
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
|
|
title String
|
|
description String?
|
|
|
|
// Source tracking — "manual" | "api" | "webhook:<system>" so we can
|
|
// report on where briefs come from without needing a separate enum.
|
|
source String @default("manual")
|
|
|
|
// External idempotency key (from webhook/API). Unique per org so the
|
|
// same upstream system can't create duplicates on replay.
|
|
externalId String?
|
|
|
|
// Requestor info — client-side contact. Freeform because intake
|
|
// systems can't be relied on to have user rows.
|
|
requestorName String?
|
|
requestorEmail String?
|
|
|
|
status BriefStatus @default(PENDING)
|
|
priority Priority @default(MEDIUM)
|
|
|
|
requestedDueDate DateTime?
|
|
|
|
clientTeamId String?
|
|
clientTeam ClientTeam? @relation(fields: [clientTeamId], references: [id])
|
|
|
|
// When promoted to a Project, fill in convertedProjectId (CONVERTED status).
|
|
convertedProjectId String?
|
|
convertedProject Project? @relation("BriefConvertedProject", fields: [convertedProjectId], references: [id])
|
|
|
|
// Pass-through field for anything in the incoming payload we didn't map.
|
|
rawPayload Json?
|
|
|
|
receivedAt DateTime @default(now())
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@unique([organizationId, externalId])
|
|
@@index([organizationId])
|
|
@@index([status])
|
|
@@index([clientTeamId])
|
|
@@map("briefs")
|
|
}
|
|
|
|
// ─── Dow: Production Pods (capacity grouping) ──────────
|
|
|
|
model Pod {
|
|
id String @id @default(cuid())
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
name String
|
|
slug String
|
|
leadUserId String?
|
|
leadUser User? @relation("PodLead", fields: [leadUserId], references: [id])
|
|
createdAt DateTime @default(now())
|
|
|
|
members User[] @relation("PodHome")
|
|
|
|
@@unique([organizationId, slug])
|
|
@@index([organizationId])
|
|
@@map("pods")
|
|
}
|
|
|
|
// ─── Dow: Resource bookings (daily capacity grid) ──────
|
|
// One row per (user, date, jobNumber) — matches the Resources.html
|
|
// prototype's model: a producer assigns N hours of a given job to a
|
|
// person on a specific day. Multiple rows per (user, date) are expected
|
|
// (split days across jobs). jobNumber is a freeform string so it can
|
|
// hold Project.omgJobNumber, Project.projectCode, or an ad-hoc scratch
|
|
// label — the capacity planner doesn't care what project the hours are
|
|
// against, just that the total for the day/week is tracked.
|
|
model ResourceBooking {
|
|
id String @id @default(cuid())
|
|
organizationId String
|
|
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
|
|
userId String
|
|
user User @relation("BookingResource", fields: [userId], references: [id], onDelete: Cascade)
|
|
date DateTime // date-only — we store 00:00:00 UTC
|
|
jobNumber String
|
|
hours Float
|
|
note String?
|
|
createdById String
|
|
createdBy User @relation("BookingCreator", fields: [createdById], references: [id])
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([organizationId, date])
|
|
@@index([userId, date])
|
|
@@map("resource_bookings")
|
|
}
|