Manufacturing — Data model

14 tables · auto-generated from migrations-v2/*.sql — do not edit by hand.

ER diagram

%%{init: {"theme":"base","themeVariables":{"darkMode":true,"background":"#1e1e1e","primaryColor":"#2d2d30","primaryBorderColor":"#569cd6","primaryTextColor":"#d4d4d4","lineColor":"#6796c6","secondaryColor":"#3a3d41","secondaryBorderColor":"#dcdcaa","secondaryTextColor":"#d4d4d4","tertiaryColor":"#252526","tertiaryBorderColor":"#569cd6","noteBkgColor":"#3a3d41","noteTextColor":"#dcdcaa","clusterBkg":"#252526","clusterBorder":"#569cd6","titleColor":"#569cd6","attributeBackgroundColorOdd":"#252526","attributeBackgroundColorEven":"#2d2d30"},"er":{"useMaxWidth":false}}}%%
erDiagram
  MFG_BOM {
    integer id PK
    text company_id FK
    integer fg_item_id FK
    integer routing_id FK
    integer version
    text status
    numeric output_qty
    numeric standard_unit_cost
  }
  MFG_BOM_COMPONENT {
    integer id PK
    text company_id FK
    integer bom_id FK
    integer component_item_id FK
    integer uom_id FK
    integer seq
    numeric qty_per
    numeric scrap_pct
  }
  MFG_BOM_OUTPUT {
    integer id PK
    text company_id FK
    integer bom_id FK
    integer item_id FK
    integer uom_id FK
    integer seq
    text output_kind
    numeric qty_per
  }
  MFG_COST_HOOK {
    integer id PK
    text company_id FK
    integer bom_id FK
    integer work_order_id FK
    text hook_type
    numeric amount
    text payload
    integer created_at
  }
  MFG_COST_ROLLUP_SNAPSHOT {
    integer id PK
    text company_id FK
    integer bom_id FK
    integer work_order_id FK
    numeric material_cost
    numeric labor_cost
    numeric overhead_cost
    numeric variance_cost
  }
  MFG_COST_VARIANCE {
    integer id PK
    text company_id FK
    integer work_order_id FK
    integer source_doc_id FK
    numeric standard_amount
    numeric actual_amount
    numeric variance_amount
    integer created_at
  }
  MFG_ROUTING {
    integer id PK
    text company_id FK
    text ref
    integer version
    text status
    text name
    integer created_at
  }
  MFG_ROUTING_OPERATION {
    integer id PK
    text company_id FK
    integer routing_id FK
    integer work_center_id FK
    integer seq
    text name
    numeric setup_hours
    numeric run_hours_per_unit
  }
  MFG_SCRAP_RECORD {
    integer id PK
    text company_id FK
    integer work_order_id FK
    integer operation_id FK
    integer item_id FK
    integer location_id FK
    integer source_doc_id FK
    numeric qty_base
  }
  MFG_WIP_MOVEMENT {
    integer id PK
    text company_id FK
    integer work_order_id FK
    integer operation_id FK
    integer source_doc_id FK
    text movement_type
    numeric amount
    text payload
  }
  MFG_WORK_CENTER {
    integer id PK
    text company_id FK
    integer location_id FK
    text ref
    text name
    numeric cost_rate_per_hour
    numeric overhead_rate_per_hour
    integer active
  }
  MFG_WORK_ORDER {
    integer id PK
    text company_id FK
    integer fg_item_id FK
    integer bom_id FK
    integer input_location_id FK
    integer wip_location_id FK
    integer output_location_id FK
    integer scrap_location_id FK
  }
  MFG_WORK_ORDER_COMPONENT_LINE {
    integer id PK
    text company_id FK
    integer work_order_id FK
    integer bom_component_id FK
    integer component_item_id FK
    integer uom_id FK
    integer seq
    numeric qty_required
  }
  MFG_WORK_ORDER_OPERATION {
    integer id PK
    text company_id FK
    integer work_order_id FK
    integer routing_operation_id FK
    integer work_center_id FK
    integer seq
    text status
    numeric planned_setup_hours
  }
  MFG_ROUTING ||--o{ MFG_BOM : routing
  MFG_BOM ||--o{ MFG_BOM_COMPONENT : bom
  MFG_BOM ||--o{ MFG_BOM_OUTPUT : bom
  MFG_BOM ||--o{ MFG_COST_HOOK : bom
  MFG_WORK_ORDER ||--o{ MFG_COST_HOOK : work_order
  MFG_BOM ||--o{ MFG_COST_ROLLUP_SNAPSHOT : bom
  MFG_WORK_ORDER ||--o{ MFG_COST_ROLLUP_SNAPSHOT : work_order
  MFG_WORK_ORDER ||--o{ MFG_COST_VARIANCE : work_order
  MFG_ROUTING ||--o{ MFG_ROUTING_OPERATION : routing
  MFG_WORK_CENTER ||--o{ MFG_ROUTING_OPERATION : work_center
  MFG_WORK_ORDER ||--o{ MFG_SCRAP_RECORD : work_order
  MFG_WORK_ORDER_OPERATION ||--o{ MFG_SCRAP_RECORD : operation
  MFG_WORK_ORDER ||--o{ MFG_WIP_MOVEMENT : work_order
  MFG_WORK_ORDER_OPERATION ||--o{ MFG_WIP_MOVEMENT : operation
  MFG_BOM ||--o{ MFG_WORK_ORDER : bom
  MFG_WORK_ORDER ||--o{ MFG_WORK_ORDER_COMPONENT_LINE : work_order
  MFG_BOM_COMPONENT ||--o{ MFG_WORK_ORDER_COMPONENT_LINE : bom_component
  MFG_WORK_ORDER ||--o{ MFG_WORK_ORDER_OPERATION : work_order
  MFG_ROUTING_OPERATION ||--o{ MFG_WORK_ORDER_OPERATION : routing_operation
  MFG_WORK_CENTER ||--o{ MFG_WORK_ORDER_OPERATION : work_center

Cross-module references

• MFG_BOM.company_id → COMPANY (core)
• MFG_BOM.fg_item_id → ITEM (core)
• MFG_BOM_COMPONENT.company_id → COMPANY (core)
• MFG_BOM_COMPONENT.component_item_id → ITEM (core)
• MFG_BOM_COMPONENT.uom_id → UOM (core)
• MFG_BOM_OUTPUT.company_id → COMPANY (core)
• MFG_BOM_OUTPUT.item_id → ITEM (core)
• MFG_BOM_OUTPUT.uom_id → UOM (core)
• MFG_COST_HOOK.company_id → COMPANY (core)
• MFG_COST_ROLLUP_SNAPSHOT.company_id → COMPANY (core)
• MFG_COST_VARIANCE.company_id → COMPANY (core)
• MFG_COST_VARIANCE.source_doc_id → DOCUMENT (core)
• MFG_ROUTING.company_id → COMPANY (core)
• MFG_ROUTING_OPERATION.company_id → COMPANY (core)
• MFG_SCRAP_RECORD.company_id → COMPANY (core)
• MFG_SCRAP_RECORD.item_id → ITEM (core)
• MFG_SCRAP_RECORD.location_id → LOCATION (core)
• MFG_SCRAP_RECORD.source_doc_id → DOCUMENT (core)
• MFG_WIP_MOVEMENT.company_id → COMPANY (core)
• MFG_WIP_MOVEMENT.source_doc_id → DOCUMENT (core)
• MFG_WORK_CENTER.company_id → COMPANY (core)
• MFG_WORK_CENTER.location_id → LOCATION (core)
• MFG_WORK_ORDER.company_id → COMPANY (core)
• MFG_WORK_ORDER.fg_item_id → ITEM (core)
• MFG_WORK_ORDER.input_location_id → LOCATION (core)
• MFG_WORK_ORDER.wip_location_id → LOCATION (core)
• MFG_WORK_ORDER.output_location_id → LOCATION (core)
• MFG_WORK_ORDER.scrap_location_id → LOCATION (core)
• MFG_WORK_ORDER.consume_doc_id → DOCUMENT (core)
• MFG_WORK_ORDER.wip_doc_id → DOCUMENT (core)
• MFG_WORK_ORDER.produce_doc_id → DOCUMENT (core)
• MFG_WORK_ORDER.variance_doc_id → DOCUMENT (core)
• MFG_WORK_ORDER_COMPONENT_LINE.company_id → COMPANY (core)
• MFG_WORK_ORDER_COMPONENT_LINE.component_item_id → ITEM (core)
• MFG_WORK_ORDER_COMPONENT_LINE.uom_id → UOM (core)
• MFG_WORK_ORDER_OPERATION.company_id → COMPANY (core)

Data dictionary

erp_mfg_bom · 12 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
fg_item_id	integer	FK	erp_item
version	integer
status	text
output_qty	numeric
routing_id	integer	FK	erp_mfg_routing
standard_unit_cost	numeric
overhead_amount	numeric
notes	text
created_by	text
created_at	integer

erp_mfg_bom_component · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
bom_id	integer	FK	erp_mfg_bom
seq	integer
component_item_id	integer	FK	erp_item
qty_per	numeric
uom_id	integer	FK	erp_uom
scrap_pct	numeric
is_optional	integer
consumption_mode	text
created_at	integer

erp_mfg_bom_output · 10 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
bom_id	integer	FK	erp_mfg_bom
seq	integer
item_id	integer	FK	erp_item
output_kind	text
qty_per	numeric
uom_id	integer	FK	erp_uom
cost_share_pct	numeric
created_at	integer

erp_mfg_cost_hook · 8 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
bom_id	integer	FK	erp_mfg_bom
work_order_id	integer	FK	erp_mfg_work_order
hook_type	text
amount	numeric
payload	text
created_at	integer

erp_mfg_cost_rollup_snapshot · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
bom_id	integer	FK	erp_mfg_bom
work_order_id	integer	FK	erp_mfg_work_order
material_cost	numeric
labor_cost	numeric
overhead_cost	numeric
variance_cost	numeric
total_unit_cost	numeric
detail_json	text
computed_at	integer

erp_mfg_cost_variance · 8 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
work_order_id	integer	FK	erp_mfg_work_order
standard_amount	numeric
actual_amount	numeric
variance_amount	numeric
source_doc_id	integer	FK	erp_document
created_at	integer

erp_mfg_routing · 7 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
ref	text
version	integer
status	text
name	text
created_at	integer

erp_mfg_routing_operation · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
routing_id	integer	FK	erp_mfg_routing
seq	integer
name	text
work_center_id	integer	FK	erp_mfg_work_center
setup_hours	numeric
run_hours_per_unit	numeric
created_at	integer

erp_mfg_scrap_record · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
work_order_id	integer	FK	erp_mfg_work_order
operation_id	integer	FK	erp_mfg_work_order_operation
item_id	integer	FK	erp_item
qty_base	numeric
location_id	integer	FK	erp_location
reason_code	text
value_amount	numeric
source_doc_id	integer	FK	erp_document
created_at	integer

erp_mfg_wip_movement · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
work_order_id	integer	FK	erp_mfg_work_order
operation_id	integer	FK	erp_mfg_work_order_operation
movement_type	text
amount	numeric
source_doc_id	integer	FK	erp_document
payload	text
created_at	integer

erp_mfg_work_center · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
ref	text
name	text
location_id	integer	FK	erp_location
cost_rate_per_hour	numeric
overhead_rate_per_hour	numeric
active	integer
created_at	integer

erp_mfg_work_order · 26 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
doc_no	text
fg_item_id	integer	FK	erp_item
bom_id	integer	FK	erp_mfg_bom
qty_ordered	numeric
qty_produced	numeric
input_location_id	integer	FK	erp_location
wip_location_id	integer	FK	erp_location
output_location_id	integer	FK	erp_location
scrap_location_id	integer	FK	erp_location
overhead_amount	numeric
state	text
consume_doc_id	integer	FK	erp_document
wip_doc_id	integer	FK	erp_document
produce_doc_id	integer	FK	erp_document
variance_doc_id	integer	FK	erp_document
consumed_value	numeric
labor_cost	numeric
actual_total_cost	numeric
standard_unit_cost	numeric
variance_amount	numeric
produced_unit_cost	numeric
created_by	text
created_at	integer
updated_at	integer

erp_mfg_work_order_component_line · 14 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
work_order_id	integer	FK	erp_mfg_work_order
bom_component_id	integer	FK	erp_mfg_bom_component
seq	integer
component_item_id	integer	FK	erp_item
qty_required	numeric
uom_id	integer	FK	erp_uom
qty_consumed	numeric
unit_cost	numeric
consumed_value	numeric
state	text
created_at	integer
updated_at	integer

erp_mfg_work_order_operation · 16 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
work_order_id	integer	FK	erp_mfg_work_order
routing_operation_id	integer	FK	erp_mfg_routing_operation
seq	integer
work_center_id	integer	FK	erp_mfg_work_center
status	text
planned_setup_hours	numeric
planned_run_hours	numeric
actual_setup_hours	numeric
actual_run_hours	numeric
qty_good	numeric
qty_scrapped	numeric
labor_cost	numeric
created_at	integer
updated_at	integer