Files
Ethan acebdf5b60 feat: add chat_mcp_servers table with encrypted headers (#29517)
This is the second PR in the stack that adds experimental support for
attaching MCP servers to a chat;
https://github.com/coder/coder/pull/29466 is the first. This one is just
the persistence layer, nothing reads from the table yet.

It adds a `chat_mcp_servers` table, unique on `(chat_id, slug)` and
cascading on chat deletion. Each row stores the server URL, a JSON
object of request headers, per-server tool allow and deny lists, and
three flags that later PRs in the stack use to decide when the server
gets connected: `allow_in_plan_mode` (mirroring the existing column on
`mcp_server_configs`), `allow_in_subagents`, and
`forward_coder_headers`. All three default to `false`.

Headers are the sensitive part, so they're encrypted at rest via
`dbcrypt` in the same way `external_auth_links` are: `headers_key_id`
records which key was used, and `NULL` means plaintext. Empty or `{}`
headers are stored as `{}` with no key, so servers that don't need auth
never produce ciphertext. The `dbcrypt` wrappers cover the chat-scoped
reads and writes, plus `GetChatMCPServersByChatOwnerID` and
`UpdateEncryptedChatMCPServerHeaders`, which follow the per-user shape
that `Rotate` and `Decrypt` in `cliutil.go` use. I haven't wired those
two into `coder server dbcrypt rotate` and `decrypt` in this PR.

For authz, reading a chat's servers requires read on the chat, and
upserting or deleting them requires update on the chat. The two
owner-scoped queries above are system-only.


Part of CODAGT-1131
2026-09-25 18:51:54 +10:00

74 lines
1.6 KiB
SQL

-- name: GetChatMCPServersByChatID :many
SELECT
*
FROM
chat_mcp_servers
WHERE
chat_id = @chat_id::uuid
ORDER BY
slug ASC;
-- name: UpsertChatMCPServer :one
INSERT INTO chat_mcp_servers (
id,
chat_id,
slug,
url,
headers,
headers_key_id,
tool_allow_list,
tool_deny_list,
allow_in_subagents,
forward_coder_headers
) VALUES (
@id::uuid,
@chat_id::uuid,
@slug::text,
@url::text,
@headers::text,
sqlc.narg('headers_key_id')::text,
@tool_allow_list::text[],
@tool_deny_list::text[],
@allow_in_subagents::boolean,
@forward_coder_headers::boolean
)
ON CONFLICT (chat_id, slug) DO UPDATE SET
url = EXCLUDED.url,
headers = EXCLUDED.headers,
headers_key_id = EXCLUDED.headers_key_id,
tool_allow_list = EXCLUDED.tool_allow_list,
tool_deny_list = EXCLUDED.tool_deny_list,
allow_in_subagents = EXCLUDED.allow_in_subagents,
forward_coder_headers = EXCLUDED.forward_coder_headers,
updated_at = now()
RETURNING
*;
-- name: DeleteChatMCPServersByChatIDExcludingSlugs :exec
DELETE FROM
chat_mcp_servers
WHERE
chat_id = @chat_id::uuid
AND NOT (slug = ANY(@slugs::text[]));
-- name: GetChatMCPServersByChatOwnerID :many
SELECT
cms.*
FROM
chat_mcp_servers cms
JOIN
chats ON chats.id = cms.chat_id
WHERE
chats.owner_id = @owner_id::uuid
ORDER BY
cms.id ASC;
-- name: UpdateEncryptedChatMCPServerHeaders :exec
UPDATE
chat_mcp_servers
SET
headers = @headers::text,
headers_key_id = sqlc.narg('headers_key_id')::text
WHERE
id = @id::uuid;