p1-sys-2047-redis-sla-cache

donetype/backlogtech/redis

Backlog: SYS-2047 — Redis SLA cache (CMS backend)

TL;DR

Shipped (status: done in frontmatter). Redis-backed caching for SLA master reads (sla_priority, working hours, holidays, custom rules) on the contract portal backend, reusing the same Redis as BullMQ with key prefix cms:sla:v1: — no extra npm dependency (ioredis comes via BullMQ). Includes dashboard batch priority map + calculateWorkingDays calendar short-circuit (SYS-2026, SYS-2024).

ClickUp

Implementation (code)

  • Backend docs: backend-contract-portal-dev/src/modules/sla/README.md
  • Hot path: cover-letter-dashboard.helper.ts processContractList passes Map<string, slaPriority> into resolveSlaResponseWorkingDays
  • Redis client: src/modules/sla/cache/sla-redis-cache.service.ts (disable with SLA_REDIS_ENABLED=false)

Acceptance

  • Priority list + calendar + custom rules cached with TTL + invalidation on WH/holiday/rule writes
  • Graceful degrade when Redis unavailable
  • Unit tests extended (sla-fetch.service.spec.ts)

Implementation notes (vault)

Implementation summary from Cursor agent transcript (vault triage 2026-05-13; source inbox capture consumed).

Repo / epic

  • Contract portal backend (backend-contract-portal-dev). Tracks SYS-2047 (Redis SLA cache), aligned with SYS-1970 perf breakdown (SYS-2026 N+1 priorities, SYS-2024 redundant calendar context).

Redis SLA cache

  • Reuse existing BullMQ Redis (REDIS_HOST / REDIS_PORT / REDIS_PASSWORD); no separate Redis dependency — ioredis not added as a direct package.json dep (still available transitively via BullMQ).
  • SlaRedisCacheService — timeouts, graceful fallback on errors; SLA_REDIS_ENABLED=false disables Redis reads/writes.
  • Key namespace: cms:sla:v1:* (TTLs per README).
  • SlaFetchService — cache hits for priority list, calendar (working hours / holidays), custom rules; invalidateCache / invalidateCustomRulesCache delete matching Redis keys on writes.
  • Hot-path: cover-letter-dashboard.helper.ts processContractList loads sla.getList(true) once → Map<string, slaPriority> passed into resolveSlaResponseWorkingDays (removes per-row findOne).
  • calculateWorkingDays — only getCalendarContext() when holidays or businessHours missing (honours prefetch).

Docs / tests

  • src/modules/sla/README.md — keys, TTLs, env flags.
  • sla-fetch.service.spec.ts — mocked Redis client; hit/miss/fallback behaviour.

TypeORM LATERAL join regression (dashboard SQL)

  • Symptom: Postgres invalid reference to FROM-clause entry for table "cvl" on routes using dashboard subqueries (e.g. getCountDashboardLegal), after attempts to satisfy TypeORM’s string-join getTableName() behaviour on cms_schema.table inside LATERAL (...).
  • Bad fix: Wrap as (SELECT * FROM LATERAL (...) …) — turns lateral into a non-lateral derived table → cvl no longer in scope.
  • Good fix (runner.helper.ts): Detect raw LATERAL (...) join repos and push a JoinAttribute whose alias uses subQuery (same mechanism TypeORM uses for parenthesized subquery joins). SQL stays LEFT JOIN LATERAL (...) alias ON TRUE. applySubQueryJoin used from selectQueryBuilder and selectQueryBuilderRaw.
  • Dashboard lateral definitions unchanged in cover-letter-dashboard.helper.ts getSubQueryDashboard() (reviews, cancel, hardcopy, customer, etc.).

Related

  • [[Projects/anabatic-contract-management-system/backlog/p1-fix-performance-issues]]
  • [[Projects/anabatic-contract-management-system/context/sys-1970-cms-performance-issues-breakdown]]
  • [[Projects/anabatic-contract-management-system/reports/backend-cover-letter-query-performance-audit]]