plugins

pgsquash Plugin Architecture

Version: 0.9.7 Status: Production Ready ✅

This directory contains the unified plugin system for third-party integrations (auth providers, ORMs, Platforms).

🎯 Quick Overview

The plugin system automatically detects and optimizes for popular frameworks:

• Clerk - JWT v2 organization claims
• Supabase - Auth schema, RLS policies, storage
• Prisma - Migration metadata, shadow databases
• Drizzle - IDENTITY columns, modern PostgreSQL patterns

Zero configuration required - plugins auto-detect from migration patterns.

📦 Architecture Overview

Lifecycle Hooks

Plugins integrate into all migration phases:

1. Detection - Pattern matching to identify applicable frameworks
2. Enrichment - Add metadata to parsed statements
3. Transformation - Pre-parse SQL modifications
4. Consolidation - Custom squashing rules
5. Validation - Compatibility layer injection

Priority System

90-100: Auth Services (Clerk: 95, Supabase: 90)
70-89:  ORMs (Prisma: 75, Drizzle: 75)
50-69:  Platforms (future: Neon, Railway)
0-49:   Utilities

Higher priority wins conflicts. Example:

Detected: Clerk (95) + Supabase (90)
Result: Clerk active, Supabase excluded

Auto-Discovery

No configuration required - plugins detect themselves:

# Prisma project

./pgsquash squash prisma/migrations/*/migration.sql

# [plugins] Detected: prisma

# Drizzle project

./pgsquash squash drizzle/*/migration.sql

# [plugins] Detected: drizzle

# Clerk + Prisma project

./pgsquash squash migrations/*.sql

# [plugins] Detected: clerk, prisma

🔌 Implemented Plugins

1. Clerk Plugin (clerk/)

Priority: 95 (Auth tier) Market Share: ~15% authentication Status: ✅ Production Ready

Detection Patterns

• JWT v2 organization claims: auth.jwt()->'o'->>'id'
• Clerk helper functions: clerk_user_id(), clerk_org_id()
• Organization-scoped RLS policies

Features

-- Adds STABLE markers to auth functions
CREATE FUNCTION clerk_user_id() RETURNS TEXT
LANGUAGE plpgsql SECURITY DEFINER STABLE  -- ← Added by plugin
AS $$ BEGIN RETURN auth.jwt()->>'sub'; END; $$;

-- Injects compatibility layer for validation
CREATE FUNCTION auth.jwt() RETURNS JSONB STABLE AS $$
    SELECT '{"sub": "user_test", "o": {"id": "org_test"}}'::jsonb;
$$ LANGUAGE sql;

Consolidation Rules

• Never consolidates auth helper functions
• Preserves organization-scoped RLS policies
• Protects JWT claim transformations

2. Supabase Plugin (supabase/)

Priority: 90 (Auth tier) Market Share: ~20% backend-as-a-service Status: ✅ Production Ready

Detection Patterns

• auth.uid() function calls
• auth.users, auth.schema references
• Storage bucket policies: storage.buckets, storage.objects
• Realtime subscriptions

Features

-- Adds STABLE markers
CREATE POLICY "user_policy" ON users
USING (auth.uid() = user_id);  -- STABLE marker added to auth.uid()

-- Injects Supabase compatibility layer
CREATE SCHEMA IF NOT EXISTS auth;
CREATE FUNCTION auth.uid() RETURNS UUID STABLE AS $$
    SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;
$$ LANGUAGE sql;

Consolidation Rules

• Preserves RLS policies (never consolidates)
• Protects storage bucket configurations
• Maintains realtime publication settings

3. Prisma Plugin (prisma/)

Priority: 75 (ORM tier) Market Share: ~60% TypeScript ORMs Status: ✅ Production Ready

Detection Patterns

• _prisma_migrations table
• Timestamp directory structure: YYYYMMDDHHMMSS_name/
• Prisma comments: -- CreateTable, -- AlterTable
• Auto-increment pattern: DEFAULT nextval('table_seq'::regclass)
• VARCHAR(191) lengths (MySQL compatibility)

Features

-- Never consolidates migration metadata
CREATE TABLE "_prisma_migrations" (
  id VARCHAR(36) PRIMARY KEY,
  checksum VARCHAR(64) NOT NULL,
  migration_name VARCHAR(255) NOT NULL,
  ...
);  -- Always preserved

-- Enum protection (TypeScript mapping)
CREATE TYPE "Role" AS ENUM ('USER', 'ADMIN');
-- Never consolidated - breaks TypeScript types

-- Index optimization
-- Before: DROP + CREATE
-- After: Single CREATE (consolidated)

Transformations

• VARCHAR(191) → VARCHAR(255) for non-indexed columns
• Function volatility markers (STABLE)
• Shadow database pattern handling

4. Drizzle Plugin (drizzle/)

Priority: 75 (ORM tier) Market Share: ~25% TypeScript ORMs (growing) Status: ✅ Production Ready

Detection Patterns

• __drizzle_migrations table
• GENERATED ALWAYS AS IDENTITY columns
• GENERATED BY DEFAULT AS IDENTITY columns
• Drizzle directory: drizzle/TIMESTAMP_name/
• snapshot.json files

Features

-- IDENTITY column support (PostgreSQL 14+ best practice)
CREATE TABLE "users" (
  "id" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "email" TEXT UNIQUE NOT NULL
);

-- Generated columns preservation
CREATE TABLE "products" (
  "price" NUMERIC NOT NULL,
  "tax" NUMERIC GENERATED ALWAYS AS (price * 0.1) STORED
);

-- Sequence optimization
-- Removes redundant defaults (INCREMENT BY 1, START WITH 1)

Transformations

• Sequence simplification (remove defaults)
• Optional SERIAL → IDENTITY conversion
• Function volatility markers (IMMUTABLE for generated, VOLATILE for triggers)

🏗️ Directory Structure

internal/plugins/
├── plugin.go             # Plugin interface (12 lifecycle hooks)

├── registry.go           # Global registry with auto-discovery

├── README.md             # This file

│
├── clerk/                # Clerk authentication

│   ├── clerk.go          # Detection, enrichment, validation

│   ├── consolidation.go  # Consolidation rules

│   └── transformations.go # SQL transformations

│
├── supabase/             # Supabase Platform

│   ├── supabase.go
│   ├── consolidation.go
│   └── transformations.go
│
├── prisma/               # Prisma ORM

│   ├── prisma.go
│   ├── consolidation.go
│   └── transformations.go
│
└── drizzle/              # Drizzle ORM

    ├── drizzle.go
    ├── consolidation.go
    └── transformations.go

🔧 Configuration

Default (Auto-Detect)

{
  "plugins": {
    "auto_detect": true,
    "enabled_plugins": [],
    "disabled_plugins": [],
    "verbose": false
  }
}

Manual Control

{
  "plugins": {
    "auto_detect": false,
    "enabled_plugins": ["clerk", "prisma"],
    "disabled_plugins": ["supabase"],
    "verbose": true
  }
}

Plugin-Specific Configuration

{
  "third_party_integrations": {
    "clerk": {
      "jwt_version": "v2",
      "preserve_auth_helpers": true
    },
    "supabase": {
      "preserve_auth_schema": true,
      "preserve_storage_schema": true
    },
    "prisma": {
      "preserve_migration_table": true,
      "shadow_database": true,
      "optimize_indices": true
    },
    "drizzle": {
      "prefer_identity_columns": true,
      "preserve_drizzle_meta": true,
      "optimize_sequences": true
    }
  }
}

🚀 Usage

View Registered Plugins

./pgsquash --version

# [plugins] Registered plugin: clerk (priority: 95)

# [plugins] Registered plugin: supabase (priority: 90)

# [plugins] Registered plugin: prisma (priority: 75)

# [plugins] Registered plugin: drizzle (priority: 75)

Auto-Detection (Recommended)

# Plugins detect automatically

./pgsquash squash migrations/*.sql

# With verbose logging

./pgsquash squash migrations/*.sql --config pgsquash.config.json

# (set plugins.verbose: true in config)

Test Detection

# Create test migration with Prisma pattern

echo "CREATE TABLE _prisma_migrations (...)" > test.sql

./pgsquash analyze test.sql --verbose

# [plugins] Detected: prisma

💻 Creating a Plugin

Step 1: Create Plugin Package

mkdir internal/plugins/myservice

Step 2: Implement Plugin Interface

// internal/plugins/myservice/myservice.go
package myservice

import (
    "context"
    "github.com/capysquash/pgsquash-engine/internal/plugins"
    "github.com/capysquash/pgsquash-engine/internal/types"
)

type MyServicePlugin struct {
    *plugins.BasePlugin
}

func NewMyServicePlugin() *MyServicePlugin {
    return &MyServicePlugin{
        BasePlugin: plugins.NewBasePlugin("myservice", 80),
    }
}

// Required: Detect if this plugin applies
func (p *MyServicePlugin) Detect(migrations []*types.Migration) bool {
    for _, migration := range migrations {
        for _, stmt := range migration.Statements {
            if strings.Contains(stmt.SQL, "my_pattern") {
                return true
            }
        }
    }
    return false
}

// Optional: Configure from pgsquash.config.json
func (p *MyServicePlugin) Initialize(ctx context.Context, config interface{}) error {
    return nil
}

// Optional: Enrich statements with metadata
func (p *MyServicePlugin) EnrichStatement(ctx context.Context, stmt *types.Statement) error {
    return nil
}

// Optional: Transform SQL before parsing
func (p *MyServicePlugin) TransformSQL(ctx context.Context, sql string) (string, error) {
    return sql, nil
}

// Optional: Add consolidation rules
func (p *MyServicePlugin) GetConsolidationRules() []interface{} {
    return nil
}

// Optional: Should preserve this statement?
func (p *MyServicePlugin) ShouldPreserve(stmt *types.Statement) bool {
    return false
}

// Optional: Inject compatibility SQL for validation
func (p *MyServicePlugin) GetCompatibilitySQL(ctx context.Context) ([]string, error) {
    return nil, nil
}

// Optional: Validate schema after migration
func (p *MyServicePlugin) ValidateSchema(ctx context.Context, db *sql.DB) error {
    return nil
}

Step 3: Register Plugin

Add the plugin to builtinPlugins() in pkg/plugins/detection.go. That list is the single source of truth for built-in plugins — plugins.RegisterDefault() registers from it, and the public detection/compatibility APIs derive from it:

// pkg/plugins/detection.go
import "github.com/capysquash/pgsquash-engine/internal/plugins/myservice"

func builtinPlugins() []internal_plugins.Plugin {
    return []internal_plugins.Plugin{
        clerk.NewClerkPlugin(),
        supabase.NewSupabasePlugin(),
        prisma.NewPrismaPlugin(),
        drizzle.NewDrizzlePlugin(),
        myservice.NewMyServicePlugin(), // ← Add here
    }
}

Step 4: Build & Test

go build -o pgsquash cmd/pgsquash/main.go
./pgsquash --version  # Should show your plugin

./pgsquash analyze test-migrations/*.sql --verbose

📊 Implementation Status

✅ Completed (v0.9.7)

• Core Infrastructure

• Plugin interface (12 lifecycle hooks)

• Global registry with auto-discovery

• Priority-based conflict resolution

• Lazy initialization

• Shared types package (import cycle resolution)

• Integration Points

• Parser integration (statement enrichment)

• Transformer integration (pre-parse transformations)

• Validator integration (compatibility layers)

• Squasher integration (consolidation rules)

• Main registration

• Auth Plugins (2/4)

• Clerk (JWT v2, organization claims)

• Supabase (auth.uid(), RLS, storage)

• Auth0 (enterprise RBAC)

• NextAuth (Next.js adapters)

• ORM Plugins (2/3)

• Prisma (migration metadata, enums)

• Drizzle (IDENTITY columns, generated)

• TypeORM (decorators, entities)

📋 Planned (Future Phases)

Phase 4: Additional Auth (1-2 weeks)

• Auth0 plugin
• NextAuth plugin
• Firebase Auth plugin

Phase 5: Platform Plugins (2-3 weeks)

• Neon (serverless PostgreSQL)
• Railway (multi-environment)
• PlanetScale (MySQL compatibility)

Phase 6: Community (ongoing)

• Plugin development guide
• Community plugin template
• Plugin marketplace
• Version management

🎓 Technical Details

Import Cycle Resolution

Problem: parser ↔ plugins circular dependency

Solution: Shared types package

Before: parser → plugins → parser (CYCLE!)
After:  parser → types ← plugins (NO CYCLE!)

Implementation: internal/types/parser_types.go

Type Aliases for Backward Compatibility

// internal/parser/parser.go
type Statement = types.Statement
type Migration = types.Migration

const (
    TypeTable = types.TypeTable
    OpCreate = types.OpCreate
)

All existing code continues to work without changes.

Pre-Parse Transformations

Plugins transform SQL before pg_query parsing:

// Step 0: Plugin transformations (regex-based)
transformedSQL, _ := applyPluginTransformations(ctx, sql)

// Step 1: Parse with pg_query
parseTree, _ := pg_query.Parse(transformedSQL)

This handles cases where pg_query would fail on plugin-specific syntax.

🤝 Contributing

Adding a New Plugin

1. Create plugin package: internal/plugins/yourservice/
2. Implement Plugin interface (or embed BasePlugin)
3. Add consolidation rules in consolidation.go
4. Add transformations in transformations.go
5. Register in cmd/pgsquash/main.go
6. Add tests (unit + integration)
7. Update configuration example
8. Document in this README

Testing Guidelines

Unit Tests:

func TestMyPluginDetection(t *testing.T) {
    plugin := myservice.NewMyServicePlugin()
    migrations := createTestMigrations("SELECT * FROM my_pattern;")
    assert.True(t, plugin.Detect(migrations))
}

Integration Tests:

func TestMyPluginWorkflow(t *testing.T) {
    // Test full detection → transformation → consolidation → validation
}

📚 Additional Resources

• Architecture Deep-Dive: /docs/ARCHITECTURE.md
• Configuration Reference: /docs/CONFIGURATION.md
• Session Notes: /archive/session-notes/ (implementation history)

Last Updated: 2025-10-06. Version: 0.9.7. Plugins: 4 production-ready (Clerk, Supabase, Prisma, Drizzle).