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
507 lines
20 KiB
PL/PgSQL
507 lines
20 KiB
PL/PgSQL
-- PostgreSQL Configuration Management Schema for Foxhunt HFT System
|
|
-- ==================================================================
|
|
-- This migration creates a comprehensive configuration management system
|
|
-- with PostgreSQL NOTIFY/LISTEN support for hot-reload capabilities.
|
|
|
|
-- === EXTENSIONS AND FUNCTIONS ===
|
|
|
|
-- Enable UUID extension if not already enabled
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Enable BTREE_GIN for composite indexes
|
|
CREATE EXTENSION IF NOT EXISTS btree_gin;
|
|
|
|
-- Create configuration change notification function
|
|
CREATE OR REPLACE FUNCTION notify_config_change()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
payload JSONB;
|
|
BEGIN
|
|
-- Build notification payload
|
|
payload := jsonb_build_object(
|
|
'table', TG_TABLE_NAME,
|
|
'operation', TG_OP,
|
|
'timestamp', EXTRACT(EPOCH FROM NOW()),
|
|
'config_key', COALESCE(NEW.config_key, OLD.config_key),
|
|
'category_path', COALESCE(NEW.category_path, OLD.category_path),
|
|
'environment', COALESCE(NEW.environment, OLD.environment)
|
|
);
|
|
|
|
-- Add old/new values for updates
|
|
IF TG_OP = 'UPDATE' THEN
|
|
payload := payload || jsonb_build_object(
|
|
'old_value', OLD.config_value,
|
|
'new_value', NEW.config_value,
|
|
'changed_by', NEW.updated_by
|
|
);
|
|
ELSIF TG_OP = 'INSERT' THEN
|
|
payload := payload || jsonb_build_object(
|
|
'new_value', NEW.config_value,
|
|
'created_by', NEW.created_by
|
|
);
|
|
ELSIF TG_OP = 'DELETE' THEN
|
|
payload := payload || jsonb_build_object(
|
|
'old_value', OLD.config_value,
|
|
'deleted_by', CURRENT_USER
|
|
);
|
|
END IF;
|
|
|
|
-- Send notification on foxhunt_config_changes channel
|
|
PERFORM pg_notify('foxhunt_config_changes', payload::text);
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- === CORE CONFIGURATION TABLES ===
|
|
|
|
-- Configuration Categories (hierarchical organization)
|
|
CREATE TABLE config_categories (
|
|
id SERIAL PRIMARY KEY,
|
|
category_name VARCHAR(100) NOT NULL,
|
|
parent_id INTEGER REFERENCES config_categories(id) ON DELETE CASCADE,
|
|
category_path TEXT NOT NULL, -- Computed path like 'trading.risk.limits'
|
|
description TEXT,
|
|
is_system BOOLEAN NOT NULL DEFAULT false, -- System vs user-defined categories
|
|
display_order INTEGER DEFAULT 0,
|
|
metadata JSONB DEFAULT '{}'::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
|
|
);
|
|
|
|
-- Configuration Settings (main configuration storage)
|
|
CREATE TABLE config_settings (
|
|
id SERIAL PRIMARY KEY,
|
|
config_key VARCHAR(200) NOT NULL,
|
|
category_id INTEGER NOT NULL REFERENCES config_categories(id) ON DELETE CASCADE,
|
|
category_path TEXT NOT NULL, -- Denormalized for performance
|
|
config_value JSONB NOT NULL, -- Flexible value storage
|
|
value_type VARCHAR(50) NOT NULL DEFAULT 'string', -- string, number, boolean, object, array
|
|
environment VARCHAR(50) NOT NULL DEFAULT 'development', -- development, staging, production
|
|
is_sensitive BOOLEAN NOT NULL DEFAULT false, -- Encrypted/protected values
|
|
is_system BOOLEAN NOT NULL DEFAULT false, -- System vs user-defined settings
|
|
is_readonly BOOLEAN NOT NULL DEFAULT false, -- Immutable settings
|
|
validation_schema JSONB, -- JSON Schema for value validation
|
|
default_value JSONB, -- Default value if not set
|
|
description TEXT,
|
|
tags TEXT[] DEFAULT '{}', -- Searchable tags
|
|
depends_on TEXT[], -- Dependencies on other config keys
|
|
affects TEXT[], -- What this setting affects (services, components)
|
|
hot_reload BOOLEAN NOT NULL DEFAULT true, -- Can be changed without restart
|
|
restart_required BOOLEAN NOT NULL DEFAULT false, -- Requires service restart
|
|
is_active BOOLEAN NOT NULL DEFAULT true, -- Soft delete / deactivation
|
|
version INTEGER NOT NULL DEFAULT 1, -- Version for optimistic locking
|
|
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_config_settings_key_env UNIQUE (config_key, environment),
|
|
CONSTRAINT chk_value_type CHECK (value_type IN ('string', 'number', 'boolean', 'object', 'array', 'null')),
|
|
CONSTRAINT chk_environment CHECK (environment IN ('development', 'staging', 'production', 'test')),
|
|
CONSTRAINT chk_not_both_readonly_hotreload CHECK (NOT (is_readonly AND hot_reload))
|
|
);
|
|
|
|
-- Configuration History (audit trail)
|
|
CREATE TABLE config_history (
|
|
id SERIAL PRIMARY KEY,
|
|
config_setting_id INTEGER NOT NULL REFERENCES config_settings(id) ON DELETE CASCADE,
|
|
config_key VARCHAR(200) NOT NULL,
|
|
category_path TEXT NOT NULL,
|
|
environment VARCHAR(50) NOT NULL,
|
|
old_value JSONB,
|
|
new_value JSONB NOT NULL,
|
|
change_type VARCHAR(20) NOT NULL, -- insert, update, delete
|
|
changed_by VARCHAR(100) NOT NULL,
|
|
change_reason TEXT,
|
|
change_request_id VARCHAR(100), -- External change tracking
|
|
rollback_to_id INTEGER REFERENCES config_history(id), -- For rollbacks
|
|
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_change_type CHECK (change_type IN ('insert', 'update', 'delete', 'rollback'))
|
|
);
|
|
|
|
-- Configuration Environments (environment management)
|
|
CREATE TABLE config_environments (
|
|
id SERIAL PRIMARY KEY,
|
|
environment_name VARCHAR(50) NOT NULL UNIQUE,
|
|
display_name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
is_production BOOLEAN NOT NULL DEFAULT false,
|
|
inherits_from VARCHAR(50) REFERENCES config_environments(environment_name), -- Environment inheritance
|
|
isolation_level VARCHAR(20) NOT NULL DEFAULT 'strict', -- strict, permissive
|
|
auto_sync BOOLEAN NOT NULL DEFAULT false, -- Auto-sync from parent environment
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_isolation_level CHECK (isolation_level IN ('strict', 'permissive')),
|
|
CONSTRAINT chk_no_self_inherit CHECK (environment_name != inherits_from)
|
|
);
|
|
|
|
-- Configuration Environment Overrides (environment-specific values)
|
|
CREATE TABLE config_environment_overrides (
|
|
id SERIAL PRIMARY KEY,
|
|
config_setting_id INTEGER NOT NULL REFERENCES config_settings(id) ON DELETE CASCADE,
|
|
source_environment VARCHAR(50) NOT NULL REFERENCES config_environments(environment_name),
|
|
target_environment VARCHAR(50) NOT NULL REFERENCES config_environments(environment_name),
|
|
config_key VARCHAR(200) NOT NULL,
|
|
override_value JSONB NOT NULL,
|
|
override_reason TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
expires_at TIMESTAMPTZ, -- Temporary overrides
|
|
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_config_overrides_setting_target UNIQUE (config_setting_id, target_environment),
|
|
CONSTRAINT chk_different_environments CHECK (source_environment != target_environment)
|
|
);
|
|
|
|
-- Configuration Subscriptions (services listening for changes)
|
|
CREATE TABLE config_subscriptions (
|
|
id SERIAL PRIMARY KEY,
|
|
service_name VARCHAR(100) NOT NULL,
|
|
service_instance_id VARCHAR(100), -- For multiple instances
|
|
config_pattern TEXT NOT NULL, -- Glob pattern for config keys
|
|
category_pattern TEXT, -- Glob pattern for categories
|
|
environment VARCHAR(50) NOT NULL,
|
|
subscription_type VARCHAR(20) NOT NULL DEFAULT 'notify', -- notify, poll, webhook
|
|
endpoint_url TEXT, -- For webhook subscriptions
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
last_notification_at TIMESTAMPTZ,
|
|
notification_count INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_subscription_type CHECK (subscription_type IN ('notify', 'poll', 'webhook')),
|
|
CONSTRAINT chk_webhook_endpoint CHECK (
|
|
(subscription_type = 'webhook' AND endpoint_url IS NOT NULL) OR
|
|
(subscription_type != 'webhook')
|
|
)
|
|
);
|
|
|
|
-- Configuration Locks (prevent concurrent modifications)
|
|
CREATE TABLE config_locks (
|
|
id SERIAL PRIMARY KEY,
|
|
config_key VARCHAR(200) NOT NULL,
|
|
environment VARCHAR(50) NOT NULL,
|
|
locked_by VARCHAR(100) NOT NULL,
|
|
lock_reason TEXT,
|
|
locked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '1 hour'),
|
|
|
|
-- Constraints
|
|
CONSTRAINT uk_config_locks_key_env UNIQUE (config_key, environment)
|
|
);
|
|
|
|
-- === INDEXES FOR PERFORMANCE ===
|
|
|
|
-- Config Categories Indexes
|
|
CREATE INDEX idx_config_categories_parent ON config_categories(parent_id) WHERE parent_id IS NOT NULL;
|
|
CREATE INDEX idx_config_categories_path ON config_categories(category_path);
|
|
CREATE UNIQUE INDEX idx_config_categories_path_unique ON config_categories(category_path);
|
|
|
|
-- Config Settings Indexes (optimized for HFT lookups)
|
|
CREATE INDEX idx_config_settings_category ON config_settings(category_id);
|
|
CREATE INDEX idx_config_settings_category_path ON config_settings(category_path);
|
|
CREATE INDEX idx_config_settings_environment ON config_settings(environment);
|
|
CREATE INDEX idx_config_settings_hot_reload ON config_settings(hot_reload) WHERE hot_reload = true;
|
|
CREATE INDEX idx_config_settings_system ON config_settings(is_system);
|
|
CREATE INDEX idx_config_settings_tags ON config_settings USING GIN(tags);
|
|
CREATE INDEX idx_config_settings_depends_on ON config_settings USING GIN(depends_on);
|
|
CREATE INDEX idx_config_settings_affects ON config_settings USING GIN(affects);
|
|
CREATE INDEX idx_config_settings_updated_at ON config_settings(updated_at DESC);
|
|
|
|
-- GIN index for JSONB config values (for complex queries)
|
|
CREATE INDEX idx_config_settings_value_gin ON config_settings USING GIN(config_value);
|
|
|
|
-- Composite index for fast config lookups (most common query pattern)
|
|
CREATE INDEX idx_config_settings_key_env_lookup ON config_settings(config_key, environment, is_active)
|
|
WHERE is_active = true;
|
|
|
|
-- Config History Indexes
|
|
CREATE INDEX idx_config_history_setting ON config_history(config_setting_id);
|
|
CREATE INDEX idx_config_history_key_env ON config_history(config_key, environment);
|
|
CREATE INDEX idx_config_history_applied_at ON config_history(applied_at DESC);
|
|
CREATE INDEX idx_config_history_changed_by ON config_history(changed_by);
|
|
|
|
-- Config Environment Overrides Indexes
|
|
CREATE INDEX idx_config_overrides_target_env ON config_environment_overrides(target_environment);
|
|
CREATE INDEX idx_config_overrides_active ON config_environment_overrides(is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_config_overrides_expires ON config_environment_overrides(expires_at) WHERE expires_at IS NOT NULL;
|
|
|
|
-- Config Subscriptions Indexes
|
|
CREATE INDEX idx_config_subscriptions_service ON config_subscriptions(service_name);
|
|
CREATE INDEX idx_config_subscriptions_pattern ON config_subscriptions(config_pattern);
|
|
CREATE INDEX idx_config_subscriptions_active ON config_subscriptions(is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_config_subscriptions_environment ON config_subscriptions(environment);
|
|
|
|
-- Config Locks Indexes
|
|
CREATE INDEX idx_config_locks_expires ON config_locks(expires_at);
|
|
CREATE INDEX idx_config_locks_locked_by ON config_locks(locked_by);
|
|
|
|
-- === TRIGGERS FOR NOTIFICATIONS ===
|
|
|
|
-- Trigger for config_settings changes
|
|
CREATE TRIGGER tr_config_settings_notify
|
|
AFTER INSERT OR UPDATE OR DELETE ON config_settings
|
|
FOR EACH ROW EXECUTE FUNCTION notify_config_change();
|
|
|
|
-- Trigger for config_environment_overrides changes
|
|
CREATE TRIGGER tr_config_overrides_notify
|
|
AFTER INSERT OR UPDATE OR DELETE ON config_environment_overrides
|
|
FOR EACH ROW EXECUTE FUNCTION notify_config_change();
|
|
|
|
-- === UTILITY FUNCTIONS ===
|
|
|
|
-- Function to get configuration value with environment inheritance
|
|
CREATE OR REPLACE FUNCTION get_config_value(
|
|
p_config_key VARCHAR(200),
|
|
p_environment VARCHAR(50) DEFAULT 'development'
|
|
)
|
|
RETURNS JSONB AS $$
|
|
DECLARE
|
|
result JSONB;
|
|
parent_env VARCHAR(50);
|
|
BEGIN
|
|
-- First try to get override value
|
|
SELECT override_value INTO result
|
|
FROM config_environment_overrides ceo
|
|
JOIN config_settings cs ON ceo.config_setting_id = cs.id
|
|
WHERE cs.config_key = p_config_key
|
|
AND ceo.target_environment = p_environment
|
|
AND ceo.is_active = true
|
|
AND (ceo.expires_at IS NULL OR ceo.expires_at > NOW());
|
|
|
|
-- If no override, get the regular value
|
|
IF result IS NULL THEN
|
|
SELECT config_value INTO result
|
|
FROM config_settings
|
|
WHERE config_key = p_config_key
|
|
AND environment = p_environment
|
|
AND is_active = true;
|
|
END IF;
|
|
|
|
-- If still no value and environment has parent, try parent
|
|
IF result IS NULL THEN
|
|
SELECT inherits_from INTO parent_env
|
|
FROM config_environments
|
|
WHERE environment_name = p_environment;
|
|
|
|
IF parent_env IS NOT NULL THEN
|
|
RETURN get_config_value(p_config_key, parent_env);
|
|
END IF;
|
|
END IF;
|
|
|
|
-- If still no value, try default
|
|
IF result IS NULL THEN
|
|
SELECT default_value INTO result
|
|
FROM config_settings
|
|
WHERE config_key = p_config_key
|
|
AND environment = p_environment;
|
|
END IF;
|
|
|
|
RETURN result;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Function to set configuration value with history tracking
|
|
CREATE OR REPLACE FUNCTION set_config_value(
|
|
p_config_key VARCHAR(200),
|
|
p_new_value JSONB,
|
|
p_environment VARCHAR(50) DEFAULT 'development',
|
|
p_changed_by VARCHAR(100) DEFAULT CURRENT_USER,
|
|
p_change_reason TEXT DEFAULT NULL
|
|
)
|
|
RETURNS BOOLEAN AS $$
|
|
DECLARE
|
|
setting_id INTEGER;
|
|
old_value JSONB;
|
|
setting_version INTEGER;
|
|
BEGIN
|
|
-- Get current setting
|
|
SELECT id, config_value, version INTO setting_id, old_value, setting_version
|
|
FROM config_settings
|
|
WHERE config_key = p_config_key
|
|
AND environment = p_environment
|
|
AND is_active = true;
|
|
|
|
-- If setting doesn't exist, return false
|
|
IF setting_id IS NULL THEN
|
|
RETURN false;
|
|
END IF;
|
|
|
|
-- Check if it's read-only
|
|
IF EXISTS (SELECT 1 FROM config_settings WHERE id = setting_id AND is_readonly = true) THEN
|
|
RAISE EXCEPTION 'Configuration setting "%" is read-only', p_config_key;
|
|
END IF;
|
|
|
|
-- Update the setting
|
|
UPDATE config_settings
|
|
SET config_value = p_new_value,
|
|
updated_at = NOW(),
|
|
updated_by = p_changed_by,
|
|
version = version + 1
|
|
WHERE id = setting_id;
|
|
|
|
-- Record in history
|
|
INSERT INTO config_history (
|
|
config_setting_id, config_key, category_path, environment,
|
|
old_value, new_value, change_type, changed_by, change_reason
|
|
)
|
|
SELECT
|
|
setting_id, p_config_key, category_path, p_environment,
|
|
old_value, p_new_value, 'update', p_changed_by, p_change_reason
|
|
FROM config_settings
|
|
WHERE id = setting_id;
|
|
|
|
RETURN true;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Function to build category path from hierarchy
|
|
CREATE OR REPLACE FUNCTION build_category_path(category_id INTEGER)
|
|
RETURNS TEXT AS $$
|
|
DECLARE
|
|
path TEXT := '';
|
|
current_id INTEGER := category_id;
|
|
current_name VARCHAR(100);
|
|
parent_id INTEGER;
|
|
BEGIN
|
|
LOOP
|
|
SELECT category_name, parent_id INTO current_name, parent_id
|
|
FROM config_categories
|
|
WHERE id = current_id;
|
|
|
|
EXIT WHEN current_name IS NULL;
|
|
|
|
IF path = '' THEN
|
|
path := current_name;
|
|
ELSE
|
|
path := current_name || '.' || path;
|
|
END IF;
|
|
|
|
EXIT WHEN parent_id IS NULL;
|
|
current_id := parent_id;
|
|
END LOOP;
|
|
|
|
RETURN path;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger to automatically update category_path in config_settings
|
|
CREATE OR REPLACE FUNCTION update_config_category_path()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Update category_path in config_settings when category changes
|
|
UPDATE config_settings
|
|
SET category_path = build_category_path(category_id),
|
|
updated_at = NOW()
|
|
WHERE category_id = NEW.id;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER tr_config_categories_update_path
|
|
AFTER UPDATE ON config_categories
|
|
FOR EACH ROW EXECUTE FUNCTION update_config_category_path();
|
|
|
|
-- Function to clean up expired locks
|
|
CREATE OR REPLACE FUNCTION cleanup_expired_config_locks()
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
deleted_count INTEGER;
|
|
BEGIN
|
|
DELETE FROM config_locks WHERE expires_at < NOW();
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
|
RETURN deleted_count;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- === ROW LEVEL SECURITY ===
|
|
|
|
-- Enable RLS on sensitive configuration
|
|
ALTER TABLE config_settings ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Policy: Only allow access to non-sensitive configs for regular users
|
|
CREATE POLICY config_settings_non_sensitive_policy ON config_settings
|
|
FOR SELECT USING (NOT is_sensitive OR current_user = 'foxhunt_admin');
|
|
|
|
-- Policy: Only admins can modify system configs
|
|
CREATE POLICY config_settings_system_policy ON config_settings
|
|
FOR ALL USING (NOT is_system OR current_user = 'foxhunt_admin');
|
|
|
|
-- === PERFORMANCE OPTIMIZATIONS ===
|
|
|
|
-- Update table statistics for query planning
|
|
ANALYZE config_categories;
|
|
ANALYZE config_settings;
|
|
ANALYZE config_history;
|
|
ANALYZE config_environments;
|
|
ANALYZE config_environment_overrides;
|
|
ANALYZE config_subscriptions;
|
|
ANALYZE config_locks;
|
|
|
|
-- Create performance monitoring view
|
|
CREATE OR REPLACE VIEW config_performance_stats AS
|
|
SELECT
|
|
schemaname,
|
|
relname as tablename,
|
|
n_tup_ins as inserts,
|
|
n_tup_upd as updates,
|
|
n_tup_del as deletes,
|
|
n_live_tup as live_rows,
|
|
n_dead_tup as dead_rows,
|
|
last_vacuum,
|
|
last_autovacuum,
|
|
last_analyze,
|
|
last_autoanalyze
|
|
FROM pg_stat_user_tables
|
|
WHERE relname LIKE 'config_%'
|
|
ORDER BY relname;
|
|
|
|
-- === COMMENTS FOR DOCUMENTATION ===
|
|
|
|
COMMENT ON TABLE config_categories IS 'Hierarchical organization of configuration settings with path-based lookup';
|
|
COMMENT ON TABLE config_settings IS 'Main configuration storage with JSONB values, environment support, and hot-reload capabilities';
|
|
COMMENT ON TABLE config_history IS 'Complete audit trail of all configuration changes with rollback support';
|
|
COMMENT ON TABLE config_environments IS 'Environment definitions with inheritance and isolation controls';
|
|
COMMENT ON TABLE config_environment_overrides IS 'Environment-specific configuration overrides with expiration support';
|
|
COMMENT ON TABLE config_subscriptions IS 'Service subscription management for configuration change notifications';
|
|
COMMENT ON TABLE config_locks IS 'Distributed locking mechanism to prevent concurrent configuration modifications';
|
|
|
|
COMMENT ON FUNCTION notify_config_change() IS 'Trigger function that sends PostgreSQL NOTIFY messages for configuration changes';
|
|
COMMENT ON FUNCTION get_config_value(VARCHAR, VARCHAR) IS 'Retrieves configuration value with environment inheritance and override support';
|
|
COMMENT ON FUNCTION set_config_value(VARCHAR, JSONB, VARCHAR, VARCHAR, TEXT) IS 'Updates configuration value with automatic history tracking and validation';
|
|
COMMENT ON FUNCTION build_category_path(INTEGER) IS 'Builds dot-separated category path from hierarchical structure';
|
|
COMMENT ON FUNCTION cleanup_expired_config_locks() IS 'Removes expired configuration locks (should be called periodically)';
|
|
|
|
-- Configuration schema created successfully!
|
|
-- Features:
|
|
-- - Hierarchical configuration categories with path-based organization
|
|
-- - JSONB storage for flexible configuration values with type validation
|
|
-- - Environment-specific configurations with inheritance
|
|
-- - Hot-reload support with PostgreSQL NOTIFY/LISTEN
|
|
-- - Complete audit trail with rollback capabilities
|
|
-- - Row-level security for sensitive configurations
|
|
-- - Performance-optimized indexes for HFT workloads
|
|
-- - Distributed locking for concurrent access control
|
|
-- - Subscription management for service notifications
|
|
-- - Utility functions for configuration management
|
|
--
|
|
-- Usage:
|
|
-- 1. Listen to 'foxhunt_config_changes' channel for real-time updates
|
|
-- 2. Use get_config_value('key', 'environment') for configuration retrieval
|
|
-- 3. Use set_config_value('key', value, 'environment') for updates
|
|
-- 4. Monitor config_performance_stats view for performance metrics |