# 2. Data model

76 tables in PostgreSQL 16, defined in `apps/api/src/db/schema/*.ts` (Drizzle). Migrations live in `apps/api/drizzle/` and are applied with `pnpm db:migrate` (owner connection). The application connects as `wms_app`, a role that cannot bypass Row Level Security.

## 2.1 Conventions

- Primary keys are UUIDs (`id`), generated in the database.
- Every business table has `tenant_id` (RLS policy `tenant_isolation`) and most have `created_at` / `updated_at`.
- Quantities, money, rates, volumes and weights are `numeric` (never floats). The API serialises them as strings (`"12.0000"`); the web app formats them.
- Dates (`YYYY-MM-DD`) are `date`; timestamps are `timestamptz`.
- Enumerations are Postgres enums generated from `packages/shared/src/enums.ts` (single source of truth for API, DB and web).
- Document numbers (ASN, GRN, SO, WAVE, TASK, LPN, SH, LOAD, GP, INV, ADJ, CNT, RMA, CRN, CTN, CON, DN) come from `document_sequences` per tenant/warehouse with configurable prefix and padding.
- Soft references across modules (`ref_type` + `ref_id`) are used for documents, attachments, tasks and charges so one table can point at ASNs, orders, shipments or loads.

## 2.2 Entity map

```
tenants -< warehouses -< zones -< locations
tenants -< storers -< skus -< sku_packs / sku_barcodes / sku_slottings
storers -< suppliers, customers, billing_contracts -< tariffs
users -< user_roles >- roles ; api_keys ; sessions (refresh tokens)

asns -< asn_lines -< receipt_lines ; asns -< receiving_discrepancies ; asns - grns
lpns ; inventory_balances (balance_key) ; inventory_movements (append-only) ; serial_units ; inventory_holds
inventory_adjustments -< inventory_adjustment_lines ; cycle_counts -< cycle_count_lines
tasks (type, status, from/to, lpn, sku, qty, ref)
outbound_orders -< outbound_order_lines -< allocations ; waves ; shipments -< cartons -< carton_lines ; loads ; return_orders -< return_order_lines
charge_events ; storage_snapshots ; invoices -< invoice_lines ; credit_notes
webhook_subscriptions -< webhook_deliveries ; integration_connectors ; integration_messages ; import_jobs
ai_proposals ; ai_conversations -< ai_messages
audit_logs ; documents ; attachments ; notifications ; email_outbox ; outbox_events
plans -< subscriptions ; feature_flags ; tenant_settings ; usage_records ; platform_users
```

## 2.3 Status models

| Entity | Statuses |
|--------|----------|
| ASN | DRAFT, EXPECTED, SCHEDULED, ARRIVED, RECEIVING, RECEIVED, CLOSED, CANCELLED |
| ASN / order line | OPEN, PARTIAL, COMPLETE, CANCELLED |
| Order | DRAFT, OPEN, ON_HOLD, PARTIALLY_ALLOCATED, ALLOCATED, RELEASED, PICKING, PICKED, PACKED, STAGED, LOADED, SHIPPED, SHORT_CLOSED, CANCELLED |
| Wave | PLANNED, ALLOCATING, ALLOCATED, RELEASED, IN_PROGRESS, COMPLETED, CANCELLED |
| Shipment | PACKING, PACKED, STAGED, LOADED, SHIPPED, CANCELLED |
| Load | PLANNED, LOADING, LOADED, DISPATCHED, DELIVERED, CANCELLED |
| Task | PENDING, ASSIGNED, IN_PROGRESS, COMPLETED, CANCELLED, SKIPPED, EXCEPTION |
| Inventory status | AVAILABLE, QUARANTINE, DAMAGED, HOLD, EXPIRED, QC_PENDING, IN_TRANSIT, RESERVED |
| LPN | ACTIVE, EMPTY, SHIPPED, CONSUMED |
| Adjustment | DRAFT, PENDING_APPROVAL, APPROVED, REJECTED, POSTED, CANCELLED |
| Count | PLANNED, RELEASED, IN_PROGRESS, REVIEW, COMPLETED, CANCELLED (lines: PENDING, COUNTED, RECOUNT, APPROVED, VARIANCE_POSTED, SKIPPED) |
| Return | REQUESTED, AUTHORISED, RECEIVING, RECEIVED, DISPOSITIONED, CLOSED, REJECTED |
| Charge | PENDING, INVOICED, WAIVED, DISPUTED |
| Invoice | DRAFT, ISSUED, SENT, PAID, PARTIALLY_PAID, CANCELLED, DISPUTED |
| AI proposal | PROPOSED, ACCEPTED, REJECTED, APPLIED, EXPIRED |
| Integration message | RECEIVED, PROCESSING, PROCESSED, FAILED, DEAD |
| Tenant | TRIAL, ACTIVE, SUSPENDED, CLOSED |

## 2.4 Integrity rules enforced in the database

- `inventory_balances`: `qty_on_hand >= 0`, `qty_allocated >= 0`, `qty_allocated <= qty_on_hand`, unique `balance_key`.
- Unique per tenant: SKU code per storer, location code per warehouse, LPN number, document numbers, external references on open documents, idempotency keys (receipts, charges, API documents), barcode per tenant.
- `tenant_ref_guard` trigger (migration 0002): any insert/update whose foreign key points to a row of another tenant is rejected with SQLSTATE 23503 (surfaced as `CROSS_TENANT_REFERENCE`).
- RLS policy on every table with `tenant_id` (`USING tenant_id = app_current_tenant() OR app_bypass_rls()`), forced for the table owner too.

## 2.5 Table inventory

| Table | Columns | First columns | Purpose |
|-------|---------|---------------|---------|
| `ai_proposals` | 24 | id, tenantId, warehouseId, storerId, type, status, title, summary, payload, evidence, confidence, severity ... |  |
| `ai_conversations` | 7 | id, tenantId, userId, warehouseId, title, createdAt, updatedAt |  |
| `ai_messages` | 9 | id, tenantId, conversationId, role, content, toolCalls, promptTokens, completionTokens, createdAt |  |
| `billing_contracts` | 21 | id, tenantId, storerId, contractNumber, name, currency, status, billingCycle, storageMethod, storageCap, minimumMonthly, fixedMonthlyFee ... |  |
| `tariffs` | 19 | id, tenantId, contractId, code, group, description, chargeUnit, rate, minimumCharge, tiers, trigger, conditions ... |  |
| `charge_events` | 26 | id, tenantId, warehouseId, storerId, contractId, tariffId, refType, refId, refNumber, description, quantity, unit ... |  |
| `storage_snapshots` | 13 | id, tenantId, warehouseId, storerId, snapshotDate, totalQty, totalCbm, totalWeightKg, palletPositions, inboundCbm, outboundCbm, byTemperatureClass ... |  |
| `invoices` | 22 | id, tenantId, storerId, contractId, invoiceNumber, periodStart, periodEnd, status, currency, subtotal, taxTotal, total ... |  |
| `invoice_lines` | 15 | id, tenantId, invoiceId, tariffId, group, description, quantity, unit, rate, amount, taxRatePct, taxAmount ... |  |
| `credit_notes` | 9 | id, tenantId, invoiceId, creditNumber, amount, reason, status, issuedAt, createdById |  |
| `users` | 15 | id, tenantId, email, passwordHash, name, phone, status, isTenantAdmin, storerId, locale, lastLoginAt, failedLoginAttempts ... |  |
| `roles` | 9 | id, tenantId, code, name, description, permissions, isSystem, createdAt, updatedAt |  |
| `user_roles` | 4 | id, userId, roleId, warehouseId |  |
| `user_warehouses` | 3 | userId, warehouseId, isDefault |  |
| `sessions` | 9 | id, userId, refreshTokenHash, userAgent, ip, deviceId, expiresAt, revokedAt, createdAt |  |
| `api_keys` | 13 | id, tenantId, storerId, name, prefix, keyHash, scopes, rateLimitPerMinute, lastUsedAt, expiresAt, revokedAt, createdById ... |  |
| `asns` | 38 | id, tenantId, warehouseId, storerId, asnNumber, externalRef, type, status, source, supplierId, priority, expectedAt ... |  |
| `asn_lines` | 18 | id, tenantId, asnId, lineNo, skuId, expectedQty, packLevel, packQty, expectedLot, expectedExpiry, receivedQty, damagedQty ... |  |
| `dock_appointments` | 17 | id, tenantId, warehouseId, dockLocationId, direction, refType, refId, startsAt, endsAt, status, carrierName, vehicleNo ... |  |
| `receipt_lines` | 24 | id, tenantId, asnId, asnLineId, skuId, lpnId, locationId, qty, packLevel, packQty, lotNumber, expiryDate ... |  |
| `receiving_discrepancies` | 16 | id, tenantId, asnId, asnLineId, skuId, type, expectedQty, actualQty, varianceQty, status, resolution, reportedAt ... |  |
| `grns` | 10 | id, tenantId, asnId, grnNumber, postedAt, postedById, summary, documentId, rcrDocumentId, rcrSentAt |  |
| `integration_connectors` | 9 | id, tenantId, storerId, type, name, config, active, createdAt, updatedAt |  |
| `integration_messages` | 18 | id, tenantId, storerId, connectorId, direction, messageType, externalId, idempotencyKey, payload, rawPayload, status, attempts ... |  |
| `webhook_subscriptions` | 10 | id, tenantId, storerId, name, url, secretEncrypted, events, active, createdAt, updatedAt |  |
| `webhook_deliveries` | 13 | id, tenantId, subscriptionId, eventType, eventId, payload, status, attempts, responseCode, responseBody, nextAttemptAt, deliveredAt ... |  |
| `import_jobs` | 16 | id, tenantId, warehouseId, storerId, type, fileName, status, dryRun, totalRows, successRows, errorRows, errors ... |  |
| `lpns` | 16 | id, tenantId, warehouseId, lpnNumber, type, status, locationId, parentLpnId, storerId, weightKg, heightCm, sealed ... |  |
| `inventory_balances` | 21 | id, tenantId, warehouseId, storerId, skuId, locationId, lpnId, lotNumber, expiryDate, mfgDate, status, balanceKey ... |  |
| `inventory_movements` | 27 | id, tenantId, warehouseId, storerId, skuId, type, qty, fromLocationId, toLocationId, fromLpnId, toLpnId, lotNumber ... |  |
| `serial_units` | 17 | id, tenantId, storerId, skuId, serialNumber, status, warehouseId, locationId, lpnId, lotNumber, expiryDate, receivedAsnId ... |  |
| `inventory_holds` | 15 | id, tenantId, warehouseId, scope, targetId, storerId, skuId, reasonCodeId, toStatus, affectedBalanceIds, notes, placedById ... |  |
| `inventory_adjustments` | 17 | id, tenantId, warehouseId, storerId, adjNumber, status, reasonCodeId, source, countId, notes, requestedById, approvedById ... |  |
| `inventory_adjustment_lines` | 14 | id, tenantId, adjustmentId, skuId, locationId, lpnId, lotNumber, expiryDate, fromStatus, toStatus, qtyDelta, serials ... |  |
| `cycle_counts` | 17 | id, tenantId, warehouseId, countNumber, type, status, storerId, scope, blind, recountTolerancePct, scheduledFor, releasedAt ... |  |
| `cycle_count_lines` | 23 | id, tenantId, countId, locationId, skuId, storerId, lpnId, lotNumber, expiryDate, inventoryStatus, systemQty, countedQty ... |  |
| `warehouses` | 10 | id, tenantId, code, name, address, timezone, status, settings, createdAt, updatedAt |  |
| `zones` | 11 | id, tenantId, warehouseId, code, name, type, temperatureClass, pickSequence, status, createdAt, updatedAt |  |
| `locations` | 32 | id, tenantId, warehouseId, zoneId, code, barcode, checkDigit, aisle, bay, level, position, type ... |  |
| `storers` | 12 | id, tenantId, code, name, legalName, taxId, address, contacts, status, settings, createdAt, updatedAt |  |
| `storer_warehouses` | 2 | storerId, warehouseId |  |
| `suppliers` | 10 | id, tenantId, storerId, code, name, address, contacts, status, createdAt, updatedAt |  |
| `customers` | 10 | id, tenantId, storerId, code, name, address, contacts, status, createdAt, updatedAt |  |
| `carriers` | 9 | id, tenantId, code, name, serviceLevels, contacts, status, createdAt, updatedAt |  |
| `skus` | 36 | id, tenantId, storerId, code, description, longDescription, category, brand, groupCode, materialType, storageCondition, rotationRule ... |  |
| `sku_packs` | 15 | id, tenantId, skuId, level, uomCode, qtyInBase, lengthCm, widthCm, heightCm, weightKg, barcode, palletTi ... |  |
| `sku_barcodes` | 6 | id, tenantId, skuId, barcode, packLevel, isPrimary |  |
| `sku_slottings` | 11 | id, tenantId, skuId, warehouseId, pickLocationId, minQty, maxQty, replenishPackLevel, active, createdAt, updatedAt |  |
| `putaway_rules` | 14 | id, tenantId, warehouseId, name, priority, criteria, strategy, targetZoneIds, allowMixedSku, allowMixedLots, respectCapacity, active ... |  |
| `allocation_rules` | 16 | id, tenantId, storerId, skuId, name, priority, rotation, pickFaceFirst, fullLpnFirst, allowPartialLpn, minShelfLifeDays, allowedStatuses ... |  |
| `reason_codes` | 8 | id, tenantId, type, code, description, requiresApproval, requiresPhoto, active |  |
| `document_sequences` | 7 | id, tenantId, warehouseId, docType, prefix, nextNumber, padding |  |
| `waves` | 17 | id, tenantId, warehouseId, waveNumber, status, strategy, criteria, orderCount, lineCount, taskCount, shortLines, createdById ... |  |
| `loads` | 27 | id, tenantId, warehouseId, loadNumber, status, carrierId, carrierName, vehicleNo, vehicleType, driverName, driverIdNo, driverPhone ... |  |
| `outbound_orders` | 35 | id, tenantId, warehouseId, storerId, orderNumber, externalRef, type, status, source, priority, customerId, shipTo ... |  |
| `outbound_order_lines` | 21 | id, tenantId, orderId, lineNo, skuId, orderedQty, packLevel, packQty, allocatedQty, pickedQty, packedQty, shippedQty ... |  |
| `allocations` | 17 | id, tenantId, orderLineId, orderId, balanceId, skuId, locationId, lpnId, lotNumber, expiryDate, qty, pickedQty ... |  |
| `shipments` | 24 | id, tenantId, warehouseId, orderId, shipmentNumber, status, packingLocationId, stagingLocationId, loadId, carrierId, trackingNo, cartonCount ... |  |
| `cartons` | 13 | id, tenantId, shipmentId, cartonNumber, type, lpnId, weightKg, lengthCm, widthCm, heightCm, trackingNo, sealed ... |  |
| `carton_lines` | 9 | id, tenantId, cartonId, orderLineId, skuId, qty, lotNumber, expiryDate, serials |  |
| `return_orders` | 14 | id, tenantId, warehouseId, storerId, rmaNumber, originalOrderId, status, reasonCodeId, asnId, customerRef, notes, createdById ... |  |
| `return_order_lines` | 11 | id, tenantId, returnId, lineNo, skuId, expectedQty, receivedQty, disposition, reasonCodeId, serials, notes |  |
| `tenants` | 11 | id, tenantId, planId, status, startsAt, endsAt, trialEndsAt, seats, notes, createdAt, updatedAt | A tenant is a subscribing company (e.g. WeStore). All business data hangs off a tenant. |
| `plans` | 11 | id, tenantId, planId, status, startsAt, endsAt, trialEndsAt, seats, notes, createdAt, updatedAt |  |
| `subscriptions` | 11 | id, tenantId, planId, status, startsAt, endsAt, trialEndsAt, seats, notes, createdAt, updatedAt |  |
| `feature_flags` | 5 | id, tenantId, key, enabled, config |  |
| `usage_records` | 7 | id, tenantId, metric, usageDate, quantity, createdAt, updatedAt |  |
| `platform_users` | 4 | id, tenantId, key, value | SaaS operator staff. Deliberately separate from tenant users. |
| `tenant_settings` | 4 | id, tenantId, key, value |  |
| `audit_logs` | 14 | id, tenantId, actorType, actorId, actorName, action, entityType, entityId, before, after, ip, userAgent ... |  |
| `documents` | 13 | id, tenantId, type, number, refType, refId, mimeType, storageKey, sizeBytes, generatedAt, generatedById, sentAt ... |  |
| `attachments` | 11 | id, tenantId, refType, refId, fileName, mimeType, sizeBytes, storageKey, kind, uploadedById, createdAt |  |
| `notifications` | 9 | id, tenantId, userId, type, title, body, data, readAt, createdAt |  |
| `email_outbox` | 14 | id, tenantId, to, cc, subject, bodyHtml, attachments, refType, refId, status, attempts, lastError ... |  |
| `outbox_events` | 13 | id, tenantId, type, warehouseId, storerId, actorId, payload, status, attempts, lastError, claimedAt, processedAt ... |  |
| `tasks` | 34 | id, tenantId, warehouseId, taskNumber, type, status, priority, sequence, groupKey, zoneId, storerId, skuId ... |  |

## 2.6 Rebuilding balances from the ledger

Balances are derived data. If they are ever suspected to drift (they should not; both are written in one transaction), they can be recomputed from `inventory_movements` by replaying movements in `performed_at, id` order per balance key. A maintenance script for this is listed in `docs/10-roadmap-and-gaps.md`.