-- 00001_init: the brick's first schema. The versioned layout (migrations/NNNNN_<name>/{up,down}.sql) with a per-brick
-- journal, prod_schema_migrations; `erp-migrate --schema schema.sql` applies the same tables today.
-- 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,
PRIMARY KEY (gtin)
);
-- 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,
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,
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,
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,
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,
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,
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)
);