Repository: modelcontextprotocol/registry
File: internal/database/migrations/008_clean_invalid_data.sql (SQL)

Code before:
    END IF;
END $$;

-- Delete servers with invalid names or empty versions
-- These cannot be reasonably fixed and would violate primary key constraints
DELETE FROM servers
WHERE value->>'name' NOT SIMILAR TO '[a-zA-Z0-9][a-zA-Z0-9.-]*[a-zA-Z0-9]/[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]'
   OR value->>'version' IS NULL
   OR value->>'version' = '';

-- Fix invalid status values by setting them to 'active'
-- These can be reasonably defaulted to a valid value
UPDATE servers
SET value = jsonb_set(value, '{status}', '"active"')
WHERE value->>'status' IS NOT NULL
  AND value->>'status' != ''
  AND value->>'status' NOT IN ('active', 'deprecated', 'deleted');

-- Remove duplicate name+version combinations
-- Keep the one with the highest version_id (most recently added)
DELETE FROM servers s1
WHERE EXISTS (
  SELECT 1 FROM servers s2
  WHERE s2.value->>'name' = s1.value->>'name'
    AND s2.value->>'version' = s1.value->>'version'
    AND s2.version_id > s1.version_id
);

-- Verify the operations completed as expected
DO $$
DECLARE
    remaining_count INTEGER;
    actual_deleted INTEGER;
    actual_updated INTEGER;
    still_invalid_names INTEGER;
    still_empty_versions INTEGER;
    still_invalid_status INTEGER;
BEGIN
    SELECT COUNT(*) INTO remaining_count FROM servers;

    -- Check if any invalid data remains
    SELECT COUNT(*) INTO still_invalid_names
    FROM servers
    WHERE value->>'name' NOT SIMILAR TO '[a-zA-Z0-9][a-zA-Z0-9.-]*[a-zA-Z0-9]/[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]';

    SELECT COUNT(*) INTO still_empty_versions
    FROM servers
    WHERE value->>'version' IS NULL OR value->>'version' = '';

    SELECT COUNT(*) INTO still_invalid_status
    FROM servers

Review comment (suggestion):
Assuming this is a uuid I think this one might not work very well, but it's not worth it to change it, we are talking about conflicting server versions here so not a good way to tell which should stay

Code after:
            ORDER BY value->>'name'
        LOOP
            RAISE NOTICE '  - % @ % (current status: %)', r.name, r.version, r.status;
        END LOOP;
    END IF;

    -- SAFETY CHECK: Ensure we're deleting exactly the expected data
    -- We only reach this point if we found the known bad server
    -- In production (2025-09-30), we expect exactly 5 deletions and 1 status update
    IF total_to_delete != 5 THEN
        RAISE EXCEPTION 'Safety check failed: Expected to delete exactly 5 servers but would delete %. Check the log above for details. Aborting to prevent data loss.', total_to_delete;
    END IF;

    IF invalid_status_count != 1 THEN
        RAISE EXCEPTION 'Safety check failed: Expected to update exactly 1 server status but would update %. Check the log above for details. Aborting to prevent data corruption.', invalid_status_count;
    END IF;
END $$;

-- Delete servers with invalid names or empty versions
-- These cannot be reasonably fixed and would violate primary key constraints
DELETE FROM servers
WHERE value->>'name' NOT SIMILAR TO '[a-zA-Z0-9][a-zA-Z0-9.-]*[a-zA-Z0-9]/[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]'
   OR value->>'version' IS NULL
   OR value->>'version' = '';

-- Fix invalid status values by setting them to 'active'
-- These can be reasonably defaulted to a valid value
UPDATE servers
SET value = jsonb_set(value, '{status}', '"active"')
WHERE value->>'status' IS NOT NULL
  AND value->>'status' != ''
  AND value->>'status' NOT IN ('active', 'deprecated', 'deleted');

-- Remove duplicate name+version combinations
-- Keep the one with the most recent publishedAt date
DELETE FROM servers s1
WHERE EXISTS (
  SELECT 1 FROM servers s2
  WHERE s2.value->>'name' = s1.value->>'name'
    AND s2.value->>'version' = s1.value->>'version'
    AND (s2.value->>'publishedAt')::timestamp > (s1.value->>'publishedAt')::timestamp
);

-- Verify the operations completed as expected
DO $$
DECLARE
    remaining_count INTEGER;
    actual_deleted INTEGER;
    actual_updated INTEGER;
    still_invalid_names INTEGER;
    still_empty_versions INTEGER;