missions/_docs/02_document/modules/database.md

Module: Azaion.Missions.Database (connection + migrator)

Files (2): Database/AppDataConnection.cs, Database/DatabaseMigrator.cs

NOTE (forward-looking): post-rename + post-GPS-Denied-removal state. Today's source still has Azaion.Flights.Database namespace, exposes Orthophotos / GpsCorrections ITables, and migrates 6 tables. After Jira AZ-EPIC children B5 (namespace), B7 (GPS-Denied removal), and B9 (DB migration) land, the shape described here is what stands.

Purpose

• AppDataConnection -- single LinqToDB DataConnection that exposes one ITable<T> property per persisted entity. Acts as the unit-of-work + query root for all services.
• DatabaseMigrator -- startup-time idempotent schema bootstrap. Issues a single multi-statement SQL block of CREATE TABLE IF NOT EXISTS + CREATE INDEX IF NOT EXISTS against the connection.

Public Interface

AppDataConnection

public class AppDataConnection(DataOptions options) : DataConnection(options) {
    public ITable<Vehicle>     Vehicles    => GetTable<Vehicle>();
    public ITable<Mission>     Missions    => GetTable<Mission>();
    public ITable<Waypoint>    Waypoints   => GetTable<Waypoint>();
    public ITable<MapObject>   MapObjects  => GetTable<MapObject>();
    public ITable<Media>       Media       => GetTable<Media>();        // schema owned by `annotations`
    public ITable<Annotation>  Annotations => GetTable<Annotation>();   // schema owned by `annotations`
    public ITable<Detection>   Detections  => GetTable<Detection>();    // schema owned by detection pipeline
}

DatabaseMigrator

public static class DatabaseMigrator {
    public static void Migrate(AppDataConnection db);  // synchronous; runs once at startup
}

Internal Logic

AppDataConnection

• Inherits LinqToDB's DataConnection. Constructor parameter DataOptions is built in Program.cs via new DataOptions().UsePostgreSQL(connectionString).
• Each ITable<T> property is computed via GetTable<T>() on every access -- cheap query-root handles, not cached state.
• Lifetime is scoped (registered via builder.Services.AddScoped), so each HTTP request gets its own DataConnection (and underlying Npgsql connection from the pool).

DatabaseMigrator

• Migrate(db) calls db.Execute(Sql) where Sql is a single string literal containing:
  • 4 CREATE TABLE IF NOT EXISTS statements: vehicles, missions, waypoints, map_objects.
  • 3 CREATE INDEX IF NOT EXISTS statements on the foreign-key columns: ix_missions_vehicle_id, ix_waypoints_mission_id, ix_map_objects_mission_id.
• Foreign-key constraints declared inline via REFERENCES (PostgreSQL NO ACTION is the default ON DELETE behavior — see service_mission.md and service_waypoint.md for the in-code cascade walks that compensate):
  • missions.vehicle_id REFERENCES vehicles(id)
  • waypoints.mission_id REFERENCES missions(id)
  • map_objects.mission_id REFERENCES missions(id)
• Column types: timestamps use PostgreSQL TIMESTAMP (no timezone) — missions.created_date, map_objects.first_seen_at, map_objects.last_seen_at. This means DateTime.Kind round-trips as Unspecified from the database; the application is the source of truth for "this value was stored as UTC" (MissionService.CreateMission writes DateTime.UtcNow).
• Defaults: enums default to 0, decimals (NUMERIC) to 0, booleans to FALSE, timestamps to NOW(), and the map_objects.label text column defaults to empty string ''. Nullable columns (waypoints.lat, waypoints.lon, waypoints.mgrs, map_objects.lat, map_objects.lon) have no DEFAULT clause.
• Tables intentionally NOT in this migrator: media, annotations, detection. These are exposed by AppDataConnection and consumed by services (delete cascades), but their schema is owned by other suite components (annotations migrates media + annotations; the detection pipeline owns detection). All edge-tier services share one local PostgreSQL on the device, so missions can read/delete from those tables without owning their DDL.
• Tables removed from this migrator (per Jira B7 + B9): orthophotos, gps_corrections. These are now owned by the separate gps-denied service (per ../../suite/_docs/11_gps_denied.md). Migration B9 includes a one-shot DROP TABLE IF EXISTS orthophotos; DROP TABLE IF EXISTS gps_corrections; for fielded edge devices that previously ran the legacy schema.

Dependencies

• LinqToDB, LinqToDB.Data
• Azaion.Missions.Database.Entities (all 7 entity types)

No upward dependencies.

Consumers

• Program.cs -- registers AppDataConnection as scoped, then resolves it once via app.Services.CreateScope() to call DatabaseMigrator.Migrate(db) before request handling starts.
• Services.VehicleService, Services.MissionService, Services.WaypointService -- all take AppDataConnection via primary-constructor injection.

Data Models

See modules/entities.md for column-level shape; see the SQL block in DatabaseMigrator.Sql for the authoritative DDL of the 4 owned tables.

Configuration

AppDataConnection itself reads no env vars; the connection string is supplied by the DI registration in Program.cs.

External Integrations

• PostgreSQL via Npgsql 10.0.2.

Security

• No SQL injection surface -- all services use LINQ expression trees (parameterized).
• The startup migrator runs DDL using whatever permissions the connection has. Production deployments where the app's user lacks CREATE TABLE would fail at startup.

Tests

None present.

Notes / Smells

1. Migrator is intentionally scoped to this service's owned tables (4 mission-attached tables). The 3 cross-service tables (media, annotations, detection) are migrated by their owning services into the same shared local PostgreSQL. Confirmed against ../../suite/_docs/00_top_level_architecture.md (Database Topology) and ../../suite/_docs/01_annotations.md.
2. No schema versioning -- IF NOT EXISTS is forward-only, additive only. Column drops, type changes, or constraint changes require either manual SQL or a real migration tool. The B9 DROP TABLE block is a one-time exception for the GPS-Denied removal.
3. Synchronous DDL at startup blocks the host until completion. For an empty DB this is microseconds; for a contended DB it's negligible. Acceptable for a small service.
4. No transaction wrapping -- the multi-statement Execute runs in PostgreSQL's implicit autocommit per statement (LinqToDB doesn't open a transaction unless you ask). All IF NOT EXISTS statements are individually idempotent, so partial failure leaves a partially-created schema; next startup completes it.
5. No LOWER(...) indexes for case-insensitive name searches in vehicles / missions. Likely fine for current scale.