-- =========================================================================== -- Migration: billing & subscriptions (platform-level) -- Date: 2026-07-28 -- -- Adds the SaaS billing layer on top of the platform-admin tier: -- plans — the pricing catalogue (price, cycle, trial, limits, SMS terms) -- subscriptions — one per farm; effective terms (seeded from a plan, overridable) -- billing_invoices — subscription / SMS / manual charges -- billing_payments — manually-recorded payments against those invoices -- sms_credit_ledger — prepaid SMS credit movement (top-ups + per-send usage) -- -- SMS is billed either PREPAID (a credit balance on the subscription that -- decrements per successful send and blocks at zero) or POSTPAID (usage metered -- from sms_log and invoiced at each renewal). schema.sql carries all of this for -- fresh installs. Idempotent (CREATE TABLE IF NOT EXISTS); safe to re-run. -- =========================================================================== CREATE TABLE IF NOT EXISTS plans ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL, description VARCHAR(255) NULL, price DECIMAL(12,2) NOT NULL DEFAULT 0, -- subscription price per cycle currency CHAR(3) NOT NULL DEFAULT 'UGX', billing_cycle ENUM('monthly','quarterly','annual') NOT NULL DEFAULT 'monthly', trial_days INT UNSIGNED NOT NULL DEFAULT 0, max_birds INT UNSIGNED NULL, -- NULL = unlimited max_users INT UNSIGNED NULL, -- NULL = unlimited sms_mode ENUM('prepaid','postpaid') NOT NULL DEFAULT 'postpaid', sms_rate DECIMAL(10,2) NOT NULL DEFAULT 0, -- price charged to the farm per SMS sms_included INT UNSIGNED NOT NULL DEFAULT 0, -- free SMS allowance per cycle (postpaid) is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS subscriptions ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, plan_id INT UNSIGNED NULL, -- NULL once fully custom / plan deleted status ENUM('trial','active','past_due','suspended','cancelled') NOT NULL DEFAULT 'trial', price DECIMAL(12,2) NOT NULL DEFAULT 0, -- effective price (override of plan) currency CHAR(3) NOT NULL DEFAULT 'UGX', billing_cycle ENUM('monthly','quarterly','annual') NOT NULL DEFAULT 'monthly', trial_ends_on DATE NULL, period_start DATE NULL, period_end DATE NULL, -- next due / renewal date max_birds INT UNSIGNED NULL, max_users INT UNSIGNED NULL, sms_mode ENUM('prepaid','postpaid') NOT NULL DEFAULT 'postpaid', sms_rate DECIMAL(10,2) NOT NULL DEFAULT 0, sms_included INT UNSIGNED NOT NULL DEFAULT 0, sms_balance INT NOT NULL DEFAULT 0, -- prepaid credit balance (SMS count); signed to tolerate a race notes VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_sub_farm (farm_id), KEY idx_sub_plan (plan_id), CONSTRAINT fk_sub_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS billing_invoices ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, invoice_no VARCHAR(40) NOT NULL, type ENUM('subscription','sms','manual') NOT NULL DEFAULT 'subscription', description VARCHAR(255) NULL, period_start DATE NULL, period_end DATE NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, currency CHAR(3) NOT NULL DEFAULT 'UGX', status ENUM('unpaid','paid','void') NOT NULL DEFAULT 'unpaid', issued_on DATE NOT NULL, due_on DATE NULL, paid_on DATE NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_binv (farm_id, invoice_no), KEY idx_binv_status (farm_id, status), CONSTRAINT fk_binv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS billing_payments ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, invoice_id INT UNSIGNED NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, method VARCHAR(40) NULL, reference VARCHAR(80) NULL, paid_on DATE NOT NULL, note VARCHAR(255) NULL, recorded_by INT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_bpay_farm (farm_id, paid_on), CONSTRAINT fk_bpay_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_bpay_invoice FOREIGN KEY (invoice_id) REFERENCES billing_invoices(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sms_credit_ledger ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, delta INT NOT NULL, -- +top-up / -usage / +/- adjustment balance_after INT NOT NULL, reason VARCHAR(30) NOT NULL, -- 'topup' | 'usage' | 'adjustment' sms_log_id INT UNSIGNED NULL, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_smscredit_farm (farm_id, created_at), CONSTRAINT fk_smscredit_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- A couple of starter plans so the catalogue isn't empty on first open. Inserted -- only if no plans exist yet, so re-running never duplicates them. INSERT INTO plans (name, description, price, currency, billing_cycle, trial_days, max_birds, max_users, sms_mode, sms_rate, sms_included) SELECT * FROM (SELECT 'Starter' AS name, 'Small farm — core records & reports' AS description, 50000 AS price, 'UGX' AS currency, 'monthly' AS billing_cycle, 14 AS trial_days, 2000 AS max_birds, 3 AS max_users, 'postpaid' AS sms_mode, 45.00 AS sms_rate, 50 AS sms_included) t WHERE NOT EXISTS (SELECT 1 FROM plans); INSERT INTO plans (name, description, price, currency, billing_cycle, trial_days, max_birds, max_users, sms_mode, sms_rate, sms_included) SELECT * FROM (SELECT 'Pro' AS name, 'Growing farm — higher limits & SMS bundle' AS description, 150000 AS price, 'UGX' AS currency, 'monthly' AS billing_cycle, 14 AS trial_days, NULL AS max_birds, 10 AS max_users, 'postpaid' AS sms_mode, 40.00 AS sms_rate, 300 AS sms_included) t WHERE (SELECT COUNT(*) FROM plans) = 1;