# Database Architecture

The application uses **two PostgreSQL databases**:

1. **OSAS Database** (Primary)
   - Main operational database
   - Contains waybills, scans, users, partners, etc.

2. **ONMART Database** (Secondary)
   - Marketplace-specific operations
   - Separate schema for marketplace data

### Key Database Models

#### Core Operational Models

| Model | Purpose | Key Fields |
|-------|---------|------------|
| `scans` | Tracking status history | waybill_id, status, location, timestamp |
| `waybill_numbers` | Shipment records | awb_number, origin, destination, weight |
| `partners` | 3P courier partners | name, api_token, webhook_url |
| `resi_3p` | 3P waybill mapping | internal_awb, partner_awb, partner_id |
| `user` | User accounts | username, password_hash, role_id, agent_id |

#### Scanning Models

| Model | Purpose |
|-------|---------|
| `delivery_scan` | Delivery completion records |
| `handover_scan` | Hub-to-hub transfers |
| `receiving_scan` | Package arrivals |
| `problem_scan` | Exception tracking |
| `packing_scan` | Package preparation |

#### Financial Models

| Model | Purpose |
|-------|---------|
| `billing_vip_config` | VIP customer pricing |
| `xendit_payment` | Payment gateway records |
| `xendit_payout` | Payout transactions |
| `agent_commission` | Commission calculations |

#### Master Data Models

| Model | Purpose |
|-------|---------|
| `agent_locations` | Agent location mapping |
| `agent_customer` | Customer-agent relationships |
| `awb_books` | AWB number allocation |
| `courier_partners` | Courier configurations |
| `service_types` | Available services |

#### Additional Models (52 total)

- Manifest management (`manifest_pcp`, `manifest_pos`)
- Quality management (`alter_weight`, `pod_record`, `pop_record`)
- Trucking operations (`trucking_shipment`, `trucking_config`)
- Location master data (`all_location_migration`)
- Marketplace integration models
- Report configuration models

### Database Relationships

```
User ──┐
       ├─→ Agent_Locations → Waybill_Numbers
       └─→ Role

Waybill_Numbers ──┬─→ Scans (1:many)
                  ├─→ Resi_3p
                  └─→ Delivery_Scan / Problem_Scan

Partners ─→ Resi_3p ─→ Waybill_Numbers

Agent_Customer ─→ Waybill_Numbers
```

### Connection Pooling

```javascript
// From db.config.js
pool: {
  max: DB_POOL_MAX,        // Maximum connections
  min: DB_POOL_MIN,        // Minimum connections
  acquire: DB_POOL_ACQUIRE, // Connection acquisition timeout
  idle: DB_POOL_IDLE       // Idle timeout
}
```