corpus

self-hosted listenbrainz and last.fm proxy
Log | Files | Refs | README | LICENSE

duckdb.md (2879B)

      1 # DuckDB in Corpus
      2 
      3 Corpus uses [DuckDB](https://duckdb.org/) as its primary analytical database. DuckDB's columnar storage and efficient query engine allow Corpus to provide fast filtering, pagination, and statistics over large sets of listening history data.
      4 
      5 ## Database Schema
      6 
      7 The database consists of two main tables:
      8 
      9 ### `scrobbles`
     10 Stores the raw listening history synced from ListenBrainz and/or Last.fm.
     11 
     12 | Column | Type | Description |
     13 | :--- | :--- | :--- |
     14 | `listened_at` | BIGINT | Unix timestamp (Primary Key) |
     15 | `track_name` | VARCHAR | Name of the track |
     16 | `artist_name` | VARCHAR | Name of the artist |
     17 | `release_name` | VARCHAR | Name of the album/release |
     18 | `release_mbid` | VARCHAR | MusicBrainz Release ID |
     19 | `caa_release_mbid` | VARCHAR | Cover Art Archive Release ID |
     20 
     21 ### `release_metadata`
     22 Stores enriched metadata fetched from MusicBrainz, Last.fm, and Discogs.
     23 
     24 | Column | Type | Description |
     25 | :--- | :--- | :--- |
     26 | `release_mbid` | VARCHAR | MusicBrainz Release ID (Primary Key) |
     27 | `genre` | VARCHAR | Primary genre |
     28 | `label` | VARCHAR | Record label |
     29 | `release_year` | INTEGER | Year of release |
     30 | `genre_checked_at` | INTEGER | Timestamp of last enrichment attempt |
     31 
     32 ### `api_tokens`
     33 Stores hashed user API tokens for scrobble submission.
     34 
     35 | Column | Type | Description |
     36 | :--- | :--- | :--- |
     37 | `slug` | VARCHAR | User slug (Primary Key) |
     38 | `hashed_token` | VARCHAR | SHA-256 hash of the API token (Unique) |
     39 
     40 ## Application Usage
     41 
     42 The application interacts with DuckDB via a PureScript FFI layer (`src/Db.js` and `src/Db.purs`).
     43 - **BigInt Handling**: Since DuckDB returns `BIGINT` as JavaScript `BigInt`, the FFI layer converts these to `Number` to ensure compatibility with standard JSON serialization.
     44 - **Background Enrichment**: The server identifies "unenriched" scrobbles (those with an MBID but no metadata) and performs background updates to the `release_metadata` table.
     45 
     46 ## Common Analytical Queries
     47 
     48 You can run these queries directly against your `corpus.db` file using the DuckDB CLI or any compatible tool.
     49 
     50 ### Top 10 Artists of All Time
     51 ```sql
     52 SELECT artist_name, count(*) as play_count
     53 FROM scrobbles
     54 GROUP BY artist_name
     55 ORDER BY play_count DESC
     56 LIMIT 10;
     57 ```
     58 
     59 ### Listening Activity by Hour
     60 ```sql
     61 SELECT
     62     extract('hour' from to_timestamp(listened_at)) as hour,
     63     count(*) as count
     64 FROM scrobbles
     65 GROUP BY hour
     66 ORDER BY hour;
     67 ```
     68 
     69 ### Genre Distribution
     70 ```sql
     71 SELECT rm.genre, count(*) as count
     72 FROM scrobbles s
     73 JOIN release_metadata rm ON s.release_mbid = rm.release_mbid
     74 WHERE rm.genre IS NOT NULL
     75 GROUP BY rm.genre
     76 ORDER BY count DESC;
     77 ```
     78 
     79 ### MBID Enrichment Coverage
     80 ```sql
     81 SELECT
     82     count(*) as total,
     83     count(release_mbid) FILTER (WHERE release_mbid != '') as with_mbid,
     84     (count(release_mbid) FILTER (WHERE release_mbid != '')::FLOAT / count(*)) * 100 as percentage
     85 FROM scrobbles;
     86 ```