Skip to content

feat(coderd): key chat diff statuses by ref - #29081

Merged
DanielleMaywood merged 10 commits into
mainfrom
feat/chat-diff-ref-key
Oct 1, 2026
Merged

DanielleMaywood merged 10 commits into
mainfrom
feat/chat-diff-ref-key

Conversation

@DanielleMaywood

@DanielleMaywood DanielleMaywood commented Sep 8, 2026 •

Copy link
Copy Markdown
Collaborator

Part 1 of 6.

chat_diff_statuses had one row per chat, so a chat could only track one PR, and switching branches overwrote the old branch's PR data. The primary key is now (chat_id, git_remote_origin, git_branch), so each ref the agent reports gets its own row. The API still returns a single diff_status: the first row of the chat's statuses, ordered by updated_at, newest report first. The primary ref changes only when the agent reports a ref: refreshes, claims, backoffs and URL writes do not advance updated_at, so they cannot reorder the primary.

The gitsync worker now claims, backs off and clears rows by the full key, not the whole chat. When the diff GET discovers a PR URL, it updates the existing ref row and no longer inserts one. Telemetry picks one row per chat.

NOTE: Migration 000606 swaps the primary key under an ACCESS EXCLUSIVE lock. The table is small. Existing rows already fit the new key, so no data is rewritten. The down migration keeps only the primary row per chat. Stop old coderd instances before migrating: their ON CONFLICT (chat_id) upserts fail once the old key is gone.

Generated by Coder Agents.

@DanielleMaywood DanielleMaywood added the experimental Changes that might not necessarily be merged, until its approved to proceed with. label Sep 8, 2026
@DanielleMaywood

Copy link
Copy Markdown
Collaborator Author

Stack: #29081 (database) -> #29082 (API) -> #29083 (git panel). Replaces the closed single PR #29001. Merge in order.

Comment thread coderd/database/queries/chats.sql Resolved
Comment thread coderd/database/queries/chats.sql Resolved
Comment thread coderd/x/chatd/chatd.go Resolved
Comment thread coderd/exp_chats.go Resolved
Comment thread coderd/exp_chats.go Resolved
Comment thread coderd/exp_chats.go Resolved
@DanielleMaywood

Copy link
Copy Markdown
Collaborator Author

@codex review

@chatgpt-codex-connector

chatgpt-codex-connector Bot commented Sep 11, 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-09-23T09:00:21.870859Z dbf88c2 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: 1a7076c4bd

ℹ️ 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/queries/chats.sql Resolved
@DanielleMaywood
DanielleMaywood force-pushed the feat/chat-diff-ref-key branch 3 times, most recently from e370f06 to caa4caa Compare September 15, 2026 12:48
@DanielleMaywood

Copy link
Copy Markdown
Collaborator 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: caa4caa84c

ℹ️ 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/queries/chats.sql Resolved
@DanielleMaywood

Copy link
Copy Markdown
Collaborator Author

@codex review

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. Bravo.

Reviewed commit: c6eef7d0cd

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

@DanielleMaywood
DanielleMaywood removed this pull request from stack #29089 September 18, 2026 10:48
@DanielleMaywood DanielleMaywood removed the experimental Changes that might not necessarily be merged, until its approved to proceed with. label Sep 21, 2026
@DanielleMaywood
DanielleMaywood marked this pull request as ready for review September 21, 2026 10:59

@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: 50c7b30cd0

ℹ️ 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/exp_chats.go Resolved
ADD COLUMN reported_at timestamp with time zone DEFAULT now() NOT NULL;

UPDATE chat_diff_statuses
SET reported_at = updated_at;

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.

maybe it will be apparent once I scroll some more, but what is going to be the diff between those columns?

Copy link
Copy Markdown
Collaborator Author

Choose a reason for hiding this comment

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

So the chat_diff_statuses column can be updated separately from a PR being reported as existing. Name is maybe a bit shit. updated_at is our generic update thing. Happy to change this if too odd

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.

I am using the Linear agent to discuss because I did find it odd and this is what tells me:

updated_at used to advance during background PR refreshes, so it could not safely order the primary ref. The change removes that update. After that, both timestamps advance only in UpsertChatDiffStatusReference, and both are initialized from the same value for existing rows.

So reported_at only makes sense if we want to preserve updated_at as a generic “last changed” timestamp and later allow non-report mutations to advance it again. As written, it’s redundant; ordering by updated_at would produce the same result.

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.

Not sure what model is running underneath, but maybe it's made use of further in the stack and it just lacks the context

Copy link
Copy Markdown
Collaborator Author

Choose a reason for hiding this comment

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

Your Linear agent's summary is right about the history but misses the second half of the design. Here is the full reasoning (LLM-generated, Coder Agents):

reported_at exists so the primary ref (row 0 of the chat's diff statuses) changes only when the agent reports a ref. Ordering by updated_at breaks in two ways:

  • Refresh reordering: each ref refreshes on its own schedule, so the last one refreshed became primary. A current branch with no PR writes nothing on refresh, while an old branch with a PR keeps getting bumped. The chat then shows the old PR indefinitely.
  • Discovery race: a slow diff GET that finishes after a newer report could move the old row back to primary. UpdateChatDiffStatusReferenceURL touches only the URL, and DiscoveryWriteDoesNotReorderPrimary covers this case.

The result is that only the agent-report upsert sets reported_at. Refreshes, claims, backoffs and URL writes cannot reorder the primary. You are right that today both columns advance together in practice, and that updated_at ordering would give the same result right now. The separation is the invariant: updated_at stays free to advance on any future non-report mutation without silently changing which PR the chat displays.

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.

the migration to modify the keys looks safe, it's not a big table on df

Rows 6,942 live, 515 dead; about 75–140 new rows a day
Table data 15.2 MB, but only about 1.7 MB is live data (242 B per row on average)
Indexes 12.8 MB, of which 9.7 MB is the stale_at index

Copy link
Copy Markdown
Collaborator Author

Choose a reason for hiding this comment

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

We agree with the analysis and removed reported_at in 53bc0cd. The migration no longer adds the column, and the ordering now reads updated_at.

What carries the invariant instead: this PR also removes the updated_at = NOW() that main's worker refresh set, so updated_at now advances only when the agent reports a ref. Refreshes, claims, backoffs, PR clears and URL writes cannot reorder the primary. The rule to keep for future queries on this table: do not add updated_at to a write that must not change the primary ref.

This reply was generated by Coder Agents.

@linear-code

linear-code Bot commented Sep 23, 2026

Copy link
Copy Markdown

CODAGT-990

// Authorize read on the parent chat.
_, err := q.GetChatByID(ctx, chatID)
if err != nil {
return nil, err

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.

previously we were returning an empty object, but now it's nil, I guess it makes some difference for consumers, and ChatStusesByChatIDs still returns an empty array instead of nil

Copy link
Copy Markdown
Collaborator Author

Choose a reason for hiding this comment

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

Returning nil here does not change behavior. Only the error path returns nil, and callers do not read the result when err is not nil. GetChatDiffStatusesByChatIDs also returns nil on its error path. Its []database.ChatDiffStatus{} is the success result for an empty ID list, which main already had. When there are no rows, both queries return nil, because sqlc does not emit empty slices. All callers check len(), and diff_statuses is omitempty, so nil and empty give the same JSON. return nil, err is also the usual form in dbauthz.go.

This reply was generated by Coder Agents.

Comment thread coderd/exp_chats.go
case err == nil:
diffStatus = &status
case !xerrors.Is(err, sql.ErrNoRows):
diffStatuses, err := api.Database.GetChatDiffStatusesByChatID(ctx, chat.ID)

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.

doesn't this break current fe?

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.

ok, nvm 1562 handles it

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.

probably should remove 1562-1564 if frontend handles arrays going forward

Copy link
Copy Markdown
Collaborator Author

Choose a reason for hiding this comment

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

We need to keep these lines. In this PR, diff_status is the only diff field in the API. If we remove them, the chat page does not show the PR, and TestGetChatDiffStatus fails. #29082 adds diff_statuses and marks diff_status as deprecated, but still fills it. The chat list, child chats, and watch events also fill it, and at the top of the stack the frontend still reads it from watch events. Removing the field is a breaking change to /api/v2, and multiple-PR support does not need it.

This reply was generated by Coder Agents.

@DanielleMaywood
DanielleMaywood removed this pull request from stack #29551 September 24, 2026 08:36
@DanielleMaywood
DanielleMaywood force-pushed the feat/chat-diff-ref-key branch 3 times, most recently from 2668b37 to 8ccd48b Compare September 25, 2026 09:15
@DanielleMaywood
DanielleMaywood force-pushed the feat/chat-diff-ref-key branch 3 times, most recently from 9f3ded7 to 082e0ac Compare September 30, 2026 14:37

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

Approved after schema simplification but imho the regex in the migration needs a test.

-- Strip it with the same pattern as originCredentials in coderd/x/gitsync.
-- Each chat still has one row here, so this cannot create duplicate keys.
UPDATE chat_diff_statuses
SET git_remote_origin = regexp_replace(git_remote_origin, '^(https?://)[^/]*@', '\1')

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.

Imho this type of migration with regex needs a migration test to make sure we're handling edge cases correctly. The repo has prior art so should be doable for a bot.

Copy link
Copy Markdown
Collaborator Author

Choose a reason for hiding this comment

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

Added in ffb7a98. testMigration000606ChatDiffStatusOriginCredentials is a table test and a step in TestMigrationChain. It adds one chat for each case at 605, applies 606, and checks each origin.

  • The migration strips: a token, a user and password, a password with an @, http://, a port, no path, and an @ in the path after the credentials.
  • The migration keeps: an origin without credentials, an @ only in the path, scp-style SSH, ssh://git@..., and an empty origin.

When I changed the regex to [^/@]*@, the test failed on the case with @ in the password.

This reply was generated by Coder Agents.

chat_diff_statuses held one row per chat, so a chat could only track
one pull request at a time and a branch switch carried the previous
PR's URL onto the new branch's data.

Re-key the table to (chat_id, git_remote_origin, git_branch) so every
ref the agent reports keeps its own row. Existing rows satisfy the new
key without a rewrite: both columns are NOT NULL with '' defaults and
the old key capped rows at one per chat.

The worker claims stale rows by the full key. Retained refs keep
polling on their own schedule, so no pause state or resume protocol is
needed. Telemetry reports one diff row per chat via DISTINCT ON.

The diff GET's discovery write now keys its upsert to the stored ref.
Under the old single-row key an empty ref key was safe; under the
composite key it would create a stray row the worker ignores.

Old coderd instances must finish before this migration runs: their
ON CONFLICT (chat_id) writes have no arbiter under the new key.
The primary ref is the most recently reported one, but its
ordering column was updated_at, the generic mutation time. A
discovery GET that resolves a changed PR URL late could bump the
old ref's updated_at past a newer report and steal the primary
back.

The table now tracks reported_at, set only by reports. Primary
ordering, the chat-list tiebreak, and telemetry dedup read it.
Refreshes, claims, backoffs, and URL writes cannot reorder the
primary anymore. The discovery write uses a dedicated query that
never touches ordering columns.
A remote URL can contain a token, such as
https://<token>@github.com/o/r.git. coder gitaskpass sends that URL
as it is, and the server stored it in
chat_diff_statuses.git_remote_origin.

MarkStale now removes the user and password from HTTP(S) remotes
before it stores them. Migration 000600 removes them from existing
rows before it changes the primary key. SSH remotes do not change.
@DanielleMaywood
DanielleMaywood merged commit 63a95d8 into main Oct 1, 2026
29 checks passed
@DanielleMaywood
DanielleMaywood deleted the feat/chat-diff-ref-key branch October 1, 2026 09:59
@github-actions github-actions Bot locked and limited conversation to collaborators Oct 1, 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