TELS Compliance Service - Entity-Relationship Diagrams

Overview

The Compliance Service uses PostgreSQL with the public schema. The database contains 16 application tables organized around an approval workflow engine: articles (workflow instances), steps (approval actions), roles (who approves), and rules (what triggers workflows). Dynamic data is stored as JSONB. The schema includes 68 stored procedures and 50+ migration scripts (2020-2026).

Approval Workflow Schema (PostgreSQL)

erDiagram
    %% ==========================================
    %% Core Workflow
    %% ==========================================

    article {
        BIGSERIAL id PK
        BIGINT object_type_id FK "FK to object_type"
        BIGINT client_id FK "FK to composite_identifier (chain)"
        BIGINT location_id FK "FK to composite_identifier (facility)"
        BIGINT requester_id FK "FK to composite_identifier (person)"
        JSONB attribute_dictionary "Dynamic key-value attributes"
        JSONB step_template "Copied from matching rule"
        BIGINT article_status_id FK "FK to article_status"
        TIMESTAMPTZ created_when_utc "Creation timestamp"
        TIMESTAMPTZ rules_run_when_utc "When rules were evaluated"
    }

    step {
        BIGSERIAL id PK
        BIGINT role_id FK "FK to role (who approves)"
        BIGINT article_id FK "FK to article"
        BIGINT step_status_id FK "FK to step_status"
        INT step_order "Sequence in workflow"
        INT minutes_to_authorize "Timeout period"
        TIMESTAMPTZ action_when_utc "When action taken"
        BIGINT action_person_id FK "FK to composite_identifier"
        VARCHAR_128 action_person_name "Actor display name"
        TEXT action_note "Note for action"
        BIGINT verbal_admin_id FK "FK to composite_identifier"
        VARCHAR_128 verbal_admin_name "Verbal auth admin name"
        BOOLEAN requester_in_role_auto_approves "Auto-approve flag"
    }

    %% ==========================================
    %% Workflow Types & Statuses
    %% ==========================================

    object_type {
        BIGSERIAL id PK "1=SRCreated 2=SRCompleted 3=QuoteReq 4=FulfillCompleted 5=NTEExtension"
        VARCHAR_100 description "Type name"
        JSONB attributes "Attribute schema definition"
    }

    article_status {
        BIGSERIAL id PK
        BIGINT object_type_id FK "FK to object_type"
        VARCHAR_100 description "PendingAuth, Authorized, Denied, QuoteRequested, NoRulesHit, PendingEval"
    }

    step_status {
        BIGSERIAL id PK
        BIGINT object_type_id FK "FK to object_type"
        VARCHAR_100 description "Pending, Authorized, Denied, Expired, Bypassed"
        BOOLEAN note_required "Requires action note"
        BOOLEAN is_user_action "User-initiated action"
        BIGINT final_article_status_id FK "FK to article_status"
        BOOLEAN finishes_article "Completes the workflow"
        VARCHAR action_description "Decline, Authorize, Deny"
    }

    %% ==========================================
    %% Roles & Assignments
    %% ==========================================

    role {
        BIGSERIAL id PK
        BIGINT client_id FK "FK to composite_identifier (chain)"
        VARCHAR_100 description "First Approver, Second Approver, etc."
        BOOLEAN is_active "Active flag"
        TIMESTAMPTZ created_when_utc "Created"
        TIMESTAMPTZ retired_when_utc "Retired (nullable)"
        BIGINT parent_role_id FK "FK to role (self-ref hierarchy)"
    }

    role_assignment {
        BIGINT role_id PK_FK "FK to role"
        BIGINT person PK_FK "FK to composite_identifier"
    }

    %% ==========================================
    %% Rules Engine
    %% ==========================================

    client_object_type_rule {
        BIGSERIAL id PK
        BIGINT object_type_id FK "FK to object_type"
        BIGINT client_id FK "FK to composite_identifier (chain)"
        JSONB rules "Array of ArticleConstraints + StepTemplates"
        BOOLEAN is_active "Active flag"
        BIGINT created_person_id FK "FK to composite_identifier"
        VARCHAR_128 created_person_name "Creator name"
        TIMESTAMPTZ created_when_utc "Created"
        BIGINT retired_person_id FK "FK to composite_identifier"
        VARCHAR_128 retired_person_name "Retiree name"
        TIMESTAMPTZ retired_when_utc "Retired (nullable)"
    }

    %% ==========================================
    %% External References
    %% ==========================================

    composite_identifier {
        BIGSERIAL id PK
        VARCHAR_100 domain "e.g., TELS, compliance"
        VARCHAR_100 type "e.g., Customers:ChainID, Customers:FacilityId"
        VARCHAR_100 identifier "Entity ID"
    }

    article_external_references {
        BIGINT article_id PK_FK "FK to article"
        BIGINT composite_identifier_id PK_FK "FK to composite_identifier"
    }

    %% ==========================================
    %% Notifications
    %% ==========================================

    notification_type {
        INT id PK "1=Email, 2=SMS, 3=PushNotification"
        VARCHAR_100 description "Channel name"
    }

    person_notification_configuration {
        INT person PK_FK "FK to composite_identifier"
        INT notification_type_id PK_FK "FK to notification_type"
        VARCHAR_100 address "Email or phone number"
        BOOLEAN enabled "Channel enabled"
    }

    sent_notification {
        BIGSERIAL id PK
        INT person FK "FK to composite_identifier"
        INT notification_type_id FK "FK to notification_type"
        BIGINT article_id "Article reference"
        BIGINT step_id "Step reference"
        TIMESTAMPTZ sent_when_utc "Sent timestamp"
    }

    %% ==========================================
    %% Reference Data
    %% ==========================================

    attribute_type {
        BIGSERIAL id PK "1=Int, 2=String, 3=Decimal"
        VARCHAR_100 description "Type name"
    }

    evaluation_type {
        BIGSERIAL id PK "1=equals, 2=greater_than, 3=less_than"
        VARCHAR_100 description "Operator name"
    }

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

    object_type ||--o{ article_status : "object_type_id"
    object_type ||--o{ step_status : "object_type_id"
    object_type ||--o{ article : "object_type_id"
    object_type ||--o{ client_object_type_rule : "object_type_id"
    article_status ||--o{ article : "article_status_id"
    article ||--o{ step : "article_id"
    article ||--o{ article_external_references : "article_id"
    step_status ||--o{ step : "step_status_id"
    role ||--o{ step : "role_id"
    role ||--o{ role_assignment : "role_id"
    role ||--o| role : "parent_role_id (self-ref)"
    composite_identifier ||--o{ article_external_references : "composite_identifier_id"
    composite_identifier ||--o{ role_assignment : "person"
    notification_type ||--o{ person_notification_configuration : "notification_type_id"
    notification_type ||--o{ sent_notification : "notification_type_id"

Domain Groupings

Core Workflow

article — Workflow instances with dynamic JSONB attributes and step templates. Linked to a client (chain), location (facility), and requester (person) via composite identifiers.
step — Individual approval actions within an article's workflow. Each step is assigned to a role and tracks authorization, denial, timeout, or bypass with timestamps and actor information.

Workflow Configuration

object_type — Workflow type definitions (5 types: ServiceRequestCreated, ServiceRequestCompleted, QuoteRequest, FulfillmentCompleted, NotToExceedExtensionRequest). Contains JSONB attribute schema.
article_status — Per-object-type status definitions (PendingAuthorization, Authorized, Denied, etc.).
step_status — Per-object-type step action definitions with finality and note requirements.
client_object_type_rule — Business rules with JSONB arrays of ArticleConstraints and StepTemplates.

Roles & Access

role — Hierarchical approval roles per client (chain) with parent-child relationships.
role_assignment — Junction table linking roles to people.
role_assignment_history — Audit trail for role assignment changes.

External References

composite_identifier — Domain/type/identifier references to external entities (chains, facilities, persons, work items).
article_external_references — Links articles to external entities.

Notifications

notification_type — Channel types (Email, SMS, PushNotification).
person_notification_configuration — Per-person, per-channel preferences.
sent_notification — Delivery audit log.
quote_email_variant_log — A/B email testing tracker.

Reference Data

attribute_type — Data types (Int, String, Decimal) for rule attribute evaluation.
evaluation_type — Comparison operators (equals, greater_than, less_than).

History Tables

Table	History Table
`article`	`article_history`
`article_external_references`	`article_external_references_history`
`step`	`step_history`
`role_assignment`	`role_assignment_history`

Key Schema Patterns

JSONB Workflow Definition: Rules (client_object_type_rule.rules), step templates (article.step_template), and article attributes (article.attribute_dictionary) are all JSONB, making the workflow engine fully configurable without schema changes.
Composite Identifiers: All external entity references (chains, facilities, persons, work items) go through the composite_identifier table with domain/type/identifier pattern, decoupling the compliance schema from external system IDs.
Per-Object-Type Configuration: Statuses and step statuses are defined per object type, allowing different workflow types to have different status sets.
Hierarchical Roles: Roles support parent-child relationships for organizational role inheritance.
Auto-Approval: Steps can be automatically approved when the article requester holds the approving role (requester_in_role_auto_approves).
Verbal Authorization: Steps track verbal authorization with a separate admin identity field.
Soft Retirement: Rules and roles use retired_when_utc rather than physical deletion.