Personal Data Warehouse

api_warehouse

A personal data platform that replaces several older, one-off backends (a standalone Spotify API, a standalone Google Fit API, an energy-usage dashboard) with a single, generic, config-driven ingestion engine and one unified dashboard.

Overview

Everything runs on Google Cloud, fully defined in Terraform: Cloud Run Jobs for scheduled ingestion and dbt runs, a Cloud Run Service serving a live marimo analysis dashboard, a Cloud SQL Postgres instance (reached via the Cloud SQL Auth Proxy sidecar), and Secret Manager for all third-party API credentials. Cloud Build handles deploys on push, and Cloud Scheduler/Workflows trigger ingestion-then-dbt runs on a schedule.

Ingestion engine — the generic API wrapper

Rather than writing a bespoke integration per API, a single execution engine runs every source from YAML-declared endpoint specs that get bootstrapped into the database as config, not left as static files. A run flows through the same pipeline regardless of source: load each API’s config and endpoint rows into an ApiRegistry/ApiClient, hand them to an ExecutionEngine that sorts by declared execution order, resolve any {placeholder} values in the path template against the current run’s context, fire the request through a shared async HTTP client, extract records from the response via a dotted response_path (with fallbacks for common wrapper keys like items/data/results), and write the result to Postgres.

What makes a source “just configuration” rather than new code is how that engine dispatches four distinct request shapes from the same endpoint schema:

  • Static params — a fixed request, no dependency on prior data.
  • Id-driven batch fetches — the id list comes from a column already written by an earlier endpoint (e.g. Spotify’s track/album/artist lookups running off ids seen in recently_played), batched into a handful of requests instead of one per id.
  • Single-id detail lookups — one request per id, for APIs with no batch endpoint (Trakt’s per-title movies/{id}/shows/{id} detail calls).
  • Chunked date-range requests — a list of full request bodies, one per time window, run sequentially (Google Health’s daily rollups, which hit an undocumented max-duration-per-request limit on long backfills).

A refetch_if_null flag on top of the id-driven case makes the engine self-healing: a row stored before its enrichment data existed (a missing album cover, a missing poster) gets quietly re-fetched on a later run instead of staying incomplete forever. Pagination is handled generically too — offset/limit, response-header page counts, and next-link following are all supported, with 429s retried against Retry-After before an endpoint is skipped for that run rather than failing the whole job.

Auth is the one place sources genuinely differ: OAuth refresh-token flows for Spotify/Trakt/Google Health versus a static bearer token for Hardcover. Whichever it is, the first seed comes from .env, but every refresh after that — a rotated OAuth token, a resolved Hardcover user id — gets persisted straight back into the database, which is the source of truth from then on.

See the individual pipelines for source-specific detail:

  • Spotify ingestion — recently played tracks, top artists/albums, enriched with images and popularity
  • Trakt ingestion — watch history, ratings, and watchlist, with poster art pulled from per-title detail endpoints
  • Hardcover ingestion — books read, currently reading, and want-to-read, via Hardcover’s GraphQL API
  • Google Health ingestion — daily steps, distance, calories, and active minutes (the API that replaced Fitbit’s, migrated onto Google’s own OAuth infrastructure)

Modelling

dbt sits on top of the raw spotify/trakt/ hardcover/health schemas with a strict three-layer convention:

  • Staging (materialized as views) — one thin model per raw table, renaming and casting but not reshaping. Most are plain passthroughs; a few do just enough to be usable downstream, like Hardcover’s staging models pulling book_image ->> 'url' out into a real cover_image_url column, or Google Health’s stg_active_minutes unnesting a JSONB array of per-intensity-level readings into a single daily total.
  • Intermediate (views) — used where a source needs real joins or aggregation before it’s shaped like a dimension or fact: Spotify’s int_track_enriched joins track/artist/album and computes rolling 7/30/365-day play counts per track; Trakt’s int_movies/int_shows dedupe watch history against per-title detail enrichment, and int_show_watch_stats rolls episode-level watches up to the show level. Hardcover and Google Health skip this layer entirely and go straight from staging to marts — their raw shape is already close enough to a fact table.
  • Marts (materialized as tables) — the dimension/fact models each source’s dashboard section actually queries: dim_tracks/dim_albums/ dim_artists and fct_track_stats/fct_artist_stats/fct_play_history for Spotify; dim_movies/dim_shows/dim_anime/dim_genre_map and fct_watch_history/fct_watchlist/fct_genre_stats for Trakt; dim_books and fct_reading_history/fct_reading_list/ fct_reading_stats for Hardcover; a single fct_daily_activity for Google Health, built by full-outer-joining all four metrics’ dates together first so a metric that’s still mid-backfill doesn’t silently drop days from the others.

Every mart-layer key is covered by generic dbt tests (unique, not_null, relationships back to its dimension, accepted_values for booleans/enums) rather than bespoke SQL assertions. There’s deliberately no dedicated cross-source mart joining all four together — the marimo dashboard’s combined view reads each source’s marts independently at the Python layer instead, which keeps the dbt layer simpler at the cost of doing the cross-source join in the notebook rather than the warehouse.

The dashboard itself shows listening habits, watch history, reading stats, and fitness trends — including year-over-year comparisons and day-of-week breakdowns — all refreshed on every scheduled ingestion-then-dbt run.

📊 View Dashboard