Codegen entity meta-model — SQL

The section always opens with a ** Flags drawer:

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

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.

Some entities form a tree via a self-referencing soft foreign key. A book belongs to a portfolio, and a counterparty can have a parent counterparty. Setting :has_parent_id: true makes the ores.sql.schema facet generate a recursive-CTE function that walks that tree, so consumers don't each hand-write their own recursive query. The C++ facets gate their matching members on the same feature: get_hierarchy on the repository and the service, and the protocol and NATS-handler entries that reach them.

All three properties sit in the entity's root * Flags drawer, beside :has_tenant_id: and :has_workspace_id:. :has_parent_id: is a structural feature, not one of this section's ores.sql.schema knobs; its bundle is Keys and columns.

The first property turns the block on and the other two name the columns the generated function reads. Set the first without the others and the function names empty columns, so author all three together.

counterparty is one such self-referencing entity. Its model shows where each property lives. The feature and the two column names are in the root drawer; the table name is in this section:

* Flags
,:PROPERTIES:
,:schema:               public
,:product:              ores
,:component:            refdata
,:profile:              self-referencing-hierarchy
,:parent_id_column:     parent_counterparty_id
,:hierarchy_name_field: full_name
,:END:

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

The bound Self-referencing hierarchy profile assigns :has_parent_id: true, so the model does not state it. A model with no such profile writes the line itself, in that same root drawer.

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.

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.

The bundle as a feature model:

Current-state is a distinct axis from :no_audit_columns:. A :no_audit_columns: entity (a TimescaleDB hypertable) drops only the version column and the audit tail; it keeps valid_from=/=valid_to as its transaction-time window, and its reads still filter on valid_to. A :current_state: entity drops the whole temporal axis: the SQL create has no valid_from=/=valid_to, no GIST exclusion and no temporal check, and its primary key is the model's own key (for login_info, account_id), not (tenant_id, key, valid_from, valid_to). It has no history surface: the service declares no get_<entity>_history, the C++ and TypeScript protocol headers state no history request/response pair and no history subject, the NATS registrar subscribes to no history subject, and the shell-command and presentation history facets omit the history verb and field mapper. The C++ domain type then has no recorded_at and the repository upserts (insert_or_replace) and hard-deletes instead of closing a validity window, so a caller must not expect version rows from read_all or a soft delete from remove. The insert trigger still validates tenant_id, and the hand-written table's indexes and constraints carry over through the ** Indexes and ** Checks sections (plus the per-column :skip_uuid_check: / :skip_empty_check: and per-FK :skip_check: suppressions), which is how the iam_login_info table keeps login_info_locked_idx and avoids gaining a nil-UUID check it never had. The feature is authored in the * SQL ** Flags drawer, beside the other SQL projection knobs above.

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).

Row-level security emission is an explicit intent flag, not a consequence of having a tenant column. The :rls_*: features above emit enable row level security and the matching policy blocks inline at the end of the generated per-entity create file, so a regenerated table owns its whole SQL lifecycle. They default off and no profile fixes them: many tenant-column tables deliberately skip RLS (system-owned registries, currently silenced by RLS_001 ignore entries in validation_ignore.txt), and a party_id column is not party isolation scope on its own (e.g. market_series). The emitted blocks are verbatim copies of the dominant hand-written shapes from the components' *_rls_policies_create.sql files (T1 tenant-only, T2 own-or-system-tenant, T3 restrictive party membership). Because create policy resolves ores_iam_current_tenant_id_fn at creation time, inline emission is only legal for components created after the iam section of create/create.sql; the pre-iam components keep their RLS-layer files until that ordering question is resolved.

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 entity commissioning process reference's mandatory rule. It is mentioned here only so nobody goes looking for a :security_definer: knob that no longer exists.

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

• Codegen org-entity meta-model — the hub.
• Keys and columns — the column declarations this section's DDL is built from.
• Paste blocks — the shared paste-block mechanism.
• Variability — MASD's formal vocabulary for the ores.sql.schema features above. :tablename: (§ "Flags") is structural, not variability. :has_parent_id: (§ "Hierarchy") is variability, but its bundle is structural.
• ORE Studio Variability Model — the ores.sql.schema bundle's classification and cross-facet index; this page is the narrative home for those knobs.