# The Report Engine — the report that builds itself

Part of **The Engine Room** on simranjaiswal.in: five automation engines that stop money going
missing between systems. This one is the scheduler and the gates behind the reporting case: a
nine-task DAG (plus a notify) that runs at 06:00 every morning, builds the sheet from three sources,
and refuses to publish a wrong one — yesterday's sheet stays up instead.

Live case, with the engine running in the browser: https://simranjaiswal.in/work/report-engine

## What it does

1. **Extract.** `extract_mysql` (40 s), `extract_crm` (55 s), `extract_zoho` (35 s) start together.
   An HTTP 429 fails fast (a fifth of the base duration) and retries after 2, 4, 8, 16 s; retries are
   counted, not hidden.
2. **Three gates, per extract, in order.** `gate_schema` (3 s): every expected column present — a
   renamed column stops the run, alerts, and leaves yesterday's sheet up; a human fix lands next
   morning. `gate_freshness` (4 s): the newest row must be after midnight, else poll every 5 min for
   up to 45; if the source lands, re-extract and continue, else publish with a STALE banner and alert.
   `gate_rowcount` (3 s): today's rows against the last count that passed; a move of more than 20%
   blocks publish and alerts.
3. **Transform.** `transform_orders` (25 s) needs the MySQL and CRM gates; `transform_collections`
   (20 s) needs Zoho and MySQL. Each starts the moment its gates are green.
4. **Publish.** `publish_sheets` (30 s), one batch write per tab; a 429 climbs the same retry ladder.
5. **Notify** (2 s): CLEAN, RETRIED, STALE or BLOCKED, with the gate and the source named.
6. **Tie-out.** 30 mornings = clean + retried + stale + blocked, and published + blocked = 30.

"Wrong numbers prevented" = schema-gate stops + row-count-gate blocks. Analyst time after: 8 min of
review a morning, plus 25 for each blocked morning and 5 for each stale one. The manual baseline is
108 min a morning (9 h a week over five mornings) with a 1-in-12 chance of a paste error that ships.

## The synthetic month

There is no client data or real pipeline log here. `simulate_morning()` draws, in a fixed order the
browser port mirrors exactly: task jitter (normal, sd 10% of base), row counts per source (normal,
sd 3% around 18,400 / 6,200 / 3,900), then the five incidents with the probabilities on the sliders —
CRM 429 (retry k = 1 + int(rng·3)), Sheets 429 (same), schema drift (one extract), late source (one
extract; lands with p 0.7 after 1–9 polls), row-count anomaly (one extract at 65% of normal). The DAG
is then scheduled and every task segment is timestamped. The manual-era paste errors are drawn on a
separate stream (`RNG(seed + 1000)`) so the incident sliders do not move them. Randomness is
mulberry32, a 32-bit generator the browser port implements bit-for-bit, so the page's reference run
reproduces `results.json` exactly (the page's status line says so when it does, and
`node parity.mjs` proves it).

## Run it

```
python3 engine.py                                     # reference: seed 42, CRM 429 15%, Sheets 429 10%, schema 7%, late 12%, row count 5%
python3 engine.py --crm429 0.35 --sheets429 0.25 --schema 0.15 --late 0.30 --rowcount 0.12   # a bad month
python3 engine.py --schema 0 --rowcount 0             # the gates have nothing to catch; the hours barely move
node parity.mjs                                       # MATCH: the browser port reproduces results.json
```

Stdlib only. `results.json` carries the parameters and constants, per-task base and mean durations,
the totals (statuses, gate catches, retries, mean and p95 seconds to publish, analyst minutes before
and after, errors shipped), the 30-morning log, and the longest morning's task segments and
timestamped run log.

## Reference run (seed 42)

See `results.json`. Headline: 15 clean, 7 retried, 3 stale, 5 blocked; 5 wrong numbers prevented
(3 schema, 2 row count); 17 retries absorbed; 3,240 analyst-minutes by hand → 380 with the engine
(47.7 hours returned); 3 paste errors would have shipped by hand, 0 unlabelled numbers shipped.

## Where the shape comes from

The daily reporting pipeline Simran built at a procurement startup — MySQL + CRM + Zoho into Google
Sheets, scheduled before the day started, with batch writes and schema checks that raise
(https://simranjaiswal.in/work/sheets-automation). The 9 hours → 40 minutes a week and 70% of daily
manual tasks removed are as reported in that case; the 108-minute baseline is 9 hours over five
mornings; the 1-in-12 paste error is an assumption, printed. Nothing in this repository is a
client's data.

MIT.