xpeditis2.0/apps/backend/DATABASE-SCHEMA.md

# Database Schema - Xpeditis

## Overview

PostgreSQL 15 database schema for the Xpeditis maritime freight booking platform.

**Extensions Required**:
- `uuid-ossp` - UUID generation
- `pg_trgm` - Trigram fuzzy search for ports

---

## Tables

### 1. organizations

**Purpose**: Store business organizations (freight forwarders, carriers, shippers)

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PRIMARY KEY | Organization ID |
| name | VARCHAR(255) | NOT NULL, UNIQUE | Organization name |
| type | VARCHAR(50) | NOT NULL | FREIGHT_FORWARDER, CARRIER, SHIPPER |
| scac | CHAR(4) | UNIQUE, NULLABLE | Standard Carrier Alpha Code (carriers only) |
| address_street | VARCHAR(255) | NOT NULL | Street address |
| address_city | VARCHAR(100) | NOT NULL | City |
| address_state | VARCHAR(100) | NULLABLE | State/Province |
| address_postal_code | VARCHAR(20) | NOT NULL | Postal code |
| address_country | CHAR(2) | NOT NULL | ISO 3166-1 alpha-2 country code |
| logo_url | TEXT | NULLABLE | Logo URL |
| documents | JSONB | DEFAULT '[]' | Array of document metadata |
| is_active | BOOLEAN | DEFAULT TRUE | Active status |
| created_at | TIMESTAMP | DEFAULT NOW() | Creation timestamp |
| updated_at | TIMESTAMP | DEFAULT NOW() | Last update timestamp |

**Indexes**:
- `idx_organizations_type` on (type)
- `idx_organizations_scac` on (scac)
- `idx_organizations_active` on (is_active)

**Business Rules**:
- SCAC must be 4 uppercase letters
- SCAC is required for CARRIER type, null for others
- Name must be unique

---

### 2. users

**Purpose**: User accounts for authentication and authorization

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PRIMARY KEY | User ID |
| organization_id | UUID | NOT NULL, FK | Organization reference |
| email | VARCHAR(255) | NOT NULL, UNIQUE | Email address (lowercase) |
| password_hash | VARCHAR(255) | NOT NULL | Bcrypt password hash |
| role | VARCHAR(50) | NOT NULL | ADMIN, MANAGER, USER, VIEWER |
| first_name | VARCHAR(100) | NOT NULL | First name |
| last_name | VARCHAR(100) | NOT NULL | Last name |
| phone_number | VARCHAR(20) | NULLABLE | Phone number |
| totp_secret | VARCHAR(255) | NULLABLE | 2FA TOTP secret |
| is_email_verified | BOOLEAN | DEFAULT FALSE | Email verification status |
| is_active | BOOLEAN | DEFAULT TRUE | Account active status |
| last_login_at | TIMESTAMP | NULLABLE | Last login timestamp |
| created_at | TIMESTAMP | DEFAULT NOW() | Creation timestamp |
| updated_at | TIMESTAMP | DEFAULT NOW() | Last update timestamp |

**Indexes**:
- `idx_users_email` on (email)
- `idx_users_organization` on (organization_id)
- `idx_users_role` on (role)
- `idx_users_active` on (is_active)

**Foreign Keys**:
- `organization_id` → organizations(id) ON DELETE CASCADE

**Business Rules**:
- Email must be unique and lowercase
- Password must be hashed with bcrypt (12+ rounds)

---

### 3. carriers

**Purpose**: Shipping carrier information and API configuration

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PRIMARY KEY | Carrier ID |
| name | VARCHAR(255) | NOT NULL | Carrier name (e.g., "Maersk") |
| code | VARCHAR(50) | NOT NULL, UNIQUE | Carrier code (e.g., "MAERSK") |
| scac | CHAR(4) | NOT NULL, UNIQUE | Standard Carrier Alpha Code |
| logo_url | TEXT | NULLABLE | Logo URL |
| website | TEXT | NULLABLE | Carrier website |
| api_config | JSONB | NULLABLE | API configuration (baseUrl, credentials, timeout, etc.) |
| is_active | BOOLEAN | DEFAULT TRUE | Active status |
| supports_api | BOOLEAN | DEFAULT FALSE | Has API integration |
| created_at | TIMESTAMP | DEFAULT NOW() | Creation timestamp |
| updated_at | TIMESTAMP | DEFAULT NOW() | Last update timestamp |

**Indexes**:
- `idx_carriers_code` on (code)
- `idx_carriers_scac` on (scac)
- `idx_carriers_active` on (is_active)
- `idx_carriers_supports_api` on (supports_api)

**Business Rules**:
- SCAC must be 4 uppercase letters
- Code must be uppercase letters and underscores only
- api_config is required if supports_api is true

---

### 4. ports

**Purpose**: Maritime port database (based on UN/LOCODE)

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PRIMARY KEY | Port ID |
| code | CHAR(5) | NOT NULL, UNIQUE | UN/LOCODE (e.g., "NLRTM") |
| name | VARCHAR(255) | NOT NULL | Port name |
| city | VARCHAR(255) | NOT NULL | City name |
| country | CHAR(2) | NOT NULL | ISO 3166-1 alpha-2 country code |
| country_name | VARCHAR(100) | NOT NULL | Full country name |
| latitude | DECIMAL(9,6) | NOT NULL | Latitude (-90 to 90) |
| longitude | DECIMAL(9,6) | NOT NULL | Longitude (-180 to 180) |
| timezone | VARCHAR(50) | NULLABLE | IANA timezone |
| is_active | BOOLEAN | DEFAULT TRUE | Active status |
| created_at | TIMESTAMP | DEFAULT NOW() | Creation timestamp |
| updated_at | TIMESTAMP | DEFAULT NOW() | Last update timestamp |

**Indexes**:
- `idx_ports_code` on (code)
- `idx_ports_country` on (country)
- `idx_ports_active` on (is_active)
- `idx_ports_name_trgm` GIN on (name gin_trgm_ops) -- Fuzzy search
- `idx_ports_city_trgm` GIN on (city gin_trgm_ops) -- Fuzzy search
- `idx_ports_coordinates` on (latitude, longitude)

**Business Rules**:
- Code must be 5 uppercase alphanumeric characters (UN/LOCODE format)
- Latitude: -90 to 90
- Longitude: -180 to 180

---

### 5. rate_quotes

**Purpose**: Shipping rate quotes from carriers

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PRIMARY KEY | Rate quote ID |
| carrier_id | UUID | NOT NULL, FK | Carrier reference |
| carrier_name | VARCHAR(255) | NOT NULL | Carrier name (denormalized) |
| carrier_code | VARCHAR(50) | NOT NULL | Carrier code (denormalized) |
| origin_code | CHAR(5) | NOT NULL | Origin port code |
| origin_name | VARCHAR(255) | NOT NULL | Origin port name (denormalized) |
| origin_country | VARCHAR(100) | NOT NULL | Origin country (denormalized) |
| destination_code | CHAR(5) | NOT NULL | Destination port code |
| destination_name | VARCHAR(255) | NOT NULL | Destination port name (denormalized) |
| destination_country | VARCHAR(100) | NOT NULL | Destination country (denormalized) |
| base_freight | DECIMAL(10,2) | NOT NULL | Base freight amount |
| surcharges | JSONB | DEFAULT '[]' | Array of surcharges |
| total_amount | DECIMAL(10,2) | NOT NULL | Total price |
| currency | CHAR(3) | NOT NULL | ISO 4217 currency code |
| container_type | VARCHAR(20) | NOT NULL | Container type (e.g., "40HC") |
| mode | VARCHAR(10) | NOT NULL | FCL or LCL |
| etd | TIMESTAMP | NOT NULL | Estimated Time of Departure |
| eta | TIMESTAMP | NOT NULL | Estimated Time of Arrival |
| transit_days | INTEGER | NOT NULL | Transit days |
| route | JSONB | NOT NULL | Array of route segments |
| availability | INTEGER | NOT NULL | Available container slots |
| frequency | VARCHAR(50) | NOT NULL | Service frequency |
| vessel_type | VARCHAR(100) | NULLABLE | Vessel type |
| co2_emissions_kg | INTEGER | NULLABLE | CO2 emissions in kg |
| valid_until | TIMESTAMP | NOT NULL | Quote expiry (createdAt + 15 min) |
| created_at | TIMESTAMP | DEFAULT NOW() | Creation timestamp |
| updated_at | TIMESTAMP | DEFAULT NOW() | Last update timestamp |

**Indexes**:
- `idx_rate_quotes_carrier` on (carrier_id)
- `idx_rate_quotes_origin_dest` on (origin_code, destination_code)
- `idx_rate_quotes_container_type` on (container_type)
- `idx_rate_quotes_etd` on (etd)
- `idx_rate_quotes_valid_until` on (valid_until)
- `idx_rate_quotes_created_at` on (created_at)
- `idx_rate_quotes_search` on (origin_code, destination_code, container_type, etd)

**Foreign Keys**:
- `carrier_id` → carriers(id) ON DELETE CASCADE

**Business Rules**:
- base_freight > 0
- total_amount > 0
- eta > etd
- transit_days > 0
- availability >= 0
- valid_until = created_at + 15 minutes
- Automatically delete expired quotes (valid_until < NOW())

---

### 6. containers

**Purpose**: Container information for bookings

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PRIMARY KEY | Container ID |
| booking_id | UUID | NULLABLE, FK | Booking reference (nullable until assigned) |
| type | VARCHAR(20) | NOT NULL | Container type (e.g., "40HC") |
| category | VARCHAR(20) | NOT NULL | DRY, REEFER, OPEN_TOP, FLAT_RACK, TANK |
| size | CHAR(2) | NOT NULL | 20, 40, 45 |
| height | VARCHAR(20) | NOT NULL | STANDARD, HIGH_CUBE |
| container_number | VARCHAR(11) | NULLABLE, UNIQUE | ISO 6346 container number |
| seal_number | VARCHAR(50) | NULLABLE | Seal number |
| vgm | INTEGER | NULLABLE | Verified Gross Mass (kg) |
| tare_weight | INTEGER | NULLABLE | Empty container weight (kg) |
| max_gross_weight | INTEGER | NULLABLE | Maximum gross weight (kg) |
| temperature | DECIMAL(4,1) | NULLABLE | Temperature for reefer (°C) |
| humidity | INTEGER | NULLABLE | Humidity for reefer (%) |
| ventilation | VARCHAR(100) | NULLABLE | Ventilation settings |
| is_hazmat | BOOLEAN | DEFAULT FALSE | Hazmat cargo |
| imo_class | VARCHAR(10) | NULLABLE | IMO hazmat class |
| cargo_description | TEXT | NULLABLE | Cargo description |
| created_at | TIMESTAMP | DEFAULT NOW() | Creation timestamp |
| updated_at | TIMESTAMP | DEFAULT NOW() | Last update timestamp |

**Indexes**:
- `idx_containers_booking` on (booking_id)
- `idx_containers_number` on (container_number)
- `idx_containers_type` on (type)

**Foreign Keys**:
- `booking_id` → bookings(id) ON DELETE SET NULL

**Business Rules**:
- container_number must follow ISO 6346 format if provided
- vgm > 0 if provided
- temperature between -40 and 40 for reefer containers
- imo_class required if is_hazmat = true

---

## Relationships

```
organizations 1──* users
carriers 1──* rate_quotes
```

---

## Data Volumes

**Estimated Sizes**:
- `organizations`: ~1,000 rows
- `users`: ~10,000 rows
- `carriers`: ~50 rows
- `ports`: ~10,000 rows (seeded from UN/LOCODE)
- `rate_quotes`: ~1M rows/year (auto-deleted after expiry)
- `containers`: ~100K rows/year

---

## Migrations Strategy

**Migration Order**:
1. Create extensions (uuid-ossp, pg_trgm)
2. Create organizations table + indexes
3. Create users table + indexes + FK
4. Create carriers table + indexes
5. Create ports table + indexes (with GIN indexes)
6. Create rate_quotes table + indexes + FK
7. Create containers table + indexes + FK (Phase 2)

---

## Seed Data

**Required Seeds**:
1. **Carriers** (5 major carriers)
   - Maersk (MAEU)
   - MSC (MSCU)
   - CMA CGM (CMDU)
   - Hapag-Lloyd (HLCU)
   - ONE (ONEY)

2. **Ports** (~10,000 from UN/LOCODE dataset)
   - Major ports: Rotterdam (NLRTM), Shanghai (CNSHA), Singapore (SGSIN), etc.

3. **Test Organizations** (3 test orgs)
   - Test Freight Forwarder
   - Test Carrier
   - Test Shipper

---

## Performance Optimizations

1. **Indexes**:
   - Composite index on rate_quotes (origin, destination, container_type, etd) for search
   - GIN indexes on ports (name, city) for fuzzy search with pg_trgm
   - Indexes on all foreign keys
   - Indexes on frequently filtered columns (is_active, type, etc.)

2. **Partitioning** (Future):
   - Partition rate_quotes by created_at (monthly partitions)
   - Auto-drop old partitions (>3 months)

3. **Materialized Views** (Future):
   - Popular trade lanes (top 100)
   - Carrier performance metrics

4. **Cleanup Jobs**:
   - Delete expired rate_quotes (valid_until < NOW()) - Daily cron
   - Archive old bookings (>1 year) - Monthly

---

## Security Considerations

1. **Row-Level Security** (Phase 2)
   - Users can only access their organization's data
   - Admins can access all data

2. **Sensitive Data**:
   - password_hash: bcrypt with 12+ rounds
   - totp_secret: encrypted at rest
   - api_config: encrypted credentials

3. **Audit Logging** (Phase 3)
   - Track all sensitive operations (login, booking creation, etc.)

---

**Schema Version**: 1.0.0
**Last Updated**: 2025-10-08
**Database**: PostgreSQL 15+