Database Schema Reference

The Gateway persists all of its state in a single PostgreSQL database, accessed through Flask-SQLAlchemy. Twelve tables are defined as ORM models under src/models/; a parallel set of Isaac Sim objects (4 tables, 3 views, 2 functions, 3 triggers) exists only as raw SQL in one migration and has no ORM model. Schema is managed by Alembic (flask db upgrade) in production, with an APP_ENVIRONMENT=dev shortcut that calls db.create_all(). This page is the consolidated, column-level reference. For narrative context see:

Schema Overview

Table inventory, ownership, and per-table cascade rules explained in prose.

Migrations & Connection

DBConnector, the Alembic chain, pool settings, and dev-mode create_all().

Mission & Geofence Format

How MissionPoint/GeofencePoint map to MAVLink waypoint/fence items.

Isaac Sim Raw SQL

The raw-SQL Isaac Sim tables, views, functions and triggers in depth.

Column facts on this page come from the ORM models in src/models/ and are cross-checked against the Alembic migrations in migrations/versions/. Where the two diverge (e.g. a misplaced nullable=False), that divergence is flagged as a gotcha. Migration head at time of writing: r3m4n5o6p7q8.

Core ER diagram

Everything is owned by a user. Most child tables CASCADE on user delete; the exceptions (drone.mission_id, subscription, payment) are called out under Cascade & ownership.

Cascade & ownership model

Foreign-key ondelete behavior is not uniform — this is the single most important thing to internalize before writing a migration or a delete path.

FK	ondelete	Consequence
`drone.user_id`, `mission.user_id`, `mission_point.user_id`, `mission_point.mission_id`, `asset.user_id`, `asset.drone_id`, `mission_execution.user_id`, `mission_execution.drone_id`, `geofence.user_id`, `geofence_point.`, `calendar_event.user_id`, `calendar_event_occurrence.event_id`, `user_drone_access.`, `isaac_sim_instances.user_id`, `isaac_sim_usage_tracking.*`, `subscription.user_id`, `payment.user_id`	`CASCADE`	Deleting the parent removes children automatically.
`mission_execution.mission_id`, `asset.execution_id`, `mission_execution.log_asset_id`, `calendar_event.drone_id`, `calendar_event.mission_id`, `calendar_event.execution_id`, `calendar_event_occurrence.execution_id`, `isaac_sim_instances.drone_id`, `payment.subscription_id`	`SET NULL`	Link is nulled; the child survives (e.g. a manual flight has `mission_id = NULL`).
`drone.mission_id`	none (default `RESTRICT`)	`mission_service.delete()` must manually null `drone.mission_id` and delete `mission_point` rows before deleting a `mission`, or the delete raises.
`subscription.user_id`, `payment.user_id`, `payment.subscription_id`	`CASCADE` / `CASCADE` / `SET NULL` in migration `h3c4d5e6f7g8`	Model-vs-migration divergence: the ORM models omit `ondelete`, so `db.create_all()` (dev) would make these `RESTRICT`, but the production migration builds them as `CASCADE`/`CASCADE`/`SET NULL` (rows above). In production, deleting a user cascade-deletes their subscription and payments — consistent with the rest of the user tree.

drone.mission_id has no ON DELETE rule. Any code that deletes a mission must first run the equivalent of Drone.filter(mission_id == X).update(mission_id=None) and delete its mission_point rows, exactly as mission_service.delete() does. Adding ON DELETE SET NULL in a migration would let you drop that manual cleanup.

Identity & fleet

`user` — `src/models/user.py`

Column	Type	Constraints
`id`	Integer	PK
`username`	VARCHAR(80)	UNIQUE, nullable (was `NOT NULL` in the initial migration, relaxed in the model)
`password`	VARCHAR(255)	NOT NULL — bcrypt hash
`ip`	VARCHAR(100)	UNIQUE, nullable — the user’s WireGuard VPN IP
`is_active`	Boolean	NOT NULL, default `false` — gates login/service; also used to soft-ban
`level`	ENUM `userlevel`	nullable, default `NULL` — `ADMIN` / `CUSTOMER`
`activation_token`	VARCHAR(10)	UNIQUE, nullable
`email`	VARCHAR(100)	UNIQUE, NOT NULL
`report_extra_emails`	TEXT	nullable — comma-separated extra report recipients

get_report_recipients() returns [email] + parsed extras, de-duplicated with the primary email kept first. See Executions & Reports.

`drone` — `src/models/drone.py`

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL, indexed (`ix_drone_user_id`)
`mac`	VARCHAR(100)	UNIQUE, NOT NULL — deprecated but still required (see gotcha)
`name`	VARCHAR(100)	NOT NULL
`ip`	VARCHAR(100)	UNIQUE, NOT NULL, indexed (`ix_drone_ip`) — VPN IP (physical) or container host (SITL)
`port`	Integer	NOT NULL — `9090` for physical, `9090+n` for SITL
`mission_id`	Integer	FK → `mission.id`, no ondelete, nullable — last-uploaded mission
`type`	ENUM `dronetype`	`physical` / `sitl` — DB-nullable (see gotcha)
`vehicle_type`	ENUM `dronevehicletype`	`rover` / `copter`, default `rover`, column `NOT NULL` in migrations
`video_room_id`	Integer	UNIQUE — Janus room number
`video_room_password`	VARCHAR(100)
`video_room_token`	VARCHAR(300)
`activation_token`	VARCHAR(10)	UNIQUE, nullable

`user_drone_access` — `src/models/user_drone_access.py`

Join table for sharing a drone with additional users.

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL
`drone_id`	Integer	FK → `drone.id` CASCADE, NOT NULL

Unique constraint uq_user_drone_access(user_id, drone_id).

Missions & waypoints

`mission` — `src/models/mission.py`

Route-template header. Unique constraint uq_user_name(user_id, name).

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL
`name`	VARCHAR(255)	NOT NULL

`mission_point` — `src/models/mission_point.py`

An ordered waypoint. Carries both a semantic type (base/fly/safepoint) and the full MAVLink waypoint payload. Rows are read ordered by sequence.

Column	Type	Constraints
`id`	Integer	PK
`mission_id`	Integer	FK → `mission.id` CASCADE, NOT NULL
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL
`lat` / `lng` / `altitude`	Float	NOT NULL
`type`	ENUM `mission_point_types`	NOT NULL — `base` / `fly` / `safepoint`
`label`	VARCHAR(255)	nullable
`sequence`	Integer	NOT NULL — ordering key
`frame`	Integer	default `3` (GLOBAL_RELATIVE_ALT)
`command`	Integer	default `16` (WAYPOINT); 22=TAKEOFF, 21=LAND, 20=RTL
`is_current`	Boolean	default `false`
`autocontinue`	Boolean	default `true`
`param1`–`param4`	Float	default `0.0`

to_mavlink_waypoint() emits x_lat=lat, y_long=lng, z_alt=altitude plus frame/command/is_current/autocontinue/param1-4. DroneControlService.push_mission() (src/service/drone_control_service.py:784) loads points by sequence, converts each, and prepends a synthetic TAKEOFF (cmd 22) if the first command isn’t already 22, then pushes over rosbridge and verifies wp_transfered == len.

The CLAUDE.md “Mission Format” description (speed command 178, auto-generated reversed return path, RTL command 20 at the end) is not implemented in push_mission(). The backend uploads the stored waypoints as-is plus the TAKEOFF prepend. See Mission & Geofence MAVLink Format.

Flights & media

`mission_execution` — `src/models/mission_execution.py`

The realized-flight record. An arm/flight creates a row (status = in_progress, mission_id = NULL for a manual flight); after landing, LogAnalysisService parses the ArduPilot .bin (referenced by log_asset_id) and back-fills the ~40 metric columns. Indexed on user_id, drone_id, mission_id, started_at, status.

Identity, timing, status & report columns

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL, idx
`drone_id`	Integer	FK → `drone.id` CASCADE, NOT NULL, idx
`mission_id`	Integer	FK → `mission.id` SET NULL, nullable, idx (NULL = manual)
`started_at`	DateTime(tz)	NOT NULL, idx
`ended_at`	DateTime(tz)	nullable
`duration_seconds`	Integer	nullable
`status`	ENUM `executionstatus`	NOT NULL, default `in_progress`, idx
`report_sent_at`	DateTime(tz)	nullable
`report_pending`	Boolean	NOT NULL, default `false`
`archive_s3_key`	VARCHAR(500)	nullable — ZIP of all assets
`archive_generated_at`	DateTime(tz)	nullable
`log_asset_id`	Integer	FK → `asset.id` SET NULL, nullable
`notes`	TEXT	nullable
`created`	DateTime(tz)	NOT NULL, default `now(utc)`
`modified`	DateTime(tz)	NOT NULL, default/onupdate `now(utc)`

Log-analysis metric columns (all nullable)

Group	Columns
Flight stats	`log_flight_time_seconds` (Int), `total_distance_meters`, `max_altitude_meters`, `avg_speed_ms`, `max_speed_ms` (Float), `log_analyzed_at` (DateTime tz)
Battery	`battery_start_voltage`, `battery_min_voltage`, `battery_end_voltage`, `battery_energy_wh`, `battery_remaining_pct` (Float)
GPS	`gps_avg_satellites` (Float), `gps_min_satellites` (Int), `gps_avg_hdop` (Float), `gps_fix_type` (Int), `gps_fix_type_name` (VARCHAR 20)
Efficiency	`hover_throttle_pct` (Float)
Vibration	`vibe_x/y/z_avg`, `vibe_x/y/z_max` (Float), `clip_0`, `clip_1`, `clip_2` (Int)
Errors	`error_count` (Int, default 0), `error_events` (JSON)
Modes	`mode_change_count` (Int, default 0), `failsafe_count` (Int, default 0), `mode_changes` (JSON)
Motors	`motor_balance_avg`, `motor_balance_max`, `motor_balance_min` (JSON arrays), `motor_count` (Int, default 0)

These metric columns were added across migrations d89dbd0532c0, 55cc964f0c63, n9i0j1k2l3m4, o0j1k2l3m4n5, and p1k2l3m4n5o6. See Executions, Log Analysis & Reports.

`asset` — `src/models/asset.py`

S3-backed media (video / image / logs). Uploaded during a flight, an asset carries execution_id; uploaded outside a tracked flight, it’s NULL.

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL, idx (`ix_asset_user_id`)
`drone_id`	Integer	FK → `drone.id` CASCADE, NOT NULL, idx (`ix_asset_drone_id`)
`execution_id`	Integer	FK → `mission_execution.id` SET NULL, nullable, idx (`ix_asset_execution_id`)
`file_name`	VARCHAR(255)	NOT NULL
`s3_key`	VARCHAR(1024)	nullable
`checksum`	VARCHAR(64)	nullable, idx (`ix_asset_checksum`)
`mime_type`	VARCHAR(255)	nullable
`content_length`	Integer	nullable, default `0`
`asset_type`	ENUM `assettype`	`video` / `image` / `logs`
`status`	ENUM `assetstatus`	default `'pending'` — see gotcha
`upload_id`	VARCHAR(255)	nullable — multipart upload id
`created`	DateTime(tz)	default `now`
`modified`	DateTime(naive)	default/onupdate `now` — timezone inconsistency

See S3 Assets, HLS Video & Execution Archives.

Geofences

`geofence` — `src/models/geofence.py`

Unique constraint uq_geofence_user_name(user_id, name). geofence_points cascade delete-orphan at the ORM level.

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL, idx (`ix_geofence_user_id`)
`name`	VARCHAR(255)	NOT NULL
`type`	ENUM `geofence_types`	NOT NULL — `polygon` / `circle`
`fence_type`	ENUM `fence_types`	NOT NULL, default `exclusion` — `inclusion` / `exclusion`
`enabled`	Boolean	NOT NULL, default `true`

`geofence_point` — `src/models/geofence_point.py`

A fence vertex (polygon) or center (circle), stored as a MAVLink fence item.

Column	Type	Constraints
`id`	Integer	PK
`geofence_id`	Integer	FK → `geofence.id` CASCADE, NOT NULL, idx (`ix_geofence_point_geofence_id`)
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL, idx (`ix_geofence_point_user_id`)
`lat` / `lng`	Float	NOT NULL
`sequence`	Integer	NOT NULL
`frame`	Integer	default `3`
`command`	Integer	NOT NULL — MAVLink fence cmd (below)
`param1`–`param4`	Float	default `0.0`

command is a MAVLink fence command; param1 is the polygon vertex count or the circle radius in meters. to_mavlink_fence_item() forces z_alt = 0.0 (fences are 2D).

Command	Constant	Meaning	`param1`
`5001`	`POLYGON_VERTEX_INCLUSION`	stay inside polygon	vertex count
`5002`	`POLYGON_VERTEX_EXCLUSION`	stay outside polygon	vertex count
`5003`	`CIRCLE_INCLUSION`	stay inside circle	radius (m)
`5004`	`CIRCLE_EXCLUSION`	stay outside circle	radius (m)

See Missions & Geofences API.

Calendar & scheduling

`calendar_event` — `src/models/calendar_event.py`

A scheduled (optionally recurring, RRULE) mission. Unique constraint uq_user_event_title_time(user_id, title, scheduled_time).

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL, idx
`title`	VARCHAR(255)	NOT NULL
`description`	TEXT	nullable
`scheduled_time`	DateTime(tz)	NOT NULL, idx
`status`	ENUM `event_status`	NOT NULL, default `scheduled`, idx
`drone_id`	Integer	FK → `drone.id` SET NULL, nullable, idx
`mission_id`	Integer	FK → `mission.id` SET NULL, nullable
`execution_id`	Integer	FK → `mission_execution.id` SET NULL, nullable
`recurrence_rule`	VARCHAR(255)	nullable — iCal RRULE
`created_at` / `updated_at`	DateTime(tz)	NOT NULL, default/onupdate `now(utc)`

`calendar_event_occurrence` — `src/models/calendar_event_occurrence.py`

A sparse exceptions table: recurring occurrences default to scheduled, and only occurrences whose status deviates are persisted. Unique constraint uq_event_occurrence_time(event_id, occurrence_time).

Column	Type	Constraints
`id`	Integer	PK
`event_id`	Integer	FK → `calendar_event.id` CASCADE, NOT NULL, idx
`occurrence_time`	DateTime(tz)	NOT NULL, idx
`status`	ENUM `event_status`	NOT NULL, default `scheduled`
`execution_id`	Integer	FK → `mission_execution.id` SET NULL, nullable, idx
`created_at` / `updated_at`	DateTime(tz)	NOT NULL, default/onupdate `now(utc)`

See Users, IP Allocation, Calendar & Isaac Sim.

Billing

`subscription` — `src/models/subscription.py`

One row per user (user_id UNIQUE). Pricing model is €120 / vehicle / year.

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` (`CASCADE` in migration `h3c4d5e6f7g8`; model omits `ondelete`, so `create_all` would yield `RESTRICT`), NOT NULL, UNIQUE
`stripe_customer_id`	VARCHAR(255)	UNIQUE, nullable
`stripe_subscription_id`	VARCHAR(255)	UNIQUE, nullable
`status`	ENUM `subscription_status`	NOT NULL, default `incomplete`
`vehicle_count`	Integer	NOT NULL, default `0`
`current_period_start` / `current_period_end`	DateTime (naive)	nullable
`created_at` / `updated_at`	DateTime(tz)	NOT NULL
`canceled_at`	DateTime(tz)	nullable

`payment` — `src/models/subscription.py`

Invoice/receipt history. amount is stored in cents.

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` (`CASCADE` in migration `h3c4d5e6f7g8`; model omits `ondelete`, so `create_all` would yield `RESTRICT`), NOT NULL
`subscription_id`	Integer	FK → `subscription.id` (`SET NULL` in migration `h3c4d5e6f7g8`; model omits `ondelete`, so `create_all` would yield `RESTRICT`), nullable
`stripe_payment_intent_id`	VARCHAR(255)	UNIQUE, nullable
`stripe_invoice_id`	VARCHAR(255)	UNIQUE, nullable
`amount`	Integer	NOT NULL — cents (€120.00 → `12000`)
`currency`	VARCHAR(3)	NOT NULL, default `eur`
`status`	VARCHAR(50)	NOT NULL — `succeeded` / `failed` / `pending`
`description`	VARCHAR(500)	nullable
`vehicle_count`	Integer	NOT NULL, default `1`
`created_at`	DateTime(tz)	NOT NULL

See Stripe Billing & Vehicle Limits.

Enum types

All enums are global PostgreSQL types. Two of them (event_status, subscription_status — the production type names created by migrations i4d5e6f7g8h9 and h3c4d5e6f7g8; db.create_all() would instead name them eventstatus/subscriptionstatus) declare values_callable so PostgreSQL stores the lowercase value; the rest store the enum name. For most enums the member name equals its value, so it makes no difference — except userlevel, whose type labels are the uppercase names ADMIN/CUSTOMER while the Python .value is lowercase admin/customer.

Type	Labels stored in PostgreSQL	Used by
`userlevel`	`ADMIN`, `CUSTOMER` (names)	`user.level`
`dronetype`	`physical`, `sitl`	`drone.type`
`dronevehicletype`	`rover`, `copter`	`drone.vehicle_type`
`mission_point_types`	`base`, `fly`, `safepoint`	`mission_point.type`
`geofence_types`	`polygon`, `circle`	`geofence.type`
`fence_types`	`inclusion`, `exclusion`	`geofence.fence_type`
`executionstatus`	`in_progress`, `completed`, `aborted`, `error`	`mission_execution.status`
`assettype`	`video`, `image`, `logs`	`asset.asset_type`
`assetstatus`	`pending`, `ready`, `error`	`asset.status`
`event_status`	`scheduled`, `completed`, `cancelled` (values)	`calendar_event.status`, `calendar_event_occurrence.status`
`subscription_status`	`active`, `canceled`, `past_due`, `incomplete`, `trialing`, `unpaid` (values)	`subscription.status`

Dropping or recreating a table that uses one of these enums must manage the type lifecycle separately — a global PostgreSQL enum is not dropped with the table that references it.

Isaac Sim tables (raw SQL, no ORM)

Migration migrations/versions/e85b13d42b08_add_isaac_sim_tables.py creates 4 tables + 3 views + 2 PL/pgSQL functions + 3 triggers entirely as raw SQL. There is no SQLAlchemy model for any of it; src/service/instance_scheduler_service.py reads and writes these tables with a raw psycopg2 cursor (self.db.cursor(), commit(), rollback()), bypassing the ORM/session completely.

isaac_sim_instances

EC2 instance records. Indexes on user_id, drone_id, state, auto_shutdown_time, session_id, and created_at DESC. A trigger_update_isaac_instances_updated_at bumps updated_at via update_updated_at_column().

Column	Type	Notes
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL
`drone_id`	Integer	FK → `drone.id` SET NULL, nullable
`instance_id`	VARCHAR(50)	UNIQUE, NOT NULL — EC2 instance id
`instance_type`	VARCHAR(20)	default `g6e.4xlarge`
`public_ip` / `private_ip` / `availability_zone`	VARCHAR(50)	nullable
`is_spot`	Boolean	default `true`
`state`	VARCHAR(20)	NOT NULL, default `launching`
`launch_time` / `stop_time` / `auto_shutdown_time`	DateTime	nullable
`session_id`	VARCHAR(100)	nullable
`total_runtime_hours` / `estimated_cost`	NUMERIC(10,2)	default `0`
`ebs_snapshot_id`	VARCHAR(50)	nullable
`created_at` / `updated_at`	DateTime	default `CURRENT_TIMESTAMP`

isaac_sim_usage_tracking

Historical per-session usage. Indexes on user_id, instance_id, session_start DESC.

Column	Type	Notes
`id`	Integer	PK
`user_id`	Integer	FK → `user.id` CASCADE, NOT NULL
`instance_id`	Integer	FK → `isaac_sim_instances.id` CASCADE, NOT NULL
`session_start`	DateTime	NOT NULL
`session_end`	DateTime	nullable
`duration_minutes`	Integer	nullable
`cost`	NUMERIC(10,2)	nullable
`created_at`	DateTime	default `CURRENT_TIMESTAMP`

isaac_sim_budget

Monthly rollup, one row per month (YYYY-MM). trigger_update_isaac_budget_percentage runs update_isaac_budget_percentage() BEFORE UPDATE, computing budget_percentage = total_spent / budget_limit * 100 and setting budget_exceeded = (percentage >= 100).

Column	Type	Notes
`id`	Integer	PK
`month`	VARCHAR(7)	UNIQUE, NOT NULL — `YYYY-MM`
`total_spent` / `total_hours`	NUMERIC(10,2)	default `0`
`session_count`	Integer	default `0`
`budget_limit`	NUMERIC(10,2)	default `320.00` (USD)
`budget_exceeded`	Boolean	default `false` — set by trigger
`budget_percentage`	NUMERIC(5,2)	default `0` — set by trigger
`updated_at`	DateTime	default `CURRENT_TIMESTAMP`

isaac_sim_config

Key/value settings, seeded with 11 default rows (ami_id, security_group_id, subnet_id, instance_type=g6e.4xlarge, auto_shutdown_hours=2, max_spot_price=1.50, use_spot_instances=true, monthly_budget_limit=320, max_concurrent_instances=3, user_daily_limit_hours=4, user_monthly_limit_hours=20). A trigger_update_isaac_config_updated_at bumps updated_at.

Column	Type	Notes
`id`	Integer	PK
`config_key`	VARCHAR(100)	UNIQUE, NOT NULL
`config_value`	TEXT	NOT NULL
`description`	TEXT	nullable
`updated_at`	DateTime	default `CURRENT_TIMESTAMP`

Views (read-only, defined in the same migration):

View	Purpose
`active_isaac_instances`	Instances in `launching`/`running` with computed `runtime_hours` and `minutes_until_shutdown`, joined to `user`/`drone`.
`isaac_monthly_usage_summary`	Per-user monthly session count, hours and cost from `isaac_sim_usage_tracking`.
`isaac_budget_status`	Last 12 months of budget with a `healthy`/`warning`/`critical`/`exceeded` status band.

db.create_all() (dev mode) creates only the ORM tables — none of the Isaac Sim tables, views, functions, or triggers. A dev database bootstrapped with create_all() is incomplete for Isaac Sim, and any ORM refactor of this subsystem must preserve/recreate the raw-SQL objects. See Isaac Sim Tables (Raw SQL).

Migration chain

Migrations live in migrations/versions/ and form a single linear chain from the root cd0db35fec77 (down_revision = None) to the current head r3m4n5o6p7q8. Revision IDs mix random hashes (cd0db35fec77, d741a9eb5162) with hand-authored sequential letters (i4d5e6f7g8h9 … r3m4n5o6p7q8) — and the interleaving is non-obvious (e.g. 55cc964f0c63.down = m8h9i0j1k2l3), so always trust down_revision, not the filename ordering.

# Show the current head and full history
docker exec -it skyhub_gateway_service flask db heads      # -> r3m4n5o6p7q8
docker exec -it skyhub_gateway_service flask db history

# Apply / create migrations
docker exec -it skyhub_gateway_service flask db upgrade
docker exec -it skyhub_gateway_service flask db migrate -m "<message>"

migrations/env.py pulls target metadata from the live Flask app (current_app.extensions['migrate'].db) and suppresses empty autogenerate diffs. src/models/__init__.py is empty — models register on db.metadata only as a side effect of services importing them. A new model not imported by any loaded module is invisible to both flask db migrate autogenerate and create_all(). Connection settings (DB_USERNAME/PASSWORD/IP/NAME, pool config) live in Migrations, DB Connection & Dev Mode and Gateway Environment Variables.

Gotchas a future editor must preserve

drone.type is DB-nullable despite the model saying nullable=False

In src/models/drone.py, type = db.Column(Enum(DroneType, nullable=False)) passes nullable=False to the Enum type, not the Column. The initial migration therefore created drone.type as nullable=True. vehicle_type has the same misplaced kwarg, but migration e63c68f1d55f added its column as NOT NULL — so in production vehicle_type is NOT NULL while type is nullable, and db.create_all() would make both nullable. Don’t assume NOT NULL on either without checking the migration.

drone.mac is deprecated but still NOT NULL + UNIQUE

The column is annotated TODO:FIXME: DEPRECATE! yet remains NOT NULL, UNIQUE. Every drone insert must supply a unique mac value. For SITL drones the service sets ip == mac == container_name.

Billing FK ondelete diverges: model omits it, migration CASCADEs

In src/models/subscription.py, subscription.user_id, payment.user_id, and payment.subscription_id omit ondelete, so db.create_all() (dev) would make them RESTRICT. But production migration h3c4d5e6f7g8 builds them as CASCADE, CASCADE, and SET NULL respectively — so deleting a user in production cascade-deletes their subscription and payments, consistent with the rest of the user tree. Don’t assume RESTRICT here without checking the migration. Separately, drone.mission_id has no ondelete in either the model or the migration and genuinely needs manual cleanup on mission delete.

Timezone inconsistency across models

mission_execution, calendar_event(_occurrence), subscription.created_at/updated_at, and asset.created use tz-aware DateTime(timezone=True) with datetime.now(timezone.utc). But asset.modified, subscription.current_period_start/end, and the Isaac Sim DateTime columns are naive. Comparisons that mix naive and aware timestamps will raise or silently misbehave.

asset.status default is a raw string, not an enum member

status = db.Column(Enum(AssetStatus), ... default=AssetStatus.pending.value) uses the string 'pending' rather than the member AssetStatus.pending. It works only because the member name equals its value; it’s inconsistent with every other enum default in the codebase.

HTTP & Socket.IO API

The endpoints that read and write these tables.

Environment Variables

DB_*, APP_ENVIRONMENT, and the rest of the config surface.

Redis & MAVLink Map

The non-SQL channels and the MAVLink port map.

Schema Overview

Migrations & Connection

Mission & Geofence Format

Isaac Sim Raw SQL

​Core ER diagram

​Cascade & ownership model

​Identity & fleet

​user — src/models/user.py

​drone — src/models/drone.py

​user_drone_access — src/models/user_drone_access.py

​Missions & waypoints

​mission — src/models/mission.py

​mission_point — src/models/mission_point.py

​Flights & media

​mission_execution — src/models/mission_execution.py

​asset — src/models/asset.py

​Geofences

​geofence — src/models/geofence.py

​geofence_point — src/models/geofence_point.py

​Calendar & scheduling

​calendar_event — src/models/calendar_event.py

​calendar_event_occurrence — src/models/calendar_event_occurrence.py

​Billing

​subscription — src/models/subscription.py

​payment — src/models/subscription.py

​Enum types

​Isaac Sim tables (raw SQL, no ORM)

​Migration chain

​Gotchas a future editor must preserve

​Related reference

HTTP & Socket.IO API

Environment Variables

Redis & MAVLink Map

Core ER diagram

Cascade & ownership model

Identity & fleet

`user` — `src/models/user.py`

`drone` — `src/models/drone.py`

`user_drone_access` — `src/models/user_drone_access.py`

Missions & waypoints

`mission` — `src/models/mission.py`

`mission_point` — `src/models/mission_point.py`

Flights & media

`mission_execution` — `src/models/mission_execution.py`

`asset` — `src/models/asset.py`

Geofences

`geofence` — `src/models/geofence.py`

`geofence_point` — `src/models/geofence_point.py`

Calendar & scheduling

`calendar_event` — `src/models/calendar_event.py`

`calendar_event_occurrence` — `src/models/calendar_event_occurrence.py`

Billing

`subscription` — `src/models/subscription.py`

`payment` — `src/models/subscription.py`

Enum types

Isaac Sim tables (raw SQL, no ORM)

Migration chain

Gotchas a future editor must preserve

Related reference