Jens
faeb58ef6d
GeoIntel release gates / Compile, test, contracts and builds (push) Successful in 1m49s
GeoIntel release gates / Python and npm vulnerability policy (push) Successful in 21s
GeoIntel release gates / Production AI image, SBOM and container scan (push) Successful in 5m39s
GeoIntel release gates / Deploy exact gated revision to Unraid (push) Failing after 58m43s
83 lines
1.6 KiB
Markdown
83 lines
1.6 KiB
Markdown
# Database Migration Plan
|
|
|
|
## Migration tool
|
|
|
|
Use Alembic for all schema migrations.
|
|
|
|
## Migration principles
|
|
|
|
- Every database schema change must be represented by an Alembic migration.
|
|
- Geometry columns must use PostGIS types.
|
|
- Migrations must be safe to rerun in development after database reset.
|
|
- Never modify old migrations after they have been used as a release baseline.
|
|
|
|
## Initial migration sequence
|
|
|
|
### 0001_enable_postgis
|
|
|
|
Creates the PostGIS extension.
|
|
|
|
```sql
|
|
CREATE EXTENSION IF NOT EXISTS postgis;
|
|
```
|
|
|
|
### 0002_core_tables
|
|
|
|
Creates:
|
|
|
|
- projects
|
|
- areas
|
|
- datasets
|
|
- layers
|
|
- analysis_runs
|
|
- metrics
|
|
- exports
|
|
|
|
### 0003_geoai_tables
|
|
|
|
Creates:
|
|
|
|
- detections
|
|
- segmentations
|
|
- quality_checks
|
|
- quality_check_items
|
|
|
|
### 0004_jobs_and_events
|
|
|
|
Creates:
|
|
|
|
- jobs
|
|
- domain_events
|
|
|
|
## Geometry columns
|
|
|
|
Use SRID 4326 for persisted canonical geometries unless a specific processing table requires another CRS.
|
|
|
|
Recommended columns:
|
|
|
|
```text
|
|
areas.geometry: POLYGON/MULTIPOLYGON, SRID 4326
|
|
detections.geometry: POLYGON, SRID 4326
|
|
quality_check_items.geometry: GEOMETRY, SRID 4326
|
|
```
|
|
|
|
Area measurements must not be calculated directly in EPSG:4326. Use projected CRS transformations in services.
|
|
|
|
## Indexes
|
|
|
|
Every geometry column that is queried spatially needs a GIST index.
|
|
|
|
Core indexes:
|
|
|
|
- projects.created_at
|
|
- areas.project_id
|
|
- datasets.project_id
|
|
- layers.dataset_id
|
|
- analysis_runs.project_id
|
|
- detections.analysis_run_id
|
|
- quality_checks.analysis_run_id
|
|
|
|
## Development reset
|
|
|
|
Development reset may drop and recreate the database. Production-style migrations must still remain valid.
|