Ecommerce_api

Chapter 47: E-Commerce API Project

Building a Complete E-Commerce API with Products, Orders, and Inventory

---

47.1 Project Overview

This project demonstrates a complete e-commerce API with products, categories, orders, payments, and inventory management.

                    E-Commerce API Architecture
    ================================================================================

    +-------------+     +-------------+     +-------------+     +-------------+
    |   Users     |     |  Products   |     |  Categories |     |   Orders    |
    +-------------+     +-------------+     +-------------+     +-------------+
    | id          |     | id          |     | id          |     | id          |
    | email       |<--->| name        |<--->| name        |<--->| orderNumber |
    | password    |     | price       |     | slug        |     | status      |
    | role        |     | stock       |     | parentId    |     | totalAmount |
    +-------------+     | categoryId  |     +-------------+     | userId      |
          |             +-------------+             ^           +-------------+
          |                   |                     |                 |
          |                   v                     |                 v
          |             +-------------+             |           +-------------+
          |             |   Images    |             |           | OrderItems  |
          |             +-------------+             |           +-------------+
          |             | id          |             |           | id          |
          |             | url         |             |           | orderId     |
          |             | productId   |             |           | productId   |
          |             +-------------+             |           | quantity    |
          |                                         |           | unitPrice   |
          |                                         |           +-------------+
          |                                         |
          |             +-------------+             |
          +-----------> |   Reviews   |<------------+
                        +-------------+
                        | id          |
                        | rating      |
                        | comment     |
                        | userId      |
                        | productId   |
                        +-------------+

    +-------------+     +-------------+     +-------------+
    |   Cart      |     | CartItems   |     |  Payments   |
    +-------------+     +-------------+     +-------------+
    | id          |<--->| id          |     | id          |
    | userId      |     | cartId      |     | orderId     |
    | expiresAt   |     | productId   |     | amount      |
    +-------------+     | quantity    |     | status      |
                        +-------------+     | method      |
                                            +-------------+

    ================================================================================

---

47.2 Entity Definitions

Product Entity

src/products/product.entity.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  OneToMany,
  ManyToMany,
  JoinTable,
  CreateDateColumn,
  UpdateDateColumn,
  Index,
  BeforeInsert,
  BeforeUpdate,
} from 'typeorm';
import { Category } from '../categories/category.entity';
import { ProductImage } from './product-image.entity';
import { Review } from '../reviews/review.entity';
import { Tag } from '../tags/tag.entity';

@Entity('products')
@Index(['isActive', 'createdAt'])
@Index(['categoryId', 'isActive'])
@Index(['price', 'isActive'])
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ length: 200 })
  @Index()
  name: string;

  @Column({ unique: true, length: 250 })
  slug: string;

  @Column({ type: 'text', nullable: true })
  description: string;

  @Column({ type: 'decimal', precision: 10, scale: 2 })
  price: number;

  @Column({ type: 'decimal', precision: 10, scale: 2, nullable: true })
  comparePrice: number;

  @Column({ type: 'decimal', precision: 10, scale: 2, nullable: true })
  costPrice: number;

  @Column({ default: 0 })
  stock: number;

  @Column({ default: 0 })
  reservedStock: number;

  @Column({ default: true })
  isActive: boolean;

  @Column({ default: false })
  isFeatured: boolean;

  @Column({ default: false })
  trackInventory: boolean;

  @Column({ type: 'int', default: 0 })
  soldCount: number;

  @Column({ type: 'decimal', precision: 3, scale: 2, default: 0 })
  averageRating: number;

  @Column({ default: 0 })
  reviewCount: number;

  @Column()
  categoryId: number;

  @ManyToOne(() => Category, (category) => category.products)
  category: Category;

  @OneToMany(() => ProductImage, (image) => image.product, { cascade: true })
  images: ProductImage[];

  @OneToMany(() => Review, (review) => review.product)
  reviews: Review[];

  @ManyToMany(() => Tag, (tag) => tag.products)
  @JoinTable({ name: 'product_tags' })
  tags: Tag[];

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  // Computed property
  get availableStock(): number {
    return this.stock - this.reservedStock;
  }

  @BeforeInsert()
  @BeforeUpdate()
  generateSlug() {
    if (!this.slug) {
      this.slug = this.name
        .toLowerCase()
        .replace(/[^a-z0-9]+/g, '-')
        .replace(/(^-|-$)/g, '');
    }
  }
}

Category Entity

src/categories/category.entity.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  OneToMany,
  ManyToMany,
  JoinTable,
  ClosureEntity,
  ClosureTable,
  CreateDateColumn,
  Index,
} from 'typeorm';

@Entity('categories')
@Index(['slug'])
export class Category {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ length: 100 })
  name: string;

  @Column({ unique: true, length: 120 })
  slug: string;

  @Column({ type: 'text', nullable: true })
  description: string;

  @Column({ nullable: true })
  parentId: number;

  @Column({ nullable: true })
  imageUrl: string;

  @Column({ type: 'int', default: 0 })
  sortOrder: number;

  @Column({ default: true })
  isActive: boolean;

  @ManyToOne(() => Category, (category) => category.children)
  parent: Category;

  @OneToMany(() => Category, (category) => category.parent)
  children: Category[];

  @OneToMany(() => 'Product', (product: any) => product.category)
  products: any[];

  @ManyToMany(() => 'Product', (product: any) => product.categories)
  @JoinTable({ name: 'product_categories' })
  productsMany: any[];

  @CreateDateColumn()
  createdAt: Date;
}

Order Entity

src/orders/order.entity.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  OneToMany,
  OneToOne,
  CreateDateColumn,
  UpdateDateColumn,
  Index,
} from 'typeorm';
import { User } from '../users/user.entity';
import { OrderItem } from './order-item.entity';
import { Payment } from '../payments/payment.entity';
import { ShippingAddress } from './shipping-address.entity';

export enum OrderStatus {
  PENDING = 'pending',
  CONFIRMED = 'confirmed',
  PROCESSING = 'processing',
  SHIPPED = 'shipped',
  DELIVERED = 'delivered',
  CANCELLED = 'cancelled',
  REFUNDED = 'refunded',
}

@Entity('orders')
@Index(['userId', 'status'])
@Index(['status', 'createdAt'])
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true, length: 50 })
  orderNumber: string;

  @Column({ type: 'enum', enum: OrderStatus, default: OrderStatus.PENDING })
  status: OrderStatus;

  @Column({ type: 'decimal', precision: 10, scale: 2 })
  subtotal: number;

  @Column({ type: 'decimal', precision: 10, scale: 2, default: 0 })
  discountAmount: number;

  @Column({ type: 'decimal', precision: 10, scale: 2, default: 0 })
  taxAmount: number;

  @Column({ type: 'decimal', precision: 10, scale: 2, default: 0 })
  shippingAmount: number;

  @Column({ type: 'decimal', precision: 10, scale: 2 })
  totalAmount: number;

  @Column({ type: 'text', nullable: true })
  notes: string;

  @Column()
  userId: number;

  @ManyToOne(() => User, (user) => user.orders)
  user: User;

  @OneToMany(() => OrderItem, (item) => item.order, { cascade: true })
  items: OrderItem[];

  @OneToOne(() => Payment, (payment) => payment.order)
  payment: Payment;

  @OneToOne(() => ShippingAddress, (address) => address.order, { cascade: true })
  shippingAddress: ShippingAddress;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

Order Item Entity

src/orders/order-item.entity.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  Index,
} from 'typeorm';
import { Order } from './order.entity';
import { Product } from '../products/product.entity';

@Entity('order_items')
@Index(['orderId'])
@Index(['productId'])
export class OrderItem {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  orderId: number;

  @Column()
  productId: number;

  @Column({ length: 200 })
  productName: string;

  @Column({ type: 'decimal', precision: 10, scale: 2 })
  unitPrice: number;

  @Column({ type: 'decimal', precision: 10, scale: 2, default: 0 })
  discountAmount: number;

  @Column()
  quantity: number;

  @Column({ type: 'decimal', precision: 10, scale: 2 })
  totalPrice: number;

  @ManyToOne(() => Order, (order) => order.items, { onDelete: 'CASCADE' })
  order: Order;

  @ManyToOne(() => Product)
  product: Product;
}

Cart Entity

src/cart/cart.entity.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  OneToMany,
  ManyToOne,
  CreateDateColumn,
  UpdateDateColumn,
  Index,
} from 'typeorm';
import { User } from '../users/user.entity';
import { CartItem } from './cart-item.entity';

@Entity('carts')
@Index(['userId'])
export class Cart {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ nullable: true })
  userId: number;

  @Column({ type: 'uuid' })
  sessionId: string;

  @Column({ type: 'timestamp', nullable: true })
  expiresAt: Date;

  @Column({ type: 'decimal', precision: 10, scale: 2, default: 0 })
  totalAmount: number;

  @Column({ default: 0 })
  itemCount: number;

  @ManyToOne(() => User, (user) => user.carts)
  user: User;

  @OneToMany(() => CartItem, (item) => item.cart, { cascade: true })
  items: CartItem[];

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

---

47.3 Service Implementation

Products Service

src/products/product.service.ts

import {
  Injectable,
  NotFoundException,
  BadRequestException,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource, SelectQueryBuilder } from 'typeorm';
import { Product } from './product.entity';
import { ProductImage } from './product-image.entity';
import { CreateProductDto } from './dto/create-product.dto';
import { UpdateProductDto } from './dto/update-product.dto';
import { ProductQueryDto } from './dto/product-query.dto';

@Injectable()
export class ProductService {
  constructor(
    @InjectRepository(Product)
    private productRepository: Repository<Product>,
    private dataSource: DataSource,
  ) {}

  async create(createProductDto: CreateProductDto): Promise<Product> {
    return this.dataSource.transaction(async (manager) => {
      const { images, ...productData } = createProductDto;

      const product = manager.create(Product, productData);
      const savedProduct = await manager.save(product);

      // Save images
      if (images && images.length > 0) {
        const productImages = images.map((url, index) =>
          manager.create(ProductImage, {
            url,
            productId: savedProduct.id,
            sortOrder: index,
          }),
        );
        await manager.save(productImages);
        savedProduct.images = productImages;
      }

      return savedProduct;
    });
  }

  async findAll(query: ProductQueryDto) {
    const qb = this.productRepository
      .createQueryBuilder('product')
      .leftJoinAndSelect('product.images', 'images')
      .leftJoinAndSelect('product.category', 'category');

    this.applyFilters(qb, query);

    // Apply pagination
    const { page = 1, limit = 20 } = query;
    const skip = (page - 1) * limit;
    qb.skip(skip).take(limit);

    // Apply sorting
    this.applySorting(qb, query);

    const [data, total] = await qb.getManyAndCount();

    return {
      data,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    };
  }

  async findOne(id: number): Promise<Product> {
    const product = await this.productRepository
      .createQueryBuilder('product')
      .leftJoinAndSelect('product.images', 'images')
      .leftJoinAndSelect('product.category', 'category')
      .leftJoinAndSelect('product.tags', 'tags')
      .where('product.id = :id', { id })
      .getOne();

    if (!product) {
      throw new NotFoundException('Product not found');
    }

    return product;
  }

  async update(
    id: number,
    updateProductDto: UpdateProductDto,
  ): Promise<Product> {
    return this.dataSource.transaction(async (manager) => {
      const product = await manager.findOne(Product, {
        where: { id },
        relations: ['images'],
      });

      if (!product) {
        throw new NotFoundException('Product not found');
      }

      const { images, ...productData } = updateProductDto;

      // Update product
      Object.assign(product, productData);
      await manager.save(product);

      // Update images if provided
      if (images !== undefined) {
        // Remove existing images
        await manager.delete(ProductImage, { productId: id });

        // Add new images
        if (images.length > 0) {
          const productImages = images.map((url, index) =>
            manager.create(ProductImage, {
              url,
              productId: id,
              sortOrder: index,
            }),
          );
          await manager.save(productImages);
          product.images = productImages;
        } else {
          product.images = [];
        }
      }

      return product;
    });
  }

  async updateStock(id: number, quantity: number): Promise<Product> {
    const product = await this.findOne(id);

    if (product.stock + quantity < 0) {
      throw new BadRequestException('Insufficient stock');
    }

    product.stock += quantity;
    return this.productRepository.save(product);
  }

  async reserveStock(id: number, quantity: number): Promise<boolean> {
    const result = await this.productRepository
      .createQueryBuilder()
      .update(Product)
      .set({
        reservedStock: () => `reservedStock + ${quantity}`,
      })
      .where('id = :id AND stock - reservedStock >= :quantity', {
        id,
        quantity,
      })
      .execute();

    return result.affected > 0;
  }

  async releaseReservedStock(id: number, quantity: number): Promise<void> {
    await this.productRepository
      .createQueryBuilder()
      .update(Product)
      .set({
        reservedStock: () => `GREATEST(0, reservedStock - ${quantity})`,
      })
      .where('id = :id', { id })
      .execute();
  }

  async confirmStock(id: number, quantity: number): Promise<void> {
    await this.productRepository
      .createQueryBuilder()
      .update(Product)
      .set({
        stock: () => `stock - ${quantity}`,
        reservedStock: () => `GREATEST(0, reservedStock - ${quantity})`,
        soldCount: () => `soldCount + ${quantity}`,
      })
      .where('id = :id', { id })
      .execute();
  }

  private applyFilters(qb: SelectQueryBuilder<Product>, query: ProductQueryDto) {
    qb.where('product.isActive = :isActive', { isActive: true });

    if (query.categoryId) {
      qb.andWhere('product.categoryId = :categoryId', {
        categoryId: query.categoryId,
      });
    }

    if (query.minPrice !== undefined) {
      qb.andWhere('product.price >= :minPrice', {
        minPrice: query.minPrice,
      });
    }

    if (query.maxPrice !== undefined) {
      qb.andWhere('product.price <= :maxPrice', {
        maxPrice: query.maxPrice,
      });
    }

    if (query.inStock) {
      qb.andWhere('product.stock > 0');
    }

    if (query.isFeatured) {
      qb.andWhere('product.isFeatured = :isFeatured', { isFeatured: true });
    }

    if (query.search) {
      qb.andWhere(
        '(product.name ILIKE :search OR product.description ILIKE :search)',
        { search: `%${query.search}%` },
      );
    }
  }

  private applySorting(qb: SelectQueryBuilder<Product>, query: ProductQueryDto) {
    const sortField = query.sortBy || 'createdAt';
    const sortOrder = query.sortOrder || 'DESC';

    const validSortFields = [
      'createdAt',
      'price',
      'name',
      'soldCount',
      'averageRating',
    ];

    if (validSortFields.includes(sortField)) {
      qb.orderBy(`product.${sortField}`, sortOrder);
    } else {
      qb.orderBy('product.createdAt', 'DESC');
    }
  }
}

Orders Service

src/orders/order.service.ts

import {
  Injectable,
  NotFoundException,
  BadRequestException,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource, EntityManager } from 'typeorm';
import { Order, OrderStatus } from './order.entity';
import { OrderItem } from './order-item.entity';
import { Cart } from '../cart/cart.entity';
import { CreateOrderDto } from './dto/create-order.dto';
import { ProductService } from '../products/product.service';

@Injectable()
export class OrderService {
  constructor(
    @InjectRepository(Order)
    private orderRepository: Repository<Order>,
    private dataSource: DataSource,
    private productService: ProductService,
  ) {}

  async create(userId: number, createOrderDto: CreateOrderDto): Promise<Order> {
    return this.dataSource.transaction(async (manager) => {
      // Get cart with items
      const cart = await manager
        .createQueryBuilder(Cart, 'cart')
        .leftJoinAndSelect('cart.items', 'items')
        .leftJoinAndSelect('items.product', 'product')
        .where('cart.userId = :userId', { userId })
        .getOne();

      if (!cart || cart.items.length === 0) {
        throw new BadRequestException('Cart is empty');
      }

      // Reserve stock for all items
      for (const item of cart.items) {
        const reserved = await this.productService.reserveStock(
          item.productId,
          item.quantity,
        );
        if (!reserved) {
          throw new BadRequestException(
            `Insufficient stock for ${item.product.name}`,
          );
        }
      }

      // Generate order number
      const orderNumber = await this.generateOrderNumber(manager);

      // Calculate totals
      const subtotal = cart.items.reduce(
        (sum, item) => sum + item.product.price * item.quantity,
        0,
      );

      // Create order
      const order = manager.create(Order, {
        orderNumber,
        userId,
        subtotal,
        totalAmount: subtotal,
        status: OrderStatus.PENDING,
      });

      const savedOrder = await manager.save(order);

      // Create order items
      const orderItems = cart.items.map((item) =>
        manager.create(OrderItem, {
          orderId: savedOrder.id,
          productId: item.productId,
          productName: item.product.name,
          unitPrice: item.product.price,
          quantity: item.quantity,
          totalPrice: item.product.price * item.quantity,
        }),
      );

      await manager.save(orderItems);

      // Create shipping address
      const shippingAddress = manager.create('ShippingAddress', {
        orderId: savedOrder.id,
        ...createOrderDto.shippingAddress,
      });
      await manager.save(shippingAddress);

      // Clear cart
      await manager.delete('CartItem', { cartId: cart.id });
      await manager.delete('Cart', { id: cart.id });

      return this.findOne(savedOrder.id);
    });
  }

  async updateStatus(
    id: number,
    status: OrderStatus,
  ): Promise<Order> {
    const order = await this.findOne(id);

    if (!this.isValidStatusTransition(order.status, status)) {
      throw new BadRequestException('Invalid status transition');
    }

    order.status = status;
    return this.orderRepository.save(order);
  }

  async cancel(id: number, userId: number): Promise<Order> {
    return this.dataSource.transaction(async (manager) => {
      const order = await this.findOne(id);

      if (order.userId !== userId) {
        throw new BadRequestException('Not authorized to cancel this order');
      }

      if (
        order.status !== OrderStatus.PENDING &&
        order.status !== OrderStatus.CONFIRMED
      ) {
        throw new BadRequestException('Order cannot be cancelled');
      }

      // Release reserved stock
      for (const item of order.items) {
        await this.productService.releaseReservedStock(
          item.productId,
          item.quantity,
        );
      }

      order.status = OrderStatus.CANCELLED;
      return manager.save(order);
    });
  }

  async findOne(id: number): Promise<Order> {
    const order = await this.orderRepository
      .createQueryBuilder('order')
      .leftJoinAndSelect('order.items', 'items')
      .leftJoinAndSelect('order.shippingAddress', 'shippingAddress')
      .leftJoinAndSelect('order.payment', 'payment')
      .where('order.id = :id', { id })
      .getOne();

    if (!order) {
      throw new NotFoundException('Order not found');
    }

    return order;
  }

  async findByUser(userId: number, page = 1, limit = 10) {
    const [data, total] = await this.orderRepository
      .createQueryBuilder('order')
      .leftJoinAndSelect('order.items', 'items')
      .where('order.userId = :userId', { userId })
      .orderBy('order.createdAt', 'DESC')
      .skip((page - 1) * limit)
      .take(limit)
      .getManyAndCount();

    return {
      data,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    };
  }

  private async generateOrderNumber(manager: EntityManager): Promise<string> {
    const prefix = 'ORD';
    const date = new Date().toISOString().slice(0, 10).replace(/-/g, '');

    const lastOrder = await manager
      .createQueryBuilder(Order, 'order')
      .where('order.orderNumber LIKE :prefix', { prefix: `${prefix}${date}%` })
      .orderBy('order.orderNumber', 'DESC')
      .getOne();

    let sequence = 1;
    if (lastOrder) {
      sequence = parseInt(lastOrder.orderNumber.slice(-4)) + 1;
    }

    return `${prefix}${date}${sequence.toString().padStart(4, '0')}`;
  }

  private isValidStatusTransition(
    current: OrderStatus,
    next: OrderStatus,
  ): boolean {
    const transitions: Record<OrderStatus, OrderStatus[]> = {
      [OrderStatus.PENDING]: [OrderStatus.CONFIRMED, OrderStatus.CANCELLED],
      [OrderStatus.CONFIRMED]: [OrderStatus.PROCESSING, OrderStatus.CANCELLED],
      [OrderStatus.PROCESSING]: [OrderStatus.SHIPPED],
      [OrderStatus.SHIPPED]: [OrderStatus.DELIVERED],
      [OrderStatus.DELIVERED]: [OrderStatus.REFUNDED],
      [OrderStatus.CANCELLED]: [],
      [OrderStatus.REFUNDED]: [],
    };

    return transitions[current].includes(next);
  }
}

---

47.4 Inventory Management

Stock Reservation Flow

                    Stock Reservation Flow
    ================================================================================

    +------------------+     +------------------+     +------------------+
    |   Add to Cart    |---->|  Reserve Stock   |---->|   Cart Active    |
    +------------------+     +------------------+     +------------------+
                                     |                        |
                                     |                        v
                                     |               +------------------+
                                     |               |  Stock Reserved  |
                                     |               |  (reservedStock) |
                                     |               +------------------+
                                     |                        |
                                     v                        v
    +------------------+     +------------------+     +------------------+
    |   Checkout       |---->|  Confirm Order   |---->|  Deduct Stock    |
    +------------------+     +------------------+     +------------------+
                                     |                        |
                                     |                        v
                                     |               +------------------+
                                     |               |  stock - qty     |
                                     |               |  reservedStock - |
                                     |               |  qty             |
                                     |               |  soldCount + qty |
                                     |               +------------------+
                                     |
                                     v
    +------------------+     +------------------+
    |   Cancel Order   |---->| Release Reserved |
    +------------------+     +------------------+
                                     |
                                     v
                            +------------------+
                            | reservedStock -  |
                            | qty              |
                            +------------------+

    ================================================================================

Inventory Service

src/inventory/inventory.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource } from 'typeorm';
import { Product } from '../products/product.entity';
import { InventoryLog } from './inventory-log.entity';

export enum InventoryAction {
  RESTOCK = 'restock',
  SALE = 'sale',
  RETURN = 'return',
  ADJUSTMENT = 'adjustment',
  RESERVE = 'reserve',
  RELEASE = 'release',
}

@Injectable()
export class InventoryService {
  constructor(
    @InjectRepository(Product)
    private productRepository: Repository<Product>,
    @InjectRepository(InventoryLog)
    private logRepository: Repository<InventoryLog>,
    private dataSource: DataSource,
  ) {}

  async adjustStock(
    productId: number,
    quantity: number,
    action: InventoryAction,
    reason: string,
    userId?: number,
  ): Promise<Product> {
    return this.dataSource.transaction(async (manager) => {
      const product = await manager.findOne(Product, {
        where: { id: productId },
        lock: { mode: 'pessimistic_write' },
      });

      if (!product) {
        throw new Error('Product not found');
      }

      const previousStock = product.stock;

      switch (action) {
        case InventoryAction.RESTOCK:
          product.stock += quantity;
          break;
        case InventoryAction.SALE:
          if (product.stock < quantity) {
            throw new Error('Insufficient stock');
          }
          product.stock -= quantity;
          product.soldCount += quantity;
          break;
        case InventoryAction.RETURN:
          product.stock += quantity;
          product.soldCount -= quantity;
          break;
        case InventoryAction.ADJUSTMENT:
          product.stock = quantity;
          break;
        default:
          throw new Error('Invalid action');
      }

      await manager.save(product);

      // Log the change
      const log = manager.create(InventoryLog, {
        productId,
        action,
        quantity,
        previousStock,
        newStock: product.stock,
        reason,
        userId,
      });
      await manager.save(log);

      return product;
    });
  }

  async getLowStockProducts(threshold = 10) {
    return this.productRepository
      .createQueryBuilder('product')
      .where('product.trackInventory = :track', { track: true })
      .andWhere('product.stock - product.reservedStock <= :threshold', {
        threshold,
      })
      .orderBy('product.stock', 'ASC')
      .getMany();
  }

  async getInventoryReport() {
    const stats = await this.productRepository
      .createQueryBuilder('product')
      .select([
        'COUNT(*) as totalProducts',
        'SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) as outOfStock',
        'SUM(CASE WHEN stock > 0 AND stock <= 10 THEN 1 ELSE 0 END) as lowStock',
        'SUM(stock * costPrice) as inventoryValue',
      ])
      .where('product.trackInventory = :track', { track: true })
      .getRawOne();

    return stats;
  }
}

---

47.5 Summary

This E-Commerce API project demonstrates:

• Complex Entity Relationships: Products, Categories, Orders, Cart, Payments
• Inventory Management: Stock reservation, confirmation, and release
• Order Processing: Status transitions, order number generation
• Transaction Management: Atomic operations for data consistency
• Query Optimization: Indexes, eager loading, query builders
• Business Logic: Stock validation, price calculations

---

Next Chapter

Chapter 48: Real-Time Chat API Project

---

Last Updated: February 2026