GUIDE FOR BACKEND SPECIALISTS

How backend engineers and database specialists leverage capysquash for database optimization and performance.

WHY BACKEND SPECIALISTS USE capysquash

⚡ PERFORMANCE

Faster deployments, optimized migrations

🔧 CONTROL

CLI-first, scriptable, CI/CD ready

🧠 INTELLIGENCE

Deep analysis, dependency resolution

ADVANCED CAPYSQUASH WORKFLOWS

Deep Analysis

# Comprehensive analysis with AI
export ANTHROPIC_API_KEY="sk-ant-..."
capysquash analyze migrations/*.sql \
  --deep \
  --ai-analysis \
  --report-format json \
  --export analysis.json

Custom Safety Rules

{
  "safety_level": "custom",
  "rules": {
    "consolidate_creates": true,
    "consolidate_alters": true,
    "deduplicate_indexes": true,
    "deduplicate_functions": true,
    "optimize_rls": true,
    "remove_dead_code": false,
    "merge_grants": true
  },
  "conflict_resolution": {
    "duplicate_names": "error",
    "type_conflicts": "error"
  }
}

Streaming for Large Projects

# For 1000+ migration files
capysquash squash migrations/*.sql \
  --streaming \
  --memory-limit 1024 \
  --parallel 8 \
  --progress

DATABASE OPTIMIZATION PATTERNS

Index Deduplication

Problem: Multiple developers create similar indexes

-- Developer A
CREATE INDEX idx_users_email ON users(email);

-- Developer B (later)
CREATE INDEX idx_user_email_lookup ON users(email);

capysquash detects: Duplicate indexes

Solution: Consolidates to single index

Function Deduplication

Problem: Same logic, different names

CREATE FUNCTION update_timestamp_v1() ...
CREATE FUNCTION set_updated_at() ...
-- Both do the same thing

capysquash detects: Semantic duplicates with AI

RLS Policy Optimization

Problem: RLS policies added incrementally

-- Migration 1
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY posts_select ON posts FOR SELECT ...;

-- Migration 5
CREATE POLICY posts_insert ON posts FOR INSERT ...;

-- Migration 12
CREATE POLICY posts_update ON posts FOR UPDATE ...;

capysquash consolidates: All policies together after table creation

PERFORMANCE OPTIMIZATION

Deployment Speed Analysis

# Analyze deployment time impact
capysquash analyze migrations/*.sql --metrics deployment-time

# Output shows:
# Current deployment time: ~8 minutes
# After consolidation: ~45 seconds
# Savings: 7.25 minutes per deployment

Migration Size Optimization

# Check migration folder size
du -sh migrations/
# 45 MB

capysquash squash migrations/*.sql --output clean/
du -sh clean/
# 2.1 MB (95% reduction)

CI/CD INTEGRATION

GitHub Actions

name: Migration Quality Check

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

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

      - name: Install capysquash
        run: go install github.com/CAPYSQUASH/pgsquash-engine/cmd/pgsquash@latest

      - name: Analyze migrations
        run: |
          capysquash analyze migrations/*.sql --report-format json > analysis.json

      - name: Check consolidation opportunity
        run: |
          CONSOLIDATION=$(jq '.consolidation_percentage' analysis.json)
          if (( $(echo "$CONSOLIDATION > 50" | bc -l) )); then
            echo "::warning::Migrations have $CONSOLIDATION% consolidation opportunity"
          fi

      - name: Validate migrations
        run: capysquash validate migrations/

GitLab CI

migration-analysis:
  stage: test
  image: CAPYSQUASH/pgsquash:latest
  script:
    - capysquash analyze migrations/*.sql --report-format html --export analysis.html
  artifacts:
    paths:
      - analysis.html
    expire_in: 30 days

Pre-Commit Hook

#!/bin/bash
# .git/hooks/pre-commit

# Check if migrations changed
if git diff --cached --name-only | grep -q "migrations/"; then
  echo "Migrations changed, running analysis..."
  capysquash analyze migrations/*.sql

  # Optionally fail if too many opportunities
  POTENTIAL=$(capysquash analyze migrations/*.sql --report-format json | jq '.consolidation_percentage')
  if (( $(echo "$POTENTIAL > 70" | bc -l) )); then
    echo "ERROR: Consolidation opportunity >70%. Consider running capysquash."
    exit 1
  fi
fi

ADVANCED VALIDATION

Custom PostgreSQL Version

# Validate against specific Postgres version
capysquash validate migrations/ clean/ --postgres-version 15

Schema Diff Analysis

# Show detailed differences if validation fails
capysquash validate migrations/ clean/ --show-diff --verbose

Production Database Validation

# Compare against actual production schema
export PROD_DB_DSN="postgres://..."
capysquash validate-against-db migrations/ --db-dsn $PROD_DB_DSN

DEPENDENCY GRAPH ANALYSIS

Visualize Dependencies

# Export dependency graph
capysquash analyze migrations/*.sql --export-graph deps.dot

# Convert to SVG
dot -Tsvg deps.dot > dependencies.svg

Detect Cycles

# Find circular dependencies
capysquash squash migrations/*.sql --detect-cycles --cycle-details

MONITORING & METRICS

Track Migration Health

# Weekly analysis script
#!/bin/bash
DATE=$(date +%Y-%m-%d)
capysquash analyze migrations/*.sql --report-format json > "metrics/$DATE.json"

# Track over time
echo "$DATE,$(jq '.file_count' metrics/$DATE.json),$(jq '.consolidation_percentage' metrics/$DATE.json)" >> metrics.csv

Alert on Quality Degradation

# Alert if consolidation opportunity increases significantly
CURRENT=$(capysquash analyze migrations/*.sql --report-format json | jq '.consolidation_percentage')
THRESHOLD=60

if (( $(echo "$CURRENT > $THRESHOLD" | bc -l) )); then
  # Send alert (Slack, email, etc.)
  curl -X POST $SLACK_WEBHOOK -d "{\"text\":\"Migration consolidation opportunity: $CURRENT%\"}"
fi

DATABASE MIGRATION STRATEGIES

Blue-Green Deployments

# Squash for both environments
capysquash squash migrations/*.sql --output blue/
capysquash squash migrations/*.sql --output green/

# Deploy to blue first
# Switch traffic
# Then update green

Zero-Downtime Migrations

capysquash ensures migrations are safely ordered for zero-downtime deploys:

Non-blocking operations first
Index creation with CONCURRENTLY
No DROP COLUMN without notice
RLS policies after table structure

DEBUGGING & TROUBLESHOOTING

Verbose Output

capysquash squash migrations/*.sql --verbose --debug

Parse Error Investigation

# Find which file has issues
capysquash analyze migrations/*.sql --verbose 2>&1 | grep ERROR

# Test specific file
capysquash analyze migrations/problematic.sql --debug

Memory Profiling

# Track memory usage
/usr/bin/time -v capysquash squash migrations/*.sql --memory-limit 512

BEST PRACTICES

Regular consolidation - Monthly or quarterly
Validate always - Never skip validation
Conservative in prod - Use conservative or paranoid safety
Automate analysis - Add to CI/CD pipeline
Monitor metrics - Track migration health over time
Document changes - Clear commit messages

ADVANCED CONFIGURATION

Multi-Environment Setup

# Development
capysquash squash migrations/*.sql \
  --config dev.config.json \
  --safety aggressive

# Staging
capysquash squash migrations/*.sql \
  --config staging.config.json \
  --safety standard

# Production
capysquash squash migrations/*.sql \
  --config prod.config.json \
  --safety conservative \
  --validate

NEXT STEPS

🔄 CI/CD

Complete CI/CD setup

✨ BEST PRACTICES

Expert tips and patterns

📖 capysquash-cli REFERENCE

Complete command reference

Guide for Backend Specialists