Skip to main content

Work Order Management System — 1-2-3 Quick Start

Target audience: Senior engineer with TS/Python, Docker, PostgreSQL, cloud-native background. Platform: CODITECT — compliance-native, multi-agent, regulated industry SaaS.

Overview​

  • Change Control backbone — every action on validated systems or associated documents flows through a Work Order (WO), providing an immutable audit trail per FDA 21 CFR Part 11.
  • Hierarchical decomposition — Master WO → linked child WOs, each representing a logically independent task with its own lifecycle, assignees, and job plan.
  • Multi-source origination — WOs originate from automation (PM/calibration schedules), external vendors, or manual ad-hoc creation.
  • Resource graph — WOs reference a pre-entered resource graph: Assets, Tools, Experience ratings, Persons, and Schedule actuals — enabling cost projection and availability planning.
  • Compliance-native approval — every WO terminates with System Owner/QA review and electronic signature (21 CFR Part 11 compliant).

Step 1: Local Setup — Core WO Primitives​

1.1 Database Schema (PostgreSQL)​

-- Core Work Order tables
CREATE TYPE wo_source AS ENUM ('automation', 'external', 'manual');
CREATE TYPE wo_status AS ENUM ('draft', 'pending_approval', 'assigned', 'in_progress', 'blocked', 'completed', 'closed');
CREATE TYPE wo_priority AS ENUM ('critical', 'high', 'medium', 'low');

CREATE TABLE work_orders (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wo_number       TEXT UNIQUE NOT NULL,
    master_wo_id    UUID REFERENCES work_orders(id),  -- NULL = master WO
    source          wo_source NOT NULL,
    status          wo_status NOT NULL DEFAULT 'draft',
    priority        wo_priority NOT NULL DEFAULT 'medium',
    
    -- Section 1-3: Originator, Target, Summary
    originator_id   UUID NOT NULL REFERENCES persons(id),
    target_asset_id UUID REFERENCES assets(id),
    summary         TEXT NOT NULL,
    detail          JSONB NOT NULL DEFAULT '{}',
    
    -- Section 5-6: Assignment
    assigner_id     UUID REFERENCES persons(id),
    assignee_id     UUID REFERENCES persons(id),
    assignee_team   TEXT,
    
    -- Section 8-9: Schedule & Priority
    scheduled_start TIMESTAMPTZ,
    scheduled_end   TIMESTAMPTZ,
    actual_start    TIMESTAMPTZ,
    actual_end      TIMESTAMPTZ,
    estimated_hours NUMERIC(6,2),
    actual_hours    NUMERIC(6,2),
    
    -- Compliance
    regulatory      BOOLEAN NOT NULL DEFAULT true,
    compliance_refs JSONB DEFAULT '[]',  -- [{standard: "21CFR11", section: "11.10"}]
    
    -- Audit
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by      UUID NOT NULL REFERENCES persons(id),
    version         INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE wo_approvals (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wo_id           UUID NOT NULL REFERENCES work_orders(id),
    approver_id     UUID NOT NULL REFERENCES persons(id),
    role            TEXT NOT NULL,  -- 'system_owner', 'qa', 'additional'
    status          TEXT NOT NULL DEFAULT 'pending',  -- pending, approved, rejected
    signature_hash  TEXT,           -- e-signature (21 CFR Part 11)
    signed_at       TIMESTAMPTZ,
    comments        TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE wo_audit_trail (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wo_id           UUID NOT NULL REFERENCES work_orders(id),
    action          TEXT NOT NULL,
    field_changed   TEXT,
    old_value       JSONB,
    new_value       JSONB,
    actor_id        UUID NOT NULL REFERENCES persons(id),
    timestamp       TIMESTAMPTZ NOT NULL DEFAULT now(),
    reason          TEXT  -- required for regulatory changes
);

-- Resource graph tables (pre-entered)
CREATE TABLE assets (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    asset_number    TEXT UNIQUE NOT NULL,
    asset_type      TEXT NOT NULL,  -- 'device', 'computer', 'material_part'
    name            TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'active',
    metadata        JSONB DEFAULT '{}'
);

CREATE TABLE tools (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    tool_type       TEXT NOT NULL,  -- 'basic', 'specialized'
    availability    BOOLEAN DEFAULT true,
    calibration_due TIMESTAMPTZ
);

CREATE TABLE persons (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    email           TEXT UNIQUE NOT NULL,
    department      TEXT,
    active          BOOLEAN DEFAULT true
);

CREATE TABLE experience_ratings (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    person_id       UUID NOT NULL REFERENCES persons(id),
    skill_area      TEXT NOT NULL,
    rating          INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    certified       BOOLEAN DEFAULT false,
    certified_until TIMESTAMPTZ,
    UNIQUE(person_id, skill_area)
);

-- Job Plan (Section **1)
CREATE TABLE job_plans (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wo_id           UUID NOT NULL REFERENCES work_orders(id),
    description     TEXT NOT NULL,
    dependencies    JSONB DEFAULT '[]',  -- [{wo_id: "...", type: "completion"}]
    required_tools  JSONB DEFAULT '[]',
    required_experience JSONB DEFAULT '[]',  -- [{skill: "...", min_rating: 3}]
    required_persons INTEGER DEFAULT 1,
    credentials     JSONB DEFAULT '{}',  -- {account_type: "admin", ...}
    work_instructions TEXT,
    UNIQUE(wo_id)
);

-- Indexes
CREATE INDEX idx_wo_master ON work_orders(master_wo_id) WHERE master_wo_id IS NOT NULL;
CREATE INDEX idx_wo_status ON work_orders(status);
CREATE INDEX idx_wo_assignee ON work_orders(assignee_id);
CREATE INDEX idx_audit_wo ON wo_audit_trail(wo_id, timestamp);

1.2 Core Domain Types (TypeScript)​

// src/domain/work-order.types.ts

export type WOSource = 'automation' | 'external' | 'manual';
export type WOStatus = 'draft' | 'pending_approval' | 'assigned' | 'in_progress' | 'blocked' | 'completed' | 'closed';
export type WOPriority = 'critical' | 'high' | 'medium' | 'low';

export interface WorkOrder {
  id: string;
  woNumber: string;
  masterWoId: string | null;
  source: WOSource;
  status: WOStatus;
  priority: WOPriority;
  originatorId: string;
  targetAssetId: string | null;
  summary: string;
  detail: Record<string, unknown>;
  assignerId: string | null;
  assigneeId: string | null;
  assigneeTeam: string | null;
  scheduledStart: Date | null;
  scheduledEnd: Date | null;
  actualStart: Date | null;
  actualEnd: Date | null;
  estimatedHours: number | null;
  actualHours: number | null;
  regulatory: boolean;
  complianceRefs: ComplianceRef[];
  createdAt: Date;
  updatedAt: Date;
  version: number;
}

export interface ComplianceRef {
  standard: string;   // "21CFR11", "HIPAA", "SOC2"
  section: string;
  requirement: string;
}

export interface JobPlan {
  id: string;
  woId: string;
  description: string;
  dependencies: WODependency[];
  requiredTools: ToolRequirement[];
  requiredExperience: ExperienceRequirement[];
  requiredPersons: number;
  credentials: Record<string, string>;
  workInstructions: string | null;
}

export interface WODependency {
  woId: string;
  type: 'completion' | 'approval' | 'checkpoint';
}

export interface ExperienceRequirement {
  skillArea: string;
  minRating: number;
}

export interface WOApproval {
  id: string;
  woId: string;
  approverId: string;
  role: 'system_owner' | 'qa' | 'additional';
  status: 'pending' | 'approved' | 'rejected';
  signatureHash: string | null;
  signedAt: Date | null;
  comments: string | null;
}

export interface AuditEntry {
  id: string;
  woId: string;
  action: string;
  fieldChanged: string | null;
  oldValue: unknown;
  newValue: unknown;
  actorId: string;
  timestamp: Date;
  reason: string | null;
}

1.3 Verify Setup​

# Expected: tables created, types registered
psql -d coditect -c "\dt wo_*"
psql -d coditect -c "\dt work_orders"
psql -d coditect -c "SELECT enum_range(NULL::wo_status);"
# → {draft,pending_approval,assigned,in_progress,blocked,completed,closed}

Step 2: Realistic Workflow — Master WO with Linked Children​

2.1 Create Master WO (Windows 10 → 11 Upgrade Scenario)​

// src/services/work-order.service.ts

import { Pool } from 'pg';
import { v4 as uuid } from 'uuid';
import { WorkOrder, WOSource, WOPriority, JobPlan } from '../domain/work-order.types';

export class WorkOrderService {
  constructor(private pool: Pool) {}

  async createMasterWO(params: {
    source: WOSource;
    originatorId: string;
    summary: string;
    detail: Record<string, unknown>;
    priority: WOPriority;
    regulatory: boolean;
    targetAssetId?: string;
  }): Promise<WorkOrder> {
    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');
      
      const woNumber = await this.generateWONumber(client);
      
      const result = await client.query(
        `INSERT INTO work_orders 
         (wo_number, source, originator_id, summary, detail, priority, regulatory, target_asset_id, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $3)
         RETURNING *`,
        [woNumber, params.source, params.originatorId, params.summary, 
         JSON.stringify(params.detail), params.priority, params.regulatory, params.targetAssetId]
      );
      
      // Immutable audit entry
      await this.auditLog(client, result.rows[0].id, 'CREATED', null, null, 
        { status: 'draft', source: params.source }, params.originatorId, 'Master WO created');
      
      await client.query('COMMIT');
      return this.mapRow(result.rows[0]);
    } catch (e) {
      await client.query('ROLLBACK');
      throw e;
    } finally {
      client.release();
    }
  }

  async createLinkedWO(masterWoId: string, params: {
    source: WOSource;
    originatorId: string;
    summary: string;
    detail: Record<string, unknown>;
    priority: WOPriority;
    regulatory: boolean;
    dependencies?: string[];
  }): Promise<{ wo: WorkOrder; jobPlan: JobPlan }> {
    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');
      
      // Verify master exists
      const master = await client.query('SELECT id FROM work_orders WHERE id = $1 AND master_wo_id IS NULL', [masterWoId]);
      if (master.rowCount === 0) throw new Error(`Master WO ${masterWoId} not found`);
      
      const woNumber = await this.generateWONumber(client);
      
      const woResult = await client.query(
        `INSERT INTO work_orders 
         (wo_number, master_wo_id, source, originator_id, summary, detail, priority, regulatory, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $4)
         RETURNING *`,
        [woNumber, masterWoId, params.source, params.originatorId, params.summary,
         JSON.stringify(params.detail), params.priority, params.regulatory]
      );
      
      const wo = woResult.rows[0];
      
      // Create job plan with dependencies
      const deps = (params.dependencies || []).map(d => ({ woId: d, type: 'completion' }));
      const jpResult = await client.query(
        `INSERT INTO job_plans (wo_id, description, dependencies) VALUES ($1, $2, $3) RETURNING *`,
        [wo.id, params.summary, JSON.stringify(deps)]
      );
      
      await this.auditLog(client, wo.id, 'CREATED', null, null,
        { status: 'draft', masterWoId }, params.originatorId, 'Linked WO created');
      
      await client.query('COMMIT');
      return { wo: this.mapRow(wo), jobPlan: jpResult.rows[0] };
    } catch (e) {
      await client.query('ROLLBACK');
      throw e;
    } finally {
      client.release();
    }
  }

  async transitionStatus(woId: string, newStatus: string, actorId: string, reason: string): Promise<WorkOrder> {
    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');
      
      const current = await client.query('SELECT * FROM work_orders WHERE id = $1 FOR UPDATE', [woId]);
      if (current.rowCount === 0) throw new Error(`WO ${woId} not found`);
      
      const oldStatus = current.rows[0].status;
      this.validateTransition(oldStatus, newStatus);
      
      // Check dependencies for 'in_progress' transition
      if (newStatus === 'in_progress') {
        await this.checkDependencies(client, woId);
      }
      
      // Require approval for 'completed' on regulatory WOs
      if (newStatus === 'completed' && current.rows[0].regulatory) {
        await this.requireApproval(client, woId);
      }
      
      const result = await client.query(
        `UPDATE work_orders SET status = $1, updated_at = now(), version = version + 1 WHERE id = $2 RETURNING *`,
        [newStatus, woId]
      );
      
      await this.auditLog(client, woId, 'STATUS_CHANGE', 'status', 
        oldStatus, newStatus, actorId, reason);
      
      await client.query('COMMIT');
      return this.mapRow(result.rows[0]);
    } catch (e) {
      await client.query('ROLLBACK');
      throw e;
    } finally {
      client.release();
    }
  }

  private async auditLog(client: any, woId: string, action: string, 
    field: string | null, oldVal: unknown, newVal: unknown, actorId: string, reason: string) {
    await client.query(
      `INSERT INTO wo_audit_trail (wo_id, action, field_changed, old_value, new_value, actor_id, reason)
       VALUES ($1, $2, $3, $4, $5, $6, $7)`,
      [woId, action, field, JSON.stringify(oldVal), JSON.stringify(newVal), actorId, reason]
    );
  }

  private validateTransition(from: string, to: string): void {
    const allowed: Record<string, string[]> = {
      'draft': ['pending_approval'],
      'pending_approval': ['assigned', 'draft'],
      'assigned': ['in_progress', 'blocked'],
      'in_progress': ['completed', 'blocked'],
      'blocked': ['in_progress', 'assigned'],
      'completed': ['closed'],
    };
    if (!allowed[from]?.includes(to)) {
      throw new Error(`Invalid transition: ${from} → ${to}`);
    }
  }

  // ... additional private methods
}

2.2 Full Scenario — Lab Instrument Upgrade (Appendix A)​

// Example: Windows 10 → Windows 11 upgrade workflow
const svc = new WorkOrderService(pool);

// Master WO
const master = await svc.createMasterWO({
  source: 'manual',
  originatorId: systemOwnerId,
  summary: 'Upgrade Lab Instrument Computer: Win10 → Win11',
  detail: { instrument: 'Agilent 1260 HPLC', location: 'Lab 3B' },
  priority: 'high',
  regulatory: true,
  targetAssetId: hplcAssetId,
});

// Linked WO 1: Remove from production
const wo1 = await svc.createLinkedWO(master.id, {
  source: 'manual', originatorId: systemOwnerId,
  summary: 'Ticketing: Remove HPLC system from Production status',
  detail: { ticketSystem: 'ServiceNow', assets: ['HPLC-1260-03', 'WS-LAB3B-01'] },
  priority: 'high', regulatory: true,
});

// Linked WO 2: IT creates Win11 workstation (depends on WO1)
const wo2 = await svc.createLinkedWO(master.id, {
  source: 'manual', originatorId: itLeadId,
  summary: 'IT: Build Win11 workstation from baseline image, replace Win10',
  detail: { baselineImage: 'WIN11-LAB-v2.3', physicalReplace: true },
  priority: 'high', regulatory: true,
  dependencies: [wo1.wo.id],
});

// Linked WO 3: Vendor installs application (depends on WO2)
const wo3 = await svc.createLinkedWO(master.id, {
  source: 'external', originatorId: vendorContactId,
  summary: 'Vendor: Install Agilent OpenLAB CDS on Win11 (IQ/OQ)',
  detail: { vendor: 'Agilent', deliverables: ['IQ_protocol', 'OQ_protocol', 'IQ_OQ_report'] },
  priority: 'high', regulatory: true,
  dependencies: [wo2.wo.id],
});

// Linked WO 4: Configuration + validation (depends on WO3)
const wo4 = await svc.createLinkedWO(master.id, {
  source: 'manual', originatorId: systemOwnerId,
  summary: 'Configure Win11 for lab requirements, capture Golden Image, update WIs',
  detail: { 
    tasks: ['lab_config', 'data_paths', 'cleanup_test_accounts', 'golden_image', 'update_work_instructions'],
    dataPaths: { local: 'D:\\Data', backup: '\\\\fileserver\\lab3b\\backup' }
  },
  priority: 'high', regulatory: true,
  dependencies: [wo3.wo.id],
});

// Linked WO 5: User account setup (depends on WO4)
const wo5 = await svc.createLinkedWO(master.id, {
  source: 'manual', originatorId: itLeadId,
  summary: 'User account setup on Win11 and instruments per approved requests',
  detail: { accountTypes: ['domain_user', 'instrument_user'] },
  priority: 'medium', regulatory: true,
  dependencies: [wo4.wo.id],
});

// Linked WO 6: Deprecate Win10 (depends on WO5)
const wo6 = await svc.createLinkedWO(master.id, {
  source: 'manual', originatorId: itLeadId,
  summary: 'Deprecate Win10: wipe, return to stock or dispose to e-recycling',
  detail: { disposal: 'validation_lifecycle_required', assetAction: 'return_to_stock_or_dispose' },
  priority: 'low', regulatory: true,
  dependencies: [wo5.wo.id],
});

Step 3: Deploy — CODITECT Agent Integration​

3.1 Agent-Driven WO Orchestration​

// src/agents/wo-orchestrator.agent.ts
// CODITECT agent that manages WO lifecycle via orchestrator-workers pattern

import { TaskSegment, ModelAssignment } from '../orchestrator/model-router';

export interface WOAgentTask {
  masterWoId: string;
  linkedWOs: string[];
  currentPhase: 'planning' | 'execution' | 'validation' | 'closure';
  checkpoints: WOCheckpoint[];
}

export interface WOCheckpoint {
  woId: string;
  type: 'dependency_check' | 'approval_gate' | 'compliance_validation' | 'quality_gate';
  status: 'pending' | 'passed' | 'failed' | 'escalated';
  requiredSignatures: string[];
  collectedSignatures: string[];
}

// Model routing for WO tasks
const WO_ROUTING: Record<string, { model: string; rationale: string }> = {
  'compliance_validation': { model: 'opus', rationale: '21 CFR Part 11 compliance requires highest-capability model' },
  'dependency_resolution': { model: 'sonnet', rationale: 'Complex graph traversal, non-regulatory' },
  'status_notification': { model: 'haiku', rationale: 'Template-based notification, low complexity' },
  'approval_routing': { model: 'sonnet', rationale: 'Role-based routing logic, moderate complexity' },
  'schedule_optimization': { model: 'sonnet', rationale: 'Resource constraint solving' },
  'audit_trail_generation': { model: 'opus', rationale: 'Regulatory audit must be accurate and complete' },
};

3.2 Docker Compose (Dev Environment)​

# docker-compose.wo-dev.yml
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: coditect
      POSTGRES_USER: coditect
      POSTGRES_PASSWORD: dev_only
    ports: ["5432:5432"]
    volumes:
      - ./sql/init-wo-schema.sql:/docker-entrypoint-initdb.d/01-schema.sql
      - pgdata:/var/lib/postgresql/data

  wo-service:
    build: .
    environment:
      DATABASE_URL: postgresql://coditect:dev_only@postgres:5432/coditect
      NODE_ENV: development
    ports: ["3000:3000"]
    depends_on: [postgres]

volumes:
  pgdata:
docker compose -f docker-compose.wo-dev.yml up -d
# Verify: curl http://localhost:3000/health
# Expected: {"status":"ok","db":"connected","migrations":"current"}

Version-Specific Notes​

  • PostgreSQL 16+ required for gen_random_uuid() without extension.
  • JSONB columns enable flexible schema evolution without migrations for non-critical metadata.
  • FOR UPDATE row locking on status transitions prevents race conditions in concurrent WO processing.
  • Audit trail uses append-only pattern — no UPDATE/DELETE on wo_audit_trail table. Enforce via RLS or trigger.