October 8, 20265 min read

Customer's Case Study: Fewer Baseline Steps with Ptah

A customer's Atlas migration experiment showed where Ptah removes manual baseline work, adds SQL review, and needs care before replacing a custom runner.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A customer had started moving PostgreSQL migrations to a customized Atlas runner. Their SQL history was already in place. Creating a baseline still required separate work for functions, extensions, and views, while deployment depended on the runner’s retry behavior and service integration.

They wanted to know what Ptah would add and how much of that existing work could stay. We assessed the migration experiment, found useful improvements in baseline export and SQL review, and fixed two Ptah defects exposed by valid PostgreSQL operations. Our recommendation was to introduce baseline export and CI review first, then qualify replacement of the deployed runner.

The customer is unnamed. Every table, object name, and row in the examples below is synthetic. The examples use PostgreSQL 14.24 and a Ptah build containing the merged fixes described here. The supporting files contain those fixtures and their recorded results.

Bring the baseline objects into one export

The customer’s baseline instructions called for manually adding functions and extensions and using a separate tool to export views. That reflects a boundary of Atlas Community Edition, which omits those database objects. Atlas’s standard distribution supports more objects; this assessment compared the customer’s existing workflow.

Ptah can read those objects through the same schema inspection. In the synthetic example, a member directory uses a view, and updates invoke a function through a trigger. The database also enables the uuid-ossp extension.

From the example directory, with DATABASE_URL pointing to the disposable database, the export is:

ptah-compat schema inspect --url "$DATABASE_URL" --schema public --format '{{ sql . }}' > public-baseline.sql

The resulting SQL includes:

Object	Synthetic example
View	`public.member_directory`
Function	`public.touch_member()`
Trigger	`members_touch` on `public.members`
Extension	`uuid-ossp`

These definitions travel with the tables and constraints. Developers can review one export instead of assembling the baseline from separate outputs. Schema inspection exports definitions; it does not copy the sample member rows or replace a data backup.

Review a migration without starting a development database

The customer’s documented Atlas lint workflow used a development PostgreSQL instance. Ptah’s offline SQL lint offers an additional review step before that instance is available.

Consider a synthetic migration that adds a required reference:

ALTER TABLE public.members ADD COLUMN external_ref uuid NOT NULL;

This SQL can look straightforward in an empty database. On a populated table, existing rows have no value for the new column. Run the native linter against the PostgreSQL version the migration targets:

ptah migrations lint --dir ./lint-migrations --dialect postgres --server-version 14

The recorded warning gives a sequence the reviewer can act on:

DD101: adding NOT NULL column external_ref without a DEFAULT fails or blocks on populated tables; add it nullable, backfill, then enforce NOT NULL in a later migration

The recommendation is to add a nullable column, fill existing rows, and require values in a later migration. Offline analysis identifies a risk in the statement; it does not inspect production rows or predict deployment duration. We recommended reviewing findings on changed migrations and choosing the CI failure threshold deliberately.

Follow an index through PostgreSQL’s rename

The compatibility assessment also exposed a Ptah error. PostgreSQL can rename an existing index when a constraint attaches it under a different name. The SQL is valid:

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS members_email_build ON public.members (email);
ALTER TABLE public.members
    ADD CONSTRAINT members_email_key UNIQUE USING INDEX members_email_build;

After attachment, the index is named members_email_key. Ptah previously checked the recorded name members_email_build, reported it missing, and failed after PostgreSQL had committed the valid constraint.

We fixed the migration observer to follow the resulting index identity. The observer still checks the owning table and schema and refuses an unusable index. Tracking a rename must preserve that validation.

The synthetic history includes this operation. With the corrected build, the same migration directory applies successfully:

ptah-compat migrate apply --dir file://baseline-migrations --url "$DATABASE_URL"

Catalog inspection confirms that the constraint and its valid index share the name members_email_key. Repeating apply completes cleanly. The customer’s migration SQL could stay as written.

Keep foreign keys with their owning schema

Another failure appeared during whole-database SQL export. Tables in different schemas can share a name, and their foreign keys can share a constraint name. The synthetic fixture uses composite foreign keys. Each belongs to a distinct table:

Owning table	Constraint	Referenced table
`public.members`	`members_department_fk`	`public.departments`
`support.members`	`members_department_fk`	`support.departments`

Ptah’s renderer could associate a constraint with the wrong same-named table or emit it twice. The older synthetic export contained a duplicate foreign-key declaration, so PostgreSQL refused to restore it. We fixed owner resolution so validation and SQL rendering keep the schema-qualified table identity.

The synthetic fixture reproduces this layout. A whole-database inspection now keeps each foreign key with its original table:

ptah-compat schema inspect --url "$DATABASE_URL" --format '{{ sql . }}' > baseline.sql

We restored that SQL into another disposable database. Both foreign keys rejected missing parent rows, the unique constraint rejected duplicate emails, and Ptah’s schema comparison reported no changes. These checks use only synthetic rows.

The recommendation the customer received

After the fixes, the customer’s migration histories replayed successfully without rewriting the SQL, repeat application completed cleanly, and the whole-database export succeeded. That made reuse of the existing SQL a practical option. It did not settle the deployment contract of the custom runner.

We recommended this sequence:

Step	What the customer gains	What must be settled
Use Ptah for baseline export	Fewer separate exports and manual additions	Review the resulting baseline definitions
Add Ptah SQL lint to CI	Actionable prompts about data preparation and locking	Tune findings and thresholds for changed migrations
Qualify runner replacement	Reuse of the existing SQL history	Custom retries, health checks, logging, deployment integration, and existing revision history

The customer received a route to reduce manual work immediately. The later runner decision depends on recovery behavior and integration, including handover of an existing production revision table. Fresh replay alone does not establish that handover. Equivalent coverage in the customer’s private linter was outside the supplied implementation, so we did not claim that these review checks were exclusive to Ptah.

Put Ptah to work

Example files

← All articles