Enum Table Tenancy Consolidation

1. Overview
- 1.1. Decision
- 1.2. Benefits
2. Phase 1: Inventory
- 2.1. Tables WITHOUT tenant_id (need migration)
- 2.2. Tables WITH tenant_id but missing tenant in PK/EXCLUDE (bugs to fix)
3. Phase 2: Create Enum Table Template
4. Phase 3: Create Models for Existing Enums
5. Phase 4: Generate and Diff
6. Phase 5: Update Entity Templates
- 6.1. Add validations array to entity model
- 6.2. Template generates
7. Phase 6: Resume Currency Work
8. Tasks

1. Overview

Consolidate all enum/lookup tables to use tenant 0 (system tenant) for consistency. Currently some tables have no tenant_id column while others do. This creates confusion about when to use tenant 0 vs tenant-less tables.

1.1. Decision

All shared data lives in tenant 0. No more tenant-less tables.

1.2. Benefits

Consistent model everywhere
All shared data is auditable/versioned
Future flexibility if an enum ever needs to become tenant-specific
Simpler mental model: "everything has a tenant"

2. Phase 1: Inventory

2.1. Tables WITHOUT tenant_id (need migration)

Table	Component	Description
ores_refdata_rounding_types_tbl	refdata	Rounding methods (Up, Down, Closest, etc.)
ores_iam_tenant_statuses_tbl	iam	Tenant lifecycle statuses
ores_iam_tenant_types_tbl	iam	Tenant type classifications
ores_dq_artefact_types_tbl	dq	Artefact type registry

2.2. Tables WITH tenant_id but missing tenant in PK/EXCLUDE (bugs to fix)

Table	Component
ores_refdata_currencies_tbl	refdata
ores_refdata_countries_tbl	refdata
ores_iam_roles_tbl	iam
ores_iam_permissions_tbl	iam
ores_dq_change_reasons_tbl	dq
… (all other refdata tables)	refdata

3. Phase 2: Create Enum Table Template

3.1. New template: sql_schema_enum_create.mustache

For simple enum tables that will now be tenant-aware and bi-temporal.

3.2. Model structure

{
  "enum": {
    "product": "ores",
    "component": "refdata",
    "name": "rounding_type",
    "name_plural": "rounding_types",
    "description": "Valid rounding methods per ORE XML schema",
    "has_tenant_id": true,
    "generate_validation_function": true,
    "columns": [
      {"name": "code", "type": "text", "primary_key": true},
      {"name": "name", "type": "text"},
      {"name": "description", "type": "text"},
      {"name": "display_order", "type": "integer", "default": 0}
    ]
  }
}

3.3. Generated validation function

create or replace function ores_refdata_validate_rounding_type_fn(
    p_tenant_id uuid,
    p_value text
) returns void as $$
begin
    if not exists (
        select 1 from ores_refdata_rounding_types_tbl
        where tenant_id = ores_iam_system_tenant_id_fn()
        and code = p_value
        and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception 'Invalid rounding_type: %. Must be one of: %', p_value, (
            select string_agg(code, ', ' order by display_order)
            from ores_refdata_rounding_types_tbl
            where tenant_id = ores_iam_system_tenant_id_fn()
            and valid_to = ores_utility_infinity_timestamp_fn()
        ) using errcode = '23503';
    end if;
end;
$$ language plpgsql;

4. Phase 3: Create Models for Existing Enums

4.1. refdata enums

models/refdata/rounding_type_enum.json

4.2. iam enums

models/iam/tenant_status_enum.json
models/iam/tenant_type_enum.json

4.3. dq enums

models/dq/artefact_type_enum.json

5. Phase 4: Generate and Diff

For each enum:

Generate new SQL with template
Diff against existing SQL
Verify only expected changes:
- Added tenant_id column
- Added bi-temporal columns (valid_from, valid_to, version, etc.)
- Updated primary key to include tenant_id
- Added EXCLUDE constraint with tenant_id
- Added validation function
Update populate scripts to use system tenant

6. Phase 5: Update Entity Templates

6.1. Add validations array to entity model

{
  "entity": {
    ...
    "validations": [
      {
        "function": "ores_refdata_validate_rounding_type_fn",
        "column": "rounding_type"
      }
    ]
  }
}

6.2. Template generates

perform ores_refdata_validate_rounding_type_fn(NEW.tenant_id, NEW.rounding_type);

Enum Table Tenancy Consolidation

Table of Contents

1. Overview

1.1. Decision

1.2. Benefits

2. Phase 1: Inventory

2.1. Tables WITHOUT tenant_id (need migration)

2.2. Tables WITH tenant_id but missing tenant in PK/EXCLUDE (bugs to fix)

3. Phase 2: Create Enum Table Template

3.1. New template: sql_schema_enum_create.mustache

3.2. Model structure

3.3. Generated validation function

4. Phase 3: Create Models for Existing Enums

4.1. refdata enums

4.2. iam enums

4.3. dq enums

5. Phase 4: Generate and Diff

6. Phase 5: Update Entity Templates

6.1. Add validations array to entity model

6.2. Template generates

7. Phase 6: Resume Currency Work

8. Tasks

8.1. TODO Inventory all enum tables needing migration

8.2. TODO Create sql_schema_enum_create.mustache template

8.3. TODO Create model for rounding_types as prototype

8.4. TODO Generate and diff rounding_types

8.5. TODO Create models for remaining enums

8.6. TODO Generate and diff all enums

8.7. TODO Update populate scripts for system tenant

8.8. TODO Add validations support to entity templates

8.9. TODO Resume currency generation with validations