Skip to content

feat: add the chat automations schema and RBAC resource - #32067

Open
ThomasK33 wants to merge 19 commits into
mainfrom
tk/automations-schema
Open

ThomasK33 wants to merge 19 commits into
mainfrom
tk/automations-schema

Conversation

@ThomasK33

@ThomasK33 ThomasK33 commented Nov 12, 2026 •

Copy link
Copy Markdown
Member

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. 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.
  • 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. No code writes an automation before the encryption PR (feat: encrypt the webhook secret of a chat automation with dbcrypt #32076), which lands next in this stack, so no secret is stored before encryption exists.
  • 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

Note

The migration takes ACCESS EXCLUSIVE locks on chats, chat_messages and chat_queued_messages, and SHARE ROW EXCLUSIVE locks on organizations, users, chat_projects, chat_model_configs and dbcrypt_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.

  • 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

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.
@ThomasK33
ThomasK33 temporarily deployed to main November 12, 2026 00:09 — with GitHub Actions Inactive
@ThomasK33
ThomasK33 temporarily deployed to main November 12, 2026 00:09 — with GitHub Actions Inactive
@linear-code

linear-code Bot commented Nov 12, 2026 •

Copy link
Copy Markdown

CODAGT-1135

CODAGT-1567

@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@ThomasK33

Copy link
Copy Markdown
Member Author

@codex security review

@chatgpt-codex-connector

chatgpt-codex-connector Bot commented Nov 12, 2026 •

Copy link
Copy Markdown
Codex Review Summary: ✅ Completed

This comment shows the latest Codex review activity on this pull request.

Review Status Commit Review trigger
📝 Code Review ✅ Completed 2026-11-13T14:19:05.222861Z 6a31a3d Manual request
🔒 Security Review ✅ Completed 2026-11-13T14:22:47.854176Z 6a31a3d Manual request
ℹ️ 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" or "@codex security review".

Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings.

@chatgpt-codex-connector

Copy link
Copy Markdown

🛡️ Codex Security Review

Security review completed. No security issues were found in this pull request.

Reviewed commit: 97a37edc7f

View security finding report

Only the user who started this review can view the report in Codex.

ℹ️ About Codex security reviews in GitHub

This is an experimental Codex feature. Security reviews are triggered when:

  • You comment "@codex security review"
  • A regular code review gets triggered (for example, "@codex review" or when a PR is opened), and you’re opted in so security review runs alongside code review

Once complete, Codex will leave suggestions, or a comment if no findings are found.

@chatgpt-codex-connector chatgpt-codex-connector Bot left a comment

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

💡 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.
@ThomasK33
ThomasK33 temporarily deployed to main November 12, 2026 02:13 — with GitHub Actions Inactive
@ThomasK33
ThomasK33 temporarily deployed to main November 12, 2026 02:13 — with GitHub Actions Inactive
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@ThomasK33

Copy link
Copy Markdown
Member Author

@codex security review

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. Already looking forward to the next diff.

Reviewed commit: 3f5b030448

ℹ️ 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".

@chatgpt-codex-connector

Copy link
Copy Markdown

🛡️ Codex Security Review

Security review completed. No security issues were found in this pull request.

Reviewed commit: 3f5b030448

View security finding report

Only the user who started this review can view the report in Codex.

ℹ️ About Codex security reviews in GitHub

This is an experimental Codex feature. Security reviews are triggered when:

  • You comment "@codex security review"
  • A regular code review gets triggered (for example, "@codex review" or when a PR is opened), and you’re opted in so security review runs alongside code review

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.
@ThomasK33
ThomasK33 temporarily deployed to main November 12, 2026 03:22 — with GitHub Actions Inactive
@ThomasK33
ThomasK33 temporarily deployed to main November 12, 2026 03:22 — with GitHub Actions Inactive
@ThomasK33

Copy link
Copy Markdown
Member Author

@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.
@ThomasK33
ThomasK33 temporarily deployed to main November 13, 2026 07:31 — with GitHub Actions Inactive
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@ThomasK33

Copy link
Copy Markdown
Member Author

@codex security review

@ThomasK33
ThomasK33 temporarily deployed to docs-preview November 13, 2026 07:34 — with GitHub Actions Inactive
@github-actions

Copy link
Copy Markdown
Contributor

Docs preview

Links 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.

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. You're on a roll.

Reviewed commit: 5a961ebadd

ℹ️ 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".

@chatgpt-codex-connector

Copy link
Copy Markdown

🛡️ Codex Security Review

Security review completed. No security issues were found in this pull request.

Reviewed commit: 5a961ebadd

View security finding report

Only the user who started this review can view the report in Codex.

ℹ️ About Codex security reviews in GitHub

This is an experimental Codex feature. Security reviews are triggered when:

  • You comment "@codex security review"
  • A regular code review gets triggered (for example, "@codex review" or when a PR is opened), and you’re opted in so security review runs alongside code review

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.
@ThomasK33
ThomasK33 removed this pull request from stack #32077 November 13, 2026 12:25
…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.
@ThomasK33

Copy link
Copy Markdown
Member Author

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 xum • Model: anthropic:claude-opus-5-5 • Thinking: high

@ThomasK33
ThomasK33 temporarily deployed to main November 13, 2026 14:15 — with GitHub Actions Inactive
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@ThomasK33
ThomasK33 temporarily deployed to main November 13, 2026 14:15 — with GitHub Actions Inactive
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex security review

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. Keep it up!

Reviewed commit: 6a31a3dd33

ℹ️ 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".

@chatgpt-codex-connector

Copy link
Copy Markdown

🛡️ Codex Security Review

Security review completed. No security issues were found in this pull request.

Reviewed commit: 6a31a3dd33

View security finding report

Only the user who started this review can view the report in Codex.

ℹ️ About Codex security reviews in GitHub

This is an experimental Codex feature. Security reviews are triggered when:

  • You comment "@codex security review"
  • A regular code review gets triggered (for example, "@codex review" or when a PR is opened), and you’re opted in so security review runs alongside code review

Once complete, Codex will leave suggestions, or a comment if no findings are found.

@ThomasK33
ThomasK33 added this pull request to stack #32184 November 13, 2026 17:08
Comment on lines +4 to +35
-- 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;
$$;

Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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

This branch was previously deployed

2 inactive (1 outdated) deployments
main — 6a31a3dd Deployed Nov 13, 2026 by ThomasK33 via cla #256535
docs-preview — 5a961eba Deployed Nov 13, 2026 by ThomasK33 via deploy #53
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants