Repository navigation
feat: add chat project sharing database layer - #30479
Conversation
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. |
|
/coder-agents-review |
This comment has been minimized.
This comment has been minimized.
|
/coder-agents-review |
There was a problem hiding this comment.
Since round 1, this PR adds a chatd access query and two queries that delete a project's chats, and it fixes the round 1 findings. Findings: 5 P2, 9 P3, 3 P4, 1 Nit.
16 of the 17 round 1 findings are verified fixed in 7e04612. The flag help text was updated, but docs/ai-coder/agents/chat-sharing.md:26 still describes chats only (inline). Two findings need a human decision, not only a code change: whether deleting a project should delete chats that sharees own, and whether read sharees may write project memories through their agents.
Commit 7e04612 is titled as review fixes but also adds GetChatProjectChatsForDelete and DeleteChatProjectChats; name them in the commit body or split them into a feat commit. The failing title job probably ran against the old feat(coderd): ... title; rerun it.
Out of scope (needs a ticket or explicit acceptance by a human):
coderd/x/chatd/memory.go:36: unchanged since round 1. At this head, agent memory tools still run aschatdwith site-wide memory permissions, and the only gate isChatProjectUsableByin #30439.
Notes:
coderd/database/queries/chatprojects.sql:150: none of the queries this PR adds has a caller outside tests. Their dbauthz permission choices are checked only against #30439.coderd/database/dbauthz/dbauthz.go:8032:UpdateChatProjectACLByIDauthorizes withGetChatProjectByID, so it is protected from lost updates only when the caller has already runGetChatProjectByIDForUpdatein the same transaction, as #30439 does.
docs/ai-coder/agents/chat-sharing.md:26
P3 [CRF-12] This page still says "When disabled, only chat owners can access their chats." The round 1 finding on the flag help text listed this line, and 7e04612 did not change it. (Mafu-san)
An admin who reads the chat sharing guide does not learn that the flag also makes shared projects private to their owner. Add chat projects to the sentence.
🤖
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
There was a problem hiding this comment.
Since round 2, this PR batches project chat deletion, adds the disabled-sharing guard to IsChatProjectAccessibleByUserID, and fixes the round 2 findings. Findings: 2 P2, 4 P3, 7 P4, 6 Nit.
All round 2 findings are verified fixed. The two decisions are accepted: deleting a project deletes sharees' chats, and a read (use) share lets the sharee's agents change project memories. Both consequences are stated in the code or in #30439's UI. The root-chat case of the deletion race is fixed; a sub-chat case remains (inline on line 137).
Out of scope (needs a ticket or explicit acceptance by a human):
coderd/x/chatd/memory.go:36: unchanged since round 1. Agent memory tools run aschatdwith site-wide memory permissions, soChatProjectUsableByin #30439 is the only check on sharee agents.
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
There was a problem hiding this comment.
Since round 3, this PR splits the delete lock into root and sub-chat statements, moves GetChatsByIDs to chats.sql, and fixes the round 3 comments and tests. Findings: 1 P3, 7 P4.
All round 3 findings are verified fixed, except that GetChatsByIDs still has no comment explaining why it duplicates GetChatsByIDsForRunnerSync (reply on that thread). The sub-chat race test fails if the two lock statements are merged back, so it covers the round 3 bug. The commit scope on 2ed5a28 is accepted as is, because the PR title has no scope.
Out of scope (needs a ticket or explicit acceptance by a human):
coderd/x/chatd/memory.go:36: unchanged since round 1. Agent memory tools run aschatdwith site-wide memory permissions, soChatProjectUsableByin #30439 is the only check on sharee agents.coderd/database/queries/chats.sql:1847:SyncAgentChatsContextMCPResourceslocks an agent's chats inidorder, whilechatstate.SetFamilyArchivedlocks a root before its sub-chats. When a sub-chat's ID sorts before its root's ID, one of the two transactions fails with 40P01.
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
There was a problem hiding this comment.
Since round 4, this PR adds LockChatProjectDeleteBatch, replaces DeleteChatsByIDs with DeleteChatFamiliesByRootIDs, and fixes the round 4 findings. Findings: 4 P3, 1 P4, 1 Nit.
All round 4 findings are verified fixed. Moving the lock order earlier in the test now fails TestChatProjectDeleteQueries, so the race test covers the shipped sequence.
Out of scope (needs a ticket or explicit acceptance by a human):
coderd/x/chatd/memory.go:36: unchanged since round 1. Agent memory tools run aschatdwith site-wide memory permissions, soChatProjectUsableByin #30439 is the only check on sharee agents.coderd/database/queries/chats.sql:1847:SyncAgentChatsContextMCPResourceslocks chats in id order, which conflicts with root-first family locks.coderd/database/querier_test.go:2482: thepg_stat_activity ... wait_event_type = 'Lock'poll now has five copies across the repo and nodbtestutilhelper, and the copies already differ (two excludepg_backend_pid()).
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
…acl-db # Conflicts: # coderd/database/check_constraint.go # coderd/rbac/roles.go # docs/reference/api/schemas.md
|
/coder-agents-review |
There was a problem hiding this comment.
Since round 17, this PR stamps project_id on archived sub-chats, so orphaned sub-chats stay inside their deleted project. It also documents that a deleted project's chats and memories follow chat retention, and that callers retry the delete after a deadlock. The branch also merged main, which renumbered the migration to 000618. This review covers only the PR's commits. Findings: 3 P4, 1 Nit.
All round 17 fixes are verified. Netero, Knuckle, Kite, and Mafuuu checked the new project_id stamp against every consumer of chats.project_id and found no changed result for live projects. CRF-180 is closed: Meruem accepts that #30439's chatstate.DeleteChatProject owns the sequence and the publish. Knuckle and Kite confirmed that it retries once on 40P01. CRF-154 remains deferred to #30439.
Notes:
coderd/database/queries/chatprojects.sql:208: with sub-chats stamped in step 4, theroot_chat_idbranch of the queue clear is redundant under the documented order. Deleting it passes the tests.
Out of scope (needs a ticket or explicit acceptance by a human):
- #30439
coderd/x/chatd/chatstate/family.go:205:deleteChatProjectTxcallsstore.InTx(..., nil), which uses the server's default isolation, while the contract here requires READ COMMITTED. On a server withdefault_transaction_isolation = 'repeatable read', steps 4 and 5 miss sub-chats committed during the step 3 wait. coderd/database/queries/chats.sql:2572(family-agnosticDeleteOldChats),coderd/rbac/roles.go:766,coderd/x/chatd/chatstate/machine.go:194,coderd/database/queries/chats.sql:2717,coderd/x/chatd/memory.go:36,coderd/database/tx.go:35: unchanged from earlier rounds.
Reviewed against 5ca77e2..cd7c9e2.
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
There was a problem hiding this comment.
Since round 18, this PR makes chats.project_id the only record of sub-chat membership. The migration backfills sub-chats from their root, and every family query now matches project_id alone. GetChatProjectByIDForUpdate also becomes FOR NO KEY UPDATE, so sub-chat inserts can take their foreign key lock during a delete. Findings: 1 P3, 6 P4, 4 Nit.
CRF-185, CRF-187, and CRF-189 are verified fixed. CRF-188 is fixed in this PR's queries, but the writer it depends on is not in this PR (CRF-193). Six reviewers checked the lock change against Postgres's conflict table, and three confirmed it with a mutation: FOR NO KEY UPDATE still conflicts with itself (ACL update against delete) and with FOR SHARE (root creation), and it admits only the FOR KEY SHARE taken by FK checks. Knuckle measured the backfill at 6.7 s for 40k project sub-chats among 1.14M chats. It holds the chat_projects lock from earlier in the same migration for that time, which is fine at experiment scale. Knuckle found no main-branch reader that treats project_id as roots-only. CRF-154 remains deferred to #30439.
Notes:
- PR description: after the backfill, the chat API returns
project_idfor sub-chats (db2sdk.Chat), andChatsPanel.tsx:328now opens the project's folder when a sub-chat is opened. "Nothing here changes behavior" should list this. (Kite)
Out of scope (needs a ticket or explicit acceptance by a human):
- #30439
coderd/x/chatd/chatstate/transitions.go:159: the comment says the delete locks the projectFOR UPDATE; it is nowFOR NO KEY UPDATE. - #30439
coderd/x/chatd/chatstate/family.go:205(default isolation, CRF-192),coderd/database/queries/chats.sql:2572,coderd/rbac/roles.go:766,coderd/x/chatd/chatstate/machine.go:194,coderd/database/queries/chats.sql:2717,coderd/x/chatd/memory.go:36,coderd/database/tx.go:35: unchanged from earlier rounds.
Reviewed against 5ca77e2..7e8460f.
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
There was a problem hiding this comment.
Round 20. The insert trigger closes CRF-193: insertChat holds the root FOR SHARE before InsertChat, and no query here or in #30439 updates chats.project_id, root_chat_id, or parent_chat_id, so every writer, including older replicas, gets the root's project. No P0 to P3 findings.
Verified fixed: CRF-193, 194, 195, 197, 198, 199 (mutation to FOR UPDATE now fails in 0.4s with the right message), 201, 202, 203, 204. TestMigration000618SubChatProjectID, TestChatProjectDeleteQueries, TestArchiveChatsOfDeletedChatProjectWaitsForChildCreation, and TestChatParam pass at 278f5c2.
Acknowledged: CRF-196 (closes when #30439 merges with this PR). CRF-207 is a second pair on the same detach path; if the same answer applies, say so there. Deferred: CRF-154 to #30439.
Notes (no action):
- CRF-215:
CREATE TRIGGERholds SHARE ROW EXCLUSIVE onchatsuntil the migration commits, so the backfill blocks writes from older replicas. Measured 2.7 to 3.7s on 1.1M chats with 45k project sub-chats, 0.76s with nothing to update (the expected case behind the experiment). Trigger first is the order that misses no concurrent insert. - CRF-216: the invariant is enforced on INSERT only. A future query that moves a root chat between projects must update the family or extend the trigger to
UPDATE OF project_id, root_chat_id, parent_chat_id. - CRF-217: a detach that commits between the chat read and the project read in
ExtractChatParamreturns 404 once; the next request succeeds.
Out of scope (#30439): chatstate/transitions.go:155 rejects a sub-chat ProjectID that differs from the root's, while the trigger silently overrides it; one rule, two failure behaviors (CRF-218). Earlier: transitions.go:159 FOR UPDATE comment (CRF-205), family.go:205 default isolation (CRF-192), chats.sql:2572, rbac/roles.go:766, chatstate/machine.go:194, chats.sql:2717, chatd/memory.go:36, tx.go:35.
Reviewed against 5ca77e2..278f5c2 (PR commits only).
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
There was a problem hiding this comment.
Round 21. No findings. 17c3c5a fixes everything open from round 20.
Verified fixed: CRF-206, 208, 209, 210, 211, 212, 213, 214. Keying the trigger and backfill on root_chat_id alone loses no rows: both sub-chat writers set RootChatID (subagent.go:1086, :1171), so a row with parent_chat_id set and root_chat_id NULL exists only after its root is deleted, and then there is no root project to copy. TestMigration000618SubChatProjectID and TestChatParam pass at 17c3c5a.
Acknowledged: CRF-196 and CRF-207 (closed by #30439's tombstone; the two merge together). Deferred: CRF-154 to #30439.
Note (no action), CRF-219: the down migration's root_chat_id IS NOT NULL OR parent_chat_id IS NOT NULL keeps project_id on a sub-chat whose root retention already deleted, because both FKs are ON DELETE SET NULL and the row then looks like a root chat. No predicate can tell it apart, and it is an archived chat that retention will delete, so nothing needs to change. Inferred from the FK definitions, not run.
Reviewed against 5ca77e2..17c3c5a (PR commits only).
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
There was a problem hiding this comment.
Round 22. No findings. The head (17c3c5a) has not changed since round 21, so this round was a final pass over the whole PR diff by three reviewers who had not looked at it recently. None found a defect that earlier rounds missed.
No findings are open. CRF-196 and CRF-207 stay acknowledged (closed by #30439's tombstone; the two merge together), and CRF-154 stays deferred to #30439. CRF-216 remains the one way the trigger can be bypassed: a future query that updates project_id or root_chat_id.
Reviewed against 5ca77e2..17c3c5a (PR commits only).
🤖 This review was automatically generated with Coder Agents.
|
/coder-agents-review |
There was a problem hiding this comment.
Round 23. No findings. d327a56 moves project deletion to #30683, and the remaining sharing layer holds up under a full panel.
Removal residue: none. Six reviewers searched the full diff and the worktree for the removed queries, the deleted column, and tombstone, purge, archive, trigger, backfill, and sub-chat project_id text; nothing remains. The audit table and audit-logs.md no longer list deleted for ChatProject. CRF-154, CRF-196, and CRF-207 are moot here because the code they target is gone.
GetChatProjectByIDForUpdate is back to FOR UPDATE. While the ACL transaction holds the row, chat and memory inserts into that project wait on their foreign key check. The ACL update in #30439 takes no lock after it, so this is a short wait with no deadlock, and it matches every other *ForUpdate query. The PR description still states its purpose, so CRF-6 stays fixed.
Notes (no action in this PR):
- CRF-221: origin/main now has
000618_workspace_agent_script_dependencies,000619, and000620. A trial merge conflicts indbauthz.go,querymetrics.go,dbmock.go, andquerier.go, and this migration needs renumbering to000621before merge. - CRF-222: after the rebase, main's
authorizeChatProjectInstructions(#30368) authorizesproject.RBACObject(), so this PR's ACL also governs project instructions:readsharees can read them andupdatesharees can write them, the same rule as memories. Worth listing in the description and in #30439's share dialog text.
Verified by reviewers at d327a56: TestGetChatProjectsOwnedOrSharedWithUserID (against Postgres), TestChatProjectRBACObjects, dbauthz TestMethodTestSuite plus the two disabled-mode tests, TestChatSharingPermissions, TestRolePermissions, and go test ./enterprise/audit/ pass.
Reviewed against d7bf081..d327a56 (full diff, restructure round).
🤖 This review was automatically generated with Coder Agents.
…acl-db # Conflicts: # coderd/database/dbauthz/dbauthz.go # coderd/database/dbmetrics/querymetrics.go # coderd/database/dbmock/dbmock.go # coderd/database/querier.go
|
/coder-agents-review |
There was a problem hiding this comment.
Round 24. No findings. df6d640 only merges main; the PR's added and removed lines are the same as in round 23 apart from the migration rename.
Verified:
- CRF-221 fixed: the migration is now
000621, after main's latest,000620_audit_chat_project_instructions_resource_type.make genat df6d640 leavesgit statusclean, sodump.sql,queries.sql.go,querier.go, dbmock, querymetrics, and the audit table match their sources. - The project instructions code from #30367 and #30368 does not bypass the ACL. The HTTP handlers (
chat_project_instructions.go:38,:75,:188) and dbauthz'sauthorizeChatProjectInstructions(dbauthz.go:1795) authorizeproject.RBACObject(), which drops both ACLs under--disable-chat-sharing. TheAsSystemRestrictedreads in those handlers run only after a caller-subject check on the same object. chatd'sresolveProjectInstructionsreads only the project already set on the chat. The CRF-222 note stands:readsharees can read instructions andupdatesharees can write them. - Reviewers ran these at df6d640, and they pass: dbauthz
TestMethodTestSuite(including main's instructions cases),TestChatProjectInstructions,TestResolveProjectInstructions,TestChatProjectRBACObjects,TestGetChatProjectsOwnedOrSharedWithUserID,./coderd/rbac, and./coderd/database/migrations.
Reviewed against 97c5260..df6d640 (PR diff against the merged base).
🤖 This review was automatically generated with Coder Agents.
…acl-db Renumbers the migration to 000623 and clarifies the --disable-chat-sharing help text.
Database and authorization layer for chat project sharing (experiment
chat-projects), part 1 of 2. #30439 adds the API on top of it. Nothing here changes behavior: projects start with empty ACLs, no endpoint writes them yet, and deleting a project still detaches its chats. Project deletion is in #30683.000621addsuser_aclandgroup_acljsonb columns tochat_projects, typed asChatACLlike chats, with GIN indexes. It also adds thechat_project:shareAPI key scope.GetChatProjectsOwnedOrSharedWithUserID: owned projects plus projects with an ACL entry for the user directly, through a group, or through the Everyone group (whose ID is the organization ID). Entries count only for members of the project's organization. Keys match regardless of actions, so dbauthz filters the rows.IsChatProjectAccessibleByUserID: whether a user owns a project or holds areador*grant on it, for code running under a system subject. With sharing disabled, dbauthz answers owner-only.GetChatProjectByIDForUpdate: lets the ACL update read, modify, and write in one transaction.UpdateChatProjectACLByID.shareaction onchat_project. dbauthz requires it to update the ACL.ChatProject.RBACObjectattaches the project ACL. Memory objects get a derived ACL: projectreadgrants memory read, andreadwithupdategrants memory create and delete. Wildcard grants count. Read sharees change memories through their chats' agents, which feat: add chat project sharing API #30439 gates. Project instructions authorize through the same object, so areadgrant reads them and anupdategrant edits them.--disable-chat-sharingalso covers projects: ACLs are ignored, the ACL update is rejected, and roles denychat_project:share. The help text and chat sharing docs say so.user_aclandgroup_aclchanges are tracked.chat_project:sharenegation in the roles test.The generated TypeScript changes only add the new scope value.