CloudAPI - Entity-Relationship Diagrams

PostgreSQL Schema (Primary Database)

erDiagram
    %% ==========================================
    %% Synced Schema - Dispatch List (Blue-Green Partitioned)
    %% ==========================================

    synced_dispatch_list_data {
        BIGINT id PK
        INT authorization_code
        INT work_order_id
        INT work_request_type_id
        VARCHAR severity_code
        VARCHAR status_code
        INT service_code
        INT location_business_unit_id
        VARCHAR facility_name
        INT contact_person_id
        VARCHAR contact_person_name
        TIMESTAMP created_date
        TIMESTAMP due_date
        TIMESTAMP last_modified_date
        VARCHAR assigned_to
        TEXT work_description
        DECIMAL complex_priority_score
        DECIMAL propensity_to_win_score
    }

    synced_dispatch_list_blue {
        BIGINT id PK
        INT authorization_code
        INT work_order_id
    }

    synced_dispatch_list_green {
        BIGINT id PK
        INT authorization_code
        INT work_order_id
    }

    synced_dispatch_list_sync_records {
        BIGINT id PK
        VARCHAR sync_type
        TIMESTAMP sync_start_time
        TIMESTAMP sync_end_time
        INT records_synced
        BOOLEAN success
        TEXT error_message
        VARCHAR initiated_by
    }

    synced_dispatch_list_sync_settings {
        INT id PK
        VARCHAR current_write_table
        VARCHAR current_read_table
        TIMESTAMP last_sync_time
        INT minimum_sync_interval_seconds
    }

    %% ==========================================
    %% Public Schema - Configuration & Scoring
    %% ==========================================

    public_note_checker_configurations {
        INT id PK "IDENTITY"
        INT organization_id
        TEXT phrases_to_check "JSON array"
        BOOLEAN is_active
        TIMESTAMP created_date
        TIMESTAMP modified_date
        VARCHAR created_by
        VARCHAR modified_by
    }

    public_complex_priority_score {
        INT authorization_number PK
        INT work_request_type_id PK
        DECIMAL normalized_score
        DECIMAL win_rate_for_similar_projects_in_market
        INT number_of_potential_service_providers
        INT amount_of_contracts_in_place
        BOOLEAN district_manager_configured
        DECIMAL building_historic_annual_spend
        DECIMAL chain_historic_annual_spend
        DECIMAL owner_historic_annual_spend
        DECIMAL building_project_win_rate
        DECIMAL chain_project_win_rate
        DECIMAL owner_project_win_rate
        DECIMAL chain_percent_spend
        DECIMAL owner_percent_spend
        BOOLEAN is_owned_by_welltower_or_ventas
        BOOLEAN is_core_market
        TIMESTAMP created_date
        TIMESTAMP updated_date
    }

    public_project_propensity_to_win_scores {
        INT authorization_number PK
        VARCHAR model_version PK
        DECIMAL prediction_score
        TIMESTAMP prediction_date
        INT service_id
        INT location_business_unit_id
        TEXT model_metadata "JSON"
    }

    public_dispatch_list_preferences {
        INT person_id PK
        INT version_number
        TEXT preferences "JSON"
        TIMESTAMP created_date
        TIMESTAMP updated_date
    }

    public_after_hours_override_configuration {
        INT id PK
        INT organization_id
        BOOLEAN is_enabled
        TIMESTAMP created_date
        TIMESTAMP modified_date
    }

    public_status {
        VARCHAR status_code PK
        VARCHAR status_description
        INT sort_order
    }

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

    synced_dispatch_list_data ||--o| public_complex_priority_score : "authorization_code, work_request_type_id"
    synced_dispatch_list_data ||--o| public_project_propensity_to_win_scores : "authorization_code"
    synced_dispatch_list_data }o--|| synced_dispatch_list_blue : "partitioned into"
    synced_dispatch_list_data }o--|| synced_dispatch_list_green : "partitioned into"

Partitioning Strategy

Blue-Green Dispatch List

The synced.dispatch_list_data table uses list-based partitioning for zero-downtime updates:

Blue Partition: synced.dispatch_list_blue
Green Partition: synced.dispatch_list_green

How It Works:

Current State: Tracked in dispatch_list_sync_settings.current_write_table and current_read_table
Sync Process:
Write to inactive partition (e.g., if Blue is active, write to Green)
Once sync complete, atomically switch read table via dispatch_list_get_read_table() function
Old partition data is archived/cleaned up
Benefits:
Zero downtime during sync
Safe rollback if sync fails
Consistent read performance

SQL Server Legacy Schema (Read-Only)

The following tables are queried via Dapper stored procedures:

Core Tables

Table	Schema	Purpose
`woTicket`	TELS.dbo	Work orders/tickets (primary dispatch source)
`woTicketHist`	TELS.dbo	Ticket history
`telsFacility`	CONTACT.dbo	Facilities
`buBusinessUnit`	CONTACT.dbo	Business units
`ClLocn`	MDID_BTRIEVE.dbo	Location time zones
`Severity`	TELS.dbo	Severity definitions
`Status`	TELS.dbo	Status definitions
`Permit`	TELS.dbo	Permits
`WorkItemAttachments`	TELS.dbo	Work item attachments

Stored Procedures

Procedure	Type	Purpose
`TELS.BuildingServices.CintasTicketBatch_Create`	Insert	Create Cintas batch
`TELS.BuildingServices.CintasTicketRecord_AddToBatch`	Insert	Add records to batch
`TELS.BuildingServices.WorkItemDescriptionsHistory_S`	Select	Get work item history
`TELS.BuildingServices.WorkItemDescriptionsHistoryFirstEntry_S`	Select	Get first work item entry
`TELS.BuildingServices.usp_Permits_S`	Select	Search permits
`TELS.BuildingServices.usp_Permits_I`	Insert	Insert permit
`TELS.BuildingServices.usp_Permits_U`	Update	Update permit
`TELS.BuildingServices.WorkItems_ByContactPersonId_S`	Select	Get work items by contact
`TELS.LocalServices.usp_SaveDashboardContactPreferences_IU`	Insert/Update	Save dispatch preferences
`TELS.LocalServices.usp_LoadDashboardContactPreferences_S`	Select	Load dispatch preferences

Table-Valued Parameters

TVP	Columns	Purpose
`[BuildingServices].[ServiceInfo_20230423]`	AuthorizationNumber, TicketID, WorkRequestTypeID, IsClosedToTels	Bulk invoice lookup
`[BuildingServices].[udt_nvarchar]`	Value (varchar)	Generic string list
`[BuildingServices].[udt_authorization_number_list]`	AuthorizationNumber (int)	Authorization number list

Databricks (Analytics - Read-Only)

CloudAPI queries Databricks via ODBC for complex priority scoring metrics. No direct table access - uses dynamic SQL queries.

Query Results Include: - Facility metrics (PLT, ZipCode, MarketId, ChainId, OwnerId) - Annual spend by location/chain/owner - Project win rates - Market analytics (service provider counts, district manager configuration) - Core market identification

FastAPI (ML Service - REST API)

CloudAPI calls FastAPI HTTP endpoints for ML predictions. No direct database access.

Endpoints: 1. Predict - Real-time propensity-to-win inference 2. Backfill Predict - Historical data scoring

Request Parameters: service_id, location_business_unit_id, authorization_number (backfill only), prediction_datetime

Migration Timeline

Date	Migration	Description
2022-11-28	Initial	Schema and roles
2022-12-01	dispatch_list_data	Core table creation
2022-12-05	UDT types	User-defined types
2022-12-07	sync_records	Sync tracking
2023-01-18	sync_settings	Configuration
2023-06-05	note_checker	Validation rules
2023-07-12	dispatch_list	User tracking columns
2024-03-11	Renamed	Ticketing configuration
2024-04-04	after_hours	Override config
2024-09-24	status	Status loading
2025-06-13	complex_priority	Scoring table
2025-07-01	CPS/P2W columns	Dispatch list additions
2025-08-04	CPS updates	Request type PK
2025-10-30	propensity_to_win	ML score table
2025-11-17	Enrichment	CPS and P2W columns

Domain Groupings

Dispatch List Domain

dispatch_list_data (partitioned: blue/green) - Main operational table
dispatch_list_sync_records - Audit trail of sync operations
dispatch_list_sync_settings - Sync configuration and state
dispatch_list_preferences - User-specific dispatch list settings

Scoring Domain

complex_priority_score - Multi-factor prioritization scores from Databricks
project_propensity_to_win_scores - ML-driven win probability predictions

Configuration Domain

note_checker_configurations - Content validation rules
after_hours_override_configuration - After-hours behavior settings
status - Status code definitions

Key Constraints

dispatch_list_data: Partitioned by blue/green with atomic switching
complex_priority_score: Composite PK (authorization_number, work_request_type_id)
project_propensity_to_win_scores: Composite PK (authorization_number, model_version) for version tracking
dispatch_list_preferences: PK on person_id (one preference set per person)

Cross-Database Integration

Source	Target	Mechanism	Purpose
SQL Server woTicket	PostgreSQL dispatch_list_data	Dapper stored proc	Primary data sync
PostgreSQL dispatch_list	Databricks	ODBC query	Analytics enrichment
PostgreSQL	FastAPI	HTTP REST	ML predictions
PostgreSQL complex_priority_score	Databricks	ODBC query	Metric calculation