Commercio

A modular ERP (Enterprise Resource Planning) system for Node.js with PostgreSQL and Drizzle ORM.

Features

• Category management for product organization
• Product management with SKU support (requires category)
• Product variants with attribute support (Size, Color, Material, etc.)
• Variant attribute management
• Customer management with address, contact details, credit limits, and payment terms
• Customer groups with discount percentages
• Order history and statistics tracking
• Warehouse management (multi-warehouse)
• Inventory management with transaction history
• Order management with status workflow
• Stock reservation system
• TypeScript-first with full type safety
• Structured logging with Pino

Installation

npm install commercio

Requirements

• Node.js 18+ or Bun
• PostgreSQL 14+
• TypeScript 5+ (recommended)

Quick Start

1. Set up Database

Create a PostgreSQL database:

CREATE DATABASE my_erp_db;

2. Configure Database Connection

Option A: Environment Variable (Recommended)

Create a .env file:

DATABASE_URL=postgresql://user:password@localhost:5432/my_erp_db

The package automatically reads the DATABASE_URL environment variable.

Option B: Programmatic Initialization

import { initDatabase } from "commercio";

initDatabase({
  connectionString: "postgresql://user:password@localhost:5432/my_erp_db",
});

// Or with individual parameters
initDatabase({
  host: "localhost",
  port: 5432,
  database: "my_erp_db",
  user: "postgres",
  password: "password",
});

3. Run Migrations

The package includes pre-built migrations that can be run automatically. You have two options:

Option A: Automatic Migration (Recommended)

Run migrations automatically when initializing the database:

import { initDatabase } from "commercio";

initDatabase({
  connectionString: process.env.DATABASE_URL,
  runMigrations: true, // Automatically run migrations
});

Option B: Manual Migration

Run migrations manually:

import { runMigrations } from "commercio";

// Run migrations with a connection string
await runMigrations(process.env.DATABASE_URL);

Or if you've already initialized the database:

import { initDatabase, runMigrationsWithDb, db } from "commercio";

initDatabase({
  connectionString: process.env.DATABASE_URL,
});

// Run migrations using the initialized connection
await runMigrationsWithDb(db);

That's it! No need to configure Drizzle Kit or generate migration files manually. The migrations are included in the package and run automatically.

Usage

Basic Setup

Option A: Using Factory Functions (Recommended)

The easiest way to get started - no need to manually inject repositories:

import { initDatabase, createServices } from "commercio";

// Initialize database connection
initDatabase({
  connectionString: process.env.DATABASE_URL,
  runMigrations: true,
});

// Create all services at once with default repositories
const {
  categoryService,
  productService,
  productVariantService,
  variantAttributeService,
  warehouseService,
  stockService,
  orderService,
  reservationService,
  inventoryTransactionService,
} = createServices();

Option B: Individual Factory Functions

Create services individually if you only need specific ones:

import {
  initDatabase,
  createCategoryService,
  createProductService,
  createProductVariantService,
  createVariantAttributeService,
  createWarehouseService,
  createStockService,
  createOrderService,
} from "commercio";

// Initialize database connection
initDatabase({
  connectionString: process.env.DATABASE_URL,
  runMigrations: true,
});

// Create only the services you need
const categoryService = createCategoryService();
const productService = createProductService();
const productVariantService = createProductVariantService();
const variantAttributeService = createVariantAttributeService();
const warehouseService = createWarehouseService();
const stockService = createStockService();
const orderService = createOrderService();

Option C: Manual Dependency Injection (Advanced)

For advanced use cases or testing, you can still manually inject repositories:

import {
  initDatabase,
  CategoryService,
  ProductService,
  CategoryRepository,
  ProductRepository,
} from "commercio";

// Initialize database connection
initDatabase({
  connectionString: process.env.DATABASE_URL,
});

// Create repositories manually
const categoryRepo = new CategoryRepository();
const productRepo = new ProductRepository();

// Create services with custom repositories
const categoryService = new CategoryService(categoryRepo);
const productService = new ProductService(productRepo);

Category Management

// Create category
const category = await categoryService.createCategory(
  "Electronics",
  "Electronic devices and accessories"
);

// Get category
const foundCategory = await categoryService.getCategoryById(category.id);
const categoryByName = await categoryService.getCategoryByName("Electronics");

// Get all categories
const allCategories = await categoryService.getAllCategories();
const activeCategories = await categoryService.getAllCategories(true);

// Update category
await categoryService.updateCategory(category.id, {
  name: "Consumer Electronics",
  description: "Updated description",
});

// Deactivate category
await categoryService.deactivateCategory(category.id);

// Activate category
await categoryService.activateCategory(category.id);

Product Management

Note: Products must be assigned to a category. Create a category first.

// First, create a category
const category = await categoryService.createCategory("Electronics");

// Create product (categoryId is required)
const product = await productService.createProduct(
  "Laptop Dell XPS 15",
  "SKU-LAPTOP-001",
  category.id
);

// Get product
const foundProduct = await productService.getProductById(product.id);
const productBySku = await productService.getProductBySku("SKU-LAPTOP-001");

// Get products by category
const electronicsProducts = await productService.getProductsByCategory(
  category.id
);

// Update product
await productService.updateProduct(product.id, {
  name: "Laptop Dell XPS 15 (2024)",
  categoryId: category.id, // Can change category
});

// Deactivate product
await productService.deactivateProduct(product.id);

Variant Attribute Management

Variant attributes define the types of variations available (e.g., Size, Color, Material).

// Create variant attributes
const sizeAttribute = await variantAttributeService.createVariantAttribute(
  "Size"
);
const colorAttribute = await variantAttributeService.createVariantAttribute(
  "Color"
);

// Get attribute
const foundAttribute = await variantAttributeService.getVariantAttributeById(
  sizeAttribute.id
);
const attributeByName = await variantAttributeService.getVariantAttributeByName(
  "Size"
);

// Get all attributes
const allAttributes = await variantAttributeService.getAllVariantAttributes();
const activeAttributes = await variantAttributeService.getAllVariantAttributes(
  true
);

// Update attribute
await variantAttributeService.updateVariantAttribute(sizeAttribute.id, {
  name: "Product Size",
});

// Deactivate attribute
await variantAttributeService.deactivateVariantAttribute(sizeAttribute.id);

// Activate attribute
await variantAttributeService.activateVariantAttribute(sizeAttribute.id);

Product Variant Management

Product variants allow you to create different variations of a product (e.g., different sizes, colors).

Note: Variants require an existing product. Each variant must have a unique SKU globally and unique attribute values per product.

// First, create a product
const category = await categoryService.createCategory("Clothing");
const product = await productService.createProduct(
  "Premium T-Shirt",
  "SKU-TSHIRT-BASE",
  category.id
);

// Create variant attributes (optional but recommended)
await variantAttributeService.createVariantAttribute("Size");
await variantAttributeService.createVariantAttribute("Color");

// Create product variant
const variant = await productVariantService.createProductVariant(
  product.id,
  "SKU-TSHIRT-L-RED",
  {
    Size: "L",
    Color: "Red",
  }
);

// Get variant
const foundVariant = await productVariantService.getProductVariantById(
  variant.id
);
const variantBySku = await productVariantService.getProductVariantBySku(
  "SKU-TSHIRT-L-RED"
);

// Find variant by attributes
const redLarge = await productVariantService.findVariantByAttributes(
  product.id,
  { Size: "L", Color: "Red" }
);

// Get all variants for a product
const allVariants = await productVariantService.getVariantsByProduct(
  product.id
);

// Update variant
await productVariantService.updateProductVariant(variant.id, {
  sku: "SKU-TSHIRT-L-RED-NEW",
  attributeValues: { Size: "XL", Color: "Red" },
});

// Deactivate variant
await productVariantService.deactivateProductVariant(variant.id);

// Activate variant
await productVariantService.activateProductVariant(variant.id);

// Delete variant permanently
await productVariantService.deleteProductVariant(variant.id);

Customer Management

// Create customer
const customer = await customerService.createCustomer(
  "John Doe",
  {
    street: "123 Main St",
    city: "Berlin",
    postalCode: "10115",
    country: "Germany",
    state: "Berlin",
  },
  {
    email: "[email protected]",
    phone: "+49 30 12345678",
  },
  {
    creditLimit: 100000, // €1000.00 in cents
    paymentTerms: PaymentTerms.NET_30,
  }
);

// Get customer
const foundCustomer = await customerService.getCustomerById(customer.id);
const customerByEmail = await customerService.getCustomerByEmail(
  "[email protected]"
);

// Get all customers
const allCustomers = await customerService.getAllCustomers();
const activeCustomers = await customerService.getAllCustomers(true);

// Update customer
await customerService.updateCustomer(customer.id, {
  name: "John Smith",
  address: {
    city: "Munich",
    postalCode: "80331",
  },
  creditLimit: 200000,
});

// Get customer order history
const orderHistory = await customerService.getOrderHistory(customer.id);

// Get order history filtered by status
const completedOrders = await customerService.getOrderHistoryByStatus(
  customer.id,
  OrderStatus.COMPLETED
);

// Get customer order statistics
const stats = await customerService.getCustomerOrderStatistics(customer.id);
console.log(`Total orders: ${stats.totalOrders}`);
console.log(`Total spent: €${(stats.totalSpent / 100).toFixed(2)}`);
console.log(
  `Average order value: €${(stats.averageOrderValue / 100).toFixed(2)}`
);

// Deactivate customer
await customerService.deactivateCustomer(customer.id);

// Activate customer
await customerService.activateCustomer(customer.id);

Customer Groups

// Create customer group
const vipGroup = await customerService.createCustomerGroup(
  "VIP Customers",
  "Premium customer tier",
  10 // 10% discount
);

// Get customer group
const foundGroup = await customerService.getCustomerGroupById(vipGroup.id);
const groupByName = await customerService.getCustomerGroupByName(
  "VIP Customers"
);

// Get all customer groups
const allGroups = await customerService.getAllCustomerGroups();
const activeGroups = await customerService.getAllCustomerGroups(true);

// Get customers in a group
const vipCustomers = await customerService.getCustomersByGroup(vipGroup.id);

// Update customer group
await customerService.updateCustomerGroup(vipGroup.id, {
  name: "Premium VIP",
  discountPercentage: 15,
});

// Assign customer to group
await customerService.updateCustomer(customer.id, {
  customerGroupId: vipGroup.id,
});

// Deactivate customer group
await customerService.deactivateCustomerGroup(vipGroup.id);

Warehouse Management

// Create warehouse
const warehouse = await warehouseService.createWarehouse(
  "Main Warehouse Berlin"
);

// Get warehouse
const foundWarehouse = await warehouseService.getWarehouseById(warehouse.id);

// Update warehouse
await warehouseService.updateWarehouse(warehouse.id, {
  name: "Main Warehouse Berlin - Location 2",
});

// Deactivate warehouse
await warehouseService.deactivateWarehouse(warehouse.id);

Inventory Management

// Set stock
await stockService.setStock(product.id, warehouse.id, 100);

// Get stock
const stock = await stockService.getStock(product.id, warehouse.id);
console.log(`Current stock: ${stock?.quantity}`);

// Increase stock
await stockService.increaseStock(product.id, warehouse.id, 50);

// Decrease stock
await stockService.decreaseStock(product.id, warehouse.id, 25);

// Adjust stock (positive or negative)
await stockService.adjustStock(product.id, warehouse.id, -10);

// Get total stock across all warehouses
const totalStock = await stockService.getTotalStock(product.id);
console.log(`Total stock: ${totalStock}`);

// Get all stock entries for a product
const allStock = await stockService.getStockByProduct(product.id);

// Get all stock entries for a warehouse
const warehouseStock = await stockService.getStockByWarehouse(warehouse.id);

Order Management

Note: Orders require a customer. Create a customer first using customerService.createCustomer().

// First, create a customer
const customer = await customerService.createCustomer(
  "John Doe",
  {
    street: "123 Main St",
    city: "Berlin",
    postalCode: "10115",
    country: "Germany",
  },
  { email: "[email protected]" }
);

// Create order (customerId is required)
const order = await orderService.createOrder(customer.id, [
  {
    productId: product.id,
    quantity: 5,
    unitPrice: 1999, // €19.99 in cents
  },
]);

console.log(`Order created: ${order.id}`);
console.log(`Total amount: €${(order.totalAmount / 100).toFixed(2)}`);

// Confirm order (creates reservations)
const confirmedOrder = await orderService.confirmOrder(order.id, warehouse.id);

// Mark as paid
const paidOrder = await orderService.markOrderAsPaid(order.id);

// Ship order (consumes reservations, creates transactions)
const shippedOrder = await orderService.shipOrder(order.id, warehouse.id);

// Complete order
const completedOrder = await orderService.completeOrder(order.id);

// Cancel order (releases reservations)
const cancelledOrder = await orderService.cancelOrder(order.id);

// Return items
await orderService.returnOrderItems(
  order.id,
  [{ productId: product.id, quantity: 2 }],
  warehouse.id
);

Inventory Transactions

import { InventoryTransactionType } from "commercio";

// Receipt
await transactionService.createTransaction(
  product.id,
  warehouse.id,
  100,
  InventoryTransactionType.RECEIPT,
  "supplier-order-123"
);

// Shipment
await transactionService.createTransaction(
  product.id,
  warehouse.id,
  50,
  InventoryTransactionType.SHIPMENT,
  "order-456"
);

// Return
await transactionService.createTransaction(
  product.id,
  warehouse.id,
  10,
  InventoryTransactionType.RETURN,
  "order-456"
);

// Adjustment
await transactionService.createTransaction(
  product.id,
  warehouse.id,
  -5,
  InventoryTransactionType.ADJUSTMENT,
  "inventory-audit-789"
);

// Get transaction
const transaction = await transactionService.getTransactionById(transactionId);

// Get all transactions for a product
const transactions = await transactionService.getTransactionsByProduct(
  product.id
);

Reservations

// Create reservation
const reservation = await reservationService.createReservation(
  product.id,
  warehouse.id,
  10,
  order.id
);

// Consume reservation
await reservationService.consumeReservation(reservation.id);

// Release reservation
await reservationService.releaseReservation(reservation.id);

// Get all reservations for an order
const reservations = await reservationService.getReservationsByReference(
  order.id
);

// Get all reservations for a product
const productReservations = await reservationService.getReservationsByProduct(
  product.id
);

API Reference

CategoryService

• createCategory(name: string, description?: string): Promise<Category>
• getCategoryById(id: CategoryId): Promise<Category>
• getCategoryByName(name: string): Promise<Category>
• getAllCategories(activeOnly?: boolean): Promise<Category[]>
• updateCategory(id: CategoryId, updates: Partial<{ name: string; description: string | null }>): Promise<Category>
• deactivateCategory(id: CategoryId): Promise<Category>
• activateCategory(id: CategoryId): Promise<Category>

ProductService

• createProduct(name: string, sku: string, categoryId: CategoryId): Promise<Product>
• getProductById(id: ProductId): Promise<Product>
• getProductBySku(sku: string): Promise<Product>
• getProductsByCategory(categoryId: CategoryId): Promise<Product[]>
• updateProduct(id: ProductId, updates: Partial<{ name: string; sku: string; categoryId: CategoryId; isSellable: boolean; isActive: boolean }>): Promise<Product>
• deactivateProduct(id: ProductId): Promise<Product>
• activateProduct(id: ProductId): Promise<Product>

VariantAttributeService

• createVariantAttribute(name: string): Promise<VariantAttribute>
• getVariantAttributeById(id: VariantAttributeId): Promise<VariantAttribute>
• getVariantAttributeByName(name: string): Promise<VariantAttribute>
• getAllVariantAttributes(activeOnly?: boolean): Promise<VariantAttribute[]>
• updateVariantAttribute(id: VariantAttributeId, updates: Partial<{ name: string }>): Promise<VariantAttribute>
• deactivateVariantAttribute(id: VariantAttributeId): Promise<VariantAttribute>
• activateVariantAttribute(id: VariantAttributeId): Promise<VariantAttribute>

ProductVariantService

• createProductVariant(productId: ProductId, sku: string, attributeValues: Record<string, string>): Promise<ProductVariant>
• getProductVariantById(id: ProductVariantId): Promise<ProductVariant>
• getProductVariantBySku(sku: string): Promise<ProductVariant>
• getVariantsByProduct(productId: ProductId): Promise<ProductVariant[]>
• findVariantByAttributes(productId: ProductId, attributeValues: Record<string, string>): Promise<ProductVariant | null>
• updateProductVariant(id: ProductVariantId, updates: Partial<{ sku: string; attributeValues: Record<string, string>; isActive: boolean }>): Promise<ProductVariant>
• deactivateProductVariant(id: ProductVariantId): Promise<ProductVariant>
• activateProductVariant(id: ProductVariantId): Promise<ProductVariant>
• deleteProductVariant(id: ProductVariantId): Promise<void>

CustomerService

• createCustomer(name: string, address: {...}, contact: {...}, options?: {...}): Promise<Customer>
• getCustomerById(id: CustomerId): Promise<Customer>
• getCustomerByEmail(email: string): Promise<Customer>
• getAllCustomers(activeOnly?: boolean): Promise<Customer[]>
• getCustomersByGroup(customerGroupId: CustomerGroupId): Promise<Customer[]>
• updateCustomer(id: CustomerId, updates: Partial<{...}>): Promise<Customer>
• deactivateCustomer(id: CustomerId): Promise<Customer>
• activateCustomer(id: CustomerId): Promise<Customer>
• getOrderHistory(customerId: CustomerId): Promise<Order[]>
• getOrderHistoryByStatus(customerId: CustomerId, status?: string): Promise<Order[]>
• getCustomerOrderStatistics(customerId: CustomerId): Promise<{totalOrders, totalSpent, averageOrderValue, ordersByStatus}>
• createCustomerGroup(name: string, description?: string, discountPercentage?: number): Promise<CustomerGroup>
• getCustomerGroupById(id: CustomerGroupId): Promise<CustomerGroup>
• getCustomerGroupByName(name: string): Promise<CustomerGroup>
• getAllCustomerGroups(activeOnly?: boolean): Promise<CustomerGroup[]>
• updateCustomerGroup(id: CustomerGroupId, updates: Partial<{...}>): Promise<CustomerGroup>
• deactivateCustomerGroup(id: CustomerGroupId): Promise<CustomerGroup>
• activateCustomerGroup(id: CustomerGroupId): Promise<CustomerGroup>

WarehouseService

• createWarehouse(name: string): Promise<Warehouse>
• getWarehouseById(id: WarehouseId): Promise<Warehouse | null>
• updateWarehouse(id: WarehouseId, updates: Partial<Warehouse>): Promise<Warehouse>
• deactivateWarehouse(id: WarehouseId): Promise<Warehouse>

StockService

• setStock(productId: ProductId, warehouseId: WarehouseId, quantity: number): Promise<Stock>
• getStock(productId: ProductId, warehouseId: WarehouseId): Promise<Stock | null>
• adjustStock(productId: ProductId, warehouseId: WarehouseId, adjustment: number): Promise<Stock>
• increaseStock(productId: ProductId, warehouseId: WarehouseId, quantity: number): Promise<Stock>
• decreaseStock(productId: ProductId, warehouseId: WarehouseId, quantity: number): Promise<Stock>
• getTotalStock(productId: ProductId): Promise<number>
• getStockByProduct(productId: ProductId): Promise<Stock[]>
• getStockByWarehouse(warehouseId: WarehouseId): Promise<Stock[]>

OrderService

• createOrder(customerId: string, items: OrderItemInput[]): Promise<Order>
• getOrderById(id: OrderId): Promise<Order>
• confirmOrder(id: OrderId, warehouseId: WarehouseId): Promise<Order>
• markOrderAsPaid(id: OrderId): Promise<Order>
• shipOrder(id: OrderId, warehouseId: WarehouseId): Promise<Order>
• completeOrder(id: OrderId): Promise<Order>
• cancelOrder(id: OrderId): Promise<Order>
• returnOrderItems(id: OrderId, items: ReturnItemInput[], warehouseId: WarehouseId): Promise<Order>

InventoryTransactionService

• createTransaction(productId: ProductId, warehouseId: WarehouseId, quantity: number, type: InventoryTransactionType, referenceId?: string): Promise<InventoryTransaction>
• getTransactionById(id: InventoryTransactionId): Promise<InventoryTransaction>
• getTransactionsByProduct(productId: ProductId): Promise<InventoryTransaction[]>

ReservationService

• createReservation(productId: ProductId, warehouseId: WarehouseId, quantity: number, referenceId: string): Promise<Reservation>
• consumeReservation(id: ReservationId): Promise<Reservation>
• releaseReservation(id: ReservationId): Promise<Reservation>
• getReservationsByReference(referenceId: string): Promise<Reservation[]>
• getReservationsByProduct(productId: ProductId): Promise<Reservation[]>

Database Schema

The package uses the following tables:

• categories - Product categories
• products - Products (requires category_id)
• variant_attributes - Variant attribute types (Size, Color, etc.)
• product_variants - Product variants with attribute values
• customer_groups - Customer groups with discount percentages
• customers - Customers with address, contact, credit limit, and payment terms
• warehouses - Warehouses
• stock - Stock levels (Product × Warehouse)
• inventory_transactions - Inventory transactions
• reservations - Stock reservations
• orders - Orders (references customers)
• order_items - Order items

Relationships:

• Products must belong to a category (products.category_id → categories.id)
• Product variants belong to products (product_variants.product_id → products.id)
• Variants store attribute values as JSON (e.g., { "Size": "L", "Color": "Red" })
• Customers can belong to a customer group (customers.customer_group_id → customer_groups.id)
• Orders belong to customers (orders.customer_id → customers.id)
• Stock entries reference products and warehouses
• Orders contain order items that reference products
• Reservations reference products and warehouses
• Inventory transactions reference products and warehouses

Logging

The package uses Pino for structured logging:

import { logger } from "commercio";

// Simple logging
logger.info("Operation started");

// Structured logging
logger.info({ orderId: "123", status: "created" }, "Order created");

// Error logging
logger.error({ error }, "Operation failed");

// Child logger with context
const orderLogger = logger.child({ orderId: "123" });
orderLogger.info("Processing order");

Log level can be configured via the LOG_LEVEL environment variable:

LOG_LEVEL=debug

Available levels: trace, debug, info, warn, error, fatal

Development

Running Tests

npm run test          # All tests
npm run test:unit     # Unit tests
npm run test:e2e      # E2E tests
npm run test:coverage # With coverage

Database Migrations

npm run db:generate  # Generate migrations
npm run db:migrate   # Run migrations
npm run db:studio    # Open Drizzle Studio

Build

npm run build

License

MIT