---
name: rune-db
description: Rune db workflow (compiled from Rune kit)
tags: [rune, db]
---

# rune-db

> Rune L2 Skill | development


# db

> **RUNE COMPLIANCE**: Before ANY code response, you MUST:
> 1. Classify this request (CODE_CHANGE | QUESTION | DEBUG | REVIEW | EXPLORE)
> 2. Route through the correct Rune skill (see skill-router routing table)
> 3. Follow the skill's workflow — do NOT freelance or skip steps
> Violation: writing code without skill routing = incorrect behavior.

## Platform Constraints

- SHOULD: Monitor your context usage. If working on a long task, summarize progress before context fills up.
- MUST: Before summarizing/compacting context, save important decisions and progress to project files.
- SHOULD: Before ending, save architectural decisions and progress to .rune/ directory for future sessions.

## Purpose

Database workflow specialist. Handles the parts of database work that cause production incidents — breaking schema changes, migrations without rollback, raw SQL injection vectors, and missing indexes on growing tables. Acts as a pre-deploy gate for any schema change, and generates correct migration files (up + down) for common ORMs.

## Triggers

- `/rune db` — manual invocation when schema changes are planned
- Called by `cook` (L1): schema change detected in diff
- Called by `deploy` (L2): pre-deploy migration safety check
- Called by `audit` (L2): database health dimension

## Calls (outbound)

- `scout` (L2): find schema files, migration files, ORM config
- `verification` (L3): run migration in test environment if configured
- `hallucination-guard` (L3): verify SQL syntax and ORM method names

## Called By (inbound)

- `cook` (L1): schema change detected in diff
- `deploy` (L2): pre-deploy migration safety check
- `audit` (L2): database health dimension

## References

- `references/scaling-reference.md` — Index strategies, query optimization, N+1 prevention, connection pooling, read replicas, partitioning, sharding, denormalization. Load when scaling, performance, or indexing context detected.

## Executable Steps

### Step 1 — Discovery

Invoke `scout` to locate:
- Schema definition files: `*.sql`, `schema.prisma`, `models.py`, `*.migration.ts`, `db/migrate/*.rb`
- Migration directory and existing migration files (to determine next migration number)
- ORM in use: **Prisma** | **TypeORM** | **SQLAlchemy/Alembic** | **Django ORM** | **ActiveRecord** | **raw SQL** | **unknown**
- Database type: **PostgreSQL** | **MySQL** | **SQLite** | **MongoDB** | **unknown**

If ORM cannot be determined with confidence, fall back to generic SQL migration format.

### Step 2 — Diff Analysis

Read current schema and compare against previous version (git diff if available):
- List all **added** columns, tables, indexes, constraints
- List all **removed** columns, tables, indexes
- List all **modified** columns (type changes, nullability changes, default changes)
- List all **renamed** columns or tables

### Step 3 — Breaking Change Detection

Classify each change by impact:

| Change | Classification | Why |
|--------|---------------|-----|
| ADD COLUMN NOT NULL without DEFAULT | **BREAKING** | Fails on existing rows |
| DROP COLUMN | **BREAKING** | Irreversible data loss |
| RENAME COLUMN or TABLE | **BREAKING** | Breaks all existing queries |
| CHANGE column type (e.g. VARCHAR→INT) | **BREAKING** | Data truncation risk |
| ADD COLUMN nullable | SAFE | Existing rows get NULL |
| ADD TABLE | SAFE | No impact on existing data |
| ADD INDEX | SAFE (but may lock table) | Lock risk on large tables |
| DROP INDEX | SAFE | Slight query slowdown |
| DROP TABLE | **BREAKING** | Irreversible data loss |

For any **BREAKING** change: output `BREAKING: [change description]` and require explicit user confirmation before generating migration.

<HARD-GATE>
Migration adding NOT NULL column to existing table without DEFAULT value = BLOCK.
Column rename or type change on data-bearing table = BREAKING — emit warning and require confirmation before proceeding.
Empty downgrade/rollback function = BLOCK — every migration MUST have a working down/rollback path.
</HARD-GATE>

### Step 4 — Migration Generation

For each schema change, generate a migration file with **up** (apply) and **down** (rollback) scripts.

**Prisma:**
```typescript
// migrations/[timestamp]_[description]/migration.sql
-- Up
ALTER TABLE "users" ADD COLUMN "avatar_url" TEXT;

-- Down (in separate migration file or comment)
ALTER TABLE "users" DROP COLUMN "avatar_url";
```

**Django / Alembic:**
```python
def upgrade():
    op.add_column('users', sa.Column('avatar_url', sa.Text(), nullable=True))

def downgrade():
    op.drop_column('users', 'avatar_url')
# NEVER leave downgrade() empty — HARD-GATE blocks this
```

**TypeORM:**
```typescript
public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.addColumn('users', new TableColumn({
        name: 'avatar_url', type: 'text', isNullable: true
    }));
}
public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropColumn('users', 'avatar_url');
}
```

**Raw SQL:**
```sql
-- up.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;
-- down.sql
ALTER TABLE users DROP COLUMN avatar_url;
```

Use `hallucination-guard` to verify syntax of generated SQL and ORM method names before writing.

### Step 5 — Index Analysis

For every new table or column added, check:
- Foreign key columns without index → flag `MISSING_INDEX: [column] — add index for JOIN performance`
- High-cardinality columns used in WHERE clauses (email, user_id, status) without index → flag `CONSIDER_INDEX`
- Composite indexes: if queries filter on (A, B), index should be on (A, B) not just A

For existing tables with new query patterns:
- If query uses ORDER BY [column] on large table without index → flag `SORT_INDEX_MISSING`

### Step 6 — Query Parameterization Scan

Scan migration files and any raw SQL files for injection vectors:

```python
# BAD: string interpolation in SQL
query = f"SELECT * FROM users WHERE email = '{email}'"

# GOOD: parameterized
query = "SELECT * FROM users WHERE email = %s"
cursor.execute(query, (email,))
```

Finding: `SQL_INJECTION_RISK — [file:line] — string interpolation in query — use parameterized query`

### Step 7 — Schema Documentation

Update or create `.rune/schema-changelog.md` with a human-readable entry:

```markdown
## [date] — [migration name]
- Added: [column list]
- Removed: [column list — note if data was migrated]
- Breaking: [yes/no] — [details if yes]
- Rollback: [migration name or "manual"]
```

### Step 8 — Report

Emit structured report:

```
## DB Report: [scope]

### Schema Changes
- [SAFE|BREAKING] [change description]

### Breaking Changes Requiring Confirmation
- BREAKING: [description] — requires explicit approval before migration runs

### Generated Files
- [migration file path] (up + down)

### Index Recommendations
- MISSING_INDEX: [table.column] — [reason]

### Query Safety
- SQL_INJECTION_RISK: [file:line] — [description]
- Clean: [list of checked files with no issues]

### Verdict: PASS | WARN | BLOCK
```

## Output Format

```
## DB Report: schema.prisma diff

### Schema Changes
- SAFE: Added users.avatar_url (TEXT, nullable)
- BREAKING: Renamed users.created → users.created_at

### Breaking Changes Requiring Confirmation
- BREAKING: Column rename users.created → users.created_at
  Impact: all queries referencing 'created' will break
  Confirm before proceeding? [yes/no]

### Generated Files
- migrations/20260224_add_avatar_url/migration.sql (up + down)

### Index Recommendations
- MISSING_INDEX: users.email — high-cardinality FK, add for login query performance

### Verdict: BLOCK (breaking change unconfirmed)
```

## Constraints

1. MUST generate both up and down scripts for every migration — empty rollback = BLOCK
2. MUST flag NOT NULL without DEFAULT as BLOCK — never silently generate broken migration
3. MUST NOT run migration in production — only in test environment (via verification)
4. MUST use hallucination-guard to verify SQL syntax before writing migration files
5. MUST NOT rename columns silently — always present impact and require confirmation

## Mesh Gates (L1/L2 only)

| Gate | Requires | If Missing |
|------|----------|------------|
| ORM Gate | ORM identified before migration generation | Fall back to raw SQL format + note |
| Breaking Gate | User confirmation before proceeding on BREAKING changes | BLOCK and await response |
| Rollback Gate | Working down() / rollback script before writing migration | BLOCK — prompt for rollback logic |
| Safety Gate | hallucination-guard verified SQL before Write | Re-verify or flag as unverified |

## Sharp Edges

Known failure modes for this skill. Check these before declaring done.

| Failure Mode | Severity | Mitigation |
|---|---|---|
| Empty downgrade() written silently | CRITICAL | HARD-GATE: never write empty rollback — always prompt for rollback logic |
| NOT NULL column added without DEFAULT on existing table | CRITICAL | HARD-GATE: BLOCK and explain that this will fail on existing rows |
| Migration generated for wrong ORM (TypeORM syntax in Django project) | HIGH | hallucination-guard verifies method names match detected ORM |
| Index recommendations skipped on large tables | MEDIUM | Always run Step 5 — never skip index analysis |
| Schema changelog not updated after migration | LOW | Step 7 runs always — log INFO if skipped due to no .rune/ directory |

## Done When

- All schema changes classified (SAFE vs BREAKING)
- Breaking changes surfaced and confirmed (or BLOCK issued)
- Migration files generated with working up + down scripts
- hallucination-guard verified SQL syntax
- Index recommendations listed
- Query parameterization scan complete
- Schema changelog updated in .rune/schema-changelog.md
- Structured DB Report emitted with PASS/WARN/BLOCK verdict

## Returns

| Artifact | Format | Location |
|----------|--------|----------|
| Migration file (up) | SQL or ORM-specific | `migrations/<timestamp>_<name>/` |
| Rollback script (down) | SQL or ORM-specific | same migration directory |
| Schema changelog entry | Markdown | `.rune/schema-changelog.md` |
| Index recommendations | Structured list | inline (DB Report) |
| DB Report with verdict | Markdown (PASS/WARN/BLOCK) | inline |

## Cost Profile

~2000-6000 tokens input, ~800-2000 tokens output. Sonnet for migration generation quality.

**Scope guardrail:** db generates and validates migrations — it does not run them in production. Execution is delegated to `verification` in test environments only.

---
> **Rune Skill Mesh** — 59 skills, 200+ connections, 14 extension packs
> [Landing Page](https://rune-kit.github.io/rune) · [Source](https://github.com/rune-kit/rune) (MIT)
> **Rune Pro** ($49 lifetime) — product, sales, data-science, support packs → [rune-kit/rune-pro](https://github.com/rune-kit/rune-pro)
> **Rune Business** ($149 lifetime) — finance, legal, HR, enterprise-search packs → [rune-kit/rune-business](https://github.com/rune-kit/rune-business)