AudiOhm/backend/MIGRATION_SUMMARY.md

Migration Alembic - Summary

Overview

Une migration Alembic complète a été créée pour ajouter les tables listening_history et liked_tracks à la base de données AudiOhm.

Files Created

1. Configuration Alembic

/opt/audiOhm/backend/alembic.ini

Fichier de configuration principal d'Alembic qui définit:

• L'emplacement des scripts de migration
• L'URL de connexion à la base de données
• Le format de nommage des fichiers de migration
• La configuration du logging

/opt/audiOhm/backend/alembic/env.py

Configuration de l'environnement Alembic qui:

• Charge les variables d'environnement depuis .env
• Importe tous les modèles SQLAlchemy
• Convertit l'URL asyncpg en URL PostgreSQL synchrone pour Alembic
• Configure les métadonnées pour la génération automatique

2. Migration File

/opt/audiOhm/backend/alembic/versions/001_add_library_tables.py

Migration principale qui crée deux tables:

Table listening_history:

• Stocke l'historique d'écoute des utilisateurs
• Colonnes: id, user_id, track_id, played_for, completed, source, played_at, created_at
• Foreign Keys avec CASCADE delete sur users et tracks
• 6 indexes pour optimiser les requêtes courantes

Table liked_tracks:

• Stocke les morceaux favoris des utilisateurs
• Colonnes: id, user_id, track_id, notes, created_at, updated_at
• Foreign Keys avec CASCADE delete sur users et tracks
• Contrainte unique sur (user_id, track_id) pour éviter les doublons
• 4 indexes pour des performances optimales

3. Documentation et Scripts

/opt/audiOhm/backend/ALEMBIC_GUIDE.md

Guide complet d'utilisation d'Alembic incluant:

• Structure des tables créées
• Toutes les commandes Alembic utiles
• Instructions pour la première installation
• Bonnes pratiques et dépannage

/opt/audiOhm/backend/run_migration.sh

Script shell pour faciliter l'exécution des migrations:

# Voir l'état actuel
./run_migration.sh current

# Appliquer les migrations
./run_migration.sh upgrade

# Annuler la dernière migration
./run_migration.sh downgrade-1

# Voir l'aide
./run_migration.sh help

Database Schema

listening_history Table

CREATE TABLE listening_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    track_id UUID NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    played_for INTEGER NOT NULL DEFAULT 0,
    completed BOOLEAN NOT NULL DEFAULT FALSE,
    source VARCHAR(50),
    played_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX ix_listening_history_id ON listening_history(id);
CREATE INDEX ix_listening_history_user_id ON listening_history(user_id);
CREATE INDEX ix_listening_history_track_id ON listening_history(track_id);
CREATE INDEX ix_listening_history_played_at ON listening_history(played_at);
CREATE INDEX ix_listening_history_user_played ON listening_history(user_id, played_at);
CREATE INDEX ix_listening_history_user_track ON listening_history(user_id, track_id);

liked_tracks Table

CREATE TABLE liked_tracks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    track_id UUID NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    notes VARCHAR(1000),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, track_id)
);

-- Indexes
CREATE INDEX ix_liked_tracks_id ON liked_tracks(id);
CREATE INDEX ix_liked_tracks_user_id ON liked_tracks(user_id);
CREATE INDEX ix_liked_tracks_track_id ON liked_tracks(track_id);
CREATE INDEX ix_liked_tracks_user_track ON liked_tracks(user_id, track_id);

How to Use

First Time Setup

1. Ensure PostgreSQL is running:

   sudo systemctl start postgresql
   

2. Verify database exists:

   sudo -u postgres psql -l
   

3. Check current status:

   cd /opt/audiOhm/backend
   ./run_migration.sh status
   

4. Apply migration:

   ./run_migration.sh upgrade
   

Development Workflow

When you modify SQLAlchemy models:

1. Create a new migration:

   alembic revision --autogenerate -m "Description of changes"
   

2. Review the generated migration file

3. Apply the migration:

   ./run_migration.sh upgrade
   

Production Deployment

1. Backup database:

   pg_dump spotify_le_2 > backup_$(date +%Y%m%d_%H%M%S).sql
   

2. Apply migrations:

   ./run_migration.sh upgrade
   

3. Verify application works correctly

Alembic Commands Reference

# From /opt/audiOhm/backend directory:

alembic current              # Show current version
alembic history              # Show all migrations
alembic heads                # Show latest versions
alembic upgrade head         # Apply all migrations
alembic upgrade +1           # Apply next migration only
alembic downgrade -1         # Revert last migration
alembic downgrade base       # Revert all migrations
alembic show <revision_id>   # Show migration details
alembic upgrade head --sql   # Show SQL without executing

Verification

After applying the migration, verify tables exist:

sudo -u postgres psql spotify_le_2

# List all tables
\dt

# Check listening_history table
\d listening_history

# Check liked_tracks table
\d liked_tracks

# Check Alembic version table
SELECT * FROM alembic_version;

# Exit
\q

Testing

Test that the migration works correctly:

# Check Python syntax
python3 -m py_compile alembic/versions/001_add_library_tables.py

# Validate Alembic can read the migration
alembic show 001_add_library_tables

# Check SQL generation (dry run)
alembic upgrade head --sql

Key Features

1. UUID Primary Keys: Uses PostgreSQL's gen_random_uuid() for unique identifiers
2. CASCADE Deletes: Automatically removes history/likes when user or track is deleted
3. Optimized Indexes: Strategic indexes for common query patterns
4. Unique Constraint: Prevents duplicate likes on same track by same user
5. Timestamps: Automatic tracking of when records were created
6. Reversible: Full downgrade support to undo changes if needed

Performance Considerations

listening_history indexes:

• user_id: Fast filtering by user
• played_at: Chronological ordering
• (user_id, played_at): User history queries
• (user_id, track_id): Check for existing plays

liked_tracks indexes:

• user_id: Get all user's liked tracks
• track_id: Find who liked a track
• (user_id, track_id): UNIQUE constraint prevents duplicates

Migration Status

Current state:

• Migration ID: 001_add_library_tables
• Status: Ready to apply
• Dependencies: None (initial migration)
• Tables to create: 2 (listening_history, liked_tracks)
• Indexes to create: 10 total

Next Steps

1. Test migration on development database
2. Verify application works with new tables
3. Backup production database
4. Apply migration to production
5. Monitor for any issues

Troubleshooting

If you encounter issues:

1. Check PostgreSQL is running:

   sudo systemctl status postgresql
   

2. Verify database credentials in .env

3. Check database exists:

   sudo -u postgres psql -l | grep spotify
   

4. Review Alembic logs

5. Check migration file syntax

6. Test SQL manually in psql

Files Summary

/opt/audiOhm/backend/
├── alembic.ini                      # Alembic configuration
├── ALEMBIC_GUIDE.md                 # Complete usage guide
├── MIGRATION_SUMMARY.md             # This file
├── run_migration.sh                 # Migration helper script
└── alembic/
    ├── env.py                       # Environment configuration
    ├── script.py.mako               # Migration template
    ├── README                       # Alembic documentation
    └── versions/
        └── 001_add_library_tables.py  # Main migration file

Support

For issues or questions:

• Check /opt/audiOhm/backend/ALEMBIC_GUIDE.md
• Review Alembic documentation: https://alembic.sqlalchemy.org/
• Check PostgreSQL logs: sudo journalctl -u postgresql