5. Database Design
Halaman ini mendokumentasikan seluruh skema tabel database Siloora WMS, dikelompokkan berdasarkan Functional Area: Master, Transaction, dan Stock.
Setiap tabel menyertakan:
- Nama Tabel — mengikuti konvensi
snake_case(plural) - Deskripsi — entitas yang direpresentasikan
- Functional Area — modul operasional tabel
- Skema Kolom — nama kolom, tipe data, constraint, nullable, dan deskripsi/relasi
Entity Relationship Diagram (ERD)
Section titled “Entity Relationship Diagram (ERD)”Diagram berikut menampilkan relasi lengkap antara 27 tabel database Siloora WMS dengan cardinality dan constraint relationships:
Struktur ERD:
- Master Entities — Organisasi, Site, Warehouse, Zone, Location, Product, UOM, Business Partner
- Transaction Entities — Purchase Order, Inbound Order, Sales Order, Outbound Order, Picking Task, Dispatch Stage, Material Request
- Stock Entities — Stock Moves, Stock Balances, Stock Reservations
- Cardinality Notation —
||--o{(1-to-N),}o--||(N-to-1),||--|{(identifying relationships)
Functional Area: Master
Section titled “Functional Area: Master”1. organisations
Section titled “1. organisations”- Table Name:
organisations - Description: Menyimpan data entitas organisasi (tenant) induk yang mengelola seluruh operasional WMS.
- Functional Area: Master – Organisation
- Primary Key:
organisation_id(UUID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| organisation_id | UUID | PK | NO | Identifier unik organisasi |
| code | VARCHAR | 50, Unique, Not Null | NO | Kode organisasi untuk identifikasi internal |
| legal_name | VARCHAR | 255, Not Null | NO | Nama legal organisasi |
| status | ENUM | active, inactive, Default active | NO | Status operasional organisasi |
| tax_id | VARCHAR | 50, Unique | YES | Nomor identitas pajak/NPWP |
| country_code | CHAR | 2, ISO 3166-1 alpha-2 | NO | Kode negara organisasi |
| created_at | TIMESTAMP | Default CURRENT_TIMESTAMP | NO | Waktu pencatatan data dibuat |
| updated_at | TIMESTAMP | Auto update | NO | Waktu pembaruan data terakhir |
Relationships:
organisations(1) → (N)sites
2. sites
Section titled “2. sites”- Table Name:
sites - Description: Menyimpan data lokasi/cabang operasional yang berada di bawah satu organisasi.
- Functional Area: Master – Sites
- Primary Key:
site_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| site_id | CHAR | 26 (ULID), PK | NO | Identifier unik site |
| organisation_id | UUID | FK → organisations.organisation_id | NO | Relasi site ke organisasi induk |
| site_code | VARCHAR | 50, Unique per organisation, Not Null | NO | Kode site untuk identifikasi operasional |
| name | VARCHAR | 255, Not Null | NO | Nama site |
| city | VARCHAR | 100, Not Null | NO | Kota lokasi operasional site |
| country_code | CHAR | 2, ISO 3166-1 alpha-2 | NO | Kode negara site |
| is_primary | BOOLEAN | Default false | NO | Penanda site utama dalam organisasi |
| is_active | BOOLEAN | Default true | NO | Status aktivasi site |
Relationships:
sites(N) → (1)organisations—ON DELETE RESTRICTsites(1) → (N)warehouses
3. user_role_permissions
Section titled “3. user_role_permissions”- Table Name:
user_role_permissions - Description: Menyimpan kebijakan hak akses pengguna berupa kombinasi role, permission, dan cakupan organisasi/gudang.
- Functional Area: Master – User Role & Permission
- Primary Key:
user_id(UUID, composite with scope columns)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| user_id | UUID | PK / FK on assignment | NO | Identifier pengguna yang menerima hak akses |
| organisation_id | UUID | FK → organisations.organisation_id | YES | Cakupan organisasi pengguna bila berlaku |
| role_name | VARCHAR | 100, Not Null | NO | Nama role yang diberikan ke pengguna |
| permission_name | VARCHAR | 100, Not Null | NO | Nama permission granular untuk kontrol fitur |
| guard_name | VARCHAR | 50, Framework constraint | NO | Konteks guard untuk validasi otorisasi |
| address_id | UUID | FK → business_partner_addresses.address_id | YES | Cakupan alamat atau cabang yang dapat diakses |
| warehouse_id | UUID | FK → warehouses.warehouse_id | YES | Cakupan gudang operasional yang diotorisasi |
| is_active | BOOLEAN | Default true | NO | Status aktivasi kebijakan akses |
Relationships:
user_role_permissions(N) → (1)organisationsuser_role_permissions(N) → (1)warehouses
4. business_partners
Section titled “4. business_partners”- Table Name:
business_partners - Description: Menyimpan data mitra bisnis (vendor dan/atau customer) yang terlibat dalam transaksi inbound dan outbound.
- Functional Area: Master – Business Partner
- Primary Key:
partner_id(UUID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| partner_id | UUID | PK | NO | Identifier unik business partner |
| partner_code | VARCHAR | 50, Unique, Not Null | NO | Kode partner untuk identifikasi operasional |
| legal_name | VARCHAR | 255, Not Null | NO | Nama legal mitra |
| is_vendor | BOOLEAN | Default false | NO | Penanda partner berperan sebagai vendor |
| is_customer | BOOLEAN | Default false | NO | Penanda partner berperan sebagai customer |
| tax_id | VARCHAR | 50, Unique | YES | Nomor identitas pajak partner |
| currency_code | CHAR | 3, ISO 4217 | YES | Mata uang utama transaksi partner |
| is_active | BOOLEAN | Default true | NO | Status aktivasi partner |
Relationships:
business_partners(1) → (N)business_partner_contactsbusiness_partners(1) → (N)business_partner_addressesbusiness_partners(1) → (N)purchase_orders(as vendor)business_partners(1) → (N)sales_orders(as customer)
5. business_partner_contacts
Section titled “5. business_partner_contacts”- Table Name:
business_partner_contacts - Description: Menyimpan data kontak person in charge (PIC) dari setiap business partner.
- Functional Area: Master – Business Partner
- Primary Key:
contact_id(UUID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| contact_id | UUID | PK | NO | Identifier unik kontak partner |
| partner_id | UUID | FK → business_partners.partner_id | NO | Relasi kontak ke business partner |
| contact_name | VARCHAR | 255, Not Null | NO | Nama person in charge |
| job_title | VARCHAR | 100 | YES | Jabatan atau fungsi kontak |
| VARCHAR | 255, Valid email format | YES | Alamat email kontak | |
| phone | VARCHAR | 50, Valid phone format | YES | Nomor telepon kontak |
| is_primary | BOOLEAN | Default false | NO | Penanda kontak utama partner |
| is_active | BOOLEAN | Default true | NO | Status aktivasi kontak |
Relationships:
business_partner_contacts(N) → (1)business_partners—ON DELETE RESTRICT- Rule: Hanya satu kontak utama aktif per business partner
6. business_partner_addresses
Section titled “6. business_partner_addresses”- Table Name:
business_partner_addresses - Description: Menyimpan alamat pengiriman dan alamat default dari setiap business partner.
- Functional Area: Master – Business Partner
- Primary Key:
address_id(UUID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| address_id | UUID | PK | NO | Identifier unik alamat partner |
| partner_id | UUID | FK → business_partners.partner_id | NO | Relasi alamat ke business partner |
| address_type | ENUM | DEFAULT, SHIPPING, Not Null | NO | Jenis alamat yang didukung sistem |
| address_line_1 | VARCHAR | 255, Not Null | NO | Baris alamat utama |
| city | VARCHAR | 100, Not Null | NO | Kota alamat partner |
| postal_code | VARCHAR | 20, Not Null | NO | Kode pos alamat |
| is_primary | BOOLEAN | Default false | NO | Penanda alamat utama untuk tipe terkait |
| is_active | BOOLEAN | Default true | NO | Status aktivasi alamat |
Relationships:
business_partner_addresses(N) → (1)business_partners—ON DELETE RESTRICT- Rule: Satu alamat default per tipe per business partner
7. product_templates
Section titled “7. product_templates”- Table Name:
product_templates - Description: Menyimpan definisi induk produk (template) yang menjadi dasar pembuatan item/varian produk.
- Functional Area: Master – Product
- Primary Key:
product_template_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| product_template_id | CHAR | 26 (ULID), PK | NO | Identifier unik template produk |
| name | VARCHAR | 255, Unique, Not Null | NO | Nama template produk |
| category_code | VARCHAR | 50, Not Null | NO | Kategori utama produk |
| base_uom_id | CHAR | 26 (ULID), FK → uoms.uom_id | NO | Satuan dasar yang dipakai template |
| description | TEXT | — | YES | Deskripsi ringkas template |
| is_active | BOOLEAN | Default true | NO | Status aktivasi template |
| created_at | TIMESTAMP | Default CURRENT_TIMESTAMP | NO | Waktu pencatatan template |
| updated_at | TIMESTAMP | Auto update | NO | Waktu pembaruan template |
Relationships:
product_templates(1) → (N)product_productsproduct_templates(N) → (1)uoms(viabase_uom_id)
8. product_products
Section titled “8. product_products”- Table Name:
product_products - Description: Menyimpan data item/varian produk (SKU level) yang digunakan pada seluruh operasional gudang.
- Functional Area: Master – Product
- Primary Key:
product_product_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| product_product_id | CHAR | 26 (ULID), PK | NO | Identifier unik item produk |
| product_template_id | CHAR | 26 (ULID), FK → product_templates.product_template_id | NO | Relasi item ke template produk |
| sku | VARCHAR | 50, Unique, Not Null | NO | Kode SKU unik operasional |
| barcode | VARCHAR | 100, Not Null | NO | Kode barcode untuk scan proses gudang |
| base_uom_id | CHAR | 26 (ULID), FK → uoms.uom_id | NO | Satuan dasar item |
| weight | DECIMAL | 15,2, >= 0 | YES | Berat item untuk kalkulasi logistik |
| volume | DECIMAL | 15,2, >= 0 | YES | Volume item untuk perhitungan kapasitas |
| is_active | BOOLEAN | Default true | NO | Status aktivasi item |
Relationships:
product_products(N) → (1)product_templatesproduct_products(N) → (1)uomsproduct_products(1) → (N)stock_balancesproduct_products(1) → (N)stock_moves
9. uoms
Section titled “9. uoms”- Table Name:
uoms - Description: Menyimpan definisi satuan ukur (Unit of Measure) yang digunakan pada produk dan konversi.
- Functional Area: Master – UOM
- Primary Key:
uom_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| uom_id | CHAR | 26 (ULID), PK | NO | Identifier unik satuan ukur |
| code | VARCHAR | 50, Unique, Not Null | NO | Kode UOM untuk referensi teknis |
| name | VARCHAR | 50, Unique, Not Null | NO | Nama satuan ukur |
| description | TEXT | — | YES | Deskripsi satuan |
| precision_scale | INT | >= 0 | NO | Skala pembulatan kuantitas |
| is_reference | BOOLEAN | Default false | NO | Penanda satuan referensi dalam kategori |
| is_active | BOOLEAN | Default true | NO | Status aktivasi satuan |
| updated_at | TIMESTAMP | Auto update | NO | Waktu pembaruan data terakhir |
Relationships:
uoms(1) → (N)product_templates(viabase_uom_id)uoms(1) → (N)product_productsuoms(1) → (N)uom_conversions(asfrom_uom_idorto_uom_id)
10. uom_categories
Section titled “10. uom_categories”- Table Name:
uom_categories - Description: Menyimpan kategori pengelompokan satuan ukur untuk mendukung aturan konversi antar satuan dalam domain yang sama.
- Functional Area: Master – UOM
- Primary Key:
uom_category_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| uom_category_id | CHAR | 26 (ULID), PK | NO | Identifier unik kategori satuan |
| code | VARCHAR | 50, Unique, Not Null | NO | Kode kategori UOM |
| name | VARCHAR | 100, Unique, Not Null | NO | Nama kategori pengukuran |
| description | TEXT | — | YES | Deskripsi domain pengukuran |
| reference_uom_id | CHAR | 26 (ULID), FK → uoms.uom_id | YES | Satuan referensi utama kategori |
| is_active | BOOLEAN | Default true | NO | Status kategori |
| created_at | TIMESTAMP | Default CURRENT_TIMESTAMP | NO | Waktu pencatatan kategori |
| updated_at | TIMESTAMP | Auto update | NO | Waktu pembaruan kategori |
Relationships:
uom_categories(1) → (N)uom_conversionsuom_categories(N) → (1)uoms(viareference_uom_id)
11. uom_conversions
Section titled “11. uom_conversions”- Table Name:
uom_conversions - Description: Menyimpan aturan konversi antara dua satuan ukur dalam satu kategori UOM, termasuk rasio dan metode pembulatan.
- Functional Area: Master – UOM
- Primary Key:
uom_conversion_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| uom_conversion_id | CHAR | 26 (ULID), PK | NO | Identifier unik aturan konversi |
| uom_category_id | CHAR | 26 (ULID), FK → uom_categories.uom_category_id | NO | Kategori domain konversi |
| from_uom_id | CHAR | 26 (ULID), FK → uoms.uom_id | NO | Satuan asal |
| to_uom_id | CHAR | 26 (ULID), FK → uoms.uom_id | NO | Satuan tujuan |
| conversion_ratio | DECIMAL | 15,6, > 0, Not Null | NO | Rasio konversi dari satuan asal ke tujuan |
| rounding_mode | ENUM | UP, DOWN, HALF_UP, Not Null | NO | Metode pembulatan hasil konversi |
| level | INT | >= 0 | NO | Urutan prioritas konversi dalam kategori |
| is_active | BOOLEAN | Default true | NO | Status aturan konversi |
Relationships:
uom_conversions(N) → (1)uom_categoriesuom_conversions(N) → (1)uoms(viafrom_uom_id)uom_conversions(N) → (1)uoms(viato_uom_id)
12. warehouses
Section titled “12. warehouses”- Table Name:
warehouses - Description: Menyimpan data gudang fisik yang beroperasi di bawah satu site, sebagai unit pengelolaan stok dan transaksi.
- Functional Area: Master – Warehouse
- Primary Key:
warehouse_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| warehouse_id | CHAR | 26 (ULID), PK | NO | Identifier unik gudang |
| site_id | CHAR | 26 (ULID), FK → sites.site_id | NO | Relasi gudang ke site |
| warehouse_code | VARCHAR | 20, Unique per site, Not Null | NO | Kode gudang operasional |
| name | VARCHAR | 100, Not Null | NO | Nama gudang |
| city | VARCHAR | 100 | YES | Kota lokasi gudang |
| country_code | CHAR | 2, ISO 3166-1 alpha-2 | YES | Kode negara gudang |
| requires_confirmation | BOOLEAN | Default true | NO | Kebutuhan konfirmasi proses gudang |
| is_active | BOOLEAN | Default true | NO | Status aktivasi gudang |
Relationships:
warehouses(N) → (1)siteswarehouses(1) → (N)zoneswarehouses(1) → (N)inbound_orderswarehouses(1) → (N)sales_orders
13. zones
Section titled “13. zones”- Table Name:
zones - Description: Menyimpan data zona/area operasional dalam gudang, mendukung hierarki zona berbasis path untuk navigasi dan penugasan lokasi.
- Functional Area: Master – Warehouse
- Primary Key:
zone_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| zone_id | CHAR | 26 (ULID), PK | NO | Identifier unik zona |
| warehouse_id | CHAR | 26 (ULID), FK → warehouses.warehouse_id | NO | Relasi zona ke gudang |
| parent_zone_id | CHAR | 26 (ULID), Self FK → zones.zone_id | YES | Relasi parent untuk hierarki zona |
| zone_code | VARCHAR | 20, Unique per warehouse, Not Null | NO | Kode zona |
| name | VARCHAR | 100, Not Null | NO | Nama zona |
| zone_type | VARCHAR | 50, Not Null | NO | Tipe area operasional zona |
| path | TEXT | Indexed path hierarchy | YES | Jalur hierarki zona untuk query turunan |
| is_active | BOOLEAN | Default true | NO | Status aktivasi zona |
Relationships:
zones(N) → (1)warehouseszones(N) → (1, self)zones(viaparent_zone_id) — hierarki tanpa sikluszones(1) → (N)locations
14. locations
Section titled “14. locations”- Table Name:
locations - Description: Menyimpan data lokasi fisik (rak, lantai, atau virtual) dalam zona gudang sebagai titik penyimpanan stok.
- Functional Area: Master – Warehouse
- Primary Key:
location_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| location_id | CHAR | 26 (ULID), PK | NO | Identifier unik lokasi |
| zone_id | CHAR | 26 (ULID), FK → zones.zone_id | NO | Relasi lokasi ke zona |
| location_code | VARCHAR | 50, Unique, Not Null | NO | Kode lokasi untuk proses scan |
| location_type | ENUM | RACK, FLOOR, VIRTUAL, Not Null | NO | Klasifikasi tipe lokasi |
| location_status | ENUM | EMPTY, PARTIAL, FULL, BLOCKED, Default EMPTY | NO | Status okupansi lokasi |
| max_weight | DECIMAL | 15,2, >= 0 | YES | Batas kapasitas berat lokasi |
| max_volume | DECIMAL | 15,2, >= 0 | YES | Batas kapasitas volume lokasi |
| is_mixed_sku_allowed | BOOLEAN | Default false | NO | Penanda lokasi dapat menampung multi SKU |
| is_active | BOOLEAN | Default true | NO | Status aktivasi lokasi |
Relationships:
locations(N) → (1)zoneslocations(1) → (N)stock_balanceslocations(1) → (N)stock_moves(asfrom_locationorto_location)locations(1) → (N)picking_task_lines
Functional Area: Transaction
Section titled “Functional Area: Transaction”15. purchase_orders
Section titled “15. purchase_orders”- Table Name:
purchase_orders - Description: Menyimpan header dokumen Purchase Order yang dikeluarkan kepada vendor untuk pengadaan barang.
- Functional Area: Transaction – Purchasing
- Primary Key:
purchase_order_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| purchase_order_id | CHAR | 26 (ULID), PK | NO | Identifier unik purchase order |
| po_number | VARCHAR | 50, Unique, Not Null | NO | Nomor dokumen PO |
| partner_id | CHAR | 26 (ULID), FK → business_partners.partner_id | NO | Vendor pemasok untuk PO |
| order_date | DATE | Not Null | NO | Tanggal pembuatan PO |
| expected_delivery_date | DATE | Not Null | NO | Tanggal estimasi kedatangan barang |
| status | ENUM | DRAFT, OPEN, PARTIAL, CLOSED, VOID, Not Null | NO | Status siklus hidup PO |
| total_amount | DECIMAL | 15,2, >= 0 | NO | Nilai total PO |
| currency_code | CHAR | 3, ISO 4217, Not Null | NO | Mata uang transaksi |
Relationships:
purchase_orders(N) → (1)business_partnerspurchase_orders(1) → (N)purchase_order_items- Status lifecycle:
DRAFT→OPEN→PARTIAL→CLOSED/VOID
16. purchase_order_items
Section titled “16. purchase_order_items”- Table Name:
purchase_order_items - Description: Menyimpan rincian line item dari setiap Purchase Order, termasuk kuantitas dipesan dan diterima.
- Functional Area: Transaction – Purchasing
- Primary Key:
purchase_order_item_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| purchase_order_item_id | CHAR | 26 (ULID), PK | NO | Identifier unik line item PO |
| purchase_order_id | CHAR | 26 (ULID), FK → purchase_orders.purchase_order_id | NO | Relasi line ke header PO |
| product_id | CHAR | 26 (ULID), FK → product_products.product_product_id | NO | Produk yang dipesan |
| sku_snapshot | VARCHAR | 100, Not Null | NO | Snapshot SKU saat PO dibuat |
| uom_snapshot | VARCHAR | 20, Not Null | NO | Snapshot UOM saat PO dibuat |
| quantity_ordered | DECIMAL | 15,2, > 0 | NO | Kuantitas yang dipesan |
| quantity_received | DECIMAL | 15,2, Default 0 | NO | Kuantitas yang sudah diterima |
| unit_price | DECIMAL | 15,2, >= 0 | NO | Harga satuan item |
Relationships:
purchase_order_items(N) → (1)purchase_orders—ON DELETE CASCADEpurchase_order_items(N) → (1)product_products
17. inbound_orders
Section titled “17. inbound_orders”- Table Name:
inbound_orders - Description: Menyimpan dokumen penerimaan barang ke gudang, baik dari vendor maupun transfer antar gudang.
- Functional Area: Transaction – Inbound
- Primary Key:
inbound_order_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| inbound_order_id | CHAR | 26 (ULID), PK | NO | Identifier unik inbound order |
| ib_number | VARCHAR | 50, Unique, Not Null | NO | Nomor dokumen inbound |
| inbound_type | ENUM | VENDOR, TRANSFER, Not Null | NO | Jenis inbound |
| vendor_id | CHAR | 26 (ULID), FK → business_partners.partner_id | YES | Vendor pemasok (untuk inbound vendor) |
| source_warehouse_id | CHAR | 26 (ULID), FK → warehouses.warehouse_id | YES | Gudang asal (untuk transfer) |
| warehouse_id | CHAR | 26 (ULID), FK → warehouses.warehouse_id | NO | Gudang tujuan inbound |
| status | ENUM | DRAFT, SCHEDULED, ARRIVED, RECEIVING, COMPLETED, CANCELLED, Not Null | NO | Status proses inbound |
| expected_date | TIMESTAMP | Indexed, Not Null | NO | Jadwal kedatangan barang |
Relationships:
inbound_orders(N) → (1)business_partners(vendor)inbound_orders(N) → (1)warehouses(destination)- Status lifecycle:
DRAFT→SCHEDULED→ARRIVED→RECEIVING→COMPLETED/CANCELLED
18. sales_orders
Section titled “18. sales_orders”- Table Name:
sales_orders - Description: Menyimpan header dokumen Sales Order dari customer sebagai dasar proses pemenuhan dan pengiriman barang.
- Functional Area: Transaction – Sales
- Primary Key:
sales_order_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| sales_order_id | CHAR | 26 (ULID), PK | NO | Identifier unik sales order |
| so_number | VARCHAR | 50, Unique, Not Null | NO | Nomor dokumen sales order |
| reference_number | VARCHAR | 100, Indexed | YES | Referensi eksternal order |
| customer_id | CHAR | 26 (ULID), FK → business_partners.partner_id | NO | Customer pemesan |
| warehouse_id | CHAR | 26 (ULID), FK → warehouses.warehouse_id | NO | Gudang pemenuhan order |
| order_date | TIMESTAMP | Indexed, Not Null | NO | Tanggal order dibuat |
| required_date | TIMESTAMP | Indexed, Not Null | NO | Tanggal target pemenuhan |
| status | ENUM | DRAFT, RELEASED, ALLOCATING, PICKING, PACKED, SHIPPED, CANCELLED, Not Null | NO | Status siklus order |
Relationships:
sales_orders(N) → (1)business_partners(customer)sales_orders(N) → (1)warehousessales_orders(1) → (N)outbound_orders- Status lifecycle:
DRAFT→RELEASED→ALLOCATING→PICKING→PACKED→SHIPPED/CANCELLED
19. outbound_orders
Section titled “19. outbound_orders”- Table Name:
outbound_orders - Description: Menyimpan dokumen instruksi pengeluaran barang dari gudang, mencakup tipe SALES, TRANSFER, RTV, dan SCRAP.
- Functional Area: Transaction – Outbound
- Primary Key:
outbound_order_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| outbound_order_id | CHAR | 26 (ULID), PK | NO | Identifier unik outbound order |
| ob_number | VARCHAR | 50, Unique, Not Null | NO | Nomor dokumen outbound |
| order_type | ENUM | SALES, TRANSFER, RTV, SCRAP, Not Null | NO | Jenis outbound |
| reference_number | VARCHAR | 100, Indexed, Not Null | NO | Referensi sumber order |
| origin_warehouse_id | CHAR | 26 (ULID), FK → warehouses.warehouse_id | NO | Gudang asal barang |
| target_warehouse_id | CHAR | 26 (ULID), FK → warehouses.warehouse_id | YES | Gudang tujuan untuk transfer |
| customer_id | CHAR | 26 (ULID), FK → business_partners.partner_id | YES | Customer tujuan pengiriman |
| status | ENUM | DRAFT, ALLOCATED, PICKING, PACKED, SHIPPED, DELIVERED, CANCELLED, Not Null | NO | Status proses outbound |
Relationships:
outbound_orders(N) → (1)warehouses(origin)outbound_orders(N) → (1)business_partners(customer)outbound_orders(1) → (N)picking_ordersoutbound_orders(1) → (N)dispatch_stages- Status lifecycle:
DRAFT→ALLOCATED→PICKING→PACKED→SHIPPED→DELIVERED/CANCELLED
20. picking_orders
Section titled “20. picking_orders”- Table Name:
picking_orders - Description: Menyimpan instruksi tugas picking yang diberikan kepada operator gudang untuk proses pengambilan barang dari lokasi rak.
- Functional Area: Transaction – Picking
- Primary Key:
picking_order_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| picking_order_id | CHAR | 26 (ULID), PK | NO | Identifier unik picking task |
| task_number | VARCHAR | 50, Unique, Not Null | NO | Nomor task picking |
| outbound_order_id | CHAR | 26 (ULID), FK → outbound_orders.outbound_order_id | NO | Relasi task ke outbound order |
| assigned_to | CHAR | 26 (ULID), FK → users.user_id | YES | Operator yang ditugaskan |
| status | ENUM | DRAFT, ASSIGNED, IN_PROGRESS, COMPLETED, CANCELLED, Not Null | NO | Status task picking |
| started_at | TIMESTAMP | — | YES | Waktu mulai eksekusi picking |
| completed_at | TIMESTAMP | — | YES | Waktu selesai picking |
| picked_qty_total | DECIMAL | 15,2, >= 0 | NO | Total kuantitas hasil pick |
Relationships:
picking_orders(N) → (1)outbound_orderspicking_orders(1) → (N)dispatch_stages- Status lifecycle:
DRAFT→ASSIGNED→IN_PROGRESS→COMPLETED/CANCELLED
21. picking_tasks
Section titled “21. picking_tasks”- Table Name:
picking_tasks - Description: Menyimpan dokumen instruksi picking internal WMS sebagai header tugas pengambilan barang dari rak oleh operator.
- Functional Area: Transaction – Picking
- Primary Key:
id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| id | CHAR | 26 (ULID), PK | NO | Primary Key |
| task_number | VARCHAR | 50, Unique | NO | Nomor instruksi/tugas Picking internal WMS |
| outbound_order_id | CHAR | 26 (ULID), FK → outbound_orders.id, Index | NO | Relasi ke outbound_orders.id (Dokumen dasar pengeluaran) |
| assigned_to | CHAR | 26 (ULID), FK → users.id, Index | YES | ID Operator / Picker yang ditugaskan |
| status | ENUM | DRAFT, ASSIGNED, IN_PROGRESS, COMPLETED, CANCELLED | NO | Status workflow tugas |
| started_at | TIMESTAMP | — | YES | Waktu aktual operator mulai melakukan scan lokasi/barang |
| completed_at | TIMESTAMP | — | YES | Waktu aktual seluruh baris tugas selesai dikonfirmasi |
| ext | JSON | — | YES | Ekstra kolom dinamis (catatan prioritas, alat angkut, dsb) |
| created_by | CHAR | 26 (ULID) | NO | Audit: ID User/Sistem pembuat tugas |
| updated_by | CHAR | 26 (ULID) | YES | Audit: ID User pengubah terakhir |
| created_at | TIMESTAMP | — | NO | Laravel standard |
| updated_at | TIMESTAMP | — | NO | Laravel standard |
| deleted_at | TIMESTAMP | Index | YES | Soft delete marker |
Relationships:
picking_tasks(N) → (1)outbound_orders—ON DELETE RESTRICTpicking_tasks(1) → (N)picking_task_lines—ON DELETE CASCADE- Status lifecycle:
DRAFT→ASSIGNED→IN_PROGRESS→COMPLETED/CANCELLED
22. picking_task_lines
Section titled “22. picking_task_lines”- Table Name:
picking_task_lines - Description: Menyimpan rincian lokasi spesifik rak, SKU, target kuantitas yang harus diambil (to pick), dan realisasi hasil (picked) per instruksi tugas picking.
- Functional Area: Transaction – Picking
- Primary Key:
id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| id | CHAR | 26 (ULID), PK | NO | Primary Key |
| picking_task_id | CHAR | 26 (ULID), FK → picking_tasks.id, Index | NO | Relasi ke picking_tasks.id |
| outbound_line_id | CHAR | 26 (ULID), FK → outbound_order_lines.id, Index | NO | Relasi ke outbound_order_lines.id |
| location_id | CHAR | 26 (ULID), FK → locations.location_id, Index | NO | Relasi ke locations.location_id (Lokasi rak/bin sumber) |
| product_id | CHAR | 26 (ULID), FK → product_products.id, Index | NO | Relasi ke product_products.id |
| uom_id | CHAR | 26 (ULID), FK → uoms.uom_id | NO | Relasi ke uoms.uom_id |
| qty_to_pick | DECIMAL | 15,2, Default 0.00 | NO | Target kuantitas instruksi pengambilan |
| qty_picked | DECIMAL | 15,2, Default 0.00 | NO | Kuantitas fisik yang aktual terambil di rak |
| batch_number | VARCHAR | 50, Index | YES | Nomor lot/batch yang diinstruksikan atau dikonfirmasi scan |
| ext | JSON | — | YES | Catatan item (barang rusak saat diambil, dsb) |
| created_by | CHAR | 26 (ULID) | NO | Audit: ID User pembuat |
| updated_by | CHAR | 26 (ULID) | YES | Audit: ID User pengubah terakhir |
| created_at | TIMESTAMP | — | NO | Laravel standard |
| updated_at | TIMESTAMP | — | NO | Laravel standard |
Relationships:
picking_task_lines(N) → (1)picking_tasks—ON DELETE CASCADEpicking_task_lines(N) → (1)locations—ON DELETE RESTRICTpicking_task_lines(N) → (1)outbound_order_lines—ON DELETE RESTRICTpicking_task_lines(N) → (1)product_products—ON DELETE RESTRICTpicking_task_lines(N) → (1)uoms
23. dispatch_stages
Section titled “23. dispatch_stages”- Table Name:
dispatch_stages - Description: Menyimpan rekaman serah kirim barang dari hasil picking ke proses pengiriman, termasuk status posting goods issue.
- Functional Area: Transaction – Dispatch
- Primary Key:
dispatch_stage_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| dispatch_stage_id | CHAR | 26 (ULID), PK | NO | Identifier unik tahapan dispatch |
| outbound_order_id | CHAR | 26 (ULID), FK → outbound_orders.outbound_order_id | NO | Relasi dispatch ke outbound order |
| picking_order_id | CHAR | 26 (ULID), FK → picking_orders.picking_order_id | NO | Relasi dispatch ke picking task |
| dispatch_number | VARCHAR | 50, Unique, Not Null | NO | Nomor dokumen dispatch |
| dispatched_at | TIMESTAMP | Not Null | NO | Waktu serah kirim dilakukan |
| dispatched_by | CHAR | 26 (ULID), FK → users.user_id | YES | Petugas yang memproses dispatch |
| goods_issue_status | ENUM | PENDING, POSTED, FAILED, Not Null | NO | Status posting goods issue |
| status | ENUM | READY, DISPATCHED, CLOSED, Not Null | NO | Status lifecycle dispatch stage |
Relationships:
dispatch_stages(N) → (1)outbound_ordersdispatch_stages(N) → (1)picking_orders- Status lifecycle:
READY→DISPATCHED→CLOSED
24. material_requests
Section titled “24. material_requests”- Table Name:
material_requests - Description: Menyimpan permintaan pengadaan material internal dari satu area/gudang ke gudang lain, dengan alur approval bertahap.
- Functional Area: Transaction – Material Request
- Primary Key:
material_request_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| material_request_id | CHAR | 26 (ULID), PK | NO | Identifier unik material request |
| mr_number | VARCHAR | 50, Unique, Not Null | NO | Nomor dokumen material request |
| requester_id | CHAR | 26 (ULID), FK → users.user_id | NO | Pengguna yang membuat request |
| source_warehouse_id | CHAR | 26 (ULID), FK → warehouses.warehouse_id | NO | Gudang asal pemenuhan |
| target_warehouse_id | CHAR | 26 (ULID), FK → warehouses.warehouse_id | NO | Gudang atau area tujuan |
| required_date | DATE | Not Null | NO | Tanggal kebutuhan material |
| priority | ENUM | LOW, MEDIUM, HIGH, URGENT, Default MEDIUM | NO | Prioritas pemenuhan request |
| status | ENUM | DRAFT, SUBMITTED, APPROVED, ALLOCATED, FULFILLED, CANCELLED, Not Null | NO | Status workflow material request |
Relationships:
material_requests(N) → (1)warehouses(source)material_requests(N) → (1)warehouses(target)- Status lifecycle:
DRAFT→SUBMITTED→APPROVED→ALLOCATED→FULFILLED/CANCELLED
Functional Area: Stock
Section titled “Functional Area: Stock”25. stock_moves
Section titled “25. stock_moves”- Table Name:
stock_moves - Description: Menyimpan dokumen pergerakan stok (header dan baris item) yang mencatat setiap perpindahan produk antar lokasi dalam gudang.
- Functional Area: Stock – Movement
- Primary Key:
move_id(ULID, header);item_id(ULID, line item)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| move_id | CHAR | 26 (ULID), PK (header) | NO | Identifier dokumen stock move |
| status | ENUM | DRAFT, POSTED, CANCELLED, Not Null | NO | Status workflow dokumen |
| posting_date | TIMESTAMP | Indexed, Not Null | NO | Waktu stok resmi berubah di sistem |
| move_type_group | ENUM | GR, GI, TP, ADJ, Not Null | NO | GR=receipt, GI=issue, TP=transfer, ADJ=adjustment |
| item_id | CHAR | 26 (ULID), PK (line) | NO | Identifier baris item move |
| product_id | CHAR | 26 (ULID), FK → product_products.product_product_id | NO | Produk yang bergerak |
| from_location | CHAR | 26 (ULID), FK → locations.location_id | YES | Lokasi asal pergerakan |
| to_location | CHAR | 26 (ULID), FK → locations.location_id | YES | Lokasi tujuan pergerakan |
| qty_actual | DECIMAL | 15,3, > 0 | NO | Kuantitas aktual yang berpindah |
Relationships:
stock_moves(N) → (1)product_productsstock_moves(N) → (1)locations(viafrom_location)stock_moves(N) → (1)locations(viato_location)- Status lifecycle:
DRAFT→POSTED/CANCELLED
26. stock_balances
Section titled “26. stock_balances”- Table Name:
stock_balances - Description: Menyimpan saldo stok aktual per produk, lokasi, dan satuan (quant model), termasuk kuantitas tersedia dan yang sudah direservasi.
- Functional Area: Stock – Balance
- Primary Key:
balance_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| balance_id | CHAR | 26 (ULID), PK | NO | Identifier unik quant balance |
| product_id | CHAR | 26 (ULID), FK → product_products.product_product_id | NO | Produk pada quant |
| location_id | CHAR | 26 (ULID), FK → locations.location_id | NO | Lokasi penyimpanan |
| uom_id | CHAR | 26 (ULID), FK → uoms.uom_id | NO | Satuan penyimpanan |
| qty_on_hand | DECIMAL | 15,2, Default 0, >= 0 | NO | Kuantitas fisik aktual |
| qty_reserved | DECIMAL | 15,2, Default 0, >= 0 | NO | Kuantitas yang sudah dibooking |
| qty_available | DECIMAL | 15,2, Computed: on_hand - reserved | NO | Kuantitas tersedia untuk alokasi |
| batch_number | VARCHAR | 50, Indexed | YES | Identitas batch atau lot |
| serial_number | VARCHAR | 100, Indexed | YES | Nomor seri unit |
Relationships:
stock_balances(N) → (1)product_productsstock_balances(N) → (1)locationsstock_balances(N) → (1)uomsstock_balances(1) → (N)stock_reservations
27. stock_reservations
Section titled “27. stock_reservations”- Table Name:
stock_reservations - Description: Menyimpan data reservasi stok yang mengunci kuantitas tertentu pada quant balance sebagai jaminan ketersediaan untuk dokumen transaksi yang sedang aktif.
- Functional Area: Stock – Reservation
- Primary Key:
reservation_id(ULID)
| Column Name | Data Type | Size / Constraints | Nullable | Description / Relasi |
|---|---|---|---|---|
| reservation_id | CHAR | 26 (ULID), PK | NO | Identifier unik reservasi |
| balance_id | CHAR | 26 (ULID), FK → stock_balances.balance_id | NO | Relasi ke quant balance |
| product_id | CHAR | 26 (ULID), FK → product_products.product_product_id | NO | Produk yang direservasi |
| location_id | CHAR | 26 (ULID), FK → locations.location_id | NO | Lokasi pengambilan stok |
| reference_type | VARCHAR | 50, Indexed, Not Null | NO | Jenis dokumen pemicu reservasi |
| reference_id | CHAR | 26 (ULID), Indexed, Not Null | NO | ID dokumen pemicu reservasi |
| qty_reserved | DECIMAL | 15,3, > 0 | NO | Jumlah stok yang dibooking |
| status | ENUM | ACTIVE, COMPLETED, CANCELLED, Not Null | NO | Status lifecycle reservasi |
| reserved_at | TIMESTAMP | Not Null | NO | Waktu stok dikunci |
Relationships:
stock_reservations(N) → (1)stock_balancesstock_reservations(N) → (1)product_productsstock_reservations(N) → (1)locations- Status lifecycle:
ACTIVE→COMPLETED/CANCELLED