Last-analyser

This is my own Last.fm listening history analyser, running on my own machines. It keeps my listening history in a local database, so after it has synced I can browse and analyse it without an internet connection.

It has two backends. scrobble_vault syncs and serves the data, and last_llm_service lets me analyse it using local or cloud LLM models. The SvelteKit frontend has a summary page using the vault and a chat page for the LLM service.

I made it because I want to move away from proprietary music streaming services, but I still want a way to get music recommendations.

Slide 1 is a recording of the whole thing running.

The project consists of 7 main parts:

• Scrobble vault
• Scrobble vault frontend, the summary page
• Last LLM service
• Last LLM service frontend, the chat page
• Last LLM service over MCP
• Settings
• Setup

Scrobble vault (seen in images 2 and 3)

• scrobble_vault pulls 200 scrobbles at a time from the Last.fm API into five Postgres 18 tables, which are artists, albums, tracks, scrobbles and last_sync.
• On the first run, it goes through my whole listening history. After that, it runs every 15 minutes and only fetches new listens.
• Artist, album and track information is cached locally. I also normalise the names, and a unique index on the track and timestamp stops duplicate requests and duplicate listens.
• Last.fm allows five requests per second, so every request goes through the same 200 ms gate.

• The vault has three read endpoints:

  • /music-summary is used by the summary page.
  • /vibed-sql-runner lets the LLM service run database queries.
  • /semantic-search lets the LLM service search artists, albums and tracks by their meaning.
• If the LLM service is turned off, the SQL runner and semantic search endpoints return a 404, so the vault can still run on its own.

• SQL runner:

  • /vibed-sql-runner runs SQL written by a model, which can be dangerous, so I put two layers of protection in front of it.
  • The first layer parses the query using sqlglot instead of checking strings. Markdown code fences and trailing semicolons are removed first because models often send SQL in a code block.
  • Only SELECT queries are allowed. The whole syntax tree is checked, so a write query cannot be hidden inside a CTE or subquery. It also blocks 16 functions, including pg_sleep and the dblink family.
  • Only the five real tables can be queried. If there is no limit, it adds one of 100. If the limit is too high, it caps it at 1000.
  • The query is rebuilt from the parsed syntax tree before it is run, so it does not directly run the model's original text.
  • The second layer is a Postgres role that can only read, made on the first container run. Even if the validator is bypassed, it still cannot write to the database. Vectors are also removed from results, so a SELECT * does not put all the embeddings into the model context.
  • If a query is rejected, it returns a 422 with the reason. The model can then change its query instead of getting stuck.

• Semantic search:

  • /semantic-search gives every artist, album and track a 384 dimension embedding from all-MiniLM-L6-v2, stored in a vector(384) column using pgvector.
  • For artists, I embed their tags, similar artists and bio. For albums, I embed their tags, track listing and wiki information. So I can search for what something sounds like, not just its name.
  • The results are ranked by cosine distance and each result has a similarity score, which helps the model decide if the match is useful.
  • I use FastEmbed for this, which is an ONNX build without PyTorch. It can run on a CPU, is only an 87 MB download, and can be turned off without stopping the rest of the sync.

Scrobble vault frontend, the summary page (seen in images 4, 5 and 6)

• The overview page counts scrobbles, artists, albums and tracks over 7 days, 30 days, 365 days or all time.
• It also shows the top artists, albums and tracks with cover art, plus music that I discovered for the first time during that period.
• Listening patterns are shown as a 24 bar hour chart next to a 7 bar weekday chart.
• The music page on this site uses the same summary page, reading from my server. You can see it live on the music page.

Last LLM service

• scrobble_vault holds the music data and tools, while last_llm_service runs the agent loop that uses them.
• The four tools are describe_schema, query_music_db, get_music_summary and search_music. I define them once, then expose them through both MCP and the OpenAI tool format.
• The OpenAI side talks to any compatible endpoint, so the chat page can run on a local Ollama model or a hosted one.
• The agent can use up to 20 tool rounds for each question. This lets it read the database schema, make a query, notice a mistake and query again before answering.
• Before sending the summary to a model, I remove the cover art URLs and cap discovery lists. The raw 365 day summary is nearly 1 MB otherwise.

Last LLM service frontend, the chat page (seen in images 7 and 8)

• The chat streams responses over server sent events. It shows a chip for each tool call, which pulses while it is running and gets ticked when it returns, so you can see what it is doing during a slower question.
• Conversations are kept in the browser, so there are no accounts or server side chat histories. Answers are rendered as sanitised Markdown.

Last LLM service over MCP (seen in image 9)

• The same four tools are also served over MCP, so any client that supports it can use them with whatever model that client runs, Claude for example in the images.
• So it will work with providers that doesn't provide a OpenAI API compatible API, or prices tokens it way too high, like Claude.

Settings (seen in image 10)

• I split the config into two parts. A .env file holds the values needed before startup, and a settings API handles the rest. For a normal install on one machine, I do not need to edit the .env file.
• Each service publishes a spec with its labels, types, ranges, help text and secret fields. Adding a setting is a JSON edit, not a new form.
• The settings page covers my Last.fm details, sync frequency, rate limit, model and the cap on tool rounds. Saving the settings is enough, nothing needs to restart, and every field shows whether it is a default, saved value or still needed.

Setup (seen in images 11 and 12)

• One Compose file brings up the database, vault, frontend and LLM services. I can also name one service on the command line if I only want to run that part.
• Everything binds to loopback by default, so the vault can run on a box that is always on while the frontend and model run on my laptop over a Wireguard VPN.
• What I actually run is two separate installs. My server runs the vault and its database, while my desktop runs the full stack with its own vault.
• Every version tag publishes images to the GitHub Container Registry, so a smaller machine can pull an image instead of building it. There is also a development override that mounts the source, so an edit applies without a rebuild.