Codegen entity meta-model — SQL

Physical model mapping
Flags
Hierarchy: has_parent_id
Projection configuration (variability)
Paste blocks
- Extra drop statements
See also

This page is a segment of the Codegen org-entity meta-model hub, covering the * SQL section and the ores.sql.schema facet it feeds: bi-temporal table create/drop, insert triggers, and notify triggers. The columns it operates on are declared once, shared with C++, on the Keys and columns page — this page is only the SQL-specific fields layered on top.

This page splits its fields across two diagrams, per Applied MASD § "The entity model file as a unified literate document":

Structural fields — always part of the entity's shape, never toggled — drawn as a UML class diagram under Flags below. :tablename: is one: a required identity field with no default, every entity needs exactly one value. :parent_id_column: and :hierarchy_name_field: are also structural — they name real columns — but only exist conditionally, once :has_parent_id: is on; the UML diagram marks them [0..1] for that reason, and a note on the diagram cross-references the UVL feature that gates them.
Non-structural — a single authored, optional VMM feature — drawn as a UVL feature diagram under Hierarchy below: :has_parent_id: itself, the toggle that decides whether the two conditional structural fields above exist at all.

Physical model mapping

Facet	Description
ores.sql.schema	Bi-temporal schema: table create/drop, insert triggers, notify triggers.

Flags

The section always opens with a ** Flags drawer:

* SQL
** Flags
,:PROPERTIES:
,:tablename: ores_refdata_books_tbl
,:END:

Property	Required	Meaning
`:tablename:`	yes	The generated table's name. Convention: `<product>_<component>_<entity_plural>_tbl`.

Structural view — the SQL segment's fields, per the split above, plus its one paste block kind (extra drop statements, below), drawn as its own class: a block kind's ID is the well-known kind UUID, distinct from any entity's per-instance implementing block:

(Source: entity_meta_model_sql_structural.puml, rendered with plantuml.)

The :tablename: flag above (ores_refdata_books_tbl) drives the generated create table statement's identifier directly:

create table if not exists "ores_refdata_books_tbl" (
    "id" uuid not null,
    "tenant_id" uuid not null,
    "version" integer not null,
    -- ... columns from Keys and columns ...
);

Future SQL sub-sections (custom triggers, indices) live alongside this Flags sub-heading.

Hierarchy: `has_parent_id`

Some entities form a tree via a self-referencing soft foreign key — a book belongs to a portfolio, a counterparty can have a parent counterparty. Setting :has_parent_id: true tells the ores.sql.schema facet to generate a recursive-CTE function that walks that tree, so consumers don't each hand-write their own recursive query.

Non-structural view — :has_parent_id:, the one feature on this page:

Property	Required	Meaning
`:has_parent_id:`	no	Turns the knob on.
`:parent_id_column:`	if set	The self-referencing FK column name, e.g. `parent_party_id`.
`:hierarchy_name_field:`	if set	An existing column used as the display label in the tree.

counterparty is one such self-referencing entity; its model sets all three properties together:

* SQL
** Flags
,:PROPERTIES:
,:tablename:            ores_refdata_counterparties_tbl
,:has_parent_id:        true
,:parent_id_column:     parent_counterparty_id
,:hierarchy_name_field: full_name
,:END:

Generates a <product>_<component>_<entity_plural>_hierarchy_fn(p_tenant_id, p_root_id, p_from_root) recursive-CTE function returning flat (id, parent_id, name) rows — the subtree rooted at p_root_id when p_from_root is false, or (resolving up to the real root first) the whole tenant tree when true.

The generated function only produces flat rows; it does not build a tree. Turning those rows into an actual tree is generic work that does not vary from one hierarchical entity to the next, so it is not generated at all — every consumer instead calls the same hand-written ores::utility::domain::hierarchy.hpp, whose build_tree() turns a vector of flat hierarchy_flat_row into a tree of hierarchy_node.

:has_parent_id: does not generate everything a hierarchical entity needs, though. The self-referencing FK column itself (parent_<entity>_id) and its insert-trigger validation are still hand-written today. The hierarchy function does not depend on that column being generated; it composes independently either way.

Projection configuration (variability)

Every property below is, in MASD Variability terms, a feature (ores.sql.schema bundle) — authored on the model's sql section and read through in codegen/core.py=/=org_loader.py, never computed. None of them changes the entity's logical shape, only the generated DDL, triggers, and rules. Full classification and cross-facet index: ORE Studio Variability Model § "Bundle: ores.sql.schema" — this section is the narrative home for these knobs; that page indexes them.

Feature	Type	Default	Effect
`:system_scope:`	bool	false	Table is system-tenant-scoped: `tenant_id` defaults to the system tenant and the insert trigger forces it. Structural consequence: changes the `tenant_id` column default.
`:nullable_tenant_id:`	bool	false	`tenant_id` is nullable (system records carry NULL); selects the nullable-tenant validation and index variants. Structural consequence: changes `tenant_id` nullability.
`:extra_checks:`	collection<string>	∅	Additional `CHECK` constraints to emit on the table.
`:extra_delete_sets:`	collection<string>	∅	Extra `SET` clauses in the soft-delete rule.
`:fk_copy_validations:`	collection<struct>	∅	FK validations that also copy/denormalise fields from the referenced row.
`:text_code_validations:`	collection<struct>	∅	FK validations against a text lookup column.
`:party_id_from_book_id:`	struct	absent	Derive and validate `party_id=/=portfolio_id` from `book_id` in the trigger.
`:party_id_from_session:`	bool	false	Set `party_id` from the session GUC `app.current_party_id`.

Tenant-scoping is a policy, not three booleans. :system_scope: and :nullable_tenant_id: are mutually exclusive with the default (standard-tenant) mode; together they form a single enumerated feature — tenant-scoping policy ∈ {standard, system, nullable}. If they migrate anywhere, it is into the logical model as a tenant-scoping attribute (they lean structural despite being authored as knobs).

SECURITY DEFINER on insert/validate triggers is not a feature — checked against library/templates/sql_schema_domain_entity_create.mustache, it is emitted unconditionally today, matching the evaluation checklist's mandatory rule. It is mentioned here only so nobody goes looking for a :security_definer: knob that no longer exists.

Paste blocks

The SQL segment defines the following paste block kinds, one per sub-heading below — see Paste blocks for the general mechanism they assume.

Extra drop statements

This paste block defines additional DROP statements inserted into the generated <component>_<entity_plural>_drop.sql, just before drop table if exists. Use this when the entity's companion _table.org model generates database objects (e.g. a validate function) that the entity template itself has no way to know about. If no entity implements this kind, the marker collapses to nothing.

Example of an inclusion of this paste block in a template (sql_schema_domain_entity_drop):

drop function if exists {{product}}_{{component}}_{{entity_plural}}_insert_fn;
<<paste:5E47F108-1350-4540-B3C2-E83DD5379B2D>>
drop table if exists "{{product}}_{{component}}_{{entity_plural}}_tbl";

To use this paste block on an entity, declare it under ** Paste blocks > *** Extra drop statements, with the body in a block named body. See the following example:

** Paste blocks
*** Extra drop statements

#+begin_src sql :name body :implements 5E47F108-1350-4540-B3C2-E83DD5379B2D
drop function if exists ores_refdata_validate_rounding_type_fn;
#+end_src

An entity is not limited to a single drop statement, nor to a single block: it may write several statements inside one body block, or spread them across several *** Extra drop statements sub-headings. Either way, codegen collects them all, in the order they appear in the model, and concatenates them with a blank line between each.