# Crystal 2 — handoff State as of 16 August 2026. Written so the work can be picked up cold. Sections 1 and 7 are the two that matter most: what it actually scores, and what is knowingly unfinished. --- ## 1. What the product claims, and what it actually scores Crystal estimates the audience for a broadcast in a market that may never have shown that event. The method is **case-based, structured-analogy forecasting with multiplicative decomposition and empirical prediction intervals** — see `Crystal_Method.pdf` for the literature it maps onto. Two hold-out experiments, run by `worker/fit_model.py`: | Experiment | What it tests | Result | |---|---|---| | Hold-one-row-out (1,200 rows) | a broadcast removed from its own evidence | WAPE 0.65 · median APE 0.67 · within ±50%: 37.5% | | **Leave-one-market-out (117 pairs)** | **every** row for an (event, country) removed | **WAPE 1.17 · median APE 0.89 · within 2×: 27% · σ(log) 2.45 · bias +0.96** | The second is the honest test — 101 of 117 resolved through tier 4. The first flatters the engine because 1,108 of 1,200 sampled rows never left tier 1. **Read the bias.** `bias_log_mean = +0.96` means truth averages **2.6× higher** than the estimate. A shrunk correction of 2.89 at tier 4 offsets it, but a bias that large indicates the functional form is wrong, not merely miscalibrated. That is the single most important open question. --- ## 2. Measured, not assumed `worker/fit_model.py` writes `reference_cache/calibration.json`. The engine prefers it and reports which it used via `ReferenceData.calibration_source`. | Component | Was | Now | |---|---|---| | hour-of-day curve | invented constants in `world.py` | measured, within-stratum ratios, 4,179+ groups/hour | | weekday curve | invented (Sat/Sun 1.15–1.20 vs Mon 0.90) | measured (0.95–1.00) — the invented swing was ~6× too strong | | exponent on TV universe | assumed 1.0 | **0.190** (se 0.005) | | exponent on channel share | assumed 1.0 | **0.500** (se 0.005) | | exponent on sport affinity | assumed 1.0 | **0.558** (se 0.010) | | per-tier σ | assumed 0.18–0.50 | measured **1.47 / 1.34 / 1.50 / 2.45 / 1.50** | | per-tier bias correction | never computed | **0.96 / 1.06 / 0.75 / 2.89 / 0.46** | 3,596 cells, 853 events, weighted least squares with event fixed effects absorbed. Against β = 1 the t-statistics are 160, 100 and 42 — the proportional-scaling assumption is decisively rejected. Two reference tables moved from "loaded but dead" into use, both by the viewership report rather than the estimator: `ott_multipliers` (273 markets — linear-to-OTT and out-of-home) and `pan_mapping` / `pan_channels`. Both are in the snapshot; `build_cache.py` does not yet fetch them, so a rebuilt snapshot needs them added or the platform sheets fall back to zero. **Broadcast hours joined this list.** It was an assumption of one hour per telecast wherever the frame carried no `duration` column — which the detailed export does not. `broadcast_hours()` in `engine/vr_report.py` now measures it from `start_dt`/`end_dt`, adding a day where a programme crosses midnight and discarding spans outside (0, 24] as parse artefacts. On the Tokyo run that is **3,051 hours against the 2,310 the assumption gave** — 1.32h per telecast, not 1.00. The figure always ships with the note saying which of the two it did, so an assumption can never be read as a measurement. --- ## 3. Decisions taken, and why - **Analog market chosen by resemblance, not row count.** `.mode()` selected on broadcast volume, which made South Africa the analog for every World Cup on earth (2,101 rows, median 18k). Now: `|ln(affinity ratio)| + ½|ln(GDP ratio)| + ¼·[different sub-region]`, lowest wins, with a row floor. - **Wikipedia URLs are pre-filled, not asked for cold** (user's call). A wrong guess is cheaper to correct than a blank box is to fill. - **Enrichment never blocks the estimate** (user's call). Confirming fixtures queues a *fresh* run rather than editing the first, so both are kept. - **All fixtures are kept on an ambiguous date** (user's call). The nearest by kickoff time is attached; teams are never blanked. - **Nothing enters the database without a human looking at it** — the pattern taken from `crystalcast/index.php`. Fixtures are previewed and editable before they are committed. - **Two workbooks, not one.** The VR book mirrors the sheet names an analyst already builds by hand, so it drops into the existing process; the summary book is the shorter thing you forward. Neither replaces the other. - **Pan feeds are matched, not guessed.** Against `tvviewers.pan_mapping`, 3,965 rows. Guessing from channel names found 2 feeds where 8 existed and would eventually misfile a national channel as pan. A pan feed covers many markets from one signal, so it is never added into a single-market total. - **No empty columns.** The hand-built book carries a Reach column with nothing in it on all 2,359 rows. An absent method should not look like a missing number, so the column is omitted and Method says why. - **The event name goes on every sheet.** Sheets get copied out of books. --- ## 4. Bugs found and fixed — keep this list, several will recur **Made the app work at all** 1. `config.php` did not exist. The entire PHP app was dead. 2. Worker had never run. Its systemd unit pointed at `/var/www/html/crystal/worker`, a directory that does not exist. 3. `from_fixtures()` iterated a hardcoded 8-name list and loaded 8 of 14 frames, so **affinity was silently dead in cache mode** while working in mysql mode. **Estimator correctness** 4. `FIFA World Cup 2026` — a **4-row stray** in global_sports — outranked `FIFA World Cup` (25,934 rows) because the resolver preferred the longest alias. 2,623 EPG rows attached to it and became unestimable. Fixed with evidence-weighted resolution (a candidate needs ≥1% of the best candidate's rows). 5. Tiers 1 and 3 accepted **a single row** and scored it 92 confidence. Pakistan's entire market total rested on 2 rows while India's 5,791 rows produced a 15× lower number. Fixed with `MIN_TIER_ROWS = {1:3, 2:3, 3:5, 4:2, 5:3}`. 6. Tier 3 took one median across **all 36 channels** in a market, then moved it with a single share ratio. Now pools on the nearest channel by day-share. 7. Confidence moved 6 points across a 5,000× difference in evidence. Replaced with `_depth_adjust`. 8. σ clip of 1.50 silently capped the measured 2.45 — the exact failure the calibration existed to remove. Clip raised to 3.00. **EPG ingestion** 9. `H/L` — the feed's dominant highlights marker, 22% of rows — was missed because the slash breaks `\bhl\b`. 10. `SPORT_HINT` had no football markers and no Spanish/Portuguese. `COPA DO MUNDO FIFA 2026` classified as `NON_EVENT` — a *confident wrong answer* that never reaches review. 11. Digit tokens collided: `Formula E: Tokyo E-Prix - Race 1` matched the alias `Formula 1`, mislabelling **1,338 rows**. Aliases containing a digit now require a contiguous phrase match. 12. The production GSIQ export was rejected outright — it names the country and start time differently. Fixed with `COLUMN_ALIASES`; both vocabularies now work. 13. `RECORDED_ARCHIVE`, `REPEAT`, `OTHERS` fell through to the UNKNOWN multiplier. **Output** 13b. Tier 3 could return a tier, no flag and a **silent NaN** estimate — 255 of 2,310 rows on one Formula E run, about a fifth of the book. The row looked answered: it had a strategy and no warning, but no number, so the OTT and OOH sheets that multiply it came out a fraction of their true size. Fixed by the precomputed index (medians are filtered finite before they are stored). Worth keeping because the symptom pointed at the wrong place entirely: OTT looked broken when the base audience was the thing missing. 14. `json.dumps` emitted bare `NaN`, which is **invalid JSON**. `JSON.parse` threw and the analytics page rendered blank with no error. Fixed with `_json_safe` + `allow_nan=False` so it now fails loudly instead. **Output, continued** 18. **The VR book would not build for a file with no pan feeds.** The pan sheet's empty-state branch referenced `f["sub"]`, a format key that does not exist — and that branch had never executed, because every file tested until the Formula E London grid contained pan feeds. The estimate ran, 345 rows and all, and the workbook silently fell back to a plain export. `run_pipeline` wraps the formatted write so a formatting error never loses a run; the cost is that it never announces one either. `worker/check_reports.py` now builds both books against every run on disk and statically checks that every `f["key"]` exists — run it after touching either report module. **The two books have separate format tables** (`vr_style.build` and `excel_report._formats`); checking every key against one reports two dozen false failures. **Operational** 15. **Python module caching**: the worker served the code it imported at boot, so two separate fixes appeared not to work. `run_estimation()` now drops `engine.*` from `sys.modules` each run. Do not remove this. 16. **`make_report.py` cached exports against the data file alone.** A change to the palette or the layout rebuilt nothing — `build()` returned the previous workbook because the detailed frame had not moved. This is the *second* form of the stale-artefact trap in §5c and it cost another round of "the fix did not work". The cache key now includes the mtimes of `vr_report.py`, `vr_style.py` and `excel_report.py`. **If you add another module that affects how a workbook looks, add it to that list.** 17. **Numbers added to the chart payload do not appear on old runs.** `*_chartdata.json` is written once, at the end of a run. A tile reading a key that predates the run renders nothing — no error, just an absent tile. `worker/refresh_chartdata.py` rebuilds the payload from the detailed workbook; run it after adding any headline figure. --- ## 5. Performance — what made it fast | Stage | Before | After | |---|---|---| | reference load | 220s per run | 0.8s (parquet snapshot) | | EPG reference lookups | 85s per run | 0.03s (cached) | | EPG event resolution | 71.6s | ~0.5s | | evidence index build | 14.3s per run | 0.0s (3 MB pickle) | | **estimate, per row** | **0.583s** | **0.00013s** (4,352×) | Three things did it: - **`SequenceMatcher` was 16.6 of 18 seconds** — 458,062 fuzzy comparisons for a *10-row* schedule, because tier 2 scanned every LIVE row in the market (India: 228,000). Now compares only distinct team strings sharing a token, memoised. - The de-based value of an evidence row doesn't depend on the target, so it is computed once for all 5.9M rows and group medians are precomputed. `estimate_row` receives plain dicts of floats; **no pandas in the hot path**. - Event names are resolved per *distinct title* (33) rather than per row (5,421) — that was ~29M tokenisations to answer 33 questions. Note: enrichment made the old engine *slower*, because adding teams to every row is what switched the fuzzy scan on. --- ## 5c. Comparing against a hand-built workbook Two things cost time and will again: - **An export on disk is a snapshot of the engine at that moment.** A file written yesterday afternoon carries yesterday's bugs. When a number looks wrong, re-run the input through the current engine before diagnosing; twice now the fault was already fixed and only the artefact was stale. - **Charts do not appear in a cell dump.** They live in `xl/charts/` and `xl/drawings/` inside the zip. `unzip -l file.xlsx | grep -i chart` finds them; openpyxl cell reads never will. The reference workbook's line chart was missed on the first comparison for exactly this reason. - **Extract styling, never match it by eye.** openpyxl gives fills, fonts and sizes per cell (`c.fill.start_color.rgb`, `c.font.color.rgb`, `c.font.sz`); chart styling only comes out of the XML: python3 -c "import zipfile,re; x=zipfile.ZipFile('f.xlsx').read('xl/charts/chart1.xml').decode(); \ print(sorted(set(re.findall(r'` block specifically. **An absent `` means the theme default, not "no colour"** — their line looked like a deliberate blue and was simply never styled. Verified parity against the analysts' own workbook on the same EPG: 125 markets, 533 channels, 2,310 telecasts, 2,272 estimated, 21.64M against their 21.7M, and OTT/OOH matching to the cent (Australia 181.91 / 62.37 both). ## 6. The two workbooks engine/excel_report.py summary book — Summary / Estimates / Markets / Method engine/vr_report.py VR book — the ten-sheet tournament book engine/vr_style.py formats only, kept apart from the numbers worker/make_report.py builds either on demand: make_report.py "" [client|vr] `download.php?f=client|vr|detailed` serves them. Both presentation books are built on demand and cached against the detailed workbook's mtime, so a run estimated before they existed still downloads as something presentable (~1.9s cold, ~10ms warm). The formatted write is wrapped: a formatting error falls back to a plain export rather than losing the run. Every sheet carries a brand band with the event name, a sparkline trend per market, banded rows, pre-applied filters, and print setup (landscape A4, fit to width, header repeated per page). The Method sheet in both books states the fitted parameters, the measured accuracy, and — in red — what is *not* applied. ### 6b. The palette is the analysts', not ours Both books were indigo. They are now the teal of the workbook the analysts settled on, extracted from `/home/joseph/friends_sample.xlsx` rather than matched by eye. **Do not "restore the GSIQ indigo" in `vr_style.py`** — the divergence from the web dashboard is deliberate, so the two books sit beside their own without a seam. | | Value | |---|---| | header bands, table heads | `#177F78` | | subtitle strip | `#E7F3F1`, text `#1F2937` | | body text | `#1F2937` | | rules | `#CCCCCC` / `#D9D9D9` | | Summary band | 21pt; every other sheet 13pt | | chart gridlines | dashed `#CCCCCC`, 0.75pt, both axes | | chart frame | `#D9D9D9` | | chart series | no markers, smoothed, 2.25pt | One deliberate difference: **their line is Excel's theme blue only because no colour was ever set on it** — their `<c:ser>` has no `spPr` at all. Ours carries the band teal so the chart belongs to the book. If someone asks why the line does not match, that is the answer, and it is a one-line change. Their own sheets vary the small type between 9, 10 and 12pt with no consistent rule; ours sits at 9.5 throughout. Left alone on purpose. ### 6b-ii. The Summary is two blocks, and hours are hh:mm:ss Matched to the revised sample (`/home/joseph/NEW format …_FINAL.xlsx`), which is this workbook restructured rather than a different palette — the teal in §6b was already right. - **Summary carries two blocks side by side.** Audience & Coverage in A:C, Broadcast Coverage in D:E. A reader answering "how big" and one answering "how much of it" are not scanning the same column. - **All four hours figures are `[h]:mm:ss`** — TV, OTT, OOH, global. So is every hours cell on `Broadcast_Hours_Calc`. `[h]` and not `h`, or a 3,051-hour total rolls over at 24 and reads 6:45:00. - **Mean confidence is `0.0%`**, so 77.7% rather than a bare 77.7 that could be anything out of anything. - **`Broadcast_Hours_Calc` is a new sheet**: hours per telecast, so the Summary total is a visible SUM rather than an assertion. Where the feed gave real times the row says `measured`; where it did not it falls back to `ASSUMED_HOURS` / `ASSUMED_BY_ROUND` — the same table the analysts' book applies to *every* row — and says `assumed`. On the Tokyo run all 2,310 are measured, totalling 3050:45:00, equal to `broadcast_hours()` and so to the dashboard tile. - **Hours cover every telecast, not only the estimated ones.** A broadcast we could not put an audience against still occupied the airtime. - **The Summary chart plots TWO series** — TV, and OTT + OOH combined — with a legend underneath, titled "Audience by Broadcast Day". A chart series cannot sum two ranges, so the per-day OTT+OOH sum is written as formulas to a helper row in hidden columns G onward, well below the visible rows. Formulas rather than literals so the line follows the pivots; cached values written beside them so a reader who has not recalculated does not see a flat line at zero. **Checking a chart with `re.search` finds only the first `<c:ser>`** — use `re.findall`, or a second series goes unnoticed, which is how this was missed on the first pass. - **Every span of time in the book is `[h]:mm:ss`**, including the per-telecast Duration column, which was `[h]:mm` and read "0:57" beside an hours total reading "3050:45:00" — two different units to the eye. Start and end timestamps stay `yyyy-mm-dd hh:mm`: those are clock times, not durations. **Confidence is on every sheet that has data lines.** The pivots carry a mean per market, `AMA_BY_COUNTRY_BY_DAY` a mean per market-day, `Audience_by_Program` and `Broadcast_Hours_Calc` the per-telecast score. A grid of audience figures with no indication of which rows are well evidenced invites a reader to treat them alike, and on this model they are not. ## 6c. Headline figures are computed once Total AMA and broadcast hours are computed in `_build_chart_payload()` and land in `*_chartdata.json` under `kpi`. Everything downstream reads them from there: engine/pipeline.py computes the kpi block public/api/status.php reads the file, passes the WHOLE block through public/run.php tiles on the run page public/assets/analytics.js tiles on the full-analytics page engine/vr_report.py Summary sheet, same two helpers Both pages show the same twelve tiles — rows, estimated, markets, channels, TV / OTT / OOH / global audience, broadcast hours, median, confidence, flagged — which is exactly two full rows of six at the grid's widest breakpoint. `status.php` passes the entire `kpi` object rather than a hand-listed subset. Enumerating keys there means every new figure needs adding in two places, and the one that gets forgotten fails as a tile that silently never appears. OTT and OOH come from `platform_totals()` in `vr_report.py`, which applies the same filters the OTT_AMA_By_Day and OOH_AMA_By_Day pivots do — rows with an estimate, **pan feeds excluded**, times each market's own multiplier. Verified equal to the sheets to the cent (6,994.53 and 2,786.61). The detailed export carries no pan column, so the helper derives it from `pan_mapping`; skipping that exclusion would count a pan feed's whole footprint and put the tile above the sheet it is meant to match. This is deliberate. Two code paths each producing "total audience" is two numbers to reconcile in front of a client, and the row-sum in `analytics.js` had already drifted from the workbook. That sum survives only as a fallback for runs whose payload predates the key. Hours are whole **on the tiles**. Half an hour of coverage is not a distinction anyone acts on there, and a trailing `.0` on a tile reads as unfinished — the same rule that governs the workbooks (see §5c on `#,##0.##` printing "125."). The **VR book's Summary sheet is the one exception**: it renders the figure as `[h]:mm:ss` (3050:45:00, not 3051), because that is the one number people reconcile against a rights schedule, and a rights schedule is written in clock time. `broadcast_hours()` therefore returns the value UNROUNDED and each caller decides — round it in `pipeline.py` for the tiles, divide by 24 for Excel, which counts time in days. `[h]` and not `h`, or a 3,051-hour total rolls over at 24 and reads as 6:45:00. Tile layout: the run page grid is four-up while the run is in flight and six-up once the two audience tiles arrive, toggled by the `four` class. ## 6d. Rights-grid PDFs `worker/pdf_to_epg.py` converts the PDF a rights team publishes into an EPG Crystal can read. That document is the TRANSPOSE of an EPG: one row per (territory, platform, partner), one column per session, channel in the cell. Each non-N/A cell becomes a telecast. python3 worker/pdf_to_epg.py grid.pdf out.xlsx \ --event "2026 London E-Prix" \ --competition "Formula E World Championship" \ --sessions '{"Race":"2026-08-17T14:03Z", ...}' # UTC Four things learned building it, all of which will recur on the next grid: - **Parse on word coordinates, not `extract_text()`.** A wrapped cell comes back with its characters woven into its neighbour's, and a wrapped line is letter-spaced one character per word. Anchor each row on its Linear/Digital token — a long territory name is wide enough to cross the Platform column and a fixed x split drops the row entirely (20 of 188 lost that way). - **`--competition` is not `--event`.** Crystal resolves against its evidence base, where the series is "FIA Formula E World Championship". Pass the round name alone and every row comes back unestimable — 18 of 373 estimated. - **`"SuperSport 1 / 2 / 3"`** splits into channels literally named "2" and "3" unless the stem is carried across. - **There are no session times in the grid.** Live sessions happen at one instant worldwide, so `--sessions` takes UTC and each territory is converted to its own clock. Times that DO appear in cells ("Delayed 23:00", "18/8 - 21:00") are already local and are used as-is. There is no default — an invented session time would reach an audience figure unchallenged. Measured on the S12 London grid: 188 rows, 61 territories, 644 telecasts, **345 of 373 estimated (92%)** after de-duplication. The 28 refusals are four pan footprints Crystal has no TV universe for (Pan-Europe, Pan-APAC, Pan-SE Asia, North America) — left refused rather than mapped onto a near-neighbour, which would inflate the universe instead of leaving a gap. ## 7. Open — in priority order 1. **Tier-4 bias of 2.89.** Too large to be calibration. The functional form is suspect. The literature answer is a log-linear model with partial pooling (country → sub-region → global); Crystal's factor set is already the feature matrix for it. Median APE 0.89 is the number to beat. 2. **The stage/round weight is not applied.** `match_level_weights` is loaded and never read, so a World Cup Final scores the same as a group match — even though Wikipedia enrichment now supplies the round. Highest-value small fix. 3. **Four frames still loaded and never read**: `event_country_interest_factor` (20,227 rows), `event_country_broadcast_intensity_factor` (50,513), `global_sports_ama_caps`, `event_country_tvuniverse_factor`. (`ott_multipliers` and `pan_mapping` were in this list and are now used.) 4. **Fixtures merge onto ARCHIVE rows.** The join is by (event, date), so `T20 WORLD CUP CLASSICS` inherits that day's fixture. Restrict the merge to LIVE / HIGHLIGHTS / PRE_POST. 5. **CBR "retain" is not implemented** — `run_overrides` is stored and never fed back into the case base. 6. **Not installed (needs sudo):** the systemd unit and the nightly cron. Until then the worker survives a session but **not a reboot**. 7. **`build_cache.py` does not fetch `ott_multipliers`, `pan_mapping` or `pan_channels`.** They were cached by hand. Rebuild the snapshot without adding them and the OTT, OOH and pan sheets silently go to zero. Add them to `from_mysql()` and the cache builder before the next nightly run. 8. **Reach is not produced at all.** A genuine unique-people figure needs the duplication factors sitting unused in `event_country_audience` (`dupliation_adj`, `source_dup_factor`, `diff_dup_factor`). Until then every total is viewer-broadcasts. --- ## 8. Runbook **Step-by-step install, with what to verify after each step and the two ways it fails: `worker/INSTALL.md`.** Short version: # first install -- stop the hand-started worker FIRST, or two workers # poll the same table and both claim the same PENDING run pkill -f "python3 worker/worker.py" sudo cp worker/crystal-worker.service /etc/systemd/system/ sudo systemctl daemon-reload && sudo systemctl enable --now crystal-worker # nightly -- runs as www-data on purpose, see below sudo cp worker/crystal-nightly.cron /etc/cron.d/crystal sudo chown root:root /etc/cron.d/crystal && sudo chmod 644 /etc/cron.d/crystal `/etc/cron.d` silently ignores a file that is group- or world-writable, so that `chmod` is not decoration. Run `build_cache.py` **as `www-data`**. It creates a new snapshot directory and swaps it into place, so whoever runs it owns the result; run it as a human and the worker loses write access to `reference_cache/` and pays a 14s index rebuild on every run — silently, because that write is wrapped in a try/except. The script now forces group `www-data` and the setgid bit either way, but running it as the consuming user is the thing that cannot go wrong. `build_cache.py` also needs `vendor/` on `sys.path` for pyarrow and sqlalchemy. It was the one script of the three that did not add it — as a 4am cron job that is an ImportError nobody sees and a snapshot that quietly stops refreshing. # after adding a headline figure to the chart payload python3 worker/refresh_chartdata.py # every run on disk python3 worker/refresh_chartdata.py <RUN> … # named runs `build_cache.py` stages to a temp directory and swaps atomically, and deletes `evidence_index.pkl` so it rebuilds against the new snapshot — a stale index silently serves the previous night's medians, which is the worst kind of wrong. `refresh_chartdata.py` recomputes `*_chartdata.json` from the detailed workbook and swaps it into place. It never touches the estimates and is safe to re-run. All 24 runs on disk were refreshed on 2026-08-17 when the audience tiles landed. **Writing exports as a human.** `exports/` is `www-data:www-data` and group writable, but individual files are mode 644 and owned by whoever wrote them. Rebuilding a workbook the *worker* wrote fails with EACCES for a shell user even though they are in the `www-data` group. Build to the scratchpad to inspect output, and let the web app rebuild the real artefact. **Secrets:** `worker/config.json` holds an Anthropic API key (mode 640, group `www-data`). `config.php` holds DB credentials. Both live outside `public/`. Keep them out of shared backups. **Live URLs** new.php upload and estimate run.php progress, then results results.php full analytics enrich.php Wikipedia fixture enrichment Test files: `public/demo_epg_cricket.xlsx`, `public/demo_epg.xlsx`, and the production-format `Tokyo E-Prix_005.xlsx`.