Collaboration

Enable multi-user system with the Supabase backend.

Coding Plan

Summary

Secure the multi-user write paths first: enforce owner-or-participant membership on saveChat/createMessage and stop saveChat from overwriting the chat owner.
Formalize a shared Participant type and reuse it across actions and UI.
Surface the already-built ChatShare invite/manage dialog through a stable entry point and a documented ENABLE_SHARE flag.
Confirm the Supabase Realtime subscription observes the Drizzle-managed data so collaborator changes propagate live, without adding new collaboration libraries.
Add an automated RLS authorization audit suite that runs in CI against a disposable Supabase environment and fails when the access matrix changes unexpectedly.

Design Choices

Design Choice 1: Does the Supabase Realtime project ("QCX-BACKEND") observe the same physical Postgres instance that Drizzle writes to through POSTGRES_URL?

Options Considered:

Assume a single shared database. Realtime picks up Drizzle writes directly. No dual-write for chat data is needed.
Add explicit dual-write of messages/chat_participants from Drizzle actions into QCX-BACKEND, mirroring the existing user dual-write in the Clerk webhook.

Chosen Option: 1

Rationale: The documentation and the existing single-writer pattern for chat data indicate one shared Postgres instance. Confirm the connection targets during Phase 3 rather than build a fragile dual-write path.

Design Choice 2: How should chat write access be gated for non-owner participants?

Options Considered:

Allow any participant (owner or collaborator) to append messages. Keep destructive actions (delete, rename, visibility) owner-only.
Add a separate read-only "viewer" role and branch write permission on role.

Chosen Option: 1

Rationale: This option matches the existing single collaborator role and the dialog text "view and append to this chat." It avoids adding a new role model that no code consumes.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Secure and correct the multi-user write paths

This phase makes chat and message writes safe for multiple users. Today any authenticated caller who supplies a chatId can append messages, and a collaborator's message save overwrites the chat owner. This phase enforces membership on writes and preserves the original owner. Do this first because collaboration is unsafe without it.

Task 1: Enforce chat membership on message writes

Prevent non-participants from appending to a chat and stop ownership hijacking.

Update lib/actions/chat-db.ts so saveChat and createMessage verify that the current user is the chat owner or a chatParticipants row for that chatId, before any insert or update.
Reuse the existing owner-or-participant EXISTS pattern already present in getChat for the membership check.
If the caller is not a member and the chat already exists, reject the write and return null. Do not silently insert.
Keep new-chat creation (no existing chatId) working for the creating user.

Task 2: Preserve the original chat owner on save

Stop saveChat from overwriting chats.userId when a collaborator sends a turn.

Update lib/actions/chat.ts saveChat so that, for an existing chat, it does not change chats.userId. Only set userId when the chat is being created.
Continue to stamp each message with the actual sender's userId (this behavior is already correct in app/actions.tsx and should be retained).
Confirm deleteChat and updateChat remain owner-only (no change needed, verify only).

Task 3: Formalize the participant type

Replace the ad-hoc any[] participant shape with a typed model that the UI and actions share.

Add a Participant type in lib/types/index.ts with id, role, userId, email, firstName, lastName, and avatarUrl.
Update listParticipants in lib/actions/chat.ts to return Participant[].
Extend the Chat type with an optional participants?: Participant[] field for surfaces that need it.

🤖 Prompt for AI agents

Implement Phase 1 to secure and correct the multi-user write paths for chats and
messages. The goal is to prevent non-participants from writing to a chat and to
stop a collaborator's write from overwriting the chat owner.

1. In `lib/actions/chat-db.ts`, update `saveChat` and `createMessage`. Before
any insert or update, verify that the current user is the chat owner or has a
`chatParticipants` row for that `chatId`. Reuse the existing
owner-or-participant `EXISTS` pattern already present in `getChat`. If the
caller is not a member and the chat already exists, reject the write and return
`null`. Do not silently insert in this case. Keep new-chat creation (no existing
`chatId`) working for the creating user.

2. In `lib/actions/chat.ts`, update `saveChat` so that, for an existing chat, it
does not modify `chats.userId`. Only set `userId` when creating a new chat. Keep
the existing behavior in `app/actions.tsx` that stamps each message with the
actual sender's `userId`. Verify (without changing) that `deleteChat` and
`updateChat` remain owner-only.

3. In `lib/types/index.ts`, add a `Participant` type with fields `id`, `role`,
`userId`, `email`, `firstName`, `lastName`, and `avatarUrl`. Update
`listParticipants` in `lib/actions/chat.ts` to return `Participant[]`. Extend
the `Chat` type with an optional `participants?: Participant[]` field.

Phase 2: Make the collaboration UI reachable

This phase exposes the invite-and-manage-collaborators experience. The ChatShare dialog already invites and removes participants, but it is gated behind an unset ENABLE_SHARE flag and has no stable entry point. This phase gives users a reliable way to open it and consumes the typed participant model.

Task 1: Add a collaboration entry point in the chat surface

Give every open chat a visible control to open the invite/manage dialog.

Render ChatShare from a stable location tied to the active chat, for example components/header.tsx or components/chat-panel.tsx, passing the current chatId.
Remove the dependency on the first-message-only UserMessage path in app/actions.tsx as the sole trigger.
Show the control only when a chatId exists and the current user is the owner or a participant.

Task 2: Enable the sharing feature flag consistently

Make the feature discoverable by default instead of dark.

Add ENABLE_SHARE to .env.local.example with documentation, and default the collaboration control to visible where the ticket intends the feature to ship.
Ensure components/user-message.tsx and the new entry point read the same flag so behavior is consistent.

Task 3: Consume the typed participant model in the dialog

Align the UI with the formalized type from Task 1.3.

Update components/chat-share.tsx to type its participant state as Participant[] instead of any[].
Keep the invite-by-email/Clerk-ID and remove flows, which already call the participants REST API.
Display participant role (owner vs collaborator) so users can see who owns the chat.

🤖 Prompt for AI agents

Implement Phase 2 to make the collaboration UI reachable. The goal is to expose
the existing `ChatShare` invite/manage dialog through a stable entry point and
consistent feature flag, and to align the UI with the typed participant model.

1. Render `ChatShare` from a stable location tied to the active chat, for
example `components/header.tsx` or `components/chat-panel.tsx`, passing the
current `chatId`. Remove reliance on the first-message-only `UserMessage` path
in `app/actions.tsx` as the sole trigger. Show the control only when a `chatId`
exists and the current user is the owner or a participant.

2. Add `ENABLE_SHARE` to `.env.local.example` with documentation, and default
the collaboration control to visible where the feature is intended to ship.
Ensure `components/user-message.tsx` and the new entry point read the same flag
so behavior stays consistent.

3. Update `components/chat-share.tsx` to type its participant state as
`Participant[]` instead of `any[]`. Keep the existing invite-by-email/Clerk-ID
and remove flows, which already call the participants REST API. Display
participant `role` (owner vs collaborator) so users can see who owns the chat.

Phase 3: Live multi-user updates and automated authorization verification

This phase confirms and hardens the Realtime path so changes from one collaborator appear for others. The subscription already exists but only calls router.refresh(), and its target database was not confirmed. This phase also adds an automated, repeatable RLS policy audit that gates CI, because the RLS policies that protect multi-user access have no test coverage today.

Task 1: Confirm the Realtime database target

Verify that Realtime observes the same data Drizzle writes.

Confirm that NEXT_PUBLIC_SUPABASE_URL (used by lib/supabase/browser-client.ts) and POSTGRES_URL (used by lib/db/index.ts) point to the same Postgres instance.
If they do not, document the requirement to enable Realtime on the Drizzle database, or add a dual-write of messages/chat_participants mirroring the user dual-write in app/api/clerk/webhook/route.ts.

Task 2: Keep the Realtime subscription reliable for participants

Ensure collaborators gain and lose live updates as membership changes.

Verify the existing components/chat.tsx subscription (chat-realtime-${id} on messages and chat_participants) reloads chat state after invite, remove, and new-message events.
Confirm RLS on the QCX-BACKEND project allows a participant to receive messages/chat_participants changes for chats they belong to (the is_clerk_user + chat_participants policies described in the migrations).
Keep router.refresh() as the reconciliation mechanism for this ticket. Do not add a CRDT or presence library, which is out of scope.

Task 3: Build an automated RLS authorization audit suite

Create a repeatable, executable test suite that verifies the RLS policies which protect multi-user access. This task verifies authorization only. It does not replace the write-path checks in Phase 1, the participant UI in Phase 2, or the Realtime validation in Tasks 3.1-3.2.

Define an authorization access matrix for each protected table: chats, messages, chat_participants, and users. The app is single-tenant, so use these identity cases: chat owner, chat collaborator, unrelated authenticated user (non-participant), and anonymous. Treat "unrelated authenticated user" as the analog of the cross-tenant case, because no organization/tenant table exists.
For each table and identity, define the expected outcome for SELECT, INSERT, UPDATE, and DELETE, including both allowed rows and denied rows.
Add a test-suite directory (for example tests-rls/) that seeds isolated test identities and data. Create distinct public.users rows with mapped clerk_user_id values for owner, collaborator, and unrelated user, plus the chats, messages, and chat_participants rows needed to exercise each matrix cell.
Execute each query under the correct identity/JWT context. Connect directly to Postgres under a role that is subject to RLS (not the privileged app role), and set the JWT claims per identity with SET LOCAL request.jwt.claims so the policies observe the correct auth.uid()/clerk_id()/is_clerk_user() values. Include an anonymous (no-claims) context.
Implement the assertions with pgTAP run through supabase test db, or with a bun:test suite that matches the existing tests-unit/ convention and issues raw Postgres queries. Assert both allowed row visibility and denied row visibility for every matrix cell.
Run the RLS migrations (0003_clerk_auth_sync.sql, 0004_fix_clerk_id_function.sql, 0005_add_documents_rag.sql, and the base schema) against a disposable environment before the tests. Use a local Supabase stack (Supabase CLI) or a dedicated test project provisioned in CI, because no test database exists today.
Add a CI job (extend .github/workflows/) that provisions the disposable Supabase/Postgres environment, applies the migrations, seeds the identities, and runs the RLS suite. Fail the CI job when any matrix cell result changes unexpectedly, so the suite acts as a regression gate for authorization.

🤖 Prompt for AI agents

Implement Phase 3 to confirm and harden Realtime updates for multi-user
collaboration, and to add an automated RLS authorization audit that gates CI.
The goal is to make sure changes from one collaborator propagate live to others,
and to verify that RLS policies enforce correct authorization, without adding
new collaboration libraries.

1. Confirm that `NEXT_PUBLIC_SUPABASE_URL` (used by
`lib/supabase/browser-client.ts`) and `POSTGRES_URL` (used by `lib/db/index.ts`)
point to the same Postgres instance. If they do not point to the same instance,
document the requirement to enable Realtime on the Drizzle database, or add a
dual-write of `messages`/`chat_participants` mirroring the existing user
dual-write in `app/api/clerk/webhook/route.ts`.

2. Verify the existing `components/chat.tsx` subscription (channel
`chat-realtime-${id}` on `messages` and `chat_participants`) reloads chat state
after invite, remove, and new-message events. Confirm that RLS on the
QCX-BACKEND project allows a participant to receive
`messages`/`chat_participants` changes for chats they belong to, referencing the
`is_clerk_user` and `chat_participants` policies described in the migrations.
Keep `router.refresh()` as the reconciliation mechanism for this ticket. Do not
add a CRDT or presence library; this is out of scope.

3. Build an automated RLS authorization audit suite. This verifies authorization
only; it does not replace the write-path checks in Phase 1, the participant UI
in Phase 2, or the Realtime validation in steps 1-2.
- Define an access matrix for `chats`, `messages`, `chat_participants`, and
`users`, using identity cases: chat owner, chat collaborator, unrelated
authenticated user (non-participant), and anonymous, since the app is
single-tenant and has no organization/tenant table. Treat "unrelated
authenticated user" as the analog of the cross-tenant case.
- For each table and identity, define the expected outcome for `SELECT`,
`INSERT`, `UPDATE`, and `DELETE`, covering both allowed and denied rows.
- Add a test-suite directory (for example `tests-rls/`) that seeds isolated test
identities and data: distinct `public.users` rows with mapped `clerk_user_id`
values for owner, collaborator, and unrelated user, plus the `chats`,
`messages`, and `chat_participants` rows needed for each matrix cell.
- Execute each query under the correct identity/JWT context by connecting to
Postgres under a role subject to RLS (not the privileged app role) and setting
JWT claims per identity with `SET LOCAL request.jwt.claims`, so policies observe
the correct `auth.uid()`/`clerk_id()`/`is_clerk_user()` values. Include an
anonymous (no-claims) context.
- Implement assertions with pgTAP run through `supabase test db`, or with a
`bun:test` suite matching the existing `tests-unit/` convention that issues raw
Postgres queries. Assert both allowed and denied row visibility for every matrix
cell.
- Before running tests, apply the RLS migrations (`0003_clerk_auth_sync.sql`,
`0004_fix_clerk_id_function.sql`, `0005_add_documents_rag.sql`, and the base
schema) against a disposable environment, using a local Supabase stack (Supabase
CLI) or a dedicated test project provisioned in CI, since no test database
exists today.
- Add a CI job (extend `.github/workflows/`) that provisions the disposable
Supabase/Postgres environment, applies the migrations, seeds the identities, and
runs the RLS suite. Fail the CI job when any matrix cell result changes
unexpectedly, so the suite acts as a regression gate for authorization.

Research

The app is a Next.js project. It uses Clerk for identity. It uses a Drizzle-managed Postgres database (POSTGRES_URL) for chats, messages, and users. A separate Supabase project ("QCX-BACKEND") provides Realtime and RLS.

The chatParticipants table already backs collaboration. It has a role field with default value 'collaborator' and a unique (chatId, userId) constraint.

Server actions addParticipant, removeParticipant, and listParticipants exist in lib/actions/chat.ts. These actions are exposed through app/api/chats/[id]/participants/route.ts.

Read queries in lib/actions/chat-db.ts (getChat, getChatsPage) are participant-aware.

A ChatShare invite dialog exists in components/chat-share.tsx. components/chat.tsx subscribes to Supabase postgres_changes on messages and chat_participants and calls router.refresh().

The gaps are:

Message write paths (saveChat, createMessage) perform no membership check.
saveChat in lib/actions/chat.ts overwrites chats.userId with the current sender.
The ChatShare dialog is gated behind an unset ENABLE_SHARE flag and has no real entry point.
The participant shape is untyped (any[]).

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Implement Phase 1 to secure and correct the multi-user write paths for chats and
messages. The goal is to prevent non-participants from writing to a chat and to
stop a collaborator's write from overwriting the chat owner.

1. In `lib/actions/chat-db.ts`, update `saveChat` and `createMessage`. Before
any insert or update, verify that the current user is the chat owner or has a
`chatParticipants` row for that `chatId`. Reuse the existing
owner-or-participant `EXISTS` pattern already present in `getChat`. If the
caller is not a member and the chat already exists, reject the write and return
`null`. Do not silently insert in this case. Keep new-chat creation (no existing
`chatId`) working for the creating user.

2. In `lib/actions/chat.ts`, update `saveChat` so that, for an existing chat, it
does not modify `chats.userId`. Only set `userId` when creating a new chat. Keep
the existing behavior in `app/actions.tsx` that stamps each message with the
actual sender's `userId`. Verify (without changing) that `deleteChat` and
`updateChat` remain owner-only.

3. In `lib/types/index.ts`, add a `Participant` type with fields `id`, `role`,
`userId`, `email`, `firstName`, `lastName`, and `avatarUrl`. Update
`listParticipants` in `lib/actions/chat.ts` to return `Participant[]`. Extend
the `Chat` type with an optional `participants?: Participant[]` field.
===============================================================================

Task: 2

Implement Phase 2 to make the collaboration UI reachable. The goal is to expose
the existing `ChatShare` invite/manage dialog through a stable entry point and
consistent feature flag, and to align the UI with the typed participant model.

1. Render `ChatShare` from a stable location tied to the active chat, for
example `components/header.tsx` or `components/chat-panel.tsx`, passing the
current `chatId`. Remove reliance on the first-message-only `UserMessage` path
in `app/actions.tsx` as the sole trigger. Show the control only when a `chatId`
exists and the current user is the owner or a participant.

2. Add `ENABLE_SHARE` to `.env.local.example` with documentation, and default
the collaboration control to visible where the feature is intended to ship.
Ensure `components/user-message.tsx` and the new entry point read the same flag
so behavior stays consistent.

3. Update `components/chat-share.tsx` to type its participant state as
`Participant[]` instead of `any[]`. Keep the existing invite-by-email/Clerk-ID
and remove flows, which already call the participants REST API. Display
participant `role` (owner vs collaborator) so users can see who owns the chat.
===============================================================================

Task: 3

Implement Phase 3 to confirm and harden Realtime updates for multi-user
collaboration, and to add an automated RLS authorization audit that gates CI.
The goal is to make sure changes from one collaborator propagate live to others,
and to verify that RLS policies enforce correct authorization, without adding
new collaboration libraries.

1. Confirm that `NEXT_PUBLIC_SUPABASE_URL` (used by
`lib/supabase/browser-client.ts`) and `POSTGRES_URL` (used by `lib/db/index.ts`)
point to the same Postgres instance. If they do not point to the same instance,
document the requirement to enable Realtime on the Drizzle database, or add a
dual-write of `messages`/`chat_participants` mirroring the existing user
dual-write in `app/api/clerk/webhook/route.ts`.

2. Verify the existing `components/chat.tsx` subscription (channel
`chat-realtime-${id}` on `messages` and `chat_participants`) reloads chat state
after invite, remove, and new-message events. Confirm that RLS on the
QCX-BACKEND project allows a participant to receive
`messages`/`chat_participants` changes for chats they belong to, referencing the
`is_clerk_user` and `chat_participants` policies described in the migrations.
Keep `router.refresh()` as the reconciliation mechanism for this ticket. Do not
add a CRDT or presence library; this is out of scope.

3. Build an automated RLS authorization audit suite. This verifies authorization
only; it does not replace the write-path checks in Phase 1, the participant UI
in Phase 2, or the Realtime validation in steps 1-2.
- Define an access matrix for `chats`, `messages`, `chat_participants`, and
`users`, using identity cases: chat owner, chat collaborator, unrelated
authenticated user (non-participant), and anonymous, since the app is
single-tenant and has no organization/tenant table. Treat "unrelated
authenticated user" as the analog of the cross-tenant case.
- For each table and identity, define the expected outcome for `SELECT`,
`INSERT`, `UPDATE`, and `DELETE`, covering both allowed and denied rows.
- Add a test-suite directory (for example `tests-rls/`) that seeds isolated test
identities and data: distinct `public.users` rows with mapped `clerk_user_id`
values for owner, collaborator, and unrelated user, plus the `chats`,
`messages`, and `chat_participants` rows needed for each matrix cell.
- Execute each query under the correct identity/JWT context by connecting to
Postgres under a role subject to RLS (not the privileged app role) and setting
JWT claims per identity with `SET LOCAL request.jwt.claims`, so policies observe
the correct `auth.uid()`/`clerk_id()`/`is_clerk_user()` values. Include an
anonymous (no-claims) context.
- Implement assertions with pgTAP run through `supabase test db`, or with a
`bun:test` suite matching the existing `tests-unit/` convention that issues raw
Postgres queries. Assert both allowed and denied row visibility for every matrix
cell.
- Before running tests, apply the RLS migrations (`0003_clerk_auth_sync.sql`,
`0004_fix_clerk_id_function.sql`, `0005_add_documents_rag.sql`, and the base
schema) against a disposable environment, using a local Supabase stack (Supabase
CLI) or a dedicated test project provisioned in CI, since no test database
exists today.
- Add a CI job (extend `.github/workflows/`) that provisions the disposable
Supabase/Postgres environment, applies the migrations, seeds the identities, and
runs the RLS suite. Fail the CI job when any matrix cell result changes
unexpectedly, so the suite acts as a regression gate for authorization.

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- @coderabbitai You can skip phase 3. Add a simple unit test case for phase 2.
- @coderabbitai For design choice 1 go ahead with option 3 and replan.

@{owner} — flagged this while reading the thread. The gap underneath most RLS "bugs" isn't the specific policy — it's that nobody has an automated proof of who can read what across the whole schema, and the one-off grant/permission fixes you land here start drifting the moment the next migration lands.

I built that proof: an automated RLS Policy Audit that enumerates, per identity (owner / other user / other tenant / anon), every accessible row from your real policies. $299 flat, 48h, and if nothing is exposed you don't pay. It ships a CI gate so the access matrix can't regress silently.

Proof it works: github.com/cekuu35/supabase-rls-leak-demo — happy to run it on your project free; clean report = no invoice, you keep it.

@coderabbitai plan what do you think

I've updated the plan above based on your feedback. Ready to proceed when you are!

Picking this up since QueueLab is building the multi-user Supabase piece and I flagged the AI-Health-App leak cross-scan (tarekelahmar/AI-Health-App#3). Free RLS plan for a multi-user Supabase system — the three decisions that actually bite:

1. Every actor is a Supabase Auth user. In Supabase, auth.uid() is the only way policies see an identity. If you let guests/agents/collaborators in without a real auth row, every (true) fallback policy quietly becomes "everyone sees everything." Make bots users too. Kills ~80% of leaking-table issues.

2. Scope through a join table, never using (true).

create policy "own room memberships"
on public.room_members for select
using (auth.uid() = user_id);

Then messages joins through room_members. Bare (true) on any public table = the bug I see in nearly every audit.

3. The service_role key is the real trap. Any code path that uses the anon/service key from an env file bypasses all RLS (RLS only applies to JWT users). Committed .env + a wired admin route = whole table set exposed no matter how good policies look. Plan the privileged path as an Edge Function calling security definer, keyed off current_setting('request.jwt.claim.sub').

What makes this hold long-term: an RLS test suite in CI — one query per policy, fail the build on any table with no policy or any using (true). That's the thing that keeps a 2k-user system from becoming a front-page leak.

Happy to do a full Supabase RLS + committed-secret audit of the QCX/QCX codebase — every table, every policy, every service_role path, git-history secret scan included — flat $299, done in 24–48h with a written fix list + the CI test suite. Want me to post the top 3 findings publicly in this thread first so you can judge quality before deciding? That pass is free either way.

Quick follow-up on my RLS plan from yesterday - no rush, just leaving this here.

Easiest way to judge quality before you pay for anything: I post the top 3 RLS findings for QCX/QCX publicly in this thread, free, and you keep them either way. If a couple of them are true leaks, green-light the full $299 audit (every table, every policy, every service_role path, git-history secret scan, written fix list + CI RLS test suite, 24-48h).

Want me to start with the free top-3 pass?

Appreciate the engagement. Ready to move on your side whenever you are: I can start tonight with the free top-3 RLS pass (you keep the report either way), and if it looks useful you green-light the full $299 audit. Which do you prefer - the free top-3 first, or jump straight into the full audit?

Navigation Menu

Repository navigation

Collaboration #791

Coding Plan

Summary

Implementation Steps

Phase 1: Secure and correct the multi-user write paths

Phase 2: Make the collaboration UI reachable

Phase 3: Live multi-user updates and automated authorization verification

🚀 Next Steps

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Collaboration #791

Description

Activity

ngoiyaeric commented on Sep 5, 2026

coderabbitai commented on Sep 5, 2026

Coding Plan

Summary

Implementation Steps

Phase 1: Secure and correct the multi-user write paths

Phase 2: Make the collaboration UI reachable

Phase 3: Live multi-user updates and automated authorization verification

🚀 Next Steps

cekuu35 commented on Sep 16, 2026

ngoiyaeric commented on Sep 16, 2026

coderabbitai commented on Sep 16, 2026

cekuu35 commented on Sep 16, 2026

cekuu35 commented on Sep 17, 2026

cekuu35 commented on Sep 17, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions