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, 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 |
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 |
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, 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 |
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 |
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, 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) |
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 |
geofence, geofence_point — no-fly / keep-in zones
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 |
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, 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 |
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 |
uq_event_occurrence_time(event_id, occurrence_time). Calendar behavior is documented in Users, IP Allocation, Calendar & Isaac Sim.subscription, payment — Stripe billing
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 |
Cascade rules
Ownership tables cascade fromuser; 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) ⚠️ |
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
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 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 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)
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— plaindb.DateTimewithdatetime.now(whileasset.createdis tz-aware).subscription.current_period_start/current_period_end— naivedb.DateTime.
Asymmetric billing cascade — deleting a user with billing FAILS
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 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()
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).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.