Keeping a 12k-track listening log queryable in plain SQLite

I keep a listening log. Not scrobbles — those live on someone else’s server. This is a local file I started in 2019, now at roughly 12,400 rows, one row per track I actually sat through. Until last month it was a CSV that I opened maybe twice a year. The problem wasn’t storage, it was that every question I had (“what did I play the week after that breakup”, “which records did I only ever spin once”) meant opening a spreadsheet and squinting.

So I moved it into SQLite and wrote about forty lines of Python around it. Schema first, because the shape of the questions told me what to store:

CREATE TABLE plays (
  id INTEGER PRIMARY KEY,
  played_at TEXT NOT NULL,      -- ISO8601, local time, no tz games
  artist TEXT NOT NULL,
  album TEXT,
  title TEXT NOT NULL,
  duration_s INTEGER,
  source TEXT,                  -- vinyl / file / stream
  context TEXT                  -- freeform: room, mood, whatever
);
CREATE INDEX idx_plays_time ON plays(played_at);
CREATE INDEX idx_plays_artist ON plays(artist);

Ingestion is deliberately dumb. A tiny CLI appends, and I never hand-edit the DB:

import sqlite3, datetime, sys
con = sqlite3.connect("plays.db")
con.execute(
    "INSERT INTO plays (played_at, artist, album, title, duration_s, source, context) "
    "VALUES (?,?,?,?,?,?,?)",
    (datetime.datetime.now().isoformat(timespec="minutes"), *sys.argv[1:7]),
)
con.commit()

That’s it. log.py "Low" "I Could Live in Hope" "Words" 350 file "late, headphones". Two seconds, done while the track is still playing. The queries are where it pays off:

-- artists I played once and never returned to
SELECT artist, MIN(played_at) FROM plays
GROUP BY artist HAVING COUNT(*) = 1
ORDER BY MIN(played_at) DESC LIMIT 30;

-- the 2am problem, verbatim
SELECT title, COUNT(*) c FROM plays
WHERE substr(played_at, 12, 2) IN ('00','01','02')
GROUP BY artist, title ORDER BY c DESC LIMIT 20;

Why SQLite and not a notes app or a text file: I wanted joins and grouping without writing them. The cost is real — there’s no full-text search unless I add FTS5, no fuzzy artist matching, and typos in artist silently fragment your history (I have both “Stereolab” and “stereolab” in there; I normalized once with an UPDATE ... WHERE artist = 'stereolab' and now I’m careful). Local timestamps also mean DST transitions create an hour that appears twice, which I decided to live with rather than store UTC and convert at query time. If you ever move across timezones, that choice will bite you and I know it will bite me.

Where this breaks down: it doesn’t scale to a decade of passive scrobbling — 12k rows is nothing, but 300k with this naive schema and no partitioning means the GROUP BY artist scans start to feel slow in a terminal. It also fails if you don’t actually log consistently; a log with gaps answers questions about the log, not about your listening. I lost six weeks in 2021 to a laptop migration and that hole is visible every time I query by month.

What I’m genuinely unsure about: I only record duration, not whether I skipped. Anyone found a low-friction way to capture “started but bailed after 40 seconds” without turning listening into telemetry?