feat(copilot): add copilot_chat_messages table with dual-write rollout by waleedlatif1 · Pull Request #4726 · simstudioai/sim

waleedlatif1 · 2026-05-22T17:19:16Z

Summary

Introduces dedicated copilot_chat_messages table to replace the JSONB messages array on copilot_chats. The JSONB column is preserved indefinitely as the source of truth during rollout — no breaking schema change.
Migration 0212_cynical_jack_murdock.sql runs an inline jsonb_array_elements ... ON CONFLICT DO NOTHING backfill. bun run db:migrate does everything; no scripts required.
Follows existing migration precedent (0091_backfill_user_stats.sql, 0192_invitation_unification.sql): multi-statement with gen_random_uuid(), FK with CASCADE, inline INSERT ... SELECT ... ON CONFLICT DO NOTHING.
Dual-write helpers (lib/copilot/chat/messages-dual-write.ts) wired into every JSONB-mutation callsite (post, terminal-state, inbox, fork, update-messages, superuser-import). Best-effort — errors logged, never thrown.
Catch-up sweep on server boot closes the rolling-deploy gap. Bounded 7-day window, idempotent.

Indexes

UNIQUE (chat_id, message_id) — idempotent appends, dedup streaming retries
(chat_id, created_at, id) WHERE deleted_at IS NULL — transcript load, last-N
(chat_id, stream_id) WHERE stream_id IS NOT NULL — stream replay/resume

Type of Change

New feature

Testing

Tested manually. Lint passes.

Checklist

Code follows project style guidelines
Self-reviewed my changes
Tests added/updated and passing
No new warnings introduced
I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)

Introduces a dedicated `copilot_chat_messages` table to replace the JSONB messages array on `copilot_chats`. The JSONB column is preserved as the source of truth during rollout, with dual-writes capturing every new message in both places. Schema (additive only): - New `copilot_chat_messages` table with FK to `copilot_chats` (ON DELETE CASCADE), unique (chat_id, message_id), partial indexes for hot reads - Migration runs an inline `INSERT ... SELECT jsonb_array_elements ...` backfill, idempotent via ON CONFLICT — self-hosters need no scripts Dual-write helper: - `lib/copilot/chat/messages-dual-write.ts` — best-effort append + replace helpers, errors logged but not thrown so JSONB write remains canonical Dual-write callsites (every place that mutates copilot_chats.messages): - `lib/copilot/chat/post.ts` — user message append - `lib/copilot/chat/terminal-state.ts` — assistant message append (writes after the FOR UPDATE transaction commits) - `lib/mothership/inbox/executor.ts` — inbox-derived message persistence - `app/api/mothership/chats/[chatId]/fork/route.ts` — forked transcript - `app/api/copilot/chat/update-messages/route.ts` — snapshot replace Catch-up sweep: - `lib/copilot/chat/messages-catchup.ts` — bounded 7-day sweep on server boot to close the rolling-deploy window where old code may write to JSONB before the new dual-write code is live everywhere

…on conflict

…mestamps Production data review (580,300 messages across all history) confirms every message in copilot_chats.messages has id, role, and timestamp set — making the previous fallback handling unreachable. Also corrects the migration's timestamp source: the field is `timestamp` (ISO 8601), not `createdAt`, which was always null and silently triggered the synthesized ordinal-offset fallback. - messages-dual-write: typed as PersistedMessage[], no defensive guards - migration backfill: pulls real `timestamp` instead of always falling back to chat.created_at + ordinal microseconds - catch-up sweep: same simplification

…rom update-messages

vercel · 2026-05-22T17:19:22Z

The latest updates on your projects. Learn more about Vercel for GitHub.

1 Skipped Deployment

Project	Deployment	Actions	Updated (UTC)
docs	Skipped		May 22, 2026 7:09pm

cursor · 2026-05-22T17:19:27Z

PR Summary

Medium Risk
Introduces a new persistence path and background backfill for copilot chat messages, touching multiple write callsites and adding a new table/migration; issues could cause partial data duplication or missing rows in the new table (legacy JSONB remains canonical).

Overview
Adds a new copilot_chat_messages table (with indexes + FK) and a migration that backfills from copilot_chats.messages JSONB via jsonb_array_elements with idempotent ON CONFLICT behavior.

Wires best-effort dual-write into existing chat message mutation paths: snapshot saves now replaceCopilotChatMessages, while streaming/append flows appendCopilotChatMessages (including mothership fork/import and inbox execution) and propagate model/streamId metadata.

Adds a non-blocking, on-boot 7-day catch-up sweep to close rolling-deploy gaps, plus updates/tests to cover the new dual-write helpers and adjusted DB .returning() behavior in update-messages route tests.

^{Reviewed by Cursor Bugbot for commit b7d172b. Configure here.}

greptile-apps · 2026-05-22T17:24:32Z

Greptile Summary

This PR introduces a dedicated copilot_chat_messages table to normalize the JSONB messages array currently stored on copilot_chats, using a dual-write rollout strategy where the existing JSONB column remains the source of truth throughout the transition.

Schema & migration: 0212_cynical_jack_murdock.sql creates the table with appropriate indexes (unique on chat_id/message_id, partial indexes for transcript loads and stream resume) and runs an inline backfill from the existing JSONB column.
Dual-write helpers: messages-dual-write.ts exposes appendCopilotChatMessages and replaceCopilotChatMessages, both fully best-effort (errors are caught and logged), wired into all JSONB-mutation callsites (post.ts, terminal-state.ts, inbox/executor.ts, fork, update-messages, import-workflow).
Catch-up sweep: messages-catchup.ts runs once per server boot to close the rolling-deploy gap, bounded to 7 days of activity and idempotent via ON CONFLICT DO NOTHING.

Confidence Score: 5/5

Safe to merge — the dual-write is fully best-effort, the JSONB column remains the source of truth, and the migration backfill is idempotent.

All callsites correctly gate on the returned row before writing to the normalized table, preventing FK-violation noise. The catch-up sweep is fire-and-forget and bounded to 7 days. The transaction patterns in terminal-state.ts and replaceCopilotChatMessages are sound. No breaking schema changes, and all previously flagged gaps have been addressed.

No files require special attention.

Important Files Changed

Filename	Overview
apps/sim/lib/copilot/chat/messages-dual-write.ts	New dual-write helpers; `appendCopilotChatMessages` uses `ON CONFLICT DO UPDATE` to keep streaming content fresh, `replaceCopilotChatMessages` runs a transactional delete+upsert for full-snapshot saves. Both are best-effort with proper error handling.
apps/sim/lib/copilot/chat/messages-catchup.ts	Fire-and-forget boot-time sweep that backfills the last 7 days of JSONB messages into the normalized table via `ON CONFLICT DO NOTHING`. Idempotent and bounded; runs in the background so boot is not blocked.
packages/db/migrations/0212_cynical_jack_murdock.sql	Creates `copilot_chat_messages` with appropriate NOT NULL constraints, FK with CASCADE, three targeted indexes, and an inline `INSERT ... SELECT ... ON CONFLICT DO NOTHING` backfill over the full `copilot_chats` history.
packages/db/schema.ts	New `copilotChatMessages` table definition mirrors the SQL migration exactly, with unique index and two partial indexes defined inline.
apps/sim/lib/copilot/chat/terminal-state.ts	Adds `model` to the in-transaction SELECT projection, captures `chatModel` via closure, and appends the assistant message to the normalized table after the transaction commits. The `appendedAssistantMessage` closure variable pattern is correct.
apps/sim/lib/copilot/chat/post.ts	Adds `model` to the RETURNING clause and dual-writes the user message after the JSONB update succeeds, gated on the returned row. Uses `streamId: userMessageId` to group the user message with its turn.
apps/sim/lib/mothership/inbox/executor.ts	Adds `.returning({ model })` to the JSONB update and dual-writes both user and assistant messages, gated on the returned row to avoid FK violations on missing chats.
apps/sim/app/api/copilot/chat/update-messages/route.ts	Adds `.returning({ model })` and gates `replaceCopilotChatMessages` on the returned row, preventing spurious FK-violation logs when the target chat doesn't exist.
apps/sim/app/api/mothership/chats/[chatId]/fork/route.ts	Dual-writes forked messages immediately after the new chat row is inserted, passing the parent chat's model. Best-effort; does not block the fork response.
apps/sim/app/api/superuser/import-workflow/route.ts	Adds `.returning({ id })` to the chat INSERT and dual-writes imported messages, guarded by existence and non-empty message array checks.
apps/sim/instrumentation-node.ts	Registers the catch-up sweep as a fire-and-forget task on server boot, with error catching so a sweep failure never affects startup.
apps/sim/lib/copilot/chat/messages-dual-write.test.ts	New test suite covering append (no-op on empty, row shape, ordering, options, conflict target, error-swallowing) and replace (empty snapshot, delete+upsert, model propagation, error-swallowing).

_{Reviews (3): Last reviewed commit: "fix(copilot): thread chatModel through p..." | Re-trigger Greptile}

…ions, pass chat model from inbox

waleedlatif1 · 2026-05-22T17:33:15Z

@greptile

waleedlatif1 · 2026-05-22T17:33:18Z

@cursor review

Cover row shape, ordering, options propagation, ON CONFLICT semantics, and error swallowing for appendCopilotChatMessages / replaceCopilotChatMessages. Also adds copilotChatMessages to the central schemaMock so the imports resolve. Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

The user-message append path (post.ts) and assistant-finalize path (terminal-state.ts) were calling appendCopilotChatMessages without chatModel, leaving the model column NULL on every interactive turn. post.ts now projects model from .returning(...); terminal-state.ts adds model to the in-tx SELECT and surfaces it through a closure. Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

waleedlatif1 · 2026-05-22T18:08:38Z

@greptile

waleedlatif1 · 2026-05-22T18:08:42Z

@cursor review

cursor

✅ Bugbot reviewed your changes and found no new issues!

Comment @cursor review or bugbot run to trigger another review on this PR

^{Reviewed by Cursor Bugbot for commit b7d172b. Configure here.}

…cript The boot-time messages catch-up ran on every replica × every deploy, burning ~210K row-touches/day on prod to solve a problem (dual-write transient drift) that doesn't affect users while JSONB is canonical. Replace it with a one-shot bun script that can be run manually before the eventual R+1 read cutover, or after a known outage. Default scope is the full table; --since='<interval>' narrows the window. bun apps/sim/scripts/copilot-messages-reconcile.ts bun apps/sim/scripts/copilot-messages-reconcile.ts --since='7 days' Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

waleedlatif1 added 4 commits May 22, 2026 10:16

fix(copilot): wire dual-write into superuser import + preserve model …

9d28dec

…on conflict

fix(copilot): preserve stream_id on conflict + propagate chat model f…

07f41f9

…rom update-messages

vercel Bot deployed to Preview May 22, 2026 17:21 View deployment

cursor Bot reviewed May 22, 2026

View reviewed changes

Comment thread apps/sim/lib/copilot/chat/messages-dual-write.ts Outdated

Comment thread packages/db/migrations/0212_cynical_jack_murdock.sql

greptile-apps Bot reviewed May 22, 2026

View reviewed changes

Comment thread packages/db/migrations/0212_cynical_jack_murdock.sql

Comment thread apps/sim/app/api/copilot/chat/update-messages/route.ts Outdated

Comment thread apps/sim/lib/mothership/inbox/executor.ts Outdated

test(copilot): support .returning() chain in update-messages mocks

4fa96e3

vercel Bot temporarily deployed to Preview May 22, 2026 17:27 Inactive

fix(copilot): preserve stream_id on snapshot replace, guard FK violat…

8557d13

…ions, pass chat model from inbox

vercel Bot temporarily deployed to Preview May 22, 2026 17:30 Inactive

cursor Bot reviewed May 22, 2026

View reviewed changes

Comment thread apps/sim/lib/copilot/chat/post.ts

greptile-apps Bot reviewed May 22, 2026

View reviewed changes

Comment thread apps/sim/lib/copilot/chat/terminal-state.ts

vercel Bot temporarily deployed to Preview May 22, 2026 17:53 Inactive

vercel Bot temporarily deployed to Preview May 22, 2026 18:04 Inactive

cursor Bot reviewed May 22, 2026

View reviewed changes

vercel Bot temporarily deployed to Preview May 22, 2026 19:09 Inactive

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat(copilot): add copilot_chat_messages table with dual-write rollout#4726

feat(copilot): add copilot_chat_messages table with dual-write rollout#4726
waleedlatif1 wants to merge 9 commits into
stagingfrom
waleedlatif1/copilot-chat-messages-table

waleedlatif1 commented May 22, 2026

Uh oh!

vercel Bot commented May 22, 2026 •

edited

Loading

Uh oh!

cursor Bot commented May 22, 2026 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

greptile-apps Bot commented May 22, 2026 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

Uh oh!

waleedlatif1 commented May 22, 2026

Uh oh!

waleedlatif1 commented May 22, 2026

Uh oh!

Uh oh!

Uh oh!

waleedlatif1 commented May 22, 2026

Uh oh!

waleedlatif1 commented May 22, 2026

Uh oh!

cursor Bot left a comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Conversation

waleedlatif1 commented May 22, 2026

Summary

Indexes

Type of Change

Testing

Checklist

Uh oh!

vercel Bot commented May 22, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

cursor Bot commented May 22, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

PR Summary

Uh oh!

Uh oh!

Uh oh!

greptile-apps Bot commented May 22, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Greptile Summary

Confidence Score: 5/5

Important Files Changed

Uh oh!

Uh oh!

Uh oh!

Uh oh!

waleedlatif1 commented May 22, 2026

Uh oh!

waleedlatif1 commented May 22, 2026

Uh oh!

Uh oh!

Uh oh!

waleedlatif1 commented May 22, 2026

Uh oh!

waleedlatif1 commented May 22, 2026

Uh oh!

cursor Bot left a comment

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

vercel Bot commented May 22, 2026 •

edited

Loading

cursor Bot commented May 22, 2026 •

edited

Loading

greptile-apps Bot commented May 22, 2026 •

edited

Loading