Codegen SQL Debt — Hand-Written Files with Existing Models

Table of Contents

Problem

During the identifier-length cleanup investigation (see 2026-05-13-identifier-length-cleanup) a cross-check was run between projects/ores.codegen/models/ and projects/ores.sql/create/. Two classes of debt were found:

  1. Category A — SQL files that exist and are in use, but were written by hand despite a codegen model (*_domain_entity.json or *_entity.json) covering the same entity. These files have no AUTO-GENERATED FILE - DO NOT EDIT MANUALLY header and will diverge from the template over time.
  2. Category B — Codegen models whose expected SQL output file does not exist at all. These are entities with models but no schema; they are either incomplete implementations or superseded by a differently-named hand-written file.

Neither category blocks the identifier cleanup. For that PR the Category A files are treated as hand-written (Phase 3 sed pass). This plan tracks resolution as separate work.

Remediation work started in PR #747 (investigation/codegen-drift, 2026-05-15). Progress tracked per-item below.

Category A — SQL Exists But Not Codegen-Managed

Originally 24 files; 21 resolved as of 2026-05-16.

IAM (1 remaining of 4)

SQL file Model file Status
iam/iam_account_types_create.sql iam/account_type_entity.json DONE — auto-generated via sql_schema_table_create.mustache (pre-PR #747)
iam/iam_tenants_create.sql iam/tenant_domain_entity.json DONE — investigation/codegen-drift-3 (system_scope + text_code_validations + extra_delete_sets)
iam/iam_tenant_statuses_create.sql iam/tenant_status_entity.json DONE — auto-generated via sql_schema_table_create.mustache (pre-PR #747)
iam/iam_tenant_types_create.sql iam/tenant_type_entity.json DONE — auto-generated via sql_schema_table_create.mustache (pre-PR #747)

DQ (0 remaining of 1)

SQL file Model file Status
dq/dq_artefact_types_create.sql dq/artefact_type_entity.json DONE — auto-generated via sql_schema_table_create.mustache (pre-PR #747)

Reporting (1 remaining of 4)

SQL file Model file Status
reporting/reporting_concurrency_policies_create.sql reporting/concurrency_policy_domain_entity.json DONE — PR #747
reporting/reporting_report_definitions_create.sql reporting/report_definition_domain_entity.json DONE — PR #752 (template extended with prefix_columns + soft_fk_validations)
reporting/reporting_report_instances_create.sql reporting/report_instance_domain_entity.json DONE — investigation/codegen-drift-3 (fk_copy_validations + nullable soft_fk)
reporting/reporting_report_types_create.sql reporting/report_type_domain_entity.json DONE — PR #747

Scheduler (1 remaining of 1)

SQL file Model file Status
scheduler/scheduler_job_definitions_create.sql scheduler/job_definition_domain_entity.json DONE — investigation/codegen-drift-3 (nullable_tenant_id mode + null-safe version management)

Trading (1 remaining of 14)

SQL file Model file Status
trading/trading_balance_guaranteed_swap_instruments_create.sql trading/balance_guaranteed_swap_instrument_domain_entity.json DONE — PR #747
trading/trading_business_day_convention_types_create.sql trading/business_day_convention_type_domain_entity.json DONE — PR #747
trading/trading_callable_swap_instruments_create.sql trading/callable_swap_instrument_domain_entity.json DONE — PR #747
trading/trading_cap_floor_instruments_create.sql trading/cap_floor_instrument_domain_entity.json DONE — PR #747
trading/trading_day_count_fraction_types_create.sql trading/day_count_fraction_type_domain_entity.json DONE — PR #747
trading/trading_floating_index_types_create.sql trading/floating_index_type_domain_entity.json DONE — PR #747
trading/trading_fra_instruments_create.sql trading/fra_instrument_domain_entity.json DONE — PR #747
trading/trading_inflation_swap_instruments_create.sql trading/inflation_swap_instrument_domain_entity.json DONE — PR #747
trading/trading_knock_out_swap_instruments_create.sql trading/knock_out_swap_instrument_domain_entity.json DONE — PR #747
trading/trading_leg_types_create.sql trading/leg_type_domain_entity.json DONE — PR #747
trading/trading_payment_frequency_types_create.sql trading/payment_frequency_type_domain_entity.json DONE — PR #747
trading/trading_swaption_instruments_create.sql trading/swaption_instrument_domain_entity.json DONE — PR #747
trading/trading_trades_create.sql trading/trade_domain_entity.json DONE — investigation/codegen-drift-3 (party_id_from_book_id + use_no_tenant soft FK)
trading/trading_vanilla_swap_instruments_create.sql trading/vanilla_swap_instrument_domain_entity.json DONE — PR #747

Remediation per file

For each remaining file:

  1. Run codegen to get the template's current output.
  2. Diff against the hand-written file.
  3. If the diff reveals custom columns, constraints, or trigger logic not in the template: extend the model JSON or template as required.
  4. Replace the hand-written file with the codegen output.
  5. Verify recreate_database.sh still succeeds.

IAM tenants should be done last (most complex custom logic).

Category B — Model Exists But No SQL File

Originally 9 files; all resolved as of 2026-05-16.

DQ (0 remaining of 4)

Expected SQL path Model file Status
dq/dq_dataset_bundles_create.sql dq/dataset_bundle_domain_entity.json RESOLVED — hand-written dq_dataset_bundle_create.sql (singular) is the intended file
dq/dq_lei_entities_create.sql dq/lei_entities_entity.json RESOLVED — entity.json generates _artefact_create.sql only; artefact file already codegen-managed
dq/dq_lei_relationships_create.sql dq/lei_relationships_entity.json RESOLVED — entity.json generates _artefact_create.sql only; artefact file already codegen-managed
dq/dq_report_definitions_create.sql dq/report_definitions_entity.json RESOLVED — entity.json generates _artefact_create.sql only; artefact file already codegen-managed

Database (0 remaining of 1)

Expected SQL path Model file Status
database/database_database_infos_create.sql database/database_info_domain_entity.json RESOLVED — hand-written utility/utility_database_info_create.sql covers this

Trading (0 remaining of 4)

Expected SQL path Model file Status
trading/trading_lifecycle_events_create.sql trading/lifecycle_event_domain_entity.json DONE — generated and wired in PR #747
trading/trading_rpa_instruments_create.sql trading/rpa_instrument_domain_entity.json RESOLVED — hand-written file exists; model and SQL aligned
trading/trading_trade_identifiers_create.sql trading/trade_identifier_domain_entity.json RESOLVED — hand-written file exists; model and SQL aligned
trading/trading_trade_party_roles_create.sql trading/trade_party_role_domain_entity.json RESOLVED — hand-written file exists; model and SQL aligned

Remediation for remaining items

For each remaining Category B file, determine whether:

  • (a) The model is correct and the SQL was never generated: run codegen, wire the file into the relevant *_setup.sql or include list, validate.
  • (b) The model is superseded by a differently-named hand-written file: reconcile the name and either rename or update entity_plural in the model.
  • (c) The model is stale and the entity will not be implemented: delete the model file.

Remaining Work (as of 2026-05-17)

Category A — all 4 files resolved in investigation/codegen-drift-3.

Category B — all resolved.

All Category A SQL debt is now cleared. Every *_create.sql file in projects/ores.sql/create/ for entities that have a codegen model is now auto-generated and carries the AUTO-GENERATED FILE header.

Impact on Identifier Cleanup

All files are now codegen-managed. The Phase 3 sed pass (identifier cleanup) should target only files that still lack the AUTO-GENERATED FILE header.

Category B files have no SQL, so they produce no truncation notices and require no action in the cleanup branch.

Execution Order

COMPLETE — all 4 remaining Category A files converted in investigation/codegen-drift-3:

  1. reporting_report_instances — fk_copy_validations + nullable soft_fk template extensions
  2. trading_trades — party_id_from_book_id + use_no_tenant soft FK
  3. scheduler_job_definitions — nullable_tenant_id mode + null-safe version management
  4. iam_tenants — system_scope + text_code_validations + extra_delete_sets

Date: 2026-05-14

Emacs 29.1 (Org mode 9.6.6)