Pattern-based bulk schedule generator

An AI-generated schedule generator re-checked every rule against the database inside a per-date loop and wrote rows one at a time, so its cost grew with every schedule it created. I moved the checks out of the loop, answered them from one prefetch, and batched the writes. A year of schedules went from 8,879 → 53 queries and 12.7s → 311ms (41×), with output proven identical to the original.

• For roughly 1,460 date × slot iterations per shift position, the loop re-queried licences, certificates, leave, bank holidays, conflicts, duplicates and working-time rules, plus holiday calendars, break times and status lookups.
• Almost all of that was the same answer every iteration, or answerable from a single read.
• Every schedule, officer assignment and readiness record was written with its own .create() or get_or_create(), so database round-trips grew linearly with the number of schedules.

• Validation caching: static qualification checked once per tile; only expiry-sensitive checks stay per date.
• Hoisting: holiday calendar, readiness configs, break times and status types loaded once per call.
• Prefetch + in-memory maps: existing schedules and assignments become dict/set lookups, updated as new rows are queued.
• Batched writes: bulk_create in batches of 200, flushed at date and config boundaries, with a per-row savepoint fallback so one bad row can't sink a batch.
• Shipped behind an opt-in flag next to the original, so both paths could be compared on the same input.

• Differential check: original and rewrite run on the same seed, each in a rolled-back savepoint, with every result field compared. Re-run after every change.
• Stress test: two shift positions on the same site and slot over 250 days, larger than one batch, so the second config has to merge with rows the first already flushed. Expected 500 results; both paths matched.
• Fault injection: forced every bulk_create to fail and a later row insert to fail; the fallback isolated the bad row (22 created, 1 error) and the counts stayed correct.

• Crash on first call: a local import shadowed a module-level name, raising UnboundLocalError before any work happened.
• Wrong model: the generated code wrote fields to a model they didn't belong to.
• Every officer treated as relief staff: a check tested for an attribute that never exists, so it was always true.

8,879 → 53 queries, 12.7s → 311ms median for a year of schedules, cost now flat as the range grows, and 3 latent bugs fixed before they reached users.