name: doccorretor-link-flow

description: DocCorretor public upload link flow — generate link without existing client, broker creates client + uploads documents

category: devops

DocCorretor Public Upload Link Flow

Architecture Overview

Problem: Generate a shareable link for brokers to upload client documents WITHOUT requiring an existing client in the system.

Solution: Token-based public links where the broker self-registers the client on first access.

`

Admin clicks "Link para corretor"

→ creates "Documentos" folder (client_id = NULL) ← CRITICAL: folder created HERE, not later

→ creates link record with folder_id pointing to that folder

→ Link shown in Sheet → copied to clipboard

→ Broker opens link → sees client registration form → submits

→ API updates the PRE-CREATED folder with client_id → redirect to upload

`

The Critical Bug: client_id Determines the Flow

The upload.$token.tsx page decides what to show based on whether client_id is NULL or filled:

`typescript

// From upload.$token.tsx useEffect:

const r = await fetch(/api/public/validate-upload-link?token=...);

const data = await r.json();

if (!data.client_id) {

setShowClientForm(true); // → mostra formulário de novo cliente

} else {

// → tem client_id = vai direto pra upload (pula criação de cliente)

setNewClientId(data.client_id);

setNewFolderId(data.folder_id);

}

`

The current correct flow (generateClientLink in index.tsx):

`typescript

async function generateClientLink() {

const folderId = crypto.randomUUID();

// 1. Criar pasta "Documentos" IMEDIATAMENTE quando link é gerado

await supabase.from("client_folders").insert({

id: folderId,

client_id: null, // será atualizado quando externo preencher

name: "Documentos",

created_by: u.user.id,

});

// 2. Criar link com folder_id apontando para a pasta já criada

await supabase.from("client_upload_links").insert({

client_id: null,

token,

folder_id: folderId, // pasta pré-criada

created_by: u.user.id,

});

}

`

Pre-creation flow (current, working):

1. Admin clicks "Link para corretor" → folder "Documentos" created with client_id=null → link inserted with folder_id

2. External opens link → validates token → shows new client form

3. External fills form → API fetches folder_id from link → updates pre-created folder with client_id

4. External redirected to upload page

This is the ONLY correct flow as of Maio 2026. Never create folder inside create-client-with-link.ts — it should already exist.

Why pre-create the folder: The admin expects the link to be "ready" the moment it's generated. By pre-creating the folder with client_id=null, when the external person fills the form and the API updates the folder with the real client_id, no new folder needs to be created — just an UPDATE, which is faster and more reliable.

`typescript

// ✅ CORRECT — link SEM client_id = força formulário de novo cliente

const { error } = await supabase.from("client_upload_links").insert({

client_id: null, // null = externo preenche formulário e cria cliente

token,

created_by: u.user.id,

});

// ❌ WRONG (bug) — link COM client_id = pula criação de cliente

const { error } = await supabase.from("client_upload_links").insert({

client_id: pendingClientId, // bug: externo não podia criar cliente

token,

created_by: u.user.id,

});

`

Database Schema

Tables used:

• - client_upload_links: id, client_id (NULL initially), token, folder_id, expires_at, used_at, created_at, created_by
• - client_folders: id, client_id (NULL initially), name, created_by, created_at
• - clients: id, owner_id, client_type (adesao/pme), name, cpf, cnpj, company_name, health_plan, created_at
• - documents: id, client_id, original_name, stored_name, storage_path, size_bytes, mime_type, category, folder_id, created_at

Constraints:

• - client_folders.client_id must be NULLable
• - client_upload_links.client_id starts NULL, filled when broker registers

Server Routes (TanStack Start internal API)

POST /api/public/create-client-with-link

Broker calls this after filling the registration form:

1. Validate token exists and has no client_id yet

2. INSERT into clients (using system user 00000000-0000-0000-0000-000000000001 as owner_id)

3. If link has folder_id: UPDATE the PRE-CREATED folder with client_id (current production flow)

4. If link has NO folder_id: INSERT a new "Documentos" folder, then UPDATE link with new folder_id

5. UPDATE link with client_id

6. Returns {clientId, folderId, clientName}

`typescript

// Fetch current folder_id from link

const linkInfoRes = await fetch(MANAGEMENT_API, {

method: "POST",

headers: { "Content-Type": "application/json", Authorization: Bearer ${PAT} },

body: JSON.stringify({

query: SELECT folder_id FROM public.client_upload_links WHERE token = '${token.trim()}',

}),

});

const linkInfoData = JSON.parse(await linkInfoRes.text());

let actualFolderId = linkInfoData[0].folder_id;

if (!actualFolderId) {

// Link was created WITHOUT pre-created folder (old flow — kept for backward compat)

// Create folder now

const newFolderId = crypto.randomUUID();

await supabase.from("client_folders").insert({ id: newFolderId, client_id: clientId, name: "Documentos", created_by: '00000000-...' });

actualFolderId = newFolderId;

} else {

// Link has pre-created folder (current flow) — just update it with client_id

await supabase.from("client_folders").update({ client_id: clientId }).eq("id", actualFolderId);

}

`

GET /api/public/validate-upload-link?token=xxx

Returns client data if link has client_id, or indicates is_new_client if still NULL.

POST /api/public/upload-with-link

Uses local filesystem, NOT Supabase Storage. PAT is rejected by Supabase Storage REST API ("Invalid Compact JWS"). Files saved to /var/www/documentos/uploads/public/{clientId}/{folderId}/{timestamp}-{filename}. Nginx serves /uploads/ from /var/www/documentos/uploads/. Upload validated via x-upload-token header.

Key Decisions

1. Management API via server fetch — avoids CORS issues from browser. PAT: sbp_REDACTED

2. System user: 00000000-0000-0000-0000-000000000001 (must exist in auth.users AND public.users)

3. client_id must be NULL on link creation — folder and link start empty, broker fills them

4. Link format: https://documentos.rochasalesseguros.com.br/upload/{40-char-token}

5. Asset sync after build: mkdir -p dist/client/assets && cp -r dist/server/assets/* dist/client/assets/

Verify System User

`bash

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

-H "Authorization: Bearer sbp_REDACTED" \

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

-d '{"query": "SELECT id FROM auth.users WHERE id = '\''00000000-0000-0000-0000-000000000001'\''"}'

`

Test the Flow

`bash

1. Generate link via UI (writes client_upload_links with client_id=NULL)

2. Validate link (should show no client_id → is_new_client flow)

curl -s "https://documentos.rochasalesseguros.com.br/api/public/validate-upload-link?token={TOKEN}"

3. Create client via form (call create-client-with-link)

curl -s -X POST https://documentos.rochasalesseguros.com.br/api/public/create-client-with-link \

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

-d '{"token":"{TOKEN}","client_type":"adesao","name":"João Silva","cpf":"12345678901","health_plan":"Unimed"}'

4. Validate again (should now show client_id → upload flow)

curl -s "https://documentos.rochasalesseguros.com.br/api/public/validate-upload-link?token={TOKEN}"

`

Known Issues Fixed

Issue: 403 on client_folders INSERT or UPDATE (Supabase Cloud RLS)

Symptom: generateClientLink() fails with 403 on client_folders INSERT, or 403 on UPDATE when external fills form.

Root cause: Supabase Cloud doesn't create INSERT/UPDATE policies for client_folders by default. RLS blocks all operations without explicit policies.

Fix — create missing policies via Management API:

`bash

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

-H "Authorization: Bearer sbp_REDACTED" \

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

-d '{"query": "CREATE POLICY client_folders_insert_2 ON public.client_folders FOR INSERT WITH CHECK (true);"}'

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

-H "Authorization: Bearer sbp_REDACTED" \

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

-d '{"query": "CREATE POLICY client_folders_update ON public.client_folders FOR UPDATE USING (true) WITH CHECK (true);"}'

`

Current RLS policies on client_folders (Supabase Cloud):

• - client_folders_insert — INSERT, permissive=true
• - client_folders_insert_2 — INSERT extra policy, WITH CHECK true
• - client_folders_select — SELECT, permissive=true
• - client_folders_update — UPDATE, USING true, WITH CHECK true

Current RLS policies on clients (Supabase Cloud):

• - clients_select — SELECT
• - clients_insert — INSERT
• - clients_update — UPDATE
• - clients_delete — DELETE (via Management API, not RLS — created manually)

If 403 persists, query existing policies first:

`bash

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

-H "Authorization: Bearer sbp_..." \

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

-d '{"query": "SELECT polname, polcmd, polpermissive FROM pg_policy WHERE polrelid = '\''public.client_folders'\''::regclass;"}'

`

Issue: Client deletion silently fails (no error shown)

Symptom: Clicking "Excluir" on a client card does nothing — no toast, no error, client not deleted.

Root cause: Supabase Cloud does NOT create a DELETE policy on clients by default. Without a policy for DELETE, the RLS engine denies ALL delete requests silently (no error thrown, just blocked).

Fix: Create the missing DELETE policy via Management API:

`bash

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

-H "Authorization: Bearer sbp_REDACTED" \

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

-d '{"query": "CREATE POLICY clients_delete ON public.clients FOR DELETE USING (true)"}'

`

Issue: Storage file deletion fails on client deletion

Symptom: Deleting a client fails when supabase.storage.from('documents').remove(paths) is called — even if the files don't belong to the current user's RLS scope.

Root cause: Supabase Storage RLS checks ownership. If storage_path entries in documents were inserted by a different owner_id, the storage delete request is rejected.

Fix: Wrap storage delete in try/catch and always proceed with DB deletion even if storage fails:

`typescript

async function deleteClient(c: ClientRow) {

const { data: docs } = await supabase.from("documents").select("storage_path").eq("client_id", c.id);

const paths = (docs ?? []).map((d) => d.storage_path).filter(Boolean) as string[];

try {

if (paths.length) {

await supabase.storage.from("documents").remove(paths);

}

} catch { / ignore storage errors — proceed with DB deletion / }

await supabase.from("documents").delete().eq("client_id", c.id);

await supabase.from("clients").delete().eq("id", c.id);

qc.invalidateQueries({ queryKey: ["clients"] });

}

`

Security Fixes Applied (Maio 2026)

SQL Injection Prevention — All API Queries Use Parameterized Statements

All Management API SQL queries now use $1, $2, ... placeholders with explicit params arrays instead of string interpolation. This prevents SQL injection via crafted token values.

Files fixed:

• - validate-upload-link.ts — 1 query (token)
• - upload-with-link.ts — 2 queries (token validation, document INSERT)
• - create-client-with-link.ts — 6 queries (token validation x3, client INSERT, folder INSERT/UPDATE, link UPDATE)

Pattern used:

`typescript

// ❌ WRONG — vulnerable to SQL injection

body: JSON.stringify({

query: SELECT id FROM client_upload_links WHERE token = '${token}',

})

// ✅ CORRECT — parameterized

body: JSON.stringify({

query: SELECT id FROM client_upload_links WHERE token = $1,

params: [token.trim()],

})

`

folderId Empty String Guard

In upload-with-link.ts, an empty string folderId ("") was bypassing the UUID validation check (if (folderId && ...) — empty string is falsy). This caused NULL to be inserted into folder_id. Fixed:

`typescript

// ❌ WRONG — empty string bypasses check

if (folderId && !uuidRe.test(folderId)) { ... }

// ✅ CORRECT — explicit null/empty check

if (folderId !== null && folderId !== undefined && folderId !== "" && !uuidRe.test(folderId)) { ... }

const safeFolderId = (folderId === "" || folderId === null || folderId === undefined) ? null : folderId;

`

Token Length Validation

upload-with-link.ts now validates token before use:

`typescript

if (!token || token.length < 10) {

return new Response(JSON.stringify({ error: "Token inválido" }), { status: 400 });

}

`

Files Involved

• - /var/www/documentos2/app/src/routes/api/public/create-client-with-link.ts
• - /var/www/documentos2/app/src/routes/api/public/upload-with-link.ts
• - /var/www/documentos2/app/src/routes/upload.$token.tsx — public form page, decides flow by client_id presence
• - /var/www/documentos2/app/src/routes/index.tsx — generateClientLink() must insert with client_id: null