# onwardpg documentation

Source: https://onwardpg.solberg.is/index.md

onwardpg turns your desired PostgreSQL schema into SQL for a rolling deployment.
It generates **expand** SQL to run before the application deploys and **contract**
SQL to run after the old processes have drained. It verifies both in disposable
PostgreSQL databases. Your deployment system runs the SQL in production.

Jump to the [quick start](#quick-start), or read on for the deployment problems
it addresses.

## Why do I need this?

Locally, you can stop the app, migrate the database, and restart it. During a
rolling deployment, old and new instances share the same database. A worker
may still be processing yesterday's job while the new release serves requests.
A migration can execute successfully and still break those processes.

These changes need more than a single schema cutover:

- **Renaming a column.** Rename `display_name` to `full_name` before deploying,
  and old queries fail. Deploy first, and new queries fail. For supported
  same-type renames, onwardpg adds a second column and a trigger that keeps
  writes through either name synchronized. You choose how to backfill existing
  rows. Contract checks that the values agree and removes the bridge after old
  callers drain.
- **Adding a required field.** `status text NOT NULL` can reject inserts from
  old code that doesn't supply a status. Expand adds it as nullable. The new
  release writes the field and tolerates nulls during rollout; contract runs
  your cleanup, checks for remaining nulls, and applies `NOT NULL`.
- **Changing allowed values.** Suppose a CHECK accepts `email` and `slack`,
  while the new release writes `slack` and `push`. Either endpoint constraint
  rejects one version's writes. Expand can remove the old check; contract uses
  your rule for retired values, checks the data, and restores enforcement.
  That temporarily weakens the database guarantee, which the plan reports.
- **Changing a type beneath views and indexes.** Converting `age text` to
  `age integer` needs a rule for blanks and malformed values, a way to support
  both application versions, and work on dependent views. A materialized view
  may also need rebuilding or refreshing. onwardpg identifies those
  dependencies, asks for the conversion and compatibility SQL, and verifies
  the completed bundle.

The order is **expand → deploy → drain → contract**. The new application must
handle the overlap schema and the final schema, including values old writers
can still produce. If that cannot fit one deployment, split the change across
releases. onwardpg asks for the missing decisions and SQL; it cannot infer
application behavior or a backfill's business meaning from the schema.

## Quick start

### Install

```sh
brew install jokull/tap/onwardpg
onwardpg version
```

This is a preview release. [Release archives](https://github.com/jokull/onwardpg/releases)
and [installation from Go or source](https://github.com/jokull/onwardpg/blob/main/docs/installation.md)
are also available.

You'll need PostgreSQL 15–18 for scratch databases. Use a dedicated local or CI
cluster: onwardpg creates and drops databases and temporary login roles there.
The scratch login needs permission to manage both. Never use a production or
shared application cluster for scratch.

For a local walkthrough with Docker:

```sh
docker run --name onwardpg-scratch \
  -e POSTGRES_PASSWORD=onwardpg \
  -p 127.0.0.1:55432:5432 -d postgres:18

export ONWARDPG_SCRATCH_DATABASE_URL='postgres://postgres:onwardpg@localhost:55432/postgres'
```

Wait for PostgreSQL to accept connections before continuing.

### Describe the starting schema

In an empty project directory, create `schema.sql`:

```sql
CREATE SCHEMA app;

CREATE TABLE app.bookings (
  id bigint PRIMARY KEY
);
```

Add `.onwardpg.toml` alongside it:

```toml
version = 1
bundle_root = "migrations/onward"

[targets.app]
schema_file = "schema.sql"
scratch_database_env = "ONWARDPG_SCRATCH_DATABASE_URL"
```

Check the configuration and record the starting schema:

```sh
onwardpg config check
onwardpg init
```

`init` creates and verifies the baseline migration history. In an existing
project, initialize from the schema you have **before** the feature change.
It does not change your application database.

### Add a column

Suppose bookings now need a status. Replace `schema.sql` with:

```sql
CREATE SCHEMA app;

CREATE TABLE app.bookings (
  id bigint PRIMARY KEY,
  status text
);
```

The file always describes the complete desired schema. Run:

```sh
onwardpg plan add-booking-status
```

The bundle is written to `migrations/onward/app/add-booking-status/`. Its
`plan.json` lists the statements and hazards; `phases/expand.sql` contains:

```sql
ALTER TABLE "app"."bookings" ADD COLUMN "status" text;
```

There is no contract phase for this change. Old code can keep inserting rows
without a status. Check that inserts name their columns and that readers can
handle an extra result column if they use `SELECT *`.

### Review and verify

Read the generated SQL and hazards, then run:

```sh
onwardpg verify
```

Verification replays the migration in disposable databases, runs its assertions,
and checks that the final schema matches `schema.sql` with no remaining diff.
Unresolved SQL placeholders fail verification.

Commit the schema, configuration, and generated migration files together. In CI,
select the bundle explicitly and check its saved verification evidence:

```sh
onwardpg verify --bundle add-booking-status --check
```

Use the same PostgreSQL major for scratch and production. Scratch also needs
the roles, languages, and extension packages referenced by your schema. Project
SQL runs as a restricted database owner, so extensions requiring superuser
execution need separate provisioning.

## Required columns

Before merging the feature, suppose you decide every booking must have a status.
Change the column in `schema.sql` to `status text NOT NULL` and rerun:

```sh
onwardpg plan
```

This revises the same bundle. Expand still adds a nullable column: the old
application will omit `status` until it drains. The new application must write
a status and tolerate nulls when reading during that overlap.

The planner asks how those nulls will be resolved. Choose `assert_only` if
another operation will fill them, `manual_sql` to supply cleanup in this plan,
or `split_plan` to defer enforcement to a later release.

For this example, assume old bookings should become `pending` and the table is
small enough for one update. Supply that decision:

```sh
onwardpg plan \
  --hint '{"kind":"reconcile","object":"column","name":["app","bookings","status"],"strategy":"manual_sql"}' \
  --hint '{"kind":"manual_sql","action":"reconcile_contract_sql","object":"column","name":["app","bookings","status"]}'
```

The command reports `needs_sql_edits` and names the file to edit. In
`phases/contract.sql`, replace the placeholder **inside the edit markers** with:

```sql
UPDATE "app"."bookings"
SET "status" = 'pending'
WHERE "status" IS NULL;
```

Keep the surrounding generated SQL. After this cleanup it checks that no nulls
remain, then enforces the constraint:

```sql
ALTER TABLE "app"."bookings" ALTER COLUMN "status" SET NOT NULL;
```

Run `onwardpg verify` again. The cleanup rule is yours to review; the schema
cannot tell onwardpg what an old booking means. For a large table, use an
`operator_batched` operation with a completion check, or split the work across
releases. See the [contract reference](https://github.com/jokull/onwardpg/blob/main/docs/contract-readiness.md)
for those options.

## Day-to-day use

Run `onwardpg plan` as you edit the schema. It updates the active migration
instead of adding a migration for every draft. After rebasing onto new migration
history, run it again. Decisions and SQL edits carry forward when they still
apply; changes to their underlying schema can require another answer or review.

```sh
onwardpg plan       # Revise the active plan
onwardpg status     # Inspect it
onwardpg verify     # Verify the current files
```

For renames, type conversions, and destructive changes, follow the questions in
`next_actions`. Answer with the reported `--hint` and edit only the named SQL
sections. Exit code `2` means the plan needs input or SQL edits. A rename needs
an explicit identity decision; a type conversion needs a rule for the data.
Adding another compatible column can introduce a new rename candidate, so a
previous rename decision may need confirming again. That keeps a changed set
of possible identities from silently reusing an earlier answer. Use the complete
suggested command (`argv`) for each answer: it can carry confirmed decisions
that have not yet been saved while your edited SQL waits for the remaining choices.

### Use your framework's schema

Instead of maintaining SQL by hand, export your framework's complete desired
PostgreSQL DDL. Replace `schema_file` with a command that writes it to stdout:

```toml
schema_command = ["pnpm", "--silent", "schema:export"]
```

`schema:export` is a script you provide. It must produce the same DDL for the
same models, including everything needed to build the schema from empty.
See the [schema input requirements](https://github.com/jokull/onwardpg/blob/main/docs/schema-inputs.md)
and [existing-project recipes for Drizzle, Prisma, and Django](https://github.com/jokull/onwardpg/tree/main/examples/frameworks).
Choose one executor for production DDL; running both framework migrations and
the onwardpg bundle would apply the same change twice.

Adopt from an already-deployed schema: export the old models and run `init`
before the feature edit. Keep existing framework migration history, but do not
replay the onwardpg baseline against an existing database. Django's example
exports migration state, so run `makemigrations` after model changes and use
reviewed state-only migrations when onwardpg takes over their DDL.

### Connect a development database

Optionally add these fields to `[targets.app]`:

```toml
dev_database_env = "ONWARDPG_DEV_DATABASE_URL"
dev_mode = "workspace"
```

Set that environment variable to your development database URL. onwardpg reads
its catalog and reports separate SQL to bring it up to date. In workspace mode,
objects left by another branch are preserved. Migration history still comes
from accepted bundles. The development and scratch databases must use the same
PostgreSQL major.

This comparison has its own `--dev-hint` decisions. A ready durable bundle can
still return exit code `2` because local reconciliation needs an answer; inspect
the `durable`, `development`, and `next_actions` sections separately.

### Work with a coding agent

Give the agent <a href="/skill.md">the onwardpg skill</a>:

```text
Read and follow https://onwardpg.solberg.is/skill.md.
Plan this schema change, answer decisions from the application code,
verify the bundle, and report the remaining deployment checks.
```

## Deployment

Each bundle surrounds one application deployment:

```text
expand → deploy application → drain old processes → contract
```

1. **Apply expand** before deploying the new code. Review lock, scan, index-build,
   and transaction requirements in the plan. Keep nontransactional batches out
   of transactions.
2. **Deploy the application.** It must work both before and after contract.
3. **Drain old processes.** Include workers, scheduled jobs, queues, connection
   pools, and any other client that could still write using the old schema.
4. **Check readiness, then apply contract.** Finish the specified data cleanup
   and require a ready result before enforcing constraints or removing old interfaces.

For bundles with contract work, your release system supplies expiring writer
records in `deploy-readiness.json`. The file identifies the exact plan, bundle,
environment, and release. Check it against production through a restricted
read-only observer:

```sh
onwardpg contract check \
  --target app \
  --bundle add-booking-status \
  --environment production \
  --database-env PROD_READONLY_DATABASE_URL \
  --evidence deploy-readiness.json
```

`ready` permits proceeding. `reconciliation_required` identifies cleanup your
release runner must complete before checking again. `needs_evidence`, `blocked`,
and `stale` need resolution. The [contract reference](https://github.com/jokull/onwardpg/blob/main/docs/contract-readiness.md)
defines the evidence file, observer permissions, and cleanup order. Contract SQL
repeats the data assertions at enforcement time.

Verification proves the migration runs and reaches the requested schema in
scratch. It does not prove your production data is valid, locks will be short,
replicas can keep up, or old processes have drained. Review those against the
actual workload. Plans are forward-only; arrange your application rollback
procedure before deploying.

## Reference

Use `onwardpg <command> --help` for flags. The repository has the detailed
[CLI reference](https://github.com/jokull/onwardpg/blob/main/docs/cli.md),
[supported features](https://github.com/jokull/onwardpg/blob/main/docs/supported-features.md),
[bundle format](https://github.com/jokull/onwardpg/blob/main/docs/bundles.md), and
[safety model](https://github.com/jokull/onwardpg/blob/main/docs/safety-model.md).

onwardpg is [MIT licensed](https://github.com/jokull/onwardpg/blob/main/LICENSE).
[Source and issues](https://github.com/jokull/onwardpg) are on GitHub.

## Agent skill

Source: https://onwardpg.solberg.is/skill.md

---
name: onwardpg
description: Plan, revise, restack, verify, and assess contract readiness for PostgreSQL schema migrations with onwardpg. Use when a repository has .onwardpg.toml or the user asks to create or update an onwardpg plan, resolve planner decisions, author a backfill or compatibility transition, handle schema drift across branches, verify a migration bundle, or check post-expand readiness.
license: MIT
compatibility: Requires the onwardpg CLI, the repository's configured schema exporter, and access to its configured scratch PostgreSQL administrator. Development and production database access are optional and must remain separately scoped.
metadata:
  author: onwardpg
  version: "1.0"
---

# Plan PostgreSQL migrations with onwardpg

Use onwardpg as the catalog-aware planner and verifier. Bring application intent from the repository and the user; do not invent product semantics from a schema diff.

## Hard boundaries

- onwardpg writes reviewable migration bundles. It does not deploy them to production.
- Never run generated phase SQL against development, staging, or production unless the user explicitly asks for that separate operation.
- Never treat development reconciliation (`D -> W`) as the durable feature migration (`H -> W`).
- Never copy production rows, credentials, secrets, or sensitive values into prompts, bundles, logs, or verification fixtures.
- Supply a semantic hint only when application code, product requirements, or bounded data evidence proves it.
- Edit only files and `ONWARDPG TODO` pockets named by planner output. Preserve markers and generated ownership boundaries.
- Treat successful disposable verification as structural and semantic evidence, not proof of live data distribution, lock duration, traffic drain, replica health, or deployment completion.
- Treat `onwardpg contract check` as read-only, expiring readiness evidence. It
  does not execute contract or replace the assertion repeated in contract SQL.

Read [the schema-state model](references/schema-states.md) before interpreting a diff. Read [production evidence](references/production-evidence.md) before querying any live or replicated database.

## Inspect before planning

1. Find `.onwardpg.toml` and read the target, PostgreSQL major, bundle root, schema exporter, and optional development database configuration.
2. Read the declarative schema source and the application readers and writers affected by the requested change.
3. Inspect existing bundles and repository delivery conventions. Do not create a second speculative migration for the same feature.
4. Run `onwardpg config check`. Fix configuration or export failures before interpreting planner output.
5. Run `onwardpg status` and `onwardpg history status`. If history is uninitialized, explain what `onwardpg init` will establish and obtain the user's intent before creating a baseline in an established project.

## Keep one feature plan alive

Start a new feature once:

```sh
onwardpg plan descriptive-feature-name
```

After every schema edit, answered decision, SQL edit, branch return, or rebase, revise the same active PlanID:

```sh
onwardpg plan
```

Do not stack local fixup migrations. Git moves the feature; rerunning `plan` restacks the same feature bundle on the currently accepted history and carries forward only decisions whose scoped meaning still holds.

Use JSON output, which is the default. Follow the high-level `status`, ordered
`next_actions`, nested decision choices, named edit requirements, and exit
codes rather than guessing. A `workspace_fast_forward` action contains direct
D -> W SQL plus exact argv; it is never the durable H -> W bundle. Use
`durable.status` as the effective bundle state. `durable.generated_plan` is
the raw generator result before edit reconciliation and may retain
`needs_sql_edits` as provenance after the edited artifact verifies.
Inspect `next_actions` even when the top-level status is `ready`: that state
means the durable artifact is ready, while caller-owned development SQL remains
an explicit optional action.
Lower-level `draft` reports additionally provide `next_action`:

- `0`: review the report and SQL; continue or verify.
- `2`: supply justified intent or edit only the named SQL pocket, then rerun.
- `3`: stop on unsupported catalog state; change the design, narrow an explicit ignore, or report the gap.
- `4`: repair stale history, receipts, residual differences, or clone evidence before continuing.
- `1`: fix invocation, configuration, export, connection, or environment errors.

See [the decision protocol](references/decision-protocol.md) for hints, SQL handoffs, branch switching, and rebases.

## Answer only what the repository proves

Hints contain semantic intent, never SQL or opaque planner fingerprints. An agent that already knows a rename or manual conversion strategy may provide it on the first invocation with `--hint` or `--hints-file`. onwardpg consumes only hints that match real dependency-ordered decisions and rejects stale, contradictory, impossible, or unused guesses.

Keep durable `--hint` decisions separate from local-only `--dev-hint` decisions. A messy development database is convenience evidence, not migration history.

For product-specific transformations:

1. Read all application paths that read or write the affected values.
2. Use bounded production aggregates or value-shape classifications only when the user has provided restricted read-only access.
3. Record any live precondition that must be rechecked during deployment.
4. Edit the named `expand.sql` or `contract.sql` pocket with reviewed SQL.
5. Resolve any planner-named Boolean contract-gate pocket in `contract.sql`;
   that assertion is repeated at production enforcement time. Separately add
   optional read-only assertions to `verify.sql` for synthetic `WITH ... VALUES
   (...)` conversion examples or clone-only postconditions. Do not insert
   fixtures into deployment phases or treat `verify.sql` as production
   readiness evidence.
6. Rerun `onwardpg plan` when the declarative schema changes, then run `onwardpg verify` against the exact edited bundle.

## Development databases and branches

`onwardpg plan --output sql` emits only direct development reconciliation (`D -> W`). It is never the PR bundle. In workspace mode, preserve development-only objects that may belong to another branch.

When ordinary plan output contains a `workspace_fast_forward` action, inspect
its `preserved` objects and reason. `accepted_history_changed` means a rebase
left D behind the new accepted head. The included SQL is available immediately;
the argv renders the same stream for optional execution and repeats any
consumed ephemeral dev hints. Never copy D-only
objects into the durable plan, and stop if the development report asks a
decision or reports an incompatibility.

If a branch switch removes the active bundle from the checkout, name the returning or other plan explicitly. Let onwardpg park or restore worktree-local PlanIDs. If it reports an active-plan conflict, stop and resolve which feature is present instead of deleting anchors or fabricating another plan.

## Finish with an evidence handoff

Run:

```sh
onwardpg verify
onwardpg status
```

Report:

1. the PlanID and bundle path;
2. the H -> W compatibility strategy and application deployment assumption;
3. every consumed semantic decision and its code or product evidence;
4. edited phase SQL and synthetic verification assertions;
5. verification outcome and residual diff;
6. hazards, unsupported objects, or unanswered decisions;
7. live preconditions and operational gates still owned by the deployment system.

If the user is operating a post-expand deployment, validate provider-neutral,
expiring evidence for every potential writer cohort and run the read-only gate:

```sh
onwardpg contract check \
  --environment production \
  --database-env PROD_READONLY_DATABASE_URL \
  --evidence deploy-readiness.json
```

Report `ready`, `needs_evidence`, `blocked`, or `stale`; never apply phase SQL.

Do not describe the migration as safe merely because clone verification passed.