Healthcare Data Pipeline Resilience Weakness

Implement Priority-Based Processing:

CREATE TYPE alert_priority AS ENUM ('critical', 'high', 'medium', 'low');

CREATE TABLE healthcare_alerts (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    patient_id UUID NOT NULL,
    alert_type VARCHAR(100) NOT NULL,
    priority alert_priority NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    processed_at TIMESTAMP WITH TIME ZONE,
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    INDEX idx_priority_created (priority, created_at) WHERE processed_at IS NULL
);

-- Process critical alerts first
CREATE OR REPLACE FUNCTION get_next_alert()
RETURNS healthcare_alerts AS $
DECLARE
    v_alert healthcare_alerts;
BEGIN
    SELECT * INTO v_alert
    FROM healthcare_alerts
    WHERE processed_at IS NULL
    AND retry_count < max_retries
    ORDER BY 
        CASE priority 
            WHEN 'critical' THEN 1
            WHEN 'high' THEN 2
            WHEN 'medium' THEN 3
            WHEN 'low' THEN 4
        END,
        created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED;
    
    RETURN v_alert;
END;
$ LANGUAGE plpgsql;

Circuit Breaker Implementation:

CREATE TABLE circuit_breakers (
    id SERIAL PRIMARY KEY,
    service_name VARCHAR(100) UNIQUE NOT NULL,
    state VARCHAR(20) CHECK (state IN ('closed', 'open', 'half_open')),
    failure_count INTEGER DEFAULT 0,
    success_count INTEGER DEFAULT 0,
    last_failure_time TIMESTAMP WITH TIME ZONE,
    last_success_time TIMESTAMP WITH TIME ZONE,
    next_retry_time TIMESTAMP WITH TIME ZONE,
    failure_threshold INTEGER DEFAULT 5,
    success_threshold INTEGER DEFAULT 3,
    timeout_duration INTERVAL DEFAULT '30 seconds'
);

CREATE OR REPLACE FUNCTION check_circuit_breaker(
    p_service_name VARCHAR
) RETURNS BOOLEAN AS $
DECLARE
    v_breaker circuit_breakers;
BEGIN
    SELECT * INTO v_breaker
    FROM circuit_breakers
    WHERE service_name = p_service_name;
    
    IF v_breaker.state = 'open' THEN
        IF NOW() >= v_breaker.next_retry_time THEN
            UPDATE circuit_breakers
            SET state = 'half_open',
                success_count = 0,
                failure_count = 0
            WHERE service_name = p_service_name;
            RETURN TRUE; -- Allow one attempt
        ELSE
            RETURN FALSE; -- Still in timeout
        END IF;
    END IF;
    
    RETURN TRUE; -- Closed or half-open
END;
$ LANGUAGE plpgsql;

Backpressure Management:

CREATE TABLE queue_metrics (
    id BIGSERIAL PRIMARY KEY,
    queue_name VARCHAR(100) NOT NULL,
    metric_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    queue_depth INTEGER NOT NULL,
    processing_rate DECIMAL(10,2),
    arrival_rate DECIMAL(10,2),
    avg_processing_time_ms INTEGER,
    INDEX idx_queue_time (queue_name, metric_time DESC)
);

-- Detect backlog buildup
CREATE OR REPLACE FUNCTION is_backpressure_needed(
    p_queue_name VARCHAR
) RETURNS BOOLEAN AS $
DECLARE
    v_growth_rate DECIMAL;
BEGIN
    SELECT 
        AVG(arrival_rate - processing_rate)
    INTO v_growth_rate
    FROM queue_metrics
    WHERE queue_name = p_queue_name
    AND metric_time > NOW() - INTERVAL '5 minutes';
    
    -- If queue is growing, apply backpressure
    RETURN v_growth_rate > 0;
END;
$ LANGUAGE plpgsql;

Graceful Degradation:

CREATE TABLE service_degradation_rules (
    id SERIAL PRIMARY KEY,
    load_threshold DECIMAL(5,2),
    action VARCHAR(100),
    description TEXT
);

INSERT INTO service_degradation_rules (load_threshold, action, description) VALUES
(70.0, 'disable_analytics', 'Turn off non-critical analytics processing'),
(80.0, 'reduce_video_quality', 'Lower telemedicine video quality'),
(90.0, 'critical_only', 'Process only critical alerts'),
(95.0, 'emergency_mode', 'Emergency alerts only, notify on-call');

-- Health monitoring view
CREATE VIEW system_health_dashboard AS
SELECT 
    q.queue_name,
    q.queue_depth,
    q.processing_rate,
    cb.state as circuit_breaker_state,
    CASE 
        WHEN q.queue_depth > 10000 THEN 'CRITICAL'
        WHEN q.queue_depth > 5000 THEN 'WARNING'
        ELSE 'HEALTHY'
    END as health_status
FROM (
    SELECT DISTINCT ON (queue_name) *
    FROM queue_metrics
    ORDER BY queue_name, metric_time DESC
) q
LEFT JOIN circuit_breakers cb ON q.queue_name = cb.service_name;

Additional Best Practices:

• Implement dead letter queues for failed messages
• Use message TTLs to prevent stale data processing
• Regular chaos engineering tests
• Separate queues for different priority levels
• Implement bulkheading to isolate failures