prisma-database

Original source / Raw SKILL.md

---
name: prisma-database
description: Use when scaffolding, auditing, or validating Prisma database packages in MetaSaver monorepos. Covers package structure, Prisma schema setup, database client initialization, and seed scripts. File types: .prisma, .ts, package.json.
allowed-tools: Read, Write, Edit, Bash, Glob
---

# Prisma Database Package Structure for MetaSaver

## Purpose

This skill documents the complete file and folder organization for MetaSaver Prisma database packages (e.g., rugby-crm-database). It ensures consistent patterns across:

- Package structure and naming conventions
- Prisma schema configuration
- Database client initialization and lifecycle
- Seed script organization
- TypeScript types and exports
- Environment configuration
- Build and compilation

**Use when:**

- Scaffolding a new database package
- Auditing an existing database package for compliance
- Setting up Prisma schema and migrations
- Creating database seed scripts
- Validating environment variable setup

## Directory Structure Reference

### Package Root Files

```
packages/database/{project}-database/
├── package.json              # Package metadata and scripts
├── tsconfig.json             # TypeScript configuration
├── README.md                 # Package documentation
├── .env.example              # Environment variable template
├── eslint.config.js          # ESLint configuration (flat config)
└── dist/                     # Build output (generated)
```

### Prisma Directory

```
prisma/
├── schema.prisma             # Database schema definition
├── seed/
│   ├── index.ts              # Main seed entry point
│   └── {entity}.ts           # Entity-specific seed data
└── migrations/               # Database migrations (generated)
    └── {timestamp}_{name}/   # Migration folder (auto-generated)
        └── migration.sql     # Migration SQL
```

### Source Directory Structure

```
src/
├── index.ts                  # Barrel export (main API)
├── client.ts                 # Prisma client singleton
└── types.ts                  # Type re-exports from Prisma
```

**Complete Example:**

```
packages/database/rugby-crm-database/
├── package.json
├── tsconfig.json
├── eslint.config.js
├── README.md
├── .env.example
├── prisma/
│   ├── schema.prisma
│   ├── seed/
│   │   ├── index.ts
│   │   └── user.ts
│   └── migrations/
│       └── [auto-generated by Prisma]
├── src/
│   ├── index.ts
│   ├── client.ts
│   └── types.ts
└── dist/                     # Build output (generated)
    ├── index.d.ts
    ├── index.js
    └── [compiled files]
```

## File Organization Rules

### Package.json Structure

**Required Fields:**

- `name`: `@metasaver/{project}-database` (always scoped)
- `version`: `0.1.0` (start with patch version)
- `type`: `"module"` (ESM packages)
- `description`: `"{Description} database package"`
- `main`: `"./dist/index.js"`
- `types`: `"./dist/index.d.ts"`
- `exports`: Proper export field for ESM

**Required Scripts:**

- `build`: TypeScript compilation (with Prisma generate)
- `clean`: Remove build artifacts
- `db:generate`: Prisma client generation
- `db:migrate`: Deploy migrations to database
- `db:migrate:dev`: Dev migration with prompt
- `db:seed`: Run seed scripts
- `db:studio`: Open Prisma Studio
- `db:push`: Push schema changes to database (dev only)
- `lint`, `lint:fix`, `lint:tsc`, `prettier`, `prettier:fix`
- `test:unit`: Unit tests (stub or actual)

**Build Script Details:**

```json
"build": "dotenv -e ../../../.env -- prisma generate && tsc -b"
```

This command:

1. Loads environment variables from root `.env`
2. Generates Prisma client
3. Compiles TypeScript

**Dependencies:**

- `@prisma/client`: ^6.16.2 (production)
- Standard devDeps: ESLint config, Prettier config, TypeScript config, Prisma CLI, tsx

### TypeScript Configuration

**tsconfig.json Pattern:**

```json
{
  "extends": "@metasaver/core-typescript-config/base",
  "compilerOptions": {
    "rootDir": "./src",
    "outDir": "./dist"
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist", "**/*.test.ts", "prisma"]
}
```

**Key Points:**

- Extends base config from core
- `rootDir`: `./src`
- `outDir`: `./dist`
- Excludes prisma directory from compilation

### Prisma Schema Pattern

**Rule 1: Datasource Configuration**

Provider: `"postgresql"` (standard for MetaSaver)

```prisma
datasource db {
  provider = "postgresql"
  url      = env("{PROJECT_UPPER}_DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}
```

**Rule 2: Model Naming**

- Model names: PascalCase (e.g., `User`, `Team`)
- Table names: snake_case with `@@map()` (e.g., `@@map("users")`)
- Field names: camelCase in schema, snake_case in database with `@map()`

```prisma
model User {
  id        String   @id @default(uuid())
  email     String
  name      String
  status    String   @default("active")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}
```

**Rule 3: Standard Fields**

All models must have:

- `id: String @id @default(uuid())` - UUID primary key
- `createdAt: DateTime @default(now()) @map("created_at")` - Creation timestamp
- `updatedAt: DateTime @updatedAt @map("updated_at")` - Update timestamp

**Rule 4: Relations**

Use foreign keys with proper cascade behavior:

```prisma
model User {
  id    String   @id @default(uuid())
  name  String
  teams Team[]

  @@map("users")
}

model Team {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("teams")
}
```

### Client Initialization Pattern

**Pattern: Simple Singleton**

File: `src/client.ts`

```typescript
import pkg from "@prisma/client";
const { PrismaClient } = pkg;

declare global {
  var prisma: InstanceType<typeof PrismaClient> | undefined;
}

export const prisma = global.prisma || new PrismaClient();

if (process.env.NODE_ENV !== "production") {
  global.prisma = prisma;
}

export default prisma;
```

**Key Points:**

- Single global instance (development reuse)
- Reset in development to prevent connection errors
- Direct export as `prisma`
- Default export for convenience

### Types Organization

**Rule: Simple Type Re-export**

File: `src/types.ts`

```typescript
export type * from "@prisma/client";
```

**Key Points:**

- Single file (not a folder)
- Re-export ALL Prisma types
- No custom infrastructure types
- Entity types come from contracts packages

### Seed Script Pattern

**Rule 1: Seed Entry Point**

File: `prisma/seed/index.ts`

```typescript
// Import order example
import { prisma } from "#/client.js";
import { seedUsers } from "./users.js";
import { seedTeams } from "./teams.js";

async function seed() {
  try {
    await seedUsers(prisma);
    await seedTeams(prisma);
    console.log("Seed completed successfully");
  } catch (error) {
    console.error("Seed failed:", error);
    process.exit(1);
  } finally {
    await prisma.$disconnect();
  }
}

seed();
```

**Import pattern:** Use `#/` alias for internal imports within database package.

**Rule 2: Entity-Specific Seed Files**

File: `prisma/seed/{entity}.ts`

```typescript
import type { PrismaClient } from "@prisma/client";

export async function seedUsers(prisma: PrismaClient) {
  const users = [
    { id: "user-1", email: "[email protected]", name: "Alice" },
    { id: "user-2", email: "[email protected]", name: "Bob" },
  ];

  for (const user of users) {
    await prisma.user.upsert({
      where: { id: user.id },
      update: { name: user.name },
      create: user,
    });
  }

  console.log(`Seeded ${users.length} users`);
}
```

**Rule 3: Seed Data Idempotency**

- Always use `upsert` pattern to prevent duplicates on re-run
- Document seed assumptions
- Seed only essential data (users, core entities)

### Environment Variables

**Rule 1: Variable Naming**

Format: `{PROJECT_UPPER}_DATABASE_URL`

Example for rugby-crm:

```
RUGBY_CRM_DATABASE_URL=postgresql://user:password@localhost:5432/rugby_crm
```

**Rule 2: .env.example Template**

```
# Database (required for build and migrations)
{PROJECT_UPPER}_DATABASE_URL=postgresql://user:password@localhost:5432/{project_lower}
```

**Rule 3: Script Environment Usage**

The build and migration scripts use `dotenv-cli` to load environment variables:

```json
{
  "scripts": {
    "build": "dotenv -e ../../../.env -- prisma generate && tsc -b",
    "db:migrate": "dotenv -e ../../../.env -- prisma migrate deploy"
  }
}
```

### Export Pattern (package.json)

**Package exports field:**

```json
"exports": {
  "./client": "./dist/client.js",
  "./types": "./dist/types.js"
}
```

**Consumer import examples:**

```typescript
// External package imports (from other workspace packages)
import { prisma } from "@metasaver/rugby-crm-database/client";
import type { User, Team } from "@metasaver/rugby-crm-database/types";

// Internal imports (within same package) - use #/ alias
import { prisma } from "#/client.js";
import type { User } from "#/types.js";
```

**Key Points:**

- Use package.json `exports` field for public API
- No barrel exports (index.ts files)
- Consumers import directly from specific paths
- Internal imports use `#/` alias
- External imports use full package path

## Workflow: Scaffolding New Database Package

1. **Create Package Directory**

   ```bash
   mkdir -p packages/database/{project}-database/{src,prisma/seed}
   ```

2. **Create Configuration Files** (use templates)
   - `package.json`
   - `tsconfig.json`
   - `.env.example`
   - `eslint.config.js` (copy from existing package)

3. **Create Prisma Schema** (use template)
   - `prisma/schema.prisma`

4. **Create Database Client** (use template)
   - `src/client.ts`

5. **Create Types** (use template)
   - `src/types.ts`

6. **Add Exports to package.json**

   ```json
   "exports": {
     "./client": "./dist/client.js",
     "./types": "./dist/types.js"
   }
   ```

7. **Create Seed Scripts** (use template)
   - `prisma/seed/index.ts`
   - `prisma/seed/{entity}.ts` (one per entity)

8. **Update Root Configuration** (if needed)
   - Add workspace reference to `pnpm-workspace.yaml`
   - Add build task to `turbo.json`

9. **Test Build**

   ```bash
   pnpm --filter @metasaver/{project}-database build
   ```

## Audit Checklist

### Package Structure

- [ ] Package directory at `packages/database/{project}-database/`
- [ ] All required subdirectories: `src`, `prisma/seed`
- [ ] No unnecessary files or folders
- [ ] Always use git ignore for build output in `dist/`

### package.json

- [ ] Name: `@metasaver/{project}-database` (scoped)
- [ ] Version: `0.1.0` (semantic versioning)
- [ ] Type: `"module"` (ESM)
- [ ] All required scripts: build, clean, db:\*, lint, prettier, test
- [ ] Correct dependencies: @prisma/client (production), dev configs
- [ ] No hardcoded paths or secrets
- [ ] prisma.schema and prisma.seed configured
- [ ] metasaver.projectType: "database" (if used)

### TypeScript Configuration

- [ ] `tsconfig.json` extends `@metasaver/core-typescript-config/base`
- [ ] Proper module settings for ESM
- [ ] Correct `rootDir: "./src"`
- [ ] Correct `outDir: "./dist"`

### Prisma Schema

- [ ] `prisma/schema.prisma` exists
- [ ] Datasource provider: `"postgresql"`
- [ ] Database URL: Uses `{PROJECT_UPPER}_DATABASE_URL` variable
- [ ] Generator: `prisma-client-js`
- [ ] All models have: id, createdAt, updatedAt
- [ ] Table names in snake_case with `@@map()`
- [ ] Field names in database are snake_case with `@map()`
- [ ] Relations use proper cascade behavior

### Database Client

- [ ] `src/client.ts` exists with singleton pattern
- [ ] Direct export: `export const prisma = ...`
- [ ] Global type declaration for development reuse
- [ ] Default export for convenience
- [ ] No unnecessary disconnect functions

### Types

- [ ] `src/types.ts` exists (single file, not folder)
- [ ] Exports: `export type * from "@prisma/client"`
- [ ] No custom infrastructure types
- [ ] No entity-specific types (come from contracts)

### Exports

- [ ] `package.json` has `exports` field with `/client` and `/types` paths
- [ ] NO `src/index.ts` barrel export file
- [ ] All imports use `.js` extension (ESM)
- [ ] No circular dependencies
- [ ] Internal imports use `#/` alias
- [ ] External consumers import from specific paths

### Seed Scripts

- [ ] `prisma/seed/index.ts` exists
- [ ] Seed entry point imports client and calls entity seed functions
- [ ] Proper error handling and exit codes
- [ ] Prisma disconnect called in finally
- [ ] One seed file per entity: `prisma/seed/{entity}.ts`
- [ ] Seed functions accept PrismaClient parameter
- [ ] Data uses upsert pattern for idempotency

### Environment Configuration

- [ ] Always create `.env.example` (committed)
- [ ] Always include `{PROJECT_UPPER}_DATABASE_URL` in template
- [ ] Always use `dotenv-cli` wrapper for database scripts
- [ ] Ensure `.env` file is gitignored - never commit it
- [ ] Always keep credentials out of code - use environment variables only

### Build & Compilation

- [ ] `pnpm build` succeeds without errors
- [ ] TypeScript compilation succeeds
- [ ] dist/ folder contains compiled files
- [ ] .d.ts files generated correctly
- [ ] No type errors in `pnpm lint:tsc`

## Common Violations & Fixes

**Violation:** Using factory pattern instead of singleton

```typescript
// INCORRECT - factory pattern
export function getPrismaClient(): PrismaClient {
  if (!client) {
    client = new PrismaClient();
  }
  return client;
}
```

**Fix:** Use simple singleton

```typescript
// CORRECT - simple singleton
export const prisma = global.prisma || new PrismaClient();

if (process.env.NODE_ENV !== "production") {
  global.prisma = prisma;
}
```

**Violation:** Creating types folder with pagination interfaces

```typescript
// INCORRECT - src/types/index.ts with custom types
export interface PaginationOptions {
  page?: number;
  pageSize?: number;
}
```

**Fix:** Simple type re-export

```typescript
// CORRECT - src/types.ts with Prisma re-export
export type * from "@prisma/client";
```

**Violation:** Including repository pattern in database package

```typescript
// INCORRECT - src/repositories/base.repository.ts
export abstract class BaseRepository<T> {
  // ...
}
```

**Fix:** Remove repository pattern from database package

```typescript
// CORRECT - consumers use Prisma directly or implement repositories
// Database package only provides client and types
```

**Violation:** Schema without standard timestamps

```prisma
// INCORRECT
model User {
  id    String @id @default(uuid())
  email String
}
```

**Fix:** Add standard timestamp fields

```prisma
// CORRECT
model User {
  id        String   @id @default(uuid())
  email     String
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}
```

**Violation:** Seed scripts not idempotent

```typescript
// INCORRECT - creates duplicates on re-run
async function seedUsers(prisma: PrismaClient) {
  await prisma.user.create({ data: user });
}
```

**Fix:** Use upsert for idempotency

```typescript
// CORRECT - safe to run multiple times
async function seedUsers(prisma: PrismaClient) {
  await prisma.user.upsert({
    where: { id: user.id },
    update: { name: user.name },
    create: user,
  });
}
```

## Examples

### Example 1: Audit Existing Database Package

**Audit Steps:**

1. Check directory structure:

   ```bash
   ls -la packages/database/rugby-crm-database/
   ls -la packages/database/rugby-crm-database/src/
   ```

2. Validate package.json:

   ```bash
   grep '"name"' packages/database/rugby-crm-database/package.json
   grep '"build"' packages/database/rugby-crm-database/package.json
   ```

3. Check Prisma schema:

   ```bash
   grep 'datasource db' packages/database/rugby-crm-database/prisma/schema.prisma
   grep 'DATABASE_URL' packages/database/rugby-crm-database/prisma/schema.prisma
   ```

4. Verify client pattern:

   ```bash
   grep 'export const prisma' packages/database/rugby-crm-database/src/client.ts
   grep 'global.prisma' packages/database/rugby-crm-database/src/client.ts
   ```

5. Check types export:

   ```bash
   grep 'export type' packages/database/rugby-crm-database/src/types.ts
   ```

6. Run build test:

   ```bash
   pnpm --filter @metasaver/rugby-crm-database build
   ```

### Example 2: Scaffold New Database Package

**Files to Create (use templates):**

1. `packages/database/inventory-database/package.json`
2. `packages/database/inventory-database/tsconfig.json`
3. `packages/database/inventory-database/.env.example`
4. `packages/database/inventory-database/prisma/schema.prisma`
5. `packages/database/inventory-database/src/client.ts`
6. `packages/database/inventory-database/src/types.ts`
7. `packages/database/inventory-database/src/index.ts`
8. `packages/database/inventory-database/prisma/seed/index.ts`
9. `packages/database/inventory-database/prisma/seed/product.ts`

**Build and Test:**

```bash
pnpm --filter @metasaver/inventory-database build
pnpm --filter @metasaver/inventory-database db:push
pnpm --filter @metasaver/inventory-database db:seed
```

### Example 3: Add New Model to Schema

**Steps:**

1. Add model to `prisma/schema.prisma`:

   ```prisma
   model Product {
     id        String   @id @default(uuid())
     name      String
     price     Float
     createdAt DateTime @default(now()) @map("created_at")
     updatedAt DateTime @updatedAt @map("updated_at")

     @@map("products")
   }
   ```

2. Create migration:

   ```bash
   pnpm --filter @metasaver/inventory-database db:migrate:dev
   ```

3. Add seed data in `prisma/seed/product.ts`:

   ```typescript
   export async function seedProducts(prisma: PrismaClient) {
     const products = [{ id: "prod-1", name: "Product A", price: 10.0 }];

     for (const product of products) {
       await prisma.product.upsert({
         where: { id: product.id },
         update: { price: product.price },
         create: product,
       });
     }
   }
   ```

4. Update `prisma/seed/index.ts`:

   ```typescript
   await seedProducts(prisma);
   ```

5. Build and test:

   ```bash
   pnpm --filter @metasaver/inventory-database build
   pnpm --filter @metasaver/inventory-database db:seed
   ```

## Related Skills

- **typescript-configuration** - TypeScript configuration (tsconfig.json)
- **eslint-agent** - ESLint configuration (eslint.config.js)
- **monorepo-structure** - Monorepo organization (packages/database/\*)
- **database-migrations** - Prisma migration strategies