payment-request-module-performance-report

activetype/docdomain/performance

Payment Request Module — Performance Audit Report (Synthesized)

TL;DR

Synthesized audit of backend-claim-dev payment-request module: prioritize bounded list APIs, push approver/finance filters into SQL, batch post-mutation reads, then counters/logging and list-vs-detail query splits. Measurement plan covers HTTP latency, DB statement counts, and payload size.

Scope: backend-claim-dev payment request module (fetch/trans/history/controllers/helpers).
Source: Parallel audits (DB/query patterns, service logic, API payload/shaping).
Date: 2026-05-12

Executive summary

Main issues are unbounded list traffic, SQL that pulls large candidate sets then filters in Node (especially approver/finance), N× heavy detail reloads after batch mutations, and chatty or redundant counter queries. Secondary costs: default wide joins on some list paths, hot-path logging, response mapping/transform overhead, and tracking/detail over-fetch.

P0 — Fix first (largest impact)

| Priority | Finding | Notes | | -------- | ---------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | P0 | Unbounded role-based list endpoints (requestor / approver / finance) | Add page + limit through controller → service → fetchPaymentRequest; enforce a max limit (e.g. 100). | | P0 | Approver/finance “my turn” semantics in application code after wide DB fetch | Push eligibility into SQL (e.g. subquery on t_approval_steps for current waiting step + approver_employee_id), or maintain denormalized current_approver_employee_id (or equivalent) on t_payment_requests updated on workflow transitions. | | P0 | markAsPaid (and similar): N × getPaymentRequest(id) after bulk save | Single batched read WHERE payment_request_id IN (...) with minimal projection matching API contract; avoid full graph per ID. |

Primary files (indicative):

  • src/modules/payment-request/controllers/payment-request-fetch.controller.ts
  • src/modules/payment-request/services/payment-request-fetch.service.ts
  • src/modules/payment-request/services/payment-request-trans.service.ts
  • src/helpers/payment-request.helper.ts

P1 — High value, low-to-medium risk

| Priority | Finding | Mitigation | | -------- | -------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------- | | P1 | Requestor dashboard counters: five sequential getCount round-trips | Promise.all the five counts, or one SQL with COUNT(*) FILTER (WHERE …) over shared base filter. | | P1 | Approver/finance counters: duplicate or full-list reads with mapped steps | Deduplicate fetches; derive multiple metrics in one pass; prefer SQL counts where semantics allow. | | P1 | fetchPaymentRequest pagination: two-phase ID query only when mapManyJoins.length > 1 | Use two-phase / correct DISTINCT strategy when any mapMany join exists (>= 1) to avoid join-row multiplication breaking LIMIT/OFFSET. | | P1 | Hot-path console.log in fetchPaymentRequest (and similar) | Remove or guard with env + Logger.debug. |

P2 — Structural / maintainability

| Priority | Finding | Mitigation | | -------- | -------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------- | | P2 | List vs detail query shapes not separated enough | List: header + minimal steps (and settlement only where UI needs it). Detail: full graph (items, attachments, nominatif, etc.). | | P2 | Tracking flow loads full getPaymentRequest then history | Narrow query: header + steps (+ fields actually used by generateTrackingFromRequest); avoid items/attachments unless required. | | P2 | Multiple shallow copies per row (mapPaymentRequestFields + .map) | Single-pass mapper; measure allocation/CPU on large lists. | | P2 | Global response interceptor deep-clone for bigint coercion | Profile; consider targeted transforms or serializer-level bigint handling for list payloads. |

P3 — Indexing, search, caching

| Priority | Finding | Mitigation | | -------- | -------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------ | | P3 | Non-sargable predicates (LOWER(TRIM(...)), leading % ILIKE) on UAM/vendor lookup helpers | Normalized columns, citext/trigram indexes, or structured joins instead of pattern glue. | | P3 | Reference endpoints (vendor-list, division-list, tiering rules, etc.) | Short Cache-Control: private, max-age=… and/or ETag if versioning/timestamps allow. | | P3 | Sequential file uploads / emails in loops | Bounded concurrency or queue (BullMQ) for bulk side effects; watch rate limits. |

Suggested implementation order

  1. Week 1 (quick wins): Pagination + limit cap on role list routes; remove hot console.log; parallelize counter getCount calls where safe.
  2. Week 2 (major gains): SQL-level approver/finance filtering; batched post-mutation reads (markAsPaid).
  3. Week 3 (correctness/stability): Fix mapMany pagination branch; introduce list-summary vs detail queries; narrow tracking fetch.
  4. Week 4+: Search/index tuning; optional caching; response-transform optimization after profiling.

Measurement plan (before / after)

| Metric | How | | ---------------- | -------------------------------------------------------------------------------------------------------- | | HTTP p50/p95/p99 | Per route: role list endpoints, GET /requests, counters, GET :id, tracking. | | DB | Statement count per request; EXPLAIN (ANALYZE, BUFFERS) on representative filters; pg_stat_statements. | | Payload | Serialized JSON size or Content-Length in staging. | | App | CPU/GC under load (e.g. k6). |

Success examples: p95 for role lists drops materially; rows returned per request bounded by limit; median list JSON size drops after projection/join trim.

Rollout notes

  • Add page/limit as backward-compatible optional query params with safe defaults; verify frontend tab UIs before removing “return all” behavior.
  • Validate counter semantics after merging SQL or changing deduplication (approver position / finance rules).
  • Verify tracking/history API envelope with consumers if controller response shape is normalized (avoid double-wrapping).

Related

  • [[Projects/anabatic-claim/backlog/p1-data-table-fetch-performance]]
  • [[Projects/anabatic-claim/backlog/p1-stress-test-performance-failure]]
  • Codebase: backend-claim-dev (NestJS payment-request module paths cited in body).

Document length: ~150 lines | Last updated: 2026-05-17