Repository navigation
Conversation
Chat automations get one organization-scoped resource with create, read, update and delete. The owner of an automation holds all four through agents-access. Organization admins and deployment owners hold read, update and delete, so nobody can create an automation that runs as another user. The five API key scopes stay internal-only. No scope that a user can request covers the resource. Part of CODAGT-1135 --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-fable-5-2` • Thinking: `xhigh`_
One migration adds the chat_automations table with its five enums, shape checks and the trigger that keeps a target chat, a creating chat and a project in the automation's organization. It also adds the provenance columns and the prompt_hooks_pending flag on the chat tables, and the five internal API key scopes. The migration takes its table locks first with short, retried waits, as 000609 does. dbauthz covers the five new queries. Only the owner inserts an automation, and the list never loads a webhook secret. The audit table masks the webhook secret and the prompt. Nothing calls the new queries yet. Part of CODAGT-1135 --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-fable-5-2` • Thinking: `xhigh`_
…sion 616 The test upgraded to the latest version and then asserted that the chat automations schema is gone. Migration 642 adds that schema again, so the test now upgrades to 616 through the production runner, and checks at the end that the later migrations keep the cleaned data.
Coder soft-deletes users, so the cascade on chat_automations.owner_id never fires. Migration 642 now adds the table to the user cleanup trigger, next to user secrets and AI provider keys, and the down restores the trigger. Also from the pre-PR review: - The chatd create case now asks RBAC directly, because the insert stops at the owner check first. - The chat column test reads back every query with an inline column list and both hand-written scans. - The organization trigger test covers the creating chat on a change of the organization alone. - Parallel subtests use their own test handle and context. - Comments on the chatd grant, the administrator actions, the audit resource type and the down migration state what the code does.
golangci-lint with the normal config reported G202 for two test tables that built an UPDATE from a column fragment, and G101 for two fixture strings.
Delete comments that restate the code, narrate the migration or explain our own design. Keep PostgreSQL constraints, nolint reasons, exported doc comments and every comment that sqlc or a stored function body carries. Signed-off-by: Thomas Kosiewski <tk@coder.com>
The previous commit removed every comment that the branch added. This commit puts back the ones that give a constraint or a reason the code cannot show, and leaves out the ones that restate a name or the next line.
…audits Nothing in this branch creates an audit entry for a chat automation, and the generated audit log page listed the resource as audited. The audit table keeps only the two new chat fields. The removed mapping and its tests go into the pull request that adds the first audited handler.
|
@codex review |
|
@codex security review |
Codex Review Summary: ✅ CompletedThis comment shows the latest Codex review activity on this pull request.
ℹ️ About Codex in GitHubYour team has set up Codex to review pull requests in this repo. Reviews are triggered when you
Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings. |
🛡️ Codex Security ReviewSecurity review completed. No security issues were found in this pull request. Reviewed commit: Only the user who started this review can view the report in Codex. ℹ️ About Codex security reviews in GitHubThis is an experimental Codex feature. Security reviews are triggered when:
Once complete, Codex will leave suggestions, or a comment if no findings are found. |
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: 97a37edc7f
ℹ️ About Codex in GitHub
Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you
- Open a pull request for review
- Mark a draft as ready
- Comment "@codex review".
If Codex has suggestions, it will comment; otherwise it will react with 👍.
Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".
…evious release The down left two kinds of rows that a binary without chat automations handles wrongly. It kept custom role permissions and API key allow list entries for chat_automation. The previous release cannot load them. The down now removes them, as migration 000616 did, and revokes a key whose allow list would be empty. It kept a chat_messages row whose prompt hooks have not run and dropped the flag that says so. The previous release would start that turn without the hooks. The down now stops with an error while such a row exists.
…ollback path The up migration's lock block, its 100 ms lock timeout and the per-reference check of the organization trigger had no test that fails when they are removed. Add tests that queue a message and update users after the lock block, that read chats_expanded while the up waits behind an open transaction, and that delete an automation's target chat and creating chat in one statement. The refused down now tells the operator to keep this release running until chatd has started the pending turns. It no longer suggests deleting messages. The test asserts the message, the hint, and that a refused down leaves the schema and the rows unchanged. It also asserts that no API key or custom role names chat_automation after the down.
|
@codex review |
|
@codex security review |
|
Codex Review: Didn't find any major issues. Already looking forward to the next diff. Reviewed commit: ℹ️ About Codex in GitHubYour team has set up Codex to review pull requests in this repo. Reviews are triggered when you
If Codex has suggestions, it will comment; otherwise it will react with 👍. Codex can also answer questions or update the PR. Try commenting "@codex address that feedback". |
🛡️ Codex Security ReviewSecurity review completed. No security issues were found in this pull request. Reviewed commit: Only the user who started this review can view the report in Codex. ℹ️ About Codex security reviews in GitHubThis is an experimental Codex feature. Security reviews are triggered when:
Once complete, Codex will leave suggestions, or a comment if no findings are found. |
…642 down's error No caller prints a PostgreSQL HINT, so the refused down now says in its message that the operator keeps this release running until chatd has started the pending turns. Add tests for two protections of the down that no test covered. The lock block takes custom_roles, so the down does not hold the chat tables while it waits for a locked custom role. The pending count runs after the locks, so a message that commits while the down waits still stops it. Seed a flagged message without an automation and an automation queued message without the flag.
|
@codex review |
…main The text merge kept the generated queries in another order than sqlc writes them. This is the output of make gen on the merge.
|
@codex review |
|
@codex security review |
Docs previewLinks open this pull request's preview at https://pr-32067.docs.cdr-preview.dev, which updates with each push. Check off each page once it's been reviewed. If a page changes in a later push, its checkbox clears automatically so it gets a fresh look. Pages not yet wired into the docs navigation aren't listed here. Preview sites for each built commit (1)Every built commit keeps its own preview, newest first. Previews aren't indexed by search engines and are deleted when this pull request is merged or closed. |
|
Codex Review: Didn't find any major issues. You're on a roll. Reviewed commit: ℹ️ About Codex in GitHubYour team has set up Codex to review pull requests in this repo. Reviews are triggered when you
If Codex has suggestions, it will comment; otherwise it will react with 👍. Codex can also answer questions or update the PR. Try commenting "@codex address that feedback". |
🛡️ Codex Security ReviewSecurity review completed. No security issues were found in this pull request. Reviewed commit: Only the user who started this review can view the report in Codex. ℹ️ About Codex security reviews in GitHubThis is an experimental Codex feature. Security reviews are triggered when:
Once complete, Codex will leave suggestions, or a comment if no findings are found. |
- coderd/rbac: drop TestChatAutomationScopesAreInternal. TestExternalScopesCannotReachChatAutomations owns the scope contract. - coderd/database: drop the hard-delete Owner and Organization subtests and the raw chat_model_configs delete. No code path hard-deletes those rows, and SoftDeletedOwner covers the user path. - coderd/database: drop TestChatAutomationColumnsOnChats. The sqlc row types, the gentest scan check and the Provenance readback guard the chats columns. - coderd/database: drop the GetReturnsTheInsertedRow subtest. Both queries return the same generated type. - coderd/database/dbauthz: drop the Delete subtest. The method suite and TestRolePermissions own the action and the grants. - coderd/database/dbauthz: keep only the SQL filter list assert in ScopedAPIKey. The rbac test owns the scope contract. - coderd/database/dbauthz: drop the chatd insert denial and the direct Create check. The owner check runs before RBAC. - coderd/database/migrations: drop the row count, schema and flag asserts after a refused down. One implicit transaction rolls everything back. - coderd/database/migrations: drop the HINT, removed label and leftover scans. The message equality, the round trip fingerprint and the per-key asserts cover them. - coderd/database/migrations: drop the rolled back transaction segment. It tests PostgreSQL. - coderd/database/migrations: merge the token delete lock order test into the lock block table.
…st uses them - enterprise/dbcrypt: the InsertChatAutomation webhook secret refusal and TestChatAutomationWebhookSecret go to #32076, which encrypts the secret instead. - coderd/database/migrations: the organization_id checks of enforce_chat_automation_organization, the dump.sql lines and the organization-change cases of TestChatAutomationOrganizationTrigger go to the PR that adds an organization move. - coderd/database/dbauthz: the chatd update grant on chat_automation and its direct Authorize(Update) assert go to the PR that adds the run-recording queries. - coderd/database/dbgen: chatAutomationGenCtx goes away. dbgen.ChatAutomation uses genCtx and the dbauthz test seeds its automations through rawDB.
|
Review-cap waiver, recorded before the push: the operator approved one more push on this PR on 2026-11-13. It removes tests that repeat other tests or cannot fail, and code that nothing calls yet (the dbcrypt insert refusal, which the next PR replaces with encryption, the organization-move checks in the trigger, and the chatd update grant). It starts Codex review round 7 of a 6-round cap. A new security flaw, data loss or broken core behavior found on that head stops this PR. Generated with |
|
@codex review |
|
@codex security review |
|
Codex Review: Didn't find any major issues. Keep it up! Reviewed commit: ℹ️ About Codex in GitHubYour team has set up Codex to review pull requests in this repo. Reviews are triggered when you
If Codex has suggestions, it will comment; otherwise it will react with 👍. Codex can also answer questions or update the PR. Try commenting "@codex address that feedback". |
🛡️ Codex Security ReviewSecurity review completed. No security issues were found in this pull request. Reviewed commit: Only the user who started this review can view the report in Codex. ℹ️ About Codex security reviews in GitHubThis is an experimental Codex feature. Security reviews are triggered when:
Once complete, Codex will leave suggestions, or a comment if no findings are found. |
| -- Take every table lock this migration needs before changing anything, | ||
| -- retrying short waits so that concurrent chatd traffic cannot deadlock | ||
| -- with it. See 000609_chat_automations.up.sql for the reasoning. | ||
| DO $$ | ||
| DECLARE | ||
| previous_lock_timeout text := current_setting('lock_timeout'); | ||
| deadline timestamptz := clock_timestamp() + interval '2 minutes'; | ||
| BEGIN | ||
| LOOP | ||
| BEGIN | ||
| -- Well below the default deadlock_timeout of 1s, so this | ||
| -- transaction gives up before PostgreSQL can choose an | ||
| -- application transaction as the deadlock victim. | ||
| PERFORM set_config('lock_timeout', '100ms', true); | ||
| -- chats_expanded is recreated below. DROP VIEW locks only the | ||
| -- view, while LOCK TABLE on the view would also lock users (via | ||
| -- visible_users) in ACCESS EXCLUSIVE mode. | ||
| DROP VIEW IF EXISTS chats_expanded; | ||
| LOCK TABLE chats, chat_messages, chat_queued_messages IN ACCESS EXCLUSIVE MODE; | ||
| -- The chat_automations foreign keys need these. | ||
| LOCK TABLE organizations, users, chat_projects, chat_model_configs, dbcrypt_keys IN SHARE ROW EXCLUSIVE MODE; | ||
| EXIT; | ||
| EXCEPTION WHEN lock_not_available OR deadlock_detected THEN | ||
| IF clock_timestamp() > deadline THEN | ||
| RAISE EXCEPTION 'migration 000642 could not lock the chat tables within 2 minutes'; | ||
| END IF; | ||
| END; | ||
| PERFORM pg_sleep(0.1); | ||
| END LOOP; | ||
| PERFORM set_config('lock_timeout', previous_lock_timeout, true); | ||
| END; | ||
| $$; |
There was a problem hiding this comment.
Could we drop the lock-retry block from this migration, here and in the down file?
Mathias asked me to remove the same block from #30073 (#30073 (comment)), and that PR merged without it. His draft #31167 removes it from 000609, 000611 and 000615, and it has the measurements.
The reason: all pending migrations run in one transaction, so the block only helps when its migration takes the first chat lock of the upgrade. An upgrade from v2.39 runs 000635 to 000642 together, and 000638 and 000640 lock chat_queued_messages before this block runs. The block cannot release that lock. A chat request that locked its chat row and then waits for chat_queued_messages can keep this loop retrying for 2 minutes, until the upgrade fails. Without the block, Postgres cancels one side after about 1s.
I have not run this for 000642. The lock tests in migration000642_test.go and the new row in TestMigrationChatTablesConcurrentTraffic start from 000641, so they only cover the case where 000642 takes the first lock.
Generated by an agent on behalf of the user
Why
Chat automations let a webhook or a schedule send a saved prompt to a chat as the automation's owner (RFC: Automations for Coder Agents). This is the first backend PR. It adds the database schema and the access rules, so that the API, the scheduler and the webhook endpoint can follow in small PRs behind the
chat-automationsexperiment.Part of CODAGT-1135.
What changed
000642_chat_automationsadds thechat_automationstable with five enum types, shape checks, and a trigger that rejects a target chat, creating chat or project from another organization. It addsautomation_id,input_idandprompt_hooks_pendingtochat_messagesandchat_queued_messages,automation_idandmanage_automations_enabledtochatsandchats_expanded, and fivechat_automation:*values toapi_key_scope.chat_automationhas the actions create, read, update and delete.agents-accessgrants all four on a member's own automations. Organization admins and deployment owners get read, update and delete, and no create. The chatd subject gets read. Its update grant lands with the PR that records runs, the first code that needs it. The five scopes are internal-only, so no scope that a user can request for an API key reaches an automation.webhook_secretand its key ID. The get returns the full row with the secret, because an update needs the stored secret on both sides of its audit diff and webhook verification reads it. dbauthz rejects an insert whose owner is not the actor.chat_automationyet. During a rolling upgrade, a replica of the previous release fails to load an API key whose allow list names a resource type it does not know. A later PR lifts this guard.Suggested reading order:
000642_chat_automations.up.sql,queries/chatautomations.sql,dbauthz/dbauthz.go,rbac/roles.go,rbac/policy/policy.go, then the tests. The rest of the 47 files is mostly generated code and tests.Scope
chatscolumns, and nothing acts on them.chat_automation, because they list every RBAC resource. The audit log documentation gains twoChatfields.Tradeoffs
last_run_unaccepted_count.owner_idhasON DELETE CASCADE, but Coder soft-deletes users, so the cascade never fires, and the automation of a deleted user would keep its webhook secret. The migration addschat_automationstodelete_deleted_user_resources(), as main does for user secrets, AI provider keys and skills. The column comment says that a queued message'sinput_idis set together withautomation_id. The RFC SQL has no constraint for that, so the migration adds a CHECK onchat_queued_messages, as migration 000609 did.chat_automationaction." The custom role API lets an actor grant only the permissions that the actor holds (customRoleEscalationCheck), and the RFC role table gives administrators no create. This PR keeps the RFC role table. A custom role can grant read, update and delete, and nobody can grant create that way. The RFC needs a decision on that sentence.Migration
Note
The migration takes
ACCESS EXCLUSIVElocks onchats,chat_messagesandchat_queued_messages, andSHARE ROW EXCLUSIVElocks onorganizations,users,chat_projects,chat_model_configsanddbcrypt_keys. It holds them until the migration transaction commits. During that time chat requests wait, and so do writes to the other five tables. Reads of those five tables continue.chat_messagestable, warm cache) the whole migration took 0.40 s, and the slowest statement was thechat_messagesindex at 116 ms. On a large deployment the time follows the size ofchat_messagesand the disk speed.Rollback
Coder does not support rollbacks (
docs/install/operate/upgrade.md), so the down migration serves the development server's--db-rollbackflag and tests. It is still written to be safe on real data:chats_expandedno longer has the new columns, and it can store a custom role that nameschat_automationagain.chat_messageswaits for its prompt hooks, because the previous release would start that turn without them. Its error message says what to do, and a refused down changes nothing. The check readschat_messagesonce while the down holds its table locks. No code sets that flag in this PR, so the down cannot refuse yet.chat_automationfrom custom roles and from API key scopes and allow lists, and deletes a key that is left with no scope or with an empty allow list. The previous release then loads the remaining keys and roles as it did before the upgrade.Live check
A rolling upgrade on a local deployment with the experiment off: a server on main kept answering API requests while a server on this head migrated the same database (162 requests, 0 failed), and it kept queueing messages on the new schema afterward. Up, down and up again left the schema byte-equal to a fresh one at each step.
Generated with
xum• Model:anthropic:claude-fable-5-2• Thinking:xhigh