Prisma-based database package providing schema definitions, migrations, and database utilities for the Company Manager platform.

Database Package (@company-manager/db)

The database package provides the core data layer for Company Manager using Prisma ORM with PostgreSQL. It includes schema definitions, migrations, seed data, and database utilities.

🏗️ Architecture

Technology Stack

ORM: Prisma 5+
Database: PostgreSQL 14+
Query Builder: Prisma Client
Migrations: Prisma Migrate
Schema Management: Prisma Schema Language

Package Structure

packages/db/
├── prisma/
│   ├── schema.prisma          # Database schema
│   ├── migrations/            # Migration files
│   ├── seed.ts               # Seed data
│   └── data/                 # Sample data files
├── src/
│   ├── client.ts             # Prisma client configuration
│   ├── types.ts              # Database types
│   ├── utils/                # Database utilities
│   │   ├── seed-utils.ts     # Seeding utilities
│   │   ├── validation.ts     # Schema validation
│   │   └── helpers.ts        # Query helpers
│   └── index.ts              # Package exports
└── package.json

📊 Database Schema

Core Entities

User Management

model User {
  id            String       @id @default(dbgenerated("uuid_generate_v7()"))
  email         String       @unique
  name          String?
  password      String?
  emailVerified DateTime?
  image         String?
  blocked       Boolean      @default(false)
  createdAt     DateTime     @default(now())
  updatedAt     DateTime     @updatedAt

  // Multi-tenant relationships
  tenantUsers   TenantUser[]
  siteUsers     SiteUser[]

  // Business relationships
  createdClients Client[] @relation("UserCreatedClients")
  createdOrders  Order[]  @relation("UserCreatedOrders")

  @@map("users")
}

model TenantUser {
  id       String @id @default(dbgenerated("uuid_generate_v7()"))
  tenantId String
  userId   String
  role     UserRole

  tenant   Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  user     User   @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([tenantId, userId])
  @@map("tenant_users")
}

Multi-Tenant Structure

model Tenant {
  id          String       @id @default(dbgenerated("uuid_generate_v7()"))
  name        String
  slug        String       @unique
  description String?
  status      TenantStatus @default(ACTIVE)
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt

  // Relationships
  sites       Site[]
  tenantUsers TenantUser[]
  clients     Client[]
  orders      Order[]
  products    Product[]

  @@map("tenants")
}

model Site {
  id          String     @id @default(dbgenerated("uuid_generate_v7()"))
  name        String
  slug        String
  description String?
  status      SiteStatus @default(PENDING)
  tenantId    String

  tenant      Tenant     @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  siteUsers   SiteUser[]
  clients     Client[]
  orders      Order[]

  @@unique([tenantId, slug])
  @@map("sites")
}

Business Entities

model Client {
  id         String    @id @default(dbgenerated("uuid_generate_v7()"))
  name       String
  email      String
  phone      String?
  archived   Boolean   @default(false)
  tenantId   String
  siteId     String?
  createdBy  String?
  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @updatedAt

  tenant     Tenant    @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  site       Site?     @relation(fields: [siteId], references: [id], onDelete: SetNull)
  creator    User?     @relation("UserCreatedClients", fields: [createdBy], references: [id])
  orders     Order[]
  contacts   Contact[]

  @@map("clients")
}

model Order {
  id             String      @id @default(dbgenerated("uuid_generate_v7()"))
  invoiceNumber  String      @unique
  invoiceDate    DateTime
  invoiceStatus  OrderStatus @default(DRAFT)
  totalAmount    Decimal?    @db.Decimal(10, 2)
  currency       String      @default("EUR")
  paymentMethod  String?
  customInputs   Json?
  tenantId       String
  siteId         String?
  clientId       String?
  createdBy      String?
  receiverId     String?
  createdAt      DateTime    @default(now())
  updatedAt      DateTime    @updatedAt

  tenant         Tenant      @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  site           Site?       @relation(fields: [siteId], references: [id], onDelete: SetNull)
  client         Client?     @relation(fields: [clientId], references: [id], onDelete: SetNull)
  creator        User?       @relation("UserCreatedOrders", fields: [createdBy], references: [id])
  items          OrderItem[]

  @@map("orders")
}

Enums

enum UserRole {
  SUPER_ADMIN
  ADMIN
  MANAGER
  USER
  VIEWER
}

enum TenantStatus {
  ACTIVE
  INACTIVE
  PENDING
  SUSPENDED
}

enum SiteStatus {
  ACTIVE
  INACTIVE
  PENDING
}

enum OrderStatus {
  DRAFT
  SENT
  PAID
  OVERDUE
  CANCELED
}

🚀 Getting Started

Installation

# Install the package
bun add @company-manager/db

# Install Prisma CLI (if not already installed)
bun add -g prisma

Setup

# Generate Prisma client
bunx prisma generate

# Run migrations
bunx prisma migrate dev

# Seed the database
bunx prisma db seed

Environment Configuration

# Database connection
DATABASE_URL="postgresql://username:password@localhost:5432/company_manager"

# Optional: Direct database URL for faster connections
DIRECT_URL="postgresql://username:password@localhost:5432/company_manager"

💻 Usage

Basic Client Usage

import { db } from "@company-manager/db";

// Get all clients for a tenant
const clients = await db.client.findMany({
  where: {
    tenantId: "tenant-123",
    archived: false,
  },
  include: {
    orders: true,
    contacts: true,
  },
});

// Create a new client
const newClient = await db.client.create({
  data: {
    name: "Acme Corp",
    email: "contact@acme.com",
    phone: "+1234567890",
    tenantId: "tenant-123",
    siteId: "site-456",
    createdBy: "user-789",
  },
});

Advanced Queries

// Complex query with relations and filtering
const ordersWithDetails = await db.order.findMany({
  where: {
    tenantId: "tenant-123",
    invoiceStatus: "PAID",
    createdAt: {
      gte: new Date("2024-01-01"),
    },
  },
  include: {
    client: {
      select: {
        name: true,
        email: true,
      },
    },
    items: {
      include: {
        product: true,
      },
    },
  },
  orderBy: {
    createdAt: "desc",
  },
  take: 50,
});

Transactions

// Multi-step transaction
const result = await db.$transaction(async (tx) => {
  // Create order
  const order = await tx.order.create({
    data: {
      invoiceNumber: "INV-2024-001",
      invoiceDate: new Date(),
      tenantId: "tenant-123",
      clientId: "client-456",
    },
  });

  // Create order items
  const items = await tx.orderItem.createMany({
    data: [
      {
        orderId: order.id,
        productId: "product-1",
        quantity: 2,
        unitPrice: 99.99,
      },
      {
        orderId: order.id,
        productId: "product-2",
        quantity: 1,
        unitPrice: 149.99,
      },
    ],
  });

  // Update order total
  const updatedOrder = await tx.order.update({
    where: { id: order.id },
    data: {
      totalAmount: 349.97,
    },
  });

  return { order: updatedOrder, itemsCount: items.count };
});

🔧 Database Utilities

Seed Utilities

// packages/db/src/utils/seed-utils.ts
export async function createTenantWithSite(
  tenantData: Partial<TenantCreateInput>,
  siteData: Partial<SiteCreateInput>
) {
  const tenant = await db.tenant.create({
    data: {
      name: tenantData.name || "Default Tenant",
      slug: tenantData.slug || "default",
      ...tenantData,
    },
  });

  const site = await db.site.create({
    data: {
      name: siteData.name || "Main Site",
      slug: siteData.slug || "main",
      tenantId: tenant.id,
      status: "ACTIVE",
      ...siteData,
    },
  });

  return { tenant, site };
}

Query Helpers

// packages/db/src/utils/helpers.ts
export function buildTenantFilter(tenantId: string, siteId?: string) {
  return {
    tenantId,
    ...(siteId && { siteId }),
  };
}

export function buildPaginationOptions(page: number, pageSize: number) {
  return {
    skip: (page - 1) * pageSize,
    take: pageSize,
  };
}

Validation Helpers

// packages/db/src/utils/validation.ts
import { z } from "zod/v4";

export const clientCreateSchema = z.object({
  name: z.string().min(1).max(255),
  email: z.string().email(),
  phone: z.string().optional(),
  tenantId: z.string().cuid(),
  siteId: z.string().cuid().optional(),
});

export const orderCreateSchema = z.object({
  invoiceNumber: z.string().min(1),
  invoiceDate: z.date(),
  tenantId: z.string().cuid(),
  clientId: z.string().cuid().optional(),
  items: z.array(
    z.object({
      productId: z.string().cuid(),
      quantity: z.number().positive(),
      unitPrice: z.number().positive(),
    })
  ),
});

🗄️ Migrations

Creating Migrations

# Create a new migration
bunx prisma migrate dev --name add-contact-table

# Reset database (dev only)
bunx prisma migrate reset

# Deploy migrations to production
bunx prisma migrate deploy

Migration Best Practices

Always create descriptive migration names
Test migrations on staging environment first
Use @map attributes for consistent naming
Add indexes for performance-critical queries
Consider data migration scripts for complex changes

Example Migration Script

-- Migration: 20240115_001_add_contact_table
CREATE TABLE "contacts" (
    "id" TEXT NOT NULL,
    "firstName" TEXT NOT NULL,
    "lastName" TEXT NOT NULL,
    "email" TEXT,
    "phone" TEXT,
    "clientId" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "contacts_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "contacts_clientId_idx" ON "contacts"("clientId");

🌱 Seeding

Seed Configuration

// prisma/seed.ts
import { PrismaClient } from "@company-manager/db";
import { createTenantWithSite } from "../src/utils/seed-utils";

const prisma = new PrismaClient();

async function main() {
  // Create default tenant and site
  const { tenant, site } = await createTenantWithSite(
    {
      name: "Demo Company",
      slug: "demo",
      status: "ACTIVE",
    },
    {
      name: "Main Office",
      slug: "main",
      status: "ACTIVE",
    }
  );

  // Create sample users
  const users = await prisma.user.createMany({
    data: [
      {
        email: "admin@demo.com",
        name: "Admin User",
        emailVerified: new Date(),
      },
      {
        email: "manager@demo.com",
        name: "Manager User",
        emailVerified: new Date(),
      },
    ],
  });

  console.log("Seed completed successfully");
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Running Seeds

# Run seed script
bunx prisma db seed

# Reset and seed
bunx prisma migrate reset --force

🔍 Query Optimization

Indexing Strategy

model Client {
  // ... fields

  @@index([tenantId])
  @@index([siteId])
  @@index([email])
  @@index([tenantId, archived])
}

model Order {
  // ... fields

  @@index([tenantId])
  @@index([clientId])
  @@index([invoiceStatus])
  @@index([tenantId, invoiceStatus])
  @@index([createdAt])
}

Query Performance Tips

Use select to limit returned fields
Use include carefully to avoid N+1 queries
Implement proper indexing for filter conditions
Use pagination for large datasets
Consider database views for complex queries

🧪 Testing

Database Testing Setup

// tests/db-test.ts
import { PrismaClient } from "@company-manager/db";

const testDb = new PrismaClient({
  datasources: {
    db: {
      url: process.env.TEST_DATABASE_URL,
    },
  },
});

beforeEach(async () => {
  // Clean database
  await testDb.$executeRaw`TRUNCATE TABLE "users", "tenants", "sites", "clients" CASCADE`;
});

Example Tests

describe("Client Operations", () => {
  it("should create client with valid data", async () => {
    const tenant = await testDb.tenant.create({
      data: { name: "Test Tenant", slug: "test" },
    });

    const client = await testDb.client.create({
      data: {
        name: "Test Client",
        email: "test@example.com",
        tenantId: tenant.id,
      },
    });

    expect(client.name).toBe("Test Client");
    expect(client.tenantId).toBe(tenant.id);
  });
});

📊 Monitoring & Analytics

Query Monitoring

// Enable query logging
const db = new PrismaClient({
  log: ["query", "info", "warn", "error"],
});

// Custom query logging
db.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();

  console.log(
    `Query ${params.model}.${params.action} took ${after - before}ms`
  );
  return result;
});

Performance Metrics

Track slow queries (>100ms)
Monitor connection pool usage
Analyze query execution plans
Set up alerting for database errors

🔒 Security

Data Security Best Practices

Use row-level security (RLS) where needed
Implement proper tenant isolation
Validate all inputs with Zod schemas
Use parameterized queries (built-in with Prisma)
Regular security audits of schema changes

Access Control

// Tenant-based access control
export function getTenantFilter(userId: string, tenantId: string) {
  return {
    tenantId,
    tenantUsers: {
      some: {
        userId,
      },
    },
  };
}

📚 Additional Resources

For specific schema changes or custom queries, consult the database team or create an issue in the project repository.

For complete documentation, see the Database Schema Reference.

Database Package

On this page