GustavoandClaude Sonnet 5
417da56bd6
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com> git-subtree-dir: maquina-comercial-pessoal git-subtree-mainline:f5af038e19git-subtree-split:a9ef4d776d
80 lines
6.4 KiB
Markdown
80 lines
6.4 KiB
Markdown
# Shared Number Detection Design
|
|
|
|
## Goal
|
|
|
|
Stop automatic and manual outreach from sending multiple messages to a single WhatsApp number that is shared by more than one lead in the base (the common case: an accountant's phone registered as the contact for several client CNPJs). Surface the sharing to the operator so it can be reviewed and, if it turns out not to be a third party, explicitly released.
|
|
|
|
## Background
|
|
|
|
`leads.dedup_key` is derived from CNPJ + phone + company + city, so two different companies (different CNPJs) that share the same registered phone number produce two distinct lead rows. Neither the campaign queue (`src/outreach/campaign-queue.mjs`) nor manual send (`reserveOutreachSend` in `src/db.mjs`) currently checks for this: each lead is deduplicated against its own send history and the opt-out list, not against other leads' phone numbers. This let three separate leads pointing at the same accounting-office number each queue and send a message.
|
|
|
|
## Constraints
|
|
|
|
- Work only in `MAQUINA COMERCIAL PESSOAL`.
|
|
- A number counts as shared when it appears on 2+ leads in the base, regardless of whether the CNPJs differ.
|
|
- Brazilian mobile variants with and without the ninth digit represent the same number for this check (reuse the existing equivalence rules from `src/phone.mjs`, already used for opt-out and WhatsApp verification).
|
|
- When a number is shared, block sending to **every** lead that shares it (not just the extras) — both from the automatic campaign queue and from manual send — until an operator explicitly reviews and releases that number.
|
|
- Do not silently auto-resolve; the operator decides per number.
|
|
|
|
## Data Model
|
|
|
|
New table `outreach_shared_number_overrides`, same shape as `outreach_opt_outs`:
|
|
|
|
```sql
|
|
CREATE TABLE outreach_shared_number_overrides (
|
|
numero TEXT PRIMARY KEY,
|
|
motivo TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
```
|
|
|
|
`numero` stores one normalized representative of the equivalence group (same convention as `addOptOut`: `equivalentPhoneNumbers(numero)[0]`). Presence of a row means the operator reviewed that number and decided sending is allowed despite the sharing.
|
|
|
|
No new column is added to `leads`. Sharing is computed on demand by comparing `leads.whatsapp` values through the existing equivalence rules — the base is personal-scale (thousands of rows, one operator), so a generated/indexed column is not justified.
|
|
|
|
## Shared Logic
|
|
|
|
The 8/9-digit equivalence expansion currently lives twice as SQL-fragment builders inside `src/outreach/campaign-queue.mjs` (`normalizedSql`, `equivalentSql`). Move them to `src/phone.mjs` so they can be reused by the campaign queue, the manual-send path, and the treatment list query without duplicating the expression.
|
|
|
|
Add to `src/db.mjs` (co-located with `isOptedOut`/`addOptOut`, which follow the same pattern):
|
|
|
|
- `leadsSharingNumber(db, numero)` — returns `{id, empresa, cnpj}` rows for every lead whose phone is equivalent to `numero`.
|
|
- `isNumberSharedAcrossLeads(db, numero)` — `leadsSharingNumber(...).length >= 2`.
|
|
- `isSharedNumberOverridden(db, numero)` / `addSharedNumberOverride(db, numero, motivo)` / `removeSharedNumberOverride(db, numero)` — same shape as the opt-out functions.
|
|
- `listSharedNumberGroups(db)` — scans leads with a non-empty phone, buckets them by equivalence group (same technique already used by `listConfirmedWhatsAppNumbers`), keeps groups with 2+ leads, and attaches override status/reason. Powers both the review screen and the treatment-list badge.
|
|
|
|
## Blocking Points
|
|
|
|
**Campaign queue** (`src/outreach/campaign-queue.mjs`, `candidateQuery`): add one more `WHERE` condition using the moved `equivalentSql` helper — a lead is a candidate only if fewer than 2 leads share its equivalent number, or an override row exists for that number. This keeps the exclusion inside the existing single SQL statement instead of post-filtering in JS.
|
|
|
|
**Manual send** (`reserveOutreachSend` in `src/db.mjs`): after the existing opt-out check, reject with the same `statusCode = 409` pattern if `isNumberSharedAcrossLeads(...)` is true and no override exists.
|
|
|
|
**Manual send route** (`src/server/routes/outreach.mjs`, `POST /api/outreach/enviar-manual`): mirror the existing fast-fail opt-out check (line ~85) with an equivalent shared-number check, returning a clear 409 (`"numero compartilhado por varios leads - revise em Configuracoes antes de enviar"`) before even attempting `whatsapp.sendText`. `reserveOutreachSend`'s check remains the authoritative guard against races; the route check just gives a faster, clearer failure.
|
|
|
|
## Review Screen
|
|
|
|
New section in **Configurações**, next to the existing opt-out list, listing groups from `listSharedNumberGroups`: the number and the companies/leads that share it. Each group has a **Liberar** action (with a required reason) that calls `addSharedNumberOverride`, and, once liberated, a **Bloquear de novo** action that calls `removeSharedNumberOverride`. Same admin-only permission pattern as the opt-out endpoints.
|
|
|
|
New routes in `src/server/routes/outreach.mjs`:
|
|
- `GET /api/outreach/shared-numbers` → `{ groups: listSharedNumberGroups(db) }`
|
|
- `POST /api/outreach/shared-numbers/:numero/liberar` → admin only, body `{ motivo }`
|
|
- `DELETE /api/outreach/shared-numbers/:numero/liberar` → admin only
|
|
|
|
## Treatment List Indicator
|
|
|
|
`listLeads` and `getLead` in `src/treatment/queries.mjs` add a `numero_compartilhado` (boolean) and `leads_com_mesmo_numero` (count) field per row, computed the same way as the campaign-queue condition. The treatment table (`public/js/tratamento.js`) shows a warning badge next to the existing WhatsApp status badge when `numero_compartilhado` is true — e.g. "Número compartilhado (3 leads)" — softened to a neutral badge when the number has been liberated via override.
|
|
|
|
## Testing
|
|
|
|
Automated regressions must prove:
|
|
|
|
- Two leads with the exact same digits are both blocked from the campaign queue and from manual send.
|
|
- Two leads whose phones are 8/9-digit equivalents of each other are treated as the same shared number.
|
|
- A single lead with a unique number is never blocked.
|
|
- An override releases sending for every lead sharing that number, in both the queue and manual send.
|
|
- Removing an override re-blocks the number.
|
|
- The review screen endpoint lists correct groups and reflects override state.
|
|
- The treatment list marks shared numbers and reflects override state.
|
|
|
|
Run focused tests after each change, then the complete Node test suite.
|