boxoffice-index

Movie box-office indexing tool. Pulls data from The Movie Database (TMDB) API v3, stores it in PostgreSQL, identifies box-office draws (actors repeatedly credited in the highest-revenue films of each year), and supports JSON export/import for backup and transfer. The data and ranking on draws.moviesand.me are produced from this pipeline.

For Docker usage see DOCKER.md. For coding conventions see PYTHON_STYLE.md. For architecture notes aimed at contributors see CLAUDE.md.

---

What is a box-office draw?

A draw is an actor whose presence in a film is likely to attract more viewers than average. This project operationalizes that by counting how often an actor appears in the cast of the top ten highest-revenue movies released in a given calendar year (using revenue stored locally from TMDB).

An actor qualifies as a draw once they meet the minimum appearance threshold (default 3 top-movie credits) across the year range you query. Draw records, per-year counts, qualifying movie links, and multi-year streaks can be persisted with boxidx_draws --update.

---

Prerequisites

• Python 3.11+
• PostgreSQL (local or via Docker Compose — see DOCKER.md)
• A TMDB API v3 key from themoviedb.org/settings/api

---

Quick start

cp .env.example .env
# Edit .env: set TMDB_API_KEY and DATABASE_URL

pip install -e ".[dev]"

boxidx_schema_create
boxidx_preload_data

boxidx_movie_load 2023
boxidx_draws --year 2023
boxidx_draws --year 2023 --update

Typical workflow: seed schema and genres → load movies for one or more release years → query draws (display only) → persist draws when satisfied with the results.

For loading the full historical catalogue and exporting every year, see Full catalogue load and export.

---

Full catalogue load and export

Use this when you want a fresh database populated from TMDB and a complete ROOT/YYYY/ export tree (as under asset/export/). This is the TMDB → PostgreSQL → persist draws → export path, not the import-only path in development/static/generate_data.sh.

Prerequisites

Complete Quick start through boxidx_preload_data. PostgreSQL must be running and TMDB_API_KEY set. Expect many TMDB API calls (one discover page per release year, plus detail, credits, and person lookups per film and top-billed cast member).

Steps

flowchart TD
    A[boxidx_schema_create] --> B[boxidx_preload_data]
    B --> C["boxidx_movie_load 1925-"]
    C --> D[boxidx_draws --update]
    D --> E["boxidx_export_all ROOT"]
    E --> F["Optional: boxidx_draws_list --json-list"]

1. Schema and genres (once per database):

boxidx_schema_create
boxidx_preload_data

2. Load movies for every release year from TMDB:

boxidx_movie_load 1925-

The year argument accepts a single year (2023), a closed range (2020-2023), or an open range ending at the current calendar year (1925-). The earliest year with usable TMDB discover data in this project is 1925. Each year fetches the first TMDB discover page sorted by revenue.desc (see boxidx_movie_load).

Check progress with boxidx_year_list (movie counts per release year).

3. Persist box-office draws for all loaded years:

boxidx_draws --update

With no --year or --year-range, this uses the minimum and maximum release years present in movies, capped at the previous calendar year (the current year is never persisted). Draw rows, per-year counts, qualifying movie links, and streaks are written to PostgreSQL.

Preview first without writing:

boxidx_draws
boxidx_draws --json-list

4. Export every year that has draw data:

boxidx_export_all /path/to/export/root

Each year with boxoffice_draw_years rows is written to ROOT/YYYY/ (actor/, movies/, .exported_at). Years that have movies but no persisted draws are logged and skipped.

Example using the repo’s export tree:

boxidx_export_all asset/export

5. Optional — JSONL draw summaries for a static site or tooling:

boxidx_draws_list --json-list | sort >src/boxidx/data/draws_all.jsonl
boxidx_draws_list --alive --json-list | sort >src/boxidx/data/draws_alive.jsonl

(--alive filters to actors with no stored death date; the full list includes an is_alive field per row.)

Docker Compose

With the stack from DOCKER.md, run the same commands inside the app container:

docker compose run --rm app boxidx_movie_load 1925-
docker compose run --rm app boxidx_draws --update
docker compose run --rm app boxidx_export_all asset/export

Helper scripts under development/ wrap the draw and export steps:

Script	Command
development/run_boxidx_draws__update.sh	boxidx_draws --update
development/run_boxidx_export_all.sh	boxidx_export_all (pass output root as argument)

There is no wrapper for boxidx_movie_load; invoke it directly as above.

Notes

• Runtime — A full 1925- load can take hours depending on TMDB latency and rate limits. Use -v on any command for DEBUG logging.
• Idempotent reload — Re-running boxidx_movie_load for a year skips unchanged movies (digest match) but refreshes actor birth/death dates when cast is processed. Re-running boxidx_draws --update reconciles draw tables and streaks.
• Export vs import — boxidx_export_all reads the database. To rebuild draw JSONL files from an export tree without TMDB, use development/static/generate_data.sh (boxidx_import_all + boxidx_draws_list) instead.

---

End-to-end pipeline

flowchart LR
    TMDB[(TMDB API)]
    PG[(PostgreSQL)]
    JSON[Export folder]

    TMDB -->|boxidx_preload_data| PG
    TMDB -->|boxidx_movie_load| PG
    PG -->|boxidx_draws --update| PG
    PG -->|boxidx_export| JSON
    JSON -->|boxidx_import| PG

1. Reference data — boxidx_preload_data seeds the genres table from TMDB.
2. Movie ingestion — boxidx_movie_load discovers and persists movies, actors, and credits.
3. Draw analysis — boxidx_draws tallies top-movie credits; --update writes draw tables and streaks.
4. Export / import — boxidx_export writes a portable JSON tree; boxidx_import restores it.

---

Database overview

Table	Role
genres	TMDB genre reference (seeded by preload)
movies	One row per TMDB film; digest detects content changes
actors	One row per TMDB person; birth/death dates (nullable until filled from TMDB or export)
credits	Cast billing: links actor ↔ movie with TMDB credit_id and position
boxoffice_draws	One row per draw actor; lifetime top-movie count
boxoffice_draw_years	Per-calendar-year draw count for that actor
boxoffice_draw_year_movies	Which movies qualified the actor in that year
boxoffice_draws_actor_streaks	Contiguous year ranges of draw qualification

Movies store a single genre_id (first TMDB genre that exists locally). Only cast credits are stored; crew is ignored. Movie load keeps the top ten billed cast members per film.

---

boxidx_movie_load

boxidx_movie_load is the TMDB ingestion entrypoint. It discovers the highest-revenue films for one or more calendar release years, fetches full movie and cast data from the API, and persists movies, actors, and credits rows in PostgreSQL. It does not compute or persist box-office draws; run boxidx_draws --update after loading.

Implementation: src/boxidx/entrypoint/boxidx_movie_load.py.

Prerequisites

Run once per database before the first load:

boxidx_schema_create
boxidx_preload_data   # seeds genres — movie load resolves genre_id from this table

Set TMDB_API_KEY in the environment (see Environment variables).

Usage

boxidx_movie_load 2023              # single release year
boxidx_movie_load 2020-2023         # inclusive range
boxidx_movie_load 1925-             # from 1925 through the current calendar year

Each year is processed in order inside one database session. Use boxidx_year_list afterward to see movie counts per release year. Add -v for DEBUG logging (per-movie skip/upsert messages).

What gets written

Table	Written by movie load?	Notes
movies	Yes	Insert or update when digest changes
actors	Yes	Upsert from TMDB person API when birth or death date is still missing
credits	Yes	Top ten billed cast per film; insert-only (duplicates ignored)
boxoffice_draws	No	Use boxidx_draws --update
genres	No	Preloaded by boxidx_preload_data only

Movie load never creates credit stubs — that path exists only during draw export import when co-stars are inserted from movie credits JSON (see Import from export folder).

Discovery scope

TmdbClient.discover_movies requests only the first TMDB discover page for the year, sorted by revenue.desc. That caps ingestion to roughly the twenty most commercially successful releases TMDB returns on that page (every summary on the page is processed). This is intentional: draw analysis cares about association with the most successful films, not an exhaustive catalogue.

Workflow

flowchart TD
    START["boxidx_movie_load YEAR"] --> PARSE[Parse year or range]
    PARSE --> YEARLOOP{For each release year}
    YEARLOOP --> DISCOVER["GET /discover/movie<br/>page 1, revenue.desc"]
    DISCOVER --> MOVIELOOP{For each discovered TMDB movie id}
    MOVIELOOP --> DETAIL["GET /movie/id"]
    DETAIL --> REV{Revenue greater than 0?}
    REV -->|no| SKIP1[Skip: no revenue]
    REV -->|yes| CREDITS["GET /movie/id/credits"]
    CREDITS --> CAST{Cast present?}
    CAST -->|no| SKIP2[Skip: no credits]
    CAST -->|yes| GENRE[Resolve first matching genre<br/>from local genres table]
    GENRE --> DIGEST[Compute SHA-256 movie digest]
    DIGEST --> MOVIE{Movie exists with<br/>same digest?}
    MOVIE -->|no| UPSERT[Insert or update movies row]
    MOVIE -->|yes| UNCHANGED[Skip movie field update]
    UPSERT --> CASTLOOP
    UNCHANGED --> CASTLOOP
    CASTLOOP{For top 10 cast members}
    CASTLOOP --> VITAL{Actor has birth_date<br/>and death_date?}
    VITAL -->|yes| CREDIT[Insert credits row if new]
    VITAL -->|no| PERSON["GET /person/id<br/>birthday and deathday"]
    PERSON --> ACTOR[Upsert actors row]
    ACTOR --> CREDIT
    CREDIT --> CASTLOOP
    CASTLOOP -->|done| MOVIELOOP
    SKIP1 --> MOVIELOOP
    SKIP2 --> MOVIELOOP
    MOVIELOOP -->|done| YEARLOOP
    YEARLOOP -->|done| DONE[Done]

Per-movie behavior

Skipped films — Movies with zero reported revenue or an empty cast are skipped (DEBUG log only; they are not stored).

Genre — The first TMDB genre id on the film that exists in the local genres table becomes movies.genre_id; otherwise NULL.

Digest (boxidx.digest.compute_movie_digest) — SHA-256 over canonical JSON of tmdb_movie_id, title, original title, genre_id, release timestamp, revenue, and sorted TMDB credit_id strings. If an existing row’s digest matches, mutable movie fields are left unchanged; if the digest differs, the movie row is updated and updated_at_timestamp is set.

Cast — Only cast credits are stored (crew is ignored). Billing positions 0–9 (top ten) are kept. For each cast member, if the actor row already has both birth_date and death_date, the loader skips TMDB /person/{id} and reuses the stored row. Otherwise it fetches the person record and upserts birth_date and death_date with ON CONFLICT DO UPDATE on tmdb_actor_id. Living actors (no deathday in TMDB) are re-fetched on each appearance until a death date is stored.

Credits — Inserted with ON CONFLICT DO NOTHING on tmdb_credit_id (treated as immutable).

Idempotent reload

Re-running boxidx_movie_load for a year is safe:

• Unchanged movies (matching digest) are not rewritten, but cast processing still runs. Deceased actors with both dates stored skip person API calls; others refresh from TMDB.
• New or changed TMDB data updates the corresponding rows.
• Draw tables are unaffected until you run boxidx_draws --update.

---

Determining box-office draws

Command: boxidx_draws (query only) and boxidx_draws --update (persist).

Implementation: src/boxidx/entrypoint/boxidx_draws.py.

Algorithm

flowchart TD
    A[Choose year range] --> B[For each calendar year]
    B --> C[Select top 10 movies by revenue<br/>where release year = Y]
    C --> D[Collect distinct actor_id + movie_id<br/>from credits on those movies]
    D --> E[Aggregate per actor across years]
    E --> F{Appearances >= minimum?<br/>default 3}
    F -->|no| G[Exclude from results]
    F -->|yes| H[Display table or JSONL]
    H --> I{--update?}
    I -->|no| END[Done]
    I -->|yes| J[Upsert boxoffice_draws]
    J --> K[Upsert boxoffice_draw_years + year_movies]
    K --> L[Recompute actor streaks]
    L --> END

Year range defaults: if you omit --year and --year-range, the tool uses the min/max release years in movies, capped at the previous calendar year (incomplete current-year data is excluded).

Minimum appearances: applied at query time only; the threshold is not stored in the database.

Persistence (--update):

• Creates or updates boxoffice_draws with topbilled_in_successful_movie_count_all = distinct qualifying movies across the processed years.
• Creates or updates boxoffice_draw_years per year with per-year counts.
• Synchronises boxoffice_draw_year_movies (adds new links, removes stale ones with a warning).
• Rebuilds boxoffice_draws_actor_streaks from contiguous qualifying years; last_year = NULL means the streak reaches the most recent year in the run (still ongoing).
• Sets actors.first_boxoffice_draw_id to the draw row.

Guards: explicit --year / --year-range cannot extend past the last completed calendar year when persisting incomplete results would mislead streak logic.

Draw listing, per-actor detail, and year deletion are handled by boxidx_draws_list and boxidx_draws_year_delete.

---

Export

Commands: boxidx_export FOLDER YYYY (one draw year) and boxidx_export_all ROOT (every release year with draw data under ROOT/YYYY/).

Implementation: src/boxidx/draw_year_export.py (shared by both entrypoints).

Export flow

flowchart TD
    A[boxoffice_draw_years for year Y] --> B[Join draws + actors]
    B --> C[For each actor draw year]
    C --> D[Load linked movies via<br/>boxoffice_draw_year_movies]
    D --> E[Load credits for those movies]
    E --> F[Build actor JSON<br/>movie_tmdb_ids only]
    E --> G[Deduplicate movie JSON<br/>across all actors]
    F --> H[Write actor/tmdb_actor_id.json]
    G --> I[Write movies/tmdb_movie_id.json]
    H --> J[Write .exported_at UTC timestamp]
    I --> J

Folder layout

export/2023/
├── .exported_at              # single-line UTC ISO 8601 timestamp
├── actor/
│   └── {tmdb_actor_id}.json  # draw metadata + movie_tmdb_ids
└── movies/
    └── {tmdb_movie_id}.json  # title, genre, revenue, credits array

Actor file fields: version, year, actor identity, birth/death dates, draw counts, movie_tmdb_ids (sorted TMDB IDs — no embedded movie objects).

Movie file fields: tmdb_movie_id, title, name, tmdb_genre_id, released_on_timestamp, revenue, credits (each entry: tmdb_credit_id, order, tmdb_actor_id, actor_full_name).

If no boxoffice_draw_years rows exist for the requested year, boxidx_export fails with an error; boxidx_export_all logs and skips that year.

---

Import from export folder

Command: boxidx_import FOLDER (or boxidx_import_all ROOT for ROOT/YYYY/ trees).

Implementation: src/boxidx/draw_year_import.py.

This path restores draw snapshots plus the movies and cast needed to run draw queries again. It is separate from TMDB movie load: data comes from JSON files, not the API.

Import flow

flowchart TD
    A[Sorted actor/*.json files] --> B[Parse actor record]
    B --> C[Upsert actor from JSON envelope]
    C --> F[Upsert boxoffice_draws]
    F --> G[Upsert boxoffice_draw_year for file year]
    G --> H[For each movie_tmdb_id]
    H --> I[Read movies/id.json]
    I --> J{Movie row exists?}
    J -->|no| K[Insert movie + compute digest]
    J -->|yes| L[Keep existing movie row]
    K --> M[Replace all credits for movie]
    L --> M
    M --> N[Sync boxoffice_draw_year_movies]
    N --> O[Next actor file]

Per actor file (_import_record):

1. Upsert Actor from the JSON envelope on every import (refreshes name and birth/death dates). Credit-only stubs inserted from movie credits are upgraded when that actor’s draw file is processed.
2. Upsert BoxofficeDraw and BoxofficeDrawYear counts from the file.
3. Resolve movies from movie_tmdb_ids → movies/{id}.json (legacy layouts with root-level *.json or embedded movies arrays still work).
4. Insert missing Movie rows with digest derived from export fields and credit IDs.
5. Replace all Credit rows for each imported movie from the credits array (so cast matches export even when the movie row already existed).
6. Synchronise BoxofficeDrawYearMovie associations.

Prerequisites on a fresh database: run boxidx_schema_create and boxidx_preload_data first so genres exist for tmdb_genre_id lookup (genres are not created during import).

Round-trip: integration tests export two years, clear the database, import, re-export, and compare trees byte-for-byte except .exported_at.

---

Command reference

Command	Purpose
boxidx_schema_create	Run Alembic migrations
boxidx_preload_data	Seed genres from TMDB
boxidx_movie_load YEAR	Load movies/actors/credits from TMDB (2020-2023, 2015-)
boxidx_draws	Query draws (table or --json-list)
boxidx_draws --update	Persist draws and recompute streaks
boxidx_draws_list	List persisted draw records
boxidx_draws_year_delete YYYY	Remove one year's draw data
boxidx_export FOLDER YYYY	Export one year's draws to JSON
boxidx_export_all ROOT	Export every year with draws under ROOT/YYYY/
boxidx_import FOLDER	Import one export folder
boxidx_import_all ROOT	Import each ROOT/YYYY/ folder
boxidx_year_list	Movie counts by release year
boxidx_truncate_excess	Prune movies/credits outside configured limits

All entrypoints accept -v / --verbose for DEBUG logging.

---

Environment variables

Variable	Purpose
TMDB_API_KEY	TMDB API v3 key (required for preload and movie load)
DATABASE_URL	SQLAlchemy PostgreSQL URL

See .env.example.

---

Tests

pytest

Integration tests (real PostgreSQL) run only when opted in:

BOXIDX_INTEGRATION_TEST=1 pytest tests/integration/ -v

---

Key design decisions

• tmdbsimple wraps TMDB; HTTP/network errors become TmdbError.
• First page, revenue sort on discover limits catalogue size and focuses on commercially successful titles.
• Top ten cast per movie balances billing signal with API cost.
• Digest-based change detection avoids rewriting unchanged movies.
• Draw threshold at query time keeps stored counts as raw tallies; filtering is a display/persist gate.
• Export splits actor and movie files so shared films are stored once; import replaces credits to keep cast authoritative for draw queries.