dodil/erp-brick-manufacturing
DODIL ERP brick: manufacturing
src/brick_manufacturing/schema.sql
-- Every table prefixed `mfg_`, a natural primary key each (never SERIAL), money NUMERIC(18,2), rates and quantities NUMERIC(19,4),
-- creation columns last so a later column is an append. Additive only.
CREATE TABLE IF NOT EXISTS mfg_ai_finding (
production_order_id VARCHAR NOT NULL,
finding_no INTEGER NOT NULL,
step_key VARCHAR,
model VARCHAR,
inputs_digest VARCHAR,
finding_json VARCHAR,
decision VARCHAR,
requested_by VARCHAR,
requested_sub VARCHAR,
decided_by VARCHAR,
decided_sub VARCHAR,
decided_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id, finding_no)
);
CREATE TABLE IF NOT EXISTS mfg_bom (
bom_id VARCHAR NOT NULL,
product_id VARCHAR,
usage VARCHAR,
alternative VARCHAR,
company_code VARCHAR,
row_version INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (bom_id)
);
-- Optimistic versions (erp_platform.repository): a save CLAIMS version n+1 with a plain INSERT here,
-- in the same transaction as its rows; a racer from the same version fails 23505 -> 409.
CREATE TABLE IF NOT EXISTS mfg_bom_versions (
bom_id VARCHAR NOT NULL,
row_version INTEGER NOT NULL,
saved_at TIMESTAMP NOT NULL,
PRIMARY KEY (bom_id, row_version)
);
CREATE TABLE IF NOT EXISTS mfg_bom_line (
bom_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
line_no INTEGER NOT NULL,
kind VARCHAR,
product_id VARCHAR,
quantity NUMERIC(19,4),
unit_code VARCHAR,
quantity_base NUMERIC(19,4),
base_unit_code VARCHAR,
replaces_product_id VARCHAR,
modifier_id VARCHAR,
nrv_unit_cost NUMERIC(19,4),
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (bom_id, version, line_no)
);
CREATE TABLE IF NOT EXISTS mfg_bom_version (
bom_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
valid_from DATE,
valid_to DATE,
output_quantity NUMERIC(19,4),
output_unit_code VARCHAR,
output_quantity_base NUMERIC(19,4),
output_base_unit_code VARCHAR,
planned_yield_pct NUMERIC(9,4),
status VARCHAR,
released_by VARCHAR,
released_sub VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (bom_id, version)
);
CREATE TABLE IF NOT EXISTS mfg_claim (
claim_scope VARCHAR NOT NULL,
claim_key VARCHAR NOT NULL,
n INTEGER NOT NULL,
ref VARCHAR,
claimed_by VARCHAR,
claimed_at TIMESTAMP,
PRIMARY KEY (claim_scope, claim_key, n)
);
CREATE TABLE IF NOT EXISTS mfg_confirmation (
production_order_id VARCHAR NOT NULL,
op_no INTEGER NOT NULL,
confirmation_no INTEGER NOT NULL,
work_centre_id VARCHAR,
machine_minutes NUMERIC(19,4),
labour_minutes NUMERIC(19,4),
is_final BOOLEAN,
cost NUMERIC(18,2),
journal_id VARCHAR,
reverses_confirmation_no INTEGER,
recorded_by VARCHAR,
recorded_sub VARCHAR,
business_date DATE,
status VARCHAR,
outbox_ref VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id, op_no, confirmation_no)
);
CREATE TABLE IF NOT EXISTS mfg_cost_rollup (
rollup_id VARCHAR NOT NULL,
company_code VARCHAR,
effective_on DATE,
status VARCHAR,
ran_by VARCHAR,
ran_sub VARCHAR,
approved_by VARCHAR,
approved_sub VARCHAR,
released_on DATE,
stale_cause VARCHAR,
used_json VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (rollup_id)
);
CREATE TABLE IF NOT EXISTS mfg_cost_rollup_line (
rollup_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
bom_id VARCHAR,
bom_version INTEGER,
base_unit_code VARCHAR,
material NUMERIC(19,4),
conversion NUMERIC(19,4),
by_product_credit NUMERIC(19,4),
total NUMERIC(19,4),
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (rollup_id, product_id)
);
CREATE TABLE IF NOT EXISTS mfg_dependent_requirement (
warehouse_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
need_date DATE NOT NULL,
source_ref VARCHAR NOT NULL,
quantity NUMERIC(19,4),
unit_code VARCHAR,
source_kind VARCHAR,
run_no INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (warehouse_id, product_id, need_date, source_ref)
);
CREATE TABLE IF NOT EXISTS mfg_forecast (
warehouse_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
week_start DATE NOT NULL,
quantity NUMERIC(19,4),
unit_code VARCHAR,
entered_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (warehouse_id, product_id, week_start)
);
CREATE TABLE IF NOT EXISTS mfg_lot_genealogy (
output_product_id VARCHAR NOT NULL,
output_lot_code VARCHAR NOT NULL,
input_product_id VARCHAR NOT NULL,
input_lot_code VARCHAR NOT NULL,
production_order_id VARCHAR NOT NULL,
input_quantity NUMERIC(19,4),
output_quantity NUMERIC(19,4),
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (output_product_id, output_lot_code, input_product_id, input_lot_code, production_order_id)
);
CREATE TABLE IF NOT EXISTS mfg_migration_map (
odoo_db VARCHAR NOT NULL,
odoo_model VARCHAR NOT NULL,
odoo_id VARCHAR NOT NULL,
our_key VARCHAR,
run_id VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);
CREATE TABLE IF NOT EXISTS mfg_migration_run (
run_id VARCHAR NOT NULL,
odoo_db VARCHAR,
company_code VARCHAR,
cutover_date DATE,
status VARCHAR,
doors_json VARCHAR,
checks_json VARCHAR,
summary_json VARCHAR,
loaded_by VARCHAR,
signed_by VARCHAR,
signed_sub VARCHAR,
signed_on DATE,
discard_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (run_id)
);
CREATE TABLE IF NOT EXISTS mfg_mrp_run (
run_no INTEGER NOT NULL,
warehouse_id VARCHAR,
from_date DATE,
horizon_weeks INTEGER,
planned_count INTEGER,
stale BOOLEAN,
stale_cause VARCHAR,
started_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (run_no)
);
CREATE TABLE IF NOT EXISTS mfg_number_series (
company_code VARCHAR NOT NULL,
year INTEGER NOT NULL,
n INTEGER NOT NULL,
production_order_id VARCHAR,
claimed_at TIMESTAMP,
PRIMARY KEY (company_code, year, n)
);
CREATE TABLE IF NOT EXISTS mfg_order_movement (
move_ref VARCHAR NOT NULL,
production_order_id VARCHAR,
kind VARCHAR,
line_no INTEGER,
seq INTEGER,
product_id VARCHAR,
lot_code VARCHAR,
quantity NUMERIC(19,4),
unit_code VARCHAR,
quantity_base NUMERIC(19,4),
base_unit_code VARCHAR,
location_id VARCHAR,
over_issue BOOLEAN,
unit_cost NUMERIC(19,4),
inventory_move_id VARCHAR,
value NUMERIC(18,2),
status VARCHAR,
reject_reason VARCHAR,
recorded_by VARCHAR,
recorded_sub VARCHAR,
business_date DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (move_ref)
);
CREATE TABLE IF NOT EXISTS mfg_outbox (
target VARCHAR NOT NULL,
ref VARCHAR NOT NULL,
production_order_id VARCHAR,
payload VARCHAR,
status VARCHAR,
attempts INTEGER,
last_error VARCHAR,
sent_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (target, ref)
);
CREATE TABLE IF NOT EXISTS mfg_planned_order (
warehouse_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
need_date DATE NOT NULL,
run_no INTEGER NOT NULL,
quantity NUMERIC(19,4),
unit_code VARCHAR,
planned_order_ref VARCHAR,
status VARCHAR,
cause_json VARCHAR,
production_order_id VARCHAR,
firmed_by VARCHAR,
firmed_sub VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (warehouse_id, product_id, need_date, run_no)
);
CREATE TABLE IF NOT EXISTS mfg_production_order (
production_order_id VARCHAR NOT NULL,
company_code VARCHAR,
product_id VARCHAR,
warehouse_id VARCHAR,
planned_quantity NUMERIC(19,4),
unit_code VARCHAR,
due_on DATE,
bom_id VARCHAR,
bom_version INTEGER,
frozen_bom_json VARCHAR,
status VARCHAR,
created_by VARCHAR,
released_on DATE,
released_by VARCHAR,
shortage_warning VARCHAR,
completed_on DATE,
closed_on DATE,
cancel_reason VARCHAR,
row_version INTEGER,
routing_version INTEGER,
frozen_routing_json VARCHAR,
planned_order_ref VARCHAR,
stale_note VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id)
);
-- Optimistic versions (erp_platform.repository): a save CLAIMS version n+1 with a plain INSERT here,
-- in the same transaction as its rows; a racer from the same version fails 23505 -> 409.
CREATE TABLE IF NOT EXISTS mfg_production_order_versions (
production_order_id VARCHAR NOT NULL,
row_version INTEGER NOT NULL,
saved_at TIMESTAMP NOT NULL,
PRIMARY KEY (production_order_id, row_version)
);
CREATE TABLE IF NOT EXISTS mfg_routing (
product_id VARCHAR NOT NULL,
routing_version INTEGER NOT NULL,
valid_from DATE,
operations_json VARCHAR,
written_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_id, routing_version)
);
CREATE TABLE IF NOT EXISTS mfg_scrap (
production_order_id VARCHAR NOT NULL,
scrap_no INTEGER NOT NULL,
product_id VARCHAR,
quantity NUMERIC(19,4),
unit_code VARCHAR,
quantity_base NUMERIC(19,4),
reason_code VARCHAR,
standard_value NUMERIC(18,2),
status VARCHAR,
recorded_by VARCHAR,
recorded_sub VARCHAR,
approved_by VARCHAR,
approved_sub VARCHAR,
business_date DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id, scrap_no)
);
CREATE TABLE IF NOT EXISTS mfg_scrap_reason (
reason_code VARCHAR NOT NULL,
description VARCHAR,
abnormal BOOLEAN,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (reason_code)
);
CREATE TABLE IF NOT EXISTS mfg_settings (
setting_key VARCHAR NOT NULL,
valid_from DATE NOT NULL,
value VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (setting_key, valid_from)
);
CREATE TABLE IF NOT EXISTS mfg_settlement (
production_order_id VARCHAR NOT NULL,
settlement_no INTEGER NOT NULL,
wip_debit NUMERIC(18,2),
wip_credit NUMERIC(18,2),
standard_material NUMERIC(18,2),
abnormal_scrap NUMERIC(18,2),
material_usage_variance NUMERIC(18,2),
activity_variance NUMERIC(18,2),
journal_id BIGINT,
valuation_method VARCHAR,
settled_by VARCHAR,
settled_sub VARCHAR,
approved_by VARCHAR,
approved_sub VARCHAR,
business_date DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id, settlement_no)
);
CREATE TABLE IF NOT EXISTS mfg_variance_approval (
production_order_id VARCHAR NOT NULL,
approval_no INTEGER NOT NULL,
variance NUMERIC(18,2),
approved_by VARCHAR,
approved_sub VARCHAR,
approved_on DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id, approval_no)
);
CREATE TABLE IF NOT EXISTS mfg_work_centre (
work_centre_id VARCHAR NOT NULL,
company_code VARCHAR,
warehouse_id VARCHAR,
cost_centre VARCHAR,
batch_capacity NUMERIC(19,4),
capacity_unit_code VARCHAR,
hours_per_day NUMERIC(19,4),
status VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (work_centre_id)
);
CREATE TABLE IF NOT EXISTS mfg_work_centre_rate (
work_centre_id VARCHAR NOT NULL,
rate_kind VARCHAR NOT NULL,
valid_from DATE NOT NULL,
rate_amount NUMERIC(19,4),
currency VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (work_centre_id, rate_kind, valid_from)
);