ores.sql.schema.domain_entity_create

{{! GENERATED FILE — tangled from projects/ores.codegen/library/templates/sql_schema.org. Edit the org source. }}
{{! Template to generate SQL schema for domain entity table with UUID primary key }}
{{{sql_license}}}
/**
 * AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Template: sql_schema_domain_entity_create.mustache
 * To modify, update the template and regenerate.
 *
 * {{domain_entity.entity_title}} Table
 *
 * {{{domain_entity.description_formatted}}}
 */

create table if not exists "{{domain_entity.sql_name_base}}_tbl" (
{{#domain_entity.primary_key.columns}}
    "{{column}}" {{type}} not null,
{{/domain_entity.primary_key.columns}}
{{#domain_entity.has_tenant_id}}
{{#domain_entity.sql.system_scope}}
    "tenant_id" uuid not null default ores_utility_system_tenant_id_fn(),
{{/domain_entity.sql.system_scope}}
{{#domain_entity.sql.nullable_tenant_id}}
    "tenant_id" uuid,
{{/domain_entity.sql.nullable_tenant_id}}
{{^domain_entity.sql.system_scope}}
{{^domain_entity.sql.nullable_tenant_id}}
    "tenant_id" uuid not null,
{{/domain_entity.sql.nullable_tenant_id}}
{{/domain_entity.sql.system_scope}}
{{/domain_entity.has_tenant_id}}
{{#domain_entity.has_audit_columns}}
    "version" integer not null,
{{/domain_entity.has_audit_columns}}
{{#domain_entity.natural_keys}}
{{#nullable}}
    "{{column}}" {{type}} null,
{{/nullable}}
{{^nullable}}
    "{{column}}" {{type}} not null,
{{/nullable}}
{{/domain_entity.natural_keys}}
{{#domain_entity.columns}}
{{^is_image_id}}
    "{{name}}" {{type}}{{#nullable}} null{{/nullable}}{{^nullable}} not null{{/nullable}}{{#default}} default {{{default}}}{{/default}},
{{/is_image_id}}
{{/domain_entity.columns}}
{{#domain_entity.has_coding_scheme}}
    "coding_scheme_code" text not null,
{{/domain_entity.has_coding_scheme}}
{{#domain_entity.has_nullable_coding_scheme}}
    "coding_scheme_code" text,
{{/domain_entity.has_nullable_coding_scheme}}
{{#domain_entity.has_image_id}}
    "image_id" uuid,
{{/domain_entity.has_image_id}}
{{#domain_entity.has_workspace_id}}
    "workspace_id" uuid not null default ores_utility_live_workspace_id_fn(), -- soft FK to ores_workspaces_tbl(id)
{{/domain_entity.has_workspace_id}}
{{#domain_entity.has_audit_columns}}
    "modified_by" text not null,
    "performed_by" text not null,
    "change_reason_code" text not null,
    "change_commentary" text not null,
{{/domain_entity.has_audit_columns}}
    "valid_from" timestamp with time zone not null,
    "valid_to" timestamp with time zone not null,
{{#domain_entity.has_tenant_in_pk}}
    primary key (tenant_id, {{domain_entity.primary_key.column_list}}, valid_from, valid_to),
{{#domain_entity.has_gist_exclusion}}
    exclude using gist (
        tenant_id WITH =,
        {{{domain_entity.primary_key.gist_column_clause}}}
        tstzrange(valid_from, valid_to) WITH &&
    ),
{{/domain_entity.has_gist_exclusion}}
{{/domain_entity.has_tenant_in_pk}}
{{^domain_entity.has_tenant_in_pk}}
{{#domain_entity.sql.hypertable}}
    primary key ({{domain_entity.primary_key.column_list}}, {{domain_entity.sql.hypertable_partition_column}}),
{{/domain_entity.sql.hypertable}}
{{^domain_entity.sql.hypertable}}
    primary key ({{domain_entity.primary_key.column_list}}, valid_from, valid_to),
{{#domain_entity.has_gist_exclusion}}
    exclude using gist (
        {{{domain_entity.primary_key.gist_column_clause}}}
        tstzrange(valid_from, valid_to) WITH &&
    ),
{{/domain_entity.has_gist_exclusion}}
{{/domain_entity.sql.hypertable}}
{{/domain_entity.has_tenant_in_pk}}
    check ("valid_from" < "valid_to"){{#domain_entity.primary_key.columns}}{{#is_uuid}}{{^skip_uuid_check}},
    check ("{{column}}" <> {{uuid_check_fn}}){{/skip_uuid_check}}{{/is_uuid}}{{#is_text}},
    check ("{{column}}" <> ''){{/is_text}}{{/domain_entity.primary_key.columns}}{{#domain_entity.sql.extra_checks}},
    check ({{{.}}}){{/domain_entity.sql.extra_checks}}
);
{{#domain_entity.has_multiple_natural_keys}}
{{^domain_entity.sql.suppress_nk_index}}

-- Composite natural key: unique combination for active records
{{#domain_entity.has_tenant_id}}
create unique index if not exists {{domain_entity.index_name_prefix}}_{{domain_entity.natural_keys_composite_name}}_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" (tenant_id, {{domain_entity.natural_keys_composite_columns}})
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.has_tenant_id}}
{{^domain_entity.has_tenant_id}}
create unique index if not exists {{domain_entity.index_name_prefix}}_{{domain_entity.natural_keys_composite_name}}_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" ({{domain_entity.natural_keys_composite_columns}})
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.has_tenant_id}}
{{/domain_entity.sql.suppress_nk_index}}
{{/domain_entity.has_multiple_natural_keys}}
{{^domain_entity.has_multiple_natural_keys}}
{{#domain_entity.natural_keys}}

-- Unique {{column}} for active records
{{#domain_entity.has_tenant_in_pk}}
create unique index if not exists {{domain_entity.index_name_prefix}}_{{column}}_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" (tenant_id{{#prefix_columns}}, {{prefix_columns}}{{/prefix_columns}}, {{column}})
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.has_tenant_in_pk}}
{{^domain_entity.has_tenant_in_pk}}
create unique index if not exists {{domain_entity.index_name_prefix}}_{{column}}_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" ({{column}})
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.has_tenant_in_pk}}
{{/domain_entity.natural_keys}}
{{/domain_entity.has_multiple_natural_keys}}

{{#domain_entity.has_audit_columns}}
-- Version uniqueness for optimistic concurrency
{{/domain_entity.has_audit_columns}}
{{#domain_entity.has_tenant_in_pk}}
{{#domain_entity.has_audit_columns}}
create unique index if not exists {{domain_entity.index_name_prefix}}_version_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" (tenant_id, {{domain_entity.primary_key.column_list}}, version)
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.has_audit_columns}}

{{#domain_entity.primary_key.is_uuid}}
create unique index if not exists {{domain_entity.index_name_prefix}}_id_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" (tenant_id, {{domain_entity.primary_key.column_list}})
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.primary_key.is_uuid}}
{{^domain_entity.primary_key.is_uuid}}
create unique index if not exists {{domain_entity.index_name_prefix}}_{{domain_entity.primary_key.index_suffix}}_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" (tenant_id, {{domain_entity.primary_key.column_list}})
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.primary_key.is_uuid}}

create index if not exists {{domain_entity.index_name_prefix}}_tenant_idx
on "{{domain_entity.sql_name_base}}_tbl" (tenant_id)
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.has_tenant_in_pk}}
{{^domain_entity.has_tenant_in_pk}}
{{#domain_entity.has_audit_columns}}
create unique index if not exists {{domain_entity.index_name_prefix}}_version_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" ({{domain_entity.primary_key.column_list}}, version)
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.has_audit_columns}}

{{#domain_entity.sql.nullable_tenant_id}}
create unique index if not exists {{domain_entity.index_name_prefix}}_id_uniq_idx
on "{{domain_entity.sql_name_base}}_tbl" ({{domain_entity.primary_key.column_list}})
where valid_to = ores_utility_infinity_timestamp_fn();

create index if not exists {{domain_entity.index_name_prefix}}_tenant_idx
on "{{domain_entity.sql_name_base}}_tbl" (tenant_id)
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.sql.nullable_tenant_id}}
{{/domain_entity.has_tenant_in_pk}}
{{#domain_entity.indexes}}

create{{#unique}} unique{{/unique}} index if not exists {{domain_entity.index_name_prefix}}_{{name}}_idx
on "{{domain_entity.sql_name_base}}_tbl" ({{{columns}}}){{#current_only}}
where valid_to = ores_utility_infinity_timestamp_fn(){{#where_extra}}
  and {{{where_extra}}}{{/where_extra}}{{/current_only}}{{^current_only}}{{#where_extra}}
where {{{where_extra}}}{{/where_extra}}{{/current_only}};
{{/domain_entity.indexes}}
{{#domain_entity.has_workspace_id}}

create index if not exists {{domain_entity.index_name_prefix}}_workspace_idx
on "{{domain_entity.sql_name_base}}_tbl" (workspace_id)
where valid_to = ores_utility_infinity_timestamp_fn();
{{/domain_entity.has_workspace_id}}

{{#domain_entity.has_tenant_id}}
{{#domain_entity.generate_touch_function}}

-- =============================================================================
-- Touch-version function for {{domain_entity.entity_singular}}
-- Bumps this entity's version without changing any of its own columns,
-- called by a child entity's insert trigger / delete rule when that
-- child is flagged :bump_parent_version: true against this entity. See
-- the "Temporal composite entity versioning" architecture doc.
--
-- Deliberately does NOT duplicate the version/valid_from/valid_to
-- management here: it fetches the current row, resets version to the
-- 0 sentinel, and re-inserts — the table's own insert trigger (below)
-- then performs the exact same close-current/bump-version dance a
-- normal application save does. This also sidesteps a real footgun:
-- current_timestamp is frozen for the whole transaction, so a
-- same-transaction bulk import (parent + child created together, as
-- GLEIF provisioning does) would otherwise make the just-inserted
-- parent row's own valid_from collide with this function's close
-- timestamp.
--
-- Defined before the insert trigger/delete rule below (not after):
-- for a self-referencing entity (this entity is its own composite
-- parent, e.g. a portfolio tree), the delete rule calling this
-- function lives in the *same* file. PostgreSQL parses CREATE RULE
-- eagerly, so the function must already exist by then -- unlike a
-- PL/pgSQL function body (e.g. the insert trigger function), which is
-- opaque at CREATE time and only resolves calls at execution.
--
-- p_reason_code is passed through as-is (already a validated code from
-- the child's own row); p_child_entity distinguishes which child
-- triggered the bump in the free-text commentary.
-- =============================================================================
create or replace function {{domain_entity.sql_name_base}}_touch_version_fn(
    p_tenant_id uuid,
    p_id {{domain_entity.primary_key.type}},
    p_reason_code text,
    p_commentary text,
    p_modified_by text,
    p_performed_by text,
    p_child_entity text
) returns void as $$
declare
    rec {{domain_entity.sql_name_base}}_tbl%rowtype;
begin
    -- for update: takes the same row lock the parent's own insert
    -- trigger takes, so the snapshot in rec can't be based on a
    -- business-column value a concurrent direct edit is about to
    -- change — without this, that concurrent edit could be silently
    -- reverted once this function's later insert proceeds. See the
    -- "Temporal composite entity versioning" architecture doc,
    -- Concurrency section.
    select * into rec
    from "{{domain_entity.sql_name_base}}_tbl"
    where tenant_id = p_tenant_id
      and {{domain_entity.primary_key.column}} = p_id
      and valid_to = ores_utility_infinity_timestamp_fn()
    for update;

    if not found then
        return;
    end if;

    rec.version := 0;
    rec.modified_by := p_modified_by;
    rec.performed_by := p_performed_by;
    rec.change_reason_code := p_reason_code;
    rec.change_commentary := format('Bumped by child %s: %s', p_child_entity, coalesce(p_commentary, ''));

    insert into "{{domain_entity.sql_name_base}}_tbl"
    select (rec).*;
end;
$$ language plpgsql security definer set search_path = public, pg_temp;

{{/domain_entity.generate_touch_function}}
{{/domain_entity.has_tenant_id}}
{{#domain_entity.sql.bitemporal_soft_update}}
create or replace function {{domain_entity.sql_name_base}}_insert_fn()
returns trigger as $$
begin
    new.tenant_id := ores_iam_validate_tenant_fn(new.tenant_id);

    update "{{domain_entity.sql_name_base}}_tbl"
    set valid_to = current_timestamp
    where tenant_id = new.tenant_id
{{#domain_entity.sql.bitemporal_natural_keys}}
{{#is_nullable}}
      and coalesce({{column}}, '') = coalesce(new.{{column}}, '')
{{/is_nullable}}
{{^is_nullable}}
      and {{column}} = new.{{column}}
{{/is_nullable}}
{{/domain_entity.sql.bitemporal_natural_keys}}
      and valid_to = ores_utility_infinity_timestamp_fn()
      and valid_from < current_timestamp;

    new.valid_from := current_timestamp;
    new.valid_to   := ores_utility_infinity_timestamp_fn();
    return new;
end;
$$ language plpgsql security definer set search_path = public, pg_temp;

create or replace trigger {{domain_entity.sql_name_base}}_insert_trg
before insert on "{{domain_entity.sql_name_base}}_tbl"
for each row execute function {{domain_entity.sql_name_base}}_insert_fn();

create or replace function {{domain_entity.sql_name_base}}_delete_fn()
returns trigger as $$
begin
    update "{{domain_entity.sql_name_base}}_tbl"
    set valid_to = current_timestamp
    where tenant_id = old.tenant_id
{{#domain_entity.sql.bitemporal_natural_keys}}
{{#is_nullable}}
      and coalesce({{column}}, '') = coalesce(old.{{column}}, '')
{{/is_nullable}}
{{^is_nullable}}
      and {{column}} = old.{{column}}
{{/is_nullable}}
{{/domain_entity.sql.bitemporal_natural_keys}}
      and valid_to = ores_utility_infinity_timestamp_fn();
    return null;
end;
$$ language plpgsql security definer set search_path = public, pg_temp;

create or replace trigger {{domain_entity.sql_name_base}}_delete_trg
before delete on "{{domain_entity.sql_name_base}}_tbl"
for each row execute function {{domain_entity.sql_name_base}}_delete_fn();
{{/domain_entity.sql.bitemporal_soft_update}}
{{^domain_entity.sql.bitemporal_soft_update}}
create or replace function {{domain_entity.sql_name_base}}_insert_fn()
returns trigger as $$
declare
{{#domain_entity.has_audit_columns}}
    current_version integer;
{{/domain_entity.has_audit_columns}}
{{#domain_entity.sql.party_id_from_book_id}}
    v_book_portfolio_id uuid;
{{/domain_entity.sql.party_id_from_book_id}}
{{#domain_entity.sql.fk_copy_validations}}
{{#declare_vars}}
    {{name}} {{type}};
{{/declare_vars}}
{{/domain_entity.sql.fk_copy_validations}}
{{#domain_entity.sql.has_cardinality_limit_validations}}
    v_max_cardinality integer;
    v_current_count integer;
{{/domain_entity.sql.has_cardinality_limit_validations}}
begin
{{#domain_entity.has_tenant_in_pk}}
    -- Validate tenant_id
    NEW.tenant_id := ores_iam_validate_tenant_fn(NEW.tenant_id);

{{/domain_entity.has_tenant_in_pk}}
{{#domain_entity.has_workspace_id}}
    -- Validate workspace_id
    NEW.workspace_id := ores_workspace_validate_fn(NEW.workspace_id);

{{/domain_entity.has_workspace_id}}
{{#domain_entity.sql.nullable_tenant_id}}
    -- Validate tenant_id only when set (system records have NULL tenant_id)
    if NEW.tenant_id is not null then
        NEW.tenant_id := ores_iam_validate_tenant_fn(NEW.tenant_id);
    end if;

{{/domain_entity.sql.nullable_tenant_id}}
{{#domain_entity.sql.system_scope}}
    -- All {{domain_entity.entity_plural}} belong to the system tenant
    NEW.tenant_id := ores_utility_system_tenant_id_fn();

{{/domain_entity.sql.system_scope}}
{{#domain_entity.has_any_coding_scheme}}
    -- Validate foreign key references
    if NEW.coding_scheme_code is not null and not exists (
        select 1 from ores_dq_coding_schemes_tbl
        where code = NEW.coding_scheme_code
        and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception 'Invalid coding_scheme_code: %. Coding scheme must exist.', NEW.coding_scheme_code
        using errcode = '23503';
    end if;

{{/domain_entity.has_any_coding_scheme}}
{{#domain_entity.sql.party_id_from_session}}
    -- Set party_id from session context
    NEW.party_id := current_setting('app.current_party_id')::uuid;

{{/domain_entity.sql.party_id_from_session}}
{{#domain_entity.sql.party_id_from_book_id}}
    -- Validate book_id (soft FK to {{book_table}})
    if not exists (
        select 1 from {{book_table}}
        where tenant_id = NEW.tenant_id
          and id = NEW.book_id
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception '{{book_error_message}}', NEW.book_id
            using errcode = '23503';
    end if;

    -- Denormalise party_id and capture parent_portfolio_id from book
    select party_id, parent_portfolio_id
      into NEW.party_id, v_book_portfolio_id
    from {{book_table}}
    where id = NEW.book_id
      and tenant_id = NEW.tenant_id
      and valid_to = ores_utility_infinity_timestamp_fn();

    -- Validate portfolio_id (soft FK to {{portfolio_table}})
    if not exists (
        select 1 from {{portfolio_table}}
        where tenant_id = NEW.tenant_id
          and id = NEW.portfolio_id
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception '{{portfolio_error_message}}', NEW.portfolio_id
            using errcode = '23503';
    end if;

    -- Validate that portfolio_id matches the book's parent portfolio
    if NEW.portfolio_id != v_book_portfolio_id then
        raise exception '{{cross_check_error_message}}',
            NEW.portfolio_id, v_book_portfolio_id
            using errcode = '23514';
    end if;

{{/domain_entity.sql.party_id_from_book_id}}
{{#domain_entity.foreign_keys}}
{{^skip_check}}
{{^nullable}}
    -- Validate {{column}} (soft FK to {{table}})
    if not exists (
        select 1 from {{table}}
        where {{^use_no_tenant}}{{^use_system_tenant}}tenant_id = NEW.tenant_id
          and {{/use_system_tenant}}{{#use_system_tenant}}tenant_id = ores_utility_system_tenant_id_fn()
          and {{/use_system_tenant}}{{/use_no_tenant}}{{target_column}} = NEW.{{column}}
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception '{{error_message}}', NEW.{{column}}
            using errcode = '23503';
    end if;

{{/nullable}}
{{#nullable}}
    -- Validate {{column}} (optional soft FK to {{table}})
    if NEW.{{column}} is not null then
        if not exists (
            select 1 from {{table}}
            where {{^use_no_tenant}}{{^use_system_tenant}}tenant_id = NEW.tenant_id
              and {{/use_system_tenant}}{{#use_system_tenant}}tenant_id = ores_utility_system_tenant_id_fn()
              and {{/use_system_tenant}}{{/use_no_tenant}}{{target_column}} = NEW.{{column}}
              and valid_to = ores_utility_infinity_timestamp_fn()
        ) then
            raise exception '{{error_message}}', NEW.{{column}}
                using errcode = '23503';
        end if;
{{#prevent_cycle}}

        -- Reject a {{column}} pointing back at NEW's own row, directly or
        -- transitively, since the touch-version mechanism re-fires this
        -- same trigger walking up {{column}} -- an undetected cycle would
        -- recurse without bound instead of failing cleanly.
        if exists (
            with recursive ancestor_chain as (
                select id, {{column}} as parent_id
                from {{table}}
                where id = NEW.{{column}}
                  and valid_to = ores_utility_infinity_timestamp_fn()
                union all
                select t.id, t.{{column}} as parent_id
                from {{table}} t
                join ancestor_chain a on t.id = a.parent_id
                where t.valid_to = ores_utility_infinity_timestamp_fn()
            )
            select 1 from ancestor_chain where id = NEW.{{domain_entity.primary_key.column}}
        ) then
            raise exception 'Invalid {{column}}: % would create a cycle in the {{table}} hierarchy.', NEW.{{column}}
                using errcode = '23514';
        end if;
{{/prevent_cycle}}
    end if;

{{/nullable}}
{{/skip_check}}
{{/domain_entity.foreign_keys}}
{{#domain_entity.sql.fk_copy_validations}}
    -- Validate {{column}}; copy {{select_fields}} from {{table}} if not provided
    select {{select_fields}}
      into {{into_vars}}
    from {{table}}
    where tenant_id = NEW.tenant_id
      and id = NEW.{{column}}
      and valid_to = ores_utility_infinity_timestamp_fn();

    if not found then
        raise exception '{{error_message}}',
            NEW.{{column}}
            using errcode = '23503';
    end if;

{{#copy_empty}}
    if NEW.{{target}} = '' then
{{#coalesce}}
        NEW.{{target}} := coalesce({{source}}, '');
{{/coalesce}}
{{^coalesce}}
        NEW.{{target}} := {{source}};
{{/coalesce}}
    end if;
{{/copy_empty}}
{{/domain_entity.sql.fk_copy_validations}}
{{#domain_entity.sql.text_code_validations}}
    -- Validate {{column}} FK
    if not exists (
        select 1 from {{table}}
        where {{lookup_column}} = NEW.{{column}}
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception '{{error_message}}',
            NEW.{{column}} using errcode = '23503';
    end if;

{{/domain_entity.sql.text_code_validations}}
{{#domain_entity.insert_trigger.validations}}
{{#nullable}}
    -- Validate {{column}} (optional field -- skip validation when null)
    if NEW.{{column}} is not null then
        NEW.{{column}} := {{validation_function}}({{#domain_entity.has_tenant_id}}NEW.tenant_id, {{/domain_entity.has_tenant_id}}NEW.{{column}});
    end if;

{{/nullable}}
{{^nullable}}
    -- Validate {{column}}
    NEW.{{column}} := {{validation_function}}({{#domain_entity.has_tenant_id}}NEW.tenant_id, {{/domain_entity.has_tenant_id}}NEW.{{column}});

{{/nullable}}
{{#cardinality_limit_table}}
    -- Validate cardinality limit for this {{column}}
    select {{cardinality_limit_column}} into v_max_cardinality
    from {{cardinality_limit_table}}
    where {{#domain_entity.has_tenant_id}}tenant_id = NEW.tenant_id
      and {{/domain_entity.has_tenant_id}}code = NEW.{{column}}
      and valid_to = ores_utility_infinity_timestamp_fn();

    if v_max_cardinality is not null then
        select count(*) into v_current_count
        from "{{domain_entity.sql_name_base}}_tbl"
        where {{#domain_entity.has_tenant_id}}tenant_id = NEW.tenant_id
          and {{/domain_entity.has_tenant_id}}{{cardinality_scope_column}} = NEW.{{cardinality_scope_column}}
          and {{column}} = NEW.{{column}}
          and {{domain_entity.primary_key.column}} != NEW.{{domain_entity.primary_key.column}}
          and valid_to = ores_utility_infinity_timestamp_fn();

        if v_current_count >= v_max_cardinality then
            raise exception 'Cardinality violation for {{column}} %: % already has % {{cardinality_noun}}(s) (max %).',
                NEW.{{column}}, NEW.{{cardinality_scope_column}}, v_current_count, v_max_cardinality
                using errcode = '23514';
        end if;
    end if;

{{/cardinality_limit_table}}
{{/domain_entity.insert_trigger.validations}}
<<paste:d4e8e136-828d-440a-ba59-82ba2b9d3e2e>>
{{#domain_entity.has_audit_columns}}
{{^domain_entity.change_reason_code_declared}}
{{#domain_entity.has_tenant_in_pk}}
    -- Validate change_reason_code
    NEW.change_reason_code := ores_dq_validate_change_reason_fn(NEW.tenant_id, NEW.change_reason_code);

{{/domain_entity.has_tenant_in_pk}}
{{#domain_entity.sql.nullable_tenant_id}}
    -- Validate change_reason_code
    NEW.change_reason_code := ores_dq_validate_change_reason_fn(NEW.tenant_id, NEW.change_reason_code);

{{/domain_entity.sql.nullable_tenant_id}}
{{#domain_entity.sql.system_scope}}
    -- Validate change_reason_code (use system tenant for {{domain_entity.entity_plural}} records)
    NEW.change_reason_code := ores_dq_validate_change_reason_fn(ores_utility_system_tenant_id_fn(), NEW.change_reason_code);

{{/domain_entity.sql.system_scope}}
{{^domain_entity.has_tenant_id}}
    -- Validate change_reason_code
    NEW.change_reason_code := ores_dq_validate_change_reason_fn(NEW.change_reason_code);

{{/domain_entity.has_tenant_id}}
{{/domain_entity.change_reason_code_declared}}
{{/domain_entity.has_audit_columns}}
{{#domain_entity.foreign_keys}}
{{#bump_parent_version}}
    -- Bump {{table}}'s version alongside this write (composite entity
    -- versioning — see the "Temporal composite entity versioning"
    -- architecture doc). The touch function re-validates modified_by
    -- itself; change_reason_code is passed through as-is since it was
    -- already validated above.
    perform {{touch_function}}(
        NEW.tenant_id,
        NEW.{{column}},
        NEW.change_reason_code,
        NEW.change_commentary,
        NEW.modified_by,
        NEW.performed_by,
        '{{domain_entity.entity_singular}}'
    );

{{/bump_parent_version}}
{{/domain_entity.foreign_keys}}
{{#domain_entity.has_audit_columns}}
    -- Version management
    select version into current_version
    from "{{domain_entity.sql_name_base}}_tbl"
    where {{#domain_entity.has_tenant_in_pk}}tenant_id = NEW.tenant_id
      and {{/domain_entity.has_tenant_in_pk}}{{#domain_entity.sql.nullable_tenant_id}}(tenant_id = NEW.tenant_id or (tenant_id is null and NEW.tenant_id is null))
      and {{/domain_entity.sql.nullable_tenant_id}}{{{domain_entity.primary_key.where_new}}}
      and valid_to = ores_utility_infinity_timestamp_fn()
    for update;

    if found then
        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;
<<paste:59F4313F-B119-44D8-8E4E-05AE8B18DE95>>
        -- clock_timestamp(), not current_timestamp: current_timestamp is
        -- frozen for the whole transaction, so a same-transaction
        -- multi-write to this row (e.g. a composite entity's parent
        -- touched twice by two different children in one transaction)
        -- would collide with itself. clock_timestamp() always advances.
        update "{{domain_entity.sql_name_base}}_tbl"
        set valid_to = clock_timestamp()
        where {{#domain_entity.has_tenant_in_pk}}tenant_id = NEW.tenant_id
          and {{/domain_entity.has_tenant_in_pk}}{{#domain_entity.sql.nullable_tenant_id}}(tenant_id = NEW.tenant_id or (tenant_id is null and NEW.tenant_id is null))
          and {{/domain_entity.sql.nullable_tenant_id}}{{{domain_entity.primary_key.where_new}}}
          and valid_to = ores_utility_infinity_timestamp_fn()
          and valid_from < clock_timestamp();
    else
        NEW.version = 1;
<<paste:8697C751-ECD5-4501-AF06-77AD9A8985AB>>
    end if;
{{/domain_entity.has_audit_columns}}
{{^domain_entity.has_audit_columns}}
    -- Close the current active row before inserting the new one
    update "{{domain_entity.sql_name_base}}_tbl"
    set valid_to = clock_timestamp()
    where {{{domain_entity.primary_key.where_new}}}
      and valid_to = ores_utility_infinity_timestamp_fn()
      and valid_from < clock_timestamp();
{{/domain_entity.has_audit_columns}}

    NEW.valid_from = clock_timestamp();
    NEW.valid_to = ores_utility_infinity_timestamp_fn();
{{#domain_entity.has_audit_columns}}
    NEW.modified_by := ores_iam_validate_account_username_fn(NEW.modified_by);
    NEW.performed_by = coalesce(ores_iam_current_service_fn(), current_user);
{{/domain_entity.has_audit_columns}}

    return NEW;
end;
$$ language plpgsql security definer set search_path = public, pg_temp;

create or replace trigger {{domain_entity.sql_name_base}}_insert_trg
before insert on "{{domain_entity.sql_name_base}}_tbl"
for each row execute function {{domain_entity.sql_name_base}}_insert_fn();

create or replace rule {{domain_entity.sql_name_base}}_delete_rule as
on delete to "{{domain_entity.sql_name_base}}_tbl" do instead (
    update "{{domain_entity.sql_name_base}}_tbl"
    set valid_to = clock_timestamp(){{#domain_entity.sql.extra_delete_sets}},
        {{{.}}}{{/domain_entity.sql.extra_delete_sets}}
    where {{#domain_entity.has_tenant_in_pk}}tenant_id = OLD.tenant_id
      and {{/domain_entity.has_tenant_in_pk}}{{#domain_entity.sql.nullable_tenant_id}}(tenant_id = OLD.tenant_id or (tenant_id is null and OLD.tenant_id is null))
      and {{/domain_entity.sql.nullable_tenant_id}}{{{domain_entity.primary_key.where_old}}}
      and valid_to = ores_utility_infinity_timestamp_fn();
{{#domain_entity.foreign_keys}}
{{#bump_parent_version}}
    -- Bump {{table}}'s version on delete too (composite entity
    -- versioning), symmetric with the insert-side call above.
    select {{touch_function}}(
        OLD.tenant_id,
        OLD.{{column}},
        OLD.change_reason_code,
        OLD.change_commentary,
        OLD.modified_by,
        OLD.performed_by,
        '{{domain_entity.entity_singular}}'
    );
{{/bump_parent_version}}
{{/domain_entity.foreign_keys}}
);
{{/domain_entity.sql.bitemporal_soft_update}}
{{#domain_entity.has_tenant_id}}
{{#domain_entity.validation_fn}}
{{^domain_entity.primary_key.is_compound}}

-- =============================================================================
-- Validation function for {{domain_entity.entity_singular}}
-- Validates that a {{domain_entity.primary_key.column}} exists in the {{domain_entity.entity_plural}} table.
-- Returns the validated value, or default if null/empty.
{{#domain_entity.validation_fn.scope_system}}
-- Uses system tenant data (shared reference data).
{{/domain_entity.validation_fn.scope_system}}
{{#domain_entity.validation_fn.scope_both}}
-- Validates against both the tenant's own data and the system tenant's canonical set.
{{/domain_entity.validation_fn.scope_both}}
{{#domain_entity.validation_fn.scope_tenant}}
-- Validates against the tenant's own data only.
{{/domain_entity.validation_fn.scope_tenant}}
-- =============================================================================
create or replace function {{domain_entity.product}}_{{domain_entity.component}}_validate_{{domain_entity.entity_singular}}_fn(
    p_tenant_id uuid,
    p_value {{domain_entity.primary_key.type}}
) returns {{domain_entity.primary_key.type}} as $$
begin
    -- Return default if null or empty
    if p_value is null{{#domain_entity.primary_key.is_text}} or p_value = ''{{/domain_entity.primary_key.is_text}} then
{{#domain_entity.validation_fn.default}}
{{#domain_entity.primary_key.is_text}}
        return '{{domain_entity.validation_fn.default}}';
{{/domain_entity.primary_key.is_text}}
{{^domain_entity.primary_key.is_text}}
        return {{domain_entity.validation_fn.default}};
{{/domain_entity.primary_key.is_text}}
{{/domain_entity.validation_fn.default}}
{{^domain_entity.validation_fn.default}}
        raise exception 'Invalid {{domain_entity.entity_singular}}: value cannot be null{{#domain_entity.primary_key.is_text}} or empty{{/domain_entity.primary_key.is_text}}'
            using errcode = '23502';
{{/domain_entity.validation_fn.default}}
    end if;

{{#domain_entity.validation_fn.scope_system}}
    -- Allow pass-through during bootstrap (no active rows for system tenant).
    if not exists (
        select 1 from {{domain_entity.sql_name_base}}_tbl
        where tenant_id = ores_utility_system_tenant_id_fn()
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        return p_value;
    end if;

    -- Validate against reference data
    if not exists (
        select 1 from {{domain_entity.sql_name_base}}_tbl
        where tenant_id = ores_utility_system_tenant_id_fn()
          and {{domain_entity.primary_key.column}} = p_value
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception 'Invalid {{domain_entity.entity_singular}}: %. Must be one of: %', p_value, (
            select string_agg({{domain_entity.primary_key.column}}::text, ', ' order by {{domain_entity.validation_fn.order_by}})
            from {{domain_entity.sql_name_base}}_tbl
            where tenant_id = ores_utility_system_tenant_id_fn()
              and valid_to = ores_utility_infinity_timestamp_fn()
        ) using errcode = '23503';
    end if;
{{/domain_entity.validation_fn.scope_system}}
{{#domain_entity.validation_fn.scope_both}}
    -- Allow pass-through if neither this tenant nor the system tenant has
    -- seeded active {{domain_entity.entity_plural}} yet (freshly provisioned tenant).
    if not exists (
        select 1 from {{domain_entity.sql_name_base}}_tbl
        where tenant_id in (p_tenant_id, ores_utility_system_tenant_id_fn())
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        return p_value;
    end if;

    -- Validate against this tenant's values and the system tenant's canonical set.
    if not exists (
        select 1 from {{domain_entity.sql_name_base}}_tbl
        where tenant_id in (p_tenant_id, ores_utility_system_tenant_id_fn())
          and {{domain_entity.primary_key.column}} = p_value
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception 'Invalid {{domain_entity.entity_singular}}: %. Must be one of: %', p_value, (
            select string_agg({{domain_entity.primary_key.column}}::text, ', ' order by {{domain_entity.validation_fn.order_by}})
            from {{domain_entity.sql_name_base}}_tbl
            where tenant_id in (p_tenant_id, ores_utility_system_tenant_id_fn())
              and valid_to = ores_utility_infinity_timestamp_fn()
        ) using errcode = '23503';
    end if;
{{/domain_entity.validation_fn.scope_both}}
{{#domain_entity.validation_fn.scope_tenant}}
    -- Allow pass-through if this tenant has no active {{domain_entity.entity_plural}} yet.
    if not exists (
        select 1 from {{domain_entity.sql_name_base}}_tbl
        where tenant_id = p_tenant_id
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        return p_value;
    end if;

    -- Validate against this tenant's values.
    if not exists (
        select 1 from {{domain_entity.sql_name_base}}_tbl
        where tenant_id = p_tenant_id
          and {{domain_entity.primary_key.column}} = p_value
          and valid_to = ores_utility_infinity_timestamp_fn()
    ) then
        raise exception 'Invalid {{domain_entity.entity_singular}}: %. Must be one of: %', p_value, (
            select string_agg({{domain_entity.primary_key.column}}::text, ', ' order by {{domain_entity.validation_fn.order_by}})
            from {{domain_entity.sql_name_base}}_tbl
            where tenant_id = p_tenant_id
              and valid_to = ores_utility_infinity_timestamp_fn()
        ) using errcode = '23503';
    end if;
{{/domain_entity.validation_fn.scope_tenant}}

    return p_value;
end;
$$ language plpgsql security definer set search_path = public, pg_temp;
{{/domain_entity.primary_key.is_compound}}
{{/domain_entity.validation_fn}}
{{/domain_entity.has_tenant_id}}
{{#domain_entity.has_parent_id}}

-- =============================================================================
-- Hierarchy traversal for {{domain_entity.entity_plural}}.
-- Returns a flat set of {id, parent_id, name} nodes for the subtree rooted
-- at p_root_id. When p_from_root is true, first walks up parent_id to the
-- ultimate ancestor (parent_id is null) and recurses down from there instead,
-- returning the whole tenant tree the given node belongs to.
-- =============================================================================
create or replace function {{domain_entity.sql_name_base}}_hierarchy_fn(
    p_tenant_id uuid,
    p_root_id uuid,
    p_from_root boolean default false
) returns table(id uuid, parent_id uuid, name text) as $$
declare
    v_root_id uuid;
begin
    v_root_id := p_root_id;

    if p_from_root then
        with recursive ancestors as (
            select t.id, t.{{domain_entity.parent_id_column}} as parent_id
            from "{{domain_entity.sql_name_base}}_tbl" t
            where t.tenant_id = p_tenant_id
              and t.id = p_root_id
              and t.valid_to = ores_utility_infinity_timestamp_fn()
            union all
            select t.id, t.{{domain_entity.parent_id_column}} as parent_id
            from "{{domain_entity.sql_name_base}}_tbl" t
            join ancestors a on t.id = a.parent_id
            where t.tenant_id = p_tenant_id
              and t.valid_to = ores_utility_infinity_timestamp_fn()
        )
        select a.id into v_root_id
        from ancestors a
        where a.parent_id is null
        limit 1;

        if v_root_id is null then
            v_root_id := p_root_id;
        end if;
    end if;

    return query
    with recursive descendants as (
        select t.id, t.{{domain_entity.parent_id_column}} as parent_id,
               t.{{domain_entity.hierarchy_name_field}}::text as name
        from "{{domain_entity.sql_name_base}}_tbl" t
        where t.tenant_id = p_tenant_id
          and t.id = v_root_id
          and t.valid_to = ores_utility_infinity_timestamp_fn()
        union all
        select t.id, t.{{domain_entity.parent_id_column}} as parent_id,
               t.{{domain_entity.hierarchy_name_field}}::text as name
        from "{{domain_entity.sql_name_base}}_tbl" t
        join descendants d on t.{{domain_entity.parent_id_column}} = d.id
        where t.tenant_id = p_tenant_id
          and t.valid_to = ores_utility_infinity_timestamp_fn()
    )
    select d.id, d.parent_id, d.name from descendants d;
end;
$$ language plpgsql stable security definer set search_path = public, pg_temp;
{{/domain_entity.has_parent_id}}
{{#domain_entity.sql.hypertable}}
do $$
declare
    tsdb_installed boolean;
begin
    select exists (
        select 1 from pg_extension where extname = 'timescaledb'
    ) into tsdb_installed;

    if tsdb_installed then
        raise notice 'TimescaleDB detected - creating hypertable ({{domain_entity.sql.hypertable_chunk_interval}} chunks)';

        perform public.create_hypertable(
            '"{{domain_entity.sql_name_base}}_tbl"',
            '{{domain_entity.sql.hypertable_partition_column}}',
            chunk_time_interval => interval '{{domain_entity.sql.hypertable_chunk_interval}}',
            if_not_exists => true
        );
    else
        raise notice 'TimescaleDB not available - using regular table (manual cleanup required)';
    end if;
end $$;
{{/domain_entity.sql.hypertable}}
<<paste:92432102-d405-46fc-911a-111dfed27358>>