name: comercialrs-debug-workflow

description: Supabase Cloud CRM project on VPS — schema, Edge Functions patterns, WhatsApp dispatch, link auto-fill debugging, and critical file-loss lessons

ComercialRS Debug & Sync Workflow

Context

Supabase Cloud project dauftiqcvgaydddoxqhh for a sales CRM site (comercialrs.rochasalesseguros.com.br) deployed on VPS with Docker/nginx.

Critical Lesson: rsync OVERWRITES, does not merge

`bash

WRONG — trailing slash on SOURCE means "copy contents of commercialrs-main into comercialrs/"

rsync -avz comercialrs-main/ /var/www/comercialrs/

`

This deletes everything in /var/www/comercialrs/ that doesn't exist in commercialrs-main/ before copying. The src/ directory was wiped out.

`bash

Option A — only copy specific files you need

rsync -avz comercialrs-main/src/components/ /var/www/comercialrs/src/components/

Option B — manual copy of changed files only

cp -r comercialrs-main/src/components/onboarding/ /var/www/comercialrs/src/components/onboarding/

`

ALWAYS verify what's in the target before rsyncing.

Critical Lesson: .env is REQUIRED before rebuild

Without .env, the Vite build does NOT embed VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY, causing runtime error:

`

supabaseUrl is required.

`

Order of operations for restore:

1. Copy .env FIRST

2. npm install

3. npm run build

4. Verify in dist/assets/*.js that the Supabase URL appears

`bash

Check if .env was embedded in the bundle

grep -a "dauftiqcvgaydddoxqhh" /var/www/comercialrs/dist/assets/*.js

`

VPS Container/Port Map (CRITICAL)

Port discovery commands:

`bash

docker ps --format "{{.Names}} {{.Ports}}" # all containers + ports

ss -tlnp 2>/dev/null | grep LISTEN # all listening ports on host

docker network inspect deploy-vps_default # Docker internal IPs (172.x.x.x)

`

Supabase CLI db query limitation: supabase db query connects to LOCAL Supabase (Docker), NOT the linked cloud project — even after supabase link. Use Management API or Edge Functions to manage cloud DB.

Management API 403: api.supabase.com/v1/projects/{ref}/database/query returns 403 error code: 1010 (Cloudflare/proxy block). Cannot create tables via Management API. Workaround: Edge Function with service role (but Edge Functions cannot run raw SQL DDL). Table creation on Supabase Cloud must be done via Supabase Dashboard UI.

Database Schema (current, verified)

Tables

• - tasks — meetings/tasks
• - crm_leads_duplicate — LIVE leads from Painel do Corretor (NOT crm_leads — that one is stale)
• - crm_leads — stale, latest entry March 2026, DO NOT USE for queries
• - profiles — user profiles
• - team_members — broker team members
• - automations — automation rules
• - whatsapp_logs — WhatsApp send log (create via Dashboard UI)
• - sales, tenants — other tables

tasks table columns

id, title, status, channel, due_date, priority, progress, created_at, start_date, description, link, task_order, deleted_at, updated_at, external_ref, created_by

• - NO type, responsible_name, is_overdue, project_id columns
• - Use CriadoEm (not created_at) when querying crm_leads_duplicate

crm_leads_duplicate — the LIVE table from Painel do Corretor

Columns: Id (not id), Nome, Etapa, CriadoEm, Fechamento, Valor, Etiquetas, Contato.Nome, Contato.Email, Contato.Telefones, Vendedor, Observacao, custom_data

• - DO NOT confuse with crm_leads — that one is stale (latest entry: 2026-03-31)
• - custom_data.timeline types: NegocioCriado, AnotacaoRegistrada, NegocioTransferido, NegocioMovido
• - NO meeting/reuniao/visita types in timeline — automatic meeting creation from this table alone is NOT possible

---

Edge Function Patterns

Deploy pattern

`bash

cd /var/www/comercialrs

SUPABASE_ACCESS_TOKEN="sbp_..." supabase functions deploy --no-verify-jwt --project-ref dauftiqcvgaydddoxqhh

`

Native fetch REST pattern for Supabase Admin API

`typescript

const serviceKey = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!;

const resp = await fetch(${supabaseUrl}/auth/v1/admin/users/${userId}, {

method: 'PUT',

headers: {

'Authorization': Bearer ${serviceKey},

'apikey': serviceKey,

'Content-Type': 'application/json',

},

body: JSON.stringify({ password: newPassword }),

});

`

Tasks upsert (NO unique constraint on id)

Supabase auto-generated UUID PKs have NO named unique constraint, so upsert({ onConflict: 'id' }) fails.

Solution: Delete all first, then bulk insert:

`typescript

await supabase.from('tasks').delete().not('id', 'is', 'null');

await supabase.from('tasks').insert(mappedTasks);

`

firecrawl-extract-meeting — 3-tier fallback strategy (GraphQL PRIMARY)

CRITICAL FIX (June 2026): Original code checked DB FIRST and returned "Reunião" generic entries, skipping GraphQL entirely. Fixed: GraphQL runs FIRST and takes priority. DB is only a fallback for non-generic names.

Strategy 1 (GraphQL direct from Painel — PRIMARY):

• - Authenticates with Auth0 using password-realm grant (credentials in skill)
• - Queries https://api.paineldocorretor.net/graphql with the deal UUID
• - Returns: nome, telefones (from contato.telefones), email, etapa.nome (for channel inference), fechamento (date, but always null)
• - Deno Deploy CAN reach external hosts — network is NOT blocked (tested with test-network function)
• - IMPORTANT: anotacoes field does NOT support order_by or limit arguments — query anotacoes { mensagem criadoEm } without args, then filter/sort in JS
• - Date from annotations: Regex (\d{2})./-(?:./-)? matches "06.05", "06/05", "06-05", "06.05.26", etc. Scans all annotations, uses date if within ±60 days of today. Example: "COTAÇÃO VIA WHATS 05.05" → extracts date 2026-05-05.
• - Falls through to Strategy 2 only if GraphQL fails or returns no nome

Strategy 2 (Database UUID search — SECONDARY):

• - Only used if Strategy 1 fails OR if database has a REAL name (not generic "Reunião")
• - Added isGenericName() check — skips generic names: 'Reunião', 'Reuniao', 'Agendamento', 'New Task', 'Nova Tarefa', etc.
• - Searches in 3 fields: link, external_ref, title — returns title as name

Strategy 3 (URL fallback): Extracts name from URL slug, returns {notes: "ID: "} as final fallback.

Known data quality issue: Some due_date values are malformed (e.g., 2026-04-13T80:00 — 80 minutes is invalid). These produce date: null. This is bad data in the database, not a code bug.

Key UUID extraction regex: /([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})/i

Frontend auto-fill flow (TaskCreateDialog.tsx):

1. User pastes link → onChange detects UUID pattern → setTimeout(() => handleAutoFill(), 100)

2. handleAutoFill() calls extractMeetingFromUrl(form.link)

3. Edge Function returns {name, date, time, channel, notes}

4. Only fills empty fields — if user already typed something, it won't overwrite

Painel GraphQL API — DISCOVERED (June 2025):

• - Auth0 domain: auth.paineldocorretor.com.br
• - Auth0 client_id: MtCJ0_REDACTED
• - Auth0 audience: https://paineldocorretor.com.br/api/
• - Auth0 realm: Username-Password-Authentication
• - Login payload: username field (NOT email), grant_type: http://auth0.com/oauth/grant-type/password-realm
• - GraphQL endpoint: https://api.paineldocorretor.net/graphql
• - Tested: works from Deno Deploy (Supabase Edge Functions), not blocked by network

Painel GraphQL query for deal:

`graphql

query GetNegocio($id: UUID!) {

negocio(id: $id) {

id

nome

criadoEm

fechamento # ALWAYS null — no structured meeting date in API

etapa { nome }

contato { nome email telefones }

produto { nome }

etiquetas { nome }

valor

anotacoes { mensagem criadoEm } # NO order_by/limit — sort/filter in JS

}

}

`

IMPORTANT — fechamento is always null: The Painel GraphQL negocio.fechamento field returns null for ALL deals. There is NO structured meeting date field in the API. The "06.05" date visible in ComercialRS was either typed manually or comes from a Painel annotation text. Date is now parsed from annotation text patterns (e.g., "COTAÇÃO VIA WHATS 05.05" → date 2026-05-05).

Painel link URL structure:

• - https://app2.paineldocorretor.com.br/crm/negocios/{UUID} — deal page
• - https://app2.paineldocorretor.com.br/crm/negocios/{NAME-slug}/{UUID} — name is URL-encoded slug

Channel inference from etapa (or annotations — more specific):

• - Contains "whats", "zap", "whatsapp" in annotation text → channel: "mensagem" (HIGHEST priority)
• - Contains "reunião" or "agendada" in etapa → channel: "video"
• - Contains "telefon" or "ligação" → channel: "telefone"
• - The anotacoes text is checked FIRST (more specific), then etapa.nome

UAZAPI WhatsApp Direct (no n8n)

UAZAPI is used directly for WhatsApp — no n8n required.

⚠️ CRITICAL: Instance Token Must Match Database

The uazapi_token in automations_config table MUST match the active UAZAPI instance.

If the instance changes (new token), update the database:

`bash

Use service_role key (NOT anon key) for PATCH

curl -s -X PATCH "https://dauftiqcvgaydddoxqhh.supabase.co/rest/v1/automations_config?id=eq.a17bf11a-6e2f-4980-b8f2-5a1f7c7970a0" \

-H "Authorization: Bearer " \

-H "apikey: " \

-H "Content-Type: application/json" \

-H "Prefer: return=minimal" \

-d '{"uazapi_token": ""}'

`

Anon key only works for reads. Service role required for writes.

UAZAPI Token History (IMPORTANT — tokens change frequently)

• - OLD (INVALID): 2c9de45a-0556-4758-8707-9f263b17f07b → returns {"error":true,"code":401,"message":"Invalid token."} when tested directly
• - NEW (May 2026): c2d9806f-b02d-4a1e-acb6-a51db076c4a5 → returns {"error":true,"message":"WhatsApp disconnected: session is not reconnectable"} — WhatsApp Business session is DISCONNECTED
• - automations_config table has: uazapi_token = 2c9de45a... (OLD/INVALID) — needs update to c2d9806f... BUT new instance also has disconnected session
• - Instance name: Comercial Rs
• - Profile name: Tec RS
• - NEVER touch the old Operador1 (Renata Godoy) instance — user explicitly forbids this

UAZAPI Disconnection Symptoms (May 2026)

• - send/text with current token → {"error":true,"message":"WhatsApp disconnected: session is not reconnectable"}
• - send/text with old token → {"error":true,"code":401,"message":"Invalid token."}
• - /instance/status returns 404 — not a valid UAZAPI endpoint; ignore this
• - Root cause: WhatsApp Business was disconnected (logged out from another device or session expired)
• - User must re-scan QR code in UAZAPI dashboard to reconnect the WhatsApp session
• - Test messages from UAZAPI dashboard work → means the dashboard is using a different/active session, but the API token's session is dead

Current automations_config (Jun 2026)

`json

{

"provider": "meta",

"meta_access_token": "EAASNGf6ucRcBR...", # Meta WhatsApp Business API token

"meta_phone_number_id": "812251211981254",

"meta_waba_id": "532448808878595",

"meta_business_id": "1264409148885415",

"uazapi_url": null,

"uazapi_token": null,

"uazapi_phone": null,

"send_mode": "personal",

"sales_notification_phones": "5511940858953"

}

`

⚠️ UAZAPI ainda está no código mas provider é "meta" — o dispatch-automation detecta isMeta = config?.provider === 'meta' e usa a Meta API. UAZAPI é fallback se provider === 'uazapi'.

Current Supabase Keys (May 2026 — verify before use)

• - Anon key (frontend/public): eyJ_REDACTED
• - Service Role key (Edge Functions / admin): not confirmed — Edge Functions use env var SUPABASE_SERVICE_ROLE_KEY which is set at deploy time

How to Test dispatch-automation Directly

`bash

curl -s -X POST "https://dauftiqcvgaydddoxqhh.supabase.co/functions/v1/dispatch-automation" \

-H "Content-Type: application/json" \

-H "Authorization: Bearer eyJ_REDACTED" \

-d '{"automation_key":"whatsapp_reminders","event_type":"task_created","payload":{"title":"Teste","due_date":"2026-05-08T14:00:00Z","responsible_id":"","responsible":"Teste","channel":"video","link":""}}'

`

Expected response: {"dispatched":true,"key":"whatsapp_reminders","whatsapp_sent":0,"whatsapp_failed":1,"errors":["11913412661 não entregou"],"trigger":"instant"}

`

POST https://rochasales.uazapi.com/send/text?token=

Content-Type: application/json

Body: {

"number": "DDDNUMERO",

"text": "..."

}

`

IMPORTANT: Token MUST be a query parameter (?token=...), NOT an Authorization: Bearer header. The Authorization header returns 401 "Missing token".

Phone number format: Plain digits with DDD, no @whatsapp.net, no +. Example: 51992443697.

Check instance status:

`bash

curl -s "https://rochasales.uazapi.com/instance/status" \

-H "token: "

`

Response: status: "connected" = active, status: "disconnected" = session dead.

WhatsApp can disconnect — DO NOT RECONNECT without user permission:

• - Symptom: send/text returns {"error":true,"message":"WhatsApp disconnected"}
• - Disconnect reason 401: logged out from another device with recentMessage = session was active elsewhere
• - User instruction: NEVER touch instance reconnect/QR code without explicit permission
• - Check status first: GET /instance/status

---

PostgREST NULL Filter Syntax (CRITICAL — BREAKING BUG)

Correct: column=is.null

WRONG: is=column.null (silently returns 400 error, no data found)

The wrong syntax causes ALL queries with deleted_at filters to fail with failed to parse filter (deleted_at.null).

Full syntax reference:

dispatch-automation — COMPLETELY REWRITTEN (multiple stacked bugs fixed)

Symptoms: Brokers don't receive WhatsApp notifications when meetings are created.

Bugs found and fixed (cumulative):

1. PostgREST null filter: is=deleted_at.null → deleted_at=is.null

2. Not actually sending WhatsApp: The function only returned data, never called UAZAPI

3. Wrong UAZAPI endpoint: Was using /messages/send → correct is /send/text?token=

4. Token in wrong header: Authorization: Bearer → query param ?token=

5. event_type vs trigger mismatch: Frontend sends event_type, old code only read trigger — added support for both

6. task_created going to scheduled switch instead of instant: Frontend calls dispatch-automation with automation_key: 'whatsapp_reminders' + event_type: 'task_created' + payload. Code was checking instant triggers but then had an else if (key) that caught everything else. Fixed: instant triggers checked FIRST with effectiveTrigger guard, scheduled jobs in else if (key) block.

Code architecture (correct):

`typescript

// Instant triggers — fire IMMEDIATELY when task_created

if (isUazapi && config && effectiveTrigger && payload) {

if (effectiveTrigger === 'task_created') { / send WhatsApp NOW / }

}

// Scheduled jobs — run on cron, no payload

else if (key) {

switch (key) {

case 'whatsapp_reminders': { / daily reminder check / }

// ...

}

}

`

Critical column names in automations table: key (not automation_key), status (not active), trigger_type (null for most), last_run_at. The table does NOT have event_type or trigger columns — PostgREST filters on those columns return 400.

Frontend call pattern (automationDispatcher.ts):

`typescript

dispatchAutomation('whatsapp_reminders', 'task_created', { title, due_date, channel, link });

// Sends: { automation_key: 'whatsapp_reminders', event_type: 'task_created', payload: {...} }

`

Message format for task_created:

`

📹 Nova Reunião Criada

📌 {title}

📅 {date}

🔗 {link}

`

(Channel emoji: 📹 video, 📞 telefone, 💬 mensagem)

Test command:

`bash

curl -s -X POST "https://dauftiqcvgaydddoxqhh.supabase.co/functions/v1/dispatch-automation" \

-H "Authorization: Bearer eyJ_REDACTED" \

-H "Content-Type: application/json" \

-d '{"meetingId":"","event_type":"meeting_created"}'

`

Verify sent:

`bash

curl -s "https://dauftiqcvgaydddoxqhh.supabase.co/rest/v1/whatsapp_logs?select=*&order=sent_at.desc&limit=5" \

-H "apikey: " -H "Authorization: Bearer "

`

Tested: 9 messages sent successfully to brokers.

dispatch-automation Event Types

Debugging Commands (Supabase Cloud)

read-config function — shows automations_config, auth_users

`bash

curl -s -X POST "https://dauftiqcvgaydddoxqhh.supabase.co/functions/v1/read-config" \

-H "Content-Type: application/json" \

-H "Authorization: Bearer " | python3 -m json.tool

`

db-inspect function — shows profiles, team_members, tasks counts

`bash

curl -s -X POST "https://dauftiqcvgaydddoxqhh.supabase.co/functions/v1/db-inspect" \

-H "Content-Type: application/json" \

-H "Authorization: Bearer "

`

inspect-db function — lightweight check (tasks, members, profiles)

`bash

curl -s -X POST "https://dauftiqcvgaydddoxqhh.supabase.co/functions/v1/inspect-db" \

-H "Content-Type: application/json" \

-H "Authorization: Bearer "

`

Verified Working Edge Functions (currently deployed)

Edge Functions DELETED by rsync — must recreate if needed

WhatsApp / UAZAPI Reconnection (CRITICAL — Disconnected Session)

Symptom: dispatch-automation returns whatsapp_sent: 1 but broker never receives messages. Test calls to /send/text return {"error":true,"message":"WhatsApp disconnected"}.

Root cause: Instance disconnected — "lastDisconnectReason": "401: logged out from another device with recentMessage". Happens when someone logs in from another device or during webhook configuration work.

Diagnosis:

`bash

curl -s "https://rochasales.uazapi.com/instance/status" \

-H "token: 94f48fd6-ed40-4654-bdd7-22f8b34bb356"

`

Check "status": "connected" / "loggedIn": true in response.

Reconnection procedure:

`bash

1. Generate new QR code

curl -s -X POST "https://rochasales.uazapi.com/instance/connect" \

-H "token: 94f48fd6-ed40-4654-bdd7-22f8b34bb356" \

-H "Content-Type: application/json" -d '{}'

2. Extract QR from response JSON (base64 PNG data URL)

Save as /root/uazapi_qr.png and send to user as MEDIA:/root/uazapi_qr.png

3. User scans with WhatsApp app (WhatsApp → Settings → Linked Devices → Connect Device)

`

After reconnection, VERIFY:

`bash

curl -s "https://rochasales.uazapi.com/instance/status" -H "token: ..."

Should show "status": "open" or "connected": true

`

Documents App — Separate Supabase Project (DocCorretor)

Supabase project: dauftiqcvgaydddoxqhh (same as ComercialRS)

VPS app path: /var/www/documentos/deploy-vps-nodejs/app

Live URL: https://documentos.rochasalesseguros.com.br

Server: systemd service documentos.service, port 3010

Service Role Key Rotation — CRITICAL

The SUPABASE_SERVICE_ROLE_KEY in the app's .env was INVALIDATED (key was rotated).

Workaround for writes (table create/delete/update):

1. Dashboard UI — go to https://supabase.com/dashboard/project/dauftiqcvgaydddoxqhh/table-editor and run SQL directly in the SQL Editor

2. supabase CLI limitation — supabase db query connects to LOCAL Supabase (Docker), NOT the linked cloud project, even after supabase link. Cannot be used for cloud DB operations.

Documents App Tables (from code grep — partial list)

The app uses these tables (some may not exist in the project yet):

Cloud Storage JWT_SECRET Mismatch (CRITICAL — Jun 2026)

Symptom: Both anon key AND service_role key fail Cloud storage with:

Root cause: The keys in .env were signed with a JWT_SECRET that does NOT match the Cloud project's JWT_SECRET. Possible causes: (1) project was recreated after keys were generated, (2) JWT_SECRET was rotated in the Cloud project after key generation.

Supabase NEVER exposes JWT_SECRET via any API (security reason). Cannot verify/regenerate programmatically.

Diagnosis commands:

`bash

List projects + refs

npx supabase projects list

Output:

dauftiqcvgaydddoxqhh | Dashboard Rocha Sales Meta e CRM (West US)

nzaxsorgsecnmqwufawn | DocCorretor (East US)

Get API keys (but NOT JWT_SECRET)

npx supabase projects api-keys list --project-ref dauftiqcvgaydddoxqhh

Get server-side secrets (JWKS, DB_URL — but NOT JWT_SECRET)

npx supabase secrets list --project-ref dauftiqcvgaydddoxqhh

`

Two possible fixes:

Option A (Dashboard — fastest):

1. Go to https://supabase.com/dashboard/project/dauftiqcvgaydddoxqhh/settings/api

2. Find "JWT_secret" → click "Regenerate"

3. Copy new anon key and service_role key

4. Update .env with new keys

5. Rebuild and restart server

Option B (Personal Access Token — for automation):

1. Create PAT at https://supabase.com/dashboard/account/tokens

2. Use management API to regenerate keys:

`bash

curl -X POST "https://api.supabase.com/v1/projects/dauftiqcvgaydddoxqhh/api-keys" \

-H "Authorization: Bearer " \

-H "Content-Type: application/json" \

-d '{"type":"access_token"}'

`

CLI link pitfall: After supabase link --project-ref dauftiqcvgaydddoxqhh, the supabase/config.toml still shows old project_id = "lzoiuxulhnvtmadoymrb". This is NORMAL — the project_id in config.toml is an internal ID, different from the project ref used in URLs. Use npx supabase projects list to confirm which project is actually linked.

What still works: Auth (login/logout), REST API reads, Edge Functions — only Cloud storage is broken.

Known Issues

Bug 1: Kanban não atualiza após criar tarefa (✅ CORRIGIDO — Jun 2026)

Sintoma: Ao criar nova reunião, o Kanban não mostra a tarefa nova. Usuário precisa atualizar página manualmente.

Causa raiz: O KanbanBoard (KanbanBoard.tsx) mantém um estado local useState(externalTasks) que só sincroniza quando a referência do array muda:

`typescript

useEffect(() => {

setTasks(externalTasks); // só roda se a referência do array mudou

}, [externalTasks]);

`

O AppDataContext atualiza via realtime (mesma referência de array) — o Kanban nunca detecta.

Fix aplicado (Jun 2026) — src/components/tasks/KanbanBoard.tsx:

`typescript

// Track the external task IDs to force re-sync when realtime updates arrive

const [lastTaskIds, setLastTaskIds] = useState(() => externalTasks.map(t => t.id).join(','));

useEffect(() => {

const newIds = externalTasks.map(t => t.id).join(',');

if (newIds !== lastTaskIds) {

setLastTaskIds(newIds);

setTasks(externalTasks);

}

}, [externalTasks, lastTaskIds]);

`

Compara IDs em vez de referência de array — mudanças via realtime são detectadas.

Deploy: Build local funciona (npm run build). Deploy para VPS via rsync/scp não funciona — SSH credentials recusadas. O arquivo foi modificado localmente em /tmp/comercialrs/comercialrs-main/src/components/tasks/KanbanBoard.tsx e precisa ser enviada ao VPS.

Bug 2: WhatsApp Response → Task Status Update (✅ CORRIGIDO — Jun 2026)

Cadeia completa (como deve funcionar)

1. dispatch-automation envia template Meta WhatsApp para o corretor

2. dispatch-automation grava meta_message_id + task_id na message_log

3. Meta entrega mensagem → corretor recebe no WhatsApp

4. Corretor responde "iniciada" / "concluída" / "remarketing"

5. Meta envia webhook para meta-webhook

6. meta-webhook extrai msgContext.id (o ID da nossa mensagem)

7. meta-webhook chama RPC get_task_by_meta_message_id(msgContext.id) → obtém task_id

8. meta-webhook atualiza tasks.status via RPC

O que estava quebrado (Jun 2026)

1. message_log e incoming_messages tables não existiam — todas as tabelas e RPCs foram criadas via SQL no Dashboard.

2. dispatch-automation NÃO DISPARAVA task_created — BUG PRINCIPAL (Jun 2026)

O fluxo do dispatch-automation para salvar meta_message_id estava tecnicamente correto no código (sendToMetaRecipients → logMessageSent → POST /rest/v1/message_log). MAS o addTask no AppDataContext.tsx nunca chamava dispatchAutomation com event_type: 'task_created'. O dispatch-automation só era chamado para task_overlap e task_overload. Résultat: nenhum envio real era feito, message_log só tinha 2 registros de teste antigos.

Fix aplicado (Jun 2026):

⚠️ FALTA IMPLEMENTAR no AppDataContext.addTask:

O addTask ainda NÃO chama dispatchAutomation('whatsapp_reminders', 'task_created', payload) após inserir a tarefa. Sem isso, o corretor nunca recebe WhatsApp e o meta_message_id nunca é gravado. Código necessário:

`typescript

// Após insert com sucesso, antes do return true:

dispatchAutomation('whatsapp_reminders', 'task_created', {

id: task.id,

title: task.title,

due_date: task.dueDate,

channel: task.channel,

link: task.link,

responsible_id: task.responsible?.id,

responsible: task.responsible?.name,

});

`

SQL de setup (executar no Supabase Dashboard → SQL Editor)

`sql

-- 1. Tabela message_log

CREATE TABLE IF NOT EXISTS public.message_log (

id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

meta_message_id TEXT UNIQUE,

task_id UUID REFERENCES public.tasks(id) ON DELETE SET NULL,

responsible_id UUID REFERENCES public.team_members(id) ON DELETE SET NULL,

template_name TEXT,

phone TEXT,

status TEXT DEFAULT 'sent',

created_at TIMESTAMPTZ DEFAULT now(),

updated_at TIMESTAMPTZ DEFAULT now()

);

ALTER TABLE public.message_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY "message_log_insert_service" ON public.message_log FOR INSERT WITH CHECK (true);

CREATE POLICY "message_log_select_service" ON public.message_log FOR SELECT USING (true);

-- 2. Tabela incoming_messages

CREATE TABLE IF NOT EXISTS public.incoming_messages (

id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

message_id TEXT UNIQUE,

from_number TEXT,

timestamp BIGINT,

msg_type TEXT,

body TEXT,

parsed_status TEXT,

task_id UUID REFERENCES public.tasks(id) ON DELETE SET NULL,

created_at TIMESTAMPTZ DEFAULT now()

);

ALTER TABLE public.incoming_messages ENABLE ROW LEVEL SECURITY;

CREATE POLICY "incoming_messages_insert_service" ON public.incoming_messages FOR INSERT WITH CHECK (true);

CREATE POLICY "incoming_messages_select_service" ON public.incoming_messages FOR SELECT USING (true);

-- 3. RPCs

CREATE OR REPLACE FUNCTION public.get_task_by_meta_message_id(p_meta_message_id TEXT)

RETURNS TABLE(task_id UUID, responsible_id UUID, phone TEXT)

LANGUAGE plpgsql SECURITY DEFINER AS $$

BEGIN

RETURN QUERY

SELECT ml.task_id, ml.responsible_id, ml.phone

FROM public.message_log ml

WHERE ml.meta_message_id = p_meta_message_id AND ml.task_id IS NOT NULL

LIMIT 1;

END;

$$;

CREATE OR REPLACE FUNCTION public.log_incoming_message_rpc(

p_message_id TEXT, p_from_number TEXT, p_timestamp TEXT,

p_msg_type TEXT, p_body TEXT

) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$

BEGIN

INSERT INTO public.incoming_messages (message_id, from_number, timestamp, msg_type, body, created_at)

VALUES (p_message_id, p_from_number, p_timestamp::BIGINT, p_msg_type, p_body, now())

ON CONFLICT (message_id) DO NOTHING;

END;

$$;

CREATE OR REPLACE FUNCTION public.update_task_status_rpc(p_task_id UUID, p_status TEXT)

RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$

BEGIN UPDATE public.tasks SET status = p_status, updated_at = now() WHERE id = p_task_id; END;

$$;

CREATE OR REPLACE FUNCTION public.get_automation_config_rpc()

RETURNS TABLE(meta_access_token TEXT, meta_phone_number_id TEXT, meta_waba_id TEXT)

LANGUAGE plpgsql SECURITY DEFINER AS $$

BEGIN RETURN QUERY SELECT ac.meta_access_token::TEXT, ac.meta_phone_number_id::TEXT, ac.meta_waba_id::TEXT FROM public.automations_config ac LIMIT 1; END;

$$;

CREATE OR REPLACE FUNCTION public.log_reply_to_task(

p_meta_message_id TEXT, p_from_phone TEXT, p_response_text TEXT,

p_parsed_status TEXT, p_task_id UUID

) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$

BEGIN

INSERT INTO public.incoming_messages (message_id, from_number, body, parsed_status, task_id, created_at)

VALUES (p_meta_message_id, p_from_phone, p_response_text, p_parsed_status, p_task_id, now())

ON CONFLICT (message_id) DO NOTHING;

UPDATE public.tasks SET status = p_parsed_status, updated_at = now() WHERE id = p_task_id;

END;

$$;

CREATE OR REPLACE FUNCTION public.get_task_title_rpc(p_task_id UUID)

RETURNS TEXT LANGUAGE plpgsql SECURITY DEFINER AS $$

DECLARE v_title TEXT;

BEGIN SELECT t.title INTO v_title FROM public.tasks t WHERE t.id = p_task_id; RETURN COALESCE(v_title, 'Reunião'); END;

$$;

-- 4. Realtime publications

ALTER PUBLICATION supabase_realtime ADD TABLE public.message_log;

ALTER PUBLICATION supabase_realtime ADD TABLE public.incoming_messages;

-- 5. Permissões

GRANT USAGE ON SCHEMA public TO authenticated;

GRANT ALL ON public.message_log TO authenticated;

GRANT ALL ON public.incoming_messages TO authenticated;

GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO authenticated;

`

Status atual (Jun 2026):

Verificação pós-fix:

`bash

Verificar se message_log existe

curl -s "https://dauftiqcvgaydddoxqhh.supabase.co/rest/v1/message_log?select=id&limit=1" \

-H "Authorization: Bearer " -H "apikey: "

deve retornar [] e não erro

Verificar RPC

curl -s "https://dauftiqcvgaydddoxqhh.supabase.co/rest/v1/rpc/get_task_by_meta_message_id?p_meta_message_id=test" \

-H "Authorization: Bearer " -H "apikey: "

deve retornar [] e não PGRST202

`

WhatsApp Spam Block — Causa Raiz e Prevenção

Sintoma (Maio 2026): Número 11913412661 (Davi) bloqueado por 5 horas.

Causa: Envio de alertas/lembretes em massa para usuários que não responderam. O WhatsApp detecta comportamento de spam quando many mensagens são enviadas sem resposta.

Regras de warm-up para números novos:

O que causa bloco:

Prevenção:

Painel do Corretor — GraphQL API (DISCOVERED June 2025)

The Painel has a GraphQL API at https://api.paineldocorretor.net/graphql accessible via Auth0.

Authentication:

`

POST https://auth.paineldocorretor.com.br/oauth/token

Body: {

grant_type: "http://auth0.com/oauth/grant-type/password-realm",

realm: "Username-Password-Authentication",

username: "davi.freire@rochasalesseguros.com.br",

password: "131415pi",

client_id: "MtCJ0_REDACTED",

audience: "https://paineldocorretor.com.br/api/",

scope: "openid profile offline_access email",

response_type: "token id_token"

}

Response: { access_token, id_token, expires_in, refresh_token }

`

Refresh token works: The Auth0 response includes a refresh_token. Use it with:

`

POST https://auth.paineldocorretor.com.br/oauth/token

Body: { grant_type: "refresh_token", refresh_token: "...", client_id: "MtCJ0_REDACTED" }

`

Available fields for deal (negocio):

IMPORTANT: fechamento is always null. There is NO structured meeting date field in the Painel API. The negocio.fechamento returns null for every deal.

Database (crm_leads_duplicate) has no meeting data either:

For automatic meeting creation from Painel, the Painel needs to provide a webhook (n8n or direct) that fires when a meeting is created.

CRITICAL: created_by vs responsible_id — Rayca/Maju Visibility Bug

The Problem

Rayca (pre-sales role) logs in but sees 0 meetings on Dashboard.

Root cause — AppDataContext.tsx lines 128-134:

`typescript

const tasks = useMemo(() => {

if (userRole === 'pre_sales' && userId) {

return allTasks.filter((t) => t.createdBy === userId);

}

return allTasks;

}, [allTasks, userRole, userId]);

`

The filter compares t.createdBy (Supabase Auth UUID) against userId (current user). These are different UUIDs:

Database Reality (354 tasks)

Rayca has 7 tasks where she is responsible but 0 where she is created_by.

User Clarification Needed

User said: "A rayca e a Maju são pré vendedoras, pré só vê as reuniões que ele subiu para os corretores, porém em cronograma eles vem de todos os pré vendedores (reuniões subidas no geral)"

This suggests:

1. In Dashboard — they should see meetings they uploaded (but created_by shows 0 — all meetings have UAZ system created_by)

2. In Cronograma — they should see all meetings from all pre-sellers

All 354 created_by values are from UAZ system UUIDs — the original import did NOT preserve who created the meetings in UAZ. This is a data quality issue.

REST Query 400 — Column Mismatch Pattern (CRITICAL — BREAKING BUG)

Symptom: Supabase REST queries return 400 Bad Request in browser console, but the same query via Edge Function (service role) works fine. Session is valid (getSession result: true), but queries for specific tables always fail.

Root cause pattern: The table schema in the database is MISSING columns that the frontend code expects.

Example 1 — user_roles table:

Example 2 — clients table:

Diagnosis command:

`bash

List actual columns in a table

echo "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'tablename' ORDER BY ordinal_position;" | supabase db query --linked

`

Fix: Add missing columns one by one (bulk ALTER TABLE times out on Supabase Cloud):

`sql

ALTER TABLE clients ADD COLUMN IF NOT EXISTS cpf TEXT;

ALTER TABLE clients ADD COLUMN IF NOT EXISTS cnpj TEXT;

ALTER TABLE clients ADD COLUMN IF NOT EXISTS company_name TEXT;

ALTER TABLE clients ADD COLUMN IF NOT EXISTS client_type TEXT DEFAULT 'adesao';

ALTER TABLE clients ADD COLUMN IF NOT EXISTS health_plan TEXT;

ALTER TABLE clients ADD COLUMN IF NOT EXISTS owner_id UUID REFERENCES auth.users(id);

`

Also check RLS policies — inserts may be blocked:

`sql

-- Check existing policies

SELECT policyname, cmd, roles::text FROM pg_policies WHERE tablename = 'clients' AND schemaname = 'public';

-- Add INSERT policy if missing

CREATE POLICY clients_insert ON public.clients FOR INSERT TO authenticated WITH CHECK (true);

`

Pattern: When 400 hits a table that worked before → schema drifted, new columns needed. When 400 hits a table that never worked → the Lovable-generated code expected columns that were never created in the DB.

DocCorretor Upload — Two-Stage Flow (Server + Supabase RLS)

Fluxo de upload no DocCorretor (clients.$id.tsx):

1. Browser → POST /api/public/upload-document → servidor escreve arquivo no VPS ✅

2. Browser → supabase.from("documents").insert(...) → Supabase REST API → 403 RLS ❌

O servidor valida o token e escreve o arquivo localmente, mas a inserção no banco é direta do navegador para o Supabase — passa pelo RLS.

Causa do 403: O RLS documents_insert tem with_check: "true" (policy sem filtro, aceita tudo), mas o auth.uid() no contexto da API REST do Supabase pode não estar sendo passado corretamente pelo SDK supabase-js quando a conexão é feita no cliente.

Solução: Mover a inserção no banco para o lado do servidor (via SUPABASE_SERVICE_ROLE_KEY), contornando o RLS:

`typescript

// upload-document.ts — após salvar o arquivo no VPS:

const { data: ins, error: insErr } = await supabaseAdmin.from("documents").insert({...})

// supabaseAdmin = createClient(SUPABASE_URL, SERVICE_ROLE_KEY) // bypass RLS

`

ES256 JWT — Validação no servidor: Tokens de Google OAuth via Supabase usam ES256 (ECDSA), não HS256. jwt.verify(token, JWT_SECRET) sempre falha com "invalid algorithm".

`typescript

// CORRETO — valida via API REST do Supabase:

const validateRes = await fetch(${SB_URL}/auth/v1/user, {

headers: { Authorization: Bearer ${token}, apikey: SB_ANON_KEY }

});

const userData = await validateRes.json(); // userData.id é o userId

`

Verificar políticas RLS via API de gestão:

`bash

curl -s -X POST "https://api.supabase.com/v1/projects/{REF}/database/query" \

-H "Authorization: Bearer " \

-H "Content-Type: application/json" \

-d '{"query": "SELECT policyname, cmd, qual, with_check FROM pg_policies WHERE tablename = '\''documents'\'';"}'

`

Nota: with_check: "true" sem filtro significa policy sem restrição (aceita tudo).

has_role funciona corretamente — has_role('107f5dde...', 'adm') retorna true paraTecrocha. O problema não é a função.

Bug 3: Rollup Otimiza Away useMemo — Filtros Quebram Silenciosamente (CRÍTICO — Jun 2026)

Sintoma: Gantt chart não respeita filtro de canal (channel). Todas as barras aparecem independente do filtro selecionado. O useMemo existe no código mas o valor nunca é usado.

Causa raiz: O bundler Rollup elimina uma variável useMemo quando o resultado é passado diretamente para um componente sem ser armazenado em uma variável intermediária:

`typescript

// ❌ CÓDIGO QUE O ROLLUP OTIMIZA AWAY:

const dayFilteredTasks = useMemo(() => allTasks.filter(t => / ... /), [allTasks, date]);

// O resultado só é passado diretamente — Rollup otimiza e remove a variável

return

f === "all" ? dayFilteredTasks : dayFilteredTasks.filter(t => t.channel === f)

, [dayFilteredTasks, f])} />;

// ↑

// O Rollup PODE otimizar isso away se não for stored

`

`typescript

// ✅ CORRETO — Armazena em variável nomeada (Rollup não pode otimizar):

const filteredTasks = useMemo(() => {

if (channelFilter === 'all') return dayFilteredTasks;

return dayFilteredTasks.filter(t => t.channel === channelFilter);

}, [dayFilteredTasks, channelFilter]);

return ;

`

Diagnóstico no browser:

`js

// Verificar se as barras existem no DOM

document.querySelectorAll('.absolute.h-7').length

// 0 = barras não renderizadas; >0 = existem mas podem estar escondidas

// Verificar posição das barras (barras com left/width em % podem sobrepor)

Array.from(document.querySelectorAll('.absolute.h-7')).map(el => ({

left: window.getComputedStyle(el).left,

width: window.getComputedStyle(el).width,

bg: window.getComputedStyle(el).backgroundColor

}))

`

Verificar se o bundle foi gerado corretamente:

`bash

Verificar se o código do filtro existe no bundle compilado

grep -a "channelFilter" /var/www/comercialrs/dist/assets/Gantt-*.js | head -5

Verificar se o useMemo foi preservado

grep -a "useMemo" /var/www/comercialrs/dist/assets/Gantt-*.js | wc -l

`

Padrão de debug Gantt (verificado em produção, Jun 2026):

1. browser_console → document.querySelectorAll('.absolute.h-7').length para contar barras

2. Verificar posição (left, width) — se todas têm left: 37.5%, width: 37.5%, são reuniões do dia inteiro (09:00–18:00) que se sobrepõem

3. Verificar backgroundColor — cada cor corresponde a um status diferente

Gantt bar colors (confirmadas):

Bug 4: Cronograma — Filtro createdBy Removido (Jun 2026)

Decisão de design: Cronograma (Gantt) mostra TODAS as reuniões do dia para TODOS os usuários, sem filtro de createdBy. Os outros painéis (Dashboard, Kanban, Reuniões) mantêm o filtro por createdBy para não-admins.

`typescript

// Gantt.tsx — Removido filtro createdBy:

// ANTES (filtradva por responsável):

const tasks = useMemo(() => {

const filtered = dayFilteredTasks.filter(t =>

(!responsibleFilter || t.createdBy === responsibleFilter) &&

(!channelFilter || t.channel === channelFilter)

);

return filtered;

}, [dayFilteredTasks, responsibleFilter, channelFilter]);

// DEPOIS (sem filtro — todos veem tudo):

const filteredTasks = useMemo(() => {

if (channelFilter === 'all') return dayFilteredTasks;

return dayFilteredTasks.filter(t => t.channel === channelFilter);

}, [dayFilteredTasks, channelFilter]);

`

Regras de visibilidade por painel:

rochasalesseguros.com.br — Edge Runtime 401 Investigation (Jun 2026)

PROBLEMA: Admin panel sections (Ferramentas, Funções, Usuários) retornam {"error":"Unauthorized"} mesmo com token JWT válido de login.

ARQUITETURA:

O QUE FOI DESCARTADO:

1. Kong NÃO tem plugin JWT na rota functions (só CORS plugin existe)

2. functions-proxy nginx é proxy transparente simples (só proxy_pass)

3. Token JWT é CRIPTOGRAFICAMENTE válido (assinatura HMAC-SHA256 confere)

4. VERIFY_JWT=false em ambos GoTrue e edge-runtime

5. JWT_SECRET é IGUAL em ambos (kETxz0JNJhJyg5UYxbd8zG6g2v9kXTYZGjY+3v7LUKI=)

O QUE FALHOU:

HIPÓTESES RESTANTES (não testadas):

1. RLS nas tabelas user_roles/custom_roles bloqueia SERVICE_ROLE_KEY

2. crypto.subtle.verify no Deno do edge-runtime falha com HS256 (espera RS256?)

3. Header Authorization: Bearer mal-formatado chegando no edge-runtime

4. Kong modificando headers entre o proxy e o upstream

COMANDO ÚTIL — diff container vs local:

`bash

docker cp deploy-vps-functions-1:/home/deno/functions/main/index.ts /tmp/container_index.ts

diff /tmp/container_index.ts /opt/rochasales/supabase/functions/main/index.ts

`

COMANDO ÚTIL — ver PID e network do container:

`bash

docker inspect deploy-vps-functions-1 | grep -E '"PID"|"IPAddress"|"NetworkMode"'

`

ATENÇÃO: Não mexer em /opt/rochasales sem antes fazer backup e confirmar que não vai quebrar DocCorretor (port 3010).

Discord Paste Problem (recurring UX issue)

Users copy markdown code fences ( `sql) instead of code content. Discord adds "(editado)" metadata to edited messages, breaking SQL execution.

Always instruct: Delete the `sql lines and any "(editado)" text from pasted content before using in terminal.