-- Tables of the product brick (module `product`). Every table prefixed `prod_`, a natural primary key each (never
-- SERIAL), a price per unit a RATE NUMERIC(19,4), creation columns last so a later column is an append. Additive only.
-- configuration rows (module.yaml config_rows), edited under the category permission.
CREATE TABLE IF NOT EXISTS prod_category (
category_code VARCHAR NOT NULL,
name VARCHAR,
parent_code VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (category_code)
);
-- Which category a product, or a category's parent, sits in FROM A DATE. Append-only: a move is a new row from tomorrow.
CREATE TABLE IF NOT EXISTS prod_category_membership (
product_or_category VARCHAR NOT NULL,
valid_from DATE NOT NULL,
category_code VARCHAR,
created_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_or_category, valid_from)
);
-- Which list a customer is priced from, per currency, FROM A DATE. Append-only: a later assignment is a new row.
CREATE TABLE IF NOT EXISTS prod_customer_price_list (
bp_id BIGINT NOT NULL,
currency VARCHAR NOT NULL,
valid_from DATE NOT NULL,
price_list_id VARCHAR,
created_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (bp_id, currency, valid_from)
);
-- A GTIN claim: one barcode, one product and unit, across the whole catalogue (a plain insert: a second claimant fails).
CREATE TABLE IF NOT EXISTS prod_gtin (
gtin VARCHAR NOT NULL,
owner VARCHAR,
unit_code VARCHAR,
created_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
kind VARCHAR,
status VARCHAR,
retired_on DATE,
PRIMARY KEY (gtin)
);
-- v5 (audit MUST-1): one row per unit naming its main GTIN: the key a racing add / make-main / retire meets on (a plain insert the first time).
CREATE TABLE IF NOT EXISTS prod_gtin_main (
owner VARCHAR NOT NULL,
unit_code VARCHAR NOT NULL,
gtin VARCHAR,
updated_by VARCHAR,
updated_at TIMESTAMP,
PRIMARY KEY (owner, unit_code)
);
-- v4 (PRD-28): a unit's weights, volume and dimensions FROM A DATE, per ONE of the unit. Append-only; the row for today is
-- replaced only while no document has copied it. A plain-INSERT claim: two writers of one (owner, unit, date) cannot both win.
CREATE TABLE IF NOT EXISTS prod_unit_measure (
owner VARCHAR NOT NULL,
unit_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
gross_weight NUMERIC(19,6),
net_weight NUMERIC(19,6),
weight_unit_code VARCHAR,
volume NUMERIC(19,6),
volume_unit_code VARCHAR,
length NUMERIC(19,6),
width NUMERIC(19,6),
height NUMERIC(19,6),
dimension_unit_code VARCHAR,
set_by VARCHAR,
set_at TIMESTAMP,
copied BOOLEAN,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (owner, unit_code, valid_from)
);
-- v4 (PRD-29, PRD-30): a unit role from a date with the rounding rule that goes with it (minimum, multiple, in rule_unit_code).
CREATE TABLE IF NOT EXISTS prod_unit_role (
owner VARCHAR NOT NULL,
role VARCHAR NOT NULL,
valid_from DATE NOT NULL,
unit_code VARCHAR,
min_quantity NUMERIC(19,6),
rounding_multiple NUMERIC(19,6),
rule_unit_code VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (owner, role, valid_from)
);
-- One product's price on one list from a date. NEVER updated in place; `status` is draft until published.
CREATE TABLE IF NOT EXISTS prod_price (
price_list_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
unit_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
unit_price NUMERIC(19,4),
valid_to DATE,
status VARCHAR,
created_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
pricing_quantity INTEGER,
PRIMARY KEY (price_list_id, product_id, unit_code, valid_from)
);
CREATE TABLE IF NOT EXISTS prod_price_list (
price_list_id VARCHAR NOT NULL,
name VARCHAR,
currency VARCHAR,
based_on VARCHAR,
prices_include_tax BOOLEAN,
rounding VARCHAR,
default_from DATE,
opened_by VARCHAR,
status VARCHAR,
retired_on DATE,
publish_notes VARCHAR,
version INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (price_list_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 prod_price_list_versions (
price_list_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP NOT NULL,
PRIMARY KEY (price_list_id, version)
);
-- A price derived from another, in the list's stated order (seq). Live only once published.
CREATE TABLE IF NOT EXISTS prod_price_rule (
price_list_id VARCHAR NOT NULL,
seq INTEGER NOT NULL,
applies_to VARCHAR,
target VARCHAR,
min_quantity NUMERIC(18,4),
valid_from DATE,
valid_to DATE,
method VARCHAR,
discount_pct NUMERIC(9,4),
fixed_price NUMERIC(19,4),
surcharge_amount NUMERIC(19,4),
base_list VARCHAR,
unit_code VARCHAR,
status VARCHAR,
created_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
pricing_quantity INTEGER,
PRIMARY KEY (price_list_id, seq)
);
CREATE TABLE IF NOT EXISTS prod_product (
product_id VARCHAR NOT NULL,
name VARCHAR,
description VARCHAR,
kind VARCHAR,
base_unit_code VARCHAR,
sales_unit_code VARCHAR,
purchase_unit_code VARCHAR,
issue_unit_code VARCHAR,
category_code VARCHAR,
tax_category VARCHAR,
billing_kind VARCHAR,
billing_plan VARCHAR,
invoicing_policy VARCHAR,
cost_amount NUMERIC(19,4),
product_template_id VARCHAR,
attribute_values VARCHAR,
status VARCHAR,
version INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
product_type_code VARCHAR,
stocked_default BOOLEAN,
commodity_code VARCHAR,
country_of_origin VARCHAR,
sellable BOOLEAN,
valuation_class VARCHAR,
required_roles VARCHAR,
PRIMARY KEY (product_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 prod_product_versions (
product_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP NOT NULL,
PRIMARY KEY (product_id, version)
);
-- What happened to a product, by whom (part of the Product aggregate: its key starts with the root's).
CREATE TABLE IF NOT EXISTS prod_product_log (
product_id VARCHAR NOT NULL,
seq INTEGER NOT NULL,
event VARCHAR,
actor VARCHAR,
detail VARCHAR,
event_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_id, seq)
);
-- A unit a product (or its template) is counted in: 1 of it = numerator / denominator of the base unit, exact. Fixed once used.
CREATE TABLE IF NOT EXISTS prod_product_unit (
owner VARCHAR NOT NULL,
unit_code VARCHAR NOT NULL,
numerator BIGINT,
denominator BIGINT,
packaging_level VARCHAR,
quantity_decimals INTEGER,
gtin VARCHAR,
einvoice_unit_code VARCHAR,
status VARCHAR,
used_at TIMESTAMP,
created_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
relative_unit_code VARCHAR,
used_ref VARCHAR,
PRIMARY KEY (owner, unit_code)
);
-- module.yaml config_rows: one row per setting (JSON value); NULL = the reference behaviour.
CREATE TABLE IF NOT EXISTS prod_settings (
setting_key VARCHAR NOT NULL,
value VARCHAR,
changes VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (setting_key)
);
-- An AI step's proposal waiting for a person, or the ManualTask when no provider is wired.
CREATE TABLE IF NOT EXISTS prod_task (
task_id VARCHAR NOT NULL,
step VARCHAR,
subject_ref VARCHAR,
status VARCHAR,
capability VARCHAR,
input_json VARCHAR,
plan VARCHAR,
reason VARCHAR,
destructive BOOLEAN,
summary VARCHAR,
output_json VARCHAR,
created_by VARCHAR,
resolved_by VARCHAR,
note VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (task_id)
);
-- What several products share (Cola in three volumes); each variant is its OWN product (a SKU). Never named on a price, document or stock row.
CREATE TABLE IF NOT EXISTS prod_template (
product_template_id VARCHAR NOT NULL,
name VARCHAR,
description VARCHAR,
kind VARCHAR,
category_code VARCHAR,
tax_category VARCHAR,
billing_kind VARCHAR,
billing_plan VARCHAR,
invoicing_policy VARCHAR,
base_unit_code VARCHAR,
attributes VARCHAR,
created_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
product_type_code VARCHAR,
commodity_code VARCHAR,
country_of_origin VARCHAR,
PRIMARY KEY (product_template_id)
);
-- configuration rows: UN/ECE Recommendation 20 unit codes, seeded.
CREATE TABLE IF NOT EXISTS prod_unit (
unit_code VARCHAR NOT NULL,
name VARCHAR,
decimals INTEGER,
kind VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (unit_code)
);
-- A conversion true for EVERY product (1 KGM = 1000 GRM): exact integers, seeded.
CREATE TABLE IF NOT EXISTS prod_unit_conversion (
from_unit_code VARCHAR NOT NULL,
to_unit_code VARCHAR NOT NULL,
numerator BIGINT,
denominator BIGINT,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (from_unit_code, to_unit_code)
);
-- The Odoo id map (shared.yaml conventions.odoo_migration.id_map): what an Odoo product.product / uom.uom became here,
-- read by later bricks through GET /migration-map/{db}/{model}?ids= (added in the L2C wiring pass, migration 00002).
CREATE TABLE IF NOT EXISTS prod_migration_map (
odoo_db VARCHAR NOT NULL,
odoo_model VARCHAR NOT NULL,
odoo_id VARCHAR NOT NULL,
natural_key VARCHAR,
created_at TIMESTAMP,
PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);
-- v4 slice B (PRD-35): what kind of product this is for every module. Configuration, seeded; retired, never deleted.
CREATE TABLE IF NOT EXISTS prod_product_type (
product_type_code VARCHAR NOT NULL,
name VARCHAR,
kind VARCHAR,
stocked_default BOOLEAN,
sellable BOOLEAN,
purchasable BOOLEAN,
required_roles VARCHAR,
code_format VARCHAR,
valuation_class VARCHAR,
production_fields BOOLEAN,
status VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_type_code)
);
-- A type's code series: one claim row per number, a plain INSERT in the add's own transaction (never reused, no gap).
CREATE TABLE IF NOT EXISTS prod_code_series (
product_type_code VARCHAR NOT NULL,
number INTEGER NOT NULL,
product_id VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_type_code, number)
);
-- A document or stock used this product: its type is fixed from then on.
CREATE TABLE IF NOT EXISTS prod_product_use (
product_id VARCHAR NOT NULL,
used_at TIMESTAMP,
used_ref VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_id)
);
-- v4 slice B (PRD-36): characteristics, classes, and a product's class and values FROM A DATE.
CREATE TABLE IF NOT EXISTS prod_characteristic (
characteristic_code VARCHAR NOT NULL,
name VARCHAR,
data_type VARCHAR,
unit_code VARCHAR,
allowed_values VARCHAR,
multi_value BOOLEAN,
decimals INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (characteristic_code)
);
CREATE TABLE IF NOT EXISTS prod_class (
class_code VARCHAR NOT NULL,
name VARCHAR,
characteristics VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (class_code)
);
CREATE TABLE IF NOT EXISTS prod_classification (
owner VARCHAR NOT NULL,
class_code VARCHAR NOT NULL,
characteristic_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
value VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (owner, class_code, characteristic_code, valid_from)
);
-- v4 slice B (PRD-37): how one product is procured, planned and allowed at one site, FROM A DATE after today. Base-unit quantities.
CREATE TABLE IF NOT EXISTS prod_site (
product_id VARCHAR NOT NULL,
site_id VARCHAR NOT NULL,
valid_from DATE NOT NULL,
site_status VARCHAR,
procurement_type VARCHAR,
supplying_site_id VARCHAR,
planned_delivery_days INTEGER,
gr_processing_days INTEGER,
planning_method VARCHAR,
reorder_point NUMERIC(19,4),
safety_stock NUMERIC(19,4),
max_stock NUMERIC(19,4),
lot_size_method VARCHAR,
fixed_lot_size NUMERIC(19,4),
rounding_profile_code VARCHAR,
mrp_controller VARCHAR,
planning_time_fence_days INTEGER,
lot_size_procedure VARCHAR,
production_version VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_id, site_id, valid_from)
);
-- v4 slice B (PRD-38): pallet and layer rounding for system-proposed quantities.
CREATE TABLE IF NOT EXISTS prod_rounding_profile (
rounding_profile_code VARCHAR NOT NULL,
unit_code VARCHAR,
steps VARCHAR,
status VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (rounding_profile_code)
);