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
`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
`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

Schéma Base de Données Veza

Stratégie de migration

Conventions

A. Utilisateurs & Authentification

users

sessions

refresh_tokens

roles

permissions

user_roles (table pivot)

role_permissions (table pivot)

mfa_configs

recovery_codes

webauthn_credentials

federated_identities

api_keys

user_settings

user_profiles

user_presence

B. Pistes audio

tracks

track_versions

track_likes

track_plays

track_shares

track_comments

track_reposts

track_stems

track_lyrics

track_history

tags

track_tags (pivot)

genres

track_genres (pivot)

playback_analytics

C. Playlists

playlists

playlist_tracks (pivot ordonné)

playlist_collaborators

playlist_follows

playlist_share_links

playlist_versions

user_genre_follows (pivot)

user_tag_follows (pivot)