Skip to main content

ADR-021: PowerFill Port Strategy

Status​

Proposed (2026-04-16) — Amended 2026-04-19 with §Narrow Bug-Fix Carve-Out (see below). The verbatim-port discipline remains the default; the amendment formalises a tightly-scoped exception for empirically-demonstrated legacy bugs that prevent end-to-end PoC progress against tenant data.

Context​

The Desktop App's PowerFill plugin is approximately 19,000 lines of T-SQL embedded in PowerBuilder string literals (deployed at runtime as stored procedures), plus approximately 5,000 lines of PowerBuilder code for the window shell, menu, DataWindows, and tab user objects.

The engine has been in production for 15+ years. It contains extensive domain knowledge — constraint logic, securitization rule interactions, tolerance bands, carry cost scoring, multi-pass allocation — none of which is separately documented. The source code IS the specification.

Porting this to PSSaaS requires a strategy for the T-SQL. Three options exist:

Option A: Lift-and-Shift T-SQL​

Deploy the existing stored procedures (or functionally-identical versions) to the tenant databases. The PSSaaS .NET API calls these procedures via EF Core FromSqlRaw or raw ADO.NET.

Pros:

  • Lowest risk — we run the same code that has 15 years of tuning
  • Fastest to implement — no algorithm rewrite
  • Performance — set-based T-SQL is fast for this workload
  • Tenant DBs already have the infrastructure (SQL MI supports stored procedures)

Cons:

  • Two execution environments to debug (.NET API + T-SQL procedures)
  • Cross-tenant procedure deployment is operationally painful
  • Business logic lives in T-SQL, not in C# — harder to unit test
  • T-SQL is not the PSSaaS technology stack

Option B: Full C# Port​

Rewrite everything in C#. Load data into in-memory collections, execute allocation logic in managed code, persist results to the database.

Pros:

  • Single execution environment
  • All business logic in C# — unit testable with standard tooling
  • No stored procedure deployment across tenant databases
  • Full type safety

Cons:

  • Highest risk — 19K lines of T-SQL that "just works" rewritten from scratch
  • Performance unknown — set-based T-SQL may outperform row-by-row C# for a 10K-loan × 50-trade × constraint-priority iteration
  • Longest timeline — rewrite + verification could take months
  • High probability of behavioral drift from the Desktop App

Option C: Hybrid​

Keep the heavy set-based SQL in stored procedures (allocation engine, candidacy scoring, tolerance enforcement) because that's where T-SQL excels. Move orchestration, validation, constraint editing, API contracts, and business rule evaluation to C#.

Pros:

  • Best-of-both: T-SQL does what T-SQL is good at, C# does what C# is good at
  • Matches the Desktop App's pattern (PB shell + T-SQL engine) which is proven at scale
  • Unit-testable business rules in C#, performance-critical set operations in T-SQL
  • Lower rewrite risk than Option B
  • Less operational complexity than Option A (fewer deployed procedures)

Cons:

  • Two languages in the module
  • Contract between C# and T-SQL must be carefully maintained
  • Still requires stored procedure deployment per tenant (though fewer than Option A)
  • Mix of testing strategies

Decision​

Adopt Option C: Hybrid.

Specifically​

  • In T-SQL (deployed as stored procedures per tenant):

    • psp_powerfill_conset port — core allocation engine with constraint iteration, candidacy scoring, multi-pass allocation
    • psp_powerfillUE port — post-processing and recap finalization
    • psp_pfill_bx_cash_grids port — BestEx cash grid refresh
    • psp_pfill_bx_settle_and_price port — settlement/price computation
    • Helper procedures for set-based operations (candidacy builds, lockdown applications, kickout detection)

  • In C# (PSSaaS .NET API):

    • API endpoint controllers and routing
    • Authentication and tenant resolution
    • Input validation (preflight)
    • Options parsing and parameter construction for stored procedure calls
    • Async run orchestration (spawn job, track status, handle cancellation, handle failure)
    • Constraint CRUD editing and validation
    • Carry cost curve CRUD and validation
    • Lockdown CRUD and validation
    • Business rule evaluation for non-set operations (e.g., "is this constraint editable?")
    • Run history and audit trail
    • Result marshalling from pfill_* tables to API response shapes

Stored Procedure Deployment​

  • PSSaaS deploys PowerFill stored procedures via EF Core migrations during tenant provisioning
  • Updated procedures versioned with ADR-006-compatible migration scripts
  • Stored procedure source lives in the PSSaaS repo under src/backend/PowerSeller.SaaS.Modules.PowerFill/Sql/
  • Per ADR-006 schema preservation, the pfill_* table schemas match the Desktop App's schema exactly for coexistence period

Interface Contract​

The C# layer invokes stored procedures via a typed service (IPowerFillEngineService) that:

  1. Validates inputs
  2. Opens a SQL transaction
  3. Calls procedures in order with structured parameters
  4. Checks for procedure return codes / output parameters
  5. Commits or rolls back
  6. Reads pfill_* run-output tables into C# DTOs
  7. Returns typed results to the controller

Consequences​

Positive​

  • Minimal risk — the allocation engine is a port of battle-tested T-SQL, not a rewrite
  • API-first — modern REST endpoints, authentication, tenant isolation, audit trail
  • Testable — C# orchestration is unit-testable; T-SQL procedures are integration-testable against a seeded DB
  • Coexistence-friendly — shared schema with Desktop App per ADR-015; PSSaaS and Desktop App can run PowerFill against the same tenant database during transition
  • Performance preserved — set-based T-SQL continues to outperform row-by-row for the allocation workload
  • Matches Desktop App behavior — faithful port = easier Tom/Greg review and customer parallel validation

Negative​

  • Two languages in the module — contributors must be comfortable with both
  • Stored procedure versioning complexity — updating procedures across tenants requires migration discipline
  • Contract drift risk — if the T-SQL signature changes and C# isn't updated, runtime failures
  • T-SQL unit testability is weaker — integration tests against a seeded DB are needed, not pure unit tests
  • Harder to move to another DB engine — PostgreSQL support (if ever desired) would require procedure rewrites

Risks and Mitigations​

RiskMitigation
Procedure-C# contract driftGenerate C# DTOs from procedure signatures via code generation (future); for now, maintain integration tests that fail on mismatch
Procedure deployment inconsistency across tenantsSingle-source procedure definitions in repo, deployed via EF migrations; tenant provisioning includes PowerFill schema as standard
Debugging across two environmentsStructured logging with correlation IDs; stored procedures emit diagnostic info to a pfill_run_log table
Performance regression vs. legacyBenchmark suite runs each release against PS_DemoData baseline; alert on >10% regression

Narrow Bug-Fix Carve-Out (added 2026-04-19)​

The verbatim-port discipline above is the default. It is not absolute. Operating against PS_DemoData (and prospectively against any tenant DB whose snapshot data shape exposes latent legacy-proc bugs) revealed an empirical class of cases where strict verbatim port produces an unrunnable engine — the legacy procedure body itself contains a bug that fires deterministically on real tenant data, blocking end-to-end PoC progress.

This carve-out formalises a tightly-scoped exception.

Principle​

When a legacy proc body contains an empirically-demonstrated bug that prevents end-to-end PoC progress against tenant data, PSSaaS may apply a narrow surgical fix (typically ≤5 LOC per fix; ≤15 LOC across multiple fixes addressing one root cause) provided all four acceptance criteria are met:

  1. (a) Empirically reproducible — the bug is reproducible against tenant data with line-level NVO citations + a captured SqlException (or equivalent diagnostic) + post-state SELECT confirmation. Plan-time hypotheses count for nothing; only empirical PoC evidence opens the carve-out.
  2. (b) Mirrors legacy author intent — the fix shape is justified by adjacent code in the same proc body (e.g., the _1300 PK extension to 3 cols matches the immediately-following _1400's 3-col PK; the pt13 JOIN qualifier extension matches the column the inner subquery already selected with no other use site). Pure speculation about author intent is insufficient; the evidence must come from the code itself.
  3. (c) Documented in the assumption log with a Phase 9 parallel-validation hook — the assumption-log entry carries the empirical evidence + the fix justification + an explicit Phase 9 parallel-validation expectation (e.g., "Phase 9 must compare PSSaaS allocations to Desktop App allocations on a customer DB to confirm the fix doesn't introduce divergence").
  4. (d) Tom/Greg consultation is recorded — either:
    • PO + recipient signature — the PO escalates to Tom/Greg, captures their input, and records it in the assumption-log entry; OR
    • Demo-as-consultation note — when the fix is necessary to demo to Tom/Greg/another domain expert who would otherwise be the consulted authority, the demo IS the consultation. The PO records this disposition explicitly. The empirical evidence + the ADR-021 carve-out reasoning constitute the case the consulted expert reviews live.

What this is NOT​

  • Not a rewrite license. Carve-out fixes are surgical (≤5 LOC each; ≤15 LOC per root cause). Anything larger is a structural change requiring a new ADR.
  • Not a behavior change. The carve-out is reserved for cases where the fix is strictly more permissive than the legacy behavior (the post-fix proc accepts every input shape the pre-fix proc accepted, plus additional shapes the pre-fix proc rejected). Fixes that change which row wins, change ordering semantics, or alter aggregation results are out of scope.
  • Not a substitute for Phase 9. Phase 9 parallel-validation against the Desktop App on a customer DB remains the canonical correctness gate. The carve-out unblocks PoC progress; it does not bless the fix as customer-deployable.
  • Not a precedent for skipping ADR-021's discipline elsewhere. Every carve-out invocation requires an explicit assumption-log entry that satisfies all four acceptance criteria. Pattern repetition over time may justify a new ADR; one-off invocations stay under this carve-out.

Canonical first instance: A54 (PowerFill psp_powerfill_pool_guide — ##cte_posting_set_1300 PK + pt13 JOIN qualifier)​

Empirical evidence (a): On every PSSaaS run against PS_DemoData since Phase 6c (2026-04-19), Step 5 (pool_guide) terminated as Failed with SqlException 2627: Violation of PRIMARY KEY constraint 'PK__##cte_po__...' on ##cte_posting_set_1300. Reproducible across 5 runs (run IDs 5859cd70, fd5b63cc, 8c9e46dd, a8e5bcfd, plus 6c/6d/6e/7's prior runs). NVO source: n_cst_powerfill.sru lines 9889-9919 (_1300 CREATE), 10001-10033 (_1400 CREATE for shape comparison), 9990-9999 (pt13 INNER JOIN definition).

Mirrors legacy author intent (b): Two surgical changes:

  • PK extension — _1300's PK extended from (trade_id, loan_id) (2 cols) to (trade_id, loan_id, pa_key) (3 cols), with pa_key NUMERIC(1, 0) NOT NULL added to the table CREATE and the SELECT (using the identical CASE expression LEFT(pool_action, 1) IN ('L'/'R'/'J'/else) that _1400's SELECT already uses ~80 lines later). This mirrors _1400's author-intended shape byte-for-byte.
  • pt13 JOIN qualifier extension — added AND ps1200_13.settlement_date = pt13.settlement_date to the INNER JOIN. The pt13 inline subquery already selected (trade_id, settlement_date) and GROUPed by both — the legacy JOIN qualified on trade_id alone, which produces a fan-out when a trade has multiple distinct settlement_dates across its loans (empirically: trade 36177868 had 4 rows with 3 distinct settlement_dates in pfill_powerfill_guide mid-run, exploding each _1200 row 3x). The author selected settlement_date for pt13 with no other downstream use of that column — the JOIN qualifier omission is a clear oversight.

Documented in assumption log + Phase 9 hook (c): A54 marked RESOLVED 2026-04-19 with the full empirical-resolution arc (5 pre-fix run citations + post-fix run 43e8f148 + 7c9dfe50 Complete-status confirmations). A65 documents the broader observation (multi-pa_key Switching is one of two latent triggers; settlement-date variance is the other; PS_DemoData's snapshot exposes both). Phase 9 parallel-validation is explicitly identified as the canonical gate to confirm the fix doesn't introduce per-loan divergence vs the Desktop App.

Tom/Greg consultation recorded (d): Demo-as-consultation disposition. The PO has decided that the immediate next consumer of PSSaaS PowerFill output is Greg himself, in a Greg-demo context. The empirical evidence + this ADR amendment + the Greg-demo-readiness completion report (docs-site/docs/handoffs/powerfill-a54-fix-greg-demo-readiness.md) constitute the consultation material Greg reviews live. If Greg's review reveals a deeper concern (the legacy author's 2-col PK was intentional; the JOIN qualifier omission preserved by design), the fix is reverted and the PoC reverts to its pre-fix Failed state — i.e., this is a reversible, documented carve-out, not a permanent platform change.

Forward-only​

The fix is forward-only. PSSaaS-deployed 009_*.sql includes the amended proc body. Legacy Desktop App customers running their own copy of psp_powerfill_pool_guide are unaffected (the carve-out lives only inside PSSaaS's port). When Phase 9 runs PSSaaS against a real customer DB, the post-fix proc body will accept every (trade, loan, pa_key, settlement_date) tuple shape that the pre-fix proc accepted, plus additional tuples the pre-fix proc rejected — strictly more permissive.

Alternatives Considered and Rejected​

Option A (Lift-and-Shift T-SQL)​

Rejected because: business rule evaluation and constraint editing genuinely belong in C# for testability and API ergonomics. Pure T-SQL would force us to expose stored procedures for CRUD operations, which is awkward.

Option B (Full C# Port)​

Rejected because: the risk/timeline trade-off is bad. A 19K-line rewrite is not the right use of engineering time when Option C delivers behavioral parity with half the risk. An LP/CSP rewrite remains a Phase 2 modernization candidate (tracked as a future ADR).

Relationship to Other ADRs​

  • ADR-004 (Modular Monolith) — PowerFill is a module within the monolith; T-SQL stored procedures live in the tenant DB, not a separate service
  • ADR-005 (Database-Per-Tenant) — each tenant gets PowerFill procedures and pfill_* tables; no cross-tenant data
  • ADR-006 (Schema Preservation) — pfill_* tables preserve the legacy schema; Desktop App and PSSaaS coexist against the same tables
  • ADR-014 (Backend Language Divergence) — this ADR adds T-SQL as a third language (alongside C# and Python in PSX), scoped to PowerFill stored procedures
  • ADR-022 (Allocation Algorithm) — this ADR picks the port approach; ADR-022 picks the algorithm (port iterative passes vs. LP rewrite)
  • ADR-023 (Constraint Model) — this ADR picks execution split; ADR-023 picks the constraint data model

Revision Triggers​

This ADR should be revisited if:

  • Tom or Greg critique reveals the legacy T-SQL has bugs we inadvertently ported
  • Performance measurements show the stored procedure approach doesn't scale
  • A full C# port becomes tractable (e.g., via a constraint solver library that matches the domain)
  • PostgreSQL support becomes a product requirement (forcing a reduction in stored procedure reliance)
  • A second carve-out instance accumulates against a different proc — pattern repetition justifies promoting carve-outs to a sibling ADR (e.g., ADR-026 "PSSaaS Bug-Fix Catalog for Legacy PowerFill Procs") with a per-fix register
  • Greg or Tom rejects the A54 carve-out during their review of the Greg-demo-readiness report — the fix gets reverted and the carve-out clause itself is reconsidered