TELS Work Orders Service - Entity-Relationship Diagrams

Overview

The Work Orders Service uses SQL Server with the TELS database, spanning two schemas: dbo (core work order tables inherited from the legacy system) and WorkOrders (newer domain-specific tables). The database contains 20+ active tables, extensive history tables with trigger-based audit trails, and 82 stored procedures. Migrations are managed by DbUp with 91 schema scripts spanning 2019-2025.

Work Order Core (SQL Server — TELS Database, dbo Schema)

erDiagram
    %% ==========================================
    %% Core Work Order
    %% ==========================================

    woTicket {
        INT WorkOrderID PK "Composite PK part 1"
        INT LocationBusinessUnitID PK "Composite PK part 2, FK to buBusinessUnit"
        VARCHAR CustomerReferenceNumber "External reference"
        VARCHAR Title "Work order title"
        VARCHAR Description "Work order description"
        TINYINT CategoryID FK "FK to woCategory"
        VARCHAR OtherCategoryDescription "Free-text category"
        TINYINT PriorityID FK "FK to woPriority"
        VARCHAR PriorityChangeReason "Reason for priority change"
        VARCHAR ApartmentOrRoomNumber "Location detail"
        VARCHAR WhereLocated "Location description"
        INT ItemID FK "FK to teqItem (asset)"
        VARCHAR OtherItemDescription "Free-text item"
        INT AssignedTo "Assigned person ID"
        DATETIME DueDate "Target completion date"
        TINYINT StatusID FK "FK to woStatus"
        VARCHAR StatusComments "Status change comments"
        BIT IsNew "New work order flag"
        BIT WasEverClosed "Ever closed flag"
        INT ClosedWho "Who closed"
        DATETIME2 ClosedWhenUTC "When closed"
        INT CreatedWho "Creator person ID"
        DATETIME2 CreatedWhenUTC "Creation timestamp"
        INT UpdatedWho "Last updater"
        DATETIME2 UpdatedWhenUTC "Last update timestamp"
        VARCHAR AssignedToOther "Assigned to text (non-person)"
        INT OfferingID "Offering reference"
        DATE OccurrenceBeginningDate "Occurrence start"
        DATETIME2 OccurrenceBeginningTimeUTC "Occurrence start time"
        DATE OccurrenceEndingDate "Occurrence end"
        TINYINT ActionID FK "FK to woAction"
        INT ReasonID "Reason reference"
        INT ServiceID "Service reference"
        INT ServiceProviderID "SP business unit ID"
        INT ServiceProviderTechnicianID "SP tech person ID"
        TINYINT SeverityID "Severity level"
        INT WorkRequestTypeID "Work request type"
        BIT IsClosedToCustomers "Closed visibility to customers"
        BIT IsClosedToTels "Closed visibility to TELS"
        INT ParentWorkOrderID FK "Self-reference parent WO"
        INT ParentLocationBusinessUnitID FK "Self-reference parent BU"
        INT GpsLocationID "GPS location reference"
        INT LogTemplateRevisionID "Log template reference"
    }

    %% ==========================================
    %% Lookup Tables
    %% ==========================================

    woStatus {
        TINYINT StatusID PK
        VARCHAR Description "Status name"
        BIT IsSelectableFromTELS "TELS UI selectable"
        BIT IsSelectableFromLS "LocalServices selectable"
        VARCHAR LocalServicesDescription "LS display name"
        VARCHAR LocalServicesProse "LS description"
        BIT IsInitialValue "Default for new WOs"
        BIT IsInProgress "In-progress flag"
        BIT IsDefaultActivity "Default activity"
        BIT IsActionable "Actionable status"
        BIT IsOccurrenceTimeRequired "Requires occurrence time"
        BIT HasOccurrenceRange "Has date range"
        BIT HasComments "Comments required"
        BIT IsDispatched "Dispatched flag"
        VARCHAR DefaultComment "Auto-populated comment"
        BIT IsVisibleToCustomers "Customer-visible"
        BIT IsClosedToTels "Closed to TELS"
    }

    woCategory {
        TINYINT CategoryID PK
        VARCHAR Description "Category name"
        BIT IsActive "Active flag"
    }

    %% ==========================================
    %% Child Tables
    %% ==========================================

    woTicketStakeholder {
        INT WorkOrderID PK "Composite PK, FK to woTicket"
        INT LocationBusinessUnitID PK "Composite PK, FK to woTicket"
        INT StakeholderID PK "Person ID"
    }

    woTicketTimeLog {
        INT TimeLogID PK "IDENTITY"
        INT WorkOrderID FK "FK to woTicket"
        INT LocationBusinessUnitID FK "FK to woTicket"
        INT Who "Person who logged time"
        DECIMAL HoursSpent "Hours spent"
        INT WhoPersonId "Person ID"
    }

    woTicketMaterialCost {
        INT MaterialCostID PK "IDENTITY"
        INT WorkOrderID FK "FK to woTicket"
        INT LocationBusinessUnitID FK "FK to woTicket"
        VARCHAR Title "Material description"
        DECIMAL Cost "Cost amount"
    }

    woTicketImage {
        INT WorkOrderID FK "FK to woTicket"
        INT LocationBusinessUnitID FK "FK to woTicket"
        INT ImageID "Image identifier"
        VARCHAR ImageUrl "Image URL"
    }

    %% ==========================================
    %% Relationships
    %% ==========================================

    woTicket ||--o{ woTicketStakeholder : "WorkOrderID, LocationBusinessUnitID"
    woTicket ||--o{ woTicketTimeLog : "WorkOrderID, LocationBusinessUnitID"
    woTicket ||--o{ woTicketMaterialCost : "WorkOrderID, LocationBusinessUnitID"
    woTicket ||--o{ woTicketImage : "WorkOrderID, LocationBusinessUnitID"
    woStatus ||--o{ woTicket : "StatusID"
    woCategory ||--o{ woTicket : "CategoryID"
    woTicket ||--o| woTicket : "ParentWorkOrderID (self-ref)"

WorkOrders Schema (SQL Server — TELS Database)

erDiagram
    %% ==========================================
    %% Documents
    %% ==========================================

    WorkOrders_Documents {
        INT DocumentID PK "IDENTITY"
        INT WorkOrderID FK "FK to woTicket"
        INT LocationBusinessUnitID FK "FK to woTicket"
        INT CapturedPersonID "Who uploaded"
        DATETIME2 UploadedWhen "Upload timestamp"
        VARCHAR FileName "Original file name"
        VARCHAR FileType "File extension/type"
        BIT IsActive "Soft delete flag"
        UNIQUEIDENTIFIER FileGuid "File service GUID"
    }

    %% ==========================================
    %% Ticket Relationships
    %% ==========================================

    WorkOrders_TicketRelationships {
        INT AuthorizationNumber PK "Work order auth number"
        INT WorkRequestTypeID PK "Work request type"
        TINYINT RelationshipTypeID PK_FK "FK to TicketRelationshipTypes"
        INT RelatedAuthorizationNumber PK "Related WO auth number"
        INT RelatedWorkRequestTypeID PK "Related work request type"
    }

    WorkOrders_TicketRelationshipTypes {
        TINYINT RelationshipTypeID PK
        VARCHAR Code "DispatchToQuote, UnD2Q, FollowUpWork"
        BIT Bidirectional "Is bidirectional"
    }

    %% ==========================================
    %% Mass Creation
    %% ==========================================

    WorkOrders_MassCreateRequest {
        INT MassCreateRequestId PK "IDENTITY"
        INT ChainId "Organization chain"
        VARCHAR Status "Request status"
        VARCHAR Title "Work order title template"
        VARCHAR CustomArea "Custom area description"
        VARCHAR RequestedBy "Requestor name"
        VARCHAR Notes "Additional notes"
        VARCHAR WhereLocated "Location description"
        DATETIME DueDate "Target date"
        INT PriorityId "Priority level"
        INT CategoryId "Category"
        VARCHAR OtherCategoryDescription "Free-text category"
        DATETIME2 CreatedWhen "Creation timestamp"
        INT CreatedByPersonId "Creator person ID"
        VARCHAR CreatedByPersona "Creator persona"
        VARCHAR CreatedByName "Creator display name"
    }

    WorkOrders_MassCreateRequestFacility {
        INT MassCreateRequestId PK_FK "FK to MassCreateRequest"
        INT FacilityId PK "Target facility"
        VARCHAR Status "Per-facility status"
        INT AuthorizationNumber "Created WO auth number"
    }

    %% ==========================================
    %% Configuration
    %% ==========================================

    WorkOrders_ConfigurableFields {
        TINYINT ID PK
        VARCHAR Name "Field name (14 fields)"
    }

    WorkOrders_BusinessUnitConfigurations {
        TINYINT ConfigurableFieldID PK_FK "FK to ConfigurableFields"
        INT BusinessUnitID PK "Facility ID"
        TINYINT WorkOrderStatusID PK_FK "FK to woStatus"
        BIT IsRequired "Required flag"
    }

    %% ==========================================
    %% Work Order Extensions
    %% ==========================================

    WorkOrders_ChargeBackToResident {
        INT AuthorizationNumber PK "Work order auth number"
        BIT ChargeBackExpected "Chargeback expected"
        DECIMAL Charge "Charge amount"
        VARCHAR Reason "Chargeback reason"
    }

    WorkOrders_HasPermissionToEnter {
        INT AuthorizationNumber PK "Work order auth number"
        BIT HasPermissionToEnter "Permission flag"
    }

    WorkOrders_WorkOrderAssets {
        INT AuthorizationNumber PK "Work order auth number"
        INT AssetId PK "Asset reference"
        INT AssetActionId FK "FK to WorkOrderAssetActions"
        VARCHAR AssetActionCustom "Custom action text"
    }

    WorkOrders_WorkOrderAssetActions {
        INT AssetActionId PK
        VARCHAR Description "Repaired, Replaced, etc."
    }

    %% ==========================================
    %% Operational
    %% ==========================================

    WorkOrders_QueuedUpdateNotifications {
        INT ID PK "Notification queue entry"
    }

    %% ==========================================
    %% Relationships
    %% ==========================================

    WorkOrders_TicketRelationshipTypes ||--o{ WorkOrders_TicketRelationships : "RelationshipTypeID"
    WorkOrders_MassCreateRequest ||--o{ WorkOrders_MassCreateRequestFacility : "MassCreateRequestId"
    WorkOrders_ConfigurableFields ||--o{ WorkOrders_BusinessUnitConfigurations : "ConfigurableFieldID"
    WorkOrders_WorkOrderAssetActions ||--o{ WorkOrders_WorkOrderAssets : "AssetActionId"

Domain Groupings

Core Work Order Lifecycle (dbo schema)

Central work order tracking from creation through closure. - dbo.woTicket — The core work order entity with composite PK (WorkOrderID, LocationBusinessUnitID). Supports self-referential parent-child relationships, configurable categories/priorities/statuses, and full lifecycle tracking. - dbo.woTicketStakeholder — People associated with a work order (watchers, participants). - dbo.woTicketTimeLog — Time logged against a work order by maintenance personnel. - dbo.woTicketMaterialCost — Material costs recorded against a work order. - dbo.woTicketImage — Images attached to a work order.

Status & Category Reference (dbo schema)

dbo.woStatus — Work order statuses with rich configurability: TELS vs LocalServices selectability, actionability, occurrence time requirements, visibility controls.
dbo.woCategory — Work order categories with active/inactive flag.

Documents (WorkOrders schema)

WorkOrders.Documents — File attachments linked to work orders via the Files Service (FileGuid). Supports soft delete.

Ticket Relationships (WorkOrders schema)

WorkOrders.TicketRelationships — Links between work orders (e.g., dispatch-to-quote, follow-up work). Uses a 5-part composite key.
WorkOrders.TicketRelationshipTypes — Relationship type definitions with bidirectional support.

Mass Creation (WorkOrders schema)

WorkOrders.MassCreateRequest — Bulk work order creation requests scoped to a chain.
WorkOrders.MassCreateRequestFacility — Per-facility tracking for mass creation (status, resulting auth number).

Business Unit Configuration (WorkOrders schema)

WorkOrders.ConfigurableFields — 14 configurable fields (CustomerReferenceNumber, AssignedTo, Description, Location, DueDate, CategoryID, AssetID, StatusComments, Image, TimeLog, MaterialCosts, Document, ChargeBackToResident, HasPermissionToEnter).
WorkOrders.BusinessUnitConfigurations — Per-business-unit, per-status field requirement rules.

Work Order Extensions (WorkOrders schema)

WorkOrders.ChargeBackToResident — Resident chargeback tracking per work order.
WorkOrders.HasPermissionToEnter — Permission-to-enter flag per work order.
WorkOrders.WorkOrderAssets — Asset-to-work-order associations with action tracking (Repaired, Replaced, etc.).
WorkOrders.WorkOrderAssetActions — Asset action lookup table.

Operational (WorkOrders schema)

WorkOrders.QueuedUpdateNotifications — Queue for pending work order update notifications.

History Tables

All core tables have corresponding *Hist tables maintained by database triggers:

Table	History Table	Pattern
`dbo.woTicket`	`dbo.woTicketHist`	RetiredWhen/RetiredWho/UniqueifierID
`dbo.woTicketStakeholder`	`dbo.woTicketStakeholderHist`	Same pattern
`dbo.woTicketTimeLog`	`dbo.woTicketTimeLogHist`	Same pattern
`dbo.woTicketMaterialCost`	`dbo.woTicketMaterialCostHist`	Same pattern
`dbo.woTicketImage`	`dbo.woTicketImageHist`	Same pattern
`dbo.woCategory`	`dbo.woCategoryHist`	Same pattern
`WorkOrders.Documents`	`WorkOrders.DocumentsHist`	Same pattern
`WorkOrders.BusinessUnitConfigurations`	`WorkOrders.BusinessUnitConfigurationsHist`	Same pattern
`WorkOrders.ChargeBackToResident`	`WorkOrders.ChargeBackToResidentHist`	Same pattern
`WorkOrders.HasPermissionToEnter`	`WorkOrders.HasPermissionToEnterHist`	Same pattern
`WorkOrders.WorkOrderAssets`	`WorkOrders.WorkOrderAssetsHist`	Same pattern

Stored Procedures (82 total)

Procedure	Purpose
`WorkOrders.WorkOrderByAuthNumber_S`	Load work order by authorization number
`WorkOrders.Summary_S`	Work order summary for a business unit
`WorkOrders.Status_S`	Status lookup
`WorkOrders.Priorities_S`	Priority lookup
`WorkOrders.Categories_I/S/U`	Category CRUD
`WorkOrders.Documents_I/S/U/D`	Document CRUD
`WorkOrders.MassCreateRequest_I/S/U`	Mass creation request CRUD
`WorkOrders.BusinessUnitConfigurations_M/S`	BU config merge/select
`WorkOrders.TicketImage_Save/Load/Delete`	Image operations
`WorkOrders.ChargeBackToResident_M`	Chargeback merge
`WorkOrders.HasPermissionToEnter_M`	Permission merge

Cross-Database Dependencies

Database	Tables/Objects Referenced	Purpose
TELS (dbo)	`buBusinessUnit`, `teqItem`, `woPriority`, `woAction`	FK references from woTicket
Configuration_Manager	DSI-Internal configuration	Configuration data
CHANGETRACK	Audit tracking	User change auditing

Key Schema Patterns

Composite Primary Key: woTicket uses (WorkOrderID, LocationBusinessUnitID) — all child tables inherit this composite FK, reflecting facility-scoped work order design.
Authorization Number: Work orders are externally referenced by AuthorizationNumber (used in APIs and MQ events) rather than the internal composite PK.
History Triggers: All core tables have *Hist counterparts with RetiredWhen/RetiredWho/UniqueifierID temporal pattern.
Configurable Requirements: The BusinessUnitConfigurations table creates a 3-way relationship (field x business unit x status) for dynamic field requirements.
Dual Schema: Legacy tables in dbo (woTicket, woStatus, woCategory) alongside newer WorkOrders schema tables, reflecting incremental modernization.
Self-Referential Hierarchy: Work orders support parent-child relationships via ParentWorkOrderID/ParentLocationBusinessUnitID.
Soft Delete: Documents use IsActive flag rather than physical deletion.