-- ===================================================================== -- RETAIL PHARMACY PLATFORM -- Phase 3 Schema Pack A — Cross-sell & Commission v1.0 -- -- Apply AFTER Phase 2 packs E and F. -- Target: MySQL 8.0+ / InnoDB / utf8mb4 -- -- ================================================================= -- THE CONSTRAINT THAT SHAPES THIS ENTIRE MODULE -- ================================================================= -- -- A pharmacy knows what medicines a person takes. That means it knows, -- or can infer, what they are being treated for. The commercially -- obvious thing to do with that is target them — suggest a supplement -- to the diabetic, a monitor to the hypertensive. -- -- We are not going to do that, and the schema is built so we cannot -- drift into it later. -- -- Three reasons, in ascending order of how much they cost: -- -- 1. It is health data under the DPDP Act. Consent to receive a bill -- is not consent to be profiled by diagnosis. -- 2. A suggestion attached to a prescription reads as clinical advice -- whatever the disclaimer says, and neither Caresoft nor the -- software is qualified to give it. A chemist's licence is at risk -- long before ours is. -- 3. Caresoft's own clinicians already treat us as a technology -- vendor and not a participant in care. Monetising inferred -- diagnoses would be the fastest possible way to prove them right -- to have worried. -- -- SO: cross-sell rules are mined from AGGREGATE basket co-occurrence -- across the shop, never from an individual's history. The rule table -- below has no customer column, by design — there is nowhere to put -- one. The suggestion engine is handed a basket, not a person. -- -- This costs less commercially than it sounds. "People buying this -- also bought that, at this shop" is most of the lift, and it is -- defensible in a way targeting never is. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- A.1 Cross-sell category — the merchandising spine -- --------------------------------------------------------------------- CREATE TABLE cross_sell_category ( id CHAR(26) NOT NULL, code VARCHAR(30) NOT NULL, name VARCHAR(120) NOT NULL, parent_code VARCHAR(30) NULL, -- Commission is set per category, not per item, so a chemist can be -- told one number he can check rather than a thousand he cannot. commission_pct DECIMAL(7,4) NOT NULL DEFAULT 0, -- Medicine can never sit in a commissionable category. Enforced in -- code AND asserted nightly by the invariant suite. is_commissionable TINYINT(1) NOT NULL DEFAULT 1, display_order SMALLINT NOT NULL DEFAULT 0, 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_csc (code), KEY ix_csc_parent (parent_code, display_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- A.2 Basket rule — "bought together", at the SHOP level -- -- NOTE WHAT IS MISSING: there is no customer_id, no member_id, no -- prescription reference and no condition field. A rule cannot be -- about a person because there is nowhere to record one. -- -- min_baskets guards against learning from noise: two people buying -- the same two things once is a coincidence, not a rule. -- --------------------------------------------------------------------- CREATE TABLE cross_sell_rule ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NULL COMMENT 'NULL = platform-wide rule', store_id CHAR(26) NULL COMMENT 'NULL = applies to all stores of the tenant', trigger_item_id CHAR(26) NOT NULL, suggest_item_id CHAR(26) NOT NULL, -- Evidence, kept so a chemist can be shown WHY something is suggested baskets_together INT UNSIGNED NOT NULL DEFAULT 0, baskets_trigger INT UNSIGNED NOT NULL DEFAULT 0, confidence DECIMAL(7,4) NOT NULL DEFAULT 0 COMMENT 'together / trigger', lift DECIMAL(9,4) NOT NULL DEFAULT 0 COMMENT 'vs base rate of the suggestion', source ENUM('MINED','CURATED') NOT NULL DEFAULT 'MINED', window_from DATE NULL, window_to DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, suppressed_reason VARCHAR(200) NULL COMMENT 'Why a mined rule was rejected', 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_csr (store_id, trigger_item_id, suggest_item_id), KEY ix_csr_trigger (trigger_item_id, is_active, confidence), KEY ix_csr_quality (is_active, lift) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- A.3 Suggestion log — what was shown, and whether it was taken -- -- Without this the engine cannot be measured, and an unmeasured -- suggestion engine becomes noise the counter staff learn to dismiss. -- Also the evidence trail if anyone ever asks what we recommended. -- --------------------------------------------------------------------- CREATE TABLE cross_sell_event ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, surface ENUM('COUNTER','STOREFRONT') NOT NULL, trigger_item_id CHAR(26) NULL, suggest_item_id CHAR(26) NOT NULL, rule_id CHAR(26) NULL, shown_at DATETIME(3) NOT NULL, accepted TINYINT(1) NOT NULL DEFAULT 0, sale_bill_id CHAR(26) NULL, order_id CHAR(26) NULL, line_value DECIMAL(14,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY ix_cse_measure (store_id, shown_at, accepted), KEY ix_cse_rule (rule_id, accepted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- A.4 Commission ledger -- -- One row per commissionable line, accrued when the order is fulfilled. -- The chemist gets a monthly statement he can tie back to individual -- bills. If he cannot verify it line by line, he will not trust it, and -- a commission he does not trust is a partner he loses. -- --------------------------------------------------------------------- CREATE TABLE commission_entry ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, order_id CHAR(26) NULL, sale_bill_id CHAR(26) NULL, bill_no VARCHAR(30) NULL, entry_date DATE NOT NULL, item_id CHAR(26) NOT NULL, item_name VARCHAR(250) NOT NULL, item_type VARCHAR(20) NOT NULL, category_code VARCHAR(30) NULL, -- Zero on every DRUG line, always. Recorded rather than omitted so the -- chemist can see for himself that medicine carried no fee. line_value DECIMAL(14,2) NOT NULL DEFAULT 0, commissionable TINYINT(1) NOT NULL DEFAULT 0, commission_pct DECIMAL(7,4) NOT NULL DEFAULT 0, commission_amt DECIMAL(14,2) NOT NULL DEFAULT 0, settlement_ref VARCHAR(80) NULL COMMENT 'From the payment aggregator split', status ENUM('ACCRUED','SETTLED','REVERSED','DISPUTED') NOT NULL DEFAULT 'ACCRUED', reversed_by CHAR(26) NULL COMMENT 'Credit note that reversed it', created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), KEY ix_ce_period (store_id, entry_date, status), KEY ix_ce_order (order_id), KEY ix_ce_bill (sale_bill_id), KEY ix_ce_item (item_id, entry_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; SET FOREIGN_KEY_CHECKS = 1; -- ===================================================================== -- INVARIANTS asserted in tests: -- -- 1. No rule may suggest an item of type DRUG. Suggesting medicine is -- dispensing advice, and we are not qualified to give it. -- 2. No rule may be TRIGGERED by a Schedule H/H1 item. A suggestion -- attached to a prescription is a suggestion attached to a -- diagnosis, whatever the disclaimer says. -- 3. Rules are mined from aggregate baskets only. There is no column -- anywhere in this schema linking a rule to a person. -- 4. Commission is zero on every DRUG line, recorded rather than -- omitted so the chemist can verify it. -- 5. A credit note reverses the commission it earned. -- 6. The monthly statement ties to individual bills, line by line. -- =====================================================================