Skip to main content

ADR-025: PowerFill Report API Pattern

Status​

Proposed (PSSaaS Architect, Phase 7) — pending PO acceptance with the Phase 7 completion review.

Context​

The PowerFill spec (§Output APIs) requires 8 read endpoints (the 7 canonical PowerFill reports — Guide, Recap, Switching, Pool Candidates, Existing Disposition, Pooling Guide, Cash Trade Slotting — plus the Kickouts surface that spec line 363 also enumerates) over the pfill_* run-output tables. The endpoints are scoped to a {run_id} URL parameter.

Two structural facts about how PowerFill stores its output complicate the design:

  1. The pfill_* run-output tables hold only ONE run's output at a time. BR-9 clears the 7 user-facing tables on Failed/Cancelled runs (per A58); BR-10 overwrites them on the next Complete run. There is no per-run snapshot table at v1.

  2. The 4 syn-trades + log tables (per A58) survive BR-9 cleanup; the cash_market_map also survives (it's not in the BR-9 cleared list); the existing-disposition view reads upstream tables (loan, pscat_trades_pools_relation) that aren't run-scoped at all.

So the meaning of {run_id} in a Phase 7 endpoint URL needs an explicit semantic, and the PSSaaS-novel response-shape decisions (cursor pagination format, repository pattern, Note conventions) need a precedent-setting decision document since Phase 8+ may add risk reports, BestEx reports, etc. that should follow the same pattern.

This ADR records the three structural decisions made by Phase 7 (A7.1 / A7.2 / A7.3 in the Phase 7 plan + completion report), all of which were the Architect's call (not PO-confirmed defaults), framed via the Alternatives-First Gate.

A7.1 — {run_id} semantic: per-run vs current-state​

Three options considered:

Option A — Strict per-run​

Reject any {run_id} ≠ the latest Complete run for the tenant. Return 410 Gone for older runs.

  • Pros: Strongest contract; no surprise stale data.
  • Cons: Almost every URL gets 410 except the latest Complete run; client UX is poor; doesn't match how the data is actually stored (the audit row's response_json snapshot already gives clients access to historical RunResponses via GET /runs/{run_id} from Phase 6e).

Option B — Latest-Complete-wins (chosen)​

Validate {run_id} exists for the tenant; classify the freshness; return data from the current pfill_* tables IF the audit row matches the latest run. Otherwise return 410 Gone with the latest run id in the body for client redirection.

  • Pros: Honest about how the data is actually stored; matches the spec's existing language ("Output APIs (queries against pfill_* run-output tables, scoped to the current/latest run or a specified run_id)" — spec line 352); 410 Gone is the right semantic for "this run existed but its output has been overwritten"; future Phase 7+ can add Option C without breaking the contract.
  • Cons: Most older {run_id} URLs return 410 (acceptable — clients can fetch the latest run id from the body Note).

Option C — Snapshot replay​

Per-run snapshot tables (pfill_run_history_guide etc.) capture full output per run; endpoints read from snapshots.

  • Pros: Truly per-run; historical replay; matches {run_id} semantics fully.
  • Cons: Massive scope expansion (Q3 Option C — explicitly Phase 7+ per spec line 263); doubles storage; not in Phase 7 scope per kickoff.

A7.2 — Repository pattern: umbrella service vs per-report repositories​

Three options considered:

Option A — Single PowerFillReportService with one method per report (chosen)​

Task<GuideReportResponse> GetGuideAsync(...), Task<RecapReportResponse> GetRecapAsync(...), etc. (8 methods including kickouts).

  • Pros: One DI registration; one tenant-scope; one place to enforce A7.1 freshness logic; consistent telemetry; matches existing PSSaaS patterns (PowerFillRunHistoryService, PowerFillConfigurationService, PowerFillPreflightService are all single multi-method services).
  • Cons: One large file (~800 LOC); some report methods are independent enough that they could parallelize cleanly.

Option B — Per-report IRunReportRepository<T> with shared base​

7-8 small files; each ~50-100 LOC.

  • Pros: More cohesive per file; easier to test individually.
  • Cons: More DI registrations; A7.1 enforcement (run_id validation) gets duplicated 8x or extracted to a base/helper anyway; testing infrastructure ramps up; no precedent in PSSaaS for per-report repositories.

Option C — Hybrid: shared RunOutputResolver + 8 small IReadOnly*ReportQuery services​

Resolver returns a ReportContext (audit row + freshness verdict); each query method is one extension method on TenantDbContext keyed off the context.

  • Pros: Best separation of concerns; A7.1 in one place; queries are pure projection.
  • Cons: Most ceremony; over-engineered for 8 endpoints.

A7.3 — Pagination cursor format​

Three options considered:

Option A — Keyset on natural composite PK (chosen)​

Each report has its natural PK (Guide=loan_id, Recap=trade_id+rate, Pool Candidates=loan_id+trade_id, Kickouts=loan_id+top_trade_id). Cursor is the last PK seen. For multi-column PKs, use ?after_<col>=<value> query-param pairs.

  • Pros: Stable across re-queries; standard pattern; matches Phase 6e's existing ?before=<run_id> cursor pattern (single-column on run_id); type-safe cursor record per report.
  • Cons: Multi-column PKs need encoding (mitigated by per-column query params and per-report cursor record types).

Option B — Offset-based ?offset=N​

Simplest. But skips on duplicate timestamps; not stable for parallel queries.

Option C — Opaque server-issued token (base64-encoded JSON)​

Hides implementation detail; future-proof. But more plumbing; client can't easily slice arbitrarily.

Decision​

A7.1 = Option B (latest-Complete-wins), A7.2 = Option A (single umbrella service), A7.3 = Option A (keyset on natural composite PK).

Specifically​

  • PowerFillReportService — scoped, one method per report; private helper ResolveRunContextAsync runs the freshness check via PowerFillRunHistoryService.GetByIdAsync + a "latest run for this tenant" probe; returns a RunReportContext (Freshness enum + audit row + latest_run_id).

  • RunReportFreshness enum — 4 verdicts: Current, RunNotFound, Stale, TerminalEmpty. Each maps to a specific HTTP status + Note shape per the spec table at §Output APIs.

  • Cursor pagination — single-column ?after=<value> for Guide; multi-column ?after_<col>=<value> for Recap (after_trade_id + after_rate), Pool Candidates (after_loan_id + after_trade_id), Kickouts (after_loan_id + after_top_trade_id). Default ?limit=100, max 1000.

  • Per-report Note field — every response wrapper carries a note: string? so the service can communicate freshness verdicts (Stale, TerminalEmpty) AND per-report semantic clarifications (e.g. "Cash market map survives BR-9 per A58 but per-trade slot fields require Step 1") without expanding the wire shape.

  • HTTP status routing — RunEndpoints' BuildReportResult<T> helper inspects the response Note for the Stale prefix and emits 410 Gone in that case; otherwise 200 OK or 404 Not Found.

  • Cursor types — RecapCursor, PoolCandidateCursor, KickoutCursor are sealed records with snake_case [JsonPropertyName] properties matching the SUT's keyset query parameters.

Rationale​

The latest-Complete-wins semantic (A7.1 Option B) honestly reflects how PowerFill's data is stored at v1 (single-run tables; BR-9/BR-10 govern lifecycle). A 410 Gone with the latest run id in the body lets clients build a "you're viewing stale data — switch to the latest run" UX in Phase 8 without per-row complexity. When Phase 7+ adds snapshot replay (Q3 Option C), the endpoint contract doesn't change — only the data source does.

The umbrella service (A7.2 Option A) matches every other PSSaaS service pattern. Per-report repositories would have triplicated freshness-check logic across 8 files; the umbrella service centralizes A7.1's enforcement to one helper method.

The keyset cursor (A7.3 Option A) matches Phase 6e's existing ?before= pattern. Per-column query params for multi-column PKs are explicit + type-safe (no opaque token to decode); the cursor types carry the PK shape clearly so clients can construct them programmatically.

Consequences​

Positive​

  • Single design centerpoint for all future PowerFill report endpoints (Phase 8+ may add additional reports as Superset slices materialize requirements).
  • Phase 8 (React UI) gets a clean contract — every endpoint follows the same pattern: 4 freshness verdicts, snake_case JSON, optional cursor, optional Note.
  • Phase 9 parallel-validation can compare PSSaaS responses to Desktop App reports per-row without juggling pagination shape variants.
  • A58's preservation scope (cash_market_map + 4 syn-trades + log tables NOT BR-9-cleared) becomes observable from the read APIs (per F-7-8: Cash Trade Slotting returns 688 real rows on PS_DemoData even on Failed runs).
  • The PSSaaS-side PoolDispositionReadModel schema-drift on PS_DemoData (per A62 — view note_rate column missing) is gracefully handled by the service catching SqlException 207/208 and degrading to an empty payload + explanatory Note. Clients see the issue rather than a 500.

Negative​

  • No per-run snapshot replay at v1. Older runs return 410 Gone (clients must redirect to the latest). Snapshot replay is Phase 7+ (Q3 Option C).
  • {run_id} URL parameter is largely advisory for older runs (almost always returns 410 for non-latest). Clients should generally call GET /runs first to get the latest run_id, then call report endpoints with that id.
  • The umbrella service is large (~800 LOC). If Phase 8+ adds many more report methods, refactor to per-report file split becomes attractive.

Operational​

  • No SQL deploys this phase — Phase 7 is a read-only surface over the existing 23-table schema + Phase 2's existing view + the new upstream PscatTradeCashGrid keyless EF entity (per A61 — needed only because the legacy table reference is referenced by 003+ procedures and the Phase 7 service may grow to model the join in Phase 9).
  • Pre-push docs-build check (per Phase 6e MDX-trap lesson): docker build -f docs-site/Dockerfile.prod docs-site before pushing this ADR + spec amendments + completion report.

Alternatives Reconsidered​

These options remain viable for later phases when their trade-offs change:

  • A7.1 Option C (Snapshot replay) — when historical-replay debugging becomes a real need (e.g. Phase 9 parallel-validation requires re-running a specific historical run against current data), add pfill_run_history_guide / pfill_run_history_trades snapshot tables. The endpoint contract doesn't change.
  • A7.2 Option B (Per-report repositories) — when the report count grows to 20+ (Phase 8 Superset dashboards may surface this requirement), split the umbrella service into per-report files.
  • A7.3 Option C (Opaque token cursor) — when the cursor shape needs to change (e.g. add timestamp-based ordering instead of PK-based), wrap the existing keyset in a base64 JSON token without breaking client code that already passes the explicit ?after_<col> params.

Future Considerations​

  • Phase 8 (React UI + Superset) consumes these endpoints. The Note field is the key UX signal — clients should branch on run_status and note to render appropriate freshness indicators.
  • Phase 9 (Parallel validation) uses the Phase 7 endpoints to compare PSSaaS allocations to Desktop App allocations per-loan / per-trade. The Cash Trade Slotting + Existing Disposition reports (which read non-BR-9-cleared sources) provide validation paths that work even when A54 short-circuits Step 5 on PS_DemoData (per F-7-8 demonstration: 688 cash_market_map rows surface from a Failed run).
  • Per-tenant rate limiting — the unbounded reports could be expensive on large tenant DBs (e.g. Pool Candidates ~ 15K rows × N constraints). Phase 7+ should consider per-tenant rate limits + cache headers.
  • Snapshot replay (Q3 Option C) — see Alternatives Reconsidered above.
  • Schema drift (A62) — Backlog #24's deferred deploy of 002_CreatePowerFillViews.sql to PS_DemoData should be picked up in Phase 9 (parallel-validation surfaces the behavior diff against the encrypted definition); fix is either deploy + behavior-diff acceptance OR rename PSSaaS view to pfillv2_*.

Source References​