-- Migration 001: Initial Schema
-- Creates the initial database schema for the MCP Calendar Server
-- Create the migrations table to track applied migrations
CREATE TABLE IF NOT EXISTS migrations (
id SERIAL PRIMARY KEY,
version VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Users table
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
display_name VARCHAR(255),
timezone VARCHAR(100) DEFAULT 'UTC',
preferences JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Connected calendars table
CREATE TABLE IF NOT EXISTS connected_calendars (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
calendar_id VARCHAR(255) NOT NULL,
provider VARCHAR(50) NOT NULL,
access_token TEXT NOT NULL,
refresh_token TEXT,
token_expiry TIMESTAMP WITH TIME ZONE,
is_default BOOLEAN DEFAULT false,
sync_enabled BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(user_id, calendar_id, provider)
);
-- Reminders table
CREATE TABLE IF NOT EXISTS reminders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(500) NOT NULL,
description TEXT,
due_date_time TIMESTAMP WITH TIME ZONE,
priority VARCHAR(20) DEFAULT 'medium',
status VARCHAR(20) DEFAULT 'pending',
tags TEXT[] DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- User sessions table (for authentication)
CREATE TABLE IF NOT EXISTS user_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
session_token VARCHAR(255) UNIQUE NOT NULL,
refresh_token VARCHAR(255) UNIQUE NOT NULL,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_used TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
CREATE INDEX IF NOT EXISTS idx_connected_calendars_user_id ON connected_calendars(user_id);
CREATE INDEX IF NOT EXISTS idx_connected_calendars_calendar_id ON connected_calendars(calendar_id);
CREATE INDEX IF NOT EXISTS idx_connected_calendars_provider ON connected_calendars(provider);
CREATE INDEX IF NOT EXISTS idx_reminders_user_id ON reminders(user_id);
CREATE INDEX IF NOT EXISTS idx_reminders_due_date ON reminders(due_date_time);
CREATE INDEX IF NOT EXISTS idx_reminders_status ON reminders(status);
CREATE INDEX IF NOT EXISTS idx_reminders_priority ON reminders(priority);
CREATE INDEX IF NOT EXISTS idx_reminders_created_at ON reminders(created_at);
CREATE INDEX IF NOT EXISTS idx_reminders_tags ON reminders USING GIN (tags);
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_session_token ON user_sessions(session_token);
CREATE INDEX IF NOT EXISTS idx_user_sessions_expires_at ON user_sessions(expires_at);
-- Create trigger function for updating updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
-- Create triggers for updated_at columns
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
DROP TRIGGER IF EXISTS update_connected_calendars_updated_at ON connected_calendars;
CREATE TRIGGER update_connected_calendars_updated_at
BEFORE UPDATE ON connected_calendars
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
DROP TRIGGER IF EXISTS update_reminders_updated_at ON reminders;
CREATE TRIGGER update_reminders_updated_at
BEFORE UPDATE ON reminders
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Create constraints
ALTER TABLE users ADD CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
ALTER TABLE reminders ADD CONSTRAINT reminders_priority_check CHECK (priority IN ('low', 'medium', 'high', 'urgent'));
ALTER TABLE reminders ADD CONSTRAINT reminders_status_check CHECK (status IN ('pending', 'completed', 'cancelled'));
ALTER TABLE connected_calendars ADD CONSTRAINT connected_calendars_provider_check CHECK (provider IN ('google', 'outlook', 'apple', 'caldav'));
-- Insert initial migration record
INSERT INTO migrations (version, name) VALUES ('001', 'Initial Schema') ON CONFLICT (version) DO NOTHING;
