# Reproduce the HCI schema handover

Schema commands rerun on October 1, 2026 with Ptah Compat 0.11.3, PostgreSQL
15.19, and pgvector 0.8.6. The deployment proposal uses the official 0.11.2
image; the independent lint proposal uses 0.11.3. The original Atlas Community
1.3.1 control and 0.11.1 deployment records are retained as historical evidence.

This directory contains the article commands, SQL checks, measured output,
and provenance. The full third-party schema and data migrations stay in the
linked repository. We measured the fork's actual migration image and the
CLI commands shown in the article separately.

## Inputs

| Input | Fixed source |
| --- | --- |
| Original HCI schema, extras, and data migration `035` | [tomturing/hci-troubleshoot-platform at 4ca96cb](https://github.com/tomturing/hci-troubleshoot-platform/tree/4ca96cbf27f1d8d24c63429a596849a97ab9011e) |
| Combined desired state and deployment | [stokaro/showcase-hci-troubleshoot-platform at 4292620](https://github.com/stokaro/showcase-hci-troubleshoot-platform/tree/4292620b90d1790bfc93baf042101d82b1f14685) |
| Ptah Compat | [Ptah 0.11.3 release](https://github.com/stokaro/ptah/releases/tag/v0.11.3), Linux AMD64 archive checked against the release's `checksums.txt` |
| PostgreSQL with pgvector | `pgvector/pgvector@sha256:a947c45cdc5906a1bc951f20a8709e321256343ee0f251e4ae00b5e7def4e6da` |
| Atlas control | `arigaio/atlas@sha256:76cc66fb31f99d5d316676b8a9866ef4b6d6bf5aacc0639485d04848670839e4` |

The combined file retains the original table and index definitions, with
updated comments and added extension, function, and trigger declarations.
The original `database/desired_schema.sql` SHA-256 is
`fc13f5ebb4bd4a97e228ea32fcbed94b39d2fd35b4e35a3636e655b83ae21d36`.
The combined desired state has SHA-256
`f71bdaaccb6e08ce322e3b86f08409f89778576acbf192d1a52d7d31a066f62c`.
`verified.json` records the release, binary, and example file hashes.
`measured/version.txt` records the released binary's version output.

## Run the article commands

Check out the fork at the fixed commit above. Run from its root, where
`database/desired_schema.sql` exists. Install the released `ptah-compat`
binary and make it available on `PATH`.

Use an empty, disposable PostgreSQL 15 target and a separate empty scratch
database on a server with pgvector, pgcrypto, uuid-ossp, and pg_trgm available.
`DATABASE_URL` names the target; `DEV_URL` names the scratch database. The
connection role must be able to create extensions. Both databases and their
contents are disposable: the drift example intentionally removes objects.

The recorded CLI run used Docker context `remote-dev-container`, a server
with no published port, and UID `65534:65534`. The CLI connected over container
loopback. The temporary server's PostgreSQL data lived on tmpfs.

Run `commands/apply.txt`. It enables authored index storage parameters, including
`lists=100`, and allows unmatched names in HCI's historical tool-table exclusions.
An empty database has none of those tool tables, so the CLI warns on stderr.
`measured/diff.txt` contains stdout; its separate stderr file records the warning.
Keep those options enabled for every schema command in the reproduction.

Run `sql/catalog.sql` and `sql/behavior.sql` with psql's `ON_ERROR_STOP=1`.
The behavior file uses a transaction and rolls back its fixture rows. It checks
the object inventory, pgvector cosine distance, SQL NULL default, user timestamp
updates, advancing case IDs, and message INSERT/DELETE counting exactly once.
The default check evaluates the catalog expression. The original explicit
`DEFAULT NULL` is stored as `NULL::character varying`; a nonempty expression
there is valid, while the text value `'NULL'` fails the check.

Record `sql/object-identities.sql` with psql's unaligned, tuples-only output.
Repeat the apply command, then record the same query again.
`measured/identities-before.txt` and `measured/identities-after.txt` are identical:
all four function OIDs, 24 trigger OIDs, and both vector-index OIDs remain.
Run `commands/diff.txt`; its stdout reports synchronized schemas.

Execute `sql/drift.sql`, run the same apply command, and repeat the catalog,
behavior, and diff checks. `measured/repair-apply.txt` contains the repair plan
and execution output. `measured/repaired-diff.txt` contains the final result.

`commands.json` records all 14 steps: arguments, environment variable names,
working directory, exit code, timestamp, duration, and stream hashes. The
argument records replace the disposable password with `[REDACTED]`. Stdout and stderr are kept separately. The catalog output files remove trailing
whitespace and the final empty line; both raw and recorded hashes remain.
All other output files contain exact streams.
Fresh apply printed 226,199 bytes of planned and applied DDL. Its full output
is omitted to avoid duplicating the project's schema; its hash and the catalog
and behavior checks remain recorded.

## Deployment and upgrade verification

The current [schema PR #1107](https://github.com/tomturing/hci-troubleshoot-platform/pull/1107)
uses `stokaro/ptah:0.11.2` pinned to multi-platform index
`sha256:0013ca27cdf4443b130e516b572a0739cbc0802d907790813a682fc91974cf98`.
It copies the compatibility binary into the existing PostgreSQL image and
includes the original MIT license. The upstream DB workflow tests fresh
installation, repeat apply, and an upgrade from each PR's base revision.

The [0.11.2 deployment run](https://github.com/stokaro/showcase-hci-troubleshoot-platform/actions/runs/36749035889)
and [extended proof run](https://github.com/stokaro/showcase-hci-troubleshoot-platform/actions/runs/36748430251)
passed. `measured/deployment-0.11.2.json` records their source revisions and
corresponding commits after commit-message cleanup. The extended proof covers
upgrade data and migration history, object identities, drift repair, original
Helm initialization, legacy and future CHECK definitions, and cross-schema
column lookup. Its scripts remain on a separate fork proof branch.

### Historical 0.11.1 evidence

The records below describe the original 0.11.1 experiment. They are not
relabeled as 0.11.3 deployment results.

`measured/fork-results.json` and `measured/fork-verification.txt` record the
complete migration-image verification. The fork's
[DB Schema verification workflow](https://github.com/stokaro/showcase-hci-troubleshoot-platform/actions/runs/36707773944) passed at `2dbc048e70eae5782dc600800bbfa6dbeeadaa38`.

The workflow builds the `schema-test` target of `Dockerfile.migrations` and
runs as the deployment UID. It verifies fresh installation, business trigger
behavior, a repeat with zero diff and stable object identities, and repair of
extension, function, trigger, and vector-index drift.

The upgrade starts from the unchanged original schema and extras. It adds
representative user and bundle rows and obsolete Alembic triggers, executes the
original migration `035`, and records its original checksum. The new deployment
retains those rows and that history entry, repairs interrupted jobs, removes
duplicate counting triggers, and converges. A second upgraded deployment
preserves all 30 object identities.

An independent psql baseline uses the unchanged upstream SQL. `sql/columns.sql`
reads every column's type, default, and nullability in a stable order.
`measured/column-comparison.json` records comparisons of all 1,225 columns:
article apply, migration-image fresh install, upgrade, and native Ptah apply
have zero differences. Both IVFFlat index definitions also match the original
catalog, including their operator class, filter, and `lists=100`.

The fork's [validation notes](https://github.com/stokaro/showcase-hci-troubleshoot-platform/blob/2dbc048e70eae5782dc600800bbfa6dbeeadaa38/docs/solution/database/ptah-compat-validation.md)
explain the integration changes and how to run the migration-image verification.
The [workflow](https://github.com/stokaro/showcase-hci-troubleshoot-platform/blob/2dbc048e70eae5782dc600800bbfa6dbeeadaa38/.github/workflows/db-migration-test.yml)
holds the complete runner configuration and original upgrade inputs.

## Current schema comparison

`measured/column-comparison-0.11.3.json` records the new comparison against the
unchanged upstream SQL executed directly by PostgreSQL. All 1,225 column types,
defaults, and nullability values agree. Both IVFFlat index definitions agree.
The 14 commands in `commands.json` and their associated output files are from
the new 0.11.3 run against the current schema PR, including behavior checks,
30 unchanged object IDs on repeat apply, and repair followed by zero diff.

## Independent migration lint

[PR #1108](https://github.com/tomturing/hci-troubleshoot-platform/pull/1108)
is independent of the schema PR. Its
[fork workflow](https://github.com/stokaro/showcase-hci-troubleshoot-platform/actions/runs/36756730566)
passed with the official 0.11.3 image pinned to
`sha256:6249ff98606735997fe1dcd3dcaac4aace1091e5766153c7df29387922e066c9`.
It runs native `ptah migrations lint` on added or modified files using
`--git-base`, outside the checkout directory to avoid loading deployment
`atlas.hcl`. It emits GitHub annotations and fails on error-level findings.

A separate release-binary check was repeated on October 1: unchanged unsafe
history was ignored; a new SQL prompt containing literal `{{ ... }}` passed;
new and modified `DROP TABLE` statements failed with `DS101`; GitHub error
annotations were emitted. `measured/lint-0.11.3.json` records these outcomes.
This checks static DDL risks, not unscoped UPDATE/DELETE or PL/pgSQL idempotence.
Both upstream PRs were open when this update was prepared.

## SQL retained since v0.11.1

The 0.11.0 rehearsal needed SQL workarounds for Ptah defects. Version 0.11.1
fixes those defects. The fork now retains the original integer widths, varchar
lengths, `CURRENT_TIMESTAMP` defaults, `DEFAULT NULL`, and function return-type
modifier. Migration `040` has no column-widening statements.

The remaining changes have deployment reasons:

- Combine object declarations so one desired state owns extensions, tables,
  indexes, application functions, and triggers.
- Remove duplicate application DDL from initialization and the extras calls
  from deployment so those definitions have one owner.
- Move existing extras repairs to versioned migration `040` so removing that
  file does not remove data conversions or obsolete-trigger cleanup.
- Use `CREATE OR REPLACE TRIGGER` in migration `035` so a fresh schema's existing
  trigger does not collide with its historical creation step. Upgraded databases
  retain the original executed version's checksum and skip it by version.
- Use the same entrypoint in Compose, Helm, and Make, preserving scratch rehearsal,
  pgvector index parameters, and data-before-constraints upgrade order.

## Atlas Community control

The control uses the pinned official Atlas Community 1.3.1 image. Both its target
and scratch database have the four required extensions preinstalled. A new
`schema apply` reads the exact same combined desired-state bytes used by
Ptah 0.11.1. It exits successfully with 76 tables and no application functions
or triggers. The resulting catalog and output hash are recorded in
`measured/fork-results.json`.

The measurements cover schema and migration behavior. They make no upstream
adoption, application-load, embedding-provider, Kubernetes, or production
claim. Remove the disposable databases and resources you create when finished.