capysquash-api

The capysquash-api is a professional-grade REST API server that provides HTTP endpoints for PostgreSQL migration analysis and consolidation. Built on top of pgsquash-engine, it powers the CAPYSQUASH Platform backend.

💡 Looking for API access? Most users should use the CAPYSQUASH Platform API which provides managed API access with authentication, rate limiting, and monitoring. This page documents the standalone capysquash-api server for self-hosting or custom deployments.

📦 Repository: The API server is now maintained as a separate module at github.com/CAPYSQUASH/capysquash-api

OVERVIEW

🚀 FEATURES

☑ REST API endpoints
☑ GitHub webhook handling
☑ CORS support
☑ Health checks
☑ Docker deployment ready

🎯 USE CASES

☑ Self-hosting CAPYSQUASH's backend
☑ Custom deployment scenarios
☑ Integration into existing platforms
☑ Learning how CAPYSQUASH works

👉 For production use: Consider using CAPYSQUASH Platform which provides managed hosting, authentication, monitoring, and support.

QUICK START

Building the API Server

# Clone the capysquash-api repository
git clone https://github.com/CAPYSQUASH/capysquash-api
cd capysquash-api

# Install dependencies
go mod download

# Build the API server binary
go build -o api-server ./cmd/api-server

# Run the server
./api-server

The server will start on port 8080 by default.

📝 Note: The API server was previously part of pgsquash-engine but is now maintained as a separate module (capysquash-api) for better modularity and independent versioning.

Docker Deployment

# Using the included Dockerfile
docker build -t capysquash-api -f Dockerfile .

# Run the container
docker run -p 8080:8080 \
  -e JWT_SECRET=your-secret-key \
  capysquash-api

Docker Compose

version: '3.8'

services:
  capysquash-api:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8080:8080"
    environment:
      - PORT=8080
      - LOG_LEVEL=info
      - JWT_SECRET=your-secret-key
      - CORS_ORIGIN=https://your-domain.com
    restart: unless-stopped

CONFIGURATION

Environment Variables

Variable	Required	Default	Description
`PORT`	No	`8080`	Server port
`LOG_LEVEL`	No	`info`	Logging level (debug, info, warn, error)
`JWT_SECRET`	Yes	-	Secret key for JWT token validation
`DATABASE_URL`	Yes	-	PostgreSQL database connection string
`CORS_ORIGIN`	No	`*`	Allowed CORS origins (comma-separated)
`GITHUB_APP_ID`	No	-	GitHub App ID for automation
`GITHUB_APP_PRIVATE_KEY`	No	-	GitHub App private key
`GITHUB_WEBHOOK_SECRET`	No	-	GitHub webhook signature verification
`ANTHROPIC_API_KEY`	No	-	Anthropic API key for AI features
`OPENAI_API_KEY`	No	-	OpenAI API key for AI features

Example Configuration

# .env file
PORT=8080
LOG_LEVEL=info
JWT_SECRET=super-secret-key-change-this-256-bit
DATABASE_URL=postgresql://user:password@localhost:5432/capysquash
CORS_ORIGIN=https://capysquash.dev,https://www.capysquash.dev
GITHUB_WEBHOOK_SECRET=your-github-webhook-secret
ANTHROPIC_API_KEY=sk-ant-your-key

API ENDPOINTS

POST /analyze

Analyze migration files and return dependency graph and recommendations.

Authentication: Required (JWT Bearer token)

Request Body:

{
  "migrations": [
    "CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255));",
    "CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id));"
  ],
  "config": {
    "safety_level": "standard",
    "enable_ai": false
  }
}

Response:

{
  "summary": {
    "total_files": 2,
    "total_statements": 2,
    "warnings": 0,
    "errors": 0
  },
  "dependencies": [
    {
      "source": "users",
      "target": "posts",
      "type": "foreign_key"
    }
  ],
  "recommendations": [
    {
      "type": "info",
      "message": "Migrations can be consolidated",
      "impact": "low"
    }
  ]
}

cURL Example:

curl -X POST http://localhost:8080/analyze \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer your-jwt-token" \
  -d '{
    "migrations": ["CREATE TABLE users (id SERIAL PRIMARY KEY);"],
    "config": {"safety_level": "standard"}
  }'

POST /squash

Consolidate migrations into a single optimized file.

Authentication: Required (JWT Bearer token)

Request Body:

{
  "migrations": [
    "CREATE TABLE users (id SERIAL PRIMARY KEY);",
    "ALTER TABLE users ADD COLUMN email VARCHAR(255);",
    "ALTER TABLE users ADD COLUMN created_at TIMESTAMP;"
  ],
  "config": {
    "safety_level": "standard",
    "validate": true
  }
}

Response:

{
  "squashed": "CREATE TABLE users (\n  id SERIAL PRIMARY KEY,\n  email VARCHAR(255),\n  created_at TIMESTAMP\n);",
  "summary": {
    "original_files": 3,
    "squashed_files": 1,
    "reduction_percent": 67,
    "warnings": 0
  },
  "validation": {
    "passed": true,
    "message": "Schema validation successful"
  }
}

Note: The squashed result is always 1 consolidated file that contains all migrations in the correct dependency order.

POST /validate

Validate that squashed migrations produce equivalent schema to original migrations.

Authentication: Required (JWT Bearer token)

Request Body:

{
  "original": [
    "CREATE TABLE users (id SERIAL PRIMARY KEY);",
    "ALTER TABLE users ADD COLUMN email VARCHAR(255);"
  ],
  "squashed": [
    "CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255));"
  ]
}

Response:

{
  "equivalent": true,
  "message": "Schemas are equivalent",
  "differences": [],
  "validation_time_ms": 1234
}

POST /github/webhook

Handle GitHub webhook events for PR automation.

Authentication: GitHub signature verification (HMAC-SHA256)

Supported Events:

pull_request (opened, synchronize, reopened)
push (optional)

Request: Standard GitHub webhook payload

Response:

{
  "status": "processing",
  "message": "Analysis queued",
  "analysis_id": "abc123"
}

GET /health

Health check endpoint for monitoring and load balancers.

Authentication: None required

Response:

{
  "status": "healthy",
  "version": "0.9.7",
  "uptime_seconds": 3600
}

AUTHENTICATION

All API endpoints (except /health) require JWT authentication via the Authorization: Bearer <token> header.

Setting Up Authentication

Generate a secure JWT secret key:

openssl rand -hex 32

Set the secret in your environment:

export JWT_SECRET=your-generated-secret

Generate a JWT token with your secret and include it in API requests:

curl -X POST http://localhost:8080/analyze \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Content-Type: application/json" \
  -d @request.json

🔐 JWT Token Generation: You'll need to generate JWT tokens using the JWT_SECRET. For production use with CAPYSQUASH Platform, tokens are automatically managed through Clerk authentication.

Security Best Practices

⚠️ IMPORTANT SECURITY NOTES

► Use a strong, randomly generated secret (32+ characters)
► Never commit secrets to version control
► Rotate secrets regularly (every 90 days)
► Use HTTPS in production
► Implement rate limiting (via reverse proxy)

DEPLOYMENT

Fly.io Deployment

# Install flyctl
curl -L https://fly.io/install.sh | sh

# Login to Fly.io
fly auth login

# Create app
fly launch

# Set secrets
fly secrets set CAPYSQUASH_API_SECRET=your-secret

# Deploy
fly deploy

Railway Deployment

# Install railway CLI
npm i -g @railway/cli

# Login
railway login

# Initialize project
railway init

# Set environment variables
railway variables set CAPYSQUASH_API_SECRET=your-secret

# Deploy
railway up

Render Deployment

Connect your GitHub repository
Select "Docker" as environment
Set environment variables in dashboard
Deploy automatically on push

AWS ECS/Fargate

# Build and push Docker image
docker build -t pgsquash-api .
docker tag pgsquash-api:latest your-registry/pgsquash-api:latest
docker push your-registry/pgsquash-api:latest

# Create ECS task definition
# Create ECS service
# Configure load balancer

MONITORING

Health Checks

Configure your load balancer or monitoring system to check:

GET /health

Expected response: 200 OK with JSON body.

Logging

The API server logs to stdout in JSON format:

{
  "level": "info",
  "time": "2025-10-20T12:00:00Z",
  "message": "Request received",
  "method": "POST",
  "path": "/analyze",
  "duration_ms": 123
}

Metrics

Consider adding metrics collection:

Request count by endpoint
Response time percentiles
Error rate
Active connections

Tools: Prometheus, Datadog, New Relic

INTEGRATION EXAMPLES

GitHub Actions

name: Analyze Migrations

on:
  pull_request:
    paths:
      - 'migrations/**'

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

      - name: Analyze migrations
        run: |
          MIGRATIONS=$(cat migrations/*.sql | jq -Rs '.')

          curl -X POST ${{ secrets.CAPYSQUASH_API_URL }}/analyze \
            -H "Authorization: Bearer ${{ secrets.JWT_TOKEN }}" \
            -H "Content-Type: application/json" \
            -d "{\"migrations\": [$MIGRATIONS]}"

Node.js

const axios = require('axios');

const client = axios.create({
  baseURL: process.env.CAPYSQUASH_API_URL,
  headers: {
    'Authorization': `Bearer ${process.env.JWT_TOKEN}`,
    'Content-Type': 'application/json'
  }
});

async function analyzeMigrations(migrations) {
  const response = await client.post('/analyze', {
    migrations,
    config: { safety_level: 'standard' }
  });

  return response.data;
}

Python

import os
import requests

class CapysquashClient:
    def __init__(self):
        self.base_url = os.environ['CAPYSQUASH_API_URL']
        self.jwt_token = os.environ['JWT_TOKEN']

    def analyze(self, migrations):
        response = requests.post(
            f'{self.base_url}/analyze',
            json={'migrations': migrations},
            headers={'Authorization': f'Bearer {self.jwt_token}'}
        )
        return response.json()

Go

package main

import (
    "bytes"
    "encoding/json"
    "net/http"
    "os"
)

type AnalyzeRequest struct {
    Migrations []string `json:"migrations"`
}

func analyzeMigrations(migrations []string) error {
    req := AnalyzeRequest{Migrations: migrations}
    body, _ := json.Marshal(req)

    httpReq, _ := http.NewRequest(
        "POST",
        os.Getenv("CAPYSQUASH_API_URL")+"/analyze",
        bytes.NewBuffer(body),
    )

    httpReq.Header.Set("Authorization", "Bearer "+os.Getenv("JWT_TOKEN"))
    httpReq.Header.Set("Content-Type", "application/json")

    client := &http.Client{}
    resp, err := client.Do(httpReq)
    defer resp.Body.Close()

    return err
}

TROUBLESHOOTING

Server Won't Start

Check:

Port 8080 is not already in use
Docker is running (if using Docker)
Environment variables are set correctly

# Check if port is in use
lsof -i :8080

# Check Docker
docker ps

Authentication Failing

Symptoms: 401 Unauthorized responses

Check:

Authorization: Bearer <token> header is included
JWT token is valid and not expired
Token is signed with the correct JWT_SECRET
No extra whitespace in token
Header format is correct (case-sensitive)

CORS Errors

Symptoms: Browser console shows CORS errors

Solution:

Set CORS_ORIGIN environment variable
Include all allowed domains (comma-separated)
Use HTTPS in production

CORS_ORIGIN=https://capysquash.dev,https://app.capysquash.dev

Validation Failures

Symptoms: /validate endpoint returns errors

Check:

Docker is running
Docker socket is accessible
PostgreSQL Docker images can be pulled
Sufficient disk space

PERFORMANCE TUNING

Concurrency

The API server handles concurrent requests efficiently. For high-load scenarios:

# Increase Go's GOMAXPROCS (default: number of CPUs)
export GOMAXPROCS=8

Resource Limits

# docker-compose.yml
services:
  pgsquash-api:
    # ...
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2G
        reservations:
          cpus: '1'
          memory: 1G

Caching

Consider adding Redis for caching analysis results:

export REDIS_URL=redis://localhost:6379

API VERSIONING

Current API version: v1 (implied in all endpoints)

Future versions will use explicit versioning:

POST /v2/analyze

Breaking changes will always use a new version number.

ARCHITECTURE OVERVIEW

The capysquash-api server is built on top of pgsquash-engine and serves as the HTTP interface for migration analysis and consolidation:

┌─────────────────────────────────────────┐
│     CAPYSQUASH Platform (Next.js)       │
│  (User-facing web app with UI/UX)      │
└─────────────────┬───────────────────────┘
                  │ HTTP API calls
┌─────────────────▼───────────────────────┐
│        capysquash-api (Go)              │
│  - REST API endpoints                   │
│  - JWT authentication                   │
│  - GitHub webhooks                      │
│  - Operations tracking                  │
└─────────────────┬───────────────────────┘
                  │ Go library import
┌─────────────────▼───────────────────────┐
│      pgsquash-engine (Go library)       │
│  - SQL parsing (pg_query_go)            │
│  - Dependency resolution                │
│  - Migration consolidation              │
│  - Schema validation                    │
└─────────────────────────────────────────┘

Key Points:

capysquash-api is a separate repository for better modularity
It imports pgsquash-engine as a Go module dependency
Provides REST API access to pgsquash-engine functionality
Handles authentication, authorization, and API orchestration
Powers the CAPYSQUASH Platform backend

NEXT STEPS

CAPYSQUASH Platform API - Managed API access (recommended)
capysquash-cli Commands - Command-line usage
Configuration - Engine configuration
GitHub Integration - Platform GitHub features
pgsquash-engine Library - Go library usage

API Server (capysquash-api)

🚀 FEATURES

🎯 USE CASES

⚠️ IMPORTANT SECURITY NOTES

On this page