# Database Schema (Core Tables)

Only the tables that drive the critical flows are detailed here; the rest (referrals, support tickets, seo, etc.) follow the same conventions and are listed at the bottom.

## networks
| column | type | notes |
|---|---|---|
| id | bigint pk | |
| name | varchar | MTN, Airtel, Glo, 9mobile |
| code | varchar unique | machine code, e.g. `mtn` |
| logo_path | varchar | |
| is_active | boolean | |

## data_plans
| column | type | notes |
|---|---|---|
| id | bigint pk | |
| network_id | fk → networks | |
| name | varchar | e.g. "1GB - 30 Days" |
| size_mb | int | |
| validity_days | int | |
| api_cost | decimal(10,2) | admin-only, never returned to customer |
| customer_price | decimal(10,2) | |
| min_purchase | decimal / null | |
| max_purchase | decimal / null | |
| is_active | boolean | |

## api_providers
| column | type | notes |
|---|---|---|
| id | bigint pk | |
| name | varchar | |
| service_type | enum | data, airtime, electricity, cable, education |
| network_id | fk nullable | some providers are network-specific |
| base_url | varchar | |
| request_method | varchar | |
| timeout_seconds | int | |
| priority | int | lower = tried first, per (network_id, service_type) |
| success_count / failure_count | int | rolling counters for health |
| status | enum | active, disabled, degraded |

## api_credentials
| column | type | notes |
|---|---|---|
| id | bigint pk | |
| api_provider_id | fk | |
| key_name | varchar | api_key, secret_key, username, password, token... |
| value | text (encrypted cast) | never selected by default (`$hidden`) |

## payment_gateways
| column | type | notes |
|---|---|---|
| id | bigint pk | |
| name | varchar | Monnify, Paystack, Flutterwave, PalmPay |
| public_key / api_key (encrypted) | text | |
| secret_key (encrypted) | text | |
| contract_code (encrypted, nullable) | text | Monnify-specific |
| webhook_secret (encrypted) | text | |
| environment | enum | sandbox, live |
| is_active | boolean | |
| is_default | boolean | only one true at a time (DB constraint via unique partial index / app-level check) |

## transactions
| column | type | notes |
|---|---|---|
| id | bigint pk | |
| reference | varchar unique | public-facing, e.g. VTU-20260905-8F72K9 |
| customer_phone | varchar | |
| customer_id | fk nullable | null for guest purchases |
| service_type | enum | data, airtime, electricity, cable, education |
| network_id | fk nullable | |
| data_plan_id | fk nullable | |
| amount | decimal(10,2) | customer-facing price |
| api_cost | decimal(10,2) | |
| profit | decimal(10,2) | generated column: amount - api_cost |
| payment_gateway_id | fk | |
| api_provider_id | fk nullable | set once dispatched |
| payment_status | enum | CREATED, PAYMENT_PENDING, PAYMENT_SUCCESS, PAYMENT_FAILED, REFUND_PENDING, REFUNDED |
| service_status | enum | PENDING, PROCESSING, SUCCESS, FAILED, API_TIMEOUT, API_ERROR, PENDING_REVIEW |
| idempotency_key | varchar unique | |
| attempts | int default 0 | |
| created_at / updated_at | timestamps | |

Indexes: `reference` (unique), `customer_phone`, `(payment_status, service_status)`, `idempotency_key` (unique).

## payments
| column | type | notes |
|---|---|---|
| id | bigint pk | |
| transaction_id | fk | |
| gateway_reference | varchar | ID from Paystack/Flutterwave/etc |
| amount | decimal | |
| status | enum | pending, verified, failed |
| verified_at | timestamp nullable | set only after server-side verification call |
| raw_response | json | sanitized (no secrets) |

## wallet_accounts / wallet_ledger_entries
`wallet_accounts`: id, user_id, balance (cached), currency.
`wallet_ledger_entries`: id, wallet_account_id, transaction_reference, direction (credit/debit), amount, balance_before, balance_after, description, type, created_at. **Insert-only.**

## webhooks / webhook_logs
`webhook_logs`: id, source (gateway/provider name), reference, signature_valid (bool), payload (json, redacted), http_status_returned, processed_at, is_duplicate (bool).

## audit_logs
id, admin_id, action, model, model_id, old_value (json), new_value (json), ip_address, user_agent, created_at. **Immutable — no update/delete route exists for this table.**

## Other tables (same conventions: fk + indexes + timestamps)
users, admins, roles, permissions, role_permissions, services, refunds, referrals, api_logs (redacted, no secrets), notifications, login_logs, fraud_events, reconciliation_records, system_settings, support_tickets.