bmc_hub/MDfile/REMINDER_SYSTEM_QUICKSTART.md

Reminder System Quick Start

1. Apply Database Migration

# Connect to database and run migration
docker-compose exec -T postgres psql -U bmc_hub -d bmc_hub << EOF
$(cat migrations/096_reminder_system.sql)
EOF

Or via psql client:

psql -h localhost -U bmc_hub -d bmc_hub -f migrations/096_reminder_system.sql

Verify tables created:

\d sag_reminders
\d sag_reminder_logs
\d user_notification_preferences
\d sag_reminder_queue

2. Install Dependencies

pip install aiosmtplib==3.0.2
# Or
pip install -r requirements.txt

3. Configure Environment

Edit .env:

# ✅ Keep these disabled for local development
REMINDERS_ENABLED=false
REMINDERS_EMAIL_ENABLED=false
REMINDERS_DRY_RUN=true

# 📧 SMTP Configuration (optional for local testing)
EMAIL_SMTP_HOST=smtp.gmail.com
EMAIL_SMTP_PORT=587
EMAIL_SMTP_USER=your-email@gmail.com
EMAIL_SMTP_PASSWORD=your-app-password

# 💬 Mattermost (optional)
MATTERMOST_ENABLED=true
MATTERMOST_WEBHOOK_URL=https://mattermost.example.com/hooks/xxxxx
MATTERMOST_CHANNEL=reminders

4. Restart Application

docker-compose restart api

Check logs:

docker-compose logs -f api

Should see:

✅ Reminder job scheduled (every 5 minutes)

5. Test Frontend Notification System

1. Open http://localhost:8000/
2. Log in
3. Open browser console (F12)
4. Should see: ✅ Reminder system initialized
5. Create a test reminder via database:

INSERT INTO sag_reminders (
    sag_id, title, message, priority,
    trigger_type, trigger_config,
    recipient_user_ids, recipient_emails,
    recurrence_type, is_active, created_by_user_id,
    scheduled_at, next_check_at
) VALUES (
    1,  -- Replace with actual case ID
    'Test Reminder',
    'This is a test reminder',
    'high',
    'time_based',
    '{}',
    '{1}',  -- Replace with actual user ID
    '{}',
    'once',
    true,
    1,  -- Replace with your user ID
    now(),
    now()
);

6. Wait ~30 seconds or manually call: GET /api/v1/reminders/pending/me?user_id=1
7. Should see popup toast notification

6. Test API Endpoints

Get User Preferences

curl -X GET http://localhost:8000/api/v1/users/me/notification-preferences?user_id=1

Update User Preferences

curl -X PATCH http://localhost:8000/api/v1/users/me/notification-preferences?user_id=1 \
  -H "Content-Type: application/json" \
  -d {
    "notify_frontend": true,
    "notify_email": false,
    "notify_mattermost": true
  }

Create Reminder

curl -X POST http://localhost:8000/api/v1/sag/1/reminders?user_id=1 \
  -H "Content-Type: application/json" \
  -d {
    "title": "Test Reminder",
    "message": "This is a test",
    "priority": "normal",
    "trigger_type": "time_based",
    "trigger_config": {},
    "recipient_user_ids": [1],
    "recurrence_type": "once"
  }

List Reminders

curl -X GET http://localhost:8000/api/v1/sag/1/reminders

Snooze Reminder

curl -X POST http://localhost:8000/api/v1/sag/reminders/1/snooze?user_id=1 \
  -H "Content-Type: application/json" \
  -d { "duration_minutes": 30 }

Dismiss Reminder

curl -X POST http://localhost:8000/api/v1/sag/reminders/1/dismiss?user_id=1 \
  -H "Content-Type: application/json" \
  -d { "reason": "Already handled" }

7. Test Status Change Trigger

1. Create a reminder with status_change trigger:

INSERT INTO sag_reminders (
    sag_id, title, message, priority,
    trigger_type, trigger_config,
    recipient_user_ids, recipient_emails,
    recurrence_type, is_active, created_by_user_id
) VALUES (
    1,  -- Your test case
    'Case entered In Progress',
    'Case has been moved to "i_gang" status',
    'high',
    'status_change',
    '{"target_status": "i_gang"}',  -- Trigger when status changes to "i_gang"
    '{1}',
    '{}',
    'once',
    true,
    1
);

2. Update case status:

UPDATE sag_sager SET status = 'i_gang' WHERE id = 1;

3. Check queue:

SELECT * FROM sag_reminder_queue WHERE status = 'pending';

4. Should see pending event. Wait for scheduler to process (next 5-min interval)

5. Check logs:

SELECT * FROM sag_reminder_logs ORDER BY triggered_at DESC LIMIT 5;

8. Enable Production Features (When Ready)

To actually send reminders:

REMINDERS_ENABLED=true
REMINDERS_DRY_RUN=false

# Enable channels you want
REMINDERS_EMAIL_ENABLED=true
REMINDERS_MATTERMOST_ENABLED=true

Then restart and test again.

9. Monitor Reminder Execution

View Pending Reminders

SELECT * FROM v_pending_reminders LIMIT 5;

View Queue Status

SELECT id, reminder_id, status, error_message 
FROM sag_reminder_queue 
WHERE status IN ('pending', 'failed')
ORDER BY created_at DESC 
LIMIT 10;

View Notification Logs

SELECT id, reminder_id, sag_id, status, triggered_at, channels_used 
FROM sag_reminder_logs 
ORDER BY triggered_at DESC 
LIMIT 20;

Check Rate Limiting

SELECT user_id, COUNT(*) as count, MAX(triggered_at) as last_sent
FROM sag_reminder_logs
WHERE status = 'sent' AND triggered_at > CURRENT_TIMESTAMP - INTERVAL '1 hour'
GROUP BY user_id
ORDER BY count DESC;

Common Issues

"Reminder system not initialized"

• User not authenticated
• Check that JWT token is valid
• Check browser console for auth errors

Reminders not appearing

• Check REMINDERS_ENABLED=true
• Check next_check_at <= NOW()
• Check recipient_user_ids includes current user
• Verify polling API returns data: GET /api/v1/reminders/pending/me

Email not sending

• Check REMINDERS_EMAIL_ENABLED=true
• Check SMTP credentials in .env
• Check REMINDERS_DRY_RUN=false
• Review application logs for SMTP errors
• Try sending with REMINDERS_DRY_RUN=true first (logs only)

Status trigger not firing

• Verify case ID exists
• Check trigger_config matches: {"target_status": "expected_status"}
• Manually check: UPDATE sag_sager SET status = 'target_status'
• Query sag_reminder_queue for new events

Next Steps

1. ✅ Database migration applied
2. ✅ Environment configured
3. ✅ Frontend notifications working
4. ✅ API endpoints tested
5. → Configure email/Mattermost credentials
6. → Enable production features
7. → Monitor logs and metrics
8. → Set up alerting for failures

See REMINDER_SYSTEM_IMPLEMENTATION.md for detailed documentation.