Database Architecture
The application uses two PostgreSQL databases:
-
OSAS Database (Primary)
- Main operational database
- Contains waybills, scans, users, partners, etc.
-
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
// 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
}