exports, diffs, restores and verifies snapshots of the scouting database (hasura over graphql).
this used to be one file inside the fake data generator. it assumed a fixed table list, assumed every
table had an id, and imported blind. on 2026-08-14 a plain override import would have silently
destroyed live data, so the whole rollback had to be driven by hand. every check in here comes from
that day.
nothing is hardcoded - tables, columns, foreign keys, constraints and insert order all come from introspecting whatever database you point it at.
python 3.9+, and a .env in the repo root:
HASURA_HTTP_LINK=https://your-project.hasura.app/v1/graphql
HASURA_ADMIN_SECRET=...
with uv, which is what the laptop in the pit has:
uv sync # builds .venv from uv.lock, no python install needed
uv run ckpt # menu
uv run ckpt list # or straight to a commandor plain pip if you would rather:
pip install -r requirements.txt
python -m ckpt
python -m ckpt listuv run ckpt and python -m ckpt are the same entry point, so anywhere below that says one, the
other works.
a second database (staging, a scratch copy) gets its own suffixed vars and --target:
HASURA_HTTP_LINK_SCRATCH=...
HASURA_ADMIN_SECRET_SCRATCH=...
uv run ckpt --target scratch exportcheckpoints go in ./checkpoints unless you pass --dir or set CKPT_CHECKPOINTS_DIR. chunk sizes,
retries and timeout come from CKPT_FETCH_CHUNK, CKPT_INSERT_CHUNK, CKPT_RETRIES,
CKPT_BACKOFF, CKPT_TIMEOUT.
ckpt export [--label NAME]
ckpt list
ckpt diff <checkpoint> [--against live|<checkpoint>] [--all]
ckpt restore <checkpoint> [--preserve auto|t1,t2] [--merge] [--probe] [--answer CODE=KEY,...] [--dry-run] [--yes]
ckpt verify <checkpoint> [--against <backup>] [--preserve t1,t2]
ckpt schema --out <path>
ckpt doctor [--app ../Scouting-2026]
exit codes: 0 nothing wrong, 1 something was found (a blocking finding, a failed
restore, a verify difference), 2 you or it backed out. diff returning 1 means the
checkpoint has blocking hazards, not that the diff failed.
run it with no arguments and you get the menu instead, which is how it gets used in the pit. the menu builds the same namespace argparse would and calls the same functions, so there is one code path per action.
diff is the restore preflight with nothing attached to it - read only, safe to run whenever, and the
fastest way to answer "what has changed since this checkpoint". it keys rows on a real unique
constraint where there is one, so a table re-created row for row reads as id-churn rather than as
every row being replaced.
verify looks for <checkpoint>_pre-restore on its own. without a baseline it cannot tell a
pre-existing dangling fk from one the restore made, so it reports those without a verdict rather than
failing a healthy database.
- it exports a backup first, always, named
<checkpoint>_pre-restore. there is no flag to skip it, and if the backup fails or comes up short against the live counts the restore stops there. - it prints the whole plan before touching anything and asks for confirmation.
--dry-runprints and exits. - every finding is either auto-resolved with the reason printed, or answered by you, or it stops. nothing gets waved through quietly.
- a table that would lose live rows says so, with counts.
- verification compares values. matching row counts is not proof - that was the lesson.
--yes only skips the final confirmation. findings still need answers, so a non-interactive run with
open findings aborts instead of guessing.
--answer is how you give those answers without a terminal - --answer H1=p,H7=d,H3=d is the
2026-08-14 rollback in one line. add the table to aim at one finding out of several,
--answer H1:robot_tags=w,H1=p, first match wins. a key the finding does not offer stops the run,
and an answer that matched nothing is called out, so a typo can never look like an answer. =a
aborts, same as picking it at the prompt.
| h1 | tables the checkpoint has 0 rows for, where live has rows - a blind override empties them and restores nothing. offers preserve-and-replay |
| h2 | preserved rows holding fks into tables the restore rebuilds. validated before the wipe - if a target is missing from the checkpoint it aborts rather than orphan the row |
| h3 | NOT NULL columns added after the export. read off the object type, not _insert_input - that reports everything as optional. graphql cannot read defaults, so it is flagged, not assumed. --probe settles it by inserting one real checkpoint row and deleting it again |
| h4 | unique constraints the checkpoint predates. the checkpoint's own rows are grouped by the constraint's columns and reported if they collide. a column the checkpoint does not carry counts as one shared value, not as a null - every restored row gets the same default there, so it cannot tell two rows apart |
| h5 | delete/insert order from a topological sort of the fk graph, with a retry-until-no-progress pass behind it for cycles |
| h6 | tables with no id. identity comes from a real unique constraint, or falls back to whole-row comparison. this is the bug that would have duplicated every app_passwords row on a merge |
| h7 | rows that dangle after the restore, and separately the fks that already dangle live. actions.technical_id has a hasura relationship but no enforced fk, so 24 orphans are legal and predate all of this. verify diffs against the pre-restore backup so they do not read as new damage |
| h8 | id sequences. postgres does not rewind them on delete, so a rollback leaves the sequence above the restored max, which is the safe direction. reported, answered by the tool, not fixed |
| h9 | tables the checkpoint has that live does not |
| h10 | rows of a table the checkpoint never heard of, holding an enforced fk into one the restore empties. they block the wipe whether or not they dangle, so they come out before it and go back after |
[d] deletes the rows that would dangle. [k] keeps them, and that costs more than it looks.
the fk pointing at the missing row is enforced in postgres for the whole run, so the row cannot just
be carried across a wipe - and sparing it from the wipe is worse, because it then pins its own
parents and teams and match_type quietly keep their live values instead of being rolled back. so
[k] puts the live match back into the rebuild alongside the checkpoint's 91, and the referencing
rows come out of the wipe and go back after. children of the carried row are not carried - the
checkpoint governs those.
[k] is only printed when it can work. the row has to still be live, its unique keys have to be free
against the checkpoint's own rows, the h10 hold underneath it has to be possible, and the carried
row's own fks have to resolve in the restored data. when one of those fails the key is not offered
and the reason is printed instead. tests/test_resolutions.py is what stops a resolution being
offered and never implemented - it reads the keys out of preflight.py and refuses any that nothing
acts on.
postgres will not empty a table while anything holds an enforced fk into it, and a table the checkpoint never heard of is never emptied on its own. so every row of one that points into a table the restore rebuilds comes out before the wipe and goes back after the insert, dangling or not. preserved tables count as rebuilt here - they are emptied and replayed like everything else.
there is one right answer to that, so it is answered in the report rather than at the prompt, with
the count printed in the plan. on 2026-08-14 the single strategy_messages row happened to point at
the one match the checkpoint did not have, so h7 caught it and [d] took it out of the way. pointed
at any of the other 91 it raised nothing at all: the wipe failed on schedule_matches, then on
teams and match_type behind it, 17 other tables were rebuilt around them, and the rollback
silently did not happen for the three that failed. verify was the only thing that noticed.
the rows go back last, after the preserved replay - a held row is as likely to point into a preserved
table as into a rebuilt one, and the replay is what puts those back. the set that goes back comes out
of the pre-restore backup rather than out of the plan, since the plan was built from preflight's read
and the backup is the later one. what came out and what went back are counted either side of the
wipe and a difference is a failure, because verify only looks for the rows it was told about and a
row that landed after the backup was never one of them.
an fk that already dangles live is skipped. postgres cannot be enforcing that one or the row could
not be sitting there, and half the fk graph is a <stem>_id guess, so there is nothing to get out of
the way of.
the hold is refused, and the restore stops, when something else points at the rows being held - they
would be orphaned for the length of the restore, and the delete that takes them out fails for that
same reason. that also pulls [k] off h7, since carrying a match back is pointless if the row that
needs it cannot get out of the way.
two sources, and neither one alone is enough:
- hasura's tracked object relationships. these miss real untracked fks -
match_strategy_links.schedule_match_idandstrategy_messages.schedule_match_idare both enforced in postgres and invisible here - a
<stem>_idcolumn name heuristic with plural and prefix variants. catches both of those, plus the irregulartechnical_id -> technical_matchesandschedule_id -> schedule_matches, but has no way to seeblue_0_id -> teams
take the union, topologically sort it. 29 edges over the 23 tables, 0 edge violations, including the three tables the old hardcoded order did not know existed.
<table>_constraint enums give you names, not columns. most decompose - specific_summary_team_id_scouted_by_key
splits cleanly into (team_id, scouted_by). the ones that do not (technical_matches_scout_identity_key,
faults_identity_key, both _pkeys on the id-less tables) get narrowed against live data: a column set
with duplicates in live cannot be the enforced constraint, and a mostly-null column proves nothing
because postgres lets nulls repeat.
elimination stops at the narrowest set that survives, and that is not always the answer: rolled back
to 360 rows, technical_matches_scout_identity_key came out as (schedule_id, team_id) - without the
column it is named after, because with less data the narrower set stayed unique. a wider set wins when
the constraint's own name scores it strictly better, and only the name. the +1 per _id column is a
tie-break between equally plausible sets, and widening on it picks
(fault_status_id, message, technical_id) for faults_identity_key, which the checkpoint cannot carry
and which h4 then reports as a violation invented out of a defaulted column.
a narrow answer is only trusted when every wider set that survives elimination contains it.
faults_identity_key came out as (time) - unique across 98 rows, but (fault_status_id, message, technical_id) survives just as well and shares no column with it, so that one is called ambiguous,
lists what else it could have been, and is never promoted to an identity. app_passwords keeps
(purpose) because every wider survivor there contains it.
that resolution is the expensive part, so export caches it into the manifest.
v2 recorded row counts and table order, which is why every import had to re-introspect and guess. v3 records the schema as it was at export time:
v1 and v2 still load. constraint_sources says how each answer was reached (name, primary-key,
inferred, guess) so a guess never gets promoted to an identity key.
ckpt/
config.py env, named targets, chunk sizes
client.py the graphql client, and the read-side Database interface
schema.py introspection, fk graph, topo sort, constraint and identity resolution
manifest.py v1/v2/v3, the on-disk Checkpoint, and a checkpoint standing in as a database
export.py
preflight.py h1-h10
plan.py the restore plan and the printout
restore.py
verify.py
diff.py
doctor.py
cli.py
menu.py
ui.py
tests/
fixtures/ the 08-14 schema snapshot, DCMP-2026 and the 08-14 backup (trimmed on actions)
fake_db.py an in-memory database that enforces fks, not-null and unique
doctor.py, menu.py and ui.py came later than the rest, all of them things that
did not belong inside cli.py. the on-disk checkpoint lives in manifest.py because a checkpoint is
a manifest plus its row files.
uv run pytestno network. the fixtures are the real 23 table schema snapshot, the real DCMP-2026 checkpoint and the
real 2026-08-14 backup, with actions trimmed and its counts adjusted - the 24 rows pointing at the
technical match that does not exist are kept, since they are the h7 case.
the schema snapshot is written by ckpt schema --out tests/fixtures/schema-snapshot-2026-08-14.json,
not by hand, and a test fails if the checked-in file is not what the tool writes.
the whole 2026-08-14 rollback is in tests/test_plan_dcmp.py as assertions. tests/test_roundtrip.py
and tests/test_hazards.py run export -> restore -> verify against an in-memory database that
enforces foreign keys, not-null and unique, one file per hazard resolution. tests/test_client.py
covers retries and chunking against a fake session, tests/test_introspect.py answers a fake
graphql server so the schema reading is tested without one, tests/test_restore_guards.py is every
way the restore is supposed to refuse, and tests/test_cli.py covers the menu and the
answer-feedback loop.
the suite is checked by breaking things on purpose - drop the null coverage rule, let a guessed constraint become an identity, stop pruning the preserved snapshot, compare rows by count instead of by value, put the held rows back before the replay that their fks need - and confirming a named test fails for each. every one of those is caught.
you can also drive the plan offline:
uv run ckpt --dir tests/fixtures \
--schema tests/fixtures/schema-snapshot-2026-08-14.json \
--live-from backup-2026-08-14 \
restore DCMP-2026 --dry-run --preserve auto--live-from reads a checkpoint in place of a database. it is read only, so it can only ever dry run.
{ "version": 3, "created_at": "2026-08-14T06:11:00", "label": "DCMP-2026", "source": { "url": "...", "target": "default" }, "table_order": ["..."], "tables": { "specific_summary": { "row_count": 36, "columns": { "id": { "type": "Int", "not_null": true } }, "constraints": { "specific_summary_team_id_scouted_by_key": ["team_id", "scouted_by"] }, "constraint_sources": { "specific_summary_team_id_scouted_by_key": "name" }, "foreign_keys": [{ "column": "team_id", "references": "teams", "found_by": "relationship" }], "identity": ["id"], "identity_source": "id" } } }