November 2, 20266 min read

What Is a Database Schema Plan? A Ptah Walkthrough

Follow a saved Ptah schema plan from an existing SQLite table through review, approval, application, and stale-plan recovery.

Simon HartSolutions Engineer

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

The users table already holds Ada and Lin. A release needs an optional email column, but we want to inspect the database change before it runs. A schema plan gives us the proposed SQL as a file. We can review and sign that file, then apply it separately.

We ran this example with Ptah 0.17.0 and SQLite 3.51.0. The example files contain the complete inputs, commands, recorded output, and a second database for the stale-plan test.

Planning and signing produce files. Apply is the step that changes the database.

Begin with the current and desired schemas

Our current table has a primary key and a display name. It holds two rows:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    display_name TEXT NOT NULL
);

The desired schema adds one nullable column:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    display_name TEXT NOT NULL,
    email TEXT
);

For a disposable example, we create the starting table with Ptah and insert the rows with SQLite:

ptah schema apply --db-url "sqlite://$PWD/app.db" --schema-file baseline.sql --auto-approve
sqlite3 app.db < seed.sql
sqlite3 -header -csv app.db 'SELECT id, display_name FROM users ORDER BY id;'

The initial query returned:

id,display_name
1,Ada
2,Lin

Here, the current schema is what is in app.db. The desired schema is schema.sql. The two rows are application data, not part of either schema file. The auto-approval flag above is used only to set up this disposable database.

Save the proposed change

Ptah compares the desired file with app.db and saves the difference:

ptah schema plan --db-url "sqlite://$PWD/app.db" --schema-file schema.sql --name add_email --output add_email.plan.json

The command printed:

Planned schema changes:
ALTER TABLE "users" ADD COLUMN "email" TEXT;
Plan saved to file://add_email.plan.json

The plan file records the SQL, the database dialect, a fingerprint of the starting schema, and Ptah’s assessment of the statement:

{
  "format_version": 1,
  "name": "add_email",
  "dialect": "sqlite",
  "from_fingerprint": "sha256:d6a6edc8cd51286701cb7b10cd1c55aa8a73ebb9936e1824e751ad98fdb89c9e",
  "to_fingerprint": "sha256:9b36e1913a0580ae95aa6d113d0c170f073491dc3e1e200aa56553d83169eac2",
  "destructive": false,
  "statements": [
    {
      "sql": "ALTER TABLE \"users\" ADD COLUMN \"email\" TEXT",
      "severity": "safe",
      "reason": "does not remove data or tighten constraints"
    }
  ]
}

The SQL adds email without rebuilding the table. The plan marks this statement safe because it does not remove data or tighten a constraint. That classification does not establish application compatibility or replace a review of the SQL.

The planned ALTER TABLE has not run yet. We read the table again after saving the plan; its schema and both rows matched the starting records byte for byte. The after-plan schema and after-plan rows are recorded beside the post.

Review and approve the file

A reviewer should inspect the plan and decide whether its SQL is acceptable for the application. For this disposable run, we made a throwaway SSH key and listed its public half as an allowed signer:

ssh-keygen -q -t ed25519 -N '' -C 'reviewer@example.com' -f reviewer_key
mkdir -p .ptah
awk '{print "reviewer@example.com namespaces=\"ptah-plan\" " $1 " " $2}' reviewer_key.pub > .ptah/allowed_signers
ptah schema approve --plan add_email.plan.json --key reviewer_key
ptah schema verify-approval --plan add_email.plan.json

Verification reported:

Approved by reviewer@example.com
Plan digest: 1112f32c66f9dd0c14c4475fb5576e8db8a9af71d60f58250964857f63e15940

The signature covers the plan file’s bytes. Changing the file after signing would make the approval fail. A signature identifies the allowed key; it does not prove that a person actually read the SQL. The approval guide covers signer management and the separate signature failure cases.

Apply the approved plan, then check the database

We now run the saved plan, requiring its signature:

ptah schema apply --db-url "sqlite://$PWD/app.db" --plan add_email.plan.json --require-approval --auto-approve

Ptah verified the signer and executed the one statement:

Plan approved by reviewer@example.com
Planned schema changes:
ALTER TABLE "users" ADD COLUMN "email" TEXT;
Auto-approval enabled; applying schema changes.
Schema apply completed successfully.

The auto-approval flag answers the interactive confirmation prompt. It does not bypass the signature check. We compared the resulting schema with schema.sql and read the existing rows:

ptah schema compare --db-url "sqlite://$PWD/app.db" --schema-file schema.sql --exit-code
sqlite3 -header -csv app.db 'SELECT id, display_name, email FROM users ORDER BY id;'

The comparison exited 0 and reported no difference. The two rows remain; a NULL check confirms both new email values are NULL:

id,display_name,email
1,Ada,
2,Lin,

The plan added a column. It did not backfill email addresses.

If the database changes before apply

A saved plan describes a particular starting schema. To test that boundary, we created another disposable database from the same baseline and added a support_note column outside Ptah:

sqlite3 drift.db < baseline.sql
sqlite3 drift.db < seed.sql
sqlite3 drift.db 'ALTER TABLE users ADD COLUMN support_note TEXT;'
ptah schema apply --db-url "sqlite://$PWD/drift.db" --plan add_email.plan.json --require-approval --auto-approve

The last command exited 2. The approval still verified, but the database no longer matched the plan’s source fingerprint:

Plan approved by reviewer@example.com
error: pre-planned migration is stale: the target database schema does not match the plan's source fingerprint

The complete refusal records both fingerprints. The email column was not added. This refusal is about the changed SQLite schema. It does not test executable-provider version binding, which is a separate check for provider-backed plans. We then asked Ptah what the old desired file would mean against this changed database:

ptah schema plan --db-url "sqlite://$PWD/drift.db" --schema-file schema.sql --name drift_recheck --dry-run

The dry-run plan was marked destructive. It included a table rebuild and DROP TABLE because schema.sql did not include support_note. Applying it would discard that independently added column. A stale-plan refusal is a reason to investigate the new state, not a reason to approve the next plan without reading it.

In this example, we decided to keep support_note. We put both columns in the desired schema:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    display_name TEXT NOT NULL,
    support_note TEXT,
    email TEXT
);

Planning again produced a new, non-destructive plan that only adds email:

ptah schema plan --db-url "sqlite://$PWD/drift.db" --schema-file schema-with-note.sql --name add_email_after_note --output drift.plan.json

Planned schema changes:
ALTER TABLE "users" ADD COLUMN "email" TEXT;
Plan saved to file://drift.plan.json

We inspected drift.plan.json, then signed it with the same reviewer key and allowed-signers file used above. We applied that exact plan to drift.db and checked it against the revised desired schema:

ptah schema approve --plan drift.plan.json --key reviewer_key
ptah schema apply --db-url "sqlite://$PWD/drift.db" --plan drift.plan.json --require-approval --auto-approve
ptah schema compare --db-url "sqlite://$PWD/drift.db" --schema-file schema-with-note.sql --exit-code
sqlite3 -header -csv drift.db 'SELECT id, display_name, support_note, email FROM users ORDER BY id;'

Approval wrote drift.plan.json.sig beside the new plan. Apply reported Plan approved by reviewer@example.com and completed successfully. The comparison exited 0 with No schema differences detected. The recorded apply output, comparison, and final rows show the result. The final schema retains support_note. The signature on add_email.plan.json covered the old plan’s bytes, so the new plan required a separate approval.

Where a schema plan fits

A saved schema plan is the proposed change between one desired schema and one observed database state. It is useful when the SQL should be reviewed before a direct schema apply. The fingerprint catches the schema change in this example. It is not a promise that application code, live traffic, or every data value will remain compatible. The direct-change guide explains the workflow and its limits.

A versioned migration is a different delivery choice: reviewed SQL lives in ordered migration files with recorded history. See our guide to reviewed migrations for that path. Ptah Operator has its own artifact and reconciliation lifecycle; our operator walkthrough follows it separately.

For this SQLite run, the practical result is specific: one approved ALTER TABLE added email, both rows remained, and a stale copy refused the old plan before we created and approved a new one.

Put Ptah to work

Example files

← All articles