stock-manager/prisma/schema.prisma

// This is your Prisma schema file,
// learn more about the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider      = "prisma-client"
  output        = "../src/generated/prisma"
  binaryTargets = ["native", "debian-openssl-1.1.x"]
}

datasource db {
  provider = "postgresql"
}

// ======================================================
// USERS
// ======================================================

enum UserRole {
  ADMIN
  MANAGER
  STAFF
  VIEWER
}

enum UserStatus {
  INVITED
  ACTIVE
  SUSPENDED
  DISABLED
}

model User {
  id String @id @default(uuid(7)) @db.Uuid

  name            String
  email           String
  emailNormalized String @unique

  /**
   * Nulo mientras el usuario no haya aceptado la invitación.
   */
  passwordHash String?

  role   UserRole   @default(STAFF)
  status UserStatus @default(INVITED)

  deletedAt DateTime?

  invitedAt         DateTime?
  activatedAt       DateTime?
  passwordChangedAt DateTime?
  lastLoginAt       DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  person Person?

  createdAssignments Assignment[] @relation("AssignmentCreatedBy")
  closedAssignments  Assignment[] @relation("AssignmentClosedBy")

  receivedStockReturns AssignmentStockReturn[]
  receivedAssetReturns AssignmentAssetLine[]   @relation("AssetReturnedBy")

  movements InventoryMovement[]

  acknowledgedStockAlerts StockAlert[] @relation("StockAlertAcknowledgedBy")

  sentInvitations UserInvitation[] @relation("UserInvitationInvitedBy")
  invitations     UserInvitation[]

  @@index([status])
  @@index([deletedAt])
  @@index([createdAt])
}

model UserInvitation {
  id String @id @default(uuid(7)) @db.Uuid

  userId String @db.Uuid
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)

  /**
   * Hash del token de invitación.
   * Nunca guardar el token plano.
   */
  tokenHash String @unique

  invitedById String @db.Uuid
  invitedBy   User   @relation("UserInvitationInvitedBy", fields: [invitedById], references: [id], onDelete: Restrict, onUpdate: Cascade)

  email String

  expiresAt  DateTime
  acceptedAt DateTime?
  revokedAt  DateTime?

  createdAt DateTime @default(now())

  @@index([userId])
  @@index([expiresAt])
  @@index([acceptedAt])
  @@index([revokedAt])
}

// ======================================================
// PEOPLE
// ======================================================

model Person {
  id        String @id @default(uuid(7)) @db.Uuid
  firstName String
  lastName  String

  email String?
  phone String?

  teamId String? @db.Uuid
  team   Team?   @relation(fields: [teamId], references: [id], onDelete: SetNull, onUpdate: Cascade)

  userId String? @unique @db.Uuid
  user   User?   @relation(fields: [userId], references: [id], onDelete: SetNull, onUpdate: Cascade)

  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  assignments Assignment[]

  @@index([lastName, firstName])
  @@index([teamId, deletedAt])
  @@index([teamId])
  @@index([deletedAt])
}

model Team {
  id        String   @id @default(uuid(7)) @db.Uuid
  name      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  people Person[]
}

// ======================================================
// CATALOG
// ======================================================

enum ItemTrackingType {
  QUANTITY
  SERIALIZED
}

enum ItemStatus {
  ACTIVE
  DISCONTINUED
  ARCHIVED
}

model Category {
  id          String  @id @default(uuid(7)) @db.Uuid
  name        String  @unique
  description String?

  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  items Item[]

  @@index([deletedAt])
}

model Item {
  id          String  @id @default(uuid(7)) @db.Uuid
  sku         String  @unique
  name        String
  description String?

  trackingType ItemTrackingType
  status       ItemStatus       @default(ACTIVE)

  categoryId String   @db.Uuid
  category   Category @relation(fields: [categoryId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  /**
   * Solo se utiliza para artículos QUANTITY.
   * Para artículos SERIALIZED, las existencias se obtienen
   * contando los activos AVAILABLE.
   */
  stock Int @default(0)

  /**
   * Umbral de alerta.
   * QUANTITY:
   * Se compara contra Item.stock.
   * SERIALIZED:
   * Se compara contra número de Asset AVAILABLE.
   */
  minStock Int?

  /**
   * Nivel deseado tras reposición.
   * Compra sugerida:
   * targetStock - stock disponible.
   */
  targetStock Int?

  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  assets Asset[]

  assignmentStockLines AssignmentStockLine[]
  stockMovementLines   StockMovementLine[]

  stockAlerts StockAlert[]

  @@index([categoryId, status])
  @@index([trackingType, status])
  @@index([name])
  @@index([deletedAt])
}

// ======================================================
// SERIALIZED ASSETS
// ======================================================

enum AssetStatus {
  AVAILABLE
  ASSIGNED
  IN_REPAIR
  BROKEN
  LOST
  STOLEN
  DISPOSED
  RETIRED
}

model Asset {
  id String @id @default(uuid(7)) @db.Uuid

  /**
   * Identificador interno visible.
   * Ejemplos:
   * IT-000001
   * LAP-000042
   * MON-000117
   */
  assetTag String? @unique

  /**
   * Número de serie del fabricante.
   * Puede ser nulo.
   */
  serialNumber String @unique

  itemId String @db.Uuid
  item   Item   @relation(fields: [itemId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  status AssetStatus @default(AVAILABLE)

  manufacturer String?
  model        String?

  deliveryNote  String?
  invoiceNumber String?

  purchaseDate  DateTime?
  purchasePrice Decimal?  @db.Decimal(12, 2)

  warrantyEndsAt DateTime?

  notes String?

  retiredAt DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  deletedAt DateTime?

  assignmentLines AssignmentAssetLine[]
  movementLines   AssetMovementLine[]

  @@index([itemId, status])
  @@index([status])
  @@index([createdAt])
  @@index([deletedAt])
}

// ======================================================
// ASSIGNMENTS
// ======================================================

enum AssignmentStatus {
  OPEN
  PARTIALLY_RETURNED
  RETURNED
  CANCELLED
}

model Assignment {
  id String @id @default(uuid(7)) @db.Uuid

  personId String @db.Uuid
  person   Person @relation(fields: [personId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  status AssignmentStatus @default(OPEN)

  assignedAt DateTime  @default(now())
  dueAt      DateTime?
  closedAt   DateTime?

  notes String?

  createdById String @db.Uuid
  createdBy   User   @relation("AssignmentCreatedBy", fields: [createdById], references: [id], onDelete: Restrict, onUpdate: Cascade)

  closedById String? @db.Uuid
  closedBy   User?   @relation("AssignmentClosedBy", fields: [closedById], references: [id], onDelete: Restrict, onUpdate: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  stockLines AssignmentStockLine[]
  assetLines AssignmentAssetLine[]
  movements  InventoryMovement[]

  @@index([personId, status])
  @@index([personId, assignedAt])
  @@index([status, assignedAt])
  @@index([dueAt])
  @@index([createdById, createdAt])
}

// ======================================================
// QUANTITY ASSIGNMENTS
// ======================================================

model AssignmentStockLine {
  id String @id @default(uuid(7)) @db.Uuid

  assignmentId String     @db.Uuid
  assignment   Assignment @relation(fields: [assignmentId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  itemId String @db.Uuid
  item   Item   @relation(fields: [itemId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  quantity         Int
  returnedQuantity Int @default(0)

  notes String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  returns AssignmentStockReturn[]

  @@index([assignmentId])
  @@index([itemId, createdAt])
}

model AssignmentStockReturn {
  id String @id @default(uuid(7)) @db.Uuid

  assignmentLineId String              @db.Uuid
  assignmentLine   AssignmentStockLine @relation(fields: [assignmentLineId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  quantity Int

  returnedAt DateTime @default(now())

  receivedById String @db.Uuid
  receivedBy   User   @relation(fields: [receivedById], references: [id], onDelete: Restrict, onUpdate: Cascade)

  notes String?

  createdAt DateTime @default(now())

  @@index([assignmentLineId, returnedAt])
  @@index([receivedById, returnedAt])
}

// ======================================================
// SERIALIZED ASSET ASSIGNMENTS
// ======================================================

model AssignmentAssetLine {
  id String @id @default(uuid(7)) @db.Uuid

  assignmentId String     @db.Uuid
  assignment   Assignment @relation(fields: [assignmentId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  assetId String @db.Uuid
  asset   Asset  @relation(fields: [assetId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  assignedAt DateTime  @default(now())
  returnedAt DateTime?

  returnedById String? @db.Uuid
  returnedBy   User?   @relation("AssetReturnedBy", fields: [returnedById], references: [id], onDelete: Restrict, onUpdate: Cascade)

  returnStatus AssetStatus?

  notes String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  /**
   * La unicidad de asignación activa se protege
   * mediante índice único parcial en PostgreSQL.
   */
  @@index([assignmentId])
  @@index([assetId, assignedAt])
  @@index([returnedAt])
}

// ======================================================
// INVENTORY MOVEMENTS
// ======================================================

enum InventoryMovementType {
  RECEIPT
  ISSUE
  ASSIGNMENT
  RETURN
  ADJUSTMENT
  STATUS_CHANGE
  DISPOSAL
  INITIAL_LOAD
}

enum InventoryMovementReason {
  PURCHASE
  MANUAL_ENTRY
  EMPLOYEE_ASSIGNMENT
  EMPLOYEE_RETURN
  INVENTORY_CORRECTION
  DAMAGE
  REPAIR
  REPAIR_RETURN
  LOSS
  THEFT
  DISPOSAL
  INITIAL_LOAD
  OTHER
}

model InventoryMovement {
  id String @id @default(uuid(7)) @db.Uuid

  type   InventoryMovementType
  reason InventoryMovementReason

  assignmentId String?     @db.Uuid
  assignment   Assignment? @relation(fields: [assignmentId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  reference String?

  details String?
  notes   String?

  performedById String @db.Uuid
  performedBy   User   @relation(fields: [performedById], references: [id], onDelete: Restrict, onUpdate: Cascade)

  occurredAt DateTime @default(now())
  createdAt  DateTime @default(now())

  stockLines StockMovementLine[]
  assetLines AssetMovementLine[]

  @@index([type, occurredAt])
  @@index([reason, occurredAt])
  @@index([assignmentId])
  @@index([performedById, occurredAt])
  @@index([occurredAt])
}

// ======================================================
// QUANTITY MOVEMENTS
// ======================================================

model StockMovementLine {
  id String @id @default(uuid(7)) @db.Uuid

  movementId String            @db.Uuid
  movement   InventoryMovement @relation(fields: [movementId], references: [id], onDelete: Cascade, onUpdate: Cascade)

  itemId String @db.Uuid
  item   Item   @relation(fields: [itemId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  /**
   * Positivo: entrada/devolución/ajuste positivo.
   * Negativo: salida/asignación/ajuste negativo.
   */
  stockDelta Int

  previousStock Int
  newStock      Int

  createdAt DateTime @default(now())

  @@index([movementId])
  @@index([itemId, createdAt])
}

// ======================================================
// SERIALIZED ASSET MOVEMENTS
// ======================================================

model AssetMovementLine {
  id String @id @default(uuid(7)) @db.Uuid

  movementId String            @db.Uuid
  movement   InventoryMovement @relation(fields: [movementId], references: [id], onDelete: Cascade, onUpdate: Cascade)

  assetId String @db.Uuid
  asset   Asset  @relation(fields: [assetId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  previousStatus AssetStatus?
  newStatus      AssetStatus

  notes String?

  createdAt DateTime @default(now())

  @@unique([movementId, assetId])
  @@index([assetId, createdAt])
}

// ======================================================
// STOCK ALERTS
// ======================================================

enum StockAlertStatus {
  OPEN
  ACKNOWLEDGED
  RESOLVED
}

enum StockAlertTrigger {
  BELOW_MINIMUM
  OUT_OF_STOCK
}

model StockAlert {
  id String @id @default(uuid(7)) @db.Uuid

  itemId String @db.Uuid
  item   Item   @relation(fields: [itemId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  trigger StockAlertTrigger
  status  StockAlertStatus  @default(OPEN)

  availableStock    Int
  minimumStock      Int
  suggestedPurchase Int?

  triggeredAt DateTime @default(now())

  acknowledgedAt DateTime?

  acknowledgedById String? @db.Uuid
  acknowledgedBy   User?   @relation("StockAlertAcknowledgedBy", fields: [acknowledgedById], references: [id], onDelete: SetNull, onUpdate: Cascade)

  resolvedAt DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([itemId, status])
  @@index([status, triggeredAt])
  @@index([trigger, triggeredAt])
}