Database Schema

The Add-on Services system uses a comprehensive database schema designed for scalability, performance, and data integrity.

Core Tables Overview

┌─────────────────────────────────────────────────────────────┐
│                     Database Structure                      │
├─────────────────────────────────────────────────────────────┤
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────┐ │
│  │ billing_        │  │ billing_add_    │  │ billing_    │ │
│  │ subscriptions   │◄─┤ ons_plans      │◄─┤ add_ons_    │ │
│  │                 │  │                 │  │ payments    │ │
│  └─────────────────┘  └─────────────────┘  └─────────────┘ │
│           │                     │                          │
│           │                     │                          │
│  ┌────────▼─────┐  ┌────────────▼────┐  ┌─────────────────┐ │
│  │ billing_     │  │ billing_credits │  │ billing_credits │ │
│  │ credits_     │  │ _usages        │  │ _issuances     │ │
│  │ threshold_   │  │                │  │                │ │
│  │ settings     │  └────────────────┘  └─────────────────┘ │
│  └──────────────┘           │                             │
│           │                  │                             │
│  ┌────────▼──────────────────▼─────────────────────────────┐ │
│  │ billing_credits_threshold_notifications                │ │
│  └────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘

Table Definitions

1. billing_subscriptions

Main service subscription definitions for add-on services.

CREATE TABLE billing_subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,      -- Service display name (e.g., "SMS Messaging")
    credits_label VARCHAR(50) NOT NULL,      -- Credit unit label (e.g., "sms", "pages")
    service_link VARCHAR(255),               -- Link to service usage page
    available_quantity BIGINT DEFAULT 0,     -- Total available credits (calculated)
    status_name ENUM('Active', 'Inactive') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,               -- Soft delete support
    
    -- Performance indexes
    INDEX idx_status_product (status_name, product_name),
    INDEX idx_available_quantity (available_quantity),
    UNIQUE KEY uk_product_name (product_name, deleted_at)
);

-- Sample data
INSERT INTO billing_subscriptions VALUES
(1, 'SMS Messaging', 'sms', '/sms-service', 0, 'Active', NOW(), NOW(), NULL),
(2, 'Eva Docs', 'pages', '/lpo-processing', 0, 'Active', NOW(), NOW(), NULL);

Key Business Rules:

product_name is the unique identifier for each service type
credits_label determines how credits are referenced in UI
service_link directs users to service usage page
available_quantity is calculated sum from all plans

2. billing_add_ons_plans

Specific pricing plans within each service subscription.

CREATE TABLE billing_add_ons_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscription_id BIGINT NOT NULL,   -- FK to billing_subscriptions
    erp_code BIGINT,                  -- External ERP system reference
    company_erp_code VARCHAR(20),     -- Company-specific ERP code
    name VARCHAR(100) NOT NULL,       -- Plan display name
    quantity BIGINT NOT NULL,         -- Credits per purchase
    rate FLOAT(10,2) NOT NULL,       -- Price per plan
    rate_type ENUM('rate', 'pricing_plan') NOT NULL, -- Pricing model
    available_quantity DECIMAL(10,2) DEFAULT 0, -- Current available credits
    status_name ENUM('Active', 'Inactive') DEFAULT 'Active',
    erp_sync_status TINYINT DEFAULT 0, -- ERP synchronization status
    erp_sync_response TEXT,           -- ERP sync response log
    recommended TINYINT DEFAULT 0,    -- Featured plan flag
    notes TEXT,                       -- Additional plan information
    created_by_name VARCHAR(150),     -- Creator identification
    updated_by_name VARCHAR(150),     -- Last modifier identification
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,        -- Soft delete support
    
    -- Foreign key constraints
    CONSTRAINT fk_plans_subscription 
        FOREIGN KEY (subscription_id) REFERENCES billing_subscriptions(id),
    
    -- Performance indexes
    INDEX idx_subscription_status (subscription_id, status_name),
    INDEX idx_rate_type (rate_type),
    INDEX idx_available_quantity (available_quantity),
    UNIQUE KEY uk_erp_code (erp_code)
);

-- Sample data
INSERT INTO billing_add_ons_plans VALUES
(1, 1, 20001, 'COMP001', 'Basic SMS Plan', 100, 500.00, 'pricing_plan', 0, 'Active', 1, NULL, 0, 'Entry level SMS package', 'System', NULL, NOW(), NOW(), NULL),
(2, 1, 20002, 'COMP001', 'Premium SMS Plan', 500, 2000.00, 'pricing_plan', 0, 'Active', 1, NULL, 1, 'Popular SMS package', 'System', NULL, NOW(), NOW(), NULL),
(3, 1, 20003, 'COMP001', 'SMS Rate', 1, 5.00, 'rate', 0, 'Active', 1, NULL, 0, 'Pay per SMS', 'System', NULL, NOW(), NOW(), NULL);

Key Business Rules:

rate_type = 'pricing_plan': Fixed quantity for fixed price (bundles)
rate_type = 'rate': Variable quantity at unit price (pay-per-use)
available_quantity: Actual credits available for consumption
recommended = 1: Featured plan in UI

3. billing_add_ons_payments

Payment transaction records for credit purchases.

CREATE TABLE billing_add_ons_payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    billing_add_ons_plans_id INT NOT NULL, -- FK to billing_add_ons_plans
    payment_id VARCHAR(255),           -- FK to main payments table
    payment_method ENUM('mpesa', 'card', 'complimentary') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,     -- Amount paid
    credits INT NOT NULL,              -- Credits purchased
    user_id INT NOT NULL,              -- User who made payment
    payment_reference VARCHAR(255),    -- Payment gateway reference
    invoiced_at TIMESTAMP NULL,        -- When invoice was generated
    invoice_number VARCHAR(255) UNIQUE, -- Unique invoice identifier
    invoice_document TEXT,             -- Invoice document URL/path
    invoice_status ENUM('Pending', 'Closed') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,         -- Soft delete support
    
    -- Foreign key constraints
    CONSTRAINT fk_payments_plan 
        FOREIGN KEY (billing_add_ons_plans_id) REFERENCES billing_add_ons_plans(id),
    CONSTRAINT fk_payments_user 
        FOREIGN KEY (user_id) REFERENCES tbl_users(id),
    
    -- Performance indexes
    INDEX idx_plan_user (billing_add_ons_plans_id, user_id),
    INDEX idx_payment_reference (payment_reference),
    INDEX idx_invoice_status (invoice_status),
    INDEX idx_created_at (created_at)
);

Key Business Rules:

payment_method = 'complimentary': Admin-granted credits (amount = 0)
invoice_status = 'Pending': Awaiting payment confirmation
invoice_status = 'Closed': Payment confirmed and processed
Credits are allocated only after successful payment verification

4. billing_credits_usages

Tracks credit consumption and holds.

CREATE TABLE billing_credits_usages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscription_id INT NOT NULL,      -- FK to billing_subscriptions
    product_item_plan_id INT NOT NULL, -- FK to billing_add_ons_plans
    quantity BIGINT NOT NULL,          -- Credits consumed/held
    hold TINYINT DEFAULT 0,           -- 1 = held, 0 = consumed
    created_by BIGINT NOT NULL,        -- User who consumed credits
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraints
    CONSTRAINT fk_usage_subscription 
        FOREIGN KEY (subscription_id) REFERENCES billing_subscriptions(id),
    CONSTRAINT fk_usage_plan 
        FOREIGN KEY (product_item_plan_id) REFERENCES billing_add_ons_plans(id),
    CONSTRAINT fk_usage_user 
        FOREIGN KEY (created_by) REFERENCES tbl_users(id),
    
    -- Performance indexes
    INDEX idx_subscription_hold (subscription_id, hold),
    INDEX idx_plan_created (product_item_plan_id, created_at),
    INDEX idx_user_created (created_by, created_at)
);

Key Business Rules:

hold = 1: Credits reserved for pending operations
hold = 0: Credits permanently consumed
FIFO consumption: Oldest available credits used first
Supports credit release mechanism for failed operations

5. billing_credits_issuances

Tracks credit additions to accounts.

CREATE TABLE billing_credits_issuances (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscription_id INT NOT NULL,      -- FK to billing_subscriptions
    product_item_plan_id INT NOT NULL, -- FK to billing_add_ons_plans
    quantity BIGINT NOT NULL,          -- Credits issued
    amount BIGINT NOT NULL,            -- Amount paid for credits
    created_by INT NOT NULL,           -- Who issued the credits
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraints
    CONSTRAINT fk_issuance_subscription 
        FOREIGN KEY (subscription_id) REFERENCES billing_subscriptions(id),
    CONSTRAINT fk_issuance_plan 
        FOREIGN KEY (product_item_plan_id) REFERENCES billing_add_ons_plans(id),
    CONSTRAINT fk_issuance_user 
        FOREIGN KEY (created_by) REFERENCES tbl_users(id),
    
    -- Performance indexes
    INDEX idx_subscription_created (subscription_id, created_at),
    INDEX idx_plan_amount (product_item_plan_id, amount)
);

Key Business Rules:

Created automatically when payments are confirmed
amount: Links credit issuance to financial transaction
Audit trail for all credit additions to the system

6. billing_subscription_threshold_settings

Threshold configuration for low credit alerts.

CREATE TABLE billing_subscription_threshold_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscription_id INT NOT NULL,      -- FK to billing_subscriptions
    threshold INT NOT NULL,            -- Absolute threshold value
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    created_by INT NOT NULL,           -- User who set threshold
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,         -- Soft delete support
    
    -- Foreign key constraints
    CONSTRAINT fk_threshold_subscription 
        FOREIGN KEY (subscription_id) REFERENCES billing_subscriptions(id),
    CONSTRAINT fk_threshold_user 
        FOREIGN KEY (created_by) REFERENCES tbl_users(id),
    
    -- Performance indexes
    INDEX idx_subscription_status (subscription_id, status),
    UNIQUE KEY uk_subscription_active (subscription_id, status, deleted_at)
);

-- Sample data
INSERT INTO billing_subscription_threshold_settings VALUES
(1, 1, 50, 'Active', 1, NOW(), NOW(), NULL),  -- SMS threshold: 50 credits
(2, 2, 10, 'Active', 1, NOW(), NOW(), NULL);  -- LPO threshold: 10 pages

Key Business Rules:

Only one active threshold per subscription
Thresholds use absolute values, not percentages
Threshold breach triggers notification system

7. billing_credits_threshold_notifications

Log of threshold breach notifications.

CREATE TABLE billing_credits_threshold_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,                       -- User notified (can be NULL for system)
    subscription_id INT NOT NULL,      -- FK to billing_subscriptions
    subscription_threshold_id INT,     -- FK to threshold settings
    threshold_level ENUM('healthy', 'low', 'empty') NOT NULL,
    credits INT NOT NULL,              -- Credit balance at notification
    activity ENUM('Cron', 'Remove Credits') NOT NULL, -- Trigger type
    resolved TINYINT DEFAULT 0,        -- Whether issue was resolved
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraints
    CONSTRAINT fk_notification_subscription 
        FOREIGN KEY (subscription_id) REFERENCES billing_subscriptions(id),
    CONSTRAINT fk_notification_threshold 
        FOREIGN KEY (subscription_threshold_id) REFERENCES billing_subscription_threshold_settings(id),
    CONSTRAINT fk_notification_user 
        FOREIGN KEY (user_id) REFERENCES tbl_users(id),
    
    -- Performance indexes
    INDEX idx_subscription_activity (subscription_id, activity),
    INDEX idx_threshold_level (threshold_level),
    INDEX idx_created_at (created_at)
);

Key Business Rules:

activity = 'Cron': Scheduled threshold checks
activity = 'Remove Credits': Real-time consumption alerts
resolved = 1: Notification acknowledged or credits replenished
Prevents duplicate notifications for same threshold breach

Database Relationships

Relationship Diagram

-- Visual relationship representation
billing_subscriptions (1) ----< billing_add_ons_plans (many)
billing_add_ons_plans (1) ----< billing_add_ons_payments (many)
billing_add_ons_payments (1) ----< billing_credits_issuances (many)
billing_subscriptions (1) ----< billing_credits_usages (many)
billing_subscriptions (1) ----< billing_subscription_threshold_settings (1)
billing_subscription_threshold_settings (1) ----< billing_credits_threshold_notifications (many)

Data Validation Rules

Application-Level Validations:

Credit Balance Consistency: Regular reconciliation between plan balances and subscription totals
Payment Verification: Confirmation required before credit allocation
Threshold Logic: Prevent invalid threshold configurations
Hold Management: Automatic cleanup of expired credit holds

This comprehensive database schema provides a robust foundation for the Add-on Services system, ensuring data integrity, performance, and scalability while supporting complex business requirements.

Database Schema

On this page