Physical Counts Module

Overview

The physical-counts module provides CRUD operations for recording physical inventory count documents. A physical count captures a snapshot of actual inventory at a location, including the counted items and their quantities/prices stored as a JSON detail payload.

Domain Concepts

• Physical Count: A document representing one physical inventory counting event at a specific location and date.
• Detail: A JSON object containing an items array with product-level count data (product ID, name, quantity, price, total).
• Multi-tenancy: All records are scoped to a businessId. Operations require business context for data isolation.

Database Schema

Table: physical_count

Column	Type	Nullable	Description
id	UUID (PK)	No	Auto-generated
business_id	UUID (FK → business)	No	Multi-tenancy scope
count_date	TIMESTAMPTZ	No	Date count was performed
created_at	TIMESTAMPTZ	No	Auto-generated
created_by	UUID (FK → user)	No	User who created the record
detail	JSON	No	Counted items array
document_number	VARCHAR	Yes	Human-readable document number
location_id	UUID (FK → location)	Yes	Location where count was performed
location_name	VARCHAR	Yes	Denormalized location name
status	VARCHAR	Yes	e.g. draft, in_progress, completed, canceled
updated_at	TIMESTAMPTZ	Yes	Last update timestamp
updated_by	UUID (FK → user)	Yes	User who last updated

Architecture

Follows hexagonal architecture:

physical-counts/
├── domain/
│   └── physical-counts-repository.domain.ts   # Port (interface)
├── application/
│   └── physical-counts.service.ts             # Use cases
├── infrastructure/
│   └── physical-counts.repository.ts          # Kysely adapter
└── interfaces/
    ├── physical-counts.controller.ts          # HTTP routes
    ├── dtos/
    │   ├── create-physical-count.dto.ts
    │   └── update-physical-count.dto.ts
    └── query/
        └── paginate-physical-counts.query.ts

API Endpoints

Method	Route	Description
POST	/physical-counts	Create a physical count
GET	/physical-counts	List physical counts (paginated, filterable)
GET	/physical-counts/:id?businessId=	Get a physical count by ID
PATCH	/physical-counts/:id	Update a physical count
DELETE	/physical-counts/:id?businessId=	Delete a physical count

Query Parameters (List)

Param	Description
businessId	Filter by business (UUID)
locationId	Filter by location (UUID)
size	Page size (default: 20)
page	Page number (default: 1)
search	Search by location name, document number, or status
orderBy	Sort field: locationName, countDate, status, createdAt
order	Sort direction: asc or desc

Example: Create Physical Count

POST /physical-counts
{
  "businessId": "uuid",
  "createdBy": "uuid",
  "countDate": "2026-03-25T00:00:00.000Z",
  "locationId": "uuid",
  "locationName": "Main Warehouse",
  "status": "draft",
  "detail": {
    "items": [
      {
        "productId": "uuid",
        "productName": "COCA COLA",
        "quantity": 9,
        "price": 12.00,
        "total": 108.00
      }
    ]
  }
}

Design Decisions

1. Detail as JSON: The detail column stores a flexible JSON payload rather than normalized rows. This allows the count document to be self-contained and avoids joins for read-heavy operations.

2. Denormalized locationName: Stored alongside locationId for display purposes without requiring a join on reads.

3. businessId scoping: All read/update/delete operations require businessId to enforce multi-tenancy isolation at the query level.