ERD Schema Sistem Inventory Walet

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