validate-db

Validate Database Schema

Validate that Prisma schema matches the requirements specification. This skill only checks for discrepancies - it does NOT modify any files.

Purpose

Compare requirements documents with Prisma schema and report:

• ✅ Matching items
• ❌ Mismatching items (needs fix)
• ⚠️ Items requiring review

Workflow

┌─────────────────────────────────────┐
│  Step 1: Read Requirements          │
│  /docs/analysis/*.md                │
└───────────────┬─────────────────────┘
                │
                ▼
┌─────────────────────────────────────┐
│  Step 2: Read Prisma Schema         │
│  /prisma/schema/*.prisma            │
└───────────────┬─────────────────────┘
                │
                ▼
┌─────────────────────────────────────┐
│  Step 3: Compare & Report           │
│  - Missing tables                   │
│  - Missing columns                  │
│  - Type mismatches                  │
│  - Relationship issues              │
└─────────────────────────────────────┘

---

Step 1: Read Requirements

find docs/analysis -name "*.md" -type f

Extract from each document:

• Entity definitions
• Field requirements (name, type, nullable, constraints)
• Relationship descriptions
• Business rules

---

Step 2: Read Prisma Schema

find prisma/schema -name "*.prisma" -type f

Extract from schema:

• Model definitions
• Field types and attributes
• Relations and foreign keys
• Indexes

---

Step 3: Validation Checks

3.1 Entity Coverage

For each entity in requirements:

• ✅ Model exists in schema
• ❌ Model missing from schema

3.2 Field Coverage

For each required field:

• ✅ Field exists with correct type
• ❌ Field missing
• ❌ Field has wrong type
• ⚠️ Nullable mismatch

3.3 Relationship Coverage

For each relationship:

• ✅ Relation properly defined
• ❌ Relation missing
• ❌ Wrong cardinality (1:1, 1:N, N:M)
• ⚠️ Missing onDelete cascade

3.4 Naming Convention

• ✅ Table names follow {prefix}_{entities} pattern
• ❌ Inconsistent naming
• ⚠️ Non-standard field names

Boolean fields:

• ✅ Boolean fields have is_ prefix (e.g., is_active, is_public)
• ❌ Boolean field missing is_ prefix (e.g., active → is_active)

Field name consistency:

• ✅ Same-purpose fields have consistent names across tables
• ❌ Inconsistent naming (e.g., expires_at in one table, expired_at in another)
• Common fields to check: *_at timestamps, *_id foreign keys, status fields

3.5 Table Necessity

Compare with requirements to identify unnecessary tables:

Duplicate tables:

• ❌ Multiple tables serving same purpose
• ⚠️ Tables with overlapping responsibilities

Unnecessary snapshot/history tables:

• ❌ Snapshot table exists but requirements say data should be directly editable
• ❌ History table without audit/versioning requirement
• ✅ Snapshot table exists and requirements specify versioning/history

---

Output Format

# Validation Report: Database Schema

## Summary
- Total entities in requirements: X
- Entities found in schema: Y
- Missing entities: Z

## ✅ Valid Items
- [Entity] `users` - All fields match
- [Entity] `posts` - All fields match

## ❌ Issues Found
- [Missing Entity] `comments` - Defined in requirements but not in schema
- [Missing Field] `users.phone` - Required but not defined
- [Type Mismatch] `posts.view_count` - Expected Int, found String
- [Naming] `users.active` - Boolean field should be `is_active`
- [Naming] `sessions.expired_at` - Inconsistent with `tokens.expires_at`
- [Unnecessary Table] `post_snapshots` - Requirements say posts are directly editable

## ⚠️ Warnings
- [Nullable] `users.bio` - Requirements say required, schema allows null
- [Index] `posts` - Consider adding index on `created_at`
- [Duplicate] `user_profiles` and `users` - May have overlapping responsibilities

## Recommendation
Run `/fix-db` to fix the issues above.

---

Important

This skill is READ-ONLY.

• Does NOT modify any files
• Does NOT run any build commands
• Only reports discrepancies

To fix issues, use /fix-db skill.