92-database

🗃️ Database Rules

Auto-activated for Prisma, Drizzle, and SQL files.

Schema Design

1. Naming Conventions

// ✅ GOOD
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")  // snake_case table name
}

// ✅ Relations: explicit names
model Post {
  author   User   @relation("PostAuthor", fields: [authorId], references: [id])
  authorId String @map("author_id")
}

2. Required Fields (STRICT)

Every table MUST have:

• id — Primary key (cuid or uuid preferred)
• created_at — Creation timestamp
• updated_at — Last update timestamp

For multi-tenant apps:

• tenant_id — Tenant isolation (CRITICAL)

3. Indexes

// ✅ Index frequently queried fields
model Order {
  id        String @id
  userId    String
  status    String
  createdAt DateTime

  @@index([userId])           // FK queries
  @@index([status, createdAt]) // Filtered queries
}

Migration Rules (STRICT)

1. Never Edit Existing Migrations

# ❌ NEVER modify deployed migrations
# ✅ Create new migration for changes
npx prisma migrate dev --name fix_user_email

2. Reversible Migrations

-- ✅ Always provide rollback
-- Migration: add_status_column
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- Rollback:
-- ALTER TABLE orders DROP COLUMN status;

3. Data Migrations Separate

migrations/
├── 001_add_status_column.sql  # Schema only
└── 001_backfill_status.ts     # Data migration (separate)

Query Patterns

1. Parameterized Queries (STRICT)

// ✅ ALWAYS use parameterized queries
const user = await prisma.user.findUnique({
  where: { email: sanitizedEmail }
});

// ❌ NEVER concatenate SQL
const query = `SELECT * FROM users WHERE email = '${email}'`; // SQL INJECTION!

2. Select Only Needed Fields

// ✅ GOOD: Select specific fields
const users = await prisma.user.findMany({
  select: { id: true, email: true, name: true }
});

// ❌ BAD: Select all (may include sensitive data)
const users = await prisma.user.findMany();