Skip to main content

Pre-6b Architect Sweep — Legacy PowerFill-Plugin Auto-Migrations Enumerated and Closed

Date: 2026-04-18 Agent: Systems Architect (interpretation-heavy sweep; SQL writing self-implemented per Deliberate Non-Delegation justification — see completion report) Scope: Enumerate every _set_addcolumn / _create_table / _create_index call inside n_cst_powerfill.sru::of_update_database (the legacy plugin's first-window-open schema-migration function); classify each for sub-phase 6 relevance; ship in-scope findings as PSSaaS deploy-script entries before sub-phase 6b's allocation work surfaces them mid-pass.

This entry continues the PoC-complete arc from 2026-04-17 PM. Read that first for full context on assumption A44 and the discovery that motivated this sweep.

Why this entry exists​

A44 (codified 2026-04-17) named the legacy-plugin-migration pattern as a class of finding and explicitly recommended "an Architect session that enumerates every _set_addcolumn call in NVO 6500-6700" before sub-phase 6b ships. This session is that sweep. The deliverables are durable: the extended 007_*.sql script, A44 + A45 in the assumptions log, and the pre-6b sweep completion report.

What was done​

  1. NVO sweep of of_update_database. The kickoff estimated "NVO 6500-6700"; verified actual function boundary via Grep ^public function|^end function to be NVO 6004-6756 (752 lines). Used Grep for _set_addcolumn|_create_table|_create_index|f_table_or_view_exists|f_table_has_column|is_table_name = to enumerate every relevant call.

  2. Classification of all 23 distinct operations in the function body:

    • 17 _create_table blocks for pfill_* tables — OUT-OF-SCOPE (PSSaaS owns via 001).
    • 2 view CREATEs (pfillv_pf_forensics_tradeside, pfillv_existng_pool_disposition) — OUT-OF-SCOPE (002).
    • 1 pxcat_site_plugins.current_version registry column — OUT-OF-SCOPE (Desktop-App-only).
    • 1 rmusr_payups create-if-missing — OUT-OF-SCOPE (Risk-Manager base table; A42 documented behavior on absence).
    • 1 pfill_carry_cost default-row INSERT — OUT-OF-SCOPE (Phase 4 ConfigurationService manages CRUD per A35; operator-driven).
    • 1 rmusr_payups.div_by add-column — IN-SCOPE M1 (already shipped in 007 from the 6a discovery arc).
    • 1 8-table mkt_shipped_date add-column FOR loop (NVO 6640-6653) — IN-SCOPE M2 (newly shipped).

    Net new work: 1 in-scope finding (M2) out of 23 operations enumerated.

  3. Empirical PS_DemoData verification for each in-scope and quasi-in-scope finding via sys.columns queries:

    • mkt_shipped_date — present on all 8 target tables.
    • mkt_purchase_date — also present on all 8 (added by another plugin; comment-vs-code drift in NVO 6640).
    • pxcat_site_plugins.current_version — present.
    • rmusr_payups.div_by — present (007 M1 already applied 2026-04-17).
    • pfill_carry_cost row count — 295 (matches A35 placeholder data).

  4. Empirical local pssaas-db verification: only loan and loan_shipped exist of the 8 target tables; both already have mkt_shipped_date from base seed schema. The other 6 tables are absent — confirming the skip-safe path will fire correctly when 007 is deployed locally.

  5. Extended 007_BackfillLegacyPluginMigrations.sql with M2 — a cursor-driven loop over the 8 tables. Each iteration: OBJECT_ID guard for table-missing → PRINT skip; COL_LENGTH guard for column-already-present → PRINT skip; otherwise ALTER TABLE ... ADD mkt_shipped_date DATETIME NULL + PRINT success. Plus updated header (verified function boundary 6004-6756) and trailing out-of-scope inventory comment block (so future readers don't re-derive the classification).

  6. Deployed 007 to local pssaas-db — skip-safe paths fire as expected (rmusr_payups missing; 6 of 8 base tables missing; loan + loan_shipped already migrated). 9 PRINT lines confirm.

  7. Deployed 007 to PS_DemoData — every column was already present; all 9 PRINT lines say "skipped ALTER" (confirming idempotency on already-applied tenants AND that PS_DemoData is fully migrated).

  8. Regression baseline preserved — POST /api/powerfill/run against PS_DemoData returned status: Complete in 1.04s (within noise of the 1.91s pre-sweep baseline) with all 3 steps Succeeded. Sentinel still phase-6a-candidate-builder-ready (correctly unchanged — sweep is a 6a follow-up, not a sub-phase ship).

  9. Updated assumption log — A44 extended with sweep result; new A45 entry for M2 in the A44 house style; A41-A45 disposition section added (all VERIFIED + SHIPPED; A43 marked RESOLVED per PO's earlier GRANT EXECUTE).

  10. Authored sweep completion report at docs-site/docs/handoffs/powerfill-pre-6b-sweep-completion.md — Phase 6a completion-report format scaled to this work's scope: TL;DR, decisions, full migration enumeration table, gate findings (3-layer Primary-Source Verification + Required Delegation Categories + Deploy Verification arms a/b/c), PoC verification commands and outputs, counterfactual retro.

Findings (Primary-Source Verification Gate output)​

IDFindingDisposition
F-Sweep-1Kickoff function-boundary estimate "NVO 6500-6700" is too narrow at both ends; actual is NVO 6004-6756(a) Corrected in place — recorded in 007 header + sweep completion report. Lesson: always verify function boundaries via Grep of ^public function, never trust passed-along ranges.
F-Sweep-2Comment-vs-code drift at NVO 6640: comment says "Add mkt_purchase_date" but code adds mkt_shipped_date(a) Corrected in place — A45 documents the drift. Migration we ship matches the executable code, not the misleading comment. mkt_purchase_date is added by another plugin (out of PowerFill sweep scope).
F-Sweep-3 (M2)8-table mkt_shipped_date DATETIME NULL migration (NVO 6640-6653) is referenced by psp_powerfill_conset (NVO 1246, 1450), psp_powerfillUE (NVO 13851-13853), and Phase 6a PowerFillCandidateBuilder.cs. Latent failure mode for tenants without legacy plugin run(a) Corrected in place — M2 shipped in 007 as cursor-driven defensive-insurance migration. PS_DemoData empirically already has the column on all 8 tables, but ships anyway for future tenant safety.
F-Sweep-421 of 23 operations in of_update_database are PSSaaS-already-owned or non-PSSaaS-targeted(b) No work needed — documented in 007 trailing comment + A45 + sweep completion report. Reassuring signal: PSSaaS's existing 001/002 deploy scripts cover the bulk of legacy migration responsibility.

No findings warranted Andon-cord escalation. The sweep was smaller than expected (21:2 out-of-scope ratio), not larger.

Decisions made (highlights)​

#DecisionWhere
D1Extend 007 rather than create 008007_BackfillLegacyPluginMigrations.sql
D2Cursor-driven loop (vs 8 hand-rolled IF blocks)007 lines 124-170
D3Ship M2 even though PS_DemoData already has the column (defensive insurance for future tenants)007 M2 block
D5Deliberate Non-Delegation of the SQL writing — interpretation-heavy work, ~50 LOC of high-decision-density SQL; subagent overhead would exceed benefit. Phase 6a 006 transcription (670 lines mechanical) was correctly delegated; this sweep's M2 (50 lines structural) is correctly self-implemented.sweep completion report §"Required Delegation Categories"
D6Do NOT port pfill_carry_cost auto-seed (NVO 6660-6687) — operator-driven per A35; auto-seed conflicts with PSSaaS UX intent007 trailing comment + sweep completion report

Full decision table (7 entries) in the sweep completion report §"Decisions made".

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

  • 23 operations in of_update_database are classified. A future reader (Phase 6b/6c/6d Architect, BestEx Architect doing analogous sweep) inherits the inventory verbatim from 007_*.sql trailing comment + A45.
  • M2 (mkt_shipped_date 8-table backfill) ships as defensive insurance. Future tenant onboarding from a fresh 8.x install or alternative upgrade path will not surface the same Invalid column name failure that surfaced 6a-DATA-1 for div_by.
  • PS_DemoData state is verified fully-migrated for the entire of_update_database scope. PS_DemoData was apparently migrated by the legacy plugin at some prior point (or mkt_shipped_date was in 7.x base schema). Either way, the original 6a-DATA-1 finding (div_by was missing) was an outlier rather than a systemic gap.
  • Three-layer Primary-Source Verification Gate exercised in earnest — produced findings at each layer (function-boundary correction, comment-vs-code drift, already-present empirical state). Worth advancing the candidate refinement to v3.1 nomination after one more corroborating session.
  • A41-A45 disposition section added to the assumptions log Open Questions summary — A43 RESOLVED, A41/A42/A44/A45 VERIFIED + SHIPPED. None require Tom/Greg input.
  • Phase 6b kickoff is unblocked on the legacy-plugin-migration class. The remaining prerequisite is PO answer on Q4 (multi-pass semantics; Architect default Option A: empirical NVO trace).

Process discipline observations (banked for next revision)​

  1. Three-layer Primary-Source Verification Gate — banked from the 6a-PoC-complete devlog; this session corroborated it produced findings at each of NVO-vs-doc, NVO-vs-implementation, NVO-vs-tenant-DB. One more corroborating session and this is ready to nominate as a v3.1 process-discipline addition.
  2. A44-style "Recommended pre-X sweep" notes pay for themselves — the 2-3 hour cost of this sweep would have been borne by 6b mid-allocation as a serial drip of Invalid column name findings, much more expensive in context-switching cost. The act of writing A44 yesterday paid for itself today. Worth noting in the canonical Counterfactual Retro practice description: when a finding suggests a class-level remediation, explicitly recommend the sweep as a banked task rather than leaving it implicit.
  3. Sweep ratio (in-scope:out-of-scope) is itself signal. A 2:21 ratio means PSSaaS's existing schema deploy already covers the bulk of legacy migration responsibility. If a future plugin sweep produces a 20:3 ratio, that signals PSSaaS hasn't yet absorbed that plugin's schema responsibilities — and is worth flagging as a separate ADR/spec item rather than just shipping migration scripts. Pattern observation; not yet a process-discipline candidate.
  4. Cursor-driven SQL is more reviewable than 8 hand-rolled IF blocks when the targets share structure. PowerShell's pattern of hand-unrolling each table individually (which was on the table) would have produced ~200 lines of near-duplicate code. The cursor is ~45 lines and trivially extensible. Pattern observation banked under "SQL deploy-script idioms"; not process-discipline-level.

Files produced / modified​

Modified:

  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Sql/007_BackfillLegacyPluginMigrations.sql — header comment block updated (verified function boundary 6004-6756); M2 block added (~50 lines); trailing out-of-scope inventory comment block added.
  • docs-site/docs/specs/powerfill-assumptions-log.md — A44 extended with sweep result; A45 added; A43 marked RESOLVED in Open Questions summary; A41-A45 disposition section added.

New:

  • docs-site/docs/handoffs/powerfill-pre-6b-sweep-completion.md — sweep completion report.
  • docs-site/docs/devlog/2026-04-18-pre-6b-sweep.md — this entry.

Out of scope (deliberately not produced):

  • No C# changes (zero scope; the work is entirely SQL + docs).
  • No spec amendments to powerfill-engine.md (sweep findings don't affect any spec API contract).
  • No status sentinel bump (per kickoff direction — 6a follow-up, not a sub-phase ship).
  • No new test file (no C# code path changes; SQL is verified by Deploy Verification Gate arms a/b/c).

Verification​

# Deploy 007 to local pssaas-db (skip-safe paths verify)
docker exec pssaas-db /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "..." -No -d PSSaaS_Dev \
  -i /docker-entrypoint-initdb.d/powerfill/007_BackfillLegacyPluginMigrations.sql

# Deploy 007 to PS_DemoData (idempotency verify — all "skipped ALTER")
docker exec pssaas-db /opt/mssql-tools18/bin/sqlcmd \
  -S "hostedps-sql.public.086ea791c2f1.database.windows.net,3342" \
  -U "kevin_pssaas_dev" -P '...' -No -d PS_DemoData \
  -i /docker-entrypoint-initdb.d/powerfill/007_BackfillLegacyPluginMigrations.sql

# Regression baseline (Deploy Verification Gate arm b)
curl -s -X POST -H "X-Tenant-Id: ps-demodata" "http://pssaas.powerseller.local/api/powerfill/run" -d '{}'
# → status: Complete, duration ~1.04s, all 3 steps Succeeded

# Sentinel unchanged (Deploy Verification Gate arm a)
curl -s "http://pssaas.powerseller.local/api/powerfill/status"
# → {"module":"PowerFill","status":"phase-6a-candidate-builder-ready"}

Full output captured in the sweep completion report §"PoC verification commands and outputs".

What's next​

  • Collaborator review of 007_*.sql extension, A44/A45 in the assumption log, and the sweep completion report. Estimated 30 minutes per the kickoff target.
  • PO push of the 3 atomic commits.
  • Sub-phase 6b kickoff drafting — prerequisites: PO answers Q4 (multi-pass semantics; Architect default Option A: empirical NVO trace inside 6b's range). 6a-PERM-1 is RESOLVED; this sweep eliminated the M2 latent finding class. 6b is unblocked on PowerFill-internal concerns.
  • (Optional, deferred) Analogous sweeps for BestEx and Risk Manager plugin sources. The mkt_purchase_date column added by one of those plugins is a candidate for similar pre-X-style sweeps when those modules' data integrity becomes runtime-critical. NOT in this sweep's scope; flagged for future Architect dispatch.

Cross-references​

End of pre-6b sweep devlog. The next planned milestone is Phase 6b kickoff (multi-pass allocation engine), prerequisite-blocked on PO answering Q4.