86112cf73a
Add missing enum values to trading schema and refactor price_alerts table structure to align with backend types. Addresses DDL gaps from analysis. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
90 lines
3.9 KiB
PL/PgSQL
90 lines
3.9 KiB
PL/PgSQL
-- ============================================================================
|
|
-- Schema: trading
|
|
-- Table: price_alerts
|
|
-- Description: Alertas de precio configuradas por usuarios para el módulo de trading
|
|
-- Dependencies: trading.alert_type, trading.alert_status, auth.users, trading.symbols
|
|
-- Created: 2026-02-03
|
|
-- Updated: 2026-02-04
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE trading.price_alerts (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Relaciones
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
symbol_id UUID NOT NULL REFERENCES trading.symbols(id) ON DELETE CASCADE,
|
|
|
|
-- Configuración de alerta
|
|
alert_type trading.alert_type NOT NULL,
|
|
target_price DECIMAL(18,8),
|
|
percent_threshold DECIMAL(5,2),
|
|
|
|
-- Estado
|
|
status trading.alert_status NOT NULL DEFAULT 'active',
|
|
|
|
-- Ejecución
|
|
triggered_at TIMESTAMPTZ,
|
|
triggered_price DECIMAL(18,8),
|
|
|
|
-- Expiración
|
|
expires_at TIMESTAMPTZ,
|
|
|
|
-- Notificación
|
|
notification_channels TEXT[] DEFAULT ARRAY['push', 'email'],
|
|
|
|
-- Mensaje personalizado
|
|
message TEXT,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_target_price_positive CHECK (target_price IS NULL OR target_price > 0),
|
|
CONSTRAINT chk_percent_threshold_valid CHECK (percent_threshold IS NULL OR (percent_threshold > 0 AND percent_threshold <= 100)),
|
|
CONSTRAINT chk_alert_config CHECK (
|
|
(alert_type IN ('price_above', 'price_below') AND target_price IS NOT NULL) OR
|
|
(alert_type = 'percent_change' AND percent_threshold IS NOT NULL) OR
|
|
(alert_type = 'volume_spike')
|
|
)
|
|
);
|
|
|
|
-- Índices para performance
|
|
CREATE INDEX idx_price_alerts_user ON trading.price_alerts(user_id);
|
|
CREATE INDEX idx_price_alerts_symbol ON trading.price_alerts(symbol_id);
|
|
CREATE INDEX idx_price_alerts_active ON trading.price_alerts(status) WHERE status = 'active';
|
|
CREATE INDEX idx_price_alerts_expiry ON trading.price_alerts(expires_at)
|
|
WHERE expires_at IS NOT NULL AND status = 'active';
|
|
|
|
-- Trigger para actualizar updated_at
|
|
CREATE OR REPLACE FUNCTION trading.update_price_alerts_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_price_alerts_updated_at
|
|
BEFORE UPDATE ON trading.price_alerts
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION trading.update_price_alerts_timestamp();
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE trading.price_alerts IS 'Alertas de precio configuradas por usuarios para el módulo de trading';
|
|
COMMENT ON COLUMN trading.price_alerts.id IS 'Identificador único de la alerta';
|
|
COMMENT ON COLUMN trading.price_alerts.user_id IS 'Referencia al usuario (auth.users)';
|
|
COMMENT ON COLUMN trading.price_alerts.symbol_id IS 'Referencia al símbolo de trading';
|
|
COMMENT ON COLUMN trading.price_alerts.alert_type IS 'Tipo de alerta: price_above, price_below, percent_change, volume_spike';
|
|
COMMENT ON COLUMN trading.price_alerts.target_price IS 'Precio objetivo para alertas de precio (price_above/price_below)';
|
|
COMMENT ON COLUMN trading.price_alerts.percent_threshold IS 'Umbral porcentual para alertas de cambio porcentual';
|
|
COMMENT ON COLUMN trading.price_alerts.status IS 'Estado de la alerta: active, triggered, expired, cancelled';
|
|
COMMENT ON COLUMN trading.price_alerts.triggered_at IS 'Timestamp cuando se disparó la alerta';
|
|
COMMENT ON COLUMN trading.price_alerts.triggered_price IS 'Precio al momento de dispararse la alerta';
|
|
COMMENT ON COLUMN trading.price_alerts.expires_at IS 'Fecha de expiración opcional de la alerta';
|
|
COMMENT ON COLUMN trading.price_alerts.notification_channels IS 'Canales de notificación: push, email, sms, etc.';
|
|
COMMENT ON COLUMN trading.price_alerts.message IS 'Mensaje personalizado para la alerta';
|
|
COMMENT ON COLUMN trading.price_alerts.created_at IS 'Timestamp de creación';
|
|
COMMENT ON COLUMN trading.price_alerts.updated_at IS 'Timestamp de última actualización';
|