fix(db): Remove redundant migration and fix pgvector schema handling (#11822)

### Changes 🏗️

This PR includes two database migration fixes:

#### 1. Remove redundant Supabase extensions migration

Removes the `20260112173500_add_supabase_extensions_to_platform_schema`
migration which was attempting to manage Supabase-provided extensions
and schemas.

**What was removed:**
- Migration that created extensions (pgcrypto, uuid-ossp,
pg_stat_statements, pg_net, pgjwt, pg_graphql, pgsodium, supabase_vault)
- Schema creation for these extensions

**Why it was removed:**
- These extensions and schemas are pre-installed and managed by Supabase
automatically
- The migration was redundant and could cause schema drift warnings
- Attempting to manage Supabase-owned resources in our migrations is an
anti-pattern

#### 2. Fix pgvector extension schema handling

Improves the `20260109181714_add_docs_embedding` migration to handle
cases where pgvector exists in the wrong schema.

**Problem:**
- If pgvector was previously installed in `public` schema, `CREATE
EXTENSION IF NOT EXISTS` would succeed but not actually install it in
the `platform` schema
- This causes `type "vector" does not exist` errors because the type
isn't in the search_path

**Solution:**
- Detect if vector extension exists in a different schema than the
current one
- Drop it with CASCADE and reinstall in the correct schema (platform)
- Use dynamic SQL with `EXECUTE format()` to explicitly specify the
target schema
- Split exception handling: catch errors during removal, but let
installation fail naturally with clear PostgreSQL errors

**Impact:**
- No functional changes - Supabase continues to provide extensions as
before
- pgvector now correctly installs in the platform schema
- Cleaner migration history
- Prevents schema-related errors

### Checklist 📋

#### For code changes:
- [x] I have clearly listed my changes in the PR description
- [x] I have made a test plan
- [x] I have tested my changes according to the test plan:
  - [x] Verified migrations run successfully without the redundant file
  - [x] Confirmed Supabase extensions are still available
  - [x] Tested pgvector migration handles wrong-schema scenario
  - [x] No schema drift warnings

#### For configuration changes:
- [x] .env.default is updated or already compatible with my changes
- [x] docker-compose.yml is updated or already compatible with my
changes
- [x] I have included a list of my configuration changes in the PR
description (under **Changes**)
  - N/A - No configuration changes required

autogpt_platform/backend/migrations/20260109181714_add_docs_embedding/migration.sql

@@ -1,12 +1,37 @@
 -- CreateExtension
 -- Supabase: pgvector must be enabled via Dashboard → Database → Extensions first
--- Creates extension in current schema (determined by search_path from DATABASE_URL ?schema= param)
+-- Ensures vector extension is in the current schema (from DATABASE_URL ?schema= param)
+-- If it exists in a different schema (e.g., public), we drop and recreate it in the current schema
 -- This ensures vector type is in the same schema as tables, making ::vector work without explicit qualification
 DO $$
+DECLARE
+    current_schema_name text;
+    vector_schema text;
 BEGIN
-    CREATE EXTENSION IF NOT EXISTS "vector";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'vector extension not available or already exists, skipping';
+    -- Get the current schema from search_path
+    SELECT current_schema() INTO current_schema_name;
+
+    -- Check if vector extension exists and which schema it's in
+    SELECT n.nspname INTO vector_schema
+    FROM pg_extension e
+    JOIN pg_namespace n ON e.extnamespace = n.oid
+    WHERE e.extname = 'vector';
+
+    -- Handle removal if in wrong schema
+    IF vector_schema IS NOT NULL AND vector_schema != current_schema_name THEN
+        BEGIN
+            -- Vector exists in a different schema, drop it first
+            RAISE WARNING 'pgvector found in schema "%" but need it in "%". Dropping and reinstalling...',
+                vector_schema, current_schema_name;
+            EXECUTE 'DROP EXTENSION IF EXISTS vector CASCADE';
+        EXCEPTION WHEN OTHERS THEN
+            RAISE EXCEPTION 'Failed to drop pgvector from schema "%": %. You may need to drop it manually.',
+                vector_schema, SQLERRM;
+        END;
+    END IF;
+
+    -- Create extension in current schema (let it fail naturally if not available)
+    EXECUTE format('CREATE EXTENSION IF NOT EXISTS vector SCHEMA %I', current_schema_name);
 END $$;
 
 -- CreateEnum

autogpt_platform/backend/migrations/20260112173500_add_supabase_extensions_to_platform_schema/migration.sql

@@ -1,71 +0,0 @@
--- Acknowledge Supabase-managed extensions to prevent drift warnings
--- These extensions are pre-installed by Supabase in specific schemas
--- This migration ensures they exist where available (Supabase) or skips gracefully (CI)
-
--- Create schemas (safe in both CI and Supabase)
-CREATE SCHEMA IF NOT EXISTS "extensions";
-
--- Extensions that exist in both CI and Supabase
-DO $$
-BEGIN
-    CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'pgcrypto extension not available, skipping';
-END $$;
-
-DO $$
-BEGIN
-    CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'uuid-ossp extension not available, skipping';
-END $$;
-
--- Supabase-specific extensions (skip gracefully in CI)
-DO $$
-BEGIN
-    CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'pg_stat_statements extension not available, skipping';
-END $$;
-
-DO $$
-BEGIN
-    CREATE EXTENSION IF NOT EXISTS "pg_net" WITH SCHEMA "extensions";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'pg_net extension not available, skipping';
-END $$;
-
-DO $$
-BEGIN
-    CREATE EXTENSION IF NOT EXISTS "pgjwt" WITH SCHEMA "extensions";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'pgjwt extension not available, skipping';
-END $$;
-
-DO $$
-BEGIN
-    CREATE SCHEMA IF NOT EXISTS "graphql";
-    CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'pg_graphql extension not available, skipping';
-END $$;
-
-DO $$
-BEGIN
-    CREATE SCHEMA IF NOT EXISTS "pgsodium";
-    CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'pgsodium extension not available, skipping';
-END $$;
-
-DO $$
-BEGIN
-    CREATE SCHEMA IF NOT EXISTS "vault";
-    CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";
-EXCEPTION WHEN OTHERS THEN
-    RAISE NOTICE 'supabase_vault extension not available, skipping';
-END $$;
-
-
--- Return to platform
-CREATE SCHEMA IF NOT EXISTS "platform";