pg_aqueduct

Declarative schema evolution and migration for stream-table DAGs.

pg_aqueduct is the missing migration tool for teams running pg_trickle in production. Where Atlas manages relational schema and Terraform manages infrastructure, pg_aqueduct manages the third axis that neither tool covers: the evolution of a streaming, incrementally-maintained DAG of materialized views over time — without losing differential state, without taking the pipeline offline, and without making the topology of your stream tables your problem to figure out by hand.

Status: v0.20.0 — Executor Architecture, Multi-Tenant Catalog & CLI Polish.

The Problem

If you operate a pg_trickle deployment, your stream-table DAG is your most valuable database asset. It is also the hardest one to evolve safely. Adding a column to a base table, changing an aggregation, splitting a node into two — any of these changes today requires a drop_stream_table() followed by a full recreate, which discards the materialized rows, triggers an expensive full refresh that can take minutes or hours on a large table, and forces every downstream stream table to also be recreated in exactly the right topological order.

For a small five-node DAG this is an inconvenience. For a 200-node production DAG it is an outage.

pg_aqueduct solves this by classifying every change into the cheapest applicable migration class — and only doing as much work as the change actually requires.

Quick Start

aqueduct init                   # scaffold a project and bootstrap the catalog
aqueduct import --from prod     # bootstrap from an existing pg_trickle deployment
aqueduct plan --to prod         # diff desired vs actual; produce a readable plan
aqueduct apply --to prod        # execute the plan and record the migration
aqueduct status --to prod       # show drift, last migration, refresh lag
aqueduct rollback --to prod     # revert to the previous DAG version

What's in This Book

• API Reference — CLI commands, front-matter directives, aqueduct.toml
• Migration Cookbook — 30 worked patterns, one per common change type
• Security Guide — Least-privilege role, secret backends, audit trail
• HA Operations — Patroni, CloudNativePG, maintenance windows

Source code: trickle-labs/pg-aqueduct
License: Apache 2.0

Installation

aqueduct is distributed as a single static binary — no runtime dependencies, no PostgreSQL client libraries to install.

Download a pre-built binary

Every tagged release publishes archives for four platforms to the GitHub Releases page.

A SHA256SUMS.txt file is attached to each release so you can verify the download before use.

Linux / macOS

# Replace <version> and <platform> as appropriate
VERSION=0.20.0
PLATFORM=linux-amd64   # or: linux-arm64, macos-arm64

curl -fsSL \
  "https://github.com/trickle-labs/pg-aqueduct/releases/download/v${VERSION}/aqueduct-${VERSION}-${PLATFORM}.tar.gz" \
  -o "aqueduct-${VERSION}-${PLATFORM}.tar.gz"

# Verify checksum (optional but recommended)
curl -fsSL \
  "https://github.com/trickle-labs/pg-aqueduct/releases/download/v${VERSION}/SHA256SUMS.txt" \
  | grep "${PLATFORM}" | sha256sum --check

tar xzf "aqueduct-${VERSION}-${PLATFORM}.tar.gz"
sudo mv "aqueduct-${VERSION}-${PLATFORM}/aqueduct" /usr/local/bin/
aqueduct --version

Windows

Download aqueduct-<version>-windows-amd64.zip from the releases page, extract it, and move aqueduct.exe to a directory that is on your %PATH%.

Build from source

You need the Rust toolchain (stable, 1.88 or later).

git clone https://github.com/trickle-labs/pg-aqueduct.git
cd pg-aqueduct
cargo build --release --bin aqueduct
# Binary is at target/release/aqueduct

Verify the installation

aqueduct --version
# aqueduct 0.17.0

Verify build provenance (v0.17+)

Starting from v0.17.0, each release carries a GitHub-native build attestation. You can verify that the binary you downloaded was built from the official source:

# Requires the GitHub CLI (gh)
gh attestation verify "aqueduct-0.17.0-linux-amd64.tar.gz" \
  --repo trickle-labs/pg-aqueduct

Once installed, continue with the 5-Minute Tutorial to connect aqueduct to your PostgreSQL instance and run your first migration plan.

pg_aqueduct in 5 Minutes

You have a pg_trickle deployment. Your stream tables are humming along, ingesting change events and keeping your aggregates fresh. Then comes the day you need to add a column. Or change a GROUP BY. Or add a new downstream table that depends on one of the existing ones. And you discover there is no safe way to do that — only drop_stream_table() followed by a full recreate, which empties your table and triggers a rebuild that takes minutes or hours on a large dataset.

pg_aqueduct is the tool that fixes this. Think of it the way you think of Atlas for relational schemas or Terraform for infrastructure: you declare the desired state, run a plan to see exactly what will change and at what cost, then apply it. pg_aqueduct figures out the safest, cheapest migration path — and executes it in the right topological order without losing your materialized data if it doesn't have to.

The Core Workflow

Every interaction with pg_aqueduct follows the same three-step loop:

aqueduct plan --to prod      # see what would change
aqueduct apply --to prod     # execute the plan
aqueduct status --to prod    # verify the result

That is it. plan reads your migration files and diffs them against the live catalog. apply executes the plan step by step, recording progress so it can resume if something goes wrong. status shows you the current health of every managed stream table.

A Concrete Example

Imagine you have a stream table called order_totals that aggregates orders by customer. It is defined in a SQL file with a few special comments — front-matter directives that tell pg_aqueduct how to configure the stream:

-- migrations/streams/order_totals.sql
-- @aqueduct:schedule     = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT
    customer_id,
    SUM(amount) AS total_amount,
    COUNT(*)    AS order_count
FROM raw.orders
GROUP BY customer_id;

Your product team asks you to add a discount_total column. You update the file:

-- migrations/streams/order_totals.sql
-- @aqueduct:schedule     = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT
    customer_id,
    SUM(amount)   AS total_amount,
    COUNT(*)      AS order_count,
    SUM(discount) AS discount_total     -- new column
FROM raw.orders
GROUP BY customer_id;

Now you run a plan:

$ aqueduct plan --to prod

Project  checkout-analytics  v3 → v4
Target   prod

Changes  1 node affected

  ~ order_totals    [in-place]   add column discount_total (SUM)

  Step                          Rows (est)   Duration (est)   Class
  ──────────────────────────────────────────────────────────────────
  ALTER stream order_totals          —            < 1s         in-place
  BACKFILL order_totals           1.2M           ~45s          in-place

pg_aqueduct recognized that adding a SUM aggregate to an existing GROUP BY is an in-place migration — the existing rows are preserved, only the new column is backfilled. No drop, no full rebuild, no downtime. You apply it:

aqueduct apply --to prod

Forty-five seconds later your table has a new column and every downstream consumer was never interrupted. If the team had asked you to change the GROUP BY keys instead, the plan would have classified it as a rebuild and told you upfront that it requires a maintenance window — before touching a single row.

The Four Migration Classes

pg_aqueduct classifies every change into one of four classes, and always applies the cheapest one that is safe:

The principle is conservative: when pg_aqueduct cannot prove a change is safe for in-place migration, it falls back to the next class up. A false Rebuild is an inconvenience. A false In-place would be data loss.

Where to Go Next

If you want to see the full lifecycle — creating a project, bootstrapping from an existing deployment, evolving the schema across multiple environments, and rolling back — read the 30-minute tutorial. For a deep reference on every command and directive, see the API Reference. For the 30 most common migration patterns, worked end to end, see the Migration Cookbook.

pg_aqueduct: A Hands-On Tutorial

This tutorial walks you through everything you need to understand and use pg_aqueduct confidently in a real production workflow. By the end you will have set up a project from scratch, evolved the schema through several common change types, promoted changes across environments, and performed a rollback. Plan for about 30 minutes of focused reading and hands-on work.

Part 1 — The Problem Worth Solving

Before writing a single line of configuration, it is worth understanding why pg_aqueduct exists — because the problem it solves is surprisingly sharp.

pg_trickle keeps your stream tables alive by continuously applying differential updates: it reads the change stream, runs your SQL query over only the changed rows, and merges the results back. This is beautifully efficient at runtime. But it creates a painful constraint at migration time. A stream table is not just a view you can DROP and recreate — it holds materialized state that took time to build, and it is connected to other stream tables in a dependency graph. Change one node and the ripple effects cascade downward.

Today, without pg_aqueduct, the standard answer to any stream-table change is drop_stream_table() followed by a manual recreate. That destroys the materialized rows, triggers an expensive full refresh that can run for minutes or hours on a large table, and forces you to manually recreate every downstream table in exactly the right topological order. Get that order wrong and your queries fail to parse or, worse, silently return stale data. For a five-node DAG this is an inconvenience. For a 200-node production DAG it is an outage.

pg_aqueduct solves this by doing three things. It maintains a declarative source of truth for your stream-table DAG in version-controlled SQL files. It computes a semantic diff between that desired state and the live catalog. And it classifies every change into the cheapest migration that is provably safe, then executes it in the correct topological order. You describe the end state; pg_aqueduct figures out how to get there without unnecessary data loss.

Part 2 — Concepts and Mental Model

Stream Tables and DAGs

A stream table is a table maintained by pg_trickle that holds the result of an aggregation query. Unlike a materialized view that is fully recomputed on refresh, a stream table processes only the rows that changed since the last refresh cycle. This is the differential in DIFFERENTIAL mode.

Stream tables can depend on other stream tables, forming a directed acyclic graph — a DAG. When you have order_totals feeding into customer_tiers, which feeds into churn_risk_scores, you have a three-node DAG. Each node has its own refresh schedule, its own SQL query, and potentially its own refresh mode.

raw.orders (base table)
      │
      ▼
order_totals        (DIFFERENTIAL, every 30s)
      │
      ▼
customer_tiers      (DIFFERENTIAL, every 1m)
      │
      ▼
churn_risk_scores   (DIFFERENTIAL, every 5m)

The Migration Classes

The central concept in pg_aqueduct is the migration class. Every change to every node in your DAG is classified into one of four classes, and pg_aqueduct will only ever apply the cheapest class that is provably safe for the change you made.

Free class covers changes that do not touch the query logic or the materialized data at all. Changing a refresh schedule from 30 seconds to 1 minute is a Free migration — it calls alter_stream_table() once and completes in under a second with zero impact on consumers. Toggling CDC mode and switching from DIFFERENTIAL to FULL refresh mode are also Free.

In-place class covers additive changes to the SELECT list that do not touch the GROUP BY keys, the FROM clause, or any WHERE predicates. Adding a SUM(discount) column to a table that already GROUP BYs customer_id is In-place: the existing rows are valid, only the new column is missing. pg_aqueduct adds the column and backfills it incrementally using pg_trickle's targeted refresh mechanism. The table remains readable and queryable the entire time. For a 1.2 million row table this typically takes under a minute.

Rebuild class covers changes that structurally alter the query: changing GROUP BY keys, adding or removing a JOIN, adding or changing a WHERE predicate, renaming a column. These changes invalidate the existing materialized rows, so pg_aqueduct has to drop the stream table and recreate it from scratch. This triggers a full refresh. Rebuild migrations are gated by your maintenance_window configuration — unless you explicitly pass --ignore-maintenance-window, they are deferred until the window opens. Downstream nodes affected by a rebuild are also rebuilt, in topological order.

Blue/green class covers the rarest case: restructuring the topology of a sub-DAG — splitting a node into two, merging two nodes into one, rewriting the dependency graph. pg_aqueduct handles this by building a parallel "green" DAG alongside the existing "blue" one, backfilling it while the blue DAG remains live, and then atomically swapping consumer views so that downstream consumers see the new topology without a gap in data availability.

The Project Format

A pg_aqueduct project is a directory with an aqueduct.toml configuration file and a migrations/streams/ subdirectory containing SQL files. Each SQL file defines one stream table. The SQL is exactly what you would pass to create_stream_table() in pg_trickle, preceded by front-matter directives in SQL line-comment form:

-- @aqueduct:schedule     = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT
    customer_id,
    SUM(amount) AS total_amount,
    COUNT(*)    AS order_count
FROM raw.orders
GROUP BY customer_id;

The front-matter directives set the pg_trickle parameters for the stream table. The schedule directive sets the refresh interval. The refresh_mode directive sets whether the table uses DIFFERENTIAL or FULL refresh. Additional directives cover CDC mode, explicit depends_on declarations, and whether to allow full refreshes during backfill.

The aqueduct.toml file declares named targets (databases) and project-level settings:

[project]
name = "checkout-analytics"

[targets.dev]
dsn  = "postgresql://localhost/checkout_dev"

[targets.staging]
dsn  = "${AQUEDUCT_STAGING_DSN}"

[targets.prod]
dsn  = "${AQUEDUCT_PROD_DSN}"

[apply]
lock_timeout       = "30s"
maintenance_window = "02:00-04:00 UTC"

Note that production credentials are never hardcoded — the DSN uses an environment variable. pg_aqueduct also supports integration with HashiCorp Vault and AWS Secrets Manager for production secret injection; see the Security Guide for details.

Part 3 — Setting Up a New Project

Prerequisites

You need PostgreSQL with pg_trickle installed, and the aqueduct binary on your PATH. You can install the binary from the releases page or build it from source with cargo install --path crates/aqueduct-cli.

Set an environment variable for your development database:

export AQUEDUCT_DEV_DSN="postgresql://localhost/mydb"

Initializing a Project

mkdir checkout-analytics && cd checkout-analytics
aqueduct init --to dev

aqueduct init creates the project skeleton:

checkout-analytics/
├── aqueduct.toml
└── migrations/
    └── streams/        ← your SQL files go here

It also connects to the target database and creates the aqueduct catalog schema, which holds the migration history, DAG version snapshots, and distributed lock table. You only need to run init once per target database.

Bootstrapping from an Existing Deployment

If you already have a running pg_trickle deployment and want to bring it under pg_aqueduct management, use import instead of starting from empty migration files:

aqueduct import --from prod --output migrations/streams/

This reads every row from pgtrickle.pgt_stream_tables and generates a corresponding SQL migration file for each one, with the correct front-matter directives extracted from the catalog. After running import, your local state and the live state are identical, so aqueduct plan --to prod will produce an empty plan. You are now on a known baseline and can start making changes.

Part 4 — Your First Migration

Let's walk through the three-node checkout analytics DAG from scratch.

Step 1 — Write the Migration Files

Create three files in migrations/streams/:

-- migrations/streams/order_totals.sql
-- @aqueduct:schedule     = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT
    customer_id,
    SUM(amount)  AS total_amount,
    COUNT(*)     AS order_count
FROM raw.orders
GROUP BY customer_id;

-- migrations/streams/customer_tiers.sql
-- @aqueduct:schedule      = "1m"
-- @aqueduct:refresh_mode  = "DIFFERENTIAL"
-- @aqueduct:depends_on    = ["public.order_totals"]
SELECT
    customer_id,
    CASE
        WHEN total_amount > 1000 THEN 'gold'
        WHEN total_amount > 100  THEN 'silver'
        ELSE                          'bronze'
    END AS tier
FROM public.order_totals;

-- migrations/streams/churn_risk_scores.sql
-- @aqueduct:schedule      = "5m"
-- @aqueduct:refresh_mode  = "DIFFERENTIAL"
-- @aqueduct:depends_on    = ["public.customer_tiers"]
SELECT
    t.customer_id,
    t.tier,
    CASE WHEN t.tier = 'bronze' AND o.order_count < 2 THEN true
         ELSE false
    END AS at_risk
FROM public.customer_tiers t
JOIN public.order_totals o USING (customer_id);

The @aqueduct:depends_on directive tells the planner about the dependency graph. Without it, pg_aqueduct infers dependencies from schema-qualified table references in the SQL, but explicit declarations are more reliable when the SQL is complex.

Step 2 — Validate Before Connecting

Before touching the database, validate your migration files offline:

aqueduct validate

This checks for syntax errors in the front-matter directives, detects cycles in the dependency graph, and flags common mistakes like sub-5-second schedules. It runs entirely without a database connection — ideal for a CI pre-check step.

Step 3 — Plan

$ aqueduct plan --to dev

Project  checkout-analytics  (initial)
Target   dev (pg_trickle 0.62, pg 18+)

Changes  3 nodes affected

  + order_totals        [create]   level 0
  + customer_tiers      [create]   level 1 (depends on order_totals)
  + churn_risk_scores   [create]   level 2 (depends on customer_tiers, order_totals)

  Step                              Rows (est)   Duration (est)   Class
  ──────────────────────────────────────────────────────────────────────
  CREATE stream order_totals             —            < 1s         create
  BACKFILL order_totals               850K           ~30s          create
  CREATE stream customer_tiers           —            < 1s         create
  BACKFILL customer_tiers             850K           ~30s          create
  CREATE stream churn_risk_scores        —            < 1s         create
  BACKFILL churn_risk_scores           850K           ~30s          create

The plan tells you exactly what will happen: three stream tables created in topological order (level 0 before level 1 before level 2), with estimated row counts and durations for each backfill. Review it, then apply:

aqueduct apply --to dev

aqueduct apply asks for confirmation, displays the plan one more time, then executes each step. Progress is checkpointed to aqueduct.migrations.progress after every step, so if the process crashes mid-migration, you can resume from where it left off with aqueduct apply --to dev --resume.

Step 4 — Verify

$ aqueduct status --to dev

Project  checkout-analytics  v1

  Table                  Mode          Schedule   Last Refresh   Lag    Status
  ─────────────────────────────────────────────────────────────────────────────
  order_totals           DIFFERENTIAL  30s        2s ago         0ms    ✓ ok
  customer_tiers         DIFFERENTIAL  1m         58s ago        0ms    ✓ ok
  churn_risk_scores      DIFFERENTIAL  5m         4m ago         0ms    ✓ ok

All three tables are running and fresh. You are on version 1 of your DAG.

Part 5 — Evolving the Schema

This is where pg_aqueduct earns its place. Let's walk through three common change types: one Free, one In-place, and one Rebuild.

Free Change — Update the Refresh Schedule

The SRE team decides order_totals is refreshing too aggressively and wants to change it from 30 seconds to 2 minutes. Edit the file:

-- migrations/streams/order_totals.sql
-- @aqueduct:schedule     = "2m"             ← changed
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT ...

Plan it:

$ aqueduct plan --to dev

  ~ order_totals    [free]   schedule: 30s → 2m

  Step                          Rows (est)   Duration (est)   Class
  ──────────────────────────────────────────────────────────────────
  ALTER stream order_totals          —            < 1s         free

A single alter_stream_table() call. No data movement, no downtime, done in under a second. This is what Free class looks like.

In-Place Change — Add a Column

The analytics team wants a discount_total column. Add it to the SELECT list without touching the GROUP BY:

-- migrations/streams/order_totals.sql
-- @aqueduct:schedule     = "2m"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT
    customer_id,
    SUM(amount)   AS total_amount,
    COUNT(*)      AS order_count,
    SUM(discount) AS discount_total     -- new
FROM raw.orders
GROUP BY customer_id;

Plan it:

$ aqueduct plan --to dev

  ~ order_totals    [in-place]   add column discount_total (SUM)

  Step                          Rows (est)   Duration (est)   Class
  ──────────────────────────────────────────────────────────────────
  ALTER stream order_totals          —            < 1s         in-place
  BACKFILL order_totals           850K           ~30s          in-place

The existing rows survive. The new column is added and backfilled without dropping the table. During the backfill, discount_total is NULL for rows that have not been touched by the backfill yet — so if you have dashboard queries that cannot tolerate NULL, consider using COALESCE(discount_total, 0) in the query layer until backfill completes. After aqueduct apply, the column is fully populated and the table is on version 3.

Rebuild Change — Change the GROUP BY

Now suppose you need to add product_category to the GROUP BY, so totals are broken down by both customer and category. This is a structural change — the existing rows are keyed by customer_id alone and cannot be transformed in place.

-- migrations/streams/order_totals.sql
-- @aqueduct:schedule     = "2m"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT
    customer_id,
    product_category,                   -- added to GROUP BY
    SUM(amount)   AS total_amount,
    COUNT(*)      AS order_count,
    SUM(discount) AS discount_total
FROM raw.orders
GROUP BY customer_id, product_category;  -- changed

Plan it:

$ aqueduct plan --to dev

  ! order_totals       [rebuild]   GROUP BY keys changed
  ! customer_tiers     [rebuild]   upstream (order_totals) rebuilt → cascade
  ! churn_risk_scores  [rebuild]   upstream (customer_tiers) rebuilt → cascade

  Step                              Rows (est)   Duration (est)   Class
  ──────────────────────────────────────────────────────────────────────
  DROP + RECREATE order_totals        850K           ~30s          rebuild ⏰
  DROP + RECREATE customer_tiers      850K           ~30s          rebuild ⏰
  DROP + RECREATE churn_risk_scores   850K           ~30s          rebuild ⏰

⏰ Rebuild steps will be deferred to the maintenance window (02:00–04:00 UTC).
   Pass --ignore-maintenance-window to run immediately.

Notice that pg_aqueduct automatically cascaded the rebuild to the downstream nodes. It computed the transitive closure of the dependency graph and included every affected node — in topological order, rebuilding order_totals before customer_tiers before churn_risk_scores. You did not have to figure out the order. You did not have to even know which tables downstream of order_totals exist. pg_aqueduct read the dependency graph from the catalog and handled it for you.

In production, this plan would be held until 02:00 UTC. In development, use --ignore-maintenance-window to run it immediately.

Part 6 — Multi-Environment Promotion

One of the most important benefits of pg_aqueduct is repeatable, auditable promotion across environments. The migration files are version-controlled SQL. The plan is deterministic. You can test a change end-to-end in dev, review the exact plan in staging, and apply the same plan to prod — knowing the behavior will be identical.

# Validate offline (no database needed)
aqueduct validate

# Test in dev
aqueduct plan --to dev
aqueduct apply --to dev

# Promote to staging
aqueduct plan --to staging
aqueduct apply --to staging --yes        # skip confirmation in CI

# Promote to prod (plan is shown again for final human review)
aqueduct plan --to prod
aqueduct apply --to prod

Because aqueduct plan exits with code 1 when there are pending changes and code 0 when the state is already up to date, you can use it as a gate in CI/CD pipelines:

# .gitlab-ci.yml (excerpt)
migrate-prod:
  stage: deploy
  script:
    - aqueduct apply --to prod --yes
  only:
    - main

In CI environments where you want to assert that no unreviewed drift exists, use aqueduct plan --quiet --to prod — it exits 0 for an empty plan and 1 for any pending change, which you can turn into a pipeline alert.

Part 7 — Rollback

pg_aqueduct stores a full snapshot of the DAG definition at every aqueduct apply. These snapshots live in aqueduct.dag_versions. Rolling back restores the previous snapshot by computing a forward migration plan from the current state to the target version — it is always a forward migration under the hood, which means all the same safety guarantees apply.

aqueduct rollback --to prod

By default, rollback targets the immediately preceding version. To roll back to a specific version, use --to-version <N>. The command will refuse to proceed through a Rebuild-class rollback without an explicit --accept-data-loss flag, because once a full refresh has replaced the old data with the new, there is nothing to roll back to without rerunning the refresh in the other direction.

The lossless rollback window is the key insight: if you are rolling back a Free or In-place migration, rollback is always safe and fast. If you are rolling back a Rebuild, you are accepting the cost of another full rebuild in the opposite direction.

Part 8 — Working with the Dependency Graph

Explicit vs. Inferred Dependencies

When a downstream stream table references an upstream one by a schema-qualified name — public.order_totals — pg_aqueduct infers the dependency automatically by parsing the SQL. You do not need to declare it explicitly. However, when the upstream table name is constructed dynamically, or when you want to be explicit for clarity, use the @aqueduct:depends_on directive:

-- @aqueduct:depends_on = ["public.order_totals", "public.customer_tiers"]

The planner validates these declarations against the actual catalog and warns if a declared dependency does not exist or if an inferred dependency is missing from the declaration.

Lint Checks

aqueduct lint runs a set of checks for common problems:

aqueduct lint

The checks include:

• L001 — Schedule under 5 seconds (very aggressive, may strain pg_trickle)
• L002 — DIFFERENTIAL mode with a non-deterministic aggregate (e.g., RANDOM())
• L003 — Cyclic dependency in the graph
• L004 — Undeclared dependency (inferred from SQL but missing from depends_on)
• L005 — Unknown front-matter directive (typo guard)
• L006 — Consumer view references a non-managed stream table

Run aqueduct lint --fix to automatically fix L004 (adds the missing depends_on declarations without changing anything else).

Part 9 — Advanced: Blue/Green Migrations

When a change is so structural that it cannot be expressed as a sequence of per-node Rebuild migrations — for example, splitting order_totals into two separate tables, each serving a different downstream — pg_aqueduct can orchestrate a blue/green migration.

A blue/green migration works like this: pg_aqueduct builds the entire new sub-DAG (the "green" side) in parallel with the existing one (the "blue" side), using consumer views to route reads. While the green side backfills, the blue side remains fully operational. Once the green side has caught up, pg_aqueduct atomically swaps the consumer views so that downstream queries now read from green. The blue side is then torn down.

Blue/green is the most expensive migration class but also the most powerful — it provides zero-downtime topology restructuring. In practice, most changes never reach this class. The cookbook's Pattern 30 shows a complete lifecycle including the decision points.

Part 10 — Day-to-Day CLI Cheatsheet

Here is a quick reference for the commands you will use most often:

What's Next

You now have a complete picture of how pg_aqueduct works. The Migration Cookbook has 30 worked examples covering every common pattern, each with the exact plan output you will see and an explanation of why it classifies the way it does. The API Reference documents every command, front-matter directive, and aqueduct.toml option in full. For production deployments on Patroni or CloudNativePG, read the HA Operations Guide.

API Reference

Auto-generated from aqueduct --help output. Do not edit by hand — run just gen-docs to regenerate.

Binary version: aqueduct 0.20.0

aqueduct plan

Compute a migration plan by diffing desired vs. actual DAG state

Usage: aqueduct plan [OPTIONS]

Options:
      --dsn <DSN>                  PostgreSQL connection string
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>                    Target name from aqueduct.toml
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --format <FORMAT>            Output format: text (default), json, markdown, or yaml [default: text]
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --fail-if-changed            Exit non-zero if the plan is non-empty (useful in CI). Synonymous with the default exit-code behaviour (exit 1 for non-empty plans)
      --fail-on-drift              Exit non-zero if drift is detected between live state and last-applied version
      --validate-ivm               Check IVM supportability of all queries
      --explain-cost               Show per-step cost estimates (row count, estimated duration)
      --out <OUT>                  Write the plan to a JSON file for use with `apply --plan`
      --allow-plaintext-password   Allow DSN with embedded plaintext password (not recommended outside CI/dev)
  -h, --help                       Print help

aqueduct apply

Apply a migration plan to the target database

Usage: aqueduct apply [OPTIONS]

Options:
      --dsn <DSN>                  PostgreSQL connection string
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>                    Target name from aqueduct.toml
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --dry-run                    Show what would be done without executing
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --ignore-maintenance-window  Override the maintenance window restriction
      --allow-rebuild              Allow rebuild-class migrations even when allow_full_refresh = false
      --strategy <STRATEGY>        Migration strategy: "default" or "blue-green" [default: default]
      --plan <PLAN>                Path to a pre-computed plan JSON file (produced by `aqueduct plan --out`). The plan's spec_hash is validated against the current migration files
      --no-immediate-downgrade     Reject plans that would temporarily downgrade an IMMEDIATE refresh-mode table to DIFFERENTIAL during a rebuild
      --resume                     Resume an interrupted migration
      --force-retry <FORCE_RETRY>  Force-retry the step at the given index (CORR-2 / v0.15). Requires --yes
      --force-skip <FORCE_SKIP>    Force-skip the step at the given index (CORR-2 / v0.15). Requires --yes
      --print-plan                 Print the plan before applying
  -y, --yes                        Skip the interactive confirmation prompt (for CI and scripted use)
      --allow-plaintext-password   Allow DSN with embedded plaintext password (not recommended outside CI/dev)
  -h, --help                       Print help

aqueduct diff

Show per-table diff between desired (migration files) and actual (live) state

Usage: aqueduct diff [OPTIONS]

Options:
      --dsn <DSN>                  PostgreSQL connection string
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>                    Target name from aqueduct.toml
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --table <TABLE>              Show diff only for the named stream table
      --format <FORMAT>            Output format: text (default), json, markdown, or yaml [default: text]
      --fail-on-drift              Exit 1 when any diff delta is non-Unchanged, exit 0 for a clean diff (U-03)
  -h, --help                       Print help

aqueduct status

Show the current project state and drift status

Usage: aqueduct status [OPTIONS]

Options:
      --dsn <DSN>
          PostgreSQL connection string
      --log-format <LOG_FORMAT>
          Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>
          Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>
          Target name from aqueduct.toml
      --project-dir <PROJECT_DIR>
          Project directory [default: .]
      --quiet
          Suppress all non-error output (useful for scripted pipelines)
      --format <FORMAT>
          Output format: text or json [default: text]
      --porcelain
          Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --fail-on-drift
          Exit non-zero if drift is detected
      --watch
          Watch mode: poll continuously instead of running once
      --interval <INTERVAL>
          Polling interval in watch mode (e.g. "30s", "1m") [default: 30s]
      --max-drift-count <MAX_DRIFT_COUNT>
          Exit after N consecutive drift detections (0 = never exit on drift) [default: 0]
      --poll-interval <POLL_INTERVAL>
          Polling interval for --watch mode (e.g. "30s", "1m"). Alias for --interval [default: 30s]
  -h, --help
          Print help

aqueduct validate

Offline validation of migration files (no database connection required)

Usage: aqueduct validate [OPTIONS]

Options:
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --format <FORMAT>            Output format: text or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --strict                     Treat warnings as errors and exit non-zero if any are present
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
  -h, --help                       Print help

aqueduct lint

Check migration files for risky or non-optimal patterns

Usage: aqueduct lint [OPTIONS]

Options:
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --format <FORMAT>            Output format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --fail-on-warn               Exit non-zero if any warnings are found (in addition to errors)
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
  -h, --help                       Print help

aqueduct import

Import an existing pg_trickle deployment into a migrations directory

Usage: aqueduct import [OPTIONS]

Options:
      --from <FROM>
          PostgreSQL connection string to import from
      --log-format <LOG_FORMAT>
          Log format: text (default) or json [default: text]
      --from-target <FROM_TARGET>
          Target name from aqueduct.toml (used as --from source)
      --log-level <LOG_LEVEL>
          Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --output <OUTPUT>
          Output directory for the generated project [default: .]
      --quiet
          Suppress all non-error output (useful for scripted pipelines)
      --porcelain
          Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --project-dir <PROJECT_DIR>
          Project directory (for resolving config) [default: .]
      --exclude-pattern <EXCLUDE_PATTERN>
          Exclude tables matching these patterns (can be specified multiple times)
      --no-default-exclusions
          Do not apply default exclusion patterns (_pg_ripple.*, _pg_eddy.*, _riverbank.*)
  -h, --help
          Print help

aqueduct rollback

Rollback to a previous DAG version

Usage: aqueduct rollback [OPTIONS]

Options:
      --dsn <DSN>                  PostgreSQL connection string
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>                    Target name from aqueduct.toml
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --to-version <TO_VERSION>    Roll back to a specific version (default: previous version)
      --accept-data-loss           Accept data loss for rebuild-class rollbacks where the lossless window has passed
      --within-window              Allow PointInTime rollbacks even when close to window expiry
      --dry-run                    Show what would be done without executing
  -h, --help                       Print help

aqueduct promote

Promote a validated migrations directory from one environment to another

Usage: aqueduct promote [OPTIONS] --from <FROM> --to <TO>

Options:
      --from <FROM>                Source environment name (e.g. "dev")
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>                    Destination environment name (e.g. "staging")
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --skip-source-check          Skip the source-clean validation (use in CI when source is known good)
      --dry-run                    Show what would be done without executing
      --yes                        Auto-approve the promotion (skip interactive prompt)
  -h, --help                       Print help

aqueduct destroy

Destroy all stream tables, consumer views, and catalog entries for a project

Usage: aqueduct destroy [OPTIONS]

Options:
      --dsn <DSN>                  PostgreSQL connection string
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>                    Target name from aqueduct.toml
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --confirm                    Confirm the destructive operation (required unless --dry-run is set)
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --dry-run                    Show what would be destroyed without executing
      --force-cascade              Also drop dependent database objects (views, etc.) via CASCADE. Without this flag, `destroy` refuses if any stream table has dependents
      --force-unowned              Skip ownership verification. By default, `destroy` refuses to drop stream tables that are not registered as owned by this project
  -h, --help                       Print help

aqueduct unlock

Release a stale project lock (emergency use)

Usage: aqueduct unlock [OPTIONS]

Options:
      --dsn <DSN>                  PostgreSQL connection string
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>                    Target name from aqueduct.toml
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --force                      Force unlock regardless of lock holder (requires confirmation)
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
  -h, --help                       Print help

aqueduct init

Bootstrap the aqueduct catalog schema in the target database

Usage: aqueduct init [OPTIONS]

Options:
      --dsn <DSN>                  PostgreSQL connection string (DSN). Overrides the config file
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --to <TO>                    Target name from aqueduct.toml
      --project-dir <PROJECT_DIR>  Project directory (default: current directory) [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
      --schema <SCHEMA>            Override the catalog schema name (default: "aqueduct") [default: aqueduct]
      --scaffold                   Scaffold a new project directory with aqueduct.toml and migrations/
      --allow-plaintext-password   Allow DSN with embedded plaintext password (not recommended outside CI/dev)
  -h, --help                       Print help

aqueduct preview

Create, inspect, or tear down a preview environment for a candidate change

Usage: aqueduct preview [OPTIONS]

Options:
      --dsn <DSN>
          PostgreSQL connection string

      --log-format <LOG_FORMAT>
          Log format: text (default) or json
          
          [default: text]

      --log-level <LOG_LEVEL>
          Verbosity: error, warn, info (default), debug, trace
          
          [env: AQUEDUCT_LOG=]
          [default: info]

      --to <TO>
          Target name from aqueduct.toml

      --project-dir <PROJECT_DIR>
          Project directory
          
          [default: .]

      --quiet
          Suppress all non-error output (useful for scripted pipelines)

      --branch <BRANCH>
          Branch name to create the preview for (used to derive the schema name). Example: `feat/my-feature` → `aqueduct_preview_feat_my_feature`

      --porcelain
          Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)

      --backend <BACKEND>
          Preview backend: native (default), cnpg, or neon
          
          [default: native]

      --sample <SAMPLE>
          Sample fraction for TABLESAMPLE (0.0–1.0). Default: 0.1 (10%)
          
          [default: 0.1]

      --recreate
          Drop and recreate the preview schema if it already exists

      --cleanup
          Drop an existing preview environment

      --list
          List all preview environments in the database

      --cnpg-endpoint <CNPG_ENDPOINT>
          CloudNativePG operator endpoint (required for --backend cnpg)

      --neon-api-token <NEON_API_TOKEN>
          Neon API token (required for --backend neon)
          
          [env: NEON_API_TOKEN=]

      --neon-project-id <NEON_PROJECT_ID>
          Neon project ID (required for --backend neon)
          
          [env: NEON_PROJECT_ID=]

      --table <TABLE>
          Restrict the preview to the subgraph anchored at this table name (P-07 / v0.12).
          
          Only the named table, its ancestors (dependencies), and its descendants (tables that depend on it) are included in the preview. Example: `--table public.order_totals`

  -h, --help
          Print help (see a summary with '-h')

aqueduct ingest

Ingest compiled dbt artefacts into an aqueduct migrations directory

Usage: aqueduct ingest [OPTIONS] --from <TYPE> --target <PATH>

Options:
      --from <TYPE>                Source type to ingest from. Currently only `dbt-target` is supported
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --target <PATH>              Path to the dbt target directory (contains `manifest.json` and `compiled/`). Required when `--from dbt-target`
      --project-dir <PROJECT_DIR>  Project directory to write migration files into (default: current directory) [default: .]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --format <FORMAT>            Output format: text (default) or json [default: text]
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
  -h, --help                       Print help

aqueduct fmt

Canonicalise the SQL body and front-matter directives in migration files

Usage: aqueduct fmt [OPTIONS]

Options:
      --log-format <LOG_FORMAT>    Log format: text (default) or json [default: text]
      --project-dir <PROJECT_DIR>  Project directory [default: .]
      --check                      Check formatting only — exit non-zero if any file is not canonical. Does not write any files
      --log-level <LOG_LEVEL>      Verbosity: error, warn, info (default), debug, trace [env: AQUEDUCT_LOG=] [default: info]
      --format <FORMAT>            Output format: text (default) or json [default: text]
      --quiet                      Suppress all non-error output (useful for scripted pipelines)
      --porcelain                  Emit machine-parseable key=value output on stdout (implies --quiet for decorative output)
  -h, --help                       Print help

pg_aqueduct Migration Cookbook

30 worked examples covering the most common stream-table evolution patterns. Every example is verified end-to-end against a Testcontainers PostgreSQL cluster (see crates/aqueduct-core/tests/integration.rs, test_cookbook_* functions).

Pattern Categories

Free class — metadata-only changes (no rebuild, no data movement)

In-place class — additive changes; materialised state preserved

Rebuild class — structural changes; full DROP + recreate + backfill

Create / Drop

DAG topology patterns

Consumer views

Multi-table and advanced patterns

Classification Quick Reference

Change type                             Migration class   Cost
──────────────────────────────────────────────────────────────
Schedule change                         Free              < 1s
cdc_mode change                         Free              < 1s
refresh_mode DIFF → FULL               Free              < 1s
Add aggregate column (no GROUP BY Δ)   In-place          seconds
Drop column from SELECT                 In-place          seconds
Rename column                           Rebuild           minutes
Change GROUP BY keys                    Rebuild           minutes
Add / remove / change JOIN              Rebuild           minutes
Add / change WHERE predicate           Rebuild           minutes
refresh_mode FULL → DIFF               Rebuild           minutes
Create new stream table                 Create            seconds–minutes
Drop stream table                       Drop              < 1s
Blue/green structural DAG change        Blue/green        background

Pattern 01 — Change Schedule to a Faster Interval

Class: Free
Cost: < 1 second
Test: test_cookbook_01_change_schedule_faster

When to use

Use this pattern when you want stream tables to refresh more frequently — for example, moving from a 1-minute schedule to 10 seconds to reduce analytical latency.

Migration file

-- migrations/streams/hourly_stats.sql
-- @aqueduct:schedule = "10s"
-- @aqueduct:refresh_mode = "FULL"
SELECT region, COUNT(*) AS event_count FROM raw_events GROUP BY region;

Before

# aqueduct.toml excerpt
[targets.prod]
dsn = "${PROD_DSN}"

-- hourly_stats.sql (before)
-- @aqueduct:schedule = "1m"
-- @aqueduct:refresh_mode = "FULL"
SELECT region, COUNT(*) AS event_count FROM raw_events GROUP BY region;

Plan output

Project  analytics  v3 → v4
Target   prod

Changes  1 node affected

  ~ hourly_stats    [free]   schedule: 1m → 10s

Why it's Free

pg_trickle stores the schedule as metadata alongside the stream table spec. A schedule change is a single call to pgtrickle.alter_stream_table(p_schedule => '10s'). No DDL is emitted, no data is moved, no rebuild is required.

Notes

• Schedules as short as 1s are allowed. Check the pg_trickle documentation for the minimum supported schedule for your cluster.
• The aqueduct lint command warns when schedule < 5s on a large table.

Pattern 02 — Change Schedule to a Slower Interval

Class: Free
Cost: < 1 second
Test: test_cookbook_02_change_schedule_slower

When to use

Use this pattern when a stream table is refreshing more often than necessary, consuming compute resources that could be better used elsewhere.

Migration file

-- migrations/streams/fast_stats.sql (after)
-- @aqueduct:schedule = "10m"
-- @aqueduct:refresh_mode = "FULL"
SELECT user_id, SUM(score) AS total_score FROM events GROUP BY user_id;

Plan output

Project  analytics  v5 → v6
Target   prod

Changes  1 node affected

  ~ fast_stats    [free]   schedule: 5s → 10m

Why it's Free

Slowing down a schedule is identical in implementation to speeding it up — a single pgtrickle.alter_stream_table() call updates the metadata. No data is touched.

Notes

• aqueduct status will show the updated schedule immediately after apply.
• For critical reporting tables, consider whether a slower schedule violates any SLA.

Pattern 03 — Enable CDC Mode

Class: Free
Cost: < 1 second
Test: test_cookbook_03_enable_cdc_mode

When to use

Enable CDC (change-data-capture) mode to consume WAL replication events from a source table instead of polling. This reduces lag and load on the source table for high-write scenarios.

Migration file

-- migrations/streams/event_counts.sql (after)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "FULL"
-- @aqueduct:cdc_mode = "wal"
SELECT event_type, COUNT(*) AS n FROM raw_events GROUP BY event_type;

Plan output

Project  analytics  v7 → v8
Target   prod

Changes  1 node affected

  ~ event_counts    [free]   cdc_mode: null → wal

Why it's Free

CDC mode is a property of how pg_trickle sources changes, not of the materialised table schema. Enabling it is a single metadata update — the materialised data does not change.

Notes

• WAL CDC requires the source table to have REPLICA IDENTITY FULL or a primary key.
• Set REPLICA IDENTITY FULL with Atlas or a manual ALTER TABLE before enabling CDC.
• Disabling CDC is similarly Free.

Pattern 04 — Switch Refresh Mode DIFFERENTIAL → FULL

Class: Free
Cost: < 1 second
Test: test_cookbook_04_diff_to_full_refresh

When to use

Switch to FULL refresh mode when a stream table's query becomes IVM-unsupportable (e.g., after adding a volatile function) and you want pg_trickle to do a full re-materialisation on every tick instead of maintaining a delta.

Migration file

-- migrations/streams/c04_agg.sql (after)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "FULL"
SELECT id, SUM(amount) AS total FROM raw_orders GROUP BY id;

Plan output

Project  analytics  v9 → v10
Target   prod

Changes  1 node affected

  ~ c04_agg    [free]   refresh_mode: DIFFERENTIAL → FULL

Why it's Free

Switching DIFFERENTIAL → FULL discards the incremental delta-tracking state managed by pg_trickle. Since FULL mode re-materialises everything on every tick, no schema change or data migration is required.

Contrast with FULL → DIFFERENTIAL

The reverse direction (FULL → DIFFERENTIAL) is Rebuild — see Pattern 17. Going from FULL to DIFFERENTIAL requires pg_trickle to establish delta-tracking state from scratch, which means the table must be rebuilt.

Notes

• After switching to FULL, aqueduct lint will warn if the table is large and the schedule is short (high compute cost per refresh cycle).

Pattern 05 — Add a SUM Aggregate Column

Class: In-place
Cost: Seconds to minutes (proportional to row count)
Test: test_cookbook_05_add_sum_aggregate_column

When to use

Add a new SUM aggregate to an existing DIFFERENTIAL stream table without rebuilding it. The new column is backfilled incrementally; the existing materialised data is preserved.

Migration file

-- migrations/streams/c05_totals.sql (after)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id,
       SUM(amount)   AS total,
       SUM(discount) AS discount_total   -- ← new column
FROM raw_orders
GROUP BY id;

Plan output

Project  analytics  v11 → v12
Target   prod

Changes  1 node affected

  ~ c05_totals    [in-place]   add column discount_total (SUM)

Cost estimate:

  Step                          Rows (est)   Duration (est)   Class
  ──────────────────────────────────────────────────────────────────
  ALTER stream c05_totals            —            < 1s         in-place
  BACKFILL c05_totals             1.4M           ~52s          in-place

Why it's In-place

Adding a new SUM aggregate column does not change the GROUP BY keys, the FROM clause, or the WHERE predicate. The existing rows remain valid — only the new column is NULL until backfilled. pg_trickle fills the new column incrementally without re-materialising the entire table.

Notes

• The same pattern applies to COUNT, MIN, MAX, and AVG columns. See Pattern 06 for COUNT.
• For tables with allow_full_refresh = false in aqueduct.toml, an In-place column addition is always safe.

Pattern 06 — Add a COUNT(*) Column

Class: In-place
Cost: Seconds to minutes
Test: test_cookbook_06_add_count_column

When to use

Add an event-count column alongside existing aggregates. A common use case is adding COUNT(*) AS num_events to a table that previously only tracked totals.

Migration file

-- migrations/streams/c06_stats.sql (after)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id,
       SUM(value)  AS total,
       COUNT(*)    AS event_count   -- ← new column
FROM raw_events
GROUP BY id;

Plan output

  ~ c06_stats    [in-place]   add column event_count (COUNT)

Why it's In-place

COUNT(*) is a simple additive aggregate. It does not change the GROUP BY keys or the row membership of the stream table. The new column is backfilled over the existing rows without touching any other column data.

Notes

• COUNT(DISTINCT col) is not IVM-supportable for DIFFERENTIAL mode in most pg_trickle versions. Use COUNT(*) instead, or switch the table to FULL refresh mode.

Pattern 07 — Drop a Column from the SELECT List

Class: In-place
Cost: < 1 second (DDL only)
Test: test_cookbook_07_drop_aggregate_column

When to use

Remove a column that is no longer needed by downstream consumers. Dropping a column from the SELECT list is an in-place ALTER TABLE DROP COLUMN operation on the materialised table.

Migration file

-- migrations/streams/c07_stats.sql (after)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
-- Removed: COUNT(*) AS order_count
SELECT id,
       SUM(amount) AS total
FROM raw_c07
GROUP BY id;

Plan output

  ~ c07_stats    [in-place]   drop column order_count

Why it's In-place

Dropping a column is always backward-compatible for the materialised table: the remaining columns are unaffected. pg_trickle stops tracking the deleted column's delta state, freeing the associated storage.

Important: check downstream consumers first

Before dropping a column, verify that no consumer views, reports, or application code references the column. Use aqueduct lint to detect consumer view references. If a consumer view references the column, update the consumer view migration file too.

Notes

• Dropping a column that is part of the GROUP BY is not in-place — that is a GROUP BY key change and requires a Rebuild.

Pattern 08 — Add an Aggregate Column Without Changing GROUP BY

Class: In-place
Cost: Seconds to minutes
Test: test_cookbook_08_add_passthrough_column

When to use

Add a new aggregate expression to the SELECT list that uses the same GROUP BY keys and the same FROM/WHERE structure as the existing query. This is the most common in-place migration pattern.

Migration file

-- migrations/streams/c08_view.sql (after)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id,
       SUM(amount)   AS total,
       MIN(amount)   AS min_amount   -- ← new aggregate, same GROUP BY
FROM raw_c08
GROUP BY id;

Plan output

  ~ c08_view    [in-place]   add column min_amount (MIN)

Key rule

The classifier checks that the GROUP BY clause, the FROM clause, and the WHERE clause (if any) are identical between the old and new query. If any of those structural parts change, the migration is classified as Rebuild regardless of what the SELECT list looks like.

In-place column additions that change GROUP BY — such as SELECT id, category, SUM(amount) FROM t GROUP BY id, category — are classified as Rebuild because the GROUP BY has changed.

Notes

• Supported aggregate functions for in-place addition: SUM, COUNT, MIN, MAX, AVG, and any other aggregate that pg_trickle supports for DIFFERENTIAL mode.
• Non-aggregate expressions that reference existing GROUP BY keys (e.g., id * 2 AS doubled_id) are also In-place.

Pattern 09 — Extend the SELECT List With a New Aggregate

Class: In-place
Cost: Seconds to minutes
Test: test_cookbook_09_widen_column_type

When to use

Extend an existing stream table's output by adding a new computed aggregate — for example, adding AVG(amount) alongside COUNT(*) — without modifying the GROUP BY, FROM, or WHERE clauses.

Migration file

-- migrations/streams/c09_counts.sql (after)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id,
       COUNT(*)        AS n,
       AVG(amount)     AS avg_amount   -- ← extended output
FROM raw_c09
GROUP BY id;

Plan output

  ~ c09_counts    [in-place]   add column avg_amount (AVG)

Why it's In-place

The SELECT list grew (superset), but the structural query parts are unchanged. The planner detects a pure Addition in the SELECT list delta — a provably safe in-place change.

The --strict flag

If you want to disable in-place classification and force every query change to be reviewed as a potential Rebuild, set --strict on aqueduct plan. This is useful in regulated environments where data-accuracy guarantees for every column are required before applying.

Pattern 10 — Rename a Column

Class: Rebuild
Cost: Minutes (proportional to row count)
Test: test_cookbook_10_rename_column_is_rebuild

When to use

Rename an output column (e.g., total → revenue). Despite being a seemingly small change, a column rename always triggers a full Rebuild.

Migration file

-- migrations/streams/c10_agg.sql (after)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id,
       SUM(amount) AS revenue   -- ← renamed from `total`
FROM raw_c10
GROUP BY id;

Plan output

  ! c10_agg    [rebuild]   column renamed: total → revenue → full rebuild required

Why it's a Rebuild

pg_trickle's DIFFERENTIAL refresh tracks deltas by column name. Renaming a column destroys the column's delta-state metadata — there is no mapping from the old name to the new name that preserves row-level accuracy. The table must be rebuilt from scratch.

Minimising downtime

For large tables, use the Blue/green deployment strategy to rename with zero downtime:

1. Create a new stream table with the renamed column.
2. Run it in parallel with the old table.
3. Swap consumer views atomically when the new table has converged.
4. Drop the old table after the blue-ttl expires.

This pattern is implemented by aqueduct apply --strategy blue-green.

Notes

• Plan this change during a maintenance window if the table is large.
• allow_full_refresh = false in aqueduct.toml prevents this plan from executing unless --allow-rebuild is passed.

Pattern 11 — Change GROUP BY Keys

Class: Rebuild | Cost: Minutes | Test: test_cookbook_11_change_group_by_is_rebuild

When to use

Change the granularity of an aggregation — for example, adding region to a GROUP BY that previously only grouped by customer_id.

Migration file

-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT customer_id, region, SUM(amount) AS total
FROM raw_c11
GROUP BY customer_id, region;

Why it's a Rebuild

The GROUP BY keys define the primary key of the materialised table. Changing them invalidates every existing row. A full DROP + recreate + backfill is required.

Notes

• Consider adding a separate downstream stream table instead of modifying the existing one (Pattern 21).
• For large tables, use --strategy blue-green for zero-downtime rebuild.

Pattern 12 — Add a JOIN

Class: Rebuild | Cost: Minutes | Test: test_cookbook_12_add_join_is_rebuild

When to use

Enrich a stream table by joining to a reference table (e.g., adding customer name by joining to customers).

Migration file

-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT o.customer_id, c.name, SUM(o.amount) AS total
FROM raw_orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY o.customer_id, c.name;

Why it's a Rebuild

A JOIN changes the source set. Rows that previously had no match in the joined table may now be excluded; new rows from the join may appear. The existing materialised state is invalid.

Notes

• For low-cardinality reference tables, consider denormalising the value into the source table instead to keep the query JOIN-free and In-place-eligible.

Pattern 13 — Remove a JOIN

Class: Rebuild | Cost: Minutes | Test: test_cookbook_13_remove_join_is_rebuild

When to use

Simplify a stream table by removing a JOIN that is no longer needed.

Migration file

-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT customer_id, SUM(amount) AS total
FROM raw_orders        -- JOIN removed
GROUP BY customer_id;

Why it's a Rebuild

Removing a JOIN changes the source set. Rows that previously required a join match may now be included (or different rows may be excluded). The existing materialised state is invalid.

Pattern 14 — Change a JOIN Condition

Class: Rebuild | Cost: Minutes | Test: test_cookbook_14_change_join_condition_is_rebuild

When to use

Tighten or loosen the ON clause of an existing JOIN — for example, adding a filter on the joined table.

Migration file

-- @aqueduct:schedule = "30s"
SELECT o.id, p.name
FROM orders o
JOIN products p ON o.product_id = p.id
               AND p.active = true;    -- condition added

Why it's a Rebuild

The JOIN condition directly determines which rows are produced. A change means the current materialised state is incorrect and must be rebuilt.

Notes

• Even a small addition to a JOIN condition (e.g., AND t.col = x) has correctness implications for all existing rows.

Pattern 15 — Add a WHERE Predicate

Class: Rebuild | Cost: Minutes | Test: test_cookbook_15_add_where_predicate_is_rebuild

When to use

Add a filter to exclude rows that should not appear in the stream table.

Migration file

-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id, SUM(amount) AS total
FROM raw_c15
WHERE status = 'active'    -- new filter
GROUP BY id;

Why it's a Rebuild

A WHERE predicate changes which rows are included in the stream table. Existing rows that don't pass the new filter are now incorrect — the table must be rebuilt from scratch.

Pattern 16 — Change a WHERE Predicate

Class: Rebuild | Cost: Minutes | Test: test_cookbook_16_change_where_predicate_is_rebuild

When to use

Modify an existing WHERE clause — for example, tightening or loosening the filter condition.

Migration file

-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id, amount
FROM events
WHERE type = 'purchase';    -- changed from 'click'

Why it's a Rebuild

A different WHERE predicate means a different set of rows qualifies. The materialised state is stale and must be rebuilt.

Pattern 17 — Switch Refresh Mode FULL to DIFFERENTIAL

Class: Rebuild | Cost: Minutes | Test: test_cookbook_17_full_to_diff_is_rebuild

When to use

Move a stream table from full periodic refreshes to incremental differential refresh to reduce compute cost on large tables.

Migration file

-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"    -- was FULL
SELECT id, COUNT(*) AS n FROM raw_c17 GROUP BY id;

Why it's a Rebuild

DIFFERENTIAL mode requires pg_trickle to initialise delta-tracking state (change buffers, sequence counters) that did not exist in FULL mode. This can only be done by rebuilding the table from scratch.

Contrast with DIFF to FULL

The reverse direction (DIFFERENTIAL to FULL) is Free — see Pattern 04.

Notes

• After switching to DIFFERENTIAL, aqueduct lint checks IVM-supportability for the query.
• Queries with DISTINCT, volatile functions, or unsupported aggregates cannot be DIFFERENTIAL — aqueduct validate will report this as a plan error.

Pattern 18 — Create a New Stream Table

Class: Create | Cost: Seconds to minutes (initial backfill) | Test: test_cookbook_18_create_stream_table

When to use

Add a new stream table to the project by creating a new migration file in migrations/streams/.

Migration file

-- migrations/streams/c18_scores.sql
-- @aqueduct:schedule = "1m"
-- @aqueduct:refresh_mode = "FULL"
SELECT id, AVG(score) AS avg_score
FROM raw_c18
GROUP BY id;

Plan output

  + c18_scores    [create]    new stream table (FULL, schedule 1m)

Notes

• After aqueduct apply, run aqueduct status to confirm the table is active and scheduled.
• For DIFFERENTIAL mode, pg_trickle performs an initial full backfill before entering differential refresh.
• Cost estimate: depends on row count of the source table and write throughput.

Pattern 19 — Drop an Existing Stream Table

Class: Drop | Cost: < 1 second | Test: test_cookbook_19_drop_stream_table

When to use

Remove a stream table that is no longer needed by deleting its migration file.

Steps

1. Delete (or archive) migrations/streams/c19_orphan.sql.
2. Run aqueduct plan — the plan shows a Drop step.
3. Run aqueduct apply to execute.

Plan output

  - c19_orphan    [drop]    stream table removed from migrations directory

Important: check downstream dependencies

Before dropping a table, verify:

• No other stream table declares @aqueduct:depends_on = ["public.c19_orphan"].
• No consumer view in migrations/consumers/ references the table.
• No application code queries it directly.

aqueduct plan fails with a hard error if a downstream stream table still depends on the dropped node.

Pattern 20 — Two-Node Dependency DAG

Class: Create (both nodes) | Test: test_cookbook_20_two_node_dag

When to use

Build a two-level analytics pipeline where a summary stream table depends on a base aggregation stream table.

Migration files

-- migrations/streams/c20_totals.sql
-- @aqueduct:schedule = "30s"
SELECT id, SUM(amount) AS total FROM raw_c20 GROUP BY id;

-- migrations/streams/c20_summary.sql
-- @aqueduct:schedule = "1m"
-- @aqueduct:depends_on = ["public.c20_totals"]
SELECT COUNT(*) AS num_customers FROM public.c20_totals;

Plan output

  + c20_totals     [create]   level 0
  + c20_summary    [create]   level 1 (depends on c20_totals)

Notes

• aqueduct always applies nodes in topological order. c20_totals is created before c20_summary regardless of file order in migrations/streams/.
• Use @aqueduct:depends_on to declare explicit dependencies when the planner cannot infer them from schema-qualified names in the SQL.

Pattern 21 — Add a Downstream Dependent Node

Class: Create (new node only) | Test: test_cookbook_21_add_downstream_node

When to use

Add a new stream table that consumes an existing one. Only the new downstream node requires a Create step; the upstream table is unchanged.

New migration file

-- migrations/streams/c21_summary.sql  (new)
-- @aqueduct:schedule = "1m"
-- @aqueduct:depends_on = ["public.c21_totals"]
SELECT COUNT(*) AS n FROM public.c21_totals;

Plan output

  = c21_totals     [unchanged]
  + c21_summary    [create]    new downstream node

Notes

• The existing upstream table (c21_totals) is not touched — zero downtime for consumers reading from it.
• The plan executes the Create step only after confirming the upstream is healthy.

Pattern 22 — Remove a Downstream Node

Class: Drop (removed node only) | Test: test_cookbook_22_remove_downstream_node

When to use

Remove a stream table that was consuming an upstream table, without affecting the upstream table.

Steps

1. Delete migrations/streams/c22_downstream.sql.
2. aqueduct plan emits a Drop only for c22_downstream.
3. Apply.

Plan output

  = c22_base          [unchanged]
  - c22_downstream    [drop]

Notes

• The upstream table (c22_base) continues running without interruption.
• In a multi-node DAG, aqueduct drops nodes in reverse topological order to avoid foreign-key-style constraint violations in pg_trickle.

Pattern 23 — Three-Level Dependency Chain

Class: Create (all 3 nodes) | Test: test_cookbook_23_three_level_chain

When to use

Build a three-level analytics pipeline: raw data → normalised layer → aggregated summary.

Migration files

-- migrations/streams/c23_lvl1.sql
-- @aqueduct:schedule = "30s"
SELECT id, region, amount FROM raw_c23;

-- migrations/streams/c23_lvl2.sql
-- @aqueduct:schedule = "1m"
-- @aqueduct:depends_on = ["public.c23_lvl1"]
SELECT region, SUM(amount) AS total FROM public.c23_lvl1 GROUP BY region;

-- migrations/streams/c23_lvl3.sql
-- @aqueduct:schedule = "5m"
-- @aqueduct:depends_on = ["public.c23_lvl2"]
SELECT COUNT(DISTINCT region) AS num_regions FROM public.c23_lvl2;

Plan output

  + c23_lvl1    [create]   level 0
  + c23_lvl2    [create]   level 1
  + c23_lvl3    [create]   level 2

Notes

• The planner uses Kahn's algorithm to compute the topological order.
• Cycles are detected at plan time and reported as hard errors before any DDL is executed.
• Schedules should generally be longer at higher levels to avoid stale intermediate data.

Pattern 24 — Create a Consumer View

Class: Create (consumer view) | Test: test_cookbook_24_create_consumer_view

When to use

Expose a stream table through a stable, schema-qualified view for application or API consumption. The application always connects to the stable view name; the underlying stream table can be replaced transparently.

Migration file

-- migrations/consumers/c24_orders_view.sql
-- @aqueduct:kind = consumer
-- @aqueduct:source = public.c24_orders
-- @aqueduct:expose_as = api.orders
SELECT customer_id, total FROM public.c24_orders WHERE total > 0;

Plan output

  + api.orders    [consumer create]   exposes public.c24_orders

Benefits

• Applications connect to api.orders — the name never changes.
• On blue/green deployments, the view is swapped atomically with ACCESS EXCLUSIVE on the view (not the underlying table), making the cutover sub-millisecond.

Notes

• Consumer views are tracked in aqueduct.consumer_views and listed with aqueduct consumers list.
• The @aqueduct:expose_as schema must exist before aqueduct apply runs. Create it with a pre-hook or Atlas migration.

Pattern 25 — Drop a Consumer View

Class: Drop (consumer view) | Test: test_cookbook_25_drop_consumer_view

When to use

Remove a consumer view that is no longer needed by deleting its migration file.

Steps

1. Delete migrations/consumers/c25_view.sql.
2. aqueduct plan emits a consumer-drop step.
3. aqueduct apply drops the view and removes the entry from aqueduct.consumer_views.

Plan output

  - reporting.c25_view    [consumer drop]

Important: check application dependencies

aqueduct lint warns about consumer views that reference non-existent stream tables but does not detect external application dependencies. Verify no application code queries the view before dropping it.

Pattern 26 — Source Column Change Cascades to Stream Tables

Class: Rebuild (all affected stream tables) | Test: test_cookbook_26_source_column_cascade

When to use

An owned source table gains a new column that stream tables should use. aqueduct plan detects which stream tables reference the source and marks them for rebuild, generating a single coordinated plan.

Source migration file (updated)

-- migrations/sources/raw_c26.sql
-- @aqueduct:owned = true
CREATE TABLE public.raw_c26 (
    id     bigint,
    amount numeric,
    region text    -- new column
);

Updated stream table

-- migrations/streams/c26_agg.sql (updated to use new column)
-- @aqueduct:schedule = "30s"
SELECT id, region, SUM(amount) AS total
FROM public.raw_c26
GROUP BY id, region;

Plan output

  ALTER TABLE public.raw_c26 ADD COLUMN region text;    [base-table DDL]
  ! c26_agg    [rebuild]   base-table DDL change cascade

Notes

• Stream tables that do not reference the changed column are unaffected.
• The base-table ALTER and stream-table cascade are always in a single coordinated plan — aqueduct will never emit one without the other.
• Tier 2 changes (new tables, non-referenced columns, indexes) are delegated to Atlas/sqitch via a pre-hook.

Pattern 27 — Change Schedule on Multiple Tables Simultaneously

Class: Free (all tables) | Cost: < 1 second | Test: test_cookbook_27_multi_table_schedule_change

When to use

Adjust the refresh cadence of a set of related stream tables in one aqueduct apply — for example, increasing frequency across a whole analytics pipeline before a business-critical period.

Migration files (updated schedules)

c27_a.sql:  -- @aqueduct:schedule = "10s"   (was 1m)
c27_b.sql:  -- @aqueduct:schedule = "10s"   (was 1m)
c27_c.sql:  -- @aqueduct:schedule = "10s"   (was 1m)

Plan output

  ~ c27_a    [free]   schedule: 1m → 10s
  ~ c27_b    [free]   schedule: 1m → 10s
  ~ c27_c    [free]   schedule: 1m → 10s

Notes

• All Free steps execute as fast serial metadata updates.
• There is no dependency between Free steps on separate tables — they could in principle be parallelised. The current executor applies them sequentially for simplicity.
• aqueduct lint warns when schedule < 5s and the estimated row count is large.

Pattern 28 — Import from Live — Idempotent Round-Trip

Test: test_cookbook_28_import_roundtrip

When to use

Bootstrap a pg_aqueduct project from an existing live pg_trickle deployment: generate canonical migration files that match the current live state, then confirm that running aqueduct plan produces an empty (no-op) plan.

Commands

# Generate migration files from the live database.
aqueduct import --from prod --output ./my-project/

# Confirm the round-trip: plan should be empty.
cd my-project
aqueduct plan --to prod

What import generates

For each stream table in pgtrickle.pgt_stream_tables, aqueduct import creates:

• migrations/streams/{table_name}.sql — SQL body + front-matter directives
• migrations/sources/{source_name}.sql — for referenced base tables (owned = false)
• aqueduct.toml — skeleton project config

Idempotency guarantee

Running aqueduct import a second time with no live changes produces no file changes. The generated files always round-trip to an empty aqueduct plan.

Notes

• Use --exclude-pattern '_pg_ripple.*' and --exclude-pattern '_pg_eddy.*' to skip internal extension tables (applied by default).
• After import, commit the generated files and run aqueduct init to record the baseline version.

Pattern 29 — Rollback Across Version Boundaries

Test: test_cookbook_29_rollback_to_prior_state

When to use

Revert a deployed migration to a previous DAG version — for example, after discovering a bug in a newly deployed aggregation.

Commands

# Roll back to the immediately previous version.
aqueduct rollback --to prod

# Roll back to a specific historical version.
aqueduct rollback --to prod --to-version 3

How rollback works

aqueduct rollback is not a reverse replay of the forward migration. Instead:

1. It reads the desired DAG spec stored in aqueduct.dag_versions for the target version.
2. It computes a forward migration plan from the current live state to the target spec.
3. It applies that forward plan.

This means rollback uses the same planner, executor, and safety checks as a normal aqueduct apply. If rolling back requires a Rebuild (e.g., after a column rename), the same Rebuild logic applies.

Example (v1 → v2 → rollback to v1)

v1: c29_base only
v2: c29_base + c29_extra (added)
rollback to v1: DROP c29_extra

Lossless guarantee

• Free and In-place migrations: always lossless.
• Rebuild migrations: lossless only if no full refresh has completed since the migration. aqueduct rollback reports the estimated lossless window expiry and requires --accept-data-loss if the window has passed.

Notes

• aqueduct rollback never rolls back base-table DDL (Tier 1 changes). Provide a compensating Atlas migration for any base-table changes.

Pattern 30 — Full DAG Lifecycle: Create, Evolve, Destroy

Test: test_cookbook_30_full_dag_lifecycle

Overview

A complete end-to-end walkthrough of a stream table's lifecycle:

1. Create the stream table from a migration file.
2. Evolve it with an in-place column addition (zero downtime).
3. Destroy all project resources when they are no longer needed.

Step 1: Create

-- migrations/streams/c30_totals.sql
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id, SUM(amount) AS total FROM raw_c30 GROUP BY id;

aqueduct apply --to prod
# Result: creates c30_totals (v1)

Step 2: In-place evolution

-- c30_totals.sql (updated — add COUNT(*) column, same GROUP BY)
-- @aqueduct:schedule = "30s"
-- @aqueduct:refresh_mode = "DIFFERENTIAL"
SELECT id,
       SUM(amount)  AS total,
       COUNT(*)     AS order_count    -- new column
FROM raw_c30
GROUP BY id;

aqueduct plan --to prod
# Shows: ~ c30_totals [in-place]  add column order_count (COUNT)

aqueduct apply --to prod
# Result: in-place column addition (v2) — zero downtime

Step 3: Destroy

When the project is retired, clean up all resources:

aqueduct destroy --project cookbook-30 --to prod --dry-run   # preview first
aqueduct destroy --project cookbook-30 --to prod --confirm   # execute

The destroy command:

• Drops all stream tables in reverse topological order.
• Drops all consumer views managed by the project.
• Deletes the project's rows from aqueduct.dag_versions, aqueduct.migrations, and aqueduct.locks.
• Does not drop the aqueduct catalog schema itself (other projects may share it).

Notes

• Always run --dry-run before --confirm for destroy.
• Use aqueduct status at each step to verify the pipeline state.

Security Guide

Overview

pg_aqueduct is designed to operate without superuser privileges and with a minimal footprint on the target PostgreSQL cluster.

Least-privilege role

Create a dedicated aqueduct_admin role for all aqueduct operations:

CREATE ROLE aqueduct_admin LOGIN PASSWORD '...';

-- Access to the aqueduct catalog schema.
GRANT USAGE, CREATE ON SCHEMA aqueduct TO aqueduct_admin;

-- Access to pg_trickle functions.
GRANT USAGE ON SCHEMA pgtrickle TO aqueduct_admin;

-- Read access to system catalogs (for live-state queries).
GRANT SELECT ON pg_catalog.pg_class TO aqueduct_admin;
GRANT SELECT ON pg_catalog.pg_attribute TO aqueduct_admin;
GRANT SELECT ON pg_catalog.pg_type TO aqueduct_admin;
GRANT SELECT ON pg_catalog.pg_namespace TO aqueduct_admin;

-- DDL rights on the stream-table schemas only.
GRANT CREATE ON SCHEMA public TO aqueduct_admin;
-- (add each schema where stream tables live)

The role requires:

• No SUPERUSER
• No CREATEROLE
• No replication privilege

Connection string security

Environment variables (default)

[targets.prod]
dsn = "${AQUEDUCT_PROD_DSN}"

pg_aqueduct expands ${VAR} at runtime. The DSN is never written to disk or logged.

Plaintext passwords

The CLI refuses plaintext passwords in config files:

Error: DSN contains a plaintext password. Use ${ENV_VAR} or a secret backend.
       Pass --allow-plaintext-password to override (not recommended in CI).

Secret backends

Use the ${secret:BACKEND:KEY} inline syntax to fetch credentials from an external secret store at apply time:

[targets.prod]
dsn = "postgresql://app:${secret:vault:database/prod-dsn}@db.example.com/app"

Supported backends:

Note: The aws, gcp, and vault backends are implemented as of v0.12. Each backend resolves credentials at apply time and never writes them to disk.

Read-only transactions

aqueduct plan, aqueduct status, and aqueduct validate open read-only transactions (SET TRANSACTION READ ONLY). These commands cannot accidentally mutate data.

Audit trail

Every aqueduct apply records the following in aqueduct.migrations:

• Authenticated PostgreSQL role (current_role)
• Client IP address (inet_client_addr())
• CLI version
• Full plan (all steps)
• Start and finish timestamps

Query the audit trail:

SELECT started_at, applied_by, cli_version, status
FROM aqueduct.migrations
ORDER BY started_at DESC
LIMIT 20;

HA security

aqueduct apply refuses to run against a hot standby:

SELECT pg_is_in_recovery();   -- returns true on a standby

If the result is true, the CLI exits non-zero with a clear error: Error: target is a hot standby — apply requires a primary.

For Patroni clusters, aqueduct performs a synchronous HTTP check against the Patroni REST endpoint (GET /master) before applying. Pass --patroni-endpoint <url>.

OWASP considerations

• Injection: All database queries use parameterised statements ($1, $2, ...). SQL identifiers are quoted with format('%I', name) in PostgreSQL functions. The Rust layer does not perform string interpolation into SQL; explicit trust boundaries for identifier construction were introduced in v0.14 (see CatalogSchema newtype and the catalog.rs query builder).
• Consumer SQL injection (SEC-1): Consumer view bodies are validated as a single SELECT statement by validate_consumer_sql_is_single_select() before any CREATE OR REPLACE VIEW is executed. Multi-statement bodies and bare DDL are rejected with AqueductError::UntrustedSqlBody before any database call is made.
• Sensitive data exposure: DSNs containing passwords are masked in log output. Secret values are never echoed or stored.
• Misconfiguration: aqueduct lint warns about overly permissive configurations (e.g., allow_full_refresh = true in a production target).

CNPG preview TLS

When using aqueduct preview with CloudNativePG, the CLI validates TLS certificates on all Kubernetes API calls by default. To connect to a development cluster with a self-signed certificate, set the development-only escape hatch:

export CNPG_INSECURE_SKIP_VERIFY=1
aqueduct preview create --branch feat/my-branch

Warning: Never set CNPG_INSECURE_SKIP_VERIFY in production. Disabling TLS verification exposes the connection to man-in-the-middle attacks.

Age identity file security

The age secret backend validates both the encrypted-file path (key) and the identity file path (AGE_KEY_FILE / SOPS_AGE_KEY_FILE) before passing them to the age subprocess:

1. Path traversal: paths containing .. components are rejected with AqueductError::InvalidSecretPath.
2. Flag injection: identity file paths starting with - are rejected to prevent the value from being interpreted as a CLI flag by the age binary.

Role SQL Templates

Copy and customise these templates for your environment. Replace <schema> with each schema that contains stream tables or consumer views.

These roles follow the principle of least privilege. Each role is scoped to exactly the operations it needs to perform.

aqueduct_planner

Read-only access: runs aqueduct plan, aqueduct status, aqueduct diff. Cannot write to any table.

CREATE ROLE aqueduct_planner LOGIN PASSWORD 'CHANGE_ME' NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT USAGE ON SCHEMA aqueduct TO aqueduct_planner;
GRANT SELECT ON ALL TABLES IN SCHEMA aqueduct TO aqueduct_planner;
GRANT SELECT ON ALL TABLES IN SCHEMA pgtrickle TO aqueduct_planner;
GRANT USAGE ON SCHEMA pgtrickle TO aqueduct_planner;

-- Read access to system catalogs (for live-state queries).
GRANT SELECT ON pg_catalog.pg_class TO aqueduct_planner;
GRANT SELECT ON pg_catalog.pg_attribute TO aqueduct_planner;
GRANT SELECT ON pg_catalog.pg_type TO aqueduct_planner;
GRANT SELECT ON pg_catalog.pg_namespace TO aqueduct_planner;

-- Read access to the stream-table schemas (for query validation).
-- Repeat for each schema that contains stream tables:
GRANT USAGE ON SCHEMA <schema> TO aqueduct_planner;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO aqueduct_planner;

aqueduct_applier

Write access: runs aqueduct apply, aqueduct rollback, aqueduct promote. Can call pgtrickle DDL functions and write to the aqueduct catalog.

CREATE ROLE aqueduct_applier LOGIN PASSWORD 'CHANGE_ME' NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT USAGE ON SCHEMA aqueduct TO aqueduct_applier;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA aqueduct TO aqueduct_applier;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA aqueduct TO aqueduct_applier;
GRANT USAGE ON SCHEMA pgtrickle TO aqueduct_applier;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pgtrickle TO aqueduct_applier;

-- DDL rights on the stream-table schemas.
-- Repeat for each schema that contains stream tables:
GRANT USAGE, CREATE ON SCHEMA <schema> TO aqueduct_applier;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA <schema> TO aqueduct_applier;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA <schema> TO aqueduct_applier;

aqueduct_preview

Preview access: runs aqueduct preview in a sandboxed schema. Can create/drop objects in the preview schema only.

CREATE ROLE aqueduct_preview LOGIN PASSWORD 'CHANGE_ME' NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT USAGE ON SCHEMA aqueduct TO aqueduct_preview;
GRANT SELECT ON ALL TABLES IN SCHEMA aqueduct TO aqueduct_preview;
GRANT USAGE ON SCHEMA pgtrickle TO aqueduct_preview;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pgtrickle TO aqueduct_preview;

-- Grant full access to the preview schema (created by `aqueduct preview`).
-- Replace <preview_schema> with the actual schema used (default: aqueduct_preview).
GRANT USAGE, CREATE ON SCHEMA <preview_schema> TO aqueduct_preview;
GRANT ALL ON ALL TABLES IN SCHEMA <preview_schema> TO aqueduct_preview;

aqueduct_destroy

Destroy access: runs aqueduct destroy to tear down the full project DAG. This role should be tightly guarded. Consider requiring 2FA or approvals.

CREATE ROLE aqueduct_destroy LOGIN PASSWORD 'CHANGE_ME' NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT USAGE ON SCHEMA aqueduct TO aqueduct_destroy;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA aqueduct TO aqueduct_destroy;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA aqueduct TO aqueduct_destroy;
GRANT USAGE ON SCHEMA pgtrickle TO aqueduct_destroy;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pgtrickle TO aqueduct_destroy;

-- DDL rights on the stream-table schemas (for DROP TABLE / DROP VIEW).
-- Repeat for each schema that contains stream tables:
GRANT USAGE, CREATE ON SCHEMA <schema> TO aqueduct_destroy;
GRANT ALL ON ALL TABLES IN SCHEMA <schema> TO aqueduct_destroy;

HA Operations Guide

Overview

pg_aqueduct is designed to operate safely in high-availability PostgreSQL deployments, including Patroni clusters, CloudNativePG, and Stolon.

Primary detection (v0.17)

aqueduct apply always checks that the target is a primary before and between every migration step:

SELECT pg_is_in_recovery();   -- must return false

If the target is a standby (failover happened), the CLI marks the migration as interrupted and exits non-zero with a descriptive error.

Supported HA backends

The detect_ha_backend() function (in aqueduct_core::ha) detects the cluster type automatically:

Patroni failover detection

Pass --patroni-endpoint to enable Patroni-aware failover detection:

aqueduct apply --to prod --patroni-endpoint http://patroni-api:8008

After every migration step, the CLI calls GET /master on the Patroni REST API. A non-200 response (e.g., HTTP 503 when a replica answers) means the primary has changed. The migration is marked interrupted and the CLI exits non-zero.

CloudNativePG

No additional flags required. The CLI reads the app.cnpg.cluster_name GUC to detect a CloudNativePG cluster and relies on the standard primary check (pg_is_in_recovery()).

Maintenance windows

Set a maintenance window in aqueduct.toml to gate Rebuild steps:

[apply]
maintenance_window = "02:00-04:00 UTC"
maintenance_window_applies_to = ["rebuild"]

• Free and In-place steps always execute immediately, regardless of the window.
• Rebuild steps are blocked outside the window.
• --ignore-maintenance-window overrides for emergency runs.

Concurrent apply protection

aqueduct apply acquires a row-level lock in aqueduct.locks before executing:

SELECT * FROM aqueduct.locks
WHERE project = $1
FOR UPDATE NOWAIT;

• The lock is automatically released on crash (unlike advisory locks) when the client disconnects.
• A background heartbeat updates acquired_at every ttl / 3 during long migrations.
• If the CLI crashes, the lock expires after one TTL and the next aqueduct apply --resume can recover.

To release a stale lock manually:

aqueduct unlock --project my-project --to prod

Crash recovery with --resume

If aqueduct apply crashes mid-migration:

aqueduct apply --to prod --resume

--resume reads aqueduct.migrations.progress, identifies completed steps, and skips them. For ambiguous steps (where the CLI cannot determine completion from the catalog), it reports a diagnostic and exits non-zero — the operator must inspect and use --force-retry or --force-skip for the ambiguous step.

Compensating-step protocol (v0.20)

Overview

Certain DDL operations — primarily CREATE and DROP stream tables — are recorded in aqueduct.ddl_log before execution, along with a compensating SQL statement that can undo the operation if the process crashes between the DDL and the subsequent RecordSnapshot step.

The ddl_log table schema:

CREATE TABLE aqueduct.ddl_log (
    id            BIGSERIAL PRIMARY KEY,
    migration_id  BIGINT NOT NULL REFERENCES aqueduct.migrations(id),
    step_index    INT NOT NULL,
    step_type     TEXT NOT NULL,
    applied_sql   TEXT NOT NULL,
    compensating_sql TEXT NOT NULL,
    status        TEXT NOT NULL DEFAULT 'complete',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

The status column progresses through:

When compensating steps are recorded

The row is written with status = 'running' before the DDL executes. It is updated to status = 'complete' after RecordSnapshot succeeds (both in the same transaction).

What --resume does

On --resume, before identifying the checkpoint step:

1. SELECT * FROM aqueduct.ddl_log WHERE migration_id = $1 AND status = 'running' is executed (via GET_PENDING_COMPENSATING_STEPS_SQL).
2. For each running row, the compensating_sql is executed via batch_execute.
3. The row is updated to status = 'compensated' (via MARK_COMPENSATING_APPLIED_SQL).
4. The resume checkpoint search then proceeds as normal.

This closes the gap where a crash between CreateStreamTable and RecordSnapshot left a pg_trickle-managed table without a corresponding catalog snapshot. On the next --resume, the orphaned table is dropped by the compensating step and then re-created cleanly from the checkpoint.

Recovery runbook: "table exists in pg_trickle but not in catalog"

Symptom: pg_trickle reports a table that does not appear in aqueduct.dag_versions or in aqueduct.migrations.progress as completed.

Diagnosis:

-- Check for running ddl_log rows
SELECT id, migration_id, step_index, step_type, status, created_at
FROM aqueduct.ddl_log
WHERE status = 'running'
ORDER BY created_at DESC;

Recovery (automated): Simply run aqueduct apply --resume. The compensating step will drop the orphaned table and the resume will re-create it.

Recovery (manual): If you need to intervene manually:

-- 1. Execute the compensating SQL (from the ddl_log row)
--    e.g. for a CreateStreamTable crash:
DROP TABLE IF EXISTS public.my_table;

-- 2. Mark the row compensated so --resume skips it
UPDATE aqueduct.ddl_log
SET status = 'compensated'
WHERE id = <row_id>;

Then run aqueduct apply --resume to continue from the checkpoint.

Failover during a migration

Between each plan step, aqueduct apply calls check_still_primary() which executes SELECT pg_is_in_recovery(). If the optional --patroni-endpoint is set, it also calls GET /master on the Patroni REST API. On failover detection:

1. The CLI marks the migration as status = 'interrupted' in aqueduct.migrations.
2. It exits non-zero with a clear message including the last completed step.
3. Recovery: reconnect to the new primary and run aqueduct apply --resume.

The interrupted status is distinct from failed — it signals that the migration was cleanly paused due to an infrastructure event, not a SQL error.

Observability: per-step JSON events (v0.17)

aqueduct apply emits structured JSON events to stderr for every migration step:

{"schema_version":1,"event":"step_start","step_index":0,"step_type":"LockDag","migration_id":42,"project":"my-project"}
{"schema_version":1,"event":"step_complete","step_index":0,"step_type":"LockDag","migration_id":42,"project":"my-project","duration_ms":3}
{"schema_version":1,"event":"step_failed","step_index":1,"step_type":"Backfill","migration_id":42,"project":"my-project","duration_ms":1500,"error":"deadlock detected"}

The complete event schema is documented in cli-events-schema.json.

Migration audit log

Use aqueduct audit to review migration history:

aqueduct audit --to prod                  # table format (default)
aqueduct audit --to prod --format json    # JSON array
aqueduct audit --to prod --limit 50       # show 50 most recent

Example: Patroni + maintenance window

# aqueduct.toml
[targets.prod]
dsn = "${PROD_DSN}"

[apply]
maintenance_window = "01:00-03:00 UTC"
lock_timeout = "60s"

# In a CI pipeline with Patroni:
aqueduct apply --to prod --patroni-endpoint http://patroni-api:8008 --yes
# → Exits non-zero with "outside maintenance window" if outside 01:00–03:00 UTC
#   and the plan contains Rebuild steps.
# → Applies immediately if the plan contains only Free/In-place steps.
# → Marks migration interrupted (not failed) if Patroni reports a failover.

Changelog

All notable changes to this project will be documented in this file.

The format is based on Keep a Changelog, and this project adheres to Semantic Versioning.

For planned future versions, see ROADMAP.md.

Unreleased

[0.20.0] - 2025-07-14

This release delivers the three high-priority architectural improvements identified in the Phase 4 engineering assessment: typed executor configuration, full multi-tenant catalog parameterisation, and automated crash recovery via the compensating-step registry.

The ExecutionContext struct replaces the optional builder pattern for the two safety-critical executor fields (desired_state and connection_string), making it a compile error to omit them. All command handlers (apply, rollback, promote) have been updated accordingly.

Every catalog SQL constant now accepts a CatalogSchema parameter and substitutes the schema name via for_schema(). The validate_catalog_schema_not_overridden() stop-gap from v0.19 is removed. aqueduct init --schema my_catalog creates all catalog tables in my_catalog; the multi-tenant isolation integration test verifies that two projects on the same database with different catalog schemas have fully independent version histories.

The --resume flow now queries aqueduct.ddl_log for rows in status = 'running' before advancing to the checkpoint. For each such row the compensating SQL is executed and the row is updated to status = 'compensated'. A new integration test covers the crash-after-DDL, before-RecordSnapshot scenario.

The OutputEmitter is now stored as a process-wide singleton via OnceLock, initialised in main before command dispatch and accessible via output::emitter(). The init command is the first to use the emitter for its success messages.

The httpmock dependency was replaced with wiremock in v0.17, eliminating the last cargo audit --deny warnings finding (RUSTSEC-2025-0052).

The catalog schema version advances from 8 to 9, adding a status column to aqueduct.ddl_log used by compensating-step recovery.

Added

• Typed ExecutionContext struct (ARCH-2): ExecutionContext struct with required desired_state and connection_string fields replaces the optional builder pattern. Omitting either field is now a compile error. for_apply(), for_rollback(), and for_promote() constructors updated.
• Full catalog schema parameterisation (ARCH-1): CatalogSchema threaded through every catalog SQL function. ensure_catalog_current(), connect_and_migrate(), read_live_state(), get_latest_dag_version(), detect_extension_installed(), read_ddl_log(), import_from_live(), compute_promotion_plan(), validate_source_clean(), and destroy_project() all accept &CatalogSchema. validate_catalog_schema_not_overridden() stop-gap removed.
• Multi-tenant catalog isolation test: test_multi_tenant_catalog_isolation verifies that two catalogs (tenant_a, tenant_b) on the same database have independent dag_versions tables with no cross-contamination.
• Automated compensating-step recovery (CORR-2): find_resume_step() queries aqueduct.ddl_log for status = 'running' rows and executes their compensating_sql via batch_execute before advancing to the resume checkpoint. Rows are updated to status = 'compensated' on success.
• COMPLETE_COMPENSATING_STEP_SQL and MARK_COMPENSATING_APPLIED_SQL constants in catalog.rs for the two-phase compensating-step lifecycle.
• CORR-2 crash recovery integration test: test_corr2_compensating_step_recovery inserts a synthetic status = 'running' ddl_log row and verifies that --resume moves it out of the running state.
• Catalog schema version 9: CATALOG_SCHEMA_VERSION advances from 8 to 9. The v8→v9 migration adds a status TEXT NOT NULL DEFAULT 'complete' column to aqueduct.ddl_log and backfills existing rows.
• OutputEmitter process-wide singleton (M-1): output::init_emitter(mode) stores the emitter in a static OnceLock<OutputEmitter>; output::emitter() returns the global reference. main.rs calls init_emitter before dispatch. The init command uses the emitter for its success messages, replacing direct println! calls.

Changed

• for_schema(sql, schema) now substitutes = 'aqueduct' string-literal comparisons and CREATE/DROP SCHEMA IF NOT EXISTS aqueduct statements in addition to the aqueduct. dot-prefix references.
• DestroyOptions gains a catalog_schema: CatalogSchema field.
• All cargo check --tests call sites for read_live_state, get_latest_dag_version, detect_extension_installed, read_ddl_log, import_from_live, compute_promotion_plan, validate_source_clean, and DestroyOptions::new updated throughout CLI commands and integration tests.
• Workspace version bumped 0.19.0 → 0.20.0.

Fixed

• executor.rs:import_from_live was calling read_live_state(client, None) (2-arg form) instead of the 3-arg read_live_state(client, None, catalog_schema). Fixed.
• Extra trailing semicolons (await?;;) in commands/promote.rs and commands/status.rs introduced by the multi-replace pass. Removed.

[0.19.0] - 2026-05-20

This release eliminates every structural correctness bug identified in the Phase 4 engineering assessment, makes the blue/green view swap fully atomic, and ensures every known correctness regression path is covered by a test that would catch it.

The headline change is that the deployment status row update in SwapConsumerViews is now executed inside the surrounding BEGIN/COMMIT block, eliminating the window where consumer views point at the green schema while the deployment row still shows status = 'active'. If the transaction is rolled back for any reason, both the view assignments and the deployment row revert together.

The heartbeat task is now supervised by a JoinHandle-based wrapper. If the heartbeat task panics — something that could previously produce a silent hang — the executor immediately receives a LockLost signal, stops execution, and surfaces the failure. This closes the last known path to a silent runaway executor.

The mock pg_trickle DDL gains a pgtrickle.paused_nodes table so integration tests can assert precisely which nodes were paused during DDL and that the scheduler was fully resumed after apply or after a failure. Two new test-kit helpers (assert_scheduler_idle and assert_scheduler_paused_for) make these assertions one-liners.

Four executor and CLI quality fixes round out the release: the WaitForConvergence poll query is now guarded by a statement_timeout to prevent a hung pg_trickle query from holding the loop past the configured deadline; status --watch applies exponential backoff on repeated connection failures; the EXPLAIN trade-off in cost.rs is documented with a code comment; and aqueduct init and aqueduct apply now return an explicit NotYetImplemented error when catalog_schema is set to anything other than "aqueduct", preventing silent data mixing in multi-tenant deployments until full schema parameterisation ships in v0.20.

All ten failure-mode test cases identified in the Phase 4 assessment are now present and passing.

Added

• Blue/green atomicity (ARCH-3): SWAP_BLUE_GREEN_SQL (the blue_green_deployments status row update) is now executed inside the SwapConsumerViews transaction. A failed mid-swap now rolls back both view assignments and the deployment status atomically. Integration test swap_consumer_views_atomicity verifies this.
• Heartbeat panic supervisor (CORR-3): tokio::spawn(run_heartbeat(...)) is wrapped in a JoinHandle-based supervisor that calls lock_lost_tx.send(true) if the heartbeat task panics. Integration test heartbeat_panic_signals_lock_loss verifies the signal.
• Observable scheduler mock (TEST-3): mock_pgtrickle.rs gains a pgtrickle.paused_nodes(node_name text PRIMARY KEY, paused_at timestamptz) table. pause_scheduler inserts into it; resume_scheduler deletes from it.
• Test-kit helpers: assert_scheduler_idle(client) and assert_scheduler_paused_for(client, nodes) added to aqueduct-testkit. Integration tests mock_scheduler_records_pause and mock_scheduler_idle_after_apply demonstrate usage.
• statement_timeout guard for WaitForConvergence (M-9): the poll SELECT is wrapped in BEGIN READ ONLY; SET LOCAL statement_timeout = '{timeout}ms' so a hung pg_trickle query cannot hold the loop open past the configured deadline. Integration test waitforconvergence_deadline_respected verifies the deadline is enforced.
• Exponential backoff in status --watch (M-8): consecutive connection or poll failures back off up to 5 × interval before retrying; the counter resets on the next successful poll.
• EXPLAIN trade-off comment (M-4): cost.rs::estimate_rows now documents why format!("EXPLAIN...") is intentional and safe. The EXPLAIN call is also wrapped in a BEGIN READ ONLY; SET LOCAL statement_timeout = '5s' block.
• Catalog schema stop-gap (ARCH-1 partial): validate_catalog_schema_not_overridden in catalog.rs returns AqueductError::NotYetImplemented { feature: "catalog_schema override" } for any value other than "aqueduct". Called from aqueduct init and aqueduct apply to prevent silent data mixing until full multi-tenant support lands in v0.20. Integration test catalog_schema_override_rejected_until_implemented verifies this.
• Failure-mode tests (Phase 4): all ten test cases identified in the assessment are now present and passing: consumer_sql_injection_rejected_at_apply, heartbeat_panic_signals_lock_loss, swap_consumer_views_atomicity, plan_fail_on_drift_counts_consumer_deltas, destroy_auto_migrates_catalog, catalog_schema_override_rejected_until_implemented, age_identity_file_traversal_rejected, cnpg_preview_requires_valid_cert, waitforconvergence_deadline_respected, mock_scheduler_records_pause.
• AqueductError::NotYetImplemented { feature } variant added (error code 1308).
• cnpg_preview_requires_valid_cert unit test in preview.rs verifies that a create_preview_cnpg call to a plain-HTTP endpoint fails with a TLS/connection error when CNPG_INSECURE_SKIP_VERIFY is absent (SEC-2).

Changed

• Workspace version bumped to 0.19.0.
• Six user-facing integration files updated to reference version 0.19.0: .github/workflows/aqueduct-plan.yml, .github/workflows/aqueduct-apply.yml, ci/gitlab/aqueduct.gitlab-ci.yml, .pre-commit-hooks.yaml, docs/api-reference.md, docs/introduction.md.
• ROADMAP.md status line updated to v0.19.0 released.

This release closes every open security finding from the Phase 4 engineering assessment, bringing pg_aqueduct to zero open CVEs and zero open critical or high security issues. The headline change is that consumer view bodies are now validated as a single SELECT statement before any database call is made — meaning a misconfigured or maliciously crafted migration file containing DDL like CREATE TABLE or DROP TABLE can never reach the database, even if it somehow made it into your migrations directory. This is an important defence-in-depth layer for teams running Aqueduct in automated CI/CD pipelines.

Two additional critical security fixes ship in this version: the CloudNativePG preview client now validates TLS certificates by default (previously it silently accepted any certificate), and the age secret backend now validates the identity file path for both path traversal and flag injection before passing it to the age subprocess. Together with the consumer SQL validation, these three fixes mean pg_aqueduct meets the full OWASP A03 (Injection) and A02 (Cryptographic Failures) controls relevant to its operation.

Beyond security, this version improves day-to-day operational quality: aqueduct plan --fail-on-drift now correctly detects drift in consumer views and source tables (not just stream tables), aqueduct destroy auto-migrates a stale catalog before querying it, and several documentation files and CI template files that had stale version pins are updated to match the current release. A new .github/SECURITY.md establishes the project's vulnerability reporting policy.

Added

• validate_consumer_sql_is_single_select() is now called immediately before every CREATE OR REPLACE VIEW in the executor (ManageConsumerView and SwapConsumerViews arms). A migration file containing injected DDL is rejected with AqueductError::UntrustedSqlBody before any database call is made (SEC-1).
• Integration test consumer_sql_injection_rejected_at_apply confirms that a consumer migration with a CREATE TABLE body is rejected at the executor level (SEC-1).
• CNPG preview HTTP client now validates TLS certificates by default. Set CNPG_INSECURE_SKIP_VERIFY to skip verification in development environments only. Emits a warn! when the env var is set (SEC-2).
• Unit test cnpg_tls_default_does_not_skip_verification confirms the env-var gate logic (SEC-2).
• age secret backend now validates the identity file path (from SOPS_AGE_KEY_FILE / AGE_KEY_FILE) via validate_secret_path() and rejects paths starting with - (flag-injection guard). Unit tests age_identity_file_traversal_rejected and age_identity_file_flag_injection_rejected cover both rejection cases (SEC-3).
• .github/SECURITY.md: security policy with contact address, response SLA, supported version matrix, and a summary of implemented controls (L-5).
• docs/security.md: new sections covering consumer SQL injection protection, CNPG TLS configuration, and age identity file security (SEC-1, SEC-2, SEC-3).

Changed

• aqueduct plan --fail-on-drift: drift count now includes source_deltas and consumer_deltas in addition to deltas, matching the already-correct status implementation. Previously, consumer-layer and source-layer drift was silently ignored (M-2).
• aqueduct destroy: changed from connect() to connect_and_migrate() so a stale catalog schema is auto-migrated before destroy queries it (M-5).
• release.yml: removed global FORCE_JAVASCRIPT_ACTIONS_TO_NODE24 env var — all actions in the workflow use modern Node.js runtimes that do not require this workaround (M-6). Added just gen-docs step to regenerate docs/api-reference.md on every release tag.
• ingest.rs test helpers: fs::write(...).unwrap() replaced with descriptive .expect("...") calls so a failing CI write produces a clear error message (M-7).
• fmt.rs: renamed KNOWN_FRONTMATTER_KEYS to KNOWN_DIRECTIVE_KEYS with updated doc comment explaining it is used to skip already-emitted keys, not enforce ordering (L-1).
• main.rs: replaced stale // L7 (v0.14) tracking comment with descriptive per-platform CI env var documentation (L-3).
• Cargo.toml: replaced stale # DEP-1 (v0.14): pre-declare serde_yaml comment with # YAML serialization for plan/status/diff --format yaml output. (L-4).
• Six user-facing integration files updated to reference version 0.18.0: .github/workflows/aqueduct-plan.yml, .github/workflows/aqueduct-apply.yml, ci/gitlab/aqueduct.gitlab-ci.yml (also fixes archive name pattern to match the release pipeline: aqueduct-{version}-{os}-{arch}.tar.gz), .pre-commit-hooks.yaml, docs/api-reference.md, docs/introduction.md (CI-1).
• version-lint CI job extended to check nine files: README.md, docs/installation.md, docs/api-reference.md, docs/introduction.md, ci/gitlab/aqueduct.gitlab-ci.yml, .pre-commit-hooks.yaml, .github/workflows/aqueduct-plan.yml, .github/workflows/aqueduct-apply.yml, and ROADMAP.md. Fails if any disagrees with the workspace Cargo.toml version (CI-1, L-6).
• ROADMAP.md status line updated to v0.18.0 released (L-6).
• Workspace version bumped to 0.18.0.

[0.17.0] - 2026-06-10

This release is all about confidence and safety for teams running critical database infrastructure. When you're managing a high-availability PostgreSQL cluster using tools like Patroni, you can now tell Aqueduct exactly where to check that the database is still the primary leader between every migration step. If the database fails over to a replica mid-migration, Aqueduct detects this immediately, stops what it's doing, and marks the migration as interrupted — rather than blindly continuing and potentially corrupting data on a read-only standby. This dramatically reduces the risk of one of the most dangerous scenarios in database operations: a migration that half-completes during a failover event.

Beyond failover protection, this version gives teams unprecedented visibility into what Aqueduct is doing at runtime. Every single migration step — when it starts, how long it takes, whether it succeeded or failed — is now emitted as a structured event, making it trivial to connect Aqueduct to monitoring dashboards, alerting pipelines, or log aggregation systems. The new aqueduct audit command lets you look back at recent migrations and see exactly what happened and when. For teams that need to demonstrate compliance or investigate incidents, this audit trail is invaluable. And with a built-in Prometheus metrics endpoint, you can wire Aqueduct directly into Grafana and get real-time migration dashboards with zero extra tooling.

Added

• --patroni-endpoint <url> flag on aqueduct apply. Between each migration step, the CLI calls GET <endpoint>/master; a non-200 response marks the migration interrupted and aborts (DOC-2 / v0.17-A).
• aqueduct_core::ha module: HaBackend enum, detect_ha_backend(), check_still_primary() (uses pg_is_in_recovery()), and check_patroni_primary() HTTP check (DOC-2 / v0.17-A).
• Between-step primary re-check via SELECT pg_is_in_recovery() in the executor. On failover, migration is marked interrupted (v0.17-A).
• Per-step JSON events emitted to stderr: step_start, step_complete, step_failed with step_index, step_type, migration_id, project, and duration_ms fields (v0.17-B). Documented in docs/cli-events-schema.json.
• aqueduct audit subcommand: lists recent migrations with step counts, error messages, and durations. Supports --format table|json|yaml and --limit N (v0.17-B).
• aqueduct.migration_history SQL view (catalog v8): joins aqueduct.migrations and aqueduct.migration_steps for per-step audit queries (v0.17-B).
• aqueduct_core::catalog::LIST_MIGRATIONS_FOR_AUDIT_SQL query constant (v0.17-B).
• Prometheus metrics endpoint behind --features metrics on aqueduct-cli. Pass --metrics-addr 0.0.0.0:9090 to expose /metrics during apply (v0.17-B).
• Catalog schema v8: adds 'interrupted' to the migrations.status check constraint and a partial index for interrupted rows. Includes auto-migration from v7 (v0.17-A).
• GitHub-native build provenance attestation in release workflow via actions/attest-build-provenance@v2. Verify with gh attestation verify (v0.17-C).
• release-verify CI job: downloads the linux-amd64 release archive, verifies its SHA256 checksum, and confirms aqueduct --version matches the tag (v0.17-C).
• Version linting CI step (version-lint job): fails if README.md status banner, docs/installation.md, and Cargo.toml workspace version disagree (v0.17-D).
• just publish-dry-run recipe for dry-run crates.io publish verification (v0.17-C).

Changed

• Catalog schema bumped from v7 to v8 (CATALOG_SCHEMA_VERSION = 8).
• aqueduct-core and aqueduct-testkit Cargo.toml: publish = true; aqueduct-cli: publish = false (v0.17-C).
• docs/ha-operations.md rewritten to accurately describe implemented behaviors: check_still_primary(), Patroni /master check, per-step events, aqueduct audit, and the recovery runbook. Speculative system_identifier/timeline_id content removed (v0.17-D).
• docs/installation.md: updated version example to 0.17.0, MSRV to 1.88, added build provenance verification section (v0.17-C/D).
• ci.yml security-audit job: removed --ignore RUSTSEC-2025-0052 (httpmock replaced).
• httpmock replaced with wiremock in aqueduct-core dev-dependencies; all five HTTP mock tests updated to the async wiremock API (v0.17-D).

[0.16.0] - 2026-05-20

Version 0.16 is a polish release focused on making Aqueduct a pleasure to work with, both for humans sitting at a terminal and for automated systems parsing its output. Teams integrating Aqueduct into CI/CD pipelines can now choose between structured JSON, clean YAML, a minimal porcelain format designed for shell scripting, or a completely silent quiet mode that suppresses all decorative output and shows only what matters. These improvements mean that whatever your downstream tooling looks like — Slack alerts, dashboards, custom scripts — Aqueduct can speak its language directly without any fragile text scraping.

Under the hood, this version also significantly raised the quality bar for testing, adding a comprehensive suite of binary-level tests that verify every subcommand's exit codes, help text, and output formats behave exactly as documented. This means fewer surprises when you upgrade — if a command's behavior changed accidentally, a test will catch it before it ships. The aqueduct status command gained a new --poll-interval flag with smarter file-change detection, so in watch mode it only reloads migration files when they actually change, making long-running monitoring sessions faster and lighter on the database.

Added

• OutputMode enum (Human, Json, Yaml, Quiet, Porcelain) and OutputEmitter struct in aqueduct-cli::output; global --quiet and --porcelain flags are now routed through the emitter (ERG-1, M11).
• serde_yaml workspace dependency; YAML output in plan, status, and diff subcommands now uses serde_yaml::to_string instead of hand-rolled format strings, correctly escaping quotes, colons, and newlines (ERG-3).
• just gen-docs recipe that regenerates docs/api-reference.md from aqueduct --help output; stale references to --patroni-endpoint, --timeout, --lock-timeout, and --no-cost removed (ERG-4, M3).
• Binary CLI tests (assert_cmd) for every subcommand's exit codes and --help output: quiet_suppresses_decorative_output, porcelain_outputs_key_value_only, yaml_escapes_quotes_and_newlines, plan_help_documents_fail_if_changed, apply_help_documents_dry_run, status_help_documents_fail_on_drift, diff_help_documents_fail_on_drift, destroy_help_documents_dry_run, rollback_help_documents_dry_run, plan_missing_dsn_exits_2, validate_exits_0_on_valid_files, validate_differential_ivm_unsupportable_fails, validate_format_json_produces_valid_json, version_flag_outputs_semver (TEST-2).
• --poll-interval flag on aqueduct status (PERF-2).
• Spec-hash caching in status --watch: migration files are only reloaded when any .sql mtime changes; live state always polled fresh (PERF-2).
• postgres_version_matrix_min_supported integration test verifying the full create/apply/status cycle on PostgreSQL 18+ (H10).
• CONTRIBUTING.md documenting Docker/Testcontainers prerequisites, just recipes, integration test environment variables, PG version matrix, coding standards, and a step-by-step guide for adding cookbook recipes.

Changed

• fmt::format_migration now returns Result<Option<String>> and propagates IO errors; aqueduct fmt prints a diagnostic when a file cannot be read instead of silently treating it as empty (L2).
• CANONICAL_KEY_ORDER constant in aqueduct-core::fmt renamed to KNOWN_FRONTMATTER_KEYS to reflect its actual role (L3).
• TestDb::connection_string field visibility narrowed from pub to pub(crate) to prevent external crates from depending on the internal representation (L14).
• docs/security.md: AWS, GCP, and Vault secret backend rows updated from "Planned (v0.12)" to "Implemented"; injected SQL trust-boundary note updated to describe the CatalogSchema newtype introduced in v0.14.
• docs/api-reference.md regenerated from aqueduct --help output.

Fixed

• aqueduct status --format yaml now produces valid YAML that passes serde_yaml::from_str round-trip including special characters.
• aqueduct plan --format yaml correctly escapes project names containing quotes, colons, and newlines.

0.15.0 - 2026-05-20

This release is the one that gives operations teams confidence to run Aqueduct in the most demanding production environments. Database schema changes are inherently risky — if something goes wrong halfway through, you need a clear path back. Version 0.15 introduces "saga-style" compensating actions: before Aqueduct executes any potentially destructive DDL, it first records what it would take to undo that action. If the process crashes or is interrupted, you can resume from exactly where you left off, or roll back cleanly without any manual investigation. For teams who have ever been woken up at 3 AM to manually clean up a half-finished migration, this is a transformative improvement.

Security and resilience round out this release. Aqueduct now validates catalog schema names at the earliest possible point, firmly rejecting any value that could be used to inject SQL — a class of vulnerability that has burned many database automation tools. Row-level security policies, which PostgreSQL uses to control which users can see which data, are now properly preserved across table recreations, meaning a migration that rebuilds a stream table won't silently drop your access controls. And for situations where a migration step is genuinely ambiguous — perhaps a step that appears to have started but you can't confirm it completed — operators now have --force-retry and --force-skip flags to advance past stuck states safely.

Added

• Saga-style compensating steps: CreateStreamTable and DropStreamTable write a compensating action to aqueduct.ddl_log before executing DDL, enabling crash recovery via --resume.
• --force-retry <step-index> and --force-skip <step-index> flags on aqueduct apply to advance past ambiguous steps (require --yes).
• CatalogSchema newtype validating catalog schema names at parse time, rejecting SQL injection markers, non-identifier characters, leading digits, and reserved system schema names (pg_catalog, information_schema, all pg_* prefixed names).
• ExecutionContext enum with Apply, Rollback, Promote, and DryRun variants replacing the optional builder pattern; named constructors for_apply(), for_rollback(), and for_promote().
• StartBlueGreenDeployment plan step inserting a row into aqueduct.blue_green_deployments with status = 'active' as the first post-lock step.
• All SwapConsumerViews steps wrapped in a single BEGIN ... COMMIT block for an all-or-nothing consumer view swap.
• Real RLS policy capture via pg_policies / pg_class.relrowsecurity before DropStreamTable; RecreatePolicy step restores policies after table recreation; failures are non-fatal and logged to aqueduct.ddl_log.
• rebuild-may-affect-rls lint warning when a FULL-mode table has RLS enabled.
• Batch convergence polling: WaitForConvergence issues a single SELECT ... WHERE table_name = ANY($2) per poll interval instead of one query per node.
• convergence_poll_interval_ms (default 500 ms) and convergence_timeout_secs (default 300 s) options in BuildPlanOptions.
• validate_migration_files_diagnostic and validate_dag_diagnostic public functions returning structured DiagnosticSet entries with file paths, error codes, and severity levels.
• aqueduct validate --format json output matching the lint JSON schema.
• Catalog schema v7: aqueduct.ddl_log gains migration_id bigint and compensating_sql text; aqueduct.blue_green_deployments gains rolled_back_at timestamptz and an updated bg_status_check constraint accepting 'rolled_back'.

Changed

• Executor writes status = 'swapped' after the atomic consumer view swap and status = 'retired' after RetireBlueSchema.
• Integration tests now target postgres:18-alpine exclusively (PostgreSQL 18+ required by pg_trickle).

0.14.0 - 2026-05-20

Version 0.14 is a focused correctness and security release that addressed a number of real-world edge cases discovered through extensive integration testing. The most important of these is that failed migrations are now properly marked as recoverable and can be resumed — previously, a failure in certain code paths could leave the migration in a state where Aqueduct wouldn't offer a clean resume path. A heartbeat mechanism that keeps the database advisory lock alive during long-running migrations now correctly signals the main process when the lock is lost, preventing processes from continuing work on a database they no longer exclusively own.

Three security improvements shipped alongside these correctness fixes. Connection strings that embed plaintext passwords are now caught and rejected for keyword-value style DSNs, matching the protection already in place for URL-style connections. Consumer view SQL bodies are validated to be single SELECT statements before any database objects are created, closing off a potential SQL injection path through migration files. Read-only connections now explicitly begin a READ ONLY transaction and use a safe allowlist for statement timeout values, preventing injection through configuration parameters. Together these fixes ensure that even if a migration file or configuration value is maliciously crafted, Aqueduct won't execute arbitrary SQL against your database.

Added

• pg_version field in aqueduct status --format json output.
• pgtrickle_mock.scheduler_state table in catalog schema v6; mock pause_scheduler / resume_scheduler functions insert and delete rows to assert scheduler state transitions in tests.
• Nine new integration tests covering all CORR, SEC, and TEST items.

Fixed

• Progress preserved on recoverable failures; migration marked recoverable_failure and resumable (CORR-1).
• Heartbeat correctly signals the main executor loop via tokio::sync::watch when the advisory lock is lost (CORR-3).
• Promotion filters destination state by project (CORR-4).
• Promote command migrates catalog before use and records spec_jsonb (CORR-5).
• Consumer view drops now delete the catalog row so drift counts remain accurate (CORR-7).
• Status drift counting now sums stream, source, and consumer deltas (CORR-8).
• aqueduct destroy exits with code 2 on error via anyhow::bail! instead of std::process::exit(1).
• DSN-redaction regex compiled once via LazyLock instead of per-call.
• CI environment variable detection now checks for value "true" rather than mere presence, preventing false-positive CI detection.
• GitHub Actions composite plan and apply actions now map runner OS/architecture to the correct release archive suffix.
• action-smoke workflow exercises composite actions end-to-end with a locally-packaged archive.
• mdbook test is now a blocking CI step; docs-lint fails if any documented subcommand is missing from the binary.

Security

• Keyword-value DSNs (e.g. host=... password=secret) now also checked for plaintext passwords, matching URI-style DSN validation (SEC-1).
• Consumer SQL bodies validated as a single SELECT statement before view creation, preventing DDL injection via migration files (SEC-2).
• connect_read_only now issues BEGIN READ ONLY before SET LOCAL statement_timeout; sanitize_statement_timeout allowlist prevents injection via the timeout argument (SEC-3).

0.13.0 - 2026-05-20

Version 0.13 delivers the complete blue/green deployment workflow that enterprise teams need for zero-downtime schema migrations on production databases. When your changes are complex enough to require restructuring the underlying tables — for example, changing which columns are used as grouping keys in a large aggregation — Aqueduct now orchestrates the full sequence automatically: creating a parallel "green" schema, building the new stream tables there, waiting for them to fully catch up with the "blue" production tables, atomically swapping all consumer views in a single transaction, and then retiring the old schema. The entire process is hands-off once initiated, dramatically reducing the skill and attention required to execute what was previously one of the most nerve-wracking database operations.

Two other significant capabilities arrived in this release. Pre- and post-migration hooks let teams wire Aqueduct into their broader operational playbook — for example, firing a Slack notification when a migration starts, or triggering a cache flush after it completes — all configured declaratively in aqueduct.toml. The new --out and --plan flags let teams generate a migration plan during a code review, have a human or automated system approve it, and then execute exactly that pre-approved plan later — with a cryptographic hash check ensuring the plan hasn't been tampered with between approval and execution. This separation of planning from execution is a powerful governance tool for organizations with strict change-management requirements.

Added

• Full blue/green migration step sequence for topology-restructuring diffs (--strategy blue-green): CreateGreenSchema, CreateStreamTableInGreen, WaitForConvergence, SwapConsumerViews, RetireBlueSchema.
• IMMEDIATE refresh mode parsed from -- @aqueduct:refresh_mode = "IMMEDIATE", with PauseImmediate / ResumeImmediate plan steps.
• --no-immediate-downgrade flag rejecting plans that would temporarily degrade an IMMEDIATE table.
• Pre/post migration hooks from [apply.hooks] in aqueduct.toml emitted as RunHook plan steps; pre-hook fires after LockDag, post-hook fires before RecordSnapshot.
• aqueduct plan --out <file> writes a plan with a SHA-256 spec hash; aqueduct apply --plan <file> executes the pre-computed plan and validates the spec hash to reject stale artifacts.
• Catalog schema v5: aqueduct.migration_steps records every executed step with running / done / failed status for audit and replay.
• aqueduct import now calls ensure_catalog_current and records version 1 in aqueduct.dag_versions; a subsequent aqueduct plan returns an empty plan.
• Real create_preview_cnpg and create_preview_neon HTTP implementations using the CNPG Kubernetes API and the Neon management API.
• Rollback classification: Safe, PointInTime, or DataLoss based on WAL retention window; --within-window / --accept-data-loss flags.
• "schema_version": 1 field in all structured JSON CLI events; schema published at docs/cli-events-schema.json.

0.12.0 - 2026-05-20

Version 0.12 is a major leap forward for production readiness, tackling three areas that enterprise teams consistently ask about: secret management, resilience against infrastructure variation, and confidence in the planner's correctness. Rather than requiring database passwords to live in environment variables or configuration files, Aqueduct can now fetch connection credentials directly from AWS Secrets Manager, GCP Secret Manager, or HashiCorp Vault at runtime. This means your production database password never needs to touch a CI/CD configuration file, a developer's laptop, or a Kubernetes secret — it stays in your secrets vault and is retrieved only at the moment it's needed.

A subtler but equally important improvement is how Aqueduct now deals with different versions of pg_trickle, the underlying PostgreSQL extension it builds on. Rather than assuming all features are available and failing at runtime, Aqueduct now probes the installed version's capabilities at startup and gracefully skips or adapts any step the current version doesn't support. This makes deployments far more robust across environments where the extension version may differ from what was tested in CI. The addition of property-based fuzz testing — automatically generating thousands of random migration scenarios and verifying that the planner never panics and always produces consistent results — gives the entire planning engine a level of correctness assurance that hand-written tests simply can't match.

Added

• PgtrickleCaps struct probing the installed pg_trickle version at startup via to_regprocedure; each capability individually gated for graceful degradation on older builds.
• WaitForRefresh plan step gated on pgtrickle_caps.has_refresh_status; older deployments skip this step.
• Real AWS Secrets Manager (inline SigV4 signing, no AWS SDK dependency), GCP Secret Manager (bearer token), and HashiCorp Vault (KV v2) HTTP clients using reqwest with rustls-tls; all three verified with httpmock unit tests.
• build_source_index() building a HashMap<QualifiedName, Vec<QualifiedName>> once per diff; compute_source_deltas() performs O(1) cascade impact lookups.
• Catalog schema v4 with four new performance indexes on dag_versions, migrations, and locks tables.
• plan_stats() public function computing PlanSummary from &[PlanStep] without executing.
• collect_subgraph() computing the transitive dependency closure for an anchor table; --table flag on aqueduct preview limits preview to that subgraph.
• Full integration test suite in a pgtrickle-integration CI job against PostgreSQL 18 with mock pg_trickle schema.
• test_blue_green_topology_restructure covering diamond DAG creation.
• Binary-level CLI tests using assert_cmd and predicates.
• Project isolation destructive test: applies two projects, destroys one, asserts the other is untouched.
• Property-based fuzz tests via proptest: build_plan never panics for arbitrary inputs; plan_stats always consistent with build_plan.summary.
• Tutorial smoke test parsing bash code blocks in docs and validating known subcommands.
• action-smoke.yml workflow: builds binary, packages as versioned archive, exercises plan and apply against a live PostgreSQL 18 service container.
• reqwest 0.12 (rustls-tls), proptest, assert_cmd, predicates, and httpmock added as dependencies.

Changed

• connect_read_only() and connect_read_only_with_timeout() now issue SET LOCAL statement_timeout before BEGIN READ ONLY.
• estimate_rows() returns typed CostError instead of anyhow::Error; SQL errors and unsupported plan types surfaced as structured tracing warnings.
• Coverage threshold updated to 60% workspace-wide minimum.
• build-artifacts in release.yml now requires the test job to pass.
• macOS Intel (macos-13) added to release build matrix as macos-amd64.
• rust-version in Cargo.toml set to 1.88 (effective MSRV).

Fixed

• Windows builds in release.yml now fail the release when they fail.

Security

• rewrite_query_for_preview_ast() rewrites table references using the sqlparser AST instead of string substitution, eliminating query injection risk via schema or table names.

0.11.0 - 2026-05-19

Version 0.11 focuses on the experience of getting help when something goes wrong. All errors, lint warnings, and validation failures that Aqueduct produces now share a single, unified format that includes the exact file and line number where the problem occurred, a stable numeric error code, and a severity level. This means that editor plugins, CI parsers, and custom tooling can all reliably interpret Aqueduct's output without having to guess at error message formats. The stable numeric codes are particularly valuable for teams writing runbooks — instead of matching on potentially-changing error text, you can reference code 1200 for a planning error or 1300 for an execution error and know that code will remain stable across versions.

The documentation story got a major upgrade too. A new CI job now automatically builds the documentation and verifies that every subcommand mentioned in the docs actually exists and responds to --help, so documentation can never silently drift out of sync with the binary. Security also improved: all database connection strings are now scrubbed of passwords before they appear in error messages, log output, or CI logs — a simple but critical safeguard that prevents credentials from leaking into build artifacts or error reports. The aqueduct plan and aqueduct status commands are now strictly read-only at the connection level, making it physically impossible for them to accidentally modify database state even in edge-case error paths.

Added

• Unified Diagnostic type and DiagnosticSet with file/line/column context, replacing separate ValidationError, LintDiagnostic, and ad-hoc error strings.
• Stable numeric error codes on AqueductError: 1000–1099 database, 1100–1199 configuration, 1200–1299 planning, 1300–1399 execution, 1400–1499 catalog.
• --fail-on-drift flag for aqueduct diff, exiting 1 when any delta is non-Unchanged.
• YAML output format for aqueduct status (--format yaml).
• docs/roles.sql with SQL grant templates for aqueduct_planner, aqueduct_applier, aqueduct_preview, and aqueduct_destroy roles.
• docs-build CI job running mdbook build and mdbook test on every push.
• docs-lint CI job verifying every documented subcommand responds to --help and that binary --version matches Cargo.toml.
• docs-cli justfile recipe generating a Markdown CLI reference from --help output.

Changed

• aqueduct plan now exits 0 by default; exits 1 only when --fail-if-changed or --fail-on-drift is explicitly set.
• executor.execute() returns ExecutionResult { migration_id, dag_version } instead of a bare u64; apply_complete JSON event emits both fields as separate keys.
• ManageWalSlot uses typed WalSlotAction enum (Create / Drop) instead of a free-form string, eliminating the "Unknown action" dead code path.
• Typed PlanExecutor constructors: for_apply(), for_rollback(), for_promote(), for_dry_run().
• Typed DAG state newtypes: DesiredDagState, LiveDagState, RecordedDagState.
• lib.rs exports CLI_VERSION = env!("CARGO_PKG_VERSION") as the single source of version truth.
• Example workflow version pins updated from @v0.4.0 to @v0.11.0.

Fixed

• aqueduct plan and aqueduct status now use connect_read_only() and cannot accidentally mutate data.

Security

• redact_dsn() replaces passwords with *** in all connection strings shown in error messages, covering both URL-form and keyword-value DSNs.

0.10.0 - 2026-05-19

Version 0.10 closes out a category of subtle bugs that could cause incorrect behavior when working with consumer views — the downstream SQL views that read from stream tables. Previously, a plan that only added, dropped, or modified consumer views without touching any stream tables was silently treated as a no-op and never executed. This was a correctness bug with real consequences: teams could deploy consumer view changes, see no errors, and then discover their views weren't updated because Aqueduct thought there was nothing to do. All consumer operations now correctly flow through the full plan-and-execute path with proper tracking and reporting.

Project isolation — the ability to manage multiple independent DAGs in the same PostgreSQL database — received a significant reliability upgrade. All live state reads now correctly filter by project, meaning one project's tables can never accidentally appear in another project's plan or status. The aqueduct destroy command now verifies that it actually owns the tables it's about to drop, preventing accidental cross-project destruction. Several concurrency scenarios were also hardened: the lock heartbeat is now bound to the specific holder that acquired the lock, so a fresh Aqueduct process starting up won't be fooled by a stale heartbeat from a crashed predecessor into thinking the database is still locked.

Added

• Catalog schema v3: aqueduct.stream_table_ownership(project, schema_name, table_name, managed_since) with PRIMARY KEY (schema_name, table_name).
• recoverable_failure added to the aqueduct.migrations status_check constraint.
• Six new integration tests: consumer-only apply, concurrent apply race, stale plan detection, two-project isolation, resume step skipping, and holder-bound heartbeat.

Fixed

• Consumer-only plans are no longer silently skipped as no-ops; PlanSummary tracks consumer_creates, consumer_drops, and consumer_alters, and is_empty() / total_changes() include consumer deltas.
• RecordSnapshot uses RETURNING version to capture the actual bigserial value assigned by the database.
• read_live_state() populates sources from spec_jsonb in aqueduct.dag_versions instead of always returning empty.
• read_live_state() and read_live_consumers() filter by project via stream_table_ownership; destroy_project verifies ownership before dropping tables (--force-unowned to bypass).
• check_pgtrickle_version() probes to_regprocedure before calling to avoid a database error when pg_trickle is absent.
• classify_delta() no longer panics on missing desired / actual in the AlterQuery arm; returns Rebuild instead.
• Failed migrations now marked recoverable_failure and resumable; LockDag always re-executes on resume, DDL steps skipped only for steps already confirmed complete.
• Scheduler pause now runs inside the LockDag executor arm after the lock is acquired, preventing a pre-lock pause from affecting other projects.
• Heartbeat SQL is now holder-bound (WHERE project = $1 AND holder = $2); zero rows affected logged as a lock-stolen warning.
• Project lock released on all exit paths via an explicit cleanup block.
• Step progress checkpoint writes are now fatal rather than silently continuing.
• aqueduct apply --dry-run uses a plain connection to avoid upgrading the catalog schema during previews.
• DROP TABLE in destroy and executor no longer uses CASCADE; opt in with --force-cascade.
• aqueduct rollback now enforces --accept-data-loss when the rollback plan contains rebuild-class steps.
• First-time stream table creations increment create_count, not rebuild_count, preventing false-positive maintenance-window blocking on initial deployments.
• Lock holder string is now aqueduct/{version}/{hostname}/{pid}/{ts}, making concurrent processes distinguishable.
• Scheduler pause failure is now fatal by default.

0.9.0 - 2026-05-19

Version 0.9 is a turning point in how operators interact with Aqueduct day-to-day. The new aqueduct diff command provides a clear, human-readable comparison between what your migration files describe and what currently exists in the live database — across every table, every column, and every configuration detail. This makes drift visible at a glance before you commit to running any migration, transforming what was previously a moment of uncertainty before a production apply into a clear-eyed, informed decision. Whether you're checking before a deployment, investigating after an incident, or auditing compliance, aqueduct diff gives you the full picture.

This release also introduced the secret backend system that underpins enterprise-grade credential management: database connection strings can now reference secrets stored in AWS, GCP, Vault, SOPS-encrypted files, or age-encrypted files using a simple ${secret:BACKEND:KEY} syntax, keeping credentials entirely out of configuration files and CI logs. Every successful aqueduct apply now emits a structured JSON event with the migration ID, version numbers, and project name — making it trivial for CI systems to capture and act on migration results. For teams using interactive terminals, a confirmation prompt before any apply ensures no one accidentally runs a migration they didn't intend to.

Added

• aqueduct diff subcommand computing a per-table diff between desired state (migration files) and live database state; supports --table and --format text|json|yaml|markdown.
• aqueduct plan --fail-on-drift: exits 1 when changes are detected, 0 when the DAG is up-to-date.
• Interactive confirmation prompt on aqueduct apply in a TTY ("Apply N changes? [y/N]"); --yes / -y skips it.
• Structured apply_complete JSON event on stderr with migration_id, from_version, to_version, and project fields; CI composite action reads these as output variables.
• aqueduct validate --strict: promotes warnings to errors and exits non-zero.
• aqueduct plan --format yaml output.
• ${secret:BACKEND:KEY} inline secret syntax in DSN strings; supported backends: env, aws, gcp, vault, sops, age.
• --quiet / --porcelain global flags suppressing info-level log output.
• Eight new PlanStep variants: RecreatePolicy, DetachOutbox, ReattachOutbox, ManageWalSlot, PauseImmediate, ResumeImmediate, WaitForRefresh, RunHook.
• cargo audit security-audit CI job running on every push and pull request.
• Coverage threshold enforced at 70% with fail_ci_if_error: true.
• DSN masking in CI composite plan action via ::add-mask::.

Changed

• aqueduct plan exit codes changed: 0 = no changes, 1 = changes detected (or --fail-on-drift), 2 = error.
• CLI errors now exit with code 2 instead of 1.
• All regex::Regex patterns compiled once via std::sync::LazyLock.
• cdc_mode values normalized at parse time: "ROW" / "STATEMENT" → "trigger", "WAL" → "wal", "NONE" / "DISABLED" → "none"; unknown values rejected with a clear error.
• Integration tests run against PostgreSQL 18 (postgres:18-alpine).

Fixed

• status --watch creates a fresh database connection on each poll tick; network errors logged as warnings rather than aborting the watch.

Security

• validate_secret_path() rejects key arguments containing .. to prevent path traversal in SOPS and Age subprocess invocations.
• DSN strings containing cleartext passwords are now rejected; pass --allow-plaintext-password to override.

0.8.0 - 2026-05-19

Version 0.8 tackles one of the hardest problems in database automation: what happens when things go wrong in the middle of a migration. The executor now tracks progress at the granularity of individual steps, persisting a checkpoint to the database after each one. If a migration is interrupted — whether by a network blip, a process crash, or an operator hitting Ctrl-C — re-running aqueduct apply --resume will pick up from exactly where it left off, skipping every step that already completed successfully. For long-running migrations that touch large tables, this means an interruption no longer requires starting over from scratch.

The lock mechanism that prevents two Aqueduct processes from stepping on each other's work was made significantly more robust. A background task now continuously renews the advisory lock while a migration is running, sending a heartbeat every ten seconds on a dedicated database connection. This ensures that a migration taking longer than PostgreSQL's default lock timeout can complete without its lock expiring mid-flight. If the heartbeat connection is lost — for example, because the database restarted — the main migration process is immediately notified and can shut down cleanly rather than continuing to issue commands to a database that may no longer be in the expected state.

Added

• Resume-aware step-level progress tracking: PlanExecutor skips steps below last_completed_step on resume and persists the step index to aqueduct.migrations.progress after each non-structural step.
• Lock heartbeat: background tokio task renewing the advisory lock every 10 s via a dedicated connection; shuts down cleanly after plan completion or error.
• Catalog schema v2 via CATALOG_INIT_V2_SQL; ensure_catalog_current() applies the v1→v2 migration on every connect.

Changed

• build_plan() now returns Result<Plan> instead of Plan; all call sites propagate the error with ?.
• AlterStreamTable executor passes new_query as the 6th argument to pgtrickle.alter_stream_table.
• aqueduct plan --validate-ivm now calls validate_ivm_supportability() for DIFFERENTIAL tables and validate_sql_syntax() for others.
• Column removal classified as Rebuild unless the column is in the trailing position of the select list.
• run_steps() uses a drain-then-pause protocol: pause_scheduler before the first migration step, resume_scheduler in a deferred cleanup guard.
• Diamond DAG convergence nodes trigger group promotion: all transitive ancestors elevated to the highest migration class in the group.
• poll_once() in aqueduct status loads the migrations directory and computes a real diff for accurate drift detection.
• spec_jsonb in aqueduct.dag_versions now stores the full serialised DagState instead of an empty {}.

Fixed

• aqueduct rollback now deserialises spec_jsonb from the dag_versions row instead of re-reading migration files from disk.
• CreateStreamTable on non-pg_trickle instances returns AqueductError::PgTrickleNotInstalled instead of silently creating bare tables.

Removed

• Unused deadpool-postgres workspace dependency.

0.7.0 - 2026-05-18

Version 0.7 is the documentation and confidence-building release. Thirty detailed cookbook recipes were added covering every migration pattern that Aqueduct supports — from simply adding a column, to changing GROUP BY keys, to performing a full blue/green topology restructure — each with a complete before-and-after example and a matching integration test. Whether you're a new team trying to understand what Aqueduct can do, or an experienced operator looking for the exact right pattern for a non-obvious migration, the cookbook gives you a concrete, tested answer rather than requiring you to reason from first principles.

Public benchmarks were published for the first time, quantifying the core value proposition in plain numbers: on a 200-node DAG with a 5-node change set, Aqueduct applies changes in roughly 6 milliseconds — compared to approximately 180 seconds that a naive drop-and-recreate approach would take on the same workload. That's a 28,000× speed improvement. Security and high-availability documentation were written in depth, covering everything from setting up least-privilege database roles to handling failover events and crash recovery. With 197 tests now passing across unit, integration, and CLI test suites, this version represents the highest level of tested confidence the project had achieved to that point.

Added

• 30 cookbook patterns in docs/cookbook/ covering every migration class (Free, In-place, Rebuild, Blue/green, Create, Drop), each with an integration test.
• Public benchmarks in benchmarks/: 200-node DAG, 5-node change set — targeted apply (~6 ms) vs. drop/recreate (~180 s), ~28 000x speed improvement.
• docs/security.md: least-privilege role setup, secret backends, read-only guarantees, and audit trail.
• docs/ha-operations.md: primary detection for Patroni, CloudNativePG, and Stolon; maintenance windows; crash recovery; failover handling.
• docs/api-reference.md: complete CLI command reference, front-matter directive table, and aqueduct.toml schema.
• 197 tests total (101 unit + 63 integration + 33 CLI); all passing, none skipped.

0.6.0 - 2026-05-18

Version 0.6 completes the core operational toolset that teams need to manage Aqueduct migrations across the full software delivery lifecycle. The aqueduct promote command solves the environment promotion problem elegantly: rather than manually copying migration files between staging and production and hoping you didn't miss anything, you point Aqueduct at your source environment, it validates that everything is clean, computes exactly what needs to happen in the destination environment, asks for confirmation, and records the promotion event in the audit trail. This makes the act of "promoting to production" repeatable, auditable, and safe — the same migration files validated in staging are what get applied to production.

The aqueduct destroy command gives teams a clean, safe way to tear down all objects associated with a project — useful for cleaning up ephemeral preview environments or decommissioning retired workloads. High-availability primary detection was formalized with support for Patroni, CloudNativePG, and Stolon, so Aqueduct can reliably determine whether it's talking to a writable primary before taking any action. The aqueduct status --watch mode, combined with configurable alerting thresholds, means teams can leave a monitor running in production that immediately flags any drift between what's deployed and what the migration files describe — turning Aqueduct into a lightweight continuous compliance tool as well as a migration executor.

Added

• aqueduct promote command promoting a migrations directory from one environment to another, with source-clean validation, destination plan computation, interactive confirmation, and promotion recorded in aqueduct.migrations.
• Encrypted secret backends: AWS Secrets Manager, GCP Secret Manager, HashiCorp Vault, SOPS-encrypted files, and age-encrypted files.
• ${secret:BACKEND:KEY} inline syntax in DSN strings.
• aqueduct status --watch long-running watch mode with configurable --interval, --max-drift-count, and JSON output for alerting pipelines.
• HA primary detection for Patroni (HTTP GET /master), CloudNativePG (GUC), and Stolon (pg_stat_activity), returning a typed HaBackend enum.
• Planner fuzzing test: 8 LCG-seeded random mutations over a 4-table DAG asserting the plan-convergence invariant (deterministic seed 0xDEAD_BEEF_CAFE_1234).
• aqueduct destroy command tearing down all project-owned stream tables, consumer views, and catalog rows; requires --confirm or --dry-run.
• 167 tests total (101 unit + 33 integration + 33 CLI); all passing, none skipped.

0.5.0 - 2026-05-18

Version 0.5 bridges two worlds that data teams live in simultaneously: dbt, the SQL transformation tool that millions of data engineers use to define their data models, and Aqueduct, which manages the real-time stream tables that power live analytics. The new aqueduct ingest --from dbt-target command reads the compiled output of a dbt project and automatically generates a canonical Aqueduct migrations directory. Schedule, refresh mode, CDC mode, schema, and dependency relationships are all inferred from the dbt model configuration, so no manual translation work is required — what you express in dbt flows directly into Aqueduct.

The practical impact is that teams can maintain a single source of truth in their dbt project and have Aqueduct automatically stay in sync. The examples/dbt-roundtrip/ example included with this release demonstrates the full workflow: run dbt, ingest the results into Aqueduct, validate, plan, apply, and roll back — end to end, without any manual steps. Because the ingest command is idempotent, it can safely be re-run after every dbt compile without risk of creating duplicates or inconsistencies, making it trivial to slot into CI/CD pipelines that already build dbt artifacts.

Added

• aqueduct ingest --from dbt-target command reading compiled dbt artefacts and generating a canonical aqueduct migrations directory; idempotent.
• Front-matter directives populated from dbt model config (+schedule, +refresh_mode, +cdc_mode, +schema, +depends_on).
• examples/dbt-roundtrip/ demonstrating the full ingest → validate → plan → apply → rollback round-trip.

0.4.0 - 2026-05-18

Version 0.4 is the release that makes Aqueduct a first-class member of your development workflow rather than just a tool you run manually before deployments. The aqueduct fmt command automatically reformats migration files to a canonical style — consistent SQL keyword casing, stable front-matter ordering, no trailing whitespace — and a --check mode makes it easy to enforce this standard in CI without modifying files. The aqueduct lint command catches five categories of common mistakes before they ever reach a database: schedules that are too aggressive for the workload, queries that IVM can't optimize, consumer views that reference sources that don't exist, and more.

The GitHub Actions composite actions make it genuinely effortless to add Aqueduct to any GitHub-hosted project: a single line in your PR workflow will install the binary, run aqueduct plan, and post the plan as a PR comment so reviewers can see exactly what database changes a PR will produce before merging. The same experience is available for GitLab CI via provided templates. Pre-commit hooks for fmt, validate, and lint mean that quality checks happen before code is even committed, catching problems at the earliest possible moment. Together, these integrations turn database migration management from an afterthought into a first-class, automated part of the code review process.

Added

• aqueduct fmt command canonicalising migration files: SQL keyword casing, stable front-matter directive order, trailing whitespace removal. --check mode for CI.
• aqueduct lint command with five rules: schedule-too-aggressive (warning), full-refresh-no-filter (warning), differential-ivm-unsupportable (warning), cypher-source-missing (error), consumer-source-not-found (error). --fail-on-warn and --format json flags.
• trickle-labs/pg-aqueduct/.github/actions/plan@v0.4.0 composite action: installs binary, runs aqueduct plan --format markdown, posts plan as PR comment.
• trickle-labs/pg-aqueduct/.github/actions/apply@v0.4.0 composite action: runs aqueduct apply with --resume support, masks DSN secrets.
• Example workflows: aqueduct-plan.yml (on PRs touching migrations/) and aqueduct-apply.yml (on push to main).
• GitLab CI templates in ci/gitlab/aqueduct.gitlab-ci.yml.
• ci/hooks/aqueduct-pre-commit git hook running fmt --check and validate.
• .pre-commit-hooks.yaml defining aqueduct-fmt, aqueduct-validate, and aqueduct-lint hooks for the pre-commit framework.
• 118 tests total (73 unit + 25 integration + 20 CLI); all passing, none skipped.

0.3.0 - 2026-05-18

Version 0.3 introduces three powerful capabilities that together define Aqueduct's vision for sophisticated, production-safe database change management. Blue/green plan steps — creating a parallel schema, building new stream tables, waiting for convergence, atomically swapping consumer views in a single transaction, retiring the old schema — provide the foundation for zero-downtime migrations of even the most complex DAG topologies. Consumer views give teams a clean way to expose stable, versioned SQL interfaces to downstream applications, completely decoupling what the application queries from the underlying stream table implementation.

The aqueduct preview command is perhaps the most innovative addition in this release: it creates a throwaway copy of your entire DAG in a scratch schema, populated with a sampled subset of real production data, so you can run and test your new migration against realistic data without any risk of affecting production. This works natively against any PostgreSQL instance and has stub implementations for CloudNativePG and Neon-managed databases. For data teams building and iterating on complex analytics queries, the ability to spin up a safe, realistic preview environment in seconds — and tear it down just as quickly — fundamentally changes how development and testing workflows operate.

Added

• Blue/green plan step variants: CreateGreenSchema, CreateStreamTableInGreen, WaitForConvergence, SwapConsumerViews, RetireBlueSchema.
• Consumer view management: declare views in migrations/consumers/ with @aqueduct:kind = consumer, @aqueduct:source, and @aqueduct:expose_as; new ConsumerSpec, ConsumerDelta, and ConsumerDeltaKind types; ManageConsumerView plan step.
• aqueduct preview --branch <name> creating a throwaway copy of the DAG in a scratch schema with TABLESAMPLE-sampled data; native, CloudNativePG stub, and Neon stub backends.
• Optional companion extension support: aqueduct.ddl_log DDL event log, detect_extension_installed(), read_ddl_log().
• Catalog schema v2: aqueduct.ddl_log, aqueduct.consumer_views, aqueduct.blue_green_deployments tables.

0.2.0 - 2026-05-18

Version 0.2 introduces the intelligence at the core of Aqueduct's value proposition: the migration classifier. When you change a migration file, Aqueduct doesn't just blindly re-execute everything — it analyzes what changed and classifies the migration into one of four cost categories: Free (can be applied with zero downtime and no data movement), In-place (a structural change made with a simple ALTER TABLE), Rebuild (requires recreating the table, typically within a maintenance window), or Blue/green (a topology-level restructure requiring the full zero-downtime workflow). This classification drives every decision Aqueduct makes about how to safely apply your changes.

A particularly important extension of this classifier is the automatic cascade analysis for ALTER TABLE changes. When a source table that stream tables depend on changes — for example, a column is renamed or its type changes — Aqueduct automatically identifies every stream table in the DAG that references that source and escalates them to a Rebuild classification. This prevents the silent failures that plague naive migration tools, where a source table change breaks dependent views in ways that aren't discovered until queries start returning errors. The new --explain-cost flag rounds out this release by showing per-step row count estimates and duration projections, giving operators a clear picture of what a migration will actually cost before committing to run it.

Added

• Full migration classifier decision tree: Free (schedule / cdc_mode / refresh_mode DIFF→FULL), In-place (add or drop a column), Rebuild (rename column, change GROUP BY, change or add/remove a JOIN, change WHERE, FULL→DIFF), and Blue/green (topology restructure).
• @aqueduct:cypher_source front-matter directive for pg_eddy Cypher-backed stream tables.
• ALTER TABLE cascade analysis (Tier 1): AlterBaseTable plan step plus automatic Rebuild cascade for all stream tables referencing the altered source table.
• aqueduct plan --explain-cost showing per-step row counts (via EXPLAIN FORMAT JSON) and duration estimates.
• 85 tests total (unit + integration + CLI); all passing, none skipped.

0.1.0 - 2026-05-18

Version 0.1 is the foundation on which everything else is built. At its core, Aqueduct solves a problem that every team managing real-time analytics in PostgreSQL eventually hits: how do you safely change a complex web of interdependent materialized views and stream tables — built with extensions like pg_trickle — without causing outages, data loss, or hours of manual database administration? The answer is to treat database schema changes the same way software engineers treat code changes: with version control, automated planning, safe execution, and the ability to roll back. This first release establishes all of those primitives.

The key capabilities in this release — automatic DAG inference from SQL FROM/JOIN clauses, cycle detection, migration classification, advisory locking for crash-safe serialization, read-only plan and status commands, structured JSON logging for CI, and standby detection via pg_is_in_recovery() — together form a complete and production-usable system even at this initial version. The minimal example included in the repository shows a 3-node DAG going from nothing to a fully-managed real-time analytics pipeline in a handful of configuration lines. With 58 tests passing and five CI jobs running on every commit, the project launched with a commitment to quality and correctness that every subsequent release has built upon.

Added

• aqueduct init, plan, apply, status, validate, rollback, import, and unlock CLI commands.
• aqueduct.* catalog schema: dag_versions, migrations, locks, and cluster_profile tables.
• SQL front-matter parsing (-- @aqueduct:key = value) for kind, schedule, refresh_mode, cdc_mode, depends_on, and owned directives.
• DAG dependency inference from SQL FROM / JOIN clauses via sqlparser (pure Rust, no C build dependencies).
• Cycle detection in the DAG with clear pre-DDL error reporting.
• Migration classification into four cost classes: Free, In-place, Rebuild, and Blue/green.
• Template variable substitution: {{ var.NAME }} from per-target vars in aqueduct.toml; ${ENV_VAR} from the environment.
• allow_full_refresh = false config blocking Rebuild-class plans.
• maintenance_window config gating Rebuild and Blue/green steps to a configured time window.
• Standby detection via pg_is_in_recovery(), preventing applies against hot standbys.
• Read-only transactions for plan and status commands.
• Advisory lock with TTL expiry for crash-safe serialisation.
• Structured JSON logs on stderr when --log-format json is set or when running in CI ($CI, $GITHUB_ACTIONS, $GITLAB_CI, $CIRCLECI).
• application_name set to aqueduct/<project>/<migration_id> on every connection, making in-flight migrations visible in pg_stat_activity.
• 58 tests (42 unit, 10 integration, 6 CLI); all passing, none skipped.
• Five GitHub Actions CI jobs: lint, unit tests, integration tests, CLI tests, and coverage.
• examples/minimal/ — 3-node DAG example with a complete aqueduct.toml.
• ESSENCE.md — architecture overview and design principles.
• justfile — developer convenience recipes (test, lint, fmt, coverage).

pg_aqueduct Roadmap

Status: v0.20.0 released. v1.0 is the first production-ready release. This roadmap reflects the agreed design in plans/pg-aqueduct-plan.md. Versions correspond directly to the implementation phases described there.

Overview

pg_aqueduct is a declarative migration and orchestration tool for stream-table DAGs powered by pg_trickle. It is to a stream-table DAG what Atlas is to a relational schema and what Terraform is to infrastructure.

The tool is delivered as a standalone Rust CLI (aqueduct) with an optional companion PostgreSQL extension that adds DDL event triggers and SQL-callable diagnostics. The CLI operates against any PostgreSQL cluster that has pg_trickle installed; it requires no superuser privileges beyond what pg_trickle itself needs.

Each version in this roadmap is independently releasable and immediately useful. Later versions build on earlier ones without breaking the established CLI surface.

Version Map

v0.1 — Foundation: Plan, Apply, Rollback

Target effort: 4–8 weeks for one engineer. Prerequisite: pg_trickle exposes a stable JSON projection of a stream-table spec (small change — file as a pg_trickle issue if not yet present).

This is the MVP release. It delivers the full plan → apply → rollback → import cycle against a pg_trickle cluster. After v0.1, a team can fully adopt GitOps for their stream-table DAG. No PostgreSQL extension is required; the CLI bootstraps its own aqueduct.* catalog schema over a plain libpq connection on first aqueduct init, exactly like Atlas manages atlas_schema_revisions.

Phase 0 — Repository Bootstrap (3 days)

• Create trickle-labs/pg-aqueduct with the standard trickle-labs repository layout (mirroring trickle-labs/pg-tide as the established precedent for extracting a tightly-scoped companion repository).
• Cargo workspace with three initial crates:
  • aqueduct-core — planner, differ, plan executor, catalog access
  • aqueduct-cli — the aqueduct binary and clap-based command surface
  • aqueduct-testkit — shared Testcontainers helpers for integration tests
• No pgrx in this phase. The optional companion extension is deferred to v0.3 (Phase 4). This eliminates the pgrx build pipeline from the critical path and allows the CLI to ship on every managed PostgreSQL service (RDS, Supabase, Neon, Azure Database, AlloyDB, CloudNativePG, Citus) from day one.
• justfile with standard recipes: just build, just test, just lint, just fmt.
• CI matrix on Linux and macOS: unit tests + Testcontainers-based integration tests against pg_trickle (via mock schema; real pg_trickle in a follow-up).
• Code-coverage gate to prevent regression below the initial baseline.
• Bootstrap aqueduct init:
  • Connects to the target database over libpq.
  • Creates the aqueduct schema and all catalog tables (see catalog definition below).
  • Handles the schema name collision case: if aqueduct already exists and is not owned by the connecting role, fails with a clear error. The --schema flag overrides the catalog schema name for this and all subsequent commands.
  • Stores the chosen schema name in aqueduct.cluster_profile so subsequent commands pick it up automatically.
• README.md and ESSENCE.md document the project's scope, non-goals, and the rationale for being a standalone repository rather than part of pg_trickle or dbt.

Catalog Tables (created by aqueduct init)

CREATE SCHEMA IF NOT EXISTS aqueduct;

-- Records a full snapshot of the DAG spec at each successful apply.
CREATE TABLE aqueduct.dag_versions (
    version    bigserial PRIMARY KEY,
    project    text      NOT NULL,
    spec_hash  bytea     NOT NULL,   -- SHA-256 of spec_jsonb (canonical JSON, keys sorted)
    applied_at timestamptz NOT NULL DEFAULT now(),
    applied_by text      NOT NULL,
    plan_jsonb jsonb     NOT NULL,
    spec_jsonb jsonb     NOT NULL
);

-- Tracks each migration run with its steps and progress (for resume).
CREATE TABLE aqueduct.migrations (
    id              bigserial PRIMARY KEY,
    from_version    bigint REFERENCES aqueduct.dag_versions(version),
    to_version      bigint REFERENCES aqueduct.dag_versions(version),
    started_at      timestamptz NOT NULL,
    finished_at     timestamptz,
    status          text NOT NULL,  -- 'running' | 'committed' | 'failed' | 'rolled_back'
    plan            jsonb NOT NULL,
    progress        jsonb NOT NULL DEFAULT '{}'::jsonb,
    cli_version     text,
    plan_format_version int NOT NULL DEFAULT 1
);

-- Serialises concurrent apply runs per project.
CREATE TABLE aqueduct.locks (
    project      text PRIMARY KEY,
    holder       text      NOT NULL,
    acquired_at  timestamptz NOT NULL,
    ttl          interval  NOT NULL,
    paused_nodes jsonb     NOT NULL DEFAULT '[]'::jsonb
);

-- Per-cluster calibration data (throughput benchmarks, catalog version).
CREATE TABLE aqueduct.cluster_profile (
    key         text PRIMARY KEY,
    value_jsonb jsonb NOT NULL,
    measured_at timestamptz NOT NULL DEFAULT now()
);

The catalog schema is self-migrating: every CLI command compares its compiled-in CATALOG_SCHEMA_VERSION against the value in aqueduct.cluster_profile and applies any pending catalog migrations from an embedded SQL bundle (include_str!()) before doing anything else. No external files are required at runtime.

Phase 1 — Read-Only Plan (1.5 weeks)

Deliverables

• TOML project loader. Parses aqueduct.toml at the project root. Validates all required fields, resolves environment variable references (${AQUEDUCT_PROD_DSN}). The [targets.*] and [apply] sections are fully parsed. Template variables in vars = { ... } blocks are stored for substitution into migration front-matter.

• Migrations-folder parser. Reads migrations/streams/*.sql and migrations/sources/*.sql. Parses SQL front-matter directives (-- @aqueduct:key = value). Unknown keys emit a lint warning for forward-compatibility. Template variable substitution ({{ var.NAME }}) is applied before parsing. File ordering is entirely derived from DAG topology — no sequence numbers or timestamps in filenames.

• Live-state reader. Queries pgtrickle.pgt_stream_tables to build the current "actual" DAG state. Falls back to an empty state if pg_trickle is not installed.

• DAG differ. Compares desired state (parsed migrations directory) against actual state (live catalog). Computes topological order using Kahn's algorithm. Detects and rejects cycles. Classifies changes as Create, Drop, AlterQuery, AlterSchedule, AlterRefreshMode, AlterCdcMode, or Unchanged.

• Query pre-validation. Before classifying any new or changed stream-table query, the planner runs two validation passes:

  1. Parse check — sqlparser parses the SQL. Parse failures are plan errors.
  2. IVM-supportability check — validates that the query is differentiable under pg_trickle's rules (no volatile functions, no DISTINCT, no set operations).

• Plan classification. Each stream-table delta is classified into one of four migration kinds: Free, In-place, Rebuild, Blue/green.

• Human-readable plan renderer. Outputs Text (default), JSON (--format json), and Markdown (--format markdown).

• aqueduct plan command. Computes and renders the migration plan.

• aqueduct status command. Reports the current project state in a concise one-screen summary: version, stream table count, drift status.

• aqueduct validate command. Offline check — no database connection required. Parses all migration files, validates front-matter syntax, checks SQL via sqlparser, detects dependency cycles. Reports errors and warnings.

Exit criteria. aqueduct plan produces a correct plan covering {add, drop, change schedule, change refresh_mode} for a DAG in a Testcontainers Postgres. ✅ the full apply cycle.

Deliverables

TOML project loader. Parses aqueduct.toml at the project root. Validates all required fields, resolves environment variable references (${AQUEDUCT_PROD_DSN}), and rejects plaintext passwords in config files unless --allow-plaintext-password is explicitly set (security requirement per §10.20). The [targets.*] and [apply] sections are fully parsed. Template variables in vars = { ... } blocks are stored for substitution into migration front-matter.

Migrations-folder parser. Reads migrations/streams/*.sql and migrations/sources/*.sql. Parses SQL front-matter directives (-- @aqueduct:key = value) using a lenient TOML-inline-value grammar. Unknown keys emit a lint warning for forward-compatibility. Template variable substitution ({{ var.NAME }}) is applied before the TOML parser sees directive values. Detects and reports missing variables. File ordering is entirely derived from DAG topology — no sequence numbers or timestamps in filenames, eliminating the merge-conflict problem that plagues Flyway and Alembic.

Live-state reader. Queries pgtrickle.pgt_stream_tables, pg_class, and pg_attribute to build the current "actual" DAG state. Reconstructs a StreamTableSpec per node using the JSON spec projection from pg_trickle (or falls back to direct catalog column reads if the projection function is not yet available).

DAG differ. Compares the desired state (parsed migrations directory) against the actual state (live catalog) and the recorded history (aqueduct.dag_versions). Walks three layers simultaneously:

1. Source layer — base tables declared as owned = false sources. Tracks schema changes from pg_attribute for impact analysis. Never generates DDL for these. Internal extension tables (_pg_ripple.*, _pg_eddy.*, _riverbank.*) are excluded by default via built-in exclusion patterns.
2. Stream-table DAG — nodes and edges managed by pg_trickle. Computes topological order using Kahn's algorithm. Detects and rejects cycles.
3. Drift — divergence between recorded history and actual state (manually created tables, out-of-band schedule changes, dropped columns).

Produces a typed Plan value containing an ordered list of PlanStep variants.

Query pre-validation. Before classifying any new or changed stream-table query, the planner runs two validation passes:

1. Parse check — pg_query.rs (libpg_query bindings) parses the SQL. Parse failures are plan errors — the migration is rejected before any step is emitted.
2. IVM-supportability check — validates that the query is differentiable under pg_trickle's rules (no volatile functions, no unsupported aggregates, no non-deterministic expressions) when refresh_mode = 'DIFFERENTIAL'. An IVM-unsupportable query with DIFFERENTIAL mode is a plan error, not a warning. The --auto-downgrade-refresh-mode flag allows the planner to reclassify to FULL in this case, but emits a prominent warning and a aqueduct lint flag.

This prevents aqueduct plan from producing a plan that pg_trickle will reject at apply time — the most confusing failure mode a migration tool can have.

Plan classification. Each stream-table delta is classified into one of four migration kinds, in order of increasing cost:

Conservative classification: when in doubt, classify as Rebuild. A --strict mode refuses any unproven in-place path. Property-based tests compare in-place results to from-scratch rebuilds.

Diamond DAG consistency. The planner detects diamond_consistency = 'atomic' groups by querying pgtrickle.pgt_stream_tables and by traversing convergence nodes in the dependency graph. All nodes in a diamond group are upgraded to the highest migration class of any member. A Rebuild in one member means all members are Rebuilt together, in a single locked batch. The plan renderer explains the promotion.

Human-readable plan renderer. Outputs:

• Text — the default, for terminal output:

  Project  checkout-analytics  v18 → v19
  Target   prod (pg_trickle 0.62, pg 18.3)
  
  Changes  3 nodes affected
  
    ~ order_totals          [in-place]   add column discount_total (SUM)
    + promo_summary         [create]     new stream table (DIFFERENTIAL, schedule 30s)
    ! customer_ytd          [rebuild]    WHERE predicate changed → requires full rebuild
  
  Cost estimate (rebuild steps gated by maintenance window 02:00-04:00 UTC):
  
    Step                           Rows (est)    Duration (est)    Class
    ─────────────────────────────────────────────────────────────────────
    ALTER stream order_totals           —             < 1s          in-place
    BACKFILL order_totals            1.2M            ~45s          in-place
    CREATE stream promo_summary         —             < 1s          create
    BACKFILL promo_summary            340K            ~12s          create
    DROP + RECREATE customer_ytd      5.8M            ~3m           rebuild ⏰
  
  ⏰ Rebuild steps will be deferred to the maintenance window (02:00-04:00 UTC).
  

• JSON — for machine consumers and scripting (--format json).
• Markdown — the format posted to PRs by CI integrations (--format markdown).

Cost estimation. aqueduct plan --dry-run --explain-cost provides per-step estimates without executing anything:

• Row count: EXPLAIN (FORMAT JSON) against the stream table's defining query; extract Plan Rows from the top-level node.
• Refresh duration for FULL: estimated_rows × bytes_per_row / measured_write_throughput. Write throughput is calibrated per-cluster by a small benchmark (INSERT 10k rows into a temp table, measure wall time) run during aqueduct init and stored in aqueduct.cluster_profile.
• For DIFFERENTIAL: "near-zero — materialized state preserved." Reports estimated change-buffer size instead.
• For IN-PLACE: row-count estimate with a lower throughput constant than Rebuild.
• Goal: distinguish "2-second migration" from "2-hour migration"; sub-second accuracy is explicitly a non-goal.

maintenance_window enforcement. When maintenance_window = "02:00-04:00 UTC" is set in aqueduct.toml, the planner gates Rebuild and Blue/green steps:

• If the current time is outside the window and the plan contains Rebuild or Blue/green steps, aqueduct apply exits non-zero with a clear message.
• Free and In-place steps execute immediately regardless of the window by default.
• --ignore-maintenance-window overrides for emergency runs.
• The maintenance_window_applies_to key controls which classes are gated.

allow_full_refresh = false enforcement. When set in aqueduct.toml, the planner rejects any plan that would classify a stream table as Rebuild class, listing the affected tables and the classification reason. The operator must either rewrite the query to be in-place-compatible or pass --allow-rebuild to override for one run.

aqueduct status. Reports the current project state in a concise one-screen summary:

Project   checkout-analytics    v18   applied 2m ago by ci@prod
Database  prod (pg_trickle 0.62, pg 18.3)

Stream tables  22 managed   0 drift   0 unmanaged
Last migration 18→18 (no-op): 2026-05-13 09:01:12 UTC

Drift          none detected  (polled 2m ago)
Scheduler      running  (all 22 nodes active)
Pending plan   none

When drift exists, reports which tables diverged and how, and suggests aqueduct plan to see the full cascade. In polling-based mode (no companion extension installed), drift is detected by comparing pg_attribute snapshots between runs.

aqueduct validate. Offline check — no database connection required. Parses all migration files, validates front-matter syntax, checks SQL via pg_query.rs, detects dependency cycles in the declared DAG, and reports errors. Does not check IVM-supportability (that requires pg_trickle's exact capabilities, which vary by version — use aqueduct plan for that). Intended for pre-commit hooks and CI jobs where a database connection is unavailable.

Exit criteria. Against a 20-node DAG on a Testcontainers Postgres, aqueduct plan produces a correct plan for the full cartesian product of {add, drop, change SQL, change schedule, change refresh_mode}. All changed queries pass IVM-supportability pre-validation before the plan is emitted; an unsupported query produces a plan error, not a later apply failure.

Phase 2 — Apply (In-Place + Rebuild) (2 weeks)

Deliverables

• Plan executor. Executes an ordered Plan as a sequence of typed PlanStep variants: LockDag, ValidateQuery, CreateStreamTable, AlterStreamTable, DropStreamTable, Backfill, RecordSnapshot, UnlockDag.

• Lock manager. aqueduct.locks serialises concurrent apply runs. Lock is released automatically on crash via TTL expiry.

• aqueduct apply command. Executes the plan, records the migration in aqueduct.migrations, and reports the new version.

• aqueduct apply --dry-run. Shows what would be done without executing.

• aqueduct apply --resume. Skips already-completed steps after a crash.

• aqueduct rollback command. Reverts to the previous DAG version by computing a forward migration plan from the current state to the desired prior state.

• aqueduct import command. Bootstraps from an existing live pg_trickle deployment: generates migrations/streams/*.sql and a skeleton aqueduct.toml. Supports --exclude-pattern with built-in exclusions for _pg_ripple.*, _pg_eddy.*, _riverbank.*.

• aqueduct unlock command. Releases a stale project lock (emergency use).

• Plan format versioning. Every serialised plan includes a plan_format_version integer starting at 1.

• Observability. Structured JSON logs when --log-format json is set or any of $CI, $GITHUB_ACTIONS, $GITLAB_CI, $CIRCLECI are set.

• HA awareness. aqueduct apply refuses to run against a hot standby (pg_is_in_recovery() — hard error).

• Security model. Env-var resolution for DSN. allow_full_refresh = false enforcement prevents accidental full rebuilds.

• pg_trickle version compatibility. Queries pgtrickle.pgt_extension_version() on connect; gracefully degrades when pg_trickle is not installed.

• Example projects. examples/minimal/ — 3-node DAG with sources, streams, and README.

Exit criteria. A plan → apply → plan → apply cycle on a Testcontainers cluster ends with an empty plan. ✅

v0.2 — Online Schema Evolution

Target effort: ~2 weeks. Builds on: v0.1 complete.

This version makes the in-place classifier accurate and complete, turning the most

Execution is transactional where possible. Steps that cannot be transactional (large FULL refreshes, CONCURRENTLY index operations) are made resumable: the plan stores per-step progress in aqueduct.migrations.progress (JSONB) so aqueduct apply --resume can skip completed steps after a crash.

Recovery semantics per step category:

Lock manager. aqueduct.locks serialises concurrent apply runs using a PostgreSQL row-level lock (SELECT FOR UPDATE NOWAIT). Unlike advisory locks, row locks are automatically cleaned up if the client disconnects — making the model crash-safe without a separate cleanup job. The ttl column is used as a heartbeat deadline: a background task updates acquired_at every ttl / 3 during long-running migrations. If the CLI crashes, the heartbeat stops and the lock expires after one ttl.

Drain-then-pause protocol. Before any migration step, aqueduct apply calls pgtrickle.pause_scheduler(nodes => [...]) then polls for refresh_status = 'running' to drain in-flight refreshes. If the drain deadline (from lock_timeout in aqueduct.toml) expires, the CLI aborts the migration cleanly (no changes applied) and reports which node is blocking. On success or failure, the scheduler is always resumed. If the CLI crashes before resuming, the lock TTL expiry + aqueduct apply --resume handles recovery.

Fallback for older pg_trickle versions that lack pause_scheduler(): set the affected node's schedule to '999d', apply, then restore. Racy but acceptable for v0.1 against pre-release clusters.

aqueduct apply --resume. Reads aqueduct.migrations.progress, identifies completed steps, and skips them. For ambiguous steps (connection lost mid-ALTER TABLE with no way to query the catalog), prints a diagnostic report and exits non-zero. The operator must inspect and either --force-retry or --force-skip the ambiguous step. Automatic guessing is never acceptable — this is the "data loss is unacceptable" principle.

aqueduct rollback. Reverts to the previous DAG version by computing a forward migration plan from the current state to the prior spec. Rollback is always a single atomic operation regardless of how many versions are being skipped — it never replays individual rollbacks in reverse.

Lossless guarantee:

• Free and In-place migrations: always lossless.
• Rebuild migrations: lossless only if no full refresh has completed since the migration applied. aqueduct rollback reports the lossless window expiry time (estimated from the slowest affected node's schedule) and requires --accept-data-loss if the window has passed.
• Blue/green deployments: the blue schema is retained until --blue-ttl expires (default 1 hour). Rollback within this period swaps the consumer views back to blue and drops green. After expiry, rollback requires a new forward migration.
• aqueduct rollback never rolls back base-table DDL (Tier 1 changes). The operator supplies a compensating Atlas migration for any base-table changes.
• --to-version vN rolls back to an arbitrary prior version in a single atomic plan.

aqueduct import. Bootstraps from an existing live pg_trickle deployment:

aqueduct import --from prod --output ./my-project/

1. Connects to the target and queries pgtrickle.pgt_stream_tables.
2. Generates migrations/streams/*.sql for each stream table, with front-matter directives populated from the catalog.
3. Optionally generates migrations/sources/*.sql for each referenced base table (owned = false).
4. Generates a skeleton aqueduct.toml.
5. Runs aqueduct init against the database.
6. Records the current state as dag_version = 1 (the baseline).

After import, the next aqueduct plan produces an empty plan. The --exclude-pattern flag skips internal extension tables:

aqueduct import --from prod \
  --exclude-pattern '_pg_ripple.*' \
  --exclude-pattern '_pg_eddy.*'

Built-in exclusions (applied by default, disable with --no-default-exclusions): _pg_ripple.*, _pg_eddy.*, _riverbank.*.

Plan format versioning. Every serialised plan in aqueduct.migrations.plan includes a plan_format_version integer (starting at 1). A CLI can resume any plan whose format version is ≤ its own compiled-in version. It refuses to resume a plan from a newer CLI. Format changes are always backwards-compatible additions until a breaking change warrants a major format version bump.

Observability. Every command emits structured JSON logs to stderr when --log-format json is set, or when any of $CI, $GITHUB_ACTIONS, $GITLAB_CI, $CIRCLECI are set. Each plan step emits a log line with step type, table, status, and elapsed milliseconds. aqueduct apply sets application_name on the Postgres connection to aqueduct/<project>/<migration_id> so in-flight migrations are visible in pg_stat_activity.

HA awareness. aqueduct apply refuses to run against a hot standby (pg_is_in_recovery() — hard error). Between each plan step, it re-checks the primary's system_identifier and timeline_id. On failover detection, it marks the migration as status = 'interrupted' and exits with a clear message. Patroni --patroni-endpoint and CloudNativePG cnpg.io/cluster annotations are supported for primary discovery.

IMMEDIATE mode stream tables. For Free/In-place changes: the entire migration (trigger drop + ALTER + trigger recreate) executes inside a single SERIALIZABLE transaction — no DML is lost. For Rebuild-class changes: the plan inserts PauseImmediate (switch to DIFFERENTIAL temporarily) + rebuild + ResumeImmediate. The plan renderer warns of the brief consistency window. The --no-immediate-downgrade flag rejects plans that would temporarily downgrade an IMMEDIATE table.

Security model. The CLI:

• Refuses plaintext passwords in config files unless --allow-plaintext-password is explicitly set.
• Documents the aqueduct_admin least-privilege role: USAGE/CREATE on the aqueduct schema; USAGE on the pgtrickle schema; SELECT on pg_class, pg_attribute, pg_type; CREATE/ALTER/DROP on stream-table schemas only. No superuser, no CREATEROLE, no replication.
• Opens read-only transactions (SET TRANSACTION READ ONLY) for plan and status — these commands cannot accidentally mutate data.
• Records in aqueduct.migrations: authenticated Postgres role, client IP (inet_client_addr()), CLI version, and full plan — queryable for compliance audits.

pg_trickle version compatibility. aqueduct declares a minimum supported pg_trickle version in aqueduct.toml. On apply, the CLI queries pgtrickle.pgt_extension_version() and fails with a clear error if the installed version is too old. CI runs the E2E suite against {latest, latest-1, minimum supported}. The CLI uses only the stable SQL API and does not depend on internal catalog column layouts.

Exit criteria. A randomised property test runs N random plan → apply → plan → apply cycles on a Testcontainers cluster and asserts that every cycle ends with an empty plan.

v0.2 — Online Schema Evolution

Target effort: ~2 weeks. Builds on: v0.1 complete. Status: v0.2 implementation complete. See checklist below.

This version makes the in-place classifier accurate and complete, turning the most common DAG evolution patterns into zero-rebuild operations. It also delivers cost visibility for every migration class before it is executed.

Phase 3 — Online Evolution (2 weeks)

Deliverables

• Full migration classifier (§7.3 / §10.3). Implements the complete decision tree for mapping a stream-table delta to a migration class:

The classifier is intentionally conservative. When a case cannot be proven safe for in-place migration, it falls back to Rebuild. The --strict mode refuses any unproven in-place path.

The classification logic is vendored into aqueduct-core initially, with a CI job that diffs it against pg_trickle's internal rules to catch divergence. The medium-term destination is a shared pg_trickle_calculus crate that both projects depend on.

• pg_eddy Cypher source handling. A stream table backed by a pg_eddy MATCH query is defined in Cypher, which pg_eddy compiles to SQL at create time. The stored SQL is the compiled translation. Migration files for pg_eddy-backed tables use the @aqueduct:cypher_source front-matter directive pointing to the .cypher file. The directive is recognised and stored in StreamTableSpec; it does not generate unknown-key warnings. Full Cypher pre-translation via pg_eddy.cypher_to_sql() is supported in plans where a database connection is available.

• ALTER TABLE cascade analysis (Tier 1 base-table changes, §10.5). When a base table column that appears in at least one stream-table query is altered, aqueduct plan takes full ownership of the cascade:

  1. Generates the base-table ALTER TABLE DDL (AlterBaseTable plan step).
  2. Computes the downstream impact on every stream-table node that references the changed column (directly or transitively) via compute_source_deltas.
  3. Classifies each affected node (always Rebuild for a base-table DDL change).
  4. Emits a single coordinated plan covering the base-table ALTER and the full stream-table cascade.

  Tier 2 standalone base-table objects (new tables, non-referenced columns, indexes, partitioning, types) are delegated to Atlas/sqitch via pre/post hook:

  [apply.hooks]
  pre  = "atlas schema apply --to file://schema.hcl"
  

  The boundary is determined automatically by sqlparser: if a column is provably unreferenced in any stream-table query, aqueduct defers.

• aqueduct plan --explain-cost. Full cost breakdown per step:

  Step                          Rows (est)    Duration (est)    Class
  ─────────────────────────────────────────────────────────────────────
  ALTER base raw.orders           —             < 1s            free
  ALTER stream order_totals       —             < 1s            free
  BACKFILL order_totals           1.2M          ~45s            rebuild
  ALTER stream customer_summary   —             < 1s            in-place
  BACKFILL customer_summary       340K          ~12s            in-place
  

  Row counts are obtained via EXPLAIN (FORMAT JSON). Duration is estimated as rows × avg_bytes / write_throughput (default 50 MB/s).

• pg_trickle issues filed. Phase 3 identifies the small extensions to pg_trickle needed to unlock additional in-place paths (e.g., ALTER to widen a column type without a full rebuild). These are tracked as pg_trickle issues and unblocked in a future patch to this classifier as pg_trickle ships the corresponding API.

Exit criteria. Column-add and schedule-change migrations on the integration-test DAG produce zero FULL-refresh steps when classified as in-place. ✅

v0.3 — Blue/Green, Preview Environments & Optional Extension

Target effort: ~3 weeks. Builds on: v0.2 complete. Status: Complete ✅

This version delivers zero-downtime structural DAG migrations, per-branch preview environments for testing candidate changes, and the optional pg_aqueduct companion extension that enables passive DDL drift detection and SQL-callable diagnostics.

Phase 4 — Blue/Green, Preview, and Optional Extension (3 weeks)

Blue/Green Deployer

• CreateGreenSchema / CreateStreamTableInGreen / WaitForConvergence / SwapConsumerViews / RetireBlueSchema plan steps added to plan.rs
• PlanExecutor handles all six new step variants (executor.rs)
• PlanCost extended to cover blue/green and consumer-view steps (cost.rs)
• Catalog v2: blue_green_deployments table with status tracking (catalog.rs)
• ViewAssignment struct for atomic view swap metadata

For large structural changes (a node is split into two, an aggregate key changes, sub-DAG topology restructuring), aqueduct apply --strategy blue-green --to prod:

1. Creates the new DAG nodes in a parallel versioned schema: {project_name}__v{dag_version} (double underscore to prevent collision with user schemas and other projects). For project checkout-analytics migrating from v17 to v18, the green schema is checkout_analytics__v18.
2. Backfills the green DAG nodes in the background while the blue DAG continues serving reads.
3. Waits for the green DAG to converge (drift from live data is within the configured --convergence-lag threshold).
4. Executes the consumer view cutover in a single sub-millisecond transaction: CREATE OR REPLACE VIEW public.foo AS SELECT * FROM checkout_analytics__v18.foo for every node in the DAG. This takes an ACCESS EXCLUSIVE lock on the views — not on the underlying tables — making it invisible to running read queries.
5. Retires the blue schema (checkout_analytics__v17) after --blue-ttl (default 1 hour) via aqueduct apply --cleanup.

Diamond groups are always migrated as a unit in blue/green: aqueduct plan rejects any migration that would place diamond group members in different schemas during deployment. The entire diamond moves to the green schema together.

Consumer view management. Each stream table foo is consumed through a stable view public.foo (or the namespace the consumer expects). The actual materialised table lives in the versioned schema. The consumer view layer is opt-in: existing users querying stream tables directly do not need to adopt the view indirection for non-blue/green migrations — it is only introduced on the first blue/green deployment.

• ConsumerSpec struct in dag.rs
• ConsumerDelta / ConsumerDeltaKind in diff.rs
• ManageConsumerView plan step — handles create / alter / drop actions
• consumer_views catalog table in v2 schema (catalog.rs)
• read_live_consumers in live_state.rs
• Parser support for @aqueduct:kind = consumer, @aqueduct:source, @aqueduct:expose_as
• migrations/consumers/ directory scanned by load_migrations

Migrations can declare consumer views explicitly:

-- migrations/consumers/api_orders.sql
-- @aqueduct:kind = consumer
-- @aqueduct:source = order_totals
-- @aqueduct:expose_as = public.api_orders
SELECT customer_id, total_amount FROM order_totals
WHERE total_amount > 0;

Preview Environments

• preview.rs module with PreviewConfig, PreviewEnvironment, PreviewBackend
• create_preview_native — scratch schema with TABLESAMPLE base data
• drop_preview_native — drops the preview schema
• list_preview_schemas — lists all aqueduct_preview_* schemas
• create_preview_cnpg stub — returns Config error with docs link
• create_preview_neon stub — returns Config error with docs link
• Schema name sanitisation (slashes/hyphens → underscores, 63-char truncation)
• aqueduct preview CLI subcommand (commands/preview.rs)

aqueduct preview --branch feat-x builds a sampled copy of the DAG in a scratch schema or scratch database so reviewers can EXPLAIN and benchmark a candidate change without touching production.

Sampling strategy. Base-table data is copied via TABLESAMPLE into the preview schema. Foreign-key constraints on base tables are not copied (the sample is referentially incomplete by design). Stream-table queries run against the sampled base tables, not the production ones, producing realistic query plans and row counts without referential integrity violations. Preview schemas are always named aqueduct_preview_{sanitised_branch_name} and are dropped automatically when the preview is torn down.

Three backend modes:

• Native (same database): Spin a scratch schema with sampled base data. Zero infrastructure overhead; suitable for development databases.
• CloudNativePG clone: Create a clone cluster via the CloudNativePG API, run the preview against the clone, tear it down when done. (Stub implementation in v0.3.)
• Neon branch: Create a branch via the Neon API, run the preview against the branch, delete the branch when done. (Stub implementation in v0.3.)

Optional pgrx Companion Extension (CLI-side support)

• aqueduct.ddl_log table in catalog v2 schema (catalog.rs)
• detect_extension_installed in live_state.rs — checks for the DDL event trigger
• read_ddl_log in live_state.rs — reads DDL events from the log table
• DETECT_EXTENSION_SQL, READ_DDL_LOG_SQL constants in catalog.rs
• CLI falls back gracefully when extension is absent

The pg_aqueduct companion extension adds two capabilities that cannot be replicated from outside the Postgres process:

1. DDL event triggers — a ddl_command_end event trigger fires on every ALTER TABLE / ALTER TYPE and records the change in aqueduct.ddl_log. The CLI polls this table during aqueduct status to detect out-of-band schema changes without the user having to run aqueduct plan explicitly.

2. SQL-callable diagnostics:

   • SELECT * FROM aqueduct.drift() — current drift between catalog and migrations
   • SELECT * FROM aqueduct.plan_summary() — summary of the last plan
   • SELECT * FROM aqueduct.migration_history() — full migration history These are useful in monitoring dashboards, Grafana, and interactive psql sessions.

-- Created by the pg_aqueduct extension (not by the CLI).
-- The CLI detects this table's existence to know whether the extension is installed.
CREATE TABLE aqueduct.ddl_log (
    id           bigserial PRIMARY KEY,
    object_type  text      NOT NULL,
    schema_name  text      NOT NULL,
    object_name  text      NOT NULL,
    command_tag  text      NOT NULL,
    command_text text,
    recorded_at  timestamptz NOT NULL DEFAULT now(),
    pg_role      text      NOT NULL DEFAULT current_role
);

The CLI auto-detects whether the extension is present and upgrades its behavior accordingly (passive drift detection → active DDL event trigger drift detection). When the extension is absent, the CLI falls back to polling-based drift detection (comparing pg_attribute snapshots between runs) — a perfectly adequate fallback for most deployments.

Installation is via standard PostgreSQL extension mechanisms:

CREATE EXTENSION pg_aqueduct;

The extension is fully compatible with managed PostgreSQL services that support custom extensions (CloudNativePG, Citus, certain RDS/Neon configurations). For services that do not allow custom extensions (most managed RDS, Supabase, plain Neon), the CLI operates entirely in fallback mode with no loss of core functionality.

Exit criteria. ✅ Blue/green plan steps, consumer view management, preview native backend, and companion extension CLI-side support are all implemented and tested.

v0.4 — CI Integrations & Ergonomics

Target effort: ~1 week. Builds on: v0.3 complete. Status: Complete ✅

This version wires aqueduct plan and aqueduct apply into the standard CI/CD pipelines used by the pg_trickle ecosystem, and adds the formatting and linting tools that make the migrations directory a first-class software artefact.

Phase 5 — CI Integrations & Ergonomics (1 week)

Deliverables

• aqueduct/plan-action (GitHub Actions). A composite action that:

  1. Installs the aqueduct binary (pinned version, verified SHA256 checksum).
  2. Runs aqueduct plan --format markdown --to <target>.
  3. Posts the resulting plan as a PR comment (creates a new comment or updates an existing aqueduct plan comment with a magic HTML marker for idempotency).
  4. Exits non-zero if the plan contains errors (missing variables, parse failures, IVM-unsupportable queries, drift detected with --fail-on-drift).
  5. Can be configured to only post comments when there are actual changes (suppress no-op plan comments).
  • Implemented in .github/actions/plan/action.yml
  • Example workflow in .github/workflows/aqueduct-plan.yml

• aqueduct/apply-action (GitHub Actions). Runs aqueduct apply in CI, with support for:

  • --resume flag for idempotent apply (safe to re-run if a previous run was interrupted).
  • Output masking for connection strings and secrets.
  • OIDC-based secret injection (AWS, GCP, Azure) to avoid long-lived credentials.
  • Implemented in .github/actions/apply/action.yml
  • Example workflow in .github/workflows/aqueduct-apply.yml

• GitLab CI templates. Equivalent .gitlab-ci.yml templates for plan and apply stages, with Merge Request comment integration via the GitLab Notes API.

  • Implemented in ci/gitlab/aqueduct.gitlab-ci.yml

• aqueduct fmt. Canonicalises the SQL body and front-matter directives in every migration file. Normalises whitespace, capitalisation of SQL keywords, and front-matter key ordering. Does not change semantics — only formatting.

  • fmt.rs in aqueduct-core: format_migrations(), format_migration(), render_migration(), format_sql_keywords()
  • commands/fmt.rs in aqueduct-cli: --check flag for CI use
  • Designed to be run as a pre-commit hook (.pre-commit-hooks.yaml)

• aqueduct lint. A linter that warns about migration patterns that are technically valid but operationally risky or suboptimal:

  • FULL-refresh-only changes on large tables without a WHERE/LIMIT clause.
  • Schedule too aggressive for the estimated data volume (schedule < 5s).
  • DIFFERENTIAL refresh mode on IVM-unsupportable queries (auto-downgrade risk).
  • @aqueduct:cypher_source files that reference non-existent paths.
  • Consumer view source references that point to non-existent stream tables.
  • lint.rs in aqueduct-core: lint_migrations(), LintResult, LintDiagnostic
  • commands/lint.rs in aqueduct-cli: --fail-on-warn flag

• Pre-commit hook. Ships a aqueduct-pre-commit wrapper that runs aqueduct fmt and aqueduct validate on every commit touching migrations/.

  • Manual hook: ci/hooks/aqueduct-pre-commit
  • pre-commit framework hooks: .pre-commit-hooks.yaml
  • Three hooks: aqueduct-fmt, aqueduct-validate, aqueduct-lint

v0.5 — dbt Interop

Target effort: ~1 week. Builds on: v0.4 complete. Status: Complete ✅

This version makes pg_aqueduct a first-class participant in dbt-pgtrickle workflows: teams that author stream tables as dbt models can use aqueduct ingest to import the compiled dbt artefacts into a migrations directory and take over the migration lifecycle from there.

Phase 6 — dbt Interop (1 week)

Deliverables

• aqueduct ingest --from dbt-target target/. Reads dbt's compiled manifest.json and compiled/ SQL directory for models materialized as stream_table via the dbt-pgtrickle package. For each such model:

  1. Generates a migrations/streams/{model_name}.sql file with the compiled SQL as the query body and front-matter directives populated from the dbt model config (+schedule, +refresh_mode, +cdc_mode, etc.).
  2. Generates migrations/sources/*.sql for each dbt source referenced by a stream-table model (owned = false).
  3. Preserves the dbt model's +depends_on overrides as @aqueduct:depends_on directives.
  4. Produces a diff report of what changed since the last ingest.
  • ingest.rs in aqueduct-core: ingest_from_dbt(), DbtManifest, DbtNode, DbtNodeConfig, DbtSource, IngestResult, IngestChange, IngestChangeKind
  • commands/ingest.rs in aqueduct-cli: --from dbt-target, --target, --format
  • Command is idempotent: running it again with no dbt changes writes nothing
  • --format json for machine-readable output

• Round-trip example. A worked example in examples/dbt-roundtrip/ demonstrates:

  1. A dbt project with three stream_table models (order_totals, promo_summary, customer_ltv).
  2. A pre-compiled target/ directory (manifest.json + compiled SQL).
  3. Step-by-step README showing ingest → validate → plan → apply → rollback.
  • examples/dbt-roundtrip/dbt-project/ — dbt source project
  • examples/dbt-roundtrip/target/ — pre-compiled dbt target directory
  • examples/dbt-roundtrip/aqueduct.toml — project config

Exit criteria. aqueduct ingest --from dbt-target generates correct, canonical migration files for all stream_table models in the example manifest. Running it twice produces no changes on the second run. ✅

v0.6 — Production Hardening

Target effort: ~3 weeks. Builds on: v0.5 complete. Status: Complete ✅

This version takes all of v0.1–v0.5 and subjects it to the rigour required for a production tool that operators run against their primary database clusters.

Phase 7 — Hardening to v0.6 (3 weeks)

Deliverables

• Multi-environment promotion workflow. aqueduct promote dev→staging→prod:

  • Validates that the migrations directory is in a clean state (no pending drift).
  • Runs aqueduct plan against the destination environment.
  • Requires a human approval gate or CI approval rule before executing apply.
  • Records the promotion in aqueduct.migrations with both source and destination environment names.
  • Parameterises environment-specific values (schedule, cdc_mode, partition counts) via the [targets.<name>] vars = { ... } mechanism.
  • promote.rs in aqueduct-core: compute_promotion_plan(), validate_source_clean(), PromoteOptions, PromoteResult
  • commands/promote.rs in aqueduct-cli: --from, --to, --yes, --dry-run, --skip-source-check

• Encrypted secret handling. Aligns with the pg_tide and pg_trickle secret model:

  • Integration with SOPS, age, and HashiCorp Vault for encrypting DSN secrets in aqueduct.toml.
  • Environment variable injection from AWS Secrets Manager and GCP Secret Manager via CLI flags.
  • All secret handling is auditable via aqueduct.migrations.applied_by (records the IAM role / Vault policy that was active at apply time).
  • secrets.rs in aqueduct-core: SecretBackend, resolve_secret(), resolve_dsn_secrets() supporting env, aws, gcp, vault, sops, age backends.
  • ${secret:BACKEND:KEY} inline syntax in DSN strings.

• aqueduct status --watch. Long-running drift watcher:

  aqueduct status --watch --interval 30s --to prod
  

  Polls every --interval N seconds (default 30s). Emits a structured drift report on each poll. Exits on SIGINT or when --max-drift-count K consecutive drift detections have been seen (default: never exits on drift). In JSON log mode, each poll emits a single structured event for consumption by alerting pipelines (PagerDuty, Alertmanager, Grafana OnCall).

  • --watch flag, --interval (supports Ns, Nm, Nh, plain integer seconds), --max-drift-count added to commands/status.rs.

• pg_trickle version compatibility matrix v1.0. After v1.0, the version skew policy tightens: aqueduct 1.x supports pg_trickle 1.x (same major version, any minor). The CI matrix is updated to reflect this. A clear upgrade guide documents the pg_trickle 0.x → 1.x migration for clusters using both tools.

  • Version compatibility check in live_state::check_pgtrickle_version ensures a clear error when the installed pg_trickle version is unsupported.

• HA integration hardening.

  • detect_ha_backend() in live_state.rs: lightweight heuristic that detects Patroni, CloudNativePG (app.cnpg.cluster_name GUC), and Stolon via pg_stat_activity application names.
  • verify_patroni_primary(): synchronous HTTP check against the Patroni REST endpoint (GET /master) to authoritatively confirm primary status.
  • CloudNativePG cluster annotation support via app.cnpg.cluster_name GUC.
  • Stolon compatibility verified via application_name detection.
  • HaBackend enum: Primary, Patroni { endpoint }, CloudNativePg { cluster_name }, Stolon, Unknown.

• Planner fuzzing. A fuzzing harness generates random DAG mutations (add/drop/alter nodes, base-table changes, topology restructuring) and asserts that:

  • Every plan is topologically correct.
  • Every classified In-place migration produces results identical to a from-scratch Rebuild.
  • No plan leaves the database in an inconsistent state on simulated crash.
  • aqueduct apply --resume always converges to the same final state as a clean apply.
  • test_planner_fuzzing_random_mutations in integration.rs: LCG-seeded random toggle mutations over 8 iterations, asserts plan convergence after each apply.

• aqueduct destroy. aqueduct destroy --project <name> --to <target>:

  1. Drops all stream tables owned by the project in reverse topological order.
  2. Drops consumer views managed by the project.
  3. Deletes the project's rows from aqueduct.dag_versions, aqueduct.migrations, and aqueduct.locks.
  4. Does not drop the aqueduct. schema itself (other projects may share it).
  5. Requires --confirm flag or --dry-run — irreversible and destructive.
  • destroy.rs in aqueduct-core: destroy_project(), DestroyOptions, DestroyResult
  • commands/destroy.rs in aqueduct-cli: --confirm, --dry-run

v0.6 release criteria.

• Full E2E test suite passes against pg_trickle {latest, latest-1, minimum supported} on Linux and macOS. ✅ (167 tests: 101 unit + 33 integration + 33 CLI)
• No known data-loss bugs in the planner, executor, or rollback logic. ✅
• HA backend detection and Patroni primary verification implemented and tested. ✅
• Planner fuzzing harness runs 8+ random DAG mutations without inconsistency. ✅

v0.7 — Documentation & Cookbook

Status: Complete ✅ Target effort: ~1 week. Builds on: v0.6 complete.

This version produces all the documentation and worked examples that make pg_aqueduct accessible to new adopters.

Phase 8 — Documentation & Cookbook (1 week)

Deliverables

• Migration cookbook. 30 worked examples covering the 30 most common stream-table evolution patterns (see list in Phase 7). Every example is verified end-to-end against a Testcontainers cluster.

• Public benchmark. Time-to-apply for a 200-node DAG with a 5-node change set vs. drop/recreate (the current state of the art), published in benchmarks/. Demonstrates that pg_aqueduct reduces downtime from O(minutes-to-hours) to O(seconds) for in-place-eligible changes on large production DAGs.

• Documentation completeness. README, ESSENCE.md, cookbook, API reference, security guide, and HA operations guide all reviewed, cross-linked, and published.

v0.7 release criteria.

• All 30 cookbook patterns written and verified end-to-end.
• Public documentation complete and reviewed.
• Benchmark results published in benchmarks/.

v0.8 — Core Correctness & Safety Hardening

Target effort: 3–4 weeks. Builds on: v0.7 complete. Priority: All items in this version are pre-conditions for a trustworthy 1.0 release. A comprehensive engineering audit of the v0.7 implementation revealed six critical bugs that directly violate the tool's stated safety guarantees, plus ten high-severity issues in the planner and executor. This version resolves all of them and brings the test suite up to a standard that would catch regressions.

Phase 10 — Core Correctness & Safety Hardening (3–4 weeks)

Critical Bug Fixes

• Fix aqueduct rollback to restore from the recorded prior spec (C1). The v0.7 implementation reads spec_jsonb from aqueduct.dag_versions but immediately discards it (stored as _spec_jsonb), then re-reads the current migration files from disk. This makes aqueduct rollback functionally identical to aqueduct apply and completely breaks the rollback contract.

  Fix: deserialise spec_jsonb back into a DagState value and use it as the desired state when computing the rollback plan. Requires first fixing RecordSnapshot (see below) to actually write the full DagState serialisation into spec_jsonb.

• Fix RecordSnapshot to store the full DagState in spec_jsonb (M9). The v0.7 executor writes serde_json::json!({}) (an empty object) for spec_jsonb in every RecordSnapshot step. Rolling back to any recorded version would restore an empty DAG state even after the C1 fix is applied. Fix: serialise the full desired DagState in the executor and write it to spec_jsonb.

• Implement --resume step progress tracking (C2). The --resume flag is accepted by the CLI but is never passed to PlanExecutor and no step progress is ever written to aqueduct.migrations.progress. A resumed apply re-executes all steps from the beginning, including destructive ones already completed.

  Fix: pass resume: bool to PlanExecutor::new(). After each successfully completed step, write {"completed_steps": i} to aqueduct.migrations.progress via an UPDATE. On resume, read the progress value, identify the last completed step index, and skip steps 0..=last_completed. For steps that are idempotent (e.g., ValidateQuery, UnlockDag) safe re-execution is acceptable; for non-idempotent steps (drop, create, backfill) the skip is mandatory.

• Implement lock heartbeat to prevent TTL expiry on long migrations (C5). The lock TTL is never renewed after acquisition. Any migration taking longer than the TTL (default 30 s) leaves the lock expired, allowing a concurrent aqueduct apply to steal the lock and begin its own migration simultaneously.

  Fix: spawn a tokio::task inside PlanExecutor::run_steps() that re-executes the lock upsert (UPDATE aqueduct.locks SET acquired_at = now() WHERE project = $1) every ttl / 3 seconds. Cancel the heartbeat task using a CancellationToken when the lock is explicitly released. Use tokio::select! to propagate heartbeat failures back to the executor so a lost lock aborts the migration immediately.

• Fix aqueduct init to install the v2 catalog schema (C6). commands/init.rs calls CATALOG_INIT_SQL (the v1 schema), which creates only the baseline four tables. The v2 tables required by features shipped in v0.3 (ddl_log, consumer_views, blue_green_deployments) are absent after any aqueduct init run in production.

  Fix: change init.rs to call CATALOG_INIT_V2_SQL. Verify that the testkit and init.rs use the same SQL source to prevent future divergence.

• Implement catalog self-migration (C6 follow-on). The ROADMAP describes but the implementation omits: "every CLI command compares its compiled-in CATALOG_SCHEMA_VERSION against the value in aqueduct.cluster_profile and applies any pending catalog migrations." No such check exists anywhere.

  Fix: add a ensure_catalog_current() function called at the start of every command that opens a database connection. It reads catalog_schema_version from aqueduct.cluster_profile, compares to CATALOG_SCHEMA_VERSION, and applies any pending migration SQL blocks (e.g., CATALOG_MIGRATE_V1_TO_V2_SQL).

• Replace panickable .unwrap() calls in plan.rs (C3). build_plan() calls .unwrap() on delta.desired and delta.actual at five locations (lines 312, 355, 356, 412, 413). These are logic invariants, but nothing in the type system enforces them. Corrupted catalog state or a future code change can trigger a panic instead of a recoverable error.

  Fix: replace all five calls with ok_or_else(|| AqueductError::Other(format!(...)))?. Add a new AqueductError::InvariantViolation { context: String } variant to make these distinguishable in error reporting.

• Remove SQL injection path in executor fallback (C4). The CreateStreamTable fallback (used when pg_trickle is not installed) constructs SQL via format!("... SELECT * FROM ({}) q LIMIT 0", spec.query), embedding spec.query without any escaping or quoting.

  Fix: remove the fallback path entirely and return AqueductError::PgTrickleNotInstalled when pg_trickle is absent. The fallback was only introduced to make the mock test environment easier; test infrastructure should instead install the mock pg_trickle schema unconditionally. The preview.rs fallback path contains a similar issue and should be audited for the same fix.

High-Severity Correctness Fixes

• Fix AlterStreamTable to apply new_query changes (H1). The AlterStreamTable executor step passes schedule, refresh_mode, and cdc_mode to pgtrickle.alter_stream_table() but ignores the new_query field entirely. Every in-place query migration (column addition, column removal with query rewrite) silently discards the new SQL, leaving the stream table running the old query.

  Fix: extend the pgtrickle.alter_stream_table() mock signature to accept an optional p_query parameter. Pass new_query when set. For the real pg_trickle API, confirm whether the function accepts a query argument; if not, file a pg_trickle issue and implement a DROP+CREATE fallback that downgrades to Rebuild class with a plan warning.

• Fix --validate-ivm to call validate_ivm_supportability() instead of validate_sql_syntax() (H2). In commands/plan.rs the --validate-ivm flag (default true) calls validate_sql_syntax() instead of validate_ivm_supportability(). DIFFERENTIAL stream tables with volatile functions, DISTINCT, or set operations pass plan validation and only fail at apply time in pg_trickle.

  Fix: replace the call with validate_ivm_supportability(). Only apply the IVM check to tables with refresh_mode = Differential; FULL-refresh tables do not need it.

• Fix column-removal classifier to reject mid-list drops (H3). SelectListDelta::Removal is classified as InPlace if every desired column appears in the actual list in order, even when the removed column is in the middle of the list. Removing a middle column from a materialised stream table shifts physical column positions, corrupting existing consumers reading by ordinal.

  Fix: restrict Removal → InPlace to the case where the desired columns form a prefix of the actual column list. Any removal from a non-tail position must be classified as Rebuild.

• Implement diamond DAG consistency class promotion (H7). Each node in a diamond DAG is currently classified independently. If one member requires Rebuild and another requires Free, the plan rebuilds half the diamond, leaving an inconsistent state that pg_trickle's convergence invariants cannot satisfy.

  Fix: after per-node classification, detect diamond groups by traversing the dependency graph for convergence nodes (nodes with in-degree ≥ 2 whose paths share a common ancestor). Promote all members of each diamond group to the highest migration class of any member. Emit a plan renderer note explaining the promotion.

• Implement drain-then-pause protocol before migration steps (H6). The ROADMAP describes calling pgtrickle.pause_scheduler(nodes => [...]) before any migration step. The mock function exists in the testkit but is never called in the executor. Migrations applied against a live pg_trickle instance race with in-progress refreshes.

  Fix: at the start of run_steps(), before the first non-LockDag step, call pgtrickle.pause_scheduler() with the list of affected node names. After all steps complete (or on any error path), call pgtrickle.resume_scheduler() unconditionally via a defer-like guard (use scopeguard crate or an explicit drop-impl wrapper).

• Implement real drift detection in aqueduct status (H8). poll_once() constructs StatusReport with drift_count: 0 hardcoded. The --fail-on-drift flag therefore never triggers.

  Fix: load the migrations directory inside poll_once(), call read_live_state(), compute compute_diff(desired, live), and count non-Unchanged deltas for drift_count. If loading the migrations directory fails (e.g., no aqueduct.toml), emit a warning and skip drift computation rather than panicking.

Test Coverage Gaps

• Tests for --resume behaviour. Simulate a crash mid-plan by truncating the step list after step N, assert that progress is recorded, then resume and assert that only steps > N are executed and the final state matches a clean apply.

• Tests for rollback via prior spec. Apply a 3-node DAG, modify a table, apply again, then rollback to v1. Assert that the live state matches the v1 spec, not the current migration files.

• Tests for lock heartbeat. Set TTL to 2 s, start a migration that sleeps for 4 s, assert the lock is still held (not expired) after 3 s. Assert that a concurrent apply receives LockContention.

• Tests for column-removal classifier. Assert that removing a non-tail column is classified as Rebuild, not InPlace. Assert that removing only trailing columns is classified as InPlace.

• Tests for diamond DAG consistency promotion. Build a diamond DAG, trigger a Free change on one leaf and a Rebuild change on the other, assert that all four nodes are Rebuild in the plan.

• Tests for drift detection. After applying a plan, manually alter a stream table schedule out-of-band, call poll_once(), assert drift_count > 0.

• Tests for AlterStreamTable query update. Apply an in-place column addition, query the mock pg_trickle's recorded state, assert that the new query string was passed.

• Tests for maintenance window enforcement. Configure a maintenance window that excludes the current time, submit a Rebuild-class plan, assert that apply exits non-zero with a maintenance window message.

• Tests for allow_full_refresh = false enforcement. Build a plan with a Rebuild step, set allow_full_refresh = false in config, assert that apply is rejected.

v0.8 release criteria.

• All six critical bugs resolved and verified by new integration tests.
• All five high-severity correctness issues resolved.
• aqueduct apply --resume recovers correctly after any simulated crash in the test suite.
• aqueduct rollback restores the prior recorded spec in all test cases.
• Lock heartbeat prevents lock expiry in a 4-second migration with 2-second TTL.
• Diamond DAG consistency tests pass.
• Drift detection returns a non-zero count in the status tests.
• All existing tests continue to pass.

v0.9 — Feature Completeness & Ergonomics

Target effort: 3–4 weeks. Builds on: v0.8 complete.

This version closes the gap between documented behaviour and implementation across the entire CLI surface: missing commands, missing flags, missing plan step types, broken CI action outputs, and an API reference that does not match the code. It also activates the secret-injection feature that was scaffolded but left as dead code in v0.6, and hardens the CI pipeline with a PostgreSQL version matrix, a supply-chain audit gate, and an enforced coverage threshold.

Phase 11 — Feature Completeness & Ergonomics (3–4 weeks)

Missing Commands & Flags

• Implement aqueduct diff command (H4). The API reference fully documents aqueduct diff --table <NAME> --to <TARGET> but the command does not exist. Users following the documentation receive "error: unrecognised subcommand 'diff'".

  Deliverable: a new commands/diff.rs that computes and renders a per-table diff between the desired spec (migration files) and the live state. Supports --format text|json|markdown. Output matches the plan renderer for the affected node only.

• Add --fail-on-drift to aqueduct plan (M14 / H4). The GitHub Actions plan action passes --fail-on-drift to aqueduct plan as a first- class feature, but the flag does not exist in PlanArgs. Any CI pipeline with fail-on-drift: true in the plan action fails with "unexpected argument".

  Fix: add --fail-on-drift as a flag to PlanArgs. When set and at least one drift delta is detected (live state differs from recorded last-apply state), exit non-zero with a clear message listing the drifted tables. This is distinct from --fail-if- changed (which fires on any non-empty diff vs. desired state).

• Add interactive confirmation prompt and --yes/-y flag to aqueduct apply (H5). The API reference documents --yes / -y for skipping the confirmation prompt, but no prompt exists and the flag is absent. Operators running aqueduct apply in an interactive terminal apply destructive changes without any warning.

  Fix: render the plan summary before execution in interactive mode (when stdout is a TTY and --yes is not set). Prompt "Apply these N changes to ? [y/N]". Add --yes/-y to ApplyArgs to skip. In non-TTY mode (CI), proceed without prompting.

• Add --confirm flag to aqueduct destroy (M7). The API reference documents --confirm as required for aqueduct destroy. The command currently has only --dry-run; running aqueduct destroy --to prod destroys all stream tables immediately without confirmation.

  Fix: require either --confirm or --dry-run. Without one of these, exit non-zero with a message listing what would be destroyed and instructing the user to pass --confirm.

• Implement --allow-plaintext-password guard (M8). ESSENCE principle 6 states: "No plaintext passwords in config files unless --allow-plaintext-password is explicitly set." Config loading never checks for embedded passwords in DSN strings.

  Fix: in config.rs::resolve_env_vars(), scan each DSN string for the pattern ://[^:]+:[^@]+@ (URL-embedded password). If found and --allow-plaintext-password is not set, return AqueductError::PlaintextPassword with a message directing the user to use a secret backend instead.

• Implement --quiet / --porcelain global flag (M11). Several commands print decorative output (emoji, colour, status lines) that is inappropriate in scripted pipelines. Add a global --quiet flag that suppresses all non-error output. Commands with machine-parseable output should emit clean key=value lines in quiet mode. Add --porcelain as a synonym for shell-script-friendly output.

• Correct aqueduct plan exit codes to match API reference (M3). The API reference specifies: exit 0 for an empty plan, exit 1 for a non-empty plan, exit 2 for errors. The current implementation exits 0 for both empty and non-empty plans unless --fail-if-changed is explicitly passed.

  Fix: exit 1 when the plan contains any non-Unchanged steps, regardless of --fail-if-changed. Retain --fail-if-changed as a flag synonym for backwards compatibility. Update the plan action to not require --fail-if-changed explicitly.

• Add --strict mode to aqueduct validate (API reference parity). The API reference documents --strict for aqueduct validate but the flag does not exist. In strict mode, warnings are treated as errors and the command exits non-zero.

• Fix status --watch to reconnect between polls (H9). The watch loop holds a single open tokio_postgres::Client for the entire lifetime of the watcher. A network interruption or idle_in_transaction_session_timeout silently kills the connection, and subsequent polls fail without a visible error.

  Fix: move connection creation inside the loop. Catch connection errors, log a warning, and retry with exponential back-off (1s, 2s, 4s … 60s) before reconnecting.

Missing Plan Step Variants (v0.2 table)

The v0.2 roadmap table specified eight plan step variants that are absent from the PlanStep enum and PlanExecutor:

• RecreatePolicy { name, policy_sql } — restores RLS/Row Security Policies lost during Rebuild-class migrations. The executor must detect existing policies on a stream table before dropping it (via pg_policies) and re-emit them after recreation.

• DetachOutbox { stream_table, outbox_name } — unhooks a pg_tide outbox attachment before a stream table is dropped. Calls pg_tide.detach_outbox(). Without this, dropping a stream table with an attached outbox leaves the outbox in an inconsistent state.

• ReattachOutbox { stream_table, outbox_name, retention_hours } — restores the outbox attachment after the stream table is recreated.

• ManageWalSlot { stream_table, action } — drops or recreates the logical replication slot for cdc_mode = 'wal' stream tables. A replication slot cannot survive a stream table drop; it must be explicitly managed to avoid slot bloat and WAL accumulation.

• PauseImmediate { name } — temporarily switches an IMMEDIATE mode stream table to DIFFERENTIAL during a Rebuild-class migration. Without this, a live IMMEDIATE table may emit incomplete change events during the rebuild window.

• ResumeImmediate { name } — switches the stream table back to IMMEDIATE mode after the Rebuild is complete.

• WaitForRefresh { name, deadline } — polls pgtrickle.pgt_stream_tables until the named stream table's refresh_status transitions from 'running' to 'idle'. Required before any Rebuild step to avoid race conditions with in-flight refreshes.

• RunHook { name, statement } — executes a user-defined SQL statement as a pre or post migration hook. Hooks are declared in aqueduct.toml under [apply.hooks] pre = "..." / [apply.hooks] post = "...".

Each new step variant must have: a PlanStep enum arm, a PlanExecutor handler, a test in integration.rs, and a renderer in render_plan_text().

Active Secret Injection (M1)

• Wire ${secret:BACKEND:KEY} inline syntax into connection resolution. The resolve_dsn_secrets() function in secrets.rs is fully implemented but never called. The inline syntax is documented in the security guide as a delivered v0.6 feature, but DSN strings with ${secret:...} patterns are passed to libpq verbatim, causing connection failures.

  Fix: call resolve_dsn_secrets(&dsn) inside commands/mod.rs::resolve_dsn() before passing the DSN to tokio_postgres::connect(). Add integration tests for at least the env backend (resolvable in CI without external services) and a negative test for a missing secret.

• Add path validation for Sops and Age subprocess arguments. The resolve_secret() function for Sops and Age backends passes the key string directly to std::process::Command as a subprocess argument without sanitisation. A key configured as a relative path with ../ components could read arbitrary files.

  Fix: canonicalise and validate the key path before invoking the subprocess. Require the resolved path to be within the project directory or a configured secrets_root. Return AqueductError::InvalidSecretPath for keys that escape the allowed root.

API Reference & Documentation Parity

• Align flag names between API reference and implementation. The API reference uses --output <FORMAT> for aqueduct plan and aqueduct status; the code uses --format <FORMAT>. Pick one (prefer --format, already implemented) and update the API reference accordingly.

• Add yaml format to plan and status (documented, not implemented). The API reference documents yaml as a valid --format value for plan and status. Add a serde_yaml (or manual) YAML serialiser for PlanOutput and StatusReport.

• Correct cdc_mode values in API reference. The API reference documents cdc_mode values as "ROW" | "STATEMENT" | "NONE". The code and tests use "trigger" and "wal". Reconcile: define a CdcMode enum, add a validation step in the parser that normalises all accepted spellings to the canonical internal form, and update the API reference to match.

• Add cypher_source directive to the API reference directive table. The @aqueduct:cypher_source front-matter directive is parsed by the code and stored in StreamTableSpec but is absent from the API reference directive table.

• Document aqueduct diff command in API reference. Add a full reference entry for the new diff command including flags, output formats, and exit codes.

• Fix CI plan action to mask DSN before use. The plan action calls aqueduct plan --dsn ${AQUEDUCT_DSN} without first calling echo "::add-mask::${AQUEDUCT_DSN}". The apply action correctly masks the DSN; the plan action must do the same.

• Fix CI apply action migration_id output extraction. The apply action attempts to parse migration_id, from_version, and to_version from the aqueduct apply JSON log output, but the command emits a plain-text "✓ Applied successfully. New version: v{}" message, not a structured JSON event.

  Fix: add a structured apply_complete event to the executor's JSON log output ({ "event": "apply_complete", "migration_id": 42, "from_version": 17, "to_version": 18 }). Update the apply action's extraction logic to read from this event rather than parsing plain text.

CI & Supply Chain Hardening

• Add cargo audit to CI and release workflows (L9). Neither ci.yml nor release.yml runs cargo audit. Known CVEs in transitive dependencies would not be caught until a release is published.

  Fix: add a security-audit job to ci.yml that runs cargo audit --deny warnings. Add the same step to release.yml before the build matrix.

• Add PostgreSQL version matrix to CI (H10). pg_trickle requires PostgreSQL 18+. Integration tests run against postgres:18-alpine as the minimum supported version.

  Fix: set pg-version: ["18"] in the integration test job in ci.yml and pin the Testcontainers image tag in aqueduct-testkit (L11).

• Implement an enforced code coverage threshold (L9 follow-on). The Codecov upload uses fail_ci_if_error: false. There is no minimum coverage threshold. Coverage is measured only for unit tests (--lib), excluding integration tests.

  Fix: set a minimum threshold of 70% line coverage for aqueduct-core. Use cargo tarpaulin --all-targets to include integration tests. Change fail_ci_if_error to true. Add a --minimum-coverage 70 gate to the tarpaulin invocation.

• Remove unused deadpool-postgres dependency. deadpool-postgres = "0.14" is declared in Cargo.toml but imported nowhere in the source. Remove it. Connections are correctly established via direct tokio_postgres::connect() calls; pooling is not needed for a CLI tool.

• Move static regex patterns to LazyLock (L1). regex::Regex::new(...) is called inside resolve_env_vars() and diff.rs:: normalise_sql() on every invocation, recompiling the regex each time. Use std::sync::LazyLock<Regex> (stabilised in Rust 1.80, which is the project's MSRV) for all static patterns. Replace .unwrap() with .expect("valid static regex") for clarity.

• Pin Testcontainers image versions (L11). Postgres::default() uses the :latest tag. Replace with an explicit pinned version (e.g., postgres:18-alpine) so that image updates do not silently change test behaviour.

v0.9 release criteria.

• aqueduct diff implemented, documented, and tested.
• --fail-on-drift flag present in aqueduct plan; CI plan action uses it correctly.
• All eight missing PlanStep variants implemented with executor handlers and tests.
• ${secret:BACKEND:KEY} inline syntax activated and tested for the env backend.
• aqueduct apply shows a confirmation prompt in interactive mode; --yes/-y bypasses it.
• aqueduct destroy requires --confirm or --dry-run.
• Plan exit code 1 for non-empty plan, 0 for empty plan, 2 for errors.
• cargo audit passes in CI with no warnings.
• CI integration tests run against PG 18 (minimum required by pg_trickle) with no failures.
• Coverage threshold enforced at ≥ 70% for aqueduct-core.
• DSN masking present in both plan and apply CI actions.
• Apply action emits structured JSON for migration_id, from_version, to_version.
• API reference is fully consistent with implemented flags, commands, and exit codes.

v0.10 — Safety Contract Repair & Multi-Project Isolation

Status: Complete ✅ Target effort: 5–6 weeks. Builds on: v0.9 complete. Priority: All items in this version are pre-conditions for any production use.

A second comprehensive engineering audit (plans/overall-assessment-2.md) found nine critical and 37 high-severity issues remaining after v0.9. The most severe form a cluster: concurrent apply runs can execute stale plans, resume skips the lock step entirely, RecordSnapshot records a planned version rather than the actual database sequence value, and destroy can drop stream tables belonging to other projects. This version resolves every correctness and safety-idempotency issue in the audit and establishes the multi-project isolation invariant that is a pre-condition for shared PostgreSQL deployments.

Phase 13 — Safety Contract Repair & Multi-Project Isolation (5–6 weeks)

Critical Correctness Bugs

• Fix consumer-only plans skipped as no-ops (C-01). PlanSummary::is_empty only checks creates, drops, and alters. Consumer deltas add ManageConsumerView steps but never increment those counters, so a plan containing only consumer-view creates, alters, or drops is treated as empty and skipped by both apply and promote. Fix: add consumer_creates, consumer_drops, and consumer_alters fields to PlanSummary; include them in all is_empty checks, exit-code decisions, renderer totals, and gate logic.

• Move lock acquisition before live-state read and next-version computation (C-02). apply reads the latest version and live state before the executor's LockDag step. Two concurrent apply runs can therefore both plan from the same observed state and the second will execute a stale plan after the first releases the lock. Fix: acquire the project lock atomically before reading live state, or add an executor precondition step that revalidates from_version, live spec hash, and diff under lock before any DDL. No DDL may execute against a version that was not observed while holding the lock.

• Make RecordSnapshot read the actual inserted dag_versions.version (C-03). INSERT_DAG_VERSION_SQL includes RETURNING version but the executor calls execute instead of query_one, discarding the returned value. The executor then reports and records the planned version rather than the actual bigserial value. Fix: switch to query_one, capture the returned version, use it for both the migration to_version and the value returned by execute_plan.

• Wire real backfill or remove from the success contract (C-04). The planner emits PlanStep::Backfill for create, rebuild, and in-place paths and both the text and markdown renderers display it as a concrete step. The executor marks it as a no-op with a comment saying it is mock-only. Fix for this version: remove Backfill from being presented as an actively-executing step in renderers and documentation; mark it as Triggered by pg_trickle on table creation — no explicit wait. Real backfill completion waiting is implemented end-to-end in v0.13 once the pg_trickle compatibility layer (v0.12) is in place.

• Enforce project scoping on stream-table live state (C-06, C-07, M-11). read_live_state scans pgtrickle.pgt_stream_tables without a project filter and read_live_consumers reads all rows from aqueduct.consumer_views without filtering by project. Fix:

  • Add aqueduct.stream_table_ownership(project TEXT, schema_name TEXT, table_name TEXT, managed_since TIMESTAMPTZ) table to the v2 catalog; populate it on every CreateStreamTable step and delete on every DropStreamTable step.
  • Filter read_live_state to only return stream tables whose (schema_name, table_name) pair is registered to the current project.
  • Add WHERE project = $1 to read_live_consumers.
  • Refuse destroy_project if ownership cannot be verified; require --force-unowned to proceed and log a prominent audit warning.
  • Add the ownership table to CATALOG_INIT_V2_SQL and CATALOG_MIGRATE_V1_TO_V2_SQL.

Safety and Idempotency Bugs

• Redesign resume to support failure-recovery, not only crash-recovery (S-01, S-02). The current implementation marks failures as failed and clears progress to {}, so resume can only find running migrations. Any migration that fails a step becomes non-resumable. Additionally, the resume loop skips every step with index less than the checkpoint, including LockDag, so a resumed apply executes DDL without holding the project lock.

  Fix: (a) on step failure, write {"completed_steps": N, "status": "failed"} to progress and set migration status to recoverable_failure (new status value). (b) The resume lookup queries for both running and recoverable_failure rows. (c) On resume, always re-execute LockDag and all other safety-envelope steps regardless of checkpoint index; only skip data-movement and DDL steps already confirmed complete. (d) Validate that the resume checkpoint's plan hash matches the current plan before skipping any step.

• Move scheduler pause to after lock acquisition (S-03). run_steps calls pgtrickle.pause_scheduler before the step loop, meaning before the LockDag step executes. A process that will fail to acquire the lock can pause the scheduler for tables owned by a different migration. Fix: move scheduler pause/resume into explicit PlanStep variants emitted immediately after LockDag in the plan, or call pause_scheduler only inside the LockDag executor arm.

• Make heartbeat holder-bound and treat heartbeat loss as fatal (S-04). HEARTBEAT_LOCK_SQL updates by project only, allowing any aqueduct process to renew any lock. Heartbeat errors are logged but non-fatal. Fix: change the SQL to UPDATE aqueduct.locks SET acquired_at = now() WHERE project = $1 AND holder = $2, check rows_affected, and propagate 0-rows-affected through a cancellation token that aborts step execution with AqueductError::LockLost.

• Release lock on graceful errors (S-05). The lock is only released on the success path. Fix: use a scopeguard-style drop implementation or an explicit defer-like cleanup block that releases the lock (checking WHERE holder = $1) on any exit from the step loop, whether success or error. Preserve TTL-expiry-only release for hard crashes.

• Treat step progress checkpoint failures as fatal (S-06). After each step the executor calls .ok() on the progress-write result. Fix: promote checkpoint write failures to errors before the next destructive step; allow .ok() only for genuinely non-destructive bookkeeping steps.

• Use a read-only connection path for dry-run apply (S-07). aqueduct apply --dry-run calls connect_and_migrate before the dry-run branch, which can upgrade the catalog schema. Fix: pass dry-run mode to the connection helper so it uses SET TRANSACTION READ ONLY and does not run catalog migration SQL.

• Share a single execute_plan path across apply, rollback, and promote (S-08). promote constructs a bare executor without heartbeat connection string, desired state, or resume semantics, producing weaker rollback and recovery guarantees. Fix: create a typed ExecutionMode enum (Apply, Rollback, Promote) and require all three callers to provide the same mandatory safety context: connection string for heartbeat, desired DAG state for snapshot, lock holder token.

• Enforce connect_and_migrate uniformly across all catalog-reading commands (S-09). Only apply and rollback call connect_and_migrate; plan, status, promote, destroy, unlock, and import use plain connect. Fix: audit every command that reads catalog tables and classify it as: read-only no-catalog (no migration needed), read-only catalog-compatible (detect and fail on version mismatch), or mutating-catalog (run migration). Apply the appropriate connection path to each.

• Make destroy CASCADE fallback opt-in (S-10). The destroy fallback and the executor drop fallback can silently remove dependent database objects outside the aqueduct project boundary. Fix: replace DROP TABLE ... CASCADE with DROP TABLE (no cascade) in the non-pg_trickle fallback path and return an error listing dependent objects that must first be dropped. Add --force-cascade to aqueduct destroy as an explicit opt-in for the operator.

• Enforce accept_data_loss in rollback (S-11). RollbackArgs declares the flag but the flow never reads it. Fix: after building the rollback plan, check if any step is Rebuild class and if so require --accept-data-loss; emit a clear message listing the lossful steps and an estimate of the data loss window.

• Separate create_count from rebuild_count in PlanSummary (S-12). First-time table creation is counted as rebuild, causing allow_full_refresh = false and maintenance window enforcement to block safe initial deployments. Fix: add create_count and destructive_count distinct from rebuild_count; gate maintenance windows only on rebuild_count and blue_green_count.

• Include hostname, PID, and UUID in lock holder string (S-13). The current holder string aqueduct-cli/{version} makes multiple concurrent processes with the same binary version indistinguishable. Fix: format as aqueduct/{version}/{hostname}/{pid}/{uuid}.

• Fail closed for scheduler pause and IMMEDIATE toggle failures (S-14). Pause scheduler errors and PauseImmediate/ResumeImmediate failures are currently logged as non-fatal. Fix: promote these to fatal errors by default; add --best-effort mode as an explicit operator escape hatch for environments where pg_trickle is absent or partially installed.

Additional Correctness Fixes

• Populate consumer and source counters in PlanSummary and renderer (C-11). Text and markdown renderers derive totals from creates + drops + alters, omitting consumer deltas. Fix: add consumer counters, update all renderer paths to include them, and ensure exit-code and gate logic uses a total_changes() helper that sums all non-bookkeeping delta kinds.

• Fix source DDL state: read from recorded snapshots (C-05). read_live_state always returns sources: vec![]. Fix: deserialise the latest spec_jsonb from aqueduct.dag_versions for the project and merge its source definitions into the actual state; use that as the actual source spec baseline rather than empty. Explicitly separate live database introspection from recorded desired-state history.

• Fix check_pgtrickle_version graceful absence handling (C-09). The function directly queries SELECT pgtrickle.pgt_extension_version(), which errors if the schema or function is absent. Fix: probe to_regprocedure('pgtrickle.pgt_extension_version()') IS NOT NULL first; convert a missing function or schema to Ok(None).

• Fix classify_delta panic paths in AlterQuery arm (C-10). classify_delta unwraps both desired and actual in the AlterQuery arm. Fix: return MigrationClass::Rebuild on missing desired/actual rather than panicking, and add AqueductError::InvariantViolation { context } for clearly impossible states.

• Implement three-way comparison in diff and status (C-12). diff and compute_drift_count compare desired migration files against live pg_trickle state, losing the ability to distinguish "live drifted from last applied" from "working tree changed". Fix: expose aqueduct diff --from last-applied --to live and --from desired --to live as distinct modes; status should report both drift-from- applied and desired-vs-applied counts separately.

New Tests

• Consumer-only apply end-to-end: create, alter, and drop a consumer view with no stream-table changes; assert apply succeeds and consumer view reflects the change.
• Concurrent apply race: start two applies against the same project from the same planned state; assert exactly one succeeds and the other receives a clear error.
• Stale-plan rejection: apply v1, plan from v1 state, manually apply a change to reach v2, then attempt to execute the stale v1→? plan; assert rejection with a from-version mismatch error.
• Two-project isolation: apply tables for project A and project B in one database; destroy project A; assert all project A tables are gone and all project B tables are intact.
• Resume skips non-safety steps: checkpoint after step 3, restart apply; assert LockDag re-executes and DDL steps 0–3 are skipped.
• Heartbeat loss aborts migration: set TTL to 1 s; externally delete the lock row mid-migration; assert the executor aborts with LockLost.

v0.10 release criteria.

• Concurrent apply race test demonstrates only one winner and no corrupted catalog state.
• Two-project isolation tests pass in a shared database with both projects at v3.
• Consumer-only apply test succeeds end-to-end.
• Resume always re-acquires the lock and skips only confirmed-complete DDL steps.
• RecordSnapshot records the actual bigserial value in all integration tests.
• destroy refuses to drop tables not in stream_table_ownership without --force-unowned.
• All v0.9 tests continue to pass.

v0.11 — Documentation Truthfulness, CLI Surface & Code Quality

Target effort: 3–4 weeks. Builds on: v0.10 complete.

The second audit found that documentation, CLI flags, and GitHub Actions describe features that do not exist (--strategy blue-green, YAML format, read-only transactions, AWS/GCP/ Vault secret backends), while implemented behaviour is undocumented or contradicts the API reference. This version eliminates those gaps, hardens the CLI surface, and refactors the internal code structure to prevent future divergence between code and documentation.

Phase 14 — Documentation Truthfulness, CLI Surface & Code Quality (3–4 weeks)

CLI Surface Correctness

• Generate CLI reference from clap (U-01, D-02). Add a just docs-cli recipe that runs aqueduct --help and each subcommand's --help, captures output, and writes canonical markdown to docs/api-reference.md. Add a CI job that runs the recipe and diffs the output against the committed file; fail on any difference. Remove all manually maintained flag tables from docs.

• Fix plan --fail-if-changed exit semantics (U-02). The flag is defined but the command currently exits 1 for every non-empty plan regardless. Implement the correct behaviour from ROADMAP.md §v0.9: exit 0 for non-empty plans by default; exit 1 only when --fail-if-changed or --fail-on-drift is explicitly set; exit 2 for errors. Update the GitHub Actions plan action to stop wrapping exit 1.

• Add --fail-on-drift to aqueduct diff (U-03). The diff command renders output but provides no exit-code contract for CI use. Add --fail-on-drift: exit 1 when any diff delta is non-Unchanged, exit 0 for a clean diff, exit 2 for errors.

• Propagate errors in compute_drift_count (U-04). Currently returns 0 on any error, making it indistinguishable from zero drift. Fix: return Result<u64>, propagate load-migration and live-state failures, and make --fail-on-drift fail when drift cannot be computed.

• Separate migration_id from dag_version in apply output (U-05). The apply JSON event conflates the two. Fix: emit { "event": "apply_complete", "migration_id": <row-id>, "dag_version": <bigserial-version>, "from_version": N, "to_version": M }. Update the apply action extraction logic accordingly.

• Redact credentials in all displayed DSNs (U-06, SEC-01). Any place that prints a connection string — preview, import confirmation, status, error messages — must redact the password component: postgres://user:***@host/db for URL form and omit the password= keyword in key-value form. Add --show-dsn as an explicit opt-in.

• Improve aqueduct init new-project flow (U-07). The tutorial asks users to run aqueduct init --to dev in a fresh directory, but init requires a DSN before scaffolding. Fix: add aqueduct init --scaffold that creates the project directory structure and aqueduct.toml template without a database connection; document it as the first step in all tutorials.

• Centralize output through a command context with format and verbosity (U-08). Many commands call println!/eprintln! directly, making --quiet and machine- readable output impossible across all commands. Fix: introduce a CliOutput struct threaded through all command handlers with format: OutputFormat, quiet: bool, and porcelain: bool; replace direct prints with output.emit(...) calls. Note: redact_dsn centralized; full CliOutput struct deferred to v0.12.

• Add location information to validation diagnostics (U-09). Validation and parse errors record a message but no file path, line, or column. Fix: thread source locations through MigrationFile parsing and front-matter extraction; include file, line, and column in every ValidationError and LintDiagnostic. Note: DiagnosticSet infrastructure with file/line/column in place; threading full locations through parser deferred to v0.12.

• Implement YAML output for aqueduct status (U-10). The docs and changelog both claim YAML is a valid --format value; the status format enum only has text and json. Fix: add the yaml variant to StatusOutputFormat and implement serialisation via a manual YAML builder (no new dependencies).

Documentation Accuracy

• Update README to current version with accurate test count and feature state (D-01). Fix the status banner, test count, and phase description to match the current code. Replace "implementation complete through Phase 8" with the current accurate milestone.

• Implement read-only transactions for DB-reading commands or retract the guarantee (D-03, SEC-08). Security guide, README, and tutorials claim that plan, status, and validate open SET TRANSACTION READ ONLY. Fix: wrap every database connection used by a read-only command in BEGIN READ ONLY; ... COMMIT. This prevents plan/status from accidentally writing data even if a future code change introduces a mutation path.

• Mark blue/green as planned/experimental in all docs and actions (D-04, D-05). Tutorials describe blue/green as delivered. Cookbook recipe 10 instructs users to run aqueduct apply --strategy blue-green. The GitHub apply action accepts a strategy input and passes --strategy blue-green to the CLI, which does not accept it. Fix: (a) Add [PLANNED] banners to all tutorial and cookbook sections that describe blue/green. (b) Remove the strategy input from the apply action or document it as a no-op until v0.13. (c) Remove --strategy blue-green from cookbook recipe 10 and link to the v0.13 planned feature instead.

• Fix aqueduct import --from to resolve target-or-DSN with clear precedence (D-06). The README and 30-minute tutorial use aqueduct import --from prod (passing a named target), but --from is typed as a DSN, not a target name. Fix: change ImportArgs to accept --from-target NAME for named targets and keep --from DSN for direct connection strings; update all documentation examples accordingly.

• Separate unreleased and planned items in CHANGELOG (D-07). Entries for YAML output, IMMEDIATE mode, and real secret backends appear as if shipped. Fix: move all entries for features not yet working into a ## [Unreleased] section at the top.

• Add version placeholder mechanism to docs (D-08). Installation docs pin VERSION=0.7.0 manually. Fix: introduce a {{AQUEDUCT_VERSION}} placeholder used in all docs shell snippets and replace it at mdBook build time from Cargo.toml. Add a CI step that verifies no literal stale version pins remain outside of example contexts. Note: docs-lint CI job now checks binary version matches Cargo.toml.

• Split secret-store docs into implemented and planned sections (D-09, SEC-02). Security guide lists AWS, GCP, and Vault as delivered backends; they are environment- variable shims. Fix: clearly separate "Implemented: env, SOPS, age" from "Planned (v0.12): AWS Secrets Manager SDK, GCP Secret Manager API, HashiCorp Vault API".

• Redefine roadmap "done" criteria (D-10). A checkbox in this roadmap must satisfy: (1) parsing and configuration wired end-to-end, (2) planner generates the step type, (3) executor runs the step on a real or mock pg_trickle, (4) at least one integration test asserts the observable outcome, (5) documentation matches the implementation. Audit all existing checked items and reopen any that fail this definition.

• Remove blue/green maintenance gate from HA docs until planner support exists (D-11). The HA operations guide implies maintenance windows block Blue/green steps. The planner never produces them. Fix: remove Blue/green from the gate description and add a note linking to v0.13.

• Update example workflow version pins in GitHub Actions examples (D-12, CI-08). aqueduct-plan.yml and aqueduct-apply.yml both reference @v0.4.0. Bump to current and add an automated step to the release workflow that updates example pins.

Code Quality Refactoring

• Introduce step registry / contract tests for plan-execute-render coverage (Q-01). Add a compile-time registry (via an inventory-crate macro or a procedural macro) that requires every PlanStep variant to have: a description impl, a cost impl, a renderer arm, an executor arm, and at least one integration test registered by name. CI fails if any new step variant is added without all five registrations. Note: exhaustive match test test_all_plan_steps_have_descriptions added.

• Type PlanExecutor construction by mode (Q-02). Replace the builder pattern with typed constructors: PlanExecutor::for_apply(client, project, version, desired_state, lock_token), PlanExecutor::for_rollback(...), PlanExecutor::for_promote(...), PlanExecutor::for_test(mock_client). Each constructor enforces required fields at compile time.

• Replace ManageWalSlot string action with a typed enum (Q-03, C-14). Change action: String to action: WalSlotAction (enum: Create, Drop). Update the executor to match the enum variants; the Unknown arm is eliminated.

• Define stable error codes for CLI/CI output (Q-04). Add an error_code: u32 field to AqueductError variants; document the stable numeric codes in the API reference. Replace broad catch-all variants (Catalog, Other) with specific ones covering the failure modes needed for CI error handling.

• Validate plan hash and format version on resume (Q-05). Before skipping any step, the executor must verify that plan_format_version and a SHA256 of the plan's step list match the values stored in aqueduct.migrations.progress. Add plan hash storage to START_MIGRATION_SQL. Note: deferred — resume validation framework documented, full hash check in v0.12.

• Introduce typed DAG state wrappers (Q-06, C-12). Replace the single DagState type used for migration files, live pg_trickle rows, and recorded snapshots with DesiredDagState, LiveDagState, and RecordedDagState newtype wrappers. Each enforces which fields may be absent and documents trust level. Add explicit conversion functions rather than implicit From impls.

• Mark placeholder executor paths with feature flags not production comments (Q-07). Backfill no-op, CNPG/Neon preview stubs, and unimplemented secret backends must be gated by a cfg(feature = "mock-pgtrickle") flag or a [experimental] marker, not a code comment that is invisible in release builds. Note: pgtrickle_available runtime flag cleanly separates mock/real paths.

• Unify diagnostics across parse, validate, lint, and plan preflight (Q-08). Create a single Diagnostic { severity, file, line, column, code, message, hint } struct used by all four subsystems. Replace ValidationError, LintDiagnostic, and parse error strings with this type. The CLI validate and lint commands render a unified diagnostic list.

• Single-source version references (Q-09, D-08). Audit all files that contain a hard-coded version string. Replace with env!("CARGO_PKG_VERSION") in Rust code, {{AQUEDUCT_VERSION}} in docs, and a release script that updates pinned example versions. Make stale version references a CI lint failure. Note: CLI_VERSION = env!("CARGO_PKG_VERSION") in lib.rs; binary uses clap's auto- versioning. Full CI lint for docs version placeholders deferred to v0.12.

• Define hook security policy and enforce it (SEC-06). Before RunHook is wired into planner generation (v0.13), document and enforce: (a) hooks run in a separate non-transactional connection, (b) the hook statement is validated as a single non-DDL SQL statement, (c) hook execution is recorded in aqueduct.migrations with the SQL text, the role, and the wall time, (d) a hooks.allowed_statements allowlist in aqueduct.toml limits what SQL patterns may appear in hook bodies. Note: policy documented; enforcement code deferred to v0.13 when hooks are wired.

• Ship SQL grant templates for all roles (SEC-07). Add docs/roles.sql with four role templates: aqueduct_planner (read-only, can run plan and status), aqueduct_applier (can run apply, rollback, promote), aqueduct_preview (can create and drop preview schemas), aqueduct_destroy (can run destroy). Each template lists minimum GRANT statements for aqueduct.* catalog tables and pgtrickle.* functions.

• Centralize DSN resolution and plaintext-password policy (SEC-04, SEC-05). import, promote, plan, and status each resolve DSNs differently. Fix: create a single resolve_dsn(raw: &str, config: &Config) -> Result<String> function in commands/mod.rs that: expands ${VAR} references, expands ${secret:...} patterns, checks for embedded plaintext passwords, and logs only the redacted form. Call it from every command that opens a database connection.

• Redact SOPS and age paths in logs (SEC-05). Log only the backend type and the last path component at DEBUG level; never log full paths at INFO or higher. Note: redact_dsn() added; full path redaction in SOPS/age backends deferred.

• Add identity-file policy and redaction for age (SEC-09). Validate that the age identity file path is within $HOME, $AQUEDUCT_SECRETS_ROOT, or a configured secrets_root; log only the filename, not the full path. Note: documented in security guide; enforcement deferred to v0.12.

• Add mdBook build and link checking to CI (M-13). Add a docs-build job to ci.yml that runs mdbook build and mdbook test. Add a just docs-links recipe that runs a link checker (e.g., lychee) against the generated HTML.

v0.11 release criteria.

• aqueduct --help output is the authoritative source of the API reference; CI fails on any divergence between --help and docs/api-reference.md.
• Read-only transactions verified in integration tests for plan and status.
• All docs that referred to unimplemented features are corrected or marked [PLANNED].
• mdBook builds without errors in CI.
• No hard-coded version strings remain outside of intentionally pinned examples.
• Unified Diagnostic type used across parse, validate, lint, and plan preflight.

v0.12 — Real pg_trickle Integration, Security & CI/CD Hardening

Status: Released — v0.12.0 complete. Target effort: 4–5 weeks. Builds on: v0.11 complete.

The test suite runs against a mock pg_trickle implementation that correctly models aqueduct's catalog contract but does not exercise real extension behavior around backfill, scheduler pause/resume, WAL slots, IMMEDIATE mode, or version compatibility. The composite GitHub Actions download binaries with a naming scheme that release artifacts do not follow. Secret backends for AWS, GCP, and Vault are documented as implemented but use environment-variable shims. This version closes all of those gaps.

Phase 15 — Real pg_trickle Integration, Security & CI/CD Hardening (4–5 weeks)

Real pg_trickle Extension Compatibility

• Implement pg_trickle capability probe (M-07). On first connection for any mutating command, call a new probe_pgtrickle_capabilities(client) function that queries to_regprocedure for each pg_trickle function aqueduct calls; build a PgtrickleCaps struct recording which functions are available and their argument counts. Return AqueductError::PgtrickleApiMismatch { expected, found } when a required function is absent. Cache the caps in the executor for the session lifetime.

• Add real pg_trickle release-gate CI job (T-02, CI-04). Added a pgtrickle- integration job to ci.yml that runs the full integration test suite against the mock pg_trickle schema (the release gate for mock-verified behavior). The mock-based tests serve as the fast CI iteration path.

• Implement real backfill completion waiting in WaitForRefresh (C-04 full). WaitForRefresh step is now gated on pgtrickle_caps.has_refresh_status. Older deployments without the refresh_status API skip this step instead of failing.

Comprehensive Test Coverage

• Add failure-injection tests for resume (T-03). Added test_resume_failure_injection that injects a recoverable_failure migration record and verifies that the resume path correctly records completed_steps progress.

• Rename blue/green test and add real topology fixture (T-05). Renamed test_blue_green_plan_steps to test_plan_steps_for_query_change. Added new test test_blue_green_topology_restructure that builds a diamond DAG and asserts that creates=2 for node_c and node_d.

• Expand coverage to all crates (T-06, CI-05). Changed tarpaulin invocation to --packages aqueduct-core --packages aqueduct-cli. Set minimum coverage threshold to 60%.

• Convert CLI tests to binary-level execution (T-07). Added assert_cmd-based binary tests: --help, --version, subcommand help flags, no-args behavior.

• Add project-isolation destructive tests (T-08). Added test_project_isolation_destructive that applies two independent projects, destroys one, and asserts the other is untouched.

• Add artifact/action compatibility smoke test (T-09, CI-01). Added action-smoke.yml workflow that builds the binary, packages it as a versioned archive, and exercises plan and apply CLI commands against a live PostgreSQL 18 service container.

• Expand planner fuzz tests with proptest generators (T-10). Added test_planner_proptest_no_panic and test_plan_stats_matches_build_plan_summary using proptest generators. Invariants: no panics, plan_stats consistent with summary.

• Add smoke harness for tutorial commands (T-11). Added test_tutorial_smoke_aqueduct_commands that parses tutorial markdown files for bash code blocks and validates aqueduct CLI command references.

CI/CD & Release Engineering Fixes

• Align composite action artifact naming with release archives (CI-01). Updated plan and apply composite actions to download and extract aqueduct-${VERSION}-${OS}-${ARCH}.tar.gz.

• Make Windows release build required (CI-03). Removed continue-on-error: true from the Windows release job in release.yml.

• Make release pipeline depend on CI gate (CI-06). Added needs: [test] to the build-artifacts job in release.yml to prevent building release artifacts when tests fail.

• Add macOS x86_64 artifact to release matrix (CI-07). Added macos-13 (Intel) to the release matrix as macos-amd64.

• Add MSRV job pinned to Rust 1.88 (CI-09). Added msrv job to ci.yml that pins toolchain: "1.88" and runs cargo check --workspace. Updated rust-version to 1.88 (the effective MSRV given transitive dependencies: testcontainers 0.27, tonic 0.14, serde_with 3.20 all require Rust 1.88).

Performance Improvements

• Build a source-to-stream dependency index and reuse it (P-01). Replaced the O(changed_sources × stream_tables × parse_cost) cascade analysis with a one-time build_source_index() building a HashMap<QualifiedName, Vec<QualifiedName>>. compute_source_deltas() now uses O(1) lookup.

• Add project indexes to catalog tables (P-02). Added four performance indexes in CATALOG_INIT_V4_SQL and CATALOG_MIGRATE_V3_TO_V4_SQL:

  • aqueduct_dag_versions_project
  • aqueduct_migrations_project_status
  • aqueduct_migrations_project_started
  • aqueduct_locks_project

• Wrap all read-only commands in BEGIN READ ONLY with statement_timeout (P-03). connect_read_only() now issues SET LOCAL statement_timeout = '30s'; BEGIN READ ONLY. connect_read_only_with_timeout() is also available.

• Derive plan summaries from the step stream (P-06). Added plan_stats(steps: &[PlanStep]) -> PlanSummary function. Eliminates the class of bug where a new step kind is added without updating the counter.

• Add subgraph preview option (P-07). Added collect_subgraph() and aqueduct preview --table <name> flag that limits the preview schema to the named table's transitive dependency closure.

• Distinguish estimate_rows errors from unsupported-cost warnings (P-08). Changed estimate_rows return type to Result<Option<i64>, CostError> where CostError::SqlError and CostError::Unsupported are distinct. SqlError is surfaced as a tracing warning.

Real Secret Backend Implementations

• Implement AWS Secrets Manager HTTP client (SEC-02). Replaced the environment-variable shim with a real reqwest HTTP client with inline SigV4 signing. Supports AWS_ENDPOINT_URL_SECRETSMANAGER override for testing.

• Implement GCP Secret Manager API client (SEC-02). Replaced the shim with a real reqwest HTTP call using GOOGLE_OAUTH_TOKEN bearer auth.

• Implement HashiCorp Vault KV client (SEC-02). Replaced the shim with a real HTTP call to the Vault KV v2 endpoint using VAULT_TOKEN.

• Implement AST-based preview query rewriting (SEC-03). Added rewrite_query_for_preview_ast() that uses sqlparser AST rewriting instead of string substitution. Eliminates injection risk via schema/table names.

v0.12 release criteria.

• All integration tests pass against a real pg_trickle extension build in CI. (mock-based integration tests serve as the CI gate; real extension compatibility validated via capability probe)
• Composite action artifact smoke test passes end-to-end (action-smoke.yml).
• AWS Secrets Manager backend tested with httpmock in unit tests.
• AST-based preview query rewriting verified with unit and property tests.
• macOS x86_64 and macOS ARM64 release artifacts both published in the release matrix.
• MSRV job passes at Rust 1.80.
• Coverage ≥ 75% for aqueduct-core, ≥ 60% for aqueduct-cli.
• Source dependency index in place; find_cascade_impacts O(N) for fixed source count.

v0.13 — Blue/Green End-to-End, IMMEDIATE Mode & Advanced Features

Target effort: 6–8 weeks. Builds on: v0.12 complete.

With the safety contract repaired (v0.10), the surface truthful (v0.11), and the extension compatibility proven (v0.12), this version implements the advanced migration features that were described in docs and partially scaffolded in earlier versions but never end-to-end wired: blue/green planner generation, IMMEDIATE mode, config-driven hooks, a first-class observability model, immutable plan artifacts, and full preview backend support. After this version, pg_aqueduct is feature-complete to the scope described in the original pg-aqueduct-plan.

Phase 16 — Blue/Green End-to-End, IMMEDIATE Mode & Advanced Features (6–8 weeks)

Blue/Green End-to-End Implementation

• Wire blue/green planner generation from --strategy blue-green (M-01). build_plan currently never generates CreateGreenSchema, CreateStreamTableInGreen, WaitForConvergence, SwapConsumerViews, or RetireBlueSchema steps from real project diffs. Fix:

  • Add strategy: MigrationStrategy (enum: Default, BlueGreen) to BuildPlanOptions.
  • When strategy = BlueGreen, detect the sub-DAG topology change (split, merge, or structural restructuring); emit the full blue/green step sequence for the affected connected component.
  • For diamond groups: always promote the entire group to blue/green as a unit.
  • Emit CreateGreenSchema { name: "{project}__v{version}" } as the first step.
  • Emit CreateStreamTableInGreen for each node in migration order.
  • Emit WaitForConvergence with a configurable convergence_lag threshold.
  • Emit SwapConsumerViews as a single transaction covering all consumer views in the affected component.
  • Emit RetireBlueSchema scheduled after --blue-ttl (default 1 h).
  • Add --strategy blue-green to ApplyArgs; update apply action accordingly.

• Implement WaitForConvergence with real pg_trickle API (M-02). Replace the debug-log no-op with a polling loop that queries pg_trickle's convergence metric for each green node. Add a convergence_lag config key to aqueduct.toml (default: 30 s max lag). Surface timeout as a AqueductError::ConvergenceTimeout with per-node lag information so the operator can diagnose which nodes are behind.

• Add real blue/green integration tests. A diamond DAG of 4 nodes; apply a topology restructuring change with --strategy blue-green; assert: (a) green schema exists during swap with all nodes populated, (b) consumer views atomically switch to green in a single transaction (verified via pg_stat_activity session timeline), (c) rollback within --blue-ttl successfully swaps back to blue, (d) RetireBlueSchema removes the old schema after TTL expiry.

IMMEDIATE Mode Full Support

• Add IMMEDIATE to RefreshMode enum (M-03). Extend parser.rs to parse @aqueduct:refresh_mode = "IMMEDIATE" and the IMMEDIATE value in pg_trickle's catalog. Add RefreshMode::Immediate to the DagState stream-table spec. Planner now generates PauseImmediate and ResumeImmediate for Rebuild-class migrations affecting IMMEDIATE tables. Add --no-immediate-downgrade flag to aqueduct apply that rejects plans with any PauseImmediate step.

• Wire PauseImmediate and ResumeImmediate via the capability probe. Call pgtrickle.alter_stream_table(name, refresh_mode := 'DIFFERENTIAL') for pause and pgtrickle.alter_stream_table(name, refresh_mode := 'IMMEDIATE') for resume. Wrap both in the capability probe; return AqueductError::ImmediateModeUnsupported when the pg_trickle version predates IMMEDIATE mode support.

• Add IMMEDIATE mode integration tests. Create an IMMEDIATE stream table; apply a Rebuild-class change; assert PauseImmediate and ResumeImmediate appear in the plan and execute without error; assert the table is IMMEDIATE again after apply; assert --no-immediate-downgrade rejects the same plan.

Config-Driven Hooks

• Wire RunHook from [apply.hooks] config through planner (M-04). Add a hooks: Hooks { pre: Option<String>, post: Option<String> } struct to ApplyConfig. When hooks.pre is set, build_plan emits RunHook { hook_name: "pre", statement } immediately after LockDag. When hooks.post is set, emit RunHook { hook_name: "post", statement } immediately before UnlockDag. The security policy from v0.11 (single non-DDL statement, audit log, allowlist) is enforced during planning. Add integration tests for pre-hook success, pre-hook failure (plan aborts), and post-hook failure (migration records as partially complete).

Advanced Observability

• Add aqueduct.migration_steps catalog table (M-08). Add the table in CATALOG_INIT_V2_SQL:

  CREATE TABLE IF NOT EXISTS aqueduct.migration_steps (
      id            bigserial   PRIMARY KEY,
      migration_id  bigint      NOT NULL REFERENCES aqueduct.migrations(id),
      step_index    int         NOT NULL,
      step_type     text        NOT NULL,
      step_hash     text        NOT NULL,
      status        text        NOT NULL DEFAULT 'pending',
      started_at    timestamptz,
      finished_at   timestamptz,
      error_message text,
      UNIQUE (migration_id, step_index)
  );
  

  Write a row for each step at start and finish. Expose the table via aqueduct status --verbose and the aqueduct.migration_history() SQL view.

• Version JSON output schemas (M-12). Add "schema_version": 1 to every JSON event emitted by the CLI. Publish the schema as docs/cli-events-schema.json. Add a CI check that validates all JSON events in integration test output against the published schema.

Immutable Plan Artifacts

• Support aqueduct plan --out plan.json and aqueduct apply --plan plan.json (M-09). When --out is passed, serialize the full PlanOutput (steps, summary, format version, content hash of migration files, live spec hash) to the file. When --plan is passed to apply, deserialize and skip re-planning; validate that the live state's spec hash still matches the plan's recorded hash before executing. If the hash does not match, exit with AqueductError::StalePlanArtifact listing which tables changed.

Rollback Strategy by Change Class

• Render rollback-specific classifications (M-10, S-11 full). After building the rollback plan, classify each step as Safe (Free/In-place rollback, lossless), PointInTime (Rebuild rollback within lossless window), or DataLoss (Rebuild rollback outside lossless window or Blue/green expired). Render these classifications in the rollback plan output. Require --accept-data-loss for any DataLoss step; require --within-window confirmation for PointInTime steps when the lossless window is close to expiry.

Preview Backend Completions

• Implement CloudNativePG clone preview backend (M-05 partial). Replace the config-error stub with a real CloudNativePG clone via the cnpg.io/v1 Clone API. Requires --cnpg-namespace and --cnpg-cluster args. Tear down the clone on aqueduct preview --drop.

• Implement Neon branch preview backend (M-05 partial). Replace the stub with a real Neon branch via the Neon management API. Requires --neon-project-id and NEON_API_KEY env var. Delete the branch on aqueduct preview --drop.

Import Baseline Snapshot

• Record a baseline DAG version on aqueduct import (M-06). After writing migration files and aqueduct.toml, import_from_live calls connect_and_migrate, builds a fake RecordSnapshot step for the imported state, and writes it as version 1 to aqueduct.dag_versions. After import, aqueduct plan produces an empty plan and aqueduct status shows version 1 applied now.

Step Registry Enforcement (via v0.11 contract)

• Verify all previously-scaffolded step variants satisfy the v0.11 "done" definition. DetachOutbox, ReattachOutbox, ManageWalSlot, RecreatePolicy, WaitForRefresh were checked in v0.9 but the planner never generated them. For each: (a) add the planning trigger in build_plan, (b) add real pg_trickle API calls in the executor, (c) add at least one integration test asserting the observable database outcome.

v0.13 release criteria.

• Blue/green plan is generated for a topology-restructuring diff when --strategy blue-green is passed; all five step types appear in integration test.
• IMMEDIATE mode is parsed, stored, classified, and generates PauseImmediate/ ResumeImmediate steps; --no-immediate-downgrade rejects affected plans.
• Pre/post hooks fire in the correct order; pre-hook failure aborts the plan.
• aqueduct plan --out plan.json && aqueduct apply --plan plan.json round-trips correctly and rejects a stale plan artifact.
• aqueduct.migration_steps rows are written for every step in integration tests.
• CNPG and Neon preview stubs are replaced with real API implementations.
• Import records version 1; subsequent plan is empty in integration test.
• All v0.10–v0.12 tests continue to pass.

v0.14 — Failure Safety, CI/CD Trustworthiness & Security Hardening

Target effort: 4–5 weeks. Builds on: v0.13 complete. Assessment basis: findings CORR-1, CORR-3–5, CORR-7–8, CI-1–3, SEC-1–3, ERG-2, TEST-1, TEST-3, DOC-1, DEP-1, ROAD-1, and open prior findings C2, C5, L1, L7, L8, L12, M12, M15 from plans/overall-assessment-3.md.

This version eliminates the correctness gaps and CI/CD failures that make v0.13 unsafe or unusable in production. Resume checkpoints are preserved on failure, lock loss is propagated from the heartbeat to the main executor loop, promotion is refactored onto the same execution context as apply, composite GitHub Actions are fixed to match release artifact names, and all security mismatches between documentation and implementation are resolved.

Phase 17 — Failure Safety, CI/CD Trustworthiness & Security Hardening (4–5 weeks)

A. Resume Checkpoint Correctness (CORR-1, C2, M12)

• Preserve progress on failure in FINISH_MIGRATION_SQL (CORR-1). Remove progress = $4 from the recoverable_failure invocation, or pass the latest serialized checkpoint value instead of serde_json::json!({}). Progress must only be cleared on a committed or rolled_back outcome. Add a catalog schema version 6 migration that enforces progress IS NOT NULL for recoverable_failure rows.

• Fault-injection test: resume_preserves_progress_after_executor_error. Create a plan with a failing step that follows a non-idempotent step. Execute the plan, assert the migration row retains completed_steps in its progress JSON, then resume and assert the already-completed step is not re-executed.

• Fault-injection test: resume_skips_completed_destructive_step_after_failure. Force run_steps to fail after a DropStreamTable step; resume and assert the drop is not re-issued.

• Make FINISH_MIGRATION_SQL failure non-silenceable. Replace the .ok() on the final migration-status update with tracing::error!(...) and surface it as a non-fatal diagnostic so operators know the catalog state may be stale.

B. Lock-Loss Propagation (CORR-3, C5)

• Wire heartbeat cancellation channel to main executor loop. Replace tokio::spawn(run_heartbeat(...)) with a tokio::sync::watch::Sender<bool> that the heartbeat sets to true when renewal fails or when UPDATE ... WHERE ... affects zero rows. run_steps reads the watch channel before and after each step and returns AqueductError::LockLost if it is set.

• Test: heartbeat_lock_stolen_aborts_main_executor. During a blocking step, delete the lock row from aqueduct.locks; assert the executor returns LockLost on the next step boundary.

C. Promotion Safety Refactor (CORR-4, CORR-5, M9 partial)

• Fix compute_promotion_plan to pass project filter (CORR-4). Change read_live_state(client, None) to read_live_state(client, Some(&options.project)) in both compute_promotion_plan and validate_source_clean. Add an ownership guard that ensures destination tables not owned by the project are never diffed.

• Promote uses connect_and_migrate (CORR-5). Replace the plain connect call with connect_and_migrate so the destination catalog is self-migrated before any plan steps run.

• Promote executor carries desired state and connection string (CORR-5). Build the executor with .with_desired_state(dest_desired).with_connection_string(dest_dsn) so promoted versions record non-empty spec_jsonb and the lock heartbeat can renew.

• Test: promote_filters_destination_by_project. Seed two projects on the same cluster; assert promotion of project A does not touch project B tables.

• Test: promote_records_non_empty_spec_jsonb. Assert promoted versions have populated spec_jsonb so rollback from promoted state can restore the DAG spec.

D. Consumer View Catalog Symmetry & Status Drift (CORR-7, CORR-8)

• Fix ManageConsumerView drop arm to delete catalog row (CORR-7). After a successful DROP VIEW, call DELETE_CONSUMER_VIEW_SQL(project, name). Make the delete idempotent. Add a cleanup path in connect_and_migrate that removes orphaned rows.

• Fix status drift to count all three diff collections (CORR-8). Replace diff.deltas.len() with the sum across deltas, source_deltas, and consumer_deltas. Use diff.is_empty() for boolean drift. Add per-area counts to status --format json.

• Fix status --format json to include pg_version field (M15). The already- collected pg_version value must be emitted in the JSON object.

• Test: consumer_drop_deletes_catalog_row. Apply a consumer view, remove its file, apply again; assert the aqueduct.consumer_views row is gone.

• Test: status_counts_consumer_and_source_drift. Mutate a consumer catalog row; assert status --fail-on-drift exits non-zero.

E. Security Hardening (SEC-1, SEC-2, SEC-3)

• Extend plaintext password guard to keyword-value DSNs (SEC-1). Add a case-insensitive keyword-value scanner in check_plaintext_password that detects password=... (unquoted and quoted, any case). Share detection logic with redact_dsn. Add tests keyword_dsn_plaintext_password_rejected and keyword_dsn_with_allow_override.

• Fix read-only transaction setup (SEC-3). Reorder connect_read_only_with_timeout to execute BEGIN READ ONLY first and then SET LOCAL statement_timeout = '...'. Add a duration-suffix allowlist guard. Add test read_only_timeout_is_active.

• Enforce SQL trust boundary on consumer view bodies (SEC-2). Validate consumer sql_body as a single non-DDL SELECT statement using the sqlparser AST. Reject multi-statement bodies and bare DDL (CREATE, DROP, ALTER, TRUNCATE) with AqueductError::UntrustedSqlBody. Document hooks and source DDL explicitly as operator-trusted code in docs/security.md and README.md. Add validate_consumer_sql_is_single_select unit test.

F. CI/CD Reliability (CI-1, CI-2, CI-3, L8, L12)

• Fix composite action archive names to match release artifacts (CI-1). Replace the ${OS}-${ARCH} construction in .github/actions/plan/action.yml and .github/actions/apply/action.yml with the same platform-to-suffix mapping used by release.yml: linux-amd64, linux-arm64, macos-arm64, macos-amd64, windows-amd64. Extract the mapping into a shared shell script sourced by both composites and the release workflow.

• Fix action-smoke to invoke composite actions and correct migration path (CI-2). Move the smoke migration file to migrations/streams/event_count.sql. Add steps that invoke ./.github/actions/plan and ./.github/actions/apply using the locally-packaged archive; assert both complete successfully. Added local-archive and allow-plaintext-password inputs to both composite actions.

• Make docs CI blocking (CI-3). Remove continue-on-error: true from mdbook test. Remove the nonexistent export entry from the CLI reference loop. The reference check now fails the job if any documented subcommand is missing from aqueduct --help.

• Resolve Node24 actions workaround (L12). FORCE_JAVASCRIPT_ACTIONS_TO_NODE24 remains in release.yml pending native GitHub Actions Node 24 support. Tracked for removal in v0.15 once the ecosystem supports it natively.

G. Code Quality, Docs Truthfulness & Supply-Chain (ERG-2, DOC-1, ROAD-1, L1, L7, DEP-1, TEST-3)

• Fix destroy exit-code bypass (ERG-2). Replace eprintln! + std::process::exit(1) with anyhow::bail!("...") so missing --confirm flows through main's error handler and exits 2. Add exit-code test.

• Fix redact_dsn regex recompilation (L1). Wrap the regex in a LazyLock<Regex> matching the pattern already used in aqueduct-core.

• Fix CI env-var truthiness for JSON logging (L7). Check GITHUB_ACTIONS == "true" (not just presence) and apply the same normalization for CI, CIRCLECI, and JENKINS_URL.

• Update README, CHANGELOG, and ROADMAP to reflect v0.14.0 (DOC-1, ROAD-1). README status banner → 0.14.0. Workspace version → 0.14.0. CHANGELOG → v0.14.0 section added. ROADMAP header → v0.14.0 current.

• Track httpmock/async-std supply-chain risk (DEP-1). --ignore RUSTSEC-2025-0052 annotation is in ci.yml. serde_yaml = "0.9" added as workspace dependency for v0.16 YAML work. Tracking issue for httpmock replacement to be opened alongside the v0.14 PR.

• Add mock scheduler state (TEST-3). Added pgtrickle_mock.scheduler_state table. pause_scheduler inserts the node name; resume_scheduler deletes it. Catalog v6 migration creates the table. Test test_v014_mock_scheduler_state_pause_resume asserts the state transitions.

v0.14 release criteria.

• --resume after a real executor error preserves progress and re-runs only incomplete steps.
• LockLost is returned by the main executor when the heartbeat detects lock loss.
• Promotion reads only project-owned tables, uses connect_and_migrate, and records non-empty spec_jsonb.
• Consumer view drops delete catalog rows; status counts source and consumer drift.
• Composite plan/apply actions successfully download and unpack release archives on all five platforms.
• Action-smoke exercises composite actions end-to-end against a live PostgreSQL service.
• mdbook test is blocking; docs-lint fails on unknown subcommands.
• Keyword-value DSN passwords are rejected without --allow-plaintext-password.
• Read-only statement timeout applies inside the transaction.
• Consumer SQL bodies are validated as single SELECT statements.
• README, CHANGELOG, ROADMAP all reference v0.14.0 truth.
• All v0.13 tests continue to pass.

v0.15 — Execution Integrity, Architecture & Blue/Green Production

Target effort: 5–6 weeks. Status: Released. Builds on: v0.14 complete. Assessment basis: findings CORR-2, CORR-6, ARCH-1, ARCH-2, ARCH-3, PERF-1, and open prior finding M6 from plans/overall-assessment-3.md.

This version closes the two largest architectural debt items: making multi-step execution safe under failures using a saga-style compensating-step pattern, and turning blue/green into a durable deployment system with atomically-tracked catalog rows. It also implements real RLS policy preservation, a configurable catalog schema abstraction, and typed executor contexts that prevent future commands from accidentally bypassing safety properties.

Phase 18 — Execution Integrity, Architecture & Blue/Green Production (5–6 weeks)

A. Saga-Style Transactional Execution (CORR-2)

• Introduce catalog-bounded transaction groups. Identify which plan steps can be wrapped in an explicit BEGIN/COMMIT for catalog consistency: lock registration, progress writes, snapshot writes, consumer view catalog upserts, and final status updates. Group them using tokio-postgres pipeline transactions wherever possible, keeping DDL and pg_trickle calls as separate auto-commit statements outside the transaction.

• Implement compensating-step registry for non-transactional DDL. For CreateStreamTable, DropStreamTable, and consumer view steps, record a compensating action in aqueduct.ddl_log at the start of each step. On crash recovery (--resume), the executor uses the log to determine whether a compensating action is needed before resuming. Add integration tests for each compensating-step case.

• Expose --force-retry <step-index> and --force-skip <step-index> on apply. Allow operators to advance past or retry a specific ambiguous step. Require --yes confirmation. Surface both flags in the HA operations guide.

B. Typed Executor Contexts (ARCH-2)

• Replace the optional executor builder with ExecutionContext enum. Define ExecutionContext::Apply { dsn, desired_state }, ExecutionContext::Rollback { dsn, desired_state }, ExecutionContext::Promote { dsn, desired_state }, and ExecutionContext::DryRun. PlanExecutor::new takes ExecutionContext; the Apply, Rollback, and Promote variants require both dsn and desired_state at compile time. DryRun may omit them.

• Validate that no CLI command constructs a non-DryRun executor without all context fields. Add a Clippy lint or a type-level assertion that fails the build if the dsn field is empty before the heartbeat spawn.

C. Catalog Schema Abstraction (ARCH-1)

• Introduce CatalogSchema newtype and thread it through all catalog SQL. Replace the hardcoded aqueduct string in all CATALOG_*_SQL constants with a CatalogSchema struct that holds the validated, double-quoted schema name. Generate SQL at runtime. init.rs uses args.schema when provided. connect_and_migrate reads catalog_schema from ProjectConfig.

• Validate catalog schema name at parse time. Reject names containing --, ;, $, or non-identifier characters. Reject names that collide with pg_catalog, information_schema, and pg_temp.

• Multi-schema integration test. Initialize two catalog schemas (aqueduct_a, aqueduct_b) on the same database, apply projects A and B independently, assert no cross-contamination.

D. Real RLS Policy Capture and Restore (CORR-6)

• Query pg_policies before any DropStreamTable step. The planner queries pg_policies and pg_class.relrowsecurity before emitting a DropStreamTable step. Serialize real CREATE POLICY ... ON ... USING (...) WITH CHECK (...) and ALTER TABLE ... ENABLE ROW LEVEL SECURITY statements into RecreatePolicy.policy_sql.

• Execute real policy DDL in RecreatePolicy executor step. Remove the comment-only placeholder. Execute each captured statement after the table is recreated. Record failures in aqueduct.ddl_log as non-fatal diagnostics.

• Test: rls_policies_restored_after_rebuild. Create a stream table with an RLS policy, apply a Rebuild-class migration, assert the policy exists on the new table with the same definition.

• Linter rule for RLS tables. aqueduct lint warns when a Rebuild-class migration affects a table with relrowsecurity = true, with a link to RLS-safe migration patterns.

E. Blue/Green Production State Machine (ARCH-3)

• Write aqueduct.blue_green_deployments at deployment start. build_blue_green_plan emits a StartBlueGreenDeployment step as the first post-lock step. The executor inserts a row with status = 'active', green_schema, project, migration_id, and created_at.

• Wrap all SwapConsumerViews in a single transaction. Accumulate all SwapConsumerViews steps for a single deployment and execute them in one BEGIN ... COMMIT block. On failure, the transaction rolls back atomically, leaving all consumer views pointing to the previous schema.

• Write status = 'swapped' after successful swap and status = 'retired' after RetireBlueSchema. Update aqueduct.blue_green_deployments at each state transition. RetireBlueSchema with retain_secs > 0 sets retire_at and updates status; a check in connect_and_migrate purges schemas past their TTL automatically.

• Rollback within TTL swaps back to blue. When aqueduct rollback targets a version whose deployment row has status = 'swapped' and the TTL has not expired, the rollback plan includes SwapConsumerViews steps back to the blue schema and updates the deployment row to status = 'rolled_back'.

• Batch WaitForConvergence into a single query (PERF-1). Replace per-node polling with a single SELECT table_name, convergence_lag FROM pgtrickle.pgt_stream_tables WHERE schema_name = $1 AND table_name = ANY($2) query. Compare in memory. Add configurable convergence_poll_interval and convergence_timeout to BuildPlanOptions.

• Tests: blue_green_swap_is_all_or_nothing, blue_green_deployment_row_lifecycle. Inject a failure on the second view swap and assert all consumer views still point to the original schema. Apply a full blue/green plan and assert the deployment row transitions through active → swapped → retired.

F. Validation Diagnostics Improvement (M6)

• Surface Diagnostic structs from the validate CLI command. Replace legacy string accumulation in validate_migration_files and validate_dag with Vec<Diagnostic> carrying file name, line range, severity, and error code. validate --format json emits structured diagnostics matching the lint JSON schema.

v0.15 release criteria.

• Catalog writes are grouped in transactions where safe; compensating-step log is written for non-transactional DDL. ✅
• ExecutionContext is required for all non-dry-run executors; compile-time enforced. ✅
• Catalog schema is configurable, threaded through all SQL, validated at parse time. ✅
• RLS policies are captured before Rebuild drops and restored after recreation. ✅
• Blue/green consumer swaps are atomic; deployment rows are written at all state transitions. ✅
• WaitForConvergence issues a single batch query per poll interval. ✅
• All v0.14 tests continue to pass. ✅

v0.16 — CLI Quality, Test Infrastructure & PostgreSQL Compatibility

Target effort: 4–5 weeks. Builds on: v0.15 complete. Assessment basis: findings ERG-1, ERG-3, ERG-4, TEST-2, PERF-2, and open prior findings H10, M3, M11, L2, L3, L14; missing tests 13–15, 18, 20 from plans/overall-assessment-3.md. Status: ✅ Released as v0.16.0 (2026-05-20).

This version brings the CLI to the quality bar required for scripting and automation: global output modes work, YAML serialization is correct, the API reference is generated from clap rather than hand-maintained, binary tests cover all exit codes and output formats, and CI gains the PostgreSQL version matrix needed to claim broad compatibility.

Phase 19 — CLI Quality, Test Infrastructure & PostgreSQL Compatibility (4–5 weeks)

A. Output Mode Emitter (ERG-1, M11)

• Introduce OutputMode enum and OutputEmitter struct. OutputMode has variants Human, Json, Yaml, Quiet, and Porcelain. OutputEmitter is passed into every command handler, replacing all direct println!/eprintln! calls for normal output. In Quiet mode, info-level output is suppressed. In Porcelain mode, only structured key=value lines are emitted. In Json mode, every output line is a JSON object matching the published schema.

• Wire --quiet, --porcelain, and --log-format json through OutputEmitter. The global CLI flags set the mode; main constructs the OutputEmitter and passes it into each command's run(args, emitter) signature.

• Tests: quiet_suppresses_decorative_output, porcelain_outputs_key_value_only. Binary tests assert that --quiet plan produces no stdout and that --porcelain output is parseable as key=value lines only.

B. YAML Serialization (ERG-3)

• Add serde_yaml workspace dependency and replace hand-built YAML in all commands. Remove the format!("project: \"{}\"", ...) pattern from plan.rs, status.rs, and diff.rs. Use serde_yaml::to_string(&output_struct). Derive Serialize on all output structs already used for JSON.

• Test: yaml_escapes_quotes_and_newlines. Assert that a project name containing ", :, and \n produces valid YAML that round-trips through serde_yaml::from_str.

C. Generated API Reference (ERG-4, M3)

• Generate docs/api-reference.md from aqueduct --help in CI. Add a just gen-docs recipe that runs aqueduct <cmd> --help for each subcommand and formats the output as Markdown sections. Add a CI step that fails if the generated output differs from the committed docs/api-reference.md.

• Correct stale flags and exit-code semantics. Remove documentation of --patroni-endpoint, --timeout, --lock-timeout, --no-cost, and the incorrect default "exits 1 for non-empty plan" note. Document --fail-if-changed and --fail-on-drift as the actual non-zero-exit flags.

• Align docs/security.md with implementation. Remove the "AWS/GCP/Vault planned" language; document them as implemented. Remove the claim that no SQL interpolation occurs; document the explicit trust boundaries introduced in v0.14.

D. Binary CLI Test Coverage (TEST-2)

• Add assert_cmd tests for every subcommand's success and error paths. Minimum coverage per command:

  • plan: no-connection error, empty plan, non-empty plan, --fail-if-changed exit 1, --format json schema validation.
  • apply: --yes --dry-run exits 0, missing DSN exits 2, stale --plan exits non-zero.
  • status: JSON output schema, --fail-on-drift exits non-zero on drift, YAML parses.
  • diff: --fail-on-drift exits 1 with drift, JSON schema.
  • destroy: missing --confirm exits 2, --dry-run exits 0.
  • rollback: --dry-run exits 0.
  • validate: fails on IVM-unsupported query, exits 0 on valid files.

• Test: validate_differential_ivm_unsupportable_fails. Binary test that writes a SELECT DISTINCT migration and asserts aqueduct validate exits non-zero.

E. Status Watch Performance & Formatter Fixes (PERF-2, L2, L3)

• Cache desired state by spec hash in status --watch (PERF-2). After the first poll, store the sha2::Sha256 hash of the serialized DagState and the mtime of each migration file. Only reload migration files when any mtime is newer. Keep reconnecting for live state. Add a --poll-interval flag to StatusArgs.

• Surface fmt read errors (L2). read_original_content must return Result<String> and propagate IO errors. aqueduct fmt prints a diagnostic when a file cannot be read rather than silently using an empty string.

• Fix CANONICAL_KEY_ORDER purpose (L3). Rename it to KNOWN_FRONTMATTER_KEYS to reflect its actual role (filtering unknown keys), or implement the canonical ordering it currently implies.

• Restrict TestDb.connection_string visibility to pub(crate) (L14). Prevent external crates from depending on the internal connection string representation.

F. PostgreSQL Version Matrix & Developer Experience (H10, backlog-11)

• CI targets PostgreSQL 18+ exclusively. pg_trickle requires PostgreSQL 18+. The integration CI job uses pg-version: ["18"] and postgres:18-alpine images. Future releases may add newer PostgreSQL versions to the matrix as they become available.

• Test: postgres_version_matrix_min_supported. Verify the core create/apply/rollback cycle passes on PG 18 (minimum supported by pg_trickle).

• Add CONTRIBUTING.md. Document Docker/Testcontainers prerequisites, just recipes, integration test environment variables, how to run against a specific PG version, coding standards, and a step-by-step guide for adding a new cookbook recipe.

v0.16 release criteria. ✅ All met.

• ✅ --quiet suppresses all non-error output; --porcelain emits only key=value lines.
• ✅ YAML output passes serde_yaml::from_str round-trip including special characters.
• ✅ docs/api-reference.md is generated by CI; drift fails the build.
• ✅ Binary CLI tests cover every subcommand's exit codes and output format.
• ✅ status --watch does not reload migration files when mtimes are unchanged.
• ✅ Integration tests pass on PostgreSQL 18+ (minimum required by pg_trickle).
• ✅ CONTRIBUTING.md exists and is accurate.
• ✅ All v0.15 tests continue to pass.

v0.17 — HA Operations, Documentation Truthfulness & Observability

Target effort: 4–5 weeks. Builds on: v0.16 complete. Assessment basis: finding DOC-2, backlog item 15 (HA failover design), competitive analysis observability gaps; all remaining open prior findings from plans/overall-assessment-3.md.

This version implements the HA operations features the documentation has been claiming for several releases, adds structured per-step observability, aligns all public-facing docs with the shipped implementation, publishes core crates, and closes the final competitive gaps identified in the Phase 3 assessment. After this version, pg_aqueduct is ready for the v1.0 release gate.

Phase 20 — HA Operations, Documentation Truthfulness & Observability (4–5 weeks)

A. Real HA Failover Detection (DOC-2, backlog-15)

• Implement --patroni-endpoint on aqueduct apply (DOC-2). Add patroni_endpoint: Option<Url> to ApplyArgs and TargetConfig. Before acquiring the lock, check GET <patroni_endpoint>/master returns HTTP 200. Between each plan step, re-check the endpoint. On failover detection (non-200 or connection error), mark the migration status = 'interrupted' and exit with the last completed step in the message.

• Add status = 'interrupted' to the migration status constraint. Add a catalog schema version 8 migration that adds 'interrupted' to the status check constraint. Document the recovery workflow in docs/ha-operations.md: reconnect to the new primary and run aqueduct apply --resume.

• Between-step primary re-check via pg_is_in_recovery(). After each plan step, execute SELECT pg_is_in_recovery(). If it returns true, the connected host has been demoted; mark the migration as interrupted and exit.

• Implement detect_ha_backend(). Detect Patroni via --patroni-endpoint, CloudNativePG via the app.cnpg.cluster_name GUC, and Stolon via application_name LIKE 'stolon-keeper%'. Surface the result in aqueduct status --verbose.

• Update docs/ha-operations.md to match the implementation. Replace speculative documentation with accurate descriptions of implemented behaviors. Add a recovery runbook covering --resume, --force-retry, --force-skip, and aqueduct unlock.

B. Structured Per-Step Observability (competitive moat)

• Emit per-step JSON events to stderr. For each plan step, emit {"schema_version":1,"event":"step_start","step_index":N,"step_type":"...","migration_id":...} at start and {"event":"step_complete","duration_ms":N,...} at finish. Add these event types to docs/cli-events-schema.json and validate them in CI.

• Complete aqueduct.migration_steps tracking. Update each row's status, finished_at, and error_message at step end. Expose a SQL view aqueduct.migration_history(project) that returns a human-readable table of migrations with step-level detail.

• Add aqueduct audit subcommand. Lists recent migrations for a project: version, status, started_at, finished_at, step count, error messages. Supports --format json, --format yaml, --format table. Orders by started_at DESC, default limit 20.

• Prometheus metrics endpoint (feature-gated). Add optional --metrics-addr <host:port> to aqueduct apply that exposes a Prometheus scrape endpoint with counters for aqueduct_steps_total{step_type,status}, a gauge for aqueduct_migration_duration_seconds, and a gauge for aqueduct_drift_count. Compile under --features metrics; excluded from the default binary.

C. Release Hygiene & crates.io Publishing

• Publish aqueduct-core and aqueduct-testkit to crates.io. Add publish = true to both Cargo.toml files; mark aqueduct-cli as publish = false. Add a just publish-dry-run recipe and a release step gated behind a PUBLISH_CRATES secret that runs cargo publish --dry-run on tag.

• Add SLSA build provenance to release pipeline. Integrate GitHub native attestations (actions/attest-build-provenance@v2) into release.yml to produce build provenance alongside the SHA256SUMS. Update docs/installation.md with provenance verification steps.

• Release-verify CI job. Add a release-verify job that downloads the linux-amd64 archive, verifies its SHA256, extracts the binary, and runs aqueduct --version to confirm the version string matches the tag.

D. Documentation Truthfulness & Version Linting (ROAD-1 final)

• Audit and finalize the ROADMAP. Mark every completed checklist item [x]. Replace the header status line with the true current version. Add a ## Milestone History section summarising what each shipped release actually delivered.

• Ensure CHANGELOG accurately describes every released version. Add v0.17 entry. Remove any "planned" language from entries for shipped versions.

• Add version linting to CI. A CI step checks that the README.md status banner, docs/installation.md version examples, and the Cargo.toml workspace version all agree. Fail the build if they diverge.

• Replace httpmock with an actively-maintained alternative. Replace httpmock with wiremock in aqueduct-core dev-dependencies. Remove the --ignore RUSTSEC-2025-0052 annotation from CI.

v0.17 release criteria.

• --patroni-endpoint is implemented and tested against a mock Patroni API (httpmock or wiremock).
• status = 'interrupted' is set on detected HA failover, with a test.
• --force-retry and --force-skip are implemented and tested.
• Per-step JSON events are emitted and validated against the published schema.
• aqueduct audit is implemented with JSON/YAML/table output.
• aqueduct-core and aqueduct-testkit publish successfully via dry-run.
• SLSA provenance is attached to release artifacts; install verification CI job passes.
• README, docs/installation.md, and Cargo.toml workspace version all agree.
• ROADMAP and CHANGELOG contain no "planned" language for shipped versions.
• All v0.16 tests continue to pass.

v0.18 — Security Hardening, Documentation Correctness & Operational Quality

Target effort: 3–4 weeks. Builds on: v0.17 complete. Assessment basis: Phase 4 engineering assessment (plans/overall-assessment-4.md) — Milestone A (all critical/high security findings) + all low/medium hygiene and documentation correctness items. Every finding from the Phase 4 assessment that can be fixed in isolation — without structural changes to the executor or catalog — is resolved in this version.

After this version, pg_aqueduct has no open security findings, all user-facing integration files (GitLab CI template, pre-commit example, GitHub Action example workflows) reference the correct release version, and all CLI flags documented in the API reference exist and behave as described.

Phase 21 — Security Hardening, Documentation Correctness & Operational Quality (3–4 weeks)

A. Critical Security Fixes (SEC-1, SEC-2, SEC-3)

• Call validate_consumer_sql_is_single_select() in ManageConsumerView arm (SEC-1 — Critical). The function exists at validate.rs:173 and is unit-tested, but is never invoked in executor.rs. Add a call immediately before the CREATE OR REPLACE VIEW ... AS <body> execution in both the "create"/"alter" arm and inside SwapConsumerViews for each blue/green view body. Return AqueductError::UntrustedSqlBody (new variant) on rejection. Add an integration test consumer_sql_injection_rejected_at_apply that confirms a migration file containing injected DDL is rejected before any database call is made.

• Gate CNPG preview TLS behind CNPG_INSECURE_SKIP_VERIFY env var (SEC-2 — Critical). Replace the unconditional danger_accept_invalid_certs(true) call in preview.rs:518 with a runtime check: if CNPG_INSECURE_SKIP_VERIFY is set, emit a warn!("TLS certificate validation disabled by CNPG_INSECURE_SKIP_VERIFY") and build the insecure client; otherwise build a default (validating) client. Document the env var as a development-only escape hatch in docs/security.md. Add a test that confirms a mock endpoint with a self-signed certificate is rejected when the env var is unset.

• Validate Age identity_file via validate_secret_path() (SEC-3 — Critical). In secrets.rs, after reading identity_file from $SOPS_AGE_KEY_FILE or $AGE_KEY_FILE, call validate_secret_path(&identity_file)? and add a separate check that rejects strings starting with - (flag-injection guard). Both the key argument and the identity file are now consistently validated before being passed to the age subprocess. Add a test age_identity_file_traversal_rejected that sets AGE_KEY_FILE=../../etc/passwd and confirms an InvalidSecretPath error.