pgsquash-engine: The Technology Behind CAPYSQUASH

The open-source engine that powers CAPYSQUASH - using PostgreSQL's parser and Docker validation for accurate migration consolidation.

pgsquash-engine is the technology foundation that powers CAPYSQUASH. It's available as an open-source Go library (MIT license) and handles all the parsing, analysis, and consolidation logic. The engine uses PostgreSQL's actual parser (pg_query_go) rather than regex patterns, and validates results with Docker-based schema comparison.

What is pgsquash-engine?

• Foundation: The technology that powers CAPYSQUASH Platform and capysquash-cli
• Go Library: Available for custom integrations and tooling
• Open Source: MIT licensed, fully transparent and auditable
• Parser-Based: Uses PostgreSQL's pg_query_go parser for accurate SQL analysis

🎯 How pgsquash-engine Works

Technical Approach

pgsquash-engine processes migrations through a 5-phase pipeline that handles parsing, analysis, consolidation, generation, and validation.

📦 Public API Packages

pgsquash-engine now exports 4 public packages for different use cases:

pkg/cli - CLI Execution API

Build custom CLI wrappers or integrate the full CLI into your applications.

import "github.com/CAPYSQUASH/pgsquash-engine/pkg/cli"

// Execute the full CLI
cli.SetVersionInfo("0.9.7", "2025-10-21", "abc123")
cli.SetBrandName("capysquash")
cli.Execute()

Use cases: Custom branded CLIs, CI/CD integration, CLI automation

pkg/engine - Library API

Use pgsquash programmatically for custom migration tools and workflows.

import "github.com/CAPYSQUASH/pgsquash-engine/pkg/engine"

// Squash migrations programmatically
result, err := engine.SquashDirectory("./migrations", &engine.Config{
    SafetyLevel: engine.Standard,
    Verbose:     true,
})
fmt.Println(result.SQL)

Use cases: Custom migration tools, automated workflows, batch processing, library integration

pkg/plugins - Plugin Management

Register and manage third-party integrations.

import "github.com/CAPYSQUASH/pgsquash-engine/pkg/plugins"

// Register all built-in plugins (Supabase, Clerk, Prisma, Drizzle)
plugins.RegisterDefault()

pkg/utils - Logging Utilities

Structured logging for your applications.

import "github.com/CAPYSQUASH/pgsquash-engine/pkg/utils"

logger := utils.NewLogger(utils.LogLevelInfo, os.Stdout)
utils.SetDefaultLogger(logger)

View complete API documentation →

🏗️ Technical Architecture

Domain-Driven Design

pgsquash-engine is organized into 8 specialized domains, each with clear responsibilities:

Parser Domain (internal/parser/)

• Purpose: Convert raw SQL into structured data
• Key Files: parser.go, dependencies.go, statement_analyzer.go
• Technology: pg_query_go for PostgreSQL-grade parsing
• Output: Structured Migration objects with Statement arrays

Tracking Domain (internal/tracking/)

• Purpose: Build object lifecycle representations
• Key Files: tracker.go, lifecycle.go, dependency_graph.go
• Features: Cycle detection, risk assessment, consolidation rules
• Output: ObjectLifecycle objects + consolidation results

Squasher Domain (internal/squasher/)

• Purpose: Orchestrate the entire consolidation process
• Key Files: engine.go, unified_resolver.go, extension_detector.go
• Responsibility: Phase orchestration, plugin management, statistics

Builder Domain (internal/builder/)

• Purpose: Construct optimized SQL output
• Key Files: sql_builder.go, formatter.go
• Features: Category grouping, dependency sorting, consistent formatting

Validation Domain (internal/validation/)

• Purpose: Verify schema equivalence
• Key Files: docker_validator.go, schema_diff.go, extension_detector.go
• Technology: Docker containers, schema comparison algorithms

Plugin Domain (internal/plugins/)

• Purpose: Third-party integration support
• Supported: Clerk, Supabase, Auth0, NextAuth patterns
• Features: Pattern detection, compatibility SQL, custom rules

🎛️ Advanced Features

Plugin System

pgsquash-engine automatically detects and activates plugins for third-party services:

# Example: Clerk JWT v2 detection
[plugins] Discovering plugins from 15 migrations...
[plugins] Activated 2 plugins: [clerk supabase]

Supported Plugins:

• Clerk: JWT v2 table pattern detection and preservation
• Supabase: Auth schema, RLS policies, and function patterns
• Auth0: Custom authentication table patterns
• Neon: Serverless PostgreSQL specific optimizations

Safety Levels

Choose your optimization aggressiveness based on your needs:

AI-Powered Analysis (Optional)

When enabled, pgsquash uses AI for advanced analysis:

• Function equivalency detection: Identify duplicate functions with different names
• Dead code detection: Find unused tables, indexes, and functions
• Semantic analysis: Understand business logic patterns
• Optimization suggestions: Recommend further improvements

📊 Example Results

Case Study: Startup Migration Cleanup

Before pgsquash:

• 287 migration files
• 15 minutes deployment time
• Frequent merge conflicts
• 2.3GB migration history

After pgsquash (Standard safety):

• 12 migration files (96% reduction)
• 45 seconds deployment time (20x faster)
• No merge conflicts
• 120MB optimized history

Performance Metrics

🚀 Using pgsquash-engine

As a Library (Recommended)

# Install as a Go module
go get github.com/CAPYSQUASH/pgsquash-engine

import "github.com/CAPYSQUASH/pgsquash-engine/pkg/engine"

// Use programmatically
result, err := engine.SquashDirectory("./migrations", &engine.Config{
    SafetyLevel: engine.Standard,
})

View Library API Documentation →

Via capysquash-cli (For CLI Usage)

# Install capysquash-cli for command-line usage
brew install capysquash-cli

# Or download from releases
# https://github.com/CAPYSQUASH/capysquash-cli/releases

# Use the CLI
capysquash analyze migrations/
capysquash squash migrations/ --safety=standard

View CLI Documentation →

Configuration

Create pgsquash.config.json:

{
  "safetyLevel": "standard",
  "plugins": {
    "supabase": true,
    "clerk": true
  },
  "validation": {
    "enabled": true,
    "approach": "two_databases"
  },
  "ai": {
    "enabled": false,
    "provider": "openai"
  }
}

🔧 Advanced Usage

API Server Mode

Run pgsquash as an HTTP API server:

# Start the API server
api-server --port 8080

# Analyze via API
curl -X POST http://localhost:8080/analyze \
  -H "Content-Type: application/json" \
  -d '{"migrations": [...], "safetyLevel": "standard"}'

GitHub Integration

Set up automatic PR analysis:

# Install GitHub App (via platform)
# Configure webhook endpoint
# Automatic analysis on PR push/update

Docker Validation

Ensure mathematical equivalence:

# Validate consolidation
pgsquash validate original/ squashed/ --approach=two_containers

# Result: ☑ Validation successful: Schemas are equivalent

🎯 When to Use Each Component

Use CAPYSQUASH Platform (Recommended) When:

• Getting started with migration squashing - one-click setup
• Team collaboration and shared projects are needed
• Automated GitHub integration and PR analysis are required
• Visual interface for migration analysis and optimization
• Analytics and reporting on optimization impact are valuable
• Scheduled automation and webhook notifications are desired

Use capysquash-cli (Power Users) When:

• Local development and quick migration analysis
• CI/CD pipelines needing command-line automation
• Individual developers who prefer terminal workflows
• Quick checks before committing migrations

Use pgsquash-engine (Library) When:

• Building custom tools that need programmatic migration optimization
• Integrating into existing systems and workflows
• Creating custom automation with full control
• Understanding the internals of how CAPYSQUASH works

📚 Next Steps

• CAPYSQUASH Platform - Get started with one-click squashing
• capysquash-cli Documentation - Command-line usage
• Library API Guide - Use pgsquash-engine programmatically
• Configuration Guide - Advanced configuration options
• PostgreSQL Internals - Deep dive into parser technology

Ready to start? Try CAPYSQUASH Platform for the easiest experience, or install capysquash-cli for command-line usage. Developers can use the Library API for custom integrations.