TicketExceptions - Entity-Relationship Diagrams

PostgreSQL Schema (ticket_exceptions)

Database: tels_building_services_cloudapi (Shared with CloudAPI)

erDiagram
    %% Core Exception Tables
    ticket_exception_type {
        INT id PK "SEQUENCE"
        VARCHAR name
        VARCHAR external_system_identifier UK
    }

    ticket_exception_rule {
        INT id PK "SEQUENCE"
        BOOLEAN is_active
        INT exception_type_id FK
        VARCHAR description
    }

    ticket_exception {
        INT id PK "SERIAL"
        VARCHAR subject
        VARCHAR comment
        VARCHAR tbs_work_item_authorization_number
        INT tbs_work_item_request_type
        INT ticket_exception_type_id FK
        BOOLEAN ticket_exception_created
        BOOLEAN is_considered_solved_by_tbs
        INT service_provider_id
    }

    composite_identifier {
        INT id PK "SERIAL"
        VARCHAR domain
        VARCHAR type
        VARCHAR identifier
    }

    ticket_exception_external_reference {
        INT ticket_exception_id PK,FK
        INT composite_identifier_id PK,FK
    }

    %% History Tables
    ticket_exception_rule_hist {
        INT unique_id PK "SEQUENCE"
        INT id
        BOOLEAN is_active
        INT exception_type_id FK
        VARCHAR description
        TIMESTAMP created_when
        INT created_who_person_id
        VARCHAR created_who_person_name
        TIMESTAMP retired_when
        INT retired_who_person_id
        VARCHAR retired_who_person_name
    }

    ticket_exception_hist {
        INT unique_id PK "SEQUENCE"
        INT id
        VARCHAR subject
        VARCHAR comment
        VARCHAR tbs_work_item_authorization_number
        INT tbs_work_item_request_type
        INT ticket_exception_type_id FK
        TIMESTAMP created_when
        TIMESTAMP retired_when
        BOOLEAN ticket_exception_created
        BOOLEAN is_considered_solved_by_tbs
        INT service_provider_id
    }

    ticket_exception_external_reference_hist {
        INT id PK "SERIAL"
        INT ticket_exception_id
        INT composite_identifier_id
        TIMESTAMP created_when
        TIMESTAMP retired_when
    }

    %% Call-to-Action Configuration
    call_to_action {
        INT id PK "SERIAL"
        VARCHAR exception_type_identifier
        TEXT template_for_dayside
        TEXT template_for_after_hours
        TEXT template_for_westpark
        INT tbs_work_item_request_type
        INT priority
    }

    call_to_action_hist {
        INT unique_id PK "SERIAL"
        INT id FK
        VARCHAR exception_type_identifier
        TEXT template_for_dayside
        TEXT template_for_after_hours
        TEXT template_for_westpark
        INT tbs_work_item_request_type
        INT priority
        TIMESTAMP created_when
        TIMESTAMP retired_when
    }

    %% Additional Context
    ticket_exception_additional_comments {
        INT id PK "SERIAL"
        INT ticket_exception_id FK
        VARCHAR activity_type
        TEXT comment
        TIMESTAMPTZ created_when
    }

    ticket_exception_additional_comments_hist {
        INT id PK "SERIAL"
        INT comment_id
        INT ticket_exception_id
        VARCHAR activity_type
        TEXT comment
        TIMESTAMPTZ created_when
        TIMESTAMPTZ retired_when
    }

    ticket_exception_reopen_event {
        INT ticket_exception_id PK
        TIMESTAMP reopen_date_utc PK
        BOOLEAN simulated
    }

    %% Claims/Locks
    ticket_exception_claims {
        INT claim_id PK "SERIAL"
        INT authorization_number
        INT request_type
        VARCHAR exception_type_identifier
        TIMESTAMP created_at
    }

    ticket_claims {
        INT claim_id PK "IDENTITY"
        INT authorization_number UK
        TIMESTAMP created_at_utc
    }

    %% Service Provider Escalation
    sp_escalation_notification {
        INT id PK "SEQUENCE"
        INT service_provider_id
        DATE timestamp
        BOOLEAN was_notification_queued_for_send
        VARCHAR additional_information
    }

    sp_escalation_notification_detail {
        INT id PK "SEQUENCE"
        INT sp_escalation_notification_id FK
        VARCHAR email_address
        VARCHAR additional_information
    }

    sp_escalation_notification_authorization {
        INT sp_escalation_notification_id PK,FK
        INT tbs_work_item_request_type PK
        VARCHAR tbs_work_item_authorization_number PK
        VARCHAR_ARRAY exception_types
        INT tbs_facility_id
    }

    %% CXCC (Customer Experience Contact Center) AI Summaries
    cxcc_prompts {
        VARCHAR ticket_exception_type_name PK
        TEXT prompt
    }

    cxcc_summary {
        INT id PK "SERIAL"
        VARCHAR tbs_work_item_authorization_number
        INT tbs_work_item_request_type
        VARCHAR ticket_exception_type_name
        TEXT summary
    }

    %% Relationships
    ticket_exception_type ||--o{ ticket_exception_rule : "exception_type_id"
    ticket_exception_type ||--o{ ticket_exception : "ticket_exception_type_id"

    ticket_exception ||--o{ ticket_exception_external_reference : "ticket_exception_id"
    composite_identifier ||--o{ ticket_exception_external_reference : "composite_identifier_id"

    ticket_exception ||--o{ ticket_exception_additional_comments : "ticket_exception_id"
    ticket_exception ||--|| ticket_exception_reopen_event : "ticket_exception_id"

    sp_escalation_notification ||--o{ sp_escalation_notification_detail : "sp_escalation_notification_id"
    sp_escalation_notification ||--o{ sp_escalation_notification_authorization : "sp_escalation_notification_id"

    call_to_action ||--o{ call_to_action_hist : "id"
    ticket_exception_rule ||--o{ ticket_exception_rule_hist : "id"
    ticket_exception ||--o{ ticket_exception_hist : "id"
    ticket_exception_external_reference ||--o{ ticket_exception_external_reference_hist : "ticket_exception_id, composite_identifier_id"
    ticket_exception_additional_comments ||--o{ ticket_exception_additional_comments_hist : "comment_id"

---

History Tables Pattern

TicketExceptions uses an audit trail pattern for history tables. Each mutation (insert, update, delete) appends a new row to the corresponding *_hist table with metadata:

Column	Type	Description
unique_id or id	INT (SEQUENCE/SERIAL)	Primary key for the history record
created_when	TIMESTAMP	Timestamp of the change
created_who_person_id	INT	Person who made the change
created_who_person_name	VARCHAR	Name of the person
retired_when	TIMESTAMP	When record was retired/deleted
retired_who_person_id	INT	Who retired the record
retired_who_person_name	VARCHAR	Name of retirer

---

Domain Groupings

Exception Management Domain

• ticket_exception_type - Exception type definitions (ETA_MISSING, BID_NOT_RECEIVED, etc.)
• ticket_exception_rule - Active/inactive toggle per exception type
• ticket_exception - Individual exception instances
• ticket_exception_external_reference - Links to Zendesk tickets via composite_identifier
• composite_identifier - Generic external system reference (domain, type, identifier)

Call-to-Action Configuration Domain

• call_to_action - Template-based ticket content by shift (dayside, after-hours, westpark)
• Indexed by exception_type_identifier and tbs_work_item_request_type

Service Provider Escalation Domain

• sp_escalation_notification - Daily batch escalation records
• sp_escalation_notification_detail - Email recipients per escalation
• sp_escalation_notification_authorization - Work items included in escalation

Additional Context Domain

• ticket_exception_additional_comments - Supplemental comments/activity on exceptions
• ticket_exception_reopen_event - Tracks when exceptions are reopened

Synchronization Domain

• ticket_exception_claims - Ensures serialized processing per authorization + exception type
• ticket_claims - Ensures serialized processing per authorization number

AI/ML Domain

• cxcc_prompts - AI prompts for generating customer summaries
• cxcc_summary - Generated AI summaries for work items

---

Key Constraints & Indexes

Table	Constraint/Index	Type	Purpose
ticket_exception_type	external_system_identifier	UNIQUE	One external system per type
ticket_exception	tbs_work_item_authorization_number, tbs_work_item_request_type	INDEX	Fast lookups by work item
ticket_exception	service_provider_id (WHERE NOT NULL)	PARTIAL INDEX	Efficient SP filtering
composite_identifier	domain, type, identifier	UNIQUE	No duplicate external references
call_to_action	exception_type_identifier, tbs_work_item_request_type	UNIQUE INDEX	One template per type+request
ticket_exception_claims	authorization_number, request_type, exception_type_identifier	UNIQUE	Prevent duplicate claims
ticket_claims	authorization_number	UNIQUE	One claim per authorization
sp_escalation_notification	service_provider_id, timestamp, was_notification_queued_for_send	COMPOSITE INDEX	Escalation history queries
sp_escalation_notification_authorization	tbs_work_item_authorization_number, tbs_work_item_request_type	INDEX	Authorization lookups
sp_escalation_notification_authorization	tbs_facility_id	INDEX	Facility-based queries

---

Quartz Scheduler Tables (quartz schema)

TicketExceptions uses Quartz.NET for scheduled message processing. The standard Quartz schema includes:

• qrtz_job_details - Job definitions
• qrtz_triggers - Trigger definitions (simple, cron, etc.)
• qrtz_fired_triggers - Currently firing triggers
• qrtz_scheduler_state - Scheduler instance state
• qrtz_locks - Cluster coordination locks

---

Cross-Database References

TicketExceptions queries data from external systems but does not have direct FK relationships:

External System	Data Accessed	Mechanism
Zendesk	Ticket status, comments	REST API (HTTP)
Customers Service	Facility, customer data	HTTP API calls
Building Services	Work item details, dispatch list	HTTP API calls
Bidding Service	Bid status, conversations	MQ events
Compliance Service	Article notifications	MQ events
Contracted Services	Document upload status	MQ events

---

Migration Timeline

Date	Migration	Description
2020-03	Initial	Core exception tables
2020-06	Composite identifiers	External reference system
2021-02	Call-to-actions	Template-based ticket content
2021-08	Additional comments	Supplemental exception context
2022-01	Claims	Synchronization locks
2022-09	Reopen events	Track exception reopens
2023-03	SP Escalation	Service provider notifications
2024-05	CXCC	AI-generated summaries

---

Notes

• Database Sharing: TicketExceptions shares tels_building_services_cloudapi database with CloudAPI
• Composite Identifier: Generic pattern for storing external system references (Zendesk, future systems)
• History Tracking: All core tables have history tables for audit trail
• Quartz Persistence: Jobs stored in PostgreSQL for durable scheduled message processing
• Claims/Locks: Prevent race conditions when multiple events for same work item arrive concurrently