Complete guide to optimizing Supabase PostgreSQL migrations with automatic auth, RLS, storage, and realtime detection

Supabase Integration

Optimize Supabase migrations with intelligent auto-detection of auth schemas, RLS policies, storage buckets, and realtime functions

CAPYSQUASH automatically understands Supabase's unique patterns and safely optimizes your supabase/migrations/ directory while preserving all Supabase-specific functionality. No configuration required—just connect your repository and watch the magic happen.

Zero Configuration Required

CAPYSQUASH automatically detects Supabase projects and activates specialized optimization rules. Auth schemas, RLS policies, storage buckets, and realtime functions are all preserved during consolidation.

Why Supabase + CAPYSQUASH?

Supabase projects accumulate migration files rapidly during development. CAPYSQUASH keeps your supabase/migrations directory clean without breaking your app.

Auth Safe

Preserves auth.users, RLS policies, and JWT functions automatically

Fast Deploys

Typical projects see 30-50% faster deploy pipelines after consolidation

Smart Detection

Handles storage buckets, realtime subscriptions, and Supabase extensions

Quick Start

Get started in 5 minutes

Auto-Detection

How CAPYSQUASH recognizes Supabase

Auth & RLS

Authentication and security preservation

Best Practices

Production patterns and CI/CD

Quick Start

Install CAPYSQUASH

Choose your preferred method:

Sign up at capysquash.dev
Create an organization
Connect your GitHub repository
Auto-detection handles the rest

# macOS/Linux
brew install capysquash-cli

# Or use Go
go install github.com/CAPYSQUASH/capysquash-cli/cmd/capysquash@latest

npm install -g capysquash-cli

Analyze Your Migrations

capysquash analyze supabase/migrations/

What you'll see:

[plugins] ✓ Supabase project detected
[plugins] ✓ Found auth schema with 7 tables
[plugins] ✓ Found 23 RLS policies to preserve
[plugins] ✓ Found 3 storage buckets
[plugins] ✓ Optimization potential: 87 → 12 files (86% reduction)

Optimize with Safety

capysquash squash supabase/migrations/ \
  --output=clean/ \
  --safety=standard

Safety Levels

conservative: Production-safe (50-60% reduction)
standard: Recommended balance (70-80% reduction)
aggressive: Maximum optimization (85-95% reduction, dev only)

Validate (Recommended)

# Test with Supabase CLI
supabase db reset

# Or use Docker validation
capysquash validate \
  --original supabase/migrations/ \
  --squashed clean/

Deploy

# Replace old migrations
mv supabase/migrations supabase/migrations_backup
mv clean supabase/migrations

# Deploy to Supabase
supabase db push

Typical Results

Before: 89 migration files, 7min deployment After: 12 migration files, 45sec deployment (9x faster!)

Auto-Detection: How It Works

CAPYSQUASH automatically detects Supabase projects when it finds:

supabase/ directory with migration files
auth.* or storage.* schema references
Supabase-specific functions and extensions
Typical Supabase patterns (RLS, realtime, etc.)

[plugins] ✓ Supabase project detected
[plugins] ✓ Found auth schema with 7 tables
[plugins] ✓ Found 23 RLS policies to preserve
[plugins] ✓ Found 3 storage buckets
[plugins] ✓ Ready for Supabase-optimized consolidation

auth.users, auth.sessions, and related auth tables
JWT-related functions and triggers
RLS policy creation order and dependencies
Any function used by a policy

Example: Auth Consolidation

BEFORE: Multiple Files

001_create_users.sql

002_add_email.sql

003_add_timestamps.sql

004_enable_rls.sql

005_add_policies.sql

-- 001_create_users.sql
CREATE TABLE auth.users (id uuid, email text);

-- 002_add_email.sql
ALTER TABLE auth.users ADD COLUMN created_at timestamptz;

-- 003_add_timestamps.sql
ALTER TABLE auth.users ADD COLUMN updated_at timestamptz;

AFTER: Single Optimized File

001_auth_complete.sql

-- 001_auth_complete.sql
CREATE TABLE auth.users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE auth.users ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own data"
  ON auth.users FOR SELECT
  USING (auth.uid() = id);

Safety First

When using aggressive safety level, review auth-related changes carefully and always run validation in an isolated environment before merging to production.

Supabase-Specific Features

RLS Policy Optimization

CAPYSQUASH consolidates RLS policies while maintaining order and dependencies:

-- Before: Scattered policy migrations
CREATE POLICY "Users can view own data" ON profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own data" ON profiles FOR UPDATE USING (auth.uid() = id);
ALTER POLICY "Users can view own data" ON profiles USING (auth.uid() = id OR is_admin());

-- After: Consolidated with proper ordering
CREATE POLICY "Users can view own data" ON profiles
    FOR SELECT USING (auth.uid() = id OR is_admin());

CREATE POLICY "Users can update own data" ON profiles
    FOR UPDATE USING (auth.uid() = id);

Storage Bucket Management

Handles storage migrations without breaking file access:

-- Optimized storage schema
CREATE TABLE storage.buckets (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL UNIQUE,
    public boolean DEFAULT false,
    created_at timestamptz DEFAULT now()
);

CREATE TABLE storage.objects (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    bucket_id uuid REFERENCES storage.buckets(id) ON DELETE CASCADE,
    name text NOT NULL,
    metadata jsonb
);

Realtime Subscriptions

Preserves realtime publications and triggers:

-- Realtime-optimized messaging system
CREATE TABLE public.messages (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    room_id uuid NOT NULL REFERENCES public.rooms(id),
    user_id uuid NOT NULL REFERENCES auth.users(id),
    content text NOT NULL,
    created_at timestamptz DEFAULT now()
);

-- Realtime publication
ALTER PUBLICATION supabase_realtime ADD TABLE public.messages;

-- Optimized indexes
CREATE INDEX idx_messages_room_created ON public.messages(room_id, created_at DESC);

Extensions

Supabase extensions are auto-detected and preserved:

Commonly Used Extensions:

uuid-ossp - UUID generation
vector (pgvector) - Vector embeddings
pgjwt - JWT support
pg_net - HTTP requests
pg_graphql - GraphQL
pg_jsonschema - JSON schema validation
vault - Secrets management

Real-World Examples

Example 1: Next.js + Supabase SaaS

Project Setup:

Next.js frontend with Supabase auth
PostgreSQL database with 15 tables
RLS policies on all user data
Storage for user uploads

Results:

Metric	Before	After	Improvement
Migration Files	89	12	86% reduction
Deployment Time	7 min	45 sec	9x faster
Auth Conflicts	3/month	0/month	100% resolved
RLS Bugs	2/month	0/month	100% resolved

Example 2: Multi-tenant Application

Project Setup:

Multi-tenant architecture with tenant_id
Complex RLS policies for data isolation
Custom auth extensions
Realtime subscriptions

Results:

Metric	Before	After	Improvement
Migration Files	156	18	88% reduction
Deployment Time	12 min	1 min	12x faster
Tenant Issues	Present	None	100% resolved
Realtime Errors	4/month	0/month	100% resolved

Best Practices

Safety Level Recommendations

Conservative

Production-safe, 50-60% reduction. Recommended for first-time users.

Standard

Balanced approach, 70-80% reduction. Recommended for most users.

Aggressive

Maximum optimization, 85-95% reduction. Use with caution, dev only.

CI/CD Integration

name: Optimize Migrations
on:
  pull_request:
    paths:
      - 'supabase/migrations/**'

jobs:
  optimize:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Install capysquash
        run: go install github.com/CAPYSQUASH/capysquash-cli/cmd/capysquash@latest

      - name: Analyze
        run: capysquash analyze supabase/migrations/

      - name: Squash
        run: capysquash squash supabase/migrations/ --output clean/ --safety=conservative

      - name: Validate
        run: capysquash validate --original supabase/migrations/ --squashed clean/

optimize-migrations:
  stage: test
  script:
    - go install github.com/CAPYSQUASH/capysquash-cli/cmd/capysquash@latest
    - capysquash analyze supabase/migrations/
    - capysquash squash supabase/migrations/ --output clean/ --safety=conservative
    - capysquash validate --original supabase/migrations/ --squashed clean/
  only:
    changes:
      - supabase/migrations/**

# Analyze
capysquash analyze supabase/migrations/

# Squash with validation
capysquash squash supabase/migrations/ \
  --output=clean/ \
  --safety=conservative \
  --validate

# Test with Supabase CLI
supabase db reset

# Deploy
supabase db push

Do's and Don'ts

DO

Squash regularly (monthly)
Test with supabase db reset
Use standard safety for dev
Use conservative for prod
Validate before deployment
Keep original backups

DON'T

Skip validation step
Use aggressive on production
Squash deployed migrations
Ignore Supabase patterns
Delete original migrations
Deploy without testing

-- Multi-tenant user management
CREATE TABLE public.tenant_users (
    id uuid REFERENCES auth.users(id) PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES public.tenants(id),
    role text NOT NULL DEFAULT 'member',
    permissions jsonb DEFAULT '[]',
    joined_at timestamptz DEFAULT now()
);

-- RLS for tenant isolation
ALTER TABLE public.tenant_users ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Tenant isolation" ON public.tenant_users FOR SELECT
USING (
    tenant_id IN (
        SELECT tenant_id FROM public.tenant_users
        WHERE id = auth.uid()
    )
);

Custom Auth Functions

-- Check tenant membership
CREATE OR REPLACE FUNCTION public.is_tenant_member(tenant_uuid uuid)
RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM public.tenant_users
        WHERE tenant_id = tenant_uuid
        AND id = auth.uid()
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;

-- Check admin role
CREATE OR REPLACE FUNCTION public.is_admin(user_uuid uuid)
RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM public.profiles
        WHERE id = user_uuid AND role = 'admin'
    );
END;
$$ LANGUAGE plpgsql STABLE;

Performance Optimization

-- Optimized search with indexes
CREATE INDEX idx_documents_search ON public.documents
USING gin(to_tsvector('english', content));

CREATE INDEX idx_messages_room_created ON public.messages(room_id, created_at DESC);

-- Optimized RLS policy
CREATE POLICY "document_access" ON public.documents FOR SELECT
USING (
    user_id = auth.uid()
    OR is_public = true
    OR team_id IN (SELECT team_id FROM public.team_members WHERE user_id = auth.uid())
);

READY TO OPTIMIZE?

Start with the free CLI or try the platform and see Supabase-specific optimization in action.

Try Platform

Install CLI

Supabase Integration

Auth Safe

Fast Deploys

Smart Detection

Quick Start

Auto-Detection

Auth & RLS

Best Practices

Auth Schema Patterns

Row Level Security (RLS)

Storage System

Realtime Features

BEFORE: Multiple Files

AFTER: Single Optimized File

Conservative

Standard

Aggressive

DO

DON'T

Auth Schema Conflicts

Storage Policies Not Working

Extension Not Found

RLS Performance Issues

Realtime Subscription Delays

Getting Started

Safety Levels

Docker Validation

Platform Overview

READY TO OPTIMIZE?

On this page