dodil/erp-brick-sales-order
DODIL ERP brick: sales order
src/brick_sales_order/migrations/00001_init/up.sql
-- 00001_init — the brick's first schema (generated from infrastructure/tables.py).
-- The versioned layout (migrations/NNNNN_<name>/{up,down}.sql) with a per-brick journal, so_schema_migrations.
CREATE TABLE IF NOT EXISTS so_schema_migrations (
migration VARCHAR NOT NULL,
applied_at TIMESTAMP NOT NULL,
PRIMARY KEY (migration)
);
CREATE TABLE IF NOT EXISTS so_customer_policy (
bp_id BIGINT NOT NULL,
valid_from DATE NOT NULL,
po_required BOOLEAN,
deposit_pct NUMERIC(19,4),
delivery_terms VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (bp_id, valid_from)
);
CREATE TABLE IF NOT EXISTS so_delivery (
sales_order_id VARCHAR NOT NULL,
line_no INTEGER NOT NULL,
delivery_ref VARCHAR NOT NULL,
quantity NUMERIC(19,4),
kind VARCHAR,
corrects_ref VARCHAR,
delivered_on DATE,
recorded_by VARCHAR,
source VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sales_order_id, line_no, delivery_ref)
);
CREATE TABLE IF NOT EXISTS so_invoice_request (
sales_order_id VARCHAR NOT NULL,
request_no INTEGER NOT NULL,
kind VARCHAR,
lines_json VARCHAR,
invoice_id VARCHAR,
requested_at TIMESTAMP,
requested_by VARCHAR,
amount NUMERIC(18,2),
deducted NUMERIC(18,2),
status VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sales_order_id, request_no)
);
CREATE TABLE IF NOT EXISTS so_line (
sales_order_id VARCHAR NOT NULL,
line_no INTEGER NOT NULL,
product_id VARCHAR,
description VARCHAR,
unit_code VARCHAR,
quantity NUMERIC(19,4),
unit_numerator BIGINT,
unit_denominator BIGINT,
list_price NUMERIC(19,4),
unit_price NUMERIC(19,4),
discount_pct NUMERIC(19,4),
priced_on DATE,
priced_from VARCHAR,
tax_category VARCHAR,
tax_code VARCHAR,
net_amount NUMERIC(18,2),
tax_amount NUMERIC(18,2),
invoicing_policy VARCHAR,
billing_kind VARCHAR,
billing_plan VARCHAR,
quote_ref VARCHAR,
odoo_line_id VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sales_order_id, line_no)
);
CREATE TABLE IF NOT EXISTS so_migration_map (
odoo_db VARCHAR NOT NULL,
odoo_model VARCHAR NOT NULL,
odoo_id BIGINT NOT NULL,
sales_order_id VARCHAR,
line_no INTEGER,
delivery_ref VARCHAR,
migrated_at TIMESTAMP,
amount NUMERIC(18,2),
tax_amount NUMERIC(18,2),
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);
CREATE TABLE IF NOT EXISTS so_number_series (
series_id VARCHAR NOT NULL,
high_water_mark INTEGER,
version INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (series_id)
);
CREATE TABLE IF NOT EXISTS so_number_series_versions (
series_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP NOT NULL,
PRIMARY KEY (series_id, version)
);
CREATE TABLE IF NOT EXISTS so_order (
sales_order_id VARCHAR NOT NULL,
order_no VARCHAR,
bp_id BIGINT,
owner VARCHAR,
currency VARCHAR,
price_list_id VARCHAR,
lifecycle VARCHAR,
revision INTEGER,
customer_po_ref VARCHAR,
ordered_on DATE,
confirmed_at TIMESTAMP,
delivery_terms VARCHAR,
payment_terms_id VARCHAR,
deposit_pct NUMERIC(19,4),
subtotal NUMERIC(18,2),
tax_total NUMERIC(18,2),
total NUMERIC(18,2),
source_kind VARCHAR,
source_id VARCHAR,
version INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
confirmed_on DATE,
credit_override_by VARCHAR,
credit_override_reason VARCHAR,
cancel_reason VARCHAR,
PRIMARY KEY (sales_order_id)
);
CREATE TABLE IF NOT EXISTS so_order_versions (
sales_order_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP NOT NULL,
PRIMARY KEY (sales_order_id, version)
);
CREATE TABLE IF NOT EXISTS so_revision (
sales_order_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
reason VARCHAR,
changed_lines_json VARCHAR,
approved_by VARCHAR,
approved_at TIMESTAMP,
status VARCHAR,
proposed_by VARCHAR,
proposed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sales_order_id, revision)
);
CREATE TABLE IF NOT EXISTS so_send (
sales_order_id VARCHAR NOT NULL,
sent_at TIMESTAMP NOT NULL,
channel VARCHAR,
address VARCHAR,
revision INTEGER,
sent_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sales_order_id, sent_at)
);
CREATE TABLE IF NOT EXISTS so_settings (
setting_key VARCHAR NOT NULL,
valid_from DATE NOT NULL,
value VARCHAR,
set_by VARCHAR,
set_at TIMESTAMP,
changes VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (setting_key, valid_from)
);
CREATE TABLE IF NOT EXISTS so_suggestion (
sales_order_id VARCHAR NOT NULL,
step VARCHAR NOT NULL,
suggestion_no INTEGER NOT NULL,
model VARCHAR,
inputs_ref VARCHAR,
output_json VARCHAR,
decision VARCHAR,
decided_by VARCHAR,
decided_at TIMESTAMP,
decisions_json VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sales_order_id, step, suggestion_no)
);
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('order_number_format', DATE '1970-01-01', '"SO-{yyyy}-{seq:4}"', 'default', 'the order number stamped at confirmation');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('number_restarts_yearly', DATE '1970-01-01', 'true', 'default', 'whether the order number restarts each year');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('confirm_on_acceptance', DATE '1970-01-01', 'false', 'default', 'whether an accepted quote''s order is confirmed at once instead of waiting as a draft for a person (SO-01); so:settings:controls');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('amendment_needs_approval', DATE '1970-01-01', 'true', 'default', 'whether amending a confirmed order waits for so:order:approve by someone other than the amender (SO-05); so:settings:controls');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('default_deposit_pct', DATE '1970-01-01', '0', 'default', 'the deposit percentage a new order asks for; a customer''s policy overrides it (SO-09)');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('lock_done_orders', DATE '1970-01-01', 'true', 'default', 'whether a fully delivered and invoiced order locks (SO-13); so:settings:controls');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('business_timezone', DATE '1970-01-01', '"Europe/London"', 'default', 'the IANA time zone in which ''today'' is counted: no order, delivery or dated row may be dated against it; so:settings:controls');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('contract_creates_order', DATE '1970-01-01', 'false', 'default', 'where contracts are signed: false bills an active contract directly through invoicing''s contract adapter; true raises a draft order from it instead');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('ai.ai_order_from_po.mode', DATE '1970-01-01', '"off"', 'default', 'a customer''s PO by email becomes a checked draft in seconds instead of ten minutes of typing: off or suggest; so:settings:controls');
INSERT INTO so_settings (setting_key, valid_from, value, set_by, changes) VALUES ('ai.ai_check_po.mode', DATE '1970-01-01', '"off"', 'default', 'a PO that quietly changes a price or a quantity is caught before confirmation, not at invoicing: off or suggest; so:settings:controls');