name

data-access-guidelines

description

Database access layer guidelines for Quantum Skincare's Prisma-based data-access library. Covers Prisma schema design, DAO patterns, UUID primary keys, PostgreSQL role-based access control (RBAC), migration workflows, type-safe queries, transaction handling, soft deletes, and testing strategies. Use when working with Prisma schema, DAOs, database migrations, or data access patterns in libs/data-access.

Data Access Guidelines - Quantum Skincare

Purpose

Quick reference for Quantum Skincare's Prisma-based data access layer, emphasizing type-safe database operations, DAO patterns, schema management, and PostgreSQL RBAC.

When to Use This Skill

Working with Prisma schema (schema.prisma)
Creating or modifying DAOs (Data Access Objects)
Running database migrations
Designing database models and relations
Implementing type-safe database queries
Working with PostgreSQL RBAC roles
Handling transactions and soft deletes
Writing database tests
Troubleshooting Prisma Client issues

Quick Start

New Model Checklist

Add model to prisma/schema.prisma
Use UUID as primary key: @id @default(uuid()) @db.Uuid
Add timestamps: created_at, updated_at
Add soft delete: deleted_at DateTime? @db.Timestamptz(6)
Define relations with proper foreign keys
Run npx prisma migrate dev --name <descriptive_name>
Generate Prisma Client: npx prisma generate
Create DAO in src/lib/dao/
Add DAO exports to src/index.ts
Write unit tests for DAO functions

New DAO Checklist

Import prisma from ../db/prisma.js
Import types from @quantum/shared-types
Define parameter interfaces
Use async/await for all database operations
Handle errors appropriately
Use transactions for multi-step operations
Filter out soft-deleted records (deleted_at: null)
Return mapped types (DB → App)
Add JSDoc comments
Write unit tests with mocked Prisma

Library Structure

libs/data-access/
├── prisma/
│   ├── schema.prisma        # Single source of truth for DB schema
│   ├── migrations/          # Version-controlled migrations
│   └── seed.ts              # Database seeding script
├── src/
│   ├── lib/
│   │   ├── dao/             # Data Access Objects
│   │   │   ├── users.ts     # User operations
│   │   │   ├── scans.ts     # Skin scan operations
│   │   │   ├── personal-info.ts  # User personal info
│   │   │   └── treatment.ts # Treatment cycles
│   │   └── db/
│   │       └── prisma.ts    # PrismaClient singleton
│   └── index.ts             # Public API exports
├── package.json
├── tsconfig.json
└── README.md                # RBAC documentation

Core Principles

1. UUID Primary Keys

All models use UUIDs for primary keys:

model User {
  id             String   @id @default(uuid()) @db.Uuid
  // ...
}

Benefits:

Globally unique identifiers
No sequential enumeration
Safe for distributed systems
Better security (non-guessable)

2. Timestamps Pattern

All models have standardized timestamps:

model User {
  created_at     DateTime  @default(now()) @db.Timestamptz(6)
  updated_at     DateTime  @updatedAt @db.Timestamptz(6)
  deleted_at     DateTime? @db.Timestamptz(6)  // For soft deletes
}

3. Soft Deletes

Never hard-delete user data - use soft deletes:

// ❌ BAD: Hard delete
await prisma.user.delete({ where: { id } });

// ✅ GOOD: Soft delete
await prisma.user.update({
  where: { id },
  data: { deleted_at: new Date() },
});

// Always filter soft-deleted records
const users = await prisma.user.findMany({
  where: { deleted_at: null },
});

4. Relations Pattern

Define bi-directional relations properly:

model User {
  id           String   @id @default(uuid()) @db.Uuid
  tier_id      String   @db.Uuid
  consent_id   String?  @unique @db.Uuid

  // Relations
  tier            Tier              @relation(fields: [tier_id], references: [id])
  personalInfo    UserPersonalInfo?  // One-to-one
  consent         UserConsent?      @relation(fields: [consent_id], references: [id])
  skinScans       SkinScan[]        // One-to-many

  @@map("users")
}

model SkinScan {
  id         String   @id @default(uuid()) @db.Uuid
  user_id    String   @db.Uuid

  // Relations
  user       User     @relation(fields: [user_id], references: [id])

  @@map("skin_scans")
}

5. DAO Pattern

Encapsulate all database access in DAOs:

// src/lib/dao/users.ts
import { prisma } from '../db/prisma.js';
import type { UserDB, UserApp } from '@quantum/shared-types';

export interface CreateUserParams {
  clerk_user_id: string;
  email: string;
  full_name: string;
  email_verified: boolean;
  tier_id?: string;
}

/**
 * Creates or updates a user from Clerk webhook data
 */
export async function upsertUserFromClerk({
  clerk_user_id,
  email,
  full_name,
  email_verified,
  tier_id,
}: CreateUserParams): Promise<UserApp> {
  // Get default tier if not provided
  let finalTierId = tier_id;
  if (!finalTierId) {
    const standardTier = await prisma.tier.findFirst({
      where: { name: 'Standard' },
    });
    if (!standardTier) {
      throw new Error('Standard tier not found');
    }
    finalTierId = standardTier.id;
  }

  // Upsert by Clerk user id
  let user;
  try {
    user = await prisma.user.upsert({
      where: { clerk_user_id },
      update: {
        email,
        full_name,
        email_verified,
        updated_at: new Date(),
        deleted_at: null, // Undelete if previously soft-deleted
      },
      create: {
        clerk_user_id,
        email,
        full_name,
        email_verified,
        tier_id: finalTierId,
      },
      include: {
        tier: true,
        consent: true,
      },
    });
  } catch (error) {
    // Handle unique constraint violations
    throw error;
  }

  // Map to app type
  return {
    id: user.id,
    email: user.email,
    fullName: user.full_name,
    tier: user.tier.name,
    consent: user.consent ? user.consent.profile : null,
  };
}

/**
 * Finds a user by Clerk user ID
 */
export async function findUserByClerkId(
  clerkUserId: string,
): Promise<UserApp | null> {
  const user = await prisma.user.findUnique({
    where: {
      clerk_user_id: clerkUserId,
      deleted_at: null, // Only active users
    },
    include: {
      tier: true,
      consent: true,
    },
  });

  if (!user) return null;

  return {
    id: user.id,
    email: user.email,
    fullName: user.full_name,
    tier: user.tier.name,
    consent: user.consent ? user.consent.profile : null,
  };
}

6. Prisma Client Singleton

Use the singleton pattern for PrismaClient:

// src/lib/db/prisma.ts
import { PrismaClient } from '@prisma/client';
import dotenv from 'dotenv';

// Load .env for local development
if (process.env.NODE_ENV !== 'production') {
  dotenv.config();
}

// Ensure DATABASE_URL exists
function ensureDatabaseUrl(): void {
  if (process.env.DATABASE_URL) return;

  // Compose from DB_* env vars if needed
  const { DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME } = process.env;
  if (DB_HOST && DB_PORT && DB_USER && DB_NAME) {
    const encodedUser = encodeURIComponent(DB_USER);
    const encodedPass = DB_PASSWORD ? `:${encodeURIComponent(DB_PASSWORD)}` : '';
    const auth = `${encodedUser}${encodedPass}`;
    const host = `${DB_HOST}:${DB_PORT}`;
    const params = process.env.DB_CONN_PARAMS || 'connection_limit=10&pool_timeout=30';
    process.env.DATABASE_URL = `postgresql://${auth}@${host}/${DB_NAME}?${params}`;
  } else {
    throw new Error('DATABASE_URL not set');
  }
}

ensureDatabaseUrl();

// Singleton pattern
declare global {
  var __PRISMA__: PrismaClient | undefined;
}

export const prisma: PrismaClient = (() => {
  if (process.env.NODE_ENV !== 'production') {
    if (!global.__PRISMA__) {
      global.__PRISMA__ = new PrismaClient();
    }
    return global.__PRISMA__;
  }
  return new PrismaClient();
})();

7. Transactions

Use transactions for multi-step operations:

export async function createUserWithConsent(params: CreateUserParams) {
  return await prisma.$transaction(async (tx) => {
    // Step 1: Create consent record
    const consent = await tx.userConsent.create({
      data: {
        profile: params.consentProfile,
        version: params.consentVersion,
        biometric: params.biometric,
        // ...
      },
    });

    // Step 2: Create user with consent_id
    const user = await tx.user.create({
      data: {
        clerk_user_id: params.clerk_user_id,
        email: params.email,
        full_name: params.full_name,
        tier_id: params.tier_id,
        consent_id: consent.id,
      },
      include: {
        tier: true,
        consent: true,
      },
    });

    return user;
  });
}

Schema Design Patterns

Enums

Define enums for fixed value sets:

enum TierName {
  Standard
  Premium
  Admin
}

enum ConsentProfile {
  GDPR
  BIPA
  CCPA
  ROW
}

model User {
  // Reference enum in model
  tier Tier @relation(fields: [tier_id], references: [id])
}

model Tier {
  name TierName @unique
}

Indexes

Add indexes for frequently queried fields:

model User {
  email         String  @unique
  clerk_user_id String  @unique

  @@index([email])
  @@index([created_at])
  @@map("users")
}

model SkinScan {
  user_id String @db.Uuid

  @@index([user_id])
  @@index([created_at])
  @@map("skin_scans")
}

Composite Keys

Use composite unique constraints when needed:

model TreatmentCycle {
  user_id      String @db.Uuid
  cycle_number Int

  @@unique([user_id, cycle_number])
  @@map("treatment_cycles")
}

Migration Workflow

Creating Migrations

# From libs/data-access directory
cd libs/data-access

# Create migration
npx prisma migrate dev --name add_user_preferences

# This will:
# 1. Create migration SQL in prisma/migrations/
# 2. Apply migration to local DB
# 3. Generate Prisma Client

Migration Best Practices

Descriptive names: Use clear, specific names
- ✅ add_user_preferences_table
- ❌ update_schema
Small, focused changes: One logical change per migration
- ✅ Single table addition
- ❌ Multiple unrelated changes
Test migrations: Always test on development DB first
Review SQL: Check generated SQL before committing
Reversibility: Consider how to roll back if needed

Applying Migrations (Production)

# In Docker, handled by migrate service
npx prisma migrate deploy

# This applies pending migrations without prompting

PostgreSQL RBAC

Role Structure

Quantum uses a secure RBAC model:

Roles:

schema_migrator - Migration-only (DDL, no data access)
app_reader - Read-only (SELECT)
app_writer - Write-only (INSERT, UPDATE, DELETE, no SELECT)
app_user_ro - Login user with read access
app_user_rw - Login user with write access

Benefits:

Least privilege principle
Clear separation of concerns
Better security (compromised credentials have limited scope)
GDPR/SOC2/HIPAA compliance support

Ownership Strategy

Objects created by schema_migrator should not remain owned by it:

ALTER TABLE public.users OWNER TO postgres;

Testing DAOs

Unit Testing with Mocks

// users.spec.ts
import { describe, it, expect, vi, beforeEach } from 'vitest';
import { findUserByClerkId } from './users';
import { prisma } from '../db/prisma';

// Mock Prisma Client
vi.mock('../db/prisma', () => ({
  prisma: {
    user: {
      findUnique: vi.fn(),
    },
  },
}));

describe('findUserByClerkId', () => {
  beforeEach(() => {
    vi.clearAllMocks();
  });

  it('should return user when found', async () => {
    const mockUser = {
      id: '123',
      email: 'test@example.com',
      full_name: 'Test User',
      tier: { name: 'Standard' },
      consent: { profile: 'GDPR' },
    };

    vi.mocked(prisma.user.findUnique).mockResolvedValue(mockUser);

    const result = await findUserByClerkId('clerk_123');

    expect(result).toEqual({
      id: '123',
      email: 'test@example.com',
      fullName: 'Test User',
      tier: 'Standard',
      consent: 'GDPR',
    });

    expect(prisma.user.findUnique).toHaveBeenCalledWith({
      where: {
        clerk_user_id: 'clerk_123',
        deleted_at: null,
      },
      include: {
        tier: true,
        consent: true,
      },
    });
  });

  it('should return null when user not found', async () => {
    vi.mocked(prisma.user.findUnique).mockResolvedValue(null);

    const result = await findUserByClerkId('nonexistent');

    expect(result).toBeNull();
  });
});

Common Patterns

Pagination

export async function getUserScans(
  userId: string,
  page: number = 1,
  limit: number = 10,
) {
  const skip = (page - 1) * limit;

  const [scans, total] = await Promise.all([
    prisma.skinScan.findMany({
      where: {
        user_id: userId,
        deleted_at: null,
      },
      orderBy: { created_at: 'desc' },
      skip,
      take: limit,
      include: {
        user: {
          select: {
            id: true,
            email: true,
          },
        },
      },
    }),
    prisma.skinScan.count({
      where: {
        user_id: userId,
        deleted_at: null,
      },
    }),
  ]);

  return {
    scans,
    pagination: {
      page,
      limit,
      total,
      pages: Math.ceil(total / limit),
    },
  };
}

Upsert

export async function upsertPersonalInfo(
  userId: string,
  data: PersonalInfoData,
) {
  return await prisma.userPersonalInfo.upsert({
    where: { user_id: userId },
    update: {
      age: data.age,
      sex: data.sex,
      skin_type: data.skinType,
      updated_at: new Date(),
    },
    create: {
      user_id: userId,
      age: data.age,
      sex: data.sex,
      skin_type: data.skinType,
    },
  });
}

Aggregations

export async function getUserScanStats(userId: string) {
  return await prisma.skinScan.aggregate({
    where: {
      user_id: userId,
      deleted_at: null,
    },
    _count: true,
    _avg: {
      overall_score: true,
    },
    _min: {
      created_at: true,
    },
    _max: {
      created_at: true,
    },
  });
}

Common Issues & Solutions

Issue: Prisma Client Out of Sync

# Regenerate Prisma Client after schema changes
npx prisma generate

Issue: Migration Failed

# Mark migration as applied (if already applied manually)
npx prisma migrate resolve --applied <migration_name>

# Roll back last migration (if using SQL)
npx prisma migrate resolve --rolled-back <migration_name>

Issue: Type Errors After Schema Change

Regenerate Prisma Client: npx prisma generate
Restart TypeScript server in IDE
Update DAO types to match new schema

Anti-Patterns

❌ Don't Expose Raw Prisma Client

// BAD: Exporting raw Prisma Client
export { prisma } from './db/prisma';

// GOOD: Export DAO functions
export { findUserByClerkId, upsertUserFromClerk } from './dao/users';

❌ Don't Hard Delete User Data

// BAD
await prisma.user.delete({ where: { id } });

// GOOD
await prisma.user.update({
  where: { id },
  data: { deleted_at: new Date() },
});

❌ Don't Forget to Filter Soft Deletes

// BAD: Includes soft-deleted records
const users = await prisma.user.findMany();

// GOOD: Filters soft-deleted records
const users = await prisma.user.findMany({
  where: { deleted_at: null },
});

Reference

Prisma Docs: https://www.prisma.io/docs
Schema Reference: libs/data-access/prisma/schema.prisma
RBAC Guide: libs/data-access/README.md
CLAUDE.md: Database section

Related Skills

backend-dev-guidelines - Backend integration patterns
frontend-dev-guidelines - Frontend data consumption

Skill Status: Created for Quantum Skincare ✅ Stack: Prisma 6.16.2, PostgreSQL, TypeScript Patterns: DAO, UUID PKs, Soft Deletes, RBAC Line Count: Under 500 lines (following Anthropic best practices) ✅

Install Skill

SKILL.md