Inventory Management

Real-time stock tracking, low stock alerts, and automated inactive product detection

Overview

PC Fix features a comprehensive inventory management system that tracks product stock in real-time, alerts users when items are back in stock, notifies customers of price drops, and helps identify slow-moving inventory that needs attention.

Stock Tracking

Product Schema

Products are stored with detailed inventory information:

packages/api/prisma/schema.prisma

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)
  
  // Shipping dimensions
  peso           Decimal  @default(0.5) @db.Decimal(10, 3)
  alto           Int      @default(10)
  ancho          Int      @default(10)
  profundidad    Int      @default(10)

  categoriaId    Int
  categoria      Categoria @relation(fields: [categoriaId], references: [id])
  marcaId        Int?
  marca          Marca?    @relation(fields: [marcaId], references: [id])
  
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

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

Real-Time Stock Updates

Stock is automatically decremented when orders are created and restored when orders are cancelled:

packages/api/src/modules/sales/sales.service.ts

return await prisma.$transaction(async (tx: any) => {
  const venta = await tx.venta.create({
    data: {
      cliente: { connect: { id: cliente!.id } },
      montoTotal: subtotalReal + costoEnvio,
      estado: VentaEstado.PENDIENTE_PAGO,
      lineasVenta: { create: lineasParaCrear },
    },
    include: { lineasVenta: true }
  });

  // Decrement stock for each product (except infinite stock items)
  for (const linea of lineasParaCrear) {
    const prod = dbProducts.find((p: any) => p.id === linea.productoId);
    if (prod && prod.stock < 90000) {
      await tx.producto.update({ 
        where: { id: linea.productoId }, 
        data: { stock: { decrement: linea.cantidad } } 
      });
    }
  }
  return venta;
});

Products with stock >= 90000 are treated as “infinite stock” items (typically services) and aren’t decremented.

Stock Validation

Pre-Order Validation

Before creating an order, the system validates sufficient stock is available:

packages/api/src/modules/sales/sales.service.ts

for (const item of items) {
  const dbProduct = dbProducts.find((p: any) => p.id === Number(item.id));
  if (!dbProduct) throw new Error(`Producto ${item.id} no encontrado`);

  if (dbProduct.stock < 90000 && dbProduct.stock < item.quantity) {
    throw new Error(`Stock insuficiente: ${dbProduct.nombre}`);
  }

  // ... continue with order creation
}

Cart Quantity Limits

The shopping cart prevents adding more items than available in stock:

packages/web/src/stores/cartStore.ts

increaseQuantity: (productId) => set((state) => ({
  items: state.items.map((item) =>
    item.id === productId 
      ? { ...item, quantity: Math.min(item.stock, item.quantity + 1) } 
      : item
  ),
}))

Low Stock Alerts

Dashboard Monitoring

The admin dashboard displays a real-time count of low stock products:

packages/api/src/modules/stats/stats.service.ts

const lowStockProducts = await prisma.producto.count({
  where: { 
    deletedAt: null, 
    stock: { lte: 5 } 
  }
});

Low Stock Filter

Administrators can filter products by low stock status:

packages/api/src/modules/products/products.service.ts

async findAll(
  page: number = 1,
  limit: number = 10,
  categoryId?: number,
  brandId?: number,
  search?: string,
  filter?: string,
  sort?: string
) {
  const skip = (page - 1) * limit;
  const where: Prisma.ProductoWhereInput = { deletedAt: null };

  // ... other filters

  if (filter === 'lowStock') where.stock = { lte: 5 };
  if (filter === 'featured') where.isFeatured = true;
  if (filter === 'hasDiscount') where.precioOriginal = { not: null };

  // ... execute query
}

Dashboard KPI

Real-time count of products with stock ≤ 5

Click-Through

Dashboard card links directly to filtered product list

Stock Availability Notifications

Customer Stock Alerts

Customers can subscribe to be notified when out-of-stock products become available:

packages/api/prisma/schema.prisma

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

Automatic Alert Processing

When stock is updated from 0 to a positive number, all subscribers are notified:

packages/api/src/modules/products/products.service.ts

async update(id: number, data: any) {
  const currentProduct = await prisma.producto.findUnique({ where: { id } });
  const oldStock = currentProduct?.stock || 0;

  const updatedProduct = await prisma.producto.update({ 
    where: { id }, 
    data: updateData 
  });

  const newStock = updatedProduct.stock;
  if (oldStock === 0 && newStock > 0) {
    this.processStockAlerts(updatedProduct);
  }

  return updatedProduct;
}

private async processStockAlerts(product: any) {
  try {
    const alerts = await (prisma as any).stockAlert.findMany({
      where: { productoId: product.id }
    });

    if (alerts.length === 0) return;

    const emailService = new EmailService();
    const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:4321';
    const productLink = `${frontendUrl}/tienda/producto/${product.id}`;

    for (const alert of alerts) {
      await emailService.sendStockAlertEmail(
        alert.email,
        product.nombre,
        productLink,
        product.foto,
        Number(product.precio)
      );
    }

    // Delete alerts after sending
    await (prisma as any).stockAlert.deleteMany({
      where: { productoId: product.id }
    });
  } catch (error) {
    console.error('Error procesando alertas de stock:', error);
  }
}

Stock alerts are automatically deleted after being sent, so customers only receive one notification per subscription.

Price Drop Notifications

Favorite Product Tracking

Customers who have favorited products are notified when prices drop:

packages/api/src/modules/products/products.service.ts

private async processPriceDropAlerts(product: any, oldPrice: number, newPrice: number) {
  try {
    const favorites = await prisma.favorite.findMany({
      where: { productoId: product.id },
      include: { user: true }
    });

    if (favorites.length === 0) return;

    const emailService = new EmailService();
    const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:4321';
    const productLink = `${frontendUrl}/tienda/producto/${product.id}`;

    await Promise.all(favorites.map(fav => {
      if (fav.user.email) {
        return emailService.sendPriceDropNotification(
          fav.user.email,
          product.nombre,
          productLink,
          product.foto,
          oldPrice,
          newPrice
        ).catch((e: any) => console.error(`Error enviando alerta precio a ${fav.user.email}:`, e));
      }
      return Promise.resolve();
    }));
  } catch (error) {
    console.error('Error procesando alertas de bajada de precio:', error);
  }
}

Automatic Price Monitoring

Price drops are detected automatically during product updates:

packages/api/src/modules/products/products.service.ts

const oldPrice = Number(currentProduct?.precio || 0);
const updatedProduct = await prisma.producto.update({ where: { id }, data: updateData });
const newPrice = Number(updatedProduct.precio);

if (oldPrice > 0 && newPrice < oldPrice) {
  this.processPriceDropAlerts(updatedProduct, oldPrice, newPrice);
}

Inactive Product Detection

Dead Stock Analysis

The system automatically identifies products that haven’t sold in 90+ days:

packages/api/src/modules/stats/stats.service.ts

const ninetyDaysAgo = new Date(now);
ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);

const deadStockCandidates = await prisma.producto.findMany({
  where: {
    deletedAt: null,
    stock: { gt: 0 }
  },
  select: { id: true, nombre: true, stock: true, precio: true, createdAt: true },
});

const deadStock = [];

for (const product of deadStockCandidates) {
  const lastSale = await prisma.lineaVenta.findFirst({
    where: { productoId: product.id },
    orderBy: { venta: { fecha: 'desc' } },
    include: { venta: { select: { fecha: true } } }
  });

  const lastInteractionDate = lastSale?.venta?.fecha || product.createdAt;

  if (lastInteractionDate < ninetyDaysAgo) {
    deadStock.push({
      id: product.id,
      name: product.nombre,
      stock: product.stock,
      price: Number(product.precio),
      lastSale: lastSale?.venta?.fecha ? lastSale.venta.fecha : null,
      daysInactive: Math.floor((now.getTime() - lastInteractionDate.getTime()) / (1000 * 3600 * 24))
    });
  }
}

return { deadStock };

Flash Sale Recommendations

The admin dashboard displays inactive products with a “Flash Sale” action button:

packages/web/src/components/admin/dashboard/DashboardIntelligence.tsx

<div className="bg-white rounded-2xl shadow-sm border border-gray-100 overflow-hidden">
  <div className="p-6 border-b border-gray-50">
    <h3 className="text-lg font-bold text-gray-800 flex items-center gap-2">
      <SkullIcon className="w-5 h-5 text-gray-400" />
      Stock Inmovilizado
      <span className="bg-red-100 text-red-600 text-xs px-2 py-0.5 rounded-full">
        {data.deadStock.length}
      </span>
    </h3>
    <p className="text-sm text-gray-400">Productos sin ventas en +90 días. ¡Muévelos!</p>
  </div>

  <table className="w-full text-left border-collapse">
    <tbody className="text-sm divide-y divide-gray-50">
      {data.deadStock.slice(0, 10).map((item) => (
        <tr key={item.id} className="hover:bg-gray-50 transition-colors">
          <td className="p-4 font-medium text-gray-800">{item.name}</td>
          <td className="p-4 text-center">
            <span className="font-bold text-gray-700">{item.stock}</span>
          </td>
          <td className="p-4 text-right text-gray-500">{item.daysInactive} días</td>
          <td className="p-4 text-right">
            <button
              onClick={() => setOfferProduct(item)}
              className="bg-red-50 text-red-600 hover:bg-red-100 font-bold text-xs px-3 py-1.5 rounded-lg transition-colors inline-flex items-center gap-1"
            >
              <ZapIcon className="w-3 h-3" /> Oferta Flash
            </button>
          </td>
        </tr>
      ))}
    </tbody>
  </table>
</div>

Products are considered “dead stock” if they haven’t sold in 90+ days. The dashboard shows the top 10 worst offenders.

Soft Delete System

Preserving Data Integrity

Products are never permanently deleted to preserve order history:

packages/api/src/modules/products/products.service.ts

async delete(id: number) { 
  return await prisma.producto.update({ 
    where: { id }, 
    data: { deletedAt: new Date() } 
  }); 
}

async restore(id: number) { 
  return await prisma.producto.update({ 
    where: { id }, 
    data: { deletedAt: null } 
  }); 
}

All product queries automatically exclude deleted products:

const where: Prisma.ProductoWhereInput = { deletedAt: null };

Product Filtering

Advanced Search & Filter

packages/api/src/modules/products/products.service.ts

if (search) {
  where.OR = [
    { nombre: { contains: search, mode: 'insensitive' } },
    { descripcion: { contains: search, mode: 'insensitive' } },
    { id: !isNaN(Number(search)) ? Number(search) : undefined }
  ];
}

// Exclude custom services from listings
where.nombre = { not: 'Servicio: Servicio Personalizado' };

if (categoryId) {
  const categoryIds = await this.getCategoryIdsRecursively(categoryId);
  where.categoriaId = { in: categoryIds };
}
if (brandId) where.marcaId = brandId;
if (filter === 'lowStock') where.stock = { lte: 5 };
if (filter === 'featured') where.isFeatured = true;
if (filter === 'hasDiscount') where.precioOriginal = { not: null };

Best Sellers Tracking

Sales Analytics

The system tracks which products sell the most:

packages/api/src/modules/products/products.service.ts

async findBestSellers(limit: number = 10) {
  const bestSellers = await prisma.$queryRaw<{ productoId: number; totalSold: bigint }[]>`
    SELECT lv."productoId", SUM(lv.cantidad) as "totalSold"
    FROM "LineaVenta" lv
    INNER JOIN "Venta" v ON lv."ventaId" = v.id
    WHERE v.estado NOT IN ('CANCELADO')
    GROUP BY lv."productoId"
    ORDER BY "totalSold" DESC
    LIMIT ${limit}
  `;

  // If no sales yet, return featured products
  if (bestSellers.length === 0) {
    return await prisma.producto.findMany({
      where: {
        deletedAt: null,
        isFeatured: true,
        nombre: { not: 'Servicio: Servicio Personalizado' }
      },
      include: { categoria: true, marca: true },
      take: limit
    });
  }

  const productIds = bestSellers.map(bs => bs.productoId);
  const products = await prisma.producto.findMany({
    where: {
      id: { in: productIds },
      deletedAt: null
    },
    include: { categoria: true, marca: true }
  });

  return productIds
    .map(id => products.find(p => p.id === id))
    .filter(Boolean);
}

Inventory Metrics

Total Products

Count of all active products (deletedAt: null)

Low Stock Items

Products with stock ≤ 5 units

Dead Stock

Products without sales in 90+ days

Featured Items

Products marked as featured for homepage

Discounted

Products with precioOriginal set

Best Sellers

Top selling products by quantity

API Endpoints

Endpoint	Method	Description
/api/products	GET	List products with filters
/api/products/:id	GET	Get single product
/api/products	POST	Create product (admin)
/api/products/:id	PUT	Update product (admin)
/api/products/:id	DELETE	Soft delete product (admin)
/api/products/:id/restore	POST	Restore deleted product (admin)
/api/products/best-sellers	GET	Get best selling products
/api/stats/intelligence	GET	Get dead stock analysis (admin)

Use the dead stock report to identify products that need price reductions or promotional campaigns to move inventory.