name: comercialrs-painel-vps-sync

description: Server-side Node.js sync from Painel do Corretor GraphQL to Supabase — runs on VPS via cron, bypassing browser CSP restrictions.

ComercialRS - Painel do Corretor VPS Sync

Context

Sync leads from Painel do Corretor (Trindade) GraphQL API to Supabase for Chatwoot Kanban. The Painel app has strict CSP that blocks GM_xmlhttpRequest from Tampermonkey and native fetch to external domains. Browser-based sync is unreliable. Server-side Node.js on VPS is the correct approach.

Painel do Corretor API

Auth

• - URL: https://auth.paineldocorretor.com.br/oauth/token
• - Method: POST, JSON
• - Body:

`json

{

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

"realm": "Username-Password-Authentication",

"username": "contato@rochasalesseguros.com.br",

"password": "221109",

"client_id": "REDACTED",

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

}

`

• - Response: { access_token: "...", expires_in: 259200 }
• - Token must be refreshed before each sync cycle (Auth0 short-lived tokens)

GraphQL

• - URL: https://api.paineldocorretor.net/graphql
• - Headers: Authorization: Bearer , x-tenant: a1af7a2f-7ec6-40cd-b257-86c045875979
• - Query (paginated, max 500 per page):

`graphql

query FetchLeads($etapaId:String!,$skip:Int!){

negociosElastic(request:{

busca:null,

take:500,

skip:$skip,

conditions:[{field:"etapaId",operator:"",value:$etapaId}]

}){

quantidade

items{

id nome valor fechamento criadoEm

contato{id nome avatar telefones}

etapa{id nome}

produto{nome}

etiquetas{nome}

}

}

}

`

Stage IDs (from kanban_etapas table)

Stage Key	UUID
-----------	------
novos_leads	60008110-e9b1-4512-b514-ab1539ea1a1e
qualificacao	885d4f95-99e5-42a0-8150-b494c42c0e34
remarketing	6699997d-6978-4679-8b4c-eaab9917fefd
reuniao_agendada	4646a3e9-b21a-4339-b75b-6997fa3dbb76
proposta_enviada	4e61480b-b4dc-4b80-b0c8-dd3ddf8e43b2
analise_seguradora	29511da1-1e40-4e9c-8b14-a268ccbdc1d0
aguardando_pagamento	2584da59-4c1e-4fcb-8cba-8374acd896a7
fechado	df8db361-5b4c-4e66-baac-afcfd9fbe50a
declinado	70150b84-6864-4cff-aae1-83195a182d8d
sem_interesse	63f4b9df-55d6-4ff0-a42c-af5ff2ff367b

Supabase Setup

Tables

• - crm_leads_duplicate — CRM data (Id PK, Nome, Etapa, Valor, Contato.Nome, Contato.Telefones, Produto.Ramo, Etiquetas, source)
• - chatwoot_painel_map — mapping (id, painel_negocio_id, painel_negocio_nome, etapa, chatwoot_conversation_id, chatwoot_contact_phone)
• - kanban_etapas — stage definitions (id uuid, nome, tipo, ordem int4, chatwoot_conversation_id int8)

Upsert Pattern

Use POST /rest/v1/

?on_conflict=id with Prefer: resolution=merge-duplicates.

CRITICAL BUG FOUND (2026-05-19): chatwoot_painel_map has id as PRIMARY KEY. When a lead changes stage in Painel, the sync script was INSERTING a NEW row instead of UPDATE. Root cause: the id field was set to the CRM lead's id (correct for upsert key) but the table's on_conflict=id wasn't matching because the transform used a NEW UUID for id while storing the CRM lead ID in painel_negocio_id.

Correct approach: Both id AND painel_negocio_id must use the CRM lead's id from Painel:

`javascript

function toMap(l) {

return {

id: l.id, // CRM lead's own id = upsert key

painel_negocio_id: l.id, // same value

painel_negocio_nome: l.nome || '',

etapa: l.etapaKey,

chatwoot_conversation_id: null,

chatwoot_contact_phone: telefones,

};

}

`

Never use a new UUID for id — use the CRM lead's id so on_conflict=id correctly updates existing records.

Symptom of the bug: chatwoot_painel_map grows to 2x-10x the actual lead count. Confirmed: 10,000 records but only 5,238 unique leads (4,762 duplicates) due to this bug.

Delete Records — RLS Gotcha

Supabase RLS blocks DELETE with anon key. DELETE returns 204 but does NOT delete the row — the RLS policy silently denies it.

Symptom: You can INSERT and SELECT with anon key, but DELETE appears to succeed (204) but the record remains.

Fix options (in order of preference):

1. Use service_role key (Supabase service_role, not PAT) in the Authorization header for deletes

2. Use Supabase Management API (POST /v1/projects//database/query) with a valid Supabase PAT to run SQL DELETE directly

3. Workaround: Since upsert with on_conflict=id and resolution=merge-duplicates REPLACES existing rows, you rarely need to delete — just re-sync everything cleanly with a fresh upsert when tables get corrupted

When tables get corrupted (e.g. wrong id values inserted): The safest fix is a complete wipe-and-reseed:

1. Disable cron to stop the faulty script

2. Use clean-sync.cjs (which does DELETE → INSERT fresh) OR

3. If DELETE is blocked, use a fresh clean-sync.cjs approach that INSERTs all correct records — the old corrupted records stay but won't matter if you also update the script to use correct IDs going forward

Delete Records

Use DELETE /rest/v1/

?Id=eq. with Prefer: return=minimal. Batch DELETE with id=in.(id1,id2,...) works but individual eq deletes are more reliable.

Count Verification (Server-Side)

Supabase REST returns max 1000 records per query regardless of limit. Use offset/limit for pagination:

`javascript

for (let offset = 0; ; offset += 1000) {

const page = await fetch(${SUPABASE_URL}/rest/v1/table?select=...&limit=1000&offset=${offset}, headers);

if (!page.length) break;

allRecords.push(...page);

}

`

Node.js Server-Side Script Pattern

`javascript

// Use raw https/http module, NOT fetch (Node 18 has fetch but https module is more reliable)

const https = require('https');

function httpPost(url, headers, body, timeout = 25000) {

return new Promise((resolve, reject) => {

const urlObj = new URL(url);

const req = https.request({

hostname: urlObj.hostname, port: 443,

path: urlObj.pathname + urlObj.search,

method: 'POST',

headers: { 'Content-Type': 'application/json', ...headers },

}, (res) => {

let data = '';

res.on('data', c => data += c);

res.on('end', () => { try { resolve(JSON.parse(data)); } catch { resolve(data); } });

});

req.on('error', reject);

req.setTimeout(timeout, () => { req.destroy(); reject(new Error('timeout')); });

req.write(JSON.stringify(body));

req.end();

});

}

`

Key learnings:

• - Do NOT use GM_xmlhttpRequest in browser — CSP blocks it
• - Do NOT use Supabase JS client — anon key issues are common
• - DO use raw https.request from Node.js on VPS — works reliably
• - DO implement retry logic per batch — network errors happen
• - DO use offset/limit pagination (1000 record chunks) — Supabase caps at 1000 per response

Cron Setup

`bash

Wrapper script /var/www/chatwoot-kanban/run-sync.sh

#!/bin/bash

export NVM_DIR="$HOME/.nvm"

[ -s "$NVM_DIR/nvm.sh" ] && \. "$NVM_DIR/nvm.sh"

cd /var/www/chatwoot-kanban

node server-sync.cjs >> /var/log/crm-sync.log 2>&1

Crontab - every 5 minutes

/5 * /var/www/chatwoot-kanban/run-sync.sh

`

Use nvm use 20 (Node 20, not default v18) for better HTTPS handling.

Files

• - /var/www/chatwoot-kanban/server-sync.cjs — main sync script
• - /var/www/chatwoot-kanban/run-sync.sh — cron wrapper
• - /var/log/crm-sync.log — sync log