/django-migration-psql

name: django-migration-psql
description: >
  Reviews Django migration files for PostgreSQL best practices specific to Prowler.
  Trigger: When creating migrations, running makemigrations/pgmakemigrations, reviewing migration PRs,
  adding indexes or constraints to database tables, modifying existing migration files, or writing
  data backfill migrations. Always use this skill when you see AddIndex, CreateModel, AddConstraint,
  RunPython, bulk_create, bulk_update, or backfill operations in migration files.
license: Apache-2.0
metadata:
  author: prowler-cloud
  version: "1.1"
  scope: [api, root]
  auto_invoke:
    - "Creating or reviewing Django migrations"
    - "Adding indexes or constraints to database tables"
    - "Running makemigrations or pgmakemigrations"
    - "Writing data backfill or data migration"
allowed-tools: Read, Grep, Glob, Edit, Write, Bash

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

# 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

# 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

# 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

# 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. Post