countries-states-cities-database

Migrations: what changed between two releases

From v5.0.0, each data release is meant to carry files that tell you how its records relate to an earlier release’s, so you can update ids stored in your own systems. They are generated, never edited by hand, by bin/scripts/release/migrations.py from this folder’s ledger and the git history of contributions/.

Status: events.jsonl holds every removal since v4.0 (10,201) and the 1509 restoration, and every one is reviewed. The generator accepts the cumulative interval from v4.0, and the export workflow (below) attaches its files to each export release. A removal whose successor could not be proved is recorded as no_successor with no target; it claims nothing beyond that.

The files

File What it holds
removed_ids.jsonl One line per removal in the interval, ids created and removed in between included. Each line has the reviewed event, the removed row (before), present_at_from, present_at_to and current_targets, which follows later removals to the records that exist in this release. The removal of a repurposed id also has repurposed_at and later_entity_targets (see below). restored_at marks an id the same entity got back later.
removed_ids.csv v4.0’s columns record, old_id, name, country_code, state_code, outcome, new_table, new_id, source, then relation, automatic_redirect, event_commit. One row per current target. A removal with no successor has one row with empty new_table and new_id. Rows for later_entity_targets follow, with a relation starting later_entity> and automatic_redirect no.
added_ids.jsonl Every id present in this release and not in the earlier one: table, id, name, file and the commit that added it. An id a reviewed restoration brought back also has restored (its event and retirement commits).
identity_changes.jsonl The reviewed unresolved snapshot (baseline: true) when its anchor is in the interval, then later identity decisions in first-parent order. Reviewed identity decisions on ids that kept their number at the time, with the row before and after and the current targets. A repurpose has remediation: the removal that retired the id and the other entity’s replacement. A repurpose decided before --from is listed again, with event_before_from: true, when the removal that retires it (retires_repurpose) is in this interval; other earlier decisions are not repeated.
changed_fields.jsonl One line per changed field of a record present in both releases. Each line gives before and after as typed JSON (1, 1.0 and true differ), plus before_present and after_present, so a missing field and a null one can be told apart. translations is compared per language (translations.de), or as a whole when one side is not an object. By policy, created_at, updated_at and flag are left out: they are bookkeeping, not facts about the place (the importer keeps the values a file gives and fills missing ones).
release_manifest.json The release metadata (data, contract, policy and data-schema versions, notices), the two commit SHAs, the migration format version (3), totals per table at both ends, and the removed, added and changed counts, which reconcile (from_total - removed + added = to_total). It also has counts per field path and the SHA-256 of every file above.

Reviewed unresolved baseline

unresolved_baseline.jsonl is the reviewed inventory of every retained unresolved key at baseline_commit, including rows unchanged since v4.0. It is an input, separate from events.jsonl: a snapshot disclosure does not claim a scalar change happened at that commit. Each line has schema_version: 1, table, id, a full baseline_commit, canonical full row_sha256, outcome: "unresolved", reason, decision_round, review_status: "reviewed", review (reason and reviewer), and nonempty evidence with the ledger’s URL/manifest rules. Audit manifest pointers refer to the retained csc-data-audit evidence bundle; their SHA-256 identifies the exact bytes. The generator checks their shape without fetching sources. Review establishes inventory completeness; Git validates the keys and guards, not the semantic adequacy of the evidence.

The default input is unresolved_baseline.jsonl beside --ledger; --baseline PATH overrides that location. The file is required; an empty inventory is valid only when review found no unresolved keys. All entries share one anchor. Duplicate keys, malformed provenance, a missing/stale row at the anchor, a key already repurposed, removed or reserved, an off-line anchor, or a conflicting identity event at that commit fail before output is written. At the initial v5 pin every baseline key must remain present at --to; later reviewed removals and repurposes may retire it through the normal ledger gates. Keep the baseline immutable and ship it with the generator and ledger.

The first cumulative v4-to-v5 file carries all baseline entries with event_type: "identity", baseline: true, event_commit equal to the reviewed anchor, identical before/after rows, no targets and no redirect. Its row_sha256 is a snapshot guard, not a before_row_sha256 claim about an invented transition. Migration format version 3 distinguishes this addition. The manifest records the baseline’s SHA-256, anchor and key count. The output is ordered by first-parent commit, then key; a step interval excludes decisions at or before --from. A future anchor waits for its release. Historical guards are still checked when the anchor precedes --from.

Consumers start from the verified v5 baseline, apply later identity decisions in order and retain the latest outcome per (table, id); apply reviewed removals as retirements. An omitted key stays unresolved until an explicit later decision resolves it or a reviewed removal retires it. Category-field admission alone does not replace an explicit unresolved identity decision. The 117 listed rename keys and 12 Russian rayon mappings remain publication holds; including them in this inventory does not approve their transfer assertions or close those holds.

A later review can resolve identity without editing the data: append a normal reviewed identity event with decision_only: true, before_row_sha256 and after_row_sha256 guarding the unchanged row at that review commit. Only classification, dual_role and unresolved allow this form; a repurpose still requires a real transition and its existing remediation checks. Evidence, review, target and history checks remain mandatory.

Outcomes and relations

Outcome Meaning Targets (relation) Redirect automatically?
merged_same_entity A proved duplicate of the same place, kept under another id in the same table exactly one (merged_same_entity) only when the event says automatic_redirect: true
administrative_seat A row for an administrative unit, removed; the town where the unit’s seat is is a different place the seat town (administrative_seat), and the unit’s own record elsewhere (moved_dataset) when one exists no
moved_dataset The record now lives in another table (e.g. a district moved from cities to counties) the new record (moved_dataset), in another table; an independently reviewed seat may also be listed (administrative_seat, cities to cities only). The unit target remains required no
territorial_successor A unit replaced after a merger or reform the successor (territorial_successor) no
split_into A unit split into several two or more (split_into) no
namespace_replaced, invalid_record, no_successor No successor, for the reviewed reason given [] no

automatic_redirect must be true or false and may be true only for a reviewed merged_same_entity. A chain (A merged into B, B later merged into C) resolves to C in current_targets. It redirects automatically only if every hop does.

An identity event ("event_type": "identity") records a review of what an id denotes, at the commit where its record changed while keeping the number (the four outcomes of decisions_4_final.md):

current_targets follows each target forward in history. A removal passes to its own targets. A repurpose passes to the earlier entity (prior_identity), and an unresolved decision keeps the key: both end any automatic redirect. A removal followed by a restoration of the same id continues at that id, with the relation restored and no automatic redirect; the removal’s own line then maps the id to itself. A path that ends in a removal with no successor is listed with "end": <outcome> and no table or id. Each step moves to a later event, so every path ends. Identical results are listed once. More than 1,000,000 steps from one record (only possible with a large fan-out of splits) is refused as an expansion limit.

A restoration ("event_type": "restoration") brings a retired id back for the same entity, such as Finland’s state FI-01 (states 1509), retired by #1144 and restored by #2020. It is checked against history: the id returns at event_commit with the row hashed in restored_row_sha256; it left its whole table at retirement_commit, earlier on the same first-parent line, with the row hashed in retired_row_sha256; both rows hold the values in same_identity; and no stable identifier (wikiDataId, iso3166_2, iso3) that both rows hold differs. retirement_commit must be the id’s latest disappearance in the history read, and a removal that remediated a repurpose (it took away the later entity) cannot be restored. A restored id that leaves again, even before --from without a ledger event, needs a new restoration to return. same_identity must bind at least one of those three with a nonblank value: a shared country, category or name does not prove the same entity. An id a reviewed removal retired may return only this way, before --from as well as inside the interval; a reserved id may not be present at --from or return inside the interval without it.

The ledger (events.jsonl)

There is one JSON object per line, cumulative across releases. A removal event has:

{"schema_version": 1, "old_table": "cities", "old_id": 819,
 "event_commit": "<the first-parent commit where the id disappeared>", "before_commit": "<its first parent>",
 "before_path": "contributions/cities/AR.json", "before_row_sha256": "<SHA-256 of the removed row>",
 "outcome": "administrative_seat", "automatic_redirect": false,
 "targets": [{"table": "cities", "id": 1403, "relation": "administrative_seat"},
             {"table": "counties", "id": 6711, "relation": "moved_dataset"}],
 "review_status": "reviewed", "review": {"reason": "...", "reviewer": "..."},
 "evidence": [{"manifest": "research/<manifest>.json", "sha256": "<its SHA-256>"}]}

For a bulk removal cohort whose other fields are identical, use one removal_batch line to avoid repeating review and evidence thousands of times. Its schema is the ordinary removal schema above, except event_type is "removal_batch" and the three per-row keys are moved into rows:

{"event_type": "removal_batch", "schema_version": 1, "old_table": "postcodes",
 "event_pr": 2055, "before_commit": "<full parent SHA>", "outcome": "namespace_replaced",
 "automatic_redirect": false, "targets": [], "review_status": "reviewed",
 "review": {"reason": "...", "reviewer": "..."}, "evidence": [{"url": "https://example.org/source"}],
 "rows": [{"old_id": 123, "before_path": "contributions/postcodes/ES.json", "before_row_sha256": "<SHA-256>"}]}

rows must be a nonempty list with distinct old_id values. Every row has exactly old_id, before_path and before_row_sha256; none may appear in the shared fields. Batches represent removals only, never identity or restoration events. Each row expands to an ordinary removal before validation, including PR resolution and pending mode, history guards, targets, retirement and duplicate checks. Published files are byte-identical to those from the expanded events (without an explicit event_type). Diagnostics use one-based row indexes; row 0 identifies malformed shared fields or the row list. Use separate cohorts when any shared field differs.

before_row_sha256 is the SHA-256 of the row as UTF-8 json.dumps(row, ensure_ascii=False, sort_keys=True, separators=(',', ':')). An event may also carry the full before row, which must hash to that value. Other fields (event_pr_url, effective_date, …) are passed through. An identity event uses table and id in place of old_table and old_id, and has no before_commit or before_path.

evidence is a non-empty list. Each item is either a source URL, {"url": "https://<host>/<path>"} (http or https, with a host and no spaces), or a research manifest and its hash, {"manifest": "research/<file>.json", "sha256": "<64 hex digits>"}. Only the shape is checked; sources are not fetched. Research manifests are kept in the maintainers’ audit workspace, not in this repository; event_pr_url links the public PR that made the change.

The removal that retires a repurposed id names the repair:

{"retires_repurpose": {"identity_event_commit": "<the repurpose's event_commit>",
                       "replacement": {"table": "cities", "id": 166001}, "replacement_kind": "fresh"}}

The replacement must be one of the removal’s targets, and the removal cannot redirect automatically. fresh means a never-used id: it first appears after the repurpose, was never removed (and so never restored) in the history read, and is not reserved. existing means an id that was already there before this removal, proved the same entity by the review.

A restoration event:

{"schema_version": 1, "event_type": "restoration", "table": "states", "id": 1509,
 "event_commit": "<commit where the id returns>", "retirement_commit": "<commit that removed it>",
 "retired_row_sha256": "<SHA-256 of the removed row>", "restored_row_sha256": "<SHA-256 of the returned row>",
 "same_identity": {"iso3166_2": "FI-01", "wikiDataId": "Q5689"},
 "review_status": "reviewed", "review": {"reason": "...", "reviewer": "..."},
 "evidence": [{"url": "https://github.com/dr5hn/countries-states-cities-database/pull/2020"}]}

A held event is a placeholder. It fails the release wherever it applies, before --from as well as inside the interval; only a later release’s events are ignored.

A data PR carries its own events

Use a full event_commit SHA (existing ledgers keep working), or omit it and use a positive integer event_pr, for removals, identity decisions and restorations alike. Each PR key requires a saved GitHub association, {"event_pr": 2050, "merge_commit_sha": "<full SHA>"}, in --pr-commits (default: pr_commits.jsonl beside the ledger). Missing, duplicate or malformed associations fail. The associated commit must have exactly one parent and belong to the pinned ledger’s first-parent line. Titles never establish an association: merges, cherry-picks, reverts and edited suffixes cannot substitute for GitHub’s merge_commit_sha. The PR check and export wrapper fetch each PR-only key from GitHub’s pulls API, require it to be merged, and refuse any disagreement with saved associations in migrations/pr_commits.jsonl (initially empty). They need read access only. The export saves the verified map as pr_commits.jsonl inside every migration archive, covered by the archive checksum, so the same inputs can be replayed offline with --pr-commits <saved-map>; use trusted committed or exported evidence for offline runs. Do not invent mappings from titles. --ledger-tip pins the authoritative ledger history (default: HEAD, resolved once); it may include events after --to. Such later events, including held events, wait for their release. For historical regeneration, supply the ledger’s pinned tip and its saved map, rather than searching arbitrary branches. Explicit event_commit keys always take precedence, including in pending mode: existing events may retain event_pr as metadata without changing their meaning. Generated JSONL and CSV always record the resolved full event_commit SHA; JSONL also retains event_pr. Reference fields such as retirement_commit, retires_repurpose.identity_event_commit and recorded_later.event_commit still require full historical SHAs. The immutable unresolved baseline continues to use baseline_commit.

  1. Open the data PR to obtain its number. Append reviewed events to migrations/events.jsonl in that same PR, replacing each new event’s event_commit with event_pr: <that number> (a JSON integer). Include the evidence, review, outcome and targets as usual; no follow-up ledger PR is needed after squash merge.
  2. For a removal, set before_commit to the merge preview’s first parent (the current base-branch SHA), before_path to the row’s original path, and before_row_sha256 to its full canonical row hash at that parent. Refresh these guards when the base changes. Identity and restoration hashes keep their existing rules.
  3. Finalise every new row’s id before the check: id-less rows are allowed in intermediate history, not at --to. All existing review, target, retirement, repurpose, baseline and restoration gates remain in force. A repurpose that still leaves the id denoting the other entity at the preview is refused; PR keys do not waive remediation.
  4. Merge a PR that carries PR-keyed events with “Squash and merge”. The association must be a single-parent commit that holds the whole change: “Create a merge commit” gives a two-parent commit, and “Rebase and merge” spreads the change over several commits, so either leaves the events unresolvable and the next export refuses until the ledger is fixed. Nothing wrong is published; releases stop. Allowing only squash merging in the repository settings removes the risk.
  5. The Ledger migrations PR job checks the merge preview with --pending-pr <number>. Only events naming that number bind directly to --to; other PR keys still require verified associations. Pending mode requires a two-parent preview and refuses a PR already present in the saved associations or the base’s historical suffixes. CI also checks that the PR is still open and that the preview’s second parent is the PR’s current head (GitHub’s live merge_commit_sha is not compared: it moves when the base advances). The preview’s tree and first parent are the eventual squash inputs; all data gates run unchanged. After the merge, GitHub’s merged SHA becomes the association; the title plays no part, so editing it is harmless. Without pending mode an unmerged PR key fails. Use pending mode only for a merge preview, never to generate published release files.

For example, a reviewed no-successor removal in PR 2050 uses this event shape (replace the guards and provenance):

{"schema_version": 1, "old_table": "cities", "old_id": 123, "event_pr": 2050,
 "before_commit": "<full base SHA>", "before_path": "contributions/cities/XX.json",
 "before_row_sha256": "<canonical full-row SHA-256>", "outcome": "no_successor", "targets": [],
 "automatic_redirect": false, "review_status": "reviewed",
 "review": {"reason": "<reviewed finding>", "reviewer": "<reviewer>"},
 "evidence": [{"url": "https://example.org/source"}]}

The read-only ledger-migrations.yml reports Ledger migrations on every PR, including drafts. A complete local Git diff skips only the expensive validation step when no path under contributions/, migrations/, bin/scripts/release/ or the workflow itself changed; docs/export-only PRs still receive a successful required status. Relevant PRs fetch full history and tags and compare v4.0 to the merge preview. CI and export share source-metadata validation before adding data_version, derived from the validated contract major (5-candidate for contract 5). The job publishes and comments nothing. Require Ledger migrations in branch protection/rulesets. Its single walk takes about two minutes and 3.2 GB, with a 15-minute timeout; newer runs cancel older runs for the same PR.

To reproduce a PR check in a full clone with gh signed in, prepare the same verified inputs, then run the generator:

export PENDING_PR=2050 MERGE_SHA=<merge-preview-sha>
python3 - <<'PYCODE'
import json, os, sys
from pathlib import Path
sys.path.insert(0, 'bin/scripts/release')
from release_migrations import github_pr_commits, load_source_meta
meta = load_source_meta('migrations/release_meta.json')
meta['data_version'] = f"{meta['contract_version']}-candidate"
Path('/tmp/ledger-meta.json').write_text(json.dumps(meta))
Path('/tmp/ledger-prs.jsonl').write_text(github_pr_commits(
    Path.cwd(), 'migrations/events.jsonl', 'migrations/pr_commits.jsonl',
    int(os.environ['PENDING_PR']), os.environ['MERGE_SHA']))
PYCODE
python3 bin/scripts/release/migrations.py --from v4.0 --to "$MERGE_SHA" --pending-pr "$PENDING_PR" \
  --ledger-tip "$MERGE_SHA" --pr-commits /tmp/ledger-prs.jsonl \
  --ledger migrations/events.jsonl --reserved migrations/reserved_ids.jsonl \
  --meta /tmp/ledger-meta.json --out <scratch-output-folder>

The reserved ids

--reserved names every id that left the data before --from and must never return, as {"table", "id"} per line. Removals in ledger events before --from are added automatically. The list for v5.0.0 starts from v4.0’s published removed_ids_v3.2_to_v4.0.csv: its 4,492 ids are in reserved_ids.jsonl.

The importer (bin/scripts/sync/import_json_to_mysql.py) reads the same files, events.jsonl (its removals) and migrations/reserved_ids.jsonl, and starts the ids it assigns to new records above the highest retired or reserved id of each table, so a retired id is never handed to a new record. MySQL also keeps them above the highest id present. A malformed line in either file stops the import.

What makes the generator refuse

It exits 1 and writes nothing when any of these happens:

Ids may be missing in between: contributors may omit ids on new rows until the import assigns them. Every id must be final at --to.

Generating the files

The export workflow’s migrations job runs bin/scripts/release/release_migrations.py on a full clone after a successful build, and the publish job attaches what it writes to the release, checked against its checksums:

Asset What it is
migrations-from-<tag>.tar.gz The files above, from that published release to this one. One is cumulative, from the newest feature release of the previous major (v4.0 for v5 data); one is a step, from the newest export release of the same major (from v5.0.0’s second export on). A single archive when both are the same commit.
MIGRATIONS_SHA256SUMS The SHA-256 of each archive.

release_meta.json holds the contract, policy and data-schema versions and the notices. The run adds data_version: the newest published feature release of the contract’s major, which names the export, or <major>-candidate before that release is cut. A production run refuses until it is cut, so v5 data is never published as a v4.0 export. A failed migrations job stops publishing; a candidate build still keeps its exports.

Every id must be final at the candidate commit (assign ids with the import and sync, or normalize_json.py, first), so each export’s tag can be the start of the next step file. v4.0-export.51’s tag cannot: its 10,851 Spanish postcodes got their ids at export and were synced back later. So v5.0.0’s first export carries only the cumulative file from v4.0. It lists every id event since v4.0, so a user of any v4.0 export can look up each id they store (where a removed one went). Its added ids and changed fields are relative to v4.0, not to a later export: users of a later export load the v5 data fresh and use the file to remap the ids they store, not to patch their copy.

python3 bin/scripts/release/release_migrations.py --to <candidate sha> --out <new folder> [--production]

To run the generator by hand instead:

Pass commit SHAs, not branch names. --repo may name any folder inside the repository; every read runs from its top level. --from is the previous successful published snapshot, not a failed draft or a feature tag. --to is the release candidate. Both must be on one first-parent line and come from a full clone. For v5.0.0, from v4.0 (the cumulative file; see above for why not from export.51).

python3 bin/scripts/release/migrations.py --from <published snapshot sha> --to <candidate sha> \
  --ledger migrations/events.jsonl --reserved migrations/reserved_ids.jsonl --meta <release_meta.json> --out build/migrations

release_meta.json looks like this:

{"data_version": "5.0.0", "contract_version": "5", "policy_version": "<date>", "data_schema_version": "5",
 "notices": [".github/licenses/SOURCES.txt"]}

The same inputs always give byte-identical files. The files are written to a new staging folder (.<out>.staging-<random>) and swapped in at the end, so a failed run keeps the earlier output; when writing fails, the error says where that output is. If a run is stopped between the two renames of the swap, the last complete output is in .<out>.previous. When a validated run reaches the write stage, it restores .<out>.previous before creating its staging folder. If validation fails earlier, the last complete files remain in that backup. Run one generator at a time per output folder. A staging folder left by a stopped run is never reused; delete it by hand.

Measured on a laptop (Apple silicon), on the repository of 14 October 2026 (master 2f0ce218, about 1,018,000 keyed rows, 543 MB of source JSON). With earlier events the walk starts at the oldest of them, so the cost follows the commits walked, not the length of the interval:

Interval Commits walked Time Peak memory
#2009 to master (9 commits), checking 9,862 earlier events and the 1509 restoration 203 68 s 2.8 GB
v4.0 to master, cumulative (refused on exactly the 339 held events) 208 66 s 3.5 GB
v4.0 to master 2a04cd21, cumulative, every event reviewed (15 October) 229 117 s 3.2 GB

Tests: python3 -m unittest bin/scripts/release/test_migrations.py from the repository root, or cd bin/scripts/release && python3 -m unittest test_migrations test_release_migrations. The tests run on scratch git repositories.