Database Overview

Next.js Application
    |
    |--- Server Components ──> Repository / Query Layer ──> Prisma Client
    |--- API Routes ─────────> Repository / Query Layer ──> Prisma Client
    |--- Server Actions ─────> Repository / Query Layer ──> Prisma Client
    |
    v
Prisma Client (Singleton)
    |
    |--- Queries ──> Pooled Connection (pgbouncer) ──> Supabase PostgreSQL
    |--- Migrations ──> Direct Connection ───────────> Supabase PostgreSQL
    |
    v
PostgreSQL (Supabase)
    |--- pgvector extension (semantic search)
    |--- 10 tables (users, subscriptions, AI, credits, files, email)

generator client {
  provider        = "prisma-client-js"
  binaryTargets   = ["native", "rhel-openssl-3.0.x"]
  previewFeatures = ["postgresqlExtensions"]
}

datasource db {
  provider   = "postgresql"
  // Use pooled connection for queries (with pgbouncer)
  url        = env("DATABASE_URL")
  // Use direct connection for migrations (without pgbouncer)
  directUrl  = env("DIRECT_URL")
  extensions = [vector]
}

# Pooled connection (for queries) — port 6543 on Supabase
DATABASE_URL="postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true"

# Direct connection (for migrations) — port 5432 on Supabase
DIRECT_URL="postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"

const prismaClientSingleton = () => {
  // Check for test/demo environment
  // Demo mode uses mock data, no real database needed
  const isDemoMode = process.env.NEXT_PUBLIC_DEMO_MODE === 'true'
  const isClerkDisabled = process.env.NEXT_PUBLIC_CLERK_ENABLED === 'false'
  const isTestEnvironment = isDemoMode || isClerkDisabled

  // In test/demo environment, use mock client to avoid database connection
  if (isTestEnvironment) {
    console.log('[TEST/DEMO] Using mock Prisma client - no database connection')
    return createMockPrismaClient()
  }

  return new PrismaClient({
    // Optimize for serverless/edge environments
    log:
      process.env.NODE_ENV === 'development'
        ? ['query', 'error', 'warn']
        : ['error'],
    // Error formatting
    errorFormat: process.env.NODE_ENV === 'development' ? 'pretty' : 'minimal',
  })
}

// Prevent multiple instances during development hot reload
export const prisma = globalForPrisma.prisma ?? prismaClientSingleton()

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

import { prisma } from '@/lib/db/prisma'

// Use in Server Components, API routes, or Server Actions
const user = await prisma.user.findUnique({
  where: { clerkId: userId }
})

export abstract class RepositoryBase<T> {
  /**
   * Detects if application is running in test mode
   *
   * Test mode is detected by:
   * - NODE_ENV === 'test' (but overridden by DISABLE_REPOSITORY_MOCKS)
   * - NEXT_PUBLIC_CLERK_ENABLED === 'false' (E2E test environment)
   *
   * CRITICAL: Unit tests set DISABLE_REPOSITORY_MOCKS=true to use Prisma mocks.
   * This allows unit tests to have full control over database behavior while
   * E2E tests use Repository mock data (no database required).
   *
   * @returns true if in test mode, false for production/development/unit-tests
   */
  protected isTestMode(): boolean {
    // CRITICAL: Allow unit tests to disable repository mocking
    // Unit tests mock Prisma directly and need full control over data flow
    if (process.env.DISABLE_REPOSITORY_MOCKS === 'true') {
      return false
    }

    // E2E tests and other test scenarios use repository mocks
    return (
      process.env.NODE_ENV === 'test' ||
      process.env.NEXT_PUBLIC_CLERK_ENABLED === 'false'
    )
  }

  /**
   * Returns mock data for test environment
   *
   * Each repository must implement this method to provide
   * realistic test data that matches production schema.
   *
   * @returns Mock data instance
   */
  protected abstract getMockData(): T

  /**
   * Optional: Get mock data with custom properties
   *
   * Allows repositories to provide flexible mock data generation
   *
   * @param overrides - Partial properties to override defaults
   * @returns Mock data with overrides applied
   */
  protected getMockDataWithOverrides(overrides?: Partial<T>): T {
    return {
      ...this.getMockData(),
      ...overrides,
    } as T
  }
}

import { userRepository } from '@/lib/db/repositories'

// Works in production (real queries) and tests (mock data) — no code changes needed
const user = await userRepository.findByClerkId(clerkId)
const userWithSub = await userRepository.findByClerkIdWithSubscription(clerkId)

import { prisma } from '@/lib/db/prisma'
import { userRepository } from '@/lib/db/repositories'
import type { User } from '@prisma/client'

/**
 * Get user by Clerk ID
 */
export async function getUserByClerkId(clerkId: string): Promise<User | null> {
  return userRepository.findByClerkId(clerkId)
}

/**
 * Get user by ID
 */
export async function getUserById(id: string): Promise<User | null> {
  return userRepository.findById(id)
}

/**
 * Create user from Clerk data
 */
export async function createUser(clerkId: string): Promise<User> {
  return prisma.user.create({
    data: {
      clerkId,
    },
  })
}

/**
 * Update user
 */
export async function updateUser(
  id: string,
  data: Partial<User>
): Promise<User> {
  return prisma.user.update({
    where: { id },
    data,
  })
}

/**
 * Delete user
 */
export async function deleteUser(id: string): Promise<User> {
  return prisma.user.delete({
    where: { id },
  })
}

/**
 * Get user bonus credit preference
 */
export async function getUserBonusPreference(
  userId: string
): Promise<{ bonusCreditsAutoUse: boolean } | null> {
  return prisma.user.findUnique({
    where: { id: userId },
    select: { bonusCreditsAutoUse: true },
  })
}

/**
 * Update user bonus credit preference
 */
export async function updateUserBonusPreference(
  userId: string,
  bonusCreditsAutoUse: boolean
): Promise<{ bonusCreditsAutoUse: boolean }> {
  return prisma.user.update({
    where: { id: userId },
    data: { bonusCreditsAutoUse },
    select: { bonusCreditsAutoUse: true },
  })
}

import { prisma } from '@/lib/db/prisma'

// Atomic credit deduction with row-level locking
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.$queryRaw`
    SELECT "creditBalance" FROM "User"
    WHERE "id" = ${userId} FOR UPDATE
  `
  await tx.user.update({
    where: { id: userId },
    data: { creditBalance: { decrement: cost } }
  })
  await tx.creditTransaction.create({
    data: { userId, amount: -cost, type: 'usage' }
  })
})

// src/app/(dashboard)/layout.tsx — Server Component

// CORRECT: Direct database query (< 100ms)
const user = await userRepository.findByClerkId(clerkId)

// WRONG: Self-referencing fetch — server blocks waiting on itself (7+ seconds!)
const res = await fetch('http://localhost:3000/api/users/me')