# DATABASE_DESIGN.md — NexStock Database Design

## Overview

NexStock uses a **single MySQL 8.x database** with **tenant isolation via `tenant_id` columns**. All business-owned tables include a `tenant_id` column with a foreign key to the `businesses` table.

## Design Principles

1. **Tenant isolation is mandatory** — every business-owned table has `tenant_id`
2. **UUIDs for primary keys** — all business entities use UUIDs for external safety
3. **Composite unique constraints** — unique columns include `tenant_id`
4. **Composite indexes** — all frequently queried columns include `tenant_id` prefix
5. **Soft deletes on transactions** — sales, purchases, returns use soft deletes
6. **Immutable movements** — inventory movements are never updated
7. **Referential integrity** — proper foreign keys with appropriate cascade rules
8. **Decimal precision** — monetary: `decimal(14,4)`, quantity: `decimal(12,4)`, rates: `decimal(5,2)`

## Migration Plan

Migrations are numbered and ordered by dependency. Below is the complete schema.

---

### Platform Tables (No tenant_id)

#### `businesses` — Tenant table
```sql
CREATE TABLE businesses (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    currency_code CHAR(3) NOT NULL DEFAULT 'TZS',
    timezone VARCHAR(64) NOT NULL DEFAULT 'Africa/Dar_es_Salaam',
    tax_name VARCHAR(50) DEFAULT 'VAT',
    tax_rate DECIMAL(5,2) DEFAULT 0.00,
    tax_enabled BOOLEAN DEFAULT FALSE,
    logo_path VARCHAR(500) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    address TEXT NULL,
    onboarding_completed BOOLEAN DEFAULT FALSE,
    onboarding_step TINYINT UNSIGNED DEFAULT 0,
    status ENUM('active','suspended','cancelled') DEFAULT 'active',
    settings JSON NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    INDEX idx_businesses_status (status),
    INDEX idx_businesses_slug (slug)
);
```

#### `plans`
```sql
CREATE TABLE plans (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT NULL,
    price_monthly DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    price_yearly DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);
```

#### `plan_entitlements`
```sql
CREATE TABLE plan_entitlements (
    id CHAR(36) PRIMARY KEY,
    plan_id CHAR(36) NOT NULL,
    feature VARCHAR(100) NOT NULL,
    `limit` INT NULL, -- null = unlimited
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE,
    UNIQUE KEY uq_plan_feature (plan_id, feature)
);
```

#### `subscriptions`
```sql
CREATE TABLE subscriptions (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    plan_id CHAR(36) NOT NULL,
    status ENUM('trial','active','past_due','cancelled','expired') DEFAULT 'trial',
    trial_ends_at TIMESTAMP NULL,
    starts_at TIMESTAMP NULL,
    ends_at TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,
    grace_ends_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id),
    INDEX idx_subscriptions_tenant (tenant_id),
    INDEX idx_subscriptions_status (status)
);
```

#### `platform_admins`
```sql
CREATE TABLE platform_admins (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);
```

---

### Core Tenant Tables

#### `branches`
```sql
CREATE TABLE branches (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    address TEXT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    INDEX idx_branches_tenant (tenant_id),
    INDEX idx_branches_tenant_active (tenant_id, is_active)
);
```

#### `users`
```sql
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NULL,
    role ENUM('owner','employee') DEFAULT 'employee',
    branch_id CHAR(36) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    two_factor_secret TEXT NULL,
    two_factor_confirmed_at TIMESTAMP NULL,
    email_verified_at TIMESTAMP NULL,
    last_login_at TIMESTAMP NULL,
    remember_token VARCHAR(100) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
    INDEX idx_users_tenant (tenant_id),
    INDEX idx_users_tenant_role (tenant_id, role),
    INDEX idx_users_email (email)
);
```

#### `user_permissions`
```sql
CREATE TABLE user_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    permission VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_user_permission (user_id, permission),
    INDEX idx_user_permissions_user (user_id)
);
```

---

### Product Tables

#### `categories`
```sql
CREATE TABLE categories (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    parent_id CHAR(36) NULL,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_categories_tenant (tenant_id),
    UNIQUE KEY uq_categories_name (tenant_id, name, parent_id)
);
```

#### `brands`
```sql
CREATE TABLE brands (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    INDEX idx_brands_tenant (tenant_id),
    UNIQUE KEY uq_brands_name (tenant_id, name)
);
```

#### `units`
```sql
CREATE TABLE units (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(100) NOT NULL,
    abbreviation VARCHAR(10) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    INDEX idx_units_tenant (tenant_id),
    UNIQUE KEY uq_units_name (tenant_id, name)
);
```

#### `suppliers`
```sql
CREATE TABLE suppliers (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    address TEXT NULL,
    notes TEXT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    INDEX idx_suppliers_tenant (tenant_id),
    INDEX idx_suppliers_tenant_active (tenant_id, is_active)
);
```

#### `customers`
```sql
CREATE TABLE customers (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    address TEXT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    INDEX idx_customers_tenant (tenant_id)
);
```

#### `products`
```sql
CREATE TABLE products (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    category_id CHAR(36) NULL,
    brand_id CHAR(36) NULL,
    unit_id CHAR(36) NOT NULL,
    preferred_supplier_id CHAR(36) NULL,
    tax_rate DECIMAL(5,2) NULL,
    reorder_level DECIMAL(12,4) DEFAULT 0,
    image_path VARCHAR(500) NULL,
    has_variants BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    is_archived BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL,
    FOREIGN KEY (unit_id) REFERENCES units(id),
    FOREIGN KEY (preferred_supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    INDEX idx_products_tenant (tenant_id),
    INDEX idx_products_tenant_active (tenant_id, is_active, is_archived),
    INDEX idx_products_tenant_category (tenant_id, category_id),
    INDEX idx_products_search (tenant_id, name)
);
```

#### `product_variants`
```sql
CREATE TABLE product_variants (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    product_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(100) NULL,
    barcode VARCHAR(100) NULL,
    purchase_cost DECIMAL(12,4) DEFAULT 0,
    selling_price DECIMAL(12,4) DEFAULT 0,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product_variants_tenant (tenant_id),
    INDEX idx_product_variants_product (product_id),
    UNIQUE KEY uq_variants_sku (tenant_id, sku),
    UNIQUE KEY uq_variants_barcode (tenant_id, barcode),
    INDEX idx_variants_barcode_lookup (tenant_id, barcode),
    INDEX idx_variants_sku_lookup (tenant_id, sku)
);
```

---

### Inventory Tables

#### `inventory_movements`
```sql
CREATE TABLE inventory_movements (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    product_variant_id CHAR(36) NOT NULL,
    type ENUM(
        'opening_balance','purchase_received','sale',
        'customer_return','supplier_return',
        'adjustment_add','adjustment_remove',
        'damage','loss','transfer_out','transfer_in'
    ) NOT NULL,
    quantity DECIMAL(12,4) NOT NULL, -- signed
    unit_cost DECIMAL(12,4) NOT NULL DEFAULT 0,
    total_cost DECIMAL(14,4) NOT NULL DEFAULT 0,
    balance_after DECIMAL(12,4) NOT NULL DEFAULT 0,
    average_cost_after DECIMAL(12,4) NOT NULL DEFAULT 0,
    reference_type VARCHAR(100) NULL,
    reference_id CHAR(36) NULL,
    notes TEXT NULL,
    user_id CHAR(36) NULL,
    posted_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (product_variant_id) REFERENCES product_variants(id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_movements_tenant_variant (tenant_id, product_variant_id),
    INDEX idx_movements_tenant_branch_variant (tenant_id, branch_id, product_variant_id),
    INDEX idx_movements_tenant_type (tenant_id, type),
    INDEX idx_movements_posted (tenant_id, posted_at),
    INDEX idx_movements_reference (reference_type, reference_id)
);
```

#### `inventory_balances`
```sql
CREATE TABLE inventory_balances (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    product_variant_id CHAR(36) NOT NULL,
    quantity_on_hand DECIMAL(12,4) DEFAULT 0,
    quantity_reserved DECIMAL(12,4) DEFAULT 0,
    quantity_available DECIMAL(12,4) DEFAULT 0,
    average_cost DECIMAL(12,4) DEFAULT 0,
    total_value DECIMAL(14,4) DEFAULT 0,
    last_movement_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (product_variant_id) REFERENCES product_variants(id),
    UNIQUE KEY uq_balance (tenant_id, branch_id, product_variant_id),
    INDEX idx_balances_low_stock (tenant_id, branch_id, quantity_on_hand),
    INDEX idx_balances_variant (tenant_id, product_variant_id)
);
```

---

### Sales Tables

#### `sales`
```sql
CREATE TABLE sales (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    customer_id CHAR(36) NULL,
    receipt_number VARCHAR(50) NOT NULL,
    subtotal DECIMAL(14,4) DEFAULT 0,
    tax_amount DECIMAL(14,4) DEFAULT 0,
    discount_amount DECIMAL(14,4) DEFAULT 0,
    total DECIMAL(14,4) DEFAULT 0,
    notes TEXT NULL,
    status ENUM('completed','held','voided') DEFAULT 'completed',
    completed_at TIMESTAMP NULL,
    idempotency_key VARCHAR(64) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    UNIQUE KEY uq_sales_receipt (tenant_id, receipt_number),
    UNIQUE KEY uq_sales_idempotency (tenant_id, idempotency_key),
    INDEX idx_sales_tenant (tenant_id),
    INDEX idx_sales_tenant_date (tenant_id, completed_at),
    INDEX idx_sales_tenant_status (tenant_id, status),
    INDEX idx_sales_tenant_branch (tenant_id, branch_id),
    INDEX idx_sales_tenant_user (tenant_id, user_id)
);
```

#### `sale_lines`
```sql
CREATE TABLE sale_lines (
    id CHAR(36) PRIMARY KEY,
    sale_id CHAR(36) NOT NULL,
    product_variant_id CHAR(36) NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    sku VARCHAR(100) NULL,
    quantity DECIMAL(12,4) NOT NULL,
    unit_price DECIMAL(12,4) NOT NULL,
    cost_price DECIMAL(12,4) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(12,4) DEFAULT 0,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    tax_amount DECIMAL(14,4) DEFAULT 0,
    line_total DECIMAL(14,4) NOT NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (product_variant_id) REFERENCES product_variants(id),
    INDEX idx_sale_lines_sale (sale_id),
    INDEX idx_sale_lines_variant (product_variant_id)
);
```

#### `sale_payments`
```sql
CREATE TABLE sale_payments (
    id CHAR(36) PRIMARY KEY,
    sale_id CHAR(36) NOT NULL,
    payment_method ENUM('cash','card','bank_transfer','mobile_money') NOT NULL,
    amount DECIMAL(14,4) NOT NULL,
    reference VARCHAR(255) NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    INDEX idx_sale_payments_sale (sale_id)
);
```

#### `sale_returns`
```sql
CREATE TABLE sale_returns (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    sale_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    return_number VARCHAR(50) NOT NULL,
    total_refund DECIMAL(14,4) NOT NULL DEFAULT 0,
    refund_method ENUM('cash','card','bank_transfer','mobile_money','exchange') NOT NULL,
    reason TEXT NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_return_number (tenant_id, return_number),
    INDEX idx_returns_tenant (tenant_id),
    INDEX idx_returns_sale (sale_id)
);
```

#### `sale_return_lines`
```sql
CREATE TABLE sale_return_lines (
    id CHAR(36) PRIMARY KEY,
    sale_return_id CHAR(36) NOT NULL,
    sale_line_id CHAR(36) NOT NULL,
    product_variant_id CHAR(36) NOT NULL,
    quantity DECIMAL(12,4) NOT NULL,
    unit_price DECIMAL(12,4) NOT NULL,
    refund_amount DECIMAL(14,4) NOT NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (sale_return_id) REFERENCES sale_returns(id) ON DELETE CASCADE,
    FOREIGN KEY (sale_line_id) REFERENCES sale_lines(id),
    FOREIGN KEY (product_variant_id) REFERENCES product_variants(id),
    INDEX idx_return_lines_return (sale_return_id)
);
```

---

### Purchase Tables

#### `purchases`
```sql
CREATE TABLE purchases (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    supplier_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    purchase_number VARCHAR(50) NOT NULL,
    subtotal DECIMAL(14,4) DEFAULT 0,
    tax_amount DECIMAL(14,4) DEFAULT 0,
    total DECIMAL(14,4) DEFAULT 0,
    status ENUM('draft','ordered','partially_received','received','cancelled') DEFAULT 'draft',
    notes TEXT NULL,
    purchase_date DATE NOT NULL,
    expected_date DATE NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_purchase_number (tenant_id, purchase_number),
    INDEX idx_purchases_tenant (tenant_id),
    INDEX idx_purchases_tenant_status (tenant_id, status),
    INDEX idx_purchases_tenant_supplier (tenant_id, supplier_id),
    INDEX idx_purchases_tenant_date (tenant_id, purchase_date)
);
```

#### `purchase_lines`
```sql
CREATE TABLE purchase_lines (
    id CHAR(36) PRIMARY KEY,
    purchase_id CHAR(36) NOT NULL,
    product_variant_id CHAR(36) NOT NULL,
    quantity_ordered DECIMAL(12,4) NOT NULL,
    quantity_received DECIMAL(12,4) DEFAULT 0,
    unit_cost DECIMAL(12,4) NOT NULL,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    tax_amount DECIMAL(14,4) DEFAULT 0,
    line_total DECIMAL(14,4) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (product_variant_id) REFERENCES product_variants(id),
    INDEX idx_purchase_lines_purchase (purchase_id)
);
```

#### `purchase_receivings`
```sql
CREATE TABLE purchase_receivings (
    id CHAR(36) PRIMARY KEY,
    purchase_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    received_at TIMESTAMP NOT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_receivings_purchase (purchase_id)
);
```

#### `purchase_receiving_lines`
```sql
CREATE TABLE purchase_receiving_lines (
    id CHAR(36) PRIMARY KEY,
    purchase_receiving_id CHAR(36) NOT NULL,
    purchase_line_id CHAR(36) NOT NULL,
    product_variant_id CHAR(36) NOT NULL,
    quantity_received DECIMAL(12,4) NOT NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (purchase_receiving_id) REFERENCES purchase_receivings(id) ON DELETE CASCADE,
    FOREIGN KEY (purchase_line_id) REFERENCES purchase_lines(id),
    FOREIGN KEY (product_variant_id) REFERENCES product_variants(id),
    INDEX idx_receiving_lines_receiving (purchase_receiving_id)
);
```

---

### Cash & Expense Tables

#### `cash_shifts`
```sql
CREATE TABLE cash_shifts (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    opening_amount DECIMAL(14,4) DEFAULT 0,
    expected_amount DECIMAL(14,4) DEFAULT 0,
    counted_amount DECIMAL(14,4) NULL,
    variance DECIMAL(14,4) NULL,
    status ENUM('open','closed') DEFAULT 'open',
    opened_at TIMESTAMP NOT NULL,
    closed_at TIMESTAMP NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_shifts_tenant (tenant_id),
    INDEX idx_shifts_tenant_status (tenant_id, status),
    INDEX idx_shifts_tenant_branch (tenant_id, branch_id)
);
```

#### `cash_movements`
```sql
CREATE TABLE cash_movements (
    id CHAR(36) PRIMARY KEY,
    cash_shift_id CHAR(36) NOT NULL,
    type ENUM('cash_sale','cash_refund','cash_in','cash_out') NOT NULL,
    amount DECIMAL(14,4) NOT NULL, -- signed
    description VARCHAR(500) NULL,
    reference_type VARCHAR(100) NULL,
    reference_id CHAR(36) NULL,
    user_id CHAR(36) NOT NULL,
    created_at TIMESTAMP NULL,
    
    FOREIGN KEY (cash_shift_id) REFERENCES cash_shifts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_cash_movements_shift (cash_shift_id)
);
```

#### `expense_categories`
```sql
CREATE TABLE expense_categories (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    INDEX idx_expense_categories_tenant (tenant_id),
    UNIQUE KEY uq_expense_category_name (tenant_id, name)
);
```

#### `expenses`
```sql
CREATE TABLE expenses (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    expense_category_id CHAR(36) NOT NULL,
    amount DECIMAL(14,4) NOT NULL,
    date DATE NOT NULL,
    description TEXT NULL,
    payment_method ENUM('cash','card','bank_transfer','mobile_money') NOT NULL,
    reference VARCHAR(255) NULL,
    user_id CHAR(36) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tenant_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (expense_category_id) REFERENCES expense_categories(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_expenses_tenant (tenant_id),
    INDEX idx_expenses_tenant_date (tenant_id, date),
    INDEX idx_expenses_tenant_category (tenant_id, expense_category_id)
);
```

---

### Audit Table

#### `audit_logs`
```sql
CREATE TABLE audit_logs (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NULL,
    user_id CHAR(36) NULL,
    action VARCHAR(100) NOT NULL,
    auditable_type VARCHAR(100) NULL,
    auditable_id CHAR(36) NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    metadata JSON NULL,
    created_at TIMESTAMP NULL,
    
    INDEX idx_audit_tenant (tenant_id),
    INDEX idx_audit_tenant_action (tenant_id, action),
    INDEX idx_audit_auditable (auditable_type, auditable_id),
    INDEX idx_audit_tenant_date (tenant_id, created_at),
    INDEX idx_audit_user (user_id)
);
```

---

## Index Strategy

### Composite Index Pattern
All tenant-scoped queries use `(tenant_id, ...)` as the leading column:
- `(tenant_id, status)` — filtered listings
- `(tenant_id, completed_at)` — date-range queries
- `(tenant_id, branch_id, product_variant_id)` — inventory lookups

### Full-Text Search
Consider adding FULLTEXT index on `products.name` for product search:
```sql
ALTER TABLE products ADD FULLTEXT idx_products_fulltext (name, description);
```

### Covering Indexes
For dashboard queries (counts, sums), consider covering indexes that include the aggregated columns to avoid table lookups.

## Locking Strategy

### Inventory Operations
Use `FOR UPDATE` row locking on `inventory_balances` during:
- Sale completion (stock deduction)
- Purchase receiving (stock addition)
- Stock adjustments
- Returns processing

```sql
-- Lock the specific inventory balance row before modification
SELECT * FROM inventory_balances 
WHERE tenant_id = ? AND branch_id = ? AND product_variant_id = ?
FOR UPDATE;
```

### Receipt Number Generation
Use `FOR UPDATE` on a sequence counter or use MySQL's `LAST_INSERT_ID()` pattern for gap-free sequential receipt numbers per tenant.

## Data Retention

| Data Type | Retention | Strategy |
|-----------|-----------|----------|
| Sales | Permanent | Soft delete only |
| Purchases | Permanent | Soft delete only |
| Inventory Movements | Permanent | Never delete |
| Audit Logs | 2 years | Archive to cold storage |
| Notifications | 90 days | Hard delete |
| Cache | TTL-based | Auto-expire |
| Sessions | 30 days | Auto-expire |