Design PostGIS trail schema and Drizzle migration strategy

[kwiens]

Context

Move curated trail geometry, human-maintained metadata, operational status, condition observations, stories, event routes, and derived elevation data out of TypeScript/Mapbox and into an authoritative PostGIS model.

The model must prevent name-based joins, preserve history, distinguish observations from official decisions, and make derived elevation reproducible.

ORM recommendation

Use Drizzle ORM for application-owned schema, migrations, and typed queries.

Drizzle supports PostgreSQL, PostGIS geometry columns, GiST spatial indexes, raw PostGIS SQL, custom types, and database introspection. It does not automatically enable PostGIS, so the initial checked-in SQL migration must include:

CREATE EXTENSION IF NOT EXISTS postgis;

References:

• https://orm.drizzle.team/docs/guides/postgis-geometry-point
• https://orm.drizzle.team/docs/extensions
• https://orm.drizzle.team/docs/migrations
• https://orm.drizzle.team/docs/custom-types

Schema ownership rule

There must be exactly one schema authority:

• Drizzle migrations own all application/domain tables and database views.
• Directus introspects those tables and provides editing metadata, roles, displays, and workflows.
• Disable or procedurally prohibit production schema edits through Directus.
• Directus may create and migrate its own directus_* system tables.
• Drizzle migrations must not manage, rename, or delete directus_* tables.
• PostGIS constraints, triggers, views, and complex queries may use reviewed SQL migrations rather than forcing every construct through Drizzle's TypeScript API.
• Application/API writes should go through server-side code or Directus; clients must never connect directly to Neon.

Proposed domain model

Organizations and geography

organizations

Trail-maintaining organizations such as COTA.

• id uuid primary key
• name
• slug
• timestamps

regions

Named public map groupings.

• id uuid primary key
• name
• slug
• optional boundary geometry(MultiPolygon, 4326)

trail_systems

Recreation areas/trail systems.

• id uuid primary key
• organization_id
• region_id
• name
• slug
• description and contact fields
• optional boundary geometry(MultiPolygon, 4326)
• timestamps

Trail identity and metadata

trails

Stable trail identity and human-maintained metadata. A trail name is never its primary key.

• id uuid primary key
• trail_system_id
• name
• slug
• description
• difficulty
• directionality
• surface
• allowed_uses
• lifecycle_status: proposed, coming_soon, active, retired
• published_geometry_version_id nullable
• timestamps

trail_aliases

Crosswalk for legacy and external identifiers.

• id uuid primary key
• trail_id
• namespace: mapbox_name, arcgis_object_id, osm_way_id, legacy_slug, etc.
• value
• unique constraint on (namespace, value)

Immutable geometry history

trail_geometry_versions

Every material geometry change creates a new row. Published geometry is never overwritten.

• id uuid primary key
• trail_id
• version integer
• geometry geometry(MultiLineString, 4326)
• status: draft, in_review, published, superseded, rejected
• source_type: manual, arcgis_import, gpx_import, osm_import, survey
• source_reference
• geometry_hash
• change_summary
• created_by, reviewed_by, published_by
• timestamps
• unique (trail_id, version)
• GiST index on geometry

Database validation should enforce SRID/type/non-empty geometry and reject obviously invalid or implausible data. Application validation should additionally check suspicious jumps, duplicate segments, dramatic length changes, and geometry outside the expected region.

Derived elevation

trail_elevation_profiles

One reproducible calculation per geometry version and algorithm/data-source combination.

• id uuid primary key
• geometry_version_id
• distance_meters
• gain_meters
• loss_meters
• min_meters
• max_meters
• profile jsonb containing compact ordered samples
• sample_interval_meters
• dem_source
• dem_resolution_meters
• algorithm_version
• geometry_hash
• generated_at
• unique constraint covering geometry version, DEM source, and algorithm version

Derived numbers are never edited manually. A geometry publication triggers a durable elevation workflow, and publication state exposes whether derived data is pending, complete, or failed.

Conditions and closures

condition_reports

A report is an observation, not the official trail state.

• id uuid primary key
• trail_id
• optional reporter_user_id
• observed_at
• submitted_at
• optional location geometry(Point, 4326)
• condition categories/severity
• notes
• source: public, trusted_volunteer, staff, weather_model, sensor
• confidence
• moderation_status: pending, accepted, rejected, duplicate
• expires_at
• moderation fields and timestamps

Photos should live in object storage with asset records/relations, not in Postgres byte columns.

trail_advisories

Authoritative operational decisions.

• id uuid primary key
• nullable trail_id
• nullable trail_system_id
• optional affected-segment geometry
• status: information, caution, closed
• reason
• starts_at
• ends_at
• published_at
• published_by
• source/provenance
• timestamps

Temporary closure is an advisory. coming_soon belongs to the trail lifecycle and should not be modeled as a closure.

Create a database view or server-side projection for current public state with deterministic priority, for example: active closure > active caution > accepted recent reports > unknown.

Events and stories

event_routes

• id uuid primary key
• name, description, organizer
• event start/end
• registration/publication fields
• optional immutable route snapshot geometry(MultiLineString, 4326)

event_route_segments

Ordered reusable trail references:

• event_route_id
• position
• trail_id
• optional geometry-version ID
• optional start/end fractions or connector geometry

The route snapshot preserves history even if constituent trails later change.

trail_stories

• id uuid primary key
• trail_id
• title, slug, summary, rich body
• author/attribution
• publication state and dates
• related media

Cross-system audit/outbox

Directus revisions cover writes made through Directus, but API and background jobs also modify data. Add either:

• an application audit/event table populated consistently by all write paths, or
• database triggers for critical domain changes plus an outbox for publication jobs.

At minimum, geometry publication, advisory publication, moderation, and elevation generation must be attributable and auditable.

API and access boundaries

• The public Next.js/Expo clients consume a Vercel API/BFF or restricted Directus public endpoints.
• No browser/mobile client receives a Neon database credential.
• Okta authenticates staff and trusted reporters.
• Directus roles control CMS access.
• The Vercel API independently verifies tokens and enforces domain authorization.
• Anonymous/public reports are rate-limited and moderated.
• Prefer pooled Neon connections for application traffic and a direct connection for controlled migrations.

Initial publication strategy

For the regional dataset, serve cached GeoJSON generated from PostGIS. Do not add a tile service until measurement shows it is needed.

Later options:

• PostGIS ST_AsMVT behind a cached tile endpoint
• PMTiles generated on publication and stored on a CDN/object store
• a dedicated PostGIS tile server if live dynamic filtering requires it

Mapbox remains a renderer/basemap and is no longer the trail data source.

Migration plan

• Inventory current Mapbox, ArcGIS, OSM, TypeScript, and elevation identifiers.
• Import geometry into a staging schema/table.
• Import TypeScript metadata and match it to geometry with an explicit reviewed crosswalk.
• Assign stable UUIDs and populate trail_aliases.
• Verify record counts, unmatched names, lengths, bounds, and sample geometries.
• Import current elevation as derived legacy records with provenance.
• Run the new elevation pipeline from database geometry and compare results.
• Dual-read or feature-flag the PostGIS source in the app.
• Cut over only after map rendering and selection behavior match.
• Remove Mapbox tileset/name coupling and generated TypeScript metadata in a later cleanup.

Acceptance criteria

• Drizzle schema and reviewed SQL migrations represent the agreed model.
• PostGIS is enabled by migration and spatial columns use SRID 4326 with GiST indexes.
• Trail identity uses UUIDs; external/name identifiers live in aliases.
• Geometry history is immutable and publication is explicit.
• Condition reports and official advisories are separate.
• Elevation is linked to an exact geometry version with full provenance.
• Directus and Drizzle schema ownership boundaries are documented and tested.
• Representative seed data can be edited in Directus and queried through the Vercel application.
• Migration validation reports unmatched records and silent data loss.