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.
These tables track NVIDIA Isaac Sim runs on a cloud EC2 GPU instance (g6e family) used to simulate drones. Nothing here runs on an aircraft.

​
Everything lives in one migration

All Isaac Sim database objects are created by exactly one migration and are never altered by a later one:
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. 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.

​
isaac_sim_budget

One row per calendar month (month = YYYY-MM, UNIQUE). Rolls up spend across all users for cost-cap enforcement.

​
isaac_sim_config

Key/value settings, seeded with 11 rows on migration via INSERT … ON CONFLICT (config_key) DO NOTHING.

​
The 3 views

Read them exactly like tables, e.g.:

​
The 2 functions and 3 triggers

Two PL/pgSQL functions, wired to three BEFORE UPDATE triggers:
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).