Portfolio Project · v1.0 Master Plan

Multi-Tenant Order Management System — Implementation Plan

A production-shaped mini-SaaS order management platform — customers, products, inventory, orders, payments, shipping, discounts and full audit history — built to prove enterprise application architecture, not just CRUD. NestJS + SQL Server backend, React + TypeScript frontend, deployed on AWS.

Author

R.M. — Arsi India Info

Project

modern-order-management-system

Repository

github.com/arsiindiainfo/modern-order-management-system

Visibility

Public · MIT-licensed code, trademarked brand

1. Executive Summary

This document is the master build plan for Modern Order Management System — a mini-SaaS platform where multiple independent companies (tenants) each manage their own customers, products, inventory, orders and payments behind one shared application. It is designed and written as a portfolio centerpiece: every decision below is chosen to demonstrate enterprise patterns — multi-tenancy, RBAC, optimistic locking, transactional integrity, a stored-procedure data-access layer, audit trails, structured error handling and cloud deployment — inside a project small enough for one developer to finish and defend in an interview.

The system is scoped intentionally small in business surface (one order type, one currency path, a handful of statuses) and intentionally deep in architectural surface (tenant isolation, JWT + RBAC, pagination/filter/sort conventions, optimistic locking with version columns, a stored-procedure-only data-access layer, DB transactions across order + inventory + payment, a full audit log, Swagger-documented contracts, unit + integration tests, Docker, and a real AWS deployment path). That trade-off is deliberate — see §2.

2. Why This Is the #1 Portfolio Project

Most portfolio CRUD apps prove you can wire a form to a database. This project is scoped to prove something stronger: that the author understands how a real B2B SaaS product is put together end to end.

3. Technology Stack

4. Repository & Folder Structure

One repository, two deployable apps, one shared documentation root — matching the scaffold already committed at github.com/arsiindiainfo/modern-order-management-system.

modern-order-management-system/
├── backend/                          # NestJS API
│   └── src/
│       ├── main.ts                   # bootstrap, global pipes/filters/interceptors, Swagger mount
│       ├── app.module.ts
│       ├── config/                   # typed config (env validation with Joi/Zod)
│       ├── database/
│       │   ├── migrations/           # TypeORM migrations — tables AND stored procedures (§6)
│       │   ├── procedures/           # .sql files, one per usp_*, deployed by migration
│       │   └── seeds/                # demo tenant + sample catalog seed script
│       ├── common/
│       │   ├── decorators/           # @CurrentUser, @Roles, @TenantId
│       │   ├── filters/              # AllExceptionsFilter → standard error envelope
│       │   ├── interceptors/         # ResponseEnvelopeInterceptor, AuditInterceptor
│       │   ├── guards/                # JwtAuthGuard, RolesGuard, TenantScopeGuard
│       │   ├── pipes/                 # ParsePaginationPipe
│       │   ├── database/              # StoredProcedureRunner — the only class allowed to EXEC (§6.3)
│       │   └── exceptions/            # domain exceptions (InsufficientStockException, etc.)
│       └── modules/
│           ├── auth/                  # login, refresh, logout
│           ├── tenants/               # tenant provisioning (super-admin only)
│           ├── users/                 # tenant users + roles
│           ├── customers/
│           ├── products/
│           ├── inventory/
│           ├── orders/                # orders, order-lines, status transitions
│           ├── payments/
│           ├── shipping/
│           ├── discounts/
│           └── audit/                 # read-only audit trail endpoints
│                 each module/: *.controller.ts · *.service.ts · *.module.ts
│                               dto/ (request + response DTOs) · *repository.ts (EXEC wrappers) · *.spec.ts
│
├── frontend/                         # React + Vite SPA
│   └── src/
│       ├── app/                       # routes, providers (QueryClient, Theme, AuthProvider)
│       ├── layouts/                   # AppShell (nav + BrandFooter — see §29), AuthLayout
│       ├── features/                  # one folder per business domain, mirrors backend modules
│       │   ├── orders/
│       │   │   ├── api/               # orderService.ts — axios calls only
│       │   │   ├── hooks/             # useOrders, useOrder, useCreateOrder (React Query)
│       │   │   ├── components/        # OrderTable, OrderForm, OrderStatusBadge
│       │   │   ├── pages/             # OrdersListPage, OrderDetailPage
│       │   │   └── types.ts
│       │   ├── customers/ · products/ · inventory/ · payments/ · discounts/ …
│       │   └── auth/
│       ├── components/ui/             # shared design-system wrappers over MUI (DataTable, FormField, ConfirmDialog)
│       ├── lib/                       # apiClient.ts (axios instance + interceptors), queryClient.ts
│       └── types/                     # API contract types generated/mirrored from backend DTOs
│
├── doc/                               # this plan + ADRs + ERD exports
├── infra/                             # AWS CDK or Terraform (see §26)
├── docker-compose.yml
├── NOTICE                             # Arsi India Info attribution notice — see §29
├── LICENSE                            # MIT (code)
└── README.md

5. Multi-Tenant Isolation

Single database, single schema, shared tables with a mandatory TenantId column — the standard "pooled" multi-tenancy model for a SaaS at this scale (cheapest to operate, easiest to demo, and the pattern most interviewers expect to see explained correctly).

5.1 How a tenant is established

1. User authenticates via POST /api/auth/login; the issued JWT carries tenantId and role as claims.
2. A TenantScopeGuard reads tenantId from the verified JWT (never from a client-supplied header or body field — that would let a caller forge cross-tenant access) and attaches it to a request-scoped TenantContext.
3. A thin repository layer is the only code allowed to call a stored procedure (via StoredProcedureRunner, §6.3). Every stored procedure takes @TenantId as its mandatory first parameter, sourced only from the request-scoped TenantContext — never from client input. There is no code path to a table without going through an SP, and no SP that skips the tenant filter.
4. SUPER_ADMIN (Arsi India Info operators, not tenant staff) is the only role exempt from the filter, used solely by the tenants module's own stored procedures to provision/manage tenants.

6. Database Schema & Stored Procedures

Every table below carries only the fields the UI and API actually need — no speculative columns. Internal/derived fields (password hashes, raw audit diffs) are never serialized into an API response; see §11.4. Every column that appears in a table below is reachable only through a stored procedure — the application never issues an inline query or lets an ORM build one against these tables.

6.1 Table inventory (13 tables)

6.2 Full DDL — all 13 tables

Applied in this dependency order by the first TypeORM migration. Every TenantId is a foreign key back to Tenants except where noted.

-- ═══ Tenants ═══════════════════════════════════════════════════
CREATE TABLE Tenants (
  Id         UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  Name       NVARCHAR(150) NOT NULL,
  Slug       NVARCHAR(80)  NOT NULL,
  PlanTier   NVARCHAR(20)  NOT NULL DEFAULT 'TRIAL'
    CHECK (PlanTier IN ('TRIAL','STANDARD','PRO')),
  IsActive   BIT NOT NULL DEFAULT 1,
  CreatedAt  DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UX_Tenants_Slug UNIQUE (Slug)
);

-- ═══ Users ═════════════════════════════════════════════════════
CREATE TABLE Users (
  Id            UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  TenantId      UNIQUEIDENTIFIER NOT NULL,
  FullName      NVARCHAR(150) NOT NULL,
  Email         NVARCHAR(255) NOT NULL,
  PasswordHash  NVARCHAR(255) NOT NULL,   -- bcrypt; never selected by a list/get SP
  Role          NVARCHAR(20)  NOT NULL
    CHECK (Role IN ('SUPER_ADMIN','TENANT_ADMIN','MANAGER','STAFF')),
  IsActive      BIT NOT NULL DEFAULT 1,
  LastLoginAt   DATETIME2(3) NULL,
  CreatedAt     DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UX_Users_Tenant_Email UNIQUE (TenantId, Email),
  CONSTRAINT FK_Users_TenantId FOREIGN KEY (TenantId) REFERENCES Tenants(Id)
);

-- ═══ RefreshTokens ═════════════════════════════════════════════
CREATE TABLE RefreshTokens (
  Id          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  UserId      UNIQUEIDENTIFIER NOT NULL,
  TokenHash   NVARCHAR(255) NOT NULL,
  ExpiresAt   DATETIME2(3) NOT NULL,
  RevokedAt   DATETIME2(3) NULL,
  CreatedAt   DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT FK_RefreshTokens_UserId FOREIGN KEY (UserId) REFERENCES Users(Id)
);
CREATE INDEX IX_RefreshTokens_TokenHash ON RefreshTokens (TokenHash);

-- ═══ Customers ═════════════════════════════════════════════════
CREATE TABLE Customers (
  Id               UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  TenantId         UNIQUEIDENTIFIER NOT NULL,
  Name             NVARCHAR(150) NOT NULL,
  Email            NVARCHAR(255) NOT NULL,
  Phone            NVARCHAR(30)  NULL,
  BillingAddress   NVARCHAR(MAX) NULL,  -- JSON: {line1, city, postalCode, country}
  ShippingAddress  NVARCHAR(MAX) NULL,
  IsActive         BIT NOT NULL DEFAULT 1,
  CreatedAt        DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT FK_Customers_TenantId FOREIGN KEY (TenantId) REFERENCES Tenants(Id)
);
CREATE INDEX IX_Customers_Tenant_Email ON Customers (TenantId, Email);

-- ═══ Products ══════════════════════════════════════════════════
CREATE TABLE Products (
  Id         UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  TenantId   UNIQUEIDENTIFIER NOT NULL,
  Sku        NVARCHAR(40)  NOT NULL,
  Name       NVARCHAR(200) NOT NULL,
  UnitPrice  DECIMAL(12,2) NOT NULL,
  Currency   CHAR(3) NOT NULL DEFAULT 'USD',
  IsActive   BIT NOT NULL DEFAULT 1,
  CreatedAt  DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UX_Products_Tenant_Sku UNIQUE (TenantId, Sku),
  CONSTRAINT FK_Products_TenantId FOREIGN KEY (TenantId) REFERENCES Tenants(Id)
);

-- ═══ InventoryItems ════════════════════════════════════════════
CREATE TABLE InventoryItems (
  Id                UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  TenantId          UNIQUEIDENTIFIER NOT NULL,
  ProductId         UNIQUEIDENTIFIER NOT NULL,
  QuantityOnHand    INT NOT NULL DEFAULT 0,
  QuantityReserved  INT NOT NULL DEFAULT 0,
  ReorderLevel      INT NOT NULL DEFAULT 0,
  UpdatedAt         DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UX_InventoryItems_ProductId UNIQUE (ProductId),
  CONSTRAINT FK_InventoryItems_TenantId  FOREIGN KEY (TenantId)  REFERENCES Tenants(Id),
  CONSTRAINT FK_InventoryItems_ProductId FOREIGN KEY (ProductId) REFERENCES Products(Id),
  CONSTRAINT CK_InventoryItems_NonNegative CHECK (QuantityOnHand >= 0 AND QuantityReserved >= 0)
);

-- ═══ Orders ════════════════════════════════════════════════════
CREATE TABLE Orders (
  Id               UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  TenantId         UNIQUEIDENTIFIER NOT NULL,
  OrderNumber      NVARCHAR(20)   NOT NULL,   -- 'ORD-2026-000482'
  CustomerId       UNIQUEIDENTIFIER NOT NULL,
  Status           NVARCHAR(20)   NOT NULL DEFAULT 'PENDING'
    CHECK (Status IN ('PENDING','CONFIRMED','PROCESSING',
                     'ON_HOLD','SHIPPED','DELIVERED','CANCELLED')),
  Currency         CHAR(3)        NOT NULL DEFAULT 'USD',
  Subtotal         DECIMAL(12,2)  NOT NULL DEFAULT 0,
  DiscountTotal    DECIMAL(12,2)  NOT NULL DEFAULT 0,
  TaxTotal         DECIMAL(12,2)  NOT NULL DEFAULT 0,
  ShippingTotal    DECIMAL(12,2)  NOT NULL DEFAULT 0,
  GrandTotal       DECIMAL(12,2)  NOT NULL DEFAULT 0,
  Version          INT            NOT NULL DEFAULT 1,   -- optimistic-locking column (§9.1)
  PlacedAt         DATETIME2(3)   NOT NULL DEFAULT SYSUTCDATETIME(),
  CreatedAt        DATETIME2(3)   NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt        DATETIME2(3)   NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UX_Orders_TenantId_OrderNumber UNIQUE (TenantId, OrderNumber),
  CONSTRAINT FK_Orders_TenantId   FOREIGN KEY (TenantId)   REFERENCES Tenants(Id),
  CONSTRAINT FK_Orders_CustomerId FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
);
CREATE INDEX IX_Orders_Tenant_Status   ON Orders (TenantId, Status);
CREATE INDEX IX_Orders_Tenant_Customer ON Orders (TenantId, CustomerId);

-- ═══ OrderLines ════════════════════════════════════════════════
CREATE TABLE OrderLines (
  Id           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  OrderId      UNIQUEIDENTIFIER NOT NULL,
  ProductId    UNIQUEIDENTIFIER NOT NULL,
  ProductName  NVARCHAR(200) NOT NULL,     -- snapshot at order time
  UnitPrice    DECIMAL(12,2) NOT NULL,     -- snapshot at order time
  Quantity     INT NOT NULL CHECK (Quantity > 0),
  LineTotal    DECIMAL(12,2) NOT NULL,
  CONSTRAINT FK_OrderLines_OrderId   FOREIGN KEY (OrderId)   REFERENCES Orders(Id) ON DELETE CASCADE,
  CONSTRAINT FK_OrderLines_ProductId FOREIGN KEY (ProductId) REFERENCES Products(Id)
);
CREATE INDEX IX_OrderLines_OrderId ON OrderLines (OrderId);

-- ═══ Payments ══════════════════════════════════════════════════
CREATE TABLE Payments (
  Id              UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  OrderId         UNIQUEIDENTIFIER NOT NULL,
  Provider        NVARCHAR(30)  NOT NULL,
  Amount          DECIMAL(12,2) NOT NULL,
  Currency        CHAR(3) NOT NULL DEFAULT 'USD',
  Status          NVARCHAR(20)  NOT NULL
    CHECK (Status IN ('CAPTURED','DECLINED','REFUNDED')),
  TransactionRef  NVARCHAR(100) NOT NULL,
  PaidAt          DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT FK_Payments_OrderId FOREIGN KEY (OrderId) REFERENCES Orders(Id)
);
CREATE INDEX IX_Payments_OrderId ON Payments (OrderId);

-- ═══ Shipments ═════════════════════════════════════════════════
CREATE TABLE Shipments (
  Id              UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  OrderId         UNIQUEIDENTIFIER NOT NULL,
  Carrier         NVARCHAR(60)  NOT NULL,
  TrackingNumber  NVARCHAR(80)  NOT NULL,
  Status          NVARCHAR(20)  NOT NULL DEFAULT 'IN_TRANSIT'
    CHECK (Status IN ('IN_TRANSIT','DELIVERED','RETURNED')),
  ShippedAt       DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  DeliveredAt     DATETIME2(3) NULL,
  CONSTRAINT FK_Shipments_OrderId FOREIGN KEY (OrderId) REFERENCES Orders(Id)
);
CREATE INDEX IX_Shipments_OrderId ON Shipments (OrderId);

-- ═══ Discounts ═════════════════════════════════════════════════
CREATE TABLE Discounts (
  Id           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  TenantId     UNIQUEIDENTIFIER NOT NULL,
  Code         NVARCHAR(40) NOT NULL,
  Type         NVARCHAR(10) NOT NULL CHECK (Type IN ('PERCENT','FIXED')),
  Value        DECIMAL(12,2) NOT NULL,
  StartsAt     DATETIME2(3) NOT NULL,
  EndsAt       DATETIME2(3) NOT NULL,
  UsageLimit   INT NULL,
  TimesUsed    INT NOT NULL DEFAULT 0,
  IsActive     BIT NOT NULL DEFAULT 1,
  CONSTRAINT UX_Discounts_Tenant_Code UNIQUE (TenantId, Code),
  CONSTRAINT FK_Discounts_TenantId FOREIGN KEY (TenantId) REFERENCES Tenants(Id)
);

-- ═══ OrderStatusHistory ════════════════════════════════════════
CREATE TABLE OrderStatusHistory (
  Id              UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  OrderId         UNIQUEIDENTIFIER NOT NULL,
  FromStatus      NVARCHAR(20) NULL,
  ToStatus        NVARCHAR(20) NOT NULL,
  ChangedByUserId UNIQUEIDENTIFIER NULL,   -- NULL = system-initiated
  Note            NVARCHAR(500) NULL,
  ChangedAt       DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT FK_OrderStatusHistory_OrderId FOREIGN KEY (OrderId) REFERENCES Orders(Id)
);
CREATE INDEX IX_OrderStatusHistory_OrderId ON OrderStatusHistory (OrderId, ChangedAt);

-- ═══ AuditLogs ═════════════════════════════════════════════════
CREATE TABLE AuditLogs (
  Id              UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  TenantId        UNIQUEIDENTIFIER NOT NULL,
  EntityName      NVARCHAR(60)  NOT NULL,
  EntityId        UNIQUEIDENTIFIER NOT NULL,
  Action          NVARCHAR(20)  NOT NULL CHECK (Action IN ('CREATE','UPDATE','DELETE')),
  ChangedByUserId UNIQUEIDENTIFIER NOT NULL,
  ChangedAt       DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT FK_AuditLogs_TenantId FOREIGN KEY (TenantId) REFERENCES Tenants(Id)
);
CREATE INDEX IX_AuditLogs_Tenant_Entity ON AuditLogs (TenantId, EntityName, EntityId);

6.3 Stored Procedures — the only data-access layer

Naming convention: usp_<Entity>_<Action>. Every tenant-scoped procedure takes @TenantId as parameter 1 (§5.1); every procedure that writes also takes @ActorUserId for the audit trail (§10).

Pattern A — paginated / filtered / sortable list

CREATE OR ALTER PROCEDURE dbo.usp_Customers_List
  @TenantId  UNIQUEIDENTIFIER,
  @Page      INT = 1,
  @PageSize  INT = 20,
  @SortBy    NVARCHAR(30) = 'createdAt',
  @SortDir   NVARCHAR(4)  = 'desc',
  @Search    NVARCHAR(150) = NULL
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Offset INT = (@Page - 1) * @PageSize;

  SELECT Id, Name, Email, Phone, IsActive, CreatedAt,
         COUNT(*) OVER() AS TotalItems
  FROM Customers
  WHERE TenantId = @TenantId
    AND (@Search IS NULL OR Name LIKE '%' + @Search + '%' OR Email LIKE '%' + @Search + '%')
  ORDER BY
    CASE WHEN @SortBy = 'name'      AND @SortDir = 'asc'  THEN Name END ASC,
    CASE WHEN @SortBy = 'name'      AND @SortDir = 'desc' THEN Name END DESC,
    CASE WHEN @SortBy = 'createdAt' AND @SortDir = 'desc' THEN CreatedAt END DESC
  OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
END

Every other list endpoint (usp_Products_List, usp_Orders_List, usp_Discounts_List, usp_Users_List) follows this exact shape — same parameters, same COUNT(*) OVER() trick for total count in one round trip, only the allow-listed sort columns and filter predicate change per entity (§22).

Pattern B — transactional multi-table write (order creation)

CREATE OR ALTER PROCEDURE dbo.usp_Orders_Create
  @TenantId      UNIQUEIDENTIFIER,
  @ActorUserId   UNIQUEIDENTIFIER,
  @CustomerId    UNIQUEIDENTIFIER,
  @DiscountCode  NVARCHAR(40) = NULL,
  @Lines         dbo.OrderLineInput READONLY   -- table-valued param: ProductId, Quantity
AS
BEGIN
  SET NOCOUNT ON; SET XACT_ABORT ON;
  BEGIN TRY
    BEGIN TRANSACTION;

    -- 1. lock and validate stock for every line before writing anything
    IF EXISTS (
      SELECT 1 FROM @Lines l
      JOIN InventoryItems i WITH (UPDLOCK, HOLDLOCK) ON i.ProductId = l.ProductId
      WHERE (i.QuantityOnHand - i.QuantityReserved) < l.Quantity
    )
    BEGIN
      RAISERROR('INSUFFICIENT_STOCK', 16, 1);
    END

    -- 2. reserve stock
    UPDATE i SET i.QuantityReserved = i.QuantityReserved + l.Quantity
    FROM InventoryItems i JOIN @Lines l ON l.ProductId = i.ProductId;

    -- 3. insert order header + lines (totals/discount resolution omitted for brevity)
    DECLARE @OrderId UNIQUEIDENTIFIER = NEWID();
    INSERT INTO Orders (Id, TenantId, OrderNumber, CustomerId, Status)
    VALUES (@OrderId, @TenantId, dbo.ufn_NextOrderNumber(@TenantId), @CustomerId, 'PENDING');

    INSERT INTO OrderLines (Id, OrderId, ProductId, ProductName, UnitPrice, Quantity, LineTotal)
    SELECT NEWID(), @OrderId, p.Id, p.Name, p.UnitPrice, l.Quantity, p.UnitPrice * l.Quantity
    FROM @Lines l JOIN Products p ON p.Id = l.ProductId;

    -- 4. first history row + audit row, same transaction
    INSERT INTO OrderStatusHistory (Id, OrderId, FromStatus, ToStatus, ChangedByUserId)
    VALUES (NEWID(), @OrderId, NULL, 'PENDING', @ActorUserId);

    INSERT INTO AuditLogs (Id, TenantId, EntityName, EntityId, Action, ChangedByUserId)
    VALUES (NEWID(), @TenantId, 'Order', @OrderId, 'CREATE', @ActorUserId);

    COMMIT TRANSACTION;
    SELECT * FROM Orders WHERE Id = @OrderId;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;   -- app layer maps ERROR_MESSAGE() to the §13 error-code catalog
  END CATCH
END

The whole operation is atomic at the database layer itself — SET XACT_ABORT ON plus TRY/CATCH/ROLLBACK means a dropped connection mid-write still leaves the database consistent; NestJS issues one EXEC call per order creation, never a multi-round-trip transaction held open across the network (contrast with §9.2's TypeORM-only alternative, which this project does not use).

Pattern C — optimistic-locked status transition

CREATE OR ALTER PROCEDURE dbo.usp_Orders_UpdateStatus
  @TenantId       UNIQUEIDENTIFIER,
  @ActorUserId    UNIQUEIDENTIFIER,
  @OrderId        UNIQUEIDENTIFIER,
  @ExpectedVersion INT,
  @ToStatus       NVARCHAR(20),
  @Note           NVARCHAR(500) = NULL
AS
BEGIN
  SET NOCOUNT ON; SET XACT_ABORT ON;
  DECLARE @FromStatus NVARCHAR(20);
  SELECT @FromStatus = Status FROM Orders WHERE Id = @OrderId AND TenantId = @TenantId;

  IF @FromStatus IS NULL RAISERROR('RESOURCE_NOT_FOUND', 16, 1);
  -- legality of @FromStatus → @ToStatus checked here against the same
  -- transition table as OrderStateMachine (§8) — kept in sync by a unit test
  IF NOT EXISTS (SELECT 1 FROM dbo.ufn_LegalOrderTransitions() WHERE FromStatus=@FromStatus AND ToStatus=@ToStatus)
    RAISERROR('INVALID_STATE_TRANSITION', 16, 1);

  BEGIN TRANSACTION;
    UPDATE Orders
    SET Status = @ToStatus, Version = Version + 1, UpdatedAt = SYSUTCDATETIME()
    WHERE Id = @OrderId AND TenantId = @TenantId AND Version = @ExpectedVersion;

    IF @@ROWCOUNT = 0
    BEGIN
      ROLLBACK TRANSACTION;
      RAISERROR('ORDER_VERSION_CONFLICT', 16, 1);  -- app layer → HTTP 409
    END

    INSERT INTO OrderStatusHistory (Id, OrderId, FromStatus, ToStatus, ChangedByUserId, Note)
    VALUES (NEWID(), @OrderId, @FromStatus, @ToStatus, @ActorUserId, @Note);
  COMMIT TRANSACTION;

  SELECT * FROM Orders WHERE Id = @OrderId;
END

Used by hold (@ToStatus='ON_HOLD'), resume, cancel, ship-triggered PROCESSING→SHIPPED, and delivery confirmation — one procedure, five API actions.

Everything else follows one of these three shapes

Simple get-by-id, create, update and deactivate procedures (usp_Products_GetById, usp_Customers_Create, …) are straightforward parameterized single-statement SPs scoped by @TenantId; they aren't reproduced here in full — the complete list and what each backs is in §6.4.

6.4 Stored procedure index (28 procedures → 35+ endpoints)

7. Authentication & RBAC

7.1 Auth flow

1. POST /api/auth/login calls usp_Auth_GetUserByEmail, verifies the password (bcrypt, application-side) → issues a short-lived access token (15 min, JWT) and a long-lived refresh token (7 days, stored as a hash via usp_RefreshTokens_Create, rotated on use).
2. Access token claims: sub (userId), tenantId, role, email, iat, exp.
3. POST /api/auth/refresh calls usp_RefreshTokens_Rotate; reuse of an already-rotated refresh token revokes the whole session (replay-attack guard).
4. POST /api/auth/logout calls usp_RefreshTokens_Revoke.

7.2 Roles

7.3 Enforcement

@Roles(Role.TENANT_ADMIN, Role.MANAGER)
@UseGuards(JwtAuthGuard, RolesGuard, TenantScopeGuard)
@Post(':id/hold')
holdOrder(@Param('id') id: string, @CurrentUser() user: AuthUser) {
  return this.ordersService.hold(id, user);
}

RolesGuard reads the @Roles() metadata and the JWT's role claim — a 403 FORBIDDEN_ROLE is returned before the handler runs if the role doesn't match. TenantScopeGuard (see §5) runs alongside it so a request must pass both checks.

8. Order Status Workflow

Legal transitions are defined once, in the SQL function dbo.ufn_LegalOrderTransitions() that usp_Orders_UpdateStatus checks against (§6.3) — and mirrored in a OrderStateMachine TypeScript constant used only for frontend button visibility (e.g. hiding "Cancel" once an order is SHIPPED). The database, not the application, is the source of truth for legality, since the database is the only place every write is guaranteed to pass through. An illegal transition throws INVALID_STATE_TRANSITION → HTTP 422 (§13). Every transition writes one row to OrderStatusHistory inside the same stored-procedure call as the status update — the history can never fall out of sync with the current status.

9. Optimistic Locking & Transaction Handling

9.1 Optimistic locking

Orders.Version increments on every status/field update. The client must send the version it last read (returned in every order response) as @ExpectedVersion. As shown in §6.3 Pattern C, the owning stored procedure issues UPDATE ... WHERE Id=@Id AND Version=@ExpectedVersion and checks @@ROWCOUNT itself — if zero rows updated, it raises ORDER_VERSION_CONFLICT, which the data-access layer maps to HTTP 409 (§13). The client re-fetches and retries instead of silently clobbering a concurrent edit (e.g. two staff members updating the same order at once).

9.2 Transaction boundaries

Each of these is one EXEC call wrapping its own BEGIN TRAN … COMMIT / ROLLBACK in T-SQL (§6.3) — the transaction lives and dies inside the database engine itself. NestJS never opens a transaction over the network and holds it across multiple round trips; a dropped connection mid-operation simply never commits, and the database is never left half-written.

10. Audit History

Two distinct, intentionally separate trails:

• OrderStatusHistory — business-facing, powers GET /api/orders/:id/history (§17) via usp_Orders_GetHistory, shown directly in the order timeline UI.
• AuditLogs — technical, cross-entity ("who touched what, when"), written by every mutating stored procedure as part of its own transaction. Exposed only to TENANT_ADMIN via usp_Audit_List / GET /api/audit, and deliberately returns only { entityName, entityId, action, changedBy, changedAt } — no raw before/after payload — keeping the public API surface minimal per this project's field-minimalism rule (§11.4).

11. API Design Conventions

11.1 Base path & versioning

All endpoints are prefixed /api. The contract is versioned via an Accept header convention (Accept: application/vnd.oms.v1+json) reserved for future breaking changes — v1 is implicit and default for this build.

11.2 Auth header

Every authenticated request sends Authorization: Bearer <accessToken>. The tenant is never passed by the client — it is derived from the token (§5).

11.3 Naming & verbs

• Plural, kebab-free resource nouns: /orders, /order-lines is nested under an order, never top-level.
• Standard verbs for CRUD; state changes are explicit sub-resources: POST /orders/:id/hold, POST /orders/:id/payment, never PATCH with a magic status field.

11.4 Field minimalism (demo-portfolio rule)

The same discipline applies to requests: the global ValidationPipe is configured with whitelist: true, forbidNonWhitelisted: true, so a request body containing a field the DTO doesn't declare is rejected with 400 VALIDATION_FAILED rather than silently ignored or silently persisted.

app.useGlobalPipes(new ValidationPipe({
  whitelist: true,             // strip unknown fields
  forbidNonWhitelisted: true,  // ...or reject the request outright
  transform: true,
  errorHttpStatusCode: 400,
}));

12. Standard Response & Error Envelope

Every response — success or failure — follows one of two shapes, applied globally by a ResponseEnvelopeInterceptor and an AllExceptionsFilter, so frontend code never branches on response shape per-endpoint.

12.1 Success — single resource

HTTP/1.1 200 OK

{
  "success": true,
  "data": {
    "id": "6f1a2e10-9c3d-4b7a-8e2f-1a2b3c4d5e6f",
    "orderNumber": "ORD-2026-000482",
    "status": "CONFIRMED",
    "grandTotal": 249.99,
    "currency": "USD",
    "version": 2
  }
}

12.2 Success — paginated list

HTTP/1.1 200 OK

{
  "success": true,
  "data": [ /* array of resource objects */ ],
  "meta": {
    "page": 1,
    "pageSize": 20,
    "totalItems": 134,
    "totalPages": 7
  }
}

12.3 Error

HTTP/1.1 409 Conflict

{
  "success": false,
  "error": {
    "statusCode": 409,
    "code": "ORDER_VERSION_CONFLICT",
    "message": "This order was modified by someone else. Reload and try again.",
    "traceId": "a1c9f3e2-88b1-4e77-9c2a-df0512aab933"
  }
}

code is a stable machine-readable string the frontend can switch on (e.g. to highlight a specific field); message is the human-readable string shown in a toast; traceId matches the structured server log line, so a bug report ("traceId a1c9f3e2…") is instantly greppable in CloudWatch. Validation errors additionally include a fields array:

{
  "success": false,
  "error": {
    "statusCode": 400,
    "code": "VALIDATION_FAILED",
    "message": "One or more fields are invalid.",
    "traceId": "...",
    "fields": [
      { "field": "email", "message": "email must be a valid email address" },
      { "field": "quantity", "message": "quantity must be a positive integer" }
    ]
  }
}

13. Error Code Catalog

The AllExceptionsFilter maps a SQL Server RAISERROR message text (e.g. 'ORDER_VERSION_CONFLICT', thrown as shown in §6.3) directly to its matching row in this table — the stored procedure and the API contract share one vocabulary of error codes, never two.

14. API Reference — Auth

POST/api/auth/login

Public. Exchanges credentials for a token pair.

// Request
{ "email": "manager@acme-demo.com", "password": "••••••••" }

// 200 Response
{
  "success": true,
  "data": {
    "accessToken": "eyJhbGciOi...",
    "refreshToken": "8f3c1a...".,
    "expiresIn": 900,
    "user": { "id": "...", "fullName": "Priya Shah", "role": "MANAGER", "tenantId": "..." }
  }
}

// 401 Response — wrong credentials
{ "success": false, "error": { "statusCode": 401, "code": "INVALID_CREDENTIALS",
    "message": "Email or password is incorrect.", "traceId": "..." } }

15. API Reference — Customers

GET/api/customers/:id/orders

Roles: all authenticated. Backed by usp_Customers_GetOrders (§6.4). Returns the order history for one customer — the exact shape requested in the brief.

// 200 Response
{
  "success": true,
  "data": [
    { "id": "...", "orderNumber": "ORD-2026-000482", "status": "SHIPPED",
      "grandTotal": 249.99, "currency": "USD", "placedAt": "2026-08-01T09:12:00Z" }
  ],
  "meta": { "page": 1, "pageSize": 20, "totalItems": 3, "totalPages": 1 }
}

// 404 Response — unknown or cross-tenant customer id
{ "success": false, "error": { "statusCode": 404, "code": "RESOURCE_NOT_FOUND",
    "message": "Customer not found.", "traceId": "..." } }

16. API Reference — Products & Inventory

GET/api/products/:id/inventory

Roles: all authenticated. Backed by usp_Products_GetInventory (§6.4). Current stock position for one product.

// 200 Response
{
  "success": true,
  "data": {
    "productId": "...", "sku": "MUG-BLK-11OZ",
    "quantityOnHand": 480, "quantityReserved": 36,
    "quantityAvailable": 444, "reorderLevel": 50
  }
}

quantityAvailable is computed inside the stored procedure's SELECT (onHand − reserved) — never stored, so it can never drift out of sync with its inputs.

17. API Reference — Orders, Payments & Shipping

GET/api/orders

Roles: all authenticated. Paginated, filterable, sortable (§22), backed by usp_Orders_List.

// GET /api/orders?status=PENDING&sort=placedAt:desc&page=1&pageSize=20

// 200 Response
{
  "success": true,
  "data": [
    { "id": "...", "orderNumber": "ORD-2026-000512", "customerName": "Blue Sky Retail",
      "status": "PENDING", "grandTotal": 89.50, "currency": "USD", "placedAt": "2026-08-22T14:03:00Z" }
  ],
  "meta": { "page": 1, "pageSize": 20, "totalItems": 58, "totalPages": 3 }
}

POST/api/orders

Roles: TENANT_ADMIN MANAGER STAFF. Backed by usp_Orders_Create (§6.3 Pattern B).

// Request
{
  "customerId": "c9d1e2f3-...",
  "discountCode": "WELCOME10",
  "lines": [
    { "productId": "p1a2b3c4-...", "quantity": 2 },
    { "productId": "p5d6e7f8-...", "quantity": 1 }
  ]
}

// 201 Response
{
  "success": true,
  "data": {
    "id": "6f1a2e10-...", "orderNumber": "ORD-2026-000513", "status": "PENDING",
    "subtotal": 99.00, "discountTotal": 9.90, "taxTotal": 7.13,
    "shippingTotal": 5.00, "grandTotal": 101.23, "version": 1
  }
}

// 409 Response — a line requests more than is available
{ "success": false, "error": { "statusCode": 409, "code": "INSUFFICIENT_STOCK",
    "message": "Only 1 unit of MUG-BLK-11OZ is available.", "traceId": "..." } }

PUT/api/orders/:id

Updates order-level fields (e.g. shipping address) via usp_Orders_Update. Requires the caller's last-known version — see §9.1.

// Request
{ "version": 1, "shippingAddress": { "line1": "221B Baker Street", "city": "London", "postalCode": "NW1 6XE", "country": "GB" } }

// 200 Response — version increments
{ "success": true, "data": { "id": "...", "version": 2, "...": "..." } }

POST/api/orders/:id/hold

Roles: TENANT_ADMIN MANAGER. Moves the order to ON_HOLD via usp_Orders_UpdateStatus (§6.3 Pattern C); requires a reason.

// Request
{ "reason": "Awaiting customer confirmation on substitute item" }

// 200 Response
{ "success": true, "data": { "id": "...", "status": "ON_HOLD", "version": 3 } }

// 422 Response — order already shipped
{ "success": false, "error": { "statusCode": 422, "code": "INVALID_STATE_TRANSITION",
    "message": "Cannot place a SHIPPED order on hold.", "traceId": "..." } }

POST/api/orders/:id/payment

Records a payment capture via usp_Orders_RecordPayment; advances status when fully paid.

// Request
{ "provider": "STRIPE", "amount": 101.23, "currency": "USD", "transactionRef": "pi_3P9x..." }

// 201 Response
{
  "success": true,
  "data": { "paymentId": "...", "status": "CAPTURED", "order": { "id": "...", "status": "CONFIRMED", "version": 4 } }
}

// 402 Response — provider declined
{ "success": false, "error": { "statusCode": 402, "code": "PAYMENT_FAILED",
    "message": "Payment provider declined the transaction.", "traceId": "..." } }

GET/api/orders/:id/history

Backed by usp_Orders_GetHistory.

// 200 Response
{
  "success": true,
  "data": [
    { "fromStatus": null,        "toStatus": "PENDING",   "changedBy": "Priya Shah", "changedAt": "2026-08-22T14:03:00Z" },
    { "fromStatus": "PENDING",   "toStatus": "CONFIRMED", "changedBy": "System",     "changedAt": "2026-08-22T14:05:12Z", "note": "Payment captured" },
    { "fromStatus": "CONFIRMED", "toStatus": "ON_HOLD",   "changedBy": "Priya Shah", "changedAt": "2026-08-23T09:00:00Z", "note": "Awaiting customer confirmation on substitute item" }
  ]
}

18. API Reference — Discounts

POST/api/discounts/validate

Used by the order form to preview a discount before submitting the order. Backed by usp_Discounts_Validate, which writes nothing.

// Request
{ "code": "WELCOME10", "subtotal": 99.00 }

// 200 Response
{ "success": true, "data": { "code": "WELCOME10", "type": "PERCENT", "value": 10, "discountAmount": 9.90 } }

// 422 Response — expired
{ "success": false, "error": { "statusCode": 422, "code": "DISCOUNT_NOT_APPLICABLE",
    "message": "Discount code WELCOME10 has expired.", "traceId": "..." } }

19. Full Endpoint Index

20. Frontend Architecture

Feature-folder structure (§4), one features/<domain> per backend module. Each feature owns its own API calls, hooks, components, pages and types — nothing reaches across features except through the shared components/ui design-system layer and typed API responses.

20.1 Data flow

Page component
  └─ useOrders() / useOrder(id)         // React Query hook
       └─ ordersService.list() / .get()  // axios call, returns typed Order[]
            └─ apiClient (axios instance)
                 ├─ request interceptor: attach Authorization header
                 └─ response interceptor: on 401 → silent refresh → retry once
                                           on error → normalize to AppApiError { code, message, fields }

20.2 State ownership rule

21. UI & Validation Standards

Applied uniformly to every screen — list pages, detail pages, and every form — via the shared components/ui layer, so quality doesn't depend on remembering to add it per page.

22. Pagination, Filtering & Sorting

One shared query-parsing pipe (ParsePaginationPipe) applied to every list endpoint, whose output maps 1:1 onto the @Page/@PageSize/@SortBy/@SortDir parameters every list stored procedure accepts (§6.3 Pattern A) — the contract never varies module to module.

GET /api/orders?page=2&pageSize=20&sort=placedAt:desc&status=PENDING&placedAt[gte]=2026-08-01

• Pagination: page (1-based, default 1), pageSize (default 20, max 100 — larger values are clamped, not rejected).
• Sorting: sort=field:asc|desc, restricted per-endpoint to an explicit allow-list of sortable columns, mirrored by the CASE WHEN @SortBy = '...' branches inside the SP (never a dynamically-built ORDER BY string — that would reopen the injection risk §6.3 closes off).
• Filtering: exact-match via field=value; range operators via field[gte]/field[lte] for dates and numbers. Unknown filter keys are rejected with 400 VALIDATION_FAILED, consistent with §11.4.

23. Testing Strategy

Target: ≥80% line coverage on modules/**/*.service.ts, enforced in CI via Jest's coverageThreshold. Frontend: component tests (Testing Library) for the form-validation and empty/error/loading states in §21, run in CI alongside backend tests.

24. Swagger / OpenAPI

@nestjs/swagger mounted at /api/docs (disabled in production behind an env flag, enabled in dev/staging). Every DTO carries @ApiProperty() with an example value, so the generated docs match the exact payloads shown in §14–§18 — the Swagger schema and this plan are generated from the same DTO classes, never hand-duplicated.

export class CreateOrderDto {
  @ApiProperty({ example: 'c9d1e2f3-...' })
  @IsUUID()
  customerId: string;

  @ApiProperty({ required: false, example: 'WELCOME10' })
  @IsOptional() @IsString()
  discountCode?: string;

  @ApiProperty({ type: () => CreateOrderLineDto, isArray: true })
  @ValidateNested({ each: true }) @Type(() => CreateOrderLineDto)
  @ArrayMinSize(1)
  lines: CreateOrderLineDto[];
}

25. Docker & Local Development

# docker-compose.yml — services
sqlserver:   # mcr.microsoft.com/mssql/server, dev credentials; migration step deploys tables + all usp_* procedures on first boot
backend:     # NestJS API, hot-reload volume mount in dev
frontend:    # Vite dev server
localstack:  # S3 emulation for local document-upload testing

Multi-stage Dockerfile per app for production images (build stage → slim runtime stage). docker-compose up is the entire "how do I run this" story in the README — no undocumented setup steps.

26. AWS Deployment Architecture

27. Build Phases & Roadmap

28. Risks & Mitigations

29. Copyright, Trademark & Watermark Protection

This repository will be public on GitHub. The goal of this section is to make sure that wherever this code or its UI ends up, it is always possible to prove it originated from Arsi India Info — and to make quietly stripping that attribution both harder to do cleanly and a breach of the license under which the code was obtained.

Arsi India Info — Innovate · Integrate · Elevate.
Author of record for this repository and every file in it.

29.1 Two-layer licensing (why MIT alone isn't enough)

MIT is kept as the code license — permissive, portfolio-friendly, and exactly what reviewers expect to see on a public repo. But MIT's own terms already require something useful: "The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software." Anyone who redistributes this code must keep the copyright notice — deleting it from copies is itself a license violation, not a gray area. On top of that legal floor, the project adds a second, separate layer that MIT intentionally does not cover: brand assets.

29.2 Where the signature lives (defense in depth)

No single copy of the attribution is load-bearing — it is deliberately repeated across layers that don't all break at once:

29.3 Honest limits, and the real proof of authorship

Authorship is ultimately proven the same way it always is in open source, and this plan leans on it deliberately:

• The canonical repository — github.com/arsiindiainfo/modern-order-management-system — is owned by the Arsi India Info GitHub organization, with full commit history and timestamps predating any fork.
• Commits should be GPG-signed (recommended in Phase 0, §27) so authorship of each change is cryptographically verifiable, not just claimed.
• A CODEOWNERS file names Arsi India Info as the maintainer of record for every path in the repo.
• Every tagged release publishes a SHA-256 checksum of the build artifact in the README, so an "official" build can always be verified against a tampered redistribution.

© 2026 Arsi India Info. All rights reserved.

Modern Order Management System — source code licensed under MIT · name & logo protected under TRADEMARK.md
arsiindiainfo.com · github.com/arsiindiainfo

30. Definition of Done

This is a solo portfolio build, so §18's formal sign-off table is replaced with a checklist the author can honestly self-certify against before calling v1 "done":