name: doccorretor-admin-role-debug

description: Debug and fix admin access in DocCorretor (documentos.rochasalesseguros.com.br) — when user has role='admin' in profiles but is still redirected to login or gets "no admin access"

tags:

- supabase

- rls

- doccorretor

- auth

- roles

DocCorretor Admin Access Debug

Trigger Conditions

• - User can login but gets redirected to login page when accessing /admin
• - User shows "Entrar com Google" even after being logged in
• - User has role='admin' in profiles table but still can't access admin panel

Architecture

DocCorretor uses TWO separate role systems that must BOTH be correct:

The frontend useAuth.ts checks user_roles.role (not profiles.role) to set isAdm.

The Edge Function admin-users uses has_role(_user_id, 'admin') which queries user_roles.role.

Root Causes

Bug 1: user_roles missing entry

profiles.role is set but user_roles has no row for the user. The has_role() function returns false.

Symptom: User can login but isAdm is always false in AuthContext.

Fix: Insert into user_roles:

`sql

-- Find the user's id and the admin role_id first

SELECT id FROM profiles WHERE email = 'tecrochasales@gmail.com';

SELECT id FROM roles WHERE name = 'admin';

-- Then insert

INSERT INTO user_roles (id, user_id, role, role_id, created_at)

VALUES (

gen_random_uuid(),

'',

'admin',

'',

NOW()

);

`

Bug 2: user_roles entry has wrong role string

User exists in user_roles but with role 'adm' instead of 'admin'. The has_role() function checks exact string match.

Fix: Update the role string:

`sql

UPDATE user_roles

SET role = 'admin', role_id = ''

WHERE user_id = '';

`

Bug 3: RLS blocking reads on user_roles

The REST API (/rest/v1/user_roles) returns [] because RLS blocks reads via anon key. This is NORMAL — it doesn't mean the table is empty.

Verify with Management API (bypasses RLS):

`bash

PAT=""

PROJECT="dauftiqcvgaydddoxqhh"

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

-H "Authorization: Bearer $PAT" \

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

-d '{"query": "SELECT id, user_id, role, role_id FROM user_roles WHERE user_id = '\'''\'';"}'

`

Bug 4: profiles.role and user_roles.role out of sync

profiles.role says 'admin' but user_roles.role says 'corretor' or nothing. Frontend may show correct role in UI but backend auth checks fail.

Fix: Make both consistent — set user_roles.role = profiles.role for the user.

Diagnostic Steps

1. Get user profile id

`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, email, role FROM profiles WHERE email = '\'''\'';"}'

`

2. Check user_roles for that 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 ur.*, r.name as role_name FROM user_roles ur LEFT JOIN roles r ON r.id = ur.role_id WHERE ur.user_id = '\'''\'';"}'

`

3. Test has_role function

`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 * FROM has_role('\'''\'', '\''admin'\'');"}'

`

Returns {"has_role": true} if user has admin access.

4. Get admin role_id

`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, name FROM roles WHERE name = '\''admin'\'';"}'

`

5. Check role_permissions (section-level permissions)

`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 rp.*, p.key, p.module, p.action FROM role_permissions rp JOIN permissions p ON p.id = rp.permission_id WHERE rp.role = '\''admin'\'' ORDER BY p.module;"}'

`

This shows what the 'admin' role can do (automations.manage, sales., tasks., team.*, settings.manage).

Key Files

useAuth.ts — /var/www/documentos2/app/src/hooks/useAuth.ts

• - Sets isAdm = roles.includes('admin') based on user_roles.role
• - Queries supabase.from('user_roles').select('role').eq('user_id', uid)
• - If user_roles is empty or wrong, isAdm will be false

usePermissions.ts — /var/www/documentos2/app/src/hooks/usePermissions.ts

• - Checks role_permissions table for section-level permissions (can_read/can_edit/can_delete)
• - Skipped if isAdm === true
• - Nav items visible by default: nav_clientes, nav_admin, nav_painel

RequireAuth component — /var/www/documentos2/app/src/components/RequireAuth.tsx

• - Redirects to /auth if user doesn't have required role
• - Used as on /admin route

Full Fix Procedure

`bash

Variables

USER_EMAIL="tecrochasales@gmail.com"

PAT="sbp_REDACTED"

PROJECT="dauftiqcvgaydddoxqhh"

1. Get user id

USER_ID=$(curl -s -X POST "https://api.supabase.com/v1/projects/$PROJECT/database/query" \

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d "{\"query\": \"SELECT id FROM profiles WHERE email = '$USER_EMAIL' LIMIT 1;\"}" | \

python3 -c "import sys,json; print(json.load(sys.stdin)[0]['id'])")

2. Get admin role_id

ADMIN_ROLE_ID=$(curl -s -X POST "https://api.supabase.com/v1/projects/$PROJECT/database/query" \

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d '{"query": "SELECT id FROM roles WHERE name = '\''admin'\'' LIMIT 1;"}' | \

python3 -c "import sys,json; print(json.load(sys.stdin)[0]['id'])")

3. Check if user_roles entry exists

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

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d "{\"query\": \"SELECT * FROM user_roles WHERE user_id = '$USER_ID';\"}"

4. Insert or update

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

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d "{\"query\": \"INSERT INTO user_roles (id, user_id, role, role_id, created_at) VALUES (gen_random_uuid(), '$USER_ID', 'admin', '$ADMIN_ROLE_ID', NOW()) ON CONFLICT (user_id) DO UPDATE SET role = 'admin', role_id = '$ADMIN_ROLE_ID' RETURNING *;\"}"

5. Verify

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

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d "{\"query\": \"SELECT * FROM has_role('$USER_ID', 'admin');\"}"

`

Important Notes

• - Do NOT use anon key REST API to check user_roles — RLS always returns [] even when data exists. Use Management API (api.supabase.com/v1/projects/.../database/query) instead.
• - profiles.role and user_roles.role are independent — a user can have role='admin' in profiles but empty user_roles. The frontend checks user_roles.
• - The has_role() function checks user_roles.role with exact string match for 'admin'.
• - OAuth login (Google) redirects to /auth/callback — if that route returns 404, the app build may be stale. Rebuild with cd /var/www/documentos2/app && nvm use 20 && npm run build.

Storage & Upload URLs (related system)

• - Public uploads: /var/www/documentos/uploads/public/{clientId}/{folderId}/{timestamp}-{filename}
• - Upload links generated via Edge Function client-upload-info, stored in client_upload_links table
• - Nginx serves /uploads/ from /var/www/documentos/uploads/
• - Never use PAT for Supabase Storage REST API — use local filesystem pattern instead

DocCorretor Database Schema — REAL vs Migrations

The local migrations in app/supabase/migrations/ describe a doc_category ENUM and many NOT NULL

constraints, but the actual database (verified via Management API, Jun 2026) is DIFFERENT:

Table	Column	Used For
-------	--------	----------
profiles	role	Display label (admin/corretor/editor/viewer)
user_roles	role	Actual permission check via has_role() function
role_permissions	can_read/can_edit/can_delete	Per-section permissions per role_id
Aspect	Migrations say	Actual database
--------	---------------	---------------------
documents.category	doc_category enum	text (accepts any string)
Column nullability	Many NOT NULL	All nullable
Schema complexity	Complex with triggers	Simple

Implication: Adding document categories requires NO database migration.

Only update TypeScript code (CATEGORY_LABELS + CATS).

Adding Document Categories

Categories are defined in THREE places (all in app/src/):

1. lib/storage-helpers.ts — CATEGORY_LABELS object (human-readable labels)

2. routes/admin.tsx — const CATS array (category dropdown in admin panel)

3. Database: documents.category — plain text, no constraint

Steps to add a category:

1. Add entry to CATEGORY_LABELS in storage-helpers.ts

2. Add key to CATS array in admin.tsx

3. Build and deploy — NO SQL migration needed

Building DocCorretor on VPS

The VPS has Node 20.20.2 at /root/.nvm/versions/node/v20.20.2/bin/node.

The nvm command may not be in PATH. Always use the full path directly:

`bash

cd /var/www/documentos/deploy-vps-nodejs/app

/root/.nvm/versions/node/v20.20.2/bin/node ./node_modules/vite/bin/vite.js build

`

Running npm run build fails because it uses system node (v18). Build output: dist/client/ + dist/server/.

Deploy: copy dist/ to the running app directory and restart:

`bash

sudo systemctl stop documentos

cp -r /var/www/documentos/deploy-vps-nodejs/app/dist /var/www/documentos2/app/dist

sudo systemctl start documentos

`

Supabase Management API vs REST API

Management API (DDL — ALTER TYPE, CREATE TABLE, etc.):

• - URL: https://api.supabase.com/v1/projects/{ref}/database/query
• - Auth: Personal Access Token from supabase.com/dashboard/account/tokens
• - Service role key does NOT work — gives "JWT failed verification"

REST API (table CRUD — SELECT, INSERT, etc.):

• - URL: https://dauftiqcvgaydddoxqhh.supabase.co/rest/v1/
• - Auth: anon key in apikey + Authorization: Bearer headers
• - Service role key does NOT work for REST API (gives 401)
• - Anon key works for read queries; inserts need authenticated session

Google Drive Migration (Jun 2026)

The Drive folder 1zVhApvpFM937qxsvsz37WCcUD-7f2zUP is organized by month/year:

documentos clientes/2023/08_2023//...

Structure: top-level = year, second-level = month, third-level = client name, files inside.

gdown limitation: --folder flag cannot download folders with restricted subfolders.

If Drive folder is public, gdown may still fail on restricted content.

VPS network issues: All outbound connections (curl, wget, gdown, execute_code network)

may be blocked intermittently. If all external connections fail, use Option A:

user downloads Drive folder as .zip and uploads to VPS for local processing.