Notification Preferences — Tier 2 Implementation Plan

Context

Phase 2 (email delivery) shipped a simple email_notifications_enabled boolean on the core_data_users table — a global on/off toggle. This is insufficient for production use because:

• Users can't control which notifications trigger emails (e.g., "email me for overdue training but not plan approvals")
• Users can't choose frequency (immediate vs. daily digest)
• Company admins can't set organization-wide defaults (e.g., "all employees get training emails by default")
• Safety-critical notifications (overdue, expired, threshold exceeded) should always send immediately regardless of user preferences

This plan implements per-category preferences with frequency control, daily digest batching, and company-level defaults.

---

Design

Preference Model

Three-tier resolution: User preference -> Company default -> System default

System defaults (hardcoded)
  ^ overridden by
Company defaults (admin sets for all users, user_id = NULL)
  ^ overridden by
User preferences (individual user overrides)

Frequency Options

Frequency	Behavior
immediate	Email sent as soon as notification is created (current behavior)
daily_digest	Batched and sent at 8 AM in the user's timezone (or company TZ)
off	No email, in-app only

Always-Immediate Types (Override)

These safety-critical types always send immediately, regardless of user's frequency preference:

ALWAYS_IMMEDIATE_TYPES = {
    "training_overdue",
    "cert_expired",
    "plan_overdue",
    "quantity_threshold_exceeded",
    "ai_generation_failed",
}

System Defaults by Category

Category	In-App	Email Frequency
workflow	ON	daily_digest
inventory	ON	daily_digest
compliance	ON	immediate
ai	ON	immediate
export	ON	off
training	ON	immediate
labels	ON	off

---

Implementation Steps

Step 1: Migration — Create notification_preferences table

File: tellus-ehs-hazcom-service/alembic/versions/YYYYMMDD_HHMM_add_notification_preferences.py — CREATE

CREATE TABLE notification_preferences (
    preference_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES core_data_companies(company_id) ON DELETE CASCADE,
    user_id UUID REFERENCES core_data_users(user_id) ON DELETE CASCADE,  -- NULL = company default
    category VARCHAR(30) NOT NULL,           -- 'workflow', 'training', etc.
    in_app_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    email_frequency VARCHAR(20) NOT NULL DEFAULT 'immediate',  -- 'immediate', 'daily_digest', 'off'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE (company_id, user_id, category)   -- one preference per (company, user, category)
);

CREATE INDEX idx_notification_prefs_company ON notification_preferences(company_id);
CREATE INDEX idx_notification_prefs_user ON notification_preferences(company_id, user_id);

Design decisions:

• Per-category, not per-type. Per-type (32 types) creates too many rows and overwhelming UI. Per-category (7 categories) is manageable and maps to the UI groupings.
• email_frequency instead of email_enabled boolean. Supports the three states: immediate, daily_digest, off. More extensible than a boolean.
• user_id = NULL for company defaults. Single table for both levels avoids schema duplication.

---

Step 2: Add notification_preferences ORM model

File: tellus-ehs-hazcom-service/app/db/models/notification.py — EDIT

Add after the Notification class:

class NotificationPreference(Base):
    __tablename__ = "notification_preferences"

    preference_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    company_id = Column(
        UUID(as_uuid=True),
        ForeignKey("core_data_companies.company_id", ondelete="CASCADE"),
        nullable=False,
    )
    user_id = Column(
        UUID(as_uuid=True),
        ForeignKey("core_data_users.user_id", ondelete="CASCADE"),
        nullable=True,  # NULL = company default
    )
    category = Column(String(30), nullable=False)
    in_app_enabled = Column(Boolean, nullable=False, default=True)
    email_frequency = Column(String(20), nullable=False, default="immediate")
    created_at = Column(DateTime(timezone=True), nullable=False, default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), nullable=True)

Add constants:

class EmailFrequency:
    IMMEDIATE = "immediate"
    DAILY_DIGEST = "daily_digest"
    OFF = "off"

ALWAYS_IMMEDIATE_TYPES = {
    NotificationType.TRAINING_OVERDUE,
    NotificationType.CERT_EXPIRED,
    NotificationType.PLAN_OVERDUE,
    NotificationType.QUANTITY_THRESHOLD_EXCEEDED,
    NotificationType.AI_GENERATION_FAILED,
}

# System defaults per category: (in_app_enabled, email_frequency)
DEFAULT_PREFERENCES = {
    NotificationCategory.WORKFLOW: (True, EmailFrequency.DAILY_DIGEST),
    NotificationCategory.INVENTORY: (True, EmailFrequency.DAILY_DIGEST),
    NotificationCategory.COMPLIANCE: (True, EmailFrequency.IMMEDIATE),
    NotificationCategory.AI: (True, EmailFrequency.IMMEDIATE),
    NotificationCategory.EXPORT: (True, EmailFrequency.OFF),
    NotificationCategory.TRAINING: (True, EmailFrequency.IMMEDIATE),
    NotificationCategory.LABELS: (True, EmailFrequency.OFF),
}

---

Step 3: Add notification_preferences repository

File: tellus-ehs-hazcom-service/app/db/repositories/notification_preference_repository.py — CREATE

Methods:

• get_user_preferences(company_id, user_id) -> list of NotificationPreference rows for that user
• get_company_defaults(company_id) -> list where user_id IS NULL
• upsert(company_id, user_id, category, in_app_enabled, email_frequency) -> create or update
• delete(company_id, user_id, category) -> remove override (reverts to company/system default)

Register in UnitOfWork:

self.notification_preferences = NotificationPreferenceRepository(db)

---

Step 4: Create NotificationPreferenceService

File: tellus-ehs-hazcom-service/app/services/notification_preference_service.py — CREATE

Key method — get_effective_preference():

def get_effective_preference(
    self, company_id: UUID, user_id: UUID, category: str
) -> tuple[bool, str]:
    """
    Resolve effective preference for a user + category.
    Returns (in_app_enabled, email_frequency).

    Resolution order:
    1. User-level preference (if exists)
    2. Company default (if exists)
    3. System default from DEFAULT_PREFERENCES
    """
    # 1. Check user preference
    user_pref = self.uow.notification_preferences.get_user_preferences(
        company_id, user_id
    )
    for p in user_pref:
        if p.category == category:
            return (p.in_app_enabled, p.email_frequency)

    # 2. Check company default
    company_prefs = self.uow.notification_preferences.get_company_defaults(company_id)
    for p in company_prefs:
        if p.category == category:
            return (p.in_app_enabled, p.email_frequency)

    # 3. System default
    default = DEFAULT_PREFERENCES.get(category, (True, EmailFrequency.IMMEDIATE))
    return default

Other methods:

• get_all_preferences(company_id, user_id) -> returns all 7 categories with resolved values
• update_preference(company_id, user_id, category, in_app_enabled, email_frequency) -> upsert
• update_company_default(company_id, category, in_app_enabled, email_frequency) -> upsert with user_id=NULL
• reset_to_default(company_id, user_id, category) -> delete the user override

---

Step 5: Update NotificationService to check preferences

File: tellus-ehs-hazcom-service/app/services/notification_service.py — EDIT

5a: In-app filtering in _create_for_recipients()

Before creating a notification for a recipient, check if in_app_enabled is True for that category:

from app.services.notification_preference_service import NotificationPreferenceService

def _create_for_recipients(self, company_id, recipient_ids, notification_type, category, ...):
    pref_svc = NotificationPreferenceService(self.uow)
    notifications = []
    email_recipients = []

    for uid in recipient_ids:
        in_app_enabled, email_frequency = pref_svc.get_effective_preference(
            company_id, uid, category
        )

        # Create in-app notification (skip if user disabled this category)
        if in_app_enabled:
            n = Notification(...)
            notifications.append(n)

        # Collect recipients for email dispatch
        if notification_type in EMAIL_WORTHY_TYPES:
            if notification_type in ALWAYS_IMMEDIATE_TYPES:
                # Safety-critical: always send immediately
                email_recipients.append((uid, EmailFrequency.IMMEDIATE))
            elif email_frequency != EmailFrequency.OFF:
                email_recipients.append((uid, email_frequency))

    created = self.uow.notifications.create_bulk(notifications)

    # Dispatch emails
    immediate_ids = [uid for uid, freq in email_recipients if freq == EmailFrequency.IMMEDIATE]
    digest_ids = [uid for uid, freq in email_recipients if freq == EmailFrequency.DAILY_DIGEST]

    if immediate_ids:
        self._dispatch_emails_for_recipients(
            company_id, immediate_ids, notification_type, title, message, action_url, metadata
        )

    if digest_ids:
        # Store in pending_digest_emails for the daily digest job
        self._queue_for_digest(company_id, digest_ids, notification_type, title, message, action_url)

    return created

5b: Same for notify_user() — single-recipient version

---

Step 6: Digest email infrastructure

6a: Migration — Create pending_digest_emails table

File: tellus-ehs-hazcom-service/alembic/versions/YYYYMMDD_HHMM_add_pending_digest_emails.py — CREATE

CREATE TABLE pending_digest_emails (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES core_data_companies(company_id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES core_data_users(user_id) ON DELETE CASCADE,
    notification_type VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    action_url VARCHAR(500),
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_pending_digest_user ON pending_digest_emails(user_id, created_at);

6b: _queue_for_digest() method in NotificationService

def _queue_for_digest(self, company_id, user_ids, notification_type, title, message, action_url):
    """Insert rows into pending_digest_emails for the daily digest job."""
    from app.db.models.notification import PendingDigestEmail
    for uid in user_ids:
        self.uow._db.add(PendingDigestEmail(
            company_id=company_id, user_id=uid,
            notification_type=notification_type, title=title,
            message=message, action_url=action_url,
        ))

6c: Background job — Daily digest sender

File: tellus-ehs-background-service/app/services/email/digest.py — CREATE

async def send_daily_digests(db):
    """
    Called by the scheduler at 8 AM daily.
    Groups pending_digest_emails by user, renders a digest email, sends via Mailgun,
    then deletes processed rows.
    """
    # 1. SELECT DISTINCT user_id FROM pending_digest_emails
    # 2. For each user:
    #    a. Fetch all pending items ordered by created_at
    #    b. Check email_notifications_enabled
    #    c. Render digest template (list of notification summaries)
    #    d. Send via mailgun_client.send_email()
    #    e. DELETE processed rows

6d: Digest email template

File: tellus-ehs-background-service/app/services/email/templates.py — EDIT

Add render_digest_html(recipient_name, items) and render_digest_text(recipient_name, items):

• Renders a summary table/list of all notifications grouped by category
• Each item shows: title, message, action link
• Subject: "Tellus EHS — Daily Summary ({count} notifications)"

6e: Register digest job in scheduler

File: tellus-ehs-background-service/app/jobs/scheduler.py — EDIT

Add a new CronTrigger job:

scheduler.add_job(
    send_daily_digests_job,
    CronTrigger(hour=8, minute=0),
    id="daily_digest",
    name="Daily notification digest email",
)

---

Step 7: API endpoints for preferences

File: tellus-ehs-hazcom-service/app/api/v1/notifications.py — EDIT

Add endpoints:

@router.get("/preferences")
# Returns all 7 categories with resolved (in_app_enabled, email_frequency) for the current user

@router.put("/preferences/{category}")
# Body: { in_app_enabled: bool, email_frequency: "immediate" | "daily_digest" | "off" }
# Upserts user preference for a specific category

@router.delete("/preferences/{category}")
# Removes user override, reverts to company/system default

# Admin-only endpoints:
@router.get("/preferences/company-defaults")
# Returns company-level defaults for all 7 categories

@router.put("/preferences/company-defaults/{category}")
# Sets company-level default for a category (requires admin role)

Schemas: Add Pydantic models in app/schemas/notification.py:

class NotificationPreferenceResponse(BaseModel):
    category: str
    in_app_enabled: bool
    email_frequency: str  # "immediate", "daily_digest", "off"
    source: str           # "user", "company", "system" — shows where the value came from

class NotificationPreferencesListResponse(BaseModel):
    preferences: List[NotificationPreferenceResponse]

class NotificationPreferenceUpdate(BaseModel):
    in_app_enabled: bool
    email_frequency: str = Field(pattern="^(immediate|daily_digest|off)$")

---

Step 8: Frontend — Notification preferences page

File: tellus-ehs-hazcom-ui/src/pages/settings/notification-preferences.tsx — CREATE

UI layout:

+--------------------------------------------------+
| Notification Preferences                          |
+--------------------------------------------------+
|                                                   |
| +- Workflow ----------------------------------+   |
| | Plan approvals, rejections, publishing       |   |
| | In-App: [ON/OFF]   Email: [Immediate v]     |   |
| +----------------------------------------------+   |
|                                                   |
| +- Training ----------------------------------+   |
| | Assignments, reminders, certifications       |   |
| | In-App: [ON/OFF]   Email: [Immediate v]     |   |
| | ! Overdue & expired alerts always sent       |   |
| |   immediately for safety compliance.         |   |
| +----------------------------------------------+   |
|                                                   |
| +- Compliance --------------------------------+   |
| | Review dates, quantity thresholds             |   |
| | In-App: [ON/OFF]   Email: [Immediate v]     |   |
| +----------------------------------------------+   |
|                                                   |
| +- Inventory ---------------------------------+   |
| | Chemical additions, SDS updates               |   |
| | In-App: [ON/OFF]   Email: [Daily Digest v]  |   |
| +----------------------------------------------+   |
|                                                   |
| +- AI ----------------------------------------+   |
| | AI content generation status                  |   |
| | In-App: [ON/OFF]   Email: [Immediate v]     |   |
| +----------------------------------------------+   |
|                                                   |
| +- Export ------------------------------------+   |
| | Document export notifications                 |   |
| | In-App: [ON/OFF]   Email: [Off v]           |   |
| +----------------------------------------------+   |
|                                                   |
| +- Labels ------------------------------------+   |
| | GHS label generation and printing             |   |
| | In-App: [ON/OFF]   Email: [Off v]           |   |
| +----------------------------------------------+   |
|                                                   |
| [Save Preferences]                                |
|                                                   |
| i Source: User / Company Default / System Default |
+--------------------------------------------------+

File: tellus-ehs-hazcom-ui/src/services/api/notification.api.ts — EDIT

• Add getNotificationPreferences(), updateNotificationPreference(), resetNotificationPreference()

File: tellus-ehs-hazcom-ui/src/App.tsx — EDIT

• Add route /settings/notification-preferences

---

Step 9: Remove old email_notifications_enabled flag

Once Tier 2 is deployed and preferences are migrated:

File: Migration — EDIT

• Migrate existing email_notifications_enabled = false users to have email_frequency = 'off' for ALL categories
• Drop email_notifications_enabled column from core_data_users

File: tellus-ehs-hazcom-service/app/db/models/user.py — EDIT

• Remove email_notifications_enabled column

File: Background handlers + NotificationService — EDIT

• Remove checks for email_notifications_enabled (now handled by preference service)

---

Files Summary

#	File	Action	Step
1	service/alembic/versions/..._add_notification_preferences.py	CREATE	1
2	service/alembic/versions/..._add_pending_digest_emails.py	CREATE	6a
3	service/app/db/models/notification.py	EDIT	2
4	service/app/db/repositories/notification_preference_repository.py	CREATE	3
5	service/app/db/repositories/unit_of_work.py	EDIT	3
6	service/app/services/notification_preference_service.py	CREATE	4
7	service/app/services/notification_service.py	EDIT	5
8	service/app/api/v1/notifications.py	EDIT	7
9	service/app/schemas/notification.py	EDIT	7
10	bg-service/app/services/email/digest.py	CREATE	6c
11	bg-service/app/services/email/templates.py	EDIT	6d
12	bg-service/app/jobs/scheduler.py	EDIT	6e
13	bg-service/app/services/sqs_consumer/handlers.py	EDIT	9
14	bg-service/app/services/email/send_notification_email.py	EDIT	9
15	ui/src/pages/settings/notification-preferences.tsx	CREATE	8
16	ui/src/services/api/notification.api.ts	EDIT	8
17	ui/src/App.tsx	EDIT	8
18	service/alembic/versions/..._drop_email_notifications_enabled.py	CREATE	9
19	service/app/db/models/user.py	EDIT	9

6 new files, 13 modified files

---

Implementation Order

Step 1-2: Database + model (notification_preferences table)
    |
Step 3-4: Repository + service (preference resolution logic)
    |
Step 5: Wire into NotificationService (preference-aware filtering)
    |
Step 6: Digest infrastructure (pending_digest_emails + daily job + digest template)
    |
Step 7: API endpoints (GET/PUT/DELETE preferences)
    |
Step 8: Frontend settings page
    |
Step 9: Remove old email_notifications_enabled flag (cleanup)

Steps 1-5 and 6 can be done in parallel (preference resolution vs. digest infrastructure).

---

Key Design Decisions

1. Per-category, not per-type: 7 categories vs. 32 types. Per-type creates UI complexity and row bloat. Users think in categories ("mute training emails") not individual types.

2. Always-immediate override: 5 safety-critical types bypass user preferences entirely. A user can't turn off training_overdue emails — that's a compliance requirement.

3. Digest uses a staging table, not a queue: pending_digest_emails rows are inserted synchronously by NotificationService, then batch-processed by the 8 AM scheduler job. This avoids SQS complexity for time-delayed delivery.

4. Source tracking: The API returns source: "user" | "company" | "system" so the UI can show where a preference value came from (and whether it's an override).

5. Migration path: The existing email_notifications_enabled boolean is preserved during rollout. Step 9 (cleanup) runs after confirming Tier 2 works. Users with email_notifications_enabled=false get migrated to email_frequency='off' for all categories.