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":
- RLS policies: add a codegen facet (e.g.
ores.sql.rls-policy) that generatesENABLE ROW LEVEL SECURITY+ a tenant-isolationCREATE POLICYfor 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_idis 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. - Create/drop aggregator wiring: each component's
<component>_create.sql=/=_drop.sql(e.g.marketdata_create.sql) is a hand-maintained, ordered list of\irincludes – codegen writes e.g.marketdata_crm_topology_configs_create.sqlbut never adds the\ir ./marketdata_crm_topology_configs_create.sqlline that makes it reachable fromsetup_schema.sql. A brand-new entity's tables silently don't exist aftercompass db recreateuntil 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":
- RLS policies: add a codegen facet (e.g.
ores.sql.rls-policy) that generatesENABLE ROW LEVEL SECURITY+ a tenant-isolationCREATE POLICYfor 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_idis 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. - Create/drop aggregator wiring: each component's
<component>_create.sql=/=_drop.sql(e.g.marketdata_create.sql) is a hand-maintained, ordered list of\irincludes – codegen writes e.g.marketdata_crm_topology_configs_create.sqlbut never adds the\ir ./marketdata_crm_topology_configs_create.sqlline that makes it reachable fromsetup_schema.sql. A brand-new entity's tables silently don't exist aftercompass db recreateuntil 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\iraggregators.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
- Add RESTRICTIVE party-isolation RLS policies for the marketdata tables – the concrete instance of this gap this capture generalises from.
- Story: Cross-rates matrix (CRM) – where the third recurrence was hit.