name: supabase-sync-external-ids-to-local

description: Map external system IDs to local Supabase foreign keys when importing data — solves "names not showing" after sync

Sync External IDs to Local Supabase References

When to Use

When importing data from an external JSON/source into Supabase, and the external data has IDs pointing to people/entities, but the local Supabase database has DIFFERENT IDs for the same entities (in a team_members or similar table). The frontend expects local IDs, but the imported data has external IDs.

Problem Pattern

• - JSON has responsible_obj.id (external UUID from external system)
• - JSON has responsible_obj.name (name of the person)
• - Local Supabase team_members table has DIFFERENT UUIDs for the same people
• - Frontend does JOIN or team_members lookup by responsible_id → expects local UUID
• - Result: names don't appear in UI even though data is "synced"

Solution: Name-Based Mapping

`

external JSON task.responsible_obj.id → map by name → team_members.id (local)

`

Step-by-step

1. Fetch all team_members from Supabase → build {name_lower: local_id} map

2. For each imported task, extract task.responsible_obj.name

3. Look up name_lower in the map to get local team_members.id

4. PATCH the task's responsible_id with the LOCAL id

Critical Constraint

You CANNOT add a responsible_name column via the REST API when:

• - Management API returns 403 (insufficient permissions)
• - ALTER TABLE via RPC not available
• - PostgREST schema cache (PGRST204) blocks unknown columns

Workaround: update responsible_id to point to local team_members row (name-based lookup).

Code Pattern (Edge Function)

`typescript

const supabaseUrl = Deno.env.get('SUPABASE_URL')!;

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

// 1. Build name → local_id map from team_members

const teamMembers: Record = {};

let page = 0;

while (true) {

const data = await fetchJSON(

${supabaseUrl}/rest/v1/team_members?select=id,name&limit=1000&offset=${page * 1000},

{ headers: { 'Authorization': Bearer ${serviceKey}, 'apikey': serviceKey } }

);

if (!data || data.length === 0) break;

for (const m of data) teamMembers[m.name.toLowerCase()] = m.id;

if (data.length < 1000) break;

page++;

}

// 2. For each task, map external_id → local_id by name

for (const dbTask of allDbTasks) {

const name = idToName[dbTask.external_ref || dbTask.id];

if (!name) continue;

const localId = teamMembers[name.toLowerCase()];

if (!localId) continue;

await fetch(${supabaseUrl}/rest/v1/tasks?id=eq.${dbTask.id}, {

method: 'PATCH',

headers: {

'Authorization': Bearer ${serviceKey},

'apikey': serviceKey,

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

'Prefer': 'return=minimal',

},

body: JSON.stringify({ responsible_id: localId }),

});

}

`

Key Gotchas

• - Always use Prefer: return=minimal on PATCH to avoid body bloat
• - External IDs are USELESS for JOINs — always resolve to local IDs
• - If team_members is empty, populate it FIRST before syncing tasks
• - Name matching is case-insensitive (toLowerCase())
• - Partial match fallback: Object.keys(map).find(k => name.toLowerCase().includes(k))

Applicable Tables

• - tasks.responsible_id → team_members.id
• - automations.team_members_id → team_members.id
• - Any foreign key where source data has IDs from a different system