# Reproduce the Flyway handoff

Verified on September 24, 2026 with Flyway OSS 13.7.0, Ptah 0.8.0, and
PostgreSQL 18.6. PostgreSQL and Flyway ran on Docker context
`remote-dev-container`; the released Ptah CLI connected through a local SSH
forward. `measured/flyway-image.json` records the exact Flyway image identity.

The fixture has two versioned Flyway migrations and one repeatable view. Four
application rows are inserted separately from the migration history. The
final `migrations/` directory contains the imported versions and the new Ptah
migration, with its native checksum file.

## Run the source migrations

Prepare an empty application database and configure Flyway:

| Setting | Recorded value |
| --- | --- |
| `FLYWAY_URL` | JDBC URL for the disposable `flyway_live` database |
| `FLYWAY_USER`, `FLYWAY_PASSWORD` | Disposable database credentials |
| `FLYWAY_DEFAULT_SCHEMA` | `flyway` |
| `FLYWAY_SCHEMAS` | `flyway,public` |
| SQL files | Contents of `source/`, copied into the container's `/flyway/sql/` |

For a local CLI, set `FLYWAY_LOCATIONS` to `filesystem:source` instead. The
recorded default container location emitted a deprecation warning; the
migrations still executed successfully.

```console
flyway -v
flyway migrate
flyway info
```

Run `seed.sql` with PostgreSQL's `psql` client. The measured command used
`psql -v ON_ERROR_STOP=1` against this database. The application view initially
returns products 1 and 2. Flyway's history table is in `flyway`; application
objects are in `public`.

This layout was configured before the original Flyway deployment. Do not move
an existing history table merely to reproduce this layout. If your history
shares an application schema, establish the intended comparison scope and
review its effect before adopting that database.

## Import and baseline

Copy `source/` into a new working directory. Let Ptah create a new
`migrations/` directory rather than importing over the committed final fixture.
`DB_URL` is a PostgreSQL URL for the existing database, and `SHADOW_URL` names
a different empty database that can be reset.

```console
ptah migrations import --from flyway --source-dir source --migrations-dir migrations --dry-run
ptah migrations import --from flyway --source-dir source --migrations-dir migrations
ptah migrations baseline --db-url "$DB_URL" --migrations-dir migrations --version 3 --schemas public --shadow-db "$SHADOW_URL" --dry-run
ptah migrations baseline --db-url "$DB_URL" --migrations-dir migrations --version 3 --schemas public --shadow-db "$SHADOW_URL"
ptah migrations status --db-url "$DB_URL" --migrations-dir migrations --verify-sum
```

The dry run previews metadata; the actual baseline performs the shadow replay
and comparison. Version 3 is the imported repeatable's new Ptah version, not
a Flyway version. Inspect the import result when adopting a different history.

`verify-history.sql` confirms the four product rows and original successful
Flyway entries remain. The shadow replay logs show migrations being applied
to the shadow database. The target gains Ptah's `schema_migrations` records
without executing the imported up SQL there.

The imported down files contain `-- No rollback was provided by the source
migration.` They do not undo these objects. This example does not claim that
importing a Flyway history invents rollback behavior.

## Verify a changed repeatable is detected

Use a separate empty database, `flyway_boundary`, with the same Flyway schema
settings. Run the original source and seed first. Its availability view
returns IDs 1 and 2.

Then point Flyway's locations at `source-changed/` and run `flyway migrate`
again. Only the repeatable's predicate differs: `stock >= 5`. Flyway reapplied
that file and the view returned only ID 1. The measured outputs include both
queries and the source tool's second migration run.

Attempt the Ptah baseline with the original imported history, `--version 3`,
and this separate database as the target. It must fail its shadow comparison.
Our run returned exit 2 with a view-body mismatch between `stock >= 5` and
`stock > 0`. `to_regclass('public.schema_migrations') IS NULL` then returned
true: the failed adoption did not leave a native revision table.

After a handoff, change that view through a new Ptah migration. The imported
ordinary version 3 is not a Flyway repeatable runner.

## Generate the next migration

The desired SQL in `schema-v2.sql` adds a per-product reorder threshold and
uses it in the availability view. Normalize its view definition through
PostgreSQL before generating the change:

```console
ptah schema inspect --schema-file schema-v2.sql --dev-url "$SHADOW_URL" --schemas public --format hcl > schema-v2.hcl
ptah migrations generate --db-url "$DB_URL" --schema-file schema-v2.hcl --schemas public --migrations-dir migrations --name reorder_threshold --shadow-db "$SHADOW_URL"
```

Both commands can reset the disposable shadow database. The first writes
the normalized desired state locally before the second uses the database.

`measured/generate.txt` records the unsuccessful direct-SQL attempt: the
generated SQL ran, but the final view comparison rejected PostgreSQL's
rewritten body as different. No new migration files were published by that
failed generation. The HCL form from native inspection passed the shadow
check, including rollback and reapplication of the new migration.

The successful run produced version `1790263096`; another run will use its
own timestamp. The committed up/down pair is the exact generated output.
`expected/new-change.sql` is the statement excerpt used in the article.

## Hash, lint, apply, and replay

Review the generated files, then update the directory checksum. The recorded
attempt to lint before updating it refused the two unlisted new files. The
following sequence passed:

```console
ptah migrations hash --dir migrations
ptah migrations lint --dir migrations --dialect postgres --latest 1 --fail-on error
ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum
ptah schema compare --db-url "$DB_URL" --schema-file schema-v2.hcl --schemas public --exit-code
ptah migrations status --db-url "$DB_URL" --migrations-dir migrations --verify-sum
```

The new migration had no lint findings. The existing target applied one
pending migration. `application-query.sql` returned all four product rows
with `reorder_level = 5`, and only `KIT-42` from the view.

Use a third empty database, `FRESH_URL`, to verify a new installation:

```console
ptah migrations up --db-url "$FRESH_URL" --migrations-dir migrations --verify-sum
ptah schema compare --db-url "$FRESH_URL" --schema-file schema-v2.hcl --schemas public --exit-code
ptah migrations status --db-url "$FRESH_URL" --migrations-dir migrations --verify-sum
```

All four migrations applied. Both schema comparisons returned zero and both
status checks reported no pending migrations. The fresh database has no
product rows because the application seed is not part of the migration
history; matching schema does not copy production data.

The generated new down migration was tested only by the shadow round trip.
It removes the reorder column and therefore discards values stored there.
That is separate from the imported files' missing rollback definitions.

`verified.json` binds the examples and article commands to this run. Captured
text has trailing whitespace removed and local paths replaced with
`/examples/`; command records redact the disposable database password. Original
hashes are recorded for normalized output. The files cover SQL migrations,
this repeatable, and the described database scope. They do not establish
compatibility for Java migrations, callbacks, placeholders, or other Flyway
execution features. Remove the disposable resources after reproducing the run.