Prisma Schema Safety Architect

Your agent generates a Prisma schema that compiles clean but drops your entire production database on the next migration. This skill catches the 12 anti-patterns before they become incidents.

The Problem

Your AI agent generates a Prisma schema with a User model (no unique on email), a Post model (no index on authorId), and onDelete: Cascade on the Comment→Post relation. The dev migration runs clean. You push to production and: (1) two users sign up simultaneously, creating duplicate emails with no constraint to catch them, (2) the Post list page takes 8 seconds because every query does a full table scan on authorId, (3) deleting a Post silently destroys all its Comments, including audit logs that legal requires you to retain. The schema "works" in isolation and fails under real conditions.

What You Get

• 12 anti-pattern detection — scans for missing unique constraints on business keys, unsafe cascading deletes, missing indexes on foreign keys, N+1 query patterns, unguarded concurrent writes (no optimistic locking), soft delete without query filters, enum drift, missing map annotations, optional relations that should be required, unbounded text fields, datetime without timezone handling, and the Prisma migration reset danger
• 9-point pre-migration safety checklist — mandatory verification: backup exists, no prisma migrate reset on production, reversible migration, no data loss, safe index creation with CONCURRENTLY, valid foreign key constraints, default values provided, backward-compatible enum changes, and correct environment target
• Production-grade schema patterns — multi-tenant isolation with tenantId and composite unique constraints, audit trail with immutable Json records and soft-delete preservation, optimistic locking with version fields and atomic updates, and soft delete with Prisma client extension auto-filtering
• Emergency recovery procedures — step-by-step response for Prisma migration failures: stop, check status, restore from backup, mark migration resolved, post-mortem documentation
• Schema review protocol — 6-step systematic review: read full schema, anti-pattern scan, index audit, relation audit, migration preview with prisma migrate diff, test on copy

Why This Beats Prompting It Yourself

When you ask an agent to "design a Prisma schema for users and posts," it generates syntactically correct code that compiles — but may omit the unique constraint on email (race condition), the index on authorId (N+1 query), or the onDelete: Restrict policy (cascade data loss). The 12 anti-pattern checklist catches these patterns because they're specific to Prisma's ORM behavior — Prisma does NOT auto-index foreign keys, prisma migrate dev can reset production databases, and onDelete: Cascade destroys audit logs silently. No general-purpose prompt encodes these Prisma-specific failure modes.

Example Session

You: Review this Prisma schema for production safety.

Agent: Anti-Pattern Scan: 4 findings.

1. User.email — Missing unique constraint. Concurrent signups create duplicate emails. Fix: email String @unique
2. Post.authorId — Missing index. Every where: { authorId } does a full table scan. Fix: authorId Int @db.Integer + @@index([authorId])
3. Comment → Post — Unsafe cascade. onDelete: Cascade destroys all comments when post is deleted. Fix: onDelete: Restrict
4. User.balance — Unguarded concurrent write. Two requests read balance=100, both write 110 — money disappears. Fix: Add version Int @default(0) and use prisma.user.updateMany({ where: { id, version }, data: { balance: { increment: amount }, version: { increment: 1 } } })

Pre-Migration Checklist: 9/9 passed after fixes applied. Schema is safe for migration.

Use Cases

• Reviewing an AI-generated Prisma schema before first migration to production
• Implementing multi-tenant isolation with proper composite unique constraints
• Adding soft delete with automatic query filtering via Prisma client extensions
• Setting up optimistic locking for concurrent balance or inventory updates
• Creating an immutable audit trail with Json-based change tracking

Known Limitations

The anti-pattern scanner works by pattern-matching Prisma schema syntax and cannot detect application-level issues like missing error handling for P2002 (unique constraint violation) in API routes. Schema-level safety is necessary but not sufficient — application code must also handle Prisma error codes correctly. The pre-migration checklist requires manual verification steps that cannot be fully automated.

---

Tags: prisma database schema-design data-integrity race-condition typescript full-stack

Version: 1.0.0