-- Normalize all plugin secret/connection refs out of JSON columns-- into proper relational shape: graphql, openapi, mcp.-- pg port of apps/local/drizzle/0007_normalize_plugin_secret_refs.sql.-- (google-discovery is local-only — not in cloud's plugin list.) -- ============================================================-- graphql-- ============================================================ -- Normalize graphql plugin: move secret/connection refs out of JSON-- columns into proper relational shape so usagesForSecret /-- usagesForConnection are one indexed SELECT instead of a JSON scan.-- pg port of apps/local/drizzle/0007_normalize_graphql.sql. CREATE TABLE "graphql_source_header" (	"id" text NOT NULL,	"scope_id" text NOT NULL,	"source_id" text NOT NULL,	"name" text NOT NULL,	"kind" text NOT NULL,	"text_value" text,	"secret_id" text,	"secret_prefix" text,	CONSTRAINT "graphql_source_header_scope_id_id_pk" PRIMARY KEY("scope_id","id"));--> statement-breakpointCREATE INDEX "graphql_source_header_scope_id_idx" ON "graphql_source_header" USING btree ("scope_id");--> statement-breakpointCREATE INDEX "graphql_source_header_source_id_idx" ON "graphql_source_header" USING btree ("source_id");--> statement-breakpointCREATE INDEX "graphql_source_header_secret_id_idx" ON "graphql_source_header" USING btree ("secret_id");--> statement-breakpoint CREATE TABLE "graphql_source_query_param" (	"id" text NOT NULL,	"scope_id" text NOT NULL,	"source_id" text NOT NULL,	"name" text NOT NULL,	"kind" text NOT NULL,	"text_value" text,	"secret_id" text,	"secret_prefix" text,	CONSTRAINT "graphql_source_query_param_scope_id_id_pk" PRIMARY KEY("scope_id","id"));--> statement-breakpointCREATE INDEX "graphql_source_query_param_scope_id_idx" ON "graphql_source_query_param" USING btree ("scope_id");--> statement-breakpointCREATE INDEX "graphql_source_query_param_source_id_idx" ON "graphql_source_query_param" USING btree ("source_id");--> statement-breakpointCREATE INDEX "graphql_source_query_param_secret_id_idx" ON "graphql_source_query_param" USING btree ("secret_id");--> statement-breakpoint -- New auth columns. `auth_kind` defaults to "none" so existing rows that-- predate this migration are valid even if the json was null.ALTER TABLE "graphql_source" ADD COLUMN "auth_kind" text DEFAULT 'none' NOT NULL;--> statement-breakpointALTER TABLE "graphql_source" ADD COLUMN "auth_connection_id" text;--> statement-breakpointCREATE INDEX "graphql_source_auth_connection_id_idx" ON "graphql_source" USING btree ("auth_connection_id");--> statement-breakpoint -- Backfill auth from the JSON column. Missing keys yield NULL, so a row-- with auth=NULL or kind="none" leaves auth_connection_id NULL and-- auth_kind defaulted to "none".UPDATE "graphql_source"SET	"auth_kind" = COALESCE("auth"->>'kind', 'none'),	"auth_connection_id" = "auth"->>'connectionId'WHERE "auth" IS NOT NULL;--> statement-breakpoint -- Backfill headers. For each (source, header_name) pair: if the value-- is a json object with .secretId, write a kind=secret row; otherwise-- write a kind=text row with the literal string. jsonb_each iterates-- the keys of the headers object.INSERT INTO "graphql_source_header"	("scope_id", "id", "source_id", "name", "kind", "text_value", "secret_id", "secret_prefix")SELECT	s."scope_id",	'[' || to_jsonb(s."id")::text || ',' || to_jsonb(h.key)::text || ']',	s."id",	h.key,	CASE		WHEN jsonb_typeof(h.value) = 'object' AND h.value ? 'secretId' THEN 'secret'		ELSE 'text'	END,	CASE WHEN jsonb_typeof(h.value) = 'string' THEN h.value #>> '{}' ELSE NULL END,	CASE WHEN jsonb_typeof(h.value) = 'object' THEN h.value->>'secretId' ELSE NULL END,	CASE WHEN jsonb_typeof(h.value) = 'object' THEN h.value->>'prefix' ELSE NULL ENDFROM "graphql_source" s, jsonb_each(s."headers") hWHERE s."headers" IS NOT NULLON CONFLICT DO NOTHING;--> statement-breakpoint -- Same for query_params.INSERT INTO "graphql_source_query_param"	("scope_id", "id", "source_id", "name", "kind", "text_value", "secret_id", "secret_prefix")SELECT	s."scope_id",	'[' || to_jsonb(s."id")::text || ',' || to_jsonb(q.key)::text || ']',	s."id",	q.key,	CASE		WHEN jsonb_typeof(q.value) = 'object' AND q.value ? 'secretId' THEN 'secret'		ELSE 'text'	END,	CASE WHEN jsonb_typeof(q.value) = 'string' THEN q.value #>> '{}' ELSE NULL END,	CASE WHEN jsonb_typeof(q.value) = 'object' THEN q.value->>'secretId' ELSE NULL END,	CASE WHEN jsonb_typeof(q.value) = 'object' THEN q.value->>'prefix' ELSE NULL ENDFROM "graphql_source" s, jsonb_each(s."query_params") qWHERE s."query_params" IS NOT NULLON CONFLICT DO NOTHING;--> statement-breakpoint ALTER TABLE "graphql_source" DROP COLUMN "headers";--> statement-breakpointALTER TABLE "graphql_source" DROP COLUMN "query_params";--> statement-breakpointALTER TABLE "graphql_source" DROP COLUMN "auth"; --> statement-breakpoint -- ============================================================-- openapi-- ============================================================ -- Normalize openapi plugin: move every direct secret/connection ref out-- of JSON columns into proper relational shape. pg port of-- apps/local/drizzle/0008_normalize_openapi.sql. CREATE TABLE "openapi_source_query_param" (	"id" text NOT NULL,	"scope_id" text NOT NULL,	"source_id" text NOT NULL,	"name" text NOT NULL,	"kind" text NOT NULL,	"text_value" text,	"secret_id" text,	"secret_prefix" text,	CONSTRAINT "openapi_source_query_param_scope_id_id_pk" PRIMARY KEY("scope_id","id"));--> statement-breakpointCREATE INDEX "openapi_source_query_param_scope_id_idx" ON "openapi_source_query_param" USING btree ("scope_id");--> statement-breakpointCREATE INDEX "openapi_source_query_param_source_id_idx" ON "openapi_source_query_param" USING btree ("source_id");--> statement-breakpointCREATE INDEX "openapi_source_query_param_secret_id_idx" ON "openapi_source_query_param" USING btree ("secret_id");--> statement-breakpoint CREATE TABLE "openapi_source_spec_fetch_header" (	"id" text NOT NULL,	"scope_id" text NOT NULL,	"source_id" text NOT NULL,	"name" text NOT NULL,	"kind" text NOT NULL,	"text_value" text,	"secret_id" text,	"secret_prefix" text,	CONSTRAINT "openapi_source_spec_fetch_header_scope_id_id_pk" PRIMARY KEY("scope_id","id"));--> statement-breakpointCREATE INDEX "openapi_source_spec_fetch_header_scope_id_idx" ON "openapi_source_spec_fetch_header" USING btree ("scope_id");--> statement-breakpointCREATE INDEX "openapi_source_spec_fetch_header_source_id_idx" ON "openapi_source_spec_fetch_header" USING btree ("source_id");--> statement-breakpointCREATE INDEX "openapi_source_spec_fetch_header_secret_id_idx" ON "openapi_source_spec_fetch_header" USING btree ("secret_id");--> statement-breakpoint CREATE TABLE "openapi_source_spec_fetch_query_param" (	"id" text NOT NULL,	"scope_id" text NOT NULL,	"source_id" text NOT NULL,	"name" text NOT NULL,	"kind" text NOT NULL,	"text_value" text,	"secret_id" text,	"secret_prefix" text,	CONSTRAINT "openapi_source_spec_fetch_query_param_scope_id_id_pk" PRIMARY KEY("scope_id","id"));--> statement-breakpointCREATE INDEX "openapi_source_spec_fetch_query_param_scope_id_idx" ON "openapi_source_spec_fetch_query_param" USING btree ("scope_id");--> statement-breakpointCREATE INDEX "openapi_source_spec_fetch_query_param_source_id_idx" ON "openapi_source_spec_fetch_query_param" USING btree ("source_id");--> statement-breakpointCREATE INDEX "openapi_source_spec_fetch_query_param_secret_id_idx" ON "openapi_source_spec_fetch_query_param" USING btree ("secret_id");--> statement-breakpoint -- New columns on openapi_source_binding to flatten the value json.-- `kind` defaults to 'text' so the ALTER works on existing rows; the-- backfill below stamps the real value.ALTER TABLE "openapi_source_binding" ADD COLUMN "kind" text DEFAULT 'text' NOT NULL;--> statement-breakpointALTER TABLE "openapi_source_binding" ADD COLUMN "secret_id" text;--> statement-breakpointALTER TABLE "openapi_source_binding" ADD COLUMN "connection_id" text;--> statement-breakpointALTER TABLE "openapi_source_binding" ADD COLUMN "text_value" text;--> statement-breakpointCREATE INDEX "openapi_source_binding_secret_id_idx" ON "openapi_source_binding" USING btree ("secret_id");--> statement-breakpointCREATE INDEX "openapi_source_binding_connection_id_idx" ON "openapi_source_binding" USING btree ("connection_id");--> statement-breakpoint UPDATE "openapi_source_binding"SET	"kind" = COALESCE("value"->>'kind', 'text'),	"secret_id" = CASE WHEN "value"->>'kind' = 'secret' THEN "value"->>'secretId' ELSE NULL END,	"connection_id" = CASE WHEN "value"->>'kind' = 'connection' THEN "value"->>'connectionId' ELSE NULL END,	"text_value" = CASE WHEN "value"->>'kind' = 'text' THEN "value"->>'text' ELSE NULL ENDWHERE "value" IS NOT NULL;--> statement-breakpoint INSERT INTO "openapi_source_query_param"	("scope_id", "id", "source_id", "name", "kind", "text_value", "secret_id", "secret_prefix")SELECT	s."scope_id",	'[' || to_jsonb(s."id")::text || ',' || to_jsonb(q.key)::text || ']',	s."id",	q.key,	CASE		WHEN jsonb_typeof(q.value) = 'object' AND q.value ? 'secretId' THEN 'secret'		ELSE 'text'	END,	CASE WHEN jsonb_typeof(q.value) = 'string' THEN q.value #>> '{}' ELSE NULL END,	CASE WHEN jsonb_typeof(q.value) = 'object' THEN q.value->>'secretId' ELSE NULL END,	CASE WHEN jsonb_typeof(q.value) = 'object' THEN q.value->>'prefix' ELSE NULL ENDFROM "openapi_source" s, jsonb_each(s."query_params") qWHERE s."query_params" IS NOT NULLON CONFLICT DO NOTHING;--> statement-breakpoint INSERT INTO "openapi_source_spec_fetch_header"	("scope_id", "id", "source_id", "name", "kind", "text_value", "secret_id", "secret_prefix")SELECT	s."scope_id",	'[' || to_jsonb(s."id")::text || ',' || to_jsonb(h.key)::text || ']',	s."id",	h.key,	CASE		WHEN jsonb_typeof(h.value) = 'object' AND h.value ? 'secretId' THEN 'secret'		ELSE 'text'	END,	CASE WHEN jsonb_typeof(h.value) = 'string' THEN h.value #>> '{}' ELSE NULL END,	CASE WHEN jsonb_typeof(h.value) = 'object' THEN h.value->>'secretId' ELSE NULL END,	CASE WHEN jsonb_typeof(h.value) = 'object' THEN h.value->>'prefix' ELSE NULL ENDFROM "openapi_source" s, jsonb_each(s."invocation_config"->'specFetchCredentials'->'headers') hWHERE s."invocation_config"->'specFetchCredentials'->'headers' IS NOT NULLON CONFLICT DO NOTHING;--> statement-breakpoint INSERT INTO "openapi_source_spec_fetch_query_param"	("scope_id", "id", "source_id", "name", "kind", "text_value", "secret_id", "secret_prefix")SELECT	s."scope_id",	'[' || to_jsonb(s."id")::text || ',' || to_jsonb(q.key)::text || ']',	s."id",	q.key,	CASE		WHEN jsonb_typeof(q.value) = 'object' AND q.value ? 'secretId' THEN 'secret'		ELSE 'text'	END,	CASE WHEN jsonb_typeof(q.value) = 'string' THEN q.value #>> '{}' ELSE NULL END,	CASE WHEN jsonb_typeof(q.value) = 'object' THEN q.value->>'secretId' ELSE NULL END,	CASE WHEN jsonb_typeof(q.value) = 'object' THEN q.value->>'prefix' ELSE NULL ENDFROM "openapi_source" s, jsonb_each(s."invocation_config"->'specFetchCredentials'->'queryParams') qWHERE s."invocation_config"->'specFetchCredentials'->'queryParams' IS NOT NULLON CONFLICT DO NOTHING;--> statement-breakpoint -- Preserve any legacy OAuth payload from invocation_config.oauth2 into-- the still-existing oauth2 column before we drop invocation_config.-- migrateLegacyConnections runs after drizzle migrations and reads-- oauth2 to detect the legacy shape; without this, rows that only had-- their OAuth payload under invocation_config.oauth2 would lose it.UPDATE "openapi_source"SET "oauth2" = "invocation_config"->'oauth2'WHERE "oauth2" IS NULL  AND "invocation_config"->'oauth2' IS NOT NULL;--> statement-breakpoint ALTER TABLE "openapi_source_binding" DROP COLUMN "value";--> statement-breakpointALTER TABLE "openapi_source" DROP COLUMN "query_params";--> statement-breakpointALTER TABLE "openapi_source" DROP COLUMN "invocation_config"; --> statement-breakpoint -- ============================================================-- mcp-- ============================================================ -- Normalize mcp plugin: lift the McpConnectionAuth secret/connection-- refs and the SecretBackedMap headers/query_params out of-- mcp_source.config JSON into proper columns / child tables. pg port-- of apps/local/drizzle/0009_normalize_mcp.sql. CREATE TABLE "mcp_source_header" (	"id" text NOT NULL,	"scope_id" text NOT NULL,	"source_id" text NOT NULL,	"name" text NOT NULL,	"kind" text NOT NULL,	"text_value" text,	"secret_id" text,	"secret_prefix" text,	CONSTRAINT "mcp_source_header_scope_id_id_pk" PRIMARY KEY("scope_id","id"));--> statement-breakpointCREATE INDEX "mcp_source_header_scope_id_idx" ON "mcp_source_header" USING btree ("scope_id");--> statement-breakpointCREATE INDEX "mcp_source_header_source_id_idx" ON "mcp_source_header" USING btree ("source_id");--> statement-breakpointCREATE INDEX "mcp_source_header_secret_id_idx" ON "mcp_source_header" USING btree ("secret_id");--> statement-breakpoint CREATE TABLE "mcp_source_query_param" (	"id" text NOT NULL,	"scope_id" text NOT NULL,	"source_id" text NOT NULL,	"name" text NOT NULL,	"kind" text NOT NULL,	"text_value" text,	"secret_id" text,	"secret_prefix" text,	CONSTRAINT "mcp_source_query_param_scope_id_id_pk" PRIMARY KEY("scope_id","id"));--> statement-breakpointCREATE INDEX "mcp_source_query_param_scope_id_idx" ON "mcp_source_query_param" USING btree ("scope_id");--> statement-breakpointCREATE INDEX "mcp_source_query_param_source_id_idx" ON "mcp_source_query_param" USING btree ("source_id");--> statement-breakpointCREATE INDEX "mcp_source_query_param_secret_id_idx" ON "mcp_source_query_param" USING btree ("secret_id");--> statement-breakpoint ALTER TABLE "mcp_source" ADD COLUMN "auth_kind" text DEFAULT 'none' NOT NULL;--> statement-breakpointALTER TABLE "mcp_source" ADD COLUMN "auth_header_name" text;--> statement-breakpointALTER TABLE "mcp_source" ADD COLUMN "auth_secret_id" text;--> statement-breakpointALTER TABLE "mcp_source" ADD COLUMN "auth_secret_prefix" text;--> statement-breakpointALTER TABLE "mcp_source" ADD COLUMN "auth_connection_id" text;--> statement-breakpointALTER TABLE "mcp_source" ADD COLUMN "auth_client_id_secret_id" text;--> statement-breakpointALTER TABLE "mcp_source" ADD COLUMN "auth_client_secret_secret_id" text;--> statement-breakpointCREATE INDEX "mcp_source_auth_secret_id_idx" ON "mcp_source" USING btree ("auth_secret_id");--> statement-breakpointCREATE INDEX "mcp_source_auth_connection_id_idx" ON "mcp_source" USING btree ("auth_connection_id");--> statement-breakpointCREATE INDEX "mcp_source_auth_client_id_secret_id_idx" ON "mcp_source" USING btree ("auth_client_id_secret_id");--> statement-breakpointCREATE INDEX "mcp_source_auth_client_secret_secret_id_idx" ON "mcp_source" USING btree ("auth_client_secret_secret_id");--> statement-breakpoint -- Only update rows with explicitly current-shape auth (kind=header w/-- secretId, or kind=oauth2 w/ connectionId). Legacy inline-OAuth rows-- are left untouched so the post-migrate migrateLegacyConnections-- script can convert them to a Connection.UPDATE "mcp_source"SET	"auth_kind" = "config"#>>'{auth,kind}',	"auth_header_name" = "config"#>>'{auth,headerName}',	"auth_secret_id" = "config"#>>'{auth,secretId}',	"auth_secret_prefix" = "config"#>>'{auth,prefix}',	"auth_connection_id" = "config"#>>'{auth,connectionId}',	"auth_client_id_secret_id" = "config"#>>'{auth,clientIdSecretId}',	"auth_client_secret_secret_id" = "config"#>>'{auth,clientSecretSecretId}'WHERE "config" IS NOT NULL  AND (    (      "config"#>>'{auth,kind}' = 'header'      AND "config"#>>'{auth,secretId}' IS NOT NULL    )    OR (      "config"#>>'{auth,kind}' = 'oauth2'      AND "config"#>>'{auth,connectionId}' IS NOT NULL    )  );--> statement-breakpoint INSERT INTO "mcp_source_header"	("scope_id", "id", "source_id", "name", "kind", "text_value", "secret_id", "secret_prefix")SELECT	s."scope_id",	'[' || to_jsonb(s."id")::text || ',' || to_jsonb(h.key)::text || ']',	s."id",	h.key,	CASE		WHEN jsonb_typeof(h.value) = 'object' AND h.value ? 'secretId' THEN 'secret'		ELSE 'text'	END,	CASE WHEN jsonb_typeof(h.value) = 'string' THEN h.value #>> '{}' ELSE NULL END,	CASE WHEN jsonb_typeof(h.value) = 'object' THEN h.value->>'secretId' ELSE NULL END,	CASE WHEN jsonb_typeof(h.value) = 'object' THEN h.value->>'prefix' ELSE NULL ENDFROM "mcp_source" s, jsonb_each(s."config"->'headers') hWHERE s."config"->'headers' IS NOT NULLON CONFLICT DO NOTHING;--> statement-breakpoint INSERT INTO "mcp_source_query_param"	("scope_id", "id", "source_id", "name", "kind", "text_value", "secret_id", "secret_prefix")SELECT	s."scope_id",	'[' || to_jsonb(s."id")::text || ',' || to_jsonb(q.key)::text || ']',	s."id",	q.key,	CASE		WHEN jsonb_typeof(q.value) = 'object' AND q.value ? 'secretId' THEN 'secret'		ELSE 'text'	END,	CASE WHEN jsonb_typeof(q.value) = 'string' THEN q.value #>> '{}' ELSE NULL END,	CASE WHEN jsonb_typeof(q.value) = 'object' THEN q.value->>'secretId' ELSE NULL END,	CASE WHEN jsonb_typeof(q.value) = 'object' THEN q.value->>'prefix' ELSE NULL ENDFROM "mcp_source" s, jsonb_each(s."config"->'queryParams') qWHERE s."config"->'queryParams' IS NOT NULLON CONFLICT DO NOTHING;--> statement-breakpoint -- Strip already-copied fields from config JSON. headers/queryParams-- are always safe; auth is only stripped on rows whose auth was the-- current shape (legacy inline-OAuth rows keep config.auth so-- migrateLegacyConnections can mint a Connection from it).UPDATE "mcp_source"SET "config" = "config" - 'headers' - 'queryParams'WHERE "config" IS NOT NULL;--> statement-breakpoint UPDATE "mcp_source"SET "config" = "config" - 'auth'WHERE "config" IS NOT NULL  AND (    "config"#>>'{auth,kind}' = 'none'    OR (      "config"#>>'{auth,kind}' = 'header'      AND "config"#>>'{auth,secretId}' IS NOT NULL    )    OR (      "config"#>>'{auth,kind}' = 'oauth2'      AND "config"#>>'{auth,connectionId}' IS NOT NULL    )  );