SQL Schema Creator

1. Use code generation: Create a JSON model and generate the SQL using --profile sql. See ORE Studio Codegen for details.
2. Update templates: If the entity doesn't fit existing templates, modify the templates in library/templates/ to support the new pattern.
3. Manual creation: Only create SQL manually as a last resort when code generation cannot support the schema pattern.

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/scripts/validate_schema.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 dq_catalogs_tbl has upsert function dq_catalogs_upsert_fn, so searching for "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)

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/{component}_{entity}_create.sql.

The file must include:

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

-- Use the appropriate schema: metadata for dq_*, production for others
create table if not exists "{schema}"."{component}_{entity}_tbl" (
    "{pk_column}" 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 ({pk_column}, valid_from, valid_to),
    exclude using gist (
        {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/{component}_{entity}_notify_trigger.sql:

-- Notify function goes in same schema as the table
create or replace function {schema}.{component}_{entity}_notify_fn()
returns trigger as $$
declare
    notification_payload jsonb;
    entity_name text := '{schema}.{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 {component}_{entity}_notify_trg
after insert or update or delete on {schema}.{component}_{entity}_tbl
for each row execute function {schema}.{component}_{entity}_notify_fn();

Where:

• {schema} is metadata or production based on the component prefix
• {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/iam_accounts_notify_trigger.sql.

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

set schema '{schema}';

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

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

set schema '{schema}';

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

For reference data, create projects/ores.sql/populate/{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.

set schema '{schema}';

-- Helper function for idempotent inserts (goes in public schema)
create or replace function public.upsert_{entity}(
    p_field1 text,
    p_field2 text
    -- add parameters as needed
) returns void as $$
begin
    if not exists (
        select 1 from {schema}.{component}_{entity}_tbl
        where field1 = p_field1
          and valid_to = public.utility_infinity_timestamp_fn()
    ) then
        insert into {schema}.{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 public.upsert_{entity}('value1', 'value2');
select public.upsert_{entity}('value3', 'value4');

-- Cleanup helper function
drop function public.upsert_{entity}(text, text);

-- Summary
select '{entity}' as entity, count(*) as count
from {schema}.{component}_{entity}_tbl
where valid_to = public.utility_infinity_timestamp_fn();

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

If you have a corresponding C++ entity, update the schema and tablename constants to match:

struct {entity}_entity {
    // Use "metadata" for dq_* tables, "production" for others
    constexpr static const char* schema = "{schema}";
    constexpr static const char* tablename = "{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/{component}_{entity}_create.sql
\ir ../schema/{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/{component}_{entity}_notify_trigger_drop.sql  -- if applicable
\ir ./drop/{component}_{entity}_drop.sql

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

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

1. Drop and recreate the template database:

psql -U postgres -c "DROP DATABASE IF EXISTS ores_template"
psql -U postgres -f projects/ores.sql/setup_template.sql

1. Create a test instance:

psql -U postgres -f projects/ores.sql/create_instance.sql

1. Verify the table exists:

psql -U ores -d <database_name> -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/scripts/validate_schema.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

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:

• 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 to prevent overlapping validity periods

Always filter by valid_to = public.utility_infinity_timestamp_fn() to get current records:

select * from {schema}.{component}_{entity}_tbl
where valid_to = public.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 assets_images_tbl

All tables with change_reason_code validate the code via the insert trigger function. The trigger calls metadata.refdata_validate_change_reason_fn() to ensure the code exists:

-- Inside the insert trigger function:
new.change_reason_code := metadata.refdata_validate_change_reason_fn(new.change_reason_code);

This validates that the change reason code exists in metadata.dq_change_reasons_tbl 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)