Schema Dump or Live Catalog: Two Positions on AI SQL Review Inputs

Consider this incident pattern, which database teams keep rediscovering when review becomes cheap. A staging migration added a unique constraint that production already enforced through a partial index created during an incident. The AI reviewer approved the file because its schema dump was eleven days old and lacked that index. The deploy then failed overnight when CREATE UNIQUE INDEX validated rows the partial index had deliberately excluded.

That failure is not a model-quality story so much as an input-freshness and catalog-visibility story. Cheap generation changes the failure mode for schema work more than it changes the surface syntax of SQL. When migrations and rewrites are inexpensive to draft, stale context becomes the scarce resource rather than token supply. The practical question for SQL review is therefore narrower than model choice: should the reviewer read a dated dump file or the live catalog?

Community conversation in late August and early September 2026 has asked what happens to technical debt when AI makes code cheap. Schema objects follow the same curve, because a fluent migration file can still collide with indexes created outside Git. That discussion is a topic signal, not a measured benchmark. The rest of this article treats dump versus catalog as an evidence problem you can replay.

Two credible positions

Position A: review only a schema dump on isolated compute

Position A treats the dump as an artifact with a checksum, a clock, and no production credentials. pg_dump --schema-only produces a replayable input that an ephemeral server can inspect without reaching the cluster. Reviewers can pin a Git blob, fail a job when the dump is older than a policy window, and rerun the same prompt against the same bytes. That property matters when a finding is later disputed, because the input is not a moving catalog.

The evidence for this position is operational rather than rhetorical. Dump review cannot leak live row samples, cannot take out locks, and cannot be confused by a replica that lagged during the check. It also matches migration review, which is itself a file-based workflow with diffs and hashes. If the model only sees CREATE statements, it cannot invent statistics-based index advice that operators cannot reproduce from the pull request.

Position B: review the live catalog, preferably on a replica

Position B argues that dumps omit the facts that make SQL advice true or false in production. Invalid indexes, unlogged tables, replica identity settings, and last-analyze times live in pg_catalog, not in a migration folder. A dump that is even a few hours old can miss a hotfix index, a failed CREATE INDEX CONCURRENTLY, or a constraint created by an operations script. Text-only review then endorses a second object that collides with reality.

The evidence here is catalog-shaped rather than stylistic. pg_index.indisvalid is not present in a typical schema dump in a form models reliably parse. Privilege drift, default privileges, and sequence ownership often survive as comments or disappear entirely. Teams that already keep a read replica can grant a tightly scoped reader and get current object truth without touching the primary. The cost is credential handling, network policy, and a catalog that can change while the prompt still runs.

A reproducible artifact: freshness tripwire plus catalog pack

The following workflow is a proposal you can run in a lab cluster. It does not claim production metrics, model accuracy, or runtime cost. Label it unexecuted against your own service until you capture hashes and timestamps locally.

1. Capture a dump with an age manifest

#!/usr/bin/env bash
set -euo pipefail
# Lab-only: point REVIEW_DATABASE_URL at a disposable database.
stamp="$(date -u +%Y%m%dT%H%M%SZ)"
out_dir="${DUMP_DIR:-./schema-dumps}"
mkdir -p "$out_dir"
dump_file="${out_dir}/schema_${stamp}.sql"
manifest="${out_dir}/schema_${stamp}.manifest"

pg_dump --schema-only --no-owner --no-privileges \
  --file "$dump_file" \
  "$REVIEW_DATABASE_URL"

{
  echo "captured_at_utc=${stamp}"
  echo "sha256=$(sha256sum "$dump_file" | awk '{print $1}')"
  echo "bytes=$(wc -c < "$dump_file" | tr -d ' ')"
} > "$manifest"

echo "wrote $dump_file"
echo "wrote $manifest"

2. Fail the job when the dump is older than policy

#!/usr/bin/env python3
"""Fail if a schema dump manifest is older than max_age_hours."""
from __future__ import annotations

import argparse
from datetime import datetime, timezone
from pathlib import Path


def parse_manifest(path: Path) -> dict[str, str]:
    data: dict[str, str] = {}
    for raw in path.read_text(encoding="utf-8").splitlines():
        if "=" not in raw:
            continue
        key, value = raw.split("=", 1)
        data[key.strip()] = value.strip()
    return data


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("manifest")
    parser.add_argument("--max-age-hours", type=float, default=6.0)
    args = parser.parse_args()

    payload = parse_manifest(Path(args.manifest))
    captured = datetime.strptime(payload["captured_at_utc"], "%Y%m%dT%H%M%SZ")
    captured = captured.replace(tzinfo=timezone.utc)
    age_hours = (datetime.now(timezone.utc) - captured).total_seconds() / 3600
    if age_hours > args.max_age_hours:
        print(
            f"FAIL dump age {age_hours:.2f}h exceeds {args.max_age_hours}h"
        )
        return 2
    print(f"PASS dump age {age_hours:.2f}h within policy")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

3. Optional catalog pack for replica-only checks

Run this pack only against a replica or a lab primary with a read-only role. Do not place production URLs on a shared reviewer host, even when the queries look harmless.

-- catalog_pack.sql
-- 1) Invalid or not-ready indexes left by concurrent builds
SELECT indexrelid::regclass AS index_name,
       indisvalid,
       indisready,
       indisunique
FROM pg_index
WHERE NOT indisvalid OR NOT indisready
ORDER BY 1;

-- 2) Constraints that exist in the catalog but may be missing from an old dump
SELECT conrelid::regclass AS table_name,
       conname,
       contype
FROM pg_constraint
WHERE contype IN ('u', 'p', 'f', 'c')
ORDER BY 1, 2;

-- 3) Persistence and analyze timestamps that dumps under-specify
SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       c.relpersistence,
       c.relkind,
       s.last_analyze,
       s.last_autoanalyze
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_user_tables s
  ON s.relid = c.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND c.relkind IN ('r', 'p', 'm')
ORDER BY 1, 2;

4. Decision table operators can copy

Check type	Dump is enough	Live catalog required	Keep off shared reviewer hosts
Naming, comments, and migration text	Yes	No	Secrets in comments
Duplicate constraint versus an existing unique index	Only if dump age is inside the hotfix cycle	Yes, if hotfixes land outside Git	Production URLs
Invalid CREATE INDEX CONCURRENTLY leftovers	No	Yes	Primary write paths
Statistics-dependent rewrite advice	No	Replica pg_stat only	Row samples
Privilege and default-privilege drift	Weak	Yes	Superuser roles
Lock-risk DDL already present in the file	File plus static rules	Confirm current lock contention on a replica	Session killers

Numbered workflow that keeps both positions honest

1. Classify each finding as file-true or catalog-true before any model sees the prompt.
2. Export a schema dump and manifest from a non-production source of truth, then hash the file.
3. Reject the review job when dump age exceeds the policy window encoded in the Python check.
4. Send dump text to the model only for file-true questions such as naming, comment coverage, and obvious Cartesian joins.
5. If a finding needs catalog-true evidence, run catalog_pack.sql on a replica with a read-only role.
6. Attach the dump hash or the catalog query output to the review record so later disputes can replay the input.
7. Block merge when dump and catalog disagree on objects the migration would recreate, even if the model sounds confident.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. Dump-side jobs that need no cluster credentials can run against MonkeyCode's free model access and free server option, which is the only product role in this workflow. Catalog-true checks still belong on infrastructure you control, because a free server should never hold production connection strings. Remove the product from the pipeline and the same dump-age tripwire plus catalog pack still function.

Limitations and who should not use this approach

This approach does not measure model accuracy, token spend, or query latency, and it does not recommend EXPLAIN ANALYZE on production traffic. Schema dumps can omit grants, publications, subscription state, and some extension internals that still affect runtime. Live catalog reads can race a migration, and replica lag can present a catalog that the primary has already left behind. The Python age check only proves clock freshness; it does not prove the dump came from the cluster you intend to change.

Do not use shared free compute for catalog-true review if that host would receive a production URL, a superuser role, or dumps that contain comments with secrets. Regulated environments should treat schema dumps as potentially sensitive even without row data, because object names reveal system design. Teams without a replica should not pretend a lab dump is live truth. Operators who need lock-safe DDL still need a separate control for lock timeouts, which is a different mechanism than dump freshness.

The decision rule

Use dump-only AI review when the question is whether a file is internally consistent, and the dump hash is younger than your hotfix cycle. Move to replica catalog review when the question is whether the file collides with objects that Git does not own. If dump and catalog disagree, trust the catalog for existence and validity, and trust the dump for what the pull request actually contains. That split keeps cheap generation from turning stale context into production unique-index failures.

If schema dumps already stay off production credentials, the freshness tripwire is a reasonable overnight job on free model access and a free server rather than a reason to widen database reach.