dodil/erp-brick-inventory
DODIL ERP brick: inventory
src/brick_inventory/schema.sql
-- Tables of the inventory brick (module `inventory`, spec v3). Every table prefixed `inv_`, a natural primary key each
-- (never SERIAL), quantities NUMERIC(19,4) in the product's base unit, money NUMERIC(18,2), costs NUMERIC(19,4).
-- Generated from infrastructure/tables.py by scripts/gen_schema.py. Additive only: a new column goes at the END.
CREATE TABLE IF NOT EXISTS inv_abc_class (
warehouse_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
valid_from DATE NOT NULL,
abc_class VARCHAR,
consumption_value NUMERIC(18,2),
cumulative_share NUMERIC(19,4),
source VARCHAR,
reason VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (warehouse_id, product_id, valid_from)
);
CREATE TABLE IF NOT EXISTS inv_account_map (
company_code VARCHAR NOT NULL,
purpose VARCHAR NOT NULL,
valuation_class VARCHAR NOT NULL,
valid_from DATE NOT NULL,
account_id BIGINT,
set_by VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (company_code, purpose, valuation_class, valid_from)
);
CREATE TABLE IF NOT EXISTS inv_claim (
product_id VARCHAR NOT NULL,
location_id VARCHAR NOT NULL,
lot_code VARCHAR NOT NULL,
stock_type VARCHAR NOT NULL,
owner VARCHAR NOT NULL,
claim_no INTEGER NOT NULL,
move_id VARCHAR,
reservation_ref VARCHAR,
quantity_base NUMERIC(19,4),
on_hand_after NUMERIC(19,4),
created_at TIMESTAMP,
PRIMARY KEY (product_id, location_id, lot_code, stock_type, owner, claim_no)
);
CREATE TABLE IF NOT EXISTS inv_count (
count_id VARCHAR NOT NULL,
warehouse_id VARCHAR,
location_id VARCHAR,
scope VARCHAR,
product_ids VARCHAR,
source VARCHAR,
lifecycle VARCHAR,
started_by VARCHAR,
started_by_sub VARCHAR,
submitted_by VARCHAR,
submitted_by_sub VARCHAR,
decided_by VARCHAR,
decided_sub VARCHAR,
reason VARCHAR,
value_amount NUMERIC(18,2),
applied_on DATE,
updated_at TIMESTAMP,
created_at TIMESTAMP,
PRIMARY KEY (count_id)
);
CREATE TABLE IF NOT EXISTS inv_count_entry (
count_id VARCHAR NOT NULL,
location_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
lot_code VARCHAR NOT NULL,
stock_type VARCHAR NOT NULL,
entry_no INTEGER NOT NULL,
quantity NUMERIC(19,4),
unit_code VARCHAR,
unit_numerator BIGINT,
unit_denominator BIGINT,
quantity_base NUMERIC(19,4),
catch_quantity NUMERIC(19,4),
counted_by VARCHAR,
counted_sub VARCHAR,
counted_at TIMESTAMP,
created_at TIMESTAMP,
PRIMARY KEY (count_id, location_id, product_id, lot_code, stock_type, entry_no)
);
CREATE TABLE IF NOT EXISTS inv_count_line (
count_id VARCHAR NOT NULL,
location_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
lot_code VARCHAR NOT NULL,
stock_type VARCHAR NOT NULL,
counted_base NUMERIC(19,4),
counted_at TIMESTAMP,
counted_by VARCHAR,
counted_sub VARCHAR,
expected_base NUMERIC(19,4),
difference_base NUMERIC(19,4),
value_amount NUMERIC(18,2),
suggestion VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (count_id, location_id, product_id, lot_code, stock_type)
);
CREATE TABLE IF NOT EXISTS inv_document (
document_id VARCHAR NOT NULL,
kind VARCHAR,
warehouse_id VARCHAR,
company_code VARCHAR,
bp_id BIGINT,
source_kind VARCHAR,
source_ref VARCHAR,
purpose VARCHAR,
cost_centre VARCHAR,
rma_ref VARCHAR,
expected_arrival_on DATE,
shipped_on DATE,
pick_batch_id VARCHAR,
number VARCHAR,
lifecycle VARCHAR,
done_on DATE,
done_by VARCHAR,
requested_by_email VARCHAR,
requested_by_sub VARCHAR,
approved_by_email VARCHAR,
approved_by_sub VARCHAR,
back_order_of VARCHAR,
reason VARCHAR,
reversed BOOLEAN,
extra VARCHAR,
updated_at TIMESTAMP,
created_at TIMESTAMP,
PRIMARY KEY (document_id)
);
CREATE TABLE IF NOT EXISTS inv_document_line (
document_id VARCHAR NOT NULL,
line_no INTEGER NOT NULL,
line_kind VARCHAR,
parent_line_no INTEGER,
product_id VARCHAR,
unit_code VARCHAR,
unit_numerator BIGINT,
unit_denominator BIGINT,
quantity_expected NUMERIC(19,4),
quantity_done NUMERIC(19,4),
quantity_base NUMERIC(19,4),
source_line_ref VARCHAR,
from_location_id VARCHAR,
to_location_id VARCHAR,
lot_code VARCHAR,
stock_type VARCHAR,
owner VARCHAR,
unit_cost NUMERIC(19,4),
pricing_unit_code VARCHAR,
pricing_numerator BIGINT,
pricing_denominator BIGINT,
pricing_quantity NUMERIC(19,4),
gross_weight NUMERIC(19,4),
net_weight NUMERIC(19,4),
weight_unit_code VARCHAR,
volume NUMERIC(19,4),
volume_unit_code VARCHAR,
catch_quantity_expected NUMERIC(19,4),
value_amount NUMERIC(18,2),
note VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (document_id, line_no)
);
CREATE TABLE IF NOT EXISTS inv_expected_return (
rma_ref VARCHAR NOT NULL,
line_no INTEGER NOT NULL,
sales_order_id VARCHAR,
bp_id BIGINT,
warehouse_id VARCHAR,
product_id VARCHAR,
unit_code VARCHAR,
quantity_base NUMERIC(19,4),
original_delivery_ref VARCHAR,
serials VARCHAR,
inspect BOOLEAN,
received_base NUMERIC(19,4),
status VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (rma_ref, line_no)
);
CREATE TABLE IF NOT EXISTS inv_inspection (
document_id VARCHAR NOT NULL,
line_no INTEGER NOT NULL,
decision_no INTEGER NOT NULL,
decision VARCHAR,
quantity_base NUMERIC(19,4),
reason VARCHAR,
inspected_by_email VARCHAR,
inspected_by_sub VARCHAR,
reported_to_order BOOLEAN,
created_at TIMESTAMP,
PRIMARY KEY (document_id, line_no, decision_no)
);
CREATE TABLE IF NOT EXISTS inv_location (
location_id VARCHAR NOT NULL,
warehouse_id VARCHAR,
company_code VARCHAR,
parent_location_id VARCHAR,
kind VARCHAR,
name VARCHAR,
stock_status VARCHAR,
allowed_stock_types VARCHAR,
pick_sequence INTEGER,
max_quantity_base NUMERIC(19,4),
max_weight NUMERIC(19,4),
max_volume NUMERIC(19,4),
barcode VARCHAR,
bp_id BIGINT,
status VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (location_id)
);
CREATE TABLE IF NOT EXISTS inv_lot (
product_id VARCHAR NOT NULL,
lot_code VARCHAR NOT NULL,
kind VARCHAR,
expires_on DATE,
received_on DATE,
blocked BOOLEAN,
supplier_bp_id BIGINT,
lot_unit_code VARCHAR,
lot_numerator BIGINT,
lot_denominator BIGINT,
catch_unit_code VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (product_id, lot_code)
);
CREATE TABLE IF NOT EXISTS inv_lot_link (
product_id VARCHAR NOT NULL,
lot_code VARCHAR NOT NULL,
input_product_id VARCHAR NOT NULL,
input_lot_code VARCHAR NOT NULL,
move_ref VARCHAR NOT NULL,
input_quantity NUMERIC(19,4),
source_kind VARCHAR,
source_id VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (product_id, lot_code, input_product_id, input_lot_code, move_ref)
);
CREATE TABLE IF NOT EXISTS inv_migration_map (
odoo_db VARCHAR NOT NULL,
odoo_model VARCHAR NOT NULL,
odoo_id BIGINT NOT NULL,
our_key VARCHAR,
run_id VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);
CREATE TABLE IF NOT EXISTS inv_migration_run (
run_id VARCHAR NOT NULL,
odoo_db VARCHAR,
cutover DATE,
status VARCHAR,
signed_by VARCHAR,
signed_at TIMESTAMP,
summary VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (run_id)
);
CREATE TABLE IF NOT EXISTS inv_move (
move_id VARCHAR NOT NULL,
document_id VARCHAR,
line_no INTEGER,
product_id VARCHAR,
lot_code VARCHAR,
stock_type_from VARCHAR,
stock_type_to VARCHAR,
owner_from VARCHAR,
owner_to VARCHAR,
quantity_base NUMERIC(19,4),
unit_code VARCHAR,
quantity NUMERIC(19,4),
unit_numerator BIGINT,
unit_denominator BIGINT,
from_location_id VARCHAR,
to_location_id VARCHAR,
company_code VARCHAR,
moved_on DATE,
moved_at TIMESTAMP,
value_amount NUMERIC(18,2),
reverses_move_id VARCHAR,
move_ref VARCHAR,
by_email VARCHAR,
reason VARCHAR,
catch_quantity NUMERIC(19,4),
catch_unit_code VARCHAR,
conversion_source VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (move_id)
);
CREATE TABLE IF NOT EXISTS inv_number_series (
series_id VARCHAR NOT NULL,
number INTEGER NOT NULL,
document_id VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
PRIMARY KEY (series_id, number)
);
CREATE TABLE IF NOT EXISTS inv_odoo_history (
odoo_db VARCHAR NOT NULL,
odoo_move_line_id BIGINT NOT NULL,
reference VARCHAR,
product_id VARCHAR,
lot_code VARCHAR,
quantity_base NUMERIC(19,4),
from_location_id VARCHAR,
to_location_id VARCHAR,
done_at TIMESTAMP,
origin VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (odoo_db, odoo_move_line_id)
);
CREATE TABLE IF NOT EXISTS inv_package (
sscc VARCHAR NOT NULL,
document_id VARCHAR,
package_type_code VARCHAR,
contents VARCHAR,
gross_weight NUMERIC(19,4),
weight_unit_code VARCHAR,
packed_by VARCHAR,
packed_at TIMESTAMP,
created_at TIMESTAMP,
PRIMARY KEY (sscc)
);
CREATE TABLE IF NOT EXISTS inv_package_type (
package_type_code VARCHAR NOT NULL,
tare_weight NUMERIC(19,4),
weight_unit_code VARCHAR,
length NUMERIC(19,4),
width NUMERIC(19,4),
height NUMERIC(19,4),
dimension_unit_code VARCHAR,
max_weight NUMERIC(19,4),
created_at TIMESTAMP,
PRIMARY KEY (package_type_code)
);
CREATE TABLE IF NOT EXISTS inv_pick_batch (
pick_batch_id VARCHAR NOT NULL,
warehouse_id VARCHAR,
delivery_document_ids VARCHAR,
status VARCHAR,
created_by VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (pick_batch_id)
);
CREATE TABLE IF NOT EXISTS inv_posting (
document_id VARCHAR NOT NULL,
event VARCHAR NOT NULL,
company_code VARCHAR,
journal_id BIGINT,
status VARCHAR,
reason VARCHAR,
attempts INTEGER,
posted_at TIMESTAMP,
payload VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (document_id, event)
);
CREATE TABLE IF NOT EXISTS inv_putaway_rule (
warehouse_id VARCHAR NOT NULL,
seq INTEGER NOT NULL,
valid_from DATE NOT NULL,
applies_to VARCHAR,
target VARCHAR,
strategy VARCHAR,
target_location_id VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (warehouse_id, seq, valid_from)
);
CREATE TABLE IF NOT EXISTS inv_replenishment_proposal (
warehouse_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
business_date DATE NOT NULL,
site_row_valid_from DATE,
on_hand_base NUMERIC(19,4),
reserved_base NUMERIC(19,4),
expected_base NUMERIC(19,4),
projected_base NUMERIC(19,4),
reorder_point NUMERIC(19,4),
max_stock NUMERIC(19,4),
proposed_base NUMERIC(19,4),
rounded NUMERIC(19,4),
unit_code VARCHAR,
procurement VARCHAR,
supplying_site_id VARCHAR,
lifecycle VARCHAR,
request_ref VARCHAR,
purchase_request_id VARCHAR,
transfer_document_id VARCHAR,
decided_by VARCHAR,
reason VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (warehouse_id, product_id, business_date)
);
CREATE TABLE IF NOT EXISTS inv_reservation (
sales_order_id VARCHAR NOT NULL,
so_line_no INTEGER NOT NULL,
seq INTEGER NOT NULL,
kind VARCHAR,
warehouse_id VARCHAR,
product_id VARCHAR,
quantity_base NUMERIC(19,4),
revision INTEGER,
schedule_version INTEGER,
confirmed_on DATE,
priority INTEGER,
reason VARCHAR,
by_email VARCHAR,
reserved_at TIMESTAMP,
created_at TIMESTAMP,
PRIMARY KEY (sales_order_id, so_line_no, seq)
);
CREATE TABLE IF NOT EXISTS inv_settings (
key VARCHAR NOT NULL,
valid_from DATE NOT NULL,
value VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (key, valid_from)
);
CREATE TABLE IF NOT EXISTS inv_standard_price (
company_code VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
valid_from DATE NOT NULL,
standard_price NUMERIC(19,4),
set_by VARCHAR,
lifecycle VARCHAR,
source_ref VARCHAR,
set_sub VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (company_code, product_id, valid_from)
);
CREATE TABLE IF NOT EXISTS inv_stocked_product (
product_id VARCHAR NOT NULL,
valid_from DATE NOT NULL,
tracking VARCHAR,
removal_strategy VARCHAR,
shelf_life_days INTEGER,
set_by VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (product_id, valid_from)
);
CREATE TABLE IF NOT EXISTS inv_suggestion (
count_id VARCHAR NOT NULL,
location_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
lot_code VARCHAR NOT NULL,
step VARCHAR NOT NULL,
text VARCHAR,
decided VARCHAR,
decided_by VARCHAR,
decided_at TIMESTAMP,
created_at TIMESTAMP,
PRIMARY KEY (count_id, location_id, product_id, lot_code, step)
);
CREATE TABLE IF NOT EXISTS inv_valuation (
company_code VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
seq INTEGER NOT NULL,
kind VARCHAR,
method VARCHAR,
move_id VARCHAR,
quantity_base_change NUMERIC(19,4),
value_change NUMERIC(18,2),
quantity_after NUMERIC(19,4),
value_after NUMERIC(18,2),
average_cost_after NUMERIC(19,4),
price_difference NUMERIC(18,2),
moved_on DATE,
catch_quantity_change NUMERIC(19,4),
catch_quantity_after NUMERIC(19,4),
created_at TIMESTAMP,
PRIMARY KEY (company_code, product_id, seq)
);
CREATE TABLE IF NOT EXISTS inv_warehouse (
warehouse_id VARCHAR NOT NULL,
name VARCHAR,
company_code VARCHAR,
address VARCHAR,
status VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (warehouse_id)
);