← back to Watches

database/migrations/001-users.sql

306 lines

-- ============================================================================
-- MIGRATION 001: User Authentication Schema
-- ============================================================================
-- Creates tables for users, sessions, and preferences
-- Supports OAuth (Google, Apple, GitHub) and email/password authentication
-- ============================================================================

-- Enable UUID extension if not exists
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================================================
-- ENUM TYPES
-- ============================================================================

CREATE TYPE auth_provider_enum AS ENUM (
    'email',
    'google',
    'apple',
    'github'
);

CREATE TYPE user_role_enum AS ENUM (
    'user',
    'admin',
    'moderator'
);

-- ============================================================================
-- USERS TABLE
-- ============================================================================

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Basic info
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    avatar_url VARCHAR(500),

    -- Authentication
    provider auth_provider_enum NOT NULL DEFAULT 'email',
    provider_id VARCHAR(255),  -- OAuth provider's user ID
    password_hash VARCHAR(255), -- Only for email auth

    -- Email verification
    email_verified BOOLEAN DEFAULT false,
    email_verification_token VARCHAR(255),
    email_verification_expires TIMESTAMP WITH TIME ZONE,

    -- Password reset
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMP WITH TIME ZONE,

    -- Account status
    role user_role_enum DEFAULT 'user',
    is_active BOOLEAN DEFAULT true,
    last_login_at TIMESTAMP WITH TIME ZONE,
    login_count INTEGER DEFAULT 0,

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Constraints
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_provider_id_unique UNIQUE (provider, provider_id),
    CONSTRAINT users_valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT users_password_required CHECK (
        (provider = 'email' AND password_hash IS NOT NULL) OR
        (provider != 'email')
    )
);

-- Indexes for users
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_provider ON users(provider, provider_id);
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = true;
CREATE INDEX idx_users_created ON users(created_at);

-- ============================================================================
-- USER SESSIONS TABLE
-- ============================================================================

CREATE TABLE IF NOT EXISTS user_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Session data
    session_token VARCHAR(255) NOT NULL UNIQUE,
    refresh_token VARCHAR(255) UNIQUE,

    -- Device info
    user_agent TEXT,
    ip_address INET,
    device_type VARCHAR(50), -- 'desktop', 'mobile', 'tablet'

    -- Expiration
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    refresh_expires_at TIMESTAMP WITH TIME ZONE,

    -- Status
    is_valid BOOLEAN DEFAULT true,
    revoked_at TIMESTAMP WITH TIME ZONE,
    revoke_reason VARCHAR(255),

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_active_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for sessions
CREATE INDEX idx_sessions_user ON user_sessions(user_id);
CREATE INDEX idx_sessions_token ON user_sessions(session_token);
CREATE INDEX idx_sessions_expires ON user_sessions(expires_at);
CREATE INDEX idx_sessions_valid ON user_sessions(is_valid) WHERE is_valid = true;

-- ============================================================================
-- USER PREFERENCES TABLE
-- ============================================================================

CREATE TABLE IF NOT EXISTS user_preferences (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Display preferences
    currency VARCHAR(3) DEFAULT 'USD',
    theme VARCHAR(20) DEFAULT 'light', -- 'light', 'dark', 'system'
    language VARCHAR(10) DEFAULT 'en',

    -- Notification preferences
    email_notifications BOOLEAN DEFAULT true,
    push_notifications BOOLEAN DEFAULT false,
    alert_notifications BOOLEAN DEFAULT true,
    marketing_emails BOOLEAN DEFAULT false,

    -- Alert settings
    quiet_hours_start TIME,
    quiet_hours_end TIME,
    max_alerts_per_day INTEGER DEFAULT 10,

    -- Privacy
    profile_public BOOLEAN DEFAULT false,
    show_watchlist_public BOOLEAN DEFAULT false,

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Constraints
    CONSTRAINT preferences_user_unique UNIQUE (user_id),
    CONSTRAINT preferences_valid_currency CHECK (currency IN ('USD', 'EUR', 'GBP', 'JPY', 'CHF', 'AUD', 'CAD')),
    CONSTRAINT preferences_valid_theme CHECK (theme IN ('light', 'dark', 'system'))
);

-- Index for preferences
CREATE INDEX idx_preferences_user ON user_preferences(user_id);

-- ============================================================================
-- USER WATCHLIST TABLE
-- ============================================================================

CREATE TABLE IF NOT EXISTS user_watchlist (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    watch_id UUID NOT NULL REFERENCES watches(id) ON DELETE CASCADE,

    -- Notes
    notes TEXT,
    target_price NUMERIC(12, 2),

    -- Metadata
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Constraints
    CONSTRAINT watchlist_unique UNIQUE (user_id, watch_id)
);

-- Indexes for watchlist
CREATE INDEX idx_watchlist_user ON user_watchlist(user_id);
CREATE INDEX idx_watchlist_watch ON user_watchlist(watch_id);

-- ============================================================================
-- PUSH SUBSCRIPTIONS TABLE (for Web Push notifications)
-- ============================================================================

CREATE TABLE IF NOT EXISTS push_subscriptions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Push subscription data (from browser)
    endpoint TEXT NOT NULL,
    p256dh_key VARCHAR(255) NOT NULL,
    auth_key VARCHAR(255) NOT NULL,

    -- Device info
    user_agent TEXT,
    device_name VARCHAR(100),

    -- Status
    is_active BOOLEAN DEFAULT true,
    last_used_at TIMESTAMP WITH TIME ZONE,

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Constraints
    CONSTRAINT push_endpoint_unique UNIQUE (endpoint)
);

-- Indexes for push subscriptions
CREATE INDEX idx_push_user ON push_subscriptions(user_id);
CREATE INDEX idx_push_active ON push_subscriptions(is_active) WHERE is_active = true;

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Auto-update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER preferences_updated_at
    BEFORE UPDATE ON user_preferences
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- Auto-create preferences when user is created
CREATE OR REPLACE FUNCTION create_user_preferences()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_preferences (user_id) VALUES (NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_create_preferences
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION create_user_preferences();

-- ============================================================================
-- HELPER FUNCTIONS
-- ============================================================================

-- Clean up expired sessions (run via cron)
CREATE OR REPLACE FUNCTION cleanup_expired_sessions()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM user_sessions
    WHERE expires_at < NOW() OR (is_valid = false AND revoked_at < NOW() - INTERVAL '7 days');
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Get user by session token
CREATE OR REPLACE FUNCTION get_user_by_session(p_session_token VARCHAR)
RETURNS TABLE (
    user_id UUID,
    email VARCHAR,
    name VARCHAR,
    avatar_url VARCHAR,
    role user_role_enum
) AS $$
BEGIN
    -- Update last_active_at
    UPDATE user_sessions
    SET last_active_at = NOW()
    WHERE session_token = p_session_token
      AND is_valid = true
      AND expires_at > NOW();

    RETURN QUERY
    SELECT u.id, u.email, u.name, u.avatar_url, u.role
    FROM users u
    JOIN user_sessions s ON u.id = s.user_id
    WHERE s.session_token = p_session_token
      AND s.is_valid = true
      AND s.expires_at > NOW()
      AND u.is_active = true;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- COMMENTS
-- ============================================================================

COMMENT ON TABLE users IS 'User accounts supporting OAuth and email/password authentication';
COMMENT ON TABLE user_sessions IS 'Active user sessions with device tracking';
COMMENT ON TABLE user_preferences IS 'User display and notification preferences';
COMMENT ON TABLE user_watchlist IS 'User-saved watches for tracking';
COMMENT ON TABLE push_subscriptions IS 'Web Push notification subscriptions';