Database Schema Overview

The Gateway persists all of its state in a single PostgreSQL database, accessed through Flask-SQLAlchemy. Every model inherits db.Model from the global db instance defined in src/connector/db_connection.py, and the schema is versioned by Alembic migrations under migrations/versions/. A second, parallel set of isaac_sim_* objects has no ORM model (so db.create_all() never creates them) and is defined entirely inside one migration — those are covered on their own page. This page is the map: the entity-relationship overview, a per-table column inventory, the foreign-key cascade matrix, and the handful of schema gotchas a future editor must not “fix” without understanding them.

This page describes structure. For how the schema is created and evolved (the DBConnector, the postgresql:// URI, the Alembic chain, and the APP_ENVIRONMENT=dev db.create_all() shortcut) see Migrations, DB Connection & Dev Mode. For the ORM→MAVLink translation of waypoints and fences see Mission & Geofence MAVLink Format. For the raw-SQL Isaac Sim tables see Isaac Sim Tables (Raw SQL, no ORM).

Entity-relationship overview

user is the root of nearly every ownership chain. Almost everything a user creates — drones, missions, waypoints, flights, media, geofences, calendar events — carries a user_id foreign key that cascades on user delete. The one deliberate exception is billing (subscription, payment), which uses the default RESTRICT so a user with financial history cannot be silently deleted. Note the two relationships between MISSION_EXECUTION and ASSET: an execution captures many assets (asset.execution_id), and an execution optionally points to one log asset (mission_execution.log_asset_id, the ArduPilot .bin).

Table inventory

Thirteen ORM-backed tables plus four raw-SQL Isaac Sim tables.

Table	Model file	Purpose
`user`	`src/models/user.py`	Account owner (email/bcrypt password, VPN `ip`, level, activation token)
`drone`	`src/models/drone.py`	Fleet vehicle (physical or SITL), owns rosbridge address and video-room details
`mission`	`src/models/mission.py`	Route-template header (name, unique per user)
`mission_point`	`src/models/mission_point.py`	Ordered waypoint: semantic type and MAVLink fields
`mission_execution`	`src/models/mission_execution.py`	Realized flight record + ~40 log-analysis metric columns
`asset`	`src/models/asset.py`	S3-backed media (video/image/logs)
`user_drone_access`	`src/models/user_drone_access.py`	Join table for shared drone access
`geofence`	`src/models/geofence.py`	Inclusion/exclusion polygon or circle
`geofence_point`	`src/models/geofence_point.py`	Fence vertex/center with MAVLink fence command
`calendar_event`	`src/models/calendar_event.py`	Scheduled mission (with RRULE recurrence)
`calendar_event_occurrence`	`src/models/calendar_event_occurrence.py`	Sparse per-occurrence status exceptions
`subscription`	`src/models/subscription.py`	Stripe subscription, 1:1 with user
`payment`	`src/models/subscription.py`	Individual Stripe payment record
`isaac_sim_*` (4 tables)	(no ORM model)	Raw-SQL Isaac Sim scheduling → see its page

src/models/__init__.py is empty. Models register on db.metadata only as a side effect of being imported — the services (mission_service, drone_service, asset_service, …) import their models, and app.py imports all services. A new model that no loaded module imports will be invisible to both Alembic autogenerate and db.create_all(). Always import a new model somewhere in the startup path.

Column reference

user, drone, user_drone_access — identity & fleet

user — src/models/user.py

Column	Type	Constraints
`id`	Integer	PK
`username`	String(80)	unique, nullable (was NOT NULL in the initial migration, later relaxed)
`password`	String(255)	NOT NULL (bcrypt hash)
`ip`	String(100)	unique, nullable — the user’s WireGuard VPN address
`is_active`	Boolean	NOT NULL, default `false`
`level`	Enum `userlevel`(admin, customer)	nullable
`activation_token`	String(10)	unique, nullable
`email`	String(100)	unique, NOT NULL
`report_extra_emails`	Text	nullable — comma-separated CSV; `get_report_recipients()` splits it

get_report_recipients() returns the user’s email first, then the deduplicated extras — used by the report mailer.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`	String(100)	unique, NOT NULL — deprecated but still required (see gotchas)
`name`	String(100)	NOT NULL
`ip`	String(100)	unique, NOT NULL, indexed (`ix_drone_ip`)
`port`	Integer	NOT NULL — rosbridge port (9090 physical; `9090+n` for SITL)
`mission_id`	Integer	FK `mission.id`, no ondelete, nullable — last-uploaded mission
`type`	Enum `dronetype`(physical, sitl)	nullable at DB level (see gotchas)
`vehicle_type`	Enum `dronevehicletype`(rover, copter)	default `rover`, nullable at DB level
`video_room_id`	Integer	unique — Janus room id
`video_room_password`	String(100)
`video_room_token`	String(300)
`activation_token`	String(10)	unique, nullable

The video_room_* fields are pushed to the drone over rosbridge — see Janus Video Rooms. SITL lifecycle populates ip/port — see SITL Drone Lifecycle.user_drone_access — src/models/user_drone_access.py

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). This is the share-grant table; the User VPN writes iptables rules from it so only owning/granted users can reach a drone — see VPN IP Authentication & Jumphost Routing.

mission, mission_point — route templates

mission — src/models/mission.py

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

Unique constraint uq_user_name(user_id, name). One-to-many mission_points.mission_point — src/models/mission_point.py

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`(base, fly, safepoint)	NOT NULL
`label`	String(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`

Each row carries both a semantic type (used by the UI) and the full MAVLink command set. to_mavlink_waypoint() maps lat→x_lat, lng→y_long, altitude→z_alt. The upload-to-drone semantics (including the auto-prepended TAKEOFF) live in Mission & Geofence MAVLink Format and DroneControlService.

mission_execution, asset — flights & media

mission_execution — src/models/mission_execution.pyThe flight record. Created (status in_progress) when a drone arms/flies; mission_id is NULL for manual flights. After landing, LogAnalysisService parses the ArduPilot .bin (referenced by log_asset_id) and back-fills ~40 metric columns.

Group	Columns
Keys	`id` PK; `user_id` (CASCADE, idx); `drone_id` (CASCADE, idx); `mission_id` (SET NULL, idx); `log_asset_id` FK `asset.id` (SET NULL)
Timing	`started_at` (tz, NOT NULL, idx); `ended_at` (tz); `duration_seconds`
Status	`status` Enum `executionstatus`(in_progress, completed, aborted, error), NOT NULL default `in_progress`, idx
Report	`report_sent_at` (tz); `report_pending` (NOT NULL default `false`)
Archive	`archive_s3_key`; `archive_generated_at` (tz)
Flight stats	`log_flight_time_seconds`; `total_distance_meters`; `max_altitude_meters`; `avg_speed_ms`; `max_speed_ms`; `log_analyzed_at`
Battery	`battery_start_voltage`, `battery_min_voltage`, `battery_end_voltage`, `battery_energy_wh`, `battery_remaining_pct`
GPS	`gps_avg_satellites`, `gps_min_satellites`, `gps_avg_hdop`, `gps_fix_type`, `gps_fix_type_name`
Efficiency	`hover_throttle_pct`
Vibration	`vibe_{x,y,z}_avg`, `vibe_{x,y,z}_max`, `clip_0`, `clip_1`, `clip_2`
Errors (JSON)	`error_count`, `error_events`
Modes (JSON)	`mode_change_count`, `failsafe_count`, `mode_changes`
Motors (JSON)	`motor_balance_avg`, `motor_balance_max`, `motor_balance_min`, `motor_count`
Timestamps	`notes`; `created` (tz); `modified` (tz, onupdate)

Indexed on user_id, drone_id, mission_id, started_at, status. The metric semantics and reporting flow are documented in Executions, Log Analysis & Reports.asset — src/models/asset.py

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
`file_name`	String(255)	NOT NULL
`s3_key`	String(1024)	nullable
`checksum`	String(64)	nullable, idx (`ix_asset_checksum`)
`mime_type`	String(255)	nullable
`content_length`	Integer	default `0`
`asset_type`	Enum `assettype`(video, image, logs)	nullable at DB level
`status`	Enum `assetstatus`(pending, ready, error)	default `'pending'` (see gotchas)
`upload_id`	String(255)	nullable — multipart upload id
`created`	DateTime(timezone=True)	default `datetime.now`
`modified`	DateTime (naive)	default `datetime.now`, onupdate `datetime.now`

Asset storage/HLS/archive mechanics are in S3 Assets, HLS Video & Execution Archives.

geofence, geofence_point — no-fly / keep-in zones

geofence — src/models/geofence.py

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

Unique constraint uq_geofence_user_name(user_id, name). Points cascade via ORM delete-orphan.geofence_point — src/models/geofence_point.py

Column	Type	Constraints
`id`	Integer	PK
`geofence_id`	Integer	FK `geofence.id` CASCADE, NOT NULL, idx
`user_id`	Integer	FK `user.id` CASCADE, NOT NULL, idx
`lat`, `lng`	Float	NOT NULL
`sequence`	Integer	NOT NULL
`frame`	Integer	default `3`
`command`	Integer	NOT NULL — MAVLink fence command 5001–5004
`param1`–`param4`	Float	default `0.0`

command encodes the fence type: 5001 POLY_INCLUSION, 5002 POLY_EXCLUSION, 5003 CIRCLE_INCLUSION, 5004 CIRCLE_EXCLUSION. param1 is the polygon vertex count or the circle radius (m). to_mavlink_fence_item() forces z_alt = 0.0 (2D fences). Full mapping in Mission & Geofence MAVLink Format.

calendar_event, calendar_event_occurrence — scheduling

calendar_event — src/models/calendar_event.py

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK `user.id` CASCADE, NOT NULL, idx
`title`	String(255)	NOT NULL
`description`	Text	nullable
`scheduled_time`	DateTime(timezone=True)	NOT NULL, idx
`status`	Enum `eventstatus`(scheduled, completed, cancelled)	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`	String(255)	nullable — an iCal RRULE
`created_at`, `updated_at`	DateTime(timezone=True)	NOT NULL

Unique constraint uq_user_event_title_time(user_id, title, scheduled_time).calendar_event_occurrence — src/models/calendar_event_occurrence.pyA sparse exceptions table: a recurring event’s occurrences are virtual and default to scheduled; only occurrences whose status deviates get a row here.

Column	Type	Constraints
`id`	Integer	PK
`event_id`	Integer	FK `calendar_event.id` CASCADE, NOT NULL, idx
`occurrence_time`	DateTime(timezone=True)	NOT NULL, idx
`status`	Enum `eventstatus`	NOT NULL, default `scheduled`
`execution_id`	Integer	FK `mission_execution.id` SET NULL, nullable, idx
`created_at`, `updated_at`	DateTime(timezone=True)	NOT NULL

Unique constraint uq_event_occurrence_time(event_id, occurrence_time). Calendar behavior is documented in Users, IP Allocation, Calendar & Isaac Sim.

subscription, payment — Stripe billing

subscription — src/models/subscription.py (pricing: €120/vehicle/year)

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK `user.id` (no ondelete), NOT NULL, unique (one sub per user)
`stripe_customer_id`	String(255)	unique, nullable
`stripe_subscription_id`	String(255)	unique, nullable
`status`	Enum `subscriptionstatus`(active, canceled, past_due, incomplete, trialing, unpaid)	NOT NULL, default `incomplete`
`vehicle_count`	Integer	NOT NULL, default `0`
`current_period_start` / `current_period_end`	DateTime (naive)	nullable
`created_at`, `updated_at`, `canceled_at`	DateTime(timezone=True)

payment — src/models/subscription.py

Column	Type	Constraints
`id`	Integer	PK
`user_id`	Integer	FK `user.id` (no ondelete), NOT NULL
`subscription_id`	Integer	FK `subscription.id` (no ondelete), nullable
`stripe_payment_intent_id`	String(255)	unique, nullable
`stripe_invoice_id`	String(255)	unique, nullable
`amount`	Integer	NOT NULL — stored in cents (€120.00 = `12000`)
`currency`	String(3)	NOT NULL, default `eur`
`status`	String(50)	NOT NULL — plain string (`succeeded`/`failed`/`pending`)
`description`	String(500)	nullable
`vehicle_count`	Integer	NOT NULL, default `1`
`created_at`	DateTime(timezone=True)	NOT NULL

Billing logic and vehicle-limit enforcement live in Stripe Billing & Vehicle Limits.

Cascade rules

Ownership tables cascade from user; report/asset links soften to SET NULL so history survives the deletion of the target; billing intentionally does not cascade.

Foreign key	References	ON DELETE
`drone.user_id`	`user.id`	CASCADE
`drone.mission_id`	`mission.id`	none (RESTRICT) ⚠️
`mission.user_id`	`user.id`	CASCADE
`mission_point.mission_id`	`mission.id`	CASCADE
`mission_point.user_id`	`user.id`	CASCADE
`mission_execution.user_id`	`user.id`	CASCADE
`mission_execution.drone_id`	`drone.id`	CASCADE
`mission_execution.mission_id`	`mission.id`	SET NULL
`mission_execution.log_asset_id`	`asset.id`	SET NULL
`asset.user_id` / `asset.drone_id`	`user.id` / `drone.id`	CASCADE
`asset.execution_id`	`mission_execution.id`	SET NULL
`user_drone_access.user_id` / `.drone_id`	`user.id` / `drone.id`	CASCADE
`geofence.user_id`	`user.id`	CASCADE
`geofence_point.geofence_id` / `.user_id`	`geofence.id` / `user.id`	CASCADE (+ ORM delete-orphan)
`calendar_event.user_id`	`user.id`	CASCADE
`calendar_event.drone_id` / `.mission_id` / `.execution_id`	drone/mission/execution	SET NULL
`calendar_event_occurrence.event_id`	`calendar_event.id`	CASCADE (+ delete-orphan)
`calendar_event_occurrence.execution_id`	`mission_execution.id`	SET NULL
`subscription.user_id`	`user.id`	none (RESTRICT) ⚠️
`payment.user_id` / `.subscription_id`	`user.id` / `subscription.id`	none (RESTRICT) ⚠️

drone.mission_id has no ON DELETE rule — deleting a mission that any drone still references would RESTRICT. mission_service.delete() (src/service/mission_service.py:83) compensates: it runs Drone.query.filter(Drone.mission_id == mission_id).update({"mission_id": None}) and deletes the mission’s points before deleting the Mission. Any refactor that changes cascade behavior must preserve this manual cleanup (or add ON DELETE SET NULL).

Schema gotchas

These are load-bearing quirks. Preserve them across refactors unless you deliberately migrate the data.

Enum-nullability trap: drone.type / vehicle_type / asset_type are NULLABLE

Drone.type is declared db.Column(Enum(DroneType, nullable=False)) (src/models/drone.py:50). The nullable=False is passed to the Enum type, not the Column — so at the DB level these columns are actually nullable. The initial migration confirms it: drone.type is created nullable=True (migrations/versions/cd0db35fec77_initial_migration.py:44). The same pattern affects Drone.vehicle_type and Asset.asset_type. Do not assume NOT NULL on these columns.

Enum storage: NAME vs VALUE (values_callable)

Enum columns are backed by global PostgreSQL types: dronetype, dronevehicletype, mission_point_types, geofence_types, fence_types, executionstatus, eventstatus, subscriptionstatus, assettype, assetstatus, userlevel.EventStatus and SubscriptionStatus are declared with values_callable=lambda x: [e.value for e in x], so PostgreSQL stores the lowercase value ("scheduled"). The others (DroneType, ExecutionStatus, AssetType, …) store the member name. This matters when you write raw SQL or add new members — check the declaration before assuming what string is on disk.

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

Asset.status is an Enum(AssetStatus) column but its default is AssetStatus.pending.value — the string "pending" — not the enum member (src/models/asset.py:52). It works only because the member name equals its value. Every other enum default in the codebase passes the member itself; keep the distinction in mind when copying the pattern.

Timezone inconsistency (naive vs aware timestamps)

Most audit timestamps are timezone-aware (DateTime(timezone=True) + datetime.now(timezone.utc)): mission_execution, calendar_event, subscription.created_at/updated_at. But several are naive:

asset.modified — plain db.DateTime with datetime.now (while asset.created is tz-aware).
subscription.current_period_start / current_period_end — naive db.DateTime.

Comparing or serializing these against tz-aware values can surprise you. Normalize explicitly.

Asymmetric billing cascade — deleting a user with billing FAILS

subscription.user_id, payment.user_id, and payment.subscription_id have no ondelete, so they default to RESTRICT. Because most user-owned tables CASCADE, a naive DELETE FROM user succeeds for drones/missions/assets but fails if the user has a subscription or payment. Delete billing rows first, or add explicit cascade rules if that is the intent.

drone.mac is deprecated but still NOT NULL + UNIQUE

Drone.mac is annotated TODO:FIXME: DEPRECATE! - this is no longer used (src/models/drone.py:34) yet remains unique=True, nullable=False. Every drone insert must still supply a unique mac value — SITL creation, for example, sets ip == mac == container_name.

Isaac Sim tables have no ORM model and skip db.create_all()

The isaac_sim_instances, isaac_sim_usage_tracking, isaac_sim_budget, and isaac_sim_config tables (built with op.create_table in migrations/versions/e85b13d42b08_add_isaac_sim_tables.py) — plus 3 views, 2 PL/pgSQL functions, and their triggers (raw op.execute SQL in the same migration) — have no ORM model. They are read/written with a raw psycopg2 cursor in src/service/instance_scheduler_service.py, bypassing the ORM entirely. The dev-only db.create_all() shortcut will not create them, so a dev database is incomplete for Isaac Sim. Full details on Isaac Sim Tables (Raw SQL, no ORM).

The Alembic chain is linear to a single head (r3m4n5o6p7q8), but revision IDs interleave random hashes (cd0db35fec77) with hand-authored sequential letters (i4d5e6f7g8h9 … r3m4n5o6p7q8), and down_revision values are not always alphabetical — trust down_revision, not filenames. The migration mechanics are on Migrations, DB Connection & Dev Mode.

Where to go next

Migrations & Connection

DBConnector, the postgresql:// URI, the Alembic chain, and the dev-mode create_all() shortcut.

Mission & Geofence MAVLink Format

How MissionPoint / GeofencePoint map to MAVLink waypoints and fence items.

Isaac Sim Tables (Raw SQL)

The four raw-SQL tables, views, functions and triggers with no ORM model.

Full Database Schema Reference

The platform-wide, cross-repo schema reference.

HTTP & Socket.IO API

Services & Realtime Internals

Data, Config & Ops

Database Schema Overview

Entity-relationship overview

Table inventory

Column reference

Cascade rules

Schema gotchas

Where to go next

Migrations & Connection

Mission & Geofence MAVLink Format

Isaac Sim Tables (Raw SQL)

Full Database Schema Reference

​Entity-relationship overview

​Table inventory

​Column reference

​Cascade rules

​Schema gotchas

​Where to go next

Migrations & Connection

Mission & Geofence MAVLink Format

Isaac Sim Tables (Raw SQL)

Full Database Schema Reference

Entity-relationship overview

Table inventory

Column reference

Cascade rules

Schema gotchas

Where to go next