Pilot Walkthrough — From Discovery to Verified Repair

How to use this page: work the sections in order (Steps 1 → 9). Each step lists the exact Rivet commands and the contracts they satisfy. For a one-page “what to run in what order” summary, start at Pilot guide (README).

This is the end-to-end pilot guide that exercises the full contract stack: discovery, plan/apply, prioritization, chunked extraction with checkpoint, partition-level reconcile, targeted repair, and the committed/verified progression boundary.

If you just want to export one table, start with Getting Started (covers both Postgres and MySQL). This walkthrough is for pilots preparing a real production rollout.

Contracts referenced below: PA1–PA8 plan/apply · CC1–CC10 cursor policy · PG1–PG8 progression · RC1–RC6 / RR1–RR8 reconcile / repair.

Prerequisites

• Postgres or MySQL you can reach (structured creds or a DATABASE_URL).
• rivet --version works.
• A writeable local path or an S3/GCS bucket for output.

The repo ships a docker-compose.yaml with both engines pre-seeded by dev/postgres/init.sql / dev/mysql/init.sql and the bench seed tool (cargo run --features dev-seed --bin seed — the seeder is gated behind the off-by-default dev-seed cargo feature, so a bare cargo run --bin seed errors). Follow along on that if you don’t have a source handy.

docker compose up -d postgres mysql
cargo run --features dev-seed --bin seed -- --target both   # postgres + mysql: fills users, orders, events, page_views, content_items, orders_coalesce
# orders_sparse is created and truncated but left EMPTY — add --sparse-chunk-demo to fill it
export DATABASE_URL='postgresql://rivet:rivet@localhost:5432/rivet'

For a bigger / richer fixture (14 tables, source-group conflict scenarios, composite cursor) use the dedicated demo fixture — see demo-quickstart.md.

Production note — TLS and credential handling

Everything below works with local-dev settings. For a real pilot against a managed database:

• TLS is on by default when you set tls:. The recommended shape:

  source:
    type: postgres
    url_env: DATABASE_URL
    tls:
      mode: verify-full
      ca_file: /etc/ssl/certs/rds-ca-2019-root.pem   # if your CA is not in system trust
  

  See reference/config.md § TLS for the full matrix (disable | require | verify-ca | verify-full). Omitting tls: is only allowed for loopback hosts (localhost / 127.0.0.0/8 / ::1), which connect in plaintext; against any remote (non-loopback) host rivet refuses the connection before any network I/O with a “TLS required” error. To opt into remote plaintext you must set tls: { mode: disable } explicitly.

• Never put the DB URL on the command line in prod. Use --source-env for rivet init:

  export DATABASE_URL='postgresql://…'
  rivet init --source-env DATABASE_URL --schema public --discover -o discovery.json
  

  And use url_env: / password_env: in YAML. See reference/init.md.

Step 1 — Discovery (rivet init)

Scaffold a YAML from the live schema and, in parallel, emit a machine-readable discovery artifact for review or automation.

# YAML scaffold for a whole schema
rivet init --source-env DATABASE_URL --schema public -o pilot.yaml

# JSON discovery artifact — per-table ranked cursor + chunk candidates,
# row estimate, on-disk size, coalesce hints when `updated_at` is nullable.
rivet init --source-env DATABASE_URL --schema public --discover -o discovery.json

Inspect discovery.json to decide modes and cursor policies:

jq '.tables[] | {table, suggested_mode, best_cursor: (.cursor_candidates[0].column // null),
                 coalesce_fallback: .suggested_cursor_fallback_column, notes}' discovery.json

Step 2 — Write a chunked + checkpoint config

For any non-trivial table, use chunked mode with chunk_checkpoint: true. Checkpointing is what unlocks reconcile, repair, and progression.

source:
  type: postgres
  url_env: DATABASE_URL
  tuning:
    profile: balanced

exports:
  - name: orders
    query: "SELECT id, user_id, product, price, status, updated_at FROM orders"
    mode: chunked
    chunk_column: id
    chunk_size: 100000
    chunk_checkpoint: true          # required for reconcile/repair/progression
    parallel: 2
    format: parquet
    destination:
      type: local
      path: ./output
    columns:
      price: decimal(10,2)          # bare NUMERIC needs an explicit precision/scale

  # Composite cursor fixture — `updated_at` is nullable, fall back to `created_at`.
  - name: orders_coalesce
    query: "SELECT id, product, price, updated_at, created_at FROM orders_coalesce"
    mode: incremental
    cursor_column: updated_at
    cursor_fallback_column: created_at
    incremental_cursor_mode: coalesce    # ADR-0007 CC1
    format: parquet
    skip_empty: true
    destination:
      type: local
      path: ./output
    columns:
      price: decimal(10,2)

Validate structural constraints:

rivet check -c pilot.yaml
rivet doctor -c pilot.yaml

Step 3 — Plan (see the full intent)

rivet plan seals the execution intent into an auditable artifact (ADR-0005 PA1) and embeds source-aware prioritization (ADR-0006) when multiple exports are planned.

rivet plan -c pilot.yaml

A single-export plan prints a Priority block; a multi-export plan adds a Campaign block with waves and source_group warnings (if set). A JSON artifact is what CI/CD pipelines should consume:

rivet plan -c pilot.yaml --format json -o plan.json

For a multi-export config, plan is read-only: it prints the recommended schedule and never touches your config. Pass --annotate-waves to write the wave: and parallel_safe: fields into the config in place (preserving your comments and field order) — visible, hand-editable, and consumed by rivet apply <config> in Step 4. The flag replaces the whole schedule with the plan’s recommendations, absent fields and hand-tuned ones alike, so review the printed schedule first. The plan suggests; you stay in control.

rivet plan -c pilot.yaml                   # review the schedule (read-only)
rivet plan -c pilot.yaml --annotate-waves  # then persist it into the config

What the plan guarantees (PA1–PA8):

• PA1 — the artifact is the sole input to apply.
• PA3 — apply bails on plans older than 24h (override with --force).
• PA4 — for incremental exports, apply bails if another run moved the cursor in the meantime.
• PA5 — chunk ranges in the artifact are monotonic by construction.

Step 4 — Run, or apply by wave

Three ways to execute, by how much orchestration you want.

Run live — straightforward, config order, no waves:

rivet run -c pilot.yaml --validate

Apply the whole config wave-by-wave — rivet plan --annotate-waves (Step 3) wrote a wave: onto each export; apply runs them lowest-wave first, with a barrier between waves. Exports with no wave: run last, as one implicit final wave — so a config you never annotated still applies, just in a single band. Tables are independent, so a failed export does not block its wave-mates: apply collects the failure, runs the rest, and exits non-zero. Add --parallel-export-processes to run the cheap (parallel_safe) exports within a wave concurrently — the heavy ones still run alone (they chunk-parallelize internally):

rivet apply pilot.yaml                          # wave-ordered, sequential
rivet apply pilot.yaml --parallel-export-processes   # + within-wave parallelism for cheap exports

Apply a sealed single-export artifact — the auditable split between “what will happen” and “do it”:

rivet apply plan.json

What happens under the hood for chunked:

• For each chunk task: SELECT ... WHERE id BETWEEN start AND end ORDER BY id → Arrow → Parquet → destination → manifest entry → chunk_task.status = 'completed'.
• Ordering: write → manifest → cursor → metric (ADR-0001 I1–I4).
• On success: last_committed_chunk_index advances in export_progression (PG2, PG4).

Step 5 — Inspect progression

Get the explicit committed / verified boundary per export:

rivet state progression -c pilot.yaml

EXPORT            COMM MODE    COMMITTED         COMMITTED AT             VERI MODE  VERIFIED
orders            chunked      chunk #9          2026-04-18 12:20:15 UTC  -          -
orders_coalesce   incremental  2026-04-18T00:05  2026-04-18 12:21:02 UTC  -          -

At this point:

• Committed = “data is at the destination and recorded in the manifest” (PG2).
• Verified is still empty — no reconcile has run yet (PG5).

Step 6 — Reconcile

The GIF above walks through the whole sequence — reconcile clean, simulated drift, targeted repair, and final state progression showing RR4 (committed unchanged by repair). Steps 6–8 below expand the same flow in prose.

Partition-level COUNT(*) on the source, compared with per-chunk rows_written stored in the checkpoint.

rivet reconcile -c pilot.yaml -e orders

Possible outcomes per partition (RC3):

• match — source and exported counts equal.
• mismatch — both counts known but differ → repair candidate.
• unknown — a count is missing (chunk never completed, unparseable keys) → repair candidate.

If every partition matches (zero mismatches and zero unknowns), last_verified_chunk_index advances (RC6 / PG5). Save a JSON report for audit:

rivet reconcile -c pilot.yaml -e orders --format json -o reconcile.json

The reconcile SQL uses exactly the same build_chunk_query_sql shape the pipeline used during extraction (RC2), so the comparison is apples-to-apples.

Step 7 — Targeted repair

If the reconcile report is dirty, derive a repair plan from it:

# Dry run — prints the plan, runs no queries, writes no files (RR2).
rivet repair -c pilot.yaml -e orders --report reconcile.json

# Execute just the flagged chunks.
rivet repair -c pilot.yaml -e orders --report reconcile.json --execute

What --execute does:

• Re-runs only the flagged chunk ranges via run_chunked_sequential(ChunkSource::Precomputed) — same SQL shape as extraction and reconcile (RR3).
• Writes new output files alongside originals with <export>_<ts>_chunk<idx>_<16-hex-nonce>.<ext> naming (e.g. orders_20260611_120000_chunk2_a1b2c3d4e5f6a7b8.parquet; the random nonce is what guarantees a repair part can never overwrite the original) — Rivet does not delete or overwrite prior files (RR5), but the manifest declares the replacement: the chunk’s original part(s) are marked superseded, so rivet load and rivet validate see each row once. The superseded files stay on disk until load.gc_orphans: true collects them. A warehouse that already loaded the original part keeps those rows unless it dedups by primary key.
• Leaves last_committed_* untouched (RR4) — the chunk index was already covered at the original run; repair is corrective, not commitment.

Step 8 — Re-verify

After repair, rerun reconcile to advance verified:

rivet reconcile -c pilot.yaml -e orders

rivet state progression -c pilot.yaml

EXPORT            COMM MODE    COMMITTED    COMMITTED AT             VERI MODE  VERIFIED
orders            chunked      chunk #9     2026-04-18 12:20:15 UTC  chunked    chunk #9
orders_coalesce   incremental  ...          ...                      -          -

Now both boundaries agree: everything committed is also verified against the source.

Step 9 — Automate

A minimal daily cron that runs, reconciles, and fails loudly on unresolved mismatches:

#!/usr/bin/env bash
set -euo pipefail
cd /opt/rivet && export DATABASE_URL='…'

rivet run       -c pilot.yaml --validate
rivet reconcile -c pilot.yaml -e orders --format json -o /var/log/rivet/reconcile-$(date +%F).json

# Fail the job if reconcile is not clean (zero mismatches AND zero unknowns).
if ! jq -e '.summary.mismatches == 0 and .summary.unknown == 0' \
      /var/log/rivet/reconcile-$(date +%F).json > /dev/null; then
  echo "reconcile dirty — see report"
  exit 1
fi

For CI-style review, use the plan/apply split:

# In CI (build stage)
rivet plan -c pilot.yaml --format json -o plan.json
# Review plan.json in a PR — prioritization block tells you what's heavy/risky.

# In CI (deploy stage)
rivet apply plan.json

Contract cheat sheet

What’s next

• Production checklist — hardening before real workloads.
• UAT checklist — pilot sign-off structure.
• Tuning — profiles, batch_size, memory-aware FETCH.
• Prioritization — reading and trusting the advisory block.