36c9c7cfe3
- Removed 9 deprecated migration files (001-006 up/down, auth_schema, trading_service_events) - Updated 12 core migrations with improved constraints and indexing - Consolidated schema from 22 migrations to 18 clean migrations - All migrations tested and applied successfully (Agent 32 validation) - Zero migration errors in production database
428 lines
27 KiB
PL/PgSQL
428 lines
27 KiB
PL/PgSQL
-- Dual-Provider Configuration Migration for Foxhunt HFT Trading System
|
|
-- ======================================================================
|
|
-- This migration adds support for dual data providers (Databento + Benzinga)
|
|
-- and removes legacy Polygon configurations.
|
|
|
|
-- === PROVIDER CONFIGURATION CATEGORIES ===
|
|
|
|
-- Add market data providers category
|
|
INSERT INTO config_categories (category_name, parent_id, category_path, description, is_system, display_order)
|
|
SELECT 'providers', id, 'trading.providers', 'Market data and news provider configurations', false, 4
|
|
FROM config_categories WHERE category_path = 'trading';
|
|
|
|
-- Add subcategories for each provider
|
|
INSERT INTO config_categories (category_name, parent_id, category_path, description, is_system, display_order)
|
|
SELECT 'databento', id, 'trading.providers.databento', 'Databento market data provider settings', false, 1
|
|
FROM config_categories WHERE category_path = 'trading.providers';
|
|
|
|
INSERT INTO config_categories (category_name, parent_id, category_path, description, is_system, display_order)
|
|
SELECT 'benzinga', id, 'trading.providers.benzinga', 'Benzinga news and data provider settings', false, 2
|
|
FROM config_categories WHERE category_path = 'trading.providers';
|
|
|
|
-- === PROVIDER CONFIGURATION TABLES ===
|
|
|
|
-- Provider credentials and connection settings
|
|
CREATE TABLE IF NOT EXISTS provider_configurations (
|
|
id SERIAL PRIMARY KEY,
|
|
provider_name VARCHAR(50) NOT NULL,
|
|
config_key VARCHAR(200) NOT NULL,
|
|
config_value JSONB NOT NULL,
|
|
value_type VARCHAR(50) NOT NULL DEFAULT 'string',
|
|
environment VARCHAR(50) NOT NULL DEFAULT 'development',
|
|
is_sensitive BOOLEAN NOT NULL DEFAULT false,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
description TEXT,
|
|
validation_schema JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by VARCHAR(100) NOT NULL DEFAULT CURRENT_USER,
|
|
updated_by VARCHAR(100) NOT NULL DEFAULT CURRENT_USER,
|
|
|
|
-- Constraints
|
|
CONSTRAINT uk_provider_config_key_env UNIQUE (provider_name, config_key, environment),
|
|
CONSTRAINT chk_provider_name CHECK (provider_name IN ('databento', 'benzinga')),
|
|
CONSTRAINT chk_provider_value_type CHECK (value_type IN ('string', 'number', 'boolean', 'object', 'array')),
|
|
CONSTRAINT chk_provider_environment CHECK (environment IN ('development', 'staging', 'production', 'test'))
|
|
);
|
|
|
|
-- Provider subscription and feature settings
|
|
CREATE TABLE IF NOT EXISTS provider_subscriptions (
|
|
id SERIAL PRIMARY KEY,
|
|
provider_name VARCHAR(50) NOT NULL,
|
|
subscription_type VARCHAR(100) NOT NULL, -- 'equities', 'options', 'crypto', 'news', etc.
|
|
dataset VARCHAR(100) NOT NULL, -- Provider-specific dataset identifier
|
|
symbols TEXT[], -- Array of symbols/instruments
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
environment VARCHAR(50) NOT NULL DEFAULT 'development',
|
|
rate_limit_per_second INTEGER,
|
|
max_concurrent_connections INTEGER DEFAULT 1,
|
|
retry_attempts INTEGER DEFAULT 3,
|
|
timeout_seconds INTEGER DEFAULT 30,
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_subscription_provider CHECK (provider_name IN ('databento', 'benzinga')),
|
|
CONSTRAINT chk_subscription_environment CHECK (environment IN ('development', 'staging', 'production', 'test'))
|
|
);
|
|
|
|
-- Provider API endpoint and routing configuration
|
|
CREATE TABLE IF NOT EXISTS provider_endpoints (
|
|
id SERIAL PRIMARY KEY,
|
|
provider_name VARCHAR(50) NOT NULL,
|
|
endpoint_type VARCHAR(50) NOT NULL, -- 'live', 'historical', 'news', 'fundamentals'
|
|
base_url VARCHAR(500) NOT NULL,
|
|
websocket_url VARCHAR(500),
|
|
api_version VARCHAR(20),
|
|
environment VARCHAR(50) NOT NULL DEFAULT 'development',
|
|
is_primary BOOLEAN NOT NULL DEFAULT false, -- Primary endpoint for failover
|
|
priority INTEGER DEFAULT 0, -- Lower number = higher priority
|
|
health_check_path VARCHAR(200),
|
|
auth_method VARCHAR(50) NOT NULL DEFAULT 'api_key', -- 'api_key', 'oauth', 'bearer_token'
|
|
connection_pool_size INTEGER DEFAULT 10,
|
|
request_timeout_ms INTEGER DEFAULT 5000,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_endpoint_provider CHECK (provider_name IN ('databento', 'benzinga')),
|
|
CONSTRAINT chk_endpoint_type CHECK (endpoint_type IN ('live', 'historical', 'news', 'fundamentals', 'analytics')),
|
|
CONSTRAINT chk_endpoint_environment CHECK (environment IN ('development', 'staging', 'production', 'test')),
|
|
CONSTRAINT chk_auth_method CHECK (auth_method IN ('api_key', 'oauth', 'bearer_token', 'basic_auth'))
|
|
);
|
|
|
|
-- === INDEXES FOR PERFORMANCE ===
|
|
|
|
-- Provider configurations indexes
|
|
CREATE INDEX idx_provider_configurations_provider ON provider_configurations(provider_name);
|
|
CREATE INDEX idx_provider_configurations_environment ON provider_configurations(environment);
|
|
CREATE INDEX idx_provider_configurations_active ON provider_configurations(is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_provider_configurations_sensitive ON provider_configurations(is_sensitive) WHERE is_sensitive = true;
|
|
|
|
-- Provider subscriptions indexes
|
|
CREATE INDEX idx_provider_subscriptions_provider ON provider_subscriptions(provider_name);
|
|
CREATE INDEX idx_provider_subscriptions_type ON provider_subscriptions(subscription_type);
|
|
CREATE INDEX idx_provider_subscriptions_active ON provider_subscriptions(is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_provider_subscriptions_symbols ON provider_subscriptions USING GIN(symbols);
|
|
|
|
-- Provider endpoints indexes
|
|
CREATE INDEX idx_provider_endpoints_provider ON provider_endpoints(provider_name);
|
|
CREATE INDEX idx_provider_endpoints_type ON provider_endpoints(endpoint_type);
|
|
CREATE INDEX idx_provider_endpoints_primary ON provider_endpoints(is_primary) WHERE is_primary = true;
|
|
CREATE INDEX idx_provider_endpoints_priority ON provider_endpoints(priority);
|
|
|
|
-- === DATABENTO CONFIGURATION ===
|
|
|
|
-- Databento API Configuration
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_api_key', id, 'trading.providers.databento', '""'::jsonb, 'string', 'development', 'Databento API key for authentication', ARRAY['databento', 'api', 'authentication'], true, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_api_key', id, 'trading.providers.databento', '""'::jsonb, 'string', 'production', 'Production Databento API key', ARRAY['databento', 'api', 'authentication'], true, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_dataset', id, 'trading.providers.databento', '"XNAS.ITCH"'::jsonb, 'string', 'development', 'Primary Databento dataset for market data', ARRAY['databento', 'dataset', 'market_data'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_dataset', id, 'trading.providers.databento', '"XNAS.ITCH"'::jsonb, 'string', 'production', 'Production Databento dataset', ARRAY['databento', 'dataset', 'market_data'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_symbols', id, 'trading.providers.databento', '["AAPL", "MSFT", "GOOGL", "TSLA", "AMZN"]'::jsonb, 'array', 'development', 'List of symbols to subscribe to', ARRAY['databento', 'symbols', 'subscription'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_connection_timeout_ms', id, 'trading.providers.databento', '30000'::jsonb, 'number', 'development', 'Connection timeout for Databento API', ARRAY['databento', 'timeout', 'connection'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_request_timeout_ms', id, 'trading.providers.databento', '10000'::jsonb, 'number', 'development', 'Request timeout for Databento API calls', ARRAY['databento', 'timeout', 'request'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_rate_limit_rps', id, 'trading.providers.databento', '100'::jsonb, 'number', 'development', 'Rate limit for Databento API requests', ARRAY['databento', 'rate_limit'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_enable_live_data', id, 'trading.providers.databento', 'true'::jsonb, 'boolean', 'development', 'Enable live market data streaming', ARRAY['databento', 'live_data', 'streaming'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'databento_enable_historical_data', id, 'trading.providers.databento', 'true'::jsonb, 'boolean', 'development', 'Enable historical data retrieval', ARRAY['databento', 'historical_data'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.databento';
|
|
|
|
-- === BENZINGA CONFIGURATION ===
|
|
|
|
-- Benzinga API Configuration
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_api_key', id, 'trading.providers.benzinga', '""'::jsonb, 'string', 'development', 'Benzinga API key for authentication', ARRAY['benzinga', 'api', 'authentication'], true, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_api_key', id, 'trading.providers.benzinga', '""'::jsonb, 'string', 'production', 'Production Benzinga API key', ARRAY['benzinga', 'api', 'authentication'], true, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_subscription_tier', id, 'trading.providers.benzinga', '"pro"'::jsonb, 'string', 'development', 'Benzinga subscription tier (basic, pro, enterprise)', ARRAY['benzinga', 'subscription', 'tier'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_subscription_tier', id, 'trading.providers.benzinga', '"enterprise"'::jsonb, 'string', 'production', 'Production Benzinga subscription tier', ARRAY['benzinga', 'subscription', 'tier'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_enable_news_feed', id, 'trading.providers.benzinga', 'true'::jsonb, 'boolean', 'development', 'Enable Benzinga news feed', ARRAY['benzinga', 'news', 'feed'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_enable_analyst_ratings', id, 'trading.providers.benzinga', 'true'::jsonb, 'boolean', 'development', 'Enable analyst ratings data', ARRAY['benzinga', 'analyst_ratings'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_enable_earnings_data', id, 'trading.providers.benzinga', 'true'::jsonb, 'boolean', 'development', 'Enable earnings calendar and data', ARRAY['benzinga', 'earnings'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_connection_timeout_ms', id, 'trading.providers.benzinga', '30000'::jsonb, 'number', 'development', 'Connection timeout for Benzinga API', ARRAY['benzinga', 'timeout', 'connection'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_rate_limit_rpm', id, 'trading.providers.benzinga', '1000'::jsonb, 'number', 'development', 'Rate limit for Benzinga API requests per minute', ARRAY['benzinga', 'rate_limit'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
INSERT INTO config_settings (config_key, category_id, category_path, config_value, value_type, environment, description, tags, is_sensitive, is_system, hot_reload, restart_required)
|
|
SELECT 'benzinga_news_categories', id, 'trading.providers.benzinga', '["earnings", "analyst-ratings", "sec-filings", "mergers-acquisitions"]'::jsonb, 'array', 'development', 'News categories to subscribe to', ARRAY['benzinga', 'news', 'categories'], false, false, true, false
|
|
FROM config_categories WHERE category_path = 'trading.providers.benzinga';
|
|
|
|
-- === PROVIDER ENDPOINT CONFIGURATIONS ===
|
|
|
|
-- Databento endpoints
|
|
INSERT INTO provider_endpoints (provider_name, endpoint_type, base_url, websocket_url, api_version, environment, is_primary, priority, health_check_path, auth_method, connection_pool_size, request_timeout_ms) VALUES
|
|
('databento', 'live', 'https://api.databento.com', 'wss://api.databento.com/v0/live', 'v0', 'development', true, 1, '/v0/metadata', 'api_key', 5, 5000),
|
|
('databento', 'historical', 'https://api.databento.com', NULL, 'v0', 'development', true, 1, '/v0/metadata', 'api_key', 10, 30000),
|
|
('databento', 'live', 'https://api.databento.com', 'wss://api.databento.com/v0/live', 'v0', 'production', true, 1, '/v0/metadata', 'api_key', 20, 5000),
|
|
('databento', 'historical', 'https://api.databento.com', NULL, 'v0', 'production', true, 1, '/v0/metadata', 'api_key', 50, 30000);
|
|
|
|
-- Benzinga endpoints
|
|
INSERT INTO provider_endpoints (provider_name, endpoint_type, base_url, websocket_url, api_version, environment, is_primary, priority, health_check_path, auth_method, connection_pool_size, request_timeout_ms) VALUES
|
|
('benzinga', 'news', 'https://api.benzinga.com', 'wss://api.benzinga.com/news/stream', 'v2', 'development', true, 1, '/v2/news', 'api_key', 5, 10000),
|
|
('benzinga', 'fundamentals', 'https://api.benzinga.com', NULL, 'v2', 'development', true, 1, '/v2/fundamentals', 'api_key', 10, 15000),
|
|
('benzinga', 'analytics', 'https://api.benzinga.com', NULL, 'v2', 'development', true, 1, '/v2/analytics', 'api_key', 5, 20000),
|
|
('benzinga', 'news', 'https://api.benzinga.com', 'wss://api.benzinga.com/news/stream', 'v2', 'production', true, 1, '/v2/news', 'api_key', 20, 10000),
|
|
('benzinga', 'fundamentals', 'https://api.benzinga.com', NULL, 'v2', 'production', true, 1, '/v2/fundamentals', 'api_key', 50, 15000),
|
|
('benzinga', 'analytics', 'https://api.benzinga.com', NULL, 'v2', 'production', true, 1, '/v2/analytics', 'api_key', 20, 20000);
|
|
|
|
-- === PROVIDER SUBSCRIPTION CONFIGURATIONS ===
|
|
|
|
-- Databento subscriptions
|
|
INSERT INTO provider_subscriptions (provider_name, subscription_type, dataset, symbols, environment, rate_limit_per_second, max_concurrent_connections, metadata) VALUES
|
|
('databento', 'equities_l1', 'XNAS.ITCH', ARRAY['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN'], 'development', 100, 2, '{"schema": "mbo", "stype_in": "raw_symbol"}'::jsonb),
|
|
('databento', 'equities_l2', 'XNAS.ITCH', ARRAY['AAPL', 'MSFT', 'GOOGL'], 'development', 50, 1, '{"schema": "mbp-1", "stype_in": "raw_symbol"}'::jsonb),
|
|
('databento', 'equities_l1', 'XNAS.ITCH', ARRAY['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN', 'NVDA', 'META'], 'production', 500, 5, '{"schema": "mbo", "stype_in": "raw_symbol"}'::jsonb),
|
|
('databento', 'equities_l2', 'XNAS.ITCH', ARRAY['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN'], 'production', 200, 3, '{"schema": "mbp-1", "stype_in": "raw_symbol"}'::jsonb);
|
|
|
|
-- Benzinga subscriptions
|
|
INSERT INTO provider_subscriptions (provider_name, subscription_type, dataset, symbols, environment, rate_limit_per_second, max_concurrent_connections, metadata) VALUES
|
|
('benzinga', 'news_feed', 'general', ARRAY['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN'], 'development', 10, 1, '{"channels": ["news", "analyst-ratings"]}'::jsonb),
|
|
('benzinga', 'earnings_calendar', 'earnings', NULL, 'development', 5, 1, '{"importance": "high"}'::jsonb),
|
|
('benzinga', 'analyst_ratings', 'ratings', ARRAY['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN'], 'development', 5, 1, '{"rating_type": ["Upgrade", "Downgrade", "Initiates", "Reiterates"]}'::jsonb),
|
|
('benzinga', 'news_feed', 'general', ARRAY['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN', 'NVDA', 'META'], 'production', 50, 3, '{"channels": ["news", "analyst-ratings", "sec-filings"]}'::jsonb),
|
|
('benzinga', 'earnings_calendar', 'earnings', NULL, 'production', 20, 1, '{"importance": "high"}'::jsonb),
|
|
('benzinga', 'analyst_ratings', 'ratings', ARRAY['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN', 'NVDA', 'META'], 'production', 20, 1, '{"rating_type": ["Upgrade", "Downgrade", "Initiates", "Reiterates"]}'::jsonb);
|
|
|
|
-- === NOTIFICATION TRIGGERS FOR PROVIDER TABLES ===
|
|
|
|
-- Provider configurations change trigger
|
|
CREATE OR REPLACE FUNCTION notify_provider_config_change()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
payload JSONB;
|
|
BEGIN
|
|
payload := jsonb_build_object(
|
|
'table', 'provider_configurations',
|
|
'operation', TG_OP,
|
|
'provider', COALESCE(NEW.provider_name, OLD.provider_name),
|
|
'config_key', COALESCE(NEW.config_key, OLD.config_key),
|
|
'environment', COALESCE(NEW.environment, OLD.environment),
|
|
'timestamp', EXTRACT(EPOCH FROM NOW())
|
|
);
|
|
|
|
PERFORM pg_notify('foxhunt_provider_changes', payload::text);
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER tr_provider_configurations_notify
|
|
AFTER INSERT OR UPDATE OR DELETE ON provider_configurations
|
|
FOR EACH ROW EXECUTE FUNCTION notify_provider_config_change();
|
|
|
|
-- Provider subscriptions change trigger
|
|
CREATE OR REPLACE FUNCTION notify_provider_subscription_change()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
payload JSONB;
|
|
BEGIN
|
|
payload := jsonb_build_object(
|
|
'table', 'provider_subscriptions',
|
|
'operation', TG_OP,
|
|
'provider', COALESCE(NEW.provider_name, OLD.provider_name),
|
|
'subscription_type', COALESCE(NEW.subscription_type, OLD.subscription_type),
|
|
'environment', COALESCE(NEW.environment, OLD.environment),
|
|
'timestamp', EXTRACT(EPOCH FROM NOW())
|
|
);
|
|
|
|
PERFORM pg_notify('foxhunt_provider_changes', payload::text);
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER tr_provider_subscriptions_notify
|
|
AFTER INSERT OR UPDATE OR DELETE ON provider_subscriptions
|
|
FOR EACH ROW EXECUTE FUNCTION notify_provider_subscription_change();
|
|
|
|
-- Provider endpoints change trigger
|
|
CREATE OR REPLACE FUNCTION notify_provider_endpoint_change()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
payload JSONB;
|
|
BEGIN
|
|
payload := jsonb_build_object(
|
|
'table', 'provider_endpoints',
|
|
'operation', TG_OP,
|
|
'provider', COALESCE(NEW.provider_name, OLD.provider_name),
|
|
'endpoint_type', COALESCE(NEW.endpoint_type, OLD.endpoint_type),
|
|
'environment', COALESCE(NEW.environment, OLD.environment),
|
|
'timestamp', EXTRACT(EPOCH FROM NOW())
|
|
);
|
|
|
|
PERFORM pg_notify('foxhunt_provider_changes', payload::text);
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER tr_provider_endpoints_notify
|
|
AFTER INSERT OR UPDATE OR DELETE ON provider_endpoints
|
|
FOR EACH ROW EXECUTE FUNCTION notify_provider_endpoint_change();
|
|
|
|
-- === CONFIGURATION SUBSCRIPTIONS FOR PROVIDERS ===
|
|
|
|
-- Trading service subscribes to provider configuration changes
|
|
INSERT INTO config_subscriptions (service_name, config_pattern, category_pattern, environment, subscription_type, is_active) VALUES
|
|
('trading_service', 'trading.providers.*', 'trading.providers.*', 'development', 'notify', true),
|
|
('trading_service', 'trading.providers.*', 'trading.providers.*', 'production', 'notify', true);
|
|
|
|
-- Market data service subscribes to provider changes
|
|
INSERT INTO config_subscriptions (service_name, config_pattern, category_pattern, environment, subscription_type, is_active) VALUES
|
|
('market_data_service', 'trading.providers.*', 'trading.providers.*', 'development', 'notify', true),
|
|
('market_data_service', 'trading.providers.*', 'trading.providers.*', 'production', 'notify', true);
|
|
|
|
-- === UTILITY FUNCTIONS FOR PROVIDER MANAGEMENT ===
|
|
|
|
-- Function to get active providers for an environment
|
|
CREATE OR REPLACE FUNCTION get_active_providers(p_environment VARCHAR(50) DEFAULT 'development')
|
|
RETURNS TABLE(provider_name VARCHAR(50), config_count BIGINT) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
pc.provider_name,
|
|
COUNT(*) as config_count
|
|
FROM provider_configurations pc
|
|
WHERE pc.environment = p_environment
|
|
AND pc.is_active = true
|
|
GROUP BY pc.provider_name
|
|
ORDER BY pc.provider_name;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Function to get provider configuration with fallback
|
|
CREATE OR REPLACE FUNCTION get_provider_config(
|
|
p_provider_name VARCHAR(50),
|
|
p_config_key VARCHAR(200),
|
|
p_environment VARCHAR(50) DEFAULT 'development'
|
|
)
|
|
RETURNS JSONB AS $$
|
|
DECLARE
|
|
result JSONB;
|
|
BEGIN
|
|
SELECT config_value INTO result
|
|
FROM provider_configurations
|
|
WHERE provider_name = p_provider_name
|
|
AND config_key = p_config_key
|
|
AND environment = p_environment
|
|
AND is_active = true;
|
|
|
|
RETURN result;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Function to update provider configuration
|
|
CREATE OR REPLACE FUNCTION set_provider_config(
|
|
p_provider_name VARCHAR(50),
|
|
p_config_key VARCHAR(200),
|
|
p_config_value JSONB,
|
|
p_environment VARCHAR(50) DEFAULT 'development',
|
|
p_description TEXT DEFAULT NULL
|
|
)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
INSERT INTO provider_configurations (
|
|
provider_name, config_key, config_value, environment, description, updated_at
|
|
) VALUES (
|
|
p_provider_name, p_config_key, p_config_value, p_environment, p_description, NOW()
|
|
)
|
|
ON CONFLICT (provider_name, config_key, environment)
|
|
DO UPDATE SET
|
|
config_value = EXCLUDED.config_value,
|
|
description = EXCLUDED.description,
|
|
updated_at = NOW();
|
|
|
|
RETURN true;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- === UPDATE STATISTICS ===
|
|
ANALYZE provider_configurations;
|
|
ANALYZE provider_subscriptions;
|
|
ANALYZE provider_endpoints;
|
|
ANALYZE config_categories;
|
|
ANALYZE config_settings;
|
|
|
|
-- === COMMENTS FOR DOCUMENTATION ===
|
|
|
|
COMMENT ON TABLE provider_configurations IS 'Provider-specific configuration settings with environment support';
|
|
COMMENT ON TABLE provider_subscriptions IS 'Provider subscription and feature configurations';
|
|
COMMENT ON TABLE provider_endpoints IS 'Provider API endpoint configurations with failover support';
|
|
|
|
COMMENT ON FUNCTION notify_provider_config_change() IS 'Notification trigger for provider configuration changes';
|
|
COMMENT ON FUNCTION get_active_providers(VARCHAR) IS 'Returns list of active providers for an environment';
|
|
COMMENT ON FUNCTION get_provider_config(VARCHAR, VARCHAR, VARCHAR) IS 'Retrieves provider configuration value';
|
|
COMMENT ON FUNCTION set_provider_config(VARCHAR, VARCHAR, JSONB, VARCHAR, TEXT) IS 'Updates provider configuration value';
|
|
|
|
-- Dual-provider configuration migration completed successfully!
|
|
--
|
|
-- Created:
|
|
-- - Provider-specific configuration tables for Databento and Benzinga
|
|
-- - Hot-reload notification system for provider changes
|
|
-- - Comprehensive configuration entries for both providers
|
|
-- - Endpoint and subscription management
|
|
-- - Utility functions for provider configuration management
|
|
--
|
|
-- Key Features:
|
|
-- - Environment-specific provider configurations
|
|
-- - Secure API key storage with sensitivity flags
|
|
-- - Rate limiting and connection pooling settings
|
|
-- - Subscription management with symbol filtering
|
|
-- - Endpoint failover and priority configuration
|
|
-- - Real-time hot-reload notifications via PostgreSQL NOTIFY/LISTEN
|
|
--
|
|
-- Usage:
|
|
-- 1. Services listen to 'foxhunt_provider_changes' channel for provider updates
|
|
-- 2. Use get_provider_config('databento', 'api_key', 'production') for configuration retrieval
|
|
-- 3. Use set_provider_config() for runtime configuration updates
|
|
-- 4. TLI dashboard can manage provider configurations through PostgreSQL
|
|
-- 5. Configuration changes trigger immediate notifications to subscribed services |