c651fe5a30
ST-1.1: financial.refunds - Already exists with approval flow ST-1.2: education.instructors - Created with GIN indexes ST-1.3: trading.price_alerts - FK exists, idempotent migration added ST-1.4: ml.prediction_overlays - New table + overlay columns New files: - ddl/schemas/education/tables/17-instructors.sql - ddl/schemas/ml/tables/12-prediction_overlays.sql - migrations/2026-02-03_add_predictions_overlay.sql - migrations/2026-02-03_add_price_alerts_symbol_fk.sql Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
105 lines
3.5 KiB
SQL
105 lines
3.5 KiB
SQL
-- ============================================================================
|
|
-- Migration: Add symbol_id FK to trading.price_alerts
|
|
-- Date: 2026-02-03
|
|
-- Task: TASK-2026-02-03-ANALISIS-DDL-MODELADO / ST-1.3
|
|
-- Description: Ensures price_alerts has proper FK relationship to symbols
|
|
-- ============================================================================
|
|
|
|
-- Note: This migration is idempotent - safe to run multiple times
|
|
-- The DDL file (11-price_alerts.sql) already includes symbol_id with FK,
|
|
-- but this migration ensures existing databases are updated.
|
|
|
|
-- Step 1: Add column if not exists (for DBs created before FK was added to DDL)
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'trading'
|
|
AND table_name = 'price_alerts'
|
|
AND column_name = 'symbol_id'
|
|
) THEN
|
|
ALTER TABLE trading.price_alerts ADD COLUMN symbol_id UUID;
|
|
RAISE NOTICE 'Column symbol_id added to trading.price_alerts';
|
|
ELSE
|
|
RAISE NOTICE 'Column symbol_id already exists in trading.price_alerts';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Step 2: Add FK constraint if not exists
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.table_constraints
|
|
WHERE constraint_schema = 'trading'
|
|
AND table_name = 'price_alerts'
|
|
AND constraint_name = 'fk_price_alerts_symbol'
|
|
) AND NOT EXISTS (
|
|
SELECT 1 FROM information_schema.table_constraints
|
|
WHERE constraint_schema = 'trading'
|
|
AND table_name = 'price_alerts'
|
|
AND constraint_name = 'price_alerts_symbol_id_fkey'
|
|
) THEN
|
|
ALTER TABLE trading.price_alerts
|
|
ADD CONSTRAINT fk_price_alerts_symbol
|
|
FOREIGN KEY (symbol_id) REFERENCES trading.symbols(id) ON DELETE CASCADE;
|
|
RAISE NOTICE 'FK constraint fk_price_alerts_symbol added';
|
|
ELSE
|
|
RAISE NOTICE 'FK constraint for symbol_id already exists';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Step 3: Create index if not exists
|
|
CREATE INDEX IF NOT EXISTS idx_price_alerts_symbol_id
|
|
ON trading.price_alerts(symbol_id);
|
|
|
|
-- Step 4: Migration script for existing data
|
|
-- If there's a legacy 'symbol' varchar column, migrate data to symbol_id
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'trading'
|
|
AND table_name = 'price_alerts'
|
|
AND column_name = 'symbol'
|
|
AND data_type IN ('character varying', 'text')
|
|
) THEN
|
|
-- Migrate data from symbol (varchar) to symbol_id (uuid)
|
|
UPDATE trading.price_alerts pa
|
|
SET symbol_id = s.id
|
|
FROM trading.symbols s
|
|
WHERE pa.symbol = s.symbol
|
|
AND pa.symbol_id IS NULL;
|
|
|
|
RAISE NOTICE 'Data migrated from symbol column to symbol_id';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Step 5: Verify integrity after migration
|
|
DO $$
|
|
DECLARE
|
|
orphan_count INTEGER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO orphan_count
|
|
FROM trading.price_alerts
|
|
WHERE symbol_id IS NULL;
|
|
|
|
IF orphan_count > 0 THEN
|
|
RAISE WARNING 'Found % price_alerts with NULL symbol_id - manual review required', orphan_count;
|
|
ELSE
|
|
RAISE NOTICE 'All price_alerts have valid symbol_id references';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Optional: Make NOT NULL after confirming all data is migrated
|
|
-- Uncomment after verifying no orphan records:
|
|
-- ALTER TABLE trading.price_alerts ALTER COLUMN symbol_id SET NOT NULL;
|
|
|
|
-- ============================================================================
|
|
-- Verification query (run manually to confirm):
|
|
-- SELECT
|
|
-- COUNT(*) as total_alerts,
|
|
-- COUNT(symbol_id) as with_symbol_id,
|
|
-- COUNT(*) - COUNT(symbol_id) as missing_symbol_id
|
|
-- FROM trading.price_alerts;
|
|
-- ============================================================================
|