Keep a Supabase knowledge base in sync with your FAQ sheet

import hashlib
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, state, report_fetched

SHEET_ID = "your-spreadsheet-id"
SUPABASE = "https://your-project.supabase.co/rest/v1"

def sb(method, path, **kw):
    key = credentials.secret("SUPABASE_KEY")
    r = http.request(method, f"{SUPABASE}/{path}", **kw, headers={"apikey": key, "Authorization": f"Bearer {key}", "Prefer": "return=representation,resolution=merge-duplicates"})
    return r.raise_for_status() or (r.json() if r.content else None)

@on_trigger
def run(event):
    rows = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values().get(spreadsheetId=SHEET_ID, range="FAQ!A2:B").execute().get("values", [])
    seen = state.get("hashes") or {}
    changed = []
    for i, r in enumerate(rows, start=2):
        if len(r) == 2 and seen.get(str(i)) != (h := hashlib.sha256("\n".join(r).encode()).hexdigest()):
            changed.append((i, r, h))
    report_fetched(len(changed))
    if not changed:
        return {"summary": "Nothing new"}
    texts = [f"Q: {q}\nA: {a}" for _, (q, a), _ in changed]
    emb = http.post("https://api.openai.com/v1/embeddings", headers={"Authorization": "Bearer " + credentials.secret("OPENAI_API_KEY")},
                    json={"model": "text-embedding-3-small", "input": texts}).json()["data"]
    sb("POST", "documents?on_conflict=id", json=[{"id": f"faq-{i}", "content": t, "embedding": e["embedding"]} for (i, _, _), t, e in zip(changed, texts, emb)])
    seen.update({str(i): h for i, _, h in changed})
    state.set("hashes", seen)
    return {"synced": len(changed)}