Generalize SQL include-reachability validation beyond RLS

Table of Contents

This page is a capture in the inbox bucket of the product backlog — a pre-sprint idea, not yet pulled into a sprint as a story.

What

projects/ores.codegen/src/plantuml_er_parse_sql.py's parse_create_dir=/ =parse_drop_dir discover files via rglob('*_create.sql')=/ =rglob('*_drop.sql') — a raw filesystem scan of every matching file, regardless of whether the file is actually \ir-included (directly or transitively) from the real entry points (setup_schema.sql -> create/create.sql -> create/refdata/refdata_create.sql, and the drop equivalent). So every discovered file's own content is validated for convention compliance, but nothing checks whether the orchestrator chain actually reaches it. Generalize the reachability walk that validate_rls_policies() already does for *_rls_policies_create.sql (RLS_003: file exists but isn't reachable from rls/rls_create.sql) to cover every *_create.sql, *_drop.sql, and *_notify_trigger_{create,drop}.sql file under create/ and drop/, per component.

Why

Found while investigating why saving a rounding_type record didn't fire a live-eventing notification: its refdata_rounding_types_notify_trigger_create.sql file existed on disk (correctly codegen-generated, matching every other entity's pattern) but was never \ir-included from create/refdata/refdata_create.sql — a plain human oversight at some point in the past, unrelated to any of this session's own changes. validate_schemas.sh runs on every commit/CI cycle and is supposed to catch exactly this class of drift, but it never noticed because it scans the filesystem for matching files rather than walking the \ir chain from the real entry point. The same investigation also found 15 more refdata entities (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, supervisory_bodies) missing from refdata_drop.sql entirely (not just their notify trigger — the whole entity), which a general reachability check would also have caught immediately instead of requiring a manual grep sweep.

References

  • Task: Migrate ores.qt.refdata's history dialogs to the generic HistoryDialog — where the missing rounding_type notify trigger was found and fixed (create-side; also fixed the drop-side gaps for rounding_type and the 15 other entities as a drive-by).
  • projects/ores.codegen/src/plantuml_er_parse_sql.py=§=validate_rls_policies — the existing reachability-check pattern to generalize.
  • projects/ores.sql/utility/validate_schemas.sh — the script that should have caught this.

See also

Emacs 29.3 (Org mode 9.6.15)