Claude Code for Prisma Advanced: Transactions, Extensions, and Type Safety

Prisma’s three-layer architecture separates schema definition, query generation, and the database driver. $transaction with interactive transactions lets you read-then-write atomically. Client extensions add domain logic directly to the Prisma client — soft delete, multi-tenancy row filtering, audit logging. Middleware intercepts every query for logging or transformation. Prisma Pulse streams real-time database changes as a typed async iterator. Claude Code generates Prisma schema files, client extensions, transaction patterns, and the middleware chains for production Node.js applications.

CLAUDE.md for Prisma Projects

## Prisma Stack
- Version: prisma >= 5.0 with prisma-client-js
- Database: PostgreSQL (primary), SQLite (tests)
- Extensions: $extends for soft delete, tenant isolation, audit logging
- Transactions: $transaction for atomic multi-table operations
- Testing: jest-mock-extended or manual mock via DI
- Migrations: prisma migrate dev (local), prisma migrate deploy (CI/CD)
- Seeding: prisma/seed.ts with @faker-js/faker for realistic data
- Client: singleton pattern in lib/db.ts (prevent hot-reload connections)

Schema with Advanced Features

// prisma/schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters", "relationJoins"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  tenantId  String
  tenant    Tenant   @relation(fields: [tenantId], references: [id])
  orders    Order[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?  // Soft delete

  @@index([tenantId])
  @@index([email, tenantId])
  @@map("users")
}

model Order {
  id          String      @id @default(cuid())
  status      OrderStatus @default(PENDING)
  totalCents  Int
  userId      String
  user        User        @relation(fields: [userId], references: [id])
  tenantId    String
  items       OrderItem[]
  auditLogs   AuditLog[]
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt
  deletedAt   DateTime?

  @@index([userId, createdAt])
  @@index([tenantId, status])
  @@map("orders")
}

model AuditLog {
  id        String   @id @default(cuid())
  orderId   String
  order     Order    @relation(fields: [orderId], references: [id])
  action    String
  before    Json?
  after     Json?
  userId    String
  createdAt DateTime @default(now())

  @@index([orderId])
  @@map("audit_logs")
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

enum Role {
  USER
  ADMIN
  SUPER_ADMIN
}

Singleton Client with Extensions

// lib/db.ts — Prisma client singleton with extensions
import { PrismaClient, Prisma } from '@prisma/client'

function createPrismaClient() {
  return new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  })
}

// Prevent multiple connections in Next.js hot reload
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prismaBase = globalForPrisma.prisma ?? createPrismaClient()

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prismaBase

// Extended client with domain capabilities
export const db = prismaBase.$extends({
  name: 'soft-delete',
  model: {
    $allModels: {
      // Soft delete: sets deletedAt instead of DELETE
      async softDelete<T>(this: T, where: Prisma.Args<T, 'delete'>['where']) {
        const context = Prisma.getExtensionContext(this)
        return (context as any).update({
          where,
          data: { deletedAt: new Date() },
        })
      },

      // Restore soft-deleted record
      async restore<T>(this: T, where: Prisma.Args<T, 'update'>['where']) {
        const context = Prisma.getExtensionContext(this)
        return (context as any).update({
          where,
          data: { deletedAt: null },
        })
      },
    },
  },
  query: {
    $allModels: {
      // Automatically exclude soft-deleted records
      findMany({ model, operation, args, query }) {
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
      findFirst({ args, query }) {
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
      findUnique({ args, query }) {
        return query(args)  // findUnique uses ID — don't filter
      },
      count({ args, query }) {
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
    },
  },
})

Multi-Tenant Client Extension

// lib/tenant-db.ts — scoped client per tenant
import { db } from './db'

export function createTenantClient(tenantId: string) {
  return db.$extends({
    name: 'tenant-isolation',
    query: {
      $allModels: {
        async findMany({ args, query }) {
          args.where = { ...args.where, tenantId }
          return query(args)
        },
        async findFirst({ args, query }) {
          args.where = { ...args.where, tenantId }
          return query(args)
        },
        async create({ args, query }) {
          if ('data' in args) {
            args.data = { ...args.data, tenantId }
          }
          return query(args)
        },
        async createMany({ args, query }) {
          if (Array.isArray(args.data)) {
            args.data = args.data.map(d => ({ ...d, tenantId }))
          }
          return query(args)
        },
        async update({ args, query }) {
          args.where = { ...args.where, tenantId }
          return query(args)
        },
        async delete({ args, query }) {
          args.where = { ...args.where, tenantId }
          return query(args)
        },
      },
    },
  })
}

// Usage in API route — tenant automatically enforced
// const tenantDb = createTenantClient(req.user.tenantId)
// const orders = await tenantDb.order.findMany()  // WHERE tenantId = ?

Interactive Transactions

// lib/order-service.ts
import { db } from './db'
import { Prisma, OrderStatus } from '@prisma/client'

export async function createOrderWithInventory(input: {
  userId: string
  tenantId: string
  items: Array<{ productId: string; quantity: number; priceCents: number }>
}) {
  // Interactive transaction: read-then-write atomically
  return db.$transaction(async (tx) => {
    // 1. Verify all products exist and have sufficient stock
    for (const item of input.items) {
      const product = await tx.product.findUnique({
        where: { id: item.productId },
        select: { id: true, stockQuantity: true, name: true },
      })

      if (!product) {
        throw new Error(`Product ${item.productId} not found`)
      }

      if (product.stockQuantity < item.quantity) {
        throw new Error(`Insufficient stock for ${product.name}`)
      }
    }

    // 2. Create order
    const totalCents = input.items.reduce(
      (sum, item) => sum + item.priceCents * item.quantity,
      0
    )

    const order = await tx.order.create({
      data: {
        userId: input.userId,
        tenantId: input.tenantId,
        totalCents,
        status: OrderStatus.PENDING,
        items: {
          create: input.items.map(item => ({
            productId: item.productId,
            quantity: item.quantity,
            priceCents: item.priceCents,
          })),
        },
      },
      include: {
        items: true,
        user: { select: { name: true, email: true } },
      },
    })

    // 3. Decrement stock atomically
    for (const item of input.items) {
      await tx.product.update({
        where: { id: item.productId },
        data: { stockQuantity: { decrement: item.quantity } },
      })
    }

    // 4. Create audit log
    await tx.auditLog.create({
      data: {
        orderId: order.id,
        action: 'ORDER_CREATED',
        after: { status: order.status, totalCents },
        userId: input.userId,
      },
    })

    return order
  }, {
    isolationLevel: Prisma.TransactionIsolationLevel.ReadCommitted,
    timeout: 10000,  // 10 second max
  })
}


export async function cancelOrder(orderId: string, userId: string, reason: string) {
  return db.$transaction(async (tx) => {
    const order = await tx.order.findUnique({
      where: { id: orderId },
      include: { items: true },
    })

    if (!order) throw new Error('Order not found')
    if (!['PENDING', 'PROCESSING'].includes(order.status)) {
      throw new Error(`Cannot cancel order with status ${order.status}`)
    }

    const before = { status: order.status }

    // Update order status
    const updated = await tx.order.update({
      where: { id: orderId },
      data: { status: OrderStatus.CANCELLED },
    })

    // Restore inventory
    for (const item of order.items) {
      await tx.product.update({
        where: { id: item.productId },
        data: { stockQuantity: { increment: item.quantity } },
      })
    }

    // Audit log
    await tx.auditLog.create({
      data: {
        orderId,
        action: 'ORDER_CANCELLED',
        before,
        after: { status: OrderStatus.CANCELLED, reason },
        userId,
      },
    })

    return updated
  })
}

Middleware for Logging and Timing

// lib/db-with-middleware.ts
import { prismaBase } from './db'

prismaBase.$use(async (params, next) => {
  const start = Date.now()
  const result = await next(params)
  const duration = Date.now() - start

  if (duration > 100) {
    console.warn('Slow query:', {
      model: params.model,
      action: params.action,
      duration: `${duration}ms`,
      args: JSON.stringify(params.args).slice(0, 200),
    })
  }

  return result
})

// Middleware: log all mutations
prismaBase.$use(async (params, next) => {
  const writeMutations = ['create', 'createMany', 'update', 'updateMany', 'delete', 'deleteMany']

  if (params.model && writeMutations.includes(params.action)) {
    console.log(`[DB] ${params.model}.${params.action}`)
  }

  return next(params)
})

Type-Safe Query Helpers

// lib/order-queries.ts — reusable typed query builders
import { Prisma } from '@prisma/client'
import { db } from './db'

// Utility type: Order with user and item count
export type OrderWithUser = Prisma.OrderGetPayload<{
  include: {
    user: { select: { id: true; name: true; email: true } }
    _count: { select: { items: true } }
  }
}>

// Paginated query helper
export async function findOrdersPaginated(params: {
  tenantId: string
  status?: string
  page?: number
  pageSize?: number
}): Promise<{ data: OrderWithUser[]; total: number; pages: number }> {
  const { tenantId, status, page = 1, pageSize = 20 } = params
  const skip = (page - 1) * pageSize

  const where: Prisma.OrderWhereInput = {
    tenantId,
    ...(status && { status: status as any }),
  }

  const [data, total] = await Promise.all([
    db.order.findMany({
      where,
      include: {
        user: { select: { id: true, name: true, email: true } },
        _count: { select: { items: true } },
      },
      orderBy: { createdAt: 'desc' },
      skip,
      take: pageSize,
    }),
    db.order.count({ where }),
  ])

  return {
    data,
    total,
    pages: Math.ceil(total / pageSize),
  }
}

For the database migrations and schema evolution patterns that Prisma manages, see the database migrations guide for zero-downtime migration strategies. For the Drizzle ORM alternative with a more SQL-like query syntax, the Drizzle ORM guide covers the schema-first approach with type inference. The Claude Skills 360 bundle includes Prisma skill sets covering client extensions, interactive transactions, and multi-tenant patterns. Start with the free tier to try Prisma schema and query generation.