442 lines
13 KiB
HTML
442 lines
13 KiB
HTML
<!doctype html><html><head><meta charset="utf-8"><style>@page { size:A4; margin:2cm 1.6cm; } body { font-family: Arial, Helvetica, sans-serif; font-size:11pt; line-height:1.5; } pre { white-space: pre-wrap; font-size:9pt; }</style></head><body><h1>ERD Schema Sistem Inventory Walet</h1><pre>Project walet_inventory {
|
|
database_type: "PostgreSQL"
|
|
Note: 'ERD importable schema for lot-based traceable walet inventory system'
|
|
}
|
|
|
|
Table suppliers {
|
|
id bigint [pk, increment]
|
|
code varchar [not null, unique]
|
|
name varchar [not null]
|
|
phone varchar
|
|
email varchar
|
|
bank_name varchar
|
|
bank_account_number varchar
|
|
address text
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table customers {
|
|
id bigint [pk, increment]
|
|
code varchar [not null, unique]
|
|
name varchar [not null]
|
|
phone varchar
|
|
email varchar
|
|
bank_name varchar
|
|
bank_account_number varchar
|
|
address text
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table roles {
|
|
id bigint [pk, increment]
|
|
code varchar [not null, unique]
|
|
name varchar [not null]
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table users {
|
|
id bigint [pk, increment]
|
|
role_id bigint [not null]
|
|
name varchar [not null]
|
|
email varchar
|
|
phone varchar
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table units {
|
|
id bigint [pk, increment]
|
|
code varchar [not null, unique]
|
|
name varchar [not null]
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table item_types {
|
|
id bigint [pk, increment]
|
|
code varchar [not null, unique]
|
|
name varchar [not null]
|
|
description text
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table item_grades {
|
|
id bigint [pk, increment]
|
|
code varchar [not null, unique]
|
|
name varchar [not null]
|
|
rank_order int
|
|
description text
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table warehouses {
|
|
id bigint [pk, increment]
|
|
code varchar [not null, unique]
|
|
name varchar [not null]
|
|
address text
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table warehouse_locations {
|
|
id bigint [pk, increment]
|
|
warehouse_id bigint [not null]
|
|
code varchar [not null]
|
|
name varchar [not null]
|
|
location_type varchar
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
|
|
indexes {
|
|
(warehouse_id, code) [unique]
|
|
}
|
|
}
|
|
|
|
Table adjustment_reasons {
|
|
id bigint [pk, increment]
|
|
code varchar [not null, unique]
|
|
name varchar [not null]
|
|
category varchar [not null]
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table purchases {
|
|
id bigint [pk, increment]
|
|
purchase_no varchar [not null, unique]
|
|
supplier_id bigint [not null]
|
|
purchase_date date [not null]
|
|
supplier_invoice_no varchar
|
|
status varchar [not null, default: 'DRAFT']
|
|
notes text
|
|
created_by bigint [not null]
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table purchase_lines {
|
|
id bigint [pk, increment]
|
|
purchase_id bigint [not null]
|
|
item_type_id bigint [not null]
|
|
item_grade_id bigint
|
|
qty_ordered decimal(18,3) [not null]
|
|
unit_id bigint [not null]
|
|
unit_price decimal(18,2) [not null]
|
|
subtotal decimal(18,2) [not null]
|
|
classification_status varchar [not null, default: 'FINAL']
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table receipts {
|
|
id bigint [pk, increment]
|
|
receipt_no varchar [not null, unique]
|
|
purchase_id bigint [not null]
|
|
supplier_id bigint [not null]
|
|
receipt_date date [not null]
|
|
status varchar [not null, default: 'DRAFT']
|
|
notes text
|
|
received_by bigint [not null]
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table receipt_lines {
|
|
id bigint [pk, increment]
|
|
receipt_id bigint [not null]
|
|
purchase_line_id bigint [not null]
|
|
item_type_id bigint [not null]
|
|
item_grade_id bigint
|
|
qty_received decimal(18,3) [not null]
|
|
qty_accepted decimal(18,3) [not null]
|
|
qty_rejected decimal(18,3) [not null, default: 0]
|
|
unit_id bigint [not null]
|
|
unit_cost decimal(18,2) [not null]
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table inventory_lots {
|
|
id bigint [pk, increment]
|
|
lot_code varchar [not null, unique]
|
|
parent_lot_id bigint
|
|
source_type varchar [not null]
|
|
source_ref_id bigint
|
|
supplier_id bigint
|
|
purchase_id bigint
|
|
purchase_line_id bigint
|
|
receipt_id bigint
|
|
receipt_line_id bigint
|
|
item_type_id bigint [not null]
|
|
item_grade_id bigint [not null]
|
|
warehouse_id bigint [not null]
|
|
warehouse_location_id bigint
|
|
original_qty decimal(18,3) [not null]
|
|
available_qty decimal(18,3) [not null]
|
|
reserved_qty decimal(18,3) [not null, default: 0]
|
|
damaged_qty decimal(18,3) [not null, default: 0]
|
|
shrinkage_qty decimal(18,3) [not null, default: 0]
|
|
unit_id bigint [not null]
|
|
unit_cost decimal(18,2) [not null]
|
|
received_at timestamp [not null]
|
|
status varchar [not null, default: 'ACTIVE']
|
|
qr_code_value varchar
|
|
barcode_value varchar
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
|
|
indexes {
|
|
(item_type_id, item_grade_id, warehouse_id)
|
|
(supplier_id, item_type_id, item_grade_id)
|
|
}
|
|
}
|
|
|
|
Table sorting_sessions {
|
|
id bigint [pk, increment]
|
|
sorting_no varchar [not null, unique]
|
|
source_lot_id bigint [not null]
|
|
sorting_date timestamp [not null]
|
|
input_qty decimal(18,3) [not null]
|
|
output_qty decimal(18,3) [not null]
|
|
shrinkage_qty decimal(18,3) [not null, default: 0]
|
|
notes text
|
|
sorted_by bigint [not null]
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table sorting_results {
|
|
id bigint [pk, increment]
|
|
sorting_session_id bigint [not null]
|
|
result_lot_id bigint [not null]
|
|
item_type_id bigint [not null]
|
|
item_grade_id bigint [not null]
|
|
qty_result decimal(18,3) [not null]
|
|
unit_cost decimal(18,2) [not null]
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table sales {
|
|
id bigint [pk, increment]
|
|
sales_no varchar [not null, unique]
|
|
customer_id bigint [not null]
|
|
sales_date date [not null]
|
|
status varchar [not null, default: 'DRAFT']
|
|
notes text
|
|
created_by bigint [not null]
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table sales_lines {
|
|
id bigint [pk, increment]
|
|
sales_id bigint [not null]
|
|
item_type_id bigint [not null]
|
|
item_grade_id bigint [not null]
|
|
qty_sold decimal(18,3) [not null]
|
|
unit_id bigint [not null]
|
|
selling_price decimal(18,2) [not null]
|
|
subtotal decimal(18,2) [not null]
|
|
costing_total decimal(18,2) [not null, default: 0]
|
|
gross_margin decimal(18,2) [not null, default: 0]
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table sales_allocations {
|
|
id bigint [pk, increment]
|
|
sales_line_id bigint [not null]
|
|
inventory_lot_id bigint [not null]
|
|
qty_allocated decimal(18,3) [not null]
|
|
unit_cost decimal(18,2) [not null]
|
|
total_cost decimal(18,2) [not null]
|
|
allocated_at timestamp [not null]
|
|
allocated_by bigint [not null]
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table inventory_movements {
|
|
id bigint [pk, increment]
|
|
movement_no varchar [not null, unique]
|
|
movement_type varchar [not null]
|
|
inventory_lot_id bigint [not null]
|
|
related_lot_id bigint
|
|
warehouse_id bigint [not null]
|
|
warehouse_location_id bigint
|
|
qty_in decimal(18,3) [not null, default: 0]
|
|
qty_out decimal(18,3) [not null, default: 0]
|
|
balance_after decimal(18,3) [not null]
|
|
unit_cost decimal(18,2) [not null]
|
|
reference_type varchar [not null]
|
|
reference_id bigint [not null]
|
|
reason_id bigint
|
|
movement_date timestamp [not null]
|
|
created_by bigint [not null]
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table sales_returns {
|
|
id bigint [pk, increment]
|
|
sales_id bigint [not null]
|
|
customer_id bigint [not null]
|
|
return_date date [not null]
|
|
status varchar [not null, default: 'DRAFT']
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table sales_return_lines {
|
|
id bigint [pk, increment]
|
|
sales_return_id bigint [not null]
|
|
sales_line_id bigint [not null]
|
|
inventory_lot_id bigint
|
|
item_type_id bigint [not null]
|
|
item_grade_id bigint [not null]
|
|
qty_returned decimal(18,3) [not null]
|
|
return_condition varchar
|
|
resolution varchar
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table purchase_returns {
|
|
id bigint [pk, increment]
|
|
purchase_id bigint [not null]
|
|
supplier_id bigint [not null]
|
|
return_date date [not null]
|
|
status varchar [not null, default: 'DRAFT']
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table purchase_return_lines {
|
|
id bigint [pk, increment]
|
|
purchase_return_id bigint [not null]
|
|
inventory_lot_id bigint [not null]
|
|
qty_returned decimal(18,3) [not null]
|
|
unit_cost decimal(18,2) [not null]
|
|
notes text
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table stock_adjustments {
|
|
id bigint [pk, increment]
|
|
adjustment_no varchar [not null, unique]
|
|
inventory_lot_id bigint [not null]
|
|
adjustment_type varchar [not null]
|
|
reason_id bigint [not null]
|
|
qty_before decimal(18,3) [not null]
|
|
qty_change decimal(18,3) [not null]
|
|
qty_after decimal(18,3) [not null]
|
|
cost_impact decimal(18,2) [not null, default: 0]
|
|
adjustment_date timestamp [not null]
|
|
notes text
|
|
created_by bigint [not null]
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Table lot_labels {
|
|
id bigint [pk, increment]
|
|
inventory_lot_id bigint [not null]
|
|
label_type varchar [not null]
|
|
label_value varchar [not null]
|
|
printed_at timestamp
|
|
printed_by bigint
|
|
print_count int [not null, default: 0]
|
|
status varchar [not null, default: 'ACTIVE']
|
|
created_at timestamp [not null]
|
|
updated_at timestamp [not null]
|
|
}
|
|
|
|
Ref: users.role_id > roles.id
|
|
Ref: warehouse_locations.warehouse_id > warehouses.id
|
|
Ref: purchases.supplier_id > suppliers.id
|
|
Ref: purchases.created_by > users.id
|
|
Ref: purchase_lines.purchase_id > purchases.id
|
|
Ref: purchase_lines.item_type_id > item_types.id
|
|
Ref: purchase_lines.item_grade_id > item_grades.id
|
|
Ref: purchase_lines.unit_id > units.id
|
|
Ref: receipts.purchase_id > purchases.id
|
|
Ref: receipts.supplier_id > suppliers.id
|
|
Ref: receipts.received_by > users.id
|
|
Ref: receipt_lines.receipt_id > receipts.id
|
|
Ref: receipt_lines.purchase_line_id > purchase_lines.id
|
|
Ref: receipt_lines.item_type_id > item_types.id
|
|
Ref: receipt_lines.item_grade_id > item_grades.id
|
|
Ref: receipt_lines.unit_id > units.id
|
|
Ref: inventory_lots.parent_lot_id > inventory_lots.id
|
|
Ref: inventory_lots.supplier_id > suppliers.id
|
|
Ref: inventory_lots.purchase_id > purchases.id
|
|
Ref: inventory_lots.purchase_line_id > purchase_lines.id
|
|
Ref: inventory_lots.receipt_id > receipts.id
|
|
Ref: inventory_lots.receipt_line_id > receipt_lines.id
|
|
Ref: inventory_lots.item_type_id > item_types.id
|
|
Ref: inventory_lots.item_grade_id > item_grades.id
|
|
Ref: inventory_lots.warehouse_id > warehouses.id
|
|
Ref: inventory_lots.warehouse_location_id > warehouse_locations.id
|
|
Ref: inventory_lots.unit_id > units.id
|
|
Ref: sorting_sessions.source_lot_id > inventory_lots.id
|
|
Ref: sorting_sessions.sorted_by > users.id
|
|
Ref: sorting_results.sorting_session_id > sorting_sessions.id
|
|
Ref: sorting_results.result_lot_id > inventory_lots.id
|
|
Ref: sorting_results.item_type_id > item_types.id
|
|
Ref: sorting_results.item_grade_id > item_grades.id
|
|
Ref: sales.customer_id > customers.id
|
|
Ref: sales.created_by > users.id
|
|
Ref: sales_lines.sales_id > sales.id
|
|
Ref: sales_lines.item_type_id > item_types.id
|
|
Ref: sales_lines.item_grade_id > item_grades.id
|
|
Ref: sales_lines.unit_id > units.id
|
|
Ref: sales_allocations.sales_line_id > sales_lines.id
|
|
Ref: sales_allocations.inventory_lot_id > inventory_lots.id
|
|
Ref: sales_allocations.allocated_by > users.id
|
|
Ref: inventory_movements.inventory_lot_id > inventory_lots.id
|
|
Ref: inventory_movements.related_lot_id > inventory_lots.id
|
|
Ref: inventory_movements.warehouse_id > warehouses.id
|
|
Ref: inventory_movements.warehouse_location_id > warehouse_locations.id
|
|
Ref: inventory_movements.reason_id > adjustment_reasons.id
|
|
Ref: inventory_movements.created_by > users.id
|
|
Ref: sales_returns.sales_id > sales.id
|
|
Ref: sales_returns.customer_id > customers.id
|
|
Ref: sales_return_lines.sales_return_id > sales_returns.id
|
|
Ref: sales_return_lines.sales_line_id > sales_lines.id
|
|
Ref: sales_return_lines.inventory_lot_id > inventory_lots.id
|
|
Ref: sales_return_lines.item_type_id > item_types.id
|
|
Ref: sales_return_lines.item_grade_id > item_grades.id
|
|
Ref: purchase_returns.purchase_id > purchases.id
|
|
Ref: purchase_returns.supplier_id > suppliers.id
|
|
Ref: purchase_return_lines.purchase_return_id > purchase_returns.id
|
|
Ref: purchase_return_lines.inventory_lot_id > inventory_lots.id
|
|
Ref: stock_adjustments.inventory_lot_id > inventory_lots.id
|
|
Ref: stock_adjustments.reason_id > adjustment_reasons.id
|
|
Ref: stock_adjustments.created_by > users.id
|
|
Ref: lot_labels.inventory_lot_id > inventory_lots.id
|
|
Ref: lot_labels.printed_by > users.id
|
|
</pre></body></html> |