Investigation: Which ores.dq artefacts are handcrafted, and can the artefact tables generate from the main entity

An artefact table can generate from the same entity model as its base table. The mechanism exists today and 19 of the 49 artefact tables already use it.

Two archetypes do the work:

Each archetype projects the base entity onto the staging table:

• dataset_id, tenant_id, the primary key and version form the header.
• The entity's own columns follow, with their types, nullability and defaults.
• coding_scheme_code is added when the model declares a coding scheme.
• image_id is added when the model declares an image.
• The audit columns and the validity window are dropped: modified_by, performed_by, change_reason_code, change_commentary, valid_from, valid_to.
• Extra indexes are already expressible, because both archetypes read artefact_indexes.

The projection is also where the archetype stops being enough. Reading the 12 divergent tables against their publish functions showed that the staging table is the import contract, not a copy of the store: it renames the key, substitutes a natural key for a uuid, redacts secrets and target state, adds fields only the import needs, and leaves dataset-level fields out. Each difference is implemented deliberately in the publish function, and none is a defect. So the model must be able to state the projection, not only inherit it. The task proposes an * Artefact columns section, the sibling of the * Artefact indexes section, absent meaning the current projection.

One detail makes the pattern work across component boundaries. Codegen forces the dq_ output prefix for these two archetypes, whatever component owns the model:

# Artefact tables and population functions use 'dq_' prefix
if 'artefact' in template_name or 'populate_function' in template_name:
    output_filename = f"dq_{entity_plural}{suffix}"

So ores.iam.account_type generates dq_account_types_artefact_create.sql, and ores.refdata.business_centre generates dq_business_centres_artefact_create.sql. The artefact table belongs to the DQ product even though the entity belongs to another component. Both archetypes document this as deliberate.

The consequence is that the work is not to build a generator. The work is to give a model to every table that has none, and to reconcile the tables that genuinely differ from their base.

projects/ores.sql/create/dq declares 74 tables. 49 of them are artefact tables.

The 5 derivable tables need no model work beyond switching the archetype on. The 6 foreign tables need the owning component's model extended, not a DQ model. That leaves 19 tables that need a real decision: the 12 divergent and the 7 orphan.

The drift check cannot see these, because it diffs only what the models produce. The entity registry settles it: 13 artefact tables carry the generated marker, yet no entity with their plural exists among the 295 entities codegen knows.

The clearest case is asset_classes. The table ores_dq_asset_classes_artefact_tbl is live:

• populate/fpml/fpml_asset_class_artefact_populate.sql writes to it.
• create/dq/dq_asset_classes_population_functions_create.sql publishes from it.
• create/refdata/refdata_publish_from_dq_create.sql writes ores_refdata_asset_classes_tbl.
• create/refdata/refdata_rls_policies_create.sql and create/iam/iam_tenant_purger_create.sql reference it.

Yet no model declares the plural asset_classes. The modelled entity is asset_class_code (ores.refdata.asset_class_code.org), whose plural is asset_class_codes. The asset-class unification plan renamed the entity and left the old generated outputs in place. Both tables exist, and both are published into.

So a generated-format file with no generator is not dead code by default. asset_classes is proof that this class of finding must be resolved one table at a time, against the live database, and never deleted in bulk.

Refined on 2026-09-26. The 13 tables do have a generator, and it is retired. They are exactly the FpML codelist entities: fpml_parser.py declares 15 in ENTITY_FILE_PATTERNS, two of them (account_types, business_centres) were migrated to org models in iam and refdata, and the remaining 13 are the 13 tables above. The generator does not render SQL itself; generate_entity_model writes <plural>_entity.json, and codegen no longer discovers JSON models. So these tables are stale output of a pathway that was decommissioned, and refdata_asset_classes_create.sql shows the staleness in its body: the pre-codegen new. casing, current_timestamp, and no version-replace guard. The fix is to port the 13 to org models, and that port is already owned by capture Migrate FPML, crypto, flags and ip2country onto the org dataset pipeline. See the task's FPML section.

create/dq holds 21 handcrafted *_population_functions_create.sql files: 20 entity files plus one aggregate. They carry the DQ-side preview and publish functions, which are the data half of the artefact pipeline.

A template for this work exists: sql_populate_function_refdata.mustache. It is not dead text. It renders a preview function and a publish function for an entity with entity_plural and entity_singular.

It is nevertheless dead in practice. The facet ores.sql.populate declares ten archetypes, and this template is not among them. No file anywhere in the repository carries its banner. So the generic publication functions generate nothing, while 21 files implement the same idea by hand.

This is the largest single piece of automation available in the component. It is also the one with the most variation to absorb, because the handcrafted functions differ per entity.

Two table names are declared twice, once by a generated file and once by a handcrafted file:

In both pairs the generated file is unreachable. create/dq/dq_create.sql includes the handcrafted file and omits the generated one. The same inversion holds for the drop scripts: drop/dq/dq_catalogs_drop.sql and drop/dq/dq_subject_areas_drop.sql exist, and drop/dq/dq_drop.sql includes neither.

The notify trigger for both tables is generated and is wired. So each entity was migrated part of the way, and the table was left behind. This is the H02 signature: a hand-written duplicate of a generated artefact.

Resolved on 2026-09-26, in step 6 of the task. The generated plurals are now wired, the handcrafted twins are deleted, and the debt register behind them was larger than this finding: validation_ignore.txt recorded 49 WIRE_001 files for DQ, in five groups. All 49 are cleared and the check passes with no suppression. See the task's Step 6 results. One correction: the drop files are generated, but neither drop archetype emits an AUTO-GENERATED marker, so a drop file's provenance cannot be read from its header. That is a separate G03 defect.

The survey classified 651 non-test C++ files:

Only one of the 614 files outside tests carries the generated marker. That number does not mean the code is handcrafted. It means the tree predates the marker, as the story notes already record (#2087). The drift run settles the point: 517 files differ from what the models produce, of which 410 would change and 107 would be created.

So claim 1 is half right. The artefact and publication SQL is manual, and that is where the models are missing. The C++ is largely model-driven already; it has simply not been regenerated since the marker landed.

The 24 unclassified C++ files are 21 *_changed_event.hpp eventing headers, one workflow header, one enum implementation and the namespace umbrella. The clean standard treats eventing headers as generatable (G01), so the 21 are a finding, not infrastructure.

Give a model to every handcrafted artefact and publication family, then reach byte-identical regeneration. Do the work in this order:

1. Settle the orphan and retired-model tables first. Finding 1 and the 7 orphan tables share one question: does a live entity own this table? Answer it against the live database and the populate tree. Delete only what nothing writes and nothing reads. Never sweep the generated marker as a proxy for liveness — asset_classes disproves that shortcut.
2. Reconcile the 12 divergent tables. Each one is a decision: either the artefact table is wrong and the projection is right, or the model must express a real difference. Record the decision per table.
3. Extend the 6 foreign models. The owning component's model gains the artefact facet. No DQ model is created.
4. Switch the 5 derivable tables to the archetype and delete the handcrafted twins.
5. Revive or retire sql_populate_function_refdata.mustache. It is wired to no facet. Either absorb the 21 handcrafted files into it, or delete it and record why. A template that generates nothing while 21 files do the work by hand is the largest automation gain in the component.
6. Fix the two shadowed pairs (Finding 3) and rewire the aggregators.
7. Regenerate and reach byte-identical. The C++ tree follows from the same run (Finding 4).

Run the 49 items on the tree pass 1 leaves behind. The protocol, wiring, shell, documentation and verification items are cheaper and more honest once the component regenerates cleanly.

Step 1 is done, and it returned the opposite of a deletion list. A consumer census of all 49 tables – scripts/survey_table_consumers.py, 33,010 files scanned – found every table written, read or registered by something in the tree. No artefact table is dead.

That closes the question the Finding 1 tables raised. The 13 generated tables with no model are live, and their base tables live in refdata, iam and reporting, exactly as the archetype intends. They are missing a model, not a reason to exist.

Two qualifications survive, and they are records rather than deletions:

• ores_dq_image_tags_artefact_tbl is written by one function that nothing calls, read by nothing and registered nowhere. It is the closest to dead in the family, and step 3 decides it.
• ores_dq_payment_frequencies_artefact_tbl is registered and read but has no populate script in the repository. The staging source is either external or missing.

• Deletion is destructive and the database is recreated from scratch. A table deleted by mistake is not recoverable from a migration. The step 1 census found nothing to delete, so no deletion is proposed; if one is proposed later, it needs the same census, not a marker test.
• The 517-file drift is a single change of enormous width. Pass 1 should land it in verifiable units, not as one commit, per the sequencing principle.
• The publish path crosses service boundaries. doc/plans/2026-05-14-dq-publish-pattern.org moved publish ownership to the target services. Any change to the artefact tables must keep publish-from-dq working for refdata, assets, reporting and DQ itself.