countries-states-cities-database Agent Skills & AI Rules

# CSC Repository - Copilot Instructions

## Project Overview

This is the **Countries States Cities Database** - a comprehensive geographic database containing 250+ countries, 5,000+ states, and 150,000+ cities. It is open source under ODbL-1.0 and serves 40,000+ developers with 50M+ monthly API requests.

## Repository Structure

- `contributions/` - Where all data changes are made via PRs (cities, states, countries subdirectories)
- `json/` - Generated JSON export files (do NOT edit directly)
- `sql/` - Generated SQL export files (do NOT edit directly)
- `xml/`, `yaml/`, `csv/` - Other generated formats
- `.github/` - Workflows, scripts, skills, agents, and templates

**Important:** Contributors must ONLY edit files in the `contributions/` directory. All other export formats are auto-generated by GitHub Actions.

## Schema Rules (from schema.sql)

### Cities - Required Fields
- `name` (string, max 255 chars, non-empty)
- `state_id` (integer, must reference a valid state)
- `state_code` (string, max 255 chars)
- `country_id` (integer, must reference a valid country)
- `country_code` (string, exactly 2 characters, ISO 3166-1 alpha-2)
- `latitude` (decimal, range -90 to 90)
- `longitude` (decimal, range -180 to 180)

### Cities - Optional Fields
- `state_name`, `country_name` (denormalised for convenience)
- `wikiDataId` (format: Q followed by digits, e.g., Q65)
- `timezone` (IANA timezone string)

### States - Required Fields
- `name` (string, max 255 chars, non-empty)
- `country_id` (integer, must reference a valid country)
- `country_code` (string, exactly 2 characters)

### States - Optional Fields
- `state_code`, `iso2`, `type`, `level`, `parent_id`, `native`
- `latitude`, `longitude` (decimal, valid ranges)
- `fips_code`, `wikiDataId`

### Countries - Required Fields
- `name` (string, max 100 chars, non-empty)

### Auto-Managed Fields (MUST NOT be included in contributions)
- `id` - Auto-incremented primary key
- `created_at` - Auto-set timestamp
- `updated_at` - Auto-updated timestamp
- `flag` - Internal status flag

## Contribution Guidelines

### Data Quality Standards
- **Tier 1 Sources (Preferred):** Government websites, national statistical offices, ISO, UN
- **Tier 2 Sources (Acceptable):** GeoNames, academic institutions, well-cited Wikipedia articles
- **Unacceptable:** Personal blogs, uncited crowd-sourced content, outdated sources

### Coordinates
- WGS84 decimal degrees format
- 6+ decimal precision for cities
- Must represent actual administrative centre when possible
- Must fall within the parent country's geographic bounds

### Names
- Use internationally recognised English names
- Maintain consistency with established conventions

### Referential Integrity
- `state_id` must reference a valid state that belongs to the specified `country_id`
- `country_code` must match the ISO2 code of the referenced `country_id`
- `state_code` must match the code of the referenced `state_id`

## Finding Reference IDs

```bash
# Find state ID
grep -A 5 '"name": "California"' contributions/states/states.json

# Find country ID
grep -A 5 '"name": "United States"' contributions/countries/countries.json

# Find all states for a country
jq '.[] | select(.country_code == "US") | {id, name}' contributions/states/states.json
```

## Local Development Commands

### Initial Setup
```bash
cd bin
composer install --no-interaction --prefer-dist
```

### Database Operations
```bash
# Start MySQL
sudo systemctl start mysql.service

# Create database
mysql -uroot -proot -e "CREATE DATABASE world CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Import SQL dump
mysql -uroot -proot --default-character-set=utf8mb4 world < sql/world.sql
```

### Import & Export
```bash
# Import JSON to MySQL
python3 bin/scripts/sync/import_json_to_mysql.py

# Export MySQL to formats
cd bin
php console export:json      # 4 seconds
php console export:csv       # 1 second
php console export:xml       # 9 seconds
php console export:yaml      # 17 seconds
php console export:sql-server # 3 seconds
php console export:mongodb   # 1 second

# Sync MySQL back to JSON
python3 bin/scripts/sync/sync_mysql_to_json.py
```

### Normalise JSON (Optional)
```bash
# Auto-assign IDs and timestamps before committing
python3 bin/scripts/sync/normalize_json.py contributions/cities/US.json
python3 bin/scripts/sync/normalize_json.py contributions/states/states.json
```

## Data Enrichment Requirements

Timezone and translation fields are important for data quality. After adding or updating data:

```bash
# 1. Import to MySQL (assigns IDs)
python3 bin/scripts/sync/import_json_to_mysql.py

# 2. Add timezone to entries
python3 bin/scripts/validation/add_timezones.py --table both

# 3. Sync back to JSON
python3 bin/scripts/sync/sync_mysql_to_json.py

# 4. Validate with Wikipedia (optional)
python3 bin/scripts/validation/wikipedia_validator.py \
    --entity "New York City" --type city --country US
```

## PR Review Process

When reviewing PRs:
1. Check schema compliance (required fields, format validation)
2. Verify cross-references (state_id, country_id exist and match codes)
3. Check coordinate plausibility (within country bounds)
4. Look for duplicates (same name in same state, nearby coordinates)
5. Verify data source is documented and accessible
6. Be constructive and welcoming to contributors

## Critical Operations

The following require explicit maintainer approval:
- Any deletion of states or countries
- Modifications to more than 500 records
- Changes to schema-sensitive fields (country_id, state_id reassignments)

## Licensing

All data is under Open Database License (ODbL) v1.0. Contributors must ensure their additions comply with this licence and do not include proprietary data.