Neon Postgres: Schema Versioning Without Tears

Executive Summary

A pragmatic approach to migrations, compatibility, and rollbacks—especially when multiple apps share a DB.

Schema drift is the silent killer of multi-app ecosystems. The database doesn’t care about your intentions—it cares about columns and constraints.

Versioned migrations and compatibility contracts let multiple apps evolve without ‘surprise 500s’.

The Core Idea

Most “AI failures” are system failures: missing contracts, missing logs, missing ownership lines. Fix the system, and the model suddenly looks smarter.

-- Versioned migrations table
CREATE TABLE IF NOT EXISTS schema_migrations (
  version TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

That snippet is not a complete app. It’s a reminder: your system should prefer verifiable facts over narrative.

Failure Modes You’ll Actually See

• Breaking changes

  Dropping columns or changing types without compatibility windows breaks older clients.

• No migration ordering

  Migrations must be linear, repeatable, and tracked.

• Missing constraints

  Constraints encode reality. Without them, you store lies.

• Local vs prod mismatch

  Schema must be reproducible from scratch, not ‘it works on my DB’.

Implementation Notes

Migrations are artifacts. Track them, apply them in order, and enforce them in CI.

Avoid destructive changes without a compatibility window (add new column, backfill, switch, then drop).

Expose schema version via /health and record it in your gateway logs.

For architecture and rollout planning, use the Contact Hub.

Ship‑Ready Checklist

Use this as a pre‑deploy gate. If you can’t check these boxes, don’t pretend you’re “done.”

- A single source of truth for versions (prompt/policy/schema) and a way to display them in-app.
- Request correlation ID visible in UI, logged server-side, and searchable.
- Explicit failure UX: what happened, why, and a safe next step.
- An audit trail you can replay: inputs, decisions, outputs, and cost facts.
- A small test harness (even 20 cases) that runs before deployment.

Further Reading

External references (full links):

• Neon Postgres docs
• PostgreSQL constraints
• Database migrations (Flyway)

Related Reads in This Series

• AI Gateways: One Contract to Rule Multi‑Provider Models
• Shipping Like a Scientist: Experiment Loops for Product Teams
• The Hidden Cost of Context: Prompt Hygiene for Production