Skip to main content

Entities

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

Per-table reference. Column lists are representative, not exhaustive — see Data Model for how these relate, and Overview for which tables have no DDL in this repository at all (their columns here are inferred from queries in core/db.py, not from a CREATE TABLE statement).

devices​

The device-registry authorization spine. Introduced in 20260824_0003_device_registry_inventory_and_devices.py.

  • PK: id (UUID)
  • Identity: device_uid (unique, the MQTT-facing identifier), serial_number, mac_address, device_kind (transmitter | stream_player)
  • Bindings (denormalized caches — not source of truth): mosque_id, owner_user_id — the source of truth is the open row in device_assignments / device_ownership
  • Lifecycle: lifecycle_status — one of PENDING, VERIFIED, ACTIVE, INACTIVE, BLOCKED, REVOKED, LOST, RETIRED; only written by core/device_registry.py's transition functions (with one exception: the inventory_status_cascade trigger, see below)
  • Credential: device_secret_hash (bcrypt; NULL = cannot authenticate to MQTT)
  • Config: config (JSONB, includes the bluetooth block for a stream player — stored/republished, never gated on), config_version
  • Relationships: inventory_id → device_inventory (unique, one asset per device); referenced by device_ownership, device_assignments, device_audit_log, device_invoice_items

Two triggers enforce invariants directly in the database: devices_in_service_guard (an ACTIVE/INACTIVE device must have inventory ALLOCATED/DEPLOYED, and a stream player must have an open ownership row) and inventory_status_cascade (marking the backing inventory LOST or SCRAPPED force-revokes the device and closes its assignment, in the same transaction). Full narrative: Device Lifecycle.

device_inventory​

The commercial/asset record. Pre-Alembic table (no original DDL in this repo — see Overview); lifecycle and identity columns added by migration 0003.

  • PK: id (UUID)
  • Identity: inventory_code (human-facing, e.g. INV-TX-2026-0001, auto-generated by a trigger), mac_address (unique), device_model, device_kind
  • Commercial: vendor, purchase_order, purchase_date, purchase_cost, currency, batch_code
  • Lifecycle: status — IN_STOCK, ALLOCATED, DEPLOYED, IN_REPAIR, LOST, SCRAPPED
  • Provenance: inventory_source — manifest, legacy_import, manual, promoted_announcement
  • Relationships: backs at most one devices row (devices.inventory_id is UNIQUE)

device_ownership​

Stream-player-only ownership history. Introduced in 20260824_0005_device_history_audit_triggers.py.

  • PK: id (bigserial)
  • device_id → devices, user_id → users
  • started_at/ended_at (NULL ended_at = currently open; a unique partial index enforces exactly one open row per device)
  • reason (on close): initial_sale, gift, resale, replacement, household_transfer, reclaim

A transmitter never has a row here — it's an Azan360-owned, mosque-installed asset with no owning member; this is a deliberate absence, not a data gap. See Ownership.

device_assignments​

Mosque-binding history for any device. Introduced alongside device_ownership in migration 0005.

  • PK: id (bigserial)
  • device_id → devices, mosque_id → mosques
  • assigned_at/unassigned_at (NULL unassigned_at = open; unique partial index — one mosque at a time, enforced by the database)
  • reason (on close): initial_claim, owner_rebind, superadmin_move, ownership_transfer, retire, inventory_lost, inventory_scrapped — deliberately excludes membership_lapsed, because a lapse never closes this row (the lapsed state is derived and reversible)

See Mosque Binding.

device_audit_log​

Chain-of-custody ledger for both devices and app installs — one ledger, two possible subjects (a CHECK constraint requires exactly one of device_id / install_id, or inventory_id alone for inventory-only events).

  • PK: id (bigserial)
  • Identity snapshots at write time (device_uid_snapshot, serial_snapshot, mac_snapshot, owner_user_id_snapshot, mosque_id_snapshot, lifecycle_snapshot) so the row stays meaningful after the live FK goes NULL on delete
  • category: lifecycle, binding, ownership, inventory, config, security, diagnostics
  • correlation_id — set once per originating request, carried through every row it produces (audit → outbox → publish → ack traceable by one id)

Also used for stream-gate denial logging (action = 'stream_denied') — see Backend Deployment.

mosque_devices​

Pre-Alembic transmitter detail table (fastapi-backend/sql/mosque_devices_supabase.sql). Rows were folded into devices with the same id by migration 20260824_0007_migrate_mosque_devices.py — this table still holds transmitter-specific operational fields not modeled on devices.

  • PK: id (UUID, shared with the corresponding devices.id for transmitters)
  • mosque_id (NULL while pending approval)
  • provision_state: pending, registered, claimed, rejected, retired
  • config (JSONB), config_version, firmware_version
  • Health: connectivity, reported_status (UNKNOWN…OFFLINE), last_heartbeat_at, last_heartbeat (raw payload), temperature_c
  • Child table mosque_device_events — append-only event log (status_change, cmd, ack, azan_played, alert, provision)

app_installs​

A mobile app installation — explicitly not a device. Introduced in 20260824_0004_app_installs.py.

  • PK: id
  • Child tables: app_install_preferences (per-install settings), app_install_push_tokens (Expo/APNs/FCM tokens)
  • See Identity and App Installation module

triggers​

Records a manual or automatic broadcast/azaan trigger — pre-Alembic table, no DDL in this repo. Known columns (from core/db.py usage, see Azaan Broadcast): trigger_type, is_auto, triggered_by, prayer, title, mosque_id, timestamps. Used both as history and as dedupe state for the offline-azan scheduler.

broadcast_audit_logs / broadcast_audit_errors​

Delivery analytics for every broadcast. DDL owned by ensure_broadcast_audit_tables() in core/db.py.

  • PK: id (bigserial); broadcast_id (UUID, unique) is the external key correlated across a broadcast's start/end
  • broadcast_type: azaan_broadcast, azaan_offline, live_broadcast
  • status: in_progress, completed, completed_with_errors, failed
  • Per-provider counters: {voip,apns,fcm}_{targeted,sent,failed}, delivery-rate columns, mqtt_duration_ms, mqtt_failed
  • broadcast_audit_errors → broadcast_audit_logs.broadcast_id (ON DELETE CASCADE) — per-error detail (provider, error_type, error_message)

See Broadcast Audit.

audio_files​

Uploaded azan/announcement recordings (fastapi-backend/sql/audio_files_supabase.sql).

  • PK: id (UUID)
  • mosque_id (NULL = global default), trigger_type (azaan | jummah | waaz | announcement), prayer ('fajr' or NULL — NULL means "all other azans")
  • file_url, duration_secs, size_bytes, mime_type
  • is_active, is_default — newest upload per category becomes default automatically

azan_auto_settings​

Per-mosque offline-azan scheduler config (fastapi-backend/sql/azan_auto_settings_supabase.sql).

  • PK: mosque_id (one row per mosque, FK to mosques)
  • enabled, grace_minutes (0–60), window_minutes (1–120, must exceed grace_minutes)
  • {fajr,dhuhr,asr,maghrib,isha}_enabled — per-prayer opt-out

Missing row = defaults apply. See Azaan Broadcast.

mosque_prayer_times​

Saved prayer timetable, one row per mosque per date (fastapi-backend/sql/mosque_prayer_times_supabase.sql).

  • PK: (mosque_id, prayer_date)
  • Per-prayer adhan/iqamah pairs (fajr, fajr_adhan, fajr_iqamah, ... through isha), plus sunrise, sunset, ishraq, zawal, suhur, iftar, jumuah

device_invoices / device_invoice_items​

Sales invoicing, unified for both mosque and individual buyers by migration 20260827_0026_device_invoices_unified_devices.py (fastapi-backend/sql/device_invoices_supabase.sql for the base shape).

  • PK: id (UUID); invoice_number (unique, assigned on issue: INV-YYYY-NNNN)
  • status: draft, issued, paid, cancelled
  • mosque_id XOR owner_user_id (CHECK constraint — a buyer is a mosque or a member, never both, never neither)
  • Money columns: subtotal, discount_pct/discount_amount, tax_pct/tax_amount, total (NUMERIC(12,2))
  • device_invoice_items.device_id → devices.id (repointed from the legacy mosque_devices table by migration 0026; snapshots serial_number/device_kind so a line item survives device deletion); a device-linked line item is constrained to quantity = 1

mqtt_service_accounts / mqtt_service_account_acl​

The backend's own broker credential and rules — never a device's. Introduced in 20260824_0014_mqtt_service_accounts.py.

  • mqtt_service_accounts.username (PK), password_hash (bcrypt), disabled_at (revocation = set this, no delete)
  • mqtt_service_account_acl: (username, permission, action, topic) — action constrained to publish/subscribe/all so a broker-unrecognized action can't silently be granted and appear valid in this table

emqx_device_authn / emqx_device_authz (views, not tables)​

Introduced in migrations 0013/0014. UNION ALL of devices rows and mqtt_service_accounts rows — EMQX queries these directly as its Postgres authentication/authorization source. A device's rows are generated (six fixed topic suffixes under masjidconnect/rx/{device_uid}/..., no wildcards); the backend's rows come from mqtt_service_account_acl, including a wildcard subscription no device is ever granted. Disabling an account or clearing device_secret_hash removes it from the view — the same revocation mechanism for both subject types. Full detail: Device Deployment, MQTT Protocol.

Dropped: transmitters, receivers​

Supabase-console leftovers, confirmed dead (referenced by no code — the one name match, count_deployed_transmitters(), actually queries mosque_devices) and dropped with CASCADE in migration 20260827_0022_drop_dead_registry_tables.py, which also removed their two dependents (a superseded view, v_mosque_devices, and one FK constraint off trigger_receipts). Mentioned here only so the names aren't rediscovered and assumed live.