TELS Platform Service - Entity-Relationship Diagrams

Overview

The Platform Service uses PostgreSQL 15.3 with a single platform schema in the platform database. The schema is minimal — 2 application tables plus the DbUp journal table. All settings are stored as JSONB values, providing a flexible schema-less configuration store.

---

Platform Schema (PostgreSQL — platform Database)

erDiagram
    %% ==========================================
    %% Settings Store
    %% ==========================================

    platform_settings {
        SERIAL id PK "Auto-increment"
        VARCHAR_200 subsystem_name "Subsystem identifier"
        VARCHAR_200 subsystem_name_lower "Lowercase for case-insensitive lookup"
        VARCHAR_200 setting_name "Setting identifier"
        VARCHAR_200 setting_name_lower "Lowercase for case-insensitive lookup"
        JSONB setting_value "JSON value (any type)"
    }

    platform_settings_history {
        BIGSERIAL history_id PK "Auto-increment"
        TIMESTAMPTZ history_created_when "When change was recorded"
        INT history_person_id "Who made the change"
        VARCHAR_200 history_person_name "Person display name"
        SMALLINT history_persona "Persona enum value"
        CHAR_1 history_action "i=insert, u=update, d=delete"
        SERIAL id "Setting ID at time of change"
        VARCHAR_200 subsystem_name "Subsystem at time of change"
        VARCHAR_200 subsystem_name_lower "Lowercase subsystem"
        VARCHAR_200 setting_name "Setting name at time of change"
        VARCHAR_200 setting_name_lower "Lowercase setting"
        JSONB setting_value "Value at time of change"
    }

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

    platform_settings ||--o{ platform_settings_history : "id (logical)"

---

Domain Groupings

Settings Store

Centralized key-value configuration for all TELS subsystems. - platform.settings — Active settings identified by subsystem name + setting name. Values stored as JSONB, enabling any JSON-serializable type (strings, objects, arrays, numbers). Unique constraint on lowercased subsystem/setting name pair for case-insensitive lookups. - platform.settings_history — Complete audit trail of all setting changes. Records the action type (insert/update/delete), the person who made the change (ID, name, persona), and the setting value at the time of the change.

Infrastructure

• platform.schema_versions — DbUp migration journal tracking applied schema migrations.

---

Indexes

Table	Index	Type	Columns
platform.settings	u_settings_subsystem_name_lower_setting_name_lower	Unique	subsystem_name_lower, setting_name_lower
platform.settings_history	nu_settings_history_id_history_created_when	Non-unique	id, history_created_when

---

Stored Functions

Function	Purpose	Parameters
platform.tfn_settings_all_s	Paginated retrieval of all settings	p_skip, p_take
platform.tfn_settings_by_subsystem_s	Paginated settings for a subsystem	p_subsystem_name, p_skip, p_take
platform.tfn_setting_s	Single setting lookup	p_subsystem_name, p_setting_name
platform.tfn_setting_m	Upsert setting with audit record	Setting fields + person/persona context
platform.tfn_setting_d	Delete setting with audit record	Subsystem name, setting name + person context

---

Key Schema Patterns

1. JSONB Values: Settings are stored as PostgreSQL JSONB, avoiding the need for typed columns and allowing any JSON structure. This is the only TELS service using JSONB storage.
2. Case-Insensitive Lookups: Lowercased columns (subsystem_name_lower, setting_name_lower) with a unique index enable case-insensitive matching without runtime LOWER() calls.
3. Audit in Functions: History records are created within the stored functions (tfn_setting_m, tfn_setting_d) rather than via triggers, ensuring atomic audit trail creation.
4. Repeatable Read Isolation: Write operations use Repeatable Read transaction isolation to prevent lost updates in concurrent modification scenarios.
5. Minimal Schema: Only 2 application tables — this is a simple, focused service with no complex relationships or domain entities.