# Database Schema Documentation

## Overview

This document describes the complete database schema for the Fintech Laravel application. The schema has been generated based on comprehensive analysis of all models, controllers, and existing migrations to ensure complete compatibility with all admin operations, KYC processes, wallet funding, notifications, audit logs, and other system features.

## Files

- `database.sql` - MySQL/MariaDB compatible schema (29KB)
- `database_sqlite.sql` - SQLite compatible schema (20KB)
- `README.md` - This documentation file

## Critical Requirements Addressed

### ✅ Admin Operations Support
- Settings management with proper type casting
- User management (blocking, activation, KYC approval)
- Wallet operations (freeze/unfreeze, balance adjustments)
- Transaction oversight and admin adjustments
- API configuration management with encrypted credentials
- Notification broadcasting system
- Complete audit trail for compliance

### ✅ KYC Verification System
- Multiple document types: BVN, NIN, passport, driver's license, voter's card
- Provider integration support (Dojah, VerifyMe, etc.)
- Document expiration tracking
- Failure reason logging
- Status tracking: pending, verified, failed, expired

### ✅ Wallet & Transaction Management
- Multi-currency wallet support
- Balance locking for pending transactions
- Daily/monthly transaction limits with auto-reset
- Wallet freezing capabilities
- Complete transaction categorization including admin adjustments
- Fee and commission tracking

### ✅ Notification System
- Multi-channel support: email, SMS, push, in-app
- Broadcast notifications to user segments
- Read/unread tracking
- Admin-created notifications
- Metadata support for rich notifications

### ✅ Audit & Compliance
- Complete audit logs for all admin actions
- Both model-based and target-based tracking
- IP address and user agent logging
- Old/new value comparison
- Metadata and data field support for flexible logging

## Tables Overview

### Core Application Tables (18 total)

| Table | Purpose | Key Features |
|-------|---------|--------------|
| **users** | User accounts | Email/phone verification, 2FA, blocking, transaction PIN |
| **admins** | Admin users | Role-based permissions, activity tracking |
| **wallets** | Multi-currency wallets | Balance locking, limits, freezing |
| **transactions** | Financial transactions | 11 categories, 5 statuses, comprehensive metadata |
| **kyc_verifications** | Identity verification | 5 document types, provider integration |
| **virtual_cards** | Payment cards | Multiple providers, limits, status management |
| **virtual_accounts** | Bank accounts | Provider integration, metadata support |
| **services** | Bill payment services | Commission tracking, limits, categories |
| **api_configs** | External APIs | Encrypted credentials, health monitoring |
| **settings** | App configuration | Type-safe values, caching support |
| **notifications** | User messaging | Multi-channel, broadcast support |
| **audit_logs** | Compliance tracking | Complete admin action history |
| **cron_logs** | Task monitoring | Execution tracking, error logging |
| **webhooks** | External events | Retry logic, status tracking |

### Laravel Framework Tables (4 total)
- **cache** - Application cache
- **password_reset_tokens** - Password reset functionality  
- **personal_access_tokens** - API authentication
- **sessions** - User session storage

## Key Schema Features

### Performance Optimizations
- Strategic indexes on frequently queried columns
- Composite indexes for common query patterns
- Foreign key constraints with proper cascading
- Optimized for admin dashboard queries

### Security & Compliance
- Encrypted API credentials in api_configs
- Complete audit trail with IP tracking
- User blocking and wallet freezing capabilities
- Transaction PIN security

### Data Integrity
- Foreign key constraints with appropriate actions
- Check constraints for enum values
- Not null constraints where required
- Unique constraints for critical identifiers

### Flexibility
- JSON metadata fields for extensibility
- Multiple status values for different workflows
- Provider-agnostic design for integrations
- Type-safe settings system

## Usage Instructions

### MySQL/MariaDB Production Setup
```bash
mysql -u username -p database_name < database/database.sql
```

### SQLite Development Setup
```bash
sqlite3 database/app.db < database/database_sqlite.sql
```

### Laravel Integration
The schema is designed to work seamlessly with all existing Laravel models and controllers without any code changes required.

## Default Configuration

The schema includes 28 default settings covering:
- Business configuration (name, tagline)
- Feature toggles (wallet funding, P2P transfers, virtual cards)
- Fee structures (percentage vs flat fees)
- Transaction limits (daily, single transaction)
- Security requirements (KYC thresholds, 2FA requirements)
- Service limits (airtime, card limits)

## Validation Results

✅ **Schema Import**: Both MySQL and SQLite versions import without errors  
✅ **Laravel Compatibility**: All models work correctly with new schema  
✅ **Admin Functions**: All admin operations supported  
✅ **KYC Workflow**: Complete verification process supported  
✅ **Wallet Operations**: All wallet functions operational  
✅ **Audit Logging**: Complete compliance tracking enabled  
✅ **Notifications**: Multi-channel notification system ready  

## Migration from Existing Database

If migrating from the existing migration-based setup:

1. **Backup existing data** (critical!)
2. **Export data** from existing tables
3. **Import new schema** using provided SQL files
4. **Import backed up data** with any necessary transformations
5. **Update any status values** to match new enum constraints
6. **Verify all admin functions** work correctly

## Maintenance Notes

- Regular cleanup of audit_logs table recommended (90+ days old)
- Monitor cron_logs table size
- Cache table can be cleared safely anytime
- Settings table changes require cache invalidation

This schema provides a solid foundation for the fintech application with complete admin functionality, compliance tracking, and room for future growth.