A C# application that syncs your Spotify library to PostgreSQL for offline access and custom analytics. Analyze your music collection with genre statistics, artist insights, playlist analysis, and temporal trends.
- ✅ Full Library Sync - Import all saved tracks, artists, albums, playlists (including playlist-only tracks)
- ✅ Incremental Sync - Fast updates for new/changed data only (tracks, artists, albums, playlists)
- ✅ Partial Sync - Sync individual stages (tracks, artists, albums, playlists)
- ✅ PostgreSQL Storage - Local database with snake_case naming for offline queries
- ✅ Interactive CLI - Beautiful terminal interface powered by Spectre.Console
- ✅ Track Navigation - Browse by artist, playlist, genre, or search by name
- ✅ Paginated Tables - View large datasets with 30 rows per page navigation
- ✅ Analytics Views - 6 functional PostgreSQL views for data visualization
- ✅ Sync History - Track all import operations with statistics
- ✅ Rate Limiting - Respects Spotify API limits (30 requests/min)
- ✅ Genre Analysis - Comprehensive genre landscape with overlaps and statistics
- ✅ Genre Clustering - Auto-suggested clusters for playlist organization
- ✅ Interactive Cluster Refinement - Review and remove genres that don't fit
- ✅ Cluster Persistence - Save, edit, delete, and finalize refined clusters
- ✅ Cluster Management - Full CRUD operations with database persistence
- ✅ Smart Genre Handling - Orphaned genres intelligently reassigned or tracked
- ✅ Playlist Generation - Create Spotify playlists from finalized genre clusters
- ✅ Track Exclusion System - Remove specific tracks from cluster playlists
- ✅ Artist Insights - Top artists by follower count, track count, and popularity
- ❌ Audio Features -
Unavailable (Spotify API restricted as of Nov 27, 2024)
- 📅 Track Preview - View track lists within clusters before playlist creation
- 📅 Genre Filter - Exclude already-organized genres from new suggestions
- 📅 Advanced Reports - Genre trends, artist discovery, playlist insights
- 📅 Web Interface - Browse and analyze your library in a browser
- 📅 Audio Features - Exploring third-party APIs and local analysis tools
- 📅 Playlist Sync Back - Detect and sync manual changes to generated playlists
SpotifyTools/
├── SpotifyTools.Domain # Entity models (Track, Artist, Album, etc.)
├── SpotifyTools.Data # EF Core, repositories, Unit of Work
├── SpotifyTools.Sync # Sync orchestration with rate limiting
├── SpotifyTools.Analytics # Analytics and reporting (coming soon)
├── SpotifyClientService # Spotify API wrapper with OAuth
└── SpotifyGenreOrganizer # CLI interface
-
.NET 8.0 SDK
- Download from https://dotnet.microsoft.com/download
-
Docker Desktop
- For running PostgreSQL database
- Download from https://www.docker.com/products/docker-desktop
-
Spotify Developer App
- Go to https://developer.spotify.com/dashboard
- Create a new app
- Note your Client ID and Client Secret
- Add redirect URI:
http://127.0.0.1:5009/callback - Note: Apps created after Nov 27, 2024 cannot access audio features API
cd /path/to/spotify_tools# Copy environment template
cp .env.template .env
# Edit .env and set a secure password
# Then start PostgreSQL
docker-compose up -d
# Verify it's running
docker-compose ps# Copy the template and edit with your credentials
cd src/SpotifyGenreOrganizer
cp appsettings.json.template appsettings.jsonEdit appsettings.json with your Spotify credentials and database password:
{
"ConnectionStrings": {
"SpotifyDatabase": "Host=localhost;Port=5433;Database=spotify_tools;Username=spotify_user;Password=YOUR_PASSWORD"
},
"Spotify": {
"ClientId": "YOUR_CLIENT_ID",
"ClientSecret": "YOUR_CLIENT_SECRET",
"RedirectUri": "http://127.0.0.1:5009/callback"
}
}cd src/SpotifyTools.Data
dotnet ef database updatecd ../SpotifyGenreOrganizer
dotnet run╔══════════════════════════════════════╗
║ Spotify Tools - Main Menu ║
╚══════════════════════════════════════╝
1. Full Sync (Import all data)
2. Incremental Sync (Update changes only)
3. Partial Sync (Select stages)
4. Genre Analysis
5. Explore Genre Clusters & Playlists
6. View Last Sync Status
7. View Sync History
8. Track Detail Report
9. Exit
Option 1 performs a complete import:
- Authenticates with Spotify (opens browser)
- Fetches all saved tracks with metadata
- Fetches artist details with genres and follower counts
- Fetches album details with labels and release dates
- Fetches all user playlists including tracks not in your saved library
- Syncs audio features (currently limited - see API restrictions below)
Time Estimate:
- ~30-45 minutes per 3,000 tracks (due to API rate limiting)
- Progress updates shown in real-time
Option 2 performs a fast update of changed data only:
- Checks last sync date (falls back to full sync if >30 days)
- Fetches only new tracks added since last sync (filtered by date)
- Enriches artist/album stubs created during previous syncs
- Refreshes metadata for artists/albums not updated in 7+ days
- Checks playlist SnapshotIds and re-syncs only changed playlists
- Much faster than full sync - typically completes in 2-5 minutes
When to Use:
- Daily or weekly library updates
- After adding new tracks to Spotify
- After modifying playlists
- To keep metadata fresh without re-importing everything
Benefits:
- Significantly faster than full sync (processes only changes)
- Lower API usage (fewer rate limit concerns)
- Automatic stub enrichment (completes partial data from playlists)
- Smart playlist detection (only re-syncs modified playlists)
Options 2 & 3 show:
- Last sync completion time
- Statistics (tracks, artists, albums processed)
- Success/failure status
- Historical sync data
All database tables and columns use snake_case naming (e.g., track_id, duration_ms, first_synced_at), following PostgreSQL conventions.
- tracks - Track metadata (name, duration, popularity, ISRC, added dates)
- artists - Artist data (name, genres, popularity, followers)
- albums - Album information (name, release date, label, album type)
- playlists - User playlists
- audio_features
⚠️ - Exists but unpopulated (Spotify API restricted) - audio_analyses
⚠️ - Exists but unpopulated (Spotify API restricted)
- track_artists - Many-to-many with artist position tracking
- track_albums - Track-album relationships with disc/track numbers
- playlist_tracks - Playlist contents with positions
- sync_history - Tracks all sync operations
- spotify_tokens - OAuth tokens (future use)
6 fully functional views for data visualization and analysis:
- v_tracks_with_artists - Denormalized track-artist relationships
- v_tracks_with_albums - Tracks with album details
- v_playlist_contents - Playlist contents with track details
- v_genre_stats - Genre statistics with track counts and popularity ⭐
- v_artist_performance - Artist metrics and analytics ⭐
- v_sync_summary - Human-readable sync history
Limited functionality (audio features columns will be NULL): 7. v_track_complete_details - Complete track info (audio features unavailable) 8. v_high_energy_tracks - Cannot filter by energy/danceability (no data)
See DOCKER.md for view descriptions and example queries.
Audio Features API Unavailable: On November 27, 2024, Spotify restricted access to the /v1/audio-features endpoint for new applications. This means:
- ❌ Cannot fetch: tempo, key, mode, danceability, energy, valence, acousticness, etc.
- ✅ Can still fetch: tracks, artists, albums, playlists, genres, popularity
- 📊 Focus shifted to: genre analysis, artist insights, playlist trends, temporal patterns
What You Can Still Analyze:
- Genre distribution and popularity across your library
- Top artists by followers, track count, and popularity
- Album release trends over time
- Playlist composition and track overlap
- Library growth and listening history patterns
- Artist discovery and genre exploration
Future Plans:
- Exploring third-party APIs (Cyanite, Soundcharts)
- Local audio analysis tools (Essentia, similar solutions)
- Alternative data enrichment sources
- Port: 5433 (mapped from container)
- Database: spotify_tools
- User: spotify_user
- Password: Set in
.envfile
- Rate Limit: 30 requests/minute (configurable in code)
- Scopes: UserLibraryRead, PlaylistModifyPublic, PlaylistModifyPrivate
- OAuth: Authorization code flow with browser
- Ensure redirect URI in Spotify Dashboard exactly matches:
http://127.0.0.1:5009/callback - Use
127.0.0.1notlocalhost - Verify Client ID and Secret are correct
- PostgreSQL runs on port 5433 to avoid conflicts with local installations
- Change port in
docker-compose.ymland connection string if needed
- Ensure database migrations are applied:
dotnet ef database update - Check PostgreSQL is running:
docker-compose ps
- This is handled in the code
- All DateTimes are converted to UTC before saving
- docs/context.md - Detailed project status, architecture decisions, and progress tracking
- docs/issues.md - Known bugs and planned improvements
- CLAUDE.md - Instructions for Claude Code when working on this project
- DOCKER.md - Docker setup and PostgreSQL management guide
- docs/BACKUP.md - Backup and restore procedures
- docs/INCREMENTAL_SYNC_FIX.md - Incremental sync implementation details
- docs/ADD_TO_PLAYLIST_FEATURE.md - Playlist generation feature documentation
- docs/SPOTIFY_SYNC_FEATURE.md - Sync system architecture
- docs/SYNC_STRATEGY.md - Sync optimization strategies
- docs/API_SUMMARY.md - Spotify API endpoint reference
- docs/WebUIArchitecture.md - Planned web interface design
- docs/PHASE1_COMPLETE.md - Phase 1 completion notes
- docs/PHASE2A_COMPLETE.md - Phase 2A completion notes
- docs/PHASE2B_COMPLETE.md - Phase 2B completion notes
- archive/sql/ - One-off SQL helper scripts
- Language: C# / .NET 8
- Database: PostgreSQL 16 (Docker)
- ORM: Entity Framework Core 8.0
- Spotify API: SpotifyAPI.Web 7.2.1
- Architecture: Clean Architecture with Repository pattern
- Project structure and domain models
- Data layer with EF Core
- Sync service with rate limiting
- CLI interface with Spectre.Console
- Full library import (tracks, artists, albums, playlists)
- Database views for analytics
-
Genre Clustering & Playlist Organization:
- ✅ Genre analysis with overlap detection
- ✅ Auto-suggested genre clusters (10 predefined patterns + individual large genres)
- ✅ Interactive cluster refinement (view all genres, remove genres that don't fit)
- ✅ Smart orphaned genre handling (create new clusters, unclustered bucket, suggestions)
- ✅ Cluster persistence with full CRUD operations
- ✅ Track exclusion system for fine-tuning cluster playlists
- ✅ Spotify playlist generation from finalized clusters
-
Analytics and Reporting:
- ✅ Genre analysis and distribution reports
- ✅ Artist insights and discovery
- ✅ Track detail reports with complete metadata
- ✅ Cluster playlist reports with track counts
-
Sync Improvements:
- ✅ Incremental sync for fast updates (new tracks, stale metadata, changed playlists)
- ✅ Fixed playlist sync bugs (position calculation, playlist-only tracks)
- ✅ Complete metadata syncing for all tracks (including non-library playlist tracks)
-
Enhanced Analytics:
- Interactive visualizations
- Custom report generation
- Export capabilities (CSV, JSON)
-
Audio Features Alternatives:
- Integration with third-party APIs
- Local audio analysis pipeline
- Alternative data enrichment
- Track Preview in Clusters - View full track lists before playlist creation
- Genre Filter - Exclude already-organized genres from new cluster suggestions
- Playlist Composition Analysis - Track overlap, diversity metrics
- Temporal Trends - Library growth charts, release date patterns
- Advanced Reports - Exportable analytics (CSV, JSON)
- Web interface (ASP.NET Core with Blazor)
- Advanced recommendations engine
- External data integration (MusicBrainz, Last.fm)
- Backup and restore functionality
- Playlist sync back (detect manual changes to generated playlists)
This is a personal project, but suggestions and feedback are welcome!
MIT License - Feel free to use and modify for your own purposes.
- Built with SpotifyAPI-NET
- Powered by Entity Framework Core
- Database: PostgreSQL