ores.sql.schema.domain_entity_artefact_create

1. Template
2. See also

Staging table for a domain_entity's imported artefacts: entity columns plus artefact bookkeeping ({{#domain_entity.has_coding_scheme}}, {{#domain_entity.has_image_id}}, artefact indexes, optional insert function via {{#domain_entity.has_artefact_insert_fn}}). Ported from the lookup_entity-only sql_schema_artefact_create.mustache so migrating a lookup_entity model to domain_entity doesn't strand its artefact/staging table with no regeneration path. Always lands under the dq product regardless of the owning component – the artefact table is DQ's staging infrastructure for that entity's import pipeline, not the entity's own component.

A staging table is usually a projection of the entity table, but not always. Reading the 12 divergent staging tables in ores.dq against their publish functions showed five deliberate differences that no projection can derive: the staging table renames its key, carries a natural key where the store holds a uuid, redacts a secret, redacts a field the publisher supplies, and adds a field only the import needs. An entity that needs one of those declares its staging body in a * Artefact columns section: a top-level heading, one ** <column> child per staging column in order, each read exactly like a * Columns entry. Absent, this archetype projects the entity columns as before. Present, the section replaces the staging body after its dataset_id=/=tenant_id header, and the first entry is the staging key unless the section's own :key: property names another. A model may not declare both the section and {{#domain_entity.has_artefact_insert_fn}}, because the generated insert helper is built from the entity's own columns and would insert the wrong shape.

#+default: disabled: most domain_entity models (catalog, change_reason, …) have no artefact/staging table at all – only entities that actually import via the DQ artefact pipeline need one. An entity opts in with :ores.sql.schema.domain_entity_artefact_create.enabled: true in its * Flags drawer.

See the Template variable reference for the complete list of available variables and their semantics.

1. Template

The full template source. Edit here and re-tangle with compass build --direct tangle_codegen_templates to regenerate library/templates/sql_schema_domain_entity_artefact_create.mustache.

{{! GENERATED FILE — tangled from projects/ores.codegen/library/templates/sql_schema.org. Edit the org source. }}
{{! Template to generate SQL schema for a domain_entity's artefact/staging table }}
{{{sql_license}}}
/*
 * AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Template: sql_schema_domain_entity_artefact_create.mustache
 * To modify, update the template and regenerate.
 */

-- =============================================================================
-- {{domain_entity.description_oneline}} - Artefact Table
-- =============================================================================

create table if not exists "ores_dq_{{domain_entity.entity_plural}}_artefact_tbl" (
    "dataset_id" uuid not null,
    "tenant_id" uuid not null,
{{^domain_entity.artefact_columns}}
    "{{domain_entity.primary_key.column}}" {{domain_entity.primary_key.type}} not null,
    "version" integer not null,
{{#domain_entity.natural_keys}}
    "{{column}}" {{type}}{{#nullable}} null{{/nullable}}{{^nullable}} not null{{/nullable}}{{#default}} default {{{default}}}{{/default}}{{^last}},{{/last}}
{{/domain_entity.natural_keys}}
{{#domain_entity.columns}}
    "{{name}}" {{type}}{{#nullable}} null{{/nullable}}{{^nullable}} not null{{/nullable}}{{#default}} default {{{default}}}{{/default}}{{^last}},{{/last}}
{{/domain_entity.columns}}
{{#domain_entity.has_image_id}}
    ,"image_id" uuid
{{/domain_entity.has_image_id}}
{{#domain_entity.has_coding_scheme}}
    ,"coding_scheme_code" text not null
{{/domain_entity.has_coding_scheme}}
{{/domain_entity.artefact_columns}}
{{#domain_entity.artefact_columns}}
    "{{name}}" {{type}}{{#nullable}} null{{/nullable}}{{^nullable}} not null{{/nullable}}{{#default}} default {{{default}}}{{/default}}{{^last}},{{/last}}
{{/domain_entity.artefact_columns}}
);

create index if not exists dq_{{domain_entity.entity_plural}}_artefact_dataset_idx
on ores_dq_{{domain_entity.entity_plural}}_artefact_tbl (dataset_id);

create index if not exists dq_{{domain_entity.entity_plural}}_artefact_tenant_idx
on ores_dq_{{domain_entity.entity_plural}}_artefact_tbl (tenant_id);
{{^domain_entity.artefact_columns}}

create index if not exists dq_{{domain_entity.entity_plural}}_artefact_{{domain_entity.primary_key.column}}_idx
on ores_dq_{{domain_entity.entity_plural}}_artefact_tbl ({{domain_entity.primary_key.column}});
{{/domain_entity.artefact_columns}}
{{#domain_entity.artefact_columns}}

create index if not exists dq_{{domain_entity.entity_plural}}_artefact_{{domain_entity.artefact_key_column}}_idx
on ores_dq_{{domain_entity.entity_plural}}_artefact_tbl ({{domain_entity.artefact_key_column}});
{{/domain_entity.artefact_columns}}
{{#domain_entity.artefact_indexes}}

create index if not exists dq_{{domain_entity.entity_plural}}_artefact_{{name}}_idx
on ores_dq_{{domain_entity.entity_plural}}_artefact_tbl ({{columns}});
{{/domain_entity.artefact_indexes}}
{{#domain_entity.has_artefact_insert_fn}}

-- Function to insert {{domain_entity.entity_plural}} into the artefact table
create or replace function ores_dq_{{domain_entity.entity_plural}}_artefact_insert_fn(
    p_dataset_id uuid,
    p_tenant_id uuid,
    p_{{domain_entity.primary_key.column}} {{domain_entity.primary_key.type}},
    p_version integer,
{{#domain_entity.columns}}
    p_{{name}} {{type}}{{#default}} default {{{default}}}{{/default}}{{^last}},{{/last}}
{{/domain_entity.columns}}
{{#domain_entity.has_image_id}}
    ,p_image_id uuid default null
{{/domain_entity.has_image_id}}
) returns void as $$
begin
    insert into ores_dq_{{domain_entity.entity_plural}}_artefact_tbl (
        dataset_id, tenant_id, {{domain_entity.primary_key.column}}, version,
{{#domain_entity.columns}}
        {{name}}{{^last}},{{/last}}
{{/domain_entity.columns}}
{{#domain_entity.has_image_id}}
        ,image_id
{{/domain_entity.has_image_id}}
    )
    values (
        p_dataset_id, p_tenant_id, p_{{domain_entity.primary_key.column}}, p_version,
{{#domain_entity.columns}}
        p_{{name}}{{^last}},{{/last}}
{{/domain_entity.columns}}
{{#domain_entity.has_image_id}}
        ,p_image_id
{{/domain_entity.has_image_id}}
    );
end;
$$ language plpgsql;
{{/domain_entity.has_artefact_insert_fn}}

2. See also

Parent facet: ores.sql.schema
sql_schema_artefact_create.mustache — the lookup_entity-only sibling this was ported from.
Template variable reference

ores.sql.schema.domain_entity_artefact_create

Table of Contents

1. Template

2. See also