Auto-detect and optimize Clerk authentication schemas - preserve JWT v2 tables, organization patterns, and user metadata with zero configuration

CLERK INTEGRATION: AUTHENTICATION MIGRATION OPTIMIZATION

Zero-configuration Clerk pattern recognition - CAPYSQUASH automatically detects and preserves your Clerk authentication schema.

Clerk is a modern authentication solution with JWT v2 tokens, organization management, and flexible user metadata. CAPYSQUASH's Clerk plugin recognizes these patterns instantly and optimizes your migrations while preserving critical authentication structures.

AUTO-DETECTION IN ACTION

[plugins] Scanning migrations for patterns...
[plugins] Detected: Clerk JWT v2 authentication
[plugins] Found 4 organization-related tables
[plugins] Identified 7 user metadata columns
[plugins] Preserving session management patterns
[plugins] Ready for optimization with Clerk support

✨ No configuration needed! CAPYSQUASH recognizes Clerk patterns automatically.

WHAT GETS DETECTED

Clerk-Specific Patterns

CAPYSQUASH automatically recognizes:

AUTHENTICATION

► JWT v2 token structures
► Session management tables
► User authentication flows
► Multi-factor auth (MFA) schemas
► Magic link patterns
► OAuth connection tables

ORGANIZATIONS

► Organization tables and relationships
► Membership and role structures
► Organization metadata schemas
► Invitation and permission patterns
► Organization claims in JWT
► Domain verification tables

User Metadata Patterns

Clerk's flexible user metadata is automatically preserved:

METADATA TYPES RECOGNIZED

PUBLIC METADATA

Data accessible to frontend (name, profile picture, preferences)

PRIVATE METADATA

Backend-only data (internal IDs, admin flags, custom attributes)

UNSAFE METADATA

Client-writable data (user preferences, UI state)

COMMON CLERK MIGRATION PATTERNS

Pattern 1: User Table with Clerk Integration

Before Optimization (5 migrations):

-- 001_create_users.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clerk_user_id TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 002_add_user_email.sql
ALTER TABLE users ADD COLUMN email TEXT;

-- 003_add_clerk_metadata.sql
ALTER TABLE users ADD COLUMN public_metadata JSONB DEFAULT '{}';
ALTER TABLE users ADD COLUMN private_metadata JSONB DEFAULT '{}';

-- 004_add_organization_id.sql
ALTER TABLE users ADD COLUMN organization_id TEXT;

-- 005_add_user_indexes.sql
CREATE INDEX idx_users_clerk_id ON users(clerk_user_id);
CREATE INDEX idx_users_org_id ON users(organization_id);

After Optimization (1 migration):

-- 001_create_users_complete.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clerk_user_id TEXT UNIQUE NOT NULL,
    email TEXT,
    public_metadata JSONB DEFAULT '{}',
    private_metadata JSONB DEFAULT '{}',
    organization_id TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_clerk_id ON users(clerk_user_id);
CREATE INDEX idx_users_org_id ON users(organization_id);

5 files → 1 file (80% reduction) with perfect Clerk pattern preservation

Pattern 2: Organizations with Roles

Before Optimization (8 migrations):

-- 001_create_organizations.sql
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clerk_org_id TEXT UNIQUE NOT NULL
);

-- 002_add_org_name.sql
ALTER TABLE organizations ADD COLUMN name TEXT NOT NULL;

-- 003_create_memberships.sql
CREATE TABLE organization_memberships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id),
    user_id UUID REFERENCES users(id)
);

-- 004_add_membership_role.sql
ALTER TABLE organization_memberships
ADD COLUMN role TEXT DEFAULT 'member';

-- 005_add_org_metadata.sql
ALTER TABLE organizations
ADD COLUMN metadata JSONB DEFAULT '{}';

-- 006_add_membership_timestamps.sql
ALTER TABLE organization_memberships
ADD COLUMN joined_at TIMESTAMPTZ DEFAULT NOW();

-- 007_add_org_indexes.sql
CREATE INDEX idx_orgs_clerk_id ON organizations(clerk_org_id);

-- 008_add_membership_indexes.sql
CREATE INDEX idx_memberships_org
ON organization_memberships(organization_id);
CREATE INDEX idx_memberships_user
ON organization_memberships(user_id);

After Optimization (2 migrations):

-- 001_create_organizations_complete.sql
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clerk_org_id TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_orgs_clerk_id ON organizations(clerk_org_id);

-- 002_create_memberships_complete.sql
CREATE TABLE organization_memberships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role TEXT DEFAULT 'member',
    joined_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_memberships_org ON organization_memberships(organization_id);
CREATE INDEX idx_memberships_user ON organization_memberships(user_id);

8 files → 2 files (75% reduction) with organization hierarchy preserved

OPTIMIZATION STRATEGIES

JWT v2 Token Preservation

CAPYSQUASH ensures JWT-related fields are never corrupted:

PROTECTED FIELDS

-- These patterns are preserved exactly:
clerk_user_id TEXT UNIQUE NOT NULL      -- User identifier
clerk_org_id TEXT                       -- Organization identifier
clerk_session_id TEXT                   -- Session tracking
public_metadata JSONB                   -- Public JWT claims
private_metadata JSONB                  -- Private backend data
organization_role TEXT                  -- Role in org context

☑ Field names, types, and constraints preserved exactly

Session Management

SESSION PATTERNS

► Session tables consolidated safely
► Expiration timestamps preserved
► Device tracking maintained
► Token refresh patterns optimized

User Provisioning

PROVISIONING WORKFLOWS

Common Clerk webhook patterns recognized:

-- User creation triggers
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    -- Clerk webhook handler
    INSERT INTO user_profiles (user_id, clerk_id)
    VALUES (NEW.id, NEW.clerk_user_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Organization membership sync
CREATE OR REPLACE FUNCTION sync_org_membership()
RETURNS TRIGGER AS $$
BEGIN
    -- Clerk org webhook handler
    -- CAPYSQUASH preserves these exactly
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

BEST PRACTICES

Schema Design for Clerk

☑ DO THIS

► Use clerk_user_id TEXT UNIQUE NOT NULL
► Store metadata in JSONB columns
► Index Clerk ID fields
► Use foreign keys for relationships
► Add created_at timestamps
► Handle cascading deletes properly

☒ AVOID THIS

► Don't duplicate Clerk's user data
► Don't store passwords locally
► Don't bypass Clerk for auth logic
► Don't use non-unique clerk IDs
► Don't ignore metadata columns
► Don't hardcode organization IDs

Migration Naming Conventions

RECOMMENDED NAMING

001_create_users_with_clerk.sql
002_create_organizations.sql
003_create_org_memberships.sql
004_add_clerk_webhooks.sql
005_add_user_metadata_indexes.sql

💡 Include "clerk" in filenames to make integration obvious

CLERK + OTHER SERVICES

Clerk + Supabase

HYBRID AUTHENTICATION

Use Clerk for auth, Supabase for database:

-- Users table bridges both systems
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clerk_user_id TEXT UNIQUE NOT NULL,     -- Clerk auth
    supabase_user_id UUID,                   -- Optional Supabase linkage
    email TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- CAPYSQUASH detects both patterns!

Clerk + Neon

SERVERLESS OPTIMIZATION

Clerk auth with Neon's serverless PostgreSQL:

► CAPYSQUASH optimizes for Neon's connection pooling
► Clerk patterns preserved for webhook handlers
► Session management optimized for serverless
► Auto-pause friendly schema design

WEBHOOK INTEGRATION

Clerk Webhook Handlers

CAPYSQUASH optimizes migrations with Clerk webhook patterns:

-- User created webhook
CREATE OR REPLACE FUNCTION clerk_user_created()
RETURNS TRIGGER AS $$
BEGIN
    -- Initialize user profile
    INSERT INTO user_profiles (
        user_id,
        clerk_id,
        public_metadata,
        private_metadata
    ) VALUES (
        NEW.id,
        NEW.clerk_user_id,
        NEW.public_metadata,
        NEW.private_metadata
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_user_created
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION clerk_user_created();

-- Organization created webhook
CREATE OR REPLACE FUNCTION clerk_org_created()
RETURNS TRIGGER AS $$
BEGIN
    -- Initialize organization settings
    INSERT INTO organization_settings (org_id, defaults)
    VALUES (NEW.id, '{"notifications": true}'::jsonb);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- CAPYSQUASH preserves these trigger functions exactly!

# Verify Clerk IDs are preserved
SELECT clerk_user_id FROM users LIMIT 5;

# Check metadata columns exist
\d users
\d organizations

Validate JWT Claims

# Ensure metadata structure is preserved
SELECT public_metadata, private_metadata
FROM users
WHERE clerk_user_id = 'user_xyz';

Test Organization Relationships

# Verify org memberships work
SELECT
    u.email,
    o.name as org_name,
    om.role
FROM users u
JOIN organization_memberships om ON u.id = om.user_id
JOIN organizations o ON om.organization_id = o.id;

TROUBLESHOOTING

Common Issues

⚠️ CLERK_USER_ID NOT UNIQUE

Problem: Multiple users with same Clerk ID

Solution: Add UNIQUE constraint before optimization:

ALTER TABLE users
ADD CONSTRAINT users_clerk_id_unique
UNIQUE (clerk_user_id);

⚠️ METADATA JSONB ERRORS

Problem: Invalid JSON in metadata columns

Solution: Validate and fix JSON before optimization:

-- Find invalid JSON
SELECT id FROM users
WHERE NOT (public_metadata::text)::jsonb IS NOT NULL;

-- Fix with default
UPDATE users
SET public_metadata = '{}'::jsonb
WHERE public_metadata IS NULL;

REAL-WORLD EXAMPLE

SaaS Application Migration

Starting point: 45 migration files with Clerk authentication

BEFORE OPTIMIZATION

migrations/
├── 001_create_users.sql
├── 002_add_clerk_id.sql
├── 003_add_email.sql
├── 004_add_metadata_columns.sql
├── 005_create_organizations.sql
├── 006_add_org_clerk_id.sql
├── 007_create_memberships.sql
├── 008_add_membership_roles.sql
...
├── 045_add_final_indexes.sql

CAPYSQUASH Analysis:

Detected Clerk JWT v2 patterns
Found 3 organization tables
Identified 12 user metadata columns
Recognized webhook trigger functions

AFTER OPTIMIZATION

optimized_migrations/
├── 001_create_users_complete.sql       (merged 1-15)
├── 002_create_organizations.sql        (merged 16-23)
├── 003_create_memberships.sql          (merged 24-31)
├── 004_add_webhook_triggers.sql        (merged 32-38)
└── 005_add_all_indexes.sql             (merged 39-45)

Results:

📊 45 files → 5 files (88.9% reduction)
⏱️ Deployment time: 8 min → 1.5 min (81% faster)
☑ Perfect Clerk integration preserved
🔒 All JWT v2 patterns intact

NEXT STEPS

🚀 GET STARTED

📚 RELATED GUIDES

Questions about Clerk integration? Check our troubleshooting guide or reach out to support@capysquash.dev

Clerk Integration: Authentication Migration Optimization