Work Order System — Prisma Data Model Reference

Classification: Internal — Engineering Reference Date: 2026-02-13 Status: Proposed CODITECT Alignment: PostgreSQL state store, RLS multi-tenancy, append-only audit

1. Schema Overview

The WO data model implements 22 entities across four domains: core work order lifecycle, job planning & resource requirements, registries (assets, tools, experience, materials), and compliance (approvals, audit trail, e-signatures). Every entity carries tenant_id for row-level security isolation and version for optimistic concurrency — both mandatory for CODITECT's multi-tenant regulated architecture.

Entity Count by Domain

Domain	Entities	Tables	Join Tables
Core Lifecycle	WorkOrder, ChangeItem, Party	3	0
Job Planning	JobPlan, ToolReq, ExperienceReq, PersonReq, MaterialReq, MinReq	6	0
Registries	Person, Team, Vendor, Asset, Tool, Experience, Material, PersonExperience	8	1
Compliance	Approval, AuditTrail, ElectronicSignature, Schedule, TimeEntry	5	0
Total		22	1

2. CODITECT Platform Extensions

The source Prisma schema is extended with platform-level concerns that don't exist in a standalone QMS but are mandatory in CODITECT's multi-tenant, agent-orchestrated environment.

2.1 Multi-Tenancy (Row-Level Security)

Every table includes:

model WorkOrder {
  // ... domain fields ...
  tenantId    String   @map("tenant_id")
  
  @@index([tenantId, status])
  @@index([tenantId, masterId])
}

PostgreSQL RLS policy applied at connection level:

CREATE POLICY wo_tenant_isolation ON work_orders
  USING (tenant_id = current_setting('app.tenant_id')::text);

ALTER TABLE work_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE work_orders FORCE ROW LEVEL SECURITY;

This pattern repeats for all 22 tables. CODITECT's API Gateway sets app.tenant_id on each connection from the pool, ensuring complete data isolation without application-level filtering.

2.2 Optimistic Concurrency

All mutable entities carry a version field:

model WorkOrder {
  version  Int  @default(1)
  // Update logic:
  // UPDATE work_orders SET ..., version = version + 1
  //   WHERE id = $1 AND version = $2
  //   RETURNING *
  // If 0 rows affected → 409 Conflict
}

2.3 Agent Attribution

Every state mutation records the acting agent or human:

model AuditTrail {
  performedBy    String    // Person ID or Agent ID
  performerType  String    // "HUMAN" | "AGENT" | "SYSTEM"
  agentSessionId String?   // CODITECT agent execution trace ID
  correlationId  String?   // Distributed tracing correlation
}

This bridges the gap between the source spec (human-only performedBy) and CODITECT's autonomous agent model where an ExperienceMatchingNode or WorkOrderOrchestratorNode may initiate transitions.

3. Complete Prisma Schema (Annotated)

3.1 Enums

enum WorkOrderSourceType {
  AUTOMATION        // Agent-initiated (PM schedules, drift detection)
  EXTERNAL_VENDOR   // Vendor-originated (IQ/OQ, calibration)
  MANUAL            // Human-initiated (upgrade requests, corrections)
}

enum WorkOrderStatus {
  DRAFT             // Initial creation, metadata incomplete
  PLANNED           // Originator/item/summary set, ready for scheduling
  SCHEDULED         // JobPlan + Schedule + assignee locked
  IN_PROGRESS       // Assignee acknowledged, execution underway
  PENDING_REVIEW    // Execution complete, awaiting approval
  APPROVED          // System Owner (+ QA for reg) approved
  REJECTED          // Reviewer rejected, requires rework
  COMPLETED         // Post-approval tasks confirmed, terminal
  CANCELLED         // Cancelled with justification, terminal
}

enum PartyType {
  PERSON            // Individual human
  VENDOR            // External company
  PROGRAM           // Automated system / agent
  TEAM              // Group of persons
}

enum ApprovalRole {
  SYSTEM_OWNER      // Required for all WOs
  QA                // Required for regulatory WOs
  OTHER             // Additional approvers (department head, etc.)
}

enum PerformerType {
  HUMAN             // Authenticated user action
  AGENT             // CODITECT agent action
  SYSTEM            // Platform system action (timers, escalations)
}

3.2 Core Lifecycle Entities

model WorkOrder {
  id              String              @id @default(cuid())
  tenantId        String              @map("tenant_id")
  
  // Hierarchy
  masterId        String?             @db.VarChar(191)
  master          WorkOrder?          @relation("MasterChildWO", fields: [masterId], references: [id])
  children        WorkOrder[]         @relation("MasterChildWO")
  sequenceOrder   Int?                // Execution order within master (DAG position)
  
  // Source & Origination
  sourceType      WorkOrderSourceType
  originatorId    String
  originator      Party               @relation("OriginatedWOs", fields: [originatorId], references: [id])
  
  // Change Target
  itemId          String
  item            ChangeItem          @relation(fields: [itemId], references: [id])
  
  // Description
  summary         String
  detail          String
  
  // Assignment
  assignerId      String
  assigner        Party               @relation("AssignedBy", fields: [assignerId], references: [id])
  assigneeId      String
  assignee        Party               @relation("AssignedTo", fields: [assigneeId], references: [id])
  
  // Planning
  jobPlanId       String?
  jobPlan         JobPlan?            @relation(fields: [jobPlanId], references: [id])
  scheduleId      String?
  schedule        Schedule?           @relation(fields: [scheduleId], references: [id])
  
  // Classification
  priority        Int                 @default(3) // 1=Critical, 5=Low
  status          WorkOrderStatus     @default(DRAFT)
  regulatoryFlag  Boolean             @default(false)
  
  // Compliance Metadata
  complianceClass String?             // "FDA_21CFR11" | "HIPAA" | "SOC2" | null
  riskLevel       String?             // "HIGH" | "MEDIUM" | "LOW"
  
  // Relations
  approvals       Approval[]
  timeEntries     TimeEntry[]
  minRequirements WorkOrderMinimumRequirement[]
  dependencies    WorkOrderDependency[] @relation("DependentWO")
  dependents      WorkOrderDependency[] @relation("DependencyTarget")
  
  // Versioning
  createdAt       DateTime            @default(now())
  updatedAt       DateTime            @updatedAt
  version         Int                 @default(1)
  
  auditEvents     AuditTrail[]
  
  @@index([tenantId, status])
  @@index([tenantId, masterId])
  @@index([tenantId, assigneeId, status])
  @@index([tenantId, regulatoryFlag, status])
}

model WorkOrderDependency {
  id              String    @id @default(cuid())
  tenantId        String    @map("tenant_id")
  
  workOrderId     String
  workOrder       WorkOrder @relation("DependentWO", fields: [workOrderId], references: [id])
  dependsOnId     String
  dependsOn       WorkOrder @relation("DependencyTarget", fields: [dependsOnId], references: [id])
  
  dependencyType  String    // "BLOCKS" | "SOFT" | "INFORMATIONAL"
  
  @@unique([workOrderId, dependsOnId])
  @@index([tenantId])
}

model ChangeItem {
  id          String    @id @default(cuid())
  tenantId    String    @map("tenant_id")
  type        String    // DEVICE | COMPUTER | SYSTEM | DOCUMENT
  assetId     String?
  asset       Asset?    @relation(fields: [assetId], references: [id])
  documentId  String?   // FK to external DMS
  description String
  
  workOrders  WorkOrder[]
  
  @@index([tenantId, type])
}

model Party {
  id          String    @id @default(cuid())
  tenantId    String    @map("tenant_id")
  type        PartyType
  personId    String?
  person      Person?   @relation(fields: [personId], references: [id])
  vendorId    String?
  vendor      Vendor?   @relation(fields: [vendorId], references: [id])
  teamId      String?
  team        Team?     @relation(fields: [teamId], references: [id])
  displayName String
  
  originatedWOs WorkOrder[] @relation("OriginatedWOs")
  assignedBy    WorkOrder[] @relation("AssignedBy")
  assignedTo    WorkOrder[] @relation("AssignedTo")
  
  @@index([tenantId, type])
}

3.3 Job Planning & Requirements

model JobPlan {
  id                String   @id @default(cuid())
  tenantId          String   @map("tenant_id")
  name              String   // Template name for reusable plans
  description       String
  workInstructionRef String? // Reference to DMS document ID
  estimatedHours    Float?
  isTemplate        Boolean  @default(false) // Reusable template vs. instance
  templateId        String?  // FK to template this was cloned from
  
  tools             JobPlanToolRequirement[]
  experiences       JobPlanExperienceRequirement[]
  persons           JobPlanPersonRequirement[]
  materials         JobPlanMaterialRequirement[]
  workOrders        WorkOrder[]
  
  createdAt         DateTime @default(now())
  version           Int      @default(1)
  
  @@index([tenantId, isTemplate])
}

model JobPlanToolRequirement {
  id          String   @id @default(cuid())
  jobPlanId   String
  jobPlan     JobPlan  @relation(fields: [jobPlanId], references: [id])
  toolId      String
  tool        Tool     @relation(fields: [toolId], references: [id])
  minQuantity Int      @default(1)
  calibrationRequired Boolean @default(false) // GxP: tool must be in calibration
}

model JobPlanExperienceRequirement {
  id             String      @id @default(cuid())
  jobPlanId      String
  jobPlan        JobPlan     @relation(fields: [jobPlanId], references: [id])
  experienceId   String
  experience     Experience  @relation(fields: [experienceId], references: [id])
  minRating      Int         // 1-5 scale
  mandatory      Boolean     @default(true)
}

model JobPlanPersonRequirement {
  id          String   @id @default(cuid())
  jobPlanId   String
  jobPlan     JobPlan  @relation(fields: [jobPlanId], references: [id])
  roleLabel   String   // "IT Technician", "Validation Engineer", etc.
  headcount   Int?
  personId    String?
  person      Person?  @relation(fields: [personId], references: [id])
}

model JobPlanMaterialRequirement {
  id          String   @id @default(cuid())
  jobPlanId   String
  jobPlan     JobPlan  @relation(fields: [jobPlanId], references: [id])
  materialId  String
  material    Material @relation(fields: [materialId], references: [id])
  quantity    Int      @default(1)
  lotTracked  Boolean  @default(false) // GxP: material lot must be recorded
}

model WorkOrderMinimumRequirement {
  id              String    @id @default(cuid())
  workOrderId     String
  workOrder       WorkOrder @relation(fields: [workOrderId], references: [id])
  level           Int       // 1=mandatory, 2=recommended, 3=optional
  requirementType String    // PERSON | EXPERIENCE | TOOL | MATERIAL | INFO_SET
  refPersonId     String?
  refExperienceId String?
  refToolId       String?
  refMaterialId   String?
  infoDescription String?
  satisfied       Boolean   @default(false)
  satisfiedAt     DateTime?
}

3.4 Registry Entities

model Person {
  id           String             @id @default(cuid())
  tenantId     String             @map("tenant_id")
  name         String
  email        String
  active       Boolean            @default(true)
  department   String?
  
  experiences  PersonExperience[]
  timeEntries  TimeEntry[]
  approvals    Approval[]
  signatures   ElectronicSignature[]
  auditEvents  AuditTrail[]       @relation("PerformedByPerson")
  parties      Party[]
  personReqs   JobPlanPersonRequirement[]
  
  @@unique([tenantId, email])
  @@index([tenantId, active])
}

model Team {
  id        String   @id @default(cuid())
  tenantId  String   @map("tenant_id")
  name      String
  parties   Party[]
  members   TeamMember[]
}

model TeamMember {
  id        String  @id @default(cuid())
  teamId    String
  team      Team    @relation(fields: [teamId], references: [id])
  personId  String
  person    Person  @relation(fields: [personId], references: [id])
  role      String? // "LEAD" | "MEMBER"
  
  @@unique([teamId, personId])
}

model Vendor {
  id            String   @id @default(cuid())
  tenantId      String   @map("tenant_id")
  name          String
  contactInfo   String?
  qualifiedDate DateTime? // Date vendor was qualified (GxP requirement)
  active        Boolean   @default(true)
  parties       Party[]
  timeEntries   TimeEntry[]
}

model Asset {
  id             String    @id @default(cuid())
  tenantId       String    @map("tenant_id")
  type           String    // DEVICE | COMPUTER | INSTRUMENT | PART
  name           String
  serialNumber   String?
  status         String    // IN_PRODUCTION | OUT_OF_PRODUCTION | DECOMMISSIONED
  location       String?
  assetMgmtRef   String?   // External CMMS/ITSM reference
  validationStatus String? // VALIDATED | PENDING_VALIDATION | NOT_APPLICABLE
  lastValidatedAt DateTime?
  
  changeItems    ChangeItem[]
  
  @@index([tenantId, status])
  @@index([tenantId, type])
}

model Tool {
  id              String   @id @default(cuid())
  tenantId        String   @map("tenant_id")
  name            String
  type            String   // BASIC | SPECIALIZED
  calibrationDue  DateTime? // Next calibration date
  status          String   @default("AVAILABLE") // AVAILABLE | IN_USE | MAINTENANCE
  
  jobRequirements JobPlanToolRequirement[]
  
  @@index([tenantId, status])
}

model Experience {
  id          String             @id @default(cuid())
  tenantId    String             @map("tenant_id")
  name        String             // "Win11 lab workstation build"
  description String?
  category    String?            // "IT" | "VALIDATION" | "LAB_OPS" | "VENDOR_MGMT"
  
  persons         PersonExperience[]
  jobRequirements JobPlanExperienceRequirement[]
  
  @@index([tenantId, category])
}

model PersonExperience {
  personId     String
  experienceId String
  rating       Int       // 1-5 scale
  certifiedAt  DateTime? // Date of last certification/training
  expiresAt    DateTime? // Training expiration
  
  person       Person     @relation(fields: [personId], references: [id])
  experience   Experience @relation(fields: [experienceId], references: [id])
  
  @@id([personId, experienceId])
}

model Material {
  id          String   @id @default(cuid())
  tenantId    String   @map("tenant_id")
  name        String
  description String?
  partNumber  String?
  lotTracking Boolean  @default(false)
  
  requirements JobPlanMaterialRequirement[]
}

3.5 Scheduling & Time Tracking

model Schedule {
  id                 String      @id @default(cuid())
  tenantId           String      @map("tenant_id")
  expectedStartAt    DateTime?
  expectedEndAt      DateTime?
  expectedTotalHours Float?
  actualStartAt      DateTime?
  actualEndAt        DateTime?
  gapsNotes          String?
  slaDueAt           DateTime?   // SLA deadline for escalation
  
  workOrders         WorkOrder[]
}

model TimeEntry {
  id            String   @id @default(cuid())
  tenantId      String   @map("tenant_id")
  workOrderId   String
  workOrder     WorkOrder @relation(fields: [workOrderId], references: [id])
  personId      String
  person        Person    @relation(fields: [personId], references: [id])
  vendorId      String?
  vendor        Vendor?   @relation(fields: [vendorId], references: [id])
  actualStartAt DateTime
  actualEndAt   DateTime?
  billableHours Float?
  costCenter    String?
  rateType      String?  // "STANDARD" | "OVERTIME" | "VENDOR"
  
  @@index([tenantId, workOrderId])
  @@index([tenantId, personId])
}

3.6 Compliance: Approvals, Signatures, Audit

model Approval {
  id          String        @id @default(cuid())
  tenantId    String        @map("tenant_id")
  workOrderId String
  workOrder   WorkOrder     @relation(fields: [workOrderId], references: [id])
  role        ApprovalRole
  approverId  String
  approver    Person        @relation(fields: [approverId], references: [id])
  decision    String        // APPROVED | REJECTED
  decisionTs  DateTime
  comment     String?
  signatureId String?
  signature   ElectronicSignature? @relation(fields: [signatureId], references: [id])
  
  @@index([tenantId, workOrderId])
}

model ElectronicSignature {
  id          String   @id @default(cuid())
  tenantId    String   @map("tenant_id")
  signerId    String
  signer      Person   @relation(fields: [signerId], references: [id])
  meaning     String   // "Approval of Work Order {id}", "Review of record"
  signedAt    DateTime @default(now())
  reason      String?
  authMethod  String   // "password" | "smartcard" | "sso_reauth"
  sessionId   String?
  ipAddress   String?
  
  // Part 11 §11.50: Signature manifestations
  //   - signerId links to unique individual
  //   - meaning captures what was signed and why
  //   - signedAt provides timestamp
  //   - authMethod documents authentication context
  
  approvals   Approval[]
  
  @@index([tenantId, signerId])
}

model AuditTrail {
  id             String       @id @default(cuid())
  tenantId       String       @map("tenant_id")
  entityType     String       // WORK_ORDER | JOB_PLAN | SCHEDULE | APPROVAL | ASSET
  entityId       String
  action         String       // CREATE | UPDATE | STATUS_CHANGE | APPROVAL | SIGNATURE | LOGIN
  performedBy    String
  performer      Person       @relation("PerformedByPerson", fields: [performedBy], references: [id])
  performerType  PerformerType @default(HUMAN)
  agentSessionId String?      // CODITECT agent trace ID
  correlationId  String?      // Distributed tracing correlation
  performedAt    DateTime     @default(now())
  previousVal    Json?
  newVal         Json?
  
  // Part 11 §11.10(e): Audit trail must be computer-generated,
  // independent of operator, and not modifiable.
  // Enforced via DB trigger: no UPDATE or DELETE allowed.
  
  @@index([tenantId, entityType, entityId])
  @@index([tenantId, performedAt])
  @@index([tenantId, performedBy])
}

4. Database Constraints & Triggers

4.1 Immutable Audit Trail (Part 11 §11.10(e))

-- Prevent modification of audit records
CREATE OR REPLACE FUNCTION prevent_audit_modification()
RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'Audit trail records cannot be modified or deleted (21 CFR Part 11 §11.10(e))';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trail_immutable
  BEFORE UPDATE OR DELETE ON audit_trail
  FOR EACH ROW EXECUTE FUNCTION prevent_audit_modification();

4.2 Auto-Versioning on WorkOrder Update

CREATE OR REPLACE FUNCTION auto_version_work_order()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.version != NEW.version - 1 THEN
    RAISE EXCEPTION 'Optimistic lock violation: expected version %, got %',
      OLD.version + 1, NEW.version;
  END IF;
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER wo_auto_version
  BEFORE UPDATE ON work_orders
  FOR EACH ROW EXECUTE FUNCTION auto_version_work_order();

4.3 DAG Cycle Detection on Dependency Insert

CREATE OR REPLACE FUNCTION check_dependency_cycle()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    WITH RECURSIVE dep_chain AS (
      SELECT depends_on_id FROM work_order_dependencies
      WHERE work_order_id = NEW.depends_on_id
      UNION ALL
      SELECT d.depends_on_id FROM work_order_dependencies d
      JOIN dep_chain dc ON dc.depends_on_id = d.work_order_id
    )
    SELECT 1 FROM dep_chain WHERE depends_on_id = NEW.work_order_id
  ) THEN
    RAISE EXCEPTION 'Dependency cycle detected: WO % → WO % would create a cycle',
      NEW.work_order_id, NEW.depends_on_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER dependency_cycle_check
  BEFORE INSERT ON work_order_dependencies
  FOR EACH ROW EXECUTE FUNCTION check_dependency_cycle();

5. Index Strategy

Table	Index	Purpose
work_orders	`(tenant_id, status)`	Dashboard queries, queue views
work_orders	`(tenant_id, master_id)`	Hierarchy traversal
work_orders	`(tenant_id, assignee_id, status)`	My work queue
work_orders	`(tenant_id, regulatory_flag, status)`	Compliance dashboard
audit_trail	`(tenant_id, entity_type, entity_id)`	Entity audit history
audit_trail	`(tenant_id, performed_at)`	Time-range audit queries
approvals	`(tenant_id, work_order_id)`	Approval status lookup
person_experience	`(person_id, experience_id)`	Resource matching
time_entries	`(tenant_id, work_order_id)`	WO time accumulation

Partitioning Strategy (>10M WOs/tenant/year)

-- Partition audit_trail by month for high-volume tenants
CREATE TABLE audit_trail (
  -- ... columns ...
) PARTITION BY RANGE (performed_at);

CREATE TABLE audit_trail_2026_01 PARTITION OF audit_trail
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- Auto-create partitions via pg_partman or CODITECT scheduler

6. CODITECT Agent ↔ Prisma Integration Points

Agent Node	Primary Tables	Operations	RBAC
WorkOrderOrchestrator	WorkOrder, WorkOrderDependency	CRUD, transitions	SYSTEM
AssetManagement	Asset, ChangeItem	Status updates	AGENT
Scheduling	Schedule, TimeEntry	Create/update	AGENT
ExperienceMatching	PersonExperience, Person, Experience	Read-only queries	AGENT
QAReview	Approval, ElectronicSignature, AuditTrail	Create approvals	QA (delegated)
VendorCoordinator	Vendor, TimeEntry, WorkOrder	Update vendor WOs	VENDOR (delegated)
Documentation	ChangeItem (documentId)	Link documents	AGENT

Critical Rule: Agent nodes that create approvals or signatures MUST operate "on behalf of" an authenticated human. The performerType=AGENT + agentSessionId in AuditTrail captures this delegation, but the Approval.approverId MUST reference a real Person who authorized the action.

7. Migration File Inventory

Order	File	Purpose
001	`create_enums.sql`	All enum types
002	`create_persons_teams_vendors.sql`	Registry: people
003	`create_assets_tools_materials.sql`	Registry: physical
004	`create_experience.sql`	Registry: skills
005	`create_parties.sql`	Polymorphic party abstraction
006	`create_schedules_time_entries.sql`	Scheduling
007	`create_job_plans.sql`	Job planning with requirements
008	`create_change_items.sql`	Change targets
009	`create_work_orders.sql`	Core WO table
010	`create_wo_dependencies.sql`	DAG dependencies
011	`create_wo_min_requirements.sql`	Multi-level requirements
012	`create_signatures.sql`	Electronic signatures
013	`create_approvals.sql`	Approval records
014	`create_audit_trail.sql`	Immutable audit
015	`create_rls_policies.sql`	Row-level security for all tables
016	`create_triggers.sql`	Immutability, versioning, cycle detection
017	`create_indexes.sql`	Performance indexes
018	`seed_experiences.sql`	Default experience categories

1. Schema Overview​

Entity Count by Domain​

2. CODITECT Platform Extensions​

2.1 Multi-Tenancy (Row-Level Security)​

2.2 Optimistic Concurrency​

2.3 Agent Attribution​

3. Complete Prisma Schema (Annotated)​

3.1 Enums​

3.2 Core Lifecycle Entities​

3.3 Job Planning & Requirements​

3.4 Registry Entities​

3.5 Scheduling & Time Tracking​

3.6 Compliance: Approvals, Signatures, Audit​

4. Database Constraints & Triggers​

4.1 Immutable Audit Trail (Part 11 §11.10(e))​

4.2 Auto-Versioning on WorkOrder Update​

4.3 DAG Cycle Detection on Dependency Insert​

5. Index Strategy​

Partitioning Strategy (>10M WOs/tenant/year)​

6. CODITECT Agent ↔ Prisma Integration Points​

7. Migration File Inventory​