Story: Move the lookup artefact tables and settle the remaining DQ artefact models

Table of Contents

This page documents a story in Sprint 28. It captures the goal, current status, acceptance criteria, and the tasks that compose it.

1. Goal

Move the fifteen lookup-template artefact tables onto domain models and retire the lookup artefact template, settle the owner of the DQ-only models including the GLEIF tables, and model the geo ip2country store so its staging table is generated from a model.

2. Status

Field Value
State DONE
Parent sprint Sprint 28
Now Nothing.
Waiting on Nothing.
Next Nothing.
Last touched 2026-10-14

3. Acceptance

  • No DQ artefact table comes from the older lookup template.
  • Each remaining ores.dq artefact model has a recorded owner decision.
  • The geo ip2country store is modelled and its staging table is generated from that model.
  • The hand-written lei_bic table moves with the other GLEIF tables.

4. Tasks

Task State Start End Description
Move the lookup artefact tables onto domain models DONE     Fifteen artefact tables come from the older lookup template, sql_schema_artefact_create, through ores.codegen.lookup_entity models: trading_account_types, asset_classes, asset_measures, benchmark_rates, business_centres, business_processes, cashflow_types, entity_classifications, local_jurisdictions, party_relationships, party_roles, person_roles, regulatory_corporate_sectors, reporting_regimes and supervisory_bodies. Move their models to domain_entity with the flag, so the canonical template generates the tables, and retire the lookup artefact template once nothing uses it. Overlaps the capture Migrate remaining lookup_entity/table models (28F6B7B2).
Settle the owner of the DQ-only models DONE 2026-10-14 2026-10-14 Nine artefact models live only in ores.dq: badge_definition, badge_severity, code_domain, coding_scheme, counterparty_alias, netting_set_alias, lei_entity, lei_relationship and synthetic_fx_spot_config. The hand-written lei_bic table belongs with lei_entity and lei_relationship, so it is decided and modelled with them. Where DQ owns the data, as with the badges and coding schemes, the ores.dq model is the owning model and stays. Where another component owns the published rows, move the artefact onto that component's model with an Artefact columns section, as the denormalised synthetic feed rows need.
Model the geo ip2country store DONE 2026-10-14 2026-10-14 The ip2country artefact table is hand-written, and so is the table it publishes into, ores_geo_ip2country_tbl: the geo component has no model. The store is not temporal and keys on an int8range with a GiST index, which the entity templates cannot express. Model the store, then generate the staging table from that model.

5. Decisions

  • The artefact type row settles the owner. A model whose artefact type row is terminal – target_table and target_subject both null – stages data whose only store is the staging table, so DQ owns the model and it stays. A row that publishes onward means the rows are the publishing component's data in transit, and the model moves to that component. The rule was already stated in both the DQ guide and the artefact-staging-only profile; nothing had applied it to these nine.
  • A moved artefact keeps its shape. The owning model states the old staging columns in an * Artefact columns section, key, nullability, defaults and indexes and all, so the regenerated table differs from the old one only in its name and its comment header.
  • The GLEIF tables are DQ's. lei_entity, lei_relationship and lei_bic are terminal: DQ queries the artefact table directly and the type row publishes nowhere. The counterparties and parties that two other type rows make from the same table are refdata's, but the entity staged is a GLEIF entity and no refdata model stages one.
  • A table at a component boundary is still a staging table. The report configuration binding has no artefact type of its own; the report_definitions dataset stages it beside the definitions. It moved onto ores.reporting.report_configuration all the same, because the rows are reporting's once published.
  • A store whose only reader is a SQL function gets no client surface. A range has no C++ type, so ores.geo.ip2country could not render a domain class whatever else was true of it, and nothing in the tree would call one: the country of an address is answered by ores_geo_ip2country_lookup_fn inside the database. The SQL-only store profile withdraws one row per technical space, which takes the C++, TypeScript and shell facets together, and the notify trigger with them – its subscriber is the eventing pipeline the profile removes, and a per-row pg_notify over a table loaded in bulk is the publish path's whole cost.
  • The index faces state an access method. ip_range @> address is a containment test, which only a GiST index answers, and neither index face could say using gist. Both take one now, so a model whose index is not a btree can say so rather than shipping an index the query cannot use.
  • A stored table takes a key, even when the hand-written one had none. The current-state template asks a stored table for a key. ip_range is the honest one: a range that appears twice in a store means nothing. A dataset that carries the same range twice now refuses at publish instead of storing it twice.

6. Result

Every acceptance criterion holds, and no hand-written artefact table is left in the tree: grep -rL 'Template: ' projects/ores.sql/create --include'*_artefact_create.sql'= returns nothing.

  • No DQ artefact table comes from the older lookup template. The template and its literate source are deleted, and nothing renders from it.
  • Each remaining ores.dq artefact model has a recorded owner decision: six stayed and four moved onto the component that owns the published rows, each owning model stating the staging shape it took on.
  • The geo ip2country store is modelled, and it generates both the store and its staging table. Its columns, order and both indexes match the hand-written table; it gains a primary key on the range.
  • The hand-written lei_bic table moved with the other GLEIF tables and is generated from ores.dq.lei_bic.

The work also left three pieces of codegen infrastructure that outlive the story: the using access method on both index faces, the SQL-only store profile, and ores.geo itself registered in the component catalogue so its models are read at all.

One gap is recorded and not this story's: the three GLEIF *_artefact_insert_fn functions have no callers, and projects/ores.dq/core/modeling/ores.dq.protocol.org does not list the GLEIF subjects. See the owner task's Notes.

7. Out of scope

Emacs 29.3 (Org mode 9.6.15)