Task: Find why so much SQL is hand-written and generate what codegen can

A new entity reaches the database without a hand-edited file. Every hand-written SQL file has a recorded reason to be hand-written. What codegen can own, it generates. What stays hand-written is checked by a gate that fails the build.

• Every hand-written file under projects/ores.sql/create, drop and populate is classified by why it is hand-written, and the classification and the command that produced it are in an evidence/ file.
• Each class has a decision: generate it, keep it hand-written with a reason, or delete it.
• The component create and drop aggregates are generated from the models, or the task records why they cannot be.
• The WIRE_001 wiring check, and the other parser warnings, fail CI rather than print.
• Generated drop files carry the same generated marker as the create files, so a count of hand-written files is possible. Both templates write it now, and all 537 generated drop files carry it. A marker alone still cannot count exactly, because 44 files keep one after their generator's input was deleted, so the census script renders the generator's output as well.

No single method fits. The census is an investigation, the CI gate is a feature, and generating the aggregates would be a refactoring whose evidence is an unchanged recreate. This plan sequences them.

The census uses the drift checker's own rendering, not file markers: it renders every catalogue component into a scratch root, and a file is entity codegen output exactly when that root contains it.

Stop and ask before generating any aggregate or moving a hand-written file to codegen. Phase 5 decides, and the operator approves, before that work starts. Stop phase 3 at a recommendation if the hand order encodes dependencies the models do not hold.

Every number this task claims about a tree, with the command that produced it and the commit it was measured at. A number without both is not a measurement, because a clean-up record describes a tree and trunk moves under a branch: a consumer count, a gate's component count and a suite's size all change with no code changing, and the reader of a bare number cannot tell which tree it came from. Where the raw output matters, put it in an evidence/ file beside the task and name that file in the Command column.

The marker-based counts this task was scaffolded with (144 of 754 create scripts, 741 of 741 drop files) are superseded: a marker is missing from every generated drop file and present on 44 files whose generator input no longer exists.

Found while reviewing PR #2500 (today's market round trip). Adding five entities passed the build, the model drift check and every test, and the entities still did not reach the database until two aggregate files were edited by hand.

The aggregates are hand-written in every component. All 22 create/<component>/<component>_create.sql files and their drop twins are written by hand. They hold the \ir includes in dependency order, with prose per block. This is a project-wide convention, not something PR #2500 started.

A check exists and does not fail. validate_component_wiring in projects/ores.codegen/src/plantuml_er_parse_sql.py reports rule WIRE_001 for any create or drop file the root script does not reach. The codegen-drift.yml step runs the parser with --warn and without --strict, so the report only prints. With the five today's market RLS_001 warnings fixed, the tree has zero warnings, so --strict costs nothing today.

Generation looks possible for the aggregates. The models already hold the foreign keys, so a dependency order can be computed by a topological sort. The open questions:

• where the prose per block goes;
• how hand-written files (functions, RLS, service bundles) sit in the same order;
• whether one generated fragment per component is better than generating the whole aggregate.

Generated drop files carry no marker. For example, drop/analytics/analytics_todays_market_configs_drop.sql is generated but has no "generated" line. So the drop count below cannot tell generated from hand-written.

Manual QA scenarios (scaffolded via compass add test_scenario) that verify this task. Link new ones here as they're created; the scenario doc itself links back via its "Verifies task" field.

The hand-written SQL is measured, every class has a decision, and the checks that let two defects reach main are now gates.

The census. Of 1800 SQL files, 1119 are entity codegen output, 164 come from other generators and 517 are hand-written. The method renders the generator's own output rather than trusting markers. Every class, its reason and its decision are in the evidence file, with the scripts.

Decisions.

Why aggregates can be generated. Soft foreign keys impose no creation order: 88 tables are already created before the parent their trigger checks, and every recreate succeeds. Only a table before its trigger, functions from earlier components and hand files that name tables at creation need order.

Gates. The schema parser fails CI on any warning. WIRE_001 now covers every .sql file in the three trees, reached from their roots and from the bootstrap scripts, which found fifteen dead files and two populate scripts that never ran. Both drop templates now write the generated marker the create templates write, so the 537 generated drop files no longer look hand-written. The five real drop gaps are wired and the full drop script runs clean. The census is committed as a tool.

Recorded, not fixed here. Three captures: ores_iam_auth_events_tbl has a tenant_id and no RLS policy despite an ignore reason calling it tenant-scoped; two populate scripts were never wired, and wiring them changes what a recreate seeds; the drop script leaves 115 functions behind.

Component audit. The task touched ores.codegen and ores.sql. The automated half, validate_docs.sh, passes for all 41 components. It added one script to ores.codegen/scripts beside its peers, deleted sixteen dead SQL files and changed no folder, type or component, so the shape, morphology, naming and registry checks have nothing new to judge.

One operational note. The first recreate hung for minutes in DROP DATABASE on ProcSignalBarrier, with this environment's services still reconnecting. Stopping the services, cancelling the drop and retrying worked.