Skip to content

feat: add chat project sharing database layer - #30479

Merged
f0ssel merged 35 commits into
mainfrom
f0ssel/chat-projects-acl-db
Oct 14, 2026
Merged

f0ssel merged 35 commits into
mainfrom
f0ssel/chat-projects-acl-db

Conversation

@f0ssel

@f0ssel f0ssel commented Oct 7, 2026 •

Copy link
Copy Markdown
Member

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.

  • Schema: migration 000621 adds user_acl and group_acl jsonb columns to chat_projects, typed as ChatACL like chats, with GIN indexes. It also adds the chat_project:share API key scope.
  • Queries:
    • 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 a read or * 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.
  • RBAC:
    • New share action on chat_project. dbauthz requires it to update the ACL.
    • ChatProject.RBACObject attaches the project ACL. Memory objects get a derived ACL: project read grants memory read, and read with update grants 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 a read grant reads them and an update grant edits them.
    • --disable-chat-sharing also covers projects: ACLs are ignored, the ACL update is rejected, and roles deny chat_project:share. The help text and chat sharing docs say so.
  • Audit: user_acl and group_acl changes are tracked.
  • Tests:
    • Querier tests run the access queries both raw and through dbauthz (owner, direct user, group, Everyone, wildcard grants, a member of another org, grants without read).
    • RBAC object and memory ACL tests, with an authorizer check, in both sharing modes.
    • dbauthz cases including the disabled guards, and the chat_project:share negation in the roles test.

The generated TypeScript changes only add the new scope value.

Generated by Coder Agents.

@github-actions

github-actions Bot commented Oct 7, 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.

@f0ssel
f0ssel requested a review from jaaydenh October 7, 2026 16:34
@f0ssel
f0ssel marked this pull request as ready for review October 7, 2026 16:34
@f0ssel
f0ssel requested a review from nickvigilante as a code owner October 7, 2026 16:34
@f0ssel

f0ssel commented Oct 7, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review

This comment has been minimized.

coder-agents-review[bot]

This comment was marked as outdated.

@f0ssel f0ssel changed the title feat(coderd): add chat project sharing database layer feat: add chat project sharing database layer Oct 7, 2026
@f0ssel

f0ssel commented Oct 7, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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 as chatd with site-wide memory permissions, and the only gate is ChatProjectUsableBy in #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: UpdateChatProjectACLByID authorizes with GetChatProjectByID, so it is protected from lost updates only when the caller has already run GetChatProjectByIDForUpdate in 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.

Comment thread coderd/database/modelmethods.go Resolved
Comment thread coderd/rbac/roles.go Resolved
Comment thread codersdk/deployment.go Resolved
@f0ssel

f0ssel commented Oct 7, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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 as chatd with site-wide memory permissions, so ChatProjectUsableBy in #30439 is the only check on sharee agents.

🤖 This review was automatically generated with Coder Agents.

Comment thread coderd/database/querier_test.go Resolved
Comment thread docs/ai-coder/agents/chat-sharing.md Resolved
Comment thread docs/ai-coder/agents/chat-sharing.md Resolved
Comment thread codersdk/deployment.go Resolved
@f0ssel

f0ssel commented Oct 7, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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 as chatd with site-wide memory permissions, so ChatProjectUsableBy in #30439 is the only check on sharee agents.
  • coderd/database/queries/chats.sql:1847: SyncAgentChatsContextMCPResources locks an agent's chats in id order, while chatstate.SetFamilyArchived locks 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.

Comment thread coderd/database/querier_test.go Resolved
Comment thread coderd/database/querier_test.go Resolved
Comment thread coderd/database/querier_test.go Resolved
@f0ssel

f0ssel commented Oct 7, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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 as chatd with site-wide memory permissions, so ChatProjectUsableBy in #30439 is the only check on sharee agents.
  • coderd/database/queries/chats.sql:1847: SyncAgentChatsContextMCPResources locks chats in id order, which conflicts with root-first family locks.
  • coderd/database/querier_test.go:2482: the pg_stat_activity ... wait_event_type = 'Lock' poll now has five copies across the repo and no dbtestutil helper, and the copies already differ (two exclude pg_backend_pid()).

🤖 This review was automatically generated with Coder Agents.

Comment thread coderd/database/chatprojects.go Resolved
Comment thread coderd/database/chatprojects.go Resolved
Comment thread coderd/database/chatprojects.go Resolved
Comment thread coderd/database/querier_test.go Resolved
@f0ssel

f0ssel commented Oct 7, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

…acl-db

# Conflicts:
#	coderd/database/check_constraint.go
#	coderd/rbac/roles.go
#	docs/reference/api/schemas.md
@f0ssel

f0ssel commented Oct 12, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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, the root_chat_id branch 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: deleteChatProjectTx calls store.InTx(..., nil), which uses the server's default isolation, while the contract here requires READ COMMITTED. On a server with default_transaction_isolation = 'repeatable read', steps 4 and 5 miss sub-chats committed during the step 3 wait.
  • coderd/database/queries/chats.sql:2572 (family-agnostic DeleteOldChats), 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.

Comment thread coderd/database/dbpurge/dbpurge.go Resolved
@f0ssel

f0ssel commented Oct 12, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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_id for sub-chats (db2sdk.Chat), and ChatsPanel.tsx:328 now 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 project FOR UPDATE; it is now FOR 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.

Comment thread coderd/database/querier_test.go Resolved
Comment thread coderd/database/dbpurge/dbpurge.go Resolved
Comment thread coderd/database/querier_test.go Resolved
@f0ssel

f0ssel commented Oct 12, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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 TRIGGER holds SHARE ROW EXCLUSIVE on chats until 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 ExtractChatParam returns 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.

Comment thread coderd/httpmw/chatparam.go Resolved
Comment thread coderd/httpmw/chatparam.go Resolved
Comment thread coderd/httpmw/chatparam.go Resolved
@f0ssel

f0ssel commented Oct 12, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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.

@f0ssel

f0ssel commented Oct 12, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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.

@f0ssel

f0ssel commented Oct 13, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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, and 000620. A trial merge conflicts in dbauthz.go, querymetrics.go, dbmock.go, and querier.go, and this migration needs renumbering to 000621 before merge.
  • CRF-222: after the rebase, main's authorizeChatProjectInstructions (#30368) authorizes project.RBACObject(), so this PR's ACL also governs project instructions: read sharees can read them and update sharees 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
@f0ssel

f0ssel commented Oct 13, 2026

Copy link
Copy Markdown
Member Author

/coder-agents-review

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

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

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 gen at df6d640 leaves git status clean, so dump.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's authorizeChatProjectInstructions (dbauthz.go:1795) authorize project.RBACObject(), which drops both ACLs under --disable-chat-sharing. The AsSystemRestricted reads in those handlers run only after a caller-subject check on the same object. chatd's resolveProjectInstructions reads only the project already set on the chat. The CRF-222 note stands: read sharees can read instructions and update sharees 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.

@f0ssel
f0ssel requested a review from mafredri October 13, 2026 13:59
Comment thread cli/testdata/coder_server_--help.golden Resolved
…acl-db

Renumbers the migration to 000623 and clarifies the --disable-chat-sharing
help text.
@f0ssel
f0ssel merged commit 2b9fad8 into main Oct 14, 2026
52 of 54 checks passed
@f0ssel
f0ssel deleted the f0ssel/chat-projects-acl-db branch October 14, 2026 14:42
@github-actions github-actions Bot locked and limited conversation to collaborators Oct 14, 2026
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