-- ===================================================================== -- RETAIL PHARMACY PLATFORM -- Phase 0 — Core Schema v1.0 -- -- Caresoft Systems Private Limited -- Date: 27 August 2026 -- -- Target: MySQL 8.0+ / InnoDB / utf8mb4 -- Runs IDENTICALLY on the cloud node and on every store node. -- A single config value (NODE_ROLE = 'cloud' | 'store') switches -- application behaviour. The schema does not change between them. -- ===================================================================== -- ===================================================================== -- SECTION A — CONVENTIONS -- Read this before adding a single table. -- ===================================================================== -- -- A1. PRIMARY KEYS ARE ULIDs, CHAR(26). -- Rationale: rows are created on thousands of independent nodes -- (store counters) as well as in cloud. Auto-increment integers -- would collide. ULIDs are globally unique AND lexicographically -- sortable by creation time, so unlike UUIDv4 they preserve InnoDB -- index locality. They are plain ASCII, so PDO handling is trivial -- — no binary conversion anywhere in the PHP layer. -- -- PHP generator: crockford base32 of (48-bit ms timestamp + -- 80-bit random). ~15 lines, no Composer. See /lib/ulid.php. -- -- A2. MASTERS vs EVENTS. -- Tables in sections B–E and K are MASTERS: cloud-authoritative, -- replicated read-only to store nodes. A store node NEVER writes -- to these except through the provisional path (§C.7, §D.3). -- -- Tables in sections G, I, J are EVENTS/DOCUMENTS: append-only, -- written at the store node, pushed up. NEVER updated, NEVER -- deleted. A correction is a new document referencing the original. -- -- This split is the entire reason there is no merge logic in this -- system. Do not blur it. -- -- A3. NO HARD DELETES on masters. Use is_active = 0. Referential -- history must survive. -- -- A4. NUMERIC TYPES. -- Quantity DECIMAL(14,3) -- supports loose/strip fractions -- Rate / price DECIMAL(14,4) -- 4dp to survive scheme maths -- Amount DECIMAL(14,2) -- Percentage DECIMAL(7,4) -- Never FLOAT or DOUBLE anywhere in this schema. -- -- A5. EVERY TABLE carries created_at, updated_at, created_by, -- updated_by. Masters additionally carry row_ver (see §F). -- -- A6. TENANCY. Cloud holds all tenants in one schema, isolated by -- tenant_id, enforced at the query layer. A store node holds -- exactly one tenant's rows in the same tables. Same schema, -- same code, both sides. -- -- A7. TIMESTAMPS. All stored UTC. local_ts on events records the -- counter's wall clock at creation; server_ts records cloud -- receipt. Both are retained — clock skew on shop PCs is real. -- -- A8. CHARSET. utf8mb4 / utf8mb4_0900_ai_ci throughout. -- -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ===================================================================== -- SECTION B — PLATFORM & TENANCY (MASTER) -- ===================================================================== -- --------------------------------------------------------------------- -- B.1 Tenant — one chemist business (may own several stores) -- --------------------------------------------------------------------- CREATE TABLE tenant ( id CHAR(26) NOT NULL, code VARCHAR(20) NOT NULL COMMENT 'Short human code, e.g. CHM00123', business_name VARCHAR(200) NOT NULL, legal_name VARCHAR(200) NULL, gstin VARCHAR(15) NULL, pan VARCHAR(10) NULL, primary_mobile VARCHAR(15) NOT NULL, primary_email VARCHAR(150) NULL, partner_id CHAR(26) NULL COMMENT 'Channel partner who onboarded', plan_code VARCHAR(30) NOT NULL DEFAULT 'STANDARD', counters_licensed SMALLINT UNSIGNED NOT NULL DEFAULT 1, onboarded_on DATE NULL, status ENUM('TRIAL','ACTIVE','SUSPENDED','CHURNED') NOT NULL DEFAULT 'TRIAL', 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_tenant_code (code), KEY ix_tenant_partner (partner_id), KEY ix_tenant_status (status, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- B.2 Store — one licensed premises -- --------------------------------------------------------------------- CREATE TABLE store ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, code VARCHAR(20) NOT NULL, name VARCHAR(200) NOT NULL, address_line1 VARCHAR(200) NOT NULL, address_line2 VARCHAR(200) NULL, locality VARCHAR(120) NULL COMMENT 'Drives local SEO pages', city VARCHAR(100) NOT NULL, state_code CHAR(2) NOT NULL COMMENT 'GST state code', pincode VARCHAR(10) NOT NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, phone VARCHAR(15) NULL, gstin VARCHAR(15) NULL, -- Statutory: mandatory before any Schedule H sale is permitted drug_licence_20 VARCHAR(50) NULL COMMENT 'Retail DL-20', drug_licence_21 VARCHAR(50) NULL COMMENT 'Retail DL-21', drug_licence_expiry DATE NULL, pharmacist_name VARCHAR(150) NULL, pharmacist_reg_no VARCHAR(50) NULL, -- Consumer channel storefront_slug VARCHAR(80) NULL COMMENT 'Unique across platform', storefront_enabled TINYINT(1) NOT NULL DEFAULT 0, delivery_radius_km DECIMAL(5,2) NULL, opens_at TIME NULL, closes_at TIME NULL, is_24x7 TINYINT(1) NOT NULL DEFAULT 0, -- Ops bill_template_code VARCHAR(30) NOT NULL DEFAULT 'DM80_STD', fy_start_month TINYINT NOT NULL DEFAULT 4, 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_store_code (tenant_id, code), UNIQUE KEY uk_store_slug (storefront_slug), KEY ix_store_tenant (tenant_id, is_active), KEY ix_store_geo (state_code, city, locality), KEY ix_store_dl_expiry (drug_licence_expiry), CONSTRAINT fk_store_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- B.3 Counter — one billing terminal. Identity for number blocks & events. -- --------------------------------------------------------------------- CREATE TABLE counter ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, code VARCHAR(10) NOT NULL COMMENT 'C1, C2 — used as invoice prefix', name VARCHAR(80) NOT NULL, machine_finger VARCHAR(64) NULL COMMENT 'Hardware fingerprint, binds licence', printer_profile VARCHAR(40) NULL COMMENT 'See print_profile', is_primary_node TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Hosts the local MySQL for this store', last_seen_at DATETIME(3) NULL, agent_version VARCHAR(20) 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_counter_code (store_id, code), KEY ix_counter_tenant (tenant_id, is_active), KEY ix_counter_lastseen (last_seen_at), CONSTRAINT fk_counter_store FOREIGN KEY (store_id) REFERENCES store(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- B.4 App user -- --------------------------------------------------------------------- CREATE TABLE app_user ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NULL COMMENT 'NULL = platform staff', login_id VARCHAR(80) NOT NULL, full_name VARCHAR(150) NOT NULL, mobile VARCHAR(15) NULL, email VARCHAR(150) NULL, password_hash VARCHAR(255) NOT NULL COMMENT 'password_hash(), ARGON2ID', role_code VARCHAR(30) NOT NULL COMMENT 'OWNER|MANAGER|BILLER|PHARMACIST|DELIVERY|PARTNER|PLATFORM', default_store_id CHAR(26) NULL, must_change_pwd TINYINT(1) NOT NULL DEFAULT 0, failed_attempts SMALLINT NOT NULL DEFAULT 0, locked_until DATETIME(3) NULL, last_login_at DATETIME(3) 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_user_login (login_id), KEY ix_user_tenant (tenant_id, is_active), CONSTRAINT fk_user_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- B.5 User ↔ store access (a manager may cover several outlets) -- --------------------------------------------------------------------- CREATE TABLE user_store_access ( id CHAR(26) NOT NULL, user_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, row_ver BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_usa (user_id, store_id), KEY ix_usa_store (store_id), CONSTRAINT fk_usa_user FOREIGN KEY (user_id) REFERENCES app_user(id), CONSTRAINT fk_usa_store FOREIGN KEY (store_id) REFERENCES store(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- B.6 Channel partner -- --------------------------------------------------------------------- CREATE TABLE partner ( id CHAR(26) NOT NULL, code VARCHAR(20) NOT NULL, name VARCHAR(200) NOT NULL, contact_person VARCHAR(150) NULL, mobile VARCHAR(15) NOT NULL, email VARCHAR(150) NULL, city VARCHAR(100) NULL, state_code CHAR(2) NULL, commission_pct DECIMAL(7,4) NOT NULL DEFAULT 0, agreement_from DATE NULL, agreement_to 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_partner_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION C — DRUG & PRODUCT CATALOGUE (MASTER, cloud-authoritative) -- -- The global catalogue is replicated in FULL to every store node. -- At ~2.5 lakh SKUs this is roughly 60–90 MB on disk — entirely -- acceptable, and it is what makes sub-50ms offline item search -- possible. Do not attempt partial replication of the catalogue. -- ===================================================================== -- --------------------------------------------------------------------- -- C.1 Manufacturer / marketing company -- --------------------------------------------------------------------- CREATE TABLE mfr ( id CHAR(26) NOT NULL, name VARCHAR(200) NOT NULL, short_name VARCHAR(60) 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), KEY ix_mfr_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- C.2 Salt / composition — drives generic substitution and cross-sell -- --------------------------------------------------------------------- CREATE TABLE salt ( id CHAR(26) NOT NULL, name VARCHAR(250) NOT NULL, therapeutic VARCHAR(150) NULL COMMENT 'Therapeutic class', 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), KEY ix_salt_name (name(100)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- C.3 Item — the global SKU -- --------------------------------------------------------------------- CREATE TABLE item ( id CHAR(26) NOT NULL, item_code VARCHAR(30) NOT NULL COMMENT 'Platform catalogue code', name VARCHAR(250) NOT NULL, mfr_id CHAR(26) NULL, salt_id CHAR(26) NULL, item_type ENUM('DRUG','OTC','NUTRA','DEVICE','FMCG','AYURVEDA','OTHER') NOT NULL DEFAULT 'DRUG', -- Statutory classification — drives the prescription gate schedule_code ENUM('NONE','H','H1','X','G','NRX','OTC') NOT NULL DEFAULT 'NONE', is_narcotic TINYINT(1) NOT NULL DEFAULT 0, is_habit_form TINYINT(1) NOT NULL DEFAULT 0, -- Packing pack_desc VARCHAR(60) NULL COMMENT 'e.g. 10 TAB, 100 ML', units_per_pack DECIMAL(14,3) NOT NULL DEFAULT 1 COMMENT 'Enables loose/strip sale', allow_loose TINYINT(1) NOT NULL DEFAULT 1, -- Tax hsn_code VARCHAR(10) NULL, gst_pct DECIMAL(7,4) NOT NULL DEFAULT 12, cess_pct DECIMAL(7,4) NOT NULL DEFAULT 0, -- Price control is_dpco TINYINT(1) NOT NULL DEFAULT 0, dpco_ceiling DECIMAL(14,4) NULL COMMENT 'Per-pack ceiling; billing must refuse above this', ref_mrp DECIMAL(14,4) NULL COMMENT 'Reference MRP only; actual MRP is per batch', -- Storage / handling storage_cond VARCHAR(80) NULL, is_cold_chain TINYINT(1) NOT NULL DEFAULT 0, -- Consumer channel is_sellable_online TINYINT(1) NOT NULL DEFAULT 0, image_url VARCHAR(300) NULL, short_desc VARCHAR(500) 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_item_code (item_code), KEY ix_item_name (name(80)), KEY ix_item_mfr (mfr_id), KEY ix_item_salt (salt_id), KEY ix_item_sched (schedule_code), KEY ix_item_online (is_sellable_online, is_active), CONSTRAINT fk_item_mfr FOREIGN KEY (mfr_id) REFERENCES mfr(id), CONSTRAINT fk_item_salt FOREIGN KEY (salt_id) REFERENCES salt(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- C.4 Item barcodes (an item may carry several) -- --------------------------------------------------------------------- CREATE TABLE item_barcode ( id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, barcode VARCHAR(50) NOT NULL, row_ver BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_barcode (barcode), KEY ix_ibc_item (item_id), CONSTRAINT fk_ibc_item FOREIGN KEY (item_id) REFERENCES item(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- C.5 Item search index — DENORMALISED, built for the 50ms budget -- -- Rebuilt on the store node after every catalogue delta pull. -- search_key holds a lowercased, punctuation-stripped concatenation -- of name + salt + mfr + shortcode. Query pattern is a PREFIX match -- (search_key LIKE 'ab%'), which uses the index. NEVER a leading -- wildcard — that degrades to a full scan and blows the budget. -- -- MEASURED at 250,000 rows (real catalogue scale): -- 2-character prefix lookup, 0.25 ms — well inside the 50 ms budget. -- -- DESIGN NOTE. An earlier version added display_name, pack_desc and -- mfr_short to this index to make it covering. That does not work: a -- PREFIX index column cannot satisfy a covering read, so InnoDB visits -- the clustered row regardless. Measured side by side, the "covering" -- variant was 126 MB against 55 MB here, for identical query time — -- 2.3x the disk on the shop PC for no gain. Do not add columns back. -- --------------------------------------------------------------------- CREATE TABLE item_search_index ( item_id CHAR(26) NOT NULL, search_key VARCHAR(255) NOT NULL, display_name VARCHAR(250) NOT NULL, pack_desc VARCHAR(60) NULL, mfr_short VARCHAR(60) NULL, schedule_code VARCHAR(6) NULL, PRIMARY KEY (item_id), KEY ix_isi_prefix (search_key(24)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- C.6 Tenant item overrides — the ONLY per-tenant item writes allowed -- Does not alter the global row. Rack location, reorder level, etc. -- --------------------------------------------------------------------- CREATE TABLE tenant_item ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, rack_location VARCHAR(40) NULL, reorder_level DECIMAL(14,3) NOT NULL DEFAULT 0, reorder_qty DECIMAL(14,3) NOT NULL DEFAULT 0, max_level DECIMAL(14,3) NULL, local_shortcode VARCHAR(30) NULL, is_stocked TINYINT(1) NOT NULL DEFAULT 1, is_online_blocked 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_ti (store_id, item_id), KEY ix_ti_tenant (tenant_id), KEY ix_ti_reorder (store_id, is_stocked), CONSTRAINT fk_ti_store FOREIGN KEY (store_id) REFERENCES store(id), CONSTRAINT fk_ti_item FOREIGN KEY (item_id) REFERENCES item(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- C.7 Provisional item — created AT THE COUNTER when a SKU is missing -- -- Usable locally the instant it is created. Pushed to cloud as an -- event. Cloud matches it against the global catalogue and either -- merges (setting promoted_item_id) or promotes it to a new item row. -- The canonical row then replicates back down. -- -- This loop is a compounding asset: every store improves the -- catalogue for every other store. An on-premise competitor -- architecturally cannot do this. -- --------------------------------------------------------------------- CREATE TABLE item_provisional ( id CHAR(26) NOT NULL COMMENT 'ULID generated at counter', tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, name VARCHAR(250) NOT NULL, mfr_name_raw VARCHAR(200) NULL, pack_desc VARCHAR(60) NULL, units_per_pack DECIMAL(14,3) NOT NULL DEFAULT 1, hsn_code VARCHAR(10) NULL, gst_pct DECIMAL(7,4) NOT NULL DEFAULT 12, schedule_code VARCHAR(6) NOT NULL DEFAULT 'NONE', barcode VARCHAR(50) NULL, match_status ENUM('PENDING','MERGED','PROMOTED','REJECTED') NOT NULL DEFAULT 'PENDING', promoted_item_id CHAR(26) NULL, reviewed_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), KEY ix_ip_store (store_id, match_status), KEY ix_ip_status (match_status, created_at), CONSTRAINT fk_ip_store FOREIGN KEY (store_id) REFERENCES store(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION D — BUSINESS PARTNERS (MASTER) -- ===================================================================== -- --------------------------------------------------------------------- -- D.1 Supplier / distributor -- --------------------------------------------------------------------- CREATE TABLE supplier ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NULL COMMENT 'NULL = shared across tenant stores', code VARCHAR(20) NOT NULL, name VARCHAR(200) NOT NULL, gstin VARCHAR(15) NULL, dl_number VARCHAR(50) NULL, address VARCHAR(300) NULL, city VARCHAR(100) NULL, state_code CHAR(2) NULL, phone VARCHAR(15) NULL, email VARCHAR(150) NULL, credit_days SMALLINT NOT NULL DEFAULT 0, credit_limit DECIMAL(14,2) NOT NULL DEFAULT 0, opening_balance DECIMAL(14,2) 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_supplier_code (tenant_id, code), KEY ix_supplier_gstin (gstin), KEY ix_supplier_name (tenant_id, name(60)), CONSTRAINT fk_supplier_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- D.2 Customer — ONE identity across counter and storefront (P7) -- -- Deduplicated on (tenant_id, mobile). Created at the counter as an -- event; cloud resolves duplicates and returns the canonical id. -- -- DPDP: this is health data. consent_* columns are not optional and -- must be populated before any marketing message is dispatched. -- --------------------------------------------------------------------- CREATE TABLE customer ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, home_store_id CHAR(26) NULL, mobile VARCHAR(15) NOT NULL, name VARCHAR(150) NOT NULL, alt_mobile VARCHAR(15) NULL, email VARCHAR(150) NULL, dob DATE NULL, gender ENUM('M','F','O','U') NOT NULL DEFAULT 'U', address_line1 VARCHAR(200) NULL, address_line2 VARCHAR(200) NULL, locality VARCHAR(120) NULL, city VARCHAR(100) NULL, pincode VARCHAR(10) NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, -- Care context (drives refill engine). Free-text notes only; no -- diagnosis codes are stored by the platform. has_chronic_flag TINYINT(1) NOT NULL DEFAULT 0, care_notes VARCHAR(500) NULL, allergy_notes VARCHAR(500) NULL, -- Credit is_credit_customer TINYINT(1) NOT NULL DEFAULT 0, credit_limit DECIMAL(14,2) NOT NULL DEFAULT 0, credit_days SMALLINT NOT NULL DEFAULT 0, opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0, -- DPDP consent consent_txn TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Transactional messages', consent_marketing TINYINT(1) NOT NULL DEFAULT 0, consent_captured_at DATETIME(3) NULL, consent_source VARCHAR(30) NULL COMMENT 'COUNTER|STOREFRONT|WHATSAPP', erasure_requested_at DATETIME(3) 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_customer_mobile (tenant_id, mobile), KEY ix_customer_name (tenant_id, name(60)), KEY ix_customer_store (home_store_id, is_active), KEY ix_customer_chronic (tenant_id, has_chronic_flag), CONSTRAINT fk_customer_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- D.3 Customer family member — "Papa's BP medicine, Dadi's sugar" -- This is the pattern chemists actually serve. -- --------------------------------------------------------------------- CREATE TABLE customer_member ( id CHAR(26) NOT NULL, customer_id CHAR(26) NOT NULL, name VARCHAR(150) NOT NULL, relation VARCHAR(40) NULL, dob DATE NULL, gender ENUM('M','F','O','U') NOT NULL DEFAULT 'U', care_notes VARCHAR(500) 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), KEY ix_cm_customer (customer_id, is_active), CONSTRAINT fk_cm_customer FOREIGN KEY (customer_id) REFERENCES customer(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- D.4 Prescribing doctor -- --------------------------------------------------------------------- CREATE TABLE doctor ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, name VARCHAR(150) NOT NULL, reg_no VARCHAR(50) NULL, speciality VARCHAR(100) NULL, clinic_name VARCHAR(200) NULL, phone VARCHAR(15) 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), KEY ix_doctor_tenant (tenant_id, name(60)), CONSTRAINT fk_doctor_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION E — PRICING, SCHEMES & TAX (MASTER) -- ===================================================================== -- --------------------------------------------------------------------- -- E.1 Rate list header (corporate / institutional / retail) -- --------------------------------------------------------------------- CREATE TABLE rate_list ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, code VARCHAR(20) NOT NULL, name VARCHAR(150) NOT NULL, list_type ENUM('RETAIL','CORPORATE','INSTITUTIONAL','ONLINE') NOT NULL DEFAULT 'RETAIL', effective_from DATE NOT NULL, effective_to 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_rl_code (tenant_id, code), CONSTRAINT fk_rl_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.2 Rate list line -- --------------------------------------------------------------------- CREATE TABLE rate_list_item ( id CHAR(26) NOT NULL, rate_list_id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, disc_pct DECIMAL(7,4) NOT NULL DEFAULT 0, fixed_rate DECIMAL(14,4) NULL COMMENT 'Overrides MRP-less-discount when set', row_ver BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_rli (rate_list_id, item_id), KEY ix_rli_item (item_id), CONSTRAINT fk_rli_list FOREIGN KEY (rate_list_id) REFERENCES rate_list(id), CONSTRAINT fk_rli_item FOREIGN KEY (item_id) REFERENCES item(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.3 Purchase scheme (10+1 free goods and variants) -- --------------------------------------------------------------------- CREATE TABLE scheme ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, supplier_id CHAR(26) NULL, scheme_type ENUM('FREE_QTY','PCT_DISC','FLAT_DISC') NOT NULL DEFAULT 'FREE_QTY', buy_qty DECIMAL(14,3) NOT NULL DEFAULT 0, free_qty DECIMAL(14,3) NOT NULL DEFAULT 0, disc_pct DECIMAL(7,4) NOT NULL DEFAULT 0, valid_from DATE NOT NULL, valid_to 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, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), KEY ix_scheme_item (tenant_id, item_id, valid_from, valid_to), KEY ix_scheme_supplier (supplier_id), CONSTRAINT fk_scheme_item FOREIGN KEY (item_id) REFERENCES item(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- E.4 GST rate history — a slab change must not rewrite old invoices -- --------------------------------------------------------------------- CREATE TABLE gst_rate ( id CHAR(26) NOT NULL, hsn_code VARCHAR(10) NOT NULL, gst_pct DECIMAL(7,4) NOT NULL, cess_pct DECIMAL(7,4) NOT NULL DEFAULT 0, effective_from DATE NOT NULL, effective_to DATE NULL, row_ver BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), KEY ix_gst_hsn (hsn_code, effective_from) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION F — REPLICATION & NUMBERING -- This section is the "no sync issues" machinery. It is small on -- purpose. There is no merge algorithm here because there is nothing -- to merge. -- ===================================================================== -- --------------------------------------------------------------------- -- F.1 Master change log — CDC-lite, gives a single global ordering -- -- Cloud writes one row here on every master INSERT/UPDATE (from the -- application layer, not triggers — triggers hide behaviour from the -- team). Store nodes pull WHERE seq > their cursor. That is the -- entire downward protocol. -- --------------------------------------------------------------------- CREATE TABLE repl_change_log ( seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, table_name VARCHAR(64) NOT NULL, row_id CHAR(26) NOT NULL, op ENUM('I','U','D') NOT NULL, tenant_id CHAR(26) NULL COMMENT 'NULL = global row, goes to every node', store_id CHAR(26) NULL COMMENT 'NULL = all stores of the tenant', changed_at DATETIME(3) NOT NULL, PRIMARY KEY (seq), KEY ix_rcl_scope (tenant_id, store_id, seq), KEY ix_rcl_table (table_name, seq) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- F.2 Node replication cursor -- --------------------------------------------------------------------- CREATE TABLE repl_cursor ( id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, counter_id CHAR(26) NULL, down_cursor_seq BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Last repl_change_log.seq applied', up_last_event_seq BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Last local event acknowledged by cloud', last_sync_at DATETIME(3) NULL, last_error VARCHAR(500) NULL, consecutive_fails SMALLINT NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_rc (store_id, counter_id), KEY ix_rc_lastsync (last_sync_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- F.3 Document number blocks — allocated by cloud, consumed offline -- -- Cloud issues blocks of 500. The agent requests a refill when the -- remaining count falls below 100. A counter that exhausts its block -- while offline falls back to a counter-prefixed local series, which -- is flagged here for reconciliation. -- --------------------------------------------------------------------- CREATE TABLE number_block ( id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, counter_id CHAR(26) NOT NULL, doc_type VARCHAR(20) NOT NULL COMMENT 'SALE|SALE_RET|PURCH|RECEIPT|ORDER', fy_code VARCHAR(9) NOT NULL COMMENT 'e.g. 2026-27', series_prefix VARCHAR(10) NOT NULL, block_from BIGINT UNSIGNED NOT NULL, block_to BIGINT UNSIGNED NOT NULL, next_number BIGINT UNSIGNED NOT NULL, is_fallback TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = locally minted, needs reconciliation', is_exhausted TINYINT(1) NOT NULL DEFAULT 0, issued_at DATETIME(3) NOT NULL, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), KEY ix_nb_lookup (counter_id, doc_type, fy_code, is_exhausted), KEY ix_nb_fallback (is_fallback, store_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION G — EVENT LOG (APPEND-ONLY. THE HEART OF THE DESIGN.) -- -- RULES, enforced at code review: -- * INSERT only. No UPDATE statement may target event_log. -- * No DELETE, ever, on any node. -- * A correction is a NEW event referencing the original. -- * Events are idempotent by construction: replaying the same -- event id is a no-op. This is why at-least-once delivery is -- sufficient and why there is no conflict resolution code. -- ===================================================================== -- --------------------------------------------------------------------- -- G.1 Event log -- --------------------------------------------------------------------- CREATE TABLE event_log ( id CHAR(26) NOT NULL COMMENT 'ULID minted at origin node', tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, counter_id CHAR(26) NULL COMMENT 'NULL for cloud-origin events', local_seq BIGINT UNSIGNED NOT NULL COMMENT 'Monotonic per counter', event_type VARCHAR(40) NOT NULL, entity_type VARCHAR(40) NULL, entity_id CHAR(26) NULL, corrects_id CHAR(26) NULL COMMENT 'Event this one reverses/corrects', payload JSON NOT NULL, schema_ver SMALLINT NOT NULL DEFAULT 1, origin ENUM('COUNTER','CLOUD','STOREFRONT','API') NOT NULL DEFAULT 'COUNTER', local_ts DATETIME(3) NOT NULL COMMENT 'Counter wall clock — may be skewed', server_ts DATETIME(3) NULL COMMENT 'Cloud receipt time', user_id CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_event_seq (counter_id, local_seq), KEY ix_event_store_time (store_id, local_ts), KEY ix_event_type (event_type, local_ts), KEY ix_event_entity (entity_type, entity_id), KEY ix_event_unsynced (server_ts) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Event type vocabulary (v1). Extend by adding, never by redefining. -- sale_bill sale_return credit_note -- receipt payment_made debit_note -- purchase_invoice purchase_return -- stock_movement stock_adjustment physical_verification -- customer_registered customer_amended consent_captured -- item_provisional_created -- order_accepted order_rejected order_packed -- order_dispatched order_delivered order_cancelled -- rx_uploaded rx_verified rx_rejected -- day_close counter_opened counter_closed -- --------------------------------------------------------------------- -- G.2 Outbox — store node only. Drained by the sync daemon. -- --------------------------------------------------------------------- CREATE TABLE event_outbox ( event_id CHAR(26) NOT NULL, attempts SMALLINT NOT NULL DEFAULT 0, last_attempt DATETIME(3) NULL, last_error VARCHAR(500) NULL, queued_at DATETIME(3) NOT NULL, PRIMARY KEY (event_id), KEY ix_outbox_queue (queued_at), KEY ix_outbox_stuck (attempts, last_attempt) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION H — STOCK -- -- stock_ledger is the append-only record of truth. -- stock_balance is a DERIVED cache, rebuildable from the ledger at -- any time. If they ever disagree, the ledger wins and the balance -- is rebuilt. This is what makes "my stock doesn't match" a -- self-healing condition rather than a support call. -- ===================================================================== -- --------------------------------------------------------------------- -- H.1 Batch -- --------------------------------------------------------------------- CREATE TABLE batch ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, batch_no VARCHAR(50) NOT NULL, expiry_date DATE NULL, mrp DECIMAL(14,4) NOT NULL, -- PER PACK, like mrp. Stock quantities are in BASE UNITS (rule A4/H), -- so any valuation must divide by item.units_per_pack. Multiplying -- closing_qty by this value directly overstates stock by the pack -- size — 15x on a strip of 15. This comment exists because that bug -- shipped into three separate modules before it was caught. purchase_rate DECIMAL(14,4) NOT NULL DEFAULT 0 COMMENT 'per pack', sale_rate DECIMAL(14,4) NOT NULL DEFAULT 0 COMMENT 'per pack', supplier_id CHAR(26) NULL, first_received DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, created_by CHAR(26) NULL, updated_by CHAR(26) NULL, PRIMARY KEY (id), UNIQUE KEY uk_batch (store_id, item_id, batch_no, mrp), KEY ix_batch_expiry (store_id, expiry_date), KEY ix_batch_item (store_id, item_id, is_active), CONSTRAINT fk_batch_item FOREIGN KEY (item_id) REFERENCES item(id), CONSTRAINT fk_batch_store FOREIGN KEY (store_id) REFERENCES store(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- H.2 Stock ledger — APPEND-ONLY -- --------------------------------------------------------------------- CREATE TABLE stock_ledger ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, batch_id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, event_id CHAR(26) NOT NULL COMMENT 'Originating event_log row', txn_type VARCHAR(20) NOT NULL COMMENT 'PURCHASE|SALE|SALE_RET|PURCH_RET|ADJ|EXPIRY|BREAKAGE|OPENING|TRANSFER', direction ENUM('IN','OUT') NOT NULL, qty DECIMAL(14,3) NOT NULL COMMENT 'Always positive; direction carries the sign', free_qty DECIMAL(14,3) NOT NULL DEFAULT 0, rate DECIMAL(14,4) NOT NULL DEFAULT 0, doc_id CHAR(26) NULL, doc_no VARCHAR(30) NULL, txn_date DATE NOT NULL, created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), KEY ix_sl_batch (batch_id, txn_date), KEY ix_sl_item (store_id, item_id, txn_date), KEY ix_sl_doc (doc_id), KEY ix_sl_event (event_id), CONSTRAINT fk_sl_batch FOREIGN KEY (batch_id) REFERENCES batch(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- H.3 Stock balance — DERIVED CACHE. Rebuildable. Never authoritative. -- -- Negative closing_qty is PERMITTED. Two offline counters can both -- sell the last strip. This is accepted behaviour, flagged for -- reconciliation, and deliberately not prevented by distributed -- locking — see SOW §4.4. -- --------------------------------------------------------------------- CREATE TABLE stock_balance ( store_id CHAR(26) NOT NULL, batch_id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, closing_qty DECIMAL(14,3) NOT NULL DEFAULT 0, last_txn_at DATETIME(3) NULL, rebuilt_at DATETIME(3) NULL, PRIMARY KEY (store_id, batch_id), KEY ix_sb_item (store_id, item_id, closing_qty), KEY ix_sb_negative (store_id, closing_qty) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION I — SALE DOCUMENTS (APPEND-ONLY) -- -- ONE series across counter and storefront (P8). channel tells you -- where it came from; it does NOT create a separate numbering series, -- a separate ledger, or a separate GST return. -- ===================================================================== -- --------------------------------------------------------------------- -- I.1 Sale bill header -- --------------------------------------------------------------------- CREATE TABLE sale_bill ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, counter_id CHAR(26) NULL, event_id CHAR(26) NOT NULL, bill_no VARCHAR(30) NOT NULL, bill_series VARCHAR(10) NOT NULL, fy_code VARCHAR(9) NOT NULL, bill_date DATE NOT NULL, bill_ts DATETIME(3) NOT NULL, channel ENUM('COUNTER','ONLINE') NOT NULL DEFAULT 'COUNTER', order_id CHAR(26) NULL COMMENT 'Set when channel = ONLINE', customer_id CHAR(26) NULL, member_id CHAR(26) NULL, doctor_id CHAR(26) NULL, rx_id CHAR(26) NULL COMMENT 'Mandatory when any line is Schedule H/H1', -- Amounts gross_amount DECIMAL(14,2) NOT NULL DEFAULT 0, disc_amount DECIMAL(14,2) NOT NULL DEFAULT 0, taxable_amount DECIMAL(14,2) NOT NULL DEFAULT 0, cgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, sgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, igst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, cess_amount DECIMAL(14,2) NOT NULL DEFAULT 0, round_off DECIMAL(14,2) NOT NULL DEFAULT 0, net_amount DECIMAL(14,2) NOT NULL DEFAULT 0, paid_amount DECIMAL(14,2) NOT NULL DEFAULT 0, balance_amount DECIMAL(14,2) NOT NULL DEFAULT 0, -- Status: a bill is never edited. Cancellation is a flag plus a -- reversing credit note; the row itself is immutable. is_cancelled TINYINT(1) NOT NULL DEFAULT 0, cancel_event_id CHAR(26) NULL, printed_count SMALLINT NOT NULL DEFAULT 0, user_id CHAR(26) NULL, created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_sale_no (store_id, fy_code, bill_series, bill_no), KEY ix_sale_date (store_id, bill_date), KEY ix_sale_customer (customer_id, bill_date), KEY ix_sale_channel (store_id, channel, bill_date), KEY ix_sale_order (order_id), CONSTRAINT fk_sale_store FOREIGN KEY (store_id) REFERENCES store(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- I.2 Sale bill line -- --------------------------------------------------------------------- CREATE TABLE sale_bill_line ( id CHAR(26) NOT NULL, sale_bill_id CHAR(26) NOT NULL, line_no SMALLINT NOT NULL, item_id CHAR(26) NOT NULL, batch_id CHAR(26) NOT NULL, -- Snapshot at sale time. Never re-derived from masters later; a -- catalogue change must not alter a historical invoice. item_name VARCHAR(250) NOT NULL, batch_no VARCHAR(50) NOT NULL, expiry_date DATE NULL, pack_desc VARCHAR(60) NULL, hsn_code VARCHAR(10) NULL, schedule_code VARCHAR(6) NULL, qty DECIMAL(14,3) NOT NULL, free_qty DECIMAL(14,3) NOT NULL DEFAULT 0, is_loose TINYINT(1) NOT NULL DEFAULT 0, mrp DECIMAL(14,4) NOT NULL, rate DECIMAL(14,4) NOT NULL, disc_pct DECIMAL(7,4) NOT NULL DEFAULT 0, disc_amount DECIMAL(14,2) NOT NULL DEFAULT 0, taxable_amount DECIMAL(14,2) NOT NULL DEFAULT 0, gst_pct DECIMAL(7,4) NOT NULL DEFAULT 0, cgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, sgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, igst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, cess_amount DECIMAL(14,2) NOT NULL DEFAULT 0, line_amount DECIMAL(14,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_sbl (sale_bill_id, line_no), KEY ix_sbl_item (item_id), KEY ix_sbl_batch (batch_id), CONSTRAINT fk_sbl_bill FOREIGN KEY (sale_bill_id) REFERENCES sale_bill(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- I.3 Sale payment (a bill may split across modes) -- --------------------------------------------------------------------- CREATE TABLE sale_payment ( id CHAR(26) NOT NULL, sale_bill_id CHAR(26) NOT NULL, mode ENUM('CASH','CARD','UPI','CREDIT','WALLET','ONLINE') NOT NULL, amount DECIMAL(14,2) NOT NULL, ref_no VARCHAR(60) NULL, pg_txn_id VARCHAR(80) NULL COMMENT 'Payment aggregator reference', created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), KEY ix_sp_bill (sale_bill_id), KEY ix_sp_mode (mode, created_at), CONSTRAINT fk_sp_bill FOREIGN KEY (sale_bill_id) REFERENCES sale_bill(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- I.4 Prescription — the Schedule H/H1 gate -- A sale_bill carrying any H/H1 line MUST reference a verified rx. -- Enforced in application logic AND asserted by a nightly audit job. -- --------------------------------------------------------------------- CREATE TABLE prescription ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, customer_id CHAR(26) NULL, member_id CHAR(26) NULL, doctor_id CHAR(26) NULL, doctor_name_raw VARCHAR(150) NULL, rx_date DATE NULL, file_path VARCHAR(300) NULL, source ENUM('COUNTER','STOREFRONT','WHATSAPP') NOT NULL DEFAULT 'COUNTER', verify_status ENUM('PENDING','VERIFIED','REJECTED') NOT NULL DEFAULT 'PENDING', verified_by CHAR(26) NULL COMMENT 'Registered pharmacist user', verified_at DATETIME(3) NULL, reject_reason VARCHAR(300) NULL, retain_until DATE NULL COMMENT 'Statutory retention', created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), KEY ix_rx_store (store_id, verify_status, created_at), KEY ix_rx_customer (customer_id, rx_date), CONSTRAINT fk_rx_store FOREIGN KEY (store_id) REFERENCES store(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION J — PURCHASE DOCUMENTS (APPEND-ONLY) -- -- This is the highest-leverage module for support cost. Every field -- below exists so that a purchase can be posted WITHOUT typing. -- ===================================================================== -- --------------------------------------------------------------------- -- J.1 Purchase invoice header -- --------------------------------------------------------------------- CREATE TABLE purchase_invoice ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, event_id CHAR(26) NOT NULL, supplier_id CHAR(26) NOT NULL, supplier_inv_no VARCHAR(50) NOT NULL, supplier_inv_dt DATE NOT NULL, received_date DATE NOT NULL, -- Automation provenance: how did this get in? source ENUM('MANUAL','EINVOICE','GSTR2B','PDF_PARSE','CSV') NOT NULL DEFAULT 'MANUAL', irn VARCHAR(64) NULL COMMENT 'E-invoice reference number', source_ref VARCHAR(120) NULL, match_status ENUM('UNMATCHED','MATCHED','PARTIAL','DISPUTED') NOT NULL DEFAULT 'UNMATCHED' COMMENT 'Against GSTR-2B', gross_amount DECIMAL(14,2) NOT NULL DEFAULT 0, disc_amount DECIMAL(14,2) NOT NULL DEFAULT 0, taxable_amount DECIMAL(14,2) NOT NULL DEFAULT 0, cgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, sgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, igst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, cess_amount DECIMAL(14,2) NOT NULL DEFAULT 0, round_off DECIMAL(14,2) NOT NULL DEFAULT 0, net_amount DECIMAL(14,2) NOT NULL DEFAULT 0, is_cancelled TINYINT(1) NOT NULL DEFAULT 0, user_id CHAR(26) NULL, created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_pi (store_id, supplier_id, supplier_inv_no, supplier_inv_dt), KEY ix_pi_date (store_id, supplier_inv_dt), KEY ix_pi_supplier (supplier_id, supplier_inv_dt), KEY ix_pi_match (store_id, match_status), KEY ix_pi_irn (irn), CONSTRAINT fk_pi_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- J.2 Purchase invoice line -- --------------------------------------------------------------------- CREATE TABLE purchase_invoice_line ( id CHAR(26) NOT NULL, purchase_inv_id CHAR(26) NOT NULL, line_no SMALLINT NOT NULL, item_id CHAR(26) NULL COMMENT 'NULL until matched to catalogue', provisional_id CHAR(26) NULL COMMENT 'Set when the SKU was unknown', batch_id CHAR(26) NULL, -- Raw, exactly as received from the source document raw_item_name VARCHAR(250) NOT NULL, raw_pack VARCHAR(60) NULL, raw_hsn VARCHAR(10) NULL, batch_no VARCHAR(50) NOT NULL, expiry_date DATE NULL, qty DECIMAL(14,3) NOT NULL, free_qty DECIMAL(14,3) NOT NULL DEFAULT 0, mrp DECIMAL(14,4) NOT NULL, purchase_rate DECIMAL(14,4) NOT NULL, disc_pct DECIMAL(7,4) NOT NULL DEFAULT 0, scheme_note VARCHAR(60) NULL, taxable_amount DECIMAL(14,2) NOT NULL DEFAULT 0, gst_pct DECIMAL(7,4) NOT NULL DEFAULT 0, cgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, sgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, igst_amount DECIMAL(14,2) NOT NULL DEFAULT 0, cess_amount DECIMAL(14,2) NOT NULL DEFAULT 0, line_amount DECIMAL(14,2) NOT NULL DEFAULT 0, match_confidence DECIMAL(5,2) NULL COMMENT 'Auto-match score 0-100', PRIMARY KEY (id), UNIQUE KEY uk_pil (purchase_inv_id, line_no), KEY ix_pil_item (item_id), KEY ix_pil_unmatched (purchase_inv_id, item_id), CONSTRAINT fk_pil_inv FOREIGN KEY (purchase_inv_id) REFERENCES purchase_invoice(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ===================================================================== -- SECTION K — CONFIGURATION (MASTER) -- ===================================================================== -- --------------------------------------------------------------------- -- K.1 Print profile — the fixed template set. Rule S1: NO custom -- formats, ever. New rows here are platform releases, not -- per-customer work. -- --------------------------------------------------------------------- CREATE TABLE print_profile ( id CHAR(26) NOT NULL, code VARCHAR(30) NOT NULL COMMENT 'DM80_STD, DM132_DETAIL, TH80_STD, TH58_COMPACT, A5_GST, A4_GST', name VARCHAR(100) NOT NULL, device_type ENUM('DOTMATRIX','THERMAL','LASER') NOT NULL, columns SMALLINT NULL COMMENT '80 or 132 for dot matrix', paper_width_mm SMALLINT NULL COMMENT '58 or 80 for thermal', command_set ENUM('ESCP','ESCPOS','PDF') NOT NULL, template_body MEDIUMTEXT NOT NULL COMMENT 'Token-substituted layout', 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_pp_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- K.2 Settings — scoped configuration, NOT customisation -- --------------------------------------------------------------------- CREATE TABLE setting ( id CHAR(26) NOT NULL, scope ENUM('PLATFORM','TENANT','STORE','COUNTER') NOT NULL, scope_id CHAR(26) NULL, setting_key VARCHAR(80) NOT NULL, setting_value VARCHAR(500) NULL, 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_setting (scope, scope_id, setting_key), KEY ix_setting_key (setting_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- K.3 Node telemetry — S8. Every exception reaches engineering -- automatically. A bug reaches us once, not 500 chemists. -- --------------------------------------------------------------------- CREATE TABLE node_telemetry ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NULL, store_id CHAR(26) NULL, counter_id CHAR(26) NULL, kind ENUM('ERROR','PERF','HEALTH','UPDATE') NOT NULL, severity ENUM('INFO','WARN','ERROR','FATAL') NOT NULL DEFAULT 'INFO', code VARCHAR(60) NULL, message VARCHAR(1000) NULL, context JSON NULL, agent_version VARCHAR(20) NULL, occurred_at DATETIME(3) NOT NULL, received_at DATETIME(3) NULL, PRIMARY KEY (id), KEY ix_tel_scope (store_id, kind, occurred_at), KEY ix_tel_sev (severity, occurred_at), KEY ix_tel_code (code, occurred_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; SET FOREIGN_KEY_CHECKS = 1; -- ===================================================================== -- END OF PHASE 0 CORE SCHEMA v1.0 -- -- DELIBERATELY DEFERRED to the Phase 1 and Phase 2 schema packs: -- * Accounting: voucher, voucher_line, ledger_account, bank_recon -- * Sale return / credit note / debit note document tables -- * Consumer orders: online_order, order_line, order_status_history, -- delivery_assignment, payment_settlement -- * Cross-sell catalogue and merchandising -- * Refill schedule and messaging outbox (WhatsApp / SES) -- * Migration staging tables -- -- These are omitted because Phase 0's exit criterion is narrow and -- must be proven first: an event created on a counter node appears -- correctly in cloud, a master edited in cloud appears at the -- counter, and a raw test print fires from the helper. Adding -- tables before that loop is proven is how the master/event -- discipline gets diluted. -- =====================================================================