# Crystal Engine — Live Database Wiring Handoff You (Claude Code) are running directly on the GSIQ server with access to the real `tvviewers` MySQL database. This document is your complete task list. Follow it in order. Do not skip the safety rules — this database is used by the live, currently-in-production Crystal system; nothing here should ever write to any `tvviewers.*` table. ## Server topology note MySQL lives on a SEPARATE server from this app server — you are connecting over the network via IP address, not localhost. Before Task 1, verify: 1. Network reachability: `mysql -h -u -p -e "SELECT 1"` from THIS server. If it hangs/times out, the firewall between the two servers is blocking the MySQL port (usually 3306) — flag this to Joseph rather than trying to open firewall rules yourself. 2. `db_host` in worker/config.json and config.php must be the DB server's IP address, not 127.0.0.1 or localhost. 3. If you get "Access denied," the MySQL user may be scoped to a specific host (e.g. `'crystal'@'10.0.1.5'`) that doesn't match this server's IP — ask Joseph or whoever administers the DB server to check `SELECT user, host FROM mysql.user;` and grant this app server's IP. 4. Never attempt to change firewall rules, security groups, or MySQL user grants yourself — surface the exact error and ask. ## Ground rules (non-negotiable) 1. **Read-only on `tvviewers`.** Every query against `tvviewers.*` must be SELECT only. Never run INSERT/UPDATE/DELETE/ALTER/DROP against `tvviewers`. Writes are only ever allowed to the new `crystal` application schema (estimate_runs, run_overrides) defined in `schema.sql`. 2. **Never print, log, or commit credentials.** Read them from `worker/config.json` / `config.php` (already gitignored). Never paste a password into a commit message, a comment, or a file you show the user. 3. **Never modify `estimate_pipeline.py` or any file under the original Crystal Django app.** That system stays live and untouched until a formal cutover decision is made by Joseph. This work only touches `crystal2/`. 4. **Everything here is testable before it's trusted.** After each table is wired, run the verification query listed for it and show the row count and a 3-row sample before moving to the next table. ## Important: schema mapping is PARTIALLY done already `engine/reference.py`'s `from_mysql()` has been rewritten using REAL column names extracted directly from the original Crystal source (not guesses) — each mapped table has a comment citing the exact original file/line it came from. This covers: global_sports, gl_ratings, tvuniverse, sports_team_weight, event_host_country_factor, event_country_tvuniverse_factor. This does NOT mean Task 1/2 are unnecessary — column names being right is not the same as knowing types, nullability, indexes, or whether the live table has since evolved from what the old code queried. Run Task 1/2 as written to CONFIRM this mapping against real `SHOW CREATE TABLE` output, fix anything that's drifted, then treat Task 3 as "extend this pattern to the remaining tables in table_inventory.md" (event_interest_factor, match_level_weights, event_fallback_mapping, map_country_map_channel, country_alias, pan_mapping, global_sports_ama_caps, and the rest) rather than starting from scratch. ## What already exists (built in a separate session, without DB access) - `engine/` — the estimation engine. Fully working against SYNTHETIC data (see `engine/world.py`). Proven on a held-out synthetic backtest at WAPE 3.4%, 97% hit-within-20%, 98% interval coverage. That number is NOT meaningful for real accuracy — it was measured against invented data. - `engine/reference.py` — has a `ReferenceData.from_mysql()` method that is a PLACEHOLDER. It maps only 3 tables (tvuniverse, gl_ratings, global_sports) with GUESSED column names. This is your primary target. - `worker/worker.py` — already switches from the demo engine to the real engine automatically once `worker/config.json` has `"reference_mode": "mysql"`. You do not need to change worker.py. - The full table inventory this maps to is in `handoff/table_inventory.md`. ## Task 1 — Get real schema (read-only, structure only) For each table in `handoff/table_inventory.md`, run: ```sql SHOW CREATE TABLE tvviewers.; ``` Save all output to `handoff/real_schema.sql`. This costs nothing and touches no data — do this for all 24+ tables before writing any Python. ## Task 2 — Sample real rows (read-only, 10 rows each, no PII concern — this is broadcast/ratings data, but still keep samples out of git commits) ```sql SELECT * FROM tvviewers. LIMIT 10; ``` Compare actual column names against what `engine/reference.py` currently assumes. Note every mismatch in `handoff/schema_diff.md`. ## Task 3 — Rewrite `ReferenceData.from_mysql()` for all tables Using the real schema from Task 1, expand `from_mysql()` in `engine/reference.py` to load ALL relevant tables from `handoff/table_inventory.md` — not just the current 3. Follow the existing pattern (`self.frames[name] = q("SELECT ... FROM ...")`). Add accessor methods on `ReferenceData` for any new frame the same way `team_w()`, `host_f()` etc. already work, so `engine/core.py` doesn't need to guess at column names directly. Do NOT change the shape of `engine/core.py`'s logic in this task — only wire data in. If a table's real structure genuinely requires a logic change, STOP and describe the issue rather than improvising a fix. ## Task 4 — Real backtest (this is the number that actually matters) 1. Pick a real historical date range from `tvviewers.global_sports` — e.g. the most recent 90 days. 2. Hold out the LAST 30 of those days as "unseen." Load everything before that as evidence. 3. Run `engine.pipeline.estimate_frame()` against the held-out rows (their real event/country/channel/telecast/time — but blind the model to their real ama_000). 4. Compare against their REAL ama_000 using `engine.pipeline.backtest()`. 5. Report WAPE, bias, hit-within-20%, interval coverage — and compare against Crystal's own historical accuracy if that number exists anywhere (ask Joseph if unsure where to find it). This is the number Joseph needs before any conversation about client use. ## Task 5 — Shadow run Take 5-10 REAL recent schedule uploads (ask Joseph where past uploads are archived, or use a fresh one). Run them through BOTH the original Crystal and this engine. Produce a side-by-side comparison workbook: row, Crystal's estimate, this engine's estimate, % difference, which strategy tier each used. Do not draw a conclusion about which is "right" — just produce the comparison for Joseph and the team to review together. ## Task 6 — EPG table integration (pull instead of upload) Joseph has an internal EPG table that refreshes daily and holds ~200 MILLION records. This is a NEW discovery, not in the original table_inventory.md — treat it with extra care because of its size. ### 6a. Discover safely, before writing any integration code ```sql SHOW CREATE TABLE ; -- get real column names + existing indexes SELECT COUNT(*) FROM WHERE = CURDATE(); -- sanity check row volume per day EXPLAIN SELECT * FROM WHERE BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY AND = 'India'; -- confirm this uses an index, not a full scan ``` STOP and report back if the EXPLAIN shows a full table scan (`type: ALL`) on a 200M-row table. That query pattern must never ship if so — ask Joseph whether an index can be added, or find the column that already has one. ### 6b. Build a narrow, safe fetch function Add `ReferenceData.fetch_epg_window(country, date_from, date_to)` in `engine/reference.py`. Hard rules: - MUST always filter by date range AND country/channel — never a bare `SELECT * FROM epg_table`. - MUST have a sane maximum date range (e.g. reject requests over 60 days) to prevent an accidental multi-million-row pull from the UI. - Runs as a genuinely read-only query (SELECT only, as with all other tvviewers access). - Add a query timeout (a few seconds) so a slow query fails loudly instead of hanging the dashboard. ### 6c. Add the "Pull from EPG" option to the dashboard In `public/new.php`, add a second option alongside the file upload: a small form with country + date range, calling a new `public/api/pull_epg.php` endpoint that inserts a PENDING run the same way `api/upload.php` does, but tags it with `source=epg_pull` and the query parameters instead of an uploaded file path. The worker then calls `fetch_epg_window()` instead of `pd.read_excel()` as its schedule source — everything downstream (estimation, review, reports) is identical either way. ### 6d. Test at realistic scale before calling this done Time a real 7-day, single-country pull. Report the number of rows returned and how long the query took. If it's slow (>2-3 seconds), that's a signal the indexing needs attention before this goes anywhere near a client. Do not remove the file-upload path — keep both options. Some schedules (fixtures not yet in the EPG, manually adjusted schedules) will still need manual upload. ## What "done" looks like for this handoff - [ ] `handoff/real_schema.sql` — all 24+ tables' real structure - [ ] `handoff/schema_diff.md` — every mismatch found and fixed - [ ] `engine/reference.py` — all tables wired, verified with row counts - [ ] `handoff/real_backtest_results.json` — real accuracy numbers - [ ] `handoff/shadow_run_comparison.xlsx` — Crystal vs. new engine, same real uploads - [ ] EPG pull tested at realistic scale, with query timing reported - [ ] Confirmed no full-table-scan query pattern exists anywhere in the EPG path - [ ] A summary written in plain English for Joseph — what works, what doesn't yet, and whether real-data accuracy matches or beats the synthetic proof Do not tell Joseph this is "ready for clients." That decision needs the real backtest number and the shadow run — both of which happen in this handoff, and neither of which exists yet.