ores.sql.schema.junction_create

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

create table if not exists "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl" (
    "{{junction.left.column}}" {{junction.left.type}} not null,
{{#junction.has_tenant_id}}
    "tenant_id" uuid not null,
{{/junction.has_tenant_id}}
    "{{junction.right.column}}" {{junction.right.type}} not null,
    "version" integer not null,
{{#junction.columns}}
    "{{name}}" {{type}}{{#nullable}} null{{/nullable}}{{^nullable}} not null{{/nullable}}{{#default}} default {{{default}}}{{/default}},
{{/junction.columns}}
    "modified_by" text not null,
    "performed_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,
{{#junction.history}}
    "closed_by" text null,
{{/junction.history}}
{{#junction.has_tenant_id}}
    primary key (tenant_id, {{junction.left.column}}, {{junction.right.column}}, valid_from),
    exclude using gist (
        tenant_id WITH =,
        {{junction.left.column}} WITH =,
        {{junction.right.column}} WITH =,
        tstzrange(valid_from, valid_to) WITH &&
    ),
{{/junction.has_tenant_id}}
{{^junction.has_tenant_id}}
    primary key ({{junction.left.column}}, {{junction.right.column}}, valid_from),
    exclude using gist (
        {{junction.left.column}} WITH =,
        {{junction.right.column}} WITH =,
        tstzrange(valid_from, valid_to) WITH &&
    ),
{{/junction.has_tenant_id}}
    check ("valid_from" < "valid_to")
);

-- {{junction.left.index_comment}}
create index if not exists {{junction.name}}_{{junction.left.column_short}}_idx
on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl" ({{junction.left.column}})
where valid_to = ores_utility_infinity_timestamp_fn();

-- {{junction.right.index_comment}}
create index if not exists {{junction.name}}_{{junction.right.column_short}}_idx
on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl" ({{junction.right.column}})
where valid_to = ores_utility_infinity_timestamp_fn();

-- Unique constraint on active records for ON CONFLICT support
{{#junction.has_tenant_id}}
create unique index if not exists {{junction.name}}_uniq_idx
on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl" (tenant_id, {{junction.left.column}}, {{junction.right.column}})
where valid_to = ores_utility_infinity_timestamp_fn();

create index if not exists {{junction.name}}_tenant_idx
on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl" (tenant_id)
where valid_to = ores_utility_infinity_timestamp_fn();
{{/junction.has_tenant_id}}
{{^junction.has_tenant_id}}
create unique index if not exists {{junction.name}}_uniq_idx
on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl" ({{junction.left.column}}, {{junction.right.column}})
where valid_to = ores_utility_infinity_timestamp_fn();
{{/junction.has_tenant_id}}

create or replace function {{junction.product}}_{{junction.component}}_{{junction.name}}_insert_fn()
returns trigger as $$
declare
    current_version integer;
begin
{{#junction.has_tenant_id}}
    -- Validate tenant_id
    new.tenant_id := ores_iam_validate_tenant_fn(new.tenant_id);

{{/junction.has_tenant_id}}
    -- The actor is validated before the version management below: the
    -- validator accepts a username only while a current account row holds
    -- it, and a self write retires that row.
    new.modified_by := ores_iam_validate_account_username_fn(new.modified_by);

    -- Version management
    select version into current_version
    from "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl"
    where {{#junction.has_tenant_id}}tenant_id = new.tenant_id
    and {{/junction.has_tenant_id}}{{junction.left.column}} = new.{{junction.left.column}}
    and {{junction.right.column}} = new.{{junction.right.column}}
    and valid_to = ores_utility_infinity_timestamp_fn()
    for update;

    if found then
        if new.version != 0 and new.version != current_version then
            perform ores_outcome_raise_fn(
                'version_conflict',
                '{{junction.name}}',
                '{{junction.left.column}}',
                new.version::text,
                current_version::text);
        end if;
        new.version = current_version + 1;

        -- Close existing record.
        -- clock_timestamp(), not current_timestamp: current_timestamp is
        -- frozen for the whole transaction, so a same-transaction
        -- multi-write to this row (e.g. the same junction pair touched
        -- twice in one transaction) would collide with itself.
        -- clock_timestamp() always advances.
        update "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl"
        set valid_to = clock_timestamp()
        where {{#junction.has_tenant_id}}tenant_id = new.tenant_id
        and {{/junction.has_tenant_id}}{{junction.left.column}} = new.{{junction.left.column}}
        and {{junction.right.column}} = new.{{junction.right.column}}
        and valid_to = ores_utility_infinity_timestamp_fn()
        and valid_from < clock_timestamp();
    else
        new.version = 1;
    end if;

    new.valid_from = clock_timestamp();
    new.valid_to = ores_utility_infinity_timestamp_fn();

    new.performed_by = coalesce(ores_iam_current_service_fn(), current_user);

    new.change_reason_code := ores_dq_validate_change_reason_fn({{#junction.has_tenant_id}}new.tenant_id, {{/junction.has_tenant_id}}new.change_reason_code);

{{#junction.foreign_keys}}
{{#nullable}}
    -- Validate {{column}} (optional soft FK to {{table}})
    if new.{{column}} is not null then
{{#exists_function}}
        if not {{exists_function}}(new.{{column}}) then
            raise exception '{{error_message}}', new.{{column}}
                using errcode = '23503';
        end if;
{{/exists_function}}
{{^exists_function}}
        if not exists (
            select 1 from {{table}}
            where {{#use_system_tenant}}tenant_id = ores_utility_system_tenant_id_fn()
              and {{/use_system_tenant}}{{^use_system_tenant}}{{#junction.has_tenant_id}}tenant_id = new.tenant_id
              and {{/junction.has_tenant_id}}{{/use_system_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;
{{/exists_function}}
    end if;

{{/nullable}}
{{^nullable}}
    -- Validate {{column}} (soft FK to {{table}})
{{#exists_function}}
    if not {{exists_function}}(new.{{column}}) then
        raise exception '{{error_message}}', new.{{column}}
            using errcode = '23503';
    end if;
{{/exists_function}}
{{^exists_function}}
    if not exists (
        select 1 from {{table}}
        where {{#use_system_tenant}}tenant_id = ores_utility_system_tenant_id_fn()
          and {{/use_system_tenant}}{{^use_system_tenant}}{{#junction.has_tenant_id}}tenant_id = new.tenant_id
          and {{/junction.has_tenant_id}}{{/use_system_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;
{{/exists_function}}

{{/nullable}}
{{/junction.foreign_keys}}
{{#junction.insert_trigger.validations}}
    -- Validate {{column}}
    new.{{column}} := {{validation_function}}({{#junction.has_tenant_id}}new.tenant_id, {{/junction.has_tenant_id}}new.{{column}});

{{/junction.insert_trigger.validations}}
    return new;
end;
$$ language plpgsql;

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

create or replace rule {{junction.product}}_{{junction.component}}_{{junction.name}}_delete_rule as
on delete to "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl"
do instead
  update "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl"
  set valid_to = clock_timestamp(){{#junction.history}},
      closed_by = coalesce(ores_iam_current_actor_fn(), current_user){{/junction.history}}
  where {{#junction.has_tenant_id}}tenant_id = old.tenant_id
  and {{/junction.has_tenant_id}}{{junction.left.column}} = old.{{junction.left.column}}
  and {{junction.right.column}} = old.{{junction.right.column}}
{{#junction.system_tenant_visible}}
  -- A tenant reads the shared rows of the system tenant, so old can be one.
  -- The rule runs with the owner's rights, which row-level security does not
  -- bind, so the rule states the own-tenant limit itself.
  and tenant_id = ores_iam_current_tenant_id_fn()
{{/junction.system_tenant_visible}}
  and valid_to = ores_utility_infinity_timestamp_fn();
{{#junction.sql.rls_tenant_isolation}}

-- =============================================================================
-- Row-level security: tenant isolation for {{junction.name_title}}
{{#junction.sql.rls_system_tenant_visible}}
-- System-tenant sessions may also read every tenant's rows.
{{/junction.sql.rls_system_tenant_visible}}
{{#junction.sql.rls_own_or_system_tenant_rows}}
-- Every session reads its own rows and the system tenant's rows; writes
-- stay the current tenant's.
{{/junction.sql.rls_own_or_system_tenant_rows}}
-- =============================================================================
alter table "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl" enable row level security;

drop policy if exists {{junction.name}}_tbl_tenant_isolation_policy
    on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl";

create policy {{junction.name}}_tbl_tenant_isolation_policy
on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl"
for all using (
    tenant_id = ores_iam_current_tenant_id_fn(){{#junction.sql.rls_system_tenant_visible}}
    OR ores_iam_current_tenant_id_fn() = ores_utility_system_tenant_id_fn(){{/junction.sql.rls_system_tenant_visible}}{{#junction.sql.rls_own_or_system_tenant_rows}}
    or tenant_id = ores_utility_system_tenant_id_fn(){{/junction.sql.rls_own_or_system_tenant_rows}}
)
with check (
    tenant_id = ores_iam_current_tenant_id_fn(){{#junction.sql.rls_system_tenant_visible}}
    OR ores_iam_current_tenant_id_fn() = ores_utility_system_tenant_id_fn(){{/junction.sql.rls_system_tenant_visible}}
);
{{#junction.sql.rls_party_isolation}}

-- Party isolation (restrictive, ANDed with the tenant policy). A session with
-- no party context (visible_party_ids is NULL) passes through.
drop policy if exists {{junction.name}}_tbl_party_isolation_policy
    on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl";

create policy {{junction.name}}_tbl_party_isolation_policy
on "{{junction.product}}_{{junction.component}}_{{junction.name}}_tbl"
as restrictive
for all using (
    ores_iam_visible_party_ids_fn() is null
    or party_id = ANY(ores_iam_visible_party_ids_fn())
)
with check (
    ores_iam_visible_party_ids_fn() is null
    or party_id = ANY(ores_iam_visible_party_ids_fn())
);
{{/junction.sql.rls_party_isolation}}
{{/junction.sql.rls_tenant_isolation}}