-- ===================================================================== -- RETAIL PHARMACY PLATFORM -- Phase 2 Schema Pack E — Consumer Channel v1.0 -- -- Apply AFTER Phase 0 core and Phase 1 packs A, C, D. -- Target: MySQL 8.0+ / InnoDB / utf8mb4 -- -- ================================================================= -- THREE STRUCTURAL DECISIONS ENCODED IN THIS SCHEMA -- ================================================================= -- -- 1. ORDER-REQUEST, NOT LIVE STOCK. -- The storefront does not publish stock levels. The customer places a -- REQUEST; the chemist confirms availability; only then is a payment -- link issued. Chemist stock data is dirty in practice and a wrong -- "in stock" loses the consumer permanently. Live stock is enabled -- per store only after months of verified data quality, and that is -- a Phase 4 decision, not a Phase 2 one. -- -- 2. THE PLATFORM NEVER HOLDS CUSTOMER MONEY. -- Settlement is split at a licensed payment aggregator: funds move -- directly to the chemist and our commission is deducted at source. -- Pooling customer funds in a Caresoft account requires an RBI -- Payment Aggregator licence. There is no column in this schema for -- a platform-held balance, deliberately — you cannot accidentally -- build what has nowhere to be stored. -- -- 3. COMMISSION IS ON THE CROSS-SELL BASKET, NOT ON MEDICINE. -- Chemist margin on scheduled drugs runs ~16-20% with MRP capped -- under DPCO. A cut of that is a fifth of his margin and he will -- either refuse or take the customer offline for the refill. -- Commission applies to OTC, nutraceutical, device, FMCG and -- ayurveda lines, where margins are 25-40% and the revenue is -- genuinely incremental. See commissionable_value below. -- -- The chemist is the SELLER OF RECORD throughout. The platform is a -- technology and ordering layer. Nothing here takes title to stock. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- E.1 Storefront configuration — per store -- --------------------------------------------------------------------- CREATE TABLE storefront_config ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, display_name VARCHAR(150) NOT NULL, tagline VARCHAR(200) NULL, logo_url VARCHAR(300) NULL, theme_colour VARCHAR(7) NULL, custom_domain VARCHAR(120) NULL, -- Serviceability. An order may only route to a chemist licensed to -- supply that area; geo-fencing is a legal control, not a UX nicety. service_pincodes VARCHAR(2000) NULL COMMENT 'Comma separated', delivery_radius_km DECIMAL(5,2) NOT NULL DEFAULT 5, min_order_value DECIMAL(10,2) NOT NULL DEFAULT 0, delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0, free_delivery_above DECIMAL(10,2) NULL, -- Commercials commission_pct DECIMAL(7,4) NOT NULL DEFAULT 0 COMMENT 'Applied to commissionable lines ONLY — never to medicine', accepts_cod TINYINT(1) NOT NULL DEFAULT 1, accepts_online_pay TINYINT(1) NOT NULL DEFAULT 1, confirm_sla_minutes SMALLINT NOT NULL DEFAULT 2 COMMENT 'Target for the chemist to confirm availability', is_live TINYINT(1) NOT NULL DEFAULT 0, 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_sfc_store (store_id), UNIQUE KEY uk_sfc_domain (custom_domain), CONSTRAINT fk_sfc_store FOREIGN KEY (store_id) REFERENCES store(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.2 Online order -- --------------------------------------------------------------------- CREATE TABLE online_order ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, order_no VARCHAR(30) NOT NULL, customer_id CHAR(26) NULL, member_id CHAR(26) NULL, customer_mobile VARCHAR(15) NOT NULL, customer_name VARCHAR(150) NULL, -- Delivery target. Pincode drives the serviceability check. delivery_address VARCHAR(400) NULL, delivery_pincode VARCHAR(10) NULL, delivery_lat DECIMAL(10,7) NULL, delivery_lng DECIMAL(10,7) NULL, fulfilment ENUM('DELIVERY','PICKUP') NOT NULL DEFAULT 'DELIVERY', -- Lifecycle. Transitions are enforced in code, not by convention. status ENUM('REQUESTED','RX_PENDING','ACCEPTED','REJECTED', 'PAYMENT_PENDING','PAID','PACKED','DISPATCHED', 'DELIVERED','CANCELLED') NOT NULL DEFAULT 'REQUESTED', reject_reason VARCHAR(300) NULL, rx_id CHAR(26) NULL, requires_rx TINYINT(1) NOT NULL DEFAULT 0, -- Money. Values are provisional until the chemist confirms the basket. items_value DECIMAL(14,2) NOT NULL DEFAULT 0, delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0, order_total DECIMAL(14,2) NOT NULL DEFAULT 0, commissionable_value DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'Non-medicine lines only — the commission base', commission_amount DECIMAL(14,2) NOT NULL DEFAULT 0, pay_mode ENUM('ONLINE','COD') NOT NULL DEFAULT 'ONLINE', -- Fulfilment link. The order becomes a bill in the SAME engine and the -- SAME series as a counter sale (principle P8). sale_bill_id CHAR(26) NULL, requested_at DATETIME(3) NOT NULL, confirmed_at DATETIME(3) NULL, delivered_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_oo_no (store_id, order_no), KEY ix_oo_status (store_id, status, requested_at), KEY ix_oo_customer (customer_mobile, requested_at), KEY ix_oo_bill (sale_bill_id), CONSTRAINT fk_oo_store FOREIGN KEY (store_id) REFERENCES store(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.3 Order line -- -- qty_requested vs qty_confirmed is the whole order-request model in two -- columns. The customer asks for 2; the chemist has 1. The order does -- not fail — it is confirmed short, priced short, and the customer sees -- what he is actually paying for BEFORE the payment link is issued. -- --------------------------------------------------------------------- CREATE TABLE online_order_line ( id CHAR(26) NOT NULL, order_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, line_no SMALLINT NOT NULL, item_id CHAR(26) NULL, item_name_raw VARCHAR(250) NOT NULL COMMENT 'What the customer searched or typed', item_type VARCHAR(20) NULL COMMENT 'Copied at request time — drives commission', schedule_code VARCHAR(6) NULL, qty_requested DECIMAL(14,3) NOT NULL, qty_confirmed DECIMAL(14,3) NULL, unit_mrp DECIMAL(14,4) NULL, line_value DECIMAL(14,2) NOT NULL DEFAULT 0, is_commissionable TINYINT(1) NOT NULL DEFAULT 0, unavailable_note VARCHAR(200) NULL, PRIMARY KEY (id), UNIQUE KEY uk_ool (order_id, line_no), KEY ix_ool_item (item_id), CONSTRAINT fk_ool_order FOREIGN KEY (order_id) REFERENCES online_order(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.4 Status history — append-only, like everything else that matters. -- When a customer disputes "nobody told me it was cancelled", this -- is the answer. -- --------------------------------------------------------------------- CREATE TABLE order_status_history ( id CHAR(26) NOT NULL, order_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, from_status VARCHAR(20) NULL, to_status VARCHAR(20) NOT NULL, actor_type ENUM('CUSTOMER','CHEMIST','PHARMACIST','SYSTEM','DELIVERY') NOT NULL, actor_id CHAR(26) NULL, note VARCHAR(300) NULL, event_id CHAR(26) NULL, created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), KEY ix_osh_order (order_id, created_at), CONSTRAINT fk_osh_order FOREIGN KEY (order_id) REFERENCES online_order(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.5 Payment intent and split settlement -- -- NOTE WHAT IS ABSENT: there is no platform balance, no wallet, no -- escrow column. Funds settle from the aggregator DIRECTLY to the -- chemist's account; our commission is deducted at source and reported -- here. Building a platform-held balance would make Caresoft a payment -- aggregator under RBI rules. -- --------------------------------------------------------------------- CREATE TABLE payment_intent ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, order_id CHAR(26) NOT NULL, provider VARCHAR(30) NOT NULL COMMENT 'CASHFREE | RAZORPAY', provider_order_id VARCHAR(80) NULL, provider_txn_id VARCHAR(80) NULL, amount DECIMAL(14,2) NOT NULL, -- The split, as instructed to the aggregator vendor_amount DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'To the chemist', platform_amount DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'Commission, deducted at source', gateway_fee DECIMAL(14,2) NOT NULL DEFAULT 0, status ENUM('CREATED','PENDING','PAID','FAILED','REFUNDED','SETTLED') NOT NULL DEFAULT 'CREATED', failure_reason VARCHAR(300) NULL, settled_at DATETIME(3) NULL, settlement_ref VARCHAR(80) NULL, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_pi_order (order_id, provider), KEY ix_pi_status (store_id, status, created_at), KEY ix_pi_provider (provider_txn_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.6 Delivery assignment -- --------------------------------------------------------------------- CREATE TABLE delivery_assignment ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, order_id CHAR(26) NOT NULL, rider_user_id CHAR(26) NULL, rider_name VARCHAR(150) NULL, rider_mobile VARCHAR(15) NULL, status ENUM('ASSIGNED','PICKED','EN_ROUTE','DELIVERED','FAILED','RETURNED') NOT NULL DEFAULT 'ASSIGNED', cod_amount DECIMAL(14,2) NOT NULL DEFAULT 0, cod_collected DECIMAL(14,2) NOT NULL DEFAULT 0, cod_deposited TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Until this is true the cash is a receivable from the rider', proof_type ENUM('NONE','OTP','SIGNATURE','PHOTO') NOT NULL DEFAULT 'NONE', proof_ref VARCHAR(200) NULL, assigned_at DATETIME(3) NOT NULL, delivered_at DATETIME(3) NULL, failure_reason VARCHAR(300) NULL, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_da_order (order_id), KEY ix_da_rider (rider_user_id, status), KEY ix_da_cod (store_id, cod_deposited, status), CONSTRAINT fk_da_order FOREIGN KEY (order_id) REFERENCES online_order(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.7 Outbound message log — WhatsApp and email. -- Deduped on (order, kind) so a retry storm cannot send a customer -- nine copies of the same invoice. -- --------------------------------------------------------------------- CREATE TABLE message_outbox ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, channel ENUM('WHATSAPP','EMAIL','SMS') NOT NULL, kind VARCHAR(40) NOT NULL COMMENT 'ORDER_CONFIRMED | PAY_LINK | INVOICE | ...', ref_type VARCHAR(30) NULL, ref_id CHAR(26) NULL, to_address VARCHAR(200) NOT NULL, template_code VARCHAR(60) NULL, payload_json TEXT NULL, status ENUM('QUEUED','SENT','DELIVERED','READ','FAILED') NOT NULL DEFAULT 'QUEUED', attempts SMALLINT NOT NULL DEFAULT 0, last_error VARCHAR(300) NULL, provider_ref VARCHAR(80) NULL, queued_at DATETIME(3) NOT NULL, sent_at DATETIME(3) NULL, PRIMARY KEY (id), UNIQUE KEY uk_mo_dedupe (ref_type, ref_id, kind, channel), KEY ix_mo_queue (status, queued_at), KEY ix_mo_store (store_id, kind, queued_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; SET FOREIGN_KEY_CHECKS = 1; -- ===================================================================== -- INVARIANTS asserted in tests: -- -- 1. An order for a Schedule H/H1 item cannot reach PAID without a -- pharmacist-VERIFIED prescription. -- 2. An order cannot route to a store that does not service the -- delivery pincode. -- 3. Commission is zero on every DRUG line, always. -- 4. vendor_amount + platform_amount = amount, to the paisa. -- 5. A fulfilled order produces a bill in the SAME series as counter -- sales, and appears in the same GSTR-1. -- 6. Invalid status transitions are rejected, not silently applied. -- =====================================================================