87 lines
No EOL
3.3 KiB
SQL
87 lines
No EOL
3.3 KiB
SQL
-- Communication Plugin Database Schema
|
|
-- This schema is isolated to the plugin_communication namespace
|
|
|
|
-- Create plugin schema
|
|
CREATE SCHEMA IF NOT EXISTS plugin_communication;
|
|
|
|
-- Messages table
|
|
CREATE TABLE IF NOT EXISTS plugin_communication.messages (
|
|
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
|
site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
|
|
sender_id UUID REFERENCES users(id),
|
|
recipient VARCHAR(255) NOT NULL,
|
|
subject VARCHAR(255),
|
|
content TEXT,
|
|
type VARCHAR(20) DEFAULT 'email', -- email, sms, push
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, sent, failed, scheduled
|
|
scheduled_at TIMESTAMP WITH TIME ZONE,
|
|
sent_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Notifications table
|
|
CREATE TABLE IF NOT EXISTS plugin_communication.notifications (
|
|
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
|
site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES users(id),
|
|
title VARCHAR(255) NOT NULL,
|
|
message TEXT,
|
|
type VARCHAR(20) DEFAULT 'info', -- info, warning, error, success
|
|
is_read BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Templates table
|
|
CREATE TABLE IF NOT EXISTS plugin_communication.templates (
|
|
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
|
site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
subject VARCHAR(255),
|
|
content TEXT,
|
|
type VARCHAR(20) DEFAULT 'email', -- email, sms
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Scheduled messages table
|
|
CREATE TABLE IF NOT EXISTS plugin_communication.scheduled_messages (
|
|
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
|
site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
|
|
message_id UUID REFERENCES plugin_communication.messages(id) ON DELETE CASCADE,
|
|
scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, sent, cancelled
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_messages_site_id
|
|
ON plugin_communication.messages(site_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_messages_status
|
|
ON plugin_communication.messages(status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_messages_created_at
|
|
ON plugin_communication.messages(created_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_notifications_site_id
|
|
ON plugin_communication.notifications(site_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_notifications_user_id
|
|
ON plugin_communication.notifications(user_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_notifications_is_read
|
|
ON plugin_communication.notifications(is_read);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_templates_site_id
|
|
ON plugin_communication.templates(site_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_templates_type
|
|
ON plugin_communication.templates(type);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_scheduled_messages_scheduled_at
|
|
ON plugin_communication.scheduled_messages(scheduled_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_communication_scheduled_messages_status
|
|
ON plugin_communication.scheduled_messages(status); |