[MOB-1199] dev: update index constraints for notifications to optimize query performance

[gurusainath]

Description

• Updated index constraints for the notification table to improve query performance and data access efficiency.

Type of Change

• [X] Improvement (change that would cause existing functionality to not work as expected)
• [X] Code refactoring

References

[MOB-1199]

---

[!NOTE] Add multiple composite indexes on notifications to speed up common lookups by receiver, workspace, status, entity, state, sender, and entity lookup.

• Backend/DB
  • db.models.notification.Notification and migration 0108_*:
    • Add composite index notif_receiver_status_idx on ['receiver', 'workspace', 'read_at', 'created_at'].
    • Add composite index notif_receiver_entity_idx on ['receiver', 'workspace', 'entity_name', 'read_at'].
    • Add composite index notif_receiver_state_idx on ['receiver', 'workspace', 'snoozed_till', 'archived_at'].
    • Add composite index notif_receiver_sender_idx on ['receiver', 'workspace', 'sender'].
    • Add composite index notif_entity_lookup_idx on ['workspace', 'entity_identifier', 'entity_name'].

^{Written by Cursor Bugbot for commit 339d5845f12daaa1beeffb0b31969f7a2f769774. This will update automatically on new commits. Configure here.}

Summary by CodeRabbit

• Performance Improvements

  • Faster loading and filtering of notifications; quicker lookups by receiver, sender, entity, and status across inbox, snoozed, and archived views. Improved asset lookup performance.

• Chores

  • Applied a database migration that creates new indexes concurrently to speed queries while minimizing migration impact.

[coderabbitai[bot]]

[!NOTE]

Other AI code review bot(s) detected

CodeRabbit has detected other AI code review bot(s) in this pull request and will avoid duplicating their findings in the review comments. This may lead to a less comprehensive review.

Walkthrough

Database indexes are added to the Notification and FileAsset models to optimize query performance on receiver/workspace lookups and entity identifier searches. A non-atomic Django migration implements these six indexes using concurrent creation.

Changes

Cohort / File(s)	Change Summary
Model Index Definitions apps/api/plane/db/models/notification.py, apps/api/plane/db/models/asset.py	Added five new indexes to Notification model Meta for receiver/workspace/status/entity lookups; added one index to FileAsset model for asset field lookups.
Migration Implementation apps/api/plane/db/migrations/0111_notification_notif_receiver_status_idx_and_more.py	New non-atomic Django 4.2 migration with six AddIndexConcurrently operations: five on notification table (notif_receiver_status_idx, notif_receiver_entity_idx, notif_receiver_state_idx, notif_receiver_sender_idx, notif_entity_lookup_idx) and one on fileasset table (asset_asset_idx).

Estimated code review effort

🎯 2 (Simple) | ⏱️ ~10 minutes

• Verify that index field combinations align with actual query patterns in the codebase
• Confirm the migration syntax is correct and the concurrent index creation strategy is appropriate
• Check that the index names follow project naming conventions

Poem

🐰 Indexes bloom like carrots in the ground,
Queries hop much faster all around,
Five plus one, concurrent and so bright,
Database gardens now optimized just right! ✨

Pre-merge checks and finishing touches

✅ Passed checks (3 passed)

Check name	Status	Explanation
Title check	✅ Passed	The title accurately and concisely describes the main change: adding index constraints to notifications for query performance optimization.
Description check	✅ Passed	The PR description includes a clear description of changes, type of change (with appropriate selections), and references to the related issue (MOB-1199). Test scenarios section is missing but non-critical.
Docstring Coverage	✅ Passed	No functions found in the changed files to evaluate docstring coverage. Skipping docstring coverage check.

✨ Finishing touches

• [ ] 📝 Generate docstrings

🧪 Generate unit tests (beta)

• [ ] Create PR with unit tests
• [ ] Post copyable unit tests in a comment
• [ ] Commit unit tests in branch indexing-notification-fields

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

[makeplane[bot]]

Linked to Plane Work Item(s)

• [MOB-1199] Optimize notifications API

This comment was auto-generated by Plane

[cursor[bot]]

You have run out of free Bugbot PR reviews for this billing cycle. This will reset on October 20.

To receive reviews on all of your PRs, visit the Cursor dashboard to activate Pro and start your 14-day free trial.