Geography master data

Audience

This article is for GCM platform operators. Church admins should see the Add a member article for using the country / state / city dropdowns — this page is for the staff who maintain the underlying lists.

Every address field on the platform — member profiles, org settings, the public donation form, the visitor form — pulls from three master tables: countries, states, cities. They are platform-wide, not per-org, which is why this surface lives in Platform Admin. The Geography tab (/platform/geography) is the CRUD console for those tables, with a three-panel cascading layout and CSV bulk import for the long-tail.

The three-panel layout

The page is split into three side-by-side cards. Each panel only enables when its parent is selected:

1. Countries — the master list. Always visible.
2. States — populates when a country is selected. The card title flexes to use the country's admin_level_label ("Provinces" for Canada, "Departments" for Colombia, "States" for the US, etc.).
3. Cities — populates when a state is selected.

Selection persists per panel: picking a new country resets the state and city selections; picking a new state resets only city.

Each panel has its own search box that does a client-side includes against the visible names.

Editing a country

Click Add at the top of the Countries panel — or the pencil icon on any row — to open the country form. The fields are:

• Name (required) — the display name, e.g. "Colombia".
• ISO Code (required, 2 chars) — the ISO 3166-1 alpha-2 code, e.g. CO. Auto-uppercased.
• ISO Numeric — the ISO 3166-1 numeric code (3 digits), e.g. 170. Used by some integrations.
• State / Province Label — what your country calls its first-level subdivision. "Department", "Province", "Region", "Canton", "Prefecture". Drives the title and placeholder text in the state picker downstream.
• Dial Code — the international dialling prefix, e.g. +57. Used by the phone-number formatter.
• Flag Emoji — pasted Unicode flag, e.g. 🇨🇴. Shown next to the country name everywhere.

Save inserts a row in countries if you were adding, or updates it in place if you were editing. The cached list invalidates so the new value appears in every address dropdown on the next render.

Deleting a country opens a destructive confirmation. The alert is honest about cascade: deleting Colombia also deletes every state and city under it. If any member or visitor refers to that country by id, you will get an FK violation — clean those up first or change the cascade behaviour in a migration, not from this UI.

Editing a state

States have two fields:

• Name (required) — display name.
• ISO Code — the ISO 3166-2 subdivision code, e.g. US-CA. Optional but recommended. Auto-uppercased.

The same single-add form, the same destructive-delete confirmation. The country id is set automatically from the selection.

Editing a city

Cities have one field: Name. There are no codes; cities are just labels associated to one state and (denormalised for query speed) one country.

CSV bulk import

The single-add form is fine for a missing entry. For seeding a whole country's worth of states (or a state's worth of cities), use Import at the top of the states or cities panel.

The import dialog accepts plain text, one row per line:

• For states: name,iso_code. The ISO code is optional; if blank, the first 3 letters of the name are uppercased. Example:

  California,CA
  Texas,TX
  Florida,FL

• For cities: name only.

Lines that are blank or start with # are skipped, so you can paste in commented-out source lists.

The dialog shows a running count of valid rows parsed under the textarea, so you can sanity-check before pressing Import. All rows are inserted in a single INSERT round-trip; the unique index on (country_id, name) (for states) or (state_id, name) (for cities) means re-importing the same list throws an error on the first duplicate. The right pattern is: paste the missing rows only, not the whole list.

Sourcing data

For new countries, the canonical seed data is the GeoNames admin1CodesASCII.txt (states) and the country's cities500.txt slice. Strip them down to the columns above with awk and paste them in.

Common situations

"The member form does not show 'Cundinamarca' as a department in Colombia." Select Colombia in the Countries panel, search for "Cundi" in States. If missing, Add it with ISO CO-CUN. The dropdown refreshes everywhere immediately — cached queries invalidate on save.

"I need to add 47 missing cities to a state." Select the country, then the state, then click Import in the Cities panel. Paste the list, one per line. Hit Import. The dialog reports the count it inserted; if any failed (usually duplicates), the toast says so.

"A customer asked us to rename their country from 'United States' to 'US' for short." Don't. The country name is global — every other customer sees it too. If you must localise, the right channel is the per-locale translation files (src/i18n/locales/{en,es}.json) for the display strings, with the iso_code keeping the data stable.

What this surface does not do

• It does not geocode. The map module uses Mapbox / Nominatim at lookup time; this page only feeds the address dropdowns.
• It does not enforce a hierarchy. You can technically create a state in a country whose ISO code is wrong, or a city in a state that belongs to a different country. The form prevents most of these by setting country_id from the selection, but a manual SQL fix could break it. The audit trail is your safety net.
• It does not version. There is no history of "what the list looked like last quarter". Treat it as the current truth.

If you need historical address data preserved (e.g. for a member whose state was renamed), capture it in the member's address text columns at the moment of capture, not in the picker.