Vendix Prisma Schema Pattern

Schema Editing & Migrations - Edición de schema.prisma, relaciones y workflow de migraciones en desarrollo.

🎯 Schema Structure

File: apps/backend/prisma/schema.prisma

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

📝 Naming Conventions

Database Objects

// ✅ CORRECT - Tables in snake_case
model users {}
model product_variants {}
model sales_order_items {}

// ✅ CORRECT - Enums in snake_case with _enum suffix
enum user_state_enum {
  ACTIVE
  INACTIVE
  PENDING
}

// ❌ WRONG - Tables in PascalCase
model Users {}
model ProductVariants {}

// ❌ WRONG - Enums in PascalCase
enum UserState {}

Fields

// ✅ CORRECT - Fields in snake_case
model users {
  id               Int
  user_name        String
  email_address    String
  organization_id  Int
  main_store_id    Int?
  created_at       DateTime
  updated_at       DateTime
}

// ❌ WRONG - Fields in camelCase
model users {
  id               Int
  userName         String
  emailAddress     String
  organizationId   Int
  createdAt        DateTime
}

🔗 Relationship Patterns

One-to-Many

model organizations {
  id        Int       @id @default(autoincrement())
  name      String
  users     users[]   // One organization has many users
  stores    stores[]  // One organization has many stores
}

model users {
  id              Int            @id @default(autoincrement())
  organization_id Int
  organization    organizations  @relation(fields: [organization_id], references: [id])

  // Index for foreign key
  @@index([organization_id])
}

Many-to-Many

model users {
  id         Int          @id @default(autoincrement())
  user_name  String
  store_users store_users[]  // Join table
}

model stores {
  id         Int          @id @default(autoincrement())
  name       String
  store_users store_users[]  // Join table
}

// Join table
model store_users {
  user_id    Int
  store_id   Int
  role       String

  user       users     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  store      stores    @relation(fields: [store_id], references: [id], onDelete: Cascade)

  @@id([user_id, store_id])  // Composite primary key
}

Self-Referencing

model users {
  id           Int      @id @default(autoincrement())
  user_name    String
  manager_id   Int?
  manager      users?   @relation("ManagerRelation", fields: [manager_id], references: [id])
  subordinates users[]  @relation("ManagerRelation")
}

🎨 Field Types & Attributes

Common Field Types

model products {
  id            Int      @id @default(autoincrement())
  name          String
  description   String?  @db.Text
  base_price    Decimal  @db.Decimal(10, 2)
  is_active     Boolean  @default(true)
  stock_quantity Int     @default(0)
  created_at    DateTime @default(now())
  updated_at    DateTime @updatedAt

  // Enums
  status        product_status_enum @default(ACTIVE)

  // JSON field for metadata
  metadata      Json?

  // Array of strings (PostgreSQL specific)
  tags          String[]

  // Indexes
  @@index([organization_id, store_id])
  @@index([is_active])
}

enum product_status_enum {
  ACTIVE
  INACTIVE
  DRAFT
  ARCHIVED
}

Field Attributes

🔄 Migration Workflow

Development Mode

# 1. Make changes to schema.prisma

# 2. Create migration (auto-applies in development)
npx prisma migrate dev --name describe_your_changes

# This will:
# - Generate migration SQL
# - Apply migration to database
# - Regenerate Prisma Client

# 3. Verify migration was applied
npx prisma migrate status

# 4. If needed, reset database (WARNING: deletes all data)
npx prisma migrate reset

Production Mode

# 1. Create migration without applying
npx prisma migrate dev --create-only --name describe_your_changes

# 2. Review generated migration SQL
# Edit migration if needed

# 3. Apply migration (production environment)
npx prisma migrate deploy

🎯 Schema Editing Workflow

Step 1: Backup Current State

# Export current schema
npx prisma migrate diff \
  --from-empty \
  --to-schema-datamodel prisma/schema.prisma \
  --script > backup.sql

Step 2: Edit Schema

// Example: Adding new field
model products {
  id            Int      @id @default(autoincrement())
  name          String
  weight        Decimal?  @db.Decimal(10, 2)  // NEW FIELD
  dimensions    Json?                         // NEW FIELD
}

Step 3: Create Migration

npx prisma migrate dev --name add_product_weight_and_dimensions

Step 4: Review Migration

-- Migration SQL
ALTER TABLE "products" ADD COLUMN "weight" DECIMAL(10,2);
ALTER TABLE "products" ADD COLUMN "dimensions" JSONB;

Step 5: Apply & Test

# Migration applied automatically
# Verify with Docker logs
docker logs --tail 40 vendix_backend

🔍 Common Schema Patterns

Soft Delete Pattern

model users {
  id         Int       @id @default(autoincrement())
  user_name  String
  deleted_at DateTime?  // Soft delete timestamp
  is_active  Boolean   @default(true)

  @@index([deleted_at])
}

// Query for non-deleted records
const users = await prisma.users.findMany({
  where: {
    deleted_at: null,
    is_active: true,
  },
});

Timestamps Pattern

model base_model {
  id         Int      @id @default(autoincrement())
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt
}

Multi-Tenant Pattern

model products {
  id              Int      @id @default(autoincrement())
  name            String
  organization_id Int      // Tenant ID
  store_id        Int      // Sub-tenant ID

  organization    organizations @relation(fields: [organization_id], references: [id])
  store           stores        @relation(fields: [store_id], references: [id])

  @@index([organization_id, store_id])
}

🚫 Common Mistakes

❌ WRONG: PascalCase Tables

model Users {  // ❌ WRONG
  id Int @id
  UserName String  // ❌ WRONG
}

✅ CORRECT: snake_case Tables

model users {  // ✅ CORRECT
  id        Int      @id
  user_name String   // ✅ CORRECT
}

❌ WRONG: Missing Foreign Key Indexes

model products {
  id              Int
  organization_id Int  // ❌ Missing index
}

✅ CORRECT: With Indexes

model products {
  id              Int
  organization_id Int

  @@index([organization_id])  // ✅ Index for foreign key
}

🔍 Key Files Reference

Related Skills

• vendix-prisma-scopes - Prisma scoping system and model registration
• vendix-prisma-seed - Seed data patterns
• vendix-naming-conventions - Naming conventions (CRITICAL)