ai-schema

$npx mdskill add arcasilesgroup/ai-engineering/ai-schema

Designs database schemas and manages migrations for PostgreSQL, MySQL, SQLite, MongoDB

  • Solves the problem of designing or modifying database schemas efficiently.
  • Depends on SQL dialect-specific tools like psql, mysql, sqlite3, mongosh.
  • Decides what to do by analyzing schema designs, query performance, and data lifecycle requirements.
  • Delivers optimized schemas, migration scripts, and retention policies directly to the user.

SKILL.md

.github/skills/ai-schemaView on GitHub ↗
---
name: ai-schema
description: Designs schemas, plans safe migrations with rollback scripts, optimizes slow queries with index recommendations, defines data retention and GDPR right-to-erasure policies. Supports PostgreSQL, MySQL, SQLite, MongoDB. Trigger for 'add a column', 'we need a migration', 'the query is slow', 'define a retention policy', 'GDPR compliance for data'. Not for application-layer ORMs without DB schema; use /ai-code instead. Not for security audits; use /ai-security instead.
effort: mid
argument-hint: "design|migrate|optimize|lifecycle"
mode: agent
tags: [database, sql, migration, schema, optimization, enterprise]
requires:
  anyBins:
  - psql
  - mysql
  - sqlite3
  - mongosh
model_tier: sonnet
mirror_family: copilot-skills
generated_by: ai-eng sync
canonical_source: .claude/skills/ai-schema/SKILL.md
edit_policy: generated-do-not-edit
---



# Database Engineering

Schema design, safe migration generation, query optimization, and data lifecycle management. Multi-DB: PostgreSQL, MySQL, SQLite, MongoDB. Multi-ORM: SQLAlchemy, Prisma, TypeORM, Drizzle, Entity Framework, Diesel.

## When to Use

- Designing or modifying database schemas.
- Planning safe migrations with rollback.
- Optimizing slow queries.
- Defining retention policies or archival strategies.
- NOT for infrastructure provisioning -- no infra skill exists.

## Process

Step 0 (load contexts): read `.ai-engineering/manifest.yml` `providers.stacks`; load `.ai-engineering/overrides/<stack>/conventions.md` for each stack and `.ai-engineering/overrides/_shared/conventions.md`; load `.ai-engineering/team/*.md` for team conventions.

## Modes

### design -- Schema Design

1. **Analyze data model** -- entities, relationships, access patterns, data volume, growth projections.
2. **Apply normalization** -- 3NF+ by default. Document denormalization decisions with rationale.
3. **Design schema** -- tables, indexes, constraints, partitioning for large tables.
4. **Validate referential integrity** -- every FK has a matching PK, cascade rules defined.
5. **Output**: DDL script + entity relationship description.

### migrate -- Safe Migrations

1. **Assess impact** -- locking impact, backward compatibility, data volume affected.
2. **Use expand-contract** -- for breaking changes (add new, migrate data, drop old).
3. **Generate forward migration** -- with explicit transaction boundaries.
4. **Generate rollback migration** -- ALWAYS required. No migration ships without rollback.
5. **Test migration** -- verify on representative data volume.
6. **Output**: forward script, rollback script, execution plan.

### optimize -- Query Optimization

1. **Analyze execution plan** -- `EXPLAIN ANALYZE` (PostgreSQL), `EXPLAIN` (MySQL).
2. **Identify bottlenecks** -- sequential scans, missing indexes, N+1 patterns.
3. **Recommend indexes** -- composite indexes based on query patterns, partial indexes for filtered queries.
4. **Connection pool tuning** -- pool size, timeout, idle connection management.
5. **Output**: optimized query, index recommendations, before/after execution plan.

### lifecycle -- Data Lifecycle

1. **Retention policies** -- define per-table retention based on regulatory requirements.
2. **Archival strategies** -- partition-based archival, cold storage migration.
3. **GDPR compliance** -- right to erasure procedures, data anonymization.
4. **Multi-DB architecture** -- read replicas, caching layers, write distribution.
5. **Output**: lifecycle policy document, archival procedures.

## Quick Reference

```
/ai-schema design           # schema design with normalization
/ai-schema migrate          # safe migration with rollback
/ai-schema optimize         # query optimization with EXPLAIN
/ai-schema lifecycle        # retention and archival policies
```

## Common Mistakes

- Shipping migrations without rollback scripts -- always generate both.
- Adding indexes without checking write impact -- indexes speed reads but slow writes.
- Denormalizing without documenting why -- future developers will re-normalize.
- Running DDL without `--dry-run` first -- destructive DDL requires explicit user approval.

## Examples

### Example 1 — design + safe migration

User: "we need to add a soft-delete column to users with a backfill"

```
/ai-schema migrate
```

Generates the up + down migration, default-backed-fill-strategy, lock-impact analysis, rollback script, dry-run preview.

### Example 2 — optimize a slow query

User: "this analytics query is 5 seconds — make it fast"

```
/ai-schema optimize
```

EXPLAIN ANALYZE, identifies missing indexes, recommends index with write-impact estimate, verifies with re-run.

## Integration

Calls: `psql` / `mysql` / `sqlite3` / `mongosh` (verification). Triggers: `/ai-security` (injection pattern review). Integrates with: ORM migration systems (Alembic, Prisma Migrate, EF Migrations). See also: `/ai-security`, `/ai-governance` (destructive DDL approval).

## References

- `.ai-engineering/manifest.yml` -- governance rules for destructive operations.

$ARGUMENTS

More from arcasilesgroup/ai-engineering

SkillDescription
ai-adviseProactive governance advisor — checks standards, decisions, and quality trends during development. Always advisory, NEVER blocks. Three modes: `advise` (post-edit), `gate` (pre-dispatch), `drift` (on-demand decision audit). Trigger for 'governance check', 'advise on this change', 'check for drift', 'is this aligned with active decisions', 'shift-left advisory'. Not for blocking gates — use /ai-verify. Not for narrative code review — use /ai-review.
ai-analyze-permissionsUse when Claude Code keeps asking to approve commands you have already approved, when settings.local.json has grown large, or when you want to consolidate permission grants into wildcard patterns. Trigger for 'too many permission prompts', 'clean up permissions', 'audit my settings', 'consolidate allow rules'. Claude Code only — not available in GitHub Copilot, Antigravity, or Codex.
ai-animationDesigns motion, transitions, and micro-interactions for UI components: spring animations, gestures, easing, staggers — taste-driven detail compounding. Trigger for 'animate this', 'add transitions', 'micro-interactions for', 'gesture design', 'swipe to dismiss', 'easing for this', 'stagger the'. Not for design systems; use /ai-design instead. Not for visual art; use /ai-visual instead. Not for testing animation code; use /ai-test instead.
ai-autopilotDelivers large multi-concern specs and backlog runs autonomously: decomposes specs into sub-specs (or normalizes work items into a backlog DAG), deep-plans with parallel agents, builds a dependency DAG, implements in waves, runs a single final quality loop with one bounded quality-remediation pass (verify+guard+review on full changeset), delivers via PR. Trigger for 'implement spec-NNN end to end', 'autopilot this', 'autonomous delivery', 'decompose and ship', 'run the backlog', 'execute these GitHub issues', 'process the sprint backlog'. Invocation is the approval gate. Not for small or single-concern tasks; use /ai-build instead. Not for ambiguous requirements; use /ai-brainstorm first.
ai-boardOperates the project board (GitHub Projects v2 or Azure DevOps): discovers configuration after install (fields, state mappings, process templates) and syncs work-item state at lifecycle transitions. Trigger for 'set up the board', 'configure our ADO board', 'discover board fields', 'move this issue to in-review', 'update the board', 'mark as in progress', 'sync the work item state'. Two subcommands: `discover` (post-install configuration write) and `sync` (lifecycle state transitions). Auto-invoked via `sync` by /ai-brainstorm, /ai-build, and /ai-pr; fail-open. Not for backlog execution; use /ai-autopilot --backlog instead.
ai-brainstormForces rigorous design interrogation BEFORE any code: explores approaches, surfaces ambiguity, gathers evidence, produces an approved spec that becomes the contract for /ai-plan. Trigger for 'lets add X', 'how should we handle Y', 'whats the best approach', 'I am thinking about', 'what should we build for'. Not for existing approved specs; use /ai-plan instead. Not for execution; use /ai-build instead.
ai-branch-cleanupCleans branches safely: switches to the default branch, prunes merged and squash-merged branches, syncs to remote, sweeps stale specs, rotates `.ai-engineering/runtime/` per retention policy. Trigger for 'tidy up', 'tidy branches', 'sync to main', 'delete old branches', 'start fresh', 'rotate runtime'. Auto-invoked by /ai-pr after merge. Not for committing changes; use /ai-commit instead. Not for code-level dead-code removal; use /ai-simplify instead.
ai-buildCanonical implementation gateway: reads approved plan.md, resolves stack from manifest, deterministic-routes each task to its adapter, dispatches the build agent in an isolated worktree, runs TDD self-validation per task, then a single final quality loop with one bounded quality-remediation pass on the full changeset before /ai-pr. Trigger for 'go', 'start building', 'execute the plan', 'implement it', 'lets do this', 'build the plan', 'resume', 'continue'. Not without an approved plan; run /ai-plan first. Not for multi-concern specs needing decomposition; use /ai-autopilot instead. Not for a single function or subcomponent; use /ai-code.
ai-codeWrites production code that satisfies stack-context standards on the first pass: interface-first design, backward-compatibility checks, lightweight self-review. Trigger for 'implement this', 'write the code for', 'add X to Y', 'build this function', 'make this work'. Not for tests; use /ai-test instead. Not for debugging; use /ai-debug instead. Not for refactoring; use /ai-simplify instead. Not for executing an approved plan end-to-end; use /ai-build (the gateway).
ai-commitRuns the governed commit pipeline: auto-branches from protected, stages selectively, formats and lints, scans for secrets, gates docs, composes a conventional message, pushes. Trigger for 'commit my changes', 'save my work', 'push this to remote', 'stage these files', 'ship it'. Not for opening a PR; use /ai-pr instead. Not for branch hygiene; use /ai-branch-cleanup instead.