Skip to main content

​
Overview

OdontologyApp includes a comprehensive notification system that delivers both personal and global notifications to users based on their roles and activities. The system supports different notification types and automatically manages read/unread states.

​
Features

​
Notification Types

The system supports four notification types:
  • info: General information messages (blue theme)
  • success: Success confirmations (green theme)
  • warning: Important warnings (amber theme)
  • error: Error notifications (red theme)

​
Personal vs Global Notifications

Personal Notifications

Targeted to specific users for individual actions like appointment updates, payment confirmations, or personal alerts

Global Notifications

Broadcast to all users with specific roles (admin, doctor, secretary) for system-wide announcements

​
Database Schema

The notification system uses two tables:

​
notifications table

Stores personal notifications for individual users. 
CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  type ENUM('info','success','warning','error') DEFAULT 'info',
  title VARCHAR(200) NOT NULL,
  message TEXT,
  link VARCHAR(255),
  is_read TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)

​
global_notifications table

Stores system-wide notifications targeted by role. 
CREATE TABLE global_notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('info','success','warning','error') DEFAULT 'info',
  title VARCHAR(200) NOT NULL,
  message TEXT,
  link VARCHAR(255),
  roles JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

​
API Endpoints

The notification system is accessible through /api/notifications

​
GET /api/notifications

Retrieve all notifications for the current user (both personal and global). 
const response = await fetch('/api/notifications');
const data = await response.json();
// { notifications: [...], unreadCount: 5 }
Implementation: /src/routes/api/notifications/+server.js:5
The GET endpoint uses the stored procedure sp_get_user_notifications which automatically merges personal and global notifications based on user role.

​
POST /api/notifications

Create a new personal notification. 
const response = await fetch('/api/notifications', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    user_id: 5,
    type: 'info',
    title: 'Appointment Reminder',
    message: 'You have an appointment tomorrow at 10:00 AM',
    link: '/appointments'
  })
});
Implementation: /src/routes/api/notifications/+server.js:28

​
PATCH /api/notifications

Mark notifications as read. 
const response = await fetch('/api/notifications', {
  method: 'PATCH',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    ids: [1, 3, 5]
  })
});
Implementation: /src/routes/api/notifications/+server.js:49

​
Stored Procedures

​
sp_get_user_notifications

Retrieves merged personal and global notifications for a user. 
CALL sp_get_user_notifications(user_id, user_role);
Logic:
  • Returns personal notifications for the user
  • Unions with global notifications matching the user’s role
  • Global notifications from last 7 days only
  • Orders by created_at DESC
  • Limits to 25 most recent
Defined in: /migrate_sps_phase10.cjs:96

​
sp_create_notification

Creates a new personal notification. 
CALL sp_create_notification(
  user_id,
  'success',
  'Payment Confirmed',
  'Your payment has been processed successfully',
  '/finance'
);
Defined in: /migrate_sps_phase10.cjs:117

​
sp_mark_notifications_read

Marks one or all notifications as read. 
-- Mark specific notification
CALL sp_mark_notifications_read(user_id, notification_id);

-- Mark all as read
CALL sp_mark_notifications_read(user_id, NULL);
Defined in: /migrate_sps_phase10.cjs:132

​
Setup Instructions

1

Create Tables

Run the setup script to create notification tables:
node setup_notifications.cjs
This creates both notifications and global_notifications tables.
2

Create Stored Procedures

Run the migration to create the stored procedures:
node migrate_sps_phase10.cjs
3

Verify Setup

Check that tables and procedures were created successfully:
SHOW TABLES LIKE '%notification%';
SHOW PROCEDURE STATUS WHERE Name LIKE '%notification%';

​
Configuration Options

​
type
enum
default:"info"
Notification type: info, success, warning, or error
​
title
string
required
Notification title (max 200 characters)
​
message
text
Detailed notification message
​
link
string
Optional navigation link when notification is clicked
​
roles
JSON array
For global notifications: Array of roles to target ["admin", "doctor", "secretary"]

​
Integration Examples

​
Triggering Notifications from Backend

// In any API route
import { pool } from '$lib/server/db';

// Create notification when payment is received
await pool.query(
  'CALL sp_create_notification(?, ?, ?, ?, ?)',
  [
    userId,
    'success',
    'Payment Received',
    `Payment of $${amount} has been processed`,
    '/finance/payments'
  ]
);

​
System-wide Notifications

// Notify all admins about critical inventory
await pool.query(
  `INSERT INTO global_notifications (type, title, message, link, roles)
   VALUES (?, ?, ?, ?, ?)`,
  [
    'warning',
    'Low Inventory Alert',
    'Multiple items are below minimum stock levels',
    '/admin/inventory',
    JSON.stringify(['admin', 'secretary'])
  ]
);

​
Frontend Integration

// Fetch notifications on mount
import { onMount } from 'svelte';

let notifications = [];
let unreadCount = 0;

onMount(async () => {
  const res = await fetch('/api/notifications');
  const data = await res.json();
  notifications = data.notifications;
  unreadCount = data.unreadCount;
});

// Mark as read
async function markAsRead(notificationId) {
  await fetch('/api/notifications', {
    method: 'PATCH',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ ids: [notificationId] })
  });
}

​
Best Practices

Notification titles should be under 50 characters when possible. Use the message field for detailed information.
  • error: Only for actual errors requiring immediate attention
  • warning: Important but not critical information
  • success: Confirmation of completed actions
  • info: General informational messages
Global notifications are only shown for 7 days to prevent notification overload. For permanent announcements, consider using a different mechanism.
Personal notifications with is_read = 0 will persist indefinitely. Consider implementing an archival or cleanup strategy for old notifications.

Reminders

Automated appointment reminders via WhatsApp and email

Webhooks

Event-driven integrations with external systems

Build docs developers (and LLMs) love

Get started for freeTalk to us