# Notifications Module (Tenant + Superadmin)

## Phase 1: Requirement Analysis

### Scope
- Queue-first custom notification pipeline for tenant alerts.
- One alert can target multiple groups; same user must get one notification only.
- Tenant isolation must be strict.
- Superadmin needs cross-tenant analytics view.

### Risks
- Duplicate fanout when user exists in multiple groups.
- Cross-tenant leak if dynamic connection not set before every query/job.
- Full-table scans in high-volume alerts.
- Counter drift between unread table and actual unread rows.
- Retry storms in queue failures.

### Mitigations
- Per-user-per-alert-per-channel hash uniqueness.
- Hard tenant connection switch in every queue job.
- Process only alerts with `status='open'` and `notified_at IS NULL`.
- `notification_counters` + cache invalidation/recalculate fallback.
- Retries with backoff and failure logs in `notification_logs`.

## Phase 2: Architecture

### ERD (Tenant DB)
```mermaid
erDiagram
    alerts ||--o{ notifications : "alert_id"
    notifications ||--o{ notification_logs : "notification_id"

    alerts {
      bigint id PK
      varchar alert_hash
      timestamp notified_at
      varchar severity
      varchar status
    }

    notifications {
      bigint id PK
      bigint tenant_id
      bigint project_id
      bigint alert_id
      bigint user_id
      bigint group_id
      varchar channel
      varchar severity
      varchar status
      varchar notification_hash
      timestamp read_at
      timestamp archived_at
    }

    notification_logs {
      bigint id PK
      bigint notification_id
      bigint alert_id
      bigint user_id
      varchar status
      varchar source_hash
      int attempt
    }

    notification_counters {
      bigint id PK
      bigint tenant_id
      bigint project_id
      bigint user_id
      bigint unread_count
    }
```

### Flow
1. `alerts:generate {project_id}` queues `GenerateAlertsJob`.
2. `GenerateAlertsJob` generates alerts and queues `ProcessAlertNotificationsJob`.
3. `ProcessAlertNotificationsJob` loads pending alerts and queues `DispatchNotificationJob` per alert.
4. `DispatchNotificationJob` resolves groups/users, deduplicates users, inserts notification rows, updates counters, writes logs.
5. `MirrorNotificationEventJob` writes event to central table `central_notification_events` for superadmin reporting.

### Unread/Bell Count
- Source of truth: tenant table `notification_counters`.
- Fast path: cache key `notif:unread:{tenant_id}:{project_id}:{user_id}` TTL 30s.
- Recovery: on mismatch operations call recalculate from `notifications` unread rows.

## Phase 3: Database Design

### Tenant DB tables
- `notifications`
- `notification_logs`
- `notification_counters`

### Required fields supported
- `tenant_id`
- `project_id`
- `alert_id`
- `user_id`
- `group_id`
- `channel`
- `severity`
- `status`
- `read_at`
- `sent_at`
- `notification_hash`

### Indexes and constraints
- `notifications(notification_hash, user_id, channel)` unique for dedup.
- `notifications(user_id, created_at desc)` for user center listing.
- `notifications(user_id, read_at, archived_at)` for bell count.
- `notification_logs(source_hash)` unique for idempotent logs.
- `notification_counters(tenant_id, project_id, user_id)` unique.
- `alerts(status, notified_at, id)` to optimize pending notification scans.

### Partition recommendation (future)
For PostgreSQL millions scale:
- Partition `notifications` by month on `created_at`.
- Partition `central_notification_events` by month on `created_at`.
- Keep local indexes on each partition for `user_id` + `created_at`.

## Phase 4: Laravel Implementation

### Added components
- Models: tenant and central notification models.
- Repository: `NotificationRepositoryInterface`, `EloquentNotificationRepository`.
- Services: rule resolution, dispatch, counter, pipeline.
- Jobs: process queue, dispatch queue, central mirror queue.
- Controllers: tenant notification APIs and superadmin reporting API.
- Requests: validated filter inputs.
- Policy: user can only access own tenant notification.
- Commands:
  - `db:create-notification-tables {project_id}`
  - `notifications:dispatch {project_id}`

## Phase 5: Dedup Logic

Input case:
- Group1 users: 1,2
- Group2 users: 2,3

Output:
- Users: 1,2,3 only once.

Dedup mechanism:
1. In-memory dedup in resolver (`seenUsers`).
2. Persistent dedup via unique key with `notification_hash + user_id + channel`.

## Phase 6: Queue Design

- `ProcessAlertNotificationsJob`: orchestrator with `WithoutOverlapping` lock.
- `DispatchNotificationJob`: per-alert fanout worker, retries/backoff, tenant DB switch.
- `MirrorNotificationEventJob`: async central analytics mirror.
- Failure path writes `notification_logs` status `failed`.

## Phase 7: User Notification Center APIs

Tenant APIs under `/api/notifications`:
- `GET /unread-count`
- `GET /bell`
- `GET /`
- `GET /{id}`
- `POST /{id}/read`
- `POST /mark-all-read`
- `POST /{id}/archive`

Capabilities:
- Own notifications only.
- Pagination, filters, search, date range, severity.
- Bell unread count support.

## Phase 8: Superadmin Notification APIs

Superadmin API:
- `GET /api/superadmin/notifications`

Filters:
- `tenant_id`, `project_id`, `user_id`, `severity`, `status`, date range.

Recommended architecture:
- Best current option: async event mirror into central DB table.
- Next scale step: stream to warehouse (BigQuery/ClickHouse/Snowflake) from central events.

## Phase 9: Testing

Implemented tests:
- Dedup across groups.
- Repository duplicate prevention via unique hash.

Recommended additional tests:
- Tenant isolation feature tests.
- Queue retry and failure tests.
- High-load tests on pending alerts queue.

## Phase 10: Performance Strategy

- Keep queue workers for `notifications` queue separate from heavy import workers.
- Add index maintenance and vacuum schedule.
- Use monthly partitioning after sustained growth.
- Keep unread count in counters table + cache for O(1) bell count.

## Phase 11: Rollback Strategy

1. Stop notification workers.
2. Disable dispatch command schedule.
3. Revert route exposure.
4. Keep central mirror table for audit history (or drop if compliance allows).
5. Remove command/job dispatch from `GenerateAlertsJob`.
6. Recalculate counters after rollback completion.

## Operational Commands

```bash
php artisan db:create-notification-tables {project_id}
php artisan alerts:generate {project_id}
php artisan notifications:dispatch {project_id}
```