# Supabase BYOD (Bring Your Own Database) — Design Spec

**Date:** 2026-06-04
**Status:** Approved (Approach A)

## Problem

Supabase today is a **platform-owned backend** with two admin-selected modes:
- **Cloud** — one shared Supabase project for the whole install; each project gets a `proj_<uuid>` schema in it.
- **Self-hosted** — a per-user Docker stack the Go builder provisions on demand (`/api/supabase/{provision,suspend,resume,teardown}`), driven by `autosetup.sh` (Docker install + compose).

This carries heavy machinery: a provisioner abstraction with a full `ensure/suspend/resume/reclaim` lifecycle, per-user lifecycle columns, an admin Database settings tab, builder-side Docker orchestration, and Docker in the deploy script. It also means the platform owns/operates databases on users' behalf.

## Goal

Pivot to **BYOD**: the user supplies a ready Supabase database; the platform never provisions, suspends, reclaims, or owns one. A user manages a **library of their own Supabase connections** and attaches one to a project **at creation** so it's wired from the first build. Completely remove the global/shared backend, the self-hosted Docker path, and all their traces (code, deploy script, docs).

## Decisions (locked)

| Decision | Choice |
| --- | --- |
| Data model | Per-user `supabase_connections` table; `projects.supabase_connection_id` references it (null = no DB). Replaces the per-user `supabase_config` blob. |
| Gating | **Keep** `plans.enable_database` — admins choose which plans may use databases. Gates the library page, the create selector, and the capability payload. |
| Migration | **Clean break** — no data migration. Drop the global `database` settings, per-user lifecycle columns, provisioner abstraction, and builder Docker code. Existing projects simply have no connection until one is attached. |
| Selection timing | Attach at creation (Create-page selector); also viewable/detachable in project settings. Swapping = detach + attach + rebuild. |
| Delete/detach behavior | **Never auto-drop** the user's `proj_` schema — it lives in their own DB. `dropProjectSchema` is retained but not invoked automatically. |
| Demo | **Out of scope** (separate future spec). |

## Architecture

### 1. Data model

**New table `supabase_connections`** (migration):
`id`, `user_id` (FK, cascade on user delete), `label` (string), `url` (string), `publishable_key` (string), `secret_key` (text, `encrypted`), `db_connection` (text, `encrypted`), `last_tested_at` (nullable timestamp), timestamps.

**New model `App\Models\SupabaseConnection`**: `belongsTo(User)`, `hasMany(Project)`; casts `secret_key`/`db_connection` as `encrypted`; `$hidden = ['secret_key','db_connection']`.

**`projects` migration:** add `supabase_connection_id` (nullable FK → `supabase_connections`, `nullOnDelete`). Keep `supabase_schema`. `Project belongsTo SupabaseConnection`.

**Dropped (migrations):**
- `users`: `supabase_config`, `supabase_provisioned_at`, `supabase_status`, `supabase_suspended_at`, `supabase_reclaim_warned_at` (remove from `$fillable`/`$casts` too).
- SystemSetting `database` group keys are no longer written/read (no schema change needed; they simply become unused — the admin UI that set them is removed).

### 2. Connection library (per-user)

- **Page:** a "Database Connections" page under Profile/Settings, rendered only when the user's plan `enable_database` is true. Lists the user's connections; create/edit/delete; each row has **Test Connection**.
- **Controller:** `App\Http\Controllers\UserSupabaseConnectionController` (index/store/update/destroy/test). Routes under the authenticated group. Authorize every action to `connection.user_id === auth id`.
- **Read masking:** never return `secret_key`/`db_connection` to the browser; expose `has_secret_key` / `has_db_connection` booleans (mirror the existing admin masking pattern). On update, an empty secret field means "keep existing."
- **Test:** reuses `SupabaseService::testConnection($config)` (validates the `db_connection` reachability); stamps `last_tested_at`.
- **Validation:** `label` required; `url` a valid https URL; `db_connection` a `postgres(ql)://` URI (the existing `connect()` host/port validation applies). Test before allowing a connection to be attached is recommended but not required.

### 3. Create selector + per-project resolution

- **Create page:** add a `Database` picker (shown only when plan allows) → `None` or one of the user's connections. Submitted with the existing create payload (`template_id`, `design_system_id`, `design_accent`) as `supabase_connection_id`; persisted on the project. `CreateController`/`ProjectController@store` validates it `exists` and belongs to the user.
- **`SupabaseService` rewrite:**
  - `resolveForProject(Project)`: source **only** from `$project->supabaseConnection`. Returns `{url, publishable_key, secret_key, schema, db_connection}` from the connection, or an all-empty bundle when there is no connection. No modes, no global fallback.
  - Replace `isConfigured()` (global) with `hasConnection(Project): bool` (= `$project->supabase_connection_id !== null` and the connection resolves).
  - **Delete** `getMode()`, `MODE_*` consts, `cloudUrl()/cloudPublishableKey()/cloudSecretKey()/cloudDbConnection()`.
  - `testConnection()` keeps working but takes an explicit config array (no global fallback).
  - Keep `getProjectSchema`, `ensureProjectSchema`, `dropProjectSchema`, `setProjectSchemaExposed`, `exposeSchema`, `connect`, `defineTable`, `buildDefineTableSql` — all now operate against the resolved connection.
- **`BuilderService::buildSupabaseCapability`**: gate on `plan.databaseEnabled() && $project->supabase_connection_id` (drop the `isConfigured()` global check); body unchanged (uses `resolveForProject`). `buildPublishedConfig` unchanged (only `url`+`publishableKey`+`schema` to the client).
- **`BuilderSupabaseController::defineTable`**: gate on `plan.databaseEnabled() && $project->supabase_connection_id` (was `isConfigured()`).

### 4. Schema lifecycle (in the user's DB)

- **`ProjectObserver::created`:** if `plan.databaseEnabled()` **and** the project has a `supabase_connection_id`, call `ensureProjectSchema($project)` (creates `proj_<id>` + RLS grants + PostgREST exposure in the user's DB). **Remove** the `provisioner.ensureUserBackend()` call.
- **Attach later (settings):** attaching a connection to an existing project runs `ensureProjectSchema` and triggers a rebuild so the agent can `defineTable`.
- **`ProjectObserver::deleting`:** **no longer** auto-calls `dropProjectSchema` (it's the user's DB). Keeps the soft-delete publishing-field cleanup. `dropProjectSchema` remains in the service for a possible future explicit "also delete my data" action — not wired in v1.

### 5. Removal inventory (exhaustive)

**Laravel — delete:**
- `app/Services/Supabase/CloudProvisioner.php`, `app/Services/Supabase/SelfHostedProvisioner.php`, `app/Contracts/SupabaseProvisioner.php`, and the provisioner binding in `app/Providers/AppServiceProvider.php` (lines ~52-58).
- `resources/js/Pages/Admin/Settings/DatabaseSettingsTab.tsx`; its import + tab registration in `resources/js/Pages/Admin/Settings.tsx` (import line ~18, array entry ~46, render ~172).
- `SettingsController::{getDatabaseSettings, updateDatabase, testSupabase}` and the routes `admin.settings.database.update` / `admin.settings.database.test` (`routes/web.php:457-458`).
- The `getGroup('database')` plumbing for these keys (the `database` setting group becomes unused).
- User-model lifecycle columns + casts (see §1).

**Go builder — delete:**
- `internal/supabase/manager.go`, `internal/supabase/config.go` (the per-user stack `Manager`), `internal/api/supabase.go` (`/api/supabase/{provision,suspend,resume,teardown}` + route registration in `internal/api/router.go`), and the `WEBBY_SUPABASE_*` block in `internal/config/config.go`.
- **Keep** the `defineTable` tool (`internal/agent/tools.go`), `executeDefineTable` (`runner.go`), and the Laravel `/api/supabase/define-table` client (`internal/client/laravel/define_table.go`) — all per-project, BYOD-compatible.

**autosetup.sh — delete:**
- `prompt_supabase()` (~1401-1421), `install_docker()` (~1424-1460), `setup_supabase()` (~1463-1561); the `ENABLE_SUPABASE`/`SUPABASE_*` variable declarations (~56-68), the saved-state line (~216), the builder-config supabase block, and the `pm2 restart` tied to it. Remove the Docker dependency entirely.

**Docs — remove all self-hosted/global traces:**
- `docs/src/pages/Supabase.tsx` — **rewrite** for BYOD: remove the two-mode model, "Admin → Settings → Database", the Cloud/Self-hosted capability matrix, the `#modes` and `#self-hosted` sections, per-user-stack/autosetup content, and the admin-database screenshot. New content: the per-user connection library, adding a connection (url + publishable key + secret key + db connection + Test), plan gating (`enable_database`), attaching at project creation, "you own the database" (no auto-drop), and `defineTable`/RLS behavior.
- `docs/src/lib/searchIndex.ts` — remove the `/supabase#modes` and `/supabase#self-hosted` entries (lines ~306-314); update remaining Supabase entries to BYOD terms (connection library, bring-your-own).
- `docs/src/pages/VpsAutoSetup.tsx` — remove the "Optional: Self-hosted Supabase" callout (lines ~189-193).
- `docs/src/pages/Plugins.tsx:230` — drop the "Admin → Settings → Database" reference; the Database capability is a plan flag + per-user connection now, not an admin-configured backend.
- `docs/src/pages/UserGuide.tsx` ("Database Management", ~line 253) — note that database-backed apps require attaching one of your own Supabase connections (set at creation); keep the link to the rewritten `/supabase`.
- `docs/src/pages/AdminGuide.tsx:138` — reword the plan-feature row to "Database — lets users on this plan attach their own Supabase database to projects." (The `enable_database` flag is kept.)
- Rebuild `docs/dist`.
- **Unrelated, leave as-is:** "self-hosted Laravel Reverb" (Configuration/PusherSetup) and SSL "provisioning" (CustomDomains/AdminGuide) — not Supabase.

**Language strings (i18n) — across all 10 locales (`en, ar, de, fr, id, it, ja, pt, ru, zh`):**
- **Remove the dead admin Database-tab strings** from every `lang/<locale>/admin.json` — the block that only the removed admin Supabase UI used: `"Supabase Database"`, `"Configure the Supabase backend used for database-backed apps"`, `"Mode"`, `"Select mode"`, `"Supabase Cloud"`, `"Self-hosted"`, `"One Supabase project is shared by all users (accepted limitation)."`, `"Self-hosted Supabase is configured automatically by autosetup.sh on a fresh VPS. Self-hosted setup is not covered by customer support."`, `"These values are auto-filled by autosetup.sh. Edit them only as a fallback."`, and the autosetup-flavored `"•••••••• (saved — leave blank to keep)"` / `"Leave blank to keep the saved value."` if unused elsewhere. Remove the **same keys from all 10 locale files** so key parity is preserved (every `admin.json` currently has identical key sets).
- **Keep** `"Database (Supabase)"` (the `enable_database` plan-feature label — reword its English value to "Database — let this plan's users attach their own Supabase database") and the unrelated Reverb strings (`"Reverb (Self-hosted)"`, `"Self-hosted Laravel Reverb WebSocket server"`).
- **Add new strings** for the BYOD connection library + Create-page selector to `lang/<locale>/profile.json` (which already holds the connection-test strings) — e.g. `"Database Connections"`, `"Add Connection"`, `"Connection name"`, `"Supabase URL"`, `"Publishable Key"`, `"Secret Key"`, `"DB Connection String"`, `"Test Connection"`, `"No database"`, `"Select a database"`, plus any masking/help text. Reuse/relocate the still-needed labels (`"Project URL"`/`"Publishable Key"`/`"DB Connection String"`/`"Test Connection"`) here rather than leaving them in `admin.json`. Translate the new keys into all 9 non-English locales (the connection library is a normal in-app feature, so it **is** localized — unlike the installer/updater/docs/demo, which stay English-only).

### 6. Security

- `secret_key` + `db_connection` encrypted at rest; never serialized to Inertia/browser/preview (`$hidden`, masking on read). Only `url` + `publishable_key` reach the client (via `buildPublishedConfig` / `__APP_CONFIG__`).
- `resolveForProject` is server-to-server only (builder payload), as today.
- Outbound connections go to user-provided hosts: keep the existing `connect()` validation (scheme/host/port) and apply a short connect timeout; surface test failures clearly.
- A project may only reference a connection owned by its owner (validated on store/attach).

### 7. Testing / acceptance

- **Resolution:** `resolveForProject` returns the connection's bundle, and an empty bundle when `supabase_connection_id` is null; `hasConnection` correct.
- **Capability gating:** `{enabled:true}` only when plan `enable_database` AND a connection is attached; `{enabled:false}` otherwise; `buildPublishedConfig` never leaks secrets.
- **Library:** CRUD scoped to the owner; secrets masked on read; empty-secret update preserves the stored value; Test stamps `last_tested_at`.
- **Schema lifecycle:** creating a project with a connection runs `ensureProjectSchema` against that connection; `defineTable` writes to `proj_<id>` there; deleting the project does **not** drop the schema.
- **Removal:** grep the Laravel app for `MODE_SELF_HOSTED|MODE_CLOUD|supabase_mode|CloudProvisioner|SelfHostedProvisioner|SupabaseProvisioner|getDatabaseSettings|updateDatabase|supabase_config` → zero hits (outside the migration that drops them). `php artisan about` boots. Go builder `go build ./...` + `go vet` + `go test ./...` pass after `Manager`/`/api/supabase/*` removal. `autosetup.sh` has no `supabase`/`docker` functions (`grep -i 'supabase\|docker' autosetup.sh` → none). Docs: `grep -rni 'self.hosted\|supabase.mode\|admin.*settings.*database' docs/src` → no Supabase hits; `cd docs && npm run build` passes; no dead `#modes`/`#self-hosted` search anchors.
- **Plan gating UI:** a plan without `enable_database` hides the Connections page and the Create selector.
- **i18n:** the removed admin Database strings appear in no `lang/*/admin.json`; the new connection-library keys exist in **all 10** `lang/*/profile.json` with **identical key sets** (locale parity check — every locale's `admin.json` and `profile.json` must have the same keys as `en`); no removed key is still referenced anywhere in `resources/js`.

## Risks & notes

- **No auto-drop is the one behavior change** from today (force-delete used to `DROP SCHEMA … CASCADE`). Intentional: never destroy data in a user-owned DB. Orphaned `proj_` schemas are the user's to clean in their own Supabase.
- **Docker removal** simplifies deploys but is a hard break for anyone who ran the self-hosted path; acceptable per the clean-break decision.
- **`enable_database` semantics shift** from "platform backend is available + plan allows" to purely "plan allows the user to attach their own DB."
- The Go `defineTable` path and the per-project schema code are reused unchanged in behavior — only the *source* of the connection moves from global/per-user-mode to the project's connection.