-- ===================================================================== -- RETAIL PHARMACY PLATFORM -- Phase 1 Schema Pack H — Reordering & Purchase Orders v1.0 -- -- Apply AFTER packs A, C, D, G. -- Target: MySQL 8.0+ / InnoDB / utf8mb4 -- -- ================================================================= -- WHAT WE CANNOT COPY, AND WHAT WE CAN DO INSTEAD -- ================================================================= -- -- The incumbent's real moat is that the chemist's distributors run -- their software too, so ordering is ERP-to-ERP and inward stock lands -- without typing. That is a network, not a feature, and no amount of -- code replicates it. -- -- What we can do is be better at deciding WHAT to order. The incumbent -- reorders against a static minimum level the chemist typed in once, -- years ago, and never revised. We have his actual sales ledger, his -- actual lead times and his actual expiry dates. -- -- THE INSIGHT WORTH BUILDING: -- never suggest more than he can sell before it expires. -- -- A chemist ordering to a fixed minimum will cheerfully buy six months -- of a slow-moving item with four months of shelf life left, and write -- off the difference. Nobody's software stops him. Ours refuses to -- suggest it, and says why. That is money saved every single month, -- and it is visible in the same expiry report that shows him the -- damage he is currently doing. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- H.1 Supplier terms per item -- -- Lead time is the number that makes reordering work, and it is the -- one nobody maintains. So it is LEARNED from the gap between order -- and receipt, with the typed value as a fallback. -- --------------------------------------------------------------------- CREATE TABLE item_supplier ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, supplier_id CHAR(26) NOT NULL, is_preferred TINYINT(1) NOT NULL DEFAULT 0, last_rate DECIMAL(14,4) NULL COMMENT 'per pack', moq_packs DECIMAL(10,3) NOT NULL DEFAULT 1 COMMENT 'Minimum order quantity', pack_multiple DECIMAL(10,3) NOT NULL DEFAULT 1 COMMENT 'Order in multiples of this', stated_lead_days SMALLINT NOT NULL DEFAULT 3, observed_lead_days SMALLINT NULL COMMENT 'Learned from order-to-receipt', deliveries_seen SMALLINT NOT NULL DEFAULT 0, last_ordered_on DATE NULL, last_received_on DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, row_ver BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_is (store_id, item_id, supplier_id), KEY ix_is_pref (store_id, item_id, is_preferred), KEY ix_is_supplier (supplier_id, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- H.2 Purchase order -- --------------------------------------------------------------------- CREATE TABLE purchase_order ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, event_id CHAR(26) NULL, po_no VARCHAR(30) NOT NULL, fy_code VARCHAR(9) NOT NULL, po_date DATE NOT NULL, supplier_id CHAR(26) NOT NULL, expected_on DATE NULL COMMENT 'po_date + lead time actually observed', status ENUM('DRAFT','SENT','PART_RECEIVED','RECEIVED','CANCELLED') NOT NULL DEFAULT 'DRAFT', line_count SMALLINT NOT NULL DEFAULT 0, est_value DECIMAL(14,2) NOT NULL DEFAULT 0, received_value DECIMAL(14,2) NOT NULL DEFAULT 0, sent_at DATETIME(3) NULL, closed_at DATETIME(3) NULL, note VARCHAR(300) NULL, created_by CHAR(26) NULL, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_po_no (store_id, fy_code, po_no), KEY ix_po_supplier (supplier_id, po_date), KEY ix_po_open (store_id, status, po_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE purchase_order_line ( id CHAR(26) NOT NULL, purchase_order_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, line_no SMALLINT NOT NULL, item_id CHAR(26) NOT NULL, item_name VARCHAR(250) NOT NULL, pack_desc VARCHAR(60) NULL, qty_packs DECIMAL(14,3) NOT NULL, qty_units DECIMAL(14,3) NOT NULL COMMENT 'packs x units_per_pack, kept for stock maths', est_rate DECIMAL(14,4) NOT NULL DEFAULT 0 COMMENT 'per pack', est_value DECIMAL(14,2) NOT NULL DEFAULT 0, received_units DECIMAL(14,3) NOT NULL DEFAULT 0, -- Why the system suggested this, in the chemist's language. Shown on -- the draft so he can disagree with the reasoning, not just the number. rationale VARCHAR(300) NULL, PRIMARY KEY (id), UNIQUE KEY uk_pol (purchase_order_id, line_no), KEY ix_pol_item (item_id), CONSTRAINT fk_pol_po FOREIGN KEY (purchase_order_id) REFERENCES purchase_order(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- H.3 Reorder suggestion — snapshotted, not recomputed -- -- Kept so we can measure ourselves: what did we suggest, what did he -- actually order, and did the item go out of stock anyway. A reorder -- engine nobody audits drifts into being ignored. -- --------------------------------------------------------------------- CREATE TABLE reorder_suggestion ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, run_date DATE NOT NULL, item_id CHAR(26) NOT NULL, supplier_id CHAR(26) NULL, -- The inputs, kept so a disagreement can be settled with evidence daily_velocity DECIMAL(12,4) NOT NULL DEFAULT 0 COMMENT 'units per day, from the sale ledger', days_observed SMALLINT NOT NULL DEFAULT 0, on_hand_units DECIMAL(14,3) NOT NULL DEFAULT 0, on_order_units DECIMAL(14,3) NOT NULL DEFAULT 0, lead_days SMALLINT NOT NULL DEFAULT 0, cover_days SMALLINT NOT NULL DEFAULT 0, days_of_stock DECIMAL(10,2) NULL COMMENT 'NULL when velocity is zero', -- The output suggested_units DECIMAL(14,3) NOT NULL DEFAULT 0, suggested_packs DECIMAL(14,3) NOT NULL DEFAULT 0, -- The expiry cap, which is the whole point expiry_capped TINYINT(1) NOT NULL DEFAULT 0, sellable_before_expiry DECIMAL(14,3) NULL, classification ENUM('REORDER','URGENT','OVERSTOCKED','DEAD','NO_MOVEMENT','EXPIRY_RISK') NOT NULL DEFAULT 'REORDER', rationale VARCHAR(300) NULL, acted_on TINYINT(1) NOT NULL DEFAULT 0, po_line_id CHAR(26) NULL, created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_rs (store_id, run_date, item_id), KEY ix_rs_class (store_id, run_date, classification), KEY ix_rs_acted (store_id, acted_on, run_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; SET FOREIGN_KEY_CHECKS = 1; -- ===================================================================== -- INVARIANTS asserted in tests: -- -- 1. Velocity comes from the sale ledger, never from a typed minimum. -- 2. A suggestion nets off stock already on hand AND already on order. -- Double-ordering is the most expensive reorder bug there is. -- 3. Nothing is suggested beyond what can be sold before the shelf -- life on offer runs out. -- 4. An item with no movement is never reordered, however low its -- stock. Zero of something nobody buys is the correct amount. -- 5. Quantities respect MOQ and pack multiples — a distributor cannot -- supply 7 strips of a 10-strip box. -- 6. Every suggestion carries a rationale a chemist can argue with. -- =====================================================================