The Gateway’s PostgreSQL schema contains a small island of objects that behave nothing like the rest of the data layer. Where every other table maps to a Flask-SQLAlchemy model in src/models/ (see Database Schema Overview), the Isaac Sim EC2-instance accounting objects — 4 tables, 3 views, 2 PL/pgSQL functions and 3 triggers — exist only as hand-written SQL inside a single Alembic migration and are read/written with a raw psycopg2 cursor, never through the ORM. This page documents those objects, why they diverge from the ORM, the (currently dormant) code that uses them, and the exact set of database objects a DB-layer refactor must recreate.
Do not confuse Isaac Sim with Isaac ROS Visual SLAM. These tables track NVIDIA Isaac Sim runs on a cloud EC2 GPU instance (g6e family) used to simulate drones. The on-drone GPS-denied navigation stack is a different product — Isaac ROS Visual SLAM — documented under Isaac Visual SLAM & Pose Bridge. They share a brand name and nothing else.

​
Everything lives in one migration

All Isaac Sim database objects are created by exactly one migration and are never altered by a later one: 
migrations/versions/e85b13d42b08_add_isaac_sim_tables.py
  revision      = 'e85b13d42b08'   (Create Date: 2025-01-04)
  down_revision = '9af6ddd1b449'
  next in chain = 'f1a2b3c4d5e6'   (add_geofence_tables)
Because there is no db.Model for any of these objects:
  • db.create_all() will never create them. The dev-mode shortcut (APP_ENVIRONMENT=dev + python main.py) only reflects ORM models, so a dev database bootstrapped that way is missing the entire Isaac Sim schema — tables, views, functions and triggers. See Migrations, DB Connection & Dev Mode. Only flask db upgrade provisions them.
  • Alembic autogenerate cannot see them. flask db migrate builds diffs from db.metadata, which knows nothing about these tables. Any future change to the Isaac Sim schema must be hand-authored raw SQL (op.execute(...) / op.create_table(...)), exactly like e85b13d42b08 — you cannot autogenerate it.
  • Views, functions and triggers have no ORM equivalent at all. SQLAlchemy models cannot express CREATE VIEW, CREATE FUNCTION or CREATE TRIGGER, so even if models were added for the tables, the view/function/trigger layer would still be raw SQL.
src/models/__init__.py is empty; ORM models register on db.metadata only as a side effect of a service importing them. The Isaac Sim tables never had a model to import, so they were provisioned as raw DDL from day one rather than being an accidental omission.

​
Entity model

instance_id means two different things. On isaac_sim_instances it is the AWS EC2 instance string (e.g. i-0d2c…, VARCHAR(50) UNIQUE). On isaac_sim_usage_tracking it is an integer foreign key to isaac_sim_instances.id — the DB row id, not the EC2 id. instance_scheduler_service.py:282 inserts instance["id"] (the integer) into usage_tracking.instance_id; passing the EC2 string there would violate the FK.

​
The 4 tables

​
isaac_sim_instances

One row per launched Isaac Sim EC2 instance. user_id FK is ON DELETE CASCADE; drone_id FK is ON DELETE SET NULL.
ColumnTypeNotes
idINTEGER PK
user_idINTEGER NOT NULLFK user.id CASCADE, idx idx_isaac_instances_user
drone_idINTEGER NULLFK drone.id SET NULL, idx idx_isaac_instances_drone
instance_idVARCHAR(50) NOT NULL UNIQUEEC2 instance id
instance_typeVARCHAR(20)default g6e.4xlarge
public_ip / private_ipVARCHAR(50) NULL
availability_zoneVARCHAR(50) NULL
is_spotBOOLEANdefault true (spot vs on-demand)
stateVARCHAR(20) NOT NULLdefault launching (also running, stopped, …), idx
launch_time / stop_timeDATETIME NULL
auto_shutdown_timeDATETIME NULL2 h after launch; idx idx_isaac_instances_shutdown_time
session_idVARCHAR(100) NULLidx
total_runtime_hoursNUMERIC(10,2)default 0
estimated_costNUMERIC(10,2)default 0
ebs_snapshot_idVARCHAR(50) NULLsnapshot taken on auto-shutdown
created_at / updated_atDATETIMEdefault CURRENT_TIMESTAMP
Also indexed created_at DESC (idx_isaac_instances_created). updated_at is bumped by a trigger (below).

​
isaac_sim_usage_tracking

Immutable session-history log; one row appended per completed session. Both FKs are ON DELETE CASCADE.
ColumnTypeNotes
idINTEGER PK
user_idINTEGER NOT NULLFK user.id CASCADE, idx
instance_idINTEGER NOT NULLFK isaac_sim_instances.id CASCADE, idx
session_startDATETIME NOT NULLidx session_start DESC
session_endDATETIME NULL
duration_minutesINTEGER NULL
costNUMERIC(10,2) NULL
created_atDATETIMEdefault CURRENT_TIMESTAMP

​
isaac_sim_budget

One row per calendar month (month = YYYY-MM, UNIQUE). Rolls up spend across all users for cost-cap enforcement.
ColumnTypeNotes
idINTEGER PK
monthVARCHAR(7) NOT NULL UNIQUEe.g. 2026-07; idx month DESC
total_spentNUMERIC(10,2)default 0
total_hoursNUMERIC(10,2)default 0
session_countINTEGERdefault 0
budget_limitNUMERIC(10,2)default 320.00 (USD; 320ofa320 of a 500/mo cap)
budget_exceededBOOLEANdefault false — trigger-computed
budget_percentageNUMERIC(5,2)default 0 — trigger-computed
updated_atDATETIMEdefault CURRENT_TIMESTAMP

​
isaac_sim_config

Key/value settings, seeded with 11 rows on migration via INSERT … ON CONFLICT (config_key) DO NOTHING.
config_keyseeded config_valuemeaning
ami_id''Isaac Sim AMI id (blank — set operationally)
security_group_id''AWS security group
subnet_id''AWS subnet
instance_typeg6e.4xlargeEC2 instance type
auto_shutdown_hours2hours before auto-shutdown
max_spot_price1.50max spot price/hour
use_spot_instancestrueprefer spot
monthly_budget_limit320monthly USD cap
max_concurrent_instances3concurrency limit
user_daily_limit_hours4per-user daily cap
user_monthly_limit_hours20per-user monthly cap

​
The 3 views

ViewPurpose
active_isaac_instancesInstances with state IN ('launching','running'), joined to user/drone for username/drone_name, with computed runtime_hours (EPOCH(NOW()-launch_time)/3600) and minutes_until_shutdown. Ordered by launch_time DESC.
isaac_monthly_usage_summaryisaac_sim_usage_tracking grouped by DATE_TRUNC('month', session_start) and user_id: session_count, total_hours, total_cost, avg_session_hours.
isaac_budget_statusLast 12 isaac_sim_budget rows with remaining_budget = budget_limit - total_spent and a health status label: healthy (<50%), warning (<75%), critical (<90%), else exceeded.
Read them exactly like tables, e.g.: 
SELECT instance_id, username, minutes_until_shutdown
FROM active_isaac_instances;

SELECT month, total_spent, budget_limit, status
FROM isaac_budget_status;

​
The 2 functions and 3 triggers

Two PL/pgSQL functions, wired to three BEFORE UPDATE triggers:
FunctionTriggerOn tableEffect
update_isaac_budget_percentage()trigger_update_isaac_budget_percentageisaac_sim_budgetSets budget_percentage = (total_spent / budget_limit) * 100 and budget_exceeded = (budget_percentage >= 100)
update_updated_at_column()trigger_update_isaac_instances_updated_atisaac_sim_instancesSets updated_at = NOW()
update_updated_at_column()trigger_update_isaac_config_updated_atisaac_sim_configSets updated_at = NOW()
The budget percentage is computed in the database, not the application. No Python code sets budget_percentage or budget_exceeded; they are populated purely by the trigger. Two consequences a refactor must respect:
  • The triggers are BEFORE **UPDATE** only. A brand-new month’s first INSERT into isaac_sim_budget does not fire update_isaac_budget_percentage(), so budget_percentage stays 0 until the row is next updated.
  • update_updated_at_column() is attached to isaac_sim_instances and isaac_sim_config but not to isaac_sim_budget; the budget row’s updated_at is set manually by the ON CONFLICT DO UPDATE in the scheduler.

​
The only consumer: InstanceSchedulerService

Every read and write of these tables goes through src/service/instance_scheduler_service.py, which holds a raw psycopg2 connection (self.db) and drives it directly — self.db.cursor(), cursor.execute(sql, params), self.db.commit(), self.db.rollback(). It never touches the SQLAlchemy db from src/connector/db_connection.py. Its intended behaviour is an async loop that runs every 60 s: The service also exposes extend_instance_runtime(instance_id, hours) (auto_shutdown_time += INTERVAL) and get_scheduler_stats() (state counts + upcoming-shutdown count). Cost is estimated in code at ~$0.90/hour plus $0.10 storage (instance_scheduler_service.py:206, :271).
As of the current codebase, nothing wires this service in. InstanceSchedulerService is never instantiated — there is no InstanceSchedulerService(...) call anywhere in src/ (main.py, src/application/app.py, routes). So although flask db upgrade provisions the full Isaac Sim schema in production, no runtime code currently reads or writes these tables. They are dormant.Separately, the live /api/v1/isaac-sim/* routes (Billing, Calendar, VPN, Video & Isaac Sim API) are backed by src/service/isaac_sim_service.py (IsaacSimService), which manages a single hardcoded EC2 instance via boto3 and does not touch any isaac_sim_* table. Note its INSTANCE_TYPE is g6e.xlarge, versus the g6e.4xlarge default baked into the tables/config — a sign the two halves were never reconciled.

​
Timezone mismatch (latent)

The table columns are naive DateTime() (no timezone=True), so psycopg2 returns naive datetime objects, but the scheduler compares them against datetime.now(timezone.utc) (aware) at instance_scheduler_service.py:136 and subtracts them at :270. Mixing aware and naive datetimes raises TypeError in Python. This is masked today only because the service is not running; anyone wiring it in must normalize timezones (or make the columns TIMESTAMPTZ) first.

​
What a refactor must preserve

If you migrate the data layer (new ORM, schema rebuild, or “clean up dead code”), treat the Isaac Sim objects as a unit. Recreating only the tables silently drops the views and the budget math.
1

All 4 tables with their exact FK actions

isaac_sim_instances (user_id CASCADE, drone_id SET NULL), isaac_sim_usage_tracking (both CASCADE), isaac_sim_budget, isaac_sim_config — plus every index (idx_isaac_instances_*, idx_isaac_usage_*, idx_isaac_budget_month).
2

The 3 views verbatim

active_isaac_instances, isaac_monthly_usage_summary, isaac_budget_status. They quote "user" (reserved word) — keep the quoting.
3

Both functions and all 3 triggers

update_isaac_budget_percentage() + update_updated_at_column(), and the three BEFORE UPDATE triggers. Without the budget trigger, budget_percentage/budget_exceeded are never populated by anything.
4

The 11 seed rows in isaac_sim_config

Re-seed with ON CONFLICT (config_key) DO NOTHING so a re-run is idempotent.
5

Raw-SQL migration authoring

Any change here is invisible to flask db migrate; write DDL by hand with op.execute()/op.create_table(), and provide a matching downgrade() that drops in reverse dependency order (views → triggers → functions → tables), mirroring e85b13d42b08.
The migration’s own downgrade() is the canonical teardown order: DROP VIEW (3) → DROP TRIGGER (3) → DROP FUNCTION (2) → DROP TABLE (config, budget, usage_tracking, instances). Follow it if you ever hand-write a replacement.

Database Schema Overview

The ORM-backed tables these Isaac Sim objects deliberately bypass.

Migrations, DB Connection & Dev Mode

The Alembic chain, DB URI/pool config, and why db.create_all() misses these objects.

Platform Services (incl. Isaac Sim)

IsaacSimService, the boto3 single-instance manager behind the live routes.

Isaac Sim API

The /api/v1/isaac-sim/* HTTP endpoints (which do not use these tables).