d3ro-voice/docs/map/09-supabase-backend.md
Yun Chan 7fe49c437e chore: land in-progress workspace changes before 1.16.2
The tree carried uncommitted work from several areas (web push, mobile
screens, admin charts, test-hygiene cleanup, CI and map docs). It is landed
as one commit so the release pipeline can start from a clean tree; the
desktop transcript-correction behaviour shipped in the previous commit and
is unchanged here.
2026-11-05 21:58:44 +09:00

8.7 KiB

09 — Backend: Supabase + Cloudflare Map

Surfaces: server/supabase (Postgres + Deno Edge Functions), server/cloudflare-worker (edge gateway) Role: canonical product data, auth, RLS, storage, AI proxies, billing, notifications


1. Supabase project (server/supabase/)

  • config.toml — project_id d3ro-voice; local ports API 55321 / DB 55322 / Studio 55323 / local SMTP UI 55324; DB major v17; storage 50 MiB; auth site_url localhost:5173, redirects include https://d3ro.chanpaca.net and d3ro-voice://auth-callback; external providers Google/GitHub/Apple; per-function verify_jwt settings; analytics off. The mail section uses the Supabase CLI 3.x [local_smtp] name rather than the removed [inbucket] alias.
  • seed.sql, migrations/ (73 source files; last confirmed production state was 20260927000036), functions/ (24 source functions; the 23 functions present on 2026-09-27 were redeployed that day), functions/_shared/, tests/, deno.json.

Note: root supabase/ contains only empty scaffolding (.branches/, snippets/). The real project lives under server/supabase/.

1.1 Migrations (73) — thematic groups

Theme Examples
Core schema + RLS + auth triggers initial tables, profiles, triggers
Storage buckets audio, meeting documents, exports
Teams team invites, membership, roles, team_activities feed (20260913000033)
Cross-device sync 20260927000036: preset prompt RPCs sync_set_builtin_instruction_prompt_v1 / sync_list_builtin_instructions_v1 + default SSOT builtin_instruction_default_prompt_v1. 20260927000035: knowledge_documents tombstones/cursor/realtime + pg_cron job prune-sync-tombstones (daily 03:17 UTC). 20260927000034: sync_tombstones (AFTER DELETE triggers on 8 synced tables, owner-read RLS, realtime, prune_sync_tombstones_v1), history.revision bump on content change, meeting_memos.updated_at, (user_id, updated_at, id) cursor indexes, client-keyed sync_upsert/delete_user_template_v1, realtime for devices/memo_tags/custom_instructions/user_templates, and export_account_portability projected back onto v1 archive keys
Knowledge / RAG knowledge_documents, knowledge_chunks, pgvector
Notifications / push push tokens, durable outbox
Billing Payple, subscriptions, payment provider events/operations ('stripe' provider values kept only for historical rows)
Admin admin roles, audit log, atomic admin RPCs
Mobile platform mobile platform/monetization/runtime integrity
Commands atomic command reorder
Devices device registration + revocation
Content reporting report reasons, generation receipts
Meetings meeting documents
STT quota atomic quota reservations (00026)
Ads ad reward receipt replay protection (00028)

Forgejo CI runs check:migrations:test and check:migrations. These gates validate filename format, unique and monotonically increasing timestamps, and non-empty files; they do not apply SQL to a local/shadow database or compare remote migration state. Separately on 2026-11-04, an isolated d3ro-voice-wave4-runtime stack applied all 73 migrations from empty and after db reset; db diff --use-pg-delta --strict-coverage replayed the same history into a shadow DB and returned no schema changes. This local proof does not establish linked/production drift.

1.2 Edge Functions (24)

Function Purpose
stt-proxy User STT gateway: auth, atomic quota reservation/refund, provider fallback, fail-closed
llm-proxy LLM gateway (Claude/OpenAI)
realtime-token OpenAI Realtime ephemeral token (tier-gated, session quota)
generate-meeting-document AI document generation (minutes/report/idea-note/mindmap)
embed-chunks / search-knowledge RAG embeddings + semantic search. knowledge_embedding budgets are read from generated PLAN_QUOTA, whose source is packages/core/src/plan-catalog.ts
audio-purge Service-only audio object purge worker invoked by pg_cron. config.toml sets verify_jwt = false so modern service keys reach the handler; the handler then authorizes the exact service credential before queue/storage work. Isolated local runtime proof on 2026-11-04 covered missing/non-service key 401, service-key 200, direct drain, and pg_cron → Vault → pg_net → Edge → Storage deletion
content-report AI content reporting
team-invite / team-accept Team invitations
send-push Push delivery
account-delete Account deletion cascade + provider unlink
admin-users / admin-subscriptions / admin-payments / admin-audit-log Admin operations
billing-catalog Server pricing catalog. Prices/quotas read PLAN_PRICE_KRW/PLAN_QUOTA from functions/_shared/core-contract.generated.ts, generated from packages/core/src/plan-catalog.ts by scripts/ci/sync-core-contract.mjs (npm run contract:check fails on drift; added 2026-09-26, Wave 3, 88f24d8)
payple-checkout / payple-manage / payple-renew / payple-webhook Payple billing (Korea)
iap-verify Google Play / App Store purchase verification
admob-ssv AdMob server-side verification + reward ledger
google-play-rtdn Play Real-time Developer Notifications

Shared contracts in functions/_shared/: admin, auth, audit, cors, quota, embedding-quota, payple, push, llm, stt, team, generation-receipt, google-play, pubsub, generative-ai-safety (+ *.test.ts).

1.3 Tests (server/supabase/tests/)

Integration/E2E: content-report red e2e, content-reporting (ps1/sql), mobile platform/recording/reward-race, payment provider, mobile release preflight, public mobile runtime, push claim/outbox, team push security, STT quota.


2. Cloudflare worker (server/cloudflare-worker/)

  • wrangler.toml — worker d3ro-voice-api, compat 2024-04-01, BACKEND_ORIGIN=http://192.168.0.39:5050, optional custom domain route (commented).
  • src/index.ts — CORS preflight, /worker-health, forwards to backend origin with X-Forwarded-* / X-D3RO-Edge-Proxy headers.

Cloudflare Tunnel kd-nas maps public hostnames to NAS services.


3. Data model (product tables, high level)

Canonical product data lives in Supabase Postgres with RLS:

  • Identity: auth.users + profiles, roles/claims, identity links.
  • Content: history, meetings, meeting_memos, meeting_documents, memos, dictionary, commands/instructions, templates.
  • Knowledge: knowledge_documents, knowledge_chunks (pgvector).
  • Teams: teams, members, invites, activity feed (team_activities, RPC-only writes, realtime-enabled).
  • Delivery: devices (mobile + desktop windows/macos), push_tokens, push outbox.
  • Sync: sync_tombstones — deletion log the desktop mirror (and any offline client) reads by cursor.
  • Monetization: subscriptions, payment_provider_events/operations, ad_reward_claims, IAP receipts, generation receipts.
  • Ops: audit_log + admin operation records.
  • Portability: portable_exports (+ storage).

Shared TS types for these live in packages/api-client (SSOT).


4. Backend status summary

  • Auth (email + Google/GitHub/Apple), RLS, storage, realtime: implemented; production Auth + Google provider entry verified GREEN; GitHub/Apple provider secrets and mobile consent callback pending (external).
  • STT/LLM proxies: implemented and fail-closed (no synthetic transcripts); atomic quota reservations verified with 20-way concurrency.
  • Billing (Payple + IAP verify + webhooks/RTDN; Stripe removed 2026-09-26): implemented; live provider end-to-end and Payple webhook signature verification pending.
  • Ads (AdMob SSV, rewarded ledger, replay protection): implemented (Edge v13 ACTIVE); production AdMob serving blocked externally (review/serving limits/store link/payment profile).
  • Push: Supabase owns tokens/devices/outbox/retries. Transports implemented for FCM, Web Push (VAPID + RFC 8291), and APNs (.p8 token); the Cloudflare Worker cron drains the outbox every minute. Android still requires FCM at the device. Details: docs/deployment/push-transport-without-firebase.md.
  • Content safety: generation receipts + content-report implemented.
  • Data portability (account-delete, export/restore): implemented.
  • Audio purge source wiring, unit tests, and an isolated local pg_cron/Vault invocation are verified: the fixture queue, audio_files row, and Storage object each changed from one to zero and net._http_response recorded HTTP 200. Deployment and the production schedule/Vault/object path remain unverified.

External gates are enumerated in docs/v3/MOBILE_APP_COMPLETION_SSOT.md §0/§6 and mirrored in 11-gap-backlog.md (EXT-*, BE-*).