74 lines
2.4 KiB
MySQL
74 lines
2.4 KiB
MySQL
|
-- Email Database Initialization
|
||
|
|
|
||
|
|
CREATE SCHEMA IF NOT EXISTS email;
|
||
|
|
|
||
|
|
-- Email addresses table
|
||
|
|
CREATE TABLE IF NOT EXISTS email.addresses (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
user_id UUID NOT NULL,
|
||
|
|
email VARCHAR(255) NOT NULL,
|
||
|
|
type VARCHAR(50) NOT NULL DEFAULT 'primary',
|
||
|
|
verified BOOLEAN DEFAULT FALSE,
|
||
|
|
verified_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
UNIQUE(user_id, type)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Email preferences
|
||
|
|
CREATE TABLE IF NOT EXISTS email.preferences (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
user_id UUID NOT NULL UNIQUE,
|
||
|
|
marketing BOOLEAN DEFAULT TRUE,
|
||
|
|
transactional BOOLEAN DEFAULT TRUE,
|
||
|
|
notifications BOOLEAN DEFAULT TRUE,
|
||
|
|
digest_frequency VARCHAR(20) DEFAULT 'daily',
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Email logs
|
||
|
|
CREATE TABLE IF NOT EXISTS email.logs (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
recipient_id UUID,
|
||
|
|
recipient_email VARCHAR(255) NOT NULL,
|
||
|
|
template_id VARCHAR(100),
|
||
|
|
subject VARCHAR(500),
|
||
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
||
|
|
provider VARCHAR(50),
|
||
|
|
provider_message_id VARCHAR(255),
|
||
|
|
error_message TEXT,
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
sent_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
delivered_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
opened_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
clicked_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Email templates
|
||
|
|
CREATE TABLE IF NOT EXISTS email.templates (
|
||
|
|
id VARCHAR(100) PRIMARY KEY,
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
subject VARCHAR(500) NOT NULL,
|
||
|
|
html_content TEXT NOT NULL,
|
||
|
|
text_content TEXT,
|
||
|
|
variables JSONB DEFAULT '[]',
|
||
|
|
active BOOLEAN DEFAULT TRUE,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_addresses_user ON email.addresses(user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_logs_recipient ON email.logs(recipient_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_logs_status ON email.logs(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_logs_created ON email.logs(created_at DESC);
|
||
|
|
|
||
|
|
-- Permissions
|
||
|
|
GRANT ALL PRIVILEGES ON SCHEMA email TO lilith;
|
||
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA email TO lilith;
|
||
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA email TO lilith;
|
||
|
|
|
||
|
|
DO $$ BEGIN RAISE NOTICE 'Email database initialized'; END $$;
|