-- ============================================================ -- CareBill — Revenue, Renewal & Collections platform -- Caresoft Systems Private Limited -- MySQL 8.0 / MariaDB 10.6+ -- ============================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ------------------------------------------------------------ -- 1. Access & configuration -- ------------------------------------------------------------ CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(160) NOT NULL UNIQUE, phone VARCHAR(20), password_hash VARCHAR(255) NOT NULL, totp_secret VARCHAR(64) NULL, totp_enabled TINYINT(1) DEFAULT 0, totp_last_used INT NULL, role ENUM('admin','accounts','collector','auditor','user') NOT NULL DEFAULT 'accounts', brand_scope VARCHAR(255) DEFAULT NULL, -- CSV of brand ids; NULL = all status ENUM('active','disabled') DEFAULT 'active', must_reset TINYINT(1) DEFAULT 0, last_login DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE settings ( k VARCHAR(80) PRIMARY KEY, v TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE audit_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, entity VARCHAR(60), entity_id INT, action VARCHAR(60), detail TEXT, ip VARCHAR(45), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_audit (entity, entity_id), INDEX ix_audit_dt (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 2. Brands (one row per Caresoft brand / billing entity) -- ------------------------------------------------------------ CREATE TABLE brands ( id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(20) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, legal_name VARCHAR(180) NOT NULL, tagline VARCHAR(200), address TEXT, city VARCHAR(80), state VARCHAR(80) DEFAULT 'Maharashtra', state_code VARCHAR(4) DEFAULT '27', pincode VARCHAR(10), phone VARCHAR(120), email VARCHAR(120), website VARCHAR(120), gstin VARCHAR(20), pan VARCHAR(12), cin VARCHAR(30), msme_no VARCHAR(40), logo_path VARCHAR(200), accreditation VARCHAR(255), -- CMMI / ISO strip printed on invoice bank_name VARCHAR(120), bank_ac VARCHAR(40), bank_ifsc VARCHAR(20), bank_branch VARCHAR(120), jurisdiction VARCHAR(60) DEFAULT 'Thane', invoice_prefix VARCHAR(30) DEFAULT 'CSPL/INV', proforma_prefix VARCHAR(30) DEFAULT 'CSPL-P/SUB', cn_prefix VARCHAR(30) DEFAULT 'CSPL/CN', payment_terms_days INT DEFAULT 45, default_gst_rate DECIMAL(5,2) DEFAULT 18.00, terms_html MEDIUMTEXT, -- printed T&C block exclusions_html MEDIUMTEXT, -- "Exclusion from subscription charges" annexure footer_note VARCHAR(255), reply_to VARCHAR(120), signatory VARCHAR(120) DEFAULT 'Authorised Signatory', status ENUM('active','inactive') DEFAULT 'active', created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- per brand / per financial year running numbers CREATE TABLE doc_series ( id INT AUTO_INCREMENT PRIMARY KEY, brand_id INT NOT NULL, doc_type ENUM('proforma','tax_invoice','credit_note') NOT NULL, fy_code VARCHAR(10) NOT NULL, -- 26-27, for reference series_key VARCHAR(60) NOT NULL, -- everything in the number before the serial next_no INT NOT NULL DEFAULT 1, UNIQUE KEY uq_series (series_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 3. Billing item master (brand-wise) -- ------------------------------------------------------------ CREATE TABLE billing_items ( id INT AUTO_INCREMENT PRIMARY KEY, brand_id INT NOT NULL, code VARCHAR(40) NOT NULL, name VARCHAR(200) NOT NULL, description TEXT, -- goes onto the invoice line hsn_sac VARCHAR(12) DEFAULT '998314', uom VARCHAR(20) DEFAULT 'Nos', default_rate DECIMAL(14,2) DEFAULT 0, gst_rate DECIMAL(5,2) DEFAULT 18.00, default_cycle ENUM('monthly','quarterly','half_yearly','yearly','one_time','custom') DEFAULT 'yearly', is_recurring TINYINT(1) DEFAULT 1, revenue_head VARCHAR(60), -- AMC / Cloud / WhatsApp / Backup ... for MIS tally_ledger VARCHAR(120), status ENUM('active','inactive') DEFAULT 'active', UNIQUE KEY uq_item (brand_id, code), INDEX ix_item_brand (brand_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 4. Clients -- ------------------------------------------------------------ CREATE TABLE clients ( id INT AUTO_INCREMENT PRIMARY KEY, client_code VARCHAR(30) NOT NULL UNIQUE, name VARCHAR(200) NOT NULL, legal_name VARCHAR(220), address TEXT, location VARCHAR(120), city VARCHAR(80), state VARCHAR(80), state_code VARCHAR(4), pincode VARCHAR(10), country VARCHAR(60) DEFAULT 'India', currency CHAR(3) DEFAULT 'INR', export_type ENUM('domestic','export_lut','export_igst','sez_lut','sez_igst','deemed') DEFAULT 'domestic', gstin VARCHAR(20), pan VARCHAR(12), tan VARCHAR(15), -- their deduction account, for matching 26AS tally_ledger VARCHAR(200), -- their ledger name in Tally, if it differs place_of_supply VARCHAR(80), tds_applicable TINYINT(1) DEFAULT 0, tds_section VARCHAR(20) DEFAULT '194J', tds_rate DECIMAL(5,2) DEFAULT 10.00, payment_terms_days INT DEFAULT 45, credit_hold TINYINT(1) DEFAULT 0, risk_grade ENUM('A','B','C','D') DEFAULT 'B', live_date DATE NULL, owner_user_id INT NULL, -- account owner (sales) - view only collector_id INT NULL, -- accounts cell executive who follows up status ENUM('active','dormant','closed','lost') DEFAULT 'active', portal_enabled TINYINT(1) DEFAULT 1, notes TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_client_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE client_contacts ( id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, name VARCHAR(120), designation VARCHAR(80), email VARCHAR(160), phone VARCHAR(20), whatsapp VARCHAR(20), is_primary TINYINT(1) DEFAULT 0, rx_invoice TINYINT(1) DEFAULT 1, rx_reminder TINYINT(1) DEFAULT 1, rx_escalation TINYINT(1) DEFAULT 0, portal_access TINYINT(1) DEFAULT 1, status ENUM('active','inactive') DEFAULT 'active', INDEX ix_contact_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- names as they appear in bank narration / legacy sheets CREATE TABLE client_aliases ( id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, alias VARCHAR(200) NOT NULL, alias_key VARCHAR(200) NOT NULL, -- normalised source VARCHAR(40) DEFAULT 'manual', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_alias_key (alias_key), INDEX ix_alias_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 5. Services (the subscription / contract line that drives billing) -- ------------------------------------------------------------ CREATE TABLE services ( id INT AUTO_INCREMENT PRIMARY KEY, service_code VARCHAR(30) UNIQUE, client_id INT NOT NULL, brand_id INT NOT NULL, item_id INT NOT NULL, title VARCHAR(200), -- shown on invoice line description TEXT, qty DECIMAL(10,2) DEFAULT 1, rate DECIMAL(14,2) NOT NULL DEFAULT 0, discount_pct DECIMAL(5,2) DEFAULT 0, gst_rate DECIMAL(5,2) DEFAULT 18.00, currency CHAR(3) DEFAULT 'INR', billing_cycle ENUM('monthly','quarterly','half_yearly','yearly','one_time','custom') DEFAULT 'yearly', custom_days INT DEFAULT NULL, bill_in_advance TINYINT(1) DEFAULT 1, -- 1 = raise before period starts advance_bill_days INT DEFAULT 15, -- raise invoice N days before period start start_date DATE NOT NULL, period_from DATE NULL, -- current / next period being billed period_to DATE NULL, next_invoice_date DATE NULL, -- the date the invoice must be raised payment_terms_days INT DEFAULT 45, auto_invoice TINYINT(1) DEFAULT 1, -- draft raised automatically auto_issue TINYINT(1) DEFAULT 0, -- 0 = human approves before sending auto_renew TINYINT(1) DEFAULT 1, escalation_pct DECIMAL(5,2) DEFAULT 0, -- annual AMC hike applied on renewal contract_end DATE NULL, po_no VARCHAR(60), po_date DATE NULL, hosting VARCHAR(40), -- Cloud / On-prem / Local suspend_on_nonpayment TINYINT(1) DEFAULT 0, suspend_after_days INT DEFAULT 90, status ENUM('active','paused','closed','lost') DEFAULT 'active', close_reason VARCHAR(200), last_invoice_id INT NULL, created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_svc_client (client_id), INDEX ix_svc_next (next_invoice_date, status), INDEX ix_svc_brand (brand_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE service_events ( id BIGINT AUTO_INCREMENT PRIMARY KEY, service_id INT NOT NULL, event VARCHAR(60), -- renewed / hiked / paused / closed / suspended detail TEXT, user_id INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_sev (service_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 6. Invoices -- ------------------------------------------------------------ CREATE TABLE invoices ( id INT AUTO_INCREMENT PRIMARY KEY, brand_id INT NOT NULL, client_id INT NOT NULL, doc_type ENUM('proforma','tax_invoice','credit_note') DEFAULT 'proforma', currency CHAR(3) DEFAULT 'INR', fx_rate DECIMAL(16,6) DEFAULT 1, -- rupees per unit, as at the invoice date export_type ENUM('domestic','export_lut','export_igst','sez_lut','sez_igst','deemed') DEFAULT 'domestic', ref_invoice_id INT NULL, -- the invoice a credit note is written against invoice_no VARCHAR(60), invoice_date DATE, period_from DATE NULL, period_to DATE NULL, due_date DATE, place_of_supply VARCHAR(80), is_igst TINYINT(1) DEFAULT 0, subtotal DECIMAL(14,2) DEFAULT 0, discount DECIMAL(14,2) DEFAULT 0, taxable DECIMAL(14,2) DEFAULT 0, base_taxable DECIMAL(16,2) DEFAULT 0, cgst DECIMAL(14,2) DEFAULT 0, sgst DECIMAL(14,2) DEFAULT 0, igst DECIMAL(14,2) DEFAULT 0, round_off DECIMAL(8,2) DEFAULT 0, grand_total DECIMAL(14,2) DEFAULT 0, base_grand_total DECIMAL(16,2) DEFAULT 0, tds_expected DECIMAL(14,2) DEFAULT 0, amount_paid DECIMAL(14,2) DEFAULT 0, amount_adjusted DECIMAL(14,2) DEFAULT 0, -- TDS + writeoff + discount + bank charges balance DECIMAL(14,2) DEFAULT 0, base_balance DECIMAL(16,2) DEFAULT 0, status ENUM('draft','pending_approval','issued','partly_paid','paid','closed','cancelled') DEFAULT 'draft', parent_invoice_id INT NULL, po_no VARCHAR(60), notes TEXT, terms_html MEDIUMTEXT, reminder_stage INT DEFAULT 0, -- how far down the dunning ladder last_reminder_at DATE NULL, ptp_date DATE NULL, -- promise to pay captured on call dispute_flag TINYINT(1) DEFAULT 0, dispute_note VARCHAR(255), created_by INT NULL, approved_by INT NULL, tally_pushed_at DATETIME NULL, -- sent to Tally as a sales or credit note voucher issued_at DATETIME NULL, settled_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_inv_no (brand_id, doc_type, invoice_no), INDEX ix_inv_client (client_id), INDEX ix_inv_status (status, due_date), INDEX ix_inv_date (invoice_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE invoice_lines ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, service_id INT NULL, item_id INT NULL, description TEXT, hsn_sac VARCHAR(12), period_from DATE NULL, period_to DATE NULL, qty DECIMAL(10,2) DEFAULT 1, rate DECIMAL(14,2) DEFAULT 0, discount_pct DECIMAL(5,2) DEFAULT 0, amount DECIMAL(14,2) DEFAULT 0, taxable DECIMAL(14,2) DEFAULT 0, gst_rate DECIMAL(5,2) DEFAULT 18.00, cgst DECIMAL(14,2) DEFAULT 0, sgst DECIMAL(14,2) DEFAULT 0, igst DECIMAL(14,2) DEFAULT 0, sort_no INT DEFAULT 1, INDEX ix_line_inv (invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 7. Money in -- ------------------------------------------------------------ CREATE TABLE payments ( id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NULL, brand_id INT NULL, currency CHAR(3) DEFAULT 'INR', fx_rate DECIMAL(16,6) DEFAULT 1, base_amount DECIMAL(16,2) DEFAULT 0, pay_date DATE NOT NULL, mode ENUM('neft','rtgs','imps','upi','cheque','cash','adjustment','online','other') DEFAULT 'neft', reference VARCHAR(80), amount DECIMAL(14,2) NOT NULL, allocated DECIMAL(14,2) DEFAULT 0, unallocated DECIMAL(14,2) DEFAULT 0, narration VARCHAR(400), bank_txn_id BIGINT NULL, source ENUM('bank_import','manual','gateway','tally') DEFAULT 'manual', tally_pushed_at DATETIME NULL, -- sent to Tally as a receipt voucher gateway VARCHAR(20) NULL, gateway_ref VARCHAR(120) NULL, gateway_fee DECIMAL(12,2) DEFAULT 0, settled_to_bank TINYINT(1) DEFAULT 0, created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_pay_client (client_id), INDEX ix_pay_date (pay_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE payment_allocations ( id BIGINT AUTO_INCREMENT PRIMARY KEY, payment_id INT NULL, invoice_id INT NOT NULL, amount DECIMAL(14,2) NOT NULL, kind ENUM('payment','tds','writeoff','discount','bank_charge','gst_hold','credit_note','round_off','fx_diff','other') DEFAULT 'payment', reason VARCHAR(255), created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_alloc_inv (invoice_id), INDEX ix_alloc_pay (payment_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 8. Bank statement import & matching -- ------------------------------------------------------------ CREATE TABLE bank_imports ( id INT AUTO_INCREMENT PRIMARY KEY, brand_id INT NULL, file_name VARCHAR(200), bank VARCHAR(60) DEFAULT 'HDFC', ac_no VARCHAR(40), period_from DATE NULL, period_to DATE NULL, rows_total INT DEFAULT 0, rows_credit INT DEFAULT 0, rows_dupe INT DEFAULT 0, rows_matched INT DEFAULT 0, imported_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE bank_txns ( id BIGINT AUTO_INCREMENT PRIMARY KEY, import_id INT NOT NULL, txn_date DATE, value_date DATE NULL, narration VARCHAR(500), ref_no VARCHAR(80), debit DECIMAL(14,2) DEFAULT 0, credit DECIMAL(14,2) DEFAULT 0, balance DECIMAL(14,2) DEFAULT 0, counterparty VARCHAR(200), -- name parsed out of narration match_status ENUM('unmatched','suggested','matched','ignored') DEFAULT 'unmatched', client_id INT NULL, payment_id INT NULL, score INT DEFAULT 0, hash CHAR(40) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_txn_hash (hash), INDEX ix_txn_status (match_status), INDEX ix_txn_import (import_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 9. Communication: templates, rules, queue, log -- ------------------------------------------------------------ CREATE TABLE templates ( id INT AUTO_INCREMENT PRIMARY KEY, brand_id INT NULL, -- NULL = applies to all brands code VARCHAR(60) NOT NULL, name VARCHAR(150), channel ENUM('email','whatsapp','call_script','sms') DEFAULT 'email', dlt_template_id VARCHAR(30) NULL, -- India: the DLT-registered template id subject VARCHAR(255), body MEDIUMTEXT, wa_template_name VARCHAR(120), -- Meta approved template name active TINYINT(1) DEFAULT 1, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX ix_tpl (code, brand_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE reminder_rules ( id INT AUTO_INCREMENT PRIMARY KEY, brand_id INT NULL, code VARCHAR(60) NOT NULL, name VARCHAR(150), trigger_type ENUM('pre_generation','pre_due','on_due','post_due','renewal','contract_expiry','statement') NOT NULL, offset_days INT DEFAULT 0, -- days before/after the anchor date channel ENUM('email','whatsapp','call','internal','sms') DEFAULT 'email', template_code VARCHAR(60), audience ENUM('client','internal_accounts','internal_support','management','collector') DEFAULT 'client', repeat_every_days INT DEFAULT 0, -- 0 = fire once max_repeats INT DEFAULT 1, stage_no INT DEFAULT 1, min_amount DECIMAL(14,2) DEFAULT 0, active TINYINT(1) DEFAULT 1, INDEX ix_rule (trigger_type, active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE outbox ( id BIGINT AUTO_INCREMENT PRIMARY KEY, channel ENUM('email','whatsapp','call','sms') DEFAULT 'email', to_addr VARCHAR(255), cc_addr VARCHAR(255), bcc_addr VARCHAR(255), from_name VARCHAR(120), from_addr VARCHAR(160), reply_to VARCHAR(160), subject VARCHAR(255), body MEDIUMTEXT, attach_invoice INT NULL, meta_json TEXT, brand_id INT NULL, client_id INT NULL, invoice_id INT NULL, service_id INT NULL, template_code VARCHAR(60), rule_id INT NULL, dedupe_key VARCHAR(120) NULL, status ENUM('queued','sending','sent','failed','cancelled') DEFAULT 'queued', attempts INT DEFAULT 0, message_id VARCHAR(200) NULL, last_error VARCHAR(500), scheduled_at DATETIME DEFAULT CURRENT_TIMESTAMP, sent_at DATETIME NULL, provider_id VARCHAR(160), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_dedupe (dedupe_key), INDEX ix_outbox_status (status, scheduled_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE comm_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NULL, invoice_id INT NULL, service_id INT NULL, channel VARCHAR(20), direction ENUM('out','in') DEFAULT 'out', template_code VARCHAR(60), to_addr VARCHAR(255), subject VARCHAR(255), snippet VARCHAR(500), status VARCHAR(30), provider_id VARCHAR(160), user_id INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_comm_client (client_id), INDEX ix_comm_inv (invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE call_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NULL, invoice_id INT NULL, user_id INT NULL, phone VARCHAR(20), provider_call_id VARCHAR(120), direction ENUM('out','in') DEFAULT 'out', status VARCHAR(40), disposition ENUM('connected','no_answer','wrong_number','ptp','dispute','callback','refused','') DEFAULT '', ptp_date DATE NULL, duration_sec INT DEFAULT 0, recording_url VARCHAR(255), notes TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_call_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 10. Work queue -- ------------------------------------------------------------ CREATE TABLE tasks ( id BIGINT AUTO_INCREMENT PRIMARY KEY, type ENUM('generate_invoice','approve_invoice','call','follow_up','renewal','match_payment','dispute') DEFAULT 'follow_up', title VARCHAR(255), detail TEXT, client_id INT NULL, service_id INT NULL, invoice_id INT NULL, brand_id INT NULL, due_date DATE, priority ENUM('low','normal','high') DEFAULT 'normal', assigned_to INT NULL, status ENUM('open','done','dismissed') DEFAULT 'open', dedupe_key VARCHAR(120) NULL, completed_at DATETIME NULL, completed_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_task_dedupe (dedupe_key), INDEX ix_task_open (status, due_date), INDEX ix_task_user (assigned_to, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE cron_runs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, job VARCHAR(60), started_at DATETIME, finished_at DATETIME NULL, summary TEXT, status ENUM('running','ok','error') DEFAULT 'running', INDEX ix_cron (job, started_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 11. Reporting views -- ------------------------------------------------------------ -- ---------- client portal ---------- CREATE TABLE portal_logins ( id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, contact_id INT NULL, email VARCHAR(160) NOT NULL, code_hash VARCHAR(255) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, attempts INT DEFAULT 0, ip VARCHAR(45), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_pl_email (email, expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE portal_sessions ( id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, contact_id INT NULL, token_hash VARCHAR(255) NOT NULL, expires_at DATETIME NOT NULL, last_seen DATETIME NULL, ip VARCHAR(45), user_agent VARCHAR(255), revoked TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_ps_token (token_hash), INDEX ix_ps_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- "we have paid, here is the UTR" — an intimation, never a receipt. -- Money only enters the ledger when accounts sees it in the bank. CREATE TABLE payment_advices ( id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, invoice_id INT NULL, contact_id INT NULL, amount DECIMAL(14,2) NOT NULL, pay_date DATE NULL, mode VARCHAR(20) DEFAULT 'neft', reference VARCHAR(120), note TEXT, attachment VARCHAR(255) NULL, status ENUM('new','matched','rejected') DEFAULT 'new', payment_id BIGINT NULL, bank_txn_id BIGINT NULL, reviewed_by INT NULL, reviewed_at DATETIME NULL, review_note VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_pa_status (status, created_at), INDEX ix_pa_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- questions, disputes and document requests raised from the portal CREATE TABLE client_queries ( id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, invoice_id INT NULL, contact_id INT NULL, type ENUM('dispute','question','copy_request','contact_change') DEFAULT 'question', subject VARCHAR(200), body TEXT, status ENUM('open','answered','closed') DEFAULT 'open', reply TEXT, replied_by INT NULL, replied_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_cq_status (status, created_at), INDEX ix_cq_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- what the client did in the portal, so a later argument has a record CREATE TABLE portal_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NULL, contact_id INT NULL, email VARCHAR(160), action VARCHAR(60), detail VARCHAR(255), ip VARCHAR(45), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_plog_client (client_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- GST e-invoicing (IRN / signed QR) ---------- CREATE TABLE einvoices ( id BIGINT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, irn CHAR(64) NULL, ack_no VARCHAR(30) NULL, ack_date DATETIME NULL, signed_qr MEDIUMTEXT NULL, -- the IRP's signed string, printed as the QR signed_invoice MEDIUMTEXT NULL, ewb_no VARCHAR(20) NULL, status ENUM('pending','active','cancelled','failed') DEFAULT 'pending', error VARCHAR(500) NULL, attempts INT DEFAULT 0, driver VARCHAR(20) NULL, payload MEDIUMTEXT NULL, cancelled_at DATETIME NULL, cancel_reason VARCHAR(200) NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_einv_invoice (invoice_id), UNIQUE KEY uq_einv_irn (irn), INDEX ix_einv_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- currencies and exchange rates ---------- CREATE TABLE currencies ( code CHAR(3) PRIMARY KEY, name VARCHAR(60) NOT NULL, symbol VARCHAR(8), decimals TINYINT DEFAULT 2, status ENUM('active','inactive') DEFAULT 'active' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO currencies (code,name,symbol,decimals) VALUES ('INR','Indian Rupee','Rs.',2), ('USD','US Dollar','$',2), ('EUR','Euro','EUR',2), ('GBP','Pound Sterling','GBP',2), ('AED','UAE Dirham','AED',2), ('BHD','Bahraini Dinar','BHD',3), ('SAR','Saudi Riyal','SAR',2), ('QAR','Qatari Riyal','QAR',2), ('OMR','Omani Rial','OMR',3), ('KES','Kenyan Shilling','KSh',2), ('UGX','Ugandan Shilling','USh',0), ('TZS','Tanzanian Shilling','TSh',2), ('NGN','Nigerian Naira','NGN',2), ('XOF','West African Franc','CFA',0), ('ZAR','South African Rand','R',2), ('LKR','Sri Lankan Rupee','LKR',2), ('NPR','Nepalese Rupee','NPR',2), ('BDT','Bangladeshi Taka','BDT',2), ('MUR','Mauritian Rupee','MUR',2), ('SGD','Singapore Dollar','S$',2), ('AUD','Australian Dollar','A$',2) ON DUPLICATE KEY UPDATE name=VALUES(name); -- rate history. For GST the rate that matters is the one notified for the -- invoice date, not today's market rate, so rates are dated and kept. CREATE TABLE fx_rates ( id BIGINT AUTO_INCREMENT PRIMARY KEY, currency CHAR(3) NOT NULL, rate_date DATE NOT NULL, rate DECIMAL(16,6) NOT NULL, -- units of INR for one unit of currency source VARCHAR(40) DEFAULT 'manual', -- manual | cbic | bank note VARCHAR(160), created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_fx (currency, rate_date, source), INDEX ix_fx_lookup (currency, rate_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- online payment links ---------- CREATE TABLE payment_links ( id BIGINT AUTO_INCREMENT PRIMARY KEY, token CHAR(40) NOT NULL, -- ours, used in the URL we hand out client_id INT NOT NULL, invoice_id INT NULL, amount DECIMAL(14,2) NOT NULL, currency CHAR(3) DEFAULT 'INR', provider VARCHAR(20) DEFAULT 'manual', provider_ref VARCHAR(120) NULL, -- the gateway's own link or order id short_url VARCHAR(255) NULL, gateway_provider VARCHAR(20) NULL, -- a second gateway's page for the same link gateway_ref VARCHAR(120) NULL, gateway_url VARCHAR(255) NULL, purpose VARCHAR(160), status ENUM('created','sent','paid','partly_paid','expired','cancelled','failed') DEFAULT 'created', amount_paid DECIMAL(14,2) DEFAULT 0, expires_at DATETIME NULL, created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, paid_at DATETIME NULL, UNIQUE KEY uq_pl_token (token), INDEX ix_pl_invoice (invoice_id), INDEX ix_pl_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- every webhook the gateway sends, kept whole. The unique key on the provider's -- event id is what makes a retried or replayed webhook harmless. CREATE TABLE gateway_events ( id BIGINT AUTO_INCREMENT PRIMARY KEY, provider VARCHAR(20) NOT NULL, event_id VARCHAR(120) NOT NULL, event_type VARCHAR(60), link_id BIGINT NULL, payment_id BIGINT NULL, signature_ok TINYINT(1) DEFAULT 0, processed TINYINT(1) DEFAULT 0, result VARCHAR(255), payload MEDIUMTEXT, ip VARCHAR(45), received_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_ge (provider, event_id), INDEX ix_ge_link (link_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- two factor and login history ---------- CREATE TABLE recovery_codes ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, code_hash VARCHAR(255) NOT NULL, used_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_rc_user (user_id, used_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE login_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, email VARCHAR(160), result ENUM('ok','bad_password','bad_code','locked','disabled','unknown_user') NOT NULL, ip VARCHAR(45), user_agent VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_ll_email (email, created_at), INDEX ix_ll_user (user_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- deliverability ---------- CREATE TABLE suppressions ( id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(160) NOT NULL, reason ENUM('hard_bounce','complaint','soft_repeat','manual','invalid') NOT NULL, detail VARCHAR(255), source VARCHAR(20) DEFAULT 'webhook', client_id INT NULL, released_at DATETIME NULL, released_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_supp (email), INDEX ix_supp_reason (reason) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE delivery_events ( id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(160) NOT NULL, event ENUM('sent','delivered','soft_bounce','hard_bounce','complaint','rejected','open','click') NOT NULL, outbox_id BIGINT NULL, message_id VARCHAR(200) NULL, diagnostic VARCHAR(400), provider VARCHAR(20), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX ix_de_email (email, created_at), INDEX ix_de_event (event, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- whatsapp ---------- CREATE TABLE wa_contacts ( id BIGINT AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) NOT NULL, -- E.164 without the plus client_id INT NULL, contact_id INT NULL, consent ENUM('unknown','opted_in','opted_out') DEFAULT 'unknown', consent_source VARCHAR(60), consent_at DATETIME NULL, last_inbound_at DATETIME NULL, -- opens the 24 hour window last_outbound_at DATETIME NULL, blocked_count INT DEFAULT 0, note VARCHAR(200), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_wa_phone (phone), INDEX ix_wa_client (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE wa_messages ( id BIGINT AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) NOT NULL, direction ENUM('out','in') DEFAULT 'out', wa_message_id VARCHAR(120) NULL, outbox_id BIGINT NULL, client_id INT NULL, invoice_id INT NULL, template_code VARCHAR(40) NULL, body TEXT, status ENUM('queued','sent','delivered','read','failed','received') DEFAULT 'queued', error_code VARCHAR(20) NULL, error_detail VARCHAR(255) NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_wa_msg (wa_message_id), INDEX ix_wam_phone (phone, created_at), INDEX ix_wam_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- revenue recognition ---------- CREATE TABLE revenue_schedule ( id BIGINT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, invoice_line_id BIGINT NOT NULL, client_id INT NOT NULL, brand_id INT NOT NULL, item_id INT NULL, revenue_head VARCHAR(80), period_month CHAR(7) NOT NULL, -- YYYY-MM, the month the revenue belongs to days INT DEFAULT 0, -- days of the service period falling in that month amount DECIMAL(14,2) NOT NULL, -- in the invoice's currency, excluding GST base_amount DECIMAL(16,2) NOT NULL, -- the same at the invoice's own rate currency CHAR(3) DEFAULT 'INR', kind ENUM('recognised','reversal') DEFAULT 'recognised', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_rev (invoice_line_id, period_month, kind), INDEX ix_rev_month (period_month), INDEX ix_rev_invoice (invoice_id), INDEX ix_rev_client (client_id, period_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- a month, once closed, stops moving CREATE TABLE revenue_periods ( period_month CHAR(7) PRIMARY KEY, closed_at DATETIME NULL, closed_by INT NULL, note VARCHAR(200) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- TDS credit tracking ---------- CREATE TABLE tds_certificates ( id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, fy_code CHAR(5) NOT NULL, -- 26-27 quarter ENUM('Q1','Q2','Q3','Q4') NOT NULL, certificate_no VARCHAR(40), tan VARCHAR(15), amount DECIMAL(14,2) DEFAULT 0, -- as per the certificate section VARCHAR(10) DEFAULT '194J', received_on DATE NULL, in_26as TINYINT(1) DEFAULT 0, as26_amount DECIMAL(14,2) NULL, file_path VARCHAR(255) NULL, note VARCHAR(255), created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_tds_cert (client_id, fy_code, quarter), INDEX ix_tds_fy (fy_code, quarter) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- maker / checker ---------- CREATE TABLE approval_requests ( id BIGINT AUTO_INCREMENT PRIMARY KEY, kind ENUM('writeoff','discount','credit_note','bank_change','large_invoice') NOT NULL, subject_type VARCHAR(20), -- invoice | brand subject_id INT NULL, client_id INT NULL, amount DECIMAL(14,2) DEFAULT 0, summary VARCHAR(255), payload MEDIUMTEXT, -- exactly what will be done on approval payload_hash CHAR(64), -- so what is approved is what gets done status ENUM('pending','approved','rejected','executed','failed','withdrawn') DEFAULT 'pending', requested_by INT NOT NULL, requested_at DATETIME DEFAULT CURRENT_TIMESTAMP, reason VARCHAR(255), decided_by INT NULL, decided_at DATETIME NULL, decision_note VARCHAR(255), executed_at DATETIME NULL, result VARCHAR(255), INDEX ix_appr_status (status, requested_at), INDEX ix_appr_subject (subject_type, subject_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- CCAvenue ---------- CREATE TABLE ccav_attempts ( id BIGINT AUTO_INCREMENT PRIMARY KEY, link_id BIGINT NOT NULL, order_id VARCHAR(30) NOT NULL, -- ours, sent to CCAvenue amount DECIMAL(14,2) NOT NULL, currency CHAR(3) DEFAULT 'INR', status ENUM('initiated','returned','verified','failed','abandoned','unverified') DEFAULT 'initiated', tracking_id VARCHAR(40) NULL, -- CCAvenue's reference, once known bank_ref_no VARCHAR(60) NULL, gateway_status VARCHAR(30) NULL, -- what CCAvenue said, verbatim payment_mode VARCHAR(40) NULL, payment_id BIGINT NULL, -- the receipt, once credited checks INT DEFAULT 0, last_checked_at DATETIME NULL, note VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_ccav_order (order_id), UNIQUE KEY uq_ccav_tracking (tracking_id), -- one CCAvenue payment, one receipt, ever INDEX ix_ccav_link (link_id), INDEX ix_ccav_status (status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- targets, SMS, Tally ---------- CREATE TABLE collection_targets ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, month CHAR(7) NOT NULL, -- YYYY-MM amount DECIMAL(14,2) NOT NULL, note VARCHAR(200), set_by INT NULL, UNIQUE KEY uq_target (user_id, month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- numbers that asked for no more SMS CREATE TABLE sms_optouts ( phone VARCHAR(20) PRIMARY KEY, source VARCHAR(40), created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- what came back from Tally, so nothing is imported twice CREATE TABLE tally_imports ( id BIGINT AUTO_INCREMENT PRIMARY KEY, voucher_key VARCHAR(120) NOT NULL, -- Tally's GUID, or type+number+date voucher_type VARCHAR(40), voucher_no VARCHAR(60), voucher_date DATE, party_ledger VARCHAR(200), amount DECIMAL(14,2), client_id INT NULL, payment_id BIGINT NULL, status ENUM('imported','unmatched','skipped') DEFAULT 'imported', note VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_tally_voucher (voucher_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE OR REPLACE VIEW v_outstanding AS SELECT i.id, i.id AS invoice_id, i.brand_id, b.name AS brand_name, i.client_id, c.name AS client_name, c.location, c.collector_id, i.invoice_no, i.doc_type, i.invoice_date, i.due_date, i.period_from, i.period_to, i.currency, i.fx_rate, i.grand_total, i.amount_paid, i.amount_adjusted, i.balance, i.base_grand_total, i.base_balance, i.status, i.reminder_stage, i.ptp_date, i.dispute_flag, DATEDIFF(CURDATE(), i.due_date) AS days_overdue, CASE WHEN i.balance <= 0 THEN 'settled' WHEN DATEDIFF(CURDATE(), i.due_date) <= 0 THEN 'current' WHEN DATEDIFF(CURDATE(), i.due_date) <= 30 THEN '1-30' WHEN DATEDIFF(CURDATE(), i.due_date) <= 60 THEN '31-60' WHEN DATEDIFF(CURDATE(), i.due_date) <= 90 THEN '61-90' ELSE '90+' END AS bucket FROM invoices i JOIN clients c ON c.id = i.client_id JOIN brands b ON b.id = i.brand_id WHERE i.status IN ('issued','partly_paid') AND i.doc_type <> 'credit_note'; CREATE OR REPLACE VIEW v_renewal_pipeline AS SELECT s.id, s.client_id, c.name AS client_name, s.brand_id, b.name AS brand_name, bi.name AS item_name, bi.revenue_head, s.title, s.rate, s.qty, s.billing_cycle, s.period_from, s.period_to, s.next_invoice_date, s.status, s.auto_invoice, s.escalation_pct, DATEDIFF(s.next_invoice_date, CURDATE()) AS days_to_bill FROM services s JOIN clients c ON c.id = s.client_id JOIN brands b ON b.id = s.brand_id JOIN billing_items bi ON bi.id = s.item_id WHERE s.status = 'active'; SET FOREIGN_KEY_CHECKS = 1; -- ---------- indexes, added after measuring at scale ---------- ALTER TABLE invoices ADD INDEX ix_inv_chase (status, balance, due_date); ALTER TABLE invoices ADD INDEX ix_inv_client_status (client_id, status); ALTER TABLE invoices ADD INDEX ix_inv_brand_date (brand_id, invoice_date); ALTER TABLE payments ADD INDEX ix_pay_client_date (client_id, pay_date); ALTER TABLE payment_allocations ADD INDEX ix_pa_invoice_kind (invoice_id, kind); ALTER TABLE invoice_lines ADD INDEX ix_il_service_period (service_id, period_from); ALTER TABLE services ADD INDEX ix_svc_due (status, next_invoice_date); ALTER TABLE services ADD INDEX ix_svc_expiry (status, period_to); ALTER TABLE outbox ADD INDEX ix_out_invoice_key (invoice_id, dedupe_key); ALTER TABLE tasks ADD INDEX ix_task_invoice_key (invoice_id, dedupe_key); ALTER TABLE suppressions ADD INDEX ix_supp_email_live (email, released_at);