--- id: 1 title: 'Spotify Usage Heatmap' stacks: ['JavaScript', 'TypeScript', 'Node.js', 'React', 'Next.js', 'PostgreSQL', 'Prisma', 'TailwindCSS'] slug: 'spotify-usage-heatmap' image: 'https://lucas.untethered4life.com/images/projects/spotify/spotify-heatmap-banner.png' link_demo: 'https://lucas.untethered4life.com/dashboard' link_github: 'https://lucas.untethered4life.com/dashboard' created_at: '2025-09-25' updated_at: '2025-09-25' --- Many developers have likely seen the classic Github repo submit history map. A visualization where each square in A GRID represents a day. The color intensity of each square indicating the number of commits made on that day. Darker squares represent days with more commits, while lighter squares represent days with fewer commits. A visualization that helps to quickly review coding activity over time. After including a Spotify "Now Playing" component on this site, which was pulled from a Github repository, my curiosity got the best of me and I delved into a rabbit hole of REST API calls, relational database sctructures, data transormations, jq bash scripts, server side cron jobs, and a whole lot more. The end result was a dashboard that visualizes my Spotify listening history in a granular and expressive way. In this article I will describe: - The steps I took to build the dashboard. - Challenges I faced along the way. - Technologies I used. - Lessons and insights I gained from the experience.

This "project" is a work in progress. `interface project {usageHeatmap: HeatmapDisplayProps; site: webSite; author?: any | Luke}`
Everything I commit to has becomes a part of who I am. But those things do not define me. `export interface Luke { any }` I know that there are many things to fix amongst this work in progress, it is on my agenda (the layout on mobile will be resolved soon, please check it out on a desktop in the meantime, I have to go get sleep).

---
# Getting Started ## Sampling Spotify Data I started by sampling data returned from the Spotify API. I used [Insomnia](https://insomnia.rest/) to make REST API calls to Spotify and view the returned data. I find that Insomnia has a cleaner interface than Postman and is easier to use for simple API calls, I know, probably an unpopular opinion. Anyway...to get started you have to: - Create a [Spotify Developer account](https://developer.spotify.com/) and register an application. - Obtain necessary token to make API calls. I don't cover that here, the steps for obtaining a Spotify auth token can be found [HERE](https://developer.spotify.com/documentation/web-api/tutorials/code-flow). - Call the Spotify `v1/me/player/recently-played` API using Insomnia.
``` ##Request #canCollapse curl --request GET \ --url 'https://api.spotify.com/v1/me/player/recently-played?limit=50' \ --header 'Authorization: Bearer token' \ --header 'Content-Type: application/json' ```
``` ##Response #canCollapse #language-json { "played_at": "2025-08-29T00:00:27.043Z", "context": { "type": "playlist", "external_urls": { "spotify": "https://open.spotify.com/playlist/4gowSSvFaSz8Auzizrjkyw" }, "href": "https://api.spotify.com/v1/playlists/4gowSSvFaSz8Auzizrjkyw", "uri": "spotify:playlist:4gowSSvFaSz8Auzizrjkyw" }, "track": { "album": { "album_type": "album", "artists": [ { "external_urls": { "spotify": "https://open.spotify.com/artist/378dH6EszOLFShpRzAQkVM" }, "href": "https://api.spotify.com/v1/artists/378dH6EszOLFShpRzAQkVM", "id": "378dH6EszOLFShpRzAQkVM", "name": "Lindsey Stirling", "type": "artist", "uri": "spotify:artist:378dH6EszOLFShpRzAQkVM" } ], "available_markets": [ "AR", "AU", "..." ], "external_urls": { "spotify": "https://open.spotify.com/album/5EH0A5mhsGNCOPPpvi3RfF" }, "href": "https://api.spotify.com/v1/albums/5EH0A5mhsGNCOPPpvi3RfF", "id": "5EH0A5mhsGNCOPPpvi3RfF", "images": [ { "url": "https://i.scdn.co/image/ab67616d0000b273af2acf076c7856bfe6ef580e", "width": 640, "height": 640 }, { "url": "https://i.scdn.co/image/ab67616d00001e02af2acf076c7856bfe6ef580e", "width": 300, "height": 300 }, { "url": "https://i.scdn.co/image/ab67616d00004851af2acf076c7856bfe6ef580e", "width": 64, "height": 64 } ], "name": "Brave Enough", "release_date": "2016-08-19", "release_date_precision": "day", "total_tracks": 14, "type": "album", "uri": "spotify:album:5EH0A5mhsGNCOPPpvi3RfF" }, "artists": [ { "external_urls": { "spotify": "https://open.spotify.com/artist/378dH6EszOLFShpRzAQkVM" }, "href": "https://api.spotify.com/v1/artists/378dH6EszOLFShpRzAQkVM", "id": "378dH6EszOLFShpRzAQkVM", "name": "Lindsey Stirling", "type": "artist", "uri": "spotify:artist:378dH6EszOLFShpRzAQkVM" }, { "external_urls": { "spotify": "https://open.spotify.com/artist/31rVRoX5ZG9ZyRbHvlEwjA" }, "href": "https://api.spotify.com/v1/artists/31rVRoX5ZG9ZyRbHvlEwjA", "id": "31rVRoX5ZG9ZyRbHvlEwjA", "name": "RuthAnne", "type": "artist", "uri": "spotify:artist:31rVRoX5ZG9ZyRbHvlEwjA" } ], "available_markets": [ "AR", "AU", "..." ], "disc_number": 1, "duration_ms": 229120, "explicit": false, "external_ids": { "isrc": "TCACO1625907" }, "external_urls": { "spotify": "https://open.spotify.com/track/0uFwdpkVJzpyUxlu9reTWK" }, "href": "https://api.spotify.com/v1/tracks/0uFwdpkVJzpyUxlu9reTWK", "id": "0uFwdpkVJzpyUxlu9reTWK", "is_local": false, "name": "Love's Just a Feeling (feat. Rooty)", "popularity": 39, "preview_url": null, "track_number": 12, "type": "track", "uri": "spotify:track:0uFwdpkVJzpyUxlu9reTWK" } } ```
---
## Initial Database Setup This allowed me to jump into the design of the database schema. Keeping it simple initially, I started with unique tables for tracks, artists, albums, and play history. I stripped away any fields I didn't want to use. - `sptrack` - Table to store details about each Spotify track that I have listened to. - `spartist` - Table to store details about each Spotify artist that I have listened to. - `spalbum` - Table to store details about each Spotify album that I have listened to. - `spplayhistory` - Table to store details about my Spotify play history. I included some very simple relationships defined between the tables. While this was a good start, I knew that I would need to iterate on this design a great deal before it could would be a performant and efficient way to store and retrieve my listening history.

Spotify Play History API is limited to the last 50 tracks played. On request Spotify will send you all of your data with a notice that it may take up to 30 days to process.

I submitted a request for my data and, despite the 30 day notice, I received an email with a link to download it within a week. The data included 4 JSON files containing roughly 2 years of my Spotify listening history each. - The files were ~47MB and contained just over 60,000 records. - I anticipated that data would be in a similar format to the Spotify API, but it was different and missing a lot of details. ``` ##Bulk Play History Sample #canCollapse { "ts": "2017-07-27T00:56:06Z", "platform": "Android OS 7.0 API 24 (LGE, LGLS992)", "ms_played": 75338, "conn_country": "US", "ip_addr": "73.181.8.221", "master_metadata_track_name": "Love's Just a Feeling (feat. Rooty)", "master_metadata_album_artist_name": "Lindsey Stirling", "master_metadata_album_album_name": "Brave Enough", "spotify_track_uri": "spotify:track:0uFwdpkVJzpyUxlu9reTWK", "episode_name": null, "episode_show_name": null, "spotify_episode_uri": null, "audiobook_title": null, "audiobook_uri": null, "audiobook_chapter_uri": null, "audiobook_chapter_title": null, "reason_start": "clickrow", "reason_end": "endplay", "shuffle": false, "skipped": false, "offline": false, "offline_timestamp": null, "incognito_mode": false } ```
---
# Preparing Historical Data The next series of steps involved running a handful of one off node and bash scripts to **a)** transform the historical data, **b)** extract a list of track id's, and **c)** use those track id's to enrich that data with the missing details.
---
## Transforming the Data For the transformation, I used array destructuring `items.map(item => {})` and the `...rest` operator to remove unnecessary key value pairs from the bulk data set. The output matched the data form which would be returned from the `/v1/me/player/recently-played` endpoint. I dumped the results out to a new JSON file, `ProcessedSpotifyHistory.json`.

The functions `removeUnwantedPropertiesFromHistory()` and `transformHistoryData(history)` in the code snippet below could be wrapped into one function. I was in a flow state and knew this would only need to run once, so I just kept flowing.

``` ## Data Transformation Script #canCollapse // Import Spotify history data files (JSON format) import History1 from './History1.json' with { type: 'json' }; import History2 from './History2.json' with { type: 'json' }; import History3 from './History3.json' with { type: 'json' }; import History4 from './History4.json' with { type: 'json' }; // Node.js modules for file system and path operations import fs from 'fs'; import path from 'path'; /* // Removes extraneous metadata from each history item. // Keeps only the properties relevant for downstream processing. */ function removeUnwantedPropertiesFromHistory(history) { return history.map(item => { const { platform, ms_played, conn_country, ip_addr, master_metadata_album_artist_name, episode_name, episode_show_name, spotify_episode_uri, shuffle, audiobook_title, audiobook_uri, audiobook_chapter_uri, audiobook_chapter_title, reason_start, reason_end, skipped, offline, offline_timestamp, incognito_mode, ...rest // Retain only the remaining useful fields } = item; return rest; }); } /* // Transforms history items into a cleaner, more readable format. // Adds derived fields and removes redundant metadata. */ function transformHistoryData(history) { return history.map(item => { item.played_at = item.ts; // Rename timestamp for clarity item.album = { name: item.master_metadata_album_album_name || 'Unknown Album' }; item.title = item.master_metadata_track_name; item.songUrl = `https://open.spotify.com/track/${item.spotify_track_uri?.split(':').pop()}`; // Remove original metadata fields after transformation delete item.ts; delete item.master_metadata_album_album_name; delete item.master_metadata_track_name; delete item.spotify_track_uri; return item; }); } /* // Main function to process all history files and save the output. // Combines, cleans, transforms, and writes to a new JSON file. */ async function processAndSaveHistory() { // Merge all history files into a single array const allHistory = [...History1, ...History2, ...History3, ...History4]; // Step 1: Remove unnecessary fields const cleanedHistory = removeUnwantedPropertiesFromHistory(allHistory); // Step 2: Transform data into final format const transformedHistory = transformHistoryData(cleanedHistory); // Handle empty dataset edge case if (transformedHistory.length === 0) { console.warn('⚠️ No records to write. Check input files.'); return; } // Define output path and write processed data to disk const directory = process.cwd(); const outputPath = path.join(directory, 'ProcessedSpotifyHistory.json'); fs.writeFileSync(outputPath, JSON.stringify(transformedHistory, null, 2)); console.log(`✅ Processed history saved to ${outputPath}`); } // Execute the processing pipeline and handle any errors processAndSaveHistory().catch(console.error); ``` ``` ## Bulk Play History Transformed - ProcessedSpotifyHistory.json #canCollapse #language-json { "id": "0uFwdpkVJzpyUxlu9reTWK", "played_at": "2017-07-27T00:57:32Z", "album": { "name": "Brave Enough" }, "title": "Love's Just a Feeling (feat. Rooty)", "songUrl": "https://open.spotify.com/track/0uFwdpkVJzpyUxlu9reTWK" } ```

- A single track can only be on a single album. However, a single track or a single album can have multiple artists. - The bulk data did not include an id for the track played, it was embedded in the `spotify_track_uri` however. - The track_id was also used to compose the the `"songUrl"` key's value.

Before I could load the data into the database I had to acquire the missing track, album, and artist details. **YAY!** Spotify has `/v1/artists` and `/v1/tracks` endpoints for bulk data retriaval. **UGGH!** You can only request 50 artist or track details at a time.
---
## Extracting the Track Id's - Extract each track id from the end of every songUrl in `ProcessedSpotifyHistory.json`. - Write every set of 50 track id's to a file as a JSON array, `SpotifyTrackIdList${count}.json`. This resulted in 1224 files, all but the last file containing 50 track id's! ``` ## Extraction Script #canCollapse // Imports Node.js modules for file system and path operations. import fs from 'fs'; import path from 'path'; // Capture the start time of execution for potential benchmarking or logging. var startTime = new Date(); /* // Extracts Spotify track IDs from a history array and writes them to chunked JSON files. // Each chunk contains up to 50 records, and a new file is created per chunk. */ function getTrackIdListFromHistory(history: any[]) { const trackIdSet = new Set(); const directory = process.cwd(); var chunkCount = 1; while (history.length > 0) { // Take the first 50 records from the history array const chunk = history.splice(0, 50); // Extract track IDs from each item in the chunk chunk.forEach(item => { if (item.songUrl) { const trackId = item.songUrl.split('/').pop(); if (trackId) { trackIdSet.add(trackId); } } }); // Write the current chunk of track IDs to a JSON file const outputPath = path.join( directory, `tools/trackHistoryList/spotifyTrackIdList${chunkCount}.json` ); fs.writeFileSync(outputPath, JSON.stringify(Array.from(trackIdSet), null, 2)); log(`Track ID list #${chunkCount} saved to ${outputPath}`); // Clear the set for the next chunk trackIdSet.clear(); chunkCount++; } // Return the final (empty) set as an array—likely unused but included for completeness return Array.from(trackIdSet); } ```
---
## Enriching the Data Enriching the data would require that I call the Spotify API over 1200 times! - Open each `spotifyTrackIdList${count}.json` file. - Make REST API calls to Spotify's `/v1/tracks?${trackIdList}` endpoint to acquire the additional details for every track. - Write the returned JSON data to file.

- Use `setTimeout` to space out the API calls to avoid hitting rate limits. - Monitor the token expiration time and refresh it as needed. - Log progress after each chunk is processed to troubleshoot edge cases.

``` ## Data Enrichment Script #canCollapse import fs from 'fs'; import path from 'path'; /* // Fetches detailed track metadata from Spotify's API using a list of track IDs. // Each chunk corresponds to a JSON file containing up to 50 track IDs. */ async function getTrackDetailFromSpotifyTrackIdList(chunkNumber: number, access_token: any) { const inputPath = path.join(directory, `tools/trackHistoryList/spotifyTrackIdList${chunkNumber}.json`); // Exit early if the input file doesn't exist if (!fs.existsSync(inputPath)) { log(`Input file not found: ${inputPath}`); return; } const trackIdList = JSON.parse(fs.readFileSync(inputPath, 'utf-8')); // Exit early if the input file is empty if (trackIdList.length === 0) { log('No track IDs found in the input file.'); return; } const chunkSize = 50; // Spotify API allows up to 50 IDs per request const trackDetails: any[] = []; // Process track IDs in batches of 50 for (let i = 0; i < trackIdList.length; i += chunkSize) { const chunk = trackIdList.slice(i, i + chunkSize); const idsParam = chunk.join(','); try { const response = await fetch( `https://api.spotify.com/v1/tracks?ids=${idsParam}`, { headers: { Authorization: `Bearer ${access_token}` } } ); // Log and skip if the response is not successful if (!response.ok) { const responseJson = await response.json(); log(`Error fetching track details: ${response.status} ${response.statusText}`); log(`Response: ${responseJson}`); continue; } const data = await response.json(); trackDetails.push(...data.tracks); } catch (error) { log(`Fetch error: ${JSON.stringify(error)}`); } } // Write the fetched track details to disk const outputPath = path.join(directory, `tools/trackHistoryData/spotifyTrackDetails${chunkNumber}.json`); fs.writeFileSync(outputPath, JSON.stringify(trackDetails, null, 2)); log(`Track details chunk #${chunkNumber} saved to ${outputPath}`); } /* * Iterates through all chunk files and fetches track details for each. * Refreshes the access token if the elapsed time exceeds a threshold. */ async function getTrackDetailsInChunks() { for (let chunkNumber = 1; chunkNumber <= 1224; chunkNumber++) { getTrackDetailFromSpotifyTrackIdList(chunkNumber, access_token); const endtime = new Date(); if ((endtime.getMilliseconds() - startTime.getMilliseconds()) > 3300000) { access_token = getAccessToken(); } // Wait 10 seconds between requests to avoid rate limits await new Promise(resolve => setTimeout(resolve, 10000)); } } // Joins all individual track detail files into a single consolidated JSON file. async function joinFiles() { let allTrackDetails = []; for (let i = 1; i <= 1224; i++) { const inputPath = path.join(process.cwd(), `tools/trackHistoryData/spotifyTrackDetails${i}.json`); if (!fs.existsSync(inputPath)) { console.log(`File ${inputPath} does not exist, skipping...`); continue; } const fileData = JSON.parse(fs.readFileSync(inputPath, 'utf-8')); allTrackDetails = allTrackDetails.concat(fileData); console.log(`File #${i} processed, total tracks so far: ${allTrackDetails.length}`); } const outputPath = path.join(process.cwd(), `tools/trackHistoryData/finalSpotifyTrackDetails.json`); fs.writeFileSync(outputPath, JSON.stringify(allTrackDetails, null, 2)); console.log(`All files joined. Total tracks: ${allTrackDetails.length}`); } // Entry point: fetch all track details and join them into one file. (async () => { await getTrackDetailsInChunks(); await joinFiles(); log('All chunks processed and files joined.'); })(); ```
---
## Merging the fetched and transformed data - Using the command line tool, jq, I added details to the `ProcessedSpotifyHistory.json` for every played song. - Essentially this adds one more nested level of JSON for the track information with the `played_at` key value at the root. - Which would look like this: `{ "played_at": "2017-07-27T00:56:06Z", "track": {...} }, { "played_at": "2017-07-27T00:57:32Z", "track": {...} }` - While this, likely unnecessarily, duplicated a lot of data...I knew that it would be in the same format that future calls to the Spotify API would returns. ``` ## Merge Track Details and Play History #canCollapse jq -s ' (.[1] | map({ (.id): . }) | add) as $trackDetails | .[0] | map( ( .track_id = (.songUrl | split("/track/")[1] | split("?")[0]) | .detail = $trackDetails[.track_id] | if .detail != null then { "played_at": .played_at, "track": { "id": .track_id, "name": .detail.name, "external_ids": { "isrc": .detail.external_ids.isrc }, "duration_ms": .detail.duration_ms, "explicit": .detail.explicit, "external_urls": { "spotify": .detail.external_urls.spotify }, "album": { "id": .detail.album.id, "name": .detail.album.name, "release_date": .detail.album.release_date, "images": (.detail.album.images // null), "artists": ( .detail.album.artists // [] | map({ "id": .id, "name": .name, "external_urls": { "spotify": .external_urls.spotify } }) ) }, "artists": ( .detail.artists | map({ "id": .id, "name": .name, "external_urls": { "spotify": .external_urls.spotify } }) ) } } else empty end ) ) ' "ProcessedSpotifyHistory.json" "finalSpotifyTrackDetails.json" > "merged_spotifyTrackDetails.json" echo "✅ Merged chunk" ```
---
# Database Design Now that I had prepared all of my historical data and created a process for ingesting it, it was time to fully build out the database schema.
---
## Defining Table Relationships Design Considerations: - **A single track can have multiple artists** (features, collaborations) - **An album can have multiple artists** (compilations, various artists albums) - **Determine an efficient way to store the data for use in the heatmap** - **Not every album had an image url** - **Tracks may be on different albums with different images** (or so I thought, see Feature Enhancement section for more detail) I began by creating many-to-many relationships between tracks and artists, and albums and artists. Those relationships are managed via the `sptrack_artists` and `spalbum_artists` junction tables. The initial scope for this project was to simply track listening frequency. Counting hourly plays every time, client side, would be incredibly inefficient so a table with pre-counted hourly plays made sense. The creation of `spdailyplaystats` served that purpose. An important note about that table is that `hourly_plays` column is a Json array of 24 numbers for each hour of the day. This one table alone is enough to create an overall listening heatmap. Just grab each row in that table for the date range you want and your good to go. That table was not enough to get a list of the most listened to artists or tracks for the given time frame however. An overall listening heatmap wasn't good enough for me, I wanted to do more, I wanted to include heatmaps for specific artists or tracks also. The scale and complexity to do that became a headscratcher. Also, added common_album and common_artist tables to store information about the album that should be visually presented when displaying track information.

---
## Performance and Realizing the Algorithm I was asking AI all kinds of questions to help. It would throw out different code suggestions, table additions, indexing options, blah blah blah. None of them made any sense or really worked but I knew there had to be someway to do this efficiently. It felt obvious that data had to be precounted, but how....and that is when it hit me. Create different size "buckets" with precounted data for every artist and track. For every year, month, and day, I can precount plays for every individual artist and every individual track I have ever listened to. That way, when retreiving data real-time, I could grab the biggest buckets first, followed by the next biggest, then only count the buckets for each day on the edges of the date range. Bucket Visualization

Unbeknownst to me at the time, this is a common technique used in data warehousing and OLAP (Online Analytical Processing) systems. It is often referred to as "pre-aggregation" or "materialized views." The idea is to precompute and store aggregated data at different levels of granularity to optimize query performance. I have to admit, I'm impressed with myself when I create a complex solution that already exists and is widely used.

Below is the final DB schema. The `artiststat` and `trackstat` tables represented precounted stats for each artist and track for any given yearly, monthly, or daily bucket. The `yearlybucket`, `monthlybucket`, and `daybucket` tables containing. The `artiststat` and `trackstat` associated with a day bucket then included `hourly_plays.` Ideally, after using the largest possible buckets to determine the top artists and tracks I could then use the power of relational databases to quickly fetch all of the hourly plays for a specific artist or track.

---
# Data Upkeep and Aggregation This process took a lot of trial and error. I wiped tables regularly and started over more times than I would like to admit. The database structure described was derived after a significant number of iterations attempting to load this data into the database. I started with defining what my source of truth was then building outward from there.
---
## The Source of Truth Spotify's recently played endpoint, `v1/me/player/recently-played`, provided all the data that I needed and I had already transformed all of my historical data to match what was returned from that endpoint. That data can be fed directly into the `spplayhistory` table. For new artists, tracks, or albums, I could create new records as needed after storing the data in `spplayhistory`. I also knew that I could only fetch the last 50 played tracks....I often listen to more than 50 songs in a single day. It was critical that I started doing this immediately and not to interfere with it once it was in place. As long as `spplayhistory` was regularly populated, the rest could be iteratively solved. - `getRecentlyPlayedFromSpotify()` fetches my recent play history from Spotify. - `ingestSpotifyPlays()` uses the data returned from `getRecentlyPlayedFromSpotify` For now I will say that I quickly set up a service so that these functions ran every 30 minutes. I will describe how I did that in a later section. ``` ##Fetching recently played from Spotify #canCollapse /* * Fetches recently played tracks from the Spotify API. * Uses the timestamp of the last synced track to avoid duplicate ingestion. */ export const getRecentlyPlayedFromSpotify = async (): Promise => { try { // Retrieve a fresh access token const { access_token } = await getAccessToken(); // Get the timestamp of the most recently played track in the database const lastSync = await prisma.spplayhistory.findFirst({ orderBy: { played_at: 'desc' }, select: { played_at: true } }); // Convert the timestamp to milliseconds for the Spotify API const afterTimestamp = lastSync?.played_at instanceof Date ? lastSync.played_at.getTime() : 0; // Construct the API URL with the 'after' parameter to fetch only new plays let url = `${RECENTLY_PLAYED_ENDPOINT}&after=${afterTimestamp}`; // Make the request to Spotify's recently played endpoint const response = await axios.get(url, { headers: { Authorization: `Bearer ${access_token}` } }); // Handle cases where no new tracks are available or an error occurred if ( response.status === 204 || response.status > 400 || Array.isArray(response.data) || !response.data.items ) { console.warn('Recently played tracks empty or missing, error status:', response.status); return { status: response.status, data: [] }; } // Return raw Spotify items if successful return { status: 200, data: response.data.items }; } catch (error) { // Log and return error response console.error('Error fetching recently played tracks:', error); return { status: 500, data: [] }; } }; ```
``` ##Formatting and ingesting the returned Spotify plays. #canCollapse /* // Ingests recently played Spotify tracks and updates the database. // Performs upserts for albums, tracks, artists, relationships, and play history. // Also triggers downstream updates like artist images and daily stats. */ export const ingestSpotifyPlays = async (): Promise => { const response = await getRecentlyPlayedFromSpotify(); if (response.status !== 200 || !response.data) { console.error('Failed to fetch recently played tracks from Spotify'); return; } const items = response.data; console.log(`Processing ${items.length} plays`); // Prepare batch operations const albumUpserts: any[] = []; const trackUpserts: any[] = []; const artistUpserts: any[] = []; const playHistoryUpserts: any[] = []; const trackArtistRelations: any[] = []; const albumArtistRelations: any[] = []; // Collect all unique entities const allArtistIds = new Set(); for (const item of items as any[]) { const { track, played_at } = item; const isrc = track.external_ids?.isrc; // Collect album data albumUpserts.push({ where: { album_id: track.album.id }, update: { name: track.album.name, image_url: track.album.images[0]?.url, release_date: new Date(track.album.release_date) }, create: { album_id: track.album.id, name: track.album.name, image_url: track.album.images[0]?.url, release_date: new Date(track.album.release_date) } }); // Collect track data trackUpserts.push({ where: { track_id: track.id }, update: { title: track.name, isrc, album_id: track.album.id, explicit: track.explicit, song_url: track.external_urls.spotify, duration: Math.floor(track.duration_ms / 1000), release_date: new Date(track.album.release_date) }, create: { track_id: track.id, title: track.name, isrc, album_id: track.album.id, explicit: track.explicit, song_url: track.external_urls.spotify, duration: Math.floor(track.duration_ms / 1000), release_date: new Date(track.album.release_date) } }); // Collect artist data and relationships for (const artist of [...track.artists, ...track.album.artists]) { allArtistIds.add(artist.id); artistUpserts.push({ where: { artist_id: artist.id }, update: { name: artist.name, artist_url: artist.external_urls.spotify }, create: { artist_id: artist.id, name: artist.name, artist_url: artist.external_urls.spotify } }); } // Collect track-artist relationships for (const artist of track.artists) { trackArtistRelations.push({ where: { track_id_artist_id: { track_id: track.id, artist_id: artist.id } }, update: {}, create: { track_id: track.id, artist_id: artist.id } }); } // Collect album-artist relationships for (const artist of track.album.artists) { albumArtistRelations.push({ where: { album_id_artist_id: { album_id: track.album.id, artist_id: artist.id } }, update: {}, create: { album_id: track.album.id, artist_id: artist.id } }); } // Collect play history playHistoryUpserts.push({ where: { track_id_played_at: { track_id: track.id, played_at: new Date(played_at) } }, update: {}, create: { track_id: track.id, played_at: new Date(played_at) } }); } // Execute all operations in a transaction for better performance await prisma.$transaction(async (tx) => { const batchSize = 50; // Albums for (let i = 0; i < albumUpserts.length; i += batchSize) { const batch = albumUpserts.slice(i, i + batchSize); await Promise.all(batch.map(op => tx.spalbum.upsert(op))); } // Artists for (let i = 0; i < artistUpserts.length; i += batchSize) { const batch = artistUpserts.slice(i, i + batchSize); await Promise.all(batch.map(op => tx.spartist.upsert(op))); } // Tracks for (let i = 0; i < trackUpserts.length; i += batchSize) { const batch = trackUpserts.slice(i, i + batchSize); await Promise.all(batch.map(op => tx.sptrack.upsert(op))); } // Relationships and play history await Promise.all([ ...trackArtistRelations.map(op => tx.sptrackartist.upsert(op)), ...albumArtistRelations.map(op => tx.spalbumartist.upsert(op)), ...playHistoryUpserts.map(op => tx.spplayhistory.upsert(op)) ]); }); // Fetch artist images and update common albums await Promise.all([ updateArtistImages(Array.from(allArtistIds)), assignCommonAlbumUrls() ]); // Aggregate daily stats for the unique dates in the ingested plays const uniqueDates = new Set(); for (const item of items as any[]) { const playDate = new Date(item.played_at); const dateStr = playDate.toISOString().split('T')[0]; uniqueDates.add(dateStr); } for (const dateStr of uniqueDates) { const targetDate = new Date(`${dateStr}T00:00:00.000Z`); await aggregateDailyStats(targetDate); } console.log(`✅ Ingested ${items.length} plays and updated stats for ${uniqueDates.size} unique dates`); }; ```
---
## Initial Table Population Now I had a strong process for populating `spplayhistory`, `spalbum`, `sptrack`, `spartist`, `spalbumartist`, and `sptrackartist` in the database. I still had to take the previously merged data to push the last 8 years worth of listening history into the database. That was done with the following function with `pushPlayDetailsToDB()`.

- I should have used `setTimeout` to space out the database operations on my super tiny DB...but I just let it rip. - Monitor the token expiration time and refresh it as needed. - Log progress after each chunk is processed to troubleshoot edge cases.

``` ##Pushing Historical Play Details to DB #canCollapse /* // Pushes detailed Spotify track data into the database in manageable chunks. // Reads from the final consolidated track details file and ingests each chunk sequentially. */ async function pushPlayDetailsToDB() { const inputPath = path.join(directory, `tools/trackHistoryData/finalSpotifyTrackDetails.json`); const trackDetails = await JSON.parse(fs.readFileSync(inputPath, 'utf-8')); const CHUNK_SIZE = 400; let cursor = 0; let chunkNumber = 1; while (cursor < trackDetails.length) { // Slice out the next chunk of track data const dataChunk = trackDetails.slice(cursor, cursor + CHUNK_SIZE); log(`Ingesting chunk #${chunkNumber} with ${dataChunk.length} tracks @ ${new Date().toISOString()}`); // Ingest the current chunk into the database, throw an error if it fails try { await ingestSpotifyPlays(true, { status: 200, data: dataChunk }); } catch (error) { log(`Ingestion error in file chunk #${chunkNumber}: ${JSON.stringify(error)}`); } cursor += CHUNK_SIZE; chunkNumber++; } log('All files processed.'); } ```
While that created and ensured that my "source of truth" pipeline was operating, I still needed to prepare the `yearlybucket`, `monthlybucket`, `daybucket` tables. Each yearbucket can only reference 12 month buckets, one month bucket can only reference 28-31 day buckets, and there could be no overlap or duplicate records. I chose to precreated these buckets starting at my first play record through then end of 2030. I messed this up so bad and so many times. So much in fact that I had a process for starting over. The following functions created were used to populate the bucket tables: - `purgebuckets()` My start over function. - `buildAllBuckets()` Creates entries in the database for every possible year, month, and day bucket from 07/23/2017 to 12/31/2030. - `linkBucketHierarchy()` Establishes the relations and table constraints between each of the day, month, and year buckets. - `validateBuckets()` This checks for duplicate entries, missing links, incomplete buckets, etc.
``` ##Bucket Creation Functions #canCollapse /* // Deletes all existing stats and bucket tables from the database. // Useful for resetting the ingestion pipeline before a full rebuild. */ export async function purgeBuckets() { const logPath = path.join(process.cwd(), 'ingestion.log'); log('Purging all stats and bucket tables...', logPath); await prisma.artiststat.deleteMany({}); await prisma.trackstat.deleteMany({}); await prisma.daybucket.deleteMany({}); await prisma.monthbucket.deleteMany({}); await prisma.yearbucket.deleteMany({}); log('All stats and bucket tables purged.', logPath); } /* // Rebuilds year, month, and day bucket tables based on play history. // Uses the first play event as the start date and builds buckets through 2030. */ export async function buildAllBuckets() { const logPath = path.join(process.cwd(), 'ingestion.log'); // Use first play event as start date let start = new Date('2017-07-23T00:00:00.000Z'); const firstPlay = await prisma.spplayhistory.findFirst({ orderBy: { played_at: 'asc' } }); if (firstPlay?.played_at) { const d = new Date(firstPlay.played_at); start = new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate())); } const end = new Date('2030-12-31T00:00:00.000Z'); log('Rebuilding all bucket tables...', logPath); // Year buckets const yearStarts = eachYearOfInterval({ start, end }); const yearBucketInputs = yearStarts.map(yearStart => { const year = yearStart.getUTCFullYear(); const range_start = new Date(Date.UTC(year, 0, 1)); const range_end = new Date(Date.UTC(year, 11, 31, 23, 59, 59, 999)); return { year, range_start, range_end }; }); await prisma.yearbucket.createMany({ data: yearBucketInputs, skipDuplicates: true }); log(`Created/confirmed ${yearBucketInputs.length} yearbucket rows`, logPath); const yearRows = await prisma.yearbucket.findMany(); const yearLookup = new Map(yearRows.map(y => [y.year, y.id])); // Month buckets const monthStarts = eachMonthOfInterval({ start, end }); const monthBucketInputs = []; for (const monthStart of monthStarts) { const year = monthStart.getUTCFullYear(); const month = monthStart.getUTCMonth() + 1; const range_start = new Date(Date.UTC(year, month - 1, 1)); const range_end = new Date(Date.UTC(year, month, 0, 23, 59, 59, 999)); const yearbucketid = yearLookup.get(year); if (!yearbucketid) { log(`Skipping monthbucket for ${year}-${month}: no yearbucket found`, logPath); continue; } monthBucketInputs.push({ yearbucketid, month, range_start, range_end }); } await prisma.monthbucket.createMany({ data: monthBucketInputs, skipDuplicates: true }); log(`Created/confirmed ${monthBucketInputs.length} monthbucket rows`, logPath); const monthRows = await prisma.monthbucket.findMany(); const monthLookup = new Map(monthRows.map(m => [`${m.yearbucketid}-${m.month}`, m.id])); // Day buckets const dayStarts = eachDayOfInterval({ start, end }); const monthRowsForDays = await prisma.monthbucket.findMany(); const monthLookupForDays = new Map(monthRowsForDays.map(m => [`${m.yearbucketid}-${m.month}`, m.id])); const dayBucketInputs = dayStarts.map(date => { const year = date.getUTCFullYear(); const month = date.getUTCMonth() + 1; const yearbucketid = yearLookup.get(year); const monthbucketid = monthLookupForDays.get(`${yearbucketid}-${month}`); return { start_date: startOfDay(date), bucket_scope: 'day', monthbucketid: monthbucketid!, }; }); await prisma.daybucket.createMany({ data: dayBucketInputs, skipDuplicates: true }); log(`Created/confirmed ${dayBucketInputs.length} daybucket rows`, logPath); log('All bucket tables rebuilt.', logPath); } /* // Links monthbucket rows to their corresponding yearbucket parents. // Ensures hierarchical integrity between bucket tables. */ export async function linkBucketHierarchy() { const logPath = path.join(process.cwd(), 'ingestion.log'); log('Linking bucket hierarchy...', logPath); const yearBuckets = await prisma.yearbucket.findMany(); const monthBuckets = await prisma.monthbucket.findMany(); for (const month of monthBuckets) { const parent = yearBuckets.find(y => month.range_start >= y.range_start && month.range_end <= y.range_end ); if (parent && month.yearbucketid !== parent.id) { await prisma.monthbucket.update({ where: { id: month.id }, data: { yearbucketid: parent.id }, }); log(`monthbucket ${month.id} linked to yearbucket ${parent.id}`, logPath); } } log('Bucket hierarchy linked via range containment', logPath); } /* // Validates bucket date ranges and parent-child relationships. // Logs any inconsistencies or out-of-range entries. */ export async function validateBuckets() { const logPath = path.join(process.cwd(), 'ingestion.log'); log('Validating bucket hierarchy and date ranges...', logPath); const validStart = new Date('2017-07-23T00:00:00.000Z'); const validEnd = new Date('2030-12-31T23:59:59.999Z'); let issues = 0; const yearBuckets = await prisma.yearbucket.findMany(); for (const year of yearBuckets) { if (year.range_start < validStart || year.range_end > validEnd) { log(`yearbucket ${year.id} out of range: ${year.range_start.toISOString()} - ${year.range_end.toISOString()}`); issues++; } } const monthBuckets = await prisma.monthbucket.findMany(); for (const month of monthBuckets) { if (!month.yearbucketid) { log(`monthbucket ${month.id} missing yearbucketid`, logPath); issues++; } if (month.range_start < validStart || month.range_end > validEnd) { log(`monthbucket ${month.id} out of range: ${month.range_start.toISOString()} - ${month.range_end.toISOString()}`, logPath); issues++; } } const dayBuckets = await prisma.daybucket.findMany(); for (const day of dayBuckets) { if (day.start_date < validStart || day.start_date > validEnd) { log(`daybucket ${day.id} out of range: ${day.start_date.toISOString()}`, logPath); issues++; } } if (issues === 0) { log('All bucket hierarchy links are valid.', logPath); } else { log(`Validation complete: ${issues} issues found.`, logPath); } } ```
---
## Aggregation As plays were ingested I had to aggregate total play counts for all appropriate buckets, creating new `artiststat` and `trackstat` records. For example, if I fetched music I listened to on 9/28/2025 I'd need to update the yearly bucket for 2025, the monthly bucket for September of 2025, and the daily bucket for 9/28/2025. Updating those buckets would include creating (or updating) artiststat and trackstat records for every artist and track associated with those buckets. There were two steps to this, aggregating historical data and aggregating new data. I could restart this process as much as I needed because the source of truth, `spplayhistory`, was accurate and being updated regularly every 30 minutes. - `updateBucketedStats(dryRun: boolean = false)` This function would go through the entire `spplayhistory` table and create aggregate counts for every bucket, every artist, and every track. I used a dryRun flag so that I could log random items as it went to validate what was happening before I actually added a bunch of data to the database. - `aggregateDailyStats(targetDate: Date)` This function aggregates data for a specific date. In my case I call it every 6 hours for the last three days. I chose three days to account for possible errors during previous runs, possible time zone issues, and if I realized during development I had to aggregate differently.
``` ##aggregateDailyStats(targetDate:) #canCollapse /* // Aggregates daily play statistics for a given target date. // Computes hourly play counts, top tracks and artists, and stores results in bucketed tables. */ export const aggregateDailyStats = async (targetDate: Date): Promise => { const dateStr = targetDate.toISOString().split('T')[0]; const start = new Date(`${dateStr}T00:00:00.000Z`); const end = new Date(`${dateStr}T23:59:59.999Z`); const weekday = start.toLocaleDateString('en-US', { weekday: 'short' }); // Delete existing daily play stats for this date await prisma.spdailyplaystats.deleteMany({ where: { date: start } }); // Fetch play history for the target date, including track and artist relationships. const plays = await prisma.spplayhistory.findMany({ where: { played_at: { gte: start, lte: end } }, include: { track: { include: { track_artists: true } } } }); // Initialize counters and hourly maps const hourlyPlays = Array(24).fill(0); const trackCounts = new Map(); const artistCounts = new Map(); const trackHourlyMap = new Map(); const artistHourlyMap = new Map(); // Tally plays by hour, track, and artist. for (const play of plays) { const hour = new Date(play.played_at).getUTCHours(); hourlyPlays[hour]++; const trackId = play.track_id; trackCounts.set(trackId, (trackCounts.get(trackId) || 0) + 1); if (!trackHourlyMap.has(trackId)) trackHourlyMap.set(trackId, Array(24).fill(0)); trackHourlyMap.get(trackId)![hour]++; for (const artist of play.track.track_artists) { const artistId = artist.artist_id; artistCounts.set(artistId, (artistCounts.get(artistId) || 0) + 1); if (!artistHourlyMap.has(artistId)) artistHourlyMap.set(artistId, Array(24).fill(0)); artistHourlyMap.get(artistId)![hour]++; } } // Store overall daily play stats. await prisma.spdailyplaystats.create({ data: { date: start, weekday, hourly_plays: hourlyPlays } }); console.log(`Overall stats created for ${dateStr}`); /* // Find the corresponding daybucket for this date. // Daybuckets should be pre-created up to 2030. */ const dayBucket = await prisma.daybucket.findFirst({ where: { start_date: { gte: new Date(`${dateStr}T00:00:00.000Z`), lt: new Date(`${dateStr}T23:59:59.999Z`) } } }); if (!dayBucket) { throw new Error(`Day bucket not found for ${dateStr}. Daybuckets should be pre-created up to 2030.`); } // Upsert artist-level stats for the day. for (const [artist_id, count] of artistCounts.entries()) { await prisma.artiststat.upsert({ where: { artist_id_stat_date: { artist_id, stat_date: start } }, update: { count, hourly_plays: artistHourlyMap.get(artist_id), daybucketid: dayBucket.id, bucket_scope: 'day' }, create: { artist_id, count, hourly_plays: artistHourlyMap.get(artist_id), stat_date: start, daybucketid: dayBucket.id, bucket_scope: 'day' } }); } // Upsert track-level stats for the day. for (const [track_id, count] of trackCounts.entries()) { await prisma.trackstat.upsert({ where: { track_id_stat_date: { track_id, stat_date: start } }, update: { count, hourly_plays: trackHourlyMap.get(track_id), daybucketid: dayBucket.id, bucket_scope: 'day' }, create: { track_id, count, hourly_plays: trackHourlyMap.get(track_id), stat_date: start, daybucketid: dayBucket.id, bucket_scope: 'day' } }); } console.log(`Bucketed stats updated for ${dateStr} (daybucket: ${dayBucket.id})`); }; ```
``` ##updateBucketedStats(dryRun: boolean = false) #canCollapse export async function updateBucketedStats(dryRun: boolean = false) { const logPath = path.join(process.cwd(), 'ingestion.log'); log('🔄 Rebuilding all bucketed stats from raw play history...', logPath); log('ℹ️ Starting day bucket aggregation...', logPath); // Aggregate stats for each day bucket const dayBuckets = await prisma.daybucket.findMany({}); for (const day of dayBuckets) { log(`📅 Aggregating stats for daybucket ${day.id} (${day.start_date.toISOString()})`, logPath); const plays = await prisma.spplayhistory.findMany({ where: { played_at: { gte: day.start_date, lt: new Date(day.start_date.getTime() + 24 * 60 * 60 * 1000) } }, include: { track: { include: { track_artists: true } } } }); log(`🔎 Found ${plays.length} plays for daybucket ${day.id}`, logPath); const artistCounts = new Map(); const trackCounts = new Map(); const hourlyArtistPlays: Map = new Map(); const hourlyTrackPlays: Map = new Map(); for (const play of plays) { trackCounts.set(play.track_id, (trackCounts.get(play.track_id) || 0) + 1); if (!hourlyTrackPlays.has(play.track_id)) hourlyTrackPlays.set(play.track_id, Array(24).fill(0)); hourlyTrackPlays.get(play.track_id)![new Date(play.played_at).getUTCHours()]++; for (const ta of play.track.track_artists) { artistCounts.set(ta.artist_id, (artistCounts.get(ta.artist_id) || 0) + 1); if (!hourlyArtistPlays.has(ta.artist_id)) hourlyArtistPlays.set(ta.artist_id, Array(24).fill(0)); hourlyArtistPlays.get(ta.artist_id)![new Date(play.played_at).getUTCHours()]++; } } log(`🎨 Artist counts for daybucket ${day.id}: ${JSON.stringify(Array.from(artistCounts.entries()))}`, logPath); log(`🎵 Track counts for daybucket ${day.id}: ${JSON.stringify(Array.from(trackCounts.entries()))}`, logPath); // Actually insert artist stats for every day bucket. for (const [artist_id, count] of artistCounts.entries()) { log(`⬆️ Upserting artiststat for artist ${artist_id} in daybucket ${day.id} (count: ${count})`, logPath); if (!dryRun) { await prisma.artiststat.upsert({ where: { artist_id_stat_date: { artist_id, stat_date: day.start_date } }, update: { count, hourly_plays: hourlyArtistPlays.get(artist_id), daybucketid: day.id, bucket_scope: 'day' }, create: { artist_id, count, hourly_plays: hourlyArtistPlays.get(artist_id), stat_date: day.start_date, daybucketid: day.id, bucket_scope: 'day' }, }); } } // Actually insert track stats for every day bucket. for (const [track_id, count] of trackCounts.entries()) { log(`⬆️ Upserting trackstat for track ${track_id} in daybucket ${day.id} (count: ${count})`, logPath); if (!dryRun) { await prisma.trackstat.upsert({ where: { track_id_stat_date: { track_id, stat_date: day.start_date } }, update: { count, hourly_plays: hourlyTrackPlays.get(track_id), daybucketid: day.id, bucket_scope: 'day' }, create: { track_id, count,hourly_plays: hourlyTrackPlays.get(track_id), stat_date: day.start_date, daybucketid: day.id, bucket_scope: 'day' }, }); } } log(`✅ Updated daybucket ${day.id} stats.`, logPath); } // Aggregate stats for each month bucket log('ℹ️ Starting month bucket aggregation...', logPath); let monthBuckets = await prisma.monthbucket.findMany({ where: { id: { gte: 2801 } }, orderBy: { id: 'asc' } }); await prisma.artiststat.updateMany({ where: { monthbucketid: { not: null } }, data: { yearbucketid: null } }); await prisma.trackstat.updateMany({ where: { monthbucketid: { not: null } }, data: { yearbucketid: null } }); for (const month of monthBuckets) { log(`📆 Aggregating stats for monthbucket ${month.id} (${month.range_start.toISOString()})`, logPath); const dayBuckets = await prisma.daybucket.findMany({ where: { monthbucketid: month.id } }); const dayBucketIds = dayBuckets.map(db => db.id); log(`🔎 Found ${dayBucketIds.length} daybuckets for monthbucket ${month.id}`, logPath); log(`🗓️ Daybucket dates for monthbucket ${month.id}: ${dayBuckets.map(db => db.start_date.toISOString()).join(', ')}`, logPath); const artistStats = await prisma.artiststat.findMany({ where: { daybucketid: { in: dayBucketIds } } }); const artistMonthMap = new Map(); for (const stat of artistStats) { if (!stat.artist_id || !stat.stat_date) continue; const mb = monthBuckets.find(mb => mb.range_start <= stat.stat_date && mb.range_end >= stat.stat_date); if (!mb) continue; const key = `${stat.artist_id}|${mb.id}`; if (!artistMonthMap.has(key)) { artistMonthMap.set(key, { artist_id: stat.artist_id, monthbucketid: mb.id, count: 0 }); } artistMonthMap.get(key)!.count += stat.count; } log(`🎨 Artist counts for monthbucket ${month.id}: ${JSON.stringify(Array.from(artistMonthMap.values()).filter(a => a.monthbucketid === month.id))}`, logPath); const trackStats = await prisma.trackstat.findMany({ where: { daybucketid: { in: dayBucketIds } } }); const trackMonthMap = new Map(); for (const stat of trackStats) { if (!stat.track_id || !stat.stat_date) continue; const mb = monthBuckets.find(mb => mb.range_start <= stat.stat_date && mb.range_end >= stat.stat_date); if (!mb) continue; const key = `${stat.track_id}|${mb.id}`; if (!trackMonthMap.has(key)) { trackMonthMap.set(key, { track_id: stat.track_id, monthbucketid: mb.id, count: 0 }); } trackMonthMap.get(key)!.count += stat.count; } log(`🎵 Track counts for monthbucket ${month.id}: ${JSON.stringify(Array.from(trackMonthMap.values()).filter(t => t.monthbucketid === month.id))}`, logPath); const artistRows = Array.from(artistMonthMap.values()).filter(a => a.monthbucketid === month.id).map(({ artist_id, monthbucketid, count }) => ({ artist_id, count, bucket_scope: 'month', stat_date: month.range_start, monthbucketid })); log(JSON.stringify(artistRows[0]), logPath); // Actually insert artist stats for every month bucket. Done in batches for efficiency if (!dryRun && artistRows.length > 0) { await prisma.artiststat.createMany({ data: artistRows, skipDuplicates: true }); log(`[Prisma] Batch inserted artiststat for monthbucket=${month.id}, rows=${artistRows.length}`, logPath); } const trackRows = Array.from(trackMonthMap.values()).filter(t => t.monthbucketid === month.id).map(({ track_id, monthbucketid, count }) => ({ track_id, count, bucket_scope: 'month', stat_date: month.range_start, monthbucketid })); // Actually insert track stats for every month. Done in batches for efficiency if (!dryRun && trackRows.length > 0) { await prisma.trackstat.createMany({ data: trackRows, skipDuplicates: true }); log(`[Prisma] Batch inserted trackstat for monthbucket=${month.id}, rows=${trackRows.length}`, logPath); } log(`✅ Updated monthbucket ${month.id} stats.`, logPath); await prisma.artiststat.updateMany({ where: { monthbucketid: month.id }, data: { yearbucketid: null } }); await prisma.trackstat.updateMany({ where: { monthbucketid: month.id }, data: { yearbucketid: null } }); log(`🧹 Cleaned up yearbucketid for monthbucket ${month.id}`, logPath); } // Aggregate stats for each yearbucket log('ℹ️ Starting year bucket aggregation...', logPath); const yearBuckets = await prisma.yearbucket.findMany({}); for (const year of yearBuckets) { log(`📅 Aggregating stats for yearbucket ${year.id} (${year.range_start.toISOString()})`, logPath); const months = await prisma.monthbucket.findMany({ where: { yearbucketid: year.id } }); const monthIds = months.map(mb => mb.id); log(`🔎 Found ${monthIds.length} monthbuckets for yearbucket ${year.id}`, logPath); const artistStats = await prisma.artiststat.findMany({ where: { monthbucketid: { in: monthIds }, bucket_scope: 'month' } }); const artistCounts = new Map(); for (const stat of artistStats) { artistCounts.set(stat.artist_id, (artistCounts.get(stat.artist_id) || 0) + stat.count); } log(`🎨 Artist counts for yearbucket ${year.id}: ${JSON.stringify(Array.from(artistCounts.entries()))}`, logPath); const trackStats = await prisma.trackstat.findMany({ where: { monthbucketid: { in: monthIds }, bucket_scope: 'month' } }); const trackCounts = new Map(); for (const stat of trackStats) { trackCounts.set(stat.track_id, (trackCounts.get(stat.track_id) || 0) + stat.count); } log(`🎵 Track counts for yearbucket ${year.id}: ${JSON.stringify(Array.from(trackCounts.entries()))}`, logPath); // Actually insert artist stats for every year bucket. for (const [artist_id, count] of artistCounts.entries()) { log(`⬆️ Upserting artiststat for artist ${artist_id} in yearbucket ${year.id} (count: ${count})`, logPath); if (!dryRun) { await prisma.artiststat.upsert({ where: { artist_id_yearbucketid: { artist_id, yearbucketid: year.id } }, update: { count, yearbucketid: year.id, bucket_scope: 'year', stat_date: year.range_start }, create: { artist_id, count, bucket_scope: 'year', stat_date: year.range_start, yearbucketid: year.id }, }); } } // Actually insert track stats for every year bucket. for (const [track_id, count] of trackCounts.entries()) { log(`⬆️ Upserting trackstat for track ${track_id} in yearbucket ${year.id} (count: ${count})`, logPath); if (!dryRun) { await prisma.trackstat.upsert({ where: { track_id_yearbucketid: { track_id, yearbucketid: year.id, }, }, update: { count, yearbucketid: year.id, bucket_scope: 'year', stat_date: year.range_start }, create: { track_id, count, bucket_scope: 'year', stat_date: year.range_start, yearbucketid: year.id }, }); } } log(`✅ Updated yearbucket ${year.id} stats.`, logPath); } log('🎉 All bucketed stats rebuilt from raw play history.', logPath); } ```
---
## Routine Data Polling Okay, so I have created the functions to ingest data, populate the database, and aggregate stats. I just had to make sure that this was done routinely and on a schedule. I will point out that I could have used serverless functions with a third party cron service, which the hosting service that I am using (Render.com) offers but I stubbornly knew it could be done with the tech stack I was using. More on that later. NextJS in of itself isn't designed to run cron jobs but because it is built on top of Node.js I leveraged Node.js to set up run background services. - I added `express` and `node-cron` to my NextJS app. - Create a custom `server.js` file to start an Express server and modify the `start` script in `package.json` to run `node server.js` instead of `next start`. - Within `server.js` there are two cron jobs * 1) Runs every 30 minutes to ingest new Spotify history * 2) Runs every 6 hours to aggregate daily stats for the last three days. - Built safeguards for dev mode to prevent overlapping executions.
``` ##Server with Cron Jobs #canCollapse import express from 'express'; import next from 'next'; import { schedule } from 'node-cron'; const port = parseInt(process.env.PORT, 10) || 3000; const dev = process.env.NODE_ENV !== 'production'; const app = next({ dev }); const handle = app.getRequestHandler(); app.prepare().then(() => { const server = express(); const domain = dev ? 'http://localhost:3000' : 'https://lucas.untethered4life.com'; const historyurl = `${domain}/api/getspotifyhistory`; const dailyurl = `${domain}/api/aggregatedailystats`; console.log(`Cron job will POST to: ${historyurl} and ${dailyurl}`); console.log(`Running in ${dev ? 'development' : 'production'} mode`); let isRunning = false; // Calls the getSpotifyHistory API route to fetch recent plays and push them to the database. async function runSpotifyIngestion() { try { const response = await fetch(historyurl, { method: 'POST', headers: { 'x-cron-token': process.env.CRON_SECRET } }) const data = await response.json(); if (!response.ok) { throw new Error(`HTTP ${response.status}: `, data); } console.log('✅ Scheduled spotify ingestion response:', data); } catch (error) { console.error('❌ Error in scheduled task:', error); } } // Calls the aggregateDailyStats API route to update daily stats for the last three days. async function aggregateDailyStats() { try { const response = await fetch(dailyurl, { method: 'POST', headers: { 'x-cron-token': process.env.CRON_SECRET } }) const data = await response.json(); if (!response.ok) { throw new Error(`HTTP ${response.status}: `, JSON.stringify(data)); } console.log('✅ Scheduled aggregate daily stats response:', data); } catch (error) { console.error('❌ Error in scheduled task:', error); } } // Schedule tasks only in production mode if (!dev) { // Call runSpotifyIngestion() to fetch recent plays and push them to the database. // This is done very 30 minutes, as long as a prior job is not still running schedule('*/30 * * * *', async () => { if (isRunning) { console.warn(`⚠️ Skipping run — previous job still running`); return; } isRunning = true; console.log(`⏱️ Attempting data ingestion at ${new Date().toISOString()}`); try { await runSpotifyIngestion(); } finally { isRunning = false; } }); // Call aggregateDailyStats() to update daily stats for the last three days. // This is done every 6 hours to ensure stats are up to date. schedule('10 */6 * * *', async () => { console.log(`⏱️ Attempting daily stats aggregation at ${new Date().toISOString()}`); await aggregateDailyStats(); }); } server.all('*', (req, res) => { return handle(req, res); }); server.listen(port, (err) => { if (err) throw err; console.log(`> Ready on ${domain}`); }); }); ```
Okay....so we have all of the data ingested, aggregated, and updated on a regular basis. Can we finally get that data to the front end? Nope, not yet, but I was almost there. - Build the back end API routes to fetch this data. - Build the front end to display it.
I thought I was almost there. The back end API route to fetch data was the most difficult part of this entire project and took me roughly a week just for this function. Problems that I had: - Long processing times (10+ seconds initially, ouch). - Memory usage spikes, this code took down my deployed instance a handful of times. - Database locks, timeouts, and refused connections. - Random brain fries, where I just couldn't continue to process what was happening myself. - AI misdirecting me or writing code that was horribly wrong (admittedly AI also provided code that was solid and incredibly complex as well). - Having to take a shower, eat, feed my dog, and occasionally find a natural source of vitamin D delayed my progress too. - With a lot of caffeine, limited sleep, and my friends....ChatGPT, Copilot, and Claude we came up with a solution that worked well enough. Ideally, the front end calls the back end with a date range and the back end returns a series of objects that are used to render the heatmap. - `topArtists[]` - This is an array of the top 10 artist objects for the date range. - `topTracks[]` - This is an array of the top 10 track objects for the date range. - `playFrequency[]` - Contains an item for every day in the date range. Each item has: * `date` - The date in YYYY-MM-DD format. * `weekday` - The short name of the weekday (e.g., 'Mon', 'Tue'). * `hourly_plays[]` - An array of 24 integers representing play counts for each hour of the day (0-23). - `artistHeatmaps[]` - Contains 10 objects identical to `playFrequency[]` for each of the top 10 artists. - `trackHeatMaps[]` - Contains 10 objects identical to `playFrequency[]` for each of the top 10 tracks.
``` ##getSpotifyStatsByDateRange(startDate, endDate) #canCollapse /* * Retrieves aggregated Spotify stats for a given date range. * Includes top artists, top tracks, hourly play frequency, and heatmap data. */ export const getSpotifyStatsByDateRange = async ( startDate: Date, endDate: Date ): Promise => { try { const startTime = Date.now(); /* // Use raw SQL for fast bucket based aggregation. // Fetch daily stats, top artists, and top tracks in parallel. // A lot of help from AI to get these queries figured out, but these // queries find the top artists and tracks efficiently. */ const [dailyStats, topArtistsRaw, topTracksRaw] = await Promise.all([ prisma.spdailyplaystats.findMany({ where: { date: { gte: startDate, lte: endDate } }, select: { date: true, weekday: true, hourly_plays: true } }), prisma.$queryRaw` SELECT artist.artist_id, artist.name, artist.image_url, artist.artist_url, COALESCE(SUM(stats.count), 0) as total_count FROM spartist artist INNER JOIN artiststat stats ON artist.artist_id = stats.artist_id WHERE stats.stat_date >= ${startDate} AND stats.stat_date <= ${endDate} GROUP BY artist.artist_id, artist.name, artist.image_url, artist.artist_url ORDER BY total_count DESC LIMIT 10 `, prisma.$queryRaw` SELECT track.track_id, track.title, track.isrc, track.song_url, track.explicit, track.release_date, track.album_id, album.name as album_name, album.image_url as album_image, album.release_date as album_release_date, common_album.album_id as common_album_id, common_album.name as common_album_name, common_album.image_url as common_album_image, common_album.release_date as common_album_release_date, STRING_AGG(ar.name, ', ' ORDER BY ar.name) as artists, COALESCE(SUM(ts.count), 0) as total_count FROM sptrack track INNER JOIN trackstat ts ON track.track_id = ts.track_id LEFT JOIN spalbum album ON track.album_id = album.album_id LEFT JOIN spalbum common_album ON track.common_album_id = common_album.album_id LEFT JOIN sptrackartist ta ON track.track_id = ta.track_id LEFT JOIN spartist ar ON ta.artist_id = ar.artist_id WHERE ts.stat_date >= ${startDate} AND ts.stat_date <= ${endDate} GROUP BY track.track_id, track.title, track.isrc, track.song_url, track.explicit, track.release_date, track.album_id, album.name, album.image_url, album.release_date, common_album.album_id, common_album.name, common_album.image_url, common_album.release_date ORDER BY total_count DESC LIMIT 10 ` ]); console.log(`Raw query completed in ${Date.now() - startTime}ms`); // Format daily play frequency data. // Ensure every hour is represented, even if there were no plays. const playFrequency = dailyStats.map(stat => ({ date: stat.date.toISOString().substring(0, 10), weekday: stat.weekday, hourly_plays: Array.isArray(stat.hourly_plays) ? stat.hourly_plays : Array(24).fill(0) })); // Format top artists from raw SQL results. const topArtists = (topArtistsRaw as any[]).map(row => ({ artistId: row.artist_id, name: row.name || '', image: row.image_url || 'images/spotify-icon.svg', artist_url: row.artist_url || '', count: Number(row.total_count) })); // Format top tracks from raw SQL results. const topTracks = (topTracksRaw as any[]).map(row => ({ trackId: row.track_id, isrc: row.isrc || '', album: { albumId: row.album_id || '', name: row.album_name || '', release_date: row.album_release_date || new Date(), image: row.album_image || 'images/spotify-icon.svg' }, common_album: { albumId: row.common_album_id || row.album_id || '', name: row.common_album_name || row.album_name || '', image: row.common_album_image || row.album_image || 'images/spotify-icon.svg', release_date: row.common_album_release_date || row.album_release_date || new Date() }, artists: row.artists || '', songUrl: row.song_url || '', title: row.title || '', release_date: row.release_date || new Date(), explicit: Boolean(row.explicit), count: Number(row.total_count) })); console.log(`Data processing completed in ${Date.now() - startTime}ms`); // Fetch metadata counts for tracks and artists in the date range. const [totalCountsRaw] = await Promise.all([ prisma.$queryRaw` SELECT COUNT(DISTINCT track.track_id) as total_track_count, COUNT(DISTINCT artist.artist_id) as total_artist_count, COALESCE(SUM(CASE WHEN track.explicit = true THEN stat.count ELSE 0 END), 0) as explicit_count, COALESCE(SUM(stat.count), 0) as total_play_count FROM trackstat stat INNER JOIN sptrack track ON stat.track_id = track.track_id INNER JOIN sptrackartist trackartists ON track.track_id = trackartists.track_id INNER JOIN spartist artist ON trackartists.artist_id = artist.artist_id WHERE stat.stat_date >= ${startDate} AND stat.stat_date <= ${endDate} ` ]); const totalCounts = (totalCountsRaw as any[])[0]; const totalTrackCount = Number(totalCounts.total_track_count); const totalArtistCount = Number(totalCounts.total_artist_count); const explicitCount = Number(totalCounts.explicit_count); const totalPlayCount = Number(totalCounts.total_play_count); const percentExplicit = totalPlayCount > 0 ? parseFloat(((explicitCount / totalPlayCount) * 100).toFixed(1)) : 0.0; /* Fetch heatmap data for the top artists and tracks. // The bucket system is used to count the top artists and tracks // efficently then we can pull just the day bucket stats */ for the top artists and tracks. let artistHeatmaps: HeatmapFrequencies[] = []; let trackHeatMaps: HeatmapFrequencies[] = []; if (topArtists.length > 0 || topTracks.length > 0) { const topArtistIds = topArtists.map(a => a.artistId); const topTrackIds = topTracks.map(t => t.trackId); const [heatmapArtistStats, heatmapTrackStats] = await Promise.all([ prisma.artiststat.findMany({ where: { artist_id: { in: topArtistIds }, stat_date: { gte: startDate, lte: endDate }, bucket_scope: 'day' }, select: { artist_id: true, stat_date: true, hourly_plays: true } }), prisma.trackstat.findMany({ where: { track_id: { in: topTrackIds }, stat_date: { gte: startDate, lte: endDate }, bucket_scope: 'day' }, select: { track_id: true, stat_date: true, hourly_plays: true } }) ]); /* This takes the aggregated data pulled from database for each // artist and prepares it for the front end. Ideally, it ensures // every hour is represented, even if there weren't any plays for */ a particular year, month, day. artistHeatmaps = heatmapArtistStats.map(stat => ({ date: stat.stat_date.toISOString().split('T')[0], weekday: format(stat.stat_date, 'EEEE'), hourly_plays: Array.isArray(stat.hourly_plays) && stat.hourly_plays.length === 24 ? stat.hourly_plays : Array(24).fill(0), artistId: stat.artist_id, trackId: undefined })); trackHeatMaps = heatmapTrackStats.map(stat => ({ date: stat.stat_date.toISOString().split('T')[0], weekday: format(stat.stat_date, 'EEEE'), hourly_plays: Array.isArray(stat.hourly_plays) && stat.hourly_plays.length === 24 ? stat.hourly_plays : Array(24).fill(0), artistId: undefined, trackId: stat.track_id })); } console.log(`Query completed in ${Date.now() - startTime}ms`); // Return final aggregated response. return { status: 200, data: { topArtists, topTracks, playFrequency, artistHeatmaps, trackHeatMaps, meta: { totalTrackCount, totalArtistCount, percentExplicit } } }; } catch (error) { console.error('Error in getSpotifyStatsByDateRange:', error); return { status: 500, data: { topArtists: [], topTracks: [], playFrequency: [], artistHeatmaps: [], trackHeatMaps: [], meta: { totalTrackCount: 0, totalArtistCount: 0, percentExplicit: 0 } } }; } }; ```
---
# Deploying on Render Previously, I have hosted projects on AWS. I've used EC2 instances, Lambda functions, S3 buckets, RDS databases, and a host of other AWS services. When I first started using AWS I geeked out on all the different things that you could do. Over time I realized: - I was spending more time managing infrastructure than building actual applications. - It became more and more difficult to estimate costs, every small tidbit of processing, storage, or data transferring had an associated cost. - It was easy to make unrecoverable mistakes. With that, I did some research and found Render.com. Render is a modern cloud platform that makes it easy to deploy web applications, APIs, and databases with minimal configuration. Whenever I read statements about how easy something is technically, I am always skeptical. But I gave them a try and was pleasently surprised.
---
## Instance Creation Once you have created an account get started by simply selecting New -> Web Service. Connect your GitHub or GitLab account and select a repository and Render automatically detects the type of application, in this case NextJS, and fills in the typical build and start commands for you. Continue along and select an instance type, region, and enter your environment variables. Render Setup 2

I started with a Starter instance as I didn't expect I'd require much for a profile site. Render Setup 3

Render gave me a generated domain, `profile-site-rt4b:10000`, to access the site immediately. I added my own custom domain and Render gave me the specific DNS entries that I needed to configure with my DNS provider. Right within the Render dashboard was a link to documentation for what updating DNS records may look like depending on your DNS provider. A simply verification link was available to check for DNS propegation and once the record was verified...you'll see the green verified check mark! I added a test custom domain to demonstrate what I mean. Render Setup 6

Repeat that same process to fire up PostgreSQL database, I chose a Basic-256mb instance. They provide both an internal and an external connections string, which using the external connection string was helpful for local development and having a more direct connection string for your deployed application is a nice benefit. Render Setup 4

I had never used their services before, and it took roughly 10 minutes to build and deploy my application. That is not an exagerated statement! ## Deployment Pipeline Because I connected my GitHub repository to Render, every time I push to the main branch it automatically triggers a new build and deployment. Render provides a detailed build log so you can see exactly what is happening during the build process. By navigating to the Events tab for your service you can see the status of each deployment. Each row includes a link to the build logs, a link to the exact commit that triggered the deployment, debug logs if needed, and the ability to rollback to a previous deployment if something else went wrong. Now I did have a few hiccups with the Starter instances. Once I got into the thick of this Spotify Heatmap project I quickly realized that the Starter instances for the web service and database were not going to cut it. With a few clicks and a couple minutes, I was able to scale up to a Standard instance for both the web service and the database with not additional work required from me. Also, Render uses fairly aggressive caching for builds, so after the initial build subsequent builds were very quick but my changes wouldn't show up. A handful of times I had to clear the build cache and re-deploy....but again, this was very easy to accomplish. I didn't have to bury myself in multiple nested levels of documentation or do any sort of exhaustive debugging to figure out what was going happening. Everything I needed to do was a few clicks, literally. ## Monitoring & Alerts As I was ingesting large sets of data over and over again I made use of the monitoring Render provides within their metrics dashboards. For example, you can see very clearly when my backend cron job runs every 30 minutes to ingest new Spotify history data. As iterated through the building of the yearbuckets, monthbuckets, and daybuckets I used the Top Queries table listed in the database metrics dashboard to identify which queries were taking the longest to execute and determine if/how I could optimize them. Render Monitoring 1

## Final Opinion on Render One gripe that I did have that didn't really fit into the other sections was that I knew what Node.js, Express, and NextJS were capable of. With that, Render's SEO game is strong! Whether I did a Google search, asked my inline AI coding assistant, or independent AI assistant for instructions for running an Express server alongside a NextJS application on Render EVERYTHING, and I mean EVERYTHING suggested I create a new Background Worker or Cron Job service within Render. Creating a cron job service is nearly identical to creating a web service, you select a repo, it auto populates most fields, but there are two differences. - You enter a cron expression. - The cost is converted from a monthly rate to a per minute rate. Essentially, Render will build your code in a seperate container at the scheduled time and run it. However, I already had a Node service running where I was paying for dedicated resources monthly and I didn't want to pay for two separate instances. I wanted to leverage the existing instance that I was already paying for. Now, before you all think I am knocking Render, I'm not. Render doesn't stop you from getting in the weeds with your infrastructure if you want to. They also make it really easy to do what you need to do without the complications typically associated with more direct infrastructure management. Less time worrying about infrastructure and more time shipping code is exactly the value propisition that Render offers, and I am sold, they deliver that promise 100%. There are a slew of other features that I didn't even touch on like blueprints (their version of Infrastructure as Code), private links, log forwarding, metrics streaming, health check services, and a whole lot more. The next project I jump into that is more than just a profile site will definitely be hosted on Render and I look forward to more fully utilizing their service.
---
# Rendering Wow, it took a lot of pre-work to get here. To that final magical moment where all of the peices come together and you see the result of your labor. Or...you don't and you go back and forth debugging for hours on end, I'd like to think my code always works first time around, but I digress. Rendering turned out to be the easiest part of this entire project (except for deploying on Render, that was much easier actually). Before I jump into this, I will disclose that me and CSS are mortal enemies...when me and CSS have to work together it can get real messy, please don't judge my CSS too harshly. On a serious note, I intend to make my CSS much more modular and thematic. I actually need to do it for the entire site, but until then, a whole lot of inline tailwind CSS it is. There are a lot of pieces that use the Spotify data pulled from the backend, but the real meat and potatoes of this post is the HeatMap component. The heatmap component takes three props `Heatmap = ({ hourlyMap, weekdayMap, monthlyMap }: HeatmapDisplayProps)` where each map is formatted as `Map>()`. - `hourlyMap` is a Map object where the key is the weekday (e.g., 'Mon', 'Tue') and the value is an hour label and an array of 24 integers representing play counts for each hour of that day. - `weekdayMap` is a Map object where the key is the week (e.g., '2023-W01', '2023-W02') and the value is a day label and an array of 7 integers representing play counts for each day of that week. - `monthlyMap` is a Map object where the key is the year (e.g., 2023, 2024) and the value is a month label and an array of 12 integers representing play counts for each month of that year (0-11). This is done for the overall play frequency heatmap and for each of the top 10 artists and tracks. Once those maps are created that can be passed to the heatmap component to be rendered.
``` ##Data Preparation #canCollapse data.playFrequency?.forEach(({ date, hourly_plays }: HeatMapProps) => { if (!Array.isArray(hourly_plays)) return; const parsedDate = new Date(date); if (isNaN(parsedDate.getTime())) return; const year = getYear(parsedDate); const week = getISOWeek(parsedDate); const weekday = format(parsedDate, 'EEEE'); const weekKey = `${year}-W${week}`; if (!hourlyMap.has(weekKey)) hourlyMap.set(weekKey, new Map()); const weekMap = hourlyMap.get(weekKey)!; if (!weekMap.has(weekday)) weekMap.set(weekday, Array(24).fill(0)); const bucket = weekMap.get(weekday)!; hourly_plays.forEach((count, hour) => { bucket[hour] += count; }); }); const weekdayMap = new Map(); data.playFrequency.forEach(({ date, hourly_plays }: HeatMapProps) => { const parsedDate = new Date(date); if (isNaN(parsedDate.getTime()) || !Array.isArray(hourly_plays)) return; const d = startOfDay(parsedDate); const weekStart = startOfWeek(d, { weekStartsOn: 0 }); const label = `${format(weekStart, "MMM d ''yy")}`; const weekday = d.getDay(); const totalPlays = hourly_plays .filter((count) => typeof count === 'number') .reduce((a, b) => a + b, 0); if (!weekdayMap.has(label)) { weekdayMap.set(label, { start: weekStart, counts: Array(7).fill(null) }); } const counts = weekdayMap.get(label)!.counts; counts[weekday] = (counts[weekday] ?? 0) + totalPlays; }); const monthlyMap = new Map(); data.playFrequency.forEach(({ date, hourly_plays }: HeatMapProps) => { const d = new Date(date); const year = d.getFullYear().toString(); const monthIndex = d.getMonth(); // 0 = Jan, 11 = Dec const totalPlays = hourly_plays.reduce((a, b) => a + b, 0); if (!monthlyMap.has(year)) { monthlyMap.set(year, Array(12).fill(null)); } if (monthlyMap.get(year)) { monthlyMap.get(year)![monthIndex] = (monthlyMap.get(year)![monthIndex] ?? 0) + totalPlays; } }); ```
``` ##Heatmap Component #canCollapse import clsx from 'clsx'; import React, { useId, useState } from 'react'; import Card from '@/common/components/elements/Card'; import { HeatmapDisplayProps } from '@/common/types/spotify'; import { addDays, endOfWeek, format, setISOWeek, startOfWeek } from 'date-fns'; import { motion } from 'framer-motion'; /** * Responsive scaling CSS for the heatmap component * Scales the heatmap smoothly between 320px and 690px viewport widths * Uses transform scaling to maintain aspect ratio while fitting mobile screens */ const heatmapResponsiveStyle = ` @media (max-width: 690px) { .heatmap-responsive { width: 100vw; max-width: 100vw; transform: scale(calc(0.85 + 0.15 * ((100vw - 320px) / 370))); transform-origin: top left; } } `; /** * Heatmap component for visualizing Spotify listening patterns * Displays listening activity across three time resolutions: * - Hourly: 24-hour breakdown for each day of the week * - Daily: Week-by-week view showing daily totals * - Monthly: Year-by-year view showing monthly totals */ const Heatmap = ({ hourlyMap, weekdayMap, monthlyMap }: HeatmapDisplayProps) => { // State for switching between time resolutions const [resolution, setResolution] = useState<'Hourly' | 'Daily' | 'Monthly'>('Hourly'); // Pagination state for daily view (showing 10 weeks at a time) const [pageIndex, setPageIndex] = useState(0); const pageSize = 10; // Generate unique ID for radio button groups to avoid conflicts const uniqueId = useId(); // Dynamic scale factor for mobile responsiveness // Adjusts the grid size on smaller screens const [scale, setScale] = useState(1); React.useEffect(() => { const handleResize = () => { setScale(window.innerWidth < 690 ? 0.7 : 1); }; window.addEventListener('resize', handleResize); handleResize(); // Call once on mount to set initial scale return () => window.removeEventListener('resize', handleResize); }, []); // Calculate maximum values for each resolution // Used to normalize the color intensity across cells const maxHourlyCount = Math.max( ...Array.from(hourlyMap.entries()) .flatMap(([_, weekMap]) => Array.from(weekMap.entries()) .flatMap(([_, hourArray]) => hourArray) ) ); const maxDailyCount = Math.max( ...Array.from(weekdayMap.values()) .flatMap(({ counts }) => counts.filter((count): count is number => count !== null) ) ); const maxMonthlyCount = Math.max( ...Array.from(monthlyMap.values()) .flat() .filter((count): count is number => count !== null) ); /** * Determines the background color class based on listening intensity * @param count - Number of plays in the time period * @returns Tailwind CSS class for the appropriate color intensity * * Color scale (from least to most intense): * - neutral-700: No data available * - neutral-300: Zero plays * - rose-400 to rose-900: Increasing play counts (20% increments) */ const getIntensityClass = (count: (number | null)) => { if (count === null || count === undefined) return 'bg-neutral-700'; let ratio = 1; switch (resolution) { case 'Hourly': ratio = count / maxHourlyCount; break; case 'Daily': ratio = count / maxDailyCount; break; case 'Monthly': ratio = count / maxMonthlyCount; break; default: 'bg-neutral-200'; } // Map ratio to color intensity if (ratio === 0) return 'bg-neutral-300'; if (ratio < 0.2) return 'bg-rose-400'; if (ratio < 0.4) return 'bg-rose-600'; if (ratio < 0.6) return 'bg-rose-700'; if (ratio < 0.8) return 'bg-rose-800'; return 'bg-rose-900'; }; // Sort weeks chronologically for the daily view const sortedWeeks = [...weekdayMap.entries()] .sort(([, a], [, b]) => a.start.getTime() - b.start.getTime()); // Calculate pagination for daily view const totalPages = Math.ceil(sortedWeeks.length / pageSize); const paginatedWeeks = sortedWeeks.slice( pageIndex * pageSize, (pageIndex + 1) * pageSize ); // Week navigation for hourly view const weekKeys = Array.from(hourlyMap.keys()).sort(); const [weekIndex, setWeekIndex] = useState(0); const currentWeekKey = weekKeys[weekIndex]; // Parse the ISO week format (e.g., "2024-W15") const safeWeekKey = currentWeekKey ?? ''; const [yearStr, weekStr] = safeWeekKey.split('-W'); const year = parseInt(yearStr); const week = parseInt(weekStr); const currentWeekMap = hourlyMap.get(currentWeekKey) ?? new Map(); const currentYear = new Date().getFullYear(); // Format the week range for display (e.g., "Mon 4/1/24 – Sun 4/7/24") let formattedWeekRange = ''; if (!isNaN(year) && !isNaN(week)) { // Jan 4 is always in week 1 (ISO week date standard) const baseDate = setISOWeek(new Date(`${year}-01-04`), week); const currentWeekStart = startOfWeek(baseDate, { weekStartsOn: 0 }); const currentWeekEnd = endOfWeek(baseDate, { weekStartsOn: 0 }); formattedWeekRange = `${format(currentWeekStart, 'EEE M/d')}/${year - 2000} – ${format(currentWeekEnd, 'EEE M/d')}/${year - 2000}`; } return ( <> {/* Header with title and resolution selector */}

Listening Times

{['Hourly', 'Daily', 'Monthly'].map(option => ( setResolution(option as any)} className='mr-1 accent-rose-600' /> {option} ))}

{/* HOURLY VIEW: 7x24 grid showing hour-by-hour listening for a week */} {resolution === 'Hourly' && (

{/* Hourly Grid */}

{/* Empty cell for grid alignment */}

{/* Hour labels (12a, 1a, ... 11p) */} {[...Array(24)].map((_, hour) => (

{hour === 0 ? '12a' : hour < 12 ? `${hour}a` : hour === 12 ? '12p' : `${hour - 12}p`}

))} {/* Render each weekday row */} {['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'].map(weekday => { // Get hourly play counts for this weekday, default to empty array const hourly_plays = hourlyMap.get(weekKeys[weekIndex])?.get(weekday) ?? Array(24).fill(0); return ( {/* Weekday label (abbreviated) */}

{weekday.substring(0, 3)}

{/* Hour cells for this weekday */} {hourly_plays.map((count, hour) => (

))} ); })}

{/* Week navigation controls */}

{formattedWeekRange}

)} {/* DAILY VIEW: Shows multiple weeks with daily totals */} {resolution === 'Daily' && (

{/* Empty cell for grid alignment */}

{/* Day of week labels */} {['Su', 'M', 'Tu', 'W', 'Th', 'F', 'Sa'].map(day => (

{day}

))} {/* Render each week (paginated) */} {paginatedWeeks.map(([label, { start, counts }]) => ( {/* Week start date label */}

{format(start, "MMM d ''yy")}

{/* Daily cells for this week */} {counts.map((count, i) => { const cellDate = addDays(start, i); const dateLabel = format(cellDate, 'EEE, MMM d', { weekStartsOn: 0 }); const title = count === null || count === undefined ? `${dateLabel}: No data` : `${dateLabel}: ${count} plays`; return (

); })} ))} {/* Pagination controls for daily view */} {totalPages > 1 && (

)}

)} {/* MONTHLY VIEW: Shows years with monthly totals */} {resolution === 'Monthly' && (

{/* Empty cell for grid alignment */}

{/* Month number labels (1-12) */} {['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'].map(month => (

{month}

))} {/* Render each year's monthly data */} {[...monthlyMap.entries()] .sort(([a], [b]) => parseInt(a) - parseInt(b)) // Sort years chronologically .map(([yearLabel, monthlyCounts]) => ( {/* Year label */}

{yearLabel}

{/* Monthly cells for this year */} {monthlyCounts.map((count, i) => { const monthName = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'][i]; const title = count === null ? `${monthName}: No data` : `${monthName}: ${count} plays`; return (

); })} ))}

)}

); }; export default Heatmap; ```
---
# Lessons & Bloopers ## Types I have a love/hate relationship with Typescript. I love the type safety and the ability to catch errors at compile time rather than runtime. I've learned that autocomplete and AI assistance are only as good as the types you provide. If your types are wrong, or incomplete, or just plain missing then you are going to get bad suggestions. If your type definitions are well thought ought and used throughout your codebase then you are going to much better suggestions. A problem I ran into was if I did not import the type definitions within the file I was working on then my autocomplete or AI would suggest declaring the type rather than using the existing type. This led to a lot of duplicate types that were slightly different and caused a lot of confusion. ## Prisma Studio This was my first time using Prisma. Prisma is great as an ORM in Javascript based frameworks. It is very easy to use and the type safety is a huge plus. However, I ran into a few issues with Prisma Studio. It was just plain wrong and often! It would show data that wasn't there and not show data that was there. For example, when I was validating the creation of my bucketed stats it would show that there were no `artiststats` or `trackstats` for a given day. This led my down some incorrect troubleshooting paths, until I finally reliazed that the data was in fact there when I queried the database directly. Prisma Studio Issue 1

--- ## Count Everything, Always! At some point, AI decided that the buckets didn't really matter, especially because we can just fetch all of them. And while we're at it, let's fetch all the tracks and artists in those buckets too! ``` ## Fetching all buckets and ALL precounted stats, ALL buckets, ALL tracks, and ALL artists. const [artistStats, trackStats] = await Promise.all([ prisma.artiststat.findMany({ where: { OR: [ { bucket_scope: 'year', yearbucketid: { in: yearBucketIds } }, { bucket_scope: 'month', monthbucketid: { in: monthBucketIds } }, { bucket_scope: 'week', weekbucketid: { in: weekBucketIds } }, { bucket_scope: 'day' } ] }, include: { artist: true } }), prisma.trackstat.findMany({ where: { OR: [ { bucket_scope: 'year', yearbucketid: { in: yearBucketIds } }, { bucket_scope: 'month', monthbucketid: { in: monthBucketIds } }, { bucket_scope: 'week', weekbucketid: { in: weekBucketIds } }, { bucket_scope: 'day' } ] }, include: { track: { include: { album: true, common_album: true, track_artists: { include: { artist: true } } } } } }) ]); ``` This code gave me an opportunity to see how responsive Render's alerting services were, and I can tell you that they are very responsive. This bit of code would bring down the Starter instance and small database I began with almost instantly. ## Double Wrapped My `src/pages/api` routes return data wrapped in a standard response object like this: ``` { status: number; data: SpotifyDataResponseProps; } ``` Well from a backend service I accidentally returned data as if it was an API route: ``` res.status(200).json({ status: 200, data: { ... } }); ``` which resulted in a double wrapped return like this: ``` { status: 200, data: { status: 200, data: { ...actual data... } } } ``` That little nugget cost me some serious debugging time. --- ## Something doesn't look right... As I was building the front end I kept seeing things that just didn't look right...

--- ## Arguing with my AI I had some, well, interesting conversations with my AI assistants. Like when AI tells me how I incorrectly wrote something that I didn't write... AI Argument

Or, when your AI assistance is convinced that they know... AI Persistance Example 1

---
# Future Enhancements ## ISRC Code Integration I recognized early in this project that if the same recorded song appears on a different album that each instance has a unique Spotify `track_id`. As a result, counting "Top Tracks" may be misleading if the same song appears on multiple albums. One of my favorite artists, Neffex, has a ton of songs. He also publishes almost every song as a single and creates themed album releases for those same songs regularly. I'd even conjecture that Spotify recommends the same song but to me but from different albums based on my listening habits. To address this, I plan to integrate the International Standard Recording Code (ISRC) into my database schema. The ISRC is a unique identifier for each recording, regardless of the album it appears on. By associating each track with its ISRC, I can aggregate play counts across different albums for the same song. This will provide a more accurate representation of my listening habits and help identify my true "Top Tracks." I attempted to do this from the start but the complexity of the database schema and the time constraints of this project led me to table this enhancement for the time being. ## Specific Hour Details As of now, hovering over a specific hour in the heatmap will show the total play count for that hour across all tracks. With how the database schema is designed, it ought to be easy to extend this functionality. With an onClick() event and a single database call I can acquire the track and artist details for that specific hour. This would be a great enhancement to provide even more granular insights into my listening habits. ## Audio Features and Analysis There is some incredibly rich data available for every track on Spotify. Two endpoint in particular, `v1/audio-analysis/{id}` and `v1/audio-features/{id}`, provide a wealth of information about the musical characteristics of each track. It would be interesting to aggregate that data across my entire listening history to see if there are any patterns or trends in the types of music I listen to and. For example, do I tend to listen to more energetic songs in the morning and more mellow songs in the evening? Do I have a preference for certain keys or tempos? Can one learn anything about my mood based on the valence of the songs I listen to? Even more interesting to me would be if there are correlations with audio features and different populations? The amount of data is truly staggering. For example, here are the audio features and analysis for one of my favorite songs, "Underground" by Lindsey Stirling. Just kidding, those endpoints have been deprecated and only developers that submitted a request for access prior to deprecation can continue to use the endpoints. It seems to have sparked a little bit of an upheaval amongst existing developers that built apps around this data. Spotify does have very clear messaging in their documentation and specifically at the top of the documentation for these endpoints, that Spotify data can not be used to with machine learning or AI. I will be keeping an eye on the Web API documentation to see if these endpoints are ever re-enabled or if there are alternative ways to access this data in the future. In the mean time, here is what the data looks like for a random track in Spotify's API documentation ("Time is Running Out" by Muse): ``` ##Audio Features #canCollapse #language=Json { "acousticness": 0.00242, "analysis_url": "https://api.spotify.com/v1/audio-analysis/2takcwOaAZWiXQijPHIx7B", "danceability": 0.585, "duration_ms": 237040, "energy": 0.842, "id": "2takcwOaAZWiXQijPHIx7B", "instrumentalness": 0.00686, "key": 9, "liveness": 0.0866, "loudness": -5.883, "mode": 0, "speechiness": 0.0556, "tempo": 118.211, "time_signature": 4, "track_href": "https://api.spotify.com/v1/tracks/2takcwOaAZWiXQijPHIx7B", "type": "audio_features", "uri": "spotify:track:2takcwOaAZWiXQijPHIx7B", "valence": 0.428 } ```
``` ##Audio analysis #canCollapse #language=Json { "meta": { "analyzer_version": "4.0.0", "platform": "Linux", "detailed_status": "OK", "status_code": 0, "timestamp": 1495193577, "analysis_time": 6.93906, "input_process": "libvorbisfile L+R 44100->22050" }, "track": { "num_samples": 4585515, "duration": 207.95985, "sample_md5": "string", "offset_seconds": 0, "window_seconds": 0, "analysis_sample_rate": 22050, "analysis_channels": 1, "end_of_fade_in": 0, "start_of_fade_out": 201.13705, "loudness": -5.883, "tempo": 118.211, "tempo_confidence": 0.73, "time_signature": 4, "time_signature_confidence": 0.994, "key": 9, "key_confidence": 0.408, "mode": 0, "mode_confidence": 0.485, "codestring": "string", "code_version": 3.15, "echoprintstring": "string", "echoprint_version": 4.15, "synchstring": "string", "synch_version": 1, "rhythmstring": "string", "rhythm_version": 1 }, "bars": [ { "start": 0.49567, "duration": 2.18749, "confidence": 0.925 } ], "beats": [ { "start": 0.49567, "duration": 2.18749, "confidence": 0.925 } ], "sections": [ { "start": 0, "duration": 6.97092, "confidence": 1, "loudness": -14.938, "tempo": 113.178, "tempo_confidence": 0.647, "key": 9, "key_confidence": 0.297, "mode": -1, "mode_confidence": 0.471, "time_signature": 4, "time_signature_confidence": 1 } ], "segments": [ { "start": 0.70154, "duration": 0.19891, "confidence": 0.435, "loudness_start": -23.053, "loudness_max": -14.25, "loudness_max_time": 0.07305, "loudness_end": 0, "pitches": [0.212, 0.141, 0.294], "timbre": [42.115, 64.373, -0.233] } ], "tatums": [ { "start": 0.49567, "duration": 2.18749, "confidence": 0.925 } ] } ```
---
# Closing Remarks This project was a lot of fun and I learned a lot along the way. Data and the story it can tell is so incredible to me. I am constantly amazed at the insights that can be gleaned from data, especially when it is visualized in a way that is easy to understand yet tells a story from a different and unique perspective. I hope to continue to explore the world of data and visualization in future projects. I hope you found the post interesting and maybe even useful. If you have any questions or comments, please feel free to reach out to me [HERE](https://lucas.untethered4life.com/contact).

Projects