Skip to main content

PowerFill Phase 6a — Post-Architect Arc and First End-to-End Run Against Real Customer Data

Date: 2026-04-17 (afternoon / evening) Agents: Collaborator (review + post-Architect investigation), PO (push, GRANT EXECUTE, hypothesis on data lineage) Scope: Collaborator review of Phase 6a; resolution of 6a-PERM-1 (Azure SQL MI EXECUTE permission gap); discovery and resolution of 6a-DATA-1 (legacy plugin auto-migration div_by); creation of new deploy script 007_BackfillLegacyPluginMigrations.sql; first successful end-to-end POST /api/powerfill/run against PS_DemoData; codification of the legacy-plugin-migration pattern as assumption A44.

This entry continues the morning Phase 6a Architect commit narrative. Read that first for full context.

Why this entry exists​

The morning Architect session shipped Phase 6a code complete with two known escalations: 6a-PERM-1 (PSSaaS service account lacked EXECUTE on dbo procs against PS_DemoData) and R-DATA-1 (PS_DemoData lacks trades for the FHLMC small-balance constraint instruments). The afternoon arc resolved 6a-PERM-1, surfaced and resolved a previously-unknown 6a-DATA-1, and produced the first successful POST /api/powerfill/run against real customer data (run_id ae32f0b2-07b6-4a6a-bb5b-b7b25b6f23af, 1.63s, all 3 steps succeeded).

The arc generated material that was at risk of staying conversational rather than durable. This entry persists it.

Timeline (compressed)​

  1. Collaborator review of Phase 6a completed — verdict: approved. Spot-checked 4 commits, verified A41 NVO citation (NUMERIC(11,8) at NVO 12837), inspected 006/Loan.cs/RunService/CandidateBuilder/spec amendment/A41-A43 entries; ran live POST /run against PS_DemoData (HTTP 500 + structured RunResponse with SqlException 229 matching the 6a-PERM-1 escalation verbatim); ran live POST /candidates/preview (HTTP 200 + 0 candidates per R-DATA-1).
  2. PO pushed the 4 Phase 6a commits to origin/main (c3313c0 → f92edb2 → 3de76d7 → 57c8f19). CI build-api + build-docs + deploy-staging green. Staging API sentinel rolled to phase-6a-candidate-builder-ready. Pod digests captured.
  3. PO resolved 6a-PERM-1 directly by logging into Azure Data Studio with their powerseller.com Entra ID account (which has db_owner on PS_DemoData) and running GRANT EXECUTE ON SCHEMA::dbo TO kevin_pssaas_dev. No external escalation needed.
  4. Collaborator re-ran POST /run to verify GRANT — SqlException 229 gone, new SqlException 207: Invalid column name 'div_by'. surfaced from psp_pfill_bx_settle_and_price. New finding logged as 6a-DATA-1.
  5. PO hypothesized data lineage: "The customer that data used to belong to probably never used a version of PowerFill that created that table; Joe loaded PS_DemoData and upgraded it from 7.x to 8.x as a schema-only operation."
  6. Collaborator investigated: Grep for div_by in NVO returned exactly one creation site (n_cst_powerfill.sru:6577 in the initial create branch + NVO 6592 in the "table exists but missing column" fixup branch) and four usage sites inside psp_pfill_bx_settle_and_price (NVO 11618, 11620, 11673 + table DDLs at 11594, 11602, 11649, 11657). PO hypothesis confirmed. The legacy PowerFill plugin performs opportunistic schema migrations on first window-open; PS_DemoData never had the plugin opened against it.
  7. Collaborator wrote 007_BackfillLegacyPluginMigrations.sql — idempotent (COL_LENGTH guard); skip-safe when rmusr_payups is absent (local pssaas-db); A32 PRINT lines on every branch; full header documenting the legacy-plugin-migration pattern as a category of finding.
  8. Deployed 007 against PS_DemoData — div_by NUMERIC(5,0) NULL added; second run hit the "already exists, skipped ALTER" branch (idempotency confirmed); column verified via sys.columns query.
  9. Deployed 007 against local pssaas-db — skip-safe path triggered cleanly: rmusr_payups does not exist (local-dev / unseeded tenant) — skipping div_by backfill.
  10. Re-ran POST /run against PS_DemoData — status: Complete in 1.63s. All 3 steps succeeded. 3 FHLMC small-balance constraints iterated. 0 candidates per R-DATA-1 (data shape, not code defect — and now squarely a 6b concern).
  11. Codified the pattern as A44 in the assumptions log — "Legacy PowerFill-Plugin Auto-Migrations Are a Tenant-DB Schema Concern PSSaaS Must Own". Cites NVO; documents resolution; flags the implication for the broader port philosophy (ADR-006/021 implicitly transfer schema-migration responsibilities to PSSaaS at the moment of proc-body inheritance).

Decisions made​

#DecisionRationaleWhere
D9Backfill div_by via PSSaaS deploy script (not ad-hoc ALTER)Idempotent, reviewable, repeatable for tenant onboarding. The script becomes a permanent artifact and a template for future plugin-migration findings.007_BackfillLegacyPluginMigrations.sql
D10Use 007_*.sql naming (not 005_* which was taken by Phase 4 ROWVERSION)File-numbering preservation per existing convention.New file under Sql/
D11Skip-safe semantics when base table absent (PRINT message, no error)Local pssaas-db doesn't have rmusr_payups per A42; deploy-script must not fail there. Same pattern matches the Desktop App's plugin: it only ALTERs if the table exists, otherwise creates it.IF OBJECT_ID(N'dbo.rmusr_payups', N'U') IS NULL guard
D12Document the broader pattern as A44 (not just A43-style "this one column" entry)The PO hypothesis surfaced a class, not an instance. Future PSSaaS work will hit more of these; A44 establishes the vocabulary and the resolution pattern.powerfill-assumptions-log.md A44

Findings (Primary-Source Verification Gate output)​

IDFindingDisposition
6a-PERM-1 (Phase 6a escalation)kevin_pssaas_dev had db_ddladmin but lacked EXECUTE on dbo-owned procs on PS_DemoData(a) RESOLVED — PO ran GRANT EXECUTE ON SCHEMA::dbo TO kevin_pssaas_dev via Azure Data Studio with their db_owner Entra ID account
6a-DATA-1 (NEW, post-GRANT)SqlException 207: Invalid column name 'div_by' from psp_pfill_bx_settle_and_price against PS_DemoData(a) RESOLVED in 007 — root cause: legacy plugin auto-migration NVO 6577/6592 never ran on PS_DemoData because nobody opened the legacy PowerFill window after Joe's 7.x→8.x upgrade. Codified as A44.
Process discipline observationThe POST /run end-to-end exercise surfaced a class of finding the planning-time and implementation-time gates couldn't catch — NVO-vs-tenant-DB driftA44 §"Process discipline note" suggests refining the Primary-Source Verification Gate canonical text to enumerate three layers: NVO-vs-doc, NVO-vs-implementation, NVO-vs-tenant-DB. Not yet a v3.1 nomination — banking for now.

What's now true that wasn't this morning​

  • Phase 6a PoC criterion upgraded from "code-correct, gated on data" to "fully runnable end-to-end against PS_DemoData."
  • POST /api/powerfill/run returns status: Complete in 1.63s against real customer data.
  • R-DATA-1 (zero FHLMC small-balance trades) is now squarely a Phase 6b data-shape question, not a Phase 6a code-correctness question.
  • 007_BackfillLegacyPluginMigrations.sql exists as the canonical home for plugin-managed schema migrations PSSaaS now owns.
  • A44 establishes the legacy-plugin-migration pattern as a named category of finding (paralleling A28 syn-tables auto-create and A37 pfill_powerfill_log auto-create — the same family at different layers).
  • Tenant-onboarding artifact is partially in place for Phase 6e: 001-007 is now the canonical PowerFill deploy sequence for any new tenant DB.

A44 explicitly recommends an Architect session that enumerates every _set_addcolumn call in NVO 6500-6700 and decides which are 6a/6b/6c/6d-relevant vs Phase 7+-relevant. Each gets a row in 007_*.sql. Estimated 1-2 hours of Architect time. Closes a class of latent finding before 6b's allocation work surfaces another mid-pass.

If PO defers, the alternative is to discover them serially in 6b (which historically means surfacing ~1-2 mid-implementation per sub-phase, per the Phase 6 retro pattern).

Tooling note: WSL Ubuntu​

Per PO direction during this arc, future shell work should prefer WSL Ubuntu (bash) over PowerShell. Ubuntu is installed and running. Apt installs of jq, kubectl, dotnet-sdk-8.0, and gh are recommended (require interactive sudo password — deferred to PO at next opportunity).

The PowerShell-vs-bash impact has been concrete: today's session burned ~10 minutes on PowerShell quirks ($$ expansion in passwords, JSON-brace URL parsing, $body.Length returning line count instead of byte count, heredoc unsupported syntax). All of these vanish under bash.

Process discipline observations (banked for next revision)​

  1. PO hypothesis as primary source — the "customer probably never used PowerFill on this DB" framing was the right empirical anchor for the investigation. The Collaborator confirmed the hypothesis via NVO grep in seconds; without that anchor, the investigation might have wandered through "is the proc transcription wrong?" or "is there a Phase 6a regression?" speculation. Worth naming as a small countermeasure: "PO domain hypothesis as gate-input — when PO supplies a domain hypothesis, treat it as a Primary-Source Verification Gate input alongside NVO."
  2. Three-layer Primary-Source Verification Gate refinement — A44 §"Process discipline note" articulates this. NVO-vs-doc and NVO-vs-implementation are the existing two; NVO-vs-tenant-DB is the third, surfaced today. Worth folding into the next process-discipline revision.
  3. PowerShell quirks as a measurable cost — multiple instances today ($body.Length, $$ expansion, JSON-brace URL parsing) — collectively crossed the threshold where switching to bash via WSL is the dominant strategy. The existing antipattern Empirical-Citation Type Mismatch generalizes: "shell idioms that look like they're measuring/transmitting the right thing but aren't" is a parallel. Banking; not yet ready to nominate.

Cross-references​

End of Phase 6a PoC arc devlog. The next planned milestone is Phase 6b kickoff (multi-pass allocation engine), prerequisite-blocked on PO answering Q4 (multi-pass semantics — Architect default Option A is empirical NVO trace).