mirror of https://github.com/synctv-org/synctv
You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
32 lines
1.3 KiB
MySQL
32 lines
1.3 KiB
MySQL
2 months ago
|
CREATE TABLE IF NOT EXISTS notifications (
|
||
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
||
|
|
type SMALLINT NOT NULL,
|
||
|
|
title VARCHAR(255) NOT NULL,
|
||
|
|
content TEXT NOT NULL,
|
||
|
|
data JSONB NOT NULL,
|
||
|
|
is_read BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
||
|
|
CHECK (jsonb_typeof(data) = 'object'),
|
||
|
|
PRIMARY KEY (id, created_at)
|
||
|
|
) PARTITION BY RANGE (created_at);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_read_created
|
||
|
|
ON notifications(user_id, is_read, created_at DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_unread
|
||
|
|
ON notifications(user_id, created_at DESC)
|
||
|
|
WHERE is_read = FALSE;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_type_created
|
||
|
|
ON notifications(user_id, type, created_at DESC)
|
||
|
|
WHERE is_read = FALSE;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_title_trgm
|
||
|
|
ON notifications USING gin(title gin_trgm_ops);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_content_trgm
|
||
|
|
ON notifications USING gin(content gin_trgm_ops);
|
||
|
|
|
||
|
|
CREATE TRIGGER trigger_update_notifications_updated_at
|
||
|
|
BEFORE UPDATE ON notifications
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION update_updated_at_column();
|