name: supabase-edge-function-schema-discovery

description: Discover Supabase table schema via Edge Function insert tests when direct DB access and information_schema are unavailable

triggers:

- "supabase schema discovery"

- "PGRST204 column not found"

- "debug supabase edge function insert"

- "Supabase table structure unknown"

Supabase Edge Function — Schema Discovery via Insert Tests

Context

When you don't have direct database access (no pg_dump, no Management API for schema queries), and the PostgREST API doesn't expose information_schema.columns, you can discover table structure by making Edge Function insert calls and reading the error messages.

The Problem

Supabase Edge Functions run with service_role so they bypass RLS — but they still hit PostgREST (port 54321) which validates INSERT payloads against the actual schema. Unknown columns → PGRST204 error. Required columns without defaults → 23502 null violation.

Technique: Insert + Select Pattern

In the Edge Function, add a temporary debug_schema action:

`typescript

if (action === 'debug_schema') {

// Test 1: minimal insert (only name field)

const { data: t1, error: e1 } = await admin

.from('table_name')

.insert({ name: 'Test' })

.select('*')

.limit(1);

// Test 2: insert with suspected columns

const { data: t2, error: e2 } = await admin

.from('table_name')

.insert({ name: 'Test2', parent_id: null, client_id: null })

.select('*')

.limit(1);

return json({

test1: { data: t1, error: e1 ? { message: e1.message, code: e1.code } : null },

test2: { data: t2, error: e2 ? { message: e2.message, code: e2.code } : null }

});

}

Deploy and call with curl:

`bash

curl -s -X POST "https://PROJECT_REF.supabase.co/functions/v1/FUNCTION_NAME" \

-H "Authorization: Bearer $ANON_KEY" \

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

-d '{"action":"debug_schema"}'

What Errors Tell You

Error code	Meaning
------------	---------
`PGRST204`	Column doesn't exist in schema
`23502`	Null violation — column is REQUIRED and not provided
`42501`	RLS policy denied (Edge Function service_role bypasses this, so if you see it something is wrong with auth)
`42703`	Ambiguous column / unknown column name

Key Learnings from DocCorretor schema discovery

- client_folders table has NO is_folder column and NO parent_id column — flat structure

- client_folders requires created_by (profile UUID, not auth.users UUID)
- clients table primary key is a UUID, created_at auto-generated
- Profile IDs are the same as auth.users.id (they're identical UUIDs)
- Admin profile used for migrations: ded98056-a15d-42cf-860e-94d82d2dc7e5 (Davi)

Workflow

1. Write a debug action in the Edge Function that tries minimal inserts

2. Deploy with supabase functions deploy FUNCTION_NAME

3. Call via curl and inspect errors

4. Adjust insert payload based on error feedback

5. Repeat until insert succeeds

6. Remove debug action and implement real logic

Supabase project reference

- Project: dauftiqcvgaydddoxqhh (shared: ComercialRS + DocCorretor)
- Edge Function: migrate-drive (deployed from /var/www/documentos2/app/)
- Auth: anon key for calling, service_role key internally (via SUPABASE_SERVICE_ROLE_KEY env var)