AgrarianGameArchive/Docs/Terrain/TileRegistrySchema.md

# Tile Registry Schema

## Purpose

The tile registry is the authoritative operational index for terrain tiles. It
tracks what tiles exist, where they are, which source data generated them, what
package version is published, and whether the tile is ready for clients.

The first implementation only needs the Ground Zero tile and neighbors. The
schema is intentionally shaped so it can scale toward hundreds of millions of
possible 1 km tiles later.

## Prototype Grid

For the MVP, the tile key uses UTM zone 10N and the lower-left 1 km grid corner.

```text
gz_us_ca_pacifica_utm10n_e544_n4160
```

Fields encoded in the prototype ID:

- Location family: `gz_us_ca_pacifica`
- Projection: `utm10n`
- Easting kilometer: `e544`
- Northing kilometer: `n4160`

The final global grid is still a design decision. The MVP schema keeps explicit
projection and metric bounds so tiles can be migrated later if the global index
changes.

## Tile Status

Allowed status values:

- `unknown`: placeholder exists, no source work started.
- `queued`: selected for source lookup or generation.
- `source_data_found`: required source datasets are identified.
- `generated`: terrain package generated but not validated.
- `validated`: QA checks passed.
- `packaged`: client/server package created.
- `published`: package is available to clients.
- `deprecated`: superseded by a newer tile version.
- `blocked`: source or generation issue needs manual review.

## Core Tables

### `terrain_tiles`

Tracks one logical 1 km tile.

Required fields:

- `tile_id`
- `grid_scheme`
- `projection`
- `utm_zone`
- `easting_min_m`
- `northing_min_m`
- `easting_max_m`
- `northing_max_m`
- `tile_size_m`
- `center_latitude`
- `center_longitude`
- `status`
- `biome_primary`
- `generation_version`
- `package_version`
- `created_at`
- `updated_at`

Optional solar/time fields should be stored only for tiles that have real source
work or a published package, not for every theoretical 1 km square. The
Earth-scale registry may eventually contain hundreds of millions of possible
tiles, so solar metadata is generated on demand for existing/active tiles.

Optional growing-zone fields follow the same rule. Crop viability, frost-free
windows, and growing-season length are generated only for tiles with real source
work or published packages and explicit growing-zone data.

Optional weather lookup fields are stored only for active/source-backed tiles.
For MVP, Ground Zero uses its tile-center latitude/longitude as the canonical
real-world lookup coordinate for live weather and temperature.

### `terrain_tile_neighbors`

Tracks adjacency for stitching and prefetching.

Required fields:

- `tile_id`
- `direction`
- `neighbor_tile_id`

### `terrain_tile_sources`

Tracks datasets used or intended for each tile.

Required fields:

- `tile_id`
- `source_kind`
- `source_name`
- `source_uri`
- `license_name`
- `source_version`
- `coverage_status`

### `terrain_tile_solar_metadata`

Tracks local time-zone and solar lookup metadata for tiles that actually exist
in the registry as source-backed, generated, validated, packaged, or published
tiles.

Required fields:

- `tile_id`
- `time_zone_id`
- `standard_utc_offset_hours`
- `daylight_utc_offset_hours`
- `solar_model`
- `generated_at`

### `terrain_tile_growing_season_metadata`

Tracks the active growing zone and season window for source-backed tiles. Crop
systems use this table to decide whether a crop with a given maturity time can
finish before the represented region's growing season closes.

Required fields:

- `tile_id`
- `growing_zone_label`
- `climate_profile`
- `growing_season_start_day`
- `growing_season_end_day`
- `frost_free_days`
- `min_average_growing_temp_c`
- `crop_safety_buffer_days`
- `data_basis`
- `generated_at`

### `terrain_tile_weather_lookup_metadata`

Tracks the real-world coordinate and provider routing used to fetch live
weather and temperature snapshots for a source-backed tile.

Required fields:

- `tile_id`
- `lookup_latitude`
- `lookup_longitude`
- `coordinate_source`
- `primary_provider`
- `fallback_provider_eligible`
- `lookup_rule`

Optional fields:

- `fallback_provider`
- `generated_at`

### `terrain_tile_packages`

Tracks generated downloadable packages.

Required fields:

- `package_id`
- `tile_id`
- `package_version`
- `unreal_engine_version`
- `world_partition_ready`
- `package_uri`
- `content_hash`
- `package_size_bytes`
- `created_at`
- `published_at`

### `terrain_tile_generation_jobs`

Tracks generation pipeline work.

Required fields:

- `job_id`
- `tile_id`
- `job_type`
- `status`
- `requested_at`
- `started_at`
- `finished_at`
- `log_uri`
- `error_summary`

## Separation Of Concerns

Terrain tile state should be separate from player-made world state.

Terrain registry owns:

- Source terrain and water data.
- Generated landscape package.
- Biome/resource hints.
- Tile status and package version.
- Client cache/version compatibility.

Player/world persistence owns:

- Player inventory, stats, and position.
- Placed structures.
- Resource depletion, if needed.
- Claims, settlements, containers, and ownership.
- Tile-local gameplay changes.

This separation lets us regenerate terrain tiles later without overwriting
player-built history.

## First Validation Rules

- Tile bounds must be exactly 1000 m x 1000 m in the projected coordinate system.
- Center latitude/longitude must fall inside tile bounds.
- Every published tile must have at least one elevation source.
- Every published tile must have a generation version and package version.
- Neighbor records must be reciprocal once adjacent tiles are generated.
- A tile package cannot be `published` until it is `validated`.
- Terrain package hash must change when package version changes.