talas-group/03_APPS_&_SERVICES/SCHEMA_BASE_DE_DONNEES.md

# Schéma Base de Données Veza

> Référence complète du schéma PostgreSQL — 60+ tables, UUIDs, soft deletes.
> Source : `veza-backend-api/internal/models/` (68 fichiers Go) et `migrations/` (115 fichiers SQL)
> ORM : GORM v1 (mapping uniquement, migrations 100% SQL)

## Stratégie de migration

- **Migrations SQL pures** dans `veza-backend-api/migrations/` (001 à 115+)
- **GORM** utilisé uniquement pour le mapping Go ↔ PostgreSQL, pas pour la création de schéma
- **AutoMigrate()** est vide — tout est géré par SQL
- **Outil** : `go run ./cmd/migrate_tool/main.go up` (ou `down` pour rollback)
- Table `schema_migrations` pour le suivi des migrations appliquées

## Conventions

- **Clés primaires** : UUID v4 (`uuid.UUID` avec `google/uuid`)
- **Soft delete** : Colonne `deleted_at` indexée sur 20+ tables
- **Timestamps** : `created_at`, `updated_at` sur toutes les tables
- **JSONB** : Utilisé pour les données flexibles (social_links, specs, metadata)
- **Enums** : Types PostgreSQL custom (user_role, track_status, etc.)
- **Foreign keys** : Contraintes PostgreSQL natives, toujours actives

---

## A. Utilisateurs & Authentification

### `users`

Table centrale — profil utilisateur.

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `username` | VARCHAR(30) | NOT NULL | |
| `email` | VARCHAR | NOT NULL, UNIQUE | |
| `password_hash` | VARCHAR | NOT NULL | Masqué en JSON |
| `token_version` | INT | DEFAULT 0 | Pour révocation de tokens |
| `first_name` | VARCHAR | | |
| `last_name` | VARCHAR | | |
| `avatar` | TEXT | | URL avatar |
| `banner_url` | TEXT | | URL bannière |
| `bio` | TEXT | | |
| `location` | VARCHAR | | |
| `birthdate` | TIMESTAMP | NULLABLE | |
| `gender` | VARCHAR(20) | | |
| `role` | user_role ENUM | DEFAULT 'user' | |
| `is_active` | BOOL | DEFAULT true | |
| `is_verified` | BOOL | DEFAULT false | |
| `is_banned` | BOOL | DEFAULT false | |
| `is_admin` | BOOL | DEFAULT false | |
| `is_public` | BOOL | DEFAULT true | |
| `last_login_at` | TIMESTAMP | NULLABLE | |
| `login_count` | INT | DEFAULT 0 | |
| `password_changed_at` | TIMESTAMP | NULLABLE | |
| `social_links` | JSONB | DEFAULT '{}' | |
| `created_at` | TIMESTAMP | | |
| `updated_at` | TIMESTAMP | | |
| `deleted_at` | TIMESTAMP | INDEX | Soft delete |

**Relations** : `roles` (M2M via `user_roles`), `track_likes` (has many, CASCADE)

### `sessions`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users, INDEX | |
| `token_hash` | VARCHAR | UNIQUE INDEX | Token hashé |
| `ip_address` | VARCHAR | | |
| `user_agent` | VARCHAR | | |
| `revoked_at` | TIMESTAMP | NULLABLE | |
| `expires_at` | TIMESTAMP | NOT NULL | |
| `created_at` | TIMESTAMP | | |

### `refresh_tokens`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users (CASCADE), NOT NULL, INDEX | |
| `token_hash` | VARCHAR | NOT NULL, INDEX | |
| `expires_at` | TIMESTAMP | NOT NULL | |
| `created_at` | TIMESTAMP | | |
| `updated_at` | TIMESTAMP | | |
| `deleted_at` | TIMESTAMP | | Soft delete |

### `roles`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `name` | VARCHAR(50) | UNIQUE, NOT NULL | |
| `display_name` | VARCHAR(100) | NOT NULL | |
| `description` | TEXT | | |
| `is_system` | BOOL | | Rôle système (non supprimable) |
| `is_active` | BOOL | DEFAULT true | |
| `created_at` | TIMESTAMP | | |
| `updated_at` | TIMESTAMP | | |

**Relations** : `users` (M2M), `permissions` (M2M)

### `permissions`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `name` | VARCHAR(100) | UNIQUE, NOT NULL | |
| `resource` | VARCHAR(50) | NOT NULL | |
| `action` | VARCHAR(50) | NOT NULL | |
| `description` | TEXT | | |
| `created_at` | TIMESTAMP | | |

### `user_roles` (table pivot)

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users (CASCADE), UNIQUE(user_id, role_id) | |
| `role_id` | UUID | FK → roles (CASCADE) | |
| `role` | VARCHAR(50) | | Nom du rôle (dénormalisé) |
| `assigned_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | |
| `assigned_by` | UUID | NULLABLE, INDEX | |
| `expires_at` | TIMESTAMP | NULLABLE | |
| `is_active` | BOOL | DEFAULT true | |

### `role_permissions` (table pivot)

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `role_id` | UUID | FK → roles (CASCADE), UNIQUE(role_id, permission_id) |
| `permission_id` | UUID | FK → permissions (CASCADE) |

### `mfa_configs`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | NOT NULL, UNIQUE INDEX | |
| `secret` | VARCHAR | NOT NULL | Masqué en JSON |
| `backup_codes` | TEXT | | JSON array |
| `is_enabled` | BOOL | DEFAULT false | |
| `last_used_at` | TIMESTAMP | NULLABLE | |
| `created_at` | TIMESTAMP | | |
| `updated_at` | TIMESTAMP | | |
| `deleted_at` | TIMESTAMP | | Soft delete |

### `recovery_codes`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | INDEX |
| `code` | VARCHAR | Masqué |
| `is_used` | BOOL | DEFAULT false |
| `used_at` | TIMESTAMP | NULLABLE |
| `expires_at` | TIMESTAMP | |
| `created_at` | TIMESTAMP | |

### `webauthn_credentials`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | NOT NULL | |
| `credential_id` | BYTEA | UNIQUE, NOT NULL | |
| `public_key` | BYTEA | NOT NULL | |
| `attestation_type` | VARCHAR(50) | DEFAULT 'none' | |
| `aaguid` | BYTEA | | |
| `sign_count` | INT | DEFAULT 0 | |
| `name` | VARCHAR(100) | DEFAULT 'My Passkey' | |
| `created_at` | TIMESTAMP | | |
| `last_used_at` | TIMESTAMP | NULLABLE | |

### `federated_identities`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users (CASCADE), INDEX | |
| `provider` | VARCHAR | | google, github, facebook, twitter |
| `provider_id` | VARCHAR | | |
| `email` | VARCHAR | | |
| `display_name` | VARCHAR | | |
| `avatar_url` | VARCHAR | | |
| `access_token` | TEXT | | Masqué |
| `refresh_token` | TEXT | | Masqué |
| `expires_at` | TIMESTAMP | NULLABLE | |

### `api_keys`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | INDEX |
| `name` | VARCHAR(100) | |
| `prefix` | VARCHAR(16) | INDEX |
| `hashed_key` | VARCHAR(128) | Masqué |
| `scopes` | TEXT[] | PostgreSQL array |
| `last_used_at` | TIMESTAMP | NULLABLE |
| `expires_at` | TIMESTAMP | NULLABLE |
| `created_at` | TIMESTAMP | |

### `user_settings`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | UNIQUE |
| `email_notifications` | BOOL | |
| `push_notifications` | BOOL | |
| `browser_notifications` | BOOL | |
| `email_on_follow` | BOOL | |
| `email_on_like` | BOOL | |
| `email_on_comment` | BOOL | |
| `email_on_message` | BOOL | |
| `email_on_mention` | BOOL | |
| `email_marketing` | BOOL | |
| `allow_search_indexing` | BOOL | |
| `show_activity` | BOOL | |
| `explicit_content` | BOOL | |
| `autoplay` | BOOL | |

### `user_profiles`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | UNIQUE |
| `language` | VARCHAR | DEFAULT 'en' |
| `timezone` | VARCHAR | DEFAULT 'UTC' |
| `theme` | VARCHAR | DEFAULT 'auto' |

### `user_presence`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `user_id` | UUID | PK | |
| `status` | VARCHAR(20) | DEFAULT 'offline' | online/away/busy/offline |
| `last_seen_at` | TIMESTAMP | | |
| `status_message` | TEXT | | |
| `track_id` | UUID | NULLABLE | Piste en écoute |
| `track_title` | TEXT | | |
| `invisible` | BOOL | DEFAULT false | |
| `updated_at` | TIMESTAMP | | |

---

## B. Pistes audio

### `tracks`

Table principale des pistes audio.

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `creator_id` | UUID | FK → users (CASCADE), INDEX | |
| `file_id` | UUID | NULLABLE | |
| `title` | VARCHAR(255) | NOT NULL | |
| `artist` | VARCHAR(255) | | |
| `album` | VARCHAR(255) | | |
| `duration` | INT | | Secondes |
| `genre` | VARCHAR(100) | | |
| `tags` | TEXT[] | | PostgreSQL array |
| `year` | INT | DEFAULT 0 | |
| `bpm` | INT | NULLABLE | |
| `musical_key` | VARCHAR(10) | | |
| `file_path` | VARCHAR(500) | | |
| `file_size` | BIGINT | | Octets |
| `format` | VARCHAR(10) | | wav, mp3, flac... |
| `bitrate` | INT | | kbps |
| `sample_rate` | INT | | Hz |
| `waveform_path` | VARCHAR | | |
| `waveform_url` | VARCHAR | | |
| `cover_art_path` | VARCHAR | | |
| `is_public` | BOOL | DEFAULT true | |
| `status` | track_status ENUM | | uploading/processing/completed/failed |
| `status_message` | TEXT | | |
| `stream_status` | VARCHAR | DEFAULT 'pending' | pending/processing/ready/error |
| `stream_manifest_url` | VARCHAR | | URL playlist HLS |
| `play_count` | BIGINT | | Non exposé en API |
| `like_count` | BIGINT | | Non exposé en API |
| `created_at` | TIMESTAMP | | |
| `updated_at` | TIMESTAMP | | |
| `deleted_at` | TIMESTAMP | INDEX | Soft delete |

**Relations** : `user` (belongs to), `playlists` (M2M), `likes` (has many), `shares` (has many), `versions` (has many), `hls_streams` (has many)

### `track_versions`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `track_id` | UUID | FK → tracks (CASCADE), UNIQUE(track_id, version_number) |
| `version_number` | INT | |
| `file_path` | VARCHAR(500) | |
| `file_size` | BIGINT | Octets |
| `changelog` | TEXT | |

### `track_likes`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | FK → users (CASCADE), UNIQUE(user_id, track_id) |
| `track_id` | UUID | FK → tracks (CASCADE) |
| `created_at` | TIMESTAMP | |

### `track_plays`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `track_id` | UUID | FK → tracks, INDEX | |
| `user_id` | UUID | FK → users (SET NULL), NULLABLE, INDEX | Anonyme si non connecté |
| `duration` | INT | | Secondes jouées |
| `played_at` | TIMESTAMP | INDEX | |
| `device` | VARCHAR(100) | | |
| `ip_address` | VARCHAR(45) | | |

### `track_shares`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `track_id` | UUID | INDEX |
| `user_id` | UUID | INDEX |
| `share_token` | VARCHAR(255) | UNIQUE |
| `permissions` | VARCHAR(50) | DEFAULT 'read' (read/download) |
| `expires_at` | TIMESTAMP | NULLABLE |
| `access_count` | BIGINT | DEFAULT 0 |

### `track_comments`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `track_id` | UUID | FK → tracks (CASCADE), INDEX | |
| `user_id` | UUID | FK → users (CASCADE), INDEX | |
| `parent_id` | UUID | FK → track_comments (CASCADE), NULLABLE | Réponses |
| `content` | TEXT | NOT NULL | |
| `timestamp` | FLOAT | | Position en secondes |
| `is_edited` | BOOL | DEFAULT false | |

**Relations** : `parent` (self-ref), `replies` (has many self-ref)

### `track_reposts`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | FK → users (CASCADE), INDEX |
| `track_id` | UUID | FK → tracks (CASCADE), INDEX |
| `created_at` | TIMESTAMP | INDEX |

### `track_stems`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `track_id` | UUID | FK → tracks (CASCADE), INDEX |
| `name` | VARCHAR(100) | |
| `file_path` | VARCHAR(500) | |
| `format` | VARCHAR(10) | |
| `size_bytes` | BIGINT | |
| `created_at` | TIMESTAMP | |
| `deleted_at` | TIMESTAMP | Soft delete |

### `track_lyrics`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `track_id` | UUID | FK → tracks (CASCADE), UNIQUE |
| `content` | TEXT | NOT NULL |

### `track_history`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `track_id` | UUID | INDEX | |
| `user_id` | UUID | FK → users (SET NULL) | |
| `action` | ENUM | INDEX | created/updated/deleted/published/unpublished/restored |
| `old_value` | TEXT | | |
| `new_value` | TEXT | | |

### `tags`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `name` | VARCHAR(30) | UNIQUE |
| `use_count` | INT | DEFAULT 0 |

### `track_tags` (pivot)

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `track_id` | UUID | PK composite |
| `tag_id` | UUID | PK composite |
| `created_at` | TIMESTAMP | |

### `genres`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `slug` | VARCHAR(50) | PK |
| `name` | VARCHAR(100) | |

### `track_genres` (pivot)

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `track_id` | UUID | PK composite |
| `genre_slug` | VARCHAR(50) | PK composite |
| `position` | INT | DEFAULT 0 |

### `playback_analytics`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `track_id` | UUID | FK → tracks (CASCADE), INDEX | |
| `user_id` | UUID | FK → users (CASCADE), INDEX | |
| `play_time` | INT | | Secondes |
| `pause_count` | INT | | |
| `seek_count` | INT | | |
| `completion_rate` | DECIMAL(5,2) | | 0-100% |
| `started_at` | TIMESTAMP | | |
| `ended_at` | TIMESTAMP | NULLABLE | |

---

## C. Playlists

### `playlists`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users (CASCADE), INDEX | |
| `name` | VARCHAR(200) | | Titre (colonne DB: `name`) |
| `description` | TEXT | | |
| `is_public` | BOOL | DEFAULT true | |
| `cover_url` | VARCHAR(500) | | |
| `track_count` | INT | DEFAULT 0 | |
| `follower_count` | INT | DEFAULT 0 | |
| `is_editorial` | BOOL | DEFAULT false | Playlist éditoriale |
| `is_default_favorites` | BOOL | DEFAULT false | Playlist favoris auto |
| `deleted_at` | TIMESTAMP | | Soft delete |

**Relations** : `user` (belongs to), `tracks` (has many PlaylistTrack), `collaborators` (has many, CASCADE)

### `playlist_tracks` (pivot ordonné)

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `playlist_id` | UUID | FK → playlists (CASCADE), INDEX |
| `track_id` | UUID | FK → tracks (CASCADE), INDEX |
| `position` | INT | Ordre dans la playlist |
| `added_by` | UUID | |
| `added_at` | TIMESTAMP | |

### `playlist_collaborators`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `playlist_id` | UUID | FK → playlists, INDEX | |
| `user_id` | UUID | FK → users, INDEX | |
| `permission` | VARCHAR(20) | DEFAULT 'read' | read/write/admin |

### `playlist_follows`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `playlist_id` | UUID | INDEX |
| `user_id` | UUID | INDEX |

### `playlist_share_links`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `playlist_id` | UUID | INDEX |
| `user_id` | UUID | INDEX |
| `share_token` | VARCHAR(255) | UNIQUE |
| `expires_at` | TIMESTAMP | NULLABLE |
| `access_count` | BIGINT | DEFAULT 0 |

### `playlist_versions`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `playlist_id` | UUID | INDEX | |
| `user_id` | UUID | FK → users (SET NULL) | |
| `version` | INT | | |
| `action` | ENUM | | Type de modification |
| `title` | VARCHAR(200) | | |
| `description` | TEXT | | |
| `is_public` | BOOL | | |
| `cover_url` | VARCHAR(500) | | |
| `tracks_snapshot` | TEXT | | JSON des pistes à cette version |

### `user_genre_follows` (pivot)

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `user_id` | UUID | PK composite |
| `genre_slug` | VARCHAR | PK composite |

### `user_tag_follows` (pivot)

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `user_id` | UUID | PK composite |
| `tag_id` | UUID | PK composite |

---

## D. Chat & Messagerie

### `rooms`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `name` | VARCHAR(255) | | |
| `description` | TEXT | | |
| `room_type` | VARCHAR | DEFAULT 'public' | public/private/dm |
| `is_private` | BOOL | DEFAULT false | |
| `creator_id` | UUID | FK → users (CASCADE) | |
| `deleted_at` | TIMESTAMP | | Soft delete |

**Relations** : `members` (has many, CASCADE), `messages` (has many, CASCADE)

### `room_members`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `room_id` | UUID | FK → rooms (CASCADE) |
| `user_id` | UUID | FK → users (CASCADE) |
| `role` | VARCHAR | DEFAULT 'member' |
| `joined_at` | TIMESTAMP | |

### `room_invitations`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `room_id` | UUID | FK → rooms |
| `inviter_id` | UUID | FK → users |
| `invitee_id` | UUID | NULLABLE |
| `token` | UUID | UNIQUE |
| `status` | VARCHAR(20) | DEFAULT 'pending' (pending/accepted/expired) |
| `expires_at` | TIMESTAMP | |

### `messages`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `room_id` | UUID | FK → rooms | |
| `sender_id` | UUID | FK → users | |
| `content` | TEXT | | |
| `message_type` | VARCHAR | DEFAULT 'text' | text/image/audio/system |
| `reply_to_id` | UUID | NULLABLE, self-ref | Réponse à |
| `is_edited` | BOOL | DEFAULT false | |
| `is_deleted` | BOOL | DEFAULT false | |
| `is_pinned` | BOOL | DEFAULT false | |
| `edited_at` | TIMESTAMP | NULLABLE | |
| `status` | VARCHAR(20) | DEFAULT 'sent' | |
| `metadata` | JSONB | NULLABLE | |
| `content_tsv` | TSVECTOR | | Recherche full-text |

### `message_reactions`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | UNIQUE(user_id, message_id, emoji) |
| `message_id` | UUID | |
| `emoji` | VARCHAR(50) | |

### `read_receipts`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | UNIQUE(user_id, message_id) |
| `message_id` | UUID | |
| `read_at` | TIMESTAMP | |

### `delivered_status`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | UNIQUE(user_id, message_id) |
| `message_id` | UUID | |
| `delivered_at` | TIMESTAMP | |

---

## E. Streaming & Collaboration

### `hls_streams`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `track_id` | UUID | FK → tracks (CASCADE), INDEX | |
| `playlist_url` | VARCHAR(500) | | |
| `segments_count` | INT | DEFAULT 0 | |
| `bitrates` | JSONB | | Liste des bitrates disponibles |
| `status` | VARCHAR(20) | DEFAULT 'pending', INDEX | pending/processing/ready/failed |

### `hls_transcode_queue`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `track_id` | UUID | INDEX | |
| `priority` | INT | DEFAULT 5 | |
| `status` | VARCHAR(20) | DEFAULT 'pending', INDEX | pending/processing/completed/failed |
| `retry_count` | INT | | |
| `max_retries` | INT | | |
| `error_message` | TEXT | NULLABLE | |
| `started_at` | TIMESTAMP | NULLABLE | |
| `completed_at` | TIMESTAMP | NULLABLE | |

### `live_streams`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users (CASCADE) | |
| `title` | VARCHAR(200) | | |
| `description` | TEXT | | |
| `category` | VARCHAR(100) | | |
| `thumbnail_url` | VARCHAR(500) | | |
| `stream_key` | VARCHAR(100) | | Masqué en JSON |
| `streamer_name` | VARCHAR(100) | | |
| `is_live` | BOOL | DEFAULT false | |
| `started_at` | TIMESTAMP | NULLABLE | |
| `ended_at` | TIMESTAMP | NULLABLE | |
| `viewer_count` | INT | DEFAULT 0 | |
| `tags` | JSONB | DEFAULT '[]' | |
| `scheduled_at` | TIMESTAMP | NULLABLE | |
| `stream_url` | TEXT | DEFAULT '' | |
| `is_vod` | BOOL | DEFAULT false | |
| `deleted_at` | TIMESTAMP | | Soft delete |

### `co_listening_sessions`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `host_id` | UUID | FK → users, INDEX |
| `track_id` | UUID | FK → tracks, INDEX |
| `expires_at` | TIMESTAMP | |

### `queues`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | UNIQUE | |
| `current_track_id` | UUID | NULLABLE | |
| `current_position` | INT | DEFAULT 0 | |
| `is_playing` | BOOL | DEFAULT false | |
| `shuffle` | BOOL | DEFAULT false | |
| `repeat_mode` | VARCHAR(20) | DEFAULT 'off' | off/one/all |
| `volume` | INT | DEFAULT 100 | |

### `queue_items`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `queue_id` | UUID | FK → queues |
| `track_id` | UUID | FK → tracks |
| `position` | INT | |

### `queue_sessions` (sessions collaboratives)

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `share_token` | VARCHAR(32) | UNIQUE |
| `creator_id` | UUID | INDEX |

### `shared_queue_items`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `session_id` | UUID | INDEX |
| `track_id` | UUID | |
| `position` | INT | |

---

## F. Fichiers & Stockage

### `user_files`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users | |
| `folder_id` | UUID | FK → user_folders (SET NULL), NULLABLE | |
| `filename` | VARCHAR(255) | | |
| `s3_key` | VARCHAR(500) | | Clé MinIO/S3 |
| `size_bytes` | BIGINT | DEFAULT 0 | |
| `mime_type` | VARCHAR(100) | DEFAULT 'application/octet-stream' | |

### `user_folders`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users (CASCADE) | |
| `name` | VARCHAR(255) | | |
| `parent_id` | UUID | FK → user_folders (CASCADE), NULLABLE | Hiérarchie |

**Relations** : `parent` (self-ref), `children` (has many), `files` (has many)

### `cloud_file_versions`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `file_id` | UUID | FK → user_files (CASCADE) |
| `version` | INT | |
| `storage_key` | TEXT | |
| `size_bytes` | BIGINT | DEFAULT 0 |

### `cloud_file_shares`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `file_id` | UUID | FK → user_files (CASCADE) |
| `token` | VARCHAR(64) | UNIQUE |
| `permissions` | VARCHAR(20) | DEFAULT 'read' |
| `expires_at` | TIMESTAMP | |

### `user_storage_quotas`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `user_id` | UUID | PK | |
| `max_bytes` | BIGINT | DEFAULT 5368709120 | ~5 Go |
| `used_bytes` | BIGINT | DEFAULT 0 | |

---

## G. Équipement (Gear)

### `gear_items`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users | |
| `name` | VARCHAR(200) | | |
| `category` | VARCHAR(100) | | micro, interface, casque... |
| `brand` | VARCHAR | | |
| `model` | VARCHAR | | |
| `serial_number` | VARCHAR | | |
| `image` | VARCHAR(500) | | |
| `images` | JSONB | DEFAULT '[]' | |
| `status` | VARCHAR(50) | | actif, en réparation, vendu |
| `condition` | VARCHAR(50) | | neuf, bon, usé |
| `purchase_date` | TIMESTAMP | NULLABLE | |
| `purchase_price` | DECIMAL(12,2) | | |
| `currency` | VARCHAR(3) | DEFAULT 'USD' | |
| `vendor` | VARCHAR | | |
| `order_number` | VARCHAR | | |
| `warranty_start` | TIMESTAMP | NULLABLE | |
| `warranty_expire` | TIMESTAMP | NULLABLE | |
| `warranty_type` | VARCHAR | | |
| `warranty_notes` | TEXT | | |
| `support_contact` | VARCHAR | | |
| `specs` | JSONB | | Spécifications techniques |
| `notes` | TEXT | | |
| `documents` | JSONB | | |
| `maintenance_history` | JSONB | | |
| `is_public` | BOOL | DEFAULT false | Visible sur le profil |
| `deleted_at` | TIMESTAMP | | Soft delete |

### `gear_images`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `gear_id` | UUID | FK → gear_items (CASCADE) |
| `image_url` | VARCHAR(500) | |
| `position` | INT | DEFAULT 0 |

### `gear_repairs`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `gear_id` | UUID | FK → gear_items (CASCADE) |
| `repair_date` | DATE | |
| `description` | TEXT | |
| `cost_cents` | INT | DEFAULT 0 |
| `currency` | VARCHAR(3) | DEFAULT 'EUR' |
| `provider` | VARCHAR(255) | |
| `notes` | TEXT | |

### `gear_documents`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `gear_id` | UUID | FK → gear_items (CASCADE) |
| `type` | VARCHAR(50) | DEFAULT 'invoice' |
| `storage_key` | TEXT | |
| `filename` | VARCHAR(255) | |

---

## H. Paiements & Royalties

### `seller_stripe_accounts`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `user_id` | UUID | UNIQUE |
| `stripe_account_id` | VARCHAR(255) | UNIQUE |
| `charges_enabled` | BOOL | |
| `payouts_enabled` | BOOL | |
| `onboarding_completed` | BOOL | |
| `kyc_status` | VARCHAR(32) | DEFAULT 'not_started' |
| `kyc_verification_session_id` | VARCHAR(255) | |
| `kyc_verified_at` | TIMESTAMP | NULLABLE |
| `kyc_last_error` | TEXT | |

### `royalty_records`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `content_id` | UUID | INDEX | |
| `creator_id` | UUID | INDEX | |
| `period` | VARCHAR | INDEX | Période (ex: 2026-03) |
| `plays` | BIGINT | | |
| `revenue` | FLOAT | | |
| `royalty_amount` | FLOAT | | |
| `royalty_rate` | FLOAT | | |
| `status` | VARCHAR | DEFAULT 'calculated' | calculated/paid |
| `calculated_at` | TIMESTAMP | | |
| `paid_at` | TIMESTAMP | NULLABLE | |

### `royalty_payouts`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `payout_id` | VARCHAR | UNIQUE |
| `creator_id` | UUID | INDEX |
| `amount` | FLOAT | |
| `currency` | VARCHAR | DEFAULT 'EUR' |
| `period` | VARCHAR | INDEX |
| `status` | VARCHAR | DEFAULT 'pending' |
| `payment_method` | VARCHAR | |
| `transaction_id` | VARCHAR | |
| `processed_at` | TIMESTAMP | |
| `estimated_arrival` | TIMESTAMP | |
| `notes` | VARCHAR | |

### `royalty_rates`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `content_type` | VARCHAR | UNIQUE |
| `rate` | FLOAT | |
| `description` | VARCHAR | |
| `is_active` | BOOL | DEFAULT true |

### `creator_royalty_rates`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `creator_id` | UUID | UNIQUE | |
| `rate` | FLOAT | | Taux personnalisé |
| `reason` | VARCHAR | | |
| `is_active` | BOOL | DEFAULT true | |

### `royalty_config`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `platform_fee_rate` | FLOAT | DEFAULT 0.15 | 15% frais plateforme |
| `minimum_payout_amount` | FLOAT | DEFAULT 50.0 | Seuil minimum versement |
| `payout_schedule` | VARCHAR | DEFAULT 'monthly' | |
| `processing_delay` | INT | DEFAULT 3 | Jours |
| `currency` | VARCHAR | DEFAULT 'EUR' | |
| `is_active` | BOOL | DEFAULT true | |

---

## I. Modération & Administration

### `reports`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `reporter_id` | UUID | |
| `reported_user_id` | UUID | NULLABLE |
| `content_type` | VARCHAR(50) | |
| `content_id` | UUID | NULLABLE |
| `reason` | TEXT | |
| `status` | VARCHAR(20) | DEFAULT 'pending' |
| `resolved_by` | UUID | NULLABLE |
| `resolved_at` | TIMESTAMP | NULLABLE |

### `announcements`

| Colonne | Type | Contraintes |
|---------|------|-------------|
| `id` | UUID | PK |
| `title` | VARCHAR(200) | |
| `content` | TEXT | |
| `type` | VARCHAR(20) | DEFAULT 'info' |
| `is_active` | BOOL | DEFAULT true |
| `starts_at` | TIMESTAMP | |
| `ends_at` | TIMESTAMP | NULLABLE |
| `created_by` | UUID | NULLABLE |

### `notifications`

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | FK → users (CASCADE), INDEX | |
| `type` | VARCHAR(50) | INDEX | follow, like, comment, system... |
| `title` | VARCHAR(255) | | |
| `content` | TEXT | | |
| `link` | VARCHAR(500) | | |
| `read` | BOOL | DEFAULT false | INDEX composite avec user_id |
| `read_at` | TIMESTAMP | NULLABLE | |
| `created_at` | TIMESTAMP | INDEX | |

---

## J. Exports de données

### `data_exports` (RGPD)

| Colonne | Type | Contraintes | Description |
|---------|------|-------------|-------------|
| `id` | UUID | PK | |
| `user_id` | UUID | | |
| `status` | VARCHAR(20) | DEFAULT 'pending' | pending/processing/completed/failed |
| `s3_key` | TEXT | NULLABLE | |
| `file_size_bytes` | BIGINT | NULLABLE | |
| `expires_at` | TIMESTAMP | | |
| `completed_at` | TIMESTAMP | NULLABLE | |
| `error_message` | TEXT | NULLABLE | |

---

## Utilisation de Redis

Redis est **optionnel** — dégradation gracieuse si indisponible.

| Cas d'usage | Pattern de clé | TTL | Fallback |
|-------------|---------------|-----|----------|
| **Sessions** | `sessions:{token_hash}` | Configurable | Table `sessions` |
| **Cache HTTP** | `http_cache:{sha256(url)}` | 5-15 min | Pas de cache |
| **Rate limiting** | `ratelimit:{key}` | Variable | In-memory avec mutex |
| **Verrouillage compte** | `lockout:{email}:count`, `lockout:{email}:locked_until` | Configurable | In-memory map |
| **Token blacklist** | `token_blacklist:{jti}` | Durée JWT | Pas de révocation |
| **Présence utilisateur** | `presence:{user_id}` | Variable | Table `user_presence` |
| **Cache applicatif** | Via `CacheService` | Variable | Pas de cache |

Headers de cache : `X-Cache: HIT` ou `X-Cache: MISS`

---

## Statistiques

| Métrique | Valeur |
|----------|--------|
| Fichiers modèles Go | 68 |
| Tables PostgreSQL | 60+ |
| Migrations SQL | 115 |
| Tables avec soft delete | 20+ |
| Tables avec UUIDs | 95%+ |
| Cas d'usage Redis | 6 principaux |

---

## Documents liés

- [[ARCHITECTURE_VEZA]] — Architecture globale
- [[ROUTES_API]] — Endpoints API complets
- [[SERVEUR_STREAMING_RUST]] — Serveur streaming
- [[CONFIGURATION_ENVIRONNEMENT]] — Variables d'environnement