Database Strategy — Supabase-Centric Multi-Tenancy

This guide explains how we isolate customer data inside a shared Supabase project, when to spin up dedicated databases, and how the Forge CLI applies these decisions. It replaces the older “schema-per-app” playbook that targeted separate web/mobile bundles.

Last reviewed: October 16, 2025

---

Guiding Principles

1. Start simple: One Supabase project can host thousands of low-traffic apps when tables are prefixed per app.
2. Promote, don’t pre-optimize: Move a customer to a dedicated Supabase project (or another provider) only when usage or compliance demands it.
3. Automate the switches: ops/apps.yaml encodes the database strategy so Forge CLI can act without manual steps.
4. Never leak data: Row-level security (RLS) plus table prefixes ensure org/app isolation from day one.

---

Default: Shared Supabase Project with Prefixed Tables

Supabase Project (forge-platform)
├─ Platform Tables
│  ├─ organizations
│  ├─ apps
│  ├─ org_subscriptions
│  └─ audit_logs
├─ Shared Tables
│  └─ app_users
└─ App Tables (Prefixed)
   ├─ memory_match_posts
   ├─ memory_match_scores
   ├─ blogify_posts
   └─ blogify_comments

• Prefix rule: ${app_slug}_${table_name} (sanitized slug from ops/apps.yaml).
• Ownership: Platform tables belong to Level 2 (organizations), prefixed tables belong to Level 3 (customer apps).
• RLS: Applied where shared tables exist (e.g., app_users). Prefixed tables can rely on structural isolation, but RLS can still be added if needed.

Benefits

• One bill, one project, minimal operational overhead.
• Instant branching for previews (supabase db branch create feature/foo).
• Works seamlessly with Supabase Auth (org members vs app end-users).
• Simplifies data exports: drop schema? no—drop the prefixed tables for that app.

Implementation Details

• ops/apps.yaml → database.strategy: shared, database.tablePrefix.
• ops/cli/src/generate-env.ts injects TABLE_PREFIX, DATABASE_SCHEMA (for legacy Prisma usage), and other helpers.
• common/lib/forgeapps-sdk exposes getTableName('posts') style helpers to avoid hard-coding prefixes.
• Migrations run through Supabase SQL migration system or supabase db push. Forge CLI scaffolds starter SQL when generating a new app.

---

Promotion Path A: Dedicated Supabase Project

Use when:

• Customer exceeds shared project limits (connections, storage, compute).
• Data residency or compliance requires isolation.
• Customer is on an enterprise plan with contractual isolation.

Configuration:

apps:
  enterprise-crm:
    provider:
      type: supabase
      strategy: dedicated      # new | existing
      project_id: org_xxx/proj_yyy   # optional; CLI creates when omitted
    database:
      strategy: dedicated
      tablePrefix: crm_

Effects:

• Forge CLI provisions (or attaches to) a unique Supabase project.
• Credentials sync to Doppler under app-specific keys.
• Dashboard flags the app as Dedicated for support and billing.
• Domain and deployment flows remain identical (still Vercel) unless overridden.

---

Promotion Path B: Alternate Providers (Neon, AWS, GCP)

Use when:

• Customer insists on existing infrastructure.
• Supabase limits (functions, extensions) block required features.
• On-prem or VPC peering is required.

Configuration:

apps:
  bank-portal:
    provider:
      type: aws             # aws | gcp | neon
    database:
      strategy: dedicated
      host: bank-portal.corp.internal
      name: bank_portal
      userSecret: BANK_PORTAL_DB_USER
      passwordSecret: BANK_PORTAL_DB_PASSWORD

Effects:

• Forge CLI defers to Pulumi program (infra/pulumi-aws or infra/pulumi-gcp) to provision the database.
• Generated .env files include standard DATABASE_URL pointing to the dedicated instance.
• Supabase-specific helpers are disabled; the app template uses Prisma or direct SQL depending on the provider.

---

Row-Level Security Patterns

Even with prefixed tables, we rely on RLS for shared resources:

-- app_users: keep users scoped to their app
ALTER TABLE app_users ENABLE ROW LEVEL SECURITY;

CREATE POLICY app_user_visibility
ON app_users
USING (app_id = current_setting('app.current_app_id')::uuid);

Forge CLI sets app.current_app_id for server-side operations (via Supabase set_config or Postgres session variables). Generated apps include middleware to attach the correct app_id before executing queries.

---

Migration Workflow

1. Edit SQL in supabase/migrations.
2. Branch for local testing: supabase db branch create feature/my-change.
3. Run supabase db push or supabase db diff to verify.
4. Apply via CI using Supabase CLI or Pulumi automation.
5. Record changes in docs/DATABASE_MIGRATIONS_COMPLETED.md.

App-specific migrations live alongside the app and reference the prefix helper:

-- apps/templates/blog/sql/001_create_posts.sql
create table {{ TABLE_PREFIX }}posts (
  id uuid primary key default gen_random_uuid(),
  author_id uuid references app_users(id),
  title text not null,
  body text not null,
  created_at timestamptz default now()
);

Forge CLI renders these templates with the app’s prefix.

---

Monitoring & Promotion Criteria

Metrics we track per app to decide when to promote to a dedicated database:

Metric	Threshold	Action
Active end users	> 20k monthly	Consider dedicated Supabase project
DB connections	> 60% of shared pool	Increase pool or promote
Storage usage	> 50% of quota	Promote or enable storage add-on
Query latency (p95)	> 250ms	Investigate indexes, consider isolation
Compliance flag	Yes	Immediate promotion to dedicated stack

CI dashboards (Grafana/Metabase) pull these metrics via Supabase analytics and Stripe subscription tiers.

---

Quick Reference

• Shared Supabase prefix helper: common/lib/forgeapps-sdk/src/core/platform.ts
• Forge CLI database utilities: ops/cli/src/utils/app-deployment.ts, ops/cli/src/generate-env.ts
• Pulumi Supabase program: infra/pulumi-supabase
• Docs to pair with this guide: ARCHITECTURE.md, ARCHITECTURE_GUIDE.md, FORGE_CLI_PROVIDER_INTEGRATION.md

Keep this strategy doc aligned with live behavior—if the defaults or promotion criteria change, update the relevant sections and reference the supporting code.