# Cash Lending System - Technical Specifications

## System Overview

A comprehensive, production-ready Cash Lending Management System built with PHP, MySQL, and Bootstrap 5. Designed for small to medium-sized lending institutions and businesses.

---

## Technical Stack

### Backend
- **Language:** PHP 7.4+
- **Database:** MySQL 5.7+
- **Architecture:** MVC-inspired procedural PHP
- **Session Management:** Native PHP Sessions
- **Password Hashing:** bcrypt (PASSWORD_BCRYPT)

### Frontend
- **Framework:** Bootstrap 5.3
- **CSS:** Native CSS3 with custom styles
- **JavaScript:** Vanilla JS + jQuery (optional)
- **Icons:** Font Awesome 6.4
- **Charts:** Chart.js 3.9+

### Server Requirements
- Apache 2.4+ with mod_rewrite
- PHP 7.4 or higher
- MySQL 5.7 or MariaDB 10.3+
- 10MB minimum disk space
- OpenSSL support

---

## Database Architecture

### Tables & Relationships

```
users (1) ──────────── (M) payments
         └─ created_by

borrowers (1) ───────── (M) loans
                         │
                         └─(M) payments
```

### Table Specifications

#### users
```sql
- id (PRIMARY KEY, AUTO_INCREMENT)
- username (VARCHAR 50, UNIQUE)
- password (VARCHAR 255)
- full_name (VARCHAR 100)
- role (ENUM: admin, staff)
- email (VARCHAR 100)
- phone (VARCHAR 20)
- is_active (BOOLEAN)
- created_at (TIMESTAMP)
- updated_at (TIMESTAMP)

Indexes: username, is_active
```

#### borrowers
```sql
- id (PRIMARY KEY, AUTO_INCREMENT)
- first_name (VARCHAR 50)
- last_name (VARCHAR 50)
- address (TEXT)
- contact (VARCHAR 20, UNIQUE)
- id_type (VARCHAR 50)
- id_number (VARCHAR 50, UNIQUE)
- occupation (VARCHAR 100)
- status (ENUM: active, inactive)
- created_at (TIMESTAMP)
- updated_at (TIMESTAMP)

Indexes: status, created_at
```

#### loans
```sql
- id (PRIMARY KEY, AUTO_INCREMENT)
- borrower_id (FOREIGN KEY → borrowers.id)
- loan_amount (DECIMAL 12,2)
- interest_rate (DECIMAL 5,2)
- term (INT) - in days
- release_date (DATE)
- due_date (DATE)
- payment_mode (ENUM: daily, weekly, monthly)
- penalty (DECIMAL 5,2)
- total_interest (DECIMAL 12,2)
- total_payable (DECIMAL 12,2)
- installment_amount (DECIMAL 12,2)
- balance (DECIMAL 12,2)
- status (ENUM: pending, approved, paid, overdue)
- created_at (TIMESTAMP)
- updated_at (TIMESTAMP)

Indexes: borrower_id, status, due_date, release_date
```

#### payments
```sql
- id (PRIMARY KEY, AUTO_INCREMENT)
- loan_id (FOREIGN KEY → loans.id)
- payment_date (DATE)
- amount (DECIMAL 12,2)
- penalty_paid (DECIMAL 12,2)
- notes (TEXT)
- payment_method (VARCHAR 50)
- receipt_number (VARCHAR 50, UNIQUE)
- created_by (FOREIGN KEY → users.id)
- created_at (TIMESTAMP)

Indexes: loan_id, payment_date, created_by
```

#### settings
```sql
- id (PRIMARY KEY)
- company_name (VARCHAR 100)
- default_interest (DECIMAL 5,2)
- penalty (DECIMAL 5,2)
- logo (VARCHAR 255)
- phone (VARCHAR 20)
- address (TEXT)
- email (VARCHAR 100)
- created_at (TIMESTAMP)
- updated_at (TIMESTAMP)
```

---

## Security Implementation

### Password Security
- bcrypt hashing with default cost of 10
- Minimum 6 characters required
- Password verification using `password_verify()`

### SQL Security
- Parameterized/Prepared statements throughout
- Input sanitization with htmlspecialchars()
- Type casting on all numeric inputs

### Session Security
- Session timeout: 24 hours (configurable)
- Secure session cookies
- User role verification on protected pages
- Logout clears all session data

### Data Protection
- User credentials stored securely
- Audit trail via created_by in payments
- Non-deletable transaction records
- Soft deletes for borrowers (status field)

---

## API Endpoints

### Internal APIs (JSON)

#### /api/loan-details.php
- **Method:** GET
- **Parameters:** id (loan_id)
- **Response:** Loan details with calculations
- **Usage:** AJAX for dynamic form loading

#### /api/delete-user.php
- **Method:** POST
- **Parameters:** id (user_id)
- **Response:** JSON success/error
- **Usage:** User deletion with confirmation

#### /api/export-report.php
- **Method:** GET
- **Parameters:** year, month
- **Response:** CSV file download
- **Usage:** Report export functionality

---

## Calculation Formulas

### Interest Calculation
```
Total Interest = (Loan Amount × Interest Rate) / 100
```

### Total Payable
```
Total Payable = Loan Amount + Total Interest
```

### Installment Amount
```
Number of Installments = 
  - Daily: Term days
  - Weekly: ceil(Term / 7)
  - Monthly: ceil(Term / 30)

Installment Amount = Total Payable / Number of Installments
```

### Balance After Payment
```
New Balance = Previous Balance - Payment Amount
Loan Status = 'paid' if New Balance <= 0
```

### Overdue Detection
```
IS_OVERDUE = (Current Date > Due Date) AND (Status IN ['pending', 'approved'])
Auto-status = 'overdue' on scheduled update
```

---

## File Structure & Purposes

```
cash_lending/
│
├── config/
│   └── db.php                      # Database configuration & connection
│
├── includes/
│   ├── auth.php                    # Authentication & authorization
│   ├── functions.php               # Business logic functions
│   ├── header.php                  # HTML head & CSS
│   ├── sidebar.php                 # Navigation & topbar
│   └── footer.php                  # HTML close & JavaScript
│
├── api/
│   ├── loan-details.php            # Get loan info for forms
│   ├── delete-user.php             # User deletion API
│   └── export-report.php           # CSV export
│
├── database/
│   ├── setup.sql                   # Initial schema
│   └── sample-data.sql             # Test data
│
├── Core Pages
│   ├── index.php                   # Redirect to login/dashboard
│   ├── login.php                   # Authentication form
│   ├── logout.php                  # Session termination
│   └── dashboard.php               # Main dashboard
│
├── Borrower Management
│   ├── borrowers.php               # List all borrowers
│   ├── add-borrower.php            # Create borrower form
│   └── edit-borrower.php           # Edit borrower form
│
├── Loan Management
│   ├── loans.php                   # List all loans
│   ├── add-loan.php                # Create loan form
│   └── loan-details.php            # Loan details & history
│
├── Payment Management
│   ├── payments.php                # List all payments
│   ├── add-payment.php             # Record payment form
│   └── receipt.php                 # Payment receipt/invoice
│
├── Reports & Admin
│   ├── reports.php                 # Analytics & reports
│   ├── settings.php                # System settings
│   ├── users.php                   # User management
│   ├── add-user.php                # Create user form
│   ├── edit-user.php               # Edit user form
│   └── profile.php                 # User profile
│
├── Configuration
│   ├── .htaccess                   # Server rewrite rules
│   ├── README.md                   # Documentation
│   ├── INSTALLATION.md             # Setup guide
│   └── TECHNICAL-SPECS.md          # This file
```

---

## User Roles & Permissions

### Admin
- ✓ Full system access
- ✓ User management
- ✓ System settings
- ✓ View all loans/borrowers/payments
- ✓ Edit/delete records
- ✓ View reports

### Staff
- ✓ Add borrowers
- ✓ Edit borrowers
- ✓ View loans
- ✓ Create loans
- ✓ Record payments
- ✓ View payment history
- ✓ Generate receipts
- ✗ User management
- ✗ System settings
- ✗ Edit/delete loans
- ✗ Access admin features

---

## Performance Considerations

### Database Optimization
- Indexed foreign keys
- Indexed frequently filtered columns
- Proper data types (DECIMAL for money)
- Prepared statements to prevent full table scans

### Query Optimization
- Use of LIMIT for lists
- JOIN operations instead of multiple queries
- COUNT aggregates for statistics
- Selective field selection

### Frontend Optimization
- Bootstrap CDN for fast delivery
- Lazy loaded images
- Minimized CSS/JS files
- Browser caching headers

---

## Scalability Notes

### Current Capacity
- Up to 10,000 borrowers
- Up to 50,000 loans
- Up to 500,000 payments
- Suitable for 50 concurrent users

### Optimization Path
- Add database indexing
- Implement pagination
- Cache frequently accessed data
- Move to NoSQL for large datasets
- Implement API rate limiting

---

## Backup & Recovery

### Database Backup
```bash
# Manual backup
mysqldump -u root -p cash_lending_db > backup.sql

# Automated backup (cron job)
0 2 * * * mysqldump -u root cash_lending_db > /backups/db-$(date +\%Y\%m\%d).sql
```

### Recovery
```bash
mysql -u root -p cash_lending_db < backup.sql
```

---

## Maintenance Schedule

| Task | Frequency | Importance |
|------|-----------|-----------|
| Database Backup | Daily | Critical |
| Review Logs | Weekly | High |
| Update Passwords | Monthly | High |
| Archive Old Data | Quarterly | Medium |
| Security Audit | Quarterly | High |
| System Updates | As needed | Medium |

---

## Monitoring & Logging

### Current Implementation
- Error logging to file
- User action tracking via created_by
- Transaction audit trail in payments table

### Recommended Additions
- Login attempt logging
- Sensitive operation audit log
- Error stack traces
- Performance monitoring

---

## Future Enhancement Opportunities

1. **Mobile App** - React Native/Flutter
2. **SMS Notifications** - Payment reminders
3. **Email Integration** - Receipts & statements
4. **Advanced Analytics** - BI dashboards
5. **Multi-branch Support** - Branch management
6. **API Layer** - RESTful API for integrations
7. **Two-Factor Authentication** - Enhanced security
8. **Automated Collections** - SMS/Email reminders
9. **Integration** - Accounting software connection
10. **Mobile-First UI** - Progressive Web App

---

## Support & Documentation

- **README.md** - Feature overview
- **INSTALLATION.md** - Setup instructions
- **TECHNICAL-SPECS.md** - This document
- **Code Comments** - Inline documentation
- **Database Schema** - SQL comments

---

## Version & Changelog

**Version:** 1.0.0  
**Release Date:** January 2025  
**Last Updated:** January 2025

### v1.0.0 Features
- Complete borrower management
- Full loan lifecycle
- Payment processing
- Receipt generation
- Comprehensive reporting
- User management
- Settings configuration
- Responsive design

---

## License & Terms

This system is provided as-is for business use. Customize as needed for your organization.

---

**Document Version:** 1.0  
**Last Updated:** January 2025
