The Gateway persists all of its state in a single PostgreSQL database, reached through one global Flask-SQLAlchemy instance and provisioned by an Alembic (Flask-Migrate) migration chain. This page covers how the connection is built, how the schema is migrated, and the APP_ENVIRONMENT=dev db.create_all() shortcut — including the sharp edges that make dev and production schemas diverge. For the table inventory and ER model see Database Schema Overview. For the raw-SQL Isaac Sim objects that live outside the ORM see Isaac Sim Tables (Raw SQL, no ORM).

​
The database connection

Everything routes through one module: src/connector/db_connection.py. It defines the global db object that every model inherits from (class X(db.Model)), and a DBConnector class that wires the URI and Flask-Migrate onto the app.
src/connector/db_connection.py
db = SQLAlchemy(
    engine_options={
        "pool_timeout": 5,
        "pool_pre_ping": True,
        "connect_args": {
            "connect_timeout": 5,
        },
    },
)


class DBConnector:
    def __init__(self, app):
        self.username = settings.DB_USERNAME
        self.password = settings.DB_PASSWORD
        self.ip = settings.DB_IP
        self.name = settings.DB_NAME

        app.config["SQLALCHEMY_DATABASE_URI"] = (
            f"postgresql://{self.username}:{self.password}@{self.ip}/{self.name}"
        )
        app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
        db.init_app(app)
        migrate = Migrate(app, db)
        migrate.init_app(app, db)
DBConnector(app) is invoked once during boot at src/main.py:178, after the Flask app object exists but before routes are registered. db itself is imported at src/main.py:20 (from connector.db_connection import DBConnector, db).

​
Connection string

The URI is assembled by f-string as postgresql://<user>:<pass>@<host>/<db> — note there is no explicit port, so SQLAlchemy/psycopg2 falls back to the PostgreSQL default 5432. There is no +psycopg2 suffix, so SQLAlchemy uses its default PostgreSQL DBAPI (psycopg2, pinned to psycopg2-binary 2.9.9).
Because the port is not part of the URI, you cannot point the Gateway at a non-5432 Postgres by setting an env var — there is no port variable. Front a non-standard port with a proxy, or edit the URI in db_connection.py.

​
Engine / pool settings

These are set once on the global SQLAlchemy(...) and apply to every connection the Gateway (and its gunicorn workers) opens.
OptionValueEffect
pool_timeout5Seconds to wait for a free pooled connection before raising TimeoutError.
pool_pre_pingTrueEmits a lightweight SELECT 1 before handing out a pooled connection; transparently recycles stale/dropped connections (important behind the VPN/jumphost where idle sockets die).
connect_args.connect_timeout5psycopg2 TCP connect timeout in seconds.
SQLALCHEMY_TRACK_MODIFICATIONSFalseDisables Flask-SQLAlchemy’s per-object change signals (overhead, deprecated).

​
DB_* environment variables

All four are read in src/application/settings.py (lines 106–109) and .strip()ed. They default to empty strings, so a misconfigured environment produces a nonsense URI (postgresql://:@/) that fails fast on first query rather than at import time.
VariablePurposeDefault
DB_USERNAMEPostgres role""
DB_PASSWORDPostgres password""
DB_IPPostgres host (port is always 5432)""
DB_NAMEDatabase name""
See Gateway Environment Variables for the full env matrix and Startup, Validation & Composition Root for boot ordering.

​
How the schema gets created

There are two mutually exclusive provisioning paths, selected entirely by how the process starts: The two paths are not equivalent. db.create_all() reflects the current ORM models as they exist in code right now; flask db upgrade replays recorded history and also runs raw-SQL objects that have no ORM representation. Treat migrations as the source of truth.

​
Alembic migration chain (production path)

Production and Docker deployments provision and evolve the schema with Flask-Migrate / Alembic. The wiring lives in migrations/env.py, which is unusual in that it pulls the target metadata from the live Flask app rather than importing a Base:
migrations/env.py
config.set_main_option('sqlalchemy.url', get_engine_url())
target_db = current_app.extensions['migrate'].db
This means flask db commands must run inside the Flask app context (Flask-Migrate provides it), and the URL comes from the same SQLALCHEMY_DATABASE_URI built by DBConnector — you never repeat DB credentials for migrations. env.py also installs a process_revision_directives callback that drops empty autogenerate diffs (logs No changes in schema detected.) so a no-op flask db migrate won’t create an empty revision file.

​
One linear chain, single head

There are 31 revision files in migrations/versions/. They form a single linear chain with no branches — base cd0db35fec77 (down_revision = None) up to head r3m4n5o6p7q8. 
cd0db35fec77 (initial: user, mission, drone, mission_point)
  → 0abed27f978d → e63c68f1d55f → 5026bf9fceb2 → ad4d9128d2dd
  → 2747a065edb4 → 512f2bffba39 → 496a4295841d (email) → 397790471ed5
  → d741a9eb5162 (assets) → 4657b07379f9 (drone indexes) → 100ae028b8fe
  → b0d69a7b1b43 (MAVLink waypoint fields) → 9af6ddd1b449
  → e85b13d42b08 (Isaac Sim raw SQL) → f1a2b3c4d5e6 (geofence) → g2b3c4d5e6f7 (fence_type)
  → h3c4d5e6f7g8 (subscription/payment) → i4d5e6f7g8h9 (calendar) → j5e6f7g8h9i0 (occurrence)
  → k6f7g8h9i0j1 (mission_execution) → l7g8h9i0j1k2 → c16b8edada2a (report_pending)
  → d89dbd0532c0 (flight stats) → m8h9i0j1k2l3 (calendar drone_id nullable)
  → 55cc964f0c63 (battery/GPS metrics) → n9i0j1k2l3m4 (vibration/error/motor)
  → o0j1k2l3m4n5 (gps fix type) → p1k2l3m4n5o6 (archive cols)
  → q2l3m4n5o6p7 (calendar execution_id) → r3m4n5o6p7q8 (execution_id index)  ← HEAD
Revision IDs mix random hashes (cd0db35fec77, 55cc964f0c63) with hand-authored sequential letters (i4d5e6f7g8h9 … r3m4n5o6p7q8), and the ordering is not alphabetical. For example 55cc964f0c63.down_revision = m8h9i0j1k2l3 splices a hash revision on top of a lettered one. Trust down_revision, never the filename sort order, when reasoning about the chain. Keeping it a single head is a hard requirement — a branch (two revisions with the same down_revision) will make flask db upgrade fail with “Multiple head revisions”.

​
Working with migrations

1

Create a migration from model changes (autogenerate)

After editing a model in src/models/, generate a revision. Inside Docker (the recommended path per CLAUDE.md):
docker exec -it skyhub_gateway_service flask db migrate -m "add my_column to drone"
Review the generated file in migrations/versions/ before committing — autogenerate misses server_defaults, enum value changes, indexes it can’t see, and anything not attached to a model (see the Isaac Sim note below).
2

Apply migrations

docker exec -it skyhub_gateway_service flask db upgrade
This walks from the DB’s current stamped revision up to the head r3m4n5o6p7q8.
3

Inspect state

docker exec -it skyhub_gateway_service flask db current   # revision the DB is stamped at
docker exec -it skyhub_gateway_service flask db heads     # must print exactly one head
docker exec -it skyhub_gateway_service flask db history   # full chain
When the new revision touches raw-SQL objects (views, functions, triggers), you must hand-write both upgrade() and downgrade() with op.execute(...) — autogenerate cannot see them. The Isaac Sim migration e85b13d42b08 is the reference example: it creates 4 tables via op.create_table, plus 3 views, 2 PL/pgSQL functions, 3 triggers, and an 11-row isaac_sim_config seed as literal op.execute SQL.

​
Model registration is implicit — src/models/__init__.py is empty

There is no central place that imports every model. src/models/__init__.py is a zero-byte file. A model class is registered on db.metadata only as a side effect of something importing its module. In practice the service layer does this: app.py imports every service (asset_service, mission_service, drone_service, calendar_service, …), and each service imports the models it uses.
A new model that no imported module references is invisible to both flask db migrate (autogenerate produces nothing) and db.create_all(). If you add a model, make sure a service — or something reachable from app.py — imports it. This is the most common cause of a “why is my table missing?” bug when adding an entity.

​
Dev mode: APP_ENVIRONMENT=dev and db.create_all()

For local iteration you can skip migrations entirely and let SQLAlchemy emit CREATE TABLE for the current models. This only happens in one narrow situation, at src/main.py:262-267:
src/main.py
if __name__ == "__main__":
    with app.app_context():
        # change the APP_ENVIRONMENT to dev in compose yaml to run in development mode for DB
        env = os.getenv("APP_ENVIRONMENT", "production").lower()
        if env == "dev":
            db.create_all()

    socketio.run(app, host=settings.SOCKET_IP, port=5000, debug=True)
Both conditions must hold:

__name__ == '__main__'

The block is guarded by if __name__ == "__main__", so it runs only when you launch with python main.py. Under gunicorn (the production/Docker entrypoint), main is imported, not executed as __main__, so db.create_all() never fires.

APP_ENVIRONMENT=dev

APP_ENVIRONMENT defaults to production; it must be lowercase-dev to trigger create_all(). Set it in docker-compose.yml (dev) or your shell.
db.create_all() is idempotent for existing tables (it only creates what’s missing) but it never alters or drops existing columns. It reflects the model definitions as they are now, ignoring migration history.

​
Limitations you must know

The isaac_sim_instances, isaac_sim_usage_tracking, isaac_sim_budget, and isaac_sim_config tables — plus 3 views, 2 functions, and 3 triggers — have no SQLAlchemy model. They are defined only in migration e85b13d42b08 — the tables via op.create_table, the views/functions/triggers as raw op.execute SQL — and are read/written with a raw psycopg2 cursor in src/service/instance_scheduler_service.py. db.create_all() reflects db.metadata, which knows nothing about them, so a dev schema built this way is incomplete for Isaac Sim. If you need those tables locally, run flask db upgrade instead (or in addition). See Isaac Sim Tables (Raw SQL, no ORM).
create_all() builds from current model code; flask db upgrade builds from recorded history. A column added to a model but not yet captured in a migration will exist in a create_all() dev DB and be absent in a migrated production DB (or vice-versa). Never treat a create_all() schema as authoritative — always confirm the migration exists.
Drone.type and Drone.vehicle_type are declared as db.Column(Enum(DroneType, nullable=False)) — the nullable=False is passed to the Enum type, not the Column, so these columns are actually NULLABLE at the DB level under both provisioning paths (confirmed: the initial migration emits drone.type ... nullable=True). (Asset.asset_type is declared db.Column(Enum(AssetType, nullable=True)), so it’s nullable too — the flag likewise lands on the Enum type, not the Column.) Don’t assume NOT NULL here in a refactor.

​
Debugging a missing table or column

1

Check what revision the DB is stamped at

flask db current vs flask db heads. If current is behind heads, run flask db upgrade.
2

Confirm a migration actually adds the column

Grep migrations/versions/ for the column. If it only exists on the model and no migration adds it, autogenerate a new revision and apply it. A create_all() dev DB will hide this gap.
3

If the model itself is missing, check imports

Confirm a loaded module imports the model module (see “Model registration is implicit” above). An unreferenced model is invisible to both provisioning paths.
4

If it's an Isaac Sim table/view, you skipped migrations

A create_all()-only dev DB will lack every isaac_sim_* object. Run flask db upgrade.
5

If connections are timing out, not missing

A 5s pool_timeout / connect_timeout means transient errors surface fast. Verify DB_IP reachability (5432, through the VPN/jumphost in production) rather than assuming a schema problem.
Local Postgres for the Gateway is easiest via Docker Compose. See Local Development Quickstart for a full stack bring-up, and Startup, Validation & Composition Root for what the app validates on boot.

Database Schema Overview

Full table inventory, columns, ER diagram, and cascade rules.

Isaac Sim Tables (Raw SQL)

The non-ORM tables, views, functions and triggers create_all() skips.

Mission & Geofence MAVLink Format

How ORM waypoints translate to MAVLink on mission upload.

Gateway Environment Variables

The full env matrix including the DB_* and APP_ENVIRONMENT vars.