Skip to content

feat: add chat automations schema, RBAC resource, and audit entries - #30085

Merged
ThomasK33 merged 17 commits into
mainfrom
tk/codagt-1207-automations-schema-rbac-audit
Oct 2, 2026
Merged

ThomasK33 merged 17 commits into
mainfrom
tk/codagt-1207-automations-schema-rbac-audit

Conversation

@ThomasK33

@ThomasK33 ThomasK33 commented Sep 29, 2026 •

Copy link
Copy Markdown
Member

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_automations table with enums chat_automation_kind, chat_automation_target_mode, chat_automation_webhook_use, and chat_automation_when_busy. It includes created_by_chat_id, the webhook secret hash and version, the single-use webhook_consumed_at marker, the schedule cursor and revision, and queue_generation. Named CHECKs enforce the target and kind shapes from the RFC. A composite foreign key keeps new_chat_model_config_id in the automation's organization, the same pattern the model override tables use. A trigger (chat_automations_chat_organization) keeps target_chat_id and created_by_chat_id in that organization too. A composite foreign key cannot do that while keeping ON DELETE SET NULL on 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 and SET NULL.
  • Provenance columns with partial indexes and no foreign key to chat_automations: automation_id, input_id, queue_generation on chat_queued_messages (all set or all null), automation_id and input_id on chat_messages, and chats.automation_id. chats_expanded is recreated to include automation_id.
  • chat_automation values for api_key_scope and resource_type. The down migration is a no-op because enum values cannot be dropped.

Authorization

  • New organization-scoped RBAC resource chat_automation (create, read, update, delete). The agents-access role grants it for the member's own automations. It is excluded from the member role's user-level permissions, so a member without agents-access gets 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.
  • The generated scopes stay out of externalLowLevel, so API keys gain no user-requestable scopes.
  • dbauthz wrappers for the three queries this PR adds: InsertChatAutomation, GetChatAutomationByID, and DeleteChatAutomationByID. InsertChatAutomation also 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 a dbgen.ChatAutomation helper.

Audit

  • enterprise/audit/table.go entries for ChatAutomation, with webhook_secret_hash and prompt marked ActionSecret. The prompt is treated as chat content, like chat titles. The schedule cursor, revision, and queue generation are ignored as internal bookkeeping. Chat.automation_id is tracked.

Required checks from the ticket

  • Migrations apply and roll back: TestMigrate and TestMigrateUpWithFixtures (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 _develop schema. After re-applying, the only difference from this branch's dump.sql was the _develop schema.
  • Secret hash marked secret: Test_chatAutomationSecretFieldsRedacted checks the table entries and that an audit diff contains neither the raw hash, its base64 form, nor the prompt text.

Also covered: TestRolePermissions cases for own, other owner, other organization, and a member without agents-access. TestChatAutomationShapeConstraints covers the named CHECKs, the cross-organization foreign key and trigger, a cross-organization retarget, and deleting referenced chats. Under site/, 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

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.
@linear-code

linear-code Bot commented Sep 29, 2026 •

Copy link
Copy Markdown

CODAGT-1135

CODAGT-1207

@github-actions

github-actions Bot commented Sep 29, 2026 •

Copy link
Copy Markdown
Contributor

Docs preview

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.

@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@chatgpt-codex-connector

chatgpt-codex-connector Bot commented Sep 29, 2026 •

Copy link
Copy Markdown

Codex Review Summary

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

Review Status Commit Review trigger
📝 Code Review ✅ Completed 2026-10-02T14:08:02.377482Z db77271 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 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: 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".

Comment thread coderd/database/dbauthz/dbauthz.go Resolved
…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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@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: 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".

Comment thread coderd/database/dbauthz/dbauthz.go Resolved
…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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@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: 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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@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: 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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@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: 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".

Comment thread enterprise/audit/table.go Resolved
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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. Hooray!

Reviewed commit: 74645385d5

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

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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

sync with main d9c13dc: no content conflicts; migrations renumbered to 000606/000607 (main added 000605_aibridge_provider_model); generated files regenerated


Generated with xum • Model: anthropic:claude-opus-5-5 • Thinking: high

@ThomasK33
ThomasK33 marked this pull request as ready for review October 1, 2026 09:38
…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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

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

@ibetitsmike ibetitsmike left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

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

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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

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

@ibetitsmike ibetitsmike left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

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

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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

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

@ibetitsmike ibetitsmike left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

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

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.

@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@chatgpt-codex-connector

Copy link
Copy Markdown

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

Reviewed commit: b72fd1b757

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

…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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

merged main 15382eb (no new migrations; ours stay 000608-000609)


Generated with xum • Model: anthropic:claude-opus-5-5 • Thinking: high

…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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

merged main dc07908 (no new migrations on main; ours stay 000608 and up)


Generated with xum • Model: anthropic:claude-opus-5-5 • Thinking: high

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`_
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. Another round soon, please!

Reviewed commit: db77271e79

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

@ibetitsmike ibetitsmike left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

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

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.

@ThomasK33
ThomasK33 merged commit f55aa6c into main Oct 2, 2026
36 checks passed
@github-actions github-actions Bot locked and limited conversation to collaborators Oct 2, 2026
@ThomasK33
ThomasK33 deleted the tk/codagt-1207-automations-schema-rbac-audit branch October 2, 2026 15:55
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants