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

```prisma
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:

```prisma
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:

```typescript
// ❌ 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:

```prisma
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:

```typescript
// 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:

```typescript
// 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:

```typescript
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:

```prisma
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:

```prisma
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:

```prisma
model TreatmentCycle {
  user_id      String @db.Uuid
  cycle_number Int

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

---

## Migration Workflow

### Creating Migrations

```bash
# 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)

```bash
# 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:

```sql
ALTER TABLE public.users OWNER TO postgres;
```

---

## Testing DAOs

### Unit Testing with Mocks

```typescript
// 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

```typescript
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

```typescript
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

```typescript
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

```bash
# Regenerate Prisma Client after schema changes
npx prisma generate
```

### Issue: Migration Failed

```bash
# 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

```typescript
// 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

```typescript
// 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

```typescript
// 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](../../libs/data-access/prisma/schema.prisma)
- **RBAC Guide**: [libs/data-access/README.md](../../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) ✅