# onwardpg complete documentation > Bulk Markdown context for onwardpg. Prefer /skill.md plus targeted page Markdown when operating the CLI. ## Agent operating 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. ## Document: Introduction Source: https://onwardpg.solberg.is/start/introduction.md Most migration tools answer one question: **how do I move the database to the schema my code declares?** onwardpg answers the production question hiding behind it: **how can the code running now and the code about to deploy both use the database while that move happens?** That distinction barely shows up locally. Development often looks atomic: stop one process, change the schema, start the new process. Production still has old instances, workers, queues, connection pools, rollback candidates, and preview deployments using yesterday's shape. The application and database do not change at the same instant. onwardpg exists for that overlap. ## Two jobs, one plan First, onwardpg is a compatibility-aware migration planner. It takes the final PostgreSQL DDL produced by Drizzle, Django, Alembic, Prisma, or your own schema source and works out a safe route to it. For a breaking change, the temporary database can be deliberately **more permissive than both the old and new schema**, so both application versions remain valid during rollout. Second, onwardpg keeps that route alive while the feature is still being built. Models change. Another migration lands on `main`. A developer switches branches and their local database gets ahead. A backfill rule is decided halfway through the work. `onwardpg plan` captures those decisions in one migration, revises it, and restacks it on the newly accepted history instead of accumulating draft fixups. Either half on its own is incomplete. Safe SQL that forgets why it was chosen is fragile during development. A durable plan that only describes the final schema still leaves old and new code fighting over the rollout. ## One application release, with compatibility on both sides The normal release shape is: ```text change the schema in your feature branch ↓ onwardpg works out how old and new code can share the database ↓ APPLY EXPAND — before the pull request merges ↓ merge and deploy one application version ↓ wait until old instances, workers, pools, and queues are gone ↓ APPLY CONTRACT — only when there is cleanup to perform ``` **Expand** runs while the old application is still live. It may add columns, loosen constraints, build indexes, or install synchronization needed by both versions. Applying it before merge is safe only because current production code must remain valid after every expand batch. The new application then deploys against that shared schema. It must work both before and after contract. **Drain** is evidence, not a timer. Contract waits until the delivery system can show that old writers and readers are actually gone. **Contract** performs final catch-up, checks the data, tightens constraints, and removes compatibility scaffolding. A purely additive change often has no contract at all; onwardpg does not invent a second phase for ceremony. This gives one feature one branch, one merge, and one application deployment. Cleanup follows the drain of that deployment instead of requiring a second feature release. ## A small example reveals the whole model Suppose the application now requires: ```sql status text NOT NULL ``` Adding that column with `NOT NULL` immediately would break the old application, because it still inserts rows without `status`. PostgreSQL turns the omitted value into `NULL`. onwardpg therefore expands with the shape both versions can tolerate: ```sql ALTER TABLE "app"."bookings" ADD COLUMN "status" text; ``` The new code writes `status` and treats it as temporarily nullable when reading rows created during the overlap; the old code can continue omitting it until it drains. Only then can the database become strict. onwardpg does **not** invent what those legacy `NULL` rows mean. The next plan offers three honest choices: assert that another operation has already filled them, ask a developer or agent for reviewed cleanup SQL, or leave the column loose and finish the change in a later plan. If the product says old bookings should become `pending`, that decision comes from the product—not from the schema diff. onwardpg places that cleanup in contract, checks that no `NULL` values remain, and only then emits: ```sql ALTER TABLE "app"."bookings" ALTER COLUMN "status" SET NOT NULL; ``` That division of responsibility scales. onwardpg can plan widening and narrowing checks, default changes, required columns, indexes, coordinated constraints, rename bridges, and dependency-aware work around views and materialized views. It asks for help when PostgreSQL cannot know whether two names mean the same thing, which duplicate should win, how malformed values should convert, or whether a large backfill needs application-specific batching. ## The plan command is the core product Start a feature plan once, then run the same command as the work changes: ```sh onwardpg plan checkout-preferences # Repeat after model edits, decisions, and rebases. onwardpg plan ``` The first call creates one `PlanID` and one forward-only migration bundle. Later calls revise that same logical migration. Decisions whose meaning still holds move forward; decisions invalidated by a schema change are rejected and asked again. Developer- or agent-written SQL is kept only while its surrounding schema still supports it. ## Rebase the branch; restack the migration A Git rebase can insert a new chain of accepted migrations underneath a feature that is already halfway built. That should not force the developer to keep a stale migration and append a correction—or throw away reviewed decisions and start again. ```sh git rebase origin/main onwardpg plan ``` Git moves the commits. The next `plan` validates and replays the new accepted migration head, then rebuilds the **same PlanID** from that new base to the feature's current DDL. Generated work that is now supplied upstream disappears. A rename, backfill, or destructive decision moves forward only if it still describes the same schema change; if the rebase changed its meaning, onwardpg asks again. The development database remains a separate concern. If it is merely missing a safe change introduced by the new history, `plan` can return the small fast-forward SQL needed locally. Objects left behind by another branch are not silently folded into the feature migration. This is restacking at the schema level: one carefully reviewed migration keeps climbing with the feature instead of leaving a trail of rebase fixups. The planner also keeps different database states in their proper roles: - **Accepted history** is replayed to establish the real base for the feature. - **Working DDL** says what the application wants when the feature is complete. - **The development database** is reconciled separately, so another branch's objects cannot accidentally become production migration history. - **Production** is read only by explicit drift and contract-readiness checks; it never silently authorizes generated repair SQL. - **Verification** uses independent disposable PostgreSQL databases to execute the exact edited files and compare the result with the requested schema. The durable migration is always accepted history → working DDL. Everything else helps author, verify, or operate that route without changing what the route means. ## Developers and agents supply the meaning PostgreSQL lacks When onwardpg reaches a rename, destructive change, data cleanup, or conversion, it asks a scoped question instead of guessing. A developer can answer it; a coding agent can often answer earlier because it has read the feature code, application write paths, and tests. Agents are especially useful for composing backfills and conversions. With carefully restricted read-only access, an agent can inspect counts and value shapes in production, turn those observations into synthetic verification cases, and fill only the SQL pocket onwardpg named. onwardpg then checks phase, dependency order, transaction boundaries, hazards, data gates, and final schema. An unresolved TODO cannot pass verification. :::tip[Using a coding agent?] Point it at [`https://onwardpg.solberg.is/skill.md`](https://onwardpg.solberg.is/skill.md) before it changes the schema. The skill contains the operating loop, evidence boundaries, stop conditions, and required review handoff. ```text Read and follow https://onwardpg.solberg.is/skill.md for this migration. Inspect this repository's schema source, accepted migration history, and the application paths affected by the change. Maintain one evolving onwardpg plan, supply only evidence-backed decisions, never apply to production, run onwardpg verify, and report the operational gates verification cannot prove. ``` ::: ## What onwardpg proves—and what it cannot `onwardpg verify` executes the exact reviewed bundle on disposable PostgreSQL. It proves that the phases run under their declared transaction rules, that required assertions pass in verification, and that the resulting catalog matches the schema the application requested. It cannot prove that a business mapping is correct, that a production table is small enough for a lock, that replicas have enough WAL headroom, or that every old process has drained. Those remain explicit review and delivery inputs. :::caution[Planner, not deployer] onwardpg never applies SQL to production or to another caller-owned database. Your delivery system runs the reviewed phase files because it is the system that can observe rollout, capacity, and drain state. ::: ## Where to begin 1. [Install and configure onwardpg](/start/installation/). 2. Follow [the plan command](/concepts/plan-command/) from an additive column to an agent-authored conversion beneath PostgreSQL dependencies. 3. [Create your first plan](/start/first-plan/). 4. Read the [expand/contract contract](/concepts/expand-contract/) before wiring delivery automation. 5. If a coding agent authors migrations, use the [agent-assisted planning workflow](/agents/agent-assisted-planning/). 6. When choosing between adjacent tools, read the [operating-model comparison](/start/comparison/). ## Document: Comparison Source: https://onwardpg.solberg.is/start/comparison.md _Reviewed 20 July 2026 against the primary documentation linked on this page._ Most migration tools answer one question: **how do I move this database to the schema my code declares?** onwardpg answers the production question hiding behind it: **how can the code running now and the code about to deploy both use the database while that move happens?** That changes the release sequence: ```text change the schema in your feature branch ↓ onwardpg works out how old and new code can share the database ↓ APPLY EXPAND — before the pull request merges ↓ merge and deploy one application version ↓ wait until old instances and workers are gone ↓ APPLY CONTRACT — only when there is cleanup to perform ``` Applying SQL before merge sounds aggressive until you see what expand means. It is designed not to break the code already running. For a breaking change, expand temporarily makes the database **more permissive than both the old and new schema**. Old code keeps working, new code gets what it needs, and both can run during the rollout. You still review locks, data volume, and database capacity; compatible does not mean free. For a purely additive change there may be no compatibility problem and no contract at all. onwardpg emits only the non-empty phase, so the common case is still boring. ## Why this almost never breaks locally A local migration often looks atomic: stop one process, change the schema, start the new process. The database may be disposable, nearly empty, or rebuilt from scratch. There are no old containers, long-lived connection pools, queue workers, scheduled jobs, rollback instances, or forgotten preview deployments continuing to use the old schema. Production has all of those. The schema and application do not change at the same instant. A normal new column is exactly as boring as you expect. If `status` is nullable, onwardpg adds it in expand and there is nothing to contract. Old code does not know the column exists, and that is fine. A required column with a database default is also compatible in one step. Old inserts omit `status`; PostgreSQL supplies the default. The interesting case is a required column **without** a database default—say the application computes a booking status that cannot honestly be invented by the database: ```sql status text NOT NULL ``` Production is still running the old version, and that version inserts bookings without `status`. PostgreSQL treats the omitted value as `NULL`; add `NOT NULL` immediately and those requests start failing. So onwardpg adds the column without `NOT NULL` before the PR merges: ```sql ALTER TABLE "app"."bookings" ADD COLUMN "status" text; ``` The new application deploys knowing how to write `status` and tolerates it being temporarily nullable while the two releases overlap. Old code still creates `NULL` rows until it drains. At this point onwardpg has **not** chosen `pending`: it knows what old code writes, but it cannot know what those rows mean to the product. The next `plan` says exactly what is missing. It offers three choices: - `assert_only` if application code or an earlier operation will fill every row; - `manual_sql` if a developer or agent can supply the rule now; or - `split_plan` if the honest answer is “not in this release.” Choosing `manual_sql` does not smuggle a guessed value into the plan. The CLI's copyable choice is equivalent to: ```sh onwardpg plan add-booking-status \ --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"]}' ``` onwardpg then stops with `needs_sql_edits` and names `phases/contract.sql`. Inside that file is a marked pocket asking for the post-drain rule, followed by an onwardpg-owned check that no `NULL` values remain. For this example, the developer decides that old bookings mean `pending` and replaces only the pocket: ```sql UPDATE "app"."bookings" SET "status" = 'pending' WHERE "status" IS NULL; ``` That rule could just as easily be a `CASE`, a join to another table, or a separately run batched operation. An agent can also attach the reviewed SQL and its Boolean postcondition as structured `work` on the `manual_sql` hint. Either way, `onwardpg verify` runs the exact edited plan on disposable PostgreSQL; an unfilled pocket cannot pass. Before turning `NOT NULL` on, onwardpg checks that no rows are still missing a status. Then it applies the final change: ```sql ALTER TABLE "app"."bookings" ALTER COLUMN "status" SET NOT NULL; ``` A rename makes the stakes clearer. Rename the column first and old code fails. Deploy code that expects the new name first and new code fails. Without a bridge, there is no ordering that avoids an application error window—it is literal downtime dressed up as a one-line `ALTER TABLE`. onwardpg keeps both names working and their values in sync. After one application deploy and drain, it removes the old name. The same idea applies to changed checks, required columns, type conversions, and dependent views: the temporary schema exists to get the release out safely, not as accidental debt. ## Where the other tools stop | Kind of tool | What it does well | What it leaves to you | | --- | --- | --- | | **ORM sidecars** — Drizzle, Django, Alembic, Prisma | Turn application models into SQL and track migrations. | Work out how old and new application versions will share the database. | | **Diff engines** — Atlas, Stripe `pg-schema-diff`, migra | Calculate the SQL needed to move from one PostgreSQL schema to another. | Account for what the code currently running can still read and write. | | **Compatibility runtime** — pgroll | Keep old and new database interfaces running side by side. | Describe what the change means and make each app version select the right interface. | ## What onwardpg plans—and when it asks for help onwardpg already knows how to handle many changes that routinely bite teams in production. It writes the expand/contract SQL and pauses only when it needs a business decision or data cleanup that the schema cannot explain: - **Required columns** arrive nullable so old inserts keep working. Cleanup, a query that checks every row, and `SET NOT NULL` wait until contract. - **Check constraints** are widened immediately when the new rule accepts more values, retained until contract when the new rule is narrower, or temporarily relaxed when neither rule contains the other. - **Defaults** are added before a new writer starts omitting a value and removed only after the old default-dependent writer drains. - **Unique, primary-key, foreign-key, and exclusion changes** loosen the old rule while both app versions are live, then check the data and install the new rule after drain. - **Column and table renames** become overlap bridges after the developer or agent confirms identity. For an eligible column rename, onwardpg generates the second column, two-way synchronization trigger, catch-up, equality gate, cleanup, and final native rename. - **Indexes and dependent objects** are handled in the order PostgreSQL needs: concurrent replacement where supported, coordinated foreign keys, and safe removal and recreation of views and materialized views when a dependency changes. PostgreSQL can describe tables, constraints, and dependencies. It cannot tell onwardpg whether two names mean the same thing, which duplicate row should win, what a blank string means during a type conversion, whether a backfill must run in batches, or how your application will read through a more complex change. When onwardpg reaches one of those questions, it asks instead of guessing. onwardpg is deliberately designed for a developer or coding agent to supply that missing work. An agent that has read the feature code can answer those questions early, run small read-only queries to see what production values look like, and write a complex backfill or conversion in the clearly marked part of the SQL file. onwardpg then checks what it can: that the SQL is in the right phase, dependencies are handled in the right order, transaction boundaries and hazards are visible, live checks run before contract, and the resulting schema is the one the application asked for. This is not a free-form escape hatch. Unresolved TODOs fail verification. If the schema changes underneath a decision, onwardpg asks again. The exact edited SQL must run on a disposable PostgreSQL database and leave no schema difference behind. onwardpg cannot decide whether a business rule is correct for your product, but custom SQL cannot quietly bypass the plan. ## ORM tools: they tell onwardpg what the app needs [Drizzle Kit](https://orm.drizzle.team/docs/kit-overview), [Django migrations](https://docs.djangoproject.com/en/6.0/topics/migrations/), [Alembic](https://alembic.sqlalchemy.org/en/latest/autogenerate.html), and [Prisma Migrate](https://www.prisma.io/docs/orm/prisma-migrate/workflows/development-and-production) should continue to own the application schema. onwardpg is not trying to replace them. Their SQL output tells onwardpg what the database should look like when the feature is finished. For example, [`drizzle-kit export`](https://orm.drizzle.team/docs/drizzle-kit-export) prints the SQL for the tables and constraints in your TypeScript schema. Django can build the database described by its migration state; Alembic can replay its selected [revision graph](https://alembic.sqlalchemy.org/en/latest/branches.html); Prisma can produce SQL from its schema. onwardpg plans the rollout to that result while the framework remains the source of truth. ### Why not stop there? Framework migration generators see the schema you want. They do not see the old binary still writing `NULL`, the worker using the old column name, or the product rule for malformed values. Their normal unit is one migration attached to one application release. The conventional safe workaround is therefore two feature cycles: ```text PR 1 / deploy 1: add permissive shape and deploy bridge code PR 2 / deploy 2: tighten or remove the old shape after drain ``` Drizzle supports [custom SQL migrations](https://orm.drizzle.team/docs/kit-custom-migrations), and the other frameworks have comparable escape hatches. You can hand-author the steps yourself, but the tool does not preserve them as one evolving plan tied to one release. With onwardpg, the application schema can say what it really wants from the start. onwardpg changes the database in phases: ```text one branch, one merge, and one application deploy before merge: expand.sql after drain: contract.sql, if non-empty ``` As the feature changes or rebases, `onwardpg plan` updates the same migration. It keeps decisions and edited SQL only while the schema still supports them. ## Diff engines: excellent SQL, but they cannot see the rollout These tools are closest to the engine inside onwardpg: - [Atlas `schema diff`](https://atlasgo.io/declarative/diff) accepts database, SQL, HCL, or migration-directory states and generates a SQL plan. - [Stripe `pg-schema-diff`](https://github.com/stripe/pg-schema-diff) uses PostgreSQL-native online techniques, validates plans in a temporary database, and reports hazards. - [migra](https://github.com/djrobstep/migra) established the elegant “database A to database B” CLI model. It is now officially deprecated. Atlas also sells Cloud/Pro database-management, registry, policy, [lint](https://atlasgo.io/versioned/lint), drift, and apply tooling. That is a broader control plane; the relevant comparison here is its very capable diff CLI. ### Why not stop at the diff? These tools write **SQL that moves the database from A to B**, not a release plan that keeps two application versions working. They can make a constraint lock-friendlier, build an index concurrently, or warn about a drop. They cannot tell that the old application still depends on behavior the new schema removes. Online DDL is not application compatibility. A rename can take a tiny lock and still produce instant downtime. A hazard warning can tell you an operation is dangerous; it cannot invent the product-specific bridge, decide what data means, or know when old writers have drained. onwardpg uses the diff as the beginning. It works out what can happen before deploy, what must wait until old code is gone, which questions need an answer, and whether the exact reviewed SQL reaches the requested schema. ## pgroll: real compatibility, heavier machinery [pgroll](https://github.com/xataio/pgroll#how-pgroll-works) is the only adjacent tool here that genuinely keeps old and new database interfaces available at the same time. You describe the change in YAML or JSON and provide [up/down mappings](https://github.com/xataio/pgroll/blob/main/docs/guides/updown.mdx) for how values move in both directions. pgroll builds versioned schemas, views, shadow columns, backfills, synchronization triggers, completion, and active rollback. That is impressive engineering. ### Why choose a planner instead? pgroll solves compatibility by adding a runtime layer to the database. Application versions must select their matching schema, commonly through `search_path`, as its [client integration guide](https://github.com/xataio/pgroll/blob/main/docs/guides/clientapps.mdx) shows. Your application configuration, pgroll's versioned views and triggers, and the rollout now have to move together. You still have to tell pgroll what the change means. Its [`convert` workflow](https://github.com/xataio/pgroll/blob/main/docs/guides/orms.mdx) translates existing ORM SQL into pgroll operations, but its documentation says developers must still supply `up`/`down` mappings and sometimes consolidate the result manually. pgroll is a strong choice when you want the database to route old and new clients and provide active rollback. onwardpg is a strong choice when you want the application to keep using its normal PostgreSQL schema, with reviewed SQL before and after one application rollout. ## The onwardpg advantage The pitch is not “we have a better diff.” It is the release model around it: - **Compatibility lands before code.** Expand can be applied before merge because current production code remains valid against it. - **One feature stays one migration.** Model edits, decisions, SQL, rebase, and verification evolve in one bundle instead of accumulating draft fixups. - **One merge gets one deployment.** Contract follows drain as post-SQL; an empty contract disappears instead of demanding a ceremonial second release. - **Keep using your framework schema.** Drizzle, Django, Alembic, or Prisma still says what the finished database should look like. - **Test the actual SQL on PostgreSQL.** onwardpg runs the reviewed files, follows real dependencies, and checks that the final schema matches. Running the plan is intentionally boring. Your release system applies reviewed expand SQL before merge/deploy and contract SQL after drain. onwardpg does not need production write access, and your deployment scripts do not have to work out the safe order themselves. :::warning[One production operation, one DDL executor] Do not let a framework migration runner, `drizzle-kit push`, Atlas apply, and onwardpg phase files independently own the same schema operation. Choose one production execution path. ::: onwardpg still cannot prove production capacity, acceptable lock duration, application correctness, or that every old process drained. It makes those boundaries explicit—and generates the schema bridge that makes waiting for the evidence possible without taking the application down. ## Document: Installation Source: https://onwardpg.solberg.is/start/installation.md :::tip[Hand this page to a person; hand the skill to an agent] The human documentation explains the model and tradeoffs. A coding agent should begin with the versioned operating contract at [`/skill.md`](https://onwardpg.solberg.is/skill.md), then follow its links to targeted Markdown pages. [`/llms.txt`](/llms.txt) is the documentation directory, not the procedure. ::: ## Install the preview On macOS or Linux with Homebrew: ```sh brew install jokull/tap/onwardpg onwardpg version ``` Go developers can install the pinned preview directly: ```sh go install github.com/jokull/onwardpg/cmd/onwardpg@v0.1.0-preview.1 ``` ## Declare a target Add `.onwardpg.toml` at the repository root: ```toml version = 1 bundle_root = "migrations/onward" [targets.app] schema_file = "schema.sql" # Or run a deterministic exporter: # schema_command = ["pnpm", "--silent", "schema:export"] dev_database_env = "ONWARDPG_DEV_DATABASE_URL" scratch_database_env = "ONWARDPG_SCRATCH_DATABASE_URL" dev_mode = "workspace" ``` The schema source must build the complete desired PostgreSQL state from empty—not only the latest `ALTER` statements. See the [framework guides](/guides/drizzle/) for export patterns. ## Set the two database roles ```sh export ONWARDPG_DEV_DATABASE_URL='postgres://readonly:secret@localhost/myapp_dev' export ONWARDPG_SCRATCH_DATABASE_URL='postgres://postgres:secret@localhost/postgres' ``` The development URL is inspected read-only. The scratch URL is a control-plane administrator for a dedicated local or CI PostgreSQL cluster; it creates and force-drops random databases and short-lived restricted login roles. :::danger[Never use a shared application cluster as scratch] The scratch identity needs database and role lifecycle authority. Point it only at disposable local or CI infrastructure—never production, staging, or a shared developer cluster. ::: ## Check the boundary ```sh onwardpg config check ``` This validates configuration, exports DDL deterministically, materializes it, checks history, and ensures the development and scratch PostgreSQL major versions agree. PostgreSQL 15–18 are supported independently; evidence from one major is not treated as evidence for another. ## Document: Your first plan Source: https://onwardpg.solberg.is/start/first-plan.md :::tip[Pairing with a coding agent?] Copy this into the task before editing models or DDL: ```text Read and follow https://onwardpg.solberg.is/skill.md. Create or revise one onwardpg plan for this feature, follow its status, decisions, and edit_files, edit only named SQL pockets, verify the exact bundle, and summarize unresolved hazards and live deployment gates for human review. ``` ::: Assume your desired schema begins with: ```sql CREATE SCHEMA app; CREATE TABLE app.accounts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, display_name text NOT NULL ); ``` ## Establish replayable history ```sh onwardpg config check onwardpg init ``` `init` creates and clone-verifies the first content-addressed history entry. It does not apply anything to your development or production database. ## Evolve desired DDL Add a nullable status to the authoritative schema: ```sql ALTER TABLE app.accounts ADD COLUMN status text; ``` In a declarative schema file, write the resulting complete `CREATE TABLE` shape instead of the `ALTER` above. Then plan one feature: ```sh onwardpg plan add-account-status ``` This creates one worktree-local `PlanID`. The durable bundle compares accepted history replayed in PostgreSQL (H) with the current exported schema materialized in PostgreSQL (W). If a development database is configured, the same command also reports a separate D → W reconciliation; it never uses D as migration history. For this nullable additive change, the generated bundle appears under the configured `bundle_root` as: ```text migrations/onward/app/add-account-status/ ├── manifest.json ├── plan.json └── phases/ └── expand.sql ``` Only non-empty phases exist. Decision receipts appear after hints are consumed. Optional `verify.sql` appears when you add clone-only assertions; required live readiness assertions stay in `contract.sql` and `contract-gates.json`. A required column, rename bridge, or other compatibility change may also generate `contract.sql` and editable pockets. Review every statement and hazard. Continue editing your models or DDL and rerun `onwardpg plan`; the same active feature bundle is recalculated rather than stacked into a trail of local fixups. After rebasing on newly accepted migrations, run it again: the same `PlanID` is restacked on the new replayed head. ## Prove the bundle ```sh onwardpg verify ``` The command selects the active plan. In a clean checkout without that local anchor, use `onwardpg verify --bundle add-account-status`. Verification independently replays the chosen checkpoint and its continuation, runs boolean assertions, compares the final graph fingerprint, and requires an empty residual diff. Use the read-only CI form after the bundle is reviewed: ```sh onwardpg verify --check ``` If the high-level plan reports `needs_input` or `needs_sql_edits`, the bundle is intentionally incomplete. Supply one of the emitted semantic hints or replace the TODOs in the reported `edit_files`, then rerun `plan` and verify. The nested lower-level `draft` report uses `needs_decisions` for its compact decision envelope. ## Document: The plan command Source: https://onwardpg.solberg.is/concepts/plan-command.md `onwardpg plan` is the core product. It does more than print the difference between two schemas. It keeps one feature migration alive while both the feature and the accepted migration chain change underneath it. ```sh # Start the migration once. onwardpg plan checkout-preferences # Repeat after every model edit, decision, and rebase. onwardpg plan ``` The first call creates one worktree-local `PlanID` and one durable bundle. Every later call revises that same logical migration. There is no trail of speculative fixups. When accepted history absorbs the verified bundle, the next `plan` retires this checkout's authoring anchor automatically; it does not claim merge or deployment. ## Start easy. Keep the same command when it gets ugly. The useful test of a planner is not whether it can print `ALTER TABLE`. It is whether the same workflow continues to help as compatibility, product meaning, and PostgreSQL dependencies accumulate. Here is how that same workflow grows from an ordinary column addition to a change beneath dependent views. ### Easy: add a nullable column The desired schema adds `status text`. No old write becomes invalid, no data meaning is missing, and no dependent object must move. `plan` exits successfully with one expand statement: ```sql ALTER TABLE "app"."bookings" ADD COLUMN "status" text; ``` There is no empty `contract.sql` for ceremony's sake. The planner writes only the non-empty phase. This is the familiar diff-tool case—and it uses the same bundle, hazard, history, and verification machinery as every harder rung. ### Medium: make the new column required Change the destination to `status text NOT NULL` and the same SQL is no longer enough. Old code can still insert without `status` after expand. Actual output therefore asks whether to `assert_only`, supply `manual_sql`, or `split_plan`. With reviewed cleanup selected, it adds the nullable shape first, reports `needs_sql_edits`, and puts cleanup plus an exact gate before enforcement: ```sql -- expand.sql ALTER TABLE "app"."bookings" ADD COLUMN "status" text; -- contract.sql -- PRODUCT-SPECIFIC SQL: Provide reviewed reconcile_contract_sql SQL for app.bookings.status -- Verify: SELECT NOT EXISTS (SELECT 1 FROM "app"."bookings" WHERE "status" IS NULL); DO $onwardpg$ BEGIN IF NOT COALESCE((SELECT NOT EXISTS (SELECT 1 FROM "app"."bookings" WHERE "status" IS NULL)), false) THEN RAISE EXCEPTION 'onwardpg contract gate failed: data:1c16b884027de910'; END IF; END $onwardpg$; ALTER TABLE "app"."bookings" ALTER COLUMN "status" SET NOT NULL; ``` Now the output answers the question a raw diff cannot: *when is each operation compatible?* The application deploy and drain sit between those files. The developer supplies the historical value; onwardpg keeps that edit ahead of its generated data assertion and the constraint. ### Hard: rename a column while both releases are alive Two snapshots cannot prove that `display_name` and `full_name` are one product field, so `plan` first asks for a semantic rename decision. It then asks how to backfill existing rows. With `single_transaction` explicitly selected for a known-small table, onwardpg produces this choreography: ```sql -- expand.sql, selected statements in emitted order ALTER TABLE "app"."accounts" ADD COLUMN "full_name" text; CREATE TRIGGER "onwardpg_sync_column_4cff936be08db67c" BEFORE INSERT OR UPDATE OF "display_name", "full_name" ON "app"."accounts" FOR EACH ROW EXECUTE FUNCTION "app"."onwardpg_sync_column_4cff936be08db67c"(); UPDATE "app"."accounts" SET "full_name" = "display_name" WHERE "full_name" IS DISTINCT FROM "display_name"; -- contract.sql, after the old release drains UPDATE "app"."accounts" SET "full_name" = "display_name" WHERE "full_name" IS DISTINCT FROM "display_name"; DROP TRIGGER "onwardpg_sync_column_4cff936be08db67c" ON "app"."accounts"; DROP FUNCTION "app"."onwardpg_sync_column_4cff936be08db67c"(); ALTER TABLE "app"."accounts" DROP COLUMN "full_name"; ALTER TABLE "app"."accounts" RENAME COLUMN "display_name" TO "full_name"; ``` The omitted generated function is not hand-waved: it handles inserts, one-sided updates, and conflicting dual writes, while contract performs a final catch-up and equality assertion before cleanup. For a large table, provide `operator_batched` work instead. onwardpg writes a separate reviewed `operations/.json` containing the bounded batch template, progress key, idempotency notes, and generated equality completion query. It is not rendered into a phase transaction. `contract check` reports that exact operation as `reconciliation_required` until the equality gate is true. ### Nightmare: change a type beneath views and indexes Now change `app.facts.val integer` to `bigint` while it feeds an ordinary view, a materialized view, and that materialized view's unique index. This is where a coding agent becomes genuinely useful: it can inspect application readers, production value shapes, and compose the compatibility bridge and conversion. It should not have to rediscover PostgreSQL's dependency graph. After the agent supplies the `type_change: manual_sql` decision, onwardpg names the exact dependency scope and emits this order around the editable conversion: ```sql -- contract.sql, selected statements in emitted order DROP MATERIALIZED VIEW "app"."fact_cache"; DROP VIEW "app"."fact_view"; -- ONWARDPG TODO: replace this comment with reviewed CONTRACT SQL for column:app:facts:val (integer -> bigint). ANALYZE "app"."facts" ("val"); CREATE VIEW "app"."fact_view" AS SELECT val FROM app.facts; CREATE MATERIALIZED VIEW "app"."fact_cache" AS SELECT val FROM app.fact_view WITH DATA; -- onwardpg:batch nontransactional CREATE UNIQUE INDEX CONCURRENTLY "fact_cache_val_idx" ON "app"."fact_cache" USING "btree" ("val" NULLS LAST); ``` This division of labor is the point. The agent owns whether values should cast, map, reject, or be backfilled through a shadow interface. onwardpg owns the catalog-proven closure: reverse dependency order for destruction, desired dependency order for recreation, materialized-view population state, index definition, transaction boundary, hazards, and the final empty residual diff. `verify` executes the edited conversion on disposable PostgreSQL, rebuilds the dependent objects, and compares the result with the schema the application requested. Missing privileges, comments, indexes, population state, or transitive type changes cause verification to fail. The fourth rung is the payoff: onwardpg does not become less useful when an agent enters the loop. The agent supplies facts PostgreSQL cannot know; the planner keeps those facts inside a dependency-correct, evolving, verifiable migration. ## First question: what is “the current schema”? During feature development, there is no single current schema. There are several states with different authority: ```text framework models ── export SQL ── materialize in PostgreSQL ── W (working intent) accepted bundles ── replay in PostgreSQL ─────────────────── H (accepted history) developer database ── read-only catalog inspection ───────── D (local reality) verified selected bundle through expand ─────────────────── E (post-expand checkpoint) production ── explicit read-only observation ────────────── P (deployed reality) accepted history + selected bundle ── disposable replay ──── V (verification) ``` These states are not interchangeable: | State | What it answers | How onwardpg uses it | | --- | --- | --- | | **W — working** | What schema does this checkout declare now? | Framework DDL is executed in disposable PostgreSQL and catalog-inspected. | | **H — history** | What will a clean environment have before this feature? | Accepted onwardpg bundles are replayed from their content-addressed chain. | | **D — development** | What has this developer already applied locally? | Inspected read-only and reconciled separately for convenience. | | **E — expand checkpoint** | What catalog did verified expand produce? | Recorded by `verify` as the expected live catalog before contract. | | **P — production** | Does deployed reality match its declared stage? | Explicit `drift check` compares P with H; `contract check` compares P with E and evaluates gates. Neither authorizes migration generation. | | **V — verification** | Do the exact bundle bytes replay and converge? | Independent disposable clones execute the prefix and full continuation. | Here is the first “a-ha”: the durable PR migration is always **H → W**. A developer database cannot accidentally become migration history, and production state cannot silently authorize a repair. ## So why does `plan` inspect my development database? Because useful development ergonomics need a second comparison: **D → W**. One invocation produces two deliberately separate answers: ```text H → W durable bundle reviewed, merged, and deployed by the team D → W direct local reconciliation printed for the developer ``` JSON output keeps both reports in separate fields. `onwardpg plan --output sql` prints only the D → W SQL so you may choose to pipe it to your local database: ```sh onwardpg plan --output sql | psql "$ONWARDPG_DEV_WRITE_URL" ``` `ONWARDPG_DEV_DATABASE_URL`, the inspection credential configured by `dev_database_env`, may remain read-only. The separate `ONWARDPG_DEV_WRITE_URL` is an optional developer-controlled credential for applying reviewed local SQL. onwardpg itself still applies nothing to D, P, staging, or production. When D → W has safe statements, ordinary JSON output also adds a `workspace_fast_forward` next action containing the SQL and exact argv. After a rebase its reason is `accepted_history_changed`, so an agent can distinguish “bring my dev database across the new history head” from feature migration SQL. Any D-only objects that workspace mode preserved are named on the action; they are neither dropped nor absorbed into H → W. If generating that SQL consumed a local-only `--dev-hint`, the action repeats it so `--output sql` does not ask the same question again. `ready` means the durable migration is ready; onwardpg never mutates the caller-owned development database as part of that claim. Agents should inspect `next_actions` even on a successful response and may execute the optional fast-forward when they want D to catch up. If H already equals W and no durable bundle remains active, the emitted argv names the plan explicitly so it still replays successfully. ## What if I rename something before the feature merges? Suppose accepted history H has neither `quote_mode` nor `pricing_mode`. 1. Your first draft adds `quote_mode`; H → W correctly plans `ADD quote_mode`. 2. You apply that development SQL locally, so D now contains `quote_mode`. 3. Before merge, you rename the model field to `pricing_mode`. 4. H → W forgets the abandoned draft name and cleanly plans `ADD pricing_mode`. 5. D → W notices the local-only shape and asks whether to rename `quote_mode` directly or preserve it. The production bundle never inherits a rename that exists only because of local iteration. That is the second “a-ha”: the same diff engine understands *which relationship* it is planning, not merely that two catalogs differ. ## What if I switch branches and D is ahead? A long-lived development database accumulates objects from several branches. In the default `workspace` mode, onwardpg preserves D-only objects instead of proposing their removal. Absence from the current checkout is not treated as evidence that another branch’s table should be dropped. If D contains a genuine incompatible transition, `plan` may ask a separate development question. Answer it with `--dev-hint`, never the durable `--hint`: ```sh onwardpg plan \ --dev-hint '{"kind":"rename","object":"column","from":["app","quotes","quote_mode"],"to":["app","quotes","pricing_mode"]}' ``` The scopes cannot leak into each other. A local shortcut does not authorize production SQL. When a checkout switch removes the active bundle, onwardpg parks its local `PlanID`. Naming the other branch’s plan selects it; returning later restores the original identity. If the current bundle is still present, onwardpg blocks rather than inventing a second mutable plan in the same checkout. ## What if `main` moves while I am working? Rebase with Git, then run the same command: ```sh git rebase origin/main onwardpg plan ``` onwardpg does not use branch names or commit SHAs as schema proof. It validates the remaining accepted hash chain, replays its new head as H, and rebuilds the existing `PlanID` as **H(new) → W**. At the same time it recomputes D → W. If the rebase added a safe upstream change that the branch-worn dev database lacks, the response contains the small fast-forward SQL immediately. If parallel-branch state is merely surplus, it is preserved. If it conflicts with W, onwardpg asks or blocks instead of silently overwriting or importing it. The plan already knows the intent you captured: - decisions whose participating object scope is unchanged are carried forward; - decisions whose meaning changed are rejected as stale; - edits inside stable SQL pockets are transplanted; - generator-owned conflicts stop with the old, new, and edited versions for review; and - generated work already absorbed by incoming history disappears from the active bundle. Developer-owned data work is never discarded merely because a catalog diff became empty. That needs explicit reconciliation. This is the third “a-ha”: a rebase does not mean throwing away a carefully reviewed migration and starting the diff again. It means hill-climbing the same migration on firmer ground. ## Where do decisions enter? PostgreSQL can prove object shape and dependency. It cannot prove that two names mean the same product field, that data loss is intended, or how a string becomes an integer. `plan` exits `2` with the smallest semantic question it needs. Supply the answer and rerun: ```sh onwardpg plan \ --hint '{"kind":"rename","object":"column","from":["app","accounts","display_name"],"to":["app","accounts","full_name"]}' ``` Consumed hints are written automatically to `decisions.json` with their fingerprint-bound evidence. You do not copy internal question IDs or fingerprints, and you do not need to resubmit accepted hints. An agent that already understands the feature may provide several hints on the first invocation. A valid hint hidden behind an earlier dependency-ordered question is returned as `deferred` and re-emitted in `next_actions` without being recorded as accepted; irrelevant or impossible intent is rejected. When product semantics require SQL, the answer selects `manual_sql`. Small one-shot work can go into a stable phase edit pocket. Agents can instead attach typed work directly to the hint; large repeatable work becomes an operation artifact. This keeps intent, executable work, completion proof, and verification evidence distinct and reviewable. Durable H → W planning always runs. If the development database is unavailable, `plan` preserves and reports that durable result with development marked `not_available`; it does not make developers select a mode. Supplying a `--dev-hint` is an intentional request to reconcile D → W and therefore requires the development database. ## One plan, one merge, one deployment A team workflow becomes straightforward: 1. Export W from the models in the feature checkout. 2. Run `plan NAME` once, then repeat it as the feature evolves. 3. Capture semantic decisions and edit product-owned SQL in the bundle. 4. Rebase and rerun `plan`; review only what the new base invalidated. 5. Run `verify --check` in CI. 6. Merge the application change and its one bundle together. 7. The release system applies expand, deploys the application, supplies expiring evidence for every potential writer, requires `contract check` to pass, then applies the assertion-bearing contract. 8. The merged bundle becomes H for the next feature. The new application must work on both sides of contract. If it cannot, keep the compatibility shape and remove it in a later feature plan. One plan is tied to one merge and one application deployment—not to every keystroke that happened before them. ## Scenario map | What the developer encounters | What `plan` does | | --- | --- | | Models edited repeatedly before merge | Replaces the same bundle generation. | | Accepted migrations arrive during the feature | Restacks the same `PlanID` on the new H. | | A valid rename/drop/backfill decision still applies | Carries its scoped evidence forward. | | The participating schema meaning changed | Invalidates the stale decision and asks again. | | A draft-only name was already applied to D | Keeps H → W clean and offers a separate D → W reconciliation. | | Branch switching leaves extra objects in D | Preserves them in workspace mode. | | Incoming history absorbs generated feature work | Removes the redundant generated bundle. | | Incoming history collides with edited phase SQL | Stops with an explicit three-way handoff. | | Production differs from accepted history | Reports drift separately; never smuggles a repair into the feature plan. | The output is still SQL. The product is the continuously reconciled understanding that produced it. ## Document: Expand and contract Source: https://onwardpg.solberg.is/concepts/expand-contract.md The compatibility window is the product: ```text old app + old schema | v EXPAND <- safe while old writers and readers exist | v old app + expanded schema | v DEPLOY <- new app understands both schema contracts | v new app + expanded schema | v DRAIN <- prove old traffic is gone | v CONTRACT <- validate, tighten, remove compatibility ``` ## Example: add a required column The end state may be simple: ```sql CREATE TABLE app.bookings ( id bigint PRIMARY KEY, status text NOT NULL ); ``` Adding it as required in one operation can break old inserts. A compatible route starts with the loose shape: ```sql ALTER TABLE "app"."bookings" ADD COLUMN "status" text; ``` That is all the generated expand phase contains for this change. Legacy code can still insert a booking without `status`, including after expand has run. A one-time backfill in expand would therefore race those writers and could not establish the `NOT NULL` precondition. The first plan asks whether contract should merely assert, run reviewed cleanup, or stay loose for another deployment. Choosing `manual_sql` creates one application-owned cleanup pocket and an exact generated assertion: ```sql -- contract.sql -- onwardpg:edit begin stmt-sha256-1a5377b536479569445c7585eb95560c9e977f5d696557db5f28031f9789eec6 -- PRODUCT-SPECIFIC SQL: Provide reviewed reconcile_contract_sql SQL for app.bookings.status -- Verify: SELECT NOT EXISTS (SELECT 1 FROM "app"."bookings" WHERE "status" IS NULL); -- ONWARDPG TODO: replace this comment with reviewed SQL for reconcile_contract_sql on app.bookings.status. -- Planner analysis: Supply reviewed post-drain cleanup/backfill SQL and at least one read-only Boolean verification query for column:app:bookings:status. -- Expected effect: complete the named operation and converge to the desired catalog state. -- onwardpg:edit end stmt-sha256-1a5377b536479569445c7585eb95560c9e977f5d696557db5f28031f9789eec6 -- onwardpg:batch transactional -- Batch batch-contract-002: transactional. -- Review: safety=review; hazards=contract_data_assertion,table_scan_possible; requires_gates=writers:legacy. -- Suggested session timeouts: statement_timeout=20m, lock_timeout=3s. DO $onwardpg$ BEGIN IF NOT COALESCE((SELECT NOT EXISTS (SELECT 1 FROM "app"."bookings" WHERE "status" IS NULL)), false) THEN RAISE EXCEPTION 'onwardpg contract gate failed: data:1c16b884027de910'; END IF; END $onwardpg$; -- Review: safety=review; hazards=table_scan,access_exclusive_lock,compatibility_removal; requires_gates=data:1c16b884027de910,writers:legacy. ALTER TABLE "app"."bookings" ALTER COLUMN "status" SET NOT NULL; ``` For a product whose correct historical value is `pending`, the developer can replace only that pocket: ```sql UPDATE "app"."bookings" SET "status" = 'pending' WHERE "status" IS NULL; ``` The generated contract assertion remains between that cleanup and enforcement. The same postcondition can also live in `verify.sql`, so disposable verification names the product assumption independently: ```sql -- onwardpg:assert booking_status_present SELECT NOT EXISTS ( SELECT 1 FROM "app"."bookings" WHERE "status" IS NULL ); ``` Deploy code that writes and reads `status`, then drain every legacy writer. `onwardpg verify` executes those exact edited bytes on disposable PostgreSQL, accepts them only after they reach the requested schema, and `verify --check` proves the verified files have not drifted. It also records the graph observed after expand. After the application deploy, `onwardpg contract check` can compare production with that checkpoint and require live data plus writer evidence. The deployment system still owns execution and approval. See [contract readiness](/concepts/contract-readiness/). ## The one-deployment rule The new application version must work immediately after expand and still work after contract. If removing the old shape requires another code release, keep the compatibility object and choose a split plan. The later feature owns its removal. Backfills are not a third deployment phase. They are explicit work scheduled where old and new contracts remain safe. Contract should never be used as a vague container for “do risky things later.” ## Document: Contract readiness Source: https://onwardpg.solberg.is/concepts/contract-readiness.md Expand may deliberately accept more states than either endpoint schema. That is what lets legacy and new code overlap. Contract may tighten that envelope only after two different facts are true: 1. overlap rows satisfy the desired invariant; and 2. every potential legacy writer is upgraded, drained, isolated, or read-only. `onwardpg verify` cannot establish either production fact. It executes the exact bundle on disposable PostgreSQL, proves convergence, and receipts the catalog it observed immediately after expand. `onwardpg contract check` uses that checkpoint later without applying SQL to the caller database. ## The planner captures the repair decision Suppose the old CHECK accepts `shared | legacy` and the new CHECK accepts `shared | new`. Neither constraint is a safe overlap. Expand removes the old one so both application releases can write. The planner then asks how contract will reconcile rows admitted during that window: - `assert_only` runs the exact generated assertion when no repair is expected; - `manual_sql` captures reviewed cleanup plus the required Boolean contract gate; or - `split_plan` retains the loose schema and restores enforcement after another deployment. Where PostgreSQL supports `NOT VALID`, contract fences new writes before repair: ```sql ALTER TABLE "app"."delivery" ADD CONSTRAINT "delivery_tier_check" CHECK (tier IN ('shared', 'new')) NOT VALID; UPDATE "app"."delivery" SET tier = 'new' WHERE tier = 'legacy'; DO $onwardpg$ BEGIN IF NOT COALESCE(( SELECT NOT EXISTS ( SELECT 1 FROM "app"."delivery" WHERE (tier IN ('shared', 'new')) IS FALSE ) ), false) THEN RAISE EXCEPTION 'onwardpg contract gate failed: data:...'; END IF; END $onwardpg$; ALTER TABLE "app"."delivery" VALIDATE CONSTRAINT "delivery_tier_check"; ``` `IS FALSE` preserves PostgreSQL CHECK semantics: NULL is accepted. Foreign-key gates use typed ordered columns and MATCH SIMPLE/FULL behavior. Supported btree uniqueness preserves key expressions, predicates, collations, and NULL semantics. Shapes onwardpg cannot prove exactly require reviewed Boolean SQL or a split plan. For those manual shapes, the generated assertion is a bounded edit pocket in `contract.sql`. Edit that SQL, not the gate metadata. Successful verification writes the resolved query to `contract-gate-overrides.json`, bound to the generated gate ID and history entry. Optional `verify.sql` assertions remain clone-only semantic examples or postconditions; they do not authorize live contract. Unique and exclusion builds have no general `NOT VALID` fence. Those transitions also require a write-fence attestation so a concurrent writer cannot reopen a conflict between the exact probe and enforcement build. ## Check production read-only ```sh export PROD_READONLY_DATABASE_URL='postgres://...' onwardpg contract check \ --target app \ --bundle delivery-tier \ --environment production \ --database-env PROD_READONLY_DATABASE_URL \ --evidence deploy-readiness.json \ --statement-timeout 30s ``` The result is `ready`, `needs_evidence`, `blocked`, or `stale`. In one `REPEATABLE READ, READ ONLY` transaction it: 1. validates the bundle and requires it to be the history head; 2. compares production with the receipted post-expand graph; 3. evaluates exact read-only data gates; and 4. validates expiring writer evidence bound to the environment, PlanID, generation, history entry, and release. Writer evidence covers web deployments, workers, schedules, queues and retries, connection pools, previews, and ad-hoc writers. A scaled-to-zero preview with production write credentials is still a potential writer: it can reconnect. Zero current sessions is therefore not drain evidence. `ready` is operational feedback, not permission that survives forever. Contract SQL repeats its data assertion after cleanup, immediately before enforcement. Your release system still owns batch execution, lock budgets, replica health, approval, and the final cutover. ## Document: Decisions and manual work Source: https://onwardpg.solberg.is/concepts/decisions.md Two schema snapshots show facts, not intent. If `display_name` disappears while `full_name` appears, that could be a rename, a replacement with different meaning, or two unrelated changes. onwardpg stops and prints a semantic hint. ## Confirm a rename ```sh onwardpg plan rename-display-name \ --hint '{"kind":"rename","object":"column","from":["app","accounts","display_name"],"to":["app","accounts","full_name"]}' ``` For an eligible same-type rename, the planner can create a temporary second column and deterministic dual-write bridge. Existing rows still need an explicit strategy: reviewed manual SQL, an acknowledged single transaction for a known-small table, or a split plan. ## Change `text` to `integer` The catalog proves the types differ. It cannot decide whether an empty string becomes `NULL`, fails, or maps to a product value. After the `type_change` hint selects `manual_sql`, actual generated output contains two blocking pockets: ```sql -- expand.sql -- ONWARDPG TODO: replace this comment with reviewed EXPAND SQL for column:app:accounts:age (text -> integer). -- Establish both old and new interfaces, synchronization/conflict behavior, and any initial backfill while old code is live. -- Do not use a direct ALTER TYPE here: this plan surrounds one rolling application deployment. -- contract.sql -- ONWARDPG TODO: replace this comment with reviewed CONTRACT SQL for column:app:accounts:age (text -> integer). -- After pre-deployment writers drain, perform final catch-up/assertions, remove compatibility objects, and converge to PostgreSQL type integer. ``` The developer supplies a real compatibility bridge in those pockets. The conversion rule inside that bridge might be `NULLIF(trim(age), '')::integer`; a bare in-place `ALTER TYPE` would not keep both application contracts available during a rolling deployment. Add assertions for every data-dependent assumption: ```sql -- onwardpg:assert account_age_is_convertible SELECT NOT EXISTS ( SELECT 1 FROM app.accounts WHERE age IS NOT NULL AND trim(age) !~ '^[0-9]+$' ); ``` A minimal `manual_sql` hint carries only the decision and lets onwardpg create a marked pocket in the phase file. A coding agent may instead attach complete typed `work`, including reviewed statements and Boolean verification SQL. In either form, onwardpg accepts the resulting artifact only after clone verification succeeds. ## Why decisions expire Consumed hints are captured automatically in `decisions.json`; developers and agents do not copy internal fingerprints or resubmit accepted answers. Answers are bound to the exact participating graph scope. Unrelated schema erosion preserves them, while a later edit that changes their meaning makes them stale. This prevents a previous approval for one destructive shape from silently authorizing another. An agent that already understands the code change may provide several hints on the first `plan` call. onwardpg consumes only those that match real decisions and rejects an unused guess. See [agent-assisted planning](/agents/agent-assisted-planning/). ## Document: What verification proves Source: https://onwardpg.solberg.is/concepts/verification.md `onwardpg verify` executes only in restricted disposable PostgreSQL databases. It checks that accepted history plus the selected bundle reaches the declared desired catalog. ## It proves - the exact receipted phase SQL executes under its transactional batch rules; - the selected prefix and full continuation both replay cleanly; - every `verify.sql` assertion returns one boolean `true` row; - dependency-safe operations reach the desired typed catalog fingerprint; - the final semantic diff is empty; and - the exact catalog graph observed after expand is content-addressed in the bundle for a later read-only production comparison. For CI, `onwardpg verify --check` also rejects unreceipted edits, requires the selected bundle to be the history head, recompiles configured DDL, and rejects a stale desired fingerprint. ## It does not prove - existing production rows satisfy a conversion or backfill assumption; - locks fit your latency budget; - table scans, WAL, and replica lag are acceptable; - old application writers have drained; - hidden dynamic SQL has no dependency; - a rollback is ready; or - the release system applied either phase. Think of clone verification as a strong database-structure proof inside a deliberately limited boundary. Pair it with production preconditions, rollout telemetry, lock and statement timeouts, replica checks, and human approval for dangerous changes. For gated plans, the next boundary is [`onwardpg contract check`](/concepts/contract-readiness/). It checks the live post-expand catalog, exact data assertions, and expiring potential-writer evidence in a read-only snapshot. It still does not execute contract, and its earlier result does not replace the assertion repeated inside contract SQL. ## Document: Drizzle Source: https://onwardpg.solberg.is/guides/drizzle.md Drizzle Kit's official `export` command prints the SQL representation of the configured schema, making Drizzle models a clean desired-state source (`W`) for onwardpg ([Drizzle export reference](https://orm.drizzle.team/docs/drizzle-kit-export)). ```ts // drizzle.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "postgresql", schema: "./src/db/schema.ts", }); ``` Use the package-manager spelling your repository already owns: | pnpm | npm | yarn | bun | | --- | --- | --- | --- | | `pnpm exec drizzle-kit export --sql=true` | `npx drizzle-kit export --sql=true` | `yarn drizzle-kit export --sql=true` | `bunx drizzle-kit export --sql=true` | Install dependencies and approve any package-manager build scripts before running onwardpg. Export is deliberately checked as a read-only operation, so an exporter invocation that changes lockfiles or package-manager state is rejected instead of making planning depend on a dirty side effect. Every model must be reachable from the configured file or glob. If functions, triggers, extensions, policies, or other PostgreSQL objects live outside Drizzle, append their complete deterministic definitions in a small export wrapper. An incomplete export is an incomplete desired schema, not an onwardpg ignore. :::caution[`pgSchema()` needs its schema DDL] In a blind Drizzle Kit test, `export` emitted tables qualified with `"app"` but did not emit `CREATE SCHEMA "app"`. PostgreSQL correctly rejected that stream. If the model uses `pgSchema()`, use the checked [`export-schema.sh`](https://github.com/jokull/onwardpg/blob/main/examples/frameworks/drizzle/export-schema.sh) and list every named schema before invoking Drizzle Kit. ::: ```toml version = 1 bundle_root = "onward-bundles" [targets.app] schema_command = ["bash", "examples/frameworks/drizzle/export-schema.sh"] dev_database_env = "ONWARDPG_DEV_DATABASE_URL" scratch_database_env = "ONWARDPG_SCRATCH_DATABASE_URL" dev_mode = "workspace" ``` ## One feature, start to finish ```sh onwardpg config check onwardpg plan add-booking-status ``` The first command proves deterministic export and source coverage. `plan` writes the cumulative PR bundle and separately reports any SQL needed only to reconcile the current dev database. For a required field, select a choice from `next_actions`; `manual_sql` writes the exact contract edit pocket before returning `needs_action`. After editing, run `onwardpg verify`. Commit the Drizzle model and onwardpg bundle, but do not generate/apply a Drizzle production migration for the same change. The production runner owns onwardpg expand → deploy → drain/reconcile → contract. After merge, pull accepted history and run `onwardpg plan` once more. When the bundle has been absorbed into that history, onwardpg retires its worktree-local PlanID automatically. Begin the next feature with `onwardpg plan next-feature`. On branch switches, onwardpg parks and restores PlanIDs from the bundles visible in each checkout; there are no lifecycle commands to coordinate. For a rename or type conversion, show onwardpg both sides of the application contract: typed Drizzle code for the new writer and any raw SQL/queue/preview writer that can still use the legacy shape. That evidence determines the hint and later writer-drain attestation; the schema export alone cannot reveal it. ## Document: Django Source: https://onwardpg.solberg.is/guides/django.md Django's final migration `ProjectState` is the authoritative desired schema (`W`). onwardpg materializes that state in a new disposable database and plans the rolling-deploy route from accepted history (`H`) to that destination. Run `makemigrations --check` so models and migration state agree. Django must not also execute physical DDL for the same production change. ## Install the hermetic exporter Copy [`export-schema.sh`](https://github.com/jokull/onwardpg/blob/main/examples/frameworks/django/export-schema.sh) and [`export-current-model-state.py`](https://github.com/jokull/onwardpg/blob/main/examples/frameworks/django/export-current-model-state.py) into your project's `scripts/` directory. The wrapper: - creates and force-drops a randomly named database; - sets `PYTHONDONTWRITEBYTECODE=1` so imports do not dirty the checkout; - refuses a `pg_dump` whose major differs from the server; - asks Django's `MigrationLoader` for the final `ProjectState`, then uses `SchemaEditor` to materialize those models without executing historical database/data operations; - sends Django chatter to stderr and only deterministic DDL to stdout; and - removes Django's runtime migration-recorder table from the desired application schema. Add an export-only database alias: ```py # settings.py import dj_database_url DATABASES["onward_schema"] = { **dj_database_url.config(env="DJANGO_SCHEMA_DATABASE_URL"), "ENGINE": "django.db.backends.postgresql", } ``` Point the wrapper at an administrative **disposable PostgreSQL control plane**, never production: ```sh export ONWARDPG_DJANGO_ADMIN_URL='postgres://.../postgres' bash scripts/export-schema.sh > /tmp/django-schema.sql ``` This deliberately does **not** replay `RunPython`, `RunSQL`, or custom migration operations. Those describe history-dependent work, not the final declarative catalog. Product data changes belong in onwardpg's receipted reconciliation/operation workflow; migration state belongs in Django's ledger. Django documents the distinction between database and state operations in `SeparateDatabaseAndState` ([migration guide](https://docs.djangoproject.com/en/6.0/topics/migrations/), [operations reference](https://docs.djangoproject.com/en/6.1/ref/migration-operations/)). ```toml [targets.app] schema_command = ["bash", "scripts/export-schema.sh"] dev_database_env = "ONWARDPG_DEV_DATABASE_URL" scratch_database_env = "ONWARDPG_DJANGO_ADMIN_URL" dev_mode = "workspace" ``` `onwardpg config check` executes the exporter twice. Byte differences, checkout mutations, command failures, and underlying stderr are reported before planning. ## Normal development loop 1. Change models and run `python manage.py makemigrations`. 2. Convert the new migration to a state-only production record as described below. This does not hide the field from `W`: `ProjectState` includes `state_operations`, not the migration's empty `database_operations` list. 3. Run `onwardpg plan feature-name` and follow `next_actions`. 4. Apply only the returned development reconciliation SQL to your dev database. 5. Review and commit the Django migration plus the onwardpg bundle. Django's one-off default prompt may create an `AddField(... preserve_default=False)` operation. That default is migration-time assistance, not the final database default. If the final model is required without a default, onwardpg still sees `NOT NULL` in `W` and stages nullable expand, reconciliation, and contract. ## Give one runner production ownership Keep Django's migration graph current without letting it execute the same physical DDL. Wrap the generated operations as state operations with no database operations: ```py from django.db import migrations, models class Migration(migrations.Migration): dependencies = [("bookings", "0041_previous")] operations = [ migrations.SeparateDatabaseAndState( database_operations=[], state_operations=[ migrations.AddField( model_name="booking", name="status", field=models.TextField(), ), ], ) ] ``` The release sequence is then unambiguous: 1. the migration runner applies onwardpg `expand.sql`; 2. the new Django release deploys while old and new writers overlap; 3. writer evidence, receipted reconciliation, and `contract check` authorize `contract.sql`; 4. the runner applies contract; and 5. `python manage.py migrate --noinput` records the state-only migration. Do not run step 5 before the physical plan is known to have completed, or a later Django migration may assume schema that contract has not installed. If contract succeeds but state recording fails, rerun the state-only migration. If expand or contract fails, leave Django state unapplied and resume the onwardpg release. `migrate --fake` can mark a normal migration applied, but Django explicitly warns that incorrect fake state may require manual recovery; state-only operations make the ownership decision reviewable in source ([command reference](https://docs.djangoproject.com/en/6.0/ref/django-admin/)). This workflow is forward-only at the database boundary. Reversing Django state does not undo an onwardpg production contract; author a new forward plan for rollback. ## Document: Prisma Source: https://onwardpg.solberg.is/guides/prisma.md Prisma’s `migrate diff` can render a script from an empty source to the current declarative schema. Prisma 7 loads its CLI datasource from `prisma.config.ts`, even though this particular comparison uses the schema as its destination. Keep a root config and make `DATABASE_URL` available to the exporter: ```ts import "dotenv/config" import { defineConfig, env } from "prisma/config" export default defineConfig({ schema: "prisma/schema.prisma", datasource: { url: env("DATABASE_URL") }, }) ``` This follows Prisma’s current [configuration reference](https://www.prisma.io/docs/orm/reference/prisma-config-reference) and [`migrate diff` source model](https://www.prisma.io/docs/cli/migrate/diff). ```sh pnpm exec prisma migrate diff \ --from-empty \ --to-schema ./prisma/schema.prisma \ --script ``` In a blind Prisma 7.8 test, a missing datasource configuration made this command exit successfully with empty stdout. Use the checked wrapper, which rejects that unsafe ambiguity: ```toml [targets.app] schema_command = [ "bash", "examples/frameworks/prisma/export-schema.sh" ] dev_database_env = "ONWARDPG_DEV_DATABASE_URL" scratch_database_env = "ONWARDPG_SCRATCH_DATABASE_URL" dev_mode = "workspace" ``` Copy [`export-schema.sh`](https://github.com/jokull/onwardpg/blob/main/examples/frameworks/prisma/export-schema.sh) into the application repository and adjust its package-manager invocation if needed. If pnpm blocks Prisma’s engine install, approve the prompted `@prisma/engines` and `prisma` build scripts before trusting export output. Run `onwardpg config check` whenever Prisma or its engines change. It executes the exporter twice and requires identical bytes. Native type annotations matter: for example, Prisma `String` may map to different PostgreSQL types depending on the schema declaration. Prisma Schema Language does not represent every PostgreSQL feature. If your application relies on custom functions, triggers, extensions, or other SQL, wrap the command in a script that appends their deterministic full definitions. :::note[Migration ownership] Keep Prisma’s model as the desired-state source, but do not run `prisma migrate deploy` and the onwardpg bundle for the same production change. Pick one system to own application of that DDL. ::: ## Document: SQLAlchemy and Alembic Source: https://onwardpg.solberg.is/guides/sqlalchemy.md Alembic can render the upgrade from base to the current head without connecting to a database. This works well when the full revision chain can build a clean database. ```sh alembic upgrade head --sql ``` Create an exporter that removes Alembic’s runtime revision table from the desired application schema: ```sh #!/usr/bin/env bash set -euo pipefail alembic upgrade head --sql printf '%s\n' 'DROP TABLE IF EXISTS alembic_version;' ``` Route log output to stderr in `alembic.ini` or `env.py`; stdout must contain SQL only. ```toml [targets.app] schema_command = ["bash", "scripts/export-alembic-schema"] dev_database_env = "ONWARDPG_DEV_DATABASE_URL" scratch_database_env = "ONWARDPG_SCRATCH_DATABASE_URL" dev_mode = "workspace" ``` If the project has multiple Alembic heads, use `heads` or merge the branches intentionally. If offline rendering cannot execute a custom migration, use the Django-style adapter instead: apply `alembic upgrade head` to a fresh disposable PostgreSQL database and `pg_dump --schema-only --no-owner --no-privileges` the result. Data migrations and dialect-dependent Python must remain deterministic from empty. As with every framework adapter, the output describes the destination; onwardpg generates the compatibility route. ## Document: Agent-assisted planning Source: https://onwardpg.solberg.is/agents/agent-assisted-planning.md onwardpg is designed to be operated by a coding agent that already understands the feature, repository, and deployment environment. It does not embed an agent. Instead, it gives one a narrow, inspectable protocol for doing migration work. ## Point your agent at one file The canonical operating contract is [`https://onwardpg.solberg.is/skill.md`](https://onwardpg.solberg.is/skill.md). It is intentionally shorter and more procedural than this human guide. Give the agent that file first; it can load the linked decision, schema-state, and production-evidence references only when needed. ```text Read and follow https://onwardpg.solberg.is/skill.md for this migration. Inspect this repository's schema source, accepted history, and affected code. Maintain one evolving onwardpg plan. Supply only evidence-backed decisions, edit only planner-named SQL pockets, never apply to production, run onwardpg verify, and report what disposable verification still cannot prove. ``` For a repeatable team workflow, pin the `skills/onwardpg` package from a reviewed onwardpg release in the application repository and add a short pointer to its `AGENTS.md`. Claude Code users can import the same instructions from `CLAUDE.md` rather than maintaining a second copy. Review skill updates like any other development dependency. ```md ## PostgreSQL schema changes Before modifying the database schema, read and follow `.agents/skills/onwardpg/SKILL.md`. ``` [`llms.txt`](/llms.txt) indexes the complete documentation and every page has a clean `.md` form. It is discovery context, not a substitute for the operating skill. [`llms-full.txt`](/llms-full.txt) is available for bulk ingestion but should not be the default prompt payload. The pairing is useful because the two sides know different things: ```text coding agent onwardpg ──────────────────────────────── ──────────────────────────────── feature intent PostgreSQL catalog truth application read/write paths dependency-safe operation order product conversion rules decision scope and stale detection deployment conventions expand/contract phase constraints reviewed backfill SQL disposable convergence evidence ``` ## Why can an agent decide early? Hints describe semantic intent, not planner internals. If the agent knows from the code change that `display_name` became `full_name`, it can provide that on the first call: ```json [ { "kind": "rename", "object": "column", "from": ["app", "accounts", "display_name"], "to": ["app", "accounts", "full_name"] }, { "kind": "rename_backfill", "name": ["app", "accounts", "display_name"], "strategy": "manual_sql" } ] ``` ```sh onwardpg plan rename-display-name --hints-file onwardpg-hints.json ``` The planner consumes a hint only if a real dependency-ordered decision matches it. A contradiction, stale answer, impossible transition, or unused guess fails. The agent can be proactive without being allowed to overrule the catalog. Accepted hints are captured in the evolving bundle. On the next model edit or rebase, the agent runs `plan` again; onwardpg carries only the decisions whose scoped meaning still holds. ## Why is the output agent-friendly? - JSON is the default and each document leads with a status or error code. - Exit codes distinguish decisions, SQL edits, unsupported state, and broken evidence. - High-level `plan` reports status, exact decision choices, edit requirements, and safe `workspace_fast_forward` SQL; the lower-level `draft` protocol also supplies `next_action`. - Hazards, phases, batches, dependencies, and exact object identities are structured. - Product SQL lives in stable edit pockets instead of being smuggled through JSON. - `verify` executes the exact edited artifact that will be reviewed. Choice argv is cumulative across ephemeral development decisions: always use the newest response, whose command carries earlier `applied_hints`. It assumes the same repository directory, environment, and framework toolchain as the planner invocation; it deliberately never embeds credentials. A good agent loop is deliberately boring: ```text inspect feature -> export schema -> plan -> answer intent -> edit SQL ^ | +------------- re-plan after every change ----------+ | verify ``` ## Let the agent read production—carefully Some decisions need data evidence. For a `text` → `integer` transition, schema catalogs cannot tell the agent whether production contains blanks, whitespace, signs, decimals, or malformed strings. Giving the coding agent read-only MCP access to a production replica or tightly restricted production role can make the plan better informed. This is external evidence; onwardpg itself does not query production rows during `plan` or `verify`. Prefer shape and count queries over copying raw values: ```sql SELECT count(*) AS rows, count(*) FILTER (WHERE age IS NULL) AS nulls, count(*) FILTER (WHERE age IS NOT NULL AND trim(age) = '') AS blanks, count(*) FILTER ( WHERE age IS NOT NULL AND trim(age) <> '' AND trim(age) !~ '^[0-9]+$' ) AS non_numeric, min(length(age)) AS shortest, max(length(age)) AS longest FROM app.accounts; ``` The agent can now justify a conversion instead of assuming one: ```sql NULLIF(trim(age), '')::integer ``` It should also turn the production precondition—such as `non_numeric = 0`—into the exact reviewed Boolean contract gate requested by the planner. That gate stays in `contract.sql`, is receipted, and runs again immediately before enforcement. Disposable verification cannot prove that live rows still satisfy it; the read-only `contract check` evaluates the same effective gate after expand and writer drain. :::danger[Production access is evidence, not authority] Use a dedicated role with only `CONNECT` and `SELECT` on approved schemas, views, or columns. Enforce read-only transactions, statement timeouts, row limits, and audited access. Never give the agent onwardpg’s scratch administrator, write credentials, secrets, or unrestricted access to sensitive columns. ::: ## Turn observed shapes into a safe verification shim Do not copy production rows into Git. Instead, translate the observed *classes* of values into small synthetic examples in `verify.sql`: ```sql -- onwardpg:assert age_conversion_examples WITH examples(raw, expected) AS ( VALUES ('42'::text, 42::integer), (' 7 '::text, 7::integer), (''::text, NULL::integer), (' '::text, NULL::integer), (NULL::text, NULL::integer) ) SELECT bool_and( NULLIF(trim(raw), '')::integer IS NOT DISTINCT FROM expected ) FROM examples; ``` This assertion runs in onwardpg’s disposable verification database. It checks the intended conversion semantics across representative categories without retaining names, identifiers, or one-for-one production values. The distinction matters: - the production MCP query discovers value shapes and live preconditions; - the synthetic `VALUES` assertion tests the product rule repeatably; - the edited phase SQL performs the reviewed migration work; - the contract gate checks live rows before production enforcement; - clone verification proves the bundle executes and reaches W; and - the deployment system rechecks live preconditions, volume, locks, and drain state. Do not insert test fixtures into `expand.sql` or `contract.sql`; those files are deployment artifacts. `verify.sql` accepts read-only Boolean queries, so CTE/`VALUES` examples are the safe place for a semantic shim. ## A complete agent handoff For a product-specific type transition, ask the agent to return evidence at each boundary: 1. **Code evidence:** which readers and writers change, and whether one deployed version supports both schemas. 2. **Catalog evidence:** the `plan` output, affected dependencies, hazards, and unanswered decisions. 3. **Production evidence:** bounded read-only counts or classifications, with no secrets copied into the bundle. 4. **Intent:** semantic hints supplied up front or in response to real questions. 5. **Executable work:** reviewed edits inside generated phase pockets. 6. **Production invariant:** the planner-named Boolean gate in `contract.sql`. 7. **Semantic examples:** optional synthetic Boolean assertions in `verify.sql`. 8. **Structural proof:** successful `onwardpg verify` with an empty residual diff. 9. **Operational gates:** `contract check` over the post-expand catalog, exact data gates, and expiring writer evidence, plus lock budgets, replica health, execution, and approval owned by the release system. This makes agent participation reviewable. The agent contributes context and SQL; onwardpg constrains what that context is allowed to authorize. ## Prompting pattern Give the agent the feature and boundaries, not just “make a migration”: ```text Run onwardpg plan for this feature. Read the application paths that touch the changed columns. Supply semantic hints only when code or product context proves them. If product SQL is required, edit only the named onwardpg pockets and add the requested contract-gate Boolean in contract.sql. Add synthetic verify.sql assertions for representative conversion rules. Treat production MCP access as read-only evidence: use aggregates and value-shape classifications, do not copy sensitive rows, and record any live precondition that must be rechecked before deployment. Finish with onwardpg verify and summarize hazards that clone convergence cannot prove. ``` The result is not unattended migration generation. It is a disciplined collaboration between application understanding, PostgreSQL evidence, and explicit human review. ## Document: GitHub Actions Source: https://onwardpg.solberg.is/delivery/github-actions.md The deployment graph should encode the compatibility contract directly: ```text verify -> expand -> deploy app -> prove drain -> contract ``` The following workflow is a scaffold. Adapt authentication, artifact lookup, deployment, health checks, and drain evidence to your platform. ```yaml name: Deploy with onwardpg on: workflow_dispatch: inputs: bundle: description: Bundle directory name required: true permissions: contents: read id-token: write env: BUNDLE_DIR: migrations/onward/app/${{ inputs.bundle }} jobs: verify: runs-on: ubuntu-latest services: postgres: image: postgres:18 env: POSTGRES_PASSWORD: postgres ports: ["5432:5432"] options: >- --health-cmd "pg_isready -U postgres" --health-interval 2s --health-timeout 5s --health-retries 20 env: ONWARDPG_SCRATCH_DATABASE_URL: postgres://postgres:postgres@localhost:5432/postgres steps: - uses: actions/checkout@v4 - uses: actions/setup-go@v5 with: go-version: stable - run: go install github.com/jokull/onwardpg/cmd/onwardpg@v0.1.0-preview.1 - run: onwardpg verify --bundle "${{ inputs.bundle }}" --check expand: needs: verify runs-on: ubuntu-latest environment: production steps: - uses: actions/checkout@v4 - name: Authenticate to production run: ./scripts/auth-production - name: Apply expand env: DATABASE_URL: ${{ secrets.PRODUCTION_DATABASE_URL }} run: psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f "$BUNDLE_DIR/phases/expand.sql" deploy_app: needs: expand runs-on: ubuntu-latest environment: production steps: - uses: actions/checkout@v4 - run: ./scripts/deploy-app drain: needs: deploy_app runs-on: ubuntu-latest environment: production steps: - uses: actions/checkout@v4 - name: Prove old writers are gone run: ./scripts/wait-for-old-release-to-drain contract: needs: drain runs-on: ubuntu-latest environment: production steps: - uses: actions/checkout@v4 - name: Authenticate to production run: ./scripts/auth-production - name: Apply contract env: DATABASE_URL: ${{ secrets.PRODUCTION_DATABASE_URL }} run: psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f "$BUNDLE_DIR/phases/contract.sql" ``` ## Make drain evidence real A fixed sleep is not proof. A useful drain gate checks platform state: zero old-version instances, no old workers consuming queues, connection age below the rollout boundary, and no incompatible writer telemetry. Keep contract behind a protected GitHub environment when a human must confirm those facts. ## Production hardening - Download a reviewed, immutable bundle artifact rather than trusting a mutable workspace path. - Use workload identity instead of long-lived database credentials where possible. - Set appropriate `lock_timeout` and `statement_timeout` for each generated batch. - Record the exact bundle digest and phase outcome in deployment receipts. - Stop before contract if deployment or drain evidence is ambiguous; expand is designed to leave a compatibility window open. ## Document: Production runbook Source: https://onwardpg.solberg.is/delivery/production-runbook.md onwardpg gives you reviewed SQL and clone-convergence evidence. The production operator supplies environment evidence through a deliberately weak observer. ## Provision the contract observer Create one inert grant role and one login role. Run the grants as the owner of the application schemas and relations; repeat the relation grant when expand adds a table or view. ```sql CREATE ROLE onwardpg_observer NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS; CREATE ROLE onwardpg_contract LOGIN PASSWORD 'use-your-secret-manager' NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS; GRANT onwardpg_observer TO onwardpg_contract; GRANT CONNECT ON DATABASE app_production TO onwardpg_contract; GRANT USAGE ON SCHEMA app TO onwardpg_observer; GRANT SELECT ON ALL TABLES IN SCHEMA app TO onwardpg_observer; ``` `contract check` accepts only direct grants to the authenticated role or its direct NOLOGIN grant role. It rejects nested/built-in memberships, login or elevated grant roles, write privileges, grant options, and incomplete access. The readiness receipt lists every projected observer-only grant. Grants to application roles or `PUBLIC` remain catalog drift. Because this observer cannot see rows hidden by RLS completely, onwardpg refuses readiness when application RLS is enabled instead of claiming a partial assertion passed. ## Before expand - Confirm the bundle is the reviewed immutable artifact and `onwardpg verify --check` passed on the same PostgreSQL major. - Read every statement’s hazards, transaction boundary, and timeout guidance. - Estimate table scans, rewrites, index builds, WAL, lock queues, and replica impact against real relation sizes. - Confirm backups and the application rollback path. A down migration is not generated. - Ensure the new application can tolerate both the expanded and contracted shapes. ## Apply expand Run batches exactly as rendered. Nontransactional work such as concurrent index creation must remain outside a transaction. Observe lock waits, error rate, database saturation, WAL generation, and replica lag. If expand partially fails, stop. Diagnose the exact batch and reconcile forward; do not assume rerunning arbitrary SQL is idempotent. ## Deploy and drain Deploy the dual-compatible application. Prove old instances and writers are gone, including background workers, scheduled jobs, queues, stale connection pools, and rollback traffic. Run any `after_expand_before_contract` operation artifact through your deployment runner. A one-shot backfill embedded in a contract edit pocket runs later, as part of the exact contract batch, before its dependent enforcement statement. Potential writers matter, not only current sessions. Include scaled-to-zero or stale previews with production credentials and ad-hoc/third-party writers. Create expiring evidence bound to the exact PlanID, bundle entry, environment, and release. ```sh onwardpg contract check \ --target app \ --bundle "$BUNDLE" \ --environment production \ --database-env PROD_READONLY_DATABASE_URL \ --evidence deploy-readiness.json ``` `reconciliation_required` means catalog and writer evidence are ready but a named receipted cleanup/operation must run. Drive the exact `phase_path` through the release runner, then repeat this read-only check. Proceed to enforcement only on `ready`. `needs_evidence`, `reconciliation_required`, `blocked`, and `stale` are distinct states; the command never applies migration SQL. ## Apply contract Apply the exact contract with the same batch and timeout discipline. Generated contract SQL repeats data assertions after cleanup, so the prior readiness check is not stale authority. Contract may validate constraints, revoke behavior, remove bridges, or perform a final rewrite; “after deploy” does not mean “low risk.” ## After contract Confirm application health, database errors, locks, replicas, and the expected schema. Record bundle digest, phases, timestamps, operator, database major, and deployment release. Run [drift check](/reference/cli/#drift-check) on your normal operational cadence. ## Document: CLI reference Source: https://onwardpg.solberg.is/reference/cli.md This guide explains behavior and workflow. The [generated CLI help](/reference/generated-cli-help) is dumped from the current binary and is the exhaustive source for available flags and defaults. All commands emit JSON by default. Use `--target NAME` when a repository configures more than one target. ## `config check` ```sh onwardpg config check ``` Validate config and database boundaries, deterministically compile desired DDL, materialize it in disposable PostgreSQL, and verify accepted history. ## `init` ```sh onwardpg init ``` Create the first clone-verified history entry. It refuses a non-empty target history. ## `plan` ```sh onwardpg plan [NAME] \ [--target NAME] [--bundle ID] [--config FILE] \ [--purpose feature|repair|contract] \ [--hint JSON] [--hints-file FILE] \ [--dev-hint JSON] [--dev-hints-file FILE] \ [--output json|text|sql] ``` Start or revise one worktree-local active migration. The durable comparison is accepted history to working DDL. When a development database is configured, `--output sql` prints a separate direct development reconciliation; onwardpg does not apply it. Planner rendering and ignore flags are listed in the generated help. Exit status `2` means the plan needs a semantic decision or editable SQL, not that onwardpg guessed a result. ## `status` ```sh onwardpg status [--target NAME] [--config FILE] ``` Report the local active PlanID, selected bundle, and whether its parent is current. This is Git-independent and does not contact PostgreSQL. ## `verify` ```sh onwardpg verify [--bundle NAME] onwardpg verify --check onwardpg verify --through expand ``` With no `--bundle`, verify selects the worktree's active plan. It replays exact history and bundle bytes in disposable databases, runs assertions, and proves catalog convergence. `--check` is the read-only CI gate; partial verification proves both the prefix and its continuation without claiming either ran in a real environment. Positional bundle names are rejected. ## `contract check` ```sh onwardpg contract check \ [--target NAME] \ [--bundle NAME] \ --environment production \ --database-env PROD_READONLY_DATABASE_URL \ [--evidence deploy-readiness.json] \ [--statement-timeout 30s] [--config .onwardpg.toml] ``` Require the selected bundle to be the history head, compare the caller database with its receipted post-expand graph, evaluate exact data gates, and validate expiring writer evidence. The connection runs one repeatable-read, read-only snapshot; this command has no production migration execution path. Its report carries bundle/PlanID identity, expected and observed fingerprints, gate results, findings, and a digest. `--statement-timeout` defaults to 30 seconds. See [contract readiness](/concepts/contract-readiness/). ## `drift check` ```sh onwardpg drift check --database "$PRODUCTION_DATABASE_URL" ``` Inspect the supplied database read-only and compare it with accepted desired state. A finding is evidence to investigate, never permission for onwardpg to change production. ## `dev plan` ```sh onwardpg dev plan --output text ``` Lower-level development reconciliation from the read-only dev catalog to working DDL. Workspace mode preserves absence-only local objects to make branch switching less destructive. For the full preview protocol and lower-level `draft`, `history status`, selectors, and planner flags, see the repository’s canonical `docs/cli.md`. ## Document: Generated CLI help Source: https://onwardpg.solberg.is/reference/generated-cli-help.md This page is generated from the current binary. For workflow, semantics, and examples, read the [CLI guide](/reference/cli). ## `onwardpg` ```text Usage: onwardpg [options] Everyday workflow: config check validate configuration, DDL, databases, and history init create a replayable history ground floor plan create, revise, or restack one feature migration status inspect the worktree-local active feature plan verify clone-verify one exact bundle contract check check live contract readiness read-only drift check compare replayed history with a live catalog read-only Diagnostics and compatibility: diff diff two explicit schema sources history status inspect the repository-local hash chain dev plan diff the development catalog against desired DDL draft explicit-history compatibility bundle command version print the onwardpg build version onwardpg generates and verifies plans; it never applies them to caller databases. ``` ## `onwardpg config check` ```text Usage of config check: -config string repository configuration path (default ".onwardpg.toml") ``` ## `onwardpg init` ```text Usage of init: -bundle string root history bundle identifier (default "baseline") -concurrent-indexes create standalone indexes concurrently -config string repository configuration path (default ".onwardpg.toml") -ignore value validated catalog selector to exclude -ignore-extension-version value extension name whose version changes should be ignored; repeat for multiple names -target string configured database target name ``` ## `onwardpg plan` ```text Usage: onwardpg plan [NAME] [options] -bundle string explicit existing feature bundle identifier -cascade-drops emit CASCADE for schema and table drops -concurrent-indexes create standalone indexes concurrently -config string repository configuration path (default ".onwardpg.toml") -dev-hint value development-workspace semantic JSON hint; repeat for multiple decisions -dev-hints-file string JSON array of development-workspace semantic hints -hint value semantic JSON hint; repeat for multiple decisions -hints-file string JSON array of semantic hints -if-exists emit IF EXISTS for schema and table drops -if-not-exists emit IF NOT EXISTS for schema and table creation -ignore value validated catalog selector to exclude -ignore-extension-version value extension name whose version changes should be ignored; repeat for multiple names -output string output format: sql, text, or json (default "json") -purpose string feature, repair, or contract (default "feature") -schema-qualifier value scope to one schema and render names using this qualifier -target string configured database target name ``` ## `onwardpg status` ```text Usage: onwardpg status [options] -config string repository configuration path (default ".onwardpg.toml") -target string configured database target name ``` ## `onwardpg verify` ```text Usage of verify: -bundle string bundle to verify -check read-only verification; reject unreceipted edits -config string repository configuration path (default ".onwardpg.toml") -target string configured database target name -through string last phase to execute: expand or contract (default "contract") ``` ## `onwardpg contract check` ```text Usage of contract check: -bundle string history-head bundle to check -config string repository configuration path (default ".onwardpg.toml") -database-env string environment variable containing a read-only PostgreSQL URL -environment string deployment environment identity -evidence string writer-drain evidence JSON -statement-timeout duration timeout for each read-only data gate (default 30s) -target string configured database target name ``` ## `onwardpg drift check` ```text Usage of drift check: -config string repository configuration path (default ".onwardpg.toml") -database string live PostgreSQL URL inspected read-only -ignore value validated catalog selector to exclude -target string configured database target name ``` ## `onwardpg diff` ```text Usage: onwardpg diff --from SOURCE --to SOURCE [options] -cascade-drops emit CASCADE for schema and table drops -concurrent-indexes create standalone indexes concurrently -dev-url string PostgreSQL admin URL for disposable materialization databases -from string current PostgreSQL URL or CREATE-statement SQL file -hint value semantic JSON hint; repeat for multiple decisions -hints-file string JSON array of semantic hints -if-exists emit IF EXISTS for schema and table drops -if-not-exists emit IF NOT EXISTS for schema and table creation -ignore value selector to exclude -ignore-extension-version value extension name whose version changes should be ignored; repeat for multiple names -output string output format: text or json (default "json") -schema-qualifier value scope to one schema and render names using this qualifier (empty means unqualified) -to string desired PostgreSQL URL or CREATE-statement SQL file ``` ## `onwardpg history status` ```text Usage of history status: -bundle string selected mutable bundle to exclude from its base -config string repository configuration path (default ".onwardpg.toml") -target string configured database target name ``` ## `onwardpg dev plan` ```text Usage of dev plan: -cascade-drops emit CASCADE for schema and table drops -concurrent-indexes create standalone indexes concurrently -config string repository configuration path (default ".onwardpg.toml") -hint value semantic JSON hint; repeat for multiple decisions -hints-file string JSON array of semantic hints -if-exists emit IF EXISTS for schema and table drops -if-not-exists emit IF NOT EXISTS for schema and table creation -ignore value validated catalog selector to exclude -ignore-extension-version value extension name whose version changes should be ignored; repeat for multiple names -output string output format: text or json (default "json") -schema-qualifier value scope to one schema and render names using this qualifier -target string configured database target name ``` ## `onwardpg draft` ```text Usage of draft: -after string exact accepted head_ref this feature bundle must follow -bundle string stable logical feature bundle identifier -cascade-drops emit CASCADE for schema and table drops -concurrent-indexes create standalone indexes concurrently -config string repository configuration path (default ".onwardpg.toml") -create assert this is the bundle's first invocation -hint value semantic JSON hint; repeat for multiple decisions -hints-file string JSON array of semantic hints -if-exists emit IF EXISTS for schema and table drops -if-not-exists emit IF NOT EXISTS for schema and table creation -ignore value validated catalog selector to exclude -ignore-extension-version value extension name whose version changes should be ignored; repeat for multiple names -output string output format: text or json (default "json") -purpose string feature, repair, or contract (default "feature") -schema-qualifier value scope to one schema and render names using this qualifier -target string configured database target name ``` ## `onwardpg version` ```text Usage: onwardpg version ``` ## Document: Safety boundary Source: https://onwardpg.solberg.is/reference/safety.md onwardpg is a planner with one execution surface: random disposable PostgreSQL databases created through the configured scratch administrator. It does not apply migrations to caller-owned targets. ## Scratch isolation Each materialization uses a random, short-lived login that owns only its random database and lacks `SUPERUSER`, `CREATEDB`, `CREATEROLE`, `REPLICATION`, and `BYPASSRLS`. The administrative identity only provisions and cleans up. The scratch cluster must still provide required roles, languages, and extension packages. Extensions needing superuser installation or externally owned objects may require provisioning outside onwardpg’s database-local boundary. ## Fail-closed planning Unsupported or ambiguous catalog shapes block instead of comparing equal. Rename, destructive, cast, authorization, and backfill decisions are bound to exact graph fingerprints. Product-specific SQL remains an editable TODO until it executes and converges in verification. Every contract statement that restores row enforcement has a typed disposition: an exact data/writer gate or a narrow catalog/atomic proof. A hazard label alone cannot authorize tightening. `contract check` evaluates live catalog, data, and writer evidence read-only, while contract SQL repeats data assertions at the actual enforcement boundary. Narrow ignore selectors acknowledge a blind spot; they do not declare ignored objects equivalent. Every selector is validated and exact exclusions are reported. ## Reviewer ownership You remain responsible for: - product semantics and application query compatibility; - production data validity and backfill progress; - lock duration, table size, WAL, replicas, and availability; - rollout, drain, and rollback evidence; - secrets, authentication, and production application of SQL; and - dynamic SQL dependencies PostgreSQL itself does not catalog. Review generated files as deployment code. A green verification result is strong structural evidence, not a production safety oracle.