projects/ores.sql/
├── create/                          # Table creation and trigger scripts
│   ├── {component}/                 # Component subdirectories
│   │   ├── create_{component}.sql   # Component master script
│   │   ├── {component}_{entity}_create.sql
│   │   └── {component}_{entity}_notify_trigger_create.sql
│   └── create.sql                   # Master-of-masters
├── drop/                            # Drop scripts for cleanup
│   ├── {component}/                 # Component subdirectories
│   │   ├── drop_{component}.sql     # Component master script
│   │   ├── {component}_{entity}_drop.sql
│   │   └── {component}_{entity}_notify_trigger_drop.sql
│   └── drop.sql                     # Master-of-masters
├── populate/                        # Reference data population
│   ├── {component}/                 # Component subdirectories
│   │   ├── populate_{component}.sql # Component master script
│   │   └── {component}_{entity}_populate.sql
│   ├── data/                        # Static data files (SVG flags, etc.)
│   └── populate.sql                 # Master-of-masters
├── instance/                        # Instance-specific initialization
│   └── init_instance.sql
├── create_database.sql              # Creates database (postgres superuser)
├── setup_schema.sql                 # Sets up schema (ores_ddl_user)
├── teardown_all.sql                 # Complete cluster teardown
└── recreate_database.sql            # Full wipe and rebuild (dev)

create table if not exists "ores"."{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.

-- For a table with text primary key (e.g., iso_code, code, name):
create table if not exists "ores"."refdata_currencies_tbl" (
    "iso_code" text not null,
    ...
    check ("valid_from" < "valid_to"),
    check ("iso_code" <> '')
);

-- For a table with UUID primary key:
create table if not exists "ores"."dq_datasets_tbl" (
    "id" uuid not null,
    ...
    check ("valid_from" < "valid_to"),
    check ("id" <> '00000000-0000-0000-0000-000000000000'::uuid)
);

-- For composite keys, add checks for each key column:
create table if not exists "ores"."dq_subject_areas_tbl" (
    "name" text not null,
    "domain_name" text not null,
    ...
    check ("valid_from" < "valid_to"),
    check ("name" <> ''),
    check ("domain_name" <> '')
);

-- Version uniqueness index (required for optimistic concurrency)
create unique index if not exists {component}_{entity}_version_uniq_idx
on "ores"."{component}_{entity}_tbl" ({pk_column}, version)
where valid_to = ores.utility_infinity_timestamp_fn();

-- Natural key uniqueness index (if applicable)
create unique index if not exists {component}_{entity}_{column}_uniq_idx
on "ores"."{component}_{entity}_tbl" ({column})
where valid_to = ores.utility_infinity_timestamp_fn();

create or replace function ores.{component}_{entity}_insert_fn()
returns trigger as $$
declare
    current_version integer;
begin
    select version into current_version
    from "ores"."{component}_{entity}_tbl"
    where {pk_column} = NEW.{pk_column}
      and valid_to = ores.utility_infinity_timestamp_fn();

    if found then
        -- This is an update (record with same PK exists)
        if NEW.version != 0 and NEW.version != current_version then
            raise exception 'Version conflict: expected version %, but current version is %',
                NEW.version, current_version
                using errcode = 'P0002';
        end if;
        NEW.version = current_version + 1;

        -- Close the old record
        update "ores"."{component}_{entity}_tbl"
        set valid_to = current_timestamp
        where {pk_column} = NEW.{pk_column}
          and valid_to = ores.utility_infinity_timestamp_fn()
          and valid_from < current_timestamp;
    else
        -- This is a new record
        NEW.version = 1;
    end if;

    NEW.valid_from = current_timestamp;
    NEW.valid_to = ores.utility_infinity_timestamp_fn();

    if NEW.modified_by is null or NEW.modified_by = '' then
        NEW.modified_by = current_user;
    end if;

    -- Validate change_reason_code (raises exception if invalid)
    NEW.change_reason_code := ores.refdata_validate_change_reason_fn(NEW.change_reason_code);

    return NEW;
end;
$$ language plpgsql;

create or replace trigger {component}_{entity}_insert_trg
before insert on "ores"."{component}_{entity}_tbl"
for each row execute function ores.{component}_{entity}_insert_fn();

create or replace rule {component}_{entity}_delete_rule as
on delete to "ores"."{component}_{entity}_tbl" do instead
    update "ores"."{component}_{entity}_tbl"
    set valid_to = current_timestamp
    where {pk_column} = OLD.{pk_column}
      and valid_to = ores.utility_infinity_timestamp_fn();

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

create or replace function ores.{component}_{entity}_notify_fn()
returns trigger as $$
declare
    notification_payload jsonb;
    entity_name text := 'ores.{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 ores.{component}_{entity}_tbl
for each row execute function ores.{component}_{entity}_notify_fn();

set schema 'ores';

drop trigger if exists {component}_{entity}_insert_trg on "ores"."{component}_{entity}_tbl";
drop rule if exists {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";

set schema 'ores';

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

set schema 'ores';

-- Helper function for idempotent inserts
create or replace function ores.upsert_{entity}(
    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.upsert_{entity}('value1', 'value2');
select ores.upsert_{entity}('value3', 'value4');

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

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

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

    // ... fields matching SQL columns
};

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

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

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

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

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

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

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

select * from ores.{component}_{entity}_tbl
where valid_to = ores.utility_infinity_timestamp_fn();

"image_id" uuid,  -- Optional FK to assets_images_tbl

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