Client Care Letters Named The Wrong Lawyer

Worth recording, because the reproduction was gated by three things that have nothing to do with this defect and cost more time than the fix:

• 400 on case creation. The owning solicitor needs solicitor.canCreateCases (the BYOC flag) enabled in the admin app. Nothing in the UI says so.
• 500 with ECONNREFUSED :6010. Fee plan lookup calls the incentive service, which lives in the v3 repository and has to be started separately, with its own database migrated first.
• 500 from a 404. The incentive service then returns 404 for a firm employee with no scheme member, and v2 turns that into a 500 rather than falling back to the firm-level fee plan. That resilience gap is worth its own ticket.

Also note the wizard only offers a “lead lawyer” dropdown to non-solicitors who sit on two or more teams; a solicitor is routed straight to their own case. That is why testing this needs a genuine assistant account rather than a second solicitor.

WITH win AS MATERIALIZED (
  SELECT
    q.id        AS quote_id,
    c.id        AS case_id,
    c."ownerId" AS owner_id,
    btrim(concat_ws(' ', op."firstName", op."lastName")) AS owner_name,
    CASE
      WHEN strpos(l.letter, 'Your lawyer') > 0
      THEN substr(l.letter, strpos(l.letter, 'Your lawyer'), 300)
    END         AS lawyer_block
  FROM "Quote" q
  JOIN "Case" c           ON c.id = q."caseId"
  JOIN "FirmEmployee" ofe ON ofe.id = c."ownerId"
  JOIN "Person" op        ON op.id = ofe."personId"
  CROSS JOIN LATERAL (SELECT q."clientCareLetter"::text AS letter) l
  WHERE q."clientCareLetter" IS NOT NULL
    AND q."createdAt" >= now() - interval '12 months'
)
SELECT
  count(*)                                     AS letters,
  count(*) FILTER (WHERE lawyer_block IS NULL) AS no_lawyer_block,
  count(*) FILTER (
    WHERE lawyer_block IS NOT NULL
      AND length(owner_name) > 3
      AND lawyer_block NOT ILIKE '%' || owner_name || '%'
  )                                            AS owner_missing_from_block
FROM win;

Expect one row: 5041 · 8 · 675. If no_lawyer_block is anything other than a handful, the heading has changed and the window extraction needs revisiting before you trust the third column.

-- reuse the win CTE above, then:
flagged AS (
  SELECT * FROM win
  WHERE lawyer_block IS NOT NULL
    AND length(owner_name) > 3
    AND lawyer_block NOT ILIKE '%' || owner_name || '%'
)
SELECT
  CASE
    WHEN EXISTS (
      SELECT 1 FROM "CaseReassignment" cr
      JOIN "FirmEmployee" pfe ON pfe.id = cr."oldFirmEmployeeId"
      JOIN "Person" pp        ON pp.id = pfe."personId"
      WHERE cr."caseId" = s.case_id
        AND s.lawyer_block ILIKE '%' || btrim(concat_ws(' ', pp."firstName", pp."lastName")) || '%'
    ) THEN 'reassigned - previous lawyer'
    WHEN EXISTS (
      SELECT 1 FROM "TeamMember" tm
      JOIN "Team" t           ON t.id  = tm."teamId"
      JOIN "FirmEmployee" mfe ON mfe.id = tm."firmEmployeeId"
      JOIN "Person" mp        ON mp.id = mfe."personId"
      WHERE t."ownerId" = s.owner_id
        AND mfe.id <> s.owner_id
        AND length(btrim(concat_ws(' ', mp."firstName", mp."lastName"))) > 3
        AND s.lawyer_block ILIKE '%' || btrim(concat_ws(' ', mp."firstName", mp."lastName")) || '%'
    ) THEN 'names a team member'
    ELSE 'unrecognised name'
  END AS bucket,
  coalesce(ag.state, 'no ccl agreement row') AS agreement_state,
  count(*) AS quotes
FROM flagged s
LEFT JOIN LATERAL (
  SELECT
    CASE
      WHEN ca.status = 'completed'
        OR ca."completedAgreementSnapshotS3Key" IS NOT NULL
        OR ca."externalSignedFileCaseFileId" IS NOT NULL
        OR EXISTS (
          SELECT 1 FROM "ClientAgreementSignatureRequest" sr
          WHERE sr."agreementId" = ca.id AND sr.status = 'signed'
        )
      THEN 'signed'
      WHEN ca.status = 'revoked' THEN 'revoked'
      ELSE 'pending - unsigned'
    END AS state
  FROM "ClientAgreement" ca
  WHERE ca."quoteId" = s.quote_id AND ca.type = 'clientCareLetter'
  ORDER BY ca."createdAt" DESC
  LIMIT 1
) ag ON true
GROUP BY 1, 2
ORDER BY quotes DESC;

Expect eight rows summing to 675, led by names a team member · signed · 340. The row that decides the migration is names a team member · pending - unsigned · 50. Do not sample this with a bare LIMIT: an unordered 200-row sample put the defect at 34% of the flagged letters when the true figure is 58%, because unordered rows follow physical order and skew old.

-- reuse win + flagged, then:
SELECT
  CASE WHEN m.is_solicitor THEN 'named a solicitor colleague'
       ELSE 'named a NON-solicitor' END AS who_was_named,
  CASE
    WHEN ag.status = 'completed'
      OR ag."completedAgreementSnapshotS3Key" IS NOT NULL
      OR ag."externalSignedFileCaseFileId" IS NOT NULL
    THEN 'signed'
    WHEN ag.status = 'revoked' THEN 'revoked'
    ELSE 'pending - unsigned'
  END AS agreement_state,
  count(*) AS quotes
FROM flagged s
JOIN LATERAL (
  SELECT mfe."solicitorId" IS NOT NULL AS is_solicitor
  FROM "TeamMember" tm
  JOIN "Team" t           ON t.id  = tm."teamId"
  JOIN "FirmEmployee" mfe ON mfe.id = tm."firmEmployeeId"
  JOIN "Person" mp        ON mp.id = mfe."personId"
  WHERE t."ownerId" = s.owner_id
    AND mfe.id <> s.owner_id
    AND length(btrim(concat_ws(' ', mp."firstName", mp."lastName"))) > 3
    AND s.lawyer_block ILIKE '%' || btrim(concat_ws(' ', mp."firstName", mp."lastName")) || '%'
  ORDER BY mfe."solicitorId" NULLS FIRST
  LIMIT 1
) m ON true
LEFT JOIN LATERAL (
  SELECT ca.status, ca."completedAgreementSnapshotS3Key", ca."externalSignedFileCaseFileId"
  FROM "ClientAgreement" ca
  WHERE ca."quoteId" = s.quote_id AND ca.type = 'clientCareLetter'
  ORDER BY ca."createdAt" DESC
  LIMIT 1
) ag ON true
GROUP BY 1, 2
ORDER BY quotes DESC;

Expect five rows totalling 419, led by named a NON-solicitor · signed · 325. The total exceeds 393 because the inner join replaces the reassignment precedence; treat the 26 difference as overlap, not as new cases. solicitorId IS NULL is the same flag the product uses to decide who counts as a lawyer.

Swap the aggregate for SELECT quote_id, case_id, owner_name, member_name, lawyer_block … ORDER BY random() LIMIT 10 and read the blocks. You are checking one thing: whether the block genuinely presents a non-solicitor as the client’s lawyer, or whether the owner’s name simply sits further down than 300 characters. Ten rows settles it.

Then group the same set by firm with min(created_at) and max(created_at). If the letters cluster into a handful of firms, remediation is a few conversations rather than a mass notification, and the first date is what compliance will ask for. It should line up with when assistants could first draft quotes.