Index notification by channel and recipient for feed queries

[gregbarajas]

Environment

• Mercur 2.4.1 (@mercurjs/core), Medusa 2.22.3 (@medusajs/notification)
• Confirmed from the published npm dist/migrations of @medusajs/notification 2.22.3, not a fork

Problem / current behavior

The notification table has indexes on deleted_at, idempotency_key, provider_id and receiver_id only (@medusajs/notification/dist/migrations/Migration20240628075401.js, Migration20240830094712.js, Migration20250120110514.js). A notification feed reads one recipient's notifications on one channel, newest first: channel = 'feed' AND "to" = :recipient ORDER BY created_at DESC LIMIT n, through listNotifications({ channel, to }, { order: { created_at: "DESC" }, take }) or SQL. No index serves that filter, so every feed load scans the whole table, which grows with every email, SMS and feed notification the store sends.

Proposed solution / API

A migration in @medusajs/notification:

CREATE INDEX IF NOT EXISTS "IDX_notification_channel_to_created_at"
  ON "notification" ("channel", "to", "created_at")
  WHERE deleted_at IS NULL;

No API change.

Why core

Any project that shows a per-recipient notification feed (Medusa's admin feed, a vendor or customer inbox) runs this query, and the table grows with every notification sent. Indexing a core table from a project migration risks clashing with a later core migration, so the index belongs in the module that owns the table.

Our workaround

We add the same index from our own project migration, on a table our project doesn't own. It works, but core doesn't know the index exists, and a future core migration on the same columns could duplicate or clash with it. We'll drop ours once core ships one.

Why it matters

Feed loads stay fast as notification volume grows, for every Medusa store with a feed.

Affected apps

• API (any admin, vendor or customer feed)

Suggested implementation notes

Area	Where
Model index	packages/modules/notification/src/models/notification.ts
Migration	packages/modules/notification/src/migrations/