📄 SKILL.md

← Vault

name: google-drive-to-supabase-migration

description: Migrate files from Google Drive shared folder to Supabase Cloud storage + database via Edge Functions (bypasses RLS)

triggers: ["migrate drive documents", "google drive to supabase", "RLS blocking insert"]

Google Drive → Supabase Cloud Migration

Migrate documents from Google Drive shared folders to DocCorretor (Supabase Cloud) storage and database.

Context

DocCorretor is a TanStack Start app on VPS with Supabase Cloud backend. Documents are stored as files on VPS (/var/www/documentos/uploads/) and referenced in documents table. The site is documentos.rochasalesseguros.com.br.

Key Challenge: RLS Blocking

Supabase Cloud has Row Level Security. Anon key cannot INSERT into documents, client_folders tables.

Two reliable options to bypass RLS:

1. Management API (PAT) — recommended. Works even when service_role key returns 401.

POST https://api.supabase.com/v1/projects/{ref}/database/query with Authorization: Bearer {PAT}

2. Edge Functions — requires working service_role key (currently broken — returns 401).

Migration Pattern

1. Discover files via Playwright

`python

from playwright.sync_api import sync_playwright

def get_file_list(page, folder_url: str) -> list:

page.goto(folder_url, timeout=30000)

page.wait_for_timeout(5000)

for _ in range(5):

page.evaluate('window.scrollTo(0, document.body.scrollHeight)')

page.wait_for_timeout(1000)

return page.evaluate('''() => {

const results = [];

const seen = new Set();

document.querySelectorAll("tr[data-id]").forEach(tr => {

const id = tr.getAttribute("data-id");

const ariaLabel = tr.getAttribute("aria-label") || "";

if (ariaLabel.includes("Pasta compartilhada")) return;

if (id && !id.includes("folders") && id.length > 20 && !seen.has(id)) {

seen.add(id);

const nameSpan = tr.querySelector("span[title]");

const name = nameSpan ? nameSpan.getAttribute("title") : id;

if (name && name.length > 1) results.push({name, file_id: id});

}

});

return results;

}''')

`

2. Download files preserving real names

CRITICAL: Google Drive returns file content with filename in Content-Disposition header (e.g., attachment; filename="COMP RESIDENCIA.pdf"). The file ID from Drive UI is NOT the filename. Use curl -J -O to extract the real name:

`python

import subprocess, os, shutil

def download_file(file_id: str, original_name: str, temp_dir: str, timeout: int = 60) -> tuple:

"""Uses -J -O to extract filename from Content-Disposition header.

Returns (absolute_path, real_name) or ("", "") on failure."""

url = f"https://drive.google.com/uc?export=download&id={file_id}"

abs_temp_dir = os.path.abspath(temp_dir)

original_cwd = os.getcwd()

try:

os.chdir(abs_temp_dir)

subprocess.run(

['curl', '-s', '-L', '-J', '-O', '-m', str(timeout), url],

capture_output=True, timeout=timeout + 10

)

finally:

os.chdir(original_cwd)

# curl -J saves with the Content-Disposition name

for fname in os.listdir(abs_temp_dir):

fpath = os.path.join(abs_temp_dir, fname)

if os.path.isfile(fpath) and os.path.getsize(fpath) > 5000:

return (fpath, fname)

return ("", "")

`

Do NOT use -o with -J — they conflict. Use cwd= in subprocess or os.chdir().

3. Download files + Bypass RLS with Management API

Download from Google Drive (use gdown locally — VPS has no pip):

`bash

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

`

Management API INSERT (bypasses RLS even when service_role key is broken):

`python

import urllib.request, json

PAT = "sbp_REDACTED"

REF = "dauftiqcvgaydddoxqhh"

def insert_documents(documents: list) -> dict:

"""Insert documents via Supabase Management API. Batches of ~100."""

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}

"""

req = urllib.request.Request(

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

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

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())

`

Notes: