16 lines
650 B
SQL
16 lines
650 B
SQL
CREATE TABLE IF NOT EXISTS notifications (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
|
channel VARCHAR(20) NOT NULL CHECK (channel IN ('push', 'sms', 'email', 'in_app')),
|
|
title VARCHAR(200) NOT NULL,
|
|
body TEXT NOT NULL,
|
|
data JSONB,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed', 'read')),
|
|
sent_at TIMESTAMPTZ,
|
|
read_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_notifications_user ON notifications(user_id);
|
|
CREATE INDEX idx_notifications_status ON notifications(user_id, status);
|