Metadata-Version: 2.5
Name: coloph-migrations
Version: 1.1.2
Summary: Agent-friendly PostgreSQL migration CLI extracted from Coloph
License-Expression: GPL-3.0-only
License-File: LICENSE
Requires-Python: >=3.12
Requires-Dist: psycopg[binary]>=3.2
Requires-Dist: python-dotenv>=1.0
Requires-Dist: testcontainers[postgres]>=4.10
Description-Content-Type: text/markdown

# Coloph Migrations

`coloph-migrate` is a PostgreSQL migration CLI built for coding agents. It pairs
goal-driven project skills with numbered SQL migrations, checksum checks,
schema reconstruction, and deployed-code compatibility tests. Agents use the
CLI to inspect, change, and diagnose schemas instead of writing migration
machinery. The CLI is the public interface; Python modules are internal.

## Quick start

Add it to the repository's development dependencies (and commit the updated
`pyproject.toml` and lockfile):

```sh
uv add --dev coloph-migrations
```

The lockfile records the exact package version. To run a fixed version without
adding a dependency, replace `VERSION` in this command:

```sh
uvx --from 'coloph-migrations==VERSION' coloph-migrate --help
```

Initialize the migration files at the repository root:

```sh
uv run coloph-migrate init
```

This command creates these files if they do not exist:

```text
coloph-migrations.toml
migrations/0001_init.sql
.env
skills/change-database-schema/SKILL.md
skills/repair-database-schema/SKILL.md
```

`init` installs the bundled skills in the host project's `./skills` directory.
Configure your agent to discover that directory or load these files through
the project's agent instructions. File installation alone does not guarantee
automatic discovery in every agent host. Existing identical skills are left
alone. If a skill differs, `init` fails before writing files. Reconcile or move
that file before retrying, including when updating skills from a new release.
Existing application-specific skills, such as `db-schema`, remain separate.

The skills cover schema changes and failure diagnosis. CLI and configuration
details belong in this README and command help, not in skill descriptions.

The generated configuration contains:

```toml
migrations_dir = "migrations"
schema_snapshot = "migrations/schema.sql"
```

Set `DATABASE_URL` in `.env`. Process environment variables override `.env`.
The `COLOPH_MIGRATIONS_DATABASE_URL` variable remains available as a higher-priority override.

Edit the initial migration, inspect it, and apply it:

```sh
printf 'CREATE TABLE account (id bigint PRIMARY KEY);\n' > migrations/0001_init.sql
uv run coloph-migrate plan
uv run coloph-migrate apply
uv run coloph-migrate snapshot
```

Create each later migration with a normalized name. The command verifies the
existing sequence and writes the next padded number:

```sh
uv run coloph-migrate new add_accounts
# creates migrations/0002_add_accounts.sql
```

Use a project-specific starter file with `new add_accounts --template path/to/template.sql`.

Use an ignored `coloph-migrations.local.toml` for local credentials and
overrides. `COLOPH_MIGRATIONS_DATABASE_URL` keeps the URL out of files and
process arguments.

## Common workflows

```sh
# Show applied and pending migrations
uv run coloph-migrate list

# Create the next numbered migration
uv run coloph-migrate new add_accounts

# Execute the selected migration chain in disposable PostgreSQL before applying it
uv run coloph-migrate dry-run
# Require that every migration is applied and its checksum still matches
uv run coloph-migrate check

# Verify migration history, the committed snapshot, and the current target
uv run coloph-migrate verify

# Repair diagnostic: compare the target with its recorded migration prefix
uv run coloph-migrate validate --match-applied

# Regenerate schema.sql from a disposable reconstruction
uv run coloph-migrate snapshot --fresh

# Check a new migration number against main and deployed Git refs
uv run coloph-migrate check-chain

# Run deployed code against the new schema before deployment
uv run coloph-migrate check-backwards
```

`--json` is a global option. Put it before the command:

```sh
uv run coloph-migrate --json list
```

Use `apply --up-to 012` to apply
versions before `012` (the boundary is exclusive). `apply --reconstruction` enables
only the disposable-database policies configured for reconstruction.

## Hooks

Configure hooks when migrations need setup or follow-up work that is not part of
the migration SQL itself:

```toml
before_each_migration_sql = "migrations/before_each.sql"
after_each_migration_sql = "migrations/after_each.sql"
post_max_attempts = 5
post_statement_timeout_seconds = 30
post_lock_timeout_seconds = 10
reconstruction_after_hook_versions = ["0186"]
```

The pre-migration hook runs inside each migration transaction. If it fails, the
migration rolls back. The post-migration hook runs after that migration commits,
in a separate transaction. A failed post hook remains marked incomplete; the
next `apply` retries incomplete hooks before it runs a new migration or reports
success. Hook SQL must therefore support safe retries. `post_max_attempts`
controls retries during one run, and `post_statement_timeout_seconds` controls
the hook statement timeout.

Reconstruction runs post hooks at configured checkpoint versions and once after
the selected schema is rebuilt. If a post-hook file is missing, restore it or
configure the correct `after_each_migration_sql` path, then run `apply` again.
If the hook SQL changed, restore the version that safely completes the pending
work (or make the replacement safely retryable) before applying new migrations.

## What it prevents

| Problem | Example | Guardrail |
| --- | --- | --- |
| Edited history | An applied migration is changed, renamed, or removed | `plan` and `check` reject the invalid history. |
| Bad ordering | A branch adds `007_*.sql` while `main` already has `007_*.sql` | `check-chain` detects collisions across refs. |
| Failed SQL | A migration's second statement fails | The runner rolls back its transaction; earlier migrations remain committed. See transaction-control limitations below. |
| Schema drift | A snapshot or target differs from executable migrations | `verify` reconstructs and compares all three schemas. |
| Transaction escape | A migration commits with `END` or another transaction command | `dry-run` compares PostgreSQL transaction IDs around every migration. |
| Unsafe checksum repair | Someone wants to accept modified applied SQL | `repair-checksums` requires schema equivalence first. |
| Incompatible deployed code | New schema breaks a tested query in deployed code | Configured `check-backwards` tests exercise deployed code against the final rebuilt schema. |

## Result boundaries and recovery

`plan` reports pending files and rejects checksum mismatches; it does not run
migration SQL. `list` reports `applied`, `pending`, `orphan`, `renamed`, and
`checksum_mismatch`. `check` rejects every non-applied status. Status commands
can create the migration tracking table; `verify` requires the table to exist
and does not create it.

Run `verify` before deployment. It fails unless the complete migration history
is current and its rebuilt canonical schema matches both `schema_snapshot` and
the target database. It reports each mismatch as a unified diff, ignores only
`-- schema-doc:` snapshot comments, and changes neither the snapshot nor target.
Use `validate --match-applied` only to diagnose a target at its recorded prefix.
Neither command compares data contents.

`repair-checksums --dry-run` previews updates after comparing the target schema
with the full reconstructed chain. Schema equality does not prove equivalent
data transformations. Do not replace this check with direct tracking-table
updates or treat ordinary pending migrations as checksum repairs.

The runner owns each migration transaction. Do not include transaction control
in migration SQL. Before target changes, `apply` runs the selected chain in a
disposable database and rejects migrations that change their transaction.

The before hook runs inside the migration transaction. The after hook runs
after that migration and its record commit, in a separate transaction. If the
after hook fails on the target, the migration remains applied and its hook is
marked incomplete. A later `apply` retries incomplete hooks before new work.

`check-backwards` can return `skipped`; that is not a passed compatibility test.
It detects added SQL files between the deployed revision and committed HEAD,
then tests deployed code against a reconstruction from the current files.
Use a committed, clean candidate for deployment checks. Tests cover the final
schema, not each intermediate prefix or the production data set.

If migrations run while old code serves traffic, first deploy code that stops
using an object. Drop or rename that object in a later deployment, after the
compatible code is actually deployed. A commit on main alone is insufficient.

## Configuration

```toml
migrations_dir = "migrations"
schema_snapshot = "migrations/schema.sql"
database_url = "postgresql://postgres:postgres@localhost:5432/app"
main_ref = "main"
deployed_ref = "deployed"
deployed_fetch_remote = "origin" # optional; refresh deployed_ref before backwards check

# Optional. The before file runs in the migration transaction. During normal
# apply, the after file runs in a separate transaction after each migration is
# recorded and committed. Failed after hooks are retried before later applies
# continue. During reconstruction, the after file runs at any configured
# checkpoint versions and once after the selected schema is fully rebuilt.
before_each_migration_sql = "migrations/before_each.sql"
after_each_migration_sql = "migrations/after_each.sql"

# Disposable-reconstruction options.
fresh_statement_timeout_seconds = 90
reconstruction_after_hook_versions = ["0186"]

# Optional. Fresh databases use local Docker when this environment variable is
# absent or set to "local-docker". A PostgreSQL URL selects a shared cluster;
# non-loopback URLs must use sslmode=verify-full. Loopback URLs can use the
# caller's SSL mode so an authenticated local TCP proxy remains transparent.
test_cluster_url_env = "TEST_POSTGRES_CLUSTER_DSN"
```

Explicit CLI flags override configuration files.

The local TOML file overrides the base TOML file. Database URL resolution uses
this order: CLI flag, process `COLOPH_MIGRATIONS_DATABASE_URL`, process
`DATABASE_URL`, the same two names in `.env`, then the merged TOML value.
Relative TOML paths and `.env` resolve from the configuration directory.
These details identify which database a command will use; credentials and
cluster provisioning remain the host project's responsibility.

Fresh databases use Docker by default. A configured remote cluster must permit
temporary database creation and deletion. Schema dumps still use Docker with
a PostgreSQL client image, even when the temporary database is remote.

Configure backwards tests to accept the disposable database URL, for example:

```toml
backwards_setup_command = ["uv", "sync"]
backwards_test_command = ["uv", "run", "pytest"]
backwards_test_globs = ["tests/test_database_*.py"]
backwards_database_url_env = "TEST_DATABASE_URL"
```

The deployed test harness must use that environment variable. Test setup and
test selection belong to the consuming project.

Snapshot files can contain `-- schema-doc:` comments immediately before the SQL
statement they describe. Regeneration restores these notes when the following
statement line still matches. They do not become live database metadata.

## Command reference

```text
coloph-migrate init
coloph-migrate new NAME [--template PATH]
coloph-migrate apply
coloph-migrate dry-run
coloph-migrate list
coloph-migrate plan
coloph-migrate check
coloph-migrate snapshot
coloph-migrate verify
coloph-migrate validate
coloph-migrate repair-checksums
coloph-migrate check-chain
coloph-migrate check-backwards
```

Put global `--json` before the command, for example `coloph-migrate --json list`.

`apply --reconstruction` applies the selected migration prefix, runs the configured after
hook at explicit checkpoint versions, and then runs it once against the rebuilt
schema. This keeps historical reconstructions from repeatedly validating every
intermediate schema while preserving known migration-chain dependencies.
Ordinary production `apply` remains fail-loud and keeps per-migration after
hooks. It first performs the same migration chain as `dry-run`, so a failed
disposable run leaves the target database unchanged.

Removed reconstruction settings and compatibility paths now fail as unknown
configuration keys. Failed migrations always stop the run.

## Coloph dependency workflow

When Coloph needs a `coloph-migrations` behavior change, edit this package
directly in its local checkout, test it here, commit and push the package
change, then update Coloph's pinned Git dependency and lockfile to that exact
commit. Do not patch installed site-packages or work around dependency behavior
inside Coloph.

The test suite deliberately exercises broken numbering, explicit transaction
control, failed migration rollback, pre/post-hook transaction boundaries,
checksum drift, schema drift, and safe-versus-unsafe checksum repair.

## Public writing

Do not publish links or issue references to private repositories.
This rule applies to source files, documentation, issues, pull requests, comments, and release notes.
Explain each problem with a self-contained example, the actual result, the expected result, and the practical impact.
Separate proposed features from observed defects. Do not present missing tests alone as a defect.

## License

GPL-3.0-only. The Coloph name and logo are not licensed for use as trademarks.
