dodil/erp-brick-manufacturing
DODIL ERP brick: manufacturing
src/brick_manufacturing/migrations/00001_init/up.sql
-- 00001_init: slice A (bills of materials, production orders, movements, outbox, settlements, controls).
-- Versioned layout migrations/NNNNN_<name>/{up,down}.sql with a per-brick journal, mfg_schema_migrations.
CREATE TABLE IF NOT EXISTS mfg_schema_migrations (
version VARCHAR NOT NULL,
applied_at TIMESTAMP,
PRIMARY KEY (version)
);
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_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_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_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,
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_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)
);