Scalable system architecture for recurring revenue platforms.

We use a shared-schema, row-level isolation model. All tenants reside in a single database; each row includes a tenant_id (or equivalent). Postgres RLS policies enforce isolation at query time. This reduces operational overhead compared to database-per-tenant while maintaining strict data separation.

Stack reasoning: Supabase provides RLS out of the box. Policies are defined in SQL and apply to all access paths (API, direct SQL, Edge Functions). No application-level tenant filtering is required if RLS is correctly configured.

CREATE POLICY tenant_isolation ON subscriptions
  USING (org_id = current_setting('app.current_tenant_id')::uuid);

Stripe Billing is the source of truth for subscriptions and payments. Webhooks (customer.subscription.updated, invoice.paid, etc.) drive local state. Handlers are idempotent: duplicate events do not cause duplicate writes. The database stores a denormalized view for fast reads; Stripe remains authoritative for billing disputes.

// Idempotent webhook handler
const existing = await db.subscriptionEvents.findUnique({
  where: { stripeEventId: event.id }
});
if (existing) return { received: true };

RBAC is enforced at two layers: Supabase Auth (session, JWT) and application middleware. Roles map to permission sets (e.g. admin, member, viewer). RLS policies reference auth.jwt() → "role". Application routes validate role before rendering or before calling APIs. Never trust client-sent role claims for authorization decisions.

// Server-side role check (Next.js)
const session = await getServerSession();
if (session?.user?.role !== 'admin') {
  return redirect('/unauthorized');
}

Automation is triggered by webhooks (Stripe, external systems) or scheduled jobs (cron, pg_cron, or queue workers). Handlers run in Edge Functions or serverless runtimes. Business logic is centralized; triggers only invoke the same functions. All actions are logged with tenant and user context for audit trails.

Stack reasoning: Supabase Edge Functions and Vercel serverless handle event-driven workloads. For high-volume queues, a dedicated queue (e.g. Inngest, Trigger.dev) may be added. Start simple; add complexity when throughput demands it.

Tables are normalized for consistency; read-heavy paths use materialized views or cached aggregates. Every tenant-scoped table includes org_id (or tenant_id). Audit columns (created_at, updated_at, created_by) are standard. Composite indexes include tenant columns as the leading key to support RLS-filtered queries.

-- Composite index for tenant-scoped queries
CREATE INDEX idx_subscriptions_org_status
  ON subscriptions (org_id, status)
  WHERE status IN ('active', 'trialing');