refactor(skills): compress specialist skill contracts

- distill specialist skills into compact runtime contracts
- preserve critical compliance, migration, indexing, and query rules
- replace oversized handbook sections with local operational references

skills/django-migration-psql/SKILL.md

@@ -1,11 +1,6 @@
 ---
 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.
+description: "Trigger: When creating, reviewing, or splitting Django/PostgreSQL migrations with AddIndex, CreateModel, AddConstraint, RunPython, or backfill logic. Enforces Prowler-safe migration structure for indexes, partitioned tables, and large data moves."
 license: Apache-2.0
 metadata:
   author: prowler-cloud
@@ -19,436 +14,55 @@ metadata:
 allowed-tools: Read, Grep, Glob, Edit, Write, Bash
 ---
 
-## When to use
+## Activation Contract
 
-- 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
+Use this skill when a migration changes schema, indexes, partitioned tables, or existing data in the Prowler API database.
 
-## Why this matters
+## Hard Rules
 
-A bad migration can lock a production table for minutes, block all reads/writes, or silently skip index creation on partitioned tables.
+- Never trust auto-generated migrations as-is; review and split them by concern and table.
+- Keep `CreateModel` plus integrity constraints together, but move every `AddIndex` into separate migration files.
+- Never mix indexes for multiple tables in one migration.
+- `finding` and `resourcefindingmapping` partitioned indexes require the two-step pattern: partition creation first, parent `AddIndex` second.
+- Partition index creation with `RunPython(create_index_on_partitions, ...)` MUST use `atomic = False` and include `reverse_code`.
+- Large backfills must dispatch Celery work; do not iterate millions of rows inside the migration transaction.
+- Inline backfills are allowed only for trivial, single-statement updates on small result sets.
+- Backfill tasks must batch writes, use one `rls_transaction()` per batch, and avoid `.save()` loops.
 
-## Auto-generated migrations need splitting
+## Decision Gates
 
-`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 |
+| Question | Action |
 |---|---|
-| `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 `resourcefindingmapping` without a preceding partition migration.
-
-## Rule 4: large table indexes — fake the migration, apply manually
-
-For huge tables (findings has millions of rows), even `CREATE INDEX CONCURRENTLY` can take minutes and consume significant I/O. In production, you may want to decouple the migration from the actual index creation.
-
-### Procedure
-
-1. Write the migration normally following the two-step pattern above.
-
-2. Fake the migration so Django marks it as applied without executing it:
-
-```bash
-python manage.py migrate api 0084_finding_check_index_partitions --fake
-python manage.py migrate api 0085_finding_check_index_parent --fake
-```
-
-3. Create the index manually during a low-traffic window via `psql` or `python manage.py dbshell --database admin`:
-
-```sql
--- For each partition you care about:
-CREATE INDEX CONCURRENTLY IF NOT EXISTS findings_2026_jan_find_tenant_check_ins_idx
-    ON findings_2026_jan USING BTREE (tenant_id, check_id, inserted_at);
-
-CREATE INDEX CONCURRENTLY IF NOT EXISTS findings_2026_feb_find_tenant_check_ins_idx
-    ON findings_2026_feb USING BTREE (tenant_id, check_id, inserted_at);
-
--- Then register on the parent (this is fast, no data scan):
-CREATE INDEX IF NOT EXISTS find_tenant_check_ins_idx
-    ON findings USING BTREE (tenant_id, check_id, inserted_at);
-```
-
-4. Verify the index exists on the partitions you need:
-
-```sql
-SELECT indexrelid::regclass, indrelid::regclass
-FROM pg_index
-WHERE indexrelid::regclass::text LIKE '%find_tenant_check_ins%';
-```
-
-### When to use this approach
-
-- The table will grow exponentially, e.g.: findings.
-- You want to control exactly when the I/O hit happens (e.g., during a maintenance window).
-
-This is optional. For smaller tables or non-production environments, letting the migration run normally is fine.
-
-## Rule 5: data backfills — never inline, always batched
-
-Data backfills (updating existing rows, populating new columns, generating summary data) are the most dangerous migrations. A naive `Model.objects.all().update(...)` on a multi-million row table will hold a transaction lock for minutes, blow out WAL, and potentially OOM the worker.
-
-### Never backfill inline in the migration
-
-The migration should only dispatch the work. The actual backfill runs asynchronously via Celery tasks, outside the migration transaction.
-
-```python
-# 0090_backfill_finding_group_summaries.py
-from django.db import migrations
-
-def trigger_backfill(apps, schema_editor):
-    from tasks.jobs.backfill import backfill_finding_group_summaries_task
-    Tenant = apps.get_model("api", "Tenant")
-    from api.db_router import MainRouter
-
-    tenant_ids = Tenant.objects.using(MainRouter.admin_db).values_list("id", flat=True)
-    for tenant_id in tenant_ids:
-        backfill_finding_group_summaries_task.delay(tenant_id=str(tenant_id))
-
-class Migration(migrations.Migration):
-    dependencies = [("api", "0089_previous_migration")]
-    operations = [
-        migrations.RunPython(trigger_backfill, migrations.RunPython.noop),
-    ]
-```
-
-The migration finishes in seconds. The backfill runs in the background per-tenant.
-
-### Exception: trivial updates
-
-Single-statement bulk updates on small result sets are OK inline:
-
-```python
-# Fine — single UPDATE, small result set, no iteration
-def backfill_graph_data_ready(apps, schema_editor):
-    AttackPathsScan = apps.get_model("api", "AttackPathsScan")
-    AttackPathsScan.objects.using(MainRouter.admin_db).filter(
-        state="completed", graph_data_ready=False,
-    ).update(graph_data_ready=True)
-```
-
-Use inline only when you're confident the affected row count is small (< ~10K rows).
-
-### Batch processing in the Celery task
-
-The actual backfill task must process data in batches. Use the helpers in `api.db_utils`:
-
-```python
-from api.db_utils import create_objects_in_batches, update_objects_in_batches, batch_delete
-
-# Creating objects in batches (500 per transaction)
-create_objects_in_batches(tenant_id, ScanCategorySummary, summaries, batch_size=500)
-
-# Updating objects in batches
-update_objects_in_batches(tenant_id, Finding, findings, fields=["status"], batch_size=500)
-
-# Deleting in batches
-batch_delete(tenant_id, queryset, batch_size=settings.DJANGO_DELETION_BATCH_SIZE)
-```
-
-Each batch runs in its own `rls_transaction()` so:
-- A failure in batch N doesn't roll back batches 1 through N-1
-- Lock duration is bounded to the batch size
-- Memory stays constant regardless of total row count
-
-### Rules for backfill tasks
-
-1. **One RLS transaction per batch.** Never wrap the entire backfill in a single transaction. Each batch gets its own `rls_transaction(tenant_id)`.
-
-2. **Use `bulk_create` / `bulk_update` with explicit `batch_size`.** Never `.save()` in a loop. The default batch_size is 500.
-
-3. **Use `.iterator()` for reads.** When reading source data, use `queryset.iterator()` to avoid loading the entire result set into memory.
-
-4. **Use `.only()` / `.values_list()` for reads.** Fetch only the columns you need, not full model instances.
-
-5. **Catch and skip per-item failures.** Don't let one bad row kill the entire backfill. Log the error, count it, continue.
-
-```python
-scans_processed = 0
-scans_skipped = 0
-
-for scan_id in scan_ids:
-    try:
-        result = process_scan(tenant_id, scan_id)
-        scans_processed += 1
-    except Exception:
-        logger.warning("Failed to process scan %s", scan_id)
-        scans_skipped += 1
-
-logger.info("Backfill done: %d processed, %d skipped", scans_processed, scans_skipped)
-```
-
-6. **Log totals at start and end, not per-batch.** Per-batch logging floods the logs. Log the total count at the start, and the processed/skipped counts at the end.
-
-7. **Use `ignore_conflicts=True` for idempotent creates.** Makes the backfill safe to re-run if interrupted.
-
-```python
-Model.objects.bulk_create(objects, batch_size=500, ignore_conflicts=True)
-```
-
-8. **Iterate per-tenant.** Dispatch one Celery task per tenant. This gives you natural parallelism, bounded memory per task, and the ability to retry a single tenant without re-running everything.
-
-### Existing examples
-
-| Migration | Task |
-|---|---|
-| `0062_backfill_daily_severity_summaries.py` | `backfill_daily_severity_summaries_task` |
-| `0080_backfill_attack_paths_graph_data_ready.py` | Inline (trivial update) |
-| `0082_backfill_finding_group_summaries.py` | `backfill_finding_group_summaries_task` |
-
-Task implementations: `tasks/jobs/backfill.py`
-Batch utilities: `api/db_utils.py` (`batch_delete`, `create_objects_in_batches`, `update_objects_in_batches`)
-
-## Decision tree
-
-```
-Auto-generated migration?
-├── Yes → Split it following the rules below
-└── No → Review it against the rules below
-
-New model?
-├── Yes → CreateModel + AddConstraint in one migration
-│         AddIndex in separate migration(s), one per table
-└── No, just indexes?
-│   ├── Regular table → AddIndex in its own migration
-│   └── Partitioned table (findings, resource_finding_mappings)?
-│       ├── Step 1: RunPython + create_index_on_partitions (atomic=False)
-│       └── Step 2: AddIndex on parent (separate migration)
-│           └── Large table? → Consider --fake + manual apply
-└── Data backfill?
-    ├── Trivial update (< ~10K rows)? → Inline RunPython is OK
-    └── Large backfill? → Migration dispatches Celery task(s)
-        ├── One task per tenant
-        ├── Batch processing (bulk_create/bulk_update, batch_size=500)
-        ├── One rls_transaction per batch
-        └── Catch + skip per-item failures, log totals
-```
-
-## Quick reference
-
-| Scenario | Approach |
-|---|---|
-| Auto-generated migration | Split by concern and table before committing |
-| New model + constraints/RLS | Same migration (constraints are structural) |
-| Indexes on a regular table | Separate migration, one table per file |
-| Indexes on a partitioned table | Two migrations: partitions first (`RunPython` + `atomic=False`), then parent (`AddIndex`) |
-| Index on a huge partitioned table | Same two migrations, but fake + apply manually in production |
-| Trivial data backfill (< ~10K rows) | Inline `RunPython` with single `.update()` call |
-| Large data backfill | Migration dispatches Celery task per tenant, task batches with `rls_transaction` |
-
-## Review output format
-
-1. List each violation with rule number and one-line explanation
-2. Show corrected migration file(s)
-3. For partitioned tables, show both partition and parent migrations
-
-If migration passes all checks, say so.
-
-## Context7 lookups
-
-**Prerequisite:** Install Context7 MCP server for up-to-date documentation lookup.
-
-When implementing or debugging migration patterns, query these libraries via `mcp_context7_query-docs`:
-
-| Library | Context7 ID | Use for |
-|---------|-------------|---------|
-| Django 5.1 | `/websites/djangoproject_en_5_1` | Migration operations, indexes, constraints, `SchemaEditor` |
-| PostgreSQL | `/websites/postgresql_org_docs_current` | `CREATE INDEX CONCURRENTLY`, partitioned tables, `pg_inherits` |
-| django-postgres-extra | `/SectorLabs/django-postgres-extra` | Partitioned models, `PostgresPartitionedModel`, partition management |
-
-**Example queries:**
-```
-mcp_context7_query-docs(libraryId="/websites/djangoproject_en_5_1", query="migration operations AddIndex RunPython atomic")
-mcp_context7_query-docs(libraryId="/websites/djangoproject_en_5_1", query="database indexes Meta class concurrently")
-mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="CREATE INDEX CONCURRENTLY partitioned table")
-mcp_context7_query-docs(libraryId="/SectorLabs/django-postgres-extra", query="partitioned model range partition index")
-```
-
-> **Note:** Use `mcp_context7_resolve-library-id` first if you need to find the correct library ID.
-
-## Commands
-
-```bash
-# Generate migrations (ALWAYS review output before committing)
-python manage.py makemigrations
-python manage.py pgmakemigrations
-
-# Apply migrations
-python manage.py migrate
-
-# Fake a migration (mark as applied without running)
-python manage.py migrate api <migration_name> --fake
-
-# Manage partitions
-python manage.py pgpartition --using admin
-```
-
-## Resources
-
-- **Partition helpers**: `api/src/backend/api/db_utils.py` (`create_index_on_partitions`, `drop_index_on_partitions`)
-- **Partition config**: `api/src/backend/api/partitions.py`
-- **RLS constraints**: `api/src/backend/api/rls.py`
-- **Existing examples**: `0028` + `0029`, `0024` + `0025`, `0036` + `0037`
+| Did `makemigrations` bundle unrelated operations together? | Rewrite into focused files and delete the generated catch-all migration. |
+| New table plus indexes? | Put `CreateModel` and constraints together, then create separate index migration(s). |
+| Multiple `model_name` values in one `AddIndex` migration? | Split into one migration per table. |
+| Indexing `finding` or `resourcefindingmapping`? | Use partition helper migration first, then parent `AddIndex` migration. |
+| Very large partitioned table in production? | Consider fake-applying the migration and creating indexes manually during a maintenance window. |
+| Data migration larger than trivial update scope? | Dispatch one Celery task per tenant and batch inside the task. |
+
+## Execution Steps
+
+1. Read the generated or reviewed migration and list every operation by table and by concern.
+2. Split structural work (`CreateModel`, `AddConstraint`, field changes) from performance work (`AddIndex`).
+3. For regular-table indexes, create one migration per table with only that table's `AddIndex` operations.
+4. For partitioned tables, write migration A with `RunPython(create_index_on_partitions, reverse_code=drop_index_on_partitions)` and `atomic = False`, then migration B with the parent `AddIndex` so Django registers the index definition.
+5. If the table is huge, document whether the safe path is normal execution or `--fake` plus manual concurrent index creation.
+6. For data backfills, keep the migration as a dispatcher only unless the change is a tiny single `UPDATE`; move real work into Celery tasks that batch reads/writes, use `.iterator()`, fetch only needed columns, and tolerate per-item failures.
+7. Re-verify dependencies, migration names, and rollback behavior before finishing.
+
+## Output Contract
+
+- State whether the migration was accepted as-is or rewritten.
+- List each violation found with the governing rule: mixed concerns, mixed tables, missing partition step, unsafe backfill, or transaction misuse.
+- Show the final migration shape expected: structural file, per-table index file(s), and partition/parent pair when applicable.
+- If a backfill is involved, say whether it is inline-trivial or Celery-dispatched and why.
+- Mention the exact validation command or migration command used.
+
+## References
+
+- `api/src/backend/api/db_utils.py`
+- `api/src/backend/api/partitions.py`
+- `api/src/backend/api/rls.py`
+- `api/src/backend/tasks/jobs/backfill.py`
+- `api/src/backend/**/migrations/`
+- Existing partition examples: `0024` + `0025`, `0028` + `0029`, `0036` + `0037`

skills/postgresql-indexing/SKILL.md

@@ -1,12 +1,6 @@
 ---
 name: postgresql-indexing
-description: >
-  PostgreSQL indexing best practices for Prowler: index design, partial indexes, partitioned table
-  indexing, EXPLAIN ANALYZE validation, concurrent operations, monitoring, and maintenance.
-  Trigger: When creating or modifying PostgreSQL indexes, analyzing query performance with EXPLAIN,
-  debugging slow queries, reviewing index usage statistics, reindexing, dropping indexes, or working
-  with partitioned table indexes. Also trigger when discussing index strategies, partial indexes,
-  or index maintenance operations like VACUUM or ANALYZE.
+description: "Trigger: When designing, validating, dropping, or repairing PostgreSQL indexes, including EXPLAIN analysis and partitioned-table indexing. Enforces Prowler-safe index design, concurrent operations, and partition maintenance rules."
 license: Apache-2.0
 metadata:
   author: prowler-cloud
@@ -20,373 +14,53 @@ metadata:
 allowed-tools: Read, Grep, Glob, Bash
 ---
 
-## When to use
-
-- Creating or modifying PostgreSQL indexes
-- Analyzing query plans with `EXPLAIN`
-- Debugging slow queries or missing index usage
-- Dropping, reindexing, or validating indexes
-- Working with indexes on partitioned tables (findings, resource_finding_mappings)
-- Running VACUUM or ANALYZE after index changes
-
-## Index design
-
-### Partial indexes: constant columns go in WHERE, not in the key
-
-When a column has a fixed value for the query (e.g., `state = 'completed'`), put it in the `WHERE` clause of the index, not in the indexed columns. Otherwise the planner cannot exploit the ordering of the other columns.
-
-```sql
--- Bad: state in the key wastes space and breaks ordering
-CREATE INDEX idx_scans_tenant_state ON scans (tenant_id, state, inserted_at DESC);
-
--- Good: state as a filter, planner uses tenant_id + inserted_at ordering
-CREATE INDEX idx_scans_tenant_ins_completed ON scans (tenant_id, inserted_at DESC)
-    WHERE state = 'completed';
-```
-
-### Column order matters
-
-Put high-selectivity columns first (columns that filter out the most rows). For composite indexes, the leftmost column must appear in the query's WHERE clause for the index to be used.
-
-## Validating index effectiveness
-
-### Always EXPLAIN (ANALYZE, BUFFERS) after adding indexes
-
-Never assume an index is being used. Run `EXPLAIN (ANALYZE, BUFFERS)` to confirm.
-
-```sql
-EXPLAIN (ANALYZE, BUFFERS)
-SELECT *
-FROM users
-WHERE email = 'user@example.com';
-```
-
-Use [Postgres EXPLAIN Visualizer (pev)](https://tatiyants.com/pev/) to visualize query plans and identify bottlenecks.
-
-### Force index usage for testing
-
-The planner may choose a sequential scan on small datasets. Toggle `enable_seqscan = off` to confirm the index path works, then re-enable it.
-
-```sql
-SET enable_seqscan = off;
-
-EXPLAIN (ANALYZE, BUFFERS)
-SELECT DISTINCT ON (provider_id) provider_id
-FROM scans
-WHERE tenant_id = '95383b24-da01-44b5-a713-0d9920d554db'
-  AND state = 'completed'
-ORDER BY provider_id, inserted_at DESC;
-
-SET enable_seqscan = on;  -- always re-enable after testing
-```
-
-This is for validation only. Never leave `enable_seqscan = off` in production.
-
-## Over-indexing
-
-Every extra index has three costs that compound:
-
-1. **Write overhead.** Every INSERT and UPDATE must maintain all indexes. Extra indexes also kill HOT (Heap-Only-Tuple) updates, which normally skip index maintenance when unindexed columns change.
-
-2. **Planning time.** The planner evaluates more execution paths per index. On simple OLTP queries, planning time can exceed execution time by 4x when index count is high.
-
-3. **Lock contention (fastpath limit).** PostgreSQL uses a fast path for the first 16 locks per backend. After 16 relations (table + its indexes), it falls back to slower LWLock mechanisms. At high QPS (100+), this causes `LockManager` wait events.
-
-Rules:
-- Drop unused and redundant indexes regularly
-- Be especially careful with partitioned tables (each partition multiplies the index count)
-- Use prepared statements to reduce planning overhead when index count is high
-
-## Finding redundant indexes
-
-Two indexes are redundant when:
-- They have the same columns in the same order (duplicates)
-- One is a prefix of the other: index `(a)` is redundant to `(a, b)`, but NOT to `(b, a)`
-
-Column order matters. For partial indexes, the WHERE clause must also match.
-
-```sql
--- Quick check: find indexes that share a leading column on the same table
-SELECT
-    a.indrelid::regclass AS table_name,
-    a.indexrelid::regclass AS index_a,
-    b.indexrelid::regclass AS index_b,
-    pg_size_pretty(pg_relation_size(a.indexrelid)) AS size_a,
-    pg_size_pretty(pg_relation_size(b.indexrelid)) AS size_b
-FROM pg_index a
-JOIN pg_index b ON a.indrelid = b.indrelid
-    AND a.indexrelid != b.indexrelid
-    AND a.indkey::text = (
-        SELECT string_agg(x::text, ' ')
-        FROM unnest(b.indkey[:array_length(a.indkey, 1)]) AS x
-    )
-WHERE NOT a.indisunique;
-```
-
-Before dropping: verify on all workload nodes (primary + replicas), use `DROP INDEX CONCURRENTLY`, and monitor for plan regressions.
-
-## Monitoring index usage
-
-### Identify unused indexes
-
-Query `pg_stat_all_indexes` to find indexes that are never or rarely scanned:
-
-```sql
-SELECT
-    idxstat.schemaname AS schema_name,
-    idxstat.relname AS table_name,
-    idxstat.indexrelname AS index_name,
-    idxstat.idx_scan AS index_scans_count,
-    idxstat.last_idx_scan AS last_idx_scan_timestamp,
-    pg_size_pretty(pg_relation_size(idxstat.indexrelid)) AS index_size
-FROM pg_stat_all_indexes AS idxstat
-JOIN pg_index i ON idxstat.indexrelid = i.indexrelid
-WHERE idxstat.schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
-  AND NOT i.indisunique
-ORDER BY idxstat.idx_scan ASC, idxstat.last_idx_scan ASC;
-```
-
-Indexes with `idx_scan = 0` and no recent `last_idx_scan` are candidates for removal.
-
-Before dropping, verify:
-- Stats haven't been reset recently (check `stats_reset` in `pg_stat_database`)
-- Stats cover at least 1 month of production traffic
-- All workload nodes (primary + replicas) have been checked
-- The index isn't used by a periodic job that runs infrequently
-
-```sql
--- Check when stats were last reset
-SELECT stats_reset, age(now(), stats_reset)
-FROM pg_stat_database
-WHERE datname = current_database();
-```
-
-### Monitor index creation progress
-
-Do not assume index creation succeeded. Use `pg_stat_progress_create_index` (Postgres 12+) to watch progress live:
-
-```sql
-SELECT * FROM pg_stat_progress_create_index;
-```
-
-In psql, use `\watch 5` to refresh every 5 seconds for a live dashboard view. `CREATE INDEX CONCURRENTLY` and `REINDEX CONCURRENTLY` have more phases than standard operations: monitor for blocking sessions and wait events.
-
-### Validate index integrity
-
-Check for invalid indexes regularly:
-
-```sql
-SELECT c.relname AS index_name, i.indisvalid
-FROM pg_class c
-JOIN pg_index i ON i.indexrelid = c.oid
-WHERE i.indisvalid = false;
-```
-
-Invalid indexes are ignored by the planner. They waste space and cause inconsistent query performance, especially on partitioned tables where some partitions may have valid indexes and others do not.
-
-## Concurrent operations
-
-### Always use CONCURRENTLY in production
-
-Never create or drop indexes without `CONCURRENTLY` on live tables. Without it, the operation holds a lock that blocks all writes.
-
-```sql
--- Create
-CREATE INDEX CONCURRENTLY IF NOT EXISTS index_name ON table_name (column_name);
-
--- Drop
-DROP INDEX CONCURRENTLY IF EXISTS index_name;
-```
-
-`DROP INDEX CONCURRENTLY` cannot run inside a transaction block.
-
-### Always use IF NOT EXISTS / IF EXISTS
-
-Makes scripts idempotent. Safe to re-run without errors from duplicate or missing indexes.
-
-### Concurrent indexing can fail silently
-
-`CREATE INDEX CONCURRENTLY` can fail without raising an error. The result is an invalid index that the planner ignores. This is particularly dangerous on partitioned tables: some partitions get valid indexes, others don't, causing inconsistent query performance.
-
-After any concurrent index creation, always validate:
-
-```sql
-SELECT c.relname, i.indisvalid
-FROM pg_class c
-JOIN pg_index i ON i.indexrelid = c.oid
-WHERE c.relname LIKE '%your_index_name%';
-```
-
-## Reindexing invalid indexes
-
-Rebuild invalid indexes without locking writes:
-
-```sql
-REINDEX INDEX CONCURRENTLY index_name;
-```
-
-### Understanding _ccnew and _ccold artifacts
-
-When `CREATE INDEX CONCURRENTLY` or `REINDEX INDEX CONCURRENTLY` is interrupted, temporary indexes may remain:
-
-| Suffix | Meaning | Action |
-|--------|---------|--------|
-| `_ccnew` | New index being built, incomplete | Drop it and retry `REINDEX CONCURRENTLY` |
-| `_ccold` | Old index being replaced, rebuild succeeded | Safe to drop |
-
-```sql
--- Example: both original and temp are invalid
--- users_emails_2019       btree (col) INVALID
--- users_emails_2019_ccnew btree (col) INVALID
-
--- Drop the failed new one, then retry
-DROP INDEX CONCURRENTLY IF EXISTS users_emails_2019_ccnew;
-REINDEX INDEX CONCURRENTLY users_emails_2019;
-```
-
-These leftovers clutter the schema, confuse developers, and waste disk space. Clean them up.
-
-## Indexing partitioned tables
-
-### Do NOT use ALTER INDEX ATTACH PARTITION
-
-As stated in PostgreSQL documentation, `ALTER INDEX ... ATTACH PARTITION` prevents dropping malfunctioning or non-performant indexes from individual partitions. An attached index cannot be dropped by itself and is automatically dropped if its parent index is dropped.
-
-This removes the ability to manage indexes per-partition, which we need for:
-- Dropping broken indexes on specific partitions
-- Skipping indexes on old partitions to save storage
-- Rebuilding indexes on individual partitions without affecting others
-
-### Correct approach: create on partitions, then on parent
-
-1. Create the index on each child partition concurrently:
-
-```sql
-CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_child_partition
-    ON child_partition (column_name);
-```
-
-2. Create the index on the parent table (metadata-only, fast):
-
-```sql
-CREATE INDEX IF NOT EXISTS idx_parent
-    ON parent_table (column_name);
-```
-
-PostgreSQL will automatically recognize partition-level indexes as part of the parent index definition when the index names and definitions match.
-
-### Prioritize active partitions
-
-For time-based partitions (findings uses monthly partitions):
-
-- Create indexes on recent/current partitions where data is actively queried
-- Skip older partitions that are rarely accessed
-- The `all_partitions=False` default in `create_index_on_partitions` handles this automatically
-
-## Index maintenance and bloat
-
-Over time, B-tree indexes accumulate bloat from updates and deletes. VACUUM reclaims heap space but does NOT rebalance B-tree pages. Periodic reindexing is necessary for heavily updated tables.
-
-### Detecting bloat
-
-Indexes with estimated bloat above 50% are candidates for `REINDEX CONCURRENTLY`. Check bloat with tools like `pgstattuple` or bloat estimation queries.
-
-### Reducing bloat buildup
-
-Three things slow degradation:
-1. **Upgrade to PostgreSQL 14+** for B-tree deduplication and bottom-up deletion
-2. **Maximize HOT updates** by not indexing frequently-updated columns
-3. **Tune autovacuum** to run more aggressively on high-churn tables
-
-### Rebuilding many indexes without deadlocks
-
-If you rebuild two indexes on the same table in parallel, PostgreSQL detects a deadlock and kills one session. To rebuild many indexes across multiple sessions safely, assign all indexes for a given table to the same session:
-
-```sql
-\set NUMBER_OF_SESSIONS 10
-
-SELECT
-    format('%I.%I', n.nspname, c.relname) AS table_fqn,
-    format('%I.%I', n.nspname, i.relname) AS index_fqn,
-    mod(
-        hashtext(format('%I.%I', n.nspname, c.relname)) & 2147483647,
-        :NUMBER_OF_SESSIONS
-    ) AS session_id
-FROM pg_index idx
-JOIN pg_class c ON idx.indrelid = c.oid
-JOIN pg_class i ON idx.indexrelid = i.oid
-JOIN pg_namespace n ON c.relnamespace = n.oid
-WHERE n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
-ORDER BY table_fqn, index_fqn;
-```
-
-Then run each session's indexes in a separate `REINDEX INDEX CONCURRENTLY` call. Set `NUMBER_OF_SESSIONS` based on `max_parallel_maintenance_workers` and available I/O.
-
-## Dropping indexes
-
-### Post-drop maintenance
-
-After dropping an index, run VACUUM and ANALYZE to reclaim space and update planner statistics:
-
-```sql
--- Full vacuum + analyze (can be heavy on large tables)
-VACUUM (ANALYZE) your_table;
-
--- Lightweight alternative for huge tables: just update statistics
-ANALYZE your_table;
-```
-
-## Commands
-
-```sql
--- Validate query uses an index
-EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-
--- Check index creation progress
-SELECT * FROM pg_stat_progress_create_index;
-
--- Find invalid indexes
-SELECT c.relname, i.indisvalid
-FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid
-WHERE i.indisvalid = false;
-
--- Find unused indexes
-SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
-FROM pg_stat_all_indexes
-WHERE schemaname = 'public' AND idx_scan = 0;
-
--- Create index safely
-CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON table (columns);
-
--- Drop index safely
-DROP INDEX CONCURRENTLY IF EXISTS idx_name;
-
--- Rebuild invalid index
-REINDEX INDEX CONCURRENTLY idx_name;
-
--- Post-drop maintenance
-VACUUM (ANALYZE) table_name;
-```
-
-## Context7 lookups
-
-**Prerequisite:** Install Context7 MCP server for up-to-date documentation lookup.
-
-| Library | Context7 ID | Use for |
-|---------|-------------|---------|
-| PostgreSQL | `/websites/postgresql_org_docs_current` | Index types, EXPLAIN, partitioned table indexing, REINDEX |
-
-**Example queries:**
-```
-mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="CREATE INDEX CONCURRENTLY partitioned table")
-mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="EXPLAIN ANALYZE BUFFERS query plan")
-mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="partial index WHERE clause")
-mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="REINDEX CONCURRENTLY invalid index")
-mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="pg_stat_all_indexes monitoring")
-```
-
-> **Note:** Use `mcp_context7_resolve-library-id` first if you need to find the correct library ID.
-
-## Resources
-
-- **EXPLAIN Visualizer**: [pev](https://tatiyants.com/pev/)
+## Activation Contract
+
+Use this skill when query performance or schema work depends on PostgreSQL index design, validation, integrity, or partition-aware maintenance.
+
+## Hard Rules
+
+- Put constant filters in partial-index `WHERE` clauses, not in the indexed key.
+- Validate every proposed index with `EXPLAIN (ANALYZE, BUFFERS)`; never assume the planner will use it.
+- On live tables, create, drop, and rebuild indexes with `CONCURRENTLY` and idempotent guards where possible.
+- After `CREATE INDEX CONCURRENTLY`, always verify `indisvalid`; concurrent builds can leave unusable invalid indexes behind.
+- Do not use `ALTER INDEX ... ATTACH PARTITION`; Prowler needs independent partition index control.
+- For partitioned tables, build child-partition indexes first, then create the parent definition.
+- Watch over-indexing: extra indexes increase write cost, planning cost, and lock pressure.
+- After dropping indexes, refresh planner stats with `VACUUM (ANALYZE)` or at least `ANALYZE`.
+
+## Decision Gates
+
+| Question | Action |
+|---|---|
+| Query always filters on a fixed value like `state = 'completed'`? | Use a partial index with the constant in `WHERE`. |
+| Planner still chooses a seq scan on a tiny dataset? | Toggle `enable_seqscan = off` only for validation, then turn it back on. |
+| Creating or dropping on a live table? | Use `CONCURRENTLY`; avoid transaction wrappers that would invalidate the command. |
+| Working on `findings` or another partitioned table? | Create matching indexes on children first, then register the parent index. |
+| Index build succeeded syntactically but performance is still bad? | Check `pg_stat_progress_create_index`, `pg_index.indisvalid`, and redundant/unused index patterns. |
+| Need to remove an index? | Confirm workload coverage, drop concurrently, then run post-drop maintenance. |
+
+## Execution Steps
+
+1. Start from the query shape: filters, ordering, distinct/grouping, and whether a partial predicate can shrink the index.
+2. Choose column order by selectivity and leftmost-filter usage; avoid indexing frequently updated columns unless justified.
+3. Run `EXPLAIN (ANALYZE, BUFFERS)` before and after, and use `enable_seqscan = off` only as a temporary proof that the index path is valid.
+4. For production changes, use `CREATE INDEX CONCURRENTLY IF NOT EXISTS`, `DROP INDEX CONCURRENTLY IF EXISTS`, or `REINDEX INDEX CONCURRENTLY` as appropriate.
+5. Validate the result with `pg_index.indisvalid`; if `_ccnew` or `_ccold` artifacts appear, clean them up deliberately before retrying.
+6. On partitioned tables, create the same definition on child partitions first and only then add the parent metadata index; skip `ATTACH PARTITION`.
+7. Review redundant, unused, and bloated indexes, then run `VACUUM (ANALYZE)` or `ANALYZE` after drops or major churn.
+
+## Output Contract
+
+- Describe the target query pattern and the chosen index shape.
+- State whether the final design is full, partial, composite, or partitioned.
+- Report the validation evidence used: `EXPLAIN`, `indisvalid`, progress view, unused-index stats, or bloat checks.
+- If partitioned tables are involved, explicitly say child indexes were handled before the parent definition.
+- Mention any operational risk: over-indexing, invalid concurrent build, deadlock risk during parallel reindex, or stats refresh required.
+
+## References
+
+- `api/src/backend/api/db_utils.py`
+- `api/src/backend/api/partitions.py`
+- `skills/django-migration-psql/SKILL.md`
+- `api/src/backend/**/migrations/`

skills/prowler-attack-paths-query/SKILL.md

@@ -1,10 +1,6 @@
 ---
 name: prowler-attack-paths-query
-description: >
-  Creates Prowler Attack Paths openCypher queries using the Cartography schema as the source of truth
-  for node labels, properties, and relationships. Also covers Prowler-specific additions (Internet node,
-  ProwlerFinding, internal isolation labels) and $provider_uid scoping for predefined queries.
-  Trigger: When creating or updating Attack Paths queries.
+description: "Trigger: When creating or updating Prowler Attack Paths openCypher queries. Governs provider scoping, Cartography-schema grounding, Prowler-specific labels, and openCypher-safe query patterns."
 license: Apache-2.0
 metadata:
   author: prowler-cloud
@@ -17,471 +13,55 @@ metadata:
 allowed-tools: Read, Edit, Write, Glob, Grep, Bash, WebFetch, Task
 ---
 
-## Overview
-
-Attack Paths queries are openCypher queries that analyze cloud infrastructure graphs (ingested via Cartography) to detect security risks like privilege escalation paths, network exposure, and misconfigurations.
-
-Queries are written in **openCypher Version 9** for compatibility with both Neo4j and Amazon Neptune.
-
----
-
-## Two query audiences
-
-This skill covers two types of queries with different isolation mechanisms:
-
-| | Predefined queries | Custom queries |
-|---|---|---|
-| **Where they live** | `api/src/backend/api/attack_paths/queries/{provider}.py` | User/LLM-supplied via the custom query API endpoint |
-| **Provider isolation** | `AWSAccount {id: $provider_uid}` anchor + path connectivity | Automatic `_Provider_{uuid}` label injection via `cypher_sanitizer.py` |
-| **What to write** | Chain every MATCH from the `aws` variable | Plain Cypher, no isolation boilerplate needed |
-| **Internal labels** | Never use (`_ProviderResource`, `_Tenant_*`, `_Provider_*`) | Never use (injected automatically by the system) |
-
-**For predefined queries**: every node must be reachable from the `AWSAccount` root via graph traversal. This is the isolation boundary.
-
-**For custom queries**: write natural Cypher without isolation concerns. The query runner injects a `_Provider_{uuid}` label into every node pattern before execution, and a post-query filter catches edge cases.
-
----
-
-## Input Sources
-
-Queries can be created from:
-
-1. **pathfinding.cloud ID** (e.g., `ECS-001`, `GLUE-001`)
-   - Reference: https://github.com/DataDog/pathfinding.cloud
-   - The aggregated `paths.json` is too large for WebFetch. Use Bash:
-
-   ```bash
-   # Fetch a single path by ID
-   curl -s https://raw.githubusercontent.com/DataDog/pathfinding.cloud/main/docs/paths.json \
-     | jq '.[] | select(.id == "ecs-002")'
-
-   # List all path IDs and names
-   curl -s https://raw.githubusercontent.com/DataDog/pathfinding.cloud/main/docs/paths.json \
-     | jq -r '.[] | "\(.id): \(.name)"'
-
-   # Filter by service prefix
-   curl -s https://raw.githubusercontent.com/DataDog/pathfinding.cloud/main/docs/paths.json \
-     | jq -r '.[] | select(.id | startswith("ecs")) | "\(.id): \(.name)"'
-   ```
-
-   If `jq` is not available, use `python3 -c "import json,sys; ..."` as a fallback.
-
-2. **Natural language description** from the user
-
----
-
-## Query Structure
-
-### Provider scoping parameter
-
-One parameter is injected automatically by the query runner:
-
-| Parameter       | Property it matches | Used on      | Purpose                          |
-| --------------- | ------------------- | ------------ | -------------------------------- |
-| `$provider_uid` | `id`                | `AWSAccount` | Scopes to a specific AWS account |
-
-All other nodes are isolated by path connectivity from the `AWSAccount` anchor.
-
-### Imports
-
-All query files start with these imports:
-
-```python
-from api.attack_paths.queries.types import (
-    AttackPathsQueryAttribution,
-    AttackPathsQueryDefinition,
-    AttackPathsQueryParameterDefinition,
-)
-from tasks.jobs.attack_paths.config import PROWLER_FINDING_LABEL
-```
-
-The `PROWLER_FINDING_LABEL` constant (value: `"ProwlerFinding"`) is used via f-string interpolation in all queries. Never hardcode the label string.
-
-### Privilege escalation sub-patterns
-
-There are four distinct privilege escalation patterns. Choose based on the attack type:
-
-| Sub-pattern | Target | `path_target` shape | Example |
-|---|---|---|---|
-| Self-escalation | Principal's own policies | `(aws)--(target_policy:AWSPolicy)--(principal)` | IAM-001 |
-| Lateral to user | Other IAM users | `(aws)--(target_user:AWSUser)` | IAM-002 |
-| Assume-role lateral | Assumable roles | `(aws)--(target_role:AWSRole)<-[:STS_ASSUMEROLE_ALLOW]-(principal)` | IAM-014 |
-| PassRole + service | Service-trusting roles | `(aws)--(target_role:AWSRole)-[:TRUSTS_AWS_PRINCIPAL]->(...)` | EC2-001 |
-
-#### Self-escalation (e.g., IAM-001)
-
-The principal modifies resources attached to itself. `path_target` loops back to `principal`:
-
-```python
-AWS_{QUERY_NAME} = AttackPathsQueryDefinition(
-    id="aws-{kebab-case-name}",
-    name="{Human-friendly label} ({REFERENCE_ID})",
-    short_description="{Brief explanation, no technical permissions.}",
-    description="{Detailed description of the attack vector and impact.}",
-    attribution=AttackPathsQueryAttribution(
-        text="pathfinding.cloud - {REFERENCE_ID} - {permission}",
-        link="https://pathfinding.cloud/paths/{reference_id_lowercase}",
-    ),
-    provider="aws",
-    cypher=f"""
-        // Find principals with {permission}
-        MATCH path_principal = (aws:AWSAccount {{id: $provider_uid}})--(principal:AWSPrincipal)--(policy:AWSPolicy)--(stmt:AWSPolicyStatement)
-        WHERE stmt.effect = 'Allow'
-            AND any(action IN stmt.action WHERE
-                toLower(action) = '{permission_lowercase}'
-                OR toLower(action) = '{service}:*'
-                OR action = '*'
-            )
-
-        // Find target resources attached to the same principal
-        MATCH path_target = (aws)--(target_policy:AWSPolicy)--(principal)
-        WHERE target_policy.arn CONTAINS $provider_uid
-            AND any(resource IN stmt.resource WHERE
-                resource = '*'
-                OR target_policy.arn CONTAINS resource
-            )
-
-        WITH collect(path_principal) + collect(path_target) AS paths
-        UNWIND paths AS p
-        UNWIND nodes(p) AS n
-
-        WITH paths, collect(DISTINCT n) AS unique_nodes
-        UNWIND unique_nodes AS n
-        OPTIONAL MATCH (n)-[pfr]-(pf:{PROWLER_FINDING_LABEL} {{status: 'FAIL'}})
-
-        RETURN paths, collect(DISTINCT pf) as dpf, collect(DISTINCT pfr) as dpfr
-    """,
-    parameters=[],
-)
-```
-
-#### Other sub-pattern `path_target` shapes
-
-The other 3 sub-patterns share the same `path_principal`, deduplication tail, and RETURN as self-escalation. Only the `path_target` MATCH differs:
-
-```cypher
-// Lateral to user (e.g., IAM-002) - targets other IAM users
-MATCH path_target = (aws)--(target_user:AWSUser)
-WHERE any(resource IN stmt.resource WHERE resource = '*' OR target_user.arn CONTAINS resource OR resource CONTAINS target_user.name)
-
-// Assume-role lateral (e.g., IAM-014) - targets roles the principal can assume
-MATCH path_target = (aws)--(target_role:AWSRole)<-[:STS_ASSUMEROLE_ALLOW]-(principal)
-WHERE any(resource IN stmt.resource WHERE resource = '*' OR target_role.arn CONTAINS resource OR resource CONTAINS target_role.name)
-
-// PassRole + service (e.g., EC2-001) - targets roles trusting a service
-MATCH path_target = (aws)--(target_role:AWSRole)-[:TRUSTS_AWS_PRINCIPAL]->(:AWSPrincipal {arn: '{service}.amazonaws.com'})
-WHERE any(resource IN stmt.resource WHERE resource = '*' OR target_role.arn CONTAINS resource OR resource CONTAINS target_role.name)
-```
-
-**Multi-permission**: PassRole queries require a second permission. Add `MATCH (principal)--(policy2:AWSPolicy)--(stmt2:AWSPolicyStatement)` with its own WHERE before `path_target`, then check BOTH `stmt.resource` AND `stmt2.resource` against the target. See IAM-015 or EC2-001 in `aws.py` for examples.
-
-### Network exposure pattern
-
-The Internet node is reached via `CAN_ACCESS` through the already-scoped resource, not via a standalone lookup:
-
-```python
-AWS_{QUERY_NAME} = AttackPathsQueryDefinition(
-    id="aws-{kebab-case-name}",
-    name="{Human-friendly label}",
-    short_description="{Brief explanation.}",
-    description="{Detailed description.}",
-    provider="aws",
-    cypher=f"""
-        // Match exposed resources (MUST chain from `aws`)
-        MATCH path = (aws:AWSAccount {{id: $provider_uid}})--(resource:EC2Instance)
-        WHERE resource.exposed_internet = true
-
-        // Internet node reached via path connectivity through the resource
-        OPTIONAL MATCH (internet:Internet)-[can_access:CAN_ACCESS]->(resource)
-
-        WITH collect(path) AS paths, head(collect(internet)) AS internet, collect(can_access) AS can_access
-        UNWIND paths AS p
-        UNWIND nodes(p) AS n
-
-        WITH paths, internet, can_access, collect(DISTINCT n) AS unique_nodes
-        UNWIND unique_nodes AS n
-        OPTIONAL MATCH (n)-[pfr]-(pf:{PROWLER_FINDING_LABEL} {{status: 'FAIL'}})
-
-        RETURN paths, collect(DISTINCT pf) as dpf, collect(DISTINCT pfr) as dpfr,
-            internet, can_access
-    """,
-    parameters=[],
-)
-```
-
-### Register in query list
-
-Add to the `{PROVIDER}_QUERIES` list at the bottom of the file:
-
-```python
-AWS_QUERIES: list[AttackPathsQueryDefinition] = [
-    # ... existing queries ...
-    AWS_{NEW_QUERY_NAME},  # Add here
-]
-```
-
----
-
-## Step-by-step creation process
-
-### 1. Read the queries module
-
-**FIRST**, read all files in the queries module to understand the structure, type definitions, registration, and existing style:
-
-```
-api/src/backend/api/attack_paths/queries/
-├── __init__.py      # Module exports
-├── types.py         # AttackPathsQueryDefinition, AttackPathsQueryParameterDefinition
-├── registry.py      # Query registry logic
-└── {provider}.py    # Provider-specific queries (e.g., aws.py)
-```
-
-**DO NOT** use generic templates. Match the exact style of existing queries in the file.
-
-### 2. Fetch and consult the Cartography schema
-
-**This is the most important step.** Every node label, property, and relationship in the query must exist in the Cartography schema for the pinned version. Do not guess or rely on memory.
-
-Check `api/pyproject.toml` for the Cartography dependency, then fetch the schema:
-
-```bash
-grep cartography api/pyproject.toml
-```
-
-Build the schema URL (ALWAYS use the specific tag, not master/main):
-
-```
-# Git dependency (prowler-cloud/cartography@0.126.1):
-https://raw.githubusercontent.com/prowler-cloud/cartography/refs/tags/0.126.1/docs/root/modules/{provider}/schema.md
-
-# PyPI dependency (cartography = "^0.126.0"):
-https://raw.githubusercontent.com/cartography-cncf/cartography/refs/tags/0.126.0/docs/root/modules/{provider}/schema.md
-```
-
-Read the schema to discover available node labels, properties, and relationships for the target resources. Internal labels (`_ProviderResource`, `_AWSResource`, `_Tenant_*`, `_Provider_*`) exist for isolation but should never appear in queries.
-
-### 4. Create query definition
-
-Use the appropriate pattern (privilege escalation or network exposure) with:
-
-- **id**: `{provider}-{kebab-case-description}`
-- **name**: Short, human-friendly label. For sourced queries, append the reference ID: `"EC2 Instance Launch with Privileged Role (EC2-001)"`.
-- **short_description**: Brief explanation, no technical permissions.
-- **description**: Full technical explanation. Plain text only.
-- **provider**: Provider identifier (aws, azure, gcp, kubernetes, github)
-- **cypher**: The openCypher query with proper escaping
-- **parameters**: Optional list of user-provided parameters (`parameters=[]` if none)
-- **attribution**: Optional `AttackPathsQueryAttribution(text, link)` for sourced queries. The `text` includes source, reference ID, and permissions. The `link` uses a lowercase ID. Omit for non-sourced queries.
-
-### 5. Add query to provider list
-
-Add the constant to the `{PROVIDER}_QUERIES` list.
-
----
-
-## Query naming conventions
-
-### Query ID
-
-```
-{provider}-{category}-{description}
-```
-
-Examples: `aws-ec2-privesc-passrole-iam`, `aws-ec2-instances-internet-exposed`
-
-### Query constant name
-
-```
-{PROVIDER}_{CATEGORY}_{DESCRIPTION}
-```
-
-Examples: `AWS_EC2_PRIVESC_PASSROLE_IAM`, `AWS_EC2_INSTANCES_INTERNET_EXPOSED`
-
----
-
-## Query categories
-
-| Category             | Description                    | Example                   |
-| -------------------- | ------------------------------ | ------------------------- |
-| Basic Resource       | List resources with properties | RDS instances, S3 buckets |
-| Network Exposure     | Internet-exposed resources     | EC2 with public IPs       |
-| Privilege Escalation | IAM privilege escalation paths | PassRole + RunInstances   |
-| Data Access          | Access to sensitive data       | EC2 with S3 access        |
-
----
-
-## Common openCypher patterns
-
-### Match account and principal
-
-```cypher
-MATCH path_principal = (aws:AWSAccount {id: $provider_uid})--(principal:AWSPrincipal)--(policy:AWSPolicy)--(stmt:AWSPolicyStatement)
-```
-
-### Check IAM action permissions
-
-```cypher
-WHERE stmt.effect = 'Allow'
-    AND any(action IN stmt.action WHERE
-        toLower(action) = 'iam:passrole'
-        OR toLower(action) = 'iam:*'
-        OR action = '*'
-    )
-```
-
-### Find roles trusting a service
-
-```cypher
-MATCH path_target = (aws)--(target_role:AWSRole)-[:TRUSTS_AWS_PRINCIPAL]->(:AWSPrincipal {arn: 'ec2.amazonaws.com'})
-```
-
-### Find roles the principal can assume
-
-Note the arrow direction - `STS_ASSUMEROLE_ALLOW` points from the role to the principal:
-
-```cypher
-MATCH path_target = (aws)--(target_role:AWSRole)<-[:STS_ASSUMEROLE_ALLOW]-(principal)
-```
-
-### Check resource scope
-
-```cypher
-WHERE any(resource IN stmt.resource WHERE
-    resource = '*'
-    OR target_role.arn CONTAINS resource
-    OR resource CONTAINS target_role.name
-)
-```
-
-### Internet node via path connectivity
-
-The Internet node is reached through `CAN_ACCESS` relationships to already-scoped resources. No standalone lookup needed:
-
-```cypher
-OPTIONAL MATCH (internet:Internet)-[can_access:CAN_ACCESS]->(resource)
-```
-
-### Multi-label OR (match multiple resource types)
-
-```cypher
-MATCH path = (aws:AWSAccount {id: $provider_uid})-[r]-(x)-[q]-(y)
-WHERE (x:EC2PrivateIp AND x.public_ip = $ip)
-   OR (x:EC2Instance AND x.publicipaddress = $ip)
-   OR (x:NetworkInterface AND x.public_ip = $ip)
-   OR (x:ElasticIPAddress AND x.public_ip = $ip)
-```
-
-### Include Prowler findings
-
-Deduplicate nodes before the ProwlerFinding lookup to avoid redundant OPTIONAL MATCH calls on nodes that appear in multiple paths:
-
-```cypher
-WITH collect(path_principal) + collect(path_target) AS paths
-UNWIND paths AS p
-UNWIND nodes(p) AS n
-
-WITH paths, collect(DISTINCT n) AS unique_nodes
-UNWIND unique_nodes AS n
-OPTIONAL MATCH (n)-[pfr]-(pf:{PROWLER_FINDING_LABEL} {{status: 'FAIL'}})
-
-RETURN paths, collect(DISTINCT pf) as dpf, collect(DISTINCT pfr) as dpfr
-```
-
-For network exposure queries, aggregate the internet node and relationship alongside paths:
-
-```cypher
-WITH collect(path) AS paths, head(collect(internet)) AS internet, collect(can_access) AS can_access
-UNWIND paths AS p
-UNWIND nodes(p) AS n
-
-WITH paths, internet, can_access, collect(DISTINCT n) AS unique_nodes
-UNWIND unique_nodes AS n
-OPTIONAL MATCH (n)-[pfr]-(pf:{PROWLER_FINDING_LABEL} {{status: 'FAIL'}})
-
-RETURN paths, collect(DISTINCT pf) as dpf, collect(DISTINCT pfr) as dpfr,
-    internet, can_access
-```
-
----
-
-## Prowler-specific labels and relationships
-
-These are added by the sync task, not part of the Cartography schema. For all other node labels, properties, and relationships, **always consult the Cartography schema** (see step 2 below).
-
-| Label/Relationship     | Description                                        |
-| ---------------------- | -------------------------------------------------- |
-| `ProwlerFinding`       | Finding node (`status`, `severity`, `check_id`)    |
-| `Internet`             | Internet sentinel node                             |
-| `CAN_ACCESS`           | Internet-to-resource exposure (relationship)       |
-| `HAS_FINDING`          | Resource-to-finding link (relationship)            |
-| `TRUSTS_AWS_PRINCIPAL` | Role trust relationship                            |
-| `STS_ASSUMEROLE_ALLOW` | Can assume role (direction: role -> principal)      |
-
----
-
-## Parameters
-
-For queries requiring user input:
-
-```python
-parameters=[
-    AttackPathsQueryParameterDefinition(
-        name="ip",
-        label="IP address",
-        # data_type defaults to "string", cast defaults to str.
-        # For non-string params, set both: data_type="integer", cast=int
-        description="Public IP address, e.g. 192.0.2.0.",
-        placeholder="192.0.2.0",
-    ),
-],
-```
-
----
-
-## Best practices
-
-1. **Chain all MATCHes from the root account node**: Every `MATCH` clause must connect to the `aws` variable (or another variable already bound to the account's subgraph). An unanchored `MATCH` would return nodes from all providers.
-
-   ```cypher
-   // WRONG: matches ALL AWSRoles across all providers
-   MATCH (role:AWSRole) WHERE role.name = 'admin'
-
-   // CORRECT: scoped to the specific account's subgraph
-   MATCH (aws)--(role:AWSRole) WHERE role.name = 'admin'
-   ```
-
-   **Exception**: A second-permission MATCH like `MATCH (principal)--(policy2:AWSPolicy)--(stmt2:AWSPolicyStatement)` is safe because `principal` is already bound to the account's subgraph by the first MATCH. It does not need to chain from `aws` again.
-
-2. **Include Prowler findings**: Always add `OPTIONAL MATCH (n)-[pfr]-(pf:{PROWLER_FINDING_LABEL} {{status: 'FAIL'}})` with `collect(DISTINCT pf)`.
-
-3. **Comment the query purpose**: Add inline comments explaining each MATCH clause.
-
-4. **Never use internal labels in queries**: `_ProviderResource`, `_AWSResource`, `_Tenant_*`, `_Provider_*` are for system isolation. They should never appear in predefined or custom query text.
-
-6. **Internet node uses path connectivity**: Reach it via `OPTIONAL MATCH (internet:Internet)-[can_access:CAN_ACCESS]->(resource)` where `resource` is already scoped by the account anchor. No standalone lookup.
-
----
-
-## openCypher compatibility
-
-Queries must be written in **openCypher Version 9** for compatibility with both Neo4j and Amazon Neptune.
-
-### Avoid these (not in openCypher spec)
-
-| Feature                    | Use instead                                            |
-| -------------------------- | ------------------------------------------------------ |
-| APOC procedures (`apoc.*`) | Real nodes and relationships in the graph              |
-| Neptune extensions         | Standard openCypher                                    |
-| `reduce()` function        | `UNWIND` + `collect()`                                 |
-| `FOREACH` clause           | `WITH` + `UNWIND` + `SET`                              |
-| Regex operator (`=~`)      | `toLower()` + exact match, or `CONTAINS`/`STARTS WITH`. One legacy query uses `=~` - do not add new usages |
-| `CALL () { UNION }`        | Multi-label OR in WHERE (see patterns section)         |
-
----
-
-## Reference
-
-- **pathfinding.cloud**: https://github.com/DataDog/pathfinding.cloud (use `curl | jq`, not WebFetch)
-- **Cartography schema**: `https://raw.githubusercontent.com/{org}/cartography/refs/tags/{version}/docs/root/modules/{provider}/schema.md`
-- **Neptune openCypher compliance**: https://docs.aws.amazon.com/neptune/latest/userguide/feature-opencypher-compliance.html
-- **openCypher spec**: https://github.com/opencypher/openCypher
+## Activation Contract
+
+Use this skill when editing predefined Attack Paths queries in `api/src/backend/api/attack_paths/queries/` or when designing query logic that must remain compatible with Neptune and Neo4j.
+
+## Hard Rules
+
+- Write openCypher Version 9 only; no APOC, Neptune-only extensions, fresh regex usage, `reduce()`, or `CALL ... UNION` shortcuts.
+- For predefined queries, anchor on `MATCH (aws:AWSAccount {id: $provider_uid})...` and keep every additional `MATCH` chained from `aws` or a variable already proven to belong to that scoped subgraph.
+- For custom-query behavior, do not add manual isolation boilerplate; the sanitizer injects provider labels automatically.
+- Never use internal isolation labels in query text: `_ProviderResource`, `_AWSResource`, `_Tenant_*`, `_Provider_*`.
+- Read the pinned Cartography dependency from `api/pyproject.toml` and ground every non-Prowler label, property, and relationship in that schema before writing Cypher.
+- Use `PROWLER_FINDING_LABEL` via f-string interpolation; never hardcode `ProwlerFinding`.
+- Deduplicate path nodes before the `OPTIONAL MATCH` that loads Prowler findings.
+- Register every new predefined query in the provider query list.
+
+## Decision Gates
+
+| Question | Action |
+|---|---|
+| Is this a predefined repository query? | Manually anchor on `AWSAccount {id: $provider_uid}` and preserve path connectivity for every matched node. |
+| Is this a custom query endpoint scenario? | Write plain Cypher and let the sanitizer inject provider isolation. |
+| Is the attack path privilege escalation? | Pick the correct target pattern: self-escalation, lateral user, assumable role, or PassRole + service. |
+| Is it a network exposure path? | Scope the resource first, then reach `(:Internet)-[:CAN_ACCESS]->(resource)` from that already-scoped node. |
+| Need another permission check? | Add a second policy/statement match from the already-bound principal, not a new unscoped root match. |
+| Unsure whether a label or property exists? | Stop guessing and verify against the pinned Cartography schema before proceeding. |
+
+## Execution Steps
+
+1. Read `types.py`, `registry.py`, and the provider query module to match existing constant names, registration style, and return shape.
+2. Inspect `api/pyproject.toml` for the pinned Cartography source/version and verify every non-Prowler label, property, and relationship against that schema.
+3. Choose the correct audience and pattern: predefined scoped query, custom sanitized query, privilege-escalation sub-pattern, or network-exposure pattern.
+4. Build the query definition with stable naming: `{provider}-{category}-{description}` id, uppercase constant, plain-language descriptions, and attribution only when sourced.
+5. Add inline comments, keep all matches scoped, use `PROWLER_FINDING_LABEL`, deduplicate nodes before `OPTIONAL MATCH`, and include `internet, can_access` only for network-exposure returns.
+6. Add any parameter definitions with explicit typing when non-string input is required.
+7. Register the constant in `{PROVIDER}_QUERIES` and re-check openCypher compatibility before finishing.
+
+## Output Contract
+
+- State whether the query is predefined or custom-query-oriented.
+- Name the scoping anchor used and the attack pattern selected.
+- Report which schema sources were verified locally (`api/pyproject.toml`, provider query module, pinned schema target/version).
+- Mention whether the query includes Prowler finding enrichment, network exposure return values, or multi-permission logic.
+- Confirm registration location and any parameters added.
+
+## References
+
+- `api/src/backend/api/attack_paths/queries/types.py`
+- `api/src/backend/api/attack_paths/queries/registry.py`
+- `api/src/backend/api/attack_paths/queries/{provider}.py`
+- `api/src/backend/api/attack_paths/cypher_sanitizer.py`
+- `api/src/backend/tasks/jobs/attack_paths/config.py`
+- `api/pyproject.toml`