-- ============================================================================ -- RETAYL WHATSAPP PLATFORM — schema -- -- Retayl Enterprise provides the WhatsApp Cloud API to client organisations -- (first client: Shree Halari Visa Oswal Samaj Mumbai) and bills them -- monthly, per template. -- -- Design notes that matter: -- -- * Every send goes through the central API, so template_name is always -- known. Meta's pricing callback names the CATEGORY but never the -- TEMPLATE, so send time is the only moment it can be captured. -- -- * The amount is stored on the message row, not recomputed from today's -- rate card. A finalised invoice must stay reproducible after a rate -- change. -- -- * billable / category / pricing_type come from Meta's status callback, -- not from us. A utility template sent inside an open service window is -- free, and nothing on our side can work that out. -- -- * Service messages are free for the first 1,000 per WABA per calendar -- month. The allowance is tracked here AND taken from Meta's own flag, -- so the two can be compared — see retayl_service_usage. -- -- Run once. Safe to re-run (IF NOT EXISTS / INSERT IGNORE throughout). -- ============================================================================ -- --------------------------------------------------------------------------- -- TWO DATABASES -- -- Retayl's commercial data lives in its own database, the client's platform -- data stays where it is. Same MySQL server and same user, so one PHP -- connection reaches both and cross-database joins work normally. -- -- Names used in this file: -- `shvosm_retayl` — Retayl: rates, keys, ledger, invoices -- `shvosm_shvosm` — the client: whatsapp_numbers, whatsapp_chat, members -- -- cPanel prefixes database names with the account, so create the new one in -- cPanel -> MySQL Databases as "retayl" and it becomes shvosm_retayl. Then -- ADD THE SAME USER to it with ALL PRIVILEGES, or nothing below will run. -- -- If your names differ, find and replace both strings here AND set -- RETAYL_DB in lib/retayl_config.php to match. -- --------------------------------------------------------------------------- SET NAMES utf8mb4; CREATE DATABASE IF NOT EXISTS `shvosm_retayl` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 1. CLIENTS -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_clients ( id INT(11) NOT NULL AUTO_INCREMENT, code VARCHAR(32) NOT NULL, -- short slug, e.g. 'shvosm' name VARCHAR(200) NOT NULL, contact_name VARCHAR(120) DEFAULT NULL, contact_email VARCHAR(190) DEFAULT NULL, contact_phone VARCHAR(30) DEFAULT NULL, address TEXT DEFAULT NULL, currency VARCHAR(3) NOT NULL DEFAULT 'INR', billing_day TINYINT(2) NOT NULL DEFAULT 1, -- day of the following month is_active TINYINT(1) NOT NULL DEFAULT 1, notes TEXT DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT IGNORE INTO `shvosm_retayl`.retayl_clients (code, name, contact_phone, currency) VALUES ('shvosm', 'Shree Halari Visa Oswal Samaj Mumbai', '919270717797', 'INR'); -- ---------------------------------------------------------------------------- -- 2. LIFETIME API KEYS -- -- The plaintext key is shown ONCE at creation and never stored — only a -- SHA-256 hash. key_prefix is the first 14 characters, kept so a key can -- be identified in a list and in logs without being usable. -- -- "Lifetime" = no expiry. Revoking sets is_active = 0, which is why the -- column exists rather than deleting the row: a revoked key must still -- resolve for the messages it already sent. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_api_keys ( id INT(11) NOT NULL AUTO_INCREMENT, client_id INT(11) NOT NULL, label VARCHAR(120) NOT NULL, -- 'SHVOSM mobile app', 'admin panel', 'crons' key_prefix VARCHAR(20) NOT NULL, key_hash CHAR(64) NOT NULL, -- sha256 of the full key scopes VARCHAR(255) NOT NULL DEFAULT 'send', -- csv: send,service,read allowed_ips VARCHAR(255) DEFAULT NULL, -- optional csv allowlist is_active TINYINT(1) NOT NULL DEFAULT 1, last_used_at DATETIME DEFAULT NULL, last_used_ip VARCHAR(45) DEFAULT NULL, use_count INT(11) NOT NULL DEFAULT 0, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, created_by VARCHAR(120) DEFAULT NULL, revoked_at DATETIME DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uniq_hash (key_hash), KEY idx_client (client_id, is_active), KEY idx_prefix (key_prefix) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------------------------- -- 3. NUMBERS BELONG TO A CLIENT -- whatsapp_numbers already exists; this ties each number to a client and -- marks one as the default sender. -- ---------------------------------------------------------------------------- -- IF NOT EXISTS is MariaDB syntax (cPanel ships MariaDB), so this ALTER is -- re-runnable. On stock MySQL it errors the second time — harmless, skip it. ALTER TABLE `shvosm_shvosm`.whatsapp_numbers ADD COLUMN IF NOT EXISTS client_id INT(11) NULL DEFAULT NULL AFTER phone_number_id, ADD COLUMN IF NOT EXISTS is_default TINYINT(1) NOT NULL DEFAULT 0, ADD KEY IF NOT EXISTS idx_client (client_id); UPDATE `shvosm_shvosm`.whatsapp_numbers SET client_id = (SELECT id FROM `shvosm_retayl`.retayl_clients WHERE code = 'shvosm') WHERE client_id IS NULL; UPDATE `shvosm_shvosm`.whatsapp_numbers SET is_default = 1 WHERE phone_number_id = '869282609608504'; -- ---------------------------------------------------------------------------- -- 4. TEMPLATE REGISTRY -- -- param_style is here because of a real failure: Meta accepts either -- positional {{1}} or named {{name}} variables, and the send payload must -- match. A named template sent without parameter_name fails with code 100 -- "Parameter name is missing or empty"; a positional one sent WITH it -- fails too. Storing the style means the API builds the right payload -- instead of the caller guessing. -- -- param_names is the ordered list, csv. For a named template these are -- the parameter_name values; for positional it is just labels so the -- portal can show what {{1}} means. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_templates ( id INT(11) NOT NULL AUTO_INCREMENT, client_id INT(11) NOT NULL, template_name VARCHAR(100) NOT NULL, -- as registered with Meta language VARCHAR(10) NOT NULL DEFAULT 'en', category VARCHAR(20) NOT NULL DEFAULT 'utility', -- utility/authentication/marketing waba_id VARCHAR(32) DEFAULT NULL, -- which WABA holds it phone_number_id VARCHAR(32) DEFAULT NULL, -- default sender for this template label VARCHAR(160) DEFAULT NULL, -- human name, e.g. 'Birthday wish' description TEXT DEFAULT NULL, param_style ENUM('named','positional','none') NOT NULL DEFAULT 'none', param_names VARCHAR(255) DEFAULT NULL, -- csv, in order body_preview TEXT DEFAULT NULL, -- the approved body, for the portal is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_tpl (client_id, template_name, language), KEY idx_active (client_id, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- The templates already in use. Rates are set separately and are REQUIRED. INSERT IGNORE INTO `shvosm_retayl`.retayl_templates (client_id, template_name, language, category, waba_id, phone_number_id, label, param_style, param_names) SELECT c.id, 'birthday', 'gu', 'utility', '1414814083812151', '1217486651445027', 'Birthday wish (Gujarati)', 'named', 'name' FROM `shvosm_retayl`.retayl_clients c WHERE c.code = 'shvosm'; -- ---------------------------------------------------------------------------- -- 5. PER-TEMPLATE RATES (Retayl's prices to the client) -- -- A rate applies from effective_from until a later row supersedes it, so -- editing prices never rewrites what past months cost. -- -- Rates are REQUIRED: a send whose template has no rate in force is still -- delivered and logged, but marked rate_source='unpriced' so it shows up -- as something to fix before the month is invoiced. Never silently zero. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_template_rates ( id INT(11) NOT NULL AUTO_INCREMENT, template_id INT(11) NOT NULL, rate DECIMAL(10,4) NOT NULL, currency VARCHAR(3) NOT NULL DEFAULT 'INR', effective_from DATE NOT NULL, note VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, created_by VARCHAR(120) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uniq_tpl_date (template_id, effective_from), KEY idx_tpl (template_id, effective_from) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------------------------- -- 6. SERVICE MESSAGE RATE + FREE ALLOWANCE -- -- Free-form replies from the inbox are "service" messages. Meta gives -- 1,000 free per WABA per calendar month; past that they are charged. -- -- allowance_scope matters commercially: -- 'waba' — each WABA gets its own 1,000 (what Meta actually does). -- SHVOSM has two WABAs, so 2,000 free in practice. -- 'client' — pooled at one allowance across the client's numbers. -- Retayl keeps the difference. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_service_rates ( id INT(11) NOT NULL AUTO_INCREMENT, client_id INT(11) NOT NULL, rate DECIMAL(10,4) NOT NULL DEFAULT 0, currency VARCHAR(3) NOT NULL DEFAULT 'INR', free_allowance INT(11) NOT NULL DEFAULT 1000, allowance_scope ENUM('waba','client') NOT NULL DEFAULT 'waba', effective_from DATE NOT NULL, note VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_client_date (client_id, effective_from) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT IGNORE INTO `shvosm_retayl`.retayl_service_rates (client_id, rate, free_allowance, allowance_scope, effective_from, note) SELECT id, 0.0000, 1000, 'waba', '2026-10-01', 'SET THE RATE — 1000 free per WABA per month, chargeable after' FROM `shvosm_retayl`.retayl_clients WHERE code = 'shvosm'; -- ---------------------------------------------------------------------------- -- 7. THE MESSAGE LEDGER — one row per outbound message -- -- This is the billable record. whatsapp_chat stays the conversation view; -- this is what the invoice is built from. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_messages ( id BIGINT(20) NOT NULL AUTO_INCREMENT, client_id INT(11) NOT NULL, message_id VARCHAR(255) DEFAULT NULL, -- wamid; NULL if the send failed phone_number_id VARCHAR(32) DEFAULT NULL, waba_id VARCHAR(32) DEFAULT NULL, recipient VARCHAR(20) DEFAULT NULL, kind ENUM('template','service') NOT NULL DEFAULT 'template', template_id INT(11) DEFAULT NULL, template_name VARCHAR(100) DEFAULT NULL, language VARCHAR(10) DEFAULT NULL, -- Meta's own pricing verdict, filled in by the status webhook category VARCHAR(20) DEFAULT NULL, pricing_type VARCHAR(40) DEFAULT NULL, billable TINYINT(1) DEFAULT NULL, -- NULL until Meta reports pricing_model VARCHAR(10) DEFAULT NULL, -- what we charge rate_applied DECIMAL(10,4) DEFAULT NULL, amount DECIMAL(12,4) NOT NULL DEFAULT 0, currency VARCHAR(3) NOT NULL DEFAULT 'INR', rate_source ENUM('template','service','free','unpriced') NOT NULL DEFAULT 'unpriced', free_reason VARCHAR(40) DEFAULT NULL, -- meta_free / within_allowance / failed status VARCHAR(20) DEFAULT NULL, -- sent/delivered/read/failed error_code VARCHAR(20) DEFAULT NULL, error_message TEXT DEFAULT NULL, api_key_id INT(11) DEFAULT NULL, source VARCHAR(60) DEFAULT NULL, -- caller-declared, e.g. 'app:otp' dedupe_key VARCHAR(120) DEFAULT NULL, invoice_id INT(11) DEFAULT NULL, -- set when invoiced; locks the row sent_at DATETIME DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_wamid (message_id), UNIQUE KEY uniq_dedupe (client_id, dedupe_key), KEY idx_period (client_id, sent_at), KEY idx_template (template_name, sent_at), KEY idx_invoice (invoice_id), KEY idx_unpriced (client_id, rate_source, sent_at), KEY idx_waba (waba_id, kind, sent_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------------------------- -- 8. SERVICE ALLOWANCE TALLY, per WABA per month -- -- Two counts on purpose: -- counted_here — our own running count of service messages -- meta_billable — how many Meta actually flagged billable -- -- They should agree once past 1,000. If they do not, the portal says so -- rather than quietly billing the wrong number. Nobody has crossed 1,000 -- yet (176 service messages in two months), so this is unverified against -- live data — which is exactly why both numbers are kept. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_service_usage ( client_id INT(11) NOT NULL, waba_id VARCHAR(32) NOT NULL, period CHAR(7) NOT NULL, -- 'YYYY-MM' counted_here INT(11) NOT NULL DEFAULT 0, meta_billable INT(11) NOT NULL DEFAULT 0, meta_free INT(11) NOT NULL DEFAULT 0, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (client_id, waba_id, period) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------------------------- -- 9. INVOICES -- -- status: draft — recalculated freely, nothing locked -- final — lines frozen, message rows stamped with invoice_id -- paid — final plus a payment date -- -- No GST fields: Retayl Enterprise is not GST-registered, so the document -- is a bill of supply, not a tax invoice. If that changes later, add the -- tax columns here rather than reworking the lines. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_invoices ( id INT(11) NOT NULL AUTO_INCREMENT, client_id INT(11) NOT NULL, period CHAR(7) NOT NULL, -- 'YYYY-MM' number VARCHAR(40) DEFAULT NULL, -- assigned on finalise status ENUM('draft','final','paid') NOT NULL DEFAULT 'draft', currency VARCHAR(3) NOT NULL DEFAULT 'INR', total_messages INT(11) NOT NULL DEFAULT 0, billable_count INT(11) NOT NULL DEFAULT 0, free_count INT(11) NOT NULL DEFAULT 0, unpriced_count INT(11) NOT NULL DEFAULT 0, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0, generated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, finalised_at DATETIME DEFAULT NULL, finalised_by VARCHAR(120) DEFAULT NULL, paid_at DATETIME DEFAULT NULL, visible_to_client TINYINT(1) NOT NULL DEFAULT 0, -- client sees it only when 1 notes TEXT DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uniq_client_period (client_id, period), UNIQUE KEY uniq_number (number), KEY idx_status (client_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_invoice_lines ( id INT(11) NOT NULL AUTO_INCREMENT, invoice_id INT(11) NOT NULL, line_type ENUM('template','service','adjustment') NOT NULL DEFAULT 'template', template_name VARCHAR(100) DEFAULT NULL, label VARCHAR(200) DEFAULT NULL, category VARCHAR(20) DEFAULT NULL, qty INT(11) NOT NULL DEFAULT 0, free_qty INT(11) NOT NULL DEFAULT 0, rate DECIMAL(10,4) NOT NULL DEFAULT 0, amount DECIMAL(12,2) NOT NULL DEFAULT 0, sort_order INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_invoice (invoice_id, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------------------------- -- 10. PORTAL LOGIN (Retayl staff — separate from the client's admin_users) -- -- Seed row below: username 'retayl', password 'ChangeMe!2026'. -- CHANGE IT ON FIRST LOGIN. The hash is a real password_hash() of that -- string, so the account works immediately and is obviously temporary. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_admins ( id INT(11) NOT NULL AUTO_INCREMENT, username VARCHAR(60) NOT NULL, password_hash VARCHAR(255) NOT NULL, name VARCHAR(120) DEFAULT NULL, role ENUM('owner','staff') NOT NULL DEFAULT 'staff', is_active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME DEFAULT NULL, last_login_ip VARCHAR(45) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT IGNORE INTO `shvosm_retayl`.retayl_admins (username, password_hash, name, role) VALUES ('retayl', '$2y$12$NBo8vGaVLy3g5ZpOoHGWNuQ1kwyZB7ndmHeVa5SPOQXSMFLf9QXnO', 'Retayl Owner', 'owner'); -- ---------------------------------------------------------------------------- -- 11. AUDIT — who changed a rate, finalised an invoice, revoked a key -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `shvosm_retayl`.retayl_audit ( id BIGINT(20) NOT NULL AUTO_INCREMENT, actor VARCHAR(120) DEFAULT NULL, action VARCHAR(60) NOT NULL, object VARCHAR(60) DEFAULT NULL, object_id VARCHAR(60) DEFAULT NULL, detail TEXT DEFAULT NULL, ip VARCHAR(45) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_when (created_at), KEY idx_obj (object, object_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;