T NOT NULL,
status TEXT DEFAULT 'active' CHECK (status IN ('active', 'archived', 'draft')),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS project_hub.milestones (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES project_hub.projects(id) ON DELETE CASCADE,
title TEXT NOT NULL,
deadline TIMESTAMPTZ,
is_complete BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Enable tenant isolation
ALTER TABLE project_hub.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_hub.milestones ENABLE ROW LEVEL SECURITY;
-- Policy: Owners can view their own projects
CREATE POLICY "owner_project_access" ON project_hub.projects
FOR SELECT USING (auth.uid() = owner_id);
-- Policy: Owners can view milestones belonging to their projects
CREATE POLICY "owner_milestone_access" ON project_hub.milestones
FOR SELECT USING (
EXISTS (
SELECT 1 FROM project_hub.projects p
WHERE p.id = project_hub.milestones.project_id
AND p.owner_id = auth.uid()
)
);
**Architecture Rationale:** RLS moves authorization logic from the application layer to the database engine. This eliminates the need for manual filtering in TypeScript and guarantees that even compromised client tokens cannot bypass tenant boundaries. Foreign key constraints with `ON DELETE CASCADE` maintain referential integrity automatically, removing the need for application-level cleanup routines.
### Step 2: Identity Migration
Firebase Auth and Supabase Auth use different password hashing algorithms (scrypt vs Argon2/bcrypt). Direct hash migration is cryptographically incompatible. The standard production approach is to transfer user metadata, trigger a password reset flow, and maintain a cross-reference mapping.
```typescript
// scripts/migrate-identity.ts
import { initializeApp, cert } from 'firebase-admin/app';
import { getAuth } from 'firebase-admin/auth';
import { createClient } from '@supabase/supabase-js';
import type { UserRecord } from 'firebase-admin/auth';
const firebaseApp = initializeApp({
credential: cert('./config/service-account.json')
});
const supabase = createClient(
process.env.SUPABASE_PROJECT_URL!,
process.env.SUPABASE_SERVICE_ROLE_KEY!
);
interface MigrationPayload {
email: string;
displayName: string | null;
firebaseUid: string;
}
async function syncIdentities(batchSize: number = 500): Promise<void> {
let pageToken: string | undefined;
let processedCount = 0;
do {
const listResult = await getAuth().listUsers(batchSize, pageToken);
const batch: MigrationPayload[] = listResult.users.map((user: UserRecord) => ({
email: user.email!,
displayName: user.displayName,
firebaseUid: user.uid
}));
for (const identity of batch) {
const { data, error } = await supabase.auth.admin.createUser({
email: identity.email,
email_confirm: true,
user_metadata: {
legacy_firebase_id: identity.firebaseUid,
display_name: identity.displayName
}
});
if (error) {
console.error(`[IDENTITY] Failed ${identity.email}: ${error.message}`);
} else {
console.log(`[IDENTITY] Mapped ${identity.firebaseUid} → ${data.user.id}`);
}
}
processedCount += batch.length;
pageToken = listResult.pageToken;
} while (pageToken);
console.log(`[IDENTITY] Migration complete. Total processed: ${processedCount}`);
}
syncIdentities().catch(console.error);
Architecture Rationale: Using the service role key bypasses RLS during migration, allowing bulk user creation. Storing the legacy Firebase UID in user_metadata preserves audit trails and enables gradual cutover without breaking existing session references. The batch loop prevents rate-limit throttling and provides deterministic progress logging.
Step 3: Data Synchronization & Validation
Document data must be flattened, transformed, and inserted into relational tables. Batch processing with retry logic ensures resilience against network interruptions or constraint violations.
// scripts/sync-records.ts
import { getFirestore, Timestamp } from 'firebase-admin/firestore';
import { createClient } from '@supabase/supabase-js';
const firestore = getFirestore();
const supabase = createClient(
process.env.SUPABASE_PROJECT_URL!,
process.env.SUPABASE_SERVICE_ROLE_KEY!
);
type FirestoreDoc = { id: string; data: Record<string, any> };
async function migrateCollection(
sourcePath: string,
targetTable: string,
transformer: (doc: FirestoreDoc) => Record<string, any>,
batchSize: number = 250
): Promise<void> {
const snapshot = await firestore.collection(sourcePath).get();
const records: FirestoreDoc[] = snapshot.docs.map(doc => ({
id: doc.id,
data: doc.data()
}));
const transformed = records.map(transformer);
for (let i = 0; i < transformed.length; i += batchSize) {
const chunk = transformed.slice(i, i + batchSize);
const { error } = await supabase.from(targetTable).insert(chunk);
if (error) {
console.error(`[SYNC] Batch ${i} failed for ${targetTable}:`, error.message);
// Implement exponential backoff or dead-letter queue in production
} else {
console.log(`[SYNC] Inserted ${Math.min(i + batchSize, transformed.length)}/${transformed.length} into ${targetTable}`);
}
}
}
// Execution order matters: parents before children
await migrateCollection('projects', 'projects', (doc) => ({
id: doc.id,
owner_id: doc.data.ownerId,
title: doc.data.title,
status: doc.data.status || 'draft',
created_at: doc.data.createdAt instanceof Timestamp ? doc.data.createdAt.toDate() : new Date()
}));
await migrateCollection('milestones', 'milestones', (doc) => ({
id: doc.id,
project_id: doc.data.projectId,
title: doc.data.title,
deadline: doc.data.deadline instanceof Timestamp ? doc.data.deadline.toDate() : null,
is_complete: doc.data.completed ?? false,
created_at: doc.data.createdAt instanceof Timestamp ? doc.data.createdAt.toDate() : new Date()
}));
Architecture Rationale: Executing parent tables before child tables prevents foreign key constraint violations. Timestamp conversion ensures PostgreSQL receives ISO-8601 compliant values. Batch sizing balances memory consumption with network efficiency. Production deployments should wrap these inserts in transactions and implement idempotency keys to support safe reruns.
Step 4: Application Layer Refactoring
The dual-SDK pattern is replaced by a unified client. Server Components and Server Actions share the same initialization flow, and RLS enforces data boundaries automatically.
// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';
export async function createClient() {
const cookieStore = await cookies();
return createServerClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
{
cookies: {
getAll: () => cookieStore.getAll(),
setAll: (cookiesToSet) => {
cookiesToSet.forEach(({ name, value, options }) =>
cookieStore.set(name, value, options)
);
}
}
}
);
}
// app/dashboard/page.tsx (Server Component)
import { createClient } from '@/lib/supabase/server';
export default async function DashboardPage() {
const supabase = await createClient();
const { data: projects } = await supabase
.from('projects')
.select('id, title, status, created_at')
.order('created_at', { ascending: false });
return (
<ul>
{projects?.map(project => (
<li key={project.id}>{project.title} — {project.status}</li>
))}
</ul>
);
}
// app/actions/create-project.ts (Server Action)
'use server';
import { revalidatePath } from 'next/cache';
import { createClient } from '@/lib/supabase/server';
export async function createProject(formData: FormData) {
const supabase = await createClient();
const title = formData.get('title') as string;
const { error } = await supabase.from('projects').insert({ title });
if (error) throw new Error(error.message);
revalidatePath('/dashboard');
}
Architecture Rationale: The @supabase/ssr package handles cookie serialization and JWT refresh cycles transparently. Server Components and Server Actions share the same client instance, eliminating context-switching. RLS policies automatically filter results based on the authenticated session, removing manual WHERE owner_id = ? clauses from application code.
Pitfall Guide
1. RLS Policy Gaps During Cutover
Explanation: Developers often disable RLS during migration for convenience and forget to re-enable it before production deployment. This exposes all tenant data to unauthenticated or cross-tenant requests.
Fix: Draft and test policies in a staging environment using mock JWTs. Use SET ROLE in PostgreSQL to simulate different user contexts. Never deploy without ENABLE ROW LEVEL SECURITY active.
2. Client-Side Aggregation Fallback
Explanation: Porting Firestore logic directly to Supabase without rewriting queries forces the application to fetch raw rows and aggregate in JavaScript. This negates PostgreSQL’s performance advantages and increases payload sizes.
Fix: Replace client-side grouping with GROUP BY, COUNT(), SUM(), and window functions. Use materialized views for expensive analytics queries that don’t require real-time freshness.
3. Password Hash Incompatibility
Explanation: Firebase uses scrypt with custom parameters. Supabase uses Argon2 or bcrypt. Direct hash migration fails cryptographically, locking users out of their accounts.
Fix: Implement a pre-migration email campaign instructing users to reset passwords. Alternatively, use Supabase’s password migration endpoint with custom hashing hooks if available, or accept temporary friction for a clean security baseline.
4. Connection Pool Exhaustion
Explanation: Supabase routes traffic through PgBouncer. Opening too many concurrent connections or holding transactions open during long-running operations exhausts the pool, causing too many clients errors.
Fix: Keep transactions short. Use connection pooling middleware. Avoid N+1 query patterns. Monitor pg_stat_activity and configure pool_mode = transaction for web workloads.
5. Dual-SDK Mental Model Persistence
Explanation: Engineers continue splitting client and server logic unnecessarily, initializing separate Supabase clients for browser and Node environments. This reintroduces the fragmentation the migration aimed to eliminate.
Fix: Adopt the unified @supabase/ssr pattern. Let RLS handle authorization boundaries. Use the same client factory across Server Components, Server Actions, and API routes.
6. Ignoring Index Strategy
Explanation: Firestore auto-indexes single fields and requires manual composite indexes. PostgreSQL does not auto-index. Missing indexes cause sequential scans on large tables, degrading query performance.
Fix: Analyze slow queries with EXPLAIN ANALYZE. Create B-tree indexes on frequently filtered columns. Use composite indexes for multi-column WHERE clauses. Consider partial indexes for status-based filtering.
7. Timestamp & Timezone Drift
Explanation: Firestore stores timestamps as opaque objects. PostgreSQL expects TIMESTAMPTZ. Mismatched timezone handling causes off-by-one-day errors in scheduling or reporting.
Fix: Normalize all timestamps to UTC during migration. Store as TIMESTAMPTZ in PostgreSQL. Convert to local time only at the presentation layer. Validate timezone behavior in staging with edge-case dates.
Production Bundle
Action Checklist
Decision Matrix
| Scenario | Recommended Approach | Why | Cost Impact |
|---|
| Early-stage MVP with <10K MAU | Firebase or Supabase Free Tier | Rapid iteration outweighs long-term cost concerns. Both platforms offer generous free tiers. | Negligible. Scales linearly only after traffic thresholds. |
| High-traffic SaaS with unpredictable spikes | Supabase Pro ($25/mo flat) | Decouples infrastructure cost from read/write volume. Prevents billing shocks during viral growth. | Predictable. Fixed monthly cost regardless of query volume. |
| Data-heavy analytics or complex reporting | Self-hosted PostgreSQL + Supabase Auth | Full control over indexing, materialized views, and query optimization. Avoids managed service query limits. | Higher operational overhead. Lower per-query cost at scale. |
| Strict compliance (HIPAA, SOC2, data residency) | Self-hosted Supabase or managed PostgreSQL | Open-source stack enables air-gapped deployments and custom audit trails. Meets data sovereignty requirements. | Increased DevOps investment. Eliminates vendor dependency. |
Configuration Template
// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr';
export function createBrowserSupabaseClient() {
return createBrowserClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);
}
// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';
export async function createServerSupabaseClient() {
const cookieStore = await cookies();
return createServerClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
{
cookies: {
getAll: () => cookieStore.getAll(),
setAll: (cookiesToSet) => {
cookiesToSet.forEach(({ name, value, options }) =>
cookieStore.set(name, value, options)
);
}
}
}
);
}
// sql/rls_policies.sql
-- Tenant isolation for projects
CREATE POLICY "tenant_project_access" ON projects
FOR ALL USING (auth.uid() = owner_id);
-- Read-only access for shared milestones
CREATE POLICY "public_milestone_read" ON milestones
FOR SELECT USING (EXISTS (
SELECT 1 FROM projects p
WHERE p.id = milestones.project_id
AND p.status = 'active'
));
Quick Start Guide
- Initialize Project: Run
supabase init and supabase start to spin up a local PostgreSQL instance with Auth, Storage, and Realtime enabled via Docker.
- Apply Schema: Execute
supabase db push to apply migration files containing table definitions and RLS policies.
- Configure Environment: Export
NEXT_PUBLIC_SUPABASE_URL and NEXT_PUBLIC_SUPABASE_ANON_KEY from the local dashboard or managed project settings.
- Run Migration Script: Execute
ts-node scripts/sync-records.ts to flatten Firestore collections and batch-insert into PostgreSQL. Verify row counts and foreign key integrity.
- Deploy & Validate: Push the refactored Next.js application to your hosting provider. Monitor PgBouncer connection metrics and RLS policy enforcement using Supabase logs.
