SQL Technical Space

Part
Facets and archetypes
Component mapping
See also

The SQL technical space covers all database artefacts generated by ores.codegen. It contains 3 facets and approximately 26 archetypes. Unlike the C++ TS, the SQL TS has a single implicit part — SQL files are not split by compile-time role (there is no header/implementation distinction in SQL).

The SQL TS projects that live in the repository are placed under projects/ores.sql/. All DDL output is managed by the schema lifecycle there. For the MASD concepts see MASD, Physical Space, and Facet. Group-level literate source: sql_group.

Part

The SQL TS has a single part corresponding to the schema directory:

Part	Directory	Contains
`schema/`	`projects/ores.sql/create/`	DDL scripts, trigger scripts, drop scripts

Population scripts and service setup scripts live under related directories (projects/ores.sql/populate/, projects/ores.sql/service/) but are still part of the same TS.

Facets and archetypes

Schema facet

Facet profile: --profile sql. Literate source: sql_schema. Modeling reference: SQL facet.

Generates the DDL lifecycle for an entity: the create table script, the notify trigger (which fires a pg_notify on every mutation, enabling NATS-based change propagation), and the corresponding drop scripts. The SRPP is the bi-temporal entity table pattern: every reference-data entity table has valid_from / valid_to and transaction_from / transaction_to columns, a surrogate primary key, and an auto-notify trigger.

Multiple schema model variants are supported:

Archetype	Description
Schema create (unified)	Bi-temporal entity table — unified model (preferred)
Schema create (domain entity)	Bi-temporal entity table — domain model
Schema create (legacy table)	Bi-temporal entity table — legacy table model
Schema create (non-temporal)	Non-temporal entity table (lookup tables only)
Schema create (junction)	Many-to-many junction table
Schema create (artefact)	Staging / artefact table
Entity drop	DROP TABLE script
Notify trigger create	`pg_notify` trigger
Notify trigger drop	Trigger drop script

9 archetypes total.

Service facet

Facet profile: --profile service-sql. Literate source: sql_service.

Generates the PostgreSQL user/permission setup for a service. Each service that touches the database has a dedicated database user with minimal privileges — the service facet encodes this SRPP. These are generated once per service, not once per entity.

Archetype	Description
Service users	`CREATE ROLE` statements for service DB users
DB grants	`GRANT` statements (SELECT, INSERT, etc. per table)
IAM accounts	`INSERT` into the `iam.service_account` table
Account roles	`INSERT` into the `iam.service_account_role` table

4 archetypes total.

Populate facet

Facet profile: --profile populate. Literate source: sql_populate.

Generates the reference-data seed scripts — the canonical data sets for entities such as currencies (ISO 4217), countries (ISO 3166), FPML coding schemes, methodologies, and tags. Unlike the schema and service facets, the populate facet archetypes are not parameterised per-entity-type; each archetype encodes a fixed data set. The dataset is driven by an FPML source and the ores.codegen automated route reads FPML XML to derive the populate scripts.

Archetype	Description
Batch execute	Master include script for a full dataset run
Catalog populate	FPML catalog registration
Country populate	ISO 3166 country records
Currency populate	ISO 4217 currency records
Non-ISO currency populate	Additional non-standard currencies
Dataset populate	Dataset registration
Dataset dependency populate	Dataset dependency graph
Dataset refdata	FPML dataset entry
Flag populate	Country flag SVG image records
Methodology populate	ORE methodology records
Populate function (refdata)	FPML population helper function
Populate refdata	FPML artefact population master
Tag populate	Classification tag records

13 archetypes total.

Component mapping

SQL TS projections land in the following components:

Facet	Target component
Schema	`ores.sql` (`create/` subdirectory)
Service	`ores.sql` (`service/` subdirectory)
Populate	`ores.sql` (`populate/` subdirectory)

All SQL artefacts are managed centrally in ores.sql rather than distributed per-entity-group, because the database schema is a shared resource across all components.