Data Access Guidelines

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

1. Descriptive names: Use clear, specific names

   • ✅ add_user_preferences_table
   • ❌ update_schema

2. Small, focused changes: One logical change per migration

   • ✅ Single table addition
   • ❌ Multiple unrelated changes

3. Test migrations: Always test on development DB first

4. Review SQL: Check generated SQL before committing

5. 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:

1. schema_migrator - Migration-only (DDL, no data access)
2. app_reader - Read-only (SELECT)
3. app_writer - Write-only (INSERT, UPDATE, DELETE, no SELECT)
4. app_user_ro - Login user with read access
5. 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

1. Regenerate Prisma Client: npx prisma generate
2. Restart TypeScript server in IDE
3. 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) ✅