Trakt Ingestion

One of four sources feeding api_warehouse’s personal data platform. Trakt is the source with the most moving parts: eight config-driven endpoints, a poster-art workaround, and a migration history that reads like a log of real bugs hit along the way.

Endpoints

Eight endpoints, run in order: watched_movies and watched_episodes (history), movie_details and show_details (per-title enrichment), watchlist_movies and watchlist_shows, and movie_ratings/show_ratings — which don’t get their own tables, they patch rating columns onto the rows the history endpoints already wrote.

The poster-art problem

Trakt’s history endpoints stopped returning image data even with extended=full. The only place poster/fanart images still show up is the per-title movies/{id} and shows/{id} detail endpoints — and Trakt’s API has no batch lookup for those, so it’s one request per unique id.

The engine handles this the same way it handles Spotify’s enrichment: source_table/source_column pull the distinct ids already stored from watch history, and a refetch_if_null: images_poster flag means a title that was stored before its poster was captured gets quietly re-fetched on a later run rather than staying incomplete forever.

Migration history

The trakt.* schema’s migration log doubles as a record of what Trakt’s API actually does versus what you’d assume:

• trakt_bigint_ids — history_id/watchlist_id had to be widened from int to bigint almost immediately; Trakt’s ids overflow a 32-bit column.
• trakt_extended_fields, trakt_personal_movie_ratings, trakt_personal_show_ratings — added episode metadata and my_rating/my_rated_at columns as the schema caught up to what was actually being ingested.
• add_trakt_movie_show_details_tables + refetch_if_null — built together, since the detail tables and the self-healing refetch mechanism are two halves of the same feature.
• add_generated_trakt_id_columns — added generated trakt_movie_id/trakt_show_id columns on the raw history tables so detail endpoints could query them directly, reusing a pattern already established elsewhere in the warehouse.
• split_trakt_images_column — the engine’s generic record-flattening flattens one level of every nested dict, including images, so poster data ends up pre-split into images_poster, images_clearart, etc. rather than staying one JSON blob.

Auth

A one-time interactive OAuth flow spins up a local HTTP server on the redirect URL’s port, opens a browser to Trakt’s authorize page, and catches the code param off the callback to exchange for tokens. Ongoing token refresh is handled the same way as the other OAuth sources.

dbt modelling

• Staging — one thin model per raw table (stg_watched_movies, stg_watched_episodes, stg_watchlist_movies, stg_watchlist_shows, stg_movie_details, stg_show_details).
• Intermediate — int_movies and int_shows dedupe watch history against detail enrichment (distinct on the Trakt id); int_show_watch_stats rolls episode-level watches up into per-show totals — episodes/seasons watched, total runtime, first/last watched, rolling 7- and 30-day counts.
• Marts — dim_movies and dim_shows (the latter carrying a content_type column that also classifies anime), dim_anime (a simple filter over dim_shows), dim_genre_map (unpivots each title’s genre array into one row per genre, movies and shows unioned into a shared shape), fct_watch_history (movie and episode watch events unioned into one fact table), fct_watchlist, and per-entity stats marts (fct_movie_stats, fct_show_stats, fct_genre_stats).

Engineering notes

The bigint-overflow migration is a good reminder that “it’s just an id” is a dangerous assumption — and the images-column split shows how a generic transformation (flatten-one-level) can produce a schema shape you didn’t design for, only discovered once real payloads hit it.