Skip to main content

Database Overview

:::info Status Implemented. Source: fastapi-backend/migrations/versions/, fastapi-backend/core/db.py, fastapi-backend/sql/. :::

PostgreSQL, accessed exclusively through asyncpg from core/db.py — no ORM, no query layer outside that one module. Schema ownership is split across two mechanisms that do not overlap in time.

:::caution Don't confuse this with the Supabase→self-hosted infra migration docs/postgres-migration.md documents a one-time infrastructure move — copying the existing database from Supabase to a self-hosted Postgres instance (pg_dump/pg_restore, Kubernetes manifests, rollback via DATABASE_URL). It changed where Postgres runs, not the schema. It's unrelated to the Alembic schema migrations this page describes, beyond sharing the word "migration." :::

Two sources of truth​

OwnerCoversMechanism
Alembic (fastapi-backend/migrations/versions/)Everything from the device-management module onward: devices, device_inventory (lifecycle columns), device_ownership, device_assignments, device_audit_log, app_installs and its child tables, mqtt_service_accounts/ACL, the emqx_device_authn/emqx_device_authz views, device_outbox, device_invoices constraint changesHand-written revisions, run once by the one-shot migrate compose service — see Backend Deployment
ensure_* functions in core/db.py (~17 functions)Tables that predate Alembic: users, mosques, memberships, triggers, audio_files, azan_auto_settings, mosque_prayer_times, user_devices, mosque_devices, broadcast_audit_logs, device_invoices, platform_settings, superadmin 2FA session tablesIdempotent CREATE TABLE IF NOT EXISTS / ALTER TABLE ADD COLUMN IF NOT EXISTS, run on every application startup

The rule going forward: no new ensure_* function. New DDL is always an Alembic migration, even for a table conceptually similar to an existing ensure_*-owned one. See Contributing.

Why hand-written, additive-only migrations​

There's no ORM to diff against, so there's no --autogenerate — every migration is op.execute(...) with one SQL statement per call (asyncpg prepares each statement it's given, and a prepared statement can't hold more than one command). downgrade() deliberately raises NotImplementedError in nearly every device-management-era revision: schema changes stay additive, and behavior reverts by environment-variable flag (FANOUT_V2, STREAM_AUTHZ_ENFORCE) instead of a schema rollback — a migration that dropped a column on downgrade would destroy data that reverting the application code doesn't bring back. Full detail: Backend Deployment.

The baseline​

0001_baseline is an intentionally empty revision. Azan360's schema was not built by migrations — it came from the Supabase console and the ensure_* functions — so there's no history to reconstruct. Each existing environment is stamped at this baseline once (alembic stamp 0001_baseline), and Alembic owns everything from there.

:::caution Known gap Per the baseline revision's own docstring: some production tables have no DDL anywhere in this repository at all — device_inventory existed in production before any migration or ensure_* function described it, and two dead Supabase-console tables (transmitters, receivers, dropped in migration 0022) survived unnoticed for the same reason. Core tables like users, mosques, memberships, triggers, audio_files, and azan_auto_settings are also not created by any ensure_* function — their DDL exists only as historical Supabase-console SQL (partially recoverable from fastapi-backend/sql/*.sql reference scripts for a few of them: audio_files_supabase.sql, azan_auto_settings_supabase.sql, mosque_prayer_times_supabase.sql, mosque_devices_supabase.sql, user_devices_supabase.sql, device_invoices_supabase.sql) or inferred from the columns core/db.py actually queries. Treat any column list for these tables in this documentation as "known to exist," not "complete." :::

Next​