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 ```
</div>

<p className="font-bold mt-4">
  ✨ <strong>No configuration needed!</strong> CAPYSQUASH recognizes Clerk patterns automatically.
</p>
</div>

## WHAT GETS DETECTED

### Clerk-Specific Patterns

CAPYSQUASH automatically recognizes:

<div className="grid md:grid-cols-2 gap-6 my-8">
<div className="brutalist-card p-6">
  <h3 className="text-xl font-black uppercase mb-3"><Key size={24} weight="bold" /> AUTHENTICATION</h3>
  <ul className="space-y-2 font-bold text-sm">
    <li>► JWT v2 token structures</li>
    <li>► Session management tables</li>
    <li>► User authentication flows</li>
    <li>► Multi-factor auth (MFA) schemas</li>
    <li>► Magic link patterns</li>
    <li>► OAuth connection tables</li>
  </ul>
</div>

<div className="brutalist-card p-6">
  <h3 className="text-xl font-black uppercase mb-3"><Users size={24} weight="bold" /> ORGANIZATIONS</h3>
  <ul className="space-y-2 font-bold text-sm">
    <li>► Organization tables and relationships</li>
    <li>► Membership and role structures</li>
    <li>► Organization metadata schemas</li>
    <li>► Invitation and permission patterns</li>
    <li>► Organization claims in JWT</li>
    <li>► Domain verification tables</li>
  </ul>
</div>
</div>

### User Metadata Patterns

Clerk's flexible user metadata is automatically preserved:

<div className="brutalist-card p-6 my-6">
<h4 className="font-black uppercase mb-3">METADATA TYPES RECOGNIZED</h4>
<div className="space-y-3">
  <div className="brutalist-card-sm p-4">
    <p className="font-black text-sm mb-1">PUBLIC METADATA</p>
    <p className="font-bold text-xs">
      Data accessible to frontend (name, profile picture, preferences)
    </p>
  </div>
  <div className="brutalist-card-sm p-4">
    <p className="font-black text-sm mb-1">PRIVATE METADATA</p>
    <p className="font-bold text-xs">
      Backend-only data (internal IDs, admin flags, custom attributes)
    </p>
  </div>
  <div className="brutalist-card-sm p-4">
    <p className="font-black text-sm mb-1">UNSAFE METADATA</p>
    <p className="font-bold text-xs">Client-writable data (user preferences, UI state)</p>
  </div>
</div>
</div>

## COMMON CLERK MIGRATION PATTERNS

### Pattern 1: User Table with Clerk Integration

**Before Optimization (5 migrations):**

<div className="brutalist-card p-6 my-6">
<div className="brutalist-code">
```sql
-- 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):**



```sql
-- 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);

</div>
</div>

<Callout type="success" icon={<Lightning size={20} weight="bold" />}>
**5 files → 1 file (80% reduction)** with perfect Clerk pattern preservation </Callout>

---

### Pattern 2: Organizations with Roles

**Before Optimization (8 migrations):**

<div className="brutalist-card p-6 my-6">
<div className="brutalist-code text-xs">
```sql
-- 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):**



```sql
-- 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);

</div>
</div>

<Callout type="success" icon={<Lightning size={20} weight="bold" />}>
**8 files → 2 files (75% reduction)** with organization hierarchy preserved </Callout>

---

## OPTIMIZATION STRATEGIES

### JWT v2 Token Preservation

CAPYSQUASH ensures JWT-related fields are never corrupted:

<div className="brutalist-card p-6 my-6">
<h4 className="font-black uppercase mb-3">PROTECTED FIELDS</h4>
<div className="brutalist-code text-xs">
  ```sql -- 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:

```sql
-- 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;

  </div>
  </div>

  ---

  ## BEST PRACTICES

  ### Schema Design for Clerk

  <div className="grid md:grid-cols-2 gap-6 my-8">
  <div className="brutalist-card section-accent-success p-6">
  <h3 className="text-xl font-black uppercase mb-3">☑ DO THIS</h3>
  <ul className="space-y-2 font-bold text-sm">
  <li>► Use `clerk_user_id TEXT UNIQUE NOT NULL`</li>
  <li>► Store metadata in JSONB columns</li>
  <li>► Index Clerk ID fields</li>
  <li>► Use foreign keys for relationships</li>
  <li>► Add `created_at` timestamps</li>
  <li>► Handle cascading deletes properly</li>
  </ul>
  </div>

  - <div className="brutalist-card p-6">
  - <h3 className="text-xl font-black uppercase mb-3">☒ AVOID THIS</h3>
  - <ul className="space-y-2 font-bold text-sm">
  - <li>► Don't duplicate Clerk's user data</li>
  - <li>► Don't store passwords locally</li>
  - <li>► Don't bypass Clerk for auth logic</li>
  - <li>► Don't use non-unique clerk IDs</li>
  - <li>► Don't ignore metadata columns</li>
  - <li>► Don't hardcode organization IDs</li>
  - </ul>
  - </div>
  </div>

  ### Migration Naming Conventions

  <div className="brutalist-card p-6 my-6">
  <h4 className="font-black uppercase mb-3">RECOMMENDED NAMING</h4>
  <div className="brutalist-code text-xs">

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

  </div>
  - <p className="font-bold text-sm mt-3">
  - 💡 Include "clerk" in filenames to make integration obvious
  - </p>
  </div>

  ---

  ## CLERK + OTHER SERVICES

  ### Clerk + Supabase

  <div className="brutalist-card p-6 my-6">
  <h4 className="font-black uppercase mb-3">HYBRID AUTHENTICATION</h4>
  <p className="font-bold text-sm mb-3">Use Clerk for auth, Supabase for database:</p>
  <div className="brutalist-code text-xs">
  ```sql

\ - 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:



```sql
-- 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!

  </div>
  </div>

  ---

  ## VERIFICATION & TESTING

  ### Before Deployment

  <Steps>
  <Step>

  ### Test Clerk Integration Locally

  ```bash

# Verify Clerk IDs are preserved

SELECT clerk_user_id FROM users LIMIT 5;

# Check metadata columns exist

\d users

\d organizations





### Validate JWT Claims

```bash

# Ensure metadata structure is preserved

SELECT public_metadata, private_metadata
FROM users
WHERE clerk_user_id = 'user_xyz';

# 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;

ALTER TABLE users
ADD CONSTRAINT users_clerk_id_unique
UNIQUE (clerk_user_id);

-- 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;

    </div>
    </div>

    ---

    ## REAL-WORLD EXAMPLE

    ### SaaS Application Migration

    **Starting point:** 45 migration files with Clerk authentication

    <div className="brutalist-card-lg p-6 my-8">
    <h4 className="font-black uppercase mb-3">BEFORE OPTIMIZATION</h4>
    <div className="brutalist-code text-xs">

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

    </div>
    </div>

    **CAPYSQUASH Analysis:**
    - Detected Clerk JWT v2 patterns
    - Found 3 organization tables
    - Identified 12 user metadata columns
    - Recognized webhook trigger functions

    <div className="brutalist-card-lg section-accent-success p-6 my-8">
    <h4 className="font-black uppercase mb-3">AFTER OPTIMIZATION</h4>
    <div className="brutalist-code text-xs">

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)

    </div>
    </div>

    **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

    <div className="grid md:grid-cols-2 gap-6 my-8">
    <div className="brutalist-card p-6">
    <h3 className="text-xl font-black uppercase mb-3">🚀 GET STARTED</h3>
    <ul className="space-y-2 font-bold text-sm">
    <li>► [Quick Platform Setup](/docs/capysquash/quickstart)</li>
    <li>► [One-Click Optimization](/docs/capysquash/one-click-optimization)</li>
    <li>► [GitHub Integration](/docs/capysquash/github-integration)</li>
    </ul>
    </div>

    - <div className="brutalist-card p-6">
    - <h3 className="text-xl font-black uppercase mb-3">📚 RELATED GUIDES</h3>
    - <ul className="space-y-2 font-bold text-sm">
    - <li>► [Supabase Integration](/docs/platforms/supabase)</li>
    - <li>► [Neon Integration](/docs/platforms/neon)</li>
    - <li>► [Prisma Integration](/docs/integrations/prisma)</li>
    - </ul>
    - </div>
    </div>

    ---

    **Questions about Clerk integration?** Check our [troubleshooting guide](/docs/troubleshooting) or reach out to support@capysquash.dev
    $$.