"HIGH: Missing database indexes on uid and device_id — full table scans on every notification lookup"

[pyrahermesagent]

Severity: HIGH

File: ApiApp/models.py
Lines: 14-25

Description

AttestedFCMDevice has no database indexes on uid or device_id, two fields that are queried on every API request:

• SendNotificationView: AttestedFCMDevice.objects.filter(uid=user_id) — queries ALL devices for a user
• FCMTokenUpdateView: AttestedFCMDevice.objects.get(device_id=request.device_id) — looks up the calling device
• UidUpdateView: AttestedFCMDevice.objects.get(device_id=request.device_id) — same
• DeviceRegisterSerializer: AttestedFCMDevice.objects.get(device_id=device_id, type=platform) — registration lookup

Without indexes, every query does a full table scan. As device count grows, these queries become O(n) and can cause:

• API latency spikes: Every request scans the entire table
• Database load: CPU and I/O waste on repeated full scans
• DoS vector: An attacker with many registered devices can cause all lookups to slow down

Current model:

class AttestedFCMDevice(AbstractFCMDevice):
    uid = models.TextField(verbose_name=_("User identifier"), unique=False, null=True)
    # No index on uid
    # No index on device_id (inherited from AbstractFCMDevice)

Impact

• Performance degradation: Linear query time as device count grows
• Database scalability: Table scans don not scale beyond thousands of rows
• DoS potential: High device counts → slow queries → API timeouts

Fix

Add database indexes on the most queried fields:

class Meta:
    indexes = [
        models.Index(fields=["uid"], name="idx_fcm_device_uid"),
        models.Index(fields=["device_id"], name="idx_fcm_device_id"),
        models.Index(fields=["uid", "registration_id"], name="idx_fcm_device_uid_regid"),
    ]

The composite index on ["uid", "registration_id"] optimizes the SendNotificationView query:

AttestedFCMDevice.objects.filter(uid=user_id).exclude(registration_id__isnull=True)

@valentynhol please review.