# Evolve and gate a direct schema

Change an applied desired schema, review the ALTER plan, and gate CI on schema drift.

Source: https://docs.ptah.run/v0.8.0/start/quick-start-direct/

import { Tabs, TabItem } from '@astrojs/starlight/components';

Use this page after the [default quick start](../quick-start/). It continues
from that tutorial's `schema.sql` and `app.db`, and rebuilds them below if you
have already cleaned up. You will add a column, inspect the `ALTER TABLE` Ptah
derives, apply it, and prove the database has not drifted from the file.

## Prerequisites

- Your terminal is in `ptah-quick-start`.
- `app.db` contains the `users` table from the default quick start.
- `schema.sql` still describes that table.

If you already removed the directory, rebuild that state here rather than
working through the quick start again. The commands are its first and third
steps:

<Tabs syncKey="shell">
<TabItem label="Bash">

```bash
mkdir ptah-quick-start
cd ptah-quick-start
cat > schema.sql <<'SQL'
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
SQL
ptah schema apply --schema-file schema.sql --db-url sqlite://app.db --auto-approve
```

</TabItem>
<TabItem label="PowerShell">

```powershell
New-Item -ItemType Directory ptah-quick-start | Out-Null
Set-Location ptah-quick-start
@'
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
'@ | Set-Content schema.sql
ptah schema apply --schema-file schema.sql --db-url sqlite://app.db --auto-approve
```

</TabItem>
</Tabs>

## 1. Change the desired schema

Replace `schema.sql` with this version:

<Tabs syncKey="shell">
<TabItem label="Bash">

```bash
cat > schema.sql <<'SQL'
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,
    email      TEXT NOT NULL,
    created_at TEXT
);
SQL
```

</TabItem>
<TabItem label="PowerShell">

```powershell
@'
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,
    email      TEXT NOT NULL,
    created_at TEXT
);
'@ | Set-Content schema.sql
```

</TabItem>
</Tabs>

You changed the destination shape. Ptah derives the SQL needed to reach it from
the live database.

## 2. Review the evolution plan

```console
ptah schema apply --schema-file schema.sql --db-url sqlite://app.db --dry-run
```

Expected output on standard output:

```text
Planned schema changes:
ALTER TABLE "users" ADD COLUMN "created_at" TEXT;
```

The plan alters the existing table; it does not create another table or replay
the original statement.

## 3. Apply and verify the change

```console
ptah schema apply --schema-file schema.sql --db-url sqlite://app.db --auto-approve
ptah schema drift --schema-file schema.sql --db-url sqlite://app.db
```

Expected output on standard output:

```text
Schema apply completed successfully.
No schema drift detected.
```

:::caution
`--auto-approve` is appropriate for this disposable SQLite file. Review a saved
plan or use an interactive approval before applying to a shared database.
:::

## 4. Read the column back from the database

The apply reported success and drift reported none. Both are Ptah's own
verdicts; this reads the table itself:

```console
ptah db read --db-url sqlite://app.db
```

Expected output includes, on standard output:

```text
CREATE TABLE "users" (
  "id" INTEGER PRIMARY KEY,
  "email" TEXT NOT NULL,
  "created_at" TEXT
);
```

The column is there, in `app.db` rather than in `schema.sql`.

## 5. Use drift as a CI gate

The same check belongs in automation, with the URL coming from the
environment: `ptah schema drift --schema-file schema.sql --db-url "$DATABASE_URL"`.

Branch on the exit status, not on captured prose:

| Exit | Meaning |
| --- | --- |
| `0` | The live database matches the desired schema. |
| `1` | Drift exists. The report includes the highest severity and findings. |
| `2` | Ptah could not decide because the invocation, source, or connection failed. |

[CI](../../testing/ci/) shows the GitHub Action and shell forms. [Compare and
detect drift](../../direct/compare-and-drift/) covers severities, formats, and
remediation paths.

## Clean up

<Tabs syncKey="shell">
<TabItem label="Bash">

```bash
cd ..
rm -rf ptah-quick-start
```

</TabItem>
<TabItem label="PowerShell">

```powershell
Set-Location ..
Remove-Item -Recurse -Force ptah-quick-start
```

</TabItem>
</Tabs>

## Next steps

- [Save, sign, and verify a plan](../../direct/plan-and-approve/).
- [Adopt an existing database](../adopt-an-existing-database/).
- [Generate versioned migration files](../../versioned/generate/) from the same
  desired schema.