PowerFill — Synthetic Trades Subsystem (psp_powerfillUE)

Source: X:\dev\other\PowerSeller-App\plugins\powerfill\n_cst_powerfill.sru and w_powerfill.srw. Author: PSSaaS Collaborator, 2026-04-16 — written while the Phase 4 Architect ran in parallel, to forestall the Phase 6 Architect having to do this discovery mid-implementation. Trigger: Resolves Phase-0 Truth Rot finding A28 — three pfill_syn_* tables exist in PS_DemoData and are extensively referenced in the NVO but were missing from the Phase 1 deep dive (which used of_update_database at line 6094 as the table-count source of truth; that function only declares 17 tables, missing the 3 _syn_* tables that are created by other paths or are pre-existing in customer databases).

This document is a Phase 6 prerequisite. The Phase 6 Architect kickoff should cite it directly.

---

TL;DR

psp_powerfillUE is the mandatory second stored procedure invoked on every PowerFill run, immediately after psp_powerfill_conset (the allocation engine) completes. It is not an optional or post-event procedure — it runs every time, with the same parameter array.

psp_powerfillUE does two things:

1. Resets the run-output tables (pfill_powerfill_guide, pfill_cash_market_map, pfill_syn_powerfill_guide, pfill_syn_powerfill_guide_all_rank) — full DELETE / TRUNCATE.
2. Rebuilds them from current allocation state, this time synthesizing alternative trade options for each loan and ranking them by profit metrics (payup-only and payup-plus-carry).

The three pfill_syn_* tables are the working set for these synthetic-trade what-ifs. They are read by downstream Pool Guide updates and by the pfillv_pf_forensics_tradeside view, which is what the trader sees on the PowerFill window's Forensics tab.

Implication for PSSaaS Phase 6: the allocation engine port must include both psp_powerfill_conset (the existing scope) and psp_powerfillUE (synthetic trades), invoked back-to-back. Skipping the UE pass would break the Forensics view and the Pool Guide's price / prx_and_carry columns for any loan whose best assignment is a synthetic trade rather than a real pscat_trade.

---

The three tables

All three are confirmed present in PS_DemoData with the schema below (information_schema.columns query, 2026-04-16).

pfill_syn_trade_base (11 columns)

The set of synthesizable trade variants for the current run. One row per (instrument, rate, pricing window) tuple where a payup is possible.

Column	Type	Source / Meaning
syn_trade_id	int	Surrogate key. Note: this is the only pfill_* column we've seen typed as int rather than numeric — likely an IDENTITY column for the synthesized rows.
instrument_name	varchar(30)	Synthesized instrument (the "what if we pooled into THIS instead?")
base_instrument_name	varchar(30)	The base instrument the synthesis is built from
price_denominated	numeric	Synthesized price in the synthesized instrument
rate	numeric	Note rate for the synthesis
pricing_window_in_days	numeric	From rmcat_todays_prices.pricing_window_in_days
settlement_date	datetime	market_date + pricing_window_in_days
base_price	numeric	Price of the base instrument (proxy) — used to compute payup
payup	numeric	price_denominated - base_price — how much extra a synthesis would earn
cash_grid_type	varchar(10)	From pscat_instruments.cash_grid_type
servicing_fee	numeric	From pscat_instruments.servicing_fee

pfill_syn_powerfill_guide_all_rank (40 columns)

The all-candidate scoring table. One row per (loan, synthesizable trade) pair where eligibility holds. Holds the full ranked candidate list before pruning.

40 columns — too many to enumerate; key ones:

• loan_id, loan_amount, orig_loan_amount, trade_amount — loan identity + sizes
• syn_trade_id, current_trade_id, instrument_name, base_instrument_name, cash_grid_type, servicing_fee — synthesized trade fields
• rate, current_rate, note_rate, annual_rate, note_less_annual — rate semantics
• payup, on_day, to_day, carry_days, current_carry_days, buffer_days, carry, current_carry, payup_and_carry, prx_and_carry, actual_prx_and_carry, current_prx_and_carry — the full carry-cost arithmetic
• price, actual_price, current_price — three price views (synthesized / actual / what they have today)
• eligible_date, settlement_date, current_settlement_date, close_date — eligibility windows
• assign, price_value — short codes
• payup_only_rank, payup_plus_carry_rank, price_only_rank, prx_and_carry_rank — four parallel rank columns; each is a ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY <metric> DESC, settlement_date ASC)

pfill_syn_powerfill_guide (40 columns)

Same shape as _all_rank but filtered down to the recommended synthesis per loan. One row per loan that survived the rank filter (top-1 or top-N depending on the WHERE clause we'll see in §"Lifecycle"). This is the table downstream consumers read.

---

Lifecycle — populate / consume / teardown

Trigger

w_powerfill.srw::ue_perform_powerfill (lines 94-192) is the user's "Run PowerFill" button handler. After UI parameter collection, it invokes:

ls_sp = "psp_powerfill_conset"
if ln_cst_db_fn.of_execute_procedure(ls_sp, ls_proc_parms) then
    ls_sp = "psp_powerfillUE"
    if ln_cst_db_fn.of_execute_procedure(ls_sp, ls_proc_parms) then
        // refresh tab displays

(w_powerfill.srw:170-172. Verbatim except for indentation.)

So psp_powerfillUE runs every time psp_powerfill_conset succeeds. Same ls_proc_parms array — both procs receive the same max_eligible_days, max_trade_settle_days, eligible_settle_buffer_days, scope, etc.

Phase A — teardown (NVO lines 13441-13451, inside of_get_psp_powerfillue_syntax_a)

DELETE FROM dbo.pfill_powerfill_guide;
DELETE FROM dbo.pfill_syn_powerfill_guide_all_rank;
DELETE FROM dbo.pfill_cash_market_map;
DELETE FROM dbo.pfill_syn_powerfill_guide;

Note: this is not a TRUNCATE (which would reset identity counters). It is a DELETE — preserves any trigger or audit semantics if those tables had them in some customer's environment.

pfill_syn_trade_base is not deleted here. It gets a separate TRUNCATE TABLE at line 19020 just before its own repopulation (Phase C below).

Note on what just got deleted: pfill_powerfill_guide is the primary allocation output table — the thing psp_powerfill_conset just spent its entire run populating. psp_powerfillUE deletes it and rebuilds it from scratch using psp_powerfill_conset's working tables (pfill_loan2trade_candy_level_01, pfill_trade_base) plus the new synthetic rankings. This means the contract between _conset and _UE is via working tables, not via pfill_powerfill_guide — anyone porting psp_powerfill_conset in isolation and reading the post-run pfill_powerfill_guide will get UE's output, not conset's output.

Phase B — orphan re-mix (lines 18950-19018, immediately before the synthetic-trade logic)

A loop with @k (orphan count) and @j (iteration counter) that re-tries assigning loans currently flagged 'ORPHAN' in pfill_powerfill_guide to trades with available post_session_space. We don't expand on this here — it's the standard allocation pass logic, just executed inside UE rather than _conset.

Phase C — synthetic trade base population (lines 19020-19082)

TRUNCATE TABLE dbo.pfill_syn_trade_base;

WITH cte_syn_trade_instr AS (
    SELECT i.instrument_name, i.cash_grid_rows, i.trade_type, i.rm_trade_type_family,
           p.market_date, p.rate, p.price_denominated, p.pricing_window_in_days,
           DATEADD(D, p.pricing_window_in_days, p.market_date) AS settles,
           i.base_instrument_name AS base_instrument, i.cash_grid_type, i.servicing_fee
    FROM pscat_instruments i
    INNER JOIN dbo.rmcat_todays_prices p ON i.instrument_name = p.investor_instrument_name
    WHERE cash_grid_rows > 1
      AND i.rm_trade_type_family = 'wl'
      AND p.pricing_window_in_days <= @ai_max_trade_settle_days
)
,cte_syn_trade AS (
    SELECT i.*, tp.investor_instrument_name AS proxy_instrument,
           tp.price_denominated AS base_price,
           (i.price_denominated - tp.price_denominated) AS payup
    FROM cte_syn_trade_instr i
    INNER JOIN rmcat_todays_prices tp
        ON i.base_instrument = tp.investor_instrument_name
       AND i.rate = tp.rate
       AND i.pricing_window_in_days = tp.pricing_window_in_days
)
INSERT INTO pfill_syn_trade_base (...) SELECT ... FROM cte_syn_trade;

Then a logging insert into pfill_powerfill_log (a table that does not exist in PS_DemoData per our 2026-04-16 probe; the INSERT is a silent no-op when the table is absent — or it fails, depending on whether the proc was created with a TRY/CATCH we haven't read yet — see Open Question 1).

Semantics of synthetic-trade rows: an instrument with cash_grid_rows > 1 (i.e., a payup grid is configured) gets one synthesized "what if I sold into THIS instead of into the base instrument" row per (rate, pricing window) tuple where the base instrument also exists in rmcat_todays_prices. The payup column is the dollar-difference in price between the synthesized and base instruments at that same rate/window.

Phase D — _all_rank population (lines 19083-19250)

A multi-CTE pipeline that joins pfill_powerfill_guide (the loans currently in the allocation), pfill_carry_cost, pfill_syn_trade_base, and a temp table #loan (which I haven't traced — likely populated earlier in the proc from loan + rmcat_loan).

For each (loan, synthesized trade) pair where:

• pfill_powerfill_guide.assign IN ('y','o','ox') — the loan currently has a non-skip assignment
• pg.eligible_date <= stb.settlement_date — loan would be eligible by the synthesized trade's settlement date
• stb.instrument_name <> stb.base_instrument_name — there's actually a payup (the synthesis is to a different instrument)
• stb.payup > 0 — payup is positive
• Cast(Datediff(d, COALESCE(l.close_date, l.lock_expiration_date), stb.settlement_date) AS NUMERIC(4,0)) BETWEEN cc.on_day AND cc.to_day — the carry-cost curve has a row for this loan's age bucket

…the row is inserted with four parallel rankings:

payup_only_rank          = ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY payup DESC, settlement_date ASC)
payup_plus_carry_rank    = ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY (payup + carry) DESC, settlement_date ASC)
price_only_rank          = (likely similar but on price)
prx_and_carry_rank       = (likely similar but on prx_and_carry)

The _all_rank table is the full ranked alternatives list — one (loan, trade) row per (loan, eligible synthesis) pair, with rank annotations.

Phase E — pfill_syn_powerfill_guide (the recommended synthesis, lines 19255-19347)

INSERT INTO pfill_syn_powerfill_guide (...)
SELECT ...
FROM pfill_syn_powerfill_guide_all_rank
WHERE (
    -- one of the rank-1 filters; truncated in the chunk we read
    payup_plus_carry_rank = 1  -- or similar
    ...
);

Then a logging insert.

Phase F — second-pass for loans not in pfill_powerfill_guide (lines 19450-19790)

There's a second INSERT INTO pfill_syn_powerfill_guide_all_rank block at line 19554 — populated for loans that are eligible for synthesis but were not assigned in the main allocation. These represent "loans we couldn't place but could place if we were willing to accept the synthesis." The second-pass pattern is:

LEFT JOIN pfill_syn_powerfill_guide spg ON spg_all.loan_id = spg.loan_id
WHERE spg.loan_id IS NULL  -- only loans NOT already in the recommended set

(Pattern at line 19734-19735.)

Phase G — consumption

Three downstream consumers identified:

1. psp_powerfill_pool_guide (NVO lines 8950-9160). Builds pfill_pool_guide (the user-facing Pool Guide tab). Joins:

LEFT JOIN pfill_syn_powerfill_guide spg ON pg.loan_id = spg.loan_id

…and uses CASE WHEN spg.syn_trade_id IS NULL THEN pg.trade_id ELSE spg.syn_trade_id END to substitute the synthesized trade for the actual one when a synthesis exists. Records trade_id_origin as either 'pscat_trades' or 'pfill_syn_powerfill_guide' so the user can tell what they're looking at.

2. psp_powerfill_pool_guide later UPDATE block (lines 11167-11181). Updates pfill_pool_guide.price and pfill_pool_guide.prx_and_carry to add the synthesis's payup and carry for rows where trade_id_origin = 'pfill_syn_powerfill_guide'. So the prices the trader sees on the Pool Guide already include synthesis payup.

3. pfillv_pf_forensics_tradeside view (NVO line 11187). The Forensics tab's view definition. Reads pfill_syn_powerfill_guide via the same trade_id_origin correlation. Lets the trader see why a particular trade-side row exists (real or synthetic) and what the synthesis added.

---

Upstream dependencies (Phase 6 must seed)

For the synthetic-trades subsystem to function, these tables must exist and contain data:

Table	Owner	Already in pssaas-db seed-schema?
pscat_instruments	upstream	Yes (per Phase 2 work)
rmcat_todays_prices	upstream	Yes
pfill_carry_cost	PowerFill	Yes (Phase 1 schema)
pfill_powerfill_guide	PowerFill	Yes (Phase 1 schema)
pfill_loan2trade_candy_level_01	PowerFill	Yes (Phase 1 schema; allocation working table)
pfill_trade_base	PowerFill	Yes (Phase 1 schema)
loan	shared	Yes
rmcat_loan	upstream	Yes
pfill_powerfill_log	unknown	NOT in pssaas-db; NOT in PS_DemoData — INSERT-only logging table referenced by the procedure but never created by of_update_database. See Open Question 1.

New requirement for Phase 6 schema work: add the 3 pfill_syn_* tables to 001_CreatePowerFillSchema.sql (they are not currently there because Phase 1 didn't know about them). The DDL is recoverable by reverse-engineering from PS_DemoData's information_schema.columns (already captured in this session's findings).

---

Open Questions for Tom/Greg

These are not blockers for Phase 6 planning, but should be on the list when human review becomes available.

1. pfill_powerfill_log table. Referenced by INSERT INTO dbo.pfill_powerfill_log at NVO lines 19080, 19252, 19345, 19647, 19750. Does not exist in PS_DemoData; not in the NVO's of_update_database. Either (a) the INSERTs silently fail in production and nobody noticed, (b) the table is created by a separate plugin or admin script we haven't found, or (c) the customer-deployed procedure differs from the NVO source we're reading. Worth asking.

2. Why DELETE not TRUNCATE on the run-output tables? psp_powerfillUE DELETEs pfill_powerfill_guide, pfill_syn_powerfill_guide, pfill_syn_powerfill_guide_all_rank, pfill_cash_market_map (lines 13441-13451) but TRUNCATEs pfill_syn_trade_base (line 19020). DELETE preserves identity counters and triggers; TRUNCATE doesn't. Likely intentional (syn_trade_id is the only identity column we've seen in this subsystem) but worth confirming.

3. Four parallel rank columns (payup_only_rank, payup_plus_carry_rank, price_only_rank, prx_and_carry_rank). Why all four? Different downstream consumers may use different rankings, OR the trader chooses which to apply via a UI option we haven't traced. The Pool Guide consumption (NVO 8950-9160) joins on syn_trade_id without a rank filter — implying the choice was already made when pfill_syn_powerfill_guide was filtered down from _all_rank. Which rank wins? See Phase E truncation note above.

4. pfill_powerfill_guide.assign codes. UE filters on assign IN ('y','o','ox') for synthesis eligibility (line 19124-19128) and EXCLUDES 'ycz', 'ypl', 'Ox' from the orphan re-mix (line 19006-19010). The full assign code vocabulary isn't defined in any spec we've found — likely y = yes/assigned, o = orphan, n = no, but the multi-character codes (ycz, ypl, ox lowercase vs Ox uppercase) need decoding. Greg or Tom would know.

5. Is there a configuration toggle to disable synthesis? Line 19038 hardcodes cash_grid_rows > 1 AND rm_trade_type_family = 'wl'. There's no @disable_synthesis parameter. So synthesis fires for any wl trade family with a payup grid. If a customer doesn't want synthesis (e.g., conservative ops who don't trade alternative instruments), there's no off-switch other than not configuring cash_grid_rows > 1 on any instrument. Worth confirming this matches operational reality.

---

Implications for PSSaaS

Phase 1 (already complete) — corrections needed

PowerSeller.SaaS.Modules.PowerFill.PowerFillModule does NOT register entities for the 3 pfill_syn_* tables. 001_CreatePowerFillSchema.sql does not create them. The Phase 1 deep dive's "17 tables" count needs to be amended to "17 tables declared by of_update_database + 3 pfill_syn_* tables declared elsewhere/pre-existing". Total PowerFill table count is 20, not 17.

This back-propagates to the Phase 1 devlog and assumptions A28 — already done in the assumptions log A28; should be reflected in the deep-dive doc when Phase 6 adds the entities.

Phase 4 (in progress) — no impact

Phase 4 scope is constraint / carry-cost / lockdown CRUD. The pfill_syn_* tables are run-output, not configuration. Confirmed out of scope.

Phase 6 (allocation engine) — direct impact

Phase 6 must include both psp_powerfill_conset AND psp_powerfillUE to achieve parity. The synthetic-trades subsystem is not optional — it shapes the user-facing Pool Guide and Forensics tab. Phase 6 schema work must include the 3 pfill_syn_* tables in 001_CreatePowerFillSchema.sql (or equivalent). Phase 6 entity work must add three more EF entity classes.

The architectural decision of whether to port psp_powerfillUE as a verbatim T-SQL procedure (per ADR-021) or to lift the synthesis logic into C# should be made explicitly when Phase 6 is planned. ADR-021 currently defers the decision implicitly to "T-SQL preserved" — but the synthesis logic is heavy CTE work that may benefit from C# orchestration if ADR-022's "verbatim port of iterative passes" doesn't extend cleanly to UE. Worth flagging.

Phase 7 (reports / recap APIs) — direct impact

The pfillv_pf_forensics_tradeside view depends on pfill_syn_powerfill_guide. The Forensics-tab API in Phase 7 must expose trade_id_origin so the API consumer can distinguish real trades from synthesized ones — the view already has this column (trade_id_origin VARCHAR(30) from the temp table at NVO 8929-8961).

Phase 9 (parity validation) — direct impact

Parity comparison against the Desktop App must include the synthesis output. A loan whose Desktop App Pool Guide shows trade_id = 'SYN_xxx' must match a PSSaaS Pool Guide row with the same synthesis identification. Without porting psp_powerfillUE, parity validation will show large false-negative deltas wherever syntheses fired.

---

Confidence assessment

High confidence on:

• Three tables exist, schema is as documented (PS_DemoData query 2026-04-16)
• psp_powerfillUE always runs after psp_powerfill_conset in ue_perform_powerfill (verbatim from w_powerfill.srw:170-172)
• Teardown/populate/consume lifecycle (read directly from NVO line ranges cited above)
• Three downstream consumers (psp_powerfill_pool_guide, the Pool Guide UPDATE block, pfillv_pf_forensics_tradeside view)

Medium confidence on:

• Exact rank-filter logic in pfill_syn_powerfill_guide population (Phase E) — only sampled the start of the INSERT; the full WHERE clause may use multiple rank tests
• Whether pfill_powerfill_log is genuinely orphaned or if a separate creation path exists somewhere

Low confidence on:

• Semantic decoding of assign codes (y/o/ox/ycz/ypl/Ox)
• Whether the four parallel rank columns are all used or if some are vestigial

---

Recommendation

Do not amend Phase 1 or Phase 4 deliverables based on this deep dive. A28 in the assumptions log already captures the finding correctly with a deferred-with-justification disposition.

Do cite this document directly in the Phase 6 Architect kickoff so the Architect inherits this analysis instead of repeating it. Specifically, the kickoff should:

1. List the 3 pfill_syn_* tables as Phase 6 in-scope schema additions
2. Require the Architect to read this document during onboarding
3. Surface the 5 Open Questions as Phase 6 PO-input questions
4. Ask the Architect to make an explicit ADR-021 disposition for psp_powerfillUE (verbatim T-SQL port vs C# lift vs hybrid) early in the plan

This is exactly the Phase-0 Truth Rot countermeasure pattern: catch it now, document it, hand it forward.