name: doccorretor-drive-migration

description: One-time migration of documents from Google Drive to DocCorretor — copy files to VPS storage, insert DB records via Edge Function. Includes 502/cloudflared tunnel fix.

tags:

- doccorretor

- google-drive

- migration

- supabase

DocCorretor Google Drive Migration (Executed Jun 2026)

⚠️ Migration Scope (Jun 2026 Update)

The initial migration covered ~138 documents (12 clients from 2023/08). The full Drive has 396 client folders across 4 years (2023–2026). Many folders were never migrated.

Drive year folder IDs (root for each year):

`

2023: 1nObuuq8AhcUo-O96UdhaTBbrjaRzI4lP

2024: 1CcabJC1HF1V-tVUlq9IjkpCLNYRMN_l5

2025: 17-KPk4ttAvtmz2hCXarIiW4O0MPh4rGZ

2026: 1rlhX77Rq5F40qctMFMP7k2AxD5-U3rSS

`

2024 month folder IDs:

`

02/2024: 1x_cZiLZMauYi1xOL5BsvMEruoqkfkyHj

03/2024: 1RtjJGHcrnbO25sHjuhJCF7Xh1x4Rgwyu

04/2024: 159STuwNcVd-mduJRejWFTWgeSOiIKrMZ

05/2024: 1mH9NKgzaljcmzZT812iPVkWfqms2M-zh

06/2024: 1nuFUpQ9z4UEr23J2gL6U339hoQ8WW3EV

07/2024: 1uadYZGkAKXcseD9bOWlG11ND43lOlpEO

08/2024: 1r2jnjUNJf_zSEaqdJNx21Uwp9jHOJ7Ta

09/2024: 1tvu4Sx74Yz15BGII7HV-oY9bPhgURJfW

10/2024: 1i7hfyBpcizqneqM3zdPMXeDtK5j4Olk7

11/2024: 1N7LVycNNROxCdchb66bP3DYFyAOweXUu

12/2024: 142tIHP8ja86ISJIAnA2wxNM-BfPKYph6

`

2026 month folder IDs:

`

03/2026: 1KzyVHG3vOfZ9_5zgz7x4HL_vdtvF3-Bw

04/2026: 1u2Hhlbg05mr7jJX3_oOBnH9KBOxG9gmu

05/2026: 1cJ-qMSVKsM_vwDXhUczzBU685XR99rrs

06/2026: 1Y8ghviSlw5-h4fARIy45Xgz0y7Seta2z

`

⚠️ rclone Token Expired — Use gdown Locally Instead

The VPS rclone config (/root/.config/rclone/rclone.conf) has an expired token with no refresh token.

VPS has no pip — cannot install gdown there.

Solution: Use gdown locally (hermes-agent venv):

`bash

/root/.hermes/hermes-agent/venv/bin/gdown {folder_id} -O /tmp/dest/ --folder

`

Available at: /root/.hermes/hermes-agent/venv/bin/gdown (Python 3.11.15)

Then SCP to VPS, fix permissions, insert via Management API (see below).

Completed: 138 documents migrated across 12 clients (Jun 2026)

Files copied to /var/www/documentos/uploads/5a4ebb41-9e54-47b3-86eb-1c18bc01ad5a/{client_id}/.

12 client folders created in client_folders table.

Architecture (Critical Finding)

Edge Functions run on Supabase's servers, NOT on the VPS. They cannot access local VPS files.

Two-step approach required:

1. Python script on VPS → copies files from /tmp/drive_docs/ to storage

2. Edge Function on Supabase → inserts DB records (uses service_role, bypasses RLS)

Schema (Supabase Cloud — project dauftiqcvgaydddoxqhh)

client_folders — FLAT structure (no is_folder/parent_id)

`

id | client_id | name | created_by | created_at

`

• - client_id → clients.id (nullable)
• - created_by → profiles.id

documents

`

id | client_id | folder_id | title | original_name | stored_name |

storage_path | mime_type | size_bytes | category | uploaded_by | created_at

`

• - uploaded_by → profiles.id (NOT profiles.user_id)
• - category is TEXT — accepts any string, no enum constraint

Critical FK Constraint

documents.uploaded_by must reference profiles.id, NOT profiles.user_id.

Davi's profile (ded98056...) FAILS the FK constraint — do not use.

Use Vinicius profile: 5a4ebb41-9e54-47b3-86eb-1c18bc01ad5a

Two-Step Migration

Step 1 — Copy Files to VPS Storage (Python on VPS)

`python

import shutil

from pathlib import Path

ADMIN_ID = "5a4ebb41-9e54-47b3-86eb-1c18bc01ad5a"

STORAGE_BASE = Path("/var/www/documentos/uploads")

BASE_DIR = Path("/tmp/drive_docs")

CLIENT_MAP = {

'alessandra (79) 9864-1055': '1c1fc73b-504f-42ec-af60-ad881ced018e',

'amil - cinthia - (96) 98426-6340': '548196d4-45cf-4731-9aba-863ad4da063a',

'ANDREZA SIMÕES MOREAU': 'fd96f944-838b-4601-845a-38a8633e876d',

'Ariani': 'b4383736-84c5-47c9-93f5-295c5d2ac22f',

'Bruna will': '9e948184-e2d1-4abe-99a2-beaaad0a361a',

# NBSP (\xa0) between "11" and "94878" — must be literal in Python string:

'Bruno felipe 11\xa094878-6157': 'b1d272c1-2126-449d-8cd7-da52d8a5684e',

'CASSIUS CLEY A. BEZERRA - BRADESCO ( Vinicius)': '19f83fa3-467f-4066-a957-613ea9729bca',

'DANIEL BRUNO BAETA- BRADESCO ( Michael)': 'f7780a79-896d-48d2-b277-27b922ae0a1b',

# NBSP (\xa0) before "(" in Michael):

'DAYANE DE SOUZA OLIVEIRA ( Michael)': '8d5c5fd2-bcc1-4080-b658-b7d6797c4638',

'Day contadora - cuidar.me': '8d5c5fd2-bcc1-4080-b658-b7d6797c4638',

'AUGUSTO THOEODORO DA SILVA- NOTREDAME ( Vinicius)': '01d4d8cb-720f-4c73-96ac-a36871e23488',

'BRUNO FELIPE OLIV. BARBOSA- BRADESCO ( Vinicius)': 'b1d272c1-2126-449d-8cd7-da52d8a5684e',

}

for folder_name, client_id in CLIENT_MAP.items():

src = BASE_DIR / "documentos clientes" / "2023" / "08_2023" / folder_name

dest = STORAGE_BASE / ADMIN_ID / client_id

dest.mkdir(parents=True, exist_ok=True)

for fpath in src.iterdir():

if fpath.is_file() and fpath.name != 'files':

shutil.copy2(fpath, dest / fpath.name)

IMPORTANT: after copying, fix permissions (files created as root get 600 → nginx 403)

os.system(f"chmod -R o+r {STORAGE_BASE / ADMIN_ID}")

IMPORTANT: NBSP folder names — list Drive folders with repr() to detect:

for name in os.listdir(drive_base): print(repr(name))

The actual name for "Bruno felipe 11" is "Bruno felipe 11\xa094878-6157"

Include \xa0 literally in Python string literals when matching.

`

Step 2 — Insert DB Records via Management API (NOT Edge Function)

⚠️ service_role key is broken — returns 401 on both REST API and Edge Functions.

Use Management API (PAT) instead, which works reliably.

Step 1 — Copy Files to VPS Storage (Python on VPS)

`python

import shutil

from pathlib import Path

ADMIN_ID = "5a4ebb41-9e54-47b3-86eb-1c18bc01ad5a"

STORAGE_BASE = Path("/var/www/documentos/uploads")

BASE_DIR = Path("/tmp/drive_docs")

for folder_name, client_id in CLIENT_MAP.items():

src = BASE_DIR / "documentos clientes" / "2023" / "08_2023" / folder_name

dest = STORAGE_BASE / ADMIN_ID / client_id

dest.mkdir(parents=True, exist_ok=True)

for fpath in src.iterdir():

if fpath.is_file() and fpath.name != 'files':

shutil.copy2(fpath, dest / fpath.name)

IMPORTANT: after copying, fix permissions (nginx runs as ubuntu, not www-data!)

Use chown ubuntu:ubuntu + chmod 644 (NOT chmod -R o+r)

import subprocess

subprocess.run([

'sshpass', '-p', 'Marcia19671951@', 'ssh', 'root@31.97.243.106',

f'chown ubuntu:ubuntu {STORAGE_BASE / ADMIN_ID}/{client_id}/* && '

f'chmod 644 {STORAGE_BASE / ADMIN_ID}/{client_id}/*'

], check=True)

`

Step 2 — Insert DB Records via Management API

The Management API (PAT) bypasses RLS and works even when service_role key returns 401.

Endpoint: POST https://api.supabase.com/v1/projects/{ref}/database/query

`python

import urllib.request, json

PAT = "sbp_REDACTED" # Management API PAT

REF = "dauftiqcvgaydddoxqhh"

def insert_documents_management_api(documents: list) -> dict:

"""Insert documents via Supabase Management API (bypasses RLS)."""

values = ", ".join([

f"('{d['id']}', '{d['client_id']}', '{d['folder_id']}', "

f"'{d['title'].replace(\"'\", \"''\")}', "

f"'{d['original_name'].replace(\"'\", \"''\")}', "

f"'{d['stored_name'].replace(\"'\", \"''\")}', "

f"'{d['storage_path'].replace(\"'\", \"''\")}', "

f"'{d['mime_type']}', {d['size_bytes']}, "

f"'{d['category']}', '{d['uploaded_by']}')"

for d in documents

])

sql = f"""

INSERT INTO public.documents

(id, client_id, folder_id, title, original_name, stored_name, storage_path,

mime_type, size_bytes, category, uploaded_by)

VALUES {values}

"""

payload = json.dumps({"query": sql}).encode()

req = urllib.request.Request(

f"https://api.supabase.com/v1/projects/{REF}/database/query",

data=payload,

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

method="POST"

)

with urllib.request.urlopen(req, timeout=60) as resp:

return json.loads(resp.read())

`

Management API notes:

• - No prepared statements — use literal values in SQL (no $1, $2 placeholders)
• - Single quotes in text fields must be escaped as '' (double single quote)
• - Batch insert: up to ~100 records per request (keep SQL under 64KB)
• - If HTTP 400: check SQL syntax (most common: unescaped quotes in filenames)

⚠️ Filename encoding: Files with special chars (ç, ã, é, etc.) are downloaded with ASCII names by gdown.

The stored_name in DB must match the actual filename on disk exactly.

Example: "comprovante de endereço.jpg" → stored as "comprovante de endereco.jpg" (ASCII).

⚠️ Skip incomplete downloads: Files ending in .part are 0-byte incomplete gdown downloads — skip them.

⚠️ Edge Function (deprecated for this use case):

The migrate-drive Edge Function (/var/www/documentos2/app/supabase/functions/migrate-drive/index.ts)

requires a working service_role key, which is currently broken. Use Management API instead.

Document Type Classification

Common Errors

502 Fix — Cloudflared Tunnel

Site: documentos.rochasalesseguros.com.br | App port: 3010

`bash

Check current port mapping

ss -tlnp | grep -E "3010|53682"

ps aux | grep cloudflared

Kill wrong tunnel, restart on 3010

sudo kill

nohup sudo cloudflared tunnel --url http://localhost:3010 \

> /var/log/cloudflared_documentos.log 2>&1 &

`

Drive Structure (Jun 2026 Full Analysis)

`

/documentos clientes/2023/08_2023/ ← only month present

└── 12 client folders

├── Alessandra (12 real docs)

├── Amil (9)

├── Andreza (8)

├── Ariani (13 real docs + 11 HTML/CSS export artifacts)

├── Augusto (9)

├── Bruna will (13)

├── Bruno felipe 11 (7) ← NBSP between "11" and "94878"

├── Bruno Felipe Oliv. (12)

├── Cassius (23)

├── Daniel (22 + DAIANE/ subfolder with 10 more)

├── Dayane (6)

└── Day Contadora (4)

`

Total: 138 real documents (149 total includes 11 HTML/CSS support files from Google Drive export).

Critical: NBSP in Folder Names

Google Drive folders can contain non-breaking spaces (NBSP, \u00a0) in their names.

Example: "Bruno felipe 11 94878-6157" actually contains 11\u00a094878-6157.

Python's os.listdir() returns the raw name with NBSP, but string literals with regular spaces won't match.

Detection: ls "/path/" | cat -A shows NBSP as \xc2\xa0 or M- in the output.

Fix: Use folder_name.encode('utf-8') or check with repr(name) to see NBSP bytes.

In Python, include the NBSP explicitly: 'Bruno felipe 11\xa094878-6157'.

Critical: Shared client_id — Files Get Mixed

Some Drive folders share the same client_id in the database. Their files are stored in the same directory on VPS:

Keyword	Category
---------	----------
RG, identidade	RG
CNH, habilita	CNH
CPF	CPF
comprovante, endereço	COMPROVANTE_RESIDENCIA
contrato	CONTRATO
cnpj, mei, ccme	CNPJ
carteirinha, plano	CARTEIRINHA
perman	CARTA_PERMANENCIA
casamento	CERTIDAO_CASAMENTO
nascimento	CERTIDAO_NASCIMENTO
fatura, boleto	FATURA
estudo, proposta	ESTUDO
None of above	OUTROS
Error	Cause	Fix
-------	-------	-----
FOREIGN KEY constraint failed on uploaded_by	Used admin profile or user_id	Use Vinicius profiles.id: 5a4ebb41-9e54-47b3-86eb-1c18bc01ad5a
is_folder column not found	Schema has no is_folder/parent_id	client_folders is flat
Edge Function times out	Too many sequential DB calls	Batch inserts (20 records/batch)
502 Bad Gateway on site	Cloudflared pointing to wrong port	Restart tunnel on port 3010
Drive Folder	client_id	Storage Dir
---	---	---
Bruno felipe 11 94878-6157	b1d272c1-...	/b1d272c1/
BRUNO FELIPE OLIV. BARBOSA...	b1d272c1-...	/b1d272c1/ (SAME)
DAYANE DE SOUZA OLIVEIRA...	8d5c5fd2-...	/8d5c5fd2/
Day contadora - cuidar.me	8d5c5fd2-...	/8d5c5fd2/ (SAME)

Effect: Storage dir shows combined count (e.g., 19 files for both Bruno folders combined).

This is correct behavior — both Drive folders belong to the same client in the DB.

When auditing migration completeness, compare by normalized names (see below).

Google Drive HTML Export Artifacts

When Google Drive exports a folder as HTML, it creates support files inside each folder:

• - bootstrap.min.css, all.css, print.css — CSS files
• - scripts.js.download, scripts.js — JavaScript
• - These have extensions .css, .js, .gif, .png, .svg etc.

These are NOT real documents. They must be excluded from migration.

Filter rule: skip files with extensions .css, .js, .js.download, .gif, .png, .svg, .ico, .woff, .woff2, .ttf.

Normalization-Based Comparison

When auditing migrated files, compare using normalized names:

`python

import re

TS_RE = re.compile(r'^\d{13}-(.+)$') # strip timestamp prefix from stored names

def strip_ts(fname):

m = TS_RE.match(fname)

return m.group(1) if m else fname

def normalize(name):

"""Lowercase + spaces to underscores — for cross-source comparison"""

return name.lower().replace(' ', '_').replace('\xa0', '_')

Compare Drive original names vs Storage stored names (stripped of timestamp)

drive_norms = {normalize(f) for f in drive_files}

stor_norms = {normalize(strip_ts(f)) for f in storage_files}

matches = len(drive_norms & stor_norms)

`

Drive File Discovery Notes

• - Client folder names are exact Drive folder names (include phone numbers, broker tags)
• - The files subfolder inside client folders should be skipped (not a real document)
• - Use os.walk() with dirs[:] = [d for d in dirs if d != 'files'] to exclude _files folders
• - After ANY root file copy: always run chmod -R o+r /var/www/documentos/uploads/ to fix nginx 403

Post-Migration Fixes (Jun 2026 second session)

403 Forbidden — File Permission Fix (CRITICAL)

nginx on VPS runs as user ubuntu, NOT www-data. Files with root:root + 600 cause 403.

Required fix after EVERY SCP upload:

`bash

Fix ownership and permissions for all uploaded files

sshpass -p 'Marcia19671951@' ssh root@31.97.243.106 \

"chown ubuntu:ubuntu /var/www/documentos/uploads/{user_id}/{client_id}/* && \

chmod 644 /var/www/documentos/uploads/{user_id}/{client_id}/*"

`

Do NOT use chmod -R o+r — that's for www-data. Use chown ubuntu:ubuntu + chmod 644.

Subdirectory Files — Discovery Issue

Initial script used maxdepth 1 and missed files in subdirectories.

Example: DANIEL BRUNO BAETA/DAIANE/ contained 10 real documents (CERTIFICADO MEI,

COMP RESIDENCIA, RG DAVI, etc.) that were invisible to the shallow scan.

Fix: Use os.walk() without depth limits when discovering files, then handle

_files/ folder exclusion by checking fpath.name != 'files' for each file.

NBSP Folder Names — Silent Match Failure

Python os.listdir() returns raw folder names with NBSP (\u00a0), but string literals

with regular spaces won't match. The script silently skipped Bruno felipe 11 and

DAYANE DE SOUZA OLIVEIRA because the names didn't match.

Fix: Detect NBSP with repr(name) or cat -A. Use '\xa0' in Python literals.

Timestamp Prefix in Titles — DB Cleanup

Migration script stored files with timestamp-name.jpg naming AND set title to the

same timestamp-prefixed string. Users saw ugly names like 1781035429580-alessandra_-_RG.jpeg.

Fix: Use Edge Function fix_names action (added to migrate-drive):

`python

result = call_edge({"action": "fix_names", "ids_and_names": [

{"id": doc_id, "new_title": original_name, "new_original_name": original_name}

for doc in docs_with_timestamp_prefix

]})

`

Duplicate Records — 152 Duplicates Removed

Insertion script didn't check for existing records. Running migration twice created

152 duplicate DB records (same stored_name + client_id).

Fix: Added dedupe action to Edge Function. Run deduplication after migration:

`python

Delete duplicate records (keep oldest by id)

result = call_edge({"action": "dedupe", "ids": [list_of_duplicate_ids]})

`

storage_path Empty String — Documents Won't Open

Edge Function was inserting documents with storage_path: "" instead of the actual

file path. Frontend constructs URLs as site.com/uploads/{uploaded_by}/{storage_path},

so storage_path="" caused all document clicks to fail.

Fix: Added fix_storage_paths action to Edge Function that PATCHes all 158 documents

to set storage_path = stored_name.

HTML/CSS Artifacts — 11 Files Removed from Storage and DB

Google Drive's HTML export creates bootstrap.min.css, scripts.js.download, all.css

etc. inside each folder. These were copied as "documents" and inserted in DB.

Fix: Delete files with .css, .js, .js.download, .gif, .png extensions

from storage and their corresponding DB records.

Old Test Records — 18 Orphaned Records Deleted

7 records with client_id = b4af5495... (test records) and 11 records with

client_id = acc3a249... existed in DB but had no corresponding Drive files.

Fix: Identified by client_id not in the 12 Drive client mapping, deleted via

dedupe action.

After Migration

Final state (Jun 2026): 138 documents = 138 storage files = 138 DB records — perfect 1:1:1 match.

Some clients share storage directories (same client_id):

• - Bruno felipe 11 + Bruno Felipe Oliv. → both in /b1d272c1/ (19 combined files, 12+7 unique)
• - Dayane + Day Contadora → both in /8d5c5fd2/ (10 combined files, 6+4 unique)

Some clients share storage directories (same client_id):

• - Bruno felipe 11 + Bruno Felipe Oliv. → both in /b1d272c1/ (19 combined files, 12+7 unique)
• - Dayane + Day Contadora → both in /8d5c5fd2/ (10 combined files, 6+4 unique)

This is correct — both Drive folders belong to the same client in the DB.

⚠️ Outstanding work (as of Jun 2026): 47 Drive client folders from 2023/08, 2024/08, and 2026/03-06 were identified in Drive but NOT migrated. These clients were created in DB (via Management API create_clients_bulk equivalent), but their Drive files were never downloaded/uploaded. Full Drive structure (396 folders) was mapped — see /tmp/drive_2023.json, /tmp/drive_2024.json, /tmp/drive_2025.json, /tmp/drive_2026.json for the complete client list with folder IDs.