ores.sql.schema
Table of Contents
This page is the node for the ores.sql.schema facet of the codegen
template library, part of the ores.sql technical space. Each archetype
below is documented, and holds its mustache source, in its own
linked page — this page is the facet-level summary and the routing
table to them.
1. Summary
DDL templates owning the table layer: bi-temporal entity tables in the
domain_entity format, junction tables, drop scripts, the NOTIFY
trigger pair, and the artefact/staging table. Most are wired to the
sql profile (projects/ores.sql/create/ and drop/ outputs); the
artefact-staging template is invoked directly by generator.py
rather than via a profile.
2. The ores.sql.schema facet
The SQL projection of the entity model: each entity org-model's
natural keys, columns, and temporal flags become a PostgreSQL table
with the house conventions — UUID primary key, valid_from=/=valid_to
bi-temporality where applicable, tenant_id, and a _notify trigger
announcing changes on a NATS channel. The unified table model format
this facet once also served (sql_schema_create.mustache) was
retired once every entity migrated to domain_entity — see the
Retire legacy codegen profile system; add junction support to physical-space codegen story.
3. Mustache incantations
Every template opens with a {{! purpose }} comment (kept from the
pre-literate files — they render to nothing) and injects
{{{sql_license}}} as triple-stache. Column loops use
{{^last}},{{/last}} inverted sections for comma placement, and
{{#default}}…{{/default}}=/={{#nullable}}/{{^nullable}} for
per-column DDL fragments. The entity-create templates carry
book/cross-check validation fragments gated by {{#declare_vars}},
{{#coalesce}} and {{#copy_empty}}.
4. Archetypes
| Archetype | Description |
|---|---|
| ores.sql.schema.domain_entity_artefact_create | 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}}), or an explicitly declared staging body via * Artefact columns when the staging shape is not a plain projection of the entity table. Ported from the lookup_entity-only sql_schema_artefact_create.mustache so migrating a lookup_entity model to domain_entity (see the entity-classification-drift-baseline rollout) doesn't strand its artefact/staging table with no regeneration path. |
| ores.sql.schema.domain_entity_create | The original bi-temporal create, driven by the domain_entity model ({{#domain_entity.columns}}, natural keys, primary key). Carries the richest validation machinery: book/cross-check error fragments, {{#declare_vars}} function-local declarations, {{#coalesce}} / {{#copy_empty}} column handling. sql profile; same output path as the unified variant — the two coexist while models migrate formats. |
| ores.sql.schema.domain_entity_drop | Drop script for an entity's table and dependents: a thin {{#domain_entity}} section over component / entity_plural / product. sql profile; output projects/ores.sql/drop/{component}/{component}_{entity_plural}_drop.sql. The <<paste:5E47F108-1350-4540-B3C2-E83DD5379B2D>> marker is replaced with any entity-model-defined extra drop statements (e.g. the validate function generated by the companion _table.org model). If no entity implements the kind the marker expands to an empty string. |
| ores.sql.schema.junction_artefact_create | Staging table for a junction's imported artefacts: the header, the junction's two key columns, its extra columns and a version. The junction counterpart of ores.sql.schema.domain_entity_artefact_create, which a junction cannot use because the two model shapes are different. |
| ores.sql.schema.junction_create | Many-to-many junction table from the junction model: composite text primary key over junction.left / junction.right columns, optional tenant_id ({{#junction.has_tenant_id}}), per-side indexes with comments. sql profile; output projects/ores.sql/create/{component}/{component}_{entity}_create.sql. |
| ores.sql.schema.notify_trigger | pg_notify trigger announcing entity changes (insert/update/delete with the primary-key value) on the entity's channel — the database end of the eventing pipeline. {{#domain_entity}} section. sql profile; output …/{component}_{entity_plural}_notify_trigger_create.sql. |
| ores.sql.schema.notify_trigger_drop | Drop counterpart of the notify trigger. sql profile; output projects/ores.sql/drop/{component}/{component}_{entity_plural}_notify_trigger_drop.sql. |
| ores.sql.schema.table_create | The earlier bi-temporal table create, sharing its output path and most of its body with sql_schema_create — the duplication the unified template exists to retire. sql profile. |
5. See also
- ores.sql — the parent technical space's generated facet/archetype catalogue and shared Mustache conventions.
- SQL Technical Space — the domain-concept overview of this technical space.
- Codegen template library — the groups overview.