name: rochasales-broker-area-debug

description: Debug broker area (Ferramentas) not showing on rochasalesseguros.com.br — self-hosted Supabase on VPS, two role systems confusion

rochasales Broker Area Debug

Context

rochasalesseguros.com.br broker area (Ferramentas da Área do Corretor) not showing tools.

Architecture

• - Project path: /opt/rochasales (NOT /var/www/rochasales — that's a different MongoDB project)
• - VPS: 31.97.243.106, SSH root / Marcia19671951@
• - Database: Self-hosted Supabase PostgreSQL in Docker container deploy-vps-db-1

- Access: docker exec deploy-vps-db-1 psql -U postgres -d postgres

• - Kong container: REMOVED — do NOT look for deploy-vps-kong-1. Kong is gone, replaced by direct nginx → container proxy
• - Current containers (check with docker ps):

- deploy-vps-rest-1 → PostgREST on port 3002

- deploy-vps-auth-1 → GoTrue on port 9999 (internal network: 172.23.0.3)

- deploy-vps-db-1 → PostgreSQL on port 5433 (external)

- deploy-vps-functions-1 → Edge Functions on port 3000 (internal: 172.23.0.5)

• - Supabase URL: https://rochasalesseguros.com.br
• - Anon key: eyJ_REDACTED

Key Tables

Table	Schema	Purpose
-------	--------	---------
auth.users	auth	Supabase auth users
auth.profiles	auth	EMPTY — not used by this app (ignore completely)
public.profiles	public	User profiles with custom_role_id FK to custom_roles
public.user_roles	public	Maps user_id (text, auth.users UUID) → text role ('admin','manager','user','moderator','pre_sales')
public.custom_roles	public	UUID-based roles: 'Corretor', 'ADM', 'Gestor', 'Pré-Venda'
public.broker_links	public	Tools/links (7 active) — RLS allows all authenticated
public.tool_role_access	public	Maps broker_link_id → custom_role_id (UUID)

Critical: Two Separate Role Systems

System 1 — Text roles (used by has_role() / RLS)

• - Table: public.user_roles (column: role as text)
• - Values: 'admin', 'manager', 'user', 'moderator', 'pre_sales'
• - Used by: RLS policies, has_role() function

System 2 — Custom roles (used by frontend tools)

• - Table: public.custom_roles (column: id as UUID, name as text)
• - Values: 'Corretor' (uuid 778cdd99-...), 'ADM' (9bc6f80c-...), 'Gestor' (4a033306-...), 'Pré-Venda' (f91d44e1-...)
• - Used by: tool_role_access (maps broker_link_id → custom_role_id UUID)

has_role() function

`sql

SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role = _role::text)

`

Checks public.user_roles.role (text), NOT auth.profiles.

Debug Query

`sql

SELECT p.email, p.custom_role_id, cr.name as role_name,

ur.role as text_role

FROM public.profiles p

LEFT JOIN public.custom_roles cr ON cr.id = p.custom_role_id

LEFT JOIN public.user_roles ur ON ur.user_id = p.user_id

LIMIT 10;

`

Common Bug: Wrong custom_role_id

User has custom_role_id pointing to a role that doesn't have tool access in tool_role_access.

Fix: Update custom_role_id to match a role that HAS the tools mapped:

`sql

UPDATE public.profiles

SET custom_role_id = '9bc6f80c-8e01-4ec0-9f24-a7a3a4c482dd' -- ADM

WHERE user_id = 'auth-users-uuid-here';

`

Fix Applied (2026-05-21)

• - Admin user tecrochasales@gmail.com had custom_role_id = '4a033306-...' (Gestor) — updated to '9bc6f80c-...' (ADM)
• - auth.profiles is EMPTY and irrelevant — do NOT try to populate it
• - public.user_roles and public.custom_roles are the two systems to manage

Fix Applied (2026-06-15) — REST API 404 on /rest/v1/*

• - Kong was removed from the stack (container deploy-vps-kong-1 no longer exists)
• - nginx now proxies directly to PostgREST on 127.0.0.1:3002
• - Bug: proxy_pass http://127.0.0.1:3002; (no trailing /) passes the FULL path including /rest/v1/ to PostgREST, which doesn't recognize it → 404
• - Fix: Change to proxy_pass http://127.0.0.1:3002/; (WITH trailing slash) so nginx strips the matched prefix before forwarding
• - Same fix applies to all locations: /rest/v1/, /storage/v1/, /functions/v1/, /auth/v1/
• - Test: curl -s -o /dev/null -w "%{http_code}" "https://rochasalesseguros.com.br/rest/v1/cms_content?select=id&limit=1" -H "apikey: "
• - Always backup config before changing: cp /etc/nginx/sites-enabled/rochasales.conf /etc/nginx/sites-enabled/rochasales.conf.bak.$(date +%Y%m%d%H%M%S)

Fix Applied (2026-06-15) — Auth 404 on /auth/v1/*

• - Same trailing-slash bug affects /auth/v1/ → GoTrue on 172.23.0.3:9999
• - Fix: proxy_pass http://172.23.0.3:9999/; (WITH trailing slash)
• - Test auth: curl -s -X POST "https://rochasalesseguros.com.br/auth/v1/token?grant_type=password" -H "apikey: " -H "Content-Type: application/json" -d '{"email":"admin@rochasalesseguros.com.br","password":"221109"}'

Fix Applied (2026-06-15) — Bcrypt Hash Incompatibility

• - GoTrue in this stack expects $2b$ bcrypt hashes (not $2a$)
• - If login returns "invalid_grant" via API but credentials are correct, hash may be old format
• - To reset a user's password hash directly in the DB (bypassing email), use Python on the VPS to avoid shell escaping issues:

`python

# On VPS: python3 << 'PYEOF'

import psycopg2, bcrypt

# Generate new hash

new_hash = bcrypt.hashpw(b'221109', bcrypt.gensalt(rounds=10)).decode()

# Update via local port (5433 = deploy-vps-db-1 exposed port)

conn = psycopg2.connect(host='127.0.0.1', port=5433, user='postgres', database='postgres')

cur = conn.cursor()

cur.execute('UPDATE auth.users SET encrypted_password = %s WHERE email = %s RETURNING email', (new_hash, 'admin@rochasalesseguros.com.br'))

conn.commit()

# PYEOF

`

• - Test via GoTrue CLI inside container: docker exec deploy-vps-auth-1 curl -s -X POST http://localhost:9999/token?grant_type=password ...