Db

db

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:

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

Step 4 — Migration Generation

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

Prisma:

typescriptCopy code
// 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:

pythonCopy code
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:

typescriptCopy code
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:

sqlCopy code
-- 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:

pythonCopy code
# 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:

markdownCopy code
## [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:

Copy code
## 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

Copy code
## 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)

Sharp Edges

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

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

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.