20 lines
823 B
SQL
20 lines
823 B
SQL
-- 020: User messages/notifications (user-service / notification-service)
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
title VARCHAR(200) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
type VARCHAR(30) NOT NULL DEFAULT 'system' CHECK (type IN (
|
|
'system', 'trade', 'coupon', 'wallet', 'kyc', 'compliance', 'promotion'
|
|
)),
|
|
is_read BOOLEAN NOT NULL DEFAULT false,
|
|
reference_type VARCHAR(30),
|
|
reference_id UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_messages_user_id ON messages(user_id);
|
|
CREATE INDEX idx_messages_is_read ON messages(is_read);
|
|
CREATE INDEX idx_messages_type ON messages(type);
|
|
CREATE INDEX idx_messages_created_at ON messages(created_at DESC);
|