Schema Design for E-commerce Functionality

Building a functional e-commerce backend requires careful consideration of data structures, particularly for user management, product catalogs, shopping carts, and transactional orders. The author's approach leverages Prisma, an ORM that simplifies database interactions and schema definition. The core models include User, Product, Cart, and Order. The User model establishes basic authentication fields like id, name, email, password, and a role. This role distinction is critical for differentiating between regular customers and administrators who manage products.

The Product model encompasses essential e-commerce attributes: id, name, description, price, and stock. The stock field is particularly vital for inventory management, directly impacting the order placement logic. A key decision here is how to handle product variants or multiple images, which would necessitate additional related models or JSON fields, though the provided schema focuses on the core requirements.

The Cart model acts as a temporary holding space for items a user intends to purchase. It typically links to a User and contains a list of CartItems. Each CartItem would reference a specific Product and include a quantity. This structure allows users to add, remove, and update quantities before committing to a purchase. The relationship between User and Cart is one-to-one, ensuring each user has a single active cart.

The Order model represents a completed transaction. It includes fields such as id, userId, totalPrice, status (e.g., pending, processing, shipped, delivered), and timestamps for creation and updates. Crucially, an order is composed of OrderItems, which are snapshots of the products purchased at the time of the order. Each OrderItem would store the product ID, name, price, quantity, and stock at the moment of purchase. This denormalization is essential because product details like price or stock can change over time, and an order record must reflect the state of the transaction when it occurred.

Prisma schema definition for User, Product, Cart, and Order models

Authentication Flow and JWT Integration

The authentication system builds upon patterns established in a previous project, utilizing JSON Web Tokens (JWT) for secure session management. Upon user registration, a hashed password is stored in the database. For login, the provided credentials are checked against the stored hash. If valid, a JWT is generated, containing user information such as their ID and role, and returned to the client. This token is then included in subsequent requests to protected API endpoints, allowing the backend to identify and authorize the user.

The backend uses Express.js middleware to verify JWTs on incoming requests. This middleware decodes the token, extracts user details, and attaches them to the request object. This ensures that only authenticated users can access sensitive operations like adding items to a cart or placing an order. The author emphasizes that while JWTs are effective for stateless authentication, managing token expiration and refresh mechanisms is a critical consideration for production systems. For administrative functions, the role information embedded in the JWT is used to enforce authorization, ensuring only users with an 'admin' role can, for instance, modify product inventory.

Cart Management Logic

Managing the shopping cart involves several key operations: adding items, updating quantities, removing items, and clearing the cart. When a user adds a product to their cart, the backend needs to:

  • Verify the product exists and is in stock.
  • Check if the user already has this product in their cart.
  • If the product is already in the cart, update its quantity.
  • If it's a new product, add it to the cart with the specified quantity.
  • Ensure that the quantity does not exceed available stock.

The process for updating an item's quantity follows a similar pattern, again with a check against available stock. Removing an item is straightforward, involving the deletion of a specific cart item record. Clearing the cart typically involves deleting all items associated with a user's cart. These operations are performed via API endpoints that interact with the Prisma client to modify the Cart and CartItem models.

A crucial aspect of cart management, especially before an order is finalized, is its relationship with product stock. While the cart itself doesn't directly deduct stock, the system must prevent users from adding more items than are available. This involves querying the Product.stock field before any cart modification that increases quantity.

Transactional Order Placement

The most complex and critical part of an e-commerce backend is the order placement flow, which demands transactional integrity. This means that multiple database operations must succeed or fail as a single unit to prevent data inconsistencies. In this scenario, placing an order involves at least two core operations:

  1. Deducting the purchased quantity from the product's stock.
  2. Creating the order record and its associated order items.

If these operations are not atomic, a user might successfully place an order (creating an order record) but the stock might not be updated, leading to overselling. Conversely, stock might be deducted, but the order record creation could fail, leaving the system in an inconsistent state. To address this, the backend utilizes database transactions. Prisma's transaction API allows developers to group multiple database operations within a single transaction block. Within this block, all operations are executed sequentially, and if any operation fails, the entire transaction is rolled back, ensuring the database remains in its original state.

The author describes this as the first genuinely transactional piece of logic in their internship projects. The flow would look something like this:

  1. Start a database transaction.
  2. Iterate through each item in the user's cart.
  3. For each item, check if sufficient stock is available (e.g., product.stock >= cartItem.quantity). If not, abort the transaction and return an error.
  4. If stock is sufficient, deduct the quantity from the product's stock (e.g., UPDATE Product SET stock = stock - ? WHERE id = ?).
  5. After successfully updating stock for all cart items, create the Order record and associated OrderItem records based on the cart's contents.
  6. Commit the transaction.
  7. If any step fails, the transaction is automatically rolled back by Prisma.
  8. Finally, clear the user's cart.

This atomic process guarantees that an order is only confirmed if stock is available and successfully decremented, and that the order details are accurately recorded simultaneously. This approach prevents overselling and maintains data integrity, which is paramount for any e-commerce platform. The choice of PostgreSQL as the database backend is well-suited for this due to its robust support for ACID-compliant transactions.