# Norbiz Lotto — Database Design

**Version:** 1.0  
**Stack:** Laravel 12 · MySQL 8+ (or PostgreSQL)  
**Audience:** Backend developers, DBAs, QA  
**References:** [Norbiz scope](/Users/sigosoftpvtltd/Downloads/Norbiz%20scope.md), [Game dev doc PDF](/Users/sigosoftpvtltd/Downloads/nobiz_dev_doc.pdf), [Admin design](https://ourworks.co.in/norbiz-lotto-design), [Mobile Figma](https://www.figma.com/design/np30NgbXAlMMrVoS7Yveym/Norbiz-Lotto?node-id=0-1), [Website Figma](https://www.figma.com/design/np30NgbXAlMMrVoS7Yveym/Norbiz-Lotto?node-id=86-904)

---

## 1. Design Principles

| Principle | Description |
|-----------|-------------|
| **Single user store** | All credentials and identity live in `users`. Role-specific data extends into profile tables (`admin_profiles`; `vendor_profiles` in a future phase). |
| **Immutable tickets** | Once created (and after draw cutoff), ticket rows are never edited. Corrections use reversal/adjustment transactions. |
| **Multiplier snapshots** | Payout multipliers are copied onto each ticket/line at purchase time. Admin changes never retroactively affect old tickets. |
| **Verifiable draws** | Raw Pick 3 + Pick 4 results are stored. The 7-digit draw and extracted winning positions are derived and stored for audit. |
| **Server-side truth** | Bet timestamps, cutoff enforcement, and wallet balances are always computed on the server. |
| **Multi-language** | User-facing CMS and notification content supports `en`, `fr`, `ht` (Haitian Creole). |
| **Sigosoft conventions** | Table/column naming, soft deletes, `status` flags, and audit columns follow patterns from `saimpex-backend` and `airotrack-backend`. |
| **Integer flags** | Use `$table->integer()` with `->comment()` for status/enum/flag columns. Do **not** use `tinyInteger()`. Example: `$table->integer('status')->default(1)->comment('1-Active, 2-blocked');` |

### Role Model

| `role_id` | Role | Primary clients | Phase |
|-----------|------|-----------------|-------|
| `1` | Admin | Vue admin dashboard | **1** |
| `2` | Customer | Flutter app, customer website | **1** |
| `3` | Vendor (Agent) | Vendor POS / agent app | **Future** |

**Phase 1:** Only Admin and Customer users are created and authenticated. Seed all three roles in `roles` for referential integrity; vendor-specific tables and `users` with `role_id = 3` are not used until the vendor phase.

---

## 2. Entity Relationship Overview

```
roles ─────────────┐
country_codes ─────┤
                   ├── users ──┬── customer_wallets ── wallet_transactions
                   │           └── admin_module_privileges
                   │
lottery_sources ─── draw_sessions ─── draw_results ─── draw_winning_positions
                   │                      │
game_types ────────┴── game_configs ─────┴── tickets ─── ticket_lines
                                              │
payment_methods ─── payment_transactions ─────┘
                   withdrawal_requests
cms_pages / faqs / banners / announcements
tchala_numbers / tchala_symbols
audit_logs / login_logs / config_change_logs
```

---

## 3. Core Identity & Access

### 3.1 `roles`

Lookup table for user roles.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | `1` Admin, `2` Customer, `3` Vendor |
| `name_en` | VARCHAR(100) | |
| `name_fr` | VARCHAR(100) | |
| `name_ht` | VARCHAR(100) | Haitian Creole |
| `created_at`, `updated_at` | TIMESTAMP | |

**Seed data:** Admin, Customer, Vendor (all three roles; vendor functionality deferred).

---

### 3.2 `country_codes`

Phone country codes for OTP login (+509 default for Haiti).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `country_name` | VARCHAR(100) | |
| `dial_code` | VARCHAR(10) | e.g. `+509` |
| `mobile_length` | VARCHAR(255) NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 3.3 `users` *(primary identity table)*

Central table for all user types with credentials.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `role_id` | BIGINT FK → `roles` | **Required.** `1` admin, `2` customer, `3` vendor (future) |
| `name` | VARCHAR(150) NULL | Full name |
| `email` | VARCHAR(150) NULL UNIQUE | Admin login; optional for customers |
| `username` | VARCHAR(100) NULL UNIQUE | Admin login (vendor username — future) |
| `country_code_id` | BIGINT FK NULL → `country_codes` | |
| `mobile` | VARCHAR(20) NULL | Customer/vendor phone login |
| `date_of_birth` | DATE NULL | Required for customers; 18+ validation |
| `password` | VARCHAR(255) NULL | Hashed; admin (vendor — future) |
| `image` | VARCHAR(255) NULL | Profile photo |
| `gender` | INTEGER NULL | `$table->integer('gender')->nullable()->comment('1-male, 2-female, 3-other');` |
| `id_proof_url` | VARCHAR(255) NULL | KYC document |
| `kyc_status` | INTEGER DEFAULT 0 | `$table->integer('kyc_status')->default(0)->comment('0-pending, 1-verified, 2-rejected');` |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-blocked, 3-frozen');` |
| `phone_verified` | INTEGER DEFAULT 0 | `$table->integer('phone_verified')->default(0)->comment('0-not_verified, 1-verified');` |
| `preferred_locale` | CHAR(5) DEFAULT `en` | `en`, `fr`, `ht` |
| `preferred_currency` | CHAR(3) DEFAULT `HTG` | `HTG`, `USD` |
| `fcm_token` | TEXT NULL | Push notification token |
| `notification_enabled` | BOOLEAN DEFAULT 1 | |
| `last_login_at` | TIMESTAMP NULL | |
| `last_login_ip` | VARCHAR(45) NULL | |
| `added_by` | BIGINT FK NULL → `users` | Who created this account |
| `updated_by` | BIGINT FK NULL → `users` | |
| `remember_token` | VARCHAR(100) NULL | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Indexes:** `(role_id, status)`, `(country_code_id, mobile)`, `email`, `username`.

**Notes:**
- Customers authenticate via phone + OTP (password optional/null).
- Admins authenticate via `username` + `password` (2FA optional future column).
- **Phase 1:** Only `role_id` 1 and 2 are used. Vendor users (`role_id = 3`) and vendor login are not implemented yet.

---

### 3.4 `otp_verifications`

OTP sessions for customer phone login and registration (vendor OTP — future phase).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `country_code_id` | BIGINT FK | |
| `mobile` | VARCHAR(20) | |
| `otp_code` | VARCHAR(10) | Hashed recommended |
| `purpose` | INTEGER | `$table->integer('purpose')->comment('1-login, 2-register, 3-withdraw_confirm');` |
| `expires_at` | DATETIME | |
| `is_used` | INTEGER DEFAULT 0 | `$table->integer('is_used')->default(0)->comment('0-not_used, 1-used');` |
| `attempt_count` | INT DEFAULT 0 | Rate limiting |
| `created_at`, `updated_at` | TIMESTAMP | |


### 3.5 `admin_module_privileges`

Granular admin permissions (optional sub-admins).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `module_id` | BIGINT FK → `modules` | |
| `can_view` | BOOLEAN DEFAULT 1 | |
| `can_create` | BOOLEAN DEFAULT 1 | |
| `can_update` | BOOLEAN DEFAULT 1 | |
| `can_delete` | BOOLEAN DEFAULT 0 | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 3.6 `modules`

Admin sidebar / permission modules.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `slug` | VARCHAR(100) UNIQUE | e.g. `draw_management` |
| `name_en`, `name_fr`, `name_ht` | VARCHAR(150) | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-Blocked');` |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 3.7 `vendor_profiles` — Future phase

> **Not in Phase 1.** Table may be created in a later migration when the vendor/agent network is built. Documented here for schema planning.

Agent/vendor extension (`role_id = 3`).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK UNIQUE → `users` | |
| `vendor_code` | VARCHAR(20) UNIQUE | Display/agent code |
| `business_name` | VARCHAR(200) NULL | |
| `address` | TEXT NULL | |
| `commission_rate` | DECIMAL(5,2) DEFAULT 0 | % on sales |
| `float_balance` | DECIMAL(14,2) DEFAULT 0 | Agent cash float |
| `float_limit` | DECIMAL(14,2) NULL | Max float |
| `region` | VARCHAR(100) NULL | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-suspended');` |
| `approved_by` | BIGINT FK NULL → `users` | |
| `approved_at` | TIMESTAMP NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

## 4. Lottery Sources, Draws & Results

### 4.1 `lottery_sources`

Official US state lottery sources.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `code` | VARCHAR(20) UNIQUE | `NY`, `FL`, `GA` |
| `name_en`, `name_fr`, `name_ht` | VARCHAR(150) | |
| `pick3_game_name` | VARCHAR(100) | e.g. `NY Pick 3` |
| `pick4_game_name` | VARCHAR(100) | e.g. `NY Pick 4` |
| `official_results_url` | VARCHAR(500) NULL | Verification link |
| `api_config` | JSON NULL | Future auto-fetch credentials |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-Blocked');` |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 4.2 `draw_sessions`

Scheduled draw windows (Midi, Evening, etc.).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `lottery_source_id` | BIGINT FK | |
| `slug` | VARCHAR(50) | e.g. `ny_midday` |
| `name_en`, `name_fr`, `name_ht` | VARCHAR(150) | e.g. New York Midday |
| `session_type` | INTEGER | `$table->integer('session_type')->comment('1-midday, 2-evening');` |
| `official_draw_time` | TIME | Approximate US ET draw |
| `betting_cutoff_time` | TIME | Server closes bets before draw |
| `timezone` | VARCHAR(50) DEFAULT `America/New_York` | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-enabled, 2-disabled');` |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 4.3 `draws`

A concrete draw instance for a calendar date + session.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `draw_session_id` | BIGINT FK | |
| `draw_date` | DATE | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-open, 2-cutoff, 3-awaiting_result, 4-result_entered, 5-processing, 6-completed, 7-cancelled');` |
| `betting_opened_at` | DATETIME NULL | |
| `betting_closed_at` | DATETIME NULL | Cutoff timestamp |
| `pick3_result` | CHAR(3) NULL | Raw official Pick 3 |
| `pick4_result` | CHAR(4) NULL | Raw official Pick 4 |
| `combined_draw_number` | CHAR(7) NULL | `pick3` + `pick4` concatenated |
| `result_source` | INTEGER NULL | `$table->integer('result_source')->nullable()->comment('1-manual, 2-api, 3-imported');` |
| `result_entered_by` | BIGINT FK NULL → `users` | Admin who entered |
| `result_verified_by` | BIGINT FK NULL → `users` | Optional second approval |
| `result_entered_at` | DATETIME NULL | |
| `processed_at` | DATETIME NULL | Winner engine completed |
| `total_bets_amount` | DECIMAL(16,2) DEFAULT 0 | Snapshot after close |
| `total_payout_amount` | DECIMAL(16,2) DEFAULT 0 | Snapshot after process |
| `notes` | TEXT NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

**`status` lifecycle:**  
`1` open → `2` cutoff (betting closed) → `3` awaiting_result → `4` result_entered → `5` processing → `6` completed → `7` cancelled

**Unique constraint:** `(draw_session_id, draw_date)`.

---

### 4.4 `draw_winning_positions`

Derived winning numbers extracted from the 7-digit draw. Stored for audit and fast result display.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `draw_id` | BIGINT FK → `draws` | |
| `position_key` | VARCHAR(30) | See keys below |
| `position_value` | VARCHAR(10) | Winning number string |
| `game_type_id` | BIGINT FK → `game_types` | |
| `created_at` | TIMESTAMP | |

**Position keys (from 7-digit draw `D1 D2 D3 D4 D5 D6 D7`):**

| Key | Value | Used by |
|-----|-------|---------|
| `pair_1` | D1D2 | Borlette, Marriage, Loto 5 |
| `pair_2` | D3D4 | Borlette, Marriage, Loto 5 |
| `pair_3` | D5D6 | Borlette, Marriage, Loto 5 |
| `loto3_a` | D1D2D3 | Loto 3, Loto 5 |
| `loto3_b` | D5D6D7 | Loto 3, Loto 5 |
| `loto4_a` | D1D2D3D4 | Loto 4 |
| `loto4_b` | D4D5D6D7 | Loto 4 |
| `loto5_combo` | `{loto3}_{pair}` | Up to 6 Loto 5 combos |

---

### 4.5 `draw_result_logs`

Immutable audit trail for result entry and changes.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `draw_id` | BIGINT FK | |
| `action` | VARCHAR(50) | `entered`, `verified`, `reprocessed` |
| `pick3_result` | CHAR(3) NULL | |
| `pick4_result` | CHAR(4) NULL | |
| `admin_id` | BIGINT FK → `users` | |
| `ip_address` | VARCHAR(45) | |
| `metadata` | JSON NULL | |
| `created_at` | TIMESTAMP | |

---

## 5. Games, Configuration & Risk Control

### 5.1 `game_types`

The five core Borlette-family games plus special bet modes.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `slug` | VARCHAR(30) UNIQUE | See slugs below |
| `name_en`, `name_fr`, `name_ht` | VARCHAR(150) | |
| `digit_count` | INTEGER | `$table->integer('digit_count')->comment('Base digit count for game type');` |
| `number_min` | VARCHAR(10) | e.g. `00` |
| `number_max` | VARCHAR(10) | e.g. `99` |
| `is_special_mode` | BOOLEAN DEFAULT 0 | Grap, Grap Pè L3, Maryaj |
| `parent_game_id` | BIGINT FK NULL | Special mode parent |
| `sort_order` | INT | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-Blocked');` |
| `created_at`, `updated_at` | TIMESTAMP | |

**Seed slugs:**

| Slug | Description |
|------|-------------|
| `borlette` | 2-digit Pè / Pairs (00–99) |
| `marriage` | Maryaj — two pairs must both match |
| `loto3` | 3-digit (000–999) |
| `loto4` | 4-digit (0000–9999) |
| `loto5` | Loto 3 + any pair (5 digits) |
| `grap` | Borlette special — all doubles 00,11,…,99 |
| `grap_pe_l3` | Loto 3 special — triples 000,111,…,999 |
| `maryaj_combo` | Auto pair combinations (customer app in Phase 1; vendor POS UI in future phase) |

---

### 5.2 `game_configurations`

Active payout multipliers and bet limits. **Current row = active config.** Historical values live in `game_configuration_logs`.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `game_type_id` | BIGINT FK | |
| `position_key` | VARCHAR(30) | e.g. `pair_1`, `loto3_a`, `marriage` |
| `multiplier` | DECIMAL(10,2) | Payout multiplier snapshot source |
| `min_bet` | DECIMAL(12,2) | Minimum wager |
| `max_bet_per_number` | DECIMAL(12,2) | Max per single number per draw |
| `max_total_sales_per_number` | DECIMAL(14,2) NULL | Risk cap per number per draw |
| `max_draw_payout` | DECIMAL(16,2) NULL | Total payout cap per draw |
| `currency` | CHAR(3) DEFAULT `HTG` | |
| `effective_from` | DATETIME | |
| `effective_to` | DATETIME NULL | NULL = currently active |
| `updated_by` | BIGINT FK → `users` | |
| `created_at`, `updated_at` | TIMESTAMP | |

**Default multipliers (admin-configurable, not hard-coded):**

| Game | Position | Default × |
|------|----------|-----------|
| Borlette | 1st pair (D1–D2) | 65 (scope) / 50 (PDF) — confirm with PO |
| Borlette | 2nd pair (D3–D4) | 20 |
| Borlette | 3rd pair (D5–D6) | 10 |
| Marriage | both pairs match | 500 |
| Loto 3 | Position A | 500 |
| Loto 3 | Position B | 200 |
| Loto 4 | Position A | 5,000 |
| Loto 4 | Position B | 2,000 |
| Loto 5 | combo win | 25,000 |
| Grap | any double hit | 60 per 1 HTG |
| Grap Pè L3 | triple hit | 1,000 per 1 HTG |
| Maryaj combo | per combo | 1,000 per 1 HTG (scope) |

---

### 5.3 `game_configuration_logs`

Audit log for every config change.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `game_configuration_id` | BIGINT FK NULL | |
| `game_type_id` | BIGINT FK | |
| `position_key` | VARCHAR(30) | |
| `field_name` | VARCHAR(50) | `multiplier`, `min_bet`, etc. |
| `old_value` | VARCHAR(100) | |
| `new_value` | VARCHAR(100) | |
| `admin_id` | BIGINT FK → `users` | |
| `ip_address` | VARCHAR(45) NULL | |
| `created_at` | TIMESTAMP | |

---

### 5.4 `number_sales_tracker`

Risk control — total exposure per number per draw.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `draw_id` | BIGINT FK | |
| `game_type_id` | BIGINT FK | |
| `number_value` | VARCHAR(10) | e.g. `47`, `472` |
| `total_bet_amount` | DECIMAL(14,2) DEFAULT 0 | Running total |
| `ticket_count` | INT DEFAULT 0 | |
| `is_blocked` | BOOLEAN DEFAULT 0 | Set when limit reached |
| `created_at`, `updated_at` | TIMESTAMP | |

**Unique:** `(draw_id, game_type_id, number_value)`.

---

## 6. Tickets & Betting

### 6.1 `tickets`

Order-level ticket record (one purchase transaction may contain multiple lines).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `ticket_code` | VARCHAR(20) UNIQUE | e.g. `A23455768` |
| `user_id` | BIGINT FK → `users` | Customer (payer) |
| `vendor_id` | BIGINT FK NULL → `users` | Agent who sold (`role_id=3`) — **NULL in Phase 1** |
| `draw_id` | BIGINT FK → `draws` | Auto-assigned next open draw |
| `channel` | INTEGER | `$table->integer('channel')->comment('1-app, 2-website, 3-vendor');` |
| `currency` | CHAR(3) | `HTG` / `USD` |
| `subtotal_amount` | DECIMAL(14,2) | Sum of line bets |
| `service_fee` | DECIMAL(12,2) DEFAULT 0 | Platform fee |
| `total_amount` | DECIMAL(14,2) | subtotal + fee |
| `payment_status` | INTEGER DEFAULT 0 | `$table->integer('payment_status')->default(0)->comment('0-pending, 1-paid, 2-failed, 3-refunded');` |
| `payment_transaction_id` | BIGINT FK NULL | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-pending, 2-active, 3-won, 4-lost, 5-cancelled, 6-paid_out');` |
| `purchased_at` | DATETIME | Server timestamp |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 6.2 `ticket_lines`

Individual bets within a ticket. One row = one wager.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `ticket_id` | BIGINT FK → `tickets` | |
| `game_type_id` | BIGINT FK | |
| `line_number` | INT | Display order |
| `number_primary` | VARCHAR(10) | Main number (e.g. `47`, `472`) |
| `number_secondary` | VARCHAR(10) NULL | Marriage 2nd pair, Loto5 pair part |
| `bet_mode` | VARCHAR(30) | `straight`, `grap`, `grap_pe_l3`, `maryaj`, `quick_pick` |
| `bet_amount` | DECIMAL(12,2) | Wager for this line |
| `multiplier_snapshot` | DECIMAL(10,2) | Locked at purchase |
| `position_key_snapshot` | VARCHAR(30) NULL | Which position pays (set on win) |
| `win_status` | INTEGER DEFAULT 0 | `$table->integer('win_status')->default(0)->comment('0-pending, 1-won, 2-lost');` |
| `win_amount` | DECIMAL(14,2) DEFAULT 0 | Calculated payout |
| `matched_position` | VARCHAR(30) NULL | e.g. `pair_1` |
| `processed_at` | DATETIME NULL | |
| `metadata` | JSON NULL | Maryaj combos, grap numbers expanded |
| `created_at`, `updated_at` | TIMESTAMP | |

**Business rules encoded in `metadata` JSON examples:**
- **Grap:** `{ "expanded_numbers": ["00","11",...,"99"] }`
- **Maryaj:** `{ "base_numbers": ["45","56","22","65"], "combos": [{"a":"45","b":"56","amount":25}, ...] }`
- **Grap Pè L3:** `{ "expanded_numbers": ["000","111",...,"999"] }`

**Immutability:** No UPDATE after draw cutoff except `win_status`, `win_amount`, `matched_position`, `processed_at`.

---

### 6.3 `ticket_receipts`

PDF / print receipt storage.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `ticket_id` | BIGINT FK UNIQUE | |
| `file_path` | VARCHAR(500) | |
| `generated_at` | DATETIME | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

## 7. Wallet & Payments

### 7.1 `customer_wallets`

One wallet per customer user.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK UNIQUE → `users` | `role_id = 2` |
| `balance` | DECIMAL(14,2) DEFAULT 0 | |
| `currency` | CHAR(3) DEFAULT `HTG` | |
| `is_frozen` | BOOLEAN DEFAULT 0 | Admin fraud freeze |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 7.2 `wallet_transactions`

All wallet credits and debits.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `wallet_id` | BIGINT FK | |
| `user_id` | BIGINT FK | |
| `transaction_type` | INTEGER | `$table->integer('transaction_type')->comment('1-credit, 2-debit');` |
| `source_type` | VARCHAR(50) | `deposit`, `withdrawal`, `ticket_purchase`, `win_payout`, `admin_adjustment`, `refund` |
| `source_id` | BIGINT NULL | Polymorphic reference ID |
| `amount` | DECIMAL(14,2) | |
| `balance_before` | DECIMAL(14,2) | |
| `balance_after` | DECIMAL(14,2) | |
| `currency` | CHAR(3) | |
| `description` | VARCHAR(255) NULL | |
| `reference_no` | VARCHAR(50) NULL | External ref |
| `created_by` | BIGINT FK NULL → `users` | Admin adjustments |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 7.3 `payment_methods`

Configured gateways.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `slug` | VARCHAR(30) UNIQUE | `moncash`, `paypal`, `visa`, `mastercard`, `wallet` |
| `name_en`, `name_fr`, `name_ht` | VARCHAR(100) | |
| `provider_config` | JSON NULL | API keys (encrypted at app level) |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-Blocked');` |
| `sort_order` | INT | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 7.4 `payment_transactions`

External payment records.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK | |
| `ticket_id` | BIGINT FK NULL | |
| `payment_method_id` | BIGINT FK | |
| `gateway_reference` | VARCHAR(100) NULL | |
| `amount` | DECIMAL(14,2) | |
| `currency` | CHAR(3) | |
| `status` | INTEGER DEFAULT 0 | `$table->integer('status')->default(0)->comment('0-initiated, 1-success, 2-failed, 3-refunded');` |
| `gateway_response` | JSON NULL | Raw webhook payload |
| `paid_at` | DATETIME NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 7.5 `withdrawal_requests`

Customer withdrawal with admin approval.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK | |
| `wallet_id` | BIGINT FK | |
| `amount` | DECIMAL(14,2) | |
| `currency` | CHAR(3) | |
| `payout_method` | VARCHAR(50) | `moncash`, `bank`, etc. |
| `payout_details` | JSON | Account/phone details |
| `status` | INTEGER DEFAULT 0 | `$table->integer('status')->default(0)->comment('0-pending, 1-approved, 2-rejected, 3-paid');` |
| `requested_at` | DATETIME | |
| `reviewed_by` | BIGINT FK NULL → `users` | |
| `reviewed_at` | DATETIME NULL | |
| `rejection_reason` | TEXT NULL | |
| `paid_at` | DATETIME NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 7.6 `vendor_commission_logs` — Future phase

> **Not in Phase 1.** Used when vendor/agent ticket sales are enabled.

Agent commission on ticket sales.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `vendor_id` | BIGINT FK → `users` | |
| `ticket_id` | BIGINT FK | |
| `ticket_amount` | DECIMAL(14,2) | |
| `commission_rate` | DECIMAL(5,2) | Snapshot |
| `commission_amount` | DECIMAL(12,2) | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-pending, 2-paid');` |
| `created_at`, `updated_at` | TIMESTAMP | |

---

## 8. CMS, Content & Tchala

### 8.1 `settings`

Global platform settings (single row or key-value).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `key` | VARCHAR(100) UNIQUE | e.g. `service_fee_percent` |
| `value` | TEXT | |
| `group` | VARCHAR(50) | `general`, `payment`, `maintenance` |
| `updated_by` | BIGINT FK NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

**Key settings:** service fee %, min age, OTP resend seconds (30), maintenance mode, app version gates.

---

### 8.2 `cms_pages`

Legal and static pages (multi-language).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `slug` | VARCHAR(50) UNIQUE | `terms`, `privacy`, `about`, `responsible_gaming` |
| `title_en`, `title_fr`, `title_ht` | VARCHAR(200) | |
| `content_en`, `content_fr`, `content_ht` | LONGTEXT | HTML/Markdown |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-Blocked');` |
| `updated_by` | BIGINT FK NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 8.3 `faqs`

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `category` | VARCHAR(50) NULL | `rules`, `prizes`, `general` |
| `question_en`, `question_fr`, `question_ht` | TEXT | |
| `answer_en`, `answer_fr`, `answer_ht` | TEXT | |
| `sort_order` | INT | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-Blocked');` |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 8.4 `banners`

Homepage / app carousel.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `title_en`, `title_fr`, `title_ht` | VARCHAR(200) NULL | |
| `image_url` | VARCHAR(500) | |
| `link_url` | VARCHAR(500) NULL | |
| `target_platform` | INTEGER | `$table->integer('target_platform')->comment('1-app, 2-website, 3-both');` |
| `sort_order` | INT | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-Blocked');` |
| `starts_at`, `ends_at` | DATETIME NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 8.5 `announcements`

Admin broadcast messages.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `title_en`, `title_fr`, `title_ht` | VARCHAR(200) | |
| `body_en`, `body_fr`, `body_ht` | TEXT | |
| `audience` | INTEGER | `$table->integer('audience')->comment('1-all, 2-customers, 3-vendors');` |
| `published_at` | DATETIME NULL | |
| `status` | INTEGER DEFAULT 1 | `$table->integer('status')->default(1)->comment('1-Active, 2-Blocked');` |
| `created_by` | BIGINT FK | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 8.6 `tchala_numbers`

Cultural dream-to-number reference (00–99).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `number` | CHAR(2) UNIQUE | `00`–`99` |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 8.7 `tchala_symbols`

Symbols/dreams mapped to numbers (many-to-many via this table).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `tchala_number_id` | BIGINT FK | |
| `symbol_en` | VARCHAR(200) | |
| `symbol_fr` | VARCHAR(200) | |
| `symbol_ht` | VARCHAR(200) | |
| `keywords` | TEXT NULL | Full-text search |
| `created_at`, `updated_at` | TIMESTAMP | |

---

## 9. Notifications & Logs

### 9.1 `notifications`

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK NULL | NULL = broadcast |
| `type` | VARCHAR(50) | `ticket_purchased`, `draw_reminder`, `win`, `wallet` |
| `title_en`, `title_fr`, `title_ht` | VARCHAR(200) | |
| `body_en`, `body_fr`, `body_ht` | TEXT | |
| `data` | JSON NULL | Deep link payload |
| `is_read` | BOOLEAN DEFAULT 0 | |
| `sent_at` | DATETIME NULL | |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 9.2 `login_logs`

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK NULL | |
| `role_id` | INTEGER | `$table->integer('role_id')->comment('1-admin, 2-customer, 3-vendor');` |
| `login_type` | VARCHAR(30) | `password`, `otp` |
| `ip_address` | VARCHAR(45) | |
| `user_agent` | TEXT NULL | |
| `status` | INTEGER | `$table->integer('status')->comment('1-success, 2-failed');` |
| `created_at` | TIMESTAMP | |

---

### 9.3 `audit_logs`

Admin and system activity.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK NULL | |
| `action` | VARCHAR(100) | `user_blocked`, `draw_result_entered`, etc. |
| `auditable_type` | VARCHAR(100) NULL | Model class |
| `auditable_id` | BIGINT NULL | |
| `old_values` | JSON NULL | |
| `new_values` | JSON NULL | |
| `ip_address` | VARCHAR(45) NULL | |
| `created_at` | TIMESTAMP | |

---

## 10. Laravel Framework Tables

Retain Laravel defaults (already in project):

| Table | Purpose |
|-------|---------|
| `personal_access_tokens` | Sanctum API tokens |
| `password_reset_tokens` | Admin password reset |
| `sessions` | Optional web sessions |
| `cache`, `cache_locks` | Cache |
| `jobs`, `job_batches`, `failed_jobs` | Queues (draw processing, notifications, webhooks) |

---

## 11. Indexing & Performance Notes

1. **Hot paths:** `tickets(draw_id, status)`, `ticket_lines(draw via ticket, win_status)`, `number_sales_tracker(draw_id, game_type_id, number_value)`.
2. **Draw processing:** Batch-update ticket lines after result entry; use queued job per draw.
3. **Wallet:** Use DB transactions with `SELECT ... FOR UPDATE` on `customer_wallets` row.
4. **Ticket codes:** Generate via dedicated service with uniqueness check + retry.
5. **Archival:** Partition or archive `ticket_lines` and `wallet_transactions` older than 24 months.

---

## 12. Migration Order (Suggested)

### Phase 1 (current)

```
1.  roles, country_codes, modules
2.  users, admin_module_privileges
3.  otp_verifications
4.  lottery_sources, draw_sessions, game_types
5.  game_configurations, game_configuration_logs
6.  draws, draw_winning_positions, draw_result_logs
7.  customer_wallets, payment_methods
8.  tickets, ticket_lines, number_sales_tracker
9.  payment_transactions, wallet_transactions, withdrawal_requests
10. settings, cms_pages, faqs, banners, announcements
11. tchala_numbers, tchala_symbols
12. notifications, login_logs, audit_logs
```

### Future — vendor / agent phase

```
13. vendor_profiles
14. vendor_commission_logs
```

`roles` must still seed `id = 3` (Vendor) in Phase 1 even though `vendor_profiles` and vendor APIs are deferred.

---

## 13. Open Items for Product Owner

| # | Question | Impact |
|---|----------|--------|
| 1 | Borlette 1st pair multiplier: 65 (scope) vs 50 (PDF)? | `game_configurations` seed |
| 2 | If same number matches multiple Borlette positions, pay highest only or cumulative? | Winner engine |
| 3 | Loto 4 "Position C" mentioned in PDF — confirm third winning position | `draw_winning_positions` |
| 4 | USD vs HTG: separate wallets or converted single balance? | `customer_wallets` |
| 5 | Auto-fetch US lottery API provider? | `lottery_sources.api_config` |
| 6 | Vendor float: prepaid balance or commission-only? | `vendor_profiles.float_balance` (future phase) |

---

*Document maintained in `docs/DATABASE_DESIGN.md`. Update version when schema changes.*