Mostly Wiki

branch-expansion-enriched-view

2 min read

Branch-expansion accounts in projects_enriched (migration 037)

Date: 2026-08-07 · Commit: entirely-api f8a9829 (renumbered to 037 in e80691b) · Status: DONE — code deployed AND DDL applied to prod 2026-08-07 (via Supabase MCP apply_migration)

What

The projects_enriched branch_name/branch_code CASEs (last rebuilt by migration 029) predate four accounts: Groundworks Hooksett (2026-04-08) and the JES expansion Virginia Beach / Roanoke / West Virginia (2026-06-07). Their projects render branch Unknown / ???, so every branch-grouped consumer misfiles them — most importantly the pipeline shepherd’s per-branch scheduler digests (_send_branch_scheduler_digests, 3d6afad), which silently skip Unknown.

Findings (prod, 2026-08-07)

  • Gap is latent, not active: all four accounts hold zero projects. VAB-9000..9011 are parked on ACCOUNT_UNLIKELY_PRO (PPDR cert-routing arrangement), all Closed.
  • Exactly 1 row renders Unknown today: Dustin’s own house record (6235 Lawyers Hill Rd, null account_ss_id) — expected, unchanged by 037.
  • open_rfis_by_branch needs no change: it selects pe.branch_name from projects_enriched, so it inherits the fix.

Changes

  • migrations/037_branch_expansion_enriched_view.sql — extends both CASEs (labels = config.ACCOUNT_NAMES, codes = ACCOUNT_PROJECT_PREFIX: VAB/ROA/WVA/HKS), re-asserts security_invoker = true (017 hardening).
  • pipeline_shepherd._BRANCH_LABEL_ACCOUNTS — four labels added; resolution goes through ESCALATION_ROUTING (VBeach/Roanoke/WVA → Stephaney, Hooksett → Jeff Corso), same interim contacts as RFI escalation. Inaccurate map comment + digest docstring corrected. Inert until 037 is applied.
  • Tests: label↔account↔ACCOUNT_NAMES consistency net + VBeach digest routing (suite 575 passed).

Applied + verified (2026-08-07, same day)

Applied via Supabase MCP apply_migration as branch_expansion_enriched_view. Verified: all branches labeled; Unknown count exactly 1 (the null-account house record); open_rfis_by_branch intact (1,483 rows).

Bonus find: pre-apply, the live view’s reloptions were NULL — migration 029’s CREATE OR REPLACE had silently dropped 017’s security_invoker hardening (Postgres resets unspecified view options on replace). 037’s inline WITH (security_invoker = true) restored it. Lesson for future view migrations: always carry the WITH clause in CREATE OR REPLACE VIEW.

Graph View