SQL Schema Creator

1. Use code generation (required): Create a JSON model and run the generator. See ORE Studio Codegen for details.
2. Update templates first, then generate: If the entity needs a new pattern, extend the appropriate template in library/templates/ before generating.
3. Hand-craft as last resort: Only when the schema pattern genuinely cannot be expressed via templates (e.g., multi-table soft FK chains). In this case, document why codegen was skipped with a comment in the file header, and ensure the file exactly follows the conventions of codegen output.

1. Create a JSON model in projects/ores.codegen/models/{component}/:
   • For UUID primary key tables: use *_domain_entity.json naming
   • For junction/association tables: use *_junction.json naming

2. Generate SQL:

   cd projects/ores.codegen
   ./run_generator.sh models/{component}/{entity}_domain_entity.json output/ --profile sql
   

3. Review the generated output in output/
4. Copy to projects/ores.sql/create/{component}/
5. Continue with steps 5-7 below (orchestration, testing, validation)

1. Gather requirements about the table (name, columns, relationships).
2. Determine the appropriate component prefix based on the C++ component.
3. Follow the detailed instructions to create the required SQL files.
4. Update the corresponding C++ entity file with the new table name.
5. Update the orchestration scripts to include the new files.
6. Test the schema creation.
7. Run schema validation (./projects/ores.sql/utility/validate_schemas.sh).

Entity names in tables should use the plural form:

This convention ensures consistency and makes it clear that tables contain collections of entities.

The key principle is that function names preserve the table name for grep-ability. For example, the table ores_dq_catalogs_tbl has upsert function ores_dq_catalogs_upsert_fn, so searching for "ores_dq_catalogs" finds both the table and its functions.

All create and drop scripts should be idempotent (safe to run multiple times). Follow these patterns to avoid unnecessary NOTICE messages:

Scripts that don't create schema objects but provide utility functions or generate other SQL files follow a similar pattern:

Generation scripts produce other SQL files (e.g., for review before execution). They should:

• Output to a well-known filename
• Include comments in generated output explaining how to regenerate
• Be idempotent (safe to run multiple times)

The tenant_id column is required for all temporal and artefact tables to ensure proper multi-tenant isolation. It must be included in:

• The primary key
• EXCLUDE constraints
• All queries (version lookup, updates, deletes)

Notification channels use the pattern ores_{entity} (plural form):

The notification payload includes:

• entity: Full entity name (e.g., production.iam.account, production.refdata.currency, metadata.dq.dataset)
• timestamp: Change timestamp
• entity_ids: Array of affected entity IDs

Create a file projects/ores.sql/create/ores_{component}_{entity}_create.sql.

The file must include:

1. GPL license header (copy from existing files)
2. Table definition with temporal support:

-- All tables use the public schema with ores_ prefix
create table if not exists "ores_{component}_{entity}_tbl" (
    "{pk_column}" uuid not null,
    "tenant_id" uuid not null,
    "version" integer not null,
    -- domain-specific columns here
    "modified_by" text not null,
    "change_reason_code" text not null,
    "change_commentary" text not null,
    "valid_from" timestamp with time zone not null,
    "valid_to" timestamp with time zone not null,
    primary key (tenant_id, {pk_column}, valid_from, valid_to),
    exclude using gist (
        tenant_id WITH =,
        {pk_column} WITH =,
        tstzrange(valid_from, valid_to) WITH &&
    ),
    check ("valid_from" < "valid_to"),
    check ("{pk_column}" <> '')  -- for text primary keys
    -- OR for UUID primary keys:
    -- check ("id" <> '00000000-0000-0000-0000-000000000000'::uuid)
);
-- Note: change_reason_code validation is handled by the insert trigger function,
-- not via check constraint. See Step 4 below for the trigger implementation.

Create a notification trigger to enable real-time UI updates when data changes. This is required for all entities that will be displayed in the Qt UI, as it forms the first stage of the event pipeline:

Database Trigger (pg_notify) → Event Source → Event Bus → Clients

Without the trigger, clients won't receive change notifications and their UI will become stale without any indication.

Create projects/ores.sql/create/ores_{component}_{entity}_notify_trigger.sql:

create or replace function ores_{component}_{entity}_notify_fn()
returns trigger as $$
declare
    notification_payload jsonb;
    entity_name text := '{cpp_namespace}.{entity}';
    change_timestamp timestamptz := NOW();
    changed_id text;
begin
    if TG_OP = 'DELETE' then
        changed_id := OLD.{pk_column}::text;
    else
        changed_id := NEW.{pk_column}::text;
    end if;

    notification_payload := jsonb_build_object(
        'entity', entity_name,
        'timestamp', to_char(change_timestamp, 'YYYY-MM-DD HH24:MI:SS'),
        'entity_ids', jsonb_build_array(changed_id)
    );

    perform pg_notify('ores_{entity_plural}', notification_payload::text);
    return null;
end;
$$ language plpgsql;

create or replace trigger ores_{component}_{entity}_notify_trg
after insert or update or delete on ores_{component}_{entity}_tbl
for each row execute function ores_{component}_{entity}_notify_fn();

Where:

• {cpp_namespace} is the C++ namespace (e.g., iam, refdata, assets)
• {entity_plural} is the plural form for the notification channel

Follow the pattern in projects/ores.sql/create/ores_iam_accounts_notify_trigger.sql.

Create projects/ores.sql/drop/ores_{component}_{entity}_drop.sql:

drop trigger if exists ores_{component}_{entity}_insert_trg on "ores_{component}_{entity}_tbl";
drop rule if exists ores_{component}_{entity}_delete_rule on "ores_{component}_{entity}_tbl";
drop function if exists ores_{component}_{entity}_insert_fn();
drop table if exists "ores_{component}_{entity}_tbl";

Also create the notify trigger drop script projects/ores.sql/drop/ores_{component}_{entity}_notify_trigger_drop.sql:

drop trigger if exists ores_{component}_{entity}_notify_trg on "ores_{component}_{entity}_tbl";
drop function if exists ores_{component}_{entity}_notify_fn();

Every table with a tenant_id column must have a Row-Level Security (RLS) policy for tenant isolation. Policies are grouped per component in a single file.

For reference data, create projects/ores.sql/populate/ores_{component}_{entity}_populate.sql:

Note: The insert trigger automatically manages version, valid_from, and valid_to. Population scripts should pass 0 for version (which tells the trigger to use default behavior) and can omit the temporal columns since the trigger will set them.

-- Helper function for idempotent inserts
create or replace function ores_seed_{entity}_fn(
    p_field1 text,
    p_field2 text
    -- add parameters as needed
) returns void as $$
begin
    if not exists (
        select 1 from ores_{component}_{entity}_tbl
        where field1 = p_field1
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        insert into ores_{component}_{entity}_tbl (
            id, version, field1, field2,
            modified_by, change_reason_code, change_commentary
        )
        values (
            gen_random_uuid(), 0, p_field1, p_field2,
            'system', 'system.new_record', 'System seed data'
        );
        -- Note: version will be set to 1 by the trigger (first version)
        -- valid_from and valid_to are also set by the trigger
        raise notice 'Created {entity}: %', p_field1;
    else
        raise notice '{entity} already exists: %', p_field1;
    end if;
end;
$$ language plpgsql;

-- Seed data
select ores_seed_{entity}_fn('value1', 'value2');
select ores_seed_{entity}_fn('value3', 'value4');

-- Cleanup helper function
drop function ores_seed_{entity}_fn(text, text);

-- Summary
select '{entity}' as entity, count(*) as count
from ores_{component}_{entity}_tbl
where valid_to = ores_utility_infinity_timestamp_fn();

Follow the pattern in projects/ores.sql/populate/ores_dq_change_reasons_populate.sql.

If you have a corresponding C++ entity, update the tablename constant to match:

struct {entity}_entity {
    constexpr static const char* tablename = "ores_{component}_{entity}_tbl";

    // ... fields matching SQL columns
};

Entity files are located at: projects/ores.{component}/include/ores.{component}/repository/{entity}_entity.hpp

1. Add the creation script to projects/ores.sql/template/create_schema.sql:

-- In appropriate component section
\echo '--- {Component} tables ---'
\ir ../schema/ores_{component}_{entity}_create.sql
\ir ../schema/ores_{component}_{entity}_notify_trigger.sql  -- if applicable

The file is organized in sections:

• Utility functions
• Change control tables (dq_change_reason_*)
• IAM tables
• Reference data tables
• Assets tables
• Variability tables
• Telemetry tables
• Geo tables
• Add the drop script to projects/ores.sql/drop_all.sql (in reverse dependency order):

-- {Component}
\ir ./drop/ores_{component}_{entity}_notify_trigger_drop.sql  -- if applicable
\ir ./drop/ores_{component}_{entity}_drop.sql

1. If you have population data, add it to projects/ores.sql/populate/populate.sql:

\ir ores_{component}_{entity}_populate.sql

1. Create a test database:

psql -U postgres -v db_name='ores_test_schema' -f projects/ores.sql/create_database.sql
PGPASSWORD='DDL_PASS' psql -U ores_ddl_user -d ores_test_schema -f projects/ores.sql/setup_schema.sql

1. Verify the table exists:

psql -U ores_cli_user -d ores_test_schema -c "\d ores_{component}_{entity}_tbl"

1. Test the notification trigger:

psql -U ores -d <database_name> -c "LISTEN ores_{entity_plural}; INSERT INTO ores_{component}_{entity}_tbl (...) VALUES (...);"

Run the schema validation script to check for common issues before committing:

./projects/ores.sql/utility/validate_schemas.sh

The validator checks for:

• Temporal tables missing required columns (version, modified_by)
• Functions created without corresponding drop statements
• Tables created without corresponding drop statements
• Other schema consistency issues

Note: The validator does not yet check for RLS policy create/drop parity. Policy drops must be verified manually or by reviewing the {component}_rls_policies_drop.sql files.

Important: Fix all issues reported by the validator before committing. This validation also runs in CI and will fail the build if there are warnings.

All entity tables use bitemporal support with:

• tenant_id: UUID reference to the tenant (required, part of primary key)
• valid_from / valid_to: Validity period for the record
• version: Incrementing version number for optimistic concurrency
• modified_by: Username or system identifier that made the change
• change_reason_code: Reference to dq_change_reasons_tbl
• change_commentary: Free-text explanation of the change
• GiST exclusion constraint including tenant_id WITH = to prevent overlapping validity periods within the same tenant

Always filter by valid_to = ores_utility_infinity_timestamp_fn() to get current records:

select * from ores_{component}_{entity}_tbl
where valid_to = ores_utility_infinity_timestamp_fn();

Records are never physically deleted. Instead, the delete rule updates valid_to to the current timestamp, creating a historical record.

For tables with image_id or other foreign keys to temporal tables, reference the ID without temporal constraints (the application layer manages consistency):

"image_id" uuid,  -- Optional FK to ores_assets_images_tbl

All tables with change_reason_code validate the code via the insert trigger function. The trigger calls ores_dq_validate_change_reason_fn() with the tenant_id to ensure the code exists for that tenant:

-- Inside the insert trigger function:
new.change_reason_code := ores_dq_validate_change_reason_fn(new.tenant_id, new.change_reason_code);

This validates that the change reason code exists in ores_dq_change_reasons_tbl for the specified tenant and raises an exception with SQLSTATE 23503 if invalid.

projects/ores.codegen/
├── run_generator.sh           # Main entry point
├── src/generator.py           # Generator implementation
├── library/templates/         # Mustache templates
│   ├── sql_schema_domain_entity_create.mustache
│   └── sql_schema_junction_create.mustache
└── models/                    # Model definitions
    └── dq/
        ├── dataset_bundle_domain_entity.json
        └── dataset_bundle_member_junction.json

Domain entity template produces:

• Table with UUID primary key and null-UUID check
• Unique indexes on natural key columns for active records
• Version uniqueness index
• GIST exclusion constraint on (id, temporal range)
• Insert trigger with version conflict detection
• Delete rule for soft deletes

Junction template produces:

• Table with composite primary key (left_code, right_code, valid_from)
• GIST exclusion on both keys + temporal range
• Indexes on both foreign key columns
• Unique index on composite key for active records
• Non-empty check on change_reason_code
• Insert trigger with composite key lookup

1. Create a JSON model in projects/ores.codegen/models/{component}/
2. Run the generator to produce SQL
3. Review the output in output/
4. Copy to projects/ores.sql/create/{component}/
5. Update orchestration scripts (see Step 6)
6. Test and validate (see Steps 7-8)

These tables are copied from tenant 0 (system tenant) when provisioning a new tenant. They contain essential IAM configuration that every tenant needs:

These are "system IAM tables" - their data is defined in the foundation layer (setup_schema.sql) and copied verbatim to each new tenant. Other IAM tables (accounts, sessions, login_info) are tenant-specific and start empty.

These tables are NOT copied during tenant provisioning. Instead, they are populated via the dataset library using the "Publish Datasets" feature in the Data Librarian window:

Dataset tables flow: DQ artefact tables → Publish → Production (refdata) tables.

These tables start empty for new tenants and are populated by user actions:

When implementing tenant provisioning:

1. Copy only system IAM tables (permissions, roles, role_permissions)
2. Do NOT copy refdata tables - tenants publish their own datasets
3. Tenant-specific tables start empty

When implementing tenant deprovisioning:

1. Use dynamic table discovery to clean up ALL tenant data
2. Soft-delete temporal tables (set valid_to to current timestamp)
3. Hard-delete non-temporal tables

Test files are located in projects/ores.sql/test/ and follow the naming pattern {component}_{entity}_test.sql:

projects/ores.sql/test/
├── test.sql                           # Extension setup (create extension pgtap)
├── refdata_validation_test.sql        # Validation function tests
├── refdata_currencies_test.sql        # Currency trigger behavior tests
└── ...

Each test file follows this pattern:

/* License header */

/**
 * pgTAP tests for {entity} behavior.
 *
 * Tests cover:
 * - Feature 1
 * - Feature 2
 *
 * Run with: pg_prove -d {database} test/{testfile}.sql
 */

begin;

select plan({number_of_tests});

-- Test 1: Description
select is(
    (actual_value),
    expected_value,
    'test description'
);

-- Test 2: Exception testing
select throws_ok(
    $$sql statement that should fail$$,
    'SQLSTATE',  -- e.g., '23503' for FK violation
    NULL,        -- message pattern (NULL to ignore)
    'test description'
);

select * from finish();

rollback;

Use the run_tests.sh script which reads connection details from environment variables:

# Run all tests using environment variables
ORES_TEST_DB_DATABASE=ores_dev_local1 ./run_tests.sh

# Run a specific test file
ORES_TEST_DB_DATABASE=ores_dev_local1 ./run_tests.sh refdata_validation_test.sql

Environment variables:

Tests run in a single transaction with rollback, which means:

• Multiple updates to the same entity within one test may fail due to current_timestamp returning the same value (transaction frozen)
• For version increment tests, use separate transactions or integration tests

Use a TimescaleDB hypertable (instead of a plain PostgreSQL table) when:

• Data is written frequently at high volume (heartbeat samples, telemetry, events)
• Queries are almost exclusively time-range based (e.g., last N minutes, last session)
• The data has a clear foreign key to an entity (session, device, account) that acts as a natural partition key for compression and indexing
• Retention beyond a configurable window is not needed

Plain tables remain preferable for:

• Low-volume audit/history data (session start/end, account changes)
• Bitemporal records where valid_from / valid_to are not monotonically increasing
• Reference data with infrequent writes

Always choose a timestamp column as the partitioning dimension. In ORE Studio the naming convention is sample_time, event_time, or recorded_at (with type timestamptz).

SELECT create_hypertable('ores_iam_session_samples_tbl', 'sample_time');

Enable compression after a short window (3–7 days) to reduce storage. Segment by the foreign-key column (e.g., session_id) so that time-range queries per entity can decompress only the relevant chunks.

ALTER TABLE ores_iam_session_samples_tbl SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'session_id'
);
SELECT add_compression_policy('ores_iam_session_samples_tbl',
    INTERVAL '7 days');

Drop old chunks automatically. Typical retention periods:

SELECT add_retention_policy('ores_iam_session_samples_tbl',
    INTERVAL '30 days');

Always add a composite index (foreign_key, time DESC) in addition to the primary key. This enables efficient time-range queries filtered by entity:

-- Fetch latest N samples for a session
SELECT * FROM ores_iam_session_samples_tbl
WHERE session_id = $1
ORDER BY sample_time DESC
LIMIT 100;

The DESC ordering matches the most-common access pattern (latest samples first for chart display and monitoring). TimescaleDB's chunk exclusion makes this query efficient even over very large datasets.