What I Built (In Plain English)

The fastest way for an autonomous AI agent—or a human engineer—to destroy a production company is by running a careless database migration that drops a column, resets a table, or overwrites live customer records during a deployment seed.

To make database-aware platforms 100% immune to data loss, I engineered a non-negotiable operational policy and 5-gate verification harness for Google Antigravity AI. Every schema change is strictly additive (Expand-and-Contract), every runtime seed is idempotent (CREATE IF NOT EXISTS), and every release must pass an automated 5-gate suite—including a full database snapshot diff and a live Migrate UP -> Rollback DOWN -> Re-Migrate UP round-trip test—before touching production.

Autonomous Agent CI/CD Pipeline Architecture

⚠️ Tech Ahead: The sections below cover expand-and-contract DDL invariants, idempotent runtime seeding guards, SHA-256 migration checksum immutability, 3-tier rollback synthesis, and the exact copy-paste directive for Google Antigravity AI.


Architectural Objectives

  1. Strictly Additive Schema Evolution (Expand-and-Contract): Zero DROP TABLE, DROP COLUMN, TRUNCATE, DELETE FROM, or un-defaulted NOT NULL columns in SQL migrations. Destructive ORM flags (db push, migrate reset, --force-reset, --accept-data-loss) are hard-blocked outside localhost ephemeral test DBs.
  2. Additive-Only Runtime Seeding: Production seeders and bootstrappers operate strictly under CREATE IF NOT EXISTS semantics—never calling .update() or .delete() on existing rows.
  3. 5-Gate Automated Verification (db:verify-safe & test:non-destructive): Every PR is gated by static DDL scanning, git state-transition diffs (origin/master -> HEAD), live full-DB row/column snapshot preservation, a 3-tier UP -> DOWN -> RE-UP rollback round-trip test, and mandatory pre-migration logical backups in CI/CD.

The Operational Prompt

## NON-DESTRUCTIVE DATABASE, MIGRATION & ROLLBACK POLICY (NON-NEGOTIABLE)

1. **Strictly Additive Schema Evolution (Expand-and-Contract)**:
   - Every schema migration, runtime seed, and production update MUST be 100% non-destructive.
   - NEVER include `DROP TABLE`, `DROP COLUMN`, `TRUNCATE`, `DELETE FROM`, or `ADD/MODIFY COLUMN ... NOT NULL` without a `DEFAULT` value in any SQL migration.
   - NEVER invoke destructive ORM commands (`db push`, `migrate reset`, `--force-reset`, `--accept-data-loss`) outside isolated local ephemeral test databases.

2. **Additive-Only Runtime Seeding**:
   - Runtime seeders/bootstrappers MUST strictly follow `CREATE IF NOT EXISTS` semantics.
   - NEVER overwrite, `.update()`, or `.delete()` existing records in runtime code.
   - Dev/CI reset seeders MUST verify the DB host is `localhost`/`127.0.0.1` AND require explicit `ALLOW_DESTRUCTIVE_SEED=true`.

3. **5-Gate Automated Verification (`db:verify-safe` & `test:non-destructive`)**:
   - **Static DDL & Seed Audit**: Scan all migration files, ORM schemas, and seeders for destructive statements.
   - **Git State-Transition Diff (`origin/master -> HEAD`)**: Verify zero deleted models/tables, zero deleted fields/columns, zero incompatible column type mutations, and 100% SHA-256 checksum immutability of already-published migrations.
   - **Live Full-DB Snapshot Preservation Test**: Snapshot every row and column across all tables before migration/seeding, execute the transition, and assert 100% of pre-existing rows and field values remain identical.
   - **3-Tier Rollback Planner & `UP -> DOWN -> RE-UP` Round-Trip Test**: Synthesize compensating DOWN SQL for every migration, verify Tier-1 Code-Only Rollback compatibility, and test the live `Migrate UP -> Rollback DOWN -> Re-Migrate UP` cycle against a real test database in CI.
   - **Pre-Migration Backup in CI/CD**: Always capture a compressed logical database dump immediately before running production migrations.

The 5-Gate Verification Matrix

Gate Verification Mechanism Invariant Enforced
Gate 1: Static DDL & Seed Audit AST / regex scan of SQL migrations, ORM schemas, and seed scripts. Zero DROP, TRUNCATE, DELETE FROM, or destructive ORM CLI flags.
Gate 2: Git State-Transition Diff Diff origin/master -> HEAD across schema models and migration folders. Zero removed tables/columns, zero incompatible type changes, 100% SHA-256 checksum immutability.
Gate 3: Full-DB Snapshot Preservation Deep row-and-column snapshot before and after migration + runtime seed. 100% of pre-existing records and field values remain byte-for-byte identical.
Gate 4: 3-Tier Rollback Round-Trip Synthesize compensating DOWN SQL and execute UP -> DOWN -> RE-UP. Verifies Tier-1 code-only rollback safety and lossless schema reversibility.
Gate 5: Pre-Migration CI/CD Backup Automated compressed logical dump (pg_dump / mysqldump / snapshot) pre-deploy. Instant point-in-time recovery artifact captured prior to production migration execution.
— FINIS • PRINTED IN THE ZÜRICH ARCHIVE —
NR

Nuno Ribeiro

Head of Cloud, Systems Architect (C++, Rust, TypeScript), and visual artist based in Zürich. Writing on distributed architectures, low-latency execution pipelines, and visual discipline.