The Problem

Rekordbox has no native "Last Played" field and no smart playlist functionality based on play history. Play history is recorded, but only within auto-generated History playlists — and these are excluded from XML exports and cannot be used as a filter within Rekordbox itself. For an active DJ managing a library of several thousand tracks, this makes it impossible to identify music that hasn't been played for months or years — useful both for library housekeeping and rediscovering forgotten tracks.

The Investigation

I knew the data existed — the question was how to access it. I used multiple LLMs (ChatGPT, Gemini and Claude) iteratively to map out the options, and this process itself became one of the more interesting aspects of the project.

Each LLM had partial knowledge and partial blind spots. ChatGPT correctly identified the Rekordbox database as the right data source and outlined a viable pipeline, but never identified the fundamental blocker: the database is encrypted. Gemini got further — correctly identifying SQLCipher encryption — but wrongly stated the key was unique to each machine, which would have sent the investigation down a dead end. Claude identified that Pioneer uses a single hardcoded SQLCipher key across all Rekordbox installations — publicly documented by the developer community — which unlocked the path forward.

Progress required consistent challenge and verification — confident-sounding answers were sometimes wrong, and it was only by questioning outputs and testing assumptions that a clear, workable solution emerged. Knowing the Rekordbox ecosystem gave confidence that the data existed and the problem was solvable, but it was a methodical, detail-oriented approach that ultimately got it there.

What I Built

The core design principle was safety — rather than writing directly to the live database and risking any unintended changes, the solution reads a decrypted copy and uses Rekordbox's own XML import mechanism to surface the results. No changes are ever made to the live database.

The pipeline:

1. Export rekordbox.xml from Rekordbox (captures current track IDs)
2. Decrypt a read-only copy of master.db using the published SQLCipher key
3. Query play history via SQL to compute last played date per track
4. Bucket tracks into five time-based playlists (Last 12 Months, 12–24, 24–36, 36–48, 48+)
5. Inject playlists into rekordbox.xml using track IDs as the join key
6. Reload the xml source in Rekordbox and import the playlists
7. Clean up — the decrypted copy is deleted automatically after use

The whole process runs via a single macOS Shortcut, with user-friendly dialogs covering dependency checks, stale XML warnings and encryption key validation.

Key Technologies

Python, SQL, SQLite, SQLCipher, Shell scripting, XML, macOS Shortcuts, Rekordbox.

Skills Demonstrated

Proprietary data access and reverse engineering, SQL query design, ETL pipeline thinking, XML manipulation, shell scripting, automated error handling, critical evaluation of AI-generated outputs, technical documentation.

• View on GitHub Gist
• Back to Projects