Scope: backend-contract-portal-dev — cover letter fetch services, dashboard/export helpers, SLA service, user fetch helpers used by tracking flows.
Date: 2026-05-11
Last updated: 2026-05-17 — marked completed mitigations (SYS-2047 / SYS-2026 / SYS-2024, dashboard LATERAL + runner.helper join fix); linked SYS-1970 / SYS-2022 vault notes and flagged processContractList dedupe vs execute report.
Repos / paths: src/modules/cover-letters/fetch/, src/helpers/modules/cover-letter-dashboard.helper.ts, src/helpers/modules/cover-letter.helper.ts, src/helpers/function.helper.ts, src/helpers/runner.helper.ts, src/modules/sla/fetch/sla-fetch.service.ts, src/modules/users/fetch/users-fetch.service.ts
Findings below are sorted by typical production impact (highest first). Status callouts note what is already shipped vs still open.
LEFT JOINs (no dedupe in many callers)Impact: Very high — larger intermediate joins, duplicate rows, wasted bandwidth/CPU; wrong semantic row if multiple child rows exist.
Status — partially addressed (dashboard): CoverLetterDashboardHelper.getSubQueryDashboard() now uses LEFT JOIN LATERAL ( … ORDER BY … LIMIT 1 ) for the main 1:N children (reviews, t_review_cancel, hardcopy_submissions, t_customer, subscription, etc.) so each alias returns at most one row per cvl row instead of multiplying the cartesian product. TypeORM string joins would mangle schema.table inside the lateral body; RunnerHelper detects raw LATERAL (...) repos and registers them as JoinAttribute + alias.subQuery so emitted SQL stays LEFT JOIN LATERAL (...) rev ON TRUE with correct correlation to cvl (see [[Projects/anabatic-contract-management-system/backlog/done/p1-sys-2047-redis-sla-cache]]).
// cover-letter-dashboard.helper.ts — current pattern (representative)
{
alias: 'rev',
repo: `LATERAL (
SELECT * FROM ${schemaPrefix}reviews rev1
WHERE rev1.cover_letter_id = cvl.cover_letter_id
ORDER BY rev1.review_id DESC LIMIT 1
)`,
joinType: 'LEFT',
joinClause: 'TRUE',
useMap: false,
},
Still open: legacy cover-letter-fetch.service.ts getSubQuery() and other code paths that still use plain 1:N LEFT JOIN without LATERAL / dedupe.
Vault / repo sync: [[Projects/anabatic-contract-management-system/context/clickup-execute-sys-2022-2026-05-13]] documents removal of in-memory dedupe from
processContractListfor the dashboard path (2026-05-13). The snippet below may be stale if that shipped; reconcile againstcover-letter-dashboard.helper.tsinbackend-contract-portal-dev. If dedupe remains, it is an arbitrary first-row safety net whenever any join still multiplies.
// cover-letter-dashboard.helper.ts
public async processContractList(dataContractList: any[]): Promise<any[]> {
// Dedupe by cover_letter_id: one-to-many JOINs (...) cause getRawMany() to return multiple rows per contract.
const seen = new Set<number>();
const deduped = dataContractList.filter((row) => {
const id = row.cover_letter_id;
if (seen.has(id)) return false;
seen.add(id);
return true;
});
Further mitigations (remaining callers): DISTINCT ON (cvl.cover_letter_id) or port the same LATERAL pattern to legacy fetch SQL.
trackingCovlet list mode — full getMany() + full dashboard joins for IDs onlyImpact: Very high — hydrates entire entities + join graph when only tran_code / cover_letter_id are needed.
// cover-letter-fetch-new.service.ts
const cover_letter_list = await this.runner.selectQueryBuilder({
mainRepo: coverLetters,
mainRepoAlias: 'cvl',
returnValue: true,
// select: this.covletDashboardHelper.getSelect(),
subQuery: this.covletDashboardHelper.getSubQuery(),
whereClause: listWhereClause,
});
With select omitted, RunnerHelper uses getMany(), not a slim raw projection.
Mitigations: Explicit select for tran_code and/or cover_letter_id; drop getSubQuery() on this branch.
getAllCoverLetter, exports, mention-driven IN (...)Impact: Very high at scale.
getAllCoverLetter — no WHERE, no LIMIT → full table scan behavior as data grows.exportCoverLetterForAcct — covletHelper.getSelect() + getSubQuery() + filters but no pagination (same class as large dashboard exports).trackingCovlet loads all mentions for user then OR cover_letter_id IN (...) — huge arrays hurt planning and throughput.// cover-letter-fetch-new.service.ts — mentions then OR + IN
const mentionsList = await this.runner.selectQueryBuilder({
mainRepo: mentions,
mainRepoAlias: 'ment',
returnValue: true,
whereClause: [
...helper.setWhereClause({
repoAlias: 'ment',
whereField: 'mentioned_user_id',
value: session.id_sinta,
}),
],
});
const coverLetterMentionsIDs = mentionsList.map((v) => v.cover_letter_id);
// ... pushes OR + IN(coverLetterMentionsIDs)
Mitigations: Pagination/streaming export; EXISTS/join to mentions instead of materializing all IDs; cap/recent window on mentions.
resolveSlaResponseWorkingDays → uncached slaPriorityRepo.findOne per row (N+1)Impact: Very high on dashboards that enrich hundreds/thousands of rows.
Status — addressed (SYS-2026 + SYS-2047): processContractList loads sla.getList(true) once, builds a Map<string, slaPriority>, and passes it as priorityById into resolveSlaResponseWorkingDays(val, priorityById) so the dashboard row loop does not call getById → findOne per row. sla.getList / getById also read through Redis (cms:sla:v1:*) when enabled — see src/modules/sla/README.md in the backend repo.
Other callers can still hit getById per call if they omit priorityById (e.g. legacy contractMention / export paths — §7).
// sla-fetch.service.ts — optional bulk map (dashboard uses this)
async resolveSlaResponseWorkingDays(
val: Record<string, unknown>,
priorityById?: Map<string, slaPriority>,
): Promise<number | null> {
// … delegates to lookupSlaPriorityWorkingDays(key, priorityById)
}
// cover-letter-dashboard.helper.ts — processContractList (pattern)
const priorityById = /* Map from sla.getList(true) */;
await this.slaFetchService.resolveSlaResponseWorkingDays(val, priorityById);
Mitigations (done for dashboard batch): In-request Map — shipped. Optional: extend the same pattern to contractMention / export loops (§7) if they still call resolveSlaResponseWorkingDays without a map.
calculateWorkingDays always awaits getCalendarContext() even when holidays/hours passedImpact: Medium–high under concurrency (redundant async + cache churn).
Status — addressed (SYS-2024): calculateWorkingDays now calls getCalendarContext() only when holidays or businessHours is missing, so processContractList’s prefetch is respected.
// sla-fetch.service.ts — current behaviour
const needCtx =
holidays === undefined || businessHours === undefined;
const ctx = needCtx ? await this.getCalendarContext() : null;
const cachedBusinessHours = businessHours ?? ctx!.businessHours;
const cachedHolidays = holidays ?? ctx!.holidays;
Callers like processContractList already prefetch holidays/business hours once per batch.
Mitigations: If both holidays and businessHours are defined, skip getCalendarContext() — implemented.
dateRangeBit predicates — TO_DATE(TO_CHAR(column)) breaks btree-friendly filteringImpact: Medium–high on large cover_letters — requested_date range scans become expensive.
Used by requestedDate_whereClause in both dashboard helper and cover-letter.helper.ts:
// function.helper.ts — setWhereClause branch
} else if (value.dateRangeBit) {
let format = value.dateRangeFormat ? value.dateRangeFormat : 'yyyy-mm-dd';
res = {
...res,
query: `TO_DATE(TO_CHAR(${value.repoAlias}.${value.whereField}, '${format}'), '${format}') ${value.dateRangeOperator} TO_DATE(:${value.whereField}_id_${uid}, '${format}')`,
Mitigations: Compare native timestamps/date columns to bound range parameters; add matching indexes / expression indexes only if you must keep casts.
contractMention — sequential post-query SLA workImpact: Medium–high wall time when mention list is large.
Status — still open: Dashboard processContractList benefits from §4–§5; this legacy path is unchanged unless refactored.
After one heavy query, a for loop awaits per row:
coverLetterHelper.getWeekdaysBetweenDates → calculateWorkingDays → always getCalendarContext() (see §5).covletDashboardHelper.resolveSlaResponseWorkingDaysForExport → resolveSlaResponseWorkingDays → possible DB getById per row (§4).// cover-letter-fetch.service.ts — contractMention tail
for (const coverLetter of coverLetterMentions) {
coverLetter.due_In = await this.coverLetterHelper.getWeekdaysBetweenDates(/* ... */);
coverLetter.aging_days = coverLetter.legal_received_date
? await this.coverLetterHelper.getWeekdaysBetweenDates(/* ... */)
: null;
coverLetter.sla_response_working_days_resolved =
await this.covletDashboardHelper.resolveSlaResponseWorkingDaysForExport(coverLetter);
}
Mitigations: Batch parallel like processContractList + prefetch calendar once + SLA ID cache.
Impact: Medium.
Legacy fetch — TO_CHAR / CASE / TO_DATE on dynamic columns (contractRequestAccounting, contractMention) — prevents simple index use on date columns.
// cover-letter-fetch.service.ts — example pattern
whereField: `(TO_CHAR(cvl."${filter.col}",'yyyy-mm-dd') >= '${filter.startDate}' AND ...)`,
// ...
whereField: `1 = CASE WHEN cvl."${filter.col}_type" = 'Variable' THEN 1 WHEN ... TO_DATE(...) ...`,
checkLogNumber — LIKE + TRIM/LOWER; EXTRACT(YEAR FROM timestamp) in CASE — awkward for indexes.
covlet_search_whereClause — LOWER(TRIM(col)) LIKE '%…%' across arbitrary keys.
getPreviousLogNumber — duplicate DB calls (4 round-trips vs 2)Impact: Medium (clear waste).
// cover-letter-fetch-new.service.ts
log_number.push(...(await this.getLogNumber(...)));
console.log(await this.getLogNumber(...)); // duplicate query
log_number.push(...(await this.getAgreementNumber(...)));
console.log(await this.getAgreementNumber(...)); // duplicate query
cover-letter-fetch-new.service.ts — other hotspotsImpact: Low–medium.
| Item | Note |
|---|---|
viewOneCoverLetter | Joins reviews twice (review + rev) into same relation — redundant work. |
generateTrancode / collision retry | Multiple sequence read/write + existence checks per collision — consider transactional sequencing / advisory lock. |
getContractDocuments | Calls full getOneCoverLetter before t_document query — heavy when only filenames/history needed. |
teamMemberLeveling | OR on n1–n4 + LEFT JOIN TUserEntity — verify indexes / explain on prod-like data. |
getAccessProduct | No WHERE on MProductAccess — full catalog load whenever called (e.g. trackingCovlet still invokes it). |
RunnerHelper — offsetClause applies limit instead of offsetImpact: Medium if pagination is used — wrong SQL semantics.
// runner.helper.ts (observed pattern)
if (value.offsetClause) {
model.limit(value.offsetClause.offsetNumber);
}
Expectation: model.offset(...).
reviewsImpact: Low–medium join cost; possible correctness smell.
coverLetterHelper.getSubQuery() adds multiple Employee aliases. bu_n1 is joined on rev.legal_reviewer_id while selected fields suggest BU / non-legal naming — worth validating against domain model.
// cover-letter.helper.ts
{
alias: 'bu_n1',
repo: Employee,
joinType: 'LEFT',
joinClause: 'bu_n1.id_sinta = rev.legal_reviewer_id',
useMap: false,
},
EXPLAIN (ANALYZE, BUFFERS) on heaviest endpoints (dashboard list, export, tracking list).COUNT(*) of raw SQL vs COUNT(DISTINCT cover_letter_id) — ratio surfaces join multiplication.sla_priority lookups per HTTP request — for dashboard list paths, expect one list read (plus Redis when enabled), not per-row findOne.LATERAL / runner.helper fix).Implemented in backend-contract-portal-dev: Redis keys under cms:sla:v1:*, same Redis host as BullMQ; ioredis is not an extra direct npm dependency (resolved via BullMQ). Dashboard batch-loads SLA priorities once per list, calculateWorkingDays skips redundant calendar fetch when args are present, and runner.helper correctly emits correlated LATERAL joins for getSubQueryDashboard(). Repo docs: src/modules/sla/README.md.