-- ===================================================================== -- RETAIL PHARMACY PLATFORM -- Phase 1 Schema Pack G — Returns & Stock Valuation v1.0 -- -- Apply AFTER packs A, C, D. -- Target: MySQL 8.0+ / InnoDB / utf8mb4 -- -- ================================================================= -- WHY RETURNS ARE THEIR OWN DOCUMENTS -- ================================================================= -- -- Principle P5: a correction is a new document, never an edit. The -- original invoice stays exactly as printed, because the customer is -- holding a copy of it and the GST return already carries it. -- -- A credit note is therefore a real GST document with its own number -- series, reported separately in GSTR-1, and referencing the invoice -- it partially or wholly reverses. -- -- ================================================================= -- THE ONE THAT MATTERS FOR SAFETY -- ================================================================= -- -- A returned medicine may NOT automatically go back on the shelf. -- Once a strip has left the premises the chemist cannot vouch for how -- it was stored — a heat-sensitive product that spent a day in a -- scooter box is not saleable, whatever it looks like. Reselling it is -- both a licence risk and a patient risk. -- -- So restock is an explicit decision per line, made by a person, with -- a reason recorded. The default is NOT to restock. Anything not -- restocked lands in a quarantine bucket and leaves via breakage or a -- return to the distributor — never via the counter. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- G.1 Credit note — customer returns -- --------------------------------------------------------------------- CREATE TABLE credit_note ( 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, cn_no VARCHAR(30) NOT NULL, cn_series VARCHAR(10) NOT NULL, fy_code VARCHAR(9) NOT NULL, cn_date DATE NOT NULL, cn_ts DATETIME(3) NOT NULL, -- The invoice being reversed. A credit note without one is not a -- credit note; it is an unexplained refund. sale_bill_id CHAR(26) NOT NULL, original_bill_no VARCHAR(30) NOT NULL, original_date DATE NOT NULL, customer_id CHAR(26) NULL, reason ENUM('WRONG_ITEM','PRESCRIPTION_CHANGED','DAMAGED','EXPIRED', 'CUSTOMER_CHANGED_MIND','BILLING_ERROR','FULL_CANCELLATION') NOT NULL, reason_note VARCHAR(300) NULL, -- Amounts, mirroring the invoice structure so GSTR-1 can consume it 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, refund_mode ENUM('CASH','UPI','CARD','ADJUST_CREDIT','NOT_REFUNDED') NOT NULL DEFAULT 'CASH', user_id CHAR(26) NULL, created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_cn_no (store_id, fy_code, cn_series, cn_no), KEY ix_cn_bill (sale_bill_id), KEY ix_cn_date (store_id, cn_date), KEY ix_cn_customer (customer_id, cn_date), CONSTRAINT fk_cn_bill FOREIGN KEY (sale_bill_id) REFERENCES sale_bill(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- G.2 Credit note line -- --------------------------------------------------------------------- CREATE TABLE credit_note_line ( id CHAR(26) NOT NULL, credit_note_id CHAR(26) NOT NULL, line_no SMALLINT NOT NULL, -- Points at the exact invoice line, so a partial return can never -- exceed what was actually sold on it. sale_line_id CHAR(26) NOT NULL, item_id CHAR(26) NOT NULL, batch_id CHAR(26) NOT NULL, item_name VARCHAR(250) NOT NULL, batch_no VARCHAR(50) NOT NULL, expiry_date DATE NULL, schedule_code VARCHAR(6) NULL, hsn_code VARCHAR(10) NULL, qty_returned DECIMAL(14,3) NOT NULL, 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, -- The safety decision. Default is NO. restocked TINYINT(1) NOT NULL DEFAULT 0, restock_note VARCHAR(200) NULL COMMENT 'Why it was or was not put back', PRIMARY KEY (id), UNIQUE KEY uk_cnl (credit_note_id, line_no), KEY ix_cnl_saleline (sale_line_id), KEY ix_cnl_item (item_id), CONSTRAINT fk_cnl_cn FOREIGN KEY (credit_note_id) REFERENCES credit_note(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- G.3 Quarantine — returned stock that must not be sold again -- -- This is a real physical shelf in the shop, and the software has to -- agree with it. Stock sits here until it leaves as breakage or on a -- debit note to the distributor. It is never available to the counter. -- --------------------------------------------------------------------- CREATE TABLE quarantine_stock ( 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, source ENUM('CUSTOMER_RETURN','EXPIRED','DAMAGED','RECALL') NOT NULL, source_doc_id CHAR(26) NULL, qty DECIMAL(14,3) NOT NULL, reason VARCHAR(300) NULL, disposal ENUM('PENDING','RETURNED_TO_SUPPLIER','DESTROYED','WRITTEN_OFF') NOT NULL DEFAULT 'PENDING', disposal_doc_id CHAR(26) NULL, disposed_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), KEY ix_qs_pending (store_id, disposal, created_at), KEY ix_qs_batch (batch_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- G.4 Debit note — goods returned TO the distributor -- -- This is the money the expiry report points at. Near-expiry stock -- returned in time is cash back; missed, it is a write-off. -- --------------------------------------------------------------------- CREATE TABLE debit_note ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, event_id CHAR(26) NOT NULL, dn_no VARCHAR(30) NOT NULL, fy_code VARCHAR(9) NOT NULL, dn_date DATE NOT NULL, supplier_id CHAR(26) NOT NULL, reason ENUM('NEAR_EXPIRY','EXPIRED','DAMAGED','WRONG_SUPPLY','RATE_DIFFERENCE') NOT NULL, reason_note VARCHAR(300) NULL, 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, net_amount DECIMAL(14,2) NOT NULL DEFAULT 0, -- Distributors settle these slowly and partially. Tracking what was -- claimed against what was credited is the whole point. settled_amount DECIMAL(14,2) NOT NULL DEFAULT 0, status ENUM('RAISED','SENT','PART_SETTLED','SETTLED','REJECTED') NOT NULL DEFAULT 'RAISED', created_at DATETIME(3) NOT NULL, updated_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_dn_no (store_id, fy_code, dn_no), KEY ix_dn_supplier (supplier_id, dn_date), KEY ix_dn_status (store_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE debit_note_line ( id CHAR(26) NOT NULL, debit_note_id CHAR(26) NOT NULL, line_no SMALLINT NOT NULL, item_id CHAR(26) NOT NULL, batch_id CHAR(26) NOT NULL, item_name VARCHAR(250) NOT NULL, batch_no VARCHAR(50) NOT NULL, expiry_date DATE NULL, qty DECIMAL(14,3) NOT NULL, purchase_rate DECIMAL(14,4) NOT 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, line_amount DECIMAL(14,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_dnl (debit_note_id, line_no), CONSTRAINT fk_dnl_dn FOREIGN KEY (debit_note_id) REFERENCES debit_note(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- G.5 Stock valuation snapshot -- -- WITHOUT THIS THE P&L IS WRONG, and wrong in the direction that makes -- a profitable shop look like a failing one. -- -- Purchases are expensed as they are booked. Sales are recognised as -- they happen. If nothing accounts for the goods still sitting on the -- shelf, a month with a big purchase shows a loss. The accounting test -- run showed exactly this: a ₹13,548 "loss" on a day that was in fact -- profitable, because two full purchase invoices sat against half a -- day of sales with no closing stock adjustment. -- -- Snapshotted rather than computed on demand, because a valuation is a -- statement about a moment. Recomputing last March from today's -- purchase rates would silently rewrite a filed return. -- --------------------------------------------------------------------- CREATE TABLE stock_valuation ( id CHAR(26) NOT NULL, tenant_id CHAR(26) NOT NULL, store_id CHAR(26) NOT NULL, as_on DATE NOT NULL, fy_code VARCHAR(9) NOT NULL, method ENUM('PURCHASE_RATE','WEIGHTED_AVG','MRP_LESS_MARGIN') NOT NULL DEFAULT 'PURCHASE_RATE', batches_counted INT UNSIGNED NOT NULL DEFAULT 0, total_units DECIMAL(16,3) NOT NULL DEFAULT 0, value_at_cost DECIMAL(16,2) NOT NULL DEFAULT 0, value_at_mrp DECIMAL(16,2) NOT NULL DEFAULT 0, negative_batches INT UNSIGNED NOT NULL DEFAULT 0, expired_value DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT 'Included in the count but flagged — it is not really an asset', voucher_id CHAR(26) NULL, created_at DATETIME(3) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_sv (store_id, as_on), KEY ix_sv_fy (store_id, fy_code, as_on) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; SET FOREIGN_KEY_CHECKS = 1; -- ===================================================================== -- INVARIANTS asserted in tests: -- -- 1. A return can never exceed qty sold on that invoice line, net of -- earlier returns against it. -- 2. The original invoice is never modified. Ever. -- 3. Restocking is explicit per line and defaults to NO; anything not -- restocked goes to quarantine and is invisible to the counter. -- 4. An expired batch is never restocked, whatever the operator says. -- 5. A credit note reverses tax proportionally, so GSTR-1 nets out. -- 6. Trial balance remains zero after returns and after the closing -- stock journal. -- 7. Gross profit is positive once closing stock is posted. -- =====================================================================