Database Schema

PostgreSQL database schema with Prisma ORM for PC Fix e-commerce

Overview

The PC Fix database is built on PostgreSQL and managed through Prisma 6, providing type-safe database access and automatic migrations. The schema supports a full e-commerce system with users, products, shopping carts, orders, and administrative features.

The database is hosted on Railway and includes 328 lines of carefully designed schema covering all business logic.

Database Technology

PostgreSQL

Production-grade relational database with ACID compliance

Prisma 6.18

Next-generation ORM with automatic client generation

Railway Hosting

Managed PostgreSQL with automatic backups

Prisma Studio

Visual database browser on port 5555

Schema Configuration

prisma/schema.prisma

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "debian-openssl-3.0.x"]
}

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

The binaryTargets configuration ensures compatibility with both local development and containerized deployments.

Schema Overview

The database schema is organized into five main domains:

1. Users & Authentication

   User accounts, roles, authentication tokens, and client profiles

2. Products & Catalog

   Products, categories, brands, banners, and favorites

3. Shopping & Cart

   Shopping cart, cart items, and stock alerts

4. Sales & Payments

   Orders, order lines, payments, and payment methods

5. Technical Support

   Support tickets, service items, and ticket responses

User Management

User Model

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  nombre    String
  apellido  String
  telefono  String?
  password  String?
  googleId  String?  @unique
  role      Role     @default(USER)
  
  // Relations
  cliente   Cliente?
  favoritos Favorite[]
  consultas ConsultaTecnica[]
  cart      Cart?
  
  // Password reset
  resetToken        String?
  resetTokenExpires DateTime? @db.Timestamptz(3)
  
  // Refresh tokens for JWT
  refreshTokens     RefreshToken[]
  
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)
}

enum Role {
  USER
  ADMIN
}

Users can authenticate via password or Google OAuth (googleId field). The role field enables role-based access control.

Client Profile

model Cliente {
  id          Int       @id @default(autoincrement())
  userId      Int       @unique
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  direccion   String?
  telefono    String?
  localidadId Int?
  localidad   Localidad? @relation(fields: [localidadId], references: [id])
  ventas      Venta[]

  @@index([userId])
  @@index([localidadId])
}

Location Data

model Localidad {
  id           Int       @id @default(autoincrement())
  nombre       String
  codigoPostal String
  provinciaId  Int
  provincia    Provincia @relation(fields: [provinciaId], references: [id])
  clientes     Cliente[]

  @@index([provinciaId])
}

model Provincia {
  id          Int       @id @default(autoincrement())
  nombre      String    @unique
  localidades Localidad[]
}

Location data supports Argentina’s provincial and locality structure for shipping calculations.

Refresh Tokens

model RefreshToken {
  id          Int      @id @default(autoincrement())
  token       String   @unique
  userId      Int
  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  expiresAt   DateTime @db.Timestamptz(3)
  revoked     Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  @@index([userId])
}

Product Catalog

Product Model

model Producto {
  id             Int      @id @default(autoincrement())
  nombre         String
  descripcion    String   @db.Text
  precio         Decimal  @db.Decimal(10, 2)
  precioOriginal Decimal? @db.Decimal(10, 2)
  stock          Int
  foto           String?
  isFeatured     Boolean  @default(false)
  deletedAt      DateTime? @db.Timestamptz(3) // Soft delete
  
  // Shipping dimensions
  peso           Decimal  @default(0.5) @db.Decimal(10, 3)
  alto           Int      @default(10)
  ancho          Int      @default(10)
  profundidad    Int      @default(10)

  // Relations
  categoriaId    Int
  categoria      Categoria @relation(fields: [categoriaId], references: [id])
  marcaId        Int?
  marca          Marca?    @relation(fields: [marcaId], references: [id])
  
  favoritedBy    Favorite[]
  lineasVenta    LineaVenta[]
  cartItems      CartItem[]
  stockAlerts    StockAlert[]
  
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  @@index([categoriaId])
  @@index([marcaId])
  @@index([isFeatured])
  @@index([deletedAt])
}

Key Features

• Soft Delete: deletedAt field allows marking products as deleted without removing data
• Pricing: Support for original and discounted prices
• Shipping: Physical dimensions and weight for shipping calculations
• Featured Products: isFeatured flag for homepage highlights

Stock Alerts

model StockAlert {
  id         Int      @id @default(autoincrement())
  email      String
  productoId Int
  producto   Producto @relation(fields: [productoId], references: [id])
  createdAt  DateTime @default(now()) @db.Timestamptz(3)
  @@unique([email, productoId])
}

Users can subscribe to stock alerts for out-of-stock products.

Categories

model Categoria {
  id            Int         @id @default(autoincrement())
  nombre        String      @unique
  padreId       Int?
  padre         Categoria?  @relation("CategoriaJerarquia", fields: [padreId], references: [id])
  subcategorias Categoria[] @relation("CategoriaJerarquia")
  productos     Producto[]
}

Categories support hierarchical relationships (parent/child) for organizing products into nested groups.

Brands

model Marca {
  id        Int      @id @default(autoincrement())
  nombre    String   @unique
  logo      String?
  productos Producto[]
  banners   Banner[]
}

model Banner {
  id        Int      @id @default(autoincrement())
  imagen    String
  marcaId   Int
  marca     Marca    @relation(fields: [marcaId], references: [id])
  createdAt DateTime @default(now()) @db.Timestamptz(3)

  @@index([marcaId])
}

Favorites

model Favorite {
  userId     Int
  user       User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  productoId Int
  producto   Producto @relation(fields: [productoId], references: [id])
  @@id([userId, productoId])
}

Favorites use a composite primary key to prevent duplicate entries.

Shopping Cart

model Cart {
  id                 Int        @id @default(autoincrement())
  userId             Int        @unique
  user               User       @relation(fields: [userId], references: [id], onDelete: Cascade)
  items              CartItem[]
  abandonedEmailSent Boolean    @default(false)
  updatedAt          DateTime   @updatedAt
  createdAt          DateTime   @default(now())
}

model CartItem {
  id        Int      @id @default(autoincrement())
  cartId    Int
  cart      Cart     @relation(fields: [cartId], references: [id], onDelete: Cascade)
  productoId Int
  producto   Producto @relation(fields: [productoId], references: [id])
  quantity  Int
  
  @@index([cartId])
  @@index([productoId])
}

The abandonedEmailSent flag tracks whether the user has received an abandoned cart email reminder.

Sales & Orders

Sales Model

model Venta {
  id                Int         @id @default(autoincrement())
  fecha             DateTime    @default(now()) @db.Timestamptz(3)
  montoTotal        Decimal     @db.Decimal(10, 2)
  estado            VentaEstado @default(PENDIENTE_PAGO)
  comprobante       String?
  
  // Logistics
  costoEnvio        Decimal     @default(0) @db.Decimal(10, 2)
  metodoEnvio       String      @default("CORREO_ARGENTINO")
  codigoSeguimiento String?
  etiquetaUrl       String?
  
  // Shipping address (for Zipnova)
  direccionEnvio    String?
  ciudadEnvio       String?
  provinciaEnvio    String?
  cpEnvio           String?
  telefonoEnvio     String?
  documentoEnvio    String?
  
  // Zipnova tracking
  zipnovaShipmentId String?
  
  // Customer choices
  tipoEntrega       String      @default("ENVIO") // 'ENVIO' | 'RETIRO'
  medioPago         String      @default("TRANSFERENCIA") // 'TRANSFERENCIA' | 'BINANCE' | 'EFECTIVO'
  tipoEnvio         String?

  // Relations
  clienteId         Int
  cliente           Cliente     @relation(fields: [clienteId], references: [id], onDelete: Cascade)
  lineasVenta       LineaVenta[]
  pagos             Pago[]

  @@index([clienteId])
  @@index([estado])
  @@index([fecha])
}

enum VentaEstado {
  PENDIENTE_PAGO
  PENDIENTE_APROBACION
  APROBADO
  ENVIADO
  ENTREGADO
  RECHAZADO
  CANCELADO
}

Order Status Workflow

1. PENDIENTE_PAGO - Order created, awaiting payment
2. PENDIENTE_APROBACION - Payment submitted, awaiting admin approval
3. APROBADO - Payment approved, ready to ship
4. ENVIADO - Order shipped to customer
5. ENTREGADO - Order delivered successfully
6. RECHAZADO - Payment rejected
7. CANCELADO - Order cancelled

Delivery Options

• ENVIO: Home delivery via shipping provider
• RETIRO: In-store pickup

The system supports multiple shipping providers including Correo Argentino and Zipnova.

Payment Methods

• TRANSFERENCIA: Bank transfer
• BINANCE: Cryptocurrency (USDT)
• EFECTIVO: Cash (for in-store pickup)

Order Lines

model LineaVenta {
  id         Int      @id @default(autoincrement())
  ventaId    Int
  venta      Venta    @relation(fields: [ventaId], references: [id], onDelete: Cascade)
  productoId Int
  producto   Producto @relation(fields: [productoId], references: [id])
  cantidad   Int
  subTotal   Decimal  @db.Decimal(10, 2)
  customPrice Decimal? @db.Decimal(10, 2)
  customDescription String?
  
  @@unique([ventaId, productoId])
  @@index([ventaId])
  @@index([productoId])
}

Order lines support custom pricing and descriptions for special scenarios (bulk discounts, promotions, etc.).

Payments

model Pago {
  id           Int        @id @default(autoincrement())
  fecha        DateTime   @default(now()) @db.Timestamptz(3)
  monto        Decimal    @db.Decimal(10, 2)
  ventaId      Int
  venta        Venta      @relation(fields: [ventaId], references: [id], onDelete: Cascade)
  metodoPagoId Int
  metodoPago   MetodoPago @relation(fields: [metodoPagoId], references: [id])

  @@index([ventaId])
  @@index([metodoPagoId])
}

model MetodoPago {
  id     Int    @id @default(autoincrement())
  nombre String @unique
  pagos  Pago[]
}

System Configuration

model Configuracion {
  id              Int     @id @default(autoincrement())
  
  // Bank details
  nombreBanco     String  @default("Banco Nación")
  titular         String  @default("PCFIX S.A.")
  cbu             String  @default("0000000000000000000000")
  alias           String  @default("PCFIX.VENTAS")
  
  // Shipping & pricing
  costoEnvioFijo  Decimal @default(5000) @db.Decimal(10, 2)
  cotizacionUsdt  Decimal @default(1150) @db.Decimal(10, 2)
  
  // Crypto payment
  binanceAlias    String? @default("PCFIX.USDT")
  binanceCbu      String? @default("PAY-ID-123456")
  
  // Store info
  direccionLocal  String? @default("Av. Corrientes 1234, CABA")
  horariosLocal   String? @default("Lun a Vie: 10 a 18hs")
  
  // Maintenance mode
  maintenanceMode Boolean @default(false)
}

The configuration table stores system-wide settings that can be updated by admins without code changes.

Technical Support

model ConsultaTecnica {
  id          Int      @id @default(autoincrement())
  asunto      String
  mensaje     String   @db.Text
  respuesta   String?  @db.Text
  estado      EstadoConsulta @default(PENDIENTE)
  userId      Int
  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  serviceItemId Int?
  serviceItem   ServiceItem? @relation(fields: [serviceItemId], references: [id])
  createdAt   DateTime @default(now())
  respondedAt DateTime? @db.Timestamptz(3)

  @@index([userId])
  @@index([estado])
  @@index([serviceItemId])
}

enum EstadoConsulta {
  PENDIENTE
  RESPONDIDO
}

model ServiceItem {
  id          Int      @id @default(autoincrement())
  title       String
  description String
  price       Int
  active      Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  consultas   ConsultaTecnica[]
  
  @@map("service_items")
}

Database Indexes

The schema includes strategic indexes for performance:

User Indexes

CREATE INDEX ON "User"("email");
CREATE INDEX ON "User"("googleId");

Prisma Migrations

Creating Migrations

Development

# Create and apply migration
npm run db:push --workspace=api

# Or create named migration
npx prisma migrate dev --name add_user_phone

Database Seeding

prisma/seed.ts

import { PrismaClient } from '@prisma/client';
import bcrypt from 'bcryptjs';

const prisma = new PrismaClient();

async function main() {
  // Create admin user
  const hashedPassword = await bcrypt.hash('admin123', 10);
  const admin = await prisma.user.upsert({
    where: { email: '[e-mail]' },
    update: {},
    create: {
      email: '[e-mail]',
      nombre: 'Admin',
      apellido: 'PCFIX',
      password: hashedPassword,
      role: 'ADMIN',
    },
  });
  
  // Create categories
  const categorias = ['Procesadores', 'Placas de Video', 'Memorias RAM', 'Almacenamiento'];
  for (const nombre of categorias) {
    await prisma.categoria.upsert({
      where: { nombre },
      update: {},
      create: { nombre },
    });
  }
  
  // Create brands
  const marcas = ['Intel', 'AMD', 'NVIDIA', 'Kingston', 'Corsair'];
  for (const nombre of marcas) {
    await prisma.marca.upsert({
      where: { nombre },
      update: {},
      create: { nombre },
    });
  }
  
  console.log('✅ Database seeded successfully');
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Run seed with: npx prisma db seed

Prisma Studio

Access the visual database browser:

npm run db:studio --workspace=api

Open http://localhost:5555 to:

• Browse all tables
• Create, edit, delete records
• View relationships
• Run queries

Database Relationships

Best Practices

Use Transactions

For operations affecting multiple tables (orders, payments)

Soft Deletes

Use deletedAt timestamps instead of hard deletes for products

Indexes

Add indexes on foreign keys and frequently queried fields

Timestamps

Always use @db.Timestamptz(3) for timezone-aware timestamps

Next Steps

Backend API

Learn how to query this schema

Migrations

Manage schema changes

Deployment

Deploy to production