SQL schema templates
Table of Contents
This page is the literate source for the sql_schema facet of the
codegen template library. Each section documents one template and
holds its source in a mustache block tangled to the sibling
.mustache file consumed by generator.py. The .mustache files are
generated artefacts — edit this document, then run the tangle
(compass build --direct tangle_codegen_templates or
projects/ores.lisp/src/ores-build-codegen-templates.el directly).
Output paths and profile membership come from
facet_catalogue.org.
Summary
Nine DDL templates owning the table layer: bi-temporal entity tables
in three model formats (domain_entity, table, unified), the
non-temporal variant, 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);
sql_schema_non_temporal_create belongs to non-temporal-sql, and
sql_schema_artefact_create is invoked directly by generator.py
rather than via a profile.
The sql_schema facet
The SQL projection of the entity model (entity lifecycle): 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 three near-identical create templates (create,
domain_entity_create, table_create) reflect the model formats
codegen has accreted; the temporal/non-temporal pair is the merge
target of the Phase-3 unify-templates story.
Mustache incantations
Beyond the group's standard constructs: 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}}.
Archetypes
| Archetype | Description |
|---|---|
| sql_schema_create.mustache | Bi-temporal entity table from the table model format ({{#table.columns}}, table.component, table.entity_plural): the unified successor to the per-format create templates. sql profile; output projects/ores.sql/create/{component}/{component}_{entity_plural}_create.sql (current_only gates the current-rows view). |
| sql_schema_domain_entity_create.mustache | 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. |
| sql_schema_domain_entity_drop.mustache | 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. |
| sql_schema_junction_create.mustache | 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. |
| sql_schema_non_temporal_create.mustache | Entity table without valid_from / valid_to for reference data that never changes in place; otherwise the domain-entity shape (primary key, natural keys, domain_entity.sql.tablename). non-temporal-sql profile; same create-path output. Merge candidate of the Phase-3 temporal unification. |
| sql_schema_notify_trigger.mustache | 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. |
| sql_schema_notify_trigger_drop.mustache | Drop counterpart of the notify trigger. sql profile; output projects/ores.sql/drop/{component}/{component}_{entity_plural}_notify_trigger_drop.sql. |
| sql_schema_table_create.mustache | 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. |
| sql_schema_artefact_create.mustache | Staging table for an entity's imported artefacts: entity columns plus artefact bookkeeping ({{#entity.has_coding_scheme}}, {{#entity.has_image_id}}, artefact indexes, optional insert function via {{#entity.has_artefact_insert_fn}}). No profile — invoked directly by generator.py on the dataset path. |
See also
- (Parent template group doc:
<group>_group.org.) - Codegen template library — the groups overview.
- Facet — the MASD concept these templates project.
- SQL facet — the facet's modeling reference.
- Variability Model — to understand the configuration knobs these
templates branch on (the
masd.sql.*feature bundle:system_scope,nullable_tenant_id,extra_checks,fk_copy_validations, …), see Part 2. Structural predicates (has_tenant_id,is_uuid, …) are derived from the model, not configured. - Applied MASD — the facet catalogue and codegen routes.
- facet_catalogue.org — profile → template/output mapping.