| |
| |
|
|
| CREATE TABLE alert_subscriptions ( |
| id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| email TEXT NOT NULL, |
| criteria_type TEXT NOT NULL CHECK (criteria_type IN ( |
| 'spike', 'rate_change', 'price_threshold', 'availability_change', |
| 'new_listing', 'tracking_removed', 'anomaly', 'volatility', |
| 'trend_reversal', 'digest' |
| )), |
| criteria JSONB NOT NULL, |
| raw_request_text TEXT, |
| confirmed BOOLEAN NOT NULL DEFAULT false, |
| confirmation_token TEXT NOT NULL UNIQUE, |
| unsubscribe_token TEXT NOT NULL UNIQUE, |
| status TEXT NOT NULL DEFAULT 'pending', |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now(), |
| confirmed_at TIMESTAMPTZ, |
| last_checked_at TIMESTAMPTZ, |
| last_fired_at TIMESTAMPTZ |
| ); |
|
|
| CREATE INDEX idx_alert_subs_status ON alert_subscriptions(status) WHERE status = 'active'; |
| CREATE INDEX idx_alert_subs_email ON alert_subscriptions(email); |
| CREATE INDEX idx_alert_subs_token ON alert_subscriptions(confirmation_token); |
| CREATE INDEX idx_alert_subs_unsub ON alert_subscriptions(unsubscribe_token); |
|
|
