Skip to main content

Database Schema

FSS uses PostgreSQL as its primary database with Prisma ORM for type-safe database operations.

Overview​

The database schema includes the following main entities:

  • Users - User accounts and authentication
  • Products - Product catalog
  • Orders - Order management
  • Payments - Payment records
  • Audit Logs - Security and activity logging

Database Connection​

Environment Variables​

# PostgreSQL connection
DATABASE_URL=postgresql://username:password@host:5432/database

# Connection pool settings
DATABASE_POOL_SIZE=10
DATABASE_POOL_TIMEOUT=30000

Prisma Configuration​

// prisma.config.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: process.env.NODE_ENV === 'development' 
    ? ['query', 'info', 'warn', 'error'] 
    : ['error'],
});

User Model​

model User {
  id            String    @id @default(uuid())
  email         String    @unique
  password      String
  name          String?
  role          UserRole  @default(USER)
  emailVerified Boolean   @default(false)
  mfaEnabled    Boolean   @default(false)
  mfaSecret     String?
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  
  sessions      Session[]
  orders        Order[]
  auditLogs     AuditLog[]
}

enum UserRole {
  USER
  ADMIN
}

Session Model​

model Session {
  id        String   @id @default(uuid())
  userId    String
  token     String   @unique
  device    String?
  ip        String?
  expiresAt DateTime
  createdAt DateTime @default(now())
  
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Product Model​

model Product {
  id          String   @id @default(uuid())
  name        String
  description String?
  price       Decimal  @db.Decimal(10, 2)
  stock       Int      @default(0)
  sku         String   @unique
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  orderItems  OrderItem[]
}

Order Model​

model Order {
  id          String      @id @default(uuid())
  userId      String
  status      OrderStatus @default(PENDING)
  total       Decimal     @db.Decimal(10, 2)
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt
  
  user        User        @relation(fields: [userId], references: [id])
  items       OrderItem[]
  payment     Payment?
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
  REFUNDED
}

model OrderItem {
  id        String  @id @default(uuid())
  orderId   String
  productId String
  quantity  Int
  price     Decimal @db.Decimal(10, 2)
  
  order     Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)
  product   Product @relation(fields: [productId], references: [id])
}

Payment Model​

model Payment {
  id              String        @id @default(uuid())
  orderId         String        @unique
  amount          Decimal       @db.Decimal(10, 2)
  currency        String        @default("USD")
  status          PaymentStatus @default(PENDING)
  provider        String        // "stripe" or "paypal"
  providerId      String?
  createdAt       DateTime      @default(now())
  updatedAt       DateTime      @updatedAt
  
  order           Order         @relation(fields: [orderId], references: [id])
}

enum PaymentStatus {
  PENDING
  PROCESSING
  COMPLETED
  FAILED
  REFUNDED
  CANCELLED
}

Audit Log Model​

model AuditLog {
  id         String   @id @default(uuid())
  userId     String?
  action     String
  resource   String
  resourceId String?
  details    Json?
  ip         String?
  userAgent  String?
  createdAt  DateTime @default(now())
}

Database Migrations​

Create Migration​

npx prisma migrate dev --name migration_name

Reset Database​

npx prisma migrate reset

Generate Prisma Client​

npx prisma generate

Push Schema Changes​

npx prisma db push