missions/_docs/02_document/data_model.md

Azaion.Missions — Data Model

NOTE (forward-looking): this document reflects the post-rename, post-GPS-Denied-removal state. Today the source still has 9 entity files (Aircraft.cs, Flight.cs, Orthophoto.cs, GpsCorrection.cs are still present), 6 owned tables (incl. aircrafts, flights, orthophotos, gps_corrections), and the cascade still references the legacy GPS-Denied tables. Renames + table drops are tracked under Jira AZ-EPIC children B5 (namespace), B6 (rename), B7 (GPS-Denied removal), B9 (DB migration). The doc IS the spec for that work.

This document is the system-level data model. Per-component data access patterns live in the component descriptions; column-level shape lives in modules/entities.md. The authoritative ER diagram lives at ../../suite/_docs/00_database_schema.md (this is its scoped restatement).

1. Database Topology (the load-bearing convention)

This service participates in the suite-standard shared local PostgreSQL on each edge device pattern, documented in ../../suite/_docs/00_top_level_architecture.md § Database Topology.

                  ┌────────────────────────────────────────────────────────────┐
                  │                    Edge device (Jetson / OPi)              │
                  │                                                            │
                  │   ┌─────────────────────────────────────────────────────┐  │
                  │   │              postgres-local (PostgreSQL)            │  │
                  │   │   one DB instance, shared by every backend service  │  │
                  │   │                                                     │  │
                  │   │   tables owned by:                                  │  │
                  │   │     missions      → vehicles, missions, waypoints,  │  │
                  │   │                     map_objects   (this service)    │  │
                  │   │     annotations   → media, annotations              │  │
                  │   │     detection-px  → detection                       │  │
                  │   │     gps-denied    → orthophotos, gps_corrections    │  │
                  │   │                     (post-B7 — moved out of this    │  │
                  │   │                     repo, schema owned externally)  │  │
                  │   └─────────────────────────────────────────────────────┘  │
                  │           ▲                ▲                ▲              │
                  │   ┌───────┴──────┐  ┌──────┴───────┐  ┌─────┴───────┐      │
                  │   │ missions svc │  │annotations svc│  │ ... others  │      │
                  │   └──────────────┘  └──────────────┘  └─────────────┘      │
                  └────────────────────────────────────────────────────────────┘

Each service's schema-ownership rule:

• The owner is the only writer for the table's lifecycle (CRUD).
• The owner runs the migrations (CREATE TABLE, CREATE INDEX).
• Other services may read any table through their own DataConnection (LinqToDB sees the full schema by reflection on the live DB).
• Other services may delete rows from non-owned tables only as part of a documented cross-service cascade (this service's mission-delete walk is the canonical example — see architecture.md ADR-003).

The pattern is enforced by convention, not by per-service DB users. Every service connects with the same DATABASE_URL credentials and could in principle write to any table. Reviews keep this honest.

2. Tables this service owns (post-B7 + B9)

The migrator (DatabaseMigrator.Migrate) owns the schema for exactly 4 tables and runs CREATE TABLE IF NOT EXISTS + CREATE INDEX IF NOT EXISTS for each on every startup.

Table	Purpose	Owner component	Writer	Schema-creating service
vehicles	Operator-managed inventory of mission-capable assets (Plane / Copter / UGV / GuidedMissile)	01_vehicle_catalog (logically); 04_persistence (table)	01_vehicle_catalog (VehicleService)	this service
missions	Planned mission record; FK to vehicle	02_mission_planning (logically); 04_persistence (table)	02_mission_planning (MissionService)	this service
waypoints	Ordered geo-points within a mission; FK to mission	02_mission_planning (logically); 04_persistence (table)	02_mission_planning (WaypointService)	this service
map_objects	H3-indexed detection projection (class + confidence + spatial position); FK to mission	04_persistence (table)	autopilot (per ../../suite/_docs/06_autopilot_design.md) — this service is responsible for schema migration + cascade delete only	this service

3. Tables this service borrows (read-only; cascade-delete only)

These tables exist in the same postgres-local. This service exposes ITable<T> accessors through AppDataConnection so it can read ids and delete rows during its mission/waypoint cascade. It never inserts or updates them.

Table	Schema source	Writer	This service's interaction
media	annotations migrator	annotations (Media CRUD)	Read id, waypoint_id; cascade-delete only
annotations	annotations migrator	annotations (Annotations CRUD)	Read id, media_id; cascade-delete only
detection (singular — not this service's call to rename)	detection pipeline migrator	detections / ai-training	Read id, annotation_id; cascade-delete only

4. Tables removed in B7 + B9

These tables were owned by this repo before the rename refactor; per the plan they now belong to the new gps-denied service (../../suite/_docs/11_gps_denied.md).

Table	Pre-B7 owner	Post-B7 owner	Migration step
orthophotos	this repo	gps-denied	B7 removes the entity + service code; B9 adds DROP TABLE IF EXISTS orthophotos to this service's migrator (one-shot for fielded devices that previously ran the legacy schema)
gps_corrections	this repo	gps-denied	Same — B7 + B9

The new gps-denied service owns these tables' lifecycle. It references mission_id and waypoint_id from its own tables as plain GUIDs. There is no runtime call between this service and gps-denied — see architecture.md ADR-007 and 02_mission_planning § cascade.

5. Entity-Relationship Diagram (post-B7)

erDiagram
    VEHICLE ||--o{ MISSION : "vehicle_id (FK)"
    MISSION ||--o{ WAYPOINT : "mission_id (FK)"
    MISSION ||--o{ MAP_OBJECT : "mission_id (FK)"
    WAYPOINT ||--o{ MEDIA : "waypoint_id (FK, nullable)"
    MEDIA ||--o{ ANNOTATION : "media_id (FK)"
    ANNOTATION ||--o{ DETECTION : "annotation_id (FK)"

    VEHICLE {
        uuid id PK
        int type "VehicleType: Plane Copter UGV GuidedMissile"
        text model
        text name
        int fuel_type "FuelType: Electric Gasoline Diesel"
        decimal battery_capacity
        decimal engine_consumption
        decimal engine_consumption_idle
        bool is_default
    }
    MISSION {
        uuid id PK
        timestamp created_date "PG TIMESTAMP (no TZ), DEFAULT NOW()"
        text name
        uuid vehicle_id FK "REFERENCES vehicles(id), NO ACTION on delete"
    }
    WAYPOINT {
        uuid id PK
        uuid mission_id FK "REFERENCES missions(id), NO ACTION on delete"
        decimal lat "nullable"
        decimal lon "nullable"
        text mgrs "nullable"
        int waypoint_source "WaypointSource enum, DEFAULT 0"
        int waypoint_objective "WaypointObjective enum, DEFAULT 0"
        int order_num "DEFAULT 0"
        decimal height "DEFAULT 0"
    }
    MAP_OBJECT {
        uuid id PK
        uuid mission_id FK "REFERENCES missions(id), NO ACTION on delete"
        text h3_index "Uber H3 hex grid"
        text mgrs
        decimal lat "nullable"
        decimal lon "nullable"
        int class_num "DEFAULT 0"
        text label "DEFAULT ''"
        decimal size_width_m "DEFAULT 0"
        decimal size_length_m "DEFAULT 0"
        decimal confidence "DEFAULT 0"
        int object_status "ObjectStatus enum, DEFAULT 0"
        timestamp first_seen_at "PG TIMESTAMP (no TZ), DEFAULT NOW()"
        timestamp last_seen_at "PG TIMESTAMP (no TZ), DEFAULT NOW()"
    }
    MEDIA {
        text id PK "XxHash64-based; computed by annotations service"
        uuid waypoint_id FK "nullable — Media may attach to a non-waypoint context"
    }
    ANNOTATION {
        text id PK "XxHash64-based"
        text media_id FK
    }
    DETECTION {
        uuid id PK
        text annotation_id FK
    }

The diagram above is a scoped restatement of ../../suite/_docs/00_database_schema.md (authoritative). Borrowed tables (media, annotations, detection) show only the columns this service touches; their full column shapes are owned by their respective services.

6. Key Relationships and Invariants

Owned-table invariants

• mission.vehicle_id MUST reference an existing vehicle.id — enforced by FK (REFERENCES vehicles(id) declared in the migrator) + by MissionService existence check at create / update. The two together close the TOCTOU gap (FK rejects insert with PostgreSQL error 23503 if the vehicle was deleted between check and insert; UX surfaces as a 500 instead of a 400 in that race window — see 02_mission_planning Caveats #4 and the AC-2.8 entry in 00_problem/acceptance_criteria.md).
• waypoint.mission_id MUST reference an existing mission.id — enforced by FK + by WaypointService existence check at create. The composite WHERE on update/delete (w.MissionId == missionId && w.Id == waypointId) collapses "parent missing" and "child missing" into a single 404 — see service_waypoint.md Caveats #2.
• map_object.mission_id MUST reference an existing mission.id — enforced by FK only. autopilot is the writer; missions is the cascade-deleter.
• At most one vehicle.is_default = TRUE is the spec invariant. Code enforces "exactly one default" by clearing the flag on every other row before setting it on the target — stricter than spec, race-prone without a transaction. Tracked under Jira AZ-551 (B12) for resolution.
• All FK columns have REFERENCES declared in the migrator (no ON DELETE clause; PostgreSQL defaults to NO ACTION). The in-code cascade walks in MissionService.DeleteMission and WaypointService.DeleteWaypoint delete child rows before parent rows — see architecture.md ADR-003 for why the cascade lives in code instead of ON DELETE CASCADE.
• All timestamp columns use PostgreSQL TIMESTAMP (no timezone): missions.created_date, map_objects.first_seen_at, map_objects.last_seen_at. DateTime.Kind round-trips as Unspecified from the database; the application writes DateTime.UtcNow and treats values as UTC by convention.

Cross-service-table invariants (cascade only)

• media.waypoint_id is nullable — Media can attach to a non-waypoint context (mission-level media); enforcement is on annotations's side.
• Cascade order is FK-driven — the mission-delete walk in MissionService.DeleteMission deletes child rows before parent rows: map_objects → detection → annotations → media → waypoints → missions. See diagrams/flows/flow_mission_cascade_delete.md for the authoritative order.

Cross-data-model conventions (suite-wide)

• Mixed PK types: vehicles, missions, waypoints, map_objects, detection use uuid (LinqToDB Guid); media, annotations use text (XxHash64-based content hash, computed by annotations). The text-PK shape lets annotations deduplicate the same physical media across services per ../../suite/_docs/00_database_schema.md.
• detection is a singular table name while every other table is plural. The detection pipeline owns the naming choice — this service does not "fix" it.

7. Indexes

Defined by DatabaseMigrator.Migrate (post-B7+B9):

Index	Table	Purpose
PK on id	vehicles, missions, waypoints, map_objects	Lookup-by-id; created implicitly by PRIMARY KEY
ix_missions_vehicle_id	missions	Existence check on vehicle delete; FK lookup
ix_waypoints_mission_id	waypoints	List nested waypoints; cascade-delete walk
ix_map_objects_mission_id	map_objects	Cascade-delete walk on mission delete

Indexes that DO NOT exist (could matter on growth — carry-forward as opportunistic improvements):

• No index on vehicles.is_default — partial index WHERE is_default would help if catalog grows past low hundreds of rows. Today the catalog is small.
• No index on missions.created_date — used as the ORDER BY in the paginated list. Full scan + sort today; fine while mission count is in the hundreds, becomes relevant past ~10k.
• No LOWER(...) indexes for case-insensitive name search — full scan today; fine while owned tables are small.
• No order-by index on waypoints.order_num — sort is in-memory after WHERE mission_id = ? returns. Fine for the typical-case dozens of waypoints per mission.

8. Domain Enums (stored as INTEGER in the DB)

Defined under Enums/; rendered to / from PostgreSQL INT columns by LinqToDB.

Enum	Backing column(s)	Values	Notes
VehicleType	vehicles.type	Plane=0, Copter=1, UGV=2, GuidedMissile=3	Extended from {Plane, Copter} in B6
FuelType	vehicles.fuel_type	Electric, Gasoline, Diesel	May not fit GuidedMissile — carry-forward Phase C decision (01_vehicle_catalog Caveats #6)
WaypointSource	waypoints.waypoint_source	(Operator-defined; values per Enums/WaypointSource.cs)	Source attribution for the waypoint
WaypointObjective	waypoints.waypoint_objective	(Operator-defined; values per Enums/WaypointObjective.cs)	Mission-time objective tag
ObjectStatus	map_objects.object_status	(Detection-pipeline-defined)	Cross-cutting status enum; lives in 04_persistence because it's used by MapObject (the only consumer today)

There are no CHECK constraints on the integer columns — sending an invalid integer (e.g., VehicleType = 99) is accepted at the DB level and surfaces only when LinqToDB tries to deserialize. 01_vehicle_catalog Caveats #3 notes the missing input validation.

9. Migration strategy

This service uses forward-only-additive schema bootstrap:

• Every startup: DatabaseMigrator.Migrate runs all CREATE TABLE IF NOT EXISTS + CREATE INDEX IF NOT EXISTS statements. Idempotent on a steady-state device.
• Column drops, type changes, constraint changes are not supported by this migrator; they would need manual SQL or a future migration tool (Flyway / EF Core migrations).
• The B9 ticket adds the one explicit destructive step in the migrator's history: DROP TABLE IF EXISTS orthophotos; DROP TABLE IF EXISTS gps_corrections;. Idempotent on devices that already cleaned up; one-shot on fielded edge devices that previously ran the legacy schema. Out-of-band ordering: deploy gps-denied first so it owns its own copy of the schema before missions drops the legacy tables (see diagrams/flows/flow_startup_migration.md error scenarios).

See architecture.md ADR-004 for the rationale of the IF NOT EXISTS approach.

10. Seed data

None. The migrator only creates schema. Vehicles, missions, and waypoints are operator-created via the API on first use.

11. Backward compatibility

• No schema versioning in this service today. Compatibility is enforced by the additive-only convention plus the B9 one-shot exception.
• Wire shape (HTTP) is currently divergent from spec for entity / DTO bodies (PascalCase via System.Text.Json defaults) and for the error envelope's missing errors field. Note: the error envelope is already camelCase on case (accidental match — middleware writes an anonymous object literal whose property names are lowercase-first by construction). Cross-version compatibility for clients (UI, autopilot) is implicit — both consumers were built against the live PascalCase entity shape. The future camelCase migration on entity bodies (out of this Epic) would be a coordinated cutover (see architecture.md ADR-002).
• No rollback mechanism — the additive-only migrator does not record a downgrade path. The B9 DROP is unidirectional; once gps-denied owns the tables there is no recipe to "give them back" to missions.

12. Observed data sizes (typical edge deployment)

Not specified in spec. Estimated from operational context (single operator, single edge device, single deployment cycle):

Table	Typical row count	Growth driver
vehicles	tens to low hundreds	Manual CRUD; rarely grows past the operator's usable fleet
missions	hundreds to low thousands per device per year	Operator activity
waypoints	typically 10–100 per mission (dominant), occasionally 1000+	Mission complexity
map_objects	hundreds to tens of thousands per mission	Detection cadence + mission duration; dominant table by row count
media (borrowed)	one row per captured media artifact	Owned by annotations; this service deletes via cascade
annotations (borrowed)	one row per labeled annotation	Owned by annotations
detection (borrowed)	one row per high-confidence detection	Owned by detection pipeline

These are rough operational estimates, not load-test results. They influence indexing decisions (see § 7) and inform why no transaction wrap on cascade delete is "tolerable today" — typical mission deletes touch single-digit thousands of rows at most, which is well within a single PG round-trip's span.