clickup-execute-sys-2022-2026-05-13

activetype/docdomain/execution

ClickUp Execute Report — SYS-2022 — [Audit] Fix row multiplication from 1:N LEFT JOINs in cover letter queries

TL;DR

Implemented SQL-level one-row-per-cover-letter joins in the dashboard helper so result correctness no longer depends on arbitrary first-row dedupe. Removed the old dedupe path from post-processing and documented the rule in module docs. Staging query-plan/runtime verification is still needed to fully close the acceptance criteria.

ClickUp Context

  • Task: https://app.clickup.com/t/86b9x94n7
  • Status: to do
  • Assignees: none
  • Due date: none
  • Parent/Subtasks: parent 86b9uvy8f, no subtasks returned
  • Problem statement from task: 1:N LEFT JOIN chains multiply rows per cover_letter_id; current dedupe keeps the first row and can return arbitrary child data.

Obsidian Context (Projects)

  • [[Projects/anabatic-contract-management-system/reports/backend-cover-letter-query-performance-audit]]: source audit and exact remediation guidance (Point #1).
  • [[Projects/anabatic-contract-management-system/backlog/p1-sys-2022-cover-letter-join-cardinality]]: vault backlog stub for SYS-2022 (ClickUp + open AC).
  • [[Projects/anabatic-contract-management-system/anabatic-contract-management-system]]: project hub anchor for this execution report.
  • Key audit recommendation applied: force one-row-per-cover-letter at SQL join time, not post-query filtering.

Obsidian Context (Resources)

  • No directly relevant reusable technical reference found in Resources/ for this specific query issue; implementation was driven by project audit evidence.

Proposed Execution Plan

  1. Patch multiply-prone joins in dashboard helper to deterministic single-row joins per cover_letter_id.
  2. Remove arbitrary in-memory dedupe (first row wins) from processContractList.
  3. Update module docs to codify one-row-per-cover-letter query rule.
  4. Run targeted validation and capture outcomes + gaps.

Executed Changes

  • Updated src/helpers/modules/cover-letter-dashboard.helper.ts:
    • reviews join now binds to latest row by MAX(review_id) per cover_letter_id.
    • t_review_cancel join now binds to latest row by MAX(review_cancel_id) where cancel status is null.
    • hardcopy_submissions join now binds to latest row by MAX(hardcopy_submission_id).
    • t_customer join now binds to latest row by MAX(customer_id).
    • t_subscription join now binds to latest row by MAX(subscription_id).
    • Removed post-query Set-based dedupe in processContractList; processing now runs directly on SQL output.
  • Updated src/modules/cover-letters/docs/README.md:
    • Added explicit guidance for one-row-per-cover-letter join strategy and cardinality validation target.
  • Deviation from initial plan: no list status transition in ClickUp; attempted status update to in progress failed (Status does not exist) for this list config.

Acceptance Criteria Mapping

  • Result set cardinality aligns with COUNT(DISTINCT cover_letter_id).
    • Mapped verification: run SQL cardinality comparison on staging for affected dashboard/list queries.
    • Current state: pending staging verification.
  • No arbitrary first-row dedupe path remains for this query path.
    • Mapped verification: inspect processContractList for dedupe logic removal.
    • Current state: done.
  • Query plan/runtime improves on staging for dashboard/list endpoints.
    • Mapped verification: run EXPLAIN (ANALYZE, BUFFERS) before/after on staging.
    • Current state: pending staging verification.

Risks and Unknowns

  • Risk: using MAX(<pk>) assumes increasing PK correlates with latest intended child snapshot.
    • Impact: could still select an unexpected child record if business semantics differ from insert order.
    • Mitigation: validate sampled records vs expected latest business event on staging.
  • Unknown: list-specific valid status labels in ClickUp for this task.
    • Mitigation: read allowed statuses from list config before next automated status transition.

Validation Plan

  • Run helper-targeted tests (if present).
  • Run repository tests to ensure no direct regressions in changed compilation path.
  • On staging DB:
    • Compare raw rows vs COUNT(DISTINCT cover_letter_id) for equivalent filters.
    • Run EXPLAIN (ANALYZE, BUFFERS) for affected dashboard/list endpoints and compare with baseline.

Validation Results

  • npm test -- cover-letter-dashboard.helper --runInBand
    • Result: no matching tests found for that helper path.
  • npm test -- --runInBand
    • Result: existing repo failures unrelated to this ticket:
      • SLA controller time-normalization expectation mismatches.
      • legal-service-request helper spec type/import errors.
  • ReadLints on changed files: no linter errors.

Next Actions

  • Backend owner: run staging SQL cardinality + EXPLAIN (ANALYZE, BUFFERS) on dashboard/list query paths touched by cover-letter-dashboard.helper.ts.
  • Product/QA owner: confirm deterministic-child-row selection semantics are acceptable (MAX(id) as "latest").
  • Dev owner: after staging verification, update ClickUp status with list-valid value and attach evidence snapshots.

Sources

  • https://app.clickup.com/t/86b9x94n7
  • [[Projects/anabatic-contract-management-system/reports/backend-cover-letter-query-performance-audit]]
  • [[Projects/anabatic-contract-management-system/anabatic-contract-management-system]]

Related

  • Duplicate inbox capture removed 2026-05-14 triage (canonical doc is this file).