`database/migrations/002-price-alerts.sql`

42 lines
-- Migration 002: Price Alerts Schema
CREATE TABLE IF NOT EXISTS price_alerts (
    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,
    target_price NUMERIC(12, 2) NOT NULL,
    direction VARCHAR(10) NOT NULL CHECK (direction IN ('above', 'below')),
    is_active BOOLEAN DEFAULT true,
    triggered_at TIMESTAMP WITH TIME ZONE,
    trigger_price NUMERIC(12, 2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT price_alerts_unique UNIQUE (user_id, watch_id, direction)
);

CREATE INDEX idx_alerts_user ON price_alerts(user_id);
CREATE INDEX idx_alerts_active ON price_alerts(is_active, user_id) WHERE is_active = true;
CREATE INDEX idx_alerts_watch ON price_alerts(watch_id);

CREATE TABLE IF NOT EXISTS alert_notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    alert_id UUID NOT NULL REFERENCES price_alerts(id) ON DELETE CASCADE,
    notification_type VARCHAR(20) NOT NULL,
    sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    delivered BOOLEAN DEFAULT false,
    error TEXT
);

CREATE TABLE IF NOT EXISTS aggregated_prices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    watch_id UUID NOT NULL REFERENCES watches(id) ON DELETE CASCADE UNIQUE,
    avg_price NUMERIC(12, 2),
    min_price NUMERIC(12, 2),
    max_price NUMERIC(12, 2),
    median_price NUMERIC(12, 2),
    confidence INTEGER,
    data_points INTEGER,
    sources JSONB,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_aggregated_watch ON aggregated_prices(watch_id);