📄 SKILL.md

← Vault

name: supabase-json-meetings-import

description: Import meetings from an external JSON file (Painel do Corretor) into Supabase tasks table, creating missing team members as needed. Handles dedup, column mapping, and bulk inserts via Edge Function.

category: data-science

Importar Reuniões do Painel do Corretor para Supabase

Contexto

Arquivo JSON baixado do Painel do Corretor com 105 reuniões (últimos 7 dias). Estrutura:

`json

{

"meetings": [

{

"id": "abc123",

"title": " Reunião com Cliente — João Silva",

"status": "in_progress",

"channel": "whatsapp",

"due_date": "2026-06-03T14:00:00Z",

"start_date": "2026-06-03T14:00:00Z",

"responsible_name": "Pietra",

"responsible_phone": "(51) 99999-9999",

"responsible_email": "pietra@email.com",

"link": "https://...",

"priority": "high",

"progress": 50,

"created_by": "user-uuid"

}

]

}

`

Problemas Encontrados

1. Colunas inexistentes na tabela tasks — source, responsible_email, link, start_date, priority, progress, created_by NÃO existem no schema do PostgREST (mesmo existindo no DB)

2. PostgREST schema cache — colunas novas não aparecem no cache até refresh

3. Dedup por external_ref — a maioria dos tasks existentes tem external_ref = null, então dedup precisa ser por title|due_date

4. team_members incompleto — só Ilano existia; membros das reuniões precisavam ser criados antes de referenciar responsible_id

5. Telefone duplicado nos membros — Normalizar phones com replace(/\D/g, "")

Passo a Passo

1. Criar Edge Function temporária para inspecionar o banco

`typescript

// inspect-db/index.ts

Deno.serve(async (req) => {

const url = Deno.env.get("SUPABASE_URL")!;

const key = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;

const [tasksRes, membersRes] = await Promise.all([

fetch(${url}/rest/v1/tasks?deleted_at=is.null&order=created_at.desc&limit=100, {

headers: { Authorization: Bearer ${key}, apikey: key },

}),

fetch(${url}/rest/v1/team_members?order=name, {

headers: { Authorization: Bearer ${key}, apikey: key },

}),

]);

const tasks = await tasksRes.json();

const members = await membersRes.json();

return new Response(JSON.stringify({ tasks, members }, null, 2), {

headers: { "Content-Type": "application/json", "Access-Control-Allow-Origin": "*" },

});

});

`

Deploy: SUPABASE_ACCESS_TOKEN="sbp_..." supabase functions deploy inspect-db --no-verify-jwt

2. Preparar payload JSON

`python

import json

with open('reunioes.json') as f:

data = json.load(f)

meetings = data['meetings']

payload = {"meetings": meetings}

with open('/tmp/compare_payload.json', 'w') as f:

json.dump(payload, f, ensure_ascii=False)

`

3. Criar função de sync

COLUNAS CONFIRMADAS em tasks: id, title, status, channel, due_date, external_ref, responsible_id, project_id, created_by

NÃO USAR (sem confirmar): source, responsible_email, link, start_date, priority, progress, responsible_name, responsible_phone

`typescript

// sync-meetings/index.ts

Deno.serve(async (req) => {

const url = Deno.env.get("SUPABASE_URL")!;

const key = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;

const { meetings } = await req.json();

// 1. Buscar membros existentes

const membersRes = await fetch(${url}/rest/v1/team_members?select=id,name,phone,email, {

headers: { Authorization: Bearer ${key}, apikey: key },

});

const members: Array> = await membersRes.json();

const memberByPhone = new Map();

const memberByEmail = new Map();

members.forEach(m => {

if (m.phone) memberByPhone.set(m.phone.replace(/\D/g, ""), m);

if (m.email) memberByEmail.set((m.email || "").toLowerCase(), m);

});

// 2. Buscar tasks existentes (14 dias) para dedup

const cutoff = new Date(Date.now() - 14 24 60 60 1000).toISOString();

const existingRes = await fetch(${url}/rest/v1/tasks?deleted_at=is.null&created_at=gte.${cutoff}&select=id,external_ref,title,due_date, {

headers: { Authorization: Bearer ${key}, apikey: key },

});

const rawExisting = await existingRes.json();

const existing: Array> = Array.isArray(rawExisting) ? rawExisting : [];

// Dedup: external_ref OU title|due_date

const existingRefs = new Set(existing.map(t => t.external_ref).filter(Boolean));

const existingKeys = new Set(existing.map(t => ${t.title || ""}|${t.due_date || ""}.toLowerCase()));

// 3. Criar membros faltantes

const nameToInfo = new Map();

meetings.forEach((m: Record) => {

const name = (m.responsible_name || "").trim();

if (!name) return;

const nl = name.toLowerCase();

if (!nameToInfo.has(nl)) nameToInfo.set(nl, { phone: m.responsible_phone || "", email: m.responsible_email || "" });

});

const newMembers: Array<{ name: string; phone: string; email: string }> = [];

const existingNames = new Set(members.map(m => m.name.toLowerCase()));

for (const [name, info] of nameToInfo.entries()) {

if (!existingNames.has(name)) {

newMembers.push({ name: name.replace(/\b\w/g, c => c.toUpperCase()), phone: info.phone, email: info.email });

}

}

// Inserir membros novos

if (newMembers.length > 0) {

await fetch(${url}/rest/v1/team_members, {

method: "POST",

headers: { Authorization: Bearer ${key}, apikey: key, "Content-Type: "application/json", Prefer: "return=minimal" },

body: JSON.stringify(newMembers),

});

// Refetch membros para ter IDs

const refetch = await fetch(${url}/rest/v1/team_members?select=id,name,phone,email, {

headers: { Authorization: Bearer ${key}, apikey: key },

});

const updated: Array> = await refetch.json();

updated.forEach(m => {

if (m.phone) memberByPhone.set(m.phone.replace(/\D/g, ""), m);

if (m.email) memberByEmail.set((m.email || "").toLowerCase(), m);

});

}

// 4. Preparar tasks

const toInsert: Array> = [];

for (const m of meetings) {

const ref = m.id as string;

const key2 = ${(m.title as string || "").toLowerCase()}|${m.due_date || ""};

if ((ref && existingRefs.has(ref)) || existingKeys.has(key2)) continue;

const phone = (m.responsible_phone as string || "").replace(/\D/g, "");

const email = (m.responsible_email as string || "").toLowerCase();

let responsible_id: string | null = null;

if (phone && memberByPhone.has(phone)) responsible_id = memberByPhone.get(phone)!.id;

else if (email && memberByEmail.has(email)) responsible_id = memberByEmail.get(email)!.id;

// SÓ colunas confirmadas!

toInsert.push({

external_ref: ref || null,

title: (m.title as string) || "",

status: m.status === "completed" ? "completed" : m.status === "in_progress" ? "in_progress" : "todo",

channel: m.channel || "mensagem",

due_date: m.due_date || null,

responsible_id,

});

}

// 5. Bulk insert

let inserted = 0;

for (let i = 0; i < toInsert.length; i += 100) {

const chunk = toInsert.slice(i, i + 100);

const insRes = await fetch(${url}/rest/v1/tasks, {

method: "POST",

headers: { Authorization: Bearer ${key}, apikey: key, "Content-Type": "application/json", Prefer: "return=minimal" },

body: JSON.stringify(chunk),

});

if (insRes.ok) inserted += chunk.length;

}

return new Response(JSON.stringify({ success: true, total: meetings.length, inserted }), {

headers: { "Content-Type": "application/json", "Access-Control-Allow-Origin": "*" },

});

});

`

4. Deployar e rodar

`bash

cd /var/www/comercialrs

SUPABASE_ACCESS_TOKEN="sbp_..." supabase functions deploy sync-meetings --no-verify-jwt

SERVICE_KEY="4513a6f..." # service role key

ANON_KEY="eyJhbGci..." # anon key

SB_URL="https://dauftiqcvgaydddoxqhh.supabase.co"

curl -s --max-time 60 -X POST "$SB_URL/functions/v1/sync-meetings" \

-H "Authorization: Bearer $SERVICE_KEY" \

-H "apikey: $ANON_KEY" \

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

-d @/tmp/compare_payload.json | python3 -m json.tool

`

5. Limpar funções temporárias após uso

`bash

Deletar via Dashboard ou deixar as Edge Functions temporárias

Funções temporárias criadas: inspect-db, compare-meetings, sync-meetings,

fix-team-duplicates, check-phones, check-member-phones, diagnose-sync,

reset-admin-password, read-config

`

Armadilhas