Database Schema

PostgreSQL 16 (Supabase) จัดการผ่าน Prisma 6 — schema เดียวที่ apps/api/prisma/schema.prisma ครอบคลุม 35+ models

ERD Summary (Key Relationships)

Tenant ─┬─ Warehouse ─┬─ Location ─┬─ Stock ─── StockReservation
        │             │            │
        │             │            └─ LicensePlate ─── LpnMovement
        │             │
        │             ├─ Asn ─── AsnLine ─── GrnLine
        │             │                ↑
        │             ├─ Grn ──────────┘ ─── PutawayTask
        │             │
        │             ├─ SalesOrder ─── SalesOrderLine ─── PickTask
        │             │       ↑                              ↑
        │             │       └─── Rma ─── RmaLine          Wave
        │             │
        │             ├─ Shipment ─── ShipmentPackage
        │             ├─ CycleCount ─── CycleCountLine
        │             ├─ ApprovalRequest
        │             ├─ ReplenishmentRule / ReplenishmentTask
        │             └─ PutawayRule
        │
        ├─ Uom ─── ItemUom ─── Item ─── (used by AsnLine/GrnLine/…)
        ├─ Partner   (supplier / customer / carrier)
        ├─ SystemConfig / CustomFieldDefinition
        └─ AuditLog

User              (no tenant FK at DB level — soft-linked via tenantId)
ApiKey ─── ApiKeyRequest
IntegrationRequest    (idempotency cache)
WebhookSubscription ─── WebhookDelivery
Announcement
Translation       (i18n override per tenant)

Model Count by Domain

Domain	Models
Auth & access	5 — User, ApiKey, ApiKeyRequest, IntegrationRequest, (WebhookSubscription / Delivery)
Master Data	7 — Tenant, Warehouse, Location, Uom, Item, ItemUom, Partner
Inventory	3 — Stock, StockMovement, StockReservation
LPN	2 — LicensePlate, LpnMovement
Inbound	5 — Asn, AsnLine, Grn, GrnLine, PutawayTask
Outbound	6 — SalesOrder, SalesOrderLine, Wave, PickTask, Shipment, ShipmentPackage
Returns	2 — Rma, RmaLine
Counts	2 — CycleCount, CycleCountLine
Rules & Replenishment	3 — PutawayRule, ReplenishmentRule, ReplenishmentTask
Approvals	1 — ApprovalRequest
Config & customization	2 — SystemConfig, CustomFieldDefinition
Webhooks	2 — WebhookSubscription, WebhookDelivery
Audit & misc	3 — AuditLog, Announcement, Translation
รวม	~43 models

Models by Domain (Detail)

Auth

Model	สิ่งสำคัญ
`User`	email unique, role (`admin`/`manager`/`supervisor`/`operator`/`viewer`), `adminModules: String[]`, `adminModulesWrite: String[]`, status (`active`/`inactive`/`locked`)
`ApiKey`	`keyHash` (SHA-256), `keyPrefix`, `scopes: String[]`, `ipWhitelist`, `allowedOrigins`, `rateLimitPerMinute`, `expiresAt`
`IntegrationRequest`	idempotency cache: `(tenantId, idempotencyKey, endpoint)` unique

Master Data

Model	สิ่งสำคัญ
`Tenant`	unique `code` — root entity
`Warehouse`	`(tenantId, code)` unique, timezone default `Asia/Bangkok`
`Location`	20+ capacity/storage fields — dimensions, max pallets/cases, storage type, access type, temperature zone, hazmat, abc class, allowed/restricted categories
`Uom`	`(tenantId, code)` unique — EA, BOX, CASE, PALLET
`Item`	`(tenantId, sku)` unique, tracking (`none`/`lot`/`serial`/`lot_and_serial`), abc class, shelf life, weight, volume, hazmat
`ItemUom`	conversion table (`1 CASE = 12 EA`)
`Partner`	type (`supplier`/`customer`/`carrier`/`both`)

Inventory

Model	สิ่งสำคัญ
`Stock`	unique `(locationId, itemId, lotNo, serialNo, status)` — fine-grained inventory; status `available`/`allocated`/`quarantine`/`damage`/`hold`; optional `lpnId` FK
`StockMovement`	append-only ledger — `movementType` ∈ {receipt, putaway, pick, pack, ship, adjust, transfer, return}, indexed by reference
`StockReservation`	hold stock for SO/transfer/production พร้อม `expiresAt`

Inbound

Model	สิ่งสำคัญ
`Asn` / `AsnLine`	Advance Shipment Notice; ASN no unique per warehouse; lines มี `expectedQty`/`receivedQty`
`Grn` / `GrnLine`	Goods Receipt; link ถึง Asn(Line) optional; รองรับ QC field
`PutawayTask`	from staging → final location; `actualTo` แยกจาก `toLocation` (suggested vs done)

Outbound

Model	สิ่งสำคัญ
`SalesOrder` / `SalesOrderLine`	priority 1-9, qtyOrdered/Allocated/Picked/Shipped (4 stages)
`Wave`	grouping strategy: customer/priority/zone/carrier + caps (maxOrders/Lines/Weight)
`PickTask`	individual pick — from location to staging, optional sequence (route optimization)
`Shipment` / `ShipmentPackage`	tracking number, weight, dimensions, label URL (R2)

Returns

Rma / RmaLine — disposition: restock / scrap / quarantine / repair

Counts

CycleCount / CycleCountLine — type (cycle/physical/spot), approval workflow built-in (approvalStatus, approvedBy/At)

Replenishment

ReplenishmentRule — min/max/replenish qty per (warehouse, item, location)
ReplenishmentTask — generated tasks from rules

Approvals

ApprovalRequest — generic queue: requestType (cycle_count_variance, stock_adjustment, …), details: Json, status (pending/approved/rejected)

LPN (License Plate)

LicensePlate — pallet/case/tote/container, optional parentLpnId (nesting), pallet standard (EUR/US/Thai/custom), weight/volume
LpnMovement — append-only: putaway/replenish/pick/move/build/split/merge/breakdown/ship/nest/unnest/close

Other

Model	บทบาท
`Announcement`	banner messages (type: info/warning/critical/maintenance) — Admin sets, Web shows
`AuditLog`	`BigInt` id, append-only — before/after JSON, indexed by `(entityType, entityId, createdAt DESC)`
`SystemConfig`	scoped config: system / tenant / warehouse / user; key+effectiveFrom unique
`CustomFieldDefinition`	dynamic schema for `customFields` JSON cols
`WebhookSubscription` / `WebhookDelivery`	outbound webhooks with retry tracking
`Translation`	i18n override per tenant

Conventions

Convention	ตัวอย่าง
PK	UUID v4 generated by Postgres: `gen_random_uuid()` (ยกเว้น `AuditLog`, `ApiKeyRequest`, `LpnMovement` ที่ใช้ `BigInt` autoincrement สำหรับ append-only log)
DB column	`snake_case` — `tenant_id`, `created_at`, `qty_on_hand`
Prisma field	`camelCase` — `tenantId`, `createdAt`, `qtyOnHand`
Mapping	`@map("snake_case")` + `@@map("snake_case_table")`
Soft delete	ไม่มี hard delete — ใช้ `status: 'inactive'` หรือ `active: false`
Timestamps	`@db.Timestamptz()` (timezone-aware) — defaults `now()` + `@updatedAt`
Decimal	`@db.Decimal(18, 4)` สำหรับ qty (avoid float drift), `Decimal(12, 3)` สำหรับ weight
Multi-tenant	ทุก master/transactional table มี `tenantId` หรือ `warehouseId` (warehouse owns tenant)
Custom fields	`customFields Json @default("{}")` — สำหรับ ext schema; ใช้ `CustomFieldDefinition` กำหนด UI/validation

Indexes

ระบบ index ที่สำคัญ (ดูเต็มใน schema.prisma):

Stock.@@index([itemId, warehouseId]) — ค้นหา stock per item per WH
Stock.@@index([lpnId]) — LPN inquiry
StockMovement.@@index([itemId, performedAt(sort: Desc)]) — ดู ledger ล่าสุด
StockMovement.@@index([referenceType, referenceId]) — back-link to ASN/SO/RMA
AuditLog.@@index([entityType, entityId, createdAt(sort: Desc)]) — audit query
ApprovalRequest.@@index([status, createdAt(sort: Desc)]) — pending queue
LicensePlate.@@index([warehouseId, status]) — active LPN per WH
ApiKey.@@index([keyHash]) — auth lookup
IntegrationRequest.@@index([createdAt]) — TTL cleanup
ApiKeyRequest.@@index([apiKeyId, createdAt]) — stats query

Migration Strategy

bash

# Development — generate migration files
pnpm db:migrate                 # = prisma migrate dev

# Production / Supabase — push without migration file
pnpm db:push                    # = prisma db push

# Regenerate client after schema change
pnpm db:generate                # = prisma generate

# Seed initial data (tenant + admin user)
pnpm --filter @wms/api prisma:seed

DIRECT_URL vs DATABASE_URL

DATABASE_URL → pooled (PgBouncer port 6543) — ใช้ runtime
DIRECT_URL → direct (port 5432) — ใช้ Prisma migrate/introspect

ทั้งสองต้องตั้งให้ครบใน .env มิฉะนั้น migration จะ fail ใน Supabase

Database Schema ​

ERD Summary (Key Relationships) ​

Model Count by Domain ​

Models by Domain (Detail) ​

Auth ​

Master Data ​

Inventory ​

Inbound ​

Outbound ​

Returns ​

Counts ​

Replenishment ​

Approvals ​

LPN (License Plate) ​

Other ​

Conventions ​

Indexes ​

Migration Strategy ​