27 lines
1.4 KiB
SQL
27 lines
1.4 KiB
SQL
CREATE TABLE IF NOT EXISTS notification_preferences (
|
|
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
language VARCHAR(2) NOT NULL DEFAULT 'de' CHECK (language IN ('de', 'en')),
|
|
friend_request BOOLEAN NOT NULL DEFAULT TRUE,
|
|
direct_message BOOLEAN NOT NULL DEFAULT TRUE,
|
|
event_invitation BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS push_notifications (
|
|
id UUID PRIMARY KEY,
|
|
device_id BIGINT NOT NULL REFERENCES push_devices(id) ON DELETE CASCADE,
|
|
session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
|
|
recipient_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
actor_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
kind VARCHAR(32) NOT NULL CHECK (kind IN ('friend_request', 'direct_message', 'event_invitation')),
|
|
object_id BIGINT NOT NULL,
|
|
event_key TEXT NOT NULL,
|
|
token_hash VARCHAR(64) NOT NULL,
|
|
state VARCHAR(16) NOT NULL DEFAULT 'pending' CHECK (state IN ('pending', 'sent', 'dropped', 'failed')),
|
|
attempts SMALLINT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP + INTERVAL '1 hour',
|
|
available_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(device_id, event_key)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_push_notifications_pending
|
|
ON push_notifications(available_at) WHERE state='pending';
|