Core Tables

Core Database Tables

Users & Clients

The system uses a two-table design for clients:

users Table

Stores authentication and personal information:

• id (INT, PRIMARY KEY)
• first_name (VARCHAR)
• last_name (VARCHAR)
• email (VARCHAR, UNIQUE)
• password (VARCHAR) - Bcrypt hash
• phone (VARCHAR)
• last_login (DATETIME)
• last_login_ip (VARCHAR)
• created_at (DATETIME)
• updated_at (DATETIME)

clients Table

Stores business and billing information:

• id (INT, PRIMARY KEY)
• user_id (INT, FOREIGN KEY → users.id)
• company_name (VARCHAR)
• address1, address2 (VARCHAR)
• city, state, postcode (VARCHAR)
• country (VARCHAR, 2-char code)
• credit_balance (DECIMAL)
• status (ENUM: active, suspended, closed)
• client_group_id (INT, FOREIGN KEY)
• account_number (VARCHAR, UNIQUE)

Products & Services

products Table

• id, category_id, name, slug
• description, price, billing_cycle
• module - Module name (e.g., "cpanel")
• requires_domain, free_domain_type
• allow_quantity, requires_approval
• status (active/inactive)
• server_id, whm_package_name

services Table

• id, client_id, product_id
• domain, status (active/suspended/cancelled)
• billing_cycle, next_due_date
• server_account_id - External server account ID

Billing

invoices Table

• id, client_id, invoice_number
• status (pending/paid/overdue/cancelled)
• total, currency
• due_date, paid_at
• token - For public invoice access

transactions Table

• id, invoice_id, client_id
• transaction_id - Gateway transaction ID
• gateway, amount, currency
• status (pending/completed/failed)
• gateway_response (JSON)

Relationships

• clients.user_id → users.id (1:1)
• services.client_id → clients.id (N:1)
• services.product_id → products.id (N:1)
• invoices.client_id → clients.id (N:1)
• transactions.invoice_id → invoices.id (N:1)