Codegen entity meta-model — SQL

:rls_policy_style: names the shape of the two policies the create file ends with, and the choice is about what the policy costs:

• default differs from nothing: it is the shape every tenant-scoped table already emits, and it calls ores_iam_current_tenant_id_fn and ores_iam_visible_party_ids_fn for each row the policy filters.
• hot_path emits the same two policies against the session settings directly: tenant_id = (select nullif(current_setting('app.current_tenant_id', true), '')::uuid) and party_id in (select unnest(nullif(current_setting( 'app.visible_party_ids', true), '')::uuid[])). The scalar subquery is what makes the difference: the planner evaluates it once for each query rather than once for each row, so a table that filters millions of rows pays for the setting once.

The hot-path shape is a pair, so both isolation flags are required. It also forces the table's own row-level security and names the party policy select alone, restrictive, with no null passthrough: a session that states no visible parties reads no rows, which is the rule an ingest session obeys. It therefore refuses :rls_system_tenant_visible: and :rls_own_or_system_tenant_rows:, which name different policies, and :rls_service_read_role:, whose cross-tenant read lives inside the default body and would otherwise stop appearing without a word.

A soft foreign key states which tenants it may match. By default the insert trigger checks the referenced row in the writer's own tenant, so a row cannot point at another tenant's row. :use_system_tenant: true names a target that lives only in the system tenant – the platform-managed catalogues every tenant reads. :system_tenant_visible: true names a global registry whose system-tenant rows every tenant may read while a tenant may still own rows of its own, so the check admits the row's own tenant or the system tenant. Use it for a key into an entity whose repository declares :system_tenant_visible: and whose row-level policy exposes system rows, so the trigger and the read path agree; a mismatch rejects a reference the reads happily return. A junction that carries the registry's own rows – compute's app version platforms, whose package every tenant dispatches against – declares the same flag in its * Repository drawer, which widens its reads the same way. :use_no_tenant: true names a target with no tenant_id at all. Neither :use_system_tenant: nor :system_tenant_visible: combines with :enforce:, since a REFERENCES constraint can only match the row's own tenant.

The check reads the target's current version by default, because a temporal target holds closed versions beside the live one. A target with no validity window at all – a hand-written table with no modeling org – states :target_no_validity: true, and the check then names the key column alone rather than filtering on a valid_to the target does not have. A target whose model states :immutable: needs no such flag: the generator reads its immutability itself.

A foreign key into an immutable entity can be enforced by the database. A foreign key is normally a trigger check against the target's current row, because a temporal target holds closed versions that a REFERENCES constraint would accept as current. An immutable target has no closed versions, so a foreign key declared with :enforce: true in the * Foreign keys section renders a named REFERENCES constraint (<table>_<column>_fk) and no trigger check. A key that matches its own tenant, the default, names tenant_id on both sides, so a row cannot reference another tenant's row; a tenant-scoped immutable table therefore keys on (tenant_id, <primary key>). A key with :use_no_tenant: names one column and needs a target without tenant_id. The generator refuses :enforce: when the target table's model is unknown or not immutable, when the key uses :use_system_tenant:, when the tenant scopes of the two tables differ, and when the constraint name is longer than PostgreSQL's 63 characters.

A pinned key holds a copied fact to its source. A row may copy a fact from another row, such as an instrument copying its trade's type, only when a key holds the copy to its source. A ** Pinned keys table under * SQL lists, per pin, a name, the copied columns, the source table, the target_columns they must equal, and an error_message whose % is the first column. A pin matches the row's own tenant when the row has one, and the two tables must agree on being tenant-scoped. Against an immutable source the pin is a constraint, <table>_<name>_pin, and the source must declare the referenced columns as its primary key or as a unique index with no predicate. Against a temporal source the insert trigger checks the source's current row; it holds at insert only, so copy only facts that never change on the source. The generator cannot check that rule; it is a modelling rule. In both modes a row whose pinned columns hold a null is not checked, as PostgreSQL does for a constraint. A pin into a mutable current-state table is refused, and so are repeated pin names, columns either model does not declare, and a message without exactly one %.

A routed column takes its codes from a catalogue. A model whose * SQL ** Flags drawer declares :routed_by: (a catalogue model's title, such as ores.trading.trade_type_catalogue) and :routed_column: (one of its own columns) gets a check that the column holds one of the codes the catalogue routes to this entity, in the catalogue's order. The list is the catalogue's, so every table's list comes from one source and no code can be accepted by two tables. The generator refuses one flag without the other, a column the model does not declare, a title that names no model, and a catalogue that routes no code to the entity.

A fixed column keeps its first value. A column marked :fixed: true in * Columns keeps the value of its row's first version: the insert trigger refuses a new version that changes it, with SQLSTATE 23514, and it does so for a version replace too. A copy pinned to a temporal source stays true only when the copied columns are fixed. A fixed column needs a temporal table; a current-state row has no versions to compare.

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

Name the row's owner before choosing its policy. Every row belongs to a tenant, to the system tenant, or to the installation — the deployment's own facts, which no tenant owns: the services roster, hosts and wrapper nodes, grid and node samples, telemetry logs, the database and build row. The owner decides the policy, and the three are not interchangeable:

:rls_system_tenant_visible: is not how a table says it is installation- or shared-scoped. It emits a different shape — a system-tenant session sees every tenant — which admits one tenant's data to another and is exactly what check_rls_system_tenant_widenings.py exists to stop. The two shapes spell differently and mean differently: tenant_id = system admits only the installation's own rows; current = system admits everybody's. Read Tenant ownership and the information flow before widening or hand-writing either.

Why the flag is named :rls_own_or_system_tenant_rows:, and not after the classes it serves. The old hand-written idiom and the new flag must be told apart by name alone, because a reader who confuses them reaches for the flagged widening. :rls_system_tenant_visible: names the session — it is the system tenant that sees more — and emits current = system. The new flag names the rows it admits and keeps tenant_id on the left of both comparisons: tenant_id = current or tenant_id = system. So the name says what is admitted (the row's own tenant, or the system tenant's own rows) rather than who is asking, and it can never be read as a widening. It serves both ownership classes that want this shape, shared reference and installation, because they want the same rows and the same write rule; naming it after one class would invite a second identical flag for the other.

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, the own-or-system-tenant shape, the :rls_system_tenant_visible: widening, 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.

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.