# Schéma de base de données — Xpeditis

PostgreSQL 15 — 21 tables.

**Extensions requises** : `uuid-ossp`, `pg_trgm`

---

## Vue d'ensemble des tables

| Table | Description |
|-------|-------------|
| organizations | Organisations (transitaires, transporteurs) |
| users | Comptes utilisateurs |
| carriers | Transporteurs maritimes |
| ports | Base de données des ports (UN/LOCODE) |
| rate_quotes | Cotations de tarifs carriers |
| bookings | Réservations standard |
| containers | Conteneurs liés aux réservations |
| csv_bookings | Réservations créées via CSV |
| csv_rate_configs | Configuration des carriers CSV |
| carrier_profiles | Profils portail carrier |
| carrier_activities | Activités du portail carrier |
| audit_logs | Journal d'audit des actions |
| notifications | Notifications push (WebSocket) |
| webhooks | Configuration des webhooks tiers |
| subscriptions | Abonnements Stripe |
| licenses | Licences d'utilisation |
| api_keys | Clés API (auth alternative au JWT) |
| invitation_tokens | Tokens d'invitation utilisateur |
| password_reset_tokens | Tokens de réinitialisation mot de passe |
| cookie_consents | Consentements RGPD cookies |
| blog_posts | Articles de blog |

---

## Tables détaillées

### organizations

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| name | VARCHAR(255) | NOT NULL, UNIQUE | Nom de l'organisation |
| type | VARCHAR(50) | NOT NULL | FREIGHT_FORWARDER, CARRIER, SHIPPER |
| scac | CHAR(4) | UNIQUE, NULLABLE | Standard Carrier Alpha Code |
| address_street | VARCHAR(255) | NOT NULL | Adresse |
| address_city | VARCHAR(100) | NOT NULL | Ville |
| address_state | VARCHAR(100) | NULLABLE | État/Province |
| address_postal_code | VARCHAR(20) | NOT NULL | Code postal |
| address_country | CHAR(2) | NOT NULL | Code pays ISO 3166-1 |
| logo_url | TEXT | NULLABLE | URL du logo |
| documents | JSONB | DEFAULT '[]' | Documents compliance |
| siret | VARCHAR(20) | NULLABLE | SIRET (entreprises françaises) |
| status_badge | VARCHAR(50) | NULLABLE | Badge de statut Pappers |
| is_carrier | BOOLEAN | DEFAULT FALSE | Est un carrier (portail) |
| is_active | BOOLEAN | DEFAULT TRUE | Compte actif |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |
| updated_at | TIMESTAMP | DEFAULT NOW() | Mise à jour |

---

### users

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| organization_id | UUID | FK organizations | Organisation |
| email | VARCHAR(255) | NOT NULL, UNIQUE | Email (minuscules) |
| password_hash | VARCHAR(255) | NOT NULL | Hash Argon2 |
| role | VARCHAR(50) | NOT NULL | ADMIN, MANAGER, USER, VIEWER, CARRIER |
| first_name | VARCHAR(100) | NOT NULL | Prénom |
| last_name | VARCHAR(100) | NOT NULL | Nom |
| phone_number | VARCHAR(20) | NULLABLE | Téléphone |
| is_email_verified | BOOLEAN | DEFAULT FALSE | Email vérifié |
| is_active | BOOLEAN | DEFAULT TRUE | Compte actif |
| preferred_language | VARCHAR(10) | DEFAULT 'fr' | Langue préférée (fr, en) |
| last_login_at | TIMESTAMP | NULLABLE | Dernière connexion |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |
| updated_at | TIMESTAMP | DEFAULT NOW() | Mise à jour |

Index : `idx_users_email`, `idx_users_organization`, `idx_users_role`

---

### carriers

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| name | VARCHAR(255) | NOT NULL | Nom (ex: "Maersk") |
| code | VARCHAR(50) | NOT NULL, UNIQUE | Code (ex: "MAERSK") |
| scac | CHAR(4) | NOT NULL, UNIQUE | Standard Carrier Alpha Code |
| logo_url | TEXT | NULLABLE | URL du logo |
| website | TEXT | NULLABLE | Site web |
| api_config | JSONB | NULLABLE | Config API (credentials, timeout) |
| is_active | BOOLEAN | DEFAULT TRUE | Actif |
| supports_api | BOOLEAN | DEFAULT FALSE | Intégration API disponible |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |
| updated_at | TIMESTAMP | DEFAULT NOW() | Mise à jour |

Carriers seedés : Maersk (MAEU), MSC (MSCU), CMA CGM (CMDU), Hapag-Lloyd (HLCU), ONE (ONEY)

---

### ports

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| code | CHAR(5) | NOT NULL, UNIQUE | UN/LOCODE (ex: "NLRTM") |
| name | VARCHAR(255) | NOT NULL | Nom du port |
| city | VARCHAR(255) | NOT NULL | Ville |
| country | CHAR(2) | NOT NULL | Code pays |
| country_name | VARCHAR(100) | NOT NULL | Nom du pays |
| latitude | DECIMAL(9,6) | NOT NULL | Latitude |
| longitude | DECIMAL(9,6) | NOT NULL | Longitude |
| timezone | VARCHAR(50) | NULLABLE | Fuseau horaire IANA |
| is_active | BOOLEAN | DEFAULT TRUE | Actif |

Index GIN pour recherche floue : `idx_ports_name_trgm`, `idx_ports_city_trgm`

---

### rate_quotes

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| carrier_id | UUID | FK carriers | Carrier |
| carrier_name | VARCHAR(255) | NOT NULL | Nom carrier (dénormalisé) |
| carrier_code | VARCHAR(50) | NOT NULL | Code carrier (dénormalisé) |
| origin_code | CHAR(5) | NOT NULL | Port d'origine |
| destination_code | CHAR(5) | NOT NULL | Port de destination |
| base_freight | DECIMAL(10,2) | NOT NULL | Fret de base |
| surcharges | JSONB | DEFAULT '[]' | Surcharges (BAF, CAF, etc.) |
| total_amount | DECIMAL(10,2) | NOT NULL | Prix total |
| currency | CHAR(3) | NOT NULL | Devise ISO 4217 |
| container_type | VARCHAR(20) | NOT NULL | Type conteneur (20GP, 40HC, etc.) |
| mode | VARCHAR(10) | NOT NULL | FCL ou LCL |
| etd | TIMESTAMP | NOT NULL | Départ estimé |
| eta | TIMESTAMP | NOT NULL | Arrivée estimée |
| transit_days | INTEGER | NOT NULL | Jours de transit |
| route | JSONB | NOT NULL | Escales |
| availability | INTEGER | NOT NULL | Disponibilité conteneurs |
| valid_until | TIMESTAMP | NOT NULL | Expiration (created_at + 15 min) |

---

### bookings

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| booking_number | VARCHAR(20) | NOT NULL, UNIQUE | Format WCM-YYYY-XXXXXX |
| user_id | UUID | FK users | Utilisateur créateur |
| organization_id | UUID | FK organizations | Organisation |
| rate_quote_id | UUID | FK rate_quotes, NULLABLE | Cotation source |
| carrier_id | UUID | FK carriers | Carrier |
| status | VARCHAR(50) | NOT NULL | draft, confirmed, shipped, delivered, cancelled |
| shipper | JSONB | NOT NULL | Infos expéditeur |
| consignee | JSONB | NOT NULL | Infos destinataire |
| cargo | JSONB | NULLABLE | Détails marchandise |
| notes | TEXT | NULLABLE | Notes internes |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |
| updated_at | TIMESTAMP | DEFAULT NOW() | Mise à jour |

---

### containers

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| booking_id | UUID | FK bookings, NULLABLE | Réservation |
| type | VARCHAR(20) | NOT NULL | 20GP, 40HC, 40RF, etc. |
| category | VARCHAR(20) | NOT NULL | DRY, REEFER, OPEN_TOP, FLAT_RACK, TANK |
| container_number | VARCHAR(11) | NULLABLE, UNIQUE | ISO 6346 |
| seal_number | VARCHAR(50) | NULLABLE | Numéro de plomb |
| vgm | INTEGER | NULLABLE | Masse Brute Vérifiée (kg) |
| is_hazmat | BOOLEAN | DEFAULT FALSE | Marchandise dangereuse |
| imo_class | VARCHAR(10) | NULLABLE | Classe IMO |
| cargo_description | TEXT | NULLABLE | Description de la marchandise |

---

### csv_bookings

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| organization_id | UUID | FK organizations | Organisation |
| carrier_id | UUID | FK carriers, NULLABLE | Carrier assigné |
| booking_number | VARCHAR(50) | NOT NULL, UNIQUE | Numéro de réservation |
| shipper_name | VARCHAR(255) | NOT NULL | Expéditeur |
| consignee_name | VARCHAR(255) | NOT NULL | Destinataire |
| origin_port | CHAR(5) | NOT NULL | Port d'origine (UN/LOCODE) |
| destination_port | CHAR(5) | NOT NULL | Port de destination |
| container_type | VARCHAR(20) | NOT NULL | Type conteneur |
| commodity | TEXT | NULLABLE | Marchandise |
| weight_kg | DECIMAL(10,2) | NULLABLE | Poids |
| status | VARCHAR(50) | NOT NULL | pending, accepted, rejected, in_transit, delivered |
| carrier_magic_link_token | VARCHAR(255) | NULLABLE | Token lien magique carrier |
| carrier_magic_link_expires_at | TIMESTAMP | NULLABLE | Expiration du token |
| carrier_password_hash | VARCHAR(255) | NULLABLE | Hash mdp portail carrier |
| notes | TEXT | NULLABLE | Notes |
| documents | JSONB | DEFAULT '[]' | Documents joints |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |
| updated_at | TIMESTAMP | DEFAULT NOW() | Mise à jour |

---

### csv_rate_configs

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| company_name | VARCHAR(255) | NOT NULL, UNIQUE | Nom du carrier CSV |
| csv_file_path | VARCHAR(500) | NOT NULL | Chemin fichier CSV |
| type | VARCHAR(50) | DEFAULT 'CSV_ONLY' | CSV_ONLY ou CSV_AND_API |
| has_api | BOOLEAN | DEFAULT FALSE | A une API |
| api_connector | VARCHAR(100) | NULLABLE | Identifiant du connecteur API |
| is_active | BOOLEAN | DEFAULT TRUE | Actif |
| uploaded_at | TIMESTAMP | NOT NULL | Upload |
| uploaded_by | UUID | FK users, NULLABLE | Uploadé par |
| row_count | INTEGER | NULLABLE | Nombre de lignes |

---

### carrier_profiles

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| organization_id | UUID | FK organizations, UNIQUE | Organisation carrier |
| carrier_code | VARCHAR(50) | NOT NULL | Code carrier |
| contact_email | VARCHAR(255) | NOT NULL | Email de contact |
| contact_name | VARCHAR(255) | NULLABLE | Nom du contact |
| is_active | BOOLEAN | DEFAULT TRUE | Actif |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

---

### carrier_activities

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| carrier_profile_id | UUID | FK carrier_profiles | Profil carrier |
| csv_booking_id | UUID | FK csv_bookings | Réservation CSV |
| action | VARCHAR(50) | NOT NULL | ACCEPTED, REJECTED, VIEWED, DOCUMENT_UPLOADED |
| notes | TEXT | NULLABLE | Notes de l'action |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

---

### audit_logs

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| user_id | UUID | FK users, NULLABLE | Utilisateur |
| organization_id | UUID | FK organizations, NULLABLE | Organisation |
| action | VARCHAR(100) | NOT NULL | Type d'action (26 types) |
| resource_type | VARCHAR(50) | NULLABLE | Type de ressource |
| resource_id | VARCHAR(255) | NULLABLE | ID de la ressource |
| status | VARCHAR(20) | NOT NULL | SUCCESS, FAILURE, WARNING |
| metadata | JSONB | NULLABLE | Données supplémentaires |
| ip_address | VARCHAR(45) | NULLABLE | Adresse IP |
| user_agent | TEXT | NULLABLE | User agent |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

Index : `idx_audit_logs_user_id`, `idx_audit_logs_action`, `idx_audit_logs_created_at`

---

### notifications

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| user_id | UUID | FK users | Destinataire |
| type | VARCHAR(50) | NOT NULL | BOOKING_CREATED, DOCUMENT_UPLOADED, etc. (9 types) |
| title | VARCHAR(255) | NOT NULL | Titre |
| message | TEXT | NOT NULL | Message |
| data | JSONB | NULLABLE | Données associées |
| priority | VARCHAR(20) | NOT NULL | LOW, MEDIUM, HIGH, URGENT |
| read | BOOLEAN | DEFAULT FALSE | Lu |
| read_at | TIMESTAMP | NULLABLE | Date de lecture |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

---

### webhooks

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| organization_id | UUID | FK organizations | Organisation |
| url | TEXT | NOT NULL | URL cible |
| secret | VARCHAR(255) | NOT NULL | Secret HMAC SHA-256 |
| events | JSONB | NOT NULL | Événements souscrits |
| is_active | BOOLEAN | DEFAULT TRUE | Actif |
| last_triggered_at | TIMESTAMP | NULLABLE | Dernier déclenchement |
| failure_count | INTEGER | DEFAULT 0 | Compteur d'échecs |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

---

### subscriptions

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| organization_id | UUID | FK organizations, UNIQUE | Organisation |
| stripe_customer_id | VARCHAR(255) | NULLABLE | ID client Stripe |
| stripe_subscription_id | VARCHAR(255) | NULLABLE | ID abonnement Stripe |
| plan | VARCHAR(50) | NOT NULL | FREE, BRONZE, SILVER, GOLD, PLATINIUM |
| status | VARCHAR(50) | NOT NULL | active, past_due, cancelled, trialing, pending_payment, pending_bank_transfer |
| current_period_start | TIMESTAMP | NULLABLE | Début de période |
| current_period_end | TIMESTAMP | NULLABLE | Fin de période |
| billing_interval | VARCHAR(20) | NULLABLE | monthly, yearly |
| commission_rate | DECIMAL(5,4) | DEFAULT 0 | Taux de commission Xpeditis |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |
| updated_at | TIMESTAMP | DEFAULT NOW() | Mise à jour |

---

### licenses

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| organization_id | UUID | FK organizations | Organisation |
| feature | VARCHAR(100) | NOT NULL | Fonctionnalité activée |
| is_active | BOOLEAN | DEFAULT TRUE | Actif |
| valid_until | TIMESTAMP | NULLABLE | Expiration |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

---

### api_keys

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| organization_id | UUID | FK organizations | Organisation |
| user_id | UUID | FK users | Créateur |
| name | VARCHAR(255) | NOT NULL | Nom de la clé |
| key_hash | VARCHAR(255) | NOT NULL | Hash Argon2 de la clé |
| key_prefix | VARCHAR(10) | NOT NULL | Préfixe visible (ex: xped_) |
| last_used_at | TIMESTAMP | NULLABLE | Dernière utilisation |
| expires_at | TIMESTAMP | NULLABLE | Expiration |
| is_active | BOOLEAN | DEFAULT TRUE | Actif |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

---

### invitation_tokens

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| organization_id | UUID | FK organizations | Organisation |
| email | VARCHAR(255) | NOT NULL | Email invité |
| token | VARCHAR(255) | NOT NULL, UNIQUE | Token d'invitation |
| role | VARCHAR(50) | NOT NULL | Rôle assigné |
| expires_at | TIMESTAMP | NOT NULL | Expiration (24h) |
| used_at | TIMESTAMP | NULLABLE | Utilisé le |
| created_by | UUID | FK users | Créateur |

---

### password_reset_tokens

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| user_id | UUID | FK users | Utilisateur |
| token | VARCHAR(255) | NOT NULL, UNIQUE | Token de réinitialisation |
| expires_at | TIMESTAMP | NOT NULL | Expiration (1h) |
| used_at | TIMESTAMP | NULLABLE | Utilisé le |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

---

### cookie_consents

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| user_id | UUID | FK users, NULLABLE | Utilisateur (si connecté) |
| session_id | VARCHAR(255) | NULLABLE | Session anonyme |
| analytics | BOOLEAN | NOT NULL | Consentement analytics |
| marketing | BOOLEAN | NOT NULL | Consentement marketing |
| ip_address | VARCHAR(45) | NULLABLE | IP |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |

---

### blog_posts

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| id | UUID | PK | Identifiant |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | Slug URL |
| title_fr | VARCHAR(500) | NOT NULL | Titre en français |
| title_en | VARCHAR(500) | NULLABLE | Titre en anglais |
| content_fr | TEXT | NOT NULL | Contenu en français |
| content_en | TEXT | NULLABLE | Contenu en anglais |
| excerpt_fr | TEXT | NULLABLE | Extrait français |
| excerpt_en | TEXT | NULLABLE | Extrait anglais |
| cover_image_url | TEXT | NULLABLE | Image de couverture |
| author_id | UUID | FK users | Auteur |
| is_published | BOOLEAN | DEFAULT FALSE | Publié |
| published_at | TIMESTAMP | NULLABLE | Date de publication |
| tags | JSONB | DEFAULT '[]' | Tags |
| created_at | TIMESTAMP | DEFAULT NOW() | Création |
| updated_at | TIMESTAMP | DEFAULT NOW() | Mise à jour |

---

## Relations principales

```
organizations 1──* users
organizations 1──1 subscriptions
organizations 1──* licenses
organizations 1──* api_keys
organizations 1──* webhooks
organizations 1──* bookings
organizations 1──* csv_bookings
organizations 1──1 carrier_profiles

users 1──* bookings
users 1──* audit_logs
users 1──* notifications
users 1──* api_keys

bookings 1──* containers
bookings 1──1 rate_quotes (optionnel)

csv_bookings 1──* carrier_activities (via carrier_profiles)
carrier_profiles 1──* carrier_activities
```

---

## Stratégie de migrations

Les migrations sont dans `apps/backend/src/infrastructure/persistence/typeorm/migrations/`.
Ne jamais modifier une migration déjà appliquée — créer une nouvelle migration.

```bash
cd apps/backend
npm run migration:generate -- src/infrastructure/persistence/typeorm/migrations/NomMigration
npm run migration:run
```

---

*Dernière mise à jour : Mai 2026 — 21 tables*