database-developer

Database Developer

Expert in database design, Prisma ORM, PostgreSQL, and Redis for this monorepo.

Layer References

Schema templates, naming rules, soft-delete, query patterns, and migrations are covered in layers/project/typescript/database-patterns.md. This skill adds relation examples, Redis caching, and indexing advice not found in layers.

Enum Pattern

enum UserRole {
  OWNER
  SUPER_ADMIN
  ADMIN
  USER
}

model User {
  role UserRole @default(USER)
}

Relation Patterns

// One-to-many
model User {
  id       String    @id @default(cuid())
  products Product[]
}

model Product {
  userId String
  user   User   @relation(fields: [userId], references: [id])
}

// Many-to-many (explicit join table for extra fields)
model UserOrganization {
  userId         String
  organizationId String
  role           UserRole @default(USER)
  joinedAt       DateTime @default(now())

  user         User         @relation(fields: [userId], references: [id])
  organization Organization @relation(fields: [organizationId], references: [id])

  @@id([userId, organizationId])
}

Redis Patterns

Cache with TTL

import { redis } from "./redis";

async function getCachedUser(id: string) {
  const cached = await redis.get(`user:${id}`);
  if (cached) return JSON.parse(cached);

  const user = await db.user.findUnique({ where: { id } });
  await redis.set(`user:${id}`, JSON.stringify(user), "EX", 300); // 5 min
  return user;
}

Cache invalidation on mutation

async function updateUser(id: string, data: UpdateUserInput) {
  const user = await db.user.update({ where: { id }, data });
  await redis.del(`user:${id}`);
  return user;
}

Indexing Strategy

• Index all foreign keys (@@index([userId]))
• Index frequently filtered columns (@@index([email]), @@index([createdAt]))
• Composite indexes for common query patterns (@@index([organizationId, role]))
• Unique constraints where needed (@@unique([email]))
• Every soft-delete model must have @@index([userId, deletedAt]) — without it, WHERE deletedAt IS NULL queries degrade to full table scans as zombie rows accumulate