Notification Service

Problem/Feature Description

A healthcare platform needs a notification service that manages patient appointment reminders, prescription alerts, and system notifications. The service is built with Python and PostgreSQL and must handle both creating notifications in bulk (when a batch of appointments is scheduled) and querying them efficiently.

The PostgreSQL database has these tables:

• patients -- id, name, email, phone, preferred_channel (email/sms/push), created_at
• notifications -- id, patient_id (FK), notification_type (appointment_reminder/prescription_alert/system), title, body, channel, status (pending/sent/failed/read), scheduled_at, sent_at, created_at, updated_at
• notification_templates -- id, notification_type, channel, subject_template, body_template, created_at

The service needs:

1. Schedule bulk notifications -- Given a list of patient IDs and a notification type, look up each patient's preferred channel, find the matching template, and create notification records for all of them. Appointment reminders come in batches of 200-1000 patients. All notifications in a batch must be created atomically.

2. Mark notifications as sent -- Update a list of notification IDs from "pending" to "sent" and record the sent_at timestamp. This happens in batches of 50-200 after the delivery service confirms delivery.

3. Patient notification history -- Return a patient's notification history (all types) sorted by most recent first. Some patients have thousands of notifications over years of treatment, so the query must handle large histories.

4. Pending notifications feed -- Return all pending notifications scheduled before now, grouped by channel, ordered by scheduled time. This query runs every minute and the results feed into the email/SMS/push delivery workers.

The application uses psycopg 3 (synchronous) and runs as a standalone service.

Output Specification

Produce:

• app/db.py -- Connection pool setup
• app/notifications.py -- The four functions: schedule_bulk, mark_sent, patient_history, pending_feed
• app/main.py -- Application entry point with pool lifecycle