@startuml pivot_multitenancy_erd

!define TABLE(name) entity name << (T,#FFAAAA) >>
!define PK(x) <b><u>x</u></b>
!define FK(x) <i>x</i>

skinparam linetype ortho
skinparam entity {
    BackgroundColor<<Metadata>> LightBlue
    BackgroundColor<<Data>> LightGreen
    BackgroundColor<<Index>> LightYellow
    BackgroundColor<<Relationship>> Plum
    BackgroundColor<<Audit>> LightCoral
}

' ==========================================
' METADATA TABLES
' ==========================================

entity "organizations" as org <<Metadata>> {
    PK(org_id) : INTEGER
    --
    org_name : VARCHAR(100)
    display_name : VARCHAR(255)
    is_active : BOOLEAN
    created_at : TIMESTAMPTZ
    modified_at : TIMESTAMPTZ
}

entity "mt_objects" as obj <<Metadata>> {
    PK(obj_id) : INTEGER
    --
    FK(org_id) : INTEGER
    obj_name : VARCHAR(100)
    label : VARCHAR(255)
    plural_label : VARCHAR(255)
    description : TEXT
    is_custom : BOOLEAN
    is_active : BOOLEAN
    created_at : TIMESTAMPTZ
    created_by : UUID
    modified_at : TIMESTAMPTZ
    modified_by : UUID
}

entity "mt_fields" as fld <<Metadata>> {
    PK(field_id) : INTEGER
    --
    FK(org_id) : INTEGER
    FK(obj_id) : INTEGER
    field_name : VARCHAR(100)
    label : VARCHAR(255)
    data_type : VARCHAR(50)
    field_num : INTEGER
    is_indexed : BOOLEAN
    is_unique : BOOLEAN
    is_required : BOOLEAN
    is_external_id : BOOLEAN
    FK(reference_obj_id) : INTEGER
    relationship_type : VARCHAR(50)
    relationship_name : VARCHAR(100)
    delete_constraint : VARCHAR(50)
    formula_expression : TEXT
    formula_return_type : VARCHAR(50)
    length : INTEGER
    precision : INTEGER
    scale : INTEGER
    created_at : TIMESTAMPTZ
    modified_at : TIMESTAMPTZ
}

' ==========================================
' DATA TABLES
' ==========================================

entity "mt_data" as data <<Data>> {
    PK(org_id) : INTEGER
    PK(guid) : UUID
    --
    FK(obj_id) : INTEGER
    name : VARCHAR(255)
    --
    value0 : VARCHAR(4000)
    value1 : VARCHAR(4000)
    value2 : VARCHAR(4000)
    ... : ...
    value49 : VARCHAR(4000)
    --
    is_deleted : BOOLEAN
    deleted_at : TIMESTAMPTZ
    deleted_by : UUID
    created_at : TIMESTAMPTZ
    created_by : UUID
    modified_at : TIMESTAMPTZ
    modified_by : UUID
}

entity "mt_clobs" as clob <<Data>> {
    PK(org_id) : INTEGER
    PK(clob_id) : UUID
    --
    FK(data_guid) : UUID
    field_num : INTEGER
    clob_value : TEXT
    created_at : TIMESTAMPTZ
    modified_at : TIMESTAMPTZ
}

' ==========================================
' INDEX TABLES
' ==========================================

entity "mt_indexes" as idx <<Index>> {
    PK(idx_id) : BIGINT
    --
    FK(org_id) : INTEGER
    FK(obj_id) : INTEGER
    field_num : INTEGER
    FK(guid) : UUID
    string_value : VARCHAR(255)
    num_value : DECIMAL(18,6)
    date_value : TIMESTAMPTZ
}

entity "mt_unique_fields" as uniq <<Index>> {
    PK(idx_id) : BIGINT
    --
    FK(org_id) : INTEGER
    FK(obj_id) : INTEGER
    field_num : INTEGER
    FK(guid) : UUID
    string_value : VARCHAR(255)
    num_value : DECIMAL(18,6)
    date_value : TIMESTAMPTZ
}

entity "mt_fallback_index" as fallback <<Index>> {
    PK(org_id) : INTEGER
    PK(guid) : UUID
    --
    FK(obj_id) : INTEGER
    name : VARCHAR(255)
    name_lower : VARCHAR(255)
    modified_at : TIMESTAMPTZ
}

entity "mt_name_denorm" as denorm <<Index>> {
    PK(org_id) : INTEGER
    PK(guid) : UUID
    --
    FK(obj_id) : INTEGER
    name : VARCHAR(255)
}

' ==========================================
' RELATIONSHIP TABLE
' ==========================================

entity "mt_relationships" as rel <<Relationship>> {
    PK(rel_id) : BIGINT
    --
    FK(org_id) : INTEGER
    relationship_id : INTEGER
    FK(parent_obj_id) : INTEGER
    FK(parent_guid) : UUID
    FK(child_obj_id) : INTEGER
    FK(child_guid) : UUID
}

' ==========================================
' AUDIT TABLE
' ==========================================

entity "mt_field_history" as hist <<Audit>> {
    PK(history_id) : BIGINT
    --
    FK(org_id) : INTEGER
    FK(obj_id) : INTEGER
    FK(guid) : UUID
    field_num : INTEGER
    old_value : TEXT
    new_value : TEXT
    data_type : VARCHAR(50)
    changed_at : TIMESTAMPTZ
    changed_by : UUID
}

' ==========================================
' RELATIONSHIPS
' ==========================================

' Metadata relationships
org ||--o{ obj : "contains"
org ||--o{ fld : "owns"
obj ||--o{ fld : "has"
fld }o--o| obj : "references"

' Data relationships
org ||--o{ data : "partitioned by"
obj ||--o{ data : "instances of"
data ||--o{ clob : "large text"

' Index relationships
data ||--o{ idx : "indexed in"
data ||--o{ uniq : "unique fields"
data ||--|| fallback : "name search"
data ||--|| denorm : "name display"

' Relationship table
data }o--o{ rel : "parent"
data }o--o{ rel : "child"
obj ||--o{ rel : "parent type"
obj ||--o{ rel : "child type"

' History
data ||--o{ hist : "audit trail"

@enduml