prisma-database

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:

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

{
  "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)

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()

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:

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

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

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

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

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

export async function seedUsers(prisma: PrismaClient) {
  const users = [
    { id: "user-1", email: "alice@example.com", name: "Alice" },
    { id: "user-2", email: "bob@example.com", 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:

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

Export Pattern (package.json)

Package exports field:

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

Consumer import examples:

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

   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

   "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

   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

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

Fix: Use simple singleton

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

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

Fix: Simple type re-export

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

Violation: Including repository pattern in database package

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

Fix: Remove repository pattern from database package

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

Violation: Schema without standard timestamps

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

Fix: Add standard timestamp fields

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

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

Fix: Use upsert for idempotency

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

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

2. Validate package.json:

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

3. Check Prisma schema:

   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:

   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:

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

6. Run build test:

   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:

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:

   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:

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

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

   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:

   await seedProducts(prisma);
   

5. Build and test:

   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