Skip to main content

Data Model

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

Per-table depth lives on Entities — this page is the shape of the schema and how the major clusters relate.

Cluster: users, mosques, memberships​

users, mosques, and memberships are the oldest tables in the system — predating both Alembic and any ensure_* function (see the known gap noted on the overview page). A membership joins a user to a mosque and is what Playback Entitlement and Authorization both key off of.

Cluster: device registry (Alembic-owned, migrations 0002–0005, 0022–0025)​

devices is the deliberate "authorization spine" — see migration 0003_device_registry's own description. devices.mosque_id and devices.owner_user_id are denormalized read caches of the open device_assignments / device_ownership rows, not sources of truth; a disagreement resolves to the open history row. Two database triggers enforce the invariants that keep this consistent — devices_in_service_guard (an activation precondition) and inventory_status_cascade (a permanent invariant enforced from the asset side, e.g. marking inventory LOST/SCRAPPED force-revokes the device and closes its assignment in the same transaction). Full mechanics: Device Lifecycle and Ownership.

mosque_devices predates the unified devices table and still holds transmitter-specific operational detail (config JSON, connectivity, heartbeat payloads) — migration 20260824_0007_migrate_mosque_devices.py folded every mosque_devices row into devices with the same id, so the two tables share primary keys for transmitters rather than one replacing the other. Two Supabase-console leftover tables, transmitters and receivers, were confirmed dead (referenced by no code) and dropped in migration 0022.

Cluster: app installs​

app_installs (+ app_install_preferences, app_install_push_tokens) is deliberately separate from the device registry above — an app install is never a row in devices or device_inventory. See Identity and App Installation module.

Cluster: MQTT broker credentials​

mqtt_service_accounts / mqtt_service_account_acl hold the backend's own broker credential and ACL rules (the backend needs wildcard subscriptions no device is ever granted). emqx_device_authn / emqx_device_authz are views, not tables — they union device rows from devices with rows from mqtt_service_accounts, and EMQX queries them directly as its Postgres auth source. See Device Deployment.

Cluster: broadcasts and delivery​

triggers (manual/auto broadcast records), broadcast_audit_logs + broadcast_audit_errors (delivery analytics), audio_files (azan recordings), and azan_auto_settings (per-mosque scheduler config) — see Azaan Broadcast and Broadcast Audit for how these are written and read.

Cluster: commerce​

device_invoices + device_invoice_items — a single invoice document now covers both a mosque buyer and a member buyer (mutually exclusive via a CHECK constraint, migration 0026), and each device-linked line item references devices.id directly (repointed from the legacy mosque_devices table by the same migration, needing no data migration since IDs were already shared).