this repo has no description
lewis.moe
1CREATE TYPE notification_channel AS ENUM ('email', 'discord', 'telegram', 'signal');
2CREATE TYPE notification_status AS ENUM ('pending', 'processing', 'sent', 'failed');
3CREATE TYPE notification_type AS ENUM (
4 'welcome',
5 'email_verification',
6 'password_reset',
7 'email_update',
8 'account_deletion'
9);
10
11CREATE TABLE IF NOT EXISTS notification_queue (
12 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
13 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
14 channel notification_channel NOT NULL DEFAULT 'email',
15 notification_type notification_type NOT NULL,
16 status notification_status NOT NULL DEFAULT 'pending',
17 recipient TEXT NOT NULL,
18 subject TEXT,
19 body TEXT NOT NULL,
20 metadata JSONB,
21 attempts INT NOT NULL DEFAULT 0,
22 max_attempts INT NOT NULL DEFAULT 3,
23 last_error TEXT,
24 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
25 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
26 scheduled_for TIMESTAMPTZ NOT NULL DEFAULT NOW(),
27 processed_at TIMESTAMPTZ
28);
29
30CREATE INDEX idx_notification_queue_status_scheduled
31 ON notification_queue(status, scheduled_for)
32 WHERE status = 'pending';
33
34CREATE INDEX idx_notification_queue_user_id ON notification_queue(user_id);
35
36ALTER TABLE users ADD COLUMN IF NOT EXISTS preferred_notification_channel notification_channel NOT NULL DEFAULT 'email';