senke/veza
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
veza/docs/DB_MIGRATIONS_AUDIT_V1.md
okinrev b7955a680c P0: stabilisation backend/chat/stream + nouvelle base migrations v1 
Backend Go:
- Remplacement complet des anciennes migrations par la base V1 alignée sur ORIGIN.
- Durcissement global du parsing JSON (BindAndValidateJSON + RespondWithAppError).
- Sécurisation de config.go, CORS, statuts de santé et monitoring.
- Implémentation des transactions P0 (RBAC, duplication de playlists, social toggles).
- Ajout d’un job worker structuré (emails, analytics, thumbnails) + tests associés.
- Nouvelle doc backend : AUDIT_CONFIG, BACKEND_CONFIG, AUTH_PASSWORD_RESET, JOB_WORKER_*.

Chat server (Rust):
- Refonte du pipeline JWT + sécurité, audit et rate limiting avancé.
- Implémentation complète du cycle de message (read receipts, delivered, edit/delete, typing).
- Nettoyage des panics, gestion d’erreurs robuste, logs structurés.
- Migrations chat alignées sur le schéma UUID et nouvelles features.

Stream server (Rust):
- Refonte du moteur de streaming (encoding pipeline + HLS) et des modules core.
- Transactions P0 pour les jobs et segments, garanties d’atomicité.
- Documentation détaillée de la pipeline (AUDIT_STREAM_*, DESIGN_STREAM_PIPELINE, TRANSACTIONS_P0_IMPLEMENTATION).

Documentation & audits:
- TRIAGE.md et AUDIT_STABILITY.md à jour avec l’état réel des 3 services.
- Cartographie complète des migrations et des transactions (DB_MIGRATIONS_*, DB_TRANSACTION_PLAN, AUDIT_DB_TRANSACTIONS, TRANSACTION_TESTS_PHASE3).
- Scripts de reset et de cleanup pour la lab DB et la V1.

Ce commit fige l’ensemble du travail de stabilisation P0 (UUID, backend, chat et stream) avant les phases suivantes (Coherence Guardian, WS hardening, etc.).
2025-12-06 11:14:38 +01:00

4 KiB
Raw Permalink Blame History

🕵️ DB Migrations Audit V1

Date: 04/12/2025 Author: Staff Engineer / DBA Scope: veza-backend-api Database Schema & Migrations

1. Executive Summary

The current database schema is in a transitional "Hybrid" state, resulting from an incomplete migration from INT/BIGINT to UUID. While core entities (users, tracks, playlists) have been migrated to UUIDs, the surrounding infrastructure (secondary tables, audit logs, tokens, junction tables) remains largely on BIGINT sequences.

This audit establishes the roadmap to move from this "Lab/Repair" state to a Canonical V1 Schema that is purely UUID-based, consistent, and production-ready.

Note on Source of Truth: The file docs/ORIGIN_DATABASE_SCHEMA.md was referenced but not found. This audit treats docs/UUID_DB_MIGRATION_PLAN.md (Target Architecture) and the current veza_uuid_lab_schema.sql (Entity Inventory) as the combined Source of Truth.

2. Gap Analysis: Lab Schema vs. Target V1

2.1 Core Conformance (Status: ✅ Mostly Good)

The core entities align with the UUID target.

  • Users: id is UUID.
  • Tracks: id is UUID.
  • Playlists: id is UUID.
  • RBAC (Roles/Permissions): id is UUID.

2.2 Critical Deficiencies (Status: ❌ Needs Fix)

The following tables currently use BIGINT (SERIAL) Primary Keys in the Lab Schema. In V1, these MUST be UUID.

Domain Table Current PK Target V1 PK
Auth refresh_tokens bigint UUID
Auth password_reset_tokens bigint UUID
Auth email_verification_tokens bigint UUID
Auth user_sessions bigint UUID
Streaming bitrate_adaptation_logs bigint UUID
Streaming hls_streams bigint UUID
Streaming hls_transcode_queue bigint UUID
Streaming playback_analytics bigint UUID
Streaming track_comments bigint UUID
Streaming track_history bigint UUID
Streaming track_likes bigint UUID
Streaming track_plays bigint UUID
Streaming track_shares bigint UUID
Streaming track_versions bigint UUID
Social playlist_collaborators bigint UUID
Social playlist_follows bigint UUID
Chat (Legacy) rooms bigint UUID

2.3 Structural Issues

  1. Inconsistent Defaults: Some tables use now(), others CURRENT_TIMESTAMP. V1 will standardize on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP.
  2. Missing deleted_at: Several tables lacking soft-delete where implied by domain (e.g., playlist_collaborators has it, but playlist_tracks does not). V1 will apply soft-deletes consistently for user-managed resources.
  3. Foreign Key Constraints: Many FKs in the Lab schema lack explicit ON DELETE rules. V1 will enforce ON DELETE CASCADE for ownership relationships (e.g., User -> RefreshToken) and ON DELETE SET NULL or RESTRICT for references.

3. Schema Governance & Separation

Per UUID_DB_MIGRATION_PLAN.md:

  • public Schema: Owned by veza-backend-api. Contains users, auth, tracks, playlists (and their satellite tables).
  • chat Schema: Owned by veza-chat-server. Contains conversations, messages.

V1 Scope Decision: The veza-backend-api migrations will strictly manage the public schema.

  • Legacy rooms table (if still used by Go) will be migrated to UUID in public.
  • New chat schema tables will NOT be created by these migrations to respect the separation of concerns, unless a specific "Schema Init" migration is required for integration tests.

4. Recommendation

Proceed with the V1 "Clean Slate" Strategy:

  1. Archive all existing 001...072 migrations.
  2. Create a fresh set of migrations (001...999) that define the tables correctly (UUID) from the start.
  3. Do not implement "repair" scripts; implement the "final state".