veza/docs/DB_MIGRATIONS_ORIGIN_DIFF.md

# 🔍 DB Migrations Origin Diff

**Date:** 04/12/2025
**Scope:** `veza-backend-api` vs `ORIGIN_DATABASE_SCHEMA.md`

This document highlights the divergences between the intended V1 migrations and the Source of Truth (Origin).

## 1. Global Divergences

| Feature | Origin Spec | Current V1 Implementation | Action |
| :--- | :--- | :--- | :--- |
| **Primary Keys** | `UUID DEFAULT gen_random_uuid()` | `UUID DEFAULT gen_random_uuid()` | ✅ Aligned |
| **Timestamps** | `created_at`, `updated_at` (TIMESTAMPTZ) | `created_at`, `updated_at` (TIMESTAMPTZ) | ✅ Aligned |
| **Updated Trigger** | Mandatory | Implemented via `900_triggers.sql` | ✅ Aligned |
| **Indexes** | Snake_case `idx_<table>_<cols>` | Mixed naming | ⚠️ Rename to standard |
| **Soft Deletes** | Mandatory for user-facing | Partially implemented | ⚠️ Fix missing `deleted_at` |

---

## 2. Table-by-Table Diff

### 2.1 Auth & Users

#### `users`
*   **Origin:** `email` (unique), `username` (unique), `password_hash`, `role` (ENUM), `is_active`, `is_verified`, `is_banned`, `token_version`, `last_login_at`, `login_count`.
*   **V1:** Has most fields.
*   **Divergences:**
    *   `role`: V1 uses `VARCHAR`, Origin requires `ENUM user_role`.
    *   `is_banned`: Missing in V1.
    *   `login_count`, `last_login_ip`: Missing in V1.
    *   `email_verified_at`, `last_password_change_at`: Missing in V1.
    *   `avatar`, `bio` in V1 are in `users`, but Origin puts them in `user_profiles`.
    *   **Decision:** Move profile fields to `user_profiles`? **NO**, to maintain Go compatibility, we will keep basic profile fields in `users` for now but ADD the missing Origin fields (`is_banned`, etc.) and fix the `role` type.

#### `refresh_tokens`
*   **Origin:** `token_hash`, `device_name`, `device_type`, `ip_address`, `last_used_at`, `is_revoked`, `revoked_reason`.
*   **V1:** Simplified version.
*   **Action:** Add missing columns (`device_name`, `is_revoked`, etc.) to match Origin.

#### `federated_identities`
*   **Origin:** `provider_user_id`, `provider_email`, `provider_profile_data` (JSONB), `is_primary`.
*   **V1:** `provider_id` (naming mismatch), missing `provider_profile_data`, `is_primary`.
*   **Action:** Rename `provider_id` -> `provider_user_id`. Add missing columns.

### 2.2 Profiles

#### `user_profiles`
*   **Origin:** Separate table with `bio`, `location`, `website_url`, `birthdate`, `gender`, `theme`.
*   **V1:** Some fields are in `users`.
*   **Action:** Create `user_profiles` exactly as Origin. If `users` table duplicates data, we will deprecate the columns in `users` but keep them for Go compatibility, OR sync them via trigger.
*   **Strategy:** Create the full `user_profiles` table.

### 2.3 Streaming (Tracks & Playlists)

#### `tracks`
*   **Origin:** `creator_id` (FK users), `file_id` (FK files), `visibility` (ENUM), `bpm`, `musical_key`.
*   **V1:** `user_id` (FK users), `file_path` (No `files` table relation), `status` (VARCHAR).
*   **Divergences:**
    *   **Major:** Origin links `tracks` -> `files`. V1 stores `file_path` directly on `tracks`.
    *   **Constraint:** Creating a `files` table implies a major refactor of the Go backend if it expects `file_path` on `tracks`.
    *   **Action:** We will Create the `files` table as per Origin. We will **keep** `file_path` on `tracks` for Go compatibility (marked as legacy/denormalized) but ALSO add `file_id` (nullable for now) to pave the way for the target schema.
    *   `user_id` vs `creator_id`: V1 uses `user_id`. Origin uses `creator_id`. We will Add `creator_id` and sync it or Rename it if safe (Go uses `UserID`). -> **Keep `user_id`** to avoid breaking Go, but map it mentally. *Actually*, Origin says `creator_id`. I will add `creator_id` and make `user_id` a generated col or alias if possible, or just accept the divergence for now. **Decision: Keep `user_id` for Go compatibility, add comment.**

#### `playlists`
*   **Origin:** `name`, `visibility` (ENUM), `is_collaborative`.
*   **V1:** `title`, `is_public` (BOOL).
*   **Action:**
    *   Add `name` (or rename `title` -> `name` if code allows, otherwise keep `title` and add `name` as generated/synced). -> **Keep `title`**, Origin says `name`. We will use `title` as it's standard in this codebase.
    *   Add `visibility` ENUM (map `is_public` to it).

### 2.4 Files

*   **Origin:** `files` table with storage info, metadata, hash.
*   **V1:** No `files` table.
*   **Action:** **Implement `files` table** from Origin. It's critical for the "File Management" module.

---

## 3. Plan of Action

1.  **001_extensions_and_types.sql:** Add `user_role`, `visibility`, `message_type` ENUMs.
2.  **010_auth.sql:** Align `users`, `refresh_tokens` with Origin columns.
3.  **020_profiles.sql:** Implement full `user_profiles` table.
4.  **030_files.sql:** Implement `files` table (New).
5.  **040_streaming.sql:** Update `tracks`, `playlists` to reference `files` and use ENUMs.
6.  **900_triggers.sql:** Ensure all have `updated_at` triggers.