django-migration-psql

What this skill does

## When to use

- Creating a new Django migration
- Running `makemigrations` or `pgmakemigrations`
- Reviewing a PR that adds or modifies migrations
- Adding indexes, constraints, or models to the database

## Why this matters

A bad migration can lock a production table for minutes, block all reads/writes, or silently skip index creation on partitioned tables.

## Auto-generated migrations need splitting

`makemigrations` and `pgmakemigrations` bundle everything into one file: `CreateModel`, `AddIndex`, `AddConstraint`, sometimes across multiple tables. This is the default Django behavior and it violates every rule below.

After generating a migration, ALWAYS review it and split it:

1. Read the generated file and identify every operation
2. Group operations by concern:
   - `CreateModel` + `AddConstraint` for each new table → one migration per table
   - `AddIndex` per table → one migration per table
   - `AddIndex` on partitioned tables → two migrations (partition + parent)
   - `AlterField`, `AddField`, `RemoveField` for each table → one migration per table
3. Rewrite the generated file into separate migration files with correct dependencies
4. Delete the original auto-generated migration

When adding fields or indexes to an existing model, `makemigrations` may also bundle `AddIndex` for unrelated tables that had pending model changes. Always check for stowaways from other tables.

## Rule 1: separate indexes from model creation

`CreateModel` + `AddConstraint` = same migration (structural).
`AddIndex` = separate migration file (performance).

Django runs each migration inside a transaction (unless `atomic = False`). If an index operation fails, it rolls back everything, including the model creation. Splitting means a failed index doesn't prevent the table from existing. It also lets you `--fake` index migrations independently (see Rule 4).

### Bad

```python
# 0081_finding_group_daily_summary.py — DON'T DO THIS
class Migration(migrations.Migration):
    operations = [
        migrations.CreateModel(name="FindingGroupDailySummary", ...),
        migrations.AddIndex(model_name="findinggroupdailysummary", ...),  # separate this
        migrations.AddIndex(model_name="findinggroupdailysummary", ...),  # separate this
        migrations.AddConstraint(model_name="findinggroupdailysummary", ...),  # this is fine here
    ]
```

### Good

```python
# 0081_create_finding_group_daily_summary.py
class Migration(migrations.Migration):
    operations = [
        migrations.CreateModel(name="FindingGroupDailySummary", ...),
        # Constraints belong with the model — they define its integrity rules
        migrations.AddConstraint(model_name="findinggroupdailysummary", ...),  # unique
        migrations.AddConstraint(model_name="findinggroupdailysummary", ...),  # RLS
    ]

# 0082_finding_group_daily_summary_indexes.py
class Migration(migrations.Migration):
    dependencies = [("api", "0081_create_finding_group_daily_summary")]
    operations = [
        migrations.AddIndex(model_name="findinggroupdailysummary", ...),
        migrations.AddIndex(model_name="findinggroupdailysummary", ...),
        migrations.AddIndex(model_name="findinggroupdailysummary", ...),
    ]
```

Flag any migration with both `CreateModel` and `AddIndex` in `operations`.

## Rule 2: one table's indexes per migration

Each table's indexes must live in their own migration file. Never mix `AddIndex` for different `model_name` values in one migration.

If the index on table B fails, the rollback also drops the index on table A. The migration name gives no hint that it touches unrelated tables. You lose the ability to `--fake` one table's indexes without affecting the other.

### Bad

```python
# 0081_finding_group_daily_summary.py — DON'T DO THIS
class Migration(migrations.Migration):
    operations = [
        migrations.CreateModel(name="FindingGroupDailySummary", ...),
        migrations.AddIndex(model_name="findinggroupdailysummary", ...),  # table A
        migrations.AddIndex(model_name="resource", ...),                  # table B!
        migrations.AddIndex(model_name="resource", ...),                  # table B!
        migrations.AddIndex(model_name="finding", ...),                   # table C!
    ]
```

### Good

```python
# 0081_create_finding_group_daily_summary.py  — model + constraints
# 0082_finding_group_daily_summary_indexes.py — only FindingGroupDailySummary indexes
# 0083_resource_trigram_indexes.py            — only Resource indexes
# 0084_finding_check_index_partitions.py      — only Finding partition indexes (step 1)
# 0085_finding_check_index_parent.py          — only Finding parent index (step 2)
```

Name each migration file after the table it affects. A reviewer should know which table a migration touches without opening the file.

Flag any migration where `AddIndex` operations reference more than one `model_name`.

## Rule 3: partitioned table indexes require the two-step pattern

Tables `findings` and `resource_finding_mappings` are range-partitioned. Plain `AddIndex` only creates the index definition on the parent table. Postgres does NOT propagate it to existing partitions. New partitions inherit it, but all current data stays unindexed.

Use the helpers in `api.db_utils`.

### Step 1: create indexes on actual partitions

```python
# 0084_finding_check_index_partitions.py
from functools import partial
from django.db import migrations
from api.db_utils import create_index_on_partitions, drop_index_on_partitions


class Migration(migrations.Migration):
    atomic = False  # REQUIRED — CREATE INDEX CONCURRENTLY can't run inside a transaction

    dependencies = [("api", "0083_resource_trigram_indexes")]

    operations = [
        migrations.RunPython(
            partial(
                create_index_on_partitions,
                parent_table="findings",
                index_name="find_tenant_check_ins_idx",
                columns="tenant_id, check_id, inserted_at",
            ),
            reverse_code=partial(
                drop_index_on_partitions,
                parent_table="findings",
                index_name="find_tenant_check_ins_idx",
            ),
        )
    ]
```

Key details:
- `atomic = False` is mandatory. `CREATE INDEX CONCURRENTLY` cannot run inside a transaction.
- Always provide `reverse_code` using `drop_index_on_partitions` so rollbacks work.
- The default is `all_partitions=True`, which creates indexes on every partition CONCURRENTLY (no locks). This is the safe default.
- Do NOT use `all_partitions=False` unless you understand the consequence: Step 2's `AddIndex` on the parent will create indexes on the skipped partitions **with locks** (not CONCURRENTLY), because PostgreSQL fills in missing partition indexes inline during parent index creation.

### Step 2: register the index with Django

```python
# 0085_finding_check_index_parent.py
from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [("api", "0084_finding_check_index_partitions")]

    operations = [
        migrations.AddIndex(
            model_name="finding",
            index=models.Index(
                fields=["tenant_id", "check_id", "inserted_at"],
                name="find_tenant_check_ins_idx",
            ),
        ),
    ]
```

This second migration tells Django "this index exists" so it doesn't try to recreate it. New partitions created after this point inherit the index definition from the parent.

### Existing examples in the codebase

| Partition migration | Parent migration |
|---|---|
| `0020_findings_new_performance_indexes_partitions.py` | `0021_findings_new_performance_indexes_parent.py` |
| `0024_findings_uid_index_partitions.py` | `0025_findings_uid_index_parent.py` |
| `0028_findings_check_index_partitions.py` | `0029_findings_check_index_parent.py` |
| `0036_rfm_tenant_finding_index_partitions.py` | `0037_rfm_tenant_finding_index_parent.py` |

Flag any plain `AddIndex` on `finding` or `r