Compass - Nimbly Documentation

Stock Audit backend

10 min read

1. Overview

The API-SKUs microservice is a critical component of the Nimbly inventory management system. It provides comprehensive functionality for managing SKUs, scheduling inventory checks, tracking stock movements, and generating detailed reports. The service implements a clean architecture pattern with clear separation between controllers, use cases, and repositories, supporting both MongoDB and Firebase as data stores.

System Integrations:

  • Inventory - Main inventory management feature
  • Schedule - Inventory check scheduling
  • Reports - Inventory reporting and analytics
  • Sites - Location-based inventory tracking
  • Authentication - Access control and security
  • Users - User assignment and permissions

Key Features:

  • SKU Management: Create, update, delete, and bulk import SKUs
  • Schedule Management: Configure recurring and ad-hoc inventory check schedules
  • Report Generation: Track inventory counts, movements, and generate PDF/XLSX reports
  • Multi-Database Architecture: Hybrid approach using MongoDB and Firebase
  • Authentication: JWT-based authentication with role-based access control
  • Asynchronous Processing: Pub/sub for report generation and email delivery

2. Libraries and Dependencies

Core Framework Dependencies:

  • Express.js (^4.18.1): Web application framework
  • TypeScript (4.7.4): Type-safe JavaScript development
  • Node.js: Runtime environment (Engine: Node 20)

Database and Storage:

  • mongoose (5.11.10): MongoDB ODM for data modeling
  • firebase-admin (^11.5.0): Firebase Admin SDK for Realtime Database and Storage
  • firebase-functions (^3.24.0): Firebase Functions support

Authentication and Security:

  • JWT: JSON Web Token authentication (via custom middleware)
  • cors (^2.8.5): Cross-Origin Resource Sharing support
  • dotenv (^8.6.0): Environment variable management

Data Processing and Validation:

  • @hapi/joi (^17.1.0) & joi (^17.6.0): Schema validation
  • ramda (^0.27.2): Functional programming utilities
  • moment (^2.29.4) & moment-timezone (^0.5.34): Date/time manipulation
  • papaparse (^5.3.0): CSV parsing for [bulk operationss](../Bulk Operation/BulkOperationOverview.md)
  • emoji-regex (^10.1.0): Emoji validation support

File Processing:

  • pdfmake (^0.2.5): PDF generation for reports
  • @sheet/image: Excel file manipulation
  • jimp (^0.16.2): Image processing
  • image-size (^1.0.2): Image dimension detection
  • fluent-ffmpeg (^2.1.2): Video processing for thumbnails
  • @ffmpeg-installer/ffmpeg (^1.0.13): FFmpeg binaries
  • @ffprobe-installer/ffprobe (^2.1.2): FFprobe binaries

Communication and Notifications:

  • @sendgrid/mail (^7.7.0): Email delivery service
  • axios (^0.19.2): HTTP client for API calls

Utilities:

  • busboy (^1.6.0): File upload parsing
  • base64-stream (^1.0.0): Base64 encoding streams
  • mkdirp (^1.0.4): Directory creation
  • uuidv4 (^6.2.13): UUID generation

Monitoring and Logging:

  • @google-cloud/trace-agent (^5.1.3): Google Cloud tracing
  • morgan (^1.9.1): HTTP request logging
  • express-http-context (^1.2.4): Request context management

Nimbly Internal Packages:

  • @nimbly-technologies/entity-node (1.66.81): Entity definitions
  • @nimbly-technologies/nimbly-access (^1.0.0): [Access control](../Settings/Access control/AccessControlOverview.md)
  • @nimbly-technologies/nimbly-backend-utils (^1.1.2): Common utilities
  • @nimbly-technologies/nimbly-common (1.92.86): Shared types

Development Dependencies:

  • jest (^27.0.0): Testing framework
  • eslint (^8.19.0): Code linting
  • prettier (^2.7.1): Code formatting
  • nodemon (^2.0.19): Development server

3. API Endpoints

SKU Management Endpoints

MethodEndpointDescriptionHandler
GET/searchSearch SKUs with pagination and filtersSKUController.findByQueryPaginate
GET/bulk-uploadDownload SKU bulk upload templateSKUController.getSKUBulkUploadTemplate
POST/bulk-uploadBulk upload SKUs via ExcelSKUController.bulkUploadSKUs
GET/Find SKUs by organizationSKUController.findByOrganizationID
POST/Create new SKUSKUController.create
GET/compactGet SKUs grouped by categorySKUController.findSKUPerCategory
GET/skuID/:skuIDFind SKU by SKU IDSKUController.findBySKUID
GET/:idFind SKU by MongoDB IDSKUController.findByID
PATCH/:idUpdate SKUSKUController.update
DELETE/:idSoft delete SKUSKUController.delete

Organization SKU Endpoints

MethodEndpointDescriptionHandler
GET/organization-skus/Get all organization SKUsOrganizationSKUController.findAllByOrganizationID

Site SKU Endpoints

MethodEndpointDescriptionHandler
GET/site-skus/Get all site SKUsSiteSKUController.findAllByOrganizationID
GET/site-skus/fetchSites/:userIDGet user’s assigned sitesSiteSKUController.fetchUserSites
GET/site-skus/analytics/inventory/:siteIDGet inventory analyticsSiteSKUController.getSiteInventory

SKU Settings Endpoints

MethodEndpointDescriptionHandler
GET/sku-settings/categoriesGet SKU categoriesSKUSettingsController.findCategories
POST/sku-settings/categoriesAdd new categorySKUSettingsController.addCategory

[Schedule](../Schedule/Schedule Listing/ScheduleListingOverview.md) Management Endpoints

MethodEndpointDescriptionHandler
POST/schedules/Create [schedule](../Schedule/Schedule Listing/ScheduleListingOverview.md)SKUScheduleController.create
DELETE/schedules/:idDelete [schedule](../Schedule/Schedule Listing/ScheduleListingOverview.md)SKUScheduleController.delete
PUT/schedules/site/:siteIDDisable site [schedules](../Schedule/Schedule Listing/ScheduleListingOverview.md)SKUScheduleController.findAndDisableSchedulesBysiteID
PATCH/schedules/:idUpdate [schedule](../Schedule/Schedule Listing/ScheduleListingOverview.md)SKUScheduleController.update
GET/schedules/searchSearch [schedules](../Schedule/Schedule Listing/ScheduleListingOverview.md)SKUScheduleController.findByQueryPaginate
GET/schedules/activeGet active [schedules](../Schedule/Schedule Listing/ScheduleListingOverview.md) by dateSKUScheduleController.findActiveUserScheduleByDates
GET/schedules/active/siteGet active site [schedules](../Schedule/Schedule Listing/ScheduleListingOverview.md)SKUScheduleController.findActiveScheduleBySites
GET/schedules/active/summaryGet [schedule](../Schedule/Schedule Listing/ScheduleListingOverview.md) summarySKUScheduleController.findActiveUserScheduleSummaryByDates
GET/schedules/userGet user [schedules](../Schedule/Schedule Listing/ScheduleListingOverview.md)SKUScheduleController.findActiveAtDates
GET/schedules/get-timezone/:scheduleIDGet [schedule](../Schedule/Schedule Listing/ScheduleListingOverview.md) timezoneSKUScheduleController.getTimezone
GET/schedules/:scheduleIDGet [schedule](../Schedule/Schedule Listing/ScheduleListingOverview.md) by IDSKUScheduleController.findByScheduleID
GET/schedules/Get organization [schedules](../Schedule/Schedule Listing/ScheduleListingOverview.md)SKUScheduleController.findByOrganizationID

Report Management Endpoints

MethodEndpointDescriptionHandler
GET/reports/summaryGet reports summarySKUReportController.findSKUReports
GET/reports/:skuReportIDGet report by IDSKUReportController.findByReportID
PATCH/reports/:skuReportIDUpdate reportSKUReportController.update
DELETE/reports/:skuReportIDDelete reportSKUReportController.delete
POST/reports/Create reportSKUReportController.create
GET/reports/Find reportsSKUReportController.findByQuery
POST/reports/submit/generateGenerate report fileSKUReportController.postSKUReportSubmission
POST/reports/submit/:skuReportIDSubmit reportSKUReportController.submit
GET/reports/site/:siteIDGet site reportsSKUReportController.skuReportsBySite
POST/reports/get-progressGet report progressSKUReportController.getProgress

System Endpoints

MethodEndpointDescriptionHandler
GET/pingHealth checkReturns { message: 'CONNECTED' }

4. Architecture Overview

Clean Architecture Layers

graph TB
    subgraph "Presentation Layer"
        A[Express Routes<br/>SKURouter.ts<br/>ScheduleRouter.ts<br/>ReportRouter.ts]
        B[Middleware<br/>AuthMiddleware<br/>validatorHandler<br/>parseXlsx]
        C[Validators<br/>sku.validator.ts<br/>schedule.validator.ts<br/>report.validator.ts]
    end
    
    subgraph "Application Layer"
        D[Controllers<br/>SKUController<br/>ScheduleController<br/>ReportController]
        E[Use Cases<br/>SKUUsecase<br/>ScheduleUsecase<br/>ReportUsecase]
    end
    
    subgraph "Domain Layer"
        F[Entities<br/>SKU.schema<br/>Schedule.schema<br/>Report.schema]
        G[Repository Interfaces<br/>ISKURepository<br/>IScheduleRepository<br/>IReportRepository]
        H[Business Rules<br/>validateCheckinTime<br/>hasSameSchedule<br/>mapScheduleByDate]
    end
    
    subgraph "Infrastructure Layer"
        I[MongoDB Repositories<br/>SKUMongoRepository<br/>ScheduleMongoRepository<br/>ReportMongoRepository]
        J[Firebase Repositories<br/>OrganizationSKUFirebaseRepository<br/>SiteSKUFirebaseRepository<br/>InventoryFirebaseRepository]
        K[External API Clients<br/>SiteApiRepository<br/>UOMRepository<br/>PubsubApi]
        L[Email Service<br/>SendGridMail<br/>createPdfReport<br/>createXlsxReport]
    end
    
    A --> D
    B --> D
    C --> D
    D --> E
    E --> F
    E --> G
    G --> I
    G --> J
    G --> K
    E --> L

Database Architecture


graph LR
    subgraph "MongoDB"
        M1[skus]
        M2[skuschedules]
        M3[skureports]
        M4[skusettings]
    end
    
    subgraph "Firebase Realtime DB"
        F1[organizationSKU]
        F2[siteSKU]
        F3[inventory]
    end
    
    subgraph "External Services"
        E1[Sites API]
        E2[UOM API]
        E3[User API]
    end
    
    M1 -.-> F1
    M1 -.-> F2
    M3 --> F3

5. Core Domain Flows

5.1 SKU Management Flow

SKU Creation Flow

sequenceDiagram
    participant Client
    participant Router
    participant AuthMiddleware
    participant Validator
    participant Controller
    participant UseCase
    participant Repository
    participant Firebase
    participant MongoDB
    
    Client->>Router: POST /
    Router->>AuthMiddleware: AuthMiddleware.validate()
    AuthMiddleware->>Validator: validatorHandler.validate()
    Validator->>Controller: SKUController.create()
    Controller->>UseCase: SKUUsecase.create()
    
    UseCase->>UseCase: validateSKUAtDBLevel()
    UseCase->>UseCase: validateSites()
    UseCase->>UseCase: findOrCreateCategories()
    UseCase->>UseCase: validateUOM()
    
    UseCase->>Repository: SKUMongoRepository.create()
    Repository->>MongoDB: mongoose.create()
    MongoDB-->>Repository: Created SKU document
    
    Repository-->>UseCase: Return SKU entity
    UseCase-->>Controller: Return result
    Controller-->>Client: response(data, null)

SKU Search Implementation

The SKU search functionality implements a sophisticated query system:

// Query Building in SKUUsecase.findByQueryPaginate
const query = {
    organizationID,
    disabled: withDisabled ? { $in: [true, false] } : false,
    ...(categories?.length && { categories: { $in: categories } }),
    ...(sites?.length && { sites: { $in: sites } }),
    ...(unitOfMeasurements?.length && { unitOfMeasurement: { $in: unitOfMeasurements } }),
    ...(search && {
        $or: [
            { skuID: { $regex: search, $options: 'i' } },
            { name: { $regex: search, $options: 'i' } },
            { 'sites.name': { $regex: search, $options: 'i' } },
            { categories: { $in: [new RegExp(search, 'i')] } },
        ],
    }),
};

The search supports:

  • Case-insensitive text search across multiple fields
  • Multi-select filters for categories, sites, and UOMs
  • Pagination with customizable page size
  • Sorting by site names
  • Optional inclusion of disabled SKUs

Bulk Upload Process


flowchart TD
    A[Upload Excel File] --> B[parseXlsx middleware]
    B --> C[Extract Rows<br/>req.body.data]
    C --> D{bulkUploadValidator validate}
    D -->|Valid| E[mapSiteNamesToIDs]
    D -->|Invalid| F[collectValidationErrors]
    E --> G[SKURepository findBySkuIDs]
    G --> H[SKUSettingsUsecase findOrCreateCategories]
    H --> I[SKURepository create<br/>Batch Operation]
    I --> J[Return Success Count]
    F --> K[Return Error Details]
    J --> L[response results, null]
    K --> L

5.2 Schedule Management Flow

Schedule Creation with Validation

// Schedule validation in SKUScheduleUsecase.create
1. Validate unique schedule name
2. Validate date ranges based on frequency
3. Validate department/site relationships
4. Validate auditor assignment
5. Validate SKUs exist at site (Firebase check)
6. Set timezone from site configuration

Schedule Frequency Types

graph TD
    A[Schedule Type<br/>schedule.type] --> B[Scheduled<br/>recurring]
    A --> C[Adhoc<br/>one-time]
    
    B --> D[Daily<br/>frequencyType]
    B --> E[Weekly<br/>frequencyType]
    B --> F[Monthly<br/>frequencyType]
    B --> G[Custom<br/>frequencyType]
    
    D --> H[Every Day<br/>frequencyData.daily]
    D --> I[Specific Days<br/>frequencyData.specificDays]
    
    E --> J[Specific Weekdays<br/>frequencyData.weekdays]
    
    F --> K[Specific Dates<br/>frequencyData.dates]
    
    G --> L[Custom Pattern<br/>frequencyData.custom]

Active Schedule Calculation

The system uses complex date mapping to determine active schedules:

// mapScheduleByDate function logic
1. Get current date in site timezone
2. Check if date falls within schedule range
3. Apply frequency rules:
   - Daily: Check if current day matches pattern
   - Weekly: Check if weekday matches
   - Monthly: Check if date matches
   - Custom: Apply custom logic
4. Check total occurrences limit
5. Return schedule if active

5.3 Report Generation Flow

Report Submission Process

sequenceDiagram
    participant App
    participant Controller
    participant UseCase
    participant Validator
    participant Repository
    participant PubSub
    participant EmailService
    
    App->>Controller: POST /reports/submit/:id
    Controller->>UseCase: SKUReportUsecase.submit()
    
    UseCase->>Validator: validateCheckinTime()
    Validator-->>UseCase: Time valid
    
    UseCase->>UseCase: calculateInventoryMovements()
    UseCase->>UseCase: checkNegativeStock()
    UseCase->>UseCase: identifyLowStockItems()
    
    UseCase->>Repository: SKUReportMongoRepository.update()
    UseCase->>Repository: updateSKUCounts()
    
    UseCase->>PubSub: pubsubApi.publish()
    
    PubSub->>UseCase: postSKUReportSubmission()
    UseCase->>UseCase: createPdfReport() / createXlsxReport()
    UseCase->>EmailService: sendgridMail.send()

Time Window Validation

// validateCheckinTime implementation
1. Get schedule configuration
2. Get site timezone
3. Calculate allowed time window:
   - startHour: schedule time - beforeTime
   - endHour: schedule time + afterTime
4. Convert current time to site timezone
5. Check if current time is within window
6. Special handling for midnight crossing

Report Progress Calculation

flowchart LR
    A["Report Type<br/>report.type"] --> B{"isMovement()"}
    B -->|Yes| C["checkMovementAnswered()<br/>stockIn > 0 OR stockOut > 0"]
    B -->|No| D{"isOpname()"}
    D -->|Yes| E["checkOpnameAnswered()<br/>finalCount != initialCount"]
    D -->|No| F["checkQuestionsAnswered()"]
    
    C --> G["getAnsweredSKUProgress()"]
    E --> G
    F --> G
    
    G --> H["calculatePercentage()<br/>(answered/total)*100"]
    H --> I["Return progress%"]

5.4 Firebase Integration Patterns

Organization SKU Structure

// Firebase path: /organizationSKU/{organizationID}/{skuKey}
interface OrganizationSKU {
    skuID: string;
    name: string;
    unitOfMeasurement: string;
    imageURL?: string;
    categories: string[];
    disabled: boolean;
}

Site SKU Structure

// Firebase path: /siteSKU/{organizationID}/{siteID}/{skuKey}
interface SiteSKU {
    skuID: string;
    name: string;
    onHand: number;
    unitOfMeasurement: string;
    minLevel?: number;
    maxLevel?: number;
}

Inventory Tracking

// Firebase path: /inventory/{organizationID}/{siteID}/{dateKey}
interface InventoryQuestion {
    skuID: string;
    timestamp: number;
    count: number;
    stockIn?: number;
    stockOut?: number;
    auditor: string;
    signature?: string;
}

5.5 MongoDB Schema Definitions

SKU Schema

const skuSchema = {
    skuID: { type: String, required: true },
    name: { type: String, required: true },
    organizationID: { type: String, required: true },
    sites: [{ type: String }],
    categories: [{ type: String }],
    unitOfMeasurement: { type: String, required: true },
    imageURL: String,
    disabled: { type: Boolean, default: false },
    createdBy: String,
    updatedBy: String,
    createdAt: Date,
    updatedAt: Date,
};

Schedule Schema

const scheduleSchema = {
    name: { type: String, required: true },
    organizationID: { type: String, required: true },
    departmentID: String,
    sites: [String],
    auditors: [String],
    supervisors: [String],
    skus: [String],
    type: { enum: ['scheduled', 'adhoc', 'movement', 'opname'] },
    frequencyType: { enum: ['daily', 'weekly', 'monthly', 'custom'] },
    frequencyData: Mixed,
    scheduleTime: String,
    timeConfig: {
        beforeTime: Number,
        afterTime: Number,
    },
    startDate: Date,
    endDate: Date,
    totalOccurrences: Number,
    timezone: String,
    disabled: Boolean,
    requireSignature: Boolean,
    emailTargets: [String],
};

Report Schema

const reportSchema = {
    scheduleID: ObjectId,
    organizationID: String,
    siteID: String,
    auditorID: String,
    supervisorID: String,
    skus: [{
        skuID: String,
        name: String,
        initialCount: Number,
        finalCount: Number,
        stockIn: Number,
        stockOut: Number,
        variance: Number,
        note: String,
    }],
    status: { enum: ['draft', 'completed'] },
    checkinAt: Date,
    submitAt: Date,
    signature: String,
    emailSent: Boolean,
    reportURL: String,
};

6. Advanced Implementation Details

6.1 Authentication and Authorization

JWT Token Validation

// AuthMiddleware implementation pattern
1. Extract token from Authorization header
2. Verify token with JWT_SECRET
3. Extract user context:
   - userID
   - organizationID
   - roleID
   - email
4. Attach context to request
5. Pass to next middleware

Department-Based Access Control

// injectDepartmentIndex middleware
1. Get user's department assignments
2. Extract site access permissions
3. Filter data based on department
4. Inject department index into context

6.2 Report Generation Engine

PDF Generation Process

// createPdfReport implementation
1. Set up document definition:
   - Page size and margins
   - Custom fonts (multi-language support)
   - Header with logo
   - Report metadata
 
2. Build content sections:
   - Summary statistics
   - SKU details table
   - Stock movements
   - Signature if required
 
3. Generate PDF buffer
4. Upload to storage
5. Return download URL

Excel Generation Process

// createXlsxReport implementation
1. Create workbook with sheets:
   - Summary sheet
   - Detailed SKU data
   - Stock movements
   - Audit trail
 
2. Apply formatting:
   - Headers and borders
   - Conditional formatting
   - Data validation
 
3. Generate buffer
4. Upload to storage

6.3 Email Notification System

Email Target Resolution

flowchart TD
    A["getAllEmailTargets()"] --> B["Primary:<br/>getSupervisor()"]
    A --> C["schedule.emailTargets[]"]
    A --> D["report.adhocEmailTargets[]"]
    A --> E["getOrganizationAdmins()"]
    
    B --> F["isUnsubscribed()<br/>Check blacklist"]
    C --> F
    D --> F
    E --> F
    
    F --> G["filterActiveEmails()"]
    G --> H["sendgridMail.send()"]

Recap Email System

// Daily recap email logic
1. Query all completed reports for the day
2. Group by supervisor
3. Generate summary statistics:
   - Total reports
   - Sites covered
   - SKUs checked
   - Issues found
4. Create consolidated email
5. Send at configured time

7. Conclusion

The API-SKUs microservice demonstrates a well-architected solution for inventory management, combining the flexibility of MongoDB for complex queries with the real-time capabilities of Firebase. The clean architecture pattern ensures maintainability, while the comprehensive validation and error handling provide reliability. The service successfully handles the complexity of multi-site, multi-timezone operations while maintaining data consistency and providing detailed audit trails.

Key strengths include:

  • Robust validation at multiple levels
  • Flexible scheduling system
  • Comprehensive reporting capabilities
  • Strong security implementation
  • Scalable architecture design

The hybrid database approach, while adding complexity, provides the best of both worlds: MongoDB’s powerful querying for business logic and Firebase’s real-time updates for inventory tracking. This architecture positions the system well for future growth and feature additions while maintaining current operational efficiency.

Graph View