Skip to main content

First End-to-End PowerFill Preflight Against PS_DemoData

Date: 2026-04-16 evening Agent: Collaborator (with PO active in Azure Portal for two infrastructure changes) Outcome: PSSaaS pointed at a real PowerSeller client database (PS_DemoData), executed a full 9-check PowerFill preflight, returned structured diagnostic findings.

This is the first concrete proof-of-concept artifact: code we built actually runs against production-shape customer data, and the responses are interpretable diagnostics rather than implementation errors. Five PS_DemoData live-system findings surfaced and were corrected in lockstep.

Why This Run Mattered​

Up to today PowerFill Phase 1-4 had only been validated against a synthetic local SQL Server container (pssaas-db) seeded by infra/sql/init/seed-schema.sql. The seed schema was inferred from the legacy NVO during Phase 2 reverse-engineering. Tests passed against that schema; nothing exercised the assumption that the schema was actually correct.

PoC framing (PO instruction, this evening): "Everything we are doing here is Proof of Concept to help [Tom and Greg] believe that the PowerSeller Desktop App can be migrated to SaaS and likely a piece at a time (i.e., move chunks of core business logic out of the Desktop App and into PSSaaS and have the Desktop App make API calls accordingly)." Reframed priority: not parity validation; demonstrability against real customer data.

What Was Done — Steps A through E​

Step A: A30 unblocked (committed earlier as d2bab7b)​

PO granted kevin_pssaas_dev the db_ddladmin role on PS_DemoData. Verified empirically (sys.database_role_members + actual CREATE/DROP TABLE + CREATE/DROP PROCEDURE). Backlog items #23 / #25 unblocked.

Step B: Schema validation, pfill_* tables (no commit — read-only)​

Compared information_schema.columns for all 17 pfill_* tables in PS_DemoData vs local pssaas-db.

  • 17/17 tables present in both
  • 255/255 columns identical except for one drift cause repeated 92 times: every string column is CHAR(N) in our 001 schema and VARCHAR(N) in PS_DemoData (assumption A29 confirmed at full scope; no other types/lengths/nullability/precision differences)
  • Operationally non-blocking for PoC — SQL Server implicit conversion handles this

Step C: Deploy 004 + 005 to PS_DemoData (no separate commit — DDL applied to PS_DemoData, not to repo)​

Per A32 Deploy Verification Gate (PRINT-in-guard refinement):

PowerFill 004: created table dbo.pfill_preflight_settings
PowerFill 004: inserted default preflight row (id=1)
PowerFill 005: added column dbo.pfill_constraints.rv_rowversion

Three PRINT lines = three DDL operations actually executed (not skipped). Both arms of the gate satisfied (sys catalog presence + DDL execution proof).

Post-deploy checks: pfill_preflight_settings exists with seed row (id=1, min_status='Closed', 2, 60), rv_rowversion ROWVERSION column present.

Step D: PS_DemoData as a tenant in the local API (in this commit)​

  • appsettings.Development.json — added ps-demodata tenant with empty placeholder connection string
  • docker-compose.override.yml.example — committed; documents the override pattern for future developers
  • docker-compose.override.yml — gitignored; per-developer file with the actual SQL MI connection string injected via env var (Tenants__ps-demodata__ConnectionString)
  • .gitignore — added docker-compose.override.yml line

The multi-tenant infrastructure built in earlier phases (TenantRegistry, TenantMiddleware, TenantContext) handled this with zero code changes. X-Tenant-Id: ps-demodata header dispatches the entire request to PS_DemoData.

Step E: The truth moment​

Three iterations on POST /api/powerfill/preflight with X-Tenant-Id: ps-demodata and an empty body:

Iteration 1 — Invalid column name 'loan_status'. Invalid column name 'stage_order'.

pscat_loan_stages actual columns are status_code and stage_rank in both PS_DemoData and PS608. Phase 2 reverse-engineering invented the column names. Surfaced as Truth-Rot finding T1.

Iteration 2 — Incorrect syntax near '$'

PS_DemoData was at SQL Server compatibility level 120 (SQL 2014). EF Core 8 generates OPENJSON(@param) WITH (... '$') for Contains(parameterList) queries, requiring compat level ≥130 (SQL 2016). Compat level mismatch is a Tier-1 Ghost Deploy adjacent failure mode — code that works in dev (SQL 2022, compat 160) silently fails against an older customer DB. Surfaced as live-system finding T5.

PO bumped PS_DemoData to compat level 150 in Azure Portal.

Iteration 3 — Success.

{
  "tenant_id": "ps-demodata",
  "checked_at": "2026-04-17T01:15:13Z",
  "is_ready": false,
  "summary": {"error_count": 2, "warning_count": 1, "info_count": 0},
  "checks": [
    {"code": "NO_CONSTRAINTS",  "severity": 2, "message": "pfill_constraints is empty; PowerFill requires at least one constraint before a run."},
    {"code": "PRICES_STALE",     "severity": 1, "message": "Latest BestEx prices are older than 2 day(s).", "context": {"most_recent_market_date": "2023-04-26", "threshold_days": 2}},
    {"code": "PIPELINE_EMPTY",   "severity": 2, "message": "No loans in pipeline at or above min_status 'Closed'.", "context": {"min_status": "Closed"}}
  ]
}

6 of 9 checks ran without error. The 3 failing checks returned structured findings about real properties of the demo client's data:

  • The client never configured any PowerFill constraints — pfill_constraints is empty
  • Prices are 3 years stale (most recent market date 2023-04-26); confirms PS_DemoData is a 2023-vintage snapshot
  • No loans in any "Closed" status — the client used In Process / In Underwriting / Approved / Docs Out (per pscat_loan_stages data), with no "Closed" stage at all

These are not bugs in PSSaaS. They are findings about the customer's data that PSSaaS surfaced via the preflight system. That's exactly what the system is designed to do.

Truth-Rot Findings (Primary-Source Verification Gate, live-DB arm)​

Five distinct findings; all corrected in this commit. Comparison was against both PS_DemoData (4-yr-old client) AND PS608 (current WTPO) — schemas agree on all five points.

T1 — pscat_loan_stages column names​

Phase 2 entity used loan_status and stage_order. Live schema: status_code and stage_rank. Same semantics, different names. Disposition: corrected in LoanStage.cs and infra/sql/init/seed-schema.sql. C# property names retained for code readability; only [Column] attributes changed.

T2 — pscat_pools.pool_status does not exist​

Phase 2 entity declared a pool_status field. Neither client DB has this column on pscat_pools (verified). The pfillv_existng_pool_disposition view computes a pool_status column inside its definition, which is unaffected. Disposition: removed from Pool.cs and seed-schema. No code consumed Pool.PoolStatus.

T3 — pscat_pair_offs shape​

Phase 2 entity modeled this as an event table with pair_off_id PK, trade_id, pair_off_date, pair_off_amount. Live schema: relationship table, composite PK (trade_id, designated_trade_id), columns designated_amount, designated_percent. Phase 2 invented all 4 of its column names. Disposition: rewrote PairOff.cs to match live schema; added composite-key configuration in UpstreamEntityConfiguration.cs; updated seed-schema. PairOff was registered in DbContext but never queried, so no consumer changes required.

T4 — rmcat_ra_history_trades PK shape​

Phase 2 used composite (analysis_id, trade_id). Live schema: (analysis_id, profile_name, trade_id). Also analysis_id is int in both client DBs, not numeric(10,0). Disposition: added ProfileName to TradeAnalysisHistory.cs; updated UpstreamEntityConfigurationTradeAnalysisHistoryConfiguration to the 3-column key; changed analysis_id type. Seed-schema updated.

T5 — PS_DemoData compatibility level was 120​

Independent of entity drift. EF Core 8 generates OPENJSON for parameterized Contains queries, requiring compat ≥130. Local pssaas-db is at compat level 160 (SQL 2022 default), so tests passed. PS_DemoData was at 120 — same query failed. Process discipline observation: integration tests against a local container at higher compat level cannot detect this class of issue. Phase 9 (parity validation) plan should explicitly include compatibility-level matching against the customer DB. Disposition: PO bumped PS_DemoData to 150 in Azure Portal.

PS608 Cross-Check Confirms PS_DemoData Is Canonical​

Mid-investigation question: PS_DemoData is a 4-year-old client snapshot; does it accurately reflect current customer schema?

Probed PS608 (current WTPO production database) for the 5 tables involved in the drift findings. Result: PS608 and PS_DemoData are identical on pscat_loan_stages, pscat_pools, pscat_pair_offs, and rmcat_ra_history_trades. PS608 has 2 trailing columns missing on pscat_trades that PS_DemoData has (payup_actual, payup_instrument) — neither referenced by our entities.

Verdict: schema is remarkably stable across 4 years. PS_DemoData IS the canonical PoC reference for these tables. Aligning entities to PS_DemoData is also aligning to current customer reality. ADR-006 (schema preservation) holds — the schema we're preserving against is the right one.

Verification​

  • dotnet build: 0 warnings, 0 errors
  • dotnet test: 87/87 pass (32 BestEx + 1 Api + 50 PowerFill unit + 4 integration when PFILL_TEST_SQLSERVER set; 4 skipped in dev container without env var)
  • Local pssaas-db rebuilt with new seed-schema; tests still pass
  • PS_DemoData preflight returns structured 200 OK with 3 contextual findings about real demo data

Counterfactual Retro​

If I were starting over knowing what I know now, what would I do differently?

  1. Do live-DB column probing as part of Phase 2 reverse-engineering itself, not as a Phase-N+ gate. The Primary-Source Verification Gate as defined in v3 says "verify against live systems where accessible." Phase 2 was blocked from PS_DemoData by the perms issue (A30, then unresolved). The lesson is blocked is not done. When the live arm is unavailable, the work item should be tagged "incomplete pending live-DB probe" and held visible — not silently progressed past. Process discipline candidate: add an "incomplete-but-progressed" disposition to the Gate Output Action format, or a separate "blocked-arm-of-gate" tag. Worth surfacing if a similar pattern recurs.

  2. Compatibility-level matching should be explicit in test design. Local containers default to the latest SQL Server compat level. Customer DBs frequently run on older levels. Integration tests at compat 160 give false confidence about what works at compat 120. Practice candidate (or A26 amendment): the test container should match the lowest customer-supported compat level, not the highest. Worth a Phase 9 hardening spec line.

  3. A live-DB probe in 30 minutes today produced more value than 2-3 days of Phase 2-4 reverse-engineering for these specific findings. The PoC framing makes "do less reverse-engineering, do more live probing" an obvious shift in approach. Future phases (5+) should default-probe before assuming.

What's Live Now​

  • Local API at http://pssaas.powerseller.local/api/powerfill/preflight accepts X-Tenant-Id: ps-demodata and returns structured diagnostic JSON
  • pfill_preflight_settings exists in PS_DemoData with default values
  • pfill_constraints.rv_rowversion column added to PS_DemoData (concurrency token will work when Phase 4 CRUD is exercised against PS_DemoData)
  • 4 entity drifts corrected
  • Seed-schema aligned with PS_DemoData / PS608 reality
  • Multi-tenant API tested end-to-end with two tenants (default → pssaas-db, ps-demodata → SQL MI)

What's Next (Tomorrow's Decision)​

Demo working against PS_DemoData. Three legitimate next priorities:

  • Configure pfill_constraints in PS_DemoData so preflight can return is_ready: true — that's an even stronger demo
  • Phase 5 (carry-cost calculator) dispatch — original next-phase work
  • Connector Plugin spec — the architecture story for "Desktop App calls PSSaaS API"

PO call.