Repository navigation
feat: add chat automations schema, RBAC resource, and audit entries - #30085
Conversation
Adds storage and authorization for Automations for Coder Agents (RFC step 1, CODAGT-1207). No route, SDK request type, or chatd behavior uses any of this yet. - Migration 000604 adds the chat_automations table and its enums, with shape CHECKs and a same-org composite FK for the new-chat model. It also adds FK-free provenance columns on chat_queued_messages, chat_messages, and chats, and recreates chats_expanded. - Migration 000605 adds the chat_automation api_key_scope and resource_type enum values. - New org-scoped chat_automation RBAC resource. agents-access grants CRUD on the member's own automations; the member role excludes it at the user level. Its scopes stay out of the external catalog. - dbauthz wrappers for Insert/Get/Delete, a dbgen helper, and audit table entries with webhook_secret_hash marked secret.
Only migration 604 adds a foreign key that depends on the constraint 583 creates, and the test drops and restores exactly that one. Signed-off-by: Thomas Kosiewski <tk@coder.com>
The chat_queued_messages_automation_shape CHECK added by migration 604 had no negative test: the migration fixture only inserts a fully populated provenance row. Add a subtest that inserts a row with only automation_id set and asserts the named check rejects it.
Docs previewCheck 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. |
|
@codex review |
Codex Review SummaryThis 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. |
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: 0ac94e462e
ℹ️ 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".
…ization Codex review found that an automation could reference a chat in another organization, and that InsertChatAutomation never authorized the target chat. A trigger now rejects target and creating chats outside the automation's organization on insert and update. A composite foreign key cannot do this while keeping ON DELETE SET NULL on PostgreSQL 13. InsertChatAutomation also requires update on the target chat and read on the creating chat. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
@codex review |
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: 4fd3957086
ℹ️ 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".
…rences InsertChatAutomation now reads the new-chat model config through the authorized lookup, so the caller must pass the config's user and group ACL. Partial indexes on target_chat_id and created_by_chat_id keep chat deletion from scanning chat_automations to apply ON DELETE SET NULL. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
@codex review |
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: 9890219943
ℹ️ 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".
Owner lookups without an organization, including the users ON DELETE CASCADE, could not use the (organization_id, owner_id) index. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
@codex review |
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: 8630c361fd
ℹ️ 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".
Deleting a chat referenced by both target_chat_id and created_by_chat_id failed: the first ON DELETE SET NULL update fired the organization trigger, which re-validated the other, unchanged reference against the deleted chat. The trigger now validates a reference only on insert or when that reference or organization_id changes. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
@codex review |
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: c4df097fdd
ℹ️ 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".
Automation prompts are user-authored chat content, so audit diffs record that the prompt changed without its text, like chat titles. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
@codex review |
|
Codex Review: Didn't find any major issues. Hooray! 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". |
Main added migration 000604_audit_experiment_rule_resource_type, so the chat automation migrations move to 000605 and 000606 unchanged. Generated files (dump.sql, models.go, audit and API docs) are regenerated with make gen. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
--- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
…ac-audit Main added migration 000605_aibridge_provider_model, so the chat automation migrations move to 000606 and 000607 unchanged, with the fixture and the migrate_test.go comment following. The merge had no content conflicts; generated files were regenerated with make gen and matched the merge result. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
sync with main d9c13dc: no content conflicts; migrations renumbered to 000606/000607 (main added 000605_aibridge_provider_model); generated files regenerated
Generated with |
…ac-audit Main added migration 000606_chat_diff_statuses_composite_key, so the chat automation migrations move to 000607 and 000608 unchanged, with the fixture and the migrate_test.go comment following. The merge had no content conflicts; make gen regenerated the generated files. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
sync with main 35abc22: no content conflicts; migrations renumbered to 000607/000608 (main added 000606_chat_diff_statuses_composite_key); generated files regenerated
Generated with |
ibetitsmike
left a comment
There was a problem hiding this comment.
Xum (an AI coding agent) prepared and posted this review on behalf of Mike. Each finding came from automated review passes and was re-verified against the code; please push back on anything that looks wrong.
The schema, RBAC resource, dbauthz wrappers, and audit entries match CODAGT-1207 and RFC step 1, and the standards and spec passes found nothing to fix. While this review ran, the branch at 4b77e61 had a 000606 migration collision with main's #29081 (a golang-migrate probe failed on duplicate migration file); your main merge at 5a3dbc6 renumbered the migrations to 000607 and 000608, so that is resolved and only a description nit remains.
Verdict: no blocking issues
Severity: P0 0, P1 0, P2 0, P3 1
Nits:
- PR description: the Schema section and the fixture note still say migrations 000604/000605 and "new fixture for 000604"; the files are now 000607/000608.
Review coverage
- code-review Standards: 0 findings
- code-review Spec: 0 findings
- frontend-review (generated site/ API files only): FE2 PASS, others N/A
- coder-frontend-review (generated site/ API files only): types-and-naming N/A, no findings
- Adjudicator: 1 finding added (migration collision with main), dropped after the main merge at 5a3dbc6 fixed it
…ac-audit No conflicts. Main added no migrations, so ours stay 000607 and 000608. make gen added chats.automation_id to the row of main's new GetAutoArchiveInactiveChatCandidateByID query, which selects chats_expanded.*. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
sync with main d11809f: no conflicts; main added no migrations (ours stay 000607/000608); make gen added automation_id to main's new GetAutoArchiveInactiveChatCandidateByID row
Generated with |
ibetitsmike
left a comment
There was a problem hiding this comment.
Xum re-reviewed this PR on behalf of @ibetitsmike using GPT-6.1 Sol (high) Exec subagents.
Rechecked the full schema/RBAC/audit layer and confirmed the prior migration, authorization, indexing and audit-redaction fixes, including the corrected description. New findings: standards 0, spec 0, frontend 0; no existing unresolved threads remain in the captured snapshot. Focused Go tests passed for migrations/fixtures, shape constraints, authorization wrappers, RBAC/scopes, audit redaction and chat scans on this pinned head. Frontend typecheck, whole-repo lint/generation and remote UAT were not rerun.
Main added 000607_chat_projects, so this layer's migrations move up: 000607_chat_automations becomes 000608 (with its fixture) and 000608_chat_automation_scopes becomes 000609. Both chats_expanded views in 000608 now carry main's c.project_id after c.organization_id, so the up view keeps project_id next to automation_id and the down view matches main's 000607 view. The migrate_test comment names migration 608. Resolved conflicts: - coderd/rbac/policy/policy.go, roles.go, roles_test.go and dbauthz_test.go: kept both sides (chat_automation next to main's chat_project; the agents-access member role grants both). - queries/chats.sql: git attached this layer's automation_id line to main's new UpdateChatTitleByIDIfTitle, so UpdateChatTitleByID lost it. Added it back so both title queries still return database.Chat. - Generated files (dump.sql, models.go, queries.sql.go, check_constraint.go, docs) were regenerated with make gen. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
sync with main a328bee: merged main (chat projects API landed as 000607, so this layer's migrations are now 000608_chat_automations and 000609_chat_automation_scopes; chats_expanded views carry both project_id and automation_id; rbac/dbauthz conflicts kept both sides)
Generated with |
ibetitsmike
left a comment
There was a problem hiding this comment.
Xum validated this PR and approved it on behalf of @ibetitsmike.
Validated head b72fd1b75761. The current migration/view integration preserves the reviewed automation contracts, and prior schema, authorization and audit findings remain fixed. Fresh migration replay, shape constraints, RBAC, audit-redaction, query-scan and authorization tests passed. Current required CI passed. This approval is for this introducing layer, not a blanket approval of the remaining stack.
|
@codex review |
|
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". |
…ma-rbac-audit No conflicts. make gen produced no diff. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
merged main 15382eb (no new migrations; ours stay 000608-000609)
Generated with |
…ma-rbac-audit No conflicts. make gen produced no diff. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
merged main dc07908 (no new migrations on main; ours stay 000608 and up)
Generated with |
Main added migration 000608_chat_title_source, so this layer's migrations move to 000609_chat_automations and 000610_chat_automation_scopes (fixture and migrate_test comment follow). 000609 recreates chats_expanded with main's title_source and title_updated_at columns before automation_id, and its down migration restores the 000608 view. Resolved conflicts: chats.sql column lists take main's two title columns before automation_id; main removed UpdateChatByID and UpdateChatTitleByIDIfTitle, so this layer's edits to them are dropped. modelqueries.go scans follow the same column order. dump.sql, models.go, queries.sql.go and audit-logs.md come from make gen. --- _Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
|
@codex review |
|
Codex Review: Didn't find any major issues. Another round soon, please! 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". |
ibetitsmike
left a comment
There was a problem hiding this comment.
Xum validated this PR and approved it on behalf of @ibetitsmike.
Revalidated head db77271e7912 against base ff158c0589c9. Independent Standards and Spec renewal found no new blockers; the original accepted exclusions remain unchanged.
Re-reviewed renumbered migrations and preserved title/provenance projections. Schema, RBAC, audit, authorization, native migration-chain and inherited title-persistence checks passed.
Approval is scoped to this layer and the reviewed inherited interactions. No full-repository suite, fresh Pixel screenshots or remote UAT claimed.
Adds the storage and authorization pieces for Automations for Coder Agents. No route, SDK request type, or chatd code uses them yet, so nothing is exposed.
This is the bottom PR of the Automations stack (RFC step 1). Later PRs add the chatd acceptance seam, management routes, webhooks, and schedules on top of these names and constraints.
Schema (migrations 000609, 000610)
chat_automationstable with enumschat_automation_kind,chat_automation_target_mode,chat_automation_webhook_use, andchat_automation_when_busy. It includescreated_by_chat_id, the webhook secret hash and version, the single-usewebhook_consumed_atmarker, the schedule cursor and revision, andqueue_generation. Named CHECKs enforce the target and kind shapes from the RFC. A composite foreign key keepsnew_chat_model_config_idin the automation's organization, the same pattern the model override tables use. A trigger (chat_automations_chat_organization) keepstarget_chat_idandcreated_by_chat_idin that organization too. A composite foreign key cannot do that while keepingON DELETE SET NULLon PostgreSQL 13. The trigger validates a reference only on insert or when it changes, so chat deletion can still clear both references. Indexes cover the owner and both chat references for cascades andSET NULL.chat_automations:automation_id,input_id,queue_generationonchat_queued_messages(all set or all null),automation_idandinput_idonchat_messages, andchats.automation_id.chats_expandedis recreated to includeautomation_id.chat_automationvalues forapi_key_scopeandresource_type. The down migration is a no-op because enum values cannot be dropped.Authorization
chat_automation(create, read, update, delete). Theagents-accessrole grants it for the member's own automations. It is excluded from the member role's user-level permissions, so a member withoutagents-accessgets nothing. Site owners and organization admins get it through their existing all-permissions lists. The owner-only update, enable, and run rule from the RFC belongs to the management service in a later PR.externalLowLevel, so API keys gain no user-requestable scopes.dbauthzwrappers for the three queries this PR adds:InsertChatAutomation,GetChatAutomationByID, andDeleteChatAutomationByID.InsertChatAutomationalso requires update on the target chat, read on the creating chat, and read on the new-chat model config (its user and group ACL). There is also adbgen.ChatAutomationhelper.Audit
enterprise/audit/table.goentries forChatAutomation, withwebhook_secret_hashandpromptmarkedActionSecret. The prompt is treated as chat content, like chat titles. The schedule cursor, revision, and queue generation are ignored as internal bookkeeping.Chat.automation_idis tracked.Required checks from the ticket
TestMigrateandTestMigrateUpWithFixtures(new fixture for 000609). Dogfood UAT also applied the migrations on a dev instance, rolled them back, re-applied them, and compared schema dumps. After rollback, the only differences from main were the enum values that cannot be dropped and develop.sh's own_developschema. After re-applying, the only difference from this branch'sdump.sqlwas the_developschema.Test_chatAutomationSecretFieldsRedactedchecks the table entries and that an audit diff contains neither the raw hash, its base64 form, nor the prompt text.Also covered:
TestRolePermissionscases for own, other owner, other organization, and a member withoutagents-access.TestChatAutomationShapeConstraintscovers the named CHECKs, the cross-organization foreign key and trigger, a cross-organization retarget, and deleting referenced chats. Undersite/, only generated files changed (typesGenerated.ts,rbacresourcesGenerated.ts).Closes CODAGT-1207
Part of CODAGT-1135
Generated with
xum• Model:anthropic:claude-opus-5-5• Thinking:high