-- brick-business-partner: the shared party master. The table names are the spec's
-- (modules/business-partner) and the deployed reference package's, kept so data already in a
-- bucket and every module that documents them stay valid. bp_id is BIGINT everywhere
-- (modules/shared.yaml): graph functions take an integer start node.
-- DataK3 does not enforce foreign keys: THIS BRICK BEING THE ONLY WRITER is the integrity.
CREATE TABLE IF NOT EXISTS business_partner (
bp_id BIGINT NOT NULL, -- derived from bp_key (blake2b, 56 bits)
bp_key VARCHAR NOT NULL, -- YOUR stable id for them, normalised lower-case
legal_name VARCHAR NOT NULL,
country VARCHAR NOT NULL, -- ISO 3166 alpha-2
tax_id VARCHAR NOT NULL, -- '' when none
domain VARCHAR NOT NULL, -- an attribute, not the key; '' when none
status VARCHAR NOT NULL, -- active | blocked | archived
created_at TIMESTAMP,
updated_at TIMESTAMP,
kind VARCHAR, -- 0.2.0: organisation | person; NULL (a row from before) = organisation
preferred_name VARCHAR, -- persons: the name they go by
work_email VARCHAR, -- persons: the WORK contact only — never a private one (BP-05)
work_phone VARCHAR,
anonymised_at TIMESTAMP, -- persons: erased in hr (BP-07); the bp_id and its joins stay
version INTEGER, -- 0.3.0: the optimistic version; NULL (a row from before) = 0
PRIMARY KEY (bp_id)
);
CREATE TABLE IF NOT EXISTS bp_role (
bp_id BIGINT NOT NULL,
role VARCHAR NOT NULL, -- customer | supplier | carrier | your own
since TIMESTAMP,
active BOOLEAN NOT NULL,
PRIMARY KEY (bp_id, role)
);
CREATE TABLE IF NOT EXISTS bp_edges (
src BIGINT NOT NULL, -- the child
dst BIGINT NOT NULL, -- the parent
rel VARCHAR NOT NULL, -- subsidiary_of | branch_of | framework_governs | works_for (a person → their company; hr only)
created_at TIMESTAMP,
PRIMARY KEY (src, dst, rel)
);
CREATE TABLE IF NOT EXISTS bp_events (
bp_id BIGINT NOT NULL,
event_id VARCHAR NOT NULL,
action VARCHAR NOT NULL,
detail VARCHAR,
actor VARCHAR NOT NULL,
event_at TIMESTAMP NOT NULL,
PRIMARY KEY (bp_id, event_id)
);
-- 0.2.0 — person partners (modules/business-partner BP-05..BP-07, spec commit 7f34d6f). Additive: the columns are at
-- the END of business_partner's CREATE above (for a new bucket) and added here for a bucket that predates them.
-- erp-migrate marks these DISRUPTIVE: reads on business_partner stall for minutes after an ADD COLUMN, so migrate the
-- test copy first and the live one at a quiet hour.
ALTER TABLE business_partner ADD COLUMN kind VARCHAR;
ALTER TABLE business_partner ADD COLUMN preferred_name VARCHAR;
ALTER TABLE business_partner ADD COLUMN work_email VARCHAR;
ALTER TABLE business_partner ADD COLUMN work_phone VARCHAR;
ALTER TABLE business_partner ADD COLUMN anonymised_at TIMESTAMP;
-- 0.3.0 — optimistic versions (erp_platform.repository; the CRM audit's lost updates, 2026-10-17). The column is at
-- the END of the CREATE above and added here for a bucket that predates it; the claim table is new. A save CLAIMS
-- version n+1 with a plain INSERT into business_partner_versions in the same transaction as its rows: a racer from
-- the same version fails 23505 -> 409 "changed since you opened it". DISRUPTIVE like 0.2.0's ALTERs.
ALTER TABLE business_partner ADD COLUMN version INTEGER;
CREATE TABLE IF NOT EXISTS business_partner_versions (
bp_id BIGINT NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (bp_id, version)
);
-- The Odoo id map (shared.yaml conventions.odoo_migration.id_map): what an Odoo res.partner became here, read by later
-- bricks through GET /migration-map/{db}/{model}?ids= (added in the L2C wiring pass).
CREATE TABLE IF NOT EXISTS bp_migration_map (
odoo_db VARCHAR NOT NULL,
odoo_model VARCHAR NOT NULL,
odoo_id VARCHAR NOT NULL,
bp_id BIGINT,
created_at TIMESTAMP,
PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);