Tasks Database - Entity-Relationship Diagrams

Core Operational Tables

erDiagram
    %% ==========================================
    %% External / dbo Schema Tables (referenced)
    %% ==========================================

    dbo_telsTask {
        INT TaskID PK
        INT MarketSegmentId
        TINYINT DefaultRecurrenceUnit
        INT DefaultRecurrsEvery
    }

    dbo_telsTaskInstance {
        INT TaskInstanceID PK
        INT LocationBusinessUnitId
        INT TaskId FK
        VARCHAR Status
        DATETIMEOFFSET DueBy
    }

    dbo_telsProgram {
        INT ProgramID PK
        VARCHAR Name
        VARCHAR Status
    }

    dbo_LogTemplate {
        INT LogTemplateID PK
    }

    %% ==========================================
    %% Tasks Schema - Schedule Domain
    %% ==========================================

    Tasks_Schedule {
        INT Id PK "IDENTITY"
        INT TaskId FK
        INT ProgramId FK
        INT FacilityId
        TINYINT RecurrenceUnit
        INT RecursEvery
        INT RecurrenceOffset
        INT DayOfMonth
        INT Month
        INT DayOfWeek
        BIT IsActive
        DATETIMEOFFSET CreatedWhen
        INT CreatedWhoPersonId
        NVARCHAR CreatedWhoPersonName
        DATETIMEOFFSET DeletedWhen
        INT DeletedWhoPersonId
    }

    Tasks_ScheduleBuildingLink {
        INT ScheduleId PK,FK
        INT BuildingId PK
        BIT IsActive
        DATETIMEOFFSET CreatedWhen
        INT CreatedWhoPersonId
        NVARCHAR CreatedWhoPersonName
        DATETIMEOFFSET DeletedWhen
        INT DeletedWhoPersonId
    }

    Tasks_ScheduleAssetLink {
        INT ScheduleId PK,FK
        INT AssetId PK
        BIT IsActive
        DATETIMEOFFSET CreatedWhen
        INT CreatedWhoPersonId
        NVARCHAR CreatedWhoPersonName
        DATETIMEOFFSET DeletedWhen
        INT DeletedWhoPersonId
    }

    %% ==========================================
    %% Tasks Schema - Instance Domain
    %% ==========================================

    Tasks_InstanceTimeLog {
        INT Id PK "IDENTITY"
        INT TaskInstanceId FK
        INT PersonId
        NVARCHAR PersonName
        INT DurationInMinutes
        DATETIMEOFFSET CreatedWhen
        INT CreatedWhoPersonId
        NVARCHAR CreatedWhoPersonName
        BIT IsActive
        DATETIMEOFFSET DeletedWhen
        INT DeletedWhoPersonId
    }

    Tasks_InstanceMaterialCost {
        INT Id PK "IDENTITY"
        INT TaskInstanceId FK
        INT PersonId
        NVARCHAR PersonName
        DECIMAL Cost
        NVARCHAR Description
        DATETIMEOFFSET CreatedWhen
        INT CreatedWhoPersonId
        NVARCHAR CreatedWhoPersonName
        BIT IsActive
        DATETIMEOFFSET DeletedWhen
        INT DeletedWhoPersonId
    }

    Tasks_InstanceBuildingLink {
        INT InstanceId PK,FK
        INT BuildingId PK
    }

    Tasks_InstanceAssetLink {
        INT InstanceId PK,FK
        INT AssetId PK
    }

    %% ==========================================
    %% Tasks Schema - Configuration Domain
    %% ==========================================

    Tasks_TaskDefaultLogTemplate {
        INT TaskId PK,FK
        INT LogTemplateId FK
    }

    Tasks_TaskConfigurationTemplates {
        INT Id PK "SEQUENCE"
        NVARCHAR Name
        INT MarketSegmentId
        DATETIMEOFFSET CreatedWhen
        INT CreatedWhoPersonId
        NVARCHAR CreatedWhoPersonName
        BIT IsActive
        DATETIMEOFFSET DeletedWhen
        INT DeletedWhoPersonId
    }

    Tasks_TaskConfigurationTemplateTasks {
        INT TemplateId PK,FK
        INT TaskId PK,FK
        TINYINT RecurrenceUnit
        INT RecursEvery
        INT RecurrenceOffset
    }

    Tasks_BestPracticeTaskConfigurationTemplateChains {
        INT TemplateId PK,FK
        INT ChainId PK
    }

    Tasks_FacilityTaskConfigurationTemplates {
        INT FacilityId PK
        INT TemplateId FK
    }

    Tasks_DefaultRecurrenceOverride {
        INT ChainId PK
        INT TaskId PK,FK
        INT RecursEvery
        TINYINT RecurrenceUnit
    }

    %% ==========================================
    %% Tasks Schema - Product Recommendations
    %% ==========================================

    Tasks_TaskProductReplacements {
        INT MasterItemID PK
        VARCHAR ReplacementProduct
        VARCHAR ReplacementFilter
        VARCHAR ReplacementParts
    }

    Tasks_TasksEligibleForRecommendations {
        INT TaskId PK
        BIT IsFilterTask
    }

    dbo_telsDefaultReqDocTasks {
        INT TaskID PK,FK
    }

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

    dbo_telsTask ||--o{ Tasks_Schedule : "TaskId"
    dbo_telsProgram ||--o{ Tasks_Schedule : "ProgramId"
    Tasks_Schedule ||--o{ Tasks_ScheduleBuildingLink : "ScheduleId"
    Tasks_Schedule ||--o{ Tasks_ScheduleAssetLink : "ScheduleId"

    dbo_telsTaskInstance ||--o{ Tasks_InstanceTimeLog : "TaskInstanceId"
    dbo_telsTaskInstance ||--o{ Tasks_InstanceMaterialCost : "TaskInstanceId"
    dbo_telsTaskInstance ||--o{ Tasks_InstanceBuildingLink : "InstanceId"
    dbo_telsTaskInstance ||--o{ Tasks_InstanceAssetLink : "InstanceId"

    dbo_telsTask ||--o| Tasks_TaskDefaultLogTemplate : "TaskId"
    dbo_LogTemplate ||--o{ Tasks_TaskDefaultLogTemplate : "LogTemplateId"

    Tasks_TaskConfigurationTemplates ||--o{ Tasks_TaskConfigurationTemplateTasks : "TemplateId"
    dbo_telsTask ||--o{ Tasks_TaskConfigurationTemplateTasks : "TaskId"

    Tasks_TaskConfigurationTemplates ||--o{ Tasks_BestPracticeTaskConfigurationTemplateChains : "TemplateId"
    Tasks_TaskConfigurationTemplates ||--o{ Tasks_FacilityTaskConfigurationTemplates : "TemplateId"

    dbo_telsTask ||--o{ Tasks_DefaultRecurrenceOverride : "TaskId"
    dbo_telsTask ||--o| dbo_telsDefaultReqDocTasks : "TaskID"

History Tables

The system uses two distinct patterns for *Hist tables:

Pattern 1: Audit Trail (Tasks Schema)

Tables in the Tasks schema use an audit/snapshot pattern. Every insert, update, or delete on the source table appends a new row to the corresponding *Hist table. The source table holds the current state; the *Hist table holds a complete chronological log of every mutation. Both the source row and all its history rows coexist simultaneously.

These history tables share the same columns as their parent table plus:

Column	Type	Description
HistoryId	BIGINT (SEQUENCE)	Primary key for the history record
HistoryCreatedWhen	DATETIMEOFFSET(7)	Timestamp of the change
HistoryPersonId	INT	Person who made the change
HistoryPersonName	NVARCHAR(100)	Name of the person
HistoryPersona	BIGINT	Persona/role used
HistoryAction	CHAR(1)	Action type (I=Insert, U=Update, D=Delete)

erDiagram
    Tasks_Schedule ||--o{ Tasks_ScheduleHist : "audit trail"
    Tasks_ScheduleBuildingLink ||--o{ Tasks_ScheduleBuildingLinkHist : "audit trail"
    Tasks_ScheduleAssetLink ||--o{ Tasks_ScheduleAssetLinkHist : "audit trail"
    Tasks_InstanceTimeLog ||--o{ Tasks_InstanceTimeLogHist : "audit trail"
    Tasks_InstanceMaterialCost ||--o{ Tasks_InstanceMaterialCostHist : "audit trail"
    Tasks_TaskConfigurationTemplates ||--o{ Tasks_TaskConfigurationTemplatesHist : "audit trail"
    Tasks_TaskConfigurationTemplateTasks ||--o{ Tasks_TaskConfigurationTemplateTasksHist : "audit trail"
    Tasks_BestPracticeTaskConfigurationTemplateChains ||--o{ Tasks_BPTaskConfigTemplateChainsHist : "audit trail"
    Tasks_FacilityTaskConfigurationTemplates ||--o{ Tasks_FacilityTaskConfigTemplatesHist : "audit trail"
    Tasks_DefaultRecurrenceOverride ||--o{ Tasks_DefaultRecurrenceOverrideHist : "audit trail"

Pattern 2: Hot/Cold Archival (dbo.telsTaskInstance)

dbo.telsTaskInstance and dbo.telsTaskInstanceHist use a fundamentally different pattern: hot/cold storage archival. Rather than tracking a series of snapshots, records are moved (swept) from the active table to the history table once a TaskInstance reaches the end of its mutable lifecycle phase.

• dbo.telsTaskInstance is the hot table - holds only instances that are still in a mutable state (open, in-progress)
• dbo.telsTaskInstanceHist is the cold table - holds completed/finalized instances that have been swept out of the active table for long-term, read-only storage
• The schema of both tables is identical (no additional audit columns on the Hist table)
• A record exists in one table or the other, never both simultaneously
• This keeps the active table small and fast for queries against current work

erDiagram
    dbo_telsTaskInstance ||--|| dbo_telsTaskInstanceHist : "archived after lifecycle ends"

Key distinction: When querying the full history of task instances (e.g., for reporting), both tables must be unioned. The *Hist table here is not an audit log - it is the same data, relocated for performance and storage optimization.

Cross-Database References

The Tasks schema references tables in other databases/schemas:

Reference	Source	Target
Task definitions	Tasks.Schedule.TaskId	dbo.telsTask.TaskID (TELS database)
Task instances	Tasks.InstanceTimeLog.TaskInstanceId	dbo.telsTaskInstance.TaskInstanceID (TELS database)
Programs/Features	Tasks.Schedule.ProgramId	dbo.telsProgram.ProgramID (TELS database)
Log templates	Tasks.TaskDefaultLogTemplate.LogTemplateId	dbo.LogTemplate.LogTemplateID (TELS database)
Contacts	Referenced in stored procedures	CONTACT.dbo.* tables

Domain Groupings

Schedule Domain

Manages recurring task schedules with facility/building/asset scoping: - Schedule - Core recurrence definition (which task, how often, for which facility) - ScheduleBuildingLink - Many-to-many: which buildings a schedule applies to - ScheduleAssetLink - Many-to-many: which assets a schedule applies to

Instance Domain

Tracks execution of individual task instances (time, materials, location): - InstanceTimeLog - Time spent on a task instance by a person - InstanceMaterialCost - Material costs recorded against a task instance - InstanceBuildingLink - Which buildings an instance is associated with - InstanceAssetLink - Which assets an instance is associated with

Configuration Domain

Templates and overrides for task configuration at facility/chain level: - TaskConfigurationTemplates - Named templates grouping tasks with market segment - TaskConfigurationTemplateTasks - Tasks belonging to a template (with optional recurrence overrides) - BestPracticeTaskConfigurationTemplateChains - Links templates to chains (best practice) - FacilityTaskConfigurationTemplates - Assigns a template to a facility - DefaultRecurrenceOverride - Per-chain recurrence overrides for specific tasks - TaskDefaultLogTemplate - Default log template assignment per task

Product Recommendations Domain

• TaskProductReplacements - Replacement product mappings by master item
• TasksEligibleForRecommendations - Tasks eligible for product recommendations