September 30, 20269 min read

Replacing HCI's Atlas Workaround with Ptah Compat

We replaced HCI's split Atlas and psql schema deployment with one Ptah Compat desired state, preserving functions, triggers, and pgvector indexes.

Nathan BrooksTechnical Writer

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

hci-troubleshoot-platform is a troubleshooting platform with PostgreSQL 15 and pgvector. Its database deployment had to apply a SQL file before Atlas changed the tables, then apply that file again afterward. The project’s own write-up explains why: Atlas Community left extensions, functions, and triggers outside its desired-state management.

We tried our Atlas-compatible CLI on the project’s actual schema. In our fork, desired_schema.sql now declares the extensions, functions, and triggers alongside the tables and indexes. Ptah Compat creates the complete state, restores deliberately removed objects, and leaves existing functions, triggers, and vector indexes in place on a repeated deployment. With Ptah 0.11.1, the original table and index SQL stays unchanged. The integration changes move object ownership and preserve HCI’s data repairs.

We reran the schema examples with Ptah Compat 0.11.3, PostgreSQL 15.19, and pgvector 0.8.6. The original comparison used Atlas Community 1.3.1, the version in HCI’s migration image. The supporting files hold the inputs, recorded output, and steps to repeat the comparison.

The proposed handover is now upstream PR #1107, with its rationale in issue #1106. That PR uses the official 0.11.2 Docker image. A separate PR adds migration SQL lint with 0.11.3. Both proposals are open; the project has not adopted either change yet.

The workaround HCI documented

HCI’s Atlas Community limitation report describes the split. The deployment used PostgreSQL initialization for extensions, desired_schema.sql for tables and indexes, and desired_extras.sql for most functions and triggers. Its recurring schema sequence was:

psql desired_extras.sql
atlas schema apply desired_schema.sql
psql desired_extras.sql

This kept the application objects available, but it gave them different deployment rules. Functions were replaced by psql. Triggers were dropped and created again, including when their definitions had not changed. Atlas’s schema result did not cover the complete database that the application used.

We checked that boundary with Atlas CE 1.3.1. We supplied the combined object declarations and preinstalled the extensions in the control databases. The command succeeded and created 76 tables. PostgreSQL’s catalog contained no application functions or triggers afterward.

Objects after schema apply	Atlas CE 1.3.1 control	Ptah Compat 0.11.3
Tables in `public`	76	76
Application functions	0	4
Enabled application triggers	0	24

The four extensions were already present in the Atlas control. Ptah created them from the desired schema on an empty target. That distinction matters when comparing the results: a successful table plan did not establish ownership of the other objects.

Put the objects in the desired schema

Our fork adds these declarations below the schema file’s header:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;

We append the timestamp functions, case-ID generator, message counter, and their triggers from the extras file. Moving these declarations gives the planner the complete application schema. There is no separate desired_extras.sql to apply. The pgvector extension supplies the vector type, while the file retains both IVFFlat indexes, their cosine operator class, and lists=100. The combined file retains the original table and index definitions.

From the fork’s root, with a disposable target in DATABASE_URL and a separate scratch database in DEV_URL, we ran:

export PTAH_POSTGRES_INDEX_STORAGE_PARAMS=1
export PTAH_ATLAS_ALLOW_UNMATCHED_EXCLUDE=1
ptah-compat schema apply \
  --url "$DATABASE_URL" \
  --to file://database/desired_schema.sql \
  --dev-url "$DEV_URL" \
  --exclude "schema_migrations,alembic_version,atlas_schema_revisions" \
  --auto-approve

The PostgreSQL server must have the extension binaries available, and the database role needs permission to install them. Ptah rehearses the plan in the scratch database before applying it to the target. The migration image sets the same options and applies this desired schema.

PTAH_POSTGRES_INDEX_STORAGE_PARAMS=1 keeps authored index options such as pgvector’s lists=100 in the comparison. The unmatched-exclusion option allows HCI’s historical tool-table exclusions on a fresh database. Those tables may be absent; the CLI reports that as a warning.

The resulting catalog had all four declared extensions, four application functions, and 24 enabled triggers. Both vector indexes were valid. Their definitions retained vector_cosine_ops; the knowledge-entry index also retained its filter for published entries.

Copy the compatibility binary from the image

The deployment PR uses stokaro/ptah:0.11.2, pinned by its multi-platform image digest. Since 0.11.2, the official image includes ptah-compat alongside ptah and ptah-ls. HCI’s migration image copies /usr/local/bin/ptah-compat from that build stage, keeping its existing PostgreSQL base image and mirror configuration. It no longer needs a separate release-archive download step. The migration image also includes Ptah’s MIT license.

Copying the same binary to /usr/local/bin/atlas instead preserves the atlas command name for a drop-in replacement. We use ptah-compat explicitly in the proposal so readers can see which tool runs each command. HCI’s existing atlas.hcl remains in place.

Keep HCI’s original SQL

Our first rehearsal with Ptah 0.11.0 exposed defects in our handling of valid PostgreSQL SQL. We fixed them in Ptah 0.11.1 and reran the handover with the released binary. The application no longer needs the SQL workarounds used in that rehearsal.

bundle_metadata.kbd_id stays integer, even though it references a bigint key. The category references stay varchar(32) against a varchar(64) key. PostgreSQL accepts these foreign keys; Ptah’s former exact-width requirement was too strict. The timestamp defaults stay CURRENT_TIMESTAMP, and pending_variable_name keeps DEFAULT NULL. Our reader now retains valid keyword syntax and distinguishes SQL NULL from the text value 'NULL'.

generate_case_id() also keeps RETURNS varchar(20). PostgreSQL discards function type modifiers. Ptah now compares the stored routine type accordingly, so retaining that declaration does not cause repeated replacement. Column lengths still take part in schema comparison.

An independent catalog comparison checked all 1,225 columns against the unchanged upstream SQL executed directly by PostgreSQL. The 0.11.3 schema run had zero differences in types, defaults, or nullability, matching the original fresh-install and upgrade measurements. Both vector-index definitions matched the original catalog. These checks let us remove the column-widening statements from the fork’s data migration.

Repeat without replacing the triggers

After applying the schema, we compared the database with the same file:

ptah-compat schema diff \
  --from "$DATABASE_URL" \
  --to file://database/desired_schema.sql \
  --dev-url "$DEV_URL" \
  --exclude "schema_migrations,alembic_version,atlas_schema_revisions"

Schemas are synced, no changes to be made.

We ran the apply command again. All four application functions, 24 triggers, and both vector indexes kept their PostgreSQL object IDs. The declarations agreed with the live schema, so these objects were left in place.

Catalog counts alone would miss a trigger with the wrong behavior. Our SQL checks updated a user row and verified its timestamp, generated successive case IDs, and inserted and deleted a conversation message. The message count increased exactly once and returned to zero after deletion.

Repair the same objects from the same file

In the disposable database, we removed a timestamp trigger, a vector index, and an extension, then replaced the case-ID function with a broken body:

DROP TRIGGER update_user_updated_at ON "user";
DROP INDEX idx_kb_category_embedding;
DROP EXTENSION pg_trgm;
CREATE OR REPLACE FUNCTION generate_case_id() RETURNS varchar
LANGUAGE sql AS $$ SELECT 'broken'::varchar $$;

We ran the same ptah-compat schema apply command. It restored the extension, trigger, function, and vector index from desired_schema.sql. The behavioral checks passed again, and the next schema diff reported synchronized schemas.

The repair did not require running the old extras file. Its object definitions now participate in the plan that also manages the tables.

Preserve the data repairs

The extras file held data changes as well as schema objects. Removing the file without preserving those changes would lose HCI’s interrupted-job repair and cleanup of obsolete Alembic triggers. Those old triggers could double the conversation message counter.

We moved these repairs into versioned data migration 040. This keeps the existing data conversion and obsolete-object cleanup when the extras file goes away. It contains no Ptah-specific column widening. The existing migration runner records the version and skips it on later runs.

The repair expands legacy CHECK constraints only when their old definitions need it. A fresh database already has the complete desired schema; replaying an older CHECK definition there could reject values used by a later data migration. This guard preserves HCI’s repair without narrowing newer constraints.

Migration 035 already creates the bundle-metadata trigger. We changed its declaration to CREATE OR REPLACE TRIGGER so it can run after the complete schema has installed that trigger. The upgrade test executed the original 035 and recorded its original checksum first. The new deployment preserved that history row and skipped the already applied version. The remaining older data migrations and the historical Atlas migration files are unchanged.

The migration entrypoint gives a fresh database its complete schema before data migrations, because those migrations need the tables to exist. An existing database runs data repairs before final schema reconciliation, so repaired rows meet the new constraints. Compose, Helm, and the Make targets call this same entrypoint. PostgreSQL initialization no longer carries duplicate application DDL; the desired schema owns those objects. The separate scratch database and the server’s pgvector installation remain in place.

We tested the upgrade path by loading the unchanged upstream schema and extras, adding representative existing rows and obsolete triggers, and running the fork’s migration image. User and bundle data survived, interrupted jobs were repaired, and message counting worked exactly once. A repeat run produced no schema diff and preserved function, trigger, and vector-index identities.

The upstream proposal keeps fresh-install, repeat-apply, and upgrade checks in the database workflow. The upgrade starts from the PR’s base revision, so it continues to check schema transitions after the initial Atlas handover.

Check migration SQL separately

PR #1108 adds a separate CI job using the native ptah migrations lint command from 0.11.3. It checks added or modified data migrations against the PR’s base revision, reports findings as GitHub annotations, and fails on errors such as DROP TABLE. Unchanged historical migrations do not block the PR. This job can be adopted independently of the schema handover.

HCI stores prompt text containing literal {{ ... }} in SQL. Version 0.11.3 fixes its mistaken recognition as a Go template, so linting needs no prompt rewrites. The job checks static DDL risks; it does not prove that an UPDATE or DELETE is properly scoped, or that PL/pgSQL and dynamic SQL are idempotent. Database tests and review still cover those behaviors.

The schema proposal gives the project one file to review for PostgreSQL object changes while retaining its versioned data migrations. The reproduction notes explain how to run the fork against disposable databases and inspect the result before considering a handover.

Put Ptah to work

Example files

← All articles