# DERA Database Migration Policy

## 1. Governance Principles

1. **Controlled Execution**: Schema migrations NEVER run automatically on API gateway startup. Migrations must be executed as explicit, isolated deployment steps.
2. **Review Autogeneration**: Autogenerated migrations produced by `alembic revision --autogenerate` are drafts that require human review and architectural validation.
3. **Reversible Downgrades**: Every migration file must implement both `upgrade()` and `downgrade()`.
4. **Deterministic Revision Graph**: Alembic must maintain a single head revision. Branching revisions in `migrations/versions/` are disallowed and blocked in CI.

---

## 2. Migration Risk Taxonomy

| Risk Tier | Migration Characteristics | Review Requirements |
| :--- | :--- | :--- |
| **Low Risk** | Adding new tables, adding nullable columns, non-unique indexes with concurrent builds. | Standard peer review. |
| **Medium Risk** | Adding NOT NULL columns with defaults, foreign keys, unique constraints on existing data. | Architectural review & query plan inspection. |
| **High Risk** | Dropping tables, dropping columns, altering column types, large-scale data rewrites. | Principal Architect approval, backup verification, and planned maintenance window. |

---

## 3. Standard Migration Commands

Generate a new migration:

```bash
alembic revision --autogenerate -m "describe_change"
```

Preview raw SQL without applying (offline mode):

```bash
alembic upgrade --sql base:head
```

Apply migrations to latest version:

```bash
alembic upgrade head
```

Roll back one revision:

```bash
alembic downgrade -1
```

Check current revision status:

```bash
alembic current
```