Story: Fix cross-tenant badge lookup: stale generated repositories ignore tenant_read_scope: shared

Table of Contents

This page documents a story in Sprint 24. It captures the goal, current status, acceptance criteria, and the tasks that compose it.

Goal

Found by brave_hopper: every badge in the UI renders with the default/fallback colour, for every entity, in every tenant except the system tenant.

Root cause: badge_definition_repository.cpp and badge_mapping_repository.cpp (ores.dq.core) hard-code an app-level WHERE tenant_id = ctx.tenant_id() filter on every read method – narrower than the RLS policy on both tables, which already permits: 

using (tenant_id = ores_iam_current_tenant_id_fn() OR tenant_id = ores_utility_system_tenant_id_fn())

Badge definitions/mappings are seeded once under the system tenant (ffffffff-ffff-ffff-ffff-ffffffffffff) as shared reference data meant to be visible to every tenant. RLS would let the system-tenant rows through; the repository's own narrower WHERE clause silently returns zero rows first.

Verified live: ores_dq_badge_definitions_tbl=/=ores_dq_badge_mappings_tbl have 63/114 rows, all tenant_id = ffffffff-.... BadgeCache on login logs "Fetched 0 badge definitions" / "Fetched 0 badge mappings" for a non-system tenant; server-side, ores.dq.service's own log shows "Reading latest badge definitions… Total: 0".

The fix mechanism already exists, just not applied consistently: codegen has a tenant_read_scope: shared entity-model flag (ores.codegen/src/codegen/core.py, search read_tenant_filtered) specifically built to omit the tenant filter on reads for exactly this class of table.

  • ores.dq.badge_definition.org – has tenant_read_scope: shared already, but the generated .cpp is stale, never regenerated since the flag was added.
  • ores.dq.badge_severity.org – same flag, same staleness; not directly verified live but the generated repository has the same hard-coded filter.
  • ores.dq.badge_mapping_junction.org – does not have the flag set at all.

Status

Field Value
State DONE
Parent sprint Sprint 24
Now Nothing.
Waiting on Nothing.
Next Nothing.
Last touched 2026-07-30

Acceptance

  • [X] Confirmed live whether the reported symptom (badges rendering with the fallback colour for non-system tenants) still reproduces on current main. It does not: tenant provisioning was updated upstream (independently of this story) to publish each tenant's own copy of the badge catalogue via the standard DQ bundle-publish mechanism, so every tenant now has its own badge_definition=/ =badge_severity=/=badge_mapping rows and never depends on the system-tenant RLS fallback this story set out to fix.
  • [X] tenant_read_scope: shared reverted for badge_definition=/ =badge_severity after live QA (see * Decisions) found it causes a new regression given the above: a tenant now has both its own copied rows and (via RLS's own-OR-system-tenant fallback) the original system-tenant rows visible simultaneously, so admin list views (Badge Definitions, Badge Severities) show duplicates with no dedup.

Tasks

Task State Start End Description
Add tenant_read_scope: shared to badge_mapping_junction, regenerate all three badge repositories DONE 2026-07-29 2026-07-30 Investigated, fixed, then reverted after live QA found a regression given an independent upstream provisioning change – see task Result.
Support tenant_read_scope: shared in the junction repository template ABANDONED     Motivating case (badge_mapping) no longer needs shared-tenant reads: tenants now get their own copy via bundle-publish. No other junction currently needs this.

Decisions

Reverted tenant_read_scope: shared for badge_definition/badge_severity

Live QA (doc/agile/versions/v0/sprint_23/badge-colour-scheme-support/scenario_verify-badge-tenant-scoping.org) against a freshly rebased, freshly provisioned Barclays Plc tenant found the originally-reported symptom (Total: 0, badges rendering with the fallback colour) no longer reproduces: tenant provisioning now publishes the base bundle's ore.badge_definitions=/ =ore.badge_severities=/=ore.badge_mappings datasets into each tenant's own rows (confirmed via direct SQL: Barclays' tenant has its own 62/116 rows alongside the system tenant's 63/114). This landed independently, upstream, sometime in the ~283 commits between when this story was filed and when its fix was live-tested.

Given that, tenant_read_scope: shared's RLS-fallback mechanism is no longer the operative fix for these two entities – and applying it anyway is actively harmful: with a tenant now holding its own copy and RLS still permitting the system-tenant row through, read_latest() (no dedup, no natural-key tie-break) returns both, so every code shows up twice in the Badge Definitions/Severities admin lists. Verified live via the QA scenario above (steps 2-3 FAILED with exactly this symptom before the revert).

Reverted the tenant_read_scope: shared flag and regenerated both repositories back to their original tenant-filtered reads (diff against origin/main is a clean, exact match modulo the harmless model-shape migration below). Also reverted the core.py junction read_tenant_filtered computation added to support the (now-abandoned) follow-up task – no other junction currently has a motivating use case for it.

Kept: primary-key/natural-key model-shape migration

Unrelated prerequisite discovered along the way, independent of the tenant-scope question: badge_definition.org=/=badge_severity.org were on the legacy * Primary key=/=* Natural keys heading shape, which the current org_loader.py no longer reads at all – confirmed repo-wide (~61 of 120 ores.codegen.entity models are in the same state; regenerating any of them fails the same validation). Migrated just these two to the Columns + :primary_key:=/ =:natural_key: true shape (mirroring ores.dq.subject_area.org), since it's a real, harmless fix that unblocks any future regeneration of these two entities regardless of what happens with tenant scoping. The wider repo-wide migration is out of scope here (see * Out of scope).

Out of scope

Emacs 29.3 (Org mode 9.6.15)