# Follow-ups — known issues to address later

A running list of items we know about but have deferred. Each entry has
enough context to pick up cold (different engineer, weeks later) without
re-deriving the diagnosis.

When picking one up: read the entry, run the diagnostic query (if listed),
fix, then strike-through the entry (don't delete — keep the trail).

---

## Open

### 1. Duplicate `TTM_CLIENT_FL_MAPPING` rows (exact-duplicate cleanup)

**Discovered:** 2026-05-06 while investigating client-wide FL consistency.
**Symptom:** Some clients have the same `(CLIENT_ID, CONTRACT_DOCUMENT_ID, FUNCTION_DEPT_REF_ID, FL_EMP_REF_ID)` mapping inserted multiple times — every column identical, just different row IDs and timestamps.

**Most affected client (production sample as of 2026-05-06):**
- `CLIENT_ID = 69` on `CONTRACT_DOCUMENT_ID = 127` has 18 rows that should be 3:
  - `(dept 44, FL 88)` — 6 identical rows (IDs 420, 423, 426, 429, 432, 435)
  - `(dept 50, FL 261)` — 6 identical rows (IDs 421, 424, 427, 430, 433, 436)
  - `(dept 45, FL 91)` — 6 identical rows (IDs 422, 425, 428, 431, 434, 437)
- Created in a 22-minute burst on 2026-04-30 12:13–12:35 — looks like CEO clicked Save 6 times in one session.

**Root cause (already fixed for new inserts):** the old `bulkCreateMappings` (before commit `4007185` on 2026-05-06) had no exact-duplicate guard. The new version skips duplicates and reports them in `meta.skipped_duplicate`. Going forward this won't recur.

**Why we deferred:** No production impact — duplicate rows just clutter the FL list view and may cause extra notifications. Wanted to verify on the UI first before mass-soft-delete.

**Audit query (run anytime):**
```sql
-- How many duplicate clusters and how many extra rows to remove?
SELECT
  COUNT(*) AS total_clusters_with_dupes,
  SUM(dup_count - 1) AS total_extra_rows_to_remove
FROM (
  SELECT COUNT(*) AS dup_count
    FROM TTM_CLIENT_FL_MAPPING
   WHERE ACTIVE = 1 AND DELETED = 0
   GROUP BY CLIENT_ID, CONTRACT_DOCUMENT_ID, FUNCTION_DEPT_REF_ID, FL_EMP_REF_ID
   HAVING COUNT(*) > 1
) x;
```

**Cleanup query (run inside transaction, verify `ROW_COUNT()` matches preview before COMMIT):**
```sql
START TRANSACTION;

UPDATE TTM_CLIENT_FL_MAPPING M
JOIN (
  SELECT CLIENT_ID, CONTRACT_DOCUMENT_ID, FUNCTION_DEPT_REF_ID, FL_EMP_REF_ID,
         MIN(ID) AS keep_id
  FROM TTM_CLIENT_FL_MAPPING
  WHERE ACTIVE = 1 AND DELETED = 0
  GROUP BY CLIENT_ID, CONTRACT_DOCUMENT_ID, FUNCTION_DEPT_REF_ID, FL_EMP_REF_ID
  HAVING COUNT(*) > 1
) dup
  ON dup.CLIENT_ID            <=> M.CLIENT_ID
 AND dup.CONTRACT_DOCUMENT_ID <=> M.CONTRACT_DOCUMENT_ID
 AND dup.FUNCTION_DEPT_REF_ID <=> M.FUNCTION_DEPT_REF_ID
 AND dup.FL_EMP_REF_ID        <=> M.FL_EMP_REF_ID
 AND dup.keep_id              != M.ID
SET M.DELETED = 1, M.ACTIVE = 0, M.UPDATED_AT = NOW()
WHERE M.ACTIVE = 1 AND M.DELETED = 0;

SELECT ROW_COUNT() AS rows_deleted;
COMMIT;
-- ROLLBACK if anything looks off
```

**Verification query with names (for UI cross-check before cleanup):**
```sql
SELECT M.CLIENT_ID, CR.COMPANY_NAME, M.CONTRACT_DOCUMENT_ID, CD.TITLE,
       M.FUNCTION_DEPT_REF_ID, D.DEPARTMENT_NAME, M.FL_EMP_REF_ID, U.FULL_NAME,
       COUNT(*) AS dup_count, MIN(M.ID) AS keep_id_oldest,
       GROUP_CONCAT(M.ID ORDER BY M.ID) AS all_ids
  FROM TTM_CLIENT_FL_MAPPING M
  LEFT JOIN CONTRACT          CR ON CR.CONTRACT_ID = M.CLIENT_ID            AND CR.DELETED = 0
  LEFT JOIN CONTRACT_DOCUMENT CD ON CD.DOCUMENT_ID = M.CONTRACT_DOCUMENT_ID AND CD.DELETED = 0
  LEFT JOIN departments       D  ON D.ID           = M.FUNCTION_DEPT_REF_ID
  LEFT JOIN users             U  ON U.ID           = M.FL_EMP_REF_ID        AND U.DELETED = 0
 WHERE M.ACTIVE = 1 AND M.DELETED = 0
 GROUP BY M.CLIENT_ID, CR.COMPANY_NAME, M.CONTRACT_DOCUMENT_ID, CD.TITLE,
          M.FUNCTION_DEPT_REF_ID, D.DEPARTMENT_NAME, M.FL_EMP_REF_ID, U.FULL_NAME
 HAVING COUNT(*) > 1
 ORDER BY dup_count DESC, CR.COMPANY_NAME, CD.TITLE;
```

---

### 2. Historical cross-document FL inconsistencies

**Discovered:** 2026-05-06.
**Symptom:** Same client + same function = different FL across different documents (e.g. `logical softtech` "Accounting QA" mapped to Bitla QA on SOW but Govind Kumar on NDA).

**Going forward:** prevented by `4007185` (`fix(ttm): enforce client-wide FL consistency per function across documents`) — the new logic auto-corrects incoming inserts to match the existing client-wide FL.

**Historical cleanup needed:** for any client with two different FLs for the same function, decide which FL is canonical (the OLDEST mapping, by `MIN(CREATED_AT)`) and soft-delete the others.

**Audit query (find inconsistencies):**
```sql
SELECT M.CLIENT_ID, CR.COMPANY_NAME, M.FUNCTION_DEPT_REF_ID, D.DEPARTMENT_NAME,
       COUNT(DISTINCT M.FL_EMP_REF_ID) AS distinct_fls,
       GROUP_CONCAT(DISTINCT CONCAT(U.FULL_NAME, ' (id=', M.FL_EMP_REF_ID, ')')) AS fl_names
  FROM TTM_CLIENT_FL_MAPPING M
  LEFT JOIN CONTRACT    CR ON CR.CONTRACT_ID = M.CLIENT_ID
  LEFT JOIN departments D  ON D.ID           = M.FUNCTION_DEPT_REF_ID
  LEFT JOIN users       U  ON U.ID           = M.FL_EMP_REF_ID
 WHERE M.ACTIVE = 1 AND M.DELETED = 0
 GROUP BY M.CLIENT_ID, CR.COMPANY_NAME, M.FUNCTION_DEPT_REF_ID, D.DEPARTMENT_NAME
HAVING COUNT(DISTINCT M.FL_EMP_REF_ID) > 1
 ORDER BY distinct_fls DESC, CR.COMPANY_NAME;
```

**Why we deferred:** product wants to verify on the UI per-client first, in case some inconsistencies are intentional (rare).

---

### 3. `TTM_CLIENT_RM_MAPPING` table — drop in favour of `TTM_CLIENT_POC_MAPPING`

**Discovered:** 2026-05-06.
**Symptom:** New table created in commit `f48f538` + `52b30aa` to gate VARIABLE / FIXED_VARIABLE activation authority. User pointed out the existing `TTM_CLIENT_POC_MAPPING` table already covers this need (POC2 is RM-only, POC1 can be RM/TL).

**Decision:** drop the new table, route the activation auth check through `TTM_CLIENT_POC_MAPPING` instead.

**Action items when revisiting:**
1. `git revert f48f538 52b30aa` — but **keep** the VARIABLE Client Task Mapping relaxation that's tangled in the same commit. Cleaner: cherry-pick a smaller commit.
2. `DROP TABLE IF EXISTS TTM_CLIENT_RM_MAPPING;` on dev DB if migration was run.
3. In Phase 1.B activation endpoint, the auth check becomes:
   ```sql
   EXISTS (
     SELECT 1 FROM TTM_CLIENT_POC_MAPPING P
      WHERE P.CONTRACT_ID = ?
        AND (P.POC1_EMP_REF_ID = ? OR P.POC2_EMP_REF_ID = ?)
        AND P.ACTIVE = 1 AND P.DELETED = 0
   ) OR EXISTS (
     SELECT 1 FROM TTM_CLIENT_FL_MAPPING F
      WHERE F.CLIENT_ID = ? AND F.FL_EMP_REF_ID = ?
        AND F.ACTIVE = 1 AND F.DELETED = 0
   );
   ```
4. Update the plan file `~/.claude/plans/quirky-soaring-hedgehog.md` Phase 0 + 1.A.

**Why we deferred:** wanted to focus on the FL consistency bug; no urgency since the new table is unused (no UI yet).

---

### 4. Per-doc → client-wide FL lookup in `submitMappings`

**Discovered:** 2026-05-06 while answering "where does FL data come from at task mapping time".
**Symptom:** `submitMappings` (in `ttm/clientTaskMapping/clientTaskMapping.controller.js` ~line 663) does the FL coverage check **client-wide** but the actual `ASSIGNED_FL_EMP_REF_ID` lookup is **per-document**. Mismatch can leave new TTM_TASK rows with `ASSIGNED_FL_EMP_REF_ID = NULL` even though an FL is mapped to the client on a different document.

**Quick diagnostic:**
```sql
SELECT COUNT(*) AS orphan_tasks
  FROM TTM_TASK T
 WHERE T.DELETED = 0
   AND T.ASSIGNED_FL_EMP_REF_ID IS NULL
   AND EXISTS (
     SELECT 1 FROM TTM_CLIENT_FL_MAPPING M
      WHERE M.CLIENT_ID = T.CONTRACT_ID
        AND M.ACTIVE = 1 AND M.DELETED = 0
   );
```

**Fix sketch:** change the FL lookup SQL to `ORDER BY (same-doc preferred) ASC, ID ASC` so the lookup falls back to a different-doc FL when no same-doc one exists. Detailed proposal in chat history 2026-05-06.

**Why we deferred:** waiting on user to run the diagnostic and confirm the bug is biting before changing query semantics. If the count is 0 today, it's a latent issue that may bite once new docs are added without re-mapping FLs.

---

### 5. `UQ_CLIENT_TEMPLATE` schema cleanup — filtered unique index

**Discovered:** 2026-05-06 while debugging the "Duplicate entry '180-36-1'" error during re-mapping.

**Symptom:** The unique key on `TTM_CLIENT_TASK_MAPPING` is `(CLIENT_ID, TEMPLATE_ID, DELETED)` — meaning at most ONE soft-deleted row can exist per `(client, template)`. After a few cycles of "create → delete → recreate → delete", the second deletion fails because a soft-deleted row already exists.

**Workaround shipped (commit `76e0271`):** before soft-deleting, hard-delete any pre-existing soft-deleted row for the same `(CLIENT_ID, TEMPLATE_ID)`. Loses old audit trail but unblocks re-mapping.

**Proper fix (deferred):** convert the unique index to a filtered one — only enforce uniqueness on rows where `DELETED = 0`. MySQL doesn't support filtered indexes natively, but a generated column workaround does:

```sql
-- Migration sketch for a future db/migrations/ttm_2.0/009_*.sql:
ALTER TABLE TTM_CLIENT_TASK_MAPPING
  ADD COLUMN UQ_HELPER tinyint
    GENERATED ALWAYS AS (CASE WHEN DELETED = 0 THEN 1 ELSE NULL END) STORED;

ALTER TABLE TTM_CLIENT_TASK_MAPPING
  DROP INDEX UQ_CLIENT_TEMPLATE,
  ADD UNIQUE KEY UQ_CLIENT_TEMPLATE_ACTIVE (CLIENT_ID, TEMPLATE_ID, UQ_HELPER);
```

NULL values don't equal each other in MySQL unique keys, so soft-deleted rows (UQ_HELPER = NULL) can multiply unbounded while active rows stay one-per-(client, template). Once this lands, the workaround `DELETE FROM ... WHERE DELETED = 1` lines added in commit `76e0271` can be removed too — leaving the code cleaner.

**Why deferred:** the workaround is functioning. Schema migration touches a hot table and is best done outside an active feature push.

---

## Done (struck-through entries kept for the trail)

_(none yet)_