fraiseql-confiture 0.3.13

PostgreSQL schema evolution with built-in multi-agent coordination 🍓

Confiture 🍓

PostgreSQL schema evolution with built-in multi-agent coordination

Confiture enables teams and AI agents to collaborate on database schema changes safely, with built-in multi-agent coordination and 4 flexible migration strategies for every scenario from local development to zero-downtime production deployments.

Part of the FraiseQL ecosystem - While Confiture works standalone for any PostgreSQL project, it's designed to integrate seamlessly with FraiseQL's GraphQL-first approach.

---

🍓 Part of the FraiseQL Ecosystem

confiture accelerates PostgreSQL schema evolution across the FraiseQL stack:

Server Stack (PostgreSQL + Python/Rust)

Tool	Purpose	Status	Performance Gain
pg_tviews	Incremental materialized views	Beta	100-500× faster
jsonb_delta	JSONB surgical updates	Stable	2-7× faster
pgGit	Database version control	Stable	Git for databases
confiture	PostgreSQL migrations	Beta	300-600× faster (theoretical)
fraiseql	GraphQL framework	Stable	7-10× faster
fraiseql-data	Seed data generation	Phase 6	Auto-dependency resolution

Client Libraries (TypeScript/JavaScript)

Library	Purpose	Framework Support
graphql-cascade	Automatic cache invalidation	Apollo, React Query, Relay, URQL

How confiture fits:

• Build from DDL → Fresh DB in <1s for fraiseql GraphQL testing
• pgGit automatically tracks confiture migrations
• Manage pg_tviews schema evolution with 4 migration strategies
• fraiseql-data seeds the schema confiture built

Intended workflow:

# Build schema from DDL files
confiture build --env test

# Seed test data
fraiseql-data add tb_user --count 100

# Run GraphQL tests
pytest

---

Why Confiture?

Safe Multi-Agent Collaboration 🤝

Working on database schemas with multiple agents or team members? Confiture provides automatic conflict detection:

# Agent 1: Declare intention to modify users table
confiture coordinate register --agent-id alice --tables-affected users

# Agent 2: Before modifying users table, check for conflicts
confiture coordinate check --agent-id bob --tables-affected users
# ⚠️ Conflict detected: alice is already working on 'users' table

Benefits:

• 🛡️ Prevent conflicts before code is written
• 👁️ Visibility into all active schema work
• 📋 Audit trail of coordination decisions
• 🤖 JSON output for CI/CD integration

Perfect for solo developers (optional safety), small teams (avoid surprises), and AI-assisted development (parallel agents).

→ Learn more about Multi-Agent Coordination

The Problem with Migration History

Traditional migration tools (Alembic, Django migrations, Flyway) use migration history replay: every time you build a database, the tool executes every migration file in order. This works, but it's slow and brittle:

• Slow: Fresh database builds take 5-10 minutes (replaying hundreds of operations)
• Brittle: One broken migration breaks everything - your database history is fragile
• Complicated: Developers maintain two things: current schema AND migration history
• Messy: Technical debt accumulates as migrations pile up over months/years

The Confiture Approach

Confiture flips the model: DDL source files are the single source of truth. To build a database:

1. Read all .sql files in db/schema/
2. Execute them once (in order)
3. Done ✅

No migration history to replay. No accumulated technical debt. Just your actual, current schema. Fresh databases in <1 second.

Intended Advantages Over Alembic

Feature	Confiture	Alembic	Notes
Fresh DB setup	Direct DDL execution	Migration replay	Theoretically faster
Zero-downtime migrations	Via FDW (planned)	Not built-in	Not yet production-tested
Production data sync	Built-in (with PII anonymization)	Not available	Not yet production-tested
Schema diffs	Auto-generated	Manual	Implemented
Conceptual simplicity	DDL-first	Migration-first	Different philosophy

What's Implemented

• ✅ 4 migration strategies (Build from DDL, ALTER, Production Sync, FDW)
• ✅ Python + optional Rust extension
• ✅ PII anonymization strategies
• ✅ Comprehensive test suite (3,200+ tests)
• ⚠️ Not yet used in production - Beta software

---

Core Features

🤝 Multi-Agent Coordination (NEW!)

Enable safe parallel schema development with automatic conflict detection:

# Register intention before making changes
confiture coordinate register \
    --agent-id alice \
    --feature-name user_profiles \
    --tables-affected users,profiles

# Check status and conflicts
confiture coordinate status --format json

# Complete when done
confiture coordinate complete --intent-id int_abc123

Key capabilities:

• ✅ Declare intentions before coding
• ✅ Automatic conflict detection (table, column, timing overlaps)
• ✅ Audit trail with rich terminal output
• ✅ JSON output for automation/CI-CD
• ✅ Performance: <10ms operations, 10K+ intents supported

→ Multi-Agent Coordination Guide | → Architecture Details

---

🛠️ Four Migration Strategies

Choose the right strategy for your use case:

1️⃣ Build from DDL - Execute DDL files directly (<1 second)

confiture build --env production

2️⃣ Incremental Migrations - ALTER-based changes for existing databases

confiture migrate up

3️⃣ Production Data Sync - Copy data with PII anonymization

confiture sync --from production --anonymize users.email

4️⃣ Schema-to-Schema (Zero-Downtime) - Complex migrations via FDW

confiture migrate schema-to-schema --strategy fdw

→ Migration Decision Tree

---

🔍 Git-Aware Schema Validation (NEW!)

Catch schema drift and enforce migration accompaniment before merging:

# Detect schema drift against main branch
confiture migrate validate --check-drift --base-ref origin/main

# Ensure DDL changes have corresponding migrations
confiture migrate validate --require-migration --base-ref origin/main

# Perfect for pre-commit hooks and CI/CD pipelines
confiture migrate validate --check-drift --require-migration --staged

Key capabilities:

• ✅ Detect untracked schema changes in code review
• ✅ Enforce migration files for every DDL change
• ✅ Pre-commit hook support (<500ms for staged files)
• ✅ CI/CD integration with JSON output
• ✅ Works with any git ref (branches, tags, commits)

Typical CI/CD workflow:

# GitHub Actions
- name: Validate schema changes
  run: |
    confiture migrate validate \
      --check-drift \
      --require-migration \
      --base-ref origin/main

→ Git-Aware Validation Guide | → CLI Reference

---

Quick Start

Installation

pip install fraiseql-confiture

# Or with FraiseQL integration
pip install fraiseql-confiture[fraiseql]

For Solo Developers (Traditional Workflow)

# 1. Initialize project
confiture init

# 2. Write schema DDL files
vim db/schema/10_tables/users.sql

# 3. Build database
confiture build --env local

# 4. Generate and apply migrations
confiture migrate generate --name "add_user_bio"
confiture migrate up

→ Getting Started Guide

For Teams & Multi-Agent Work (Coordination Workflow)

# 1. Initialize project with coordination database
confiture init
confiture coordinate init --db-url postgresql://localhost/confiture_coord

# 2. Register intention before making changes
confiture coordinate register \
    --agent-id alice \
    --feature-name user_profiles \
    --tables-affected users,profiles \
    --schema-changes "ALTER TABLE users ADD COLUMN bio TEXT"

# 3. Check for conflicts (by other agent)
confiture coordinate check \
    --agent-id bob \
    --tables-affected users
# ⚠️ Warning: alice is working on 'users' table

# 4. View active work
confiture coordinate status --format json

# 5. Complete when done
confiture coordinate complete --intent-id int_abc123

→ Multi-Agent Coordination Guide

Project Structure

db/
├── schema/           # DDL: CREATE TABLE, views, functions
│   ├── 00_common/
│   ├── 10_tables/
│   └── 20_views/
├── seeds/            # INSERT: Environment-specific test data
│   ├── common/
│   ├── development/
│   └── test/
├── migrations/       # Generated migration files
└── environments/     # Environment configurations
    ├── local.yaml
    ├── test.yaml
    └── production.yaml

Test Migrations Before Applying (Dry-Run)

Analyze migrations without executing them:

# Analyze pending migrations
confiture migrate up --dry-run

# Test in SAVEPOINT (guaranteed rollback)
confiture migrate up --dry-run-execute

# Save analysis to file
confiture migrate up --dry-run --format json --output report.json

# Analyze rollback impact
confiture migrate down --dry-run --steps 2

For more details, see Dry-Run Guide.

---

Documentation

Getting Started

• Getting Started Guide - First steps with Confiture
• When to Use Coordination? - Solo vs. team vs. multi-agent

Multi-Agent Coordination

• Multi-Agent Coordination Guide - Complete guide to coordination features
• Architecture & Design - Technical architecture details
• Performance Benchmarks - Performance analysis & results
• CI/CD Integration - JSON output for automation

Migration Strategies

Core Strategies:

• Build from DDL - Execute DDL files directly
• Incremental Migrations - ALTER-based changes
• Production Data Sync - Copy and anonymize data
• Zero-Downtime Migrations - Schema-to-schema via FDW
• Migration Decision Tree - Choose the right strategy

Advanced:

• Dry-Run Mode - Test migrations before applying
• Migration Hooks - Execute custom logic before/after migrations
• Anonymization - PII data masking strategies
• Compliance - HIPAA, SOX, PCI-DSS, GDPR
• Integrations - Slack, GitHub Actions, monitoring

API Reference

• CLI Reference - All commands documented
• Configuration - Environment configuration
• Schema Builder API - Building schemas programmatically
• Migrator API - Migration execution
• Syncer API - Production data sync
• Hook API - Migration lifecycle hooks
• Anonymization API - PII data masking
• Linting API - Schema validation rules

Examples

• Examples Overview - Complete examples
• Multi-Agent Workflow - Coordination examples (NEW!)
• Basic Migration - Learn the fundamentals
• FraiseQL Integration - GraphQL workflow
• Zero-Downtime - FDW-based migration
• Production Sync - PII anonymization

---

Features

🤝 Multi-Agent Coordination (Production-Ready)

• ✅ Intent registration - Declare changes before implementation
• ✅ Conflict detection - Automatic overlap detection (table, column, timing)
• ✅ Status tracking - Real-time visibility into active schema work
• ✅ Audit trail - Complete history of coordination decisions
• ✅ JSON output - CI/CD and automation integration
• ✅ High performance - <10ms operations, 10K+ intents supported
• ✅ 123 comprehensive tests - All passing, production-ready

🛠️ Migration System (Implemented)

• ✅ Build from DDL (Strategy 1) - Execute DDL files directly (<1s)
• ✅ Incremental migrations (Strategy 2) - ALTER-based changes
• ✅ Production data sync (Strategy 3) - Copy with PII anonymization
• ✅ Zero-downtime migrations (Strategy 4) - Schema-to-schema via FDW

🔧 Developer Experience

• ✅ Optional Rust extension for performance
• ✅ Schema diff detection with auto-generation
• ✅ CLI with rich terminal output
• ✅ Multi-environment configuration
• ✅ Migration hooks (pre/post execution)
• ✅ Schema linting with multiple rules
• ✅ PII anonymization strategies
• ✅ Dry-run mode for testing migrations

📖 Documentation (Comprehensive)

• ✅ Coordination guides - Multi-agent workflows, architecture, performance
• ✅ Migration guides - All 4 strategies documented
• ✅ API reference - Complete CLI and Python API docs
• ✅ Integration guides - CI/CD, Slack, GitHub Actions, monitoring
• ✅ Compliance guides - HIPAA, SOX, PCI-DSS, GDPR
• ✅ Examples - 5+ complete examples including multi-agent workflows

---

Comparison

Feature	Alembic	pgroll	Confiture
Philosophy	Migration replay	Multi-version schema	Build-from-DDL + Coordination
Multi-agent coordination	❌ No	❌ No	✅ Built-in
Fresh DB setup	Minutes	Minutes	<1 second
Zero-downtime	❌ No	✅ Yes	✅ Yes (FDW)
Production sync	❌ No	❌ No	✅ Built-in
Conflict detection	❌ No	❌ No	✅ Automatic
CI/CD integration	Basic	Basic	✅ JSON output
Language	Python	Go	Python + Rust

---

Current Version

v0.3.9 (Latest) - January 27, 2026

✨ What's New in v0.3.9

Migration File Validation & Auto-Fix:

• ✅ New confiture migrate validate command with auto-fix
• ✅ Orphaned migration file detection (missing .up.sql suffix)
• ✅ Safe auto-fix with --fix-naming flag
• ✅ Dry-run preview mode with --dry-run
• ✅ JSON output for CI/CD integration
• ✅ Comprehensive "Migration Naming Best Practices" guide (500+ lines)
• ✅ 8 new tests covering all scenarios

Previous Release - v0.3.8: Multi-Agent Coordination (Production-Ready)

• ✅ 7 CLI commands (confiture coordinate register/check/status/complete/abandon/list/conflicts)
• ✅ Automatic conflict detection (table, column, function, constraint, index, timing)
• ✅ JSON output for CI/CD integration
• ✅ 123 comprehensive tests (all passing)
• ✅ Performance: <10ms operations, 10K+ intents supported
• ✅ Complete documentation (3,500+ lines)

⚠️ Beta Software: While the multi-agent coordination system is production-ready and thoroughly tested, Confiture has not yet been used in production environments. Real-world usage may reveal edge cases. Use with appropriate caution.

What's Implemented

• ✅ Multi-agent coordination with conflict detection
• ✅ All 4 migration strategies (Build from DDL, ALTER, Production Sync, FDW)
• ✅ Comprehensive test suite (3,200+ migration tests, 123 coordination tests)
• ✅ Complete documentation and guides
• ✅ Python 3.11, 3.12, 3.13 support
• ✅ Optional Rust extension
• ✅ Migration hooks, schema linting, anonymization strategies

What's NOT Validated

• ❌ Production usage (never deployed to production)
• ❌ Performance claims (benchmarks only, not real-world workloads)
• ❌ Edge cases under load (not battle-tested at scale)
• ❌ Large-scale data migrations (theoretical performance)

---

Contributing

Contributions welcome! We'd love your help making Confiture even better.

Quick Start:

# Clone repository
git clone https://github.com/fraiseql/confiture.git
cd confiture

# Install dependencies (includes Rust build)
uv sync --all-extras

# Build Rust extension
uv run maturin develop

# Run tests
uv run pytest --cov=confiture

# Format code
uv run ruff format .

# Type checking
uv run mypy python/confiture/

Resources:

• CONTRIBUTING.md - Contributing guidelines
• CLAUDE.md - AI-assisted development guide
• PHASES.md - Detailed roadmap

What to contribute:

• 🐛 Bug fixes
• ✨ New features
• 📖 Documentation improvements
• 💡 New examples
• 🧪 Test coverage improvements

---

Author

Vibe-engineered by Lionel Hamayon 🍓

Confiture was crafted with care as the migration tool for the FraiseQL ecosystem, combining the elegance of Python with the performance of Rust, and the sweetness of strawberry jam.

---

License

MIT License - see LICENSE for details.

Copyright (c) 2025 Lionel Hamayon

---

Acknowledgments

• Inspired by printoptim_backend's build-from-scratch approach
• Built for FraiseQL GraphQL framework
• Influenced by pgroll, Alembic, and Reshape
• Developed with AI-assisted vibe engineering ✨

---

FraiseQL Ecosystem

Confiture is part of the FraiseQL family:

• FraiseQL - Modern GraphQL framework for Python
• Confiture - PostgreSQL migration tool (you are here)

---

Making jam from strawberries, one migration at a time. 🍓→🍯

Vibe-engineered with ❤️ by Lionel Hamayon

Documentation • GitHub • PyPI