Task: Codegen leaves new entities unwired: RLS policies and the create/drop aggregators

Table of Contents

This page documents a task in the Close systemic codegen gaps story. It captures the goal, current status, acceptance, and any notes or results.

Goal

Two related gaps, both "codegen writes a new entity's SQL but something still has to be hand-wired afterwards":

  1. RLS policies: add a codegen facet (e.g. ores.sql.rls-policy) that generates ENABLE ROW LEVEL SECURITY + a tenant-isolation CREATE POLICY for any entity whose model declares :has_tenant_id: true – mirroring the existing per-table hand-written blocks in each component's *_rls_policies_create.sql=/=_drop.sql (e.g. marketdata_rls_policies_create.sql). A stretch goal is a second policy for :has_party_id:-equivalent entities (party-level RESTRICTIVE isolation), which today has no first-class model flag at all – party_id is currently just an ordinary natural-key column, indistinguishable in the model from any other UUID key, so codegen has no signal to key a party-RLS facet off even if the tenant-RLS facet existed.
  2. Create/drop aggregator wiring: each component's <component>_create.sql=/=_drop.sql (e.g. marketdata_create.sql) is a hand-maintained, ordered list of \ir includes – codegen writes e.g. marketdata_crm_topology_configs_create.sql but never adds the \ir ./marketdata_crm_topology_configs_create.sql line that makes it reachable from setup_schema.sql. A brand-new entity's tables silently don't exist after compass db recreate until someone notices and wires them in by hand, in the correct dependency order (create: referenced-before-referencing; drop: reverse). Possibly fixable without a new facet at all – codegen could rewrite/append to the aggregator file as part of generating an entity's SQL facet, the same way it already writes the SQL files themselves.

Status

Field Value
State BACKLOG
Parent story Close systemic codegen gaps
Now Not yet started.
Waiting on Nothing.
Next Begin implementation.
Last touched 2026-08-06

Acceptance

Plan

(Implementation strategy. Written when work starts; key decisions are distilled into the parent story's * Decisions at close, but the plan itself stays — it is the historical record of what we did.)

Notes

Test Scenarios

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

Scenario State Notes
     

PRs

PR Title
   

Review

Comment summary File Decision Notes
       

Result

Promoted from capture

Captured 2026-07-12 in the product backlog; promoted preserving the UUID.

What

Two related gaps, both "codegen writes a new entity's SQL but something still has to be hand-wired afterwards":

  1. RLS policies: add a codegen facet (e.g. ores.sql.rls-policy) that generates ENABLE ROW LEVEL SECURITY + a tenant-isolation CREATE POLICY for any entity whose model declares :has_tenant_id: true – mirroring the existing per-table hand-written blocks in each component's *_rls_policies_create.sql=/=_drop.sql (e.g. marketdata_rls_policies_create.sql). A stretch goal is a second policy for :has_party_id:-equivalent entities (party-level RESTRICTIVE isolation), which today has no first-class model flag at all – party_id is currently just an ordinary natural-key column, indistinguishable in the model from any other UUID key, so codegen has no signal to key a party-RLS facet off even if the tenant-RLS facet existed.
  2. Create/drop aggregator wiring: each component's <component>_create.sql=/=_drop.sql (e.g. marketdata_create.sql) is a hand-maintained, ordered list of \ir includes – codegen writes e.g. marketdata_crm_topology_configs_create.sql but never adds the \ir ./marketdata_crm_topology_configs_create.sql line that makes it reachable from setup_schema.sql. A brand-new entity's tables silently don't exist after compass db recreate until someone notices and wires them in by hand, in the correct dependency order (create: referenced-before-referencing; drop: reverse). Possibly fixable without a new facet at all – codegen could rewrite/append to the aggregator file as part of generating an entity's SQL facet, the same way it already writes the SQL files themselves.

Why

Every entity with :has_tenant_id: true structurally needs a tenant isolation RLS policy, but no codegen template emits one – it is a purely manual step, hand-added to a shared, non-generated *_rls_policies_create.sql file per component. This is easy to forget: doc/agile/product_backlog/next/commit_83292021a_added_party_id_to_ores_marketdata.org already tracks four marketdata tables that shipped with party_id but no matching RESTRICTIVE policy (a validate_schemas.sh --strict RLS_002 stopgap, entries in projects/ores.sql/utility/validation_ignore.txt). While wiring the Cross-rates matrix (CRM) into ores.marketdata, three new tenant+party-scoped tables (crm_topology_config, crm_driver_pair, crm_enabled_derived_pair) needed the same hand-added tenant-isolation policy just to pass RLS_001, and were added to the same RLS_002 ignore list for the party-level gap – the exact same class of manual step, repeated a third time.

Recurred a fourth time (tenant-isolation only, no party dimension) in book_purpose_type and ledger_feed_type (see Add book_purpose_type lookup entity to book and Add ledger_feed_type lookup entity to book) – both merged without RLS policies, silently missed since validate_schemas.sh was never run as part of either task, and only surfaced when is_sweepable's task ran it explicitly. Notably, book_status=/=regulatory_book_type (their older siblings) are also missing RLS to this day – this is not just a per-entity oversight, it is a systemic gap across every :has_tenant_id: true refdata lookup table in the codebase, reinforcing that a codegen facet (not another reminder to hand-wire it) is the right fix.

The aggregator gap is worse in one respect: it isn't caught by any validation script. compass db recreate silently succeeds even when a new entity's tables were never created – the first symptom is a confusing "relation does not exist" much later (in this case, from the RLS policy statement referencing the never-created table), not an error pointing at the actual missing \ir line. Both gaps were hit, in this order, while adding the same three CRM tables: RLS first (caught by validate_schemas.sh), then the aggregator (only caught by actually running db recreate and reading the failure).

References

  • projects/ores.sql/create/marketdata/marketdata_rls_policies_create.sql – the hand-maintained file every component's tenant-isolation policies currently live in.
  • projects/ores.sql/create/marketdata/marketdata_create.sql / projects/ores.sql/drop/marketdata/marketdata_drop.sql – the hand-maintained \ir aggregators.
  • projects/ores.sql/utility/validate_schemas.sh – the RLS_001/RLS_002 checks that catch (but don't prevent) the RLS gap; nothing catches the aggregator gap.
  • projects/ores.sql/utility/validation_ignore.txt – the growing stopgap list.

See also

Emacs 29.3 (Org mode 9.6.15)