GeezerVison

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-automations experiment.

Part of CODAGT-1135.

What changed

• Migration 000642_chat_automations adds the chat_automations table with five enum types, shape checks, and a trigger that rejects a target chat, creating chat or project from another organization. It adds automation_id, input_id and prompt_hooks_pending to chat_messages and chat_queued_messages, automation_id and manage_automations_enabled to chats and chats_expanded, and five chat_automation:* values to api_key_scope.
• The organization-scoped RBAC resource chat_automation has the actions create, read, update and delete. agents-access grants 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 and update. The five scopes are internal-only, so no scope that a user can request for an API key reaches an automation.
• Queries with dbauthz checks: insert, get, list by organization (RBAC as an SQL filter), count by owner, and delete. The list leaves out webhook_secret and 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.
• A token's allow list cannot name chat_automation yet. 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

• No handler, tool or background job uses the new table or the new columns yet. The chat queries return the two new chats columns, and nothing acts on them.
• Not in this PR: the API and SDK types, the audit log entry for automations (it lands with the first handler that audits one), encryption of the webhook secret at rest, the scheduler and the webhook endpoint. Until the encryption PR, the dbcrypt store refuses an insert with a webhook secret when encryption keys are configured, so no deployment with encryption keys can store one in plaintext.
• Visible on main after this PR: the custom role editor and the role listings show chat_automation, because they list every RBAC resource. The audit log documentation gains two Chat fields.

Tradeoffs

• The migration deviates from the RFC SQL in three places. The RFC says "Automation owners also see the number of skipped and refused runs since the last acceptance. Acceptance resets this count to zero.", and its SQL has no column for that count, so the migration adds last_run_unaccepted_count. owner_id has ON 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 adds chat_automations to delete_deleted_user_resources(), as main does for user secrets, AI provider keys and skills. The column comment says that a queued message's input_id is set together with automation_id. The RFC SQL has no constraint for that, so the migration adds a CHECK on chat_queued_messages, as migration 000609 did.
• The RFC says "Custom roles can grant any chat_automation action." 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

• Locks: the migration takes every lock first, in a retry loop with a 100 ms lock timeout, as migration 000609 did (shipped in v2.39.0). It does not hold a chat table while it waits behind a long transaction. After 2 minutes without success it fails and changes nothing.
• Duration: it adds the columns without a table rewrite and builds three partial indexes. Each index build reads its whole table. On a test database with 100,000 chats and 1,000,000 messages (a 326 MB chat_messages table, warm cache) the whole migration took 0.40 s, and the slowest statement was the chat_messages index at 116 ms. On a large deployment the time follows the size of chat_messages and the disk speed.
• Rolling upgrade: a replica on the previous release keeps working while the new release migrates, and afterwards on the new schema. Its queries name their columns, and every new column is nullable or has a default. The live check below ran this.

Rollback

Coder does not support rollbacks (docs/install/operate/upgrade.md), so the down migration serves the development server's --db-rollback flag and tests. It is still written to be safe on real data:

• Stop every replica of this release first. A replica that still serves after the down fails its own chat reads, because chats_expanded no longer has the new columns, and it can store a custom role that names chat_automation again.
• The down refuses to run while a message in chat_messages waits 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 reads chat_messages once while the down holds its table locks. No code sets that flag in this PR, so the down cannot refuse yet.
• The down deletes the queued messages that an automation queued and those that still wait for their prompt hooks, removes chat_automation from 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