atomic-3phase-ddl-scriptslisted
Install: claude install-skill CarlosCaPe/octorato
# Atomic 3-Phase DDL Scripts
## What
A script architecture that separates database modifications into three
distinct phases within a single SQL file:
| Phase | Purpose | Behavior on Failure |
|-------|---------|---------------------|
| Phase 1 | Pre-check, gap analysis, dry-run gate | RAISE EXCEPTION stops all |
| Phase 2 | Execute changes (single DO block) | Rolls back atomically |
| Phase 3 | Post-check, verify final state | Reports pass/fail counts |
## Why
Separating concerns makes scripts **auditable**, **safe**, and **debuggable**.
Phase 1 tells you what *will* happen before anything changes. Phase 2 does the
work atomically. Phase 3 proves it worked.
## How
```sql
-- Phase 1: Pre-check
DO $$
BEGIN
-- gap analysis: what needs doing?
-- dry-run gate: RAISE EXCEPTION if dry_run = true
END $$;
-- Phase 2: Execute (atomic DO block)
DO $$
BEGIN
-- all modifications inside ONE block
-- if any fails, everything rolls back
END $$;
-- Phase 3: Post-check
DO $$
BEGIN
-- verify every expected outcome
-- report confirmed/failed counts
END $$;
```
The key insight is that Phase 2 is a **single DO block**. If any statement inside
it fails, PostgreSQL rolls back the entire block -- you never get a half-applied
state.
## When to Use
- Any DDL change that touches multiple objects (columns + procedures)
- Changes that need auditable before/after evidence
- Scripts that will be deployed across environments (DEV, QA, PROD)
## Where We Used It
- ****: 4