# Tutorial: a realtime chat with row-level security
Source: https://sovaria.in/docs/tutorial-realtime-chat

Build chat rooms where only members read and post, new messages appear at once on every device, and nothing leaks to people outside a room: tables, row rules, a membership function, change events and a channel rule.

You will build the backend and client code for chat rooms. Messages live in Postgres under row-level security, and realtime only tells clients **that** something changed. Clients then read the new messages through the same rules, so the realtime path can't leak a message to someone outside the room. You need a project and the JavaScript SDK ([Quickstart](https://sovaria.in/docs/quickstart)).

(Diagram: Who sees a new message)

## 1. Tables and row rules

Run this in the **SQL Editor**. `is_member` is a security definer function, so the policies can check membership without giving users read access to everyone's memberships.

SQL:
```sql
create table rooms (
  id bigint generated always as identity primary key,
  name text not null check (length(name) between 1 and 80),
  created_by uuid not null default auth.uid(),
  created_at timestamptz not null default now()
);
create table room_members (
  room_id bigint not null references rooms on delete cascade,
  user_id uuid not null,
  primary key (room_id, user_id)
);
create table messages (
  id bigint generated always as identity primary key,
  room_id bigint not null references rooms on delete cascade,
  author_id uuid not null default auth.uid(),
  body text not null check (length(body) between 1 and 4000),
  created_at timestamptz not null default now()
);
create index on messages (room_id, id);

create or replace function public.is_member(room bigint)
returns boolean language sql stable security definer set search_path = ''
as $$ select exists (select 1 from public.room_members where room_id = room and user_id = auth.uid()) $$;

alter table rooms enable row level security;
alter table rooms force row level security;
alter table room_members enable row level security;
alter table room_members force row level security;
alter table messages enable row level security;
alter table messages force row level security;

-- Rooms: members see them; anyone signed in can create one.
create policy "members read rooms" on rooms for select using (public.is_member(id) or created_by = auth.uid());
create policy "signed-in create rooms" on rooms for insert with check (auth.uid() is not null and created_by = auth.uid());

-- Memberships: see your own rooms' members; the room's creator adds people.
create policy "members see members" on room_members for select using (public.is_member(room_id));
create policy "creator adds members" on room_members for insert
  with check (exists (select 1 from public.rooms r where r.id = room_id and r.created_by = auth.uid()));

-- Messages: members read and post, as themselves.
create policy "members read messages" on messages for select using (public.is_member(room_id));
create policy "members post messages" on messages for insert
  with check (public.is_member(room_id) and author_id = auth.uid());
```

- There is no update or delete policy on `messages`, so nobody can edit or delete a message (the service role key still can, for moderation).
- A room's creator should add themselves as its first member: `insert into room_members values (<room id>, auth.uid())`.
- Write every name with its schema inside the definer function (`public.room_members`) because its `search_path` is empty ([Database functions](https://sovaria.in/docs/database-functions#policies)).

## 2. Turn on change events for messages

1. **Realtime › Database change data capture** — Switch it on and pick `messages`. Every insert now publishes an event on the channel `db.table.messages` ([Database change events](https://sovaria.in/docs/realtime-database-changes)).
2. **Let signed-in users listen** — Under **Policies**, add a channel rule. App users get no channel until a rule allows it.
```bash
ALLOW IF ctx.resource.name == "db.table.messages" AND ctx.action == "SUBSCRIBE" AND ctx.actor.is_anonymous == False
```

Change events carry the table, the kind of change and how many rows changed, never the rows themselves. A user outside a room learns only that *some* message was posted somewhere. If even that is too much, use a private channel per user instead ([Channels and connections](https://sovaria.in/docs/realtime-channels#names)) and publish to the members from a trigger function.

## 3. The client

chat.ts:
```ts
import { createClient } from '@sovaria/sdk'

export const sovaria = createClient('https://<project-ref>.sovarias.in', '<anon-key>')

type Message = { id: number; author_id: string; body: string; created_at: string }
const toObjects = <T,>(r: { columns: string[]; rows: unknown[][] }) =>
  r.rows.map((row) => Object.fromEntries(r.columns.map((c, i) => [c, row[i]])) as T)

export async function createRoom(name: string, memberIds: string[]) {
  const r = (await sovaria.data.sql('insert into rooms (name) values ($1) returning id', [name])) as any
  const roomId = r.rows[0][0] as number
  // The creator joins first (auth.uid() is the signed-in user), then adds the others.
  await sovaria.data.sql('insert into room_members (room_id, user_id) values ($1, auth.uid())', [roomId])
  for (const id of memberIds) {
    await sovaria.data.sql('insert into room_members (room_id, user_id) values ($1, $2)', [roomId, id])
  }
  return roomId
}

export const send = (roomId: number, body: string) =>
  sovaria.data.sql('insert into messages (room_id, body) values ($1, $2)', [roomId, body])

// Load the latest messages, then follow new ones. Returns a function that stops listening.
export function openRoom(roomId: number, render: (all: Message[]) => void) {
  let messages: Message[] = []
  let lastId = 0

  async function fetchNew() {
    const r = await sovaria.data.sql(
      'select id, author_id, body, created_at from messages where room_id = $1 and id > $2 order by id limit 200',
      [roomId, lastId],
    )
    const fresh = toObjects<Message>(r as never)
    if (!fresh.length) return
    lastId = fresh[fresh.length - 1].id
    messages = [...messages, ...fresh].slice(-500)
    render(messages)
  }

  fetchNew()
  const channel = sovaria.realtime.channel('db.table.messages', {
    onMessage: (event) => { if (event.event_type === 'row.inserted') fetchNew() },
    onStatus: (status) => { if (status === 'live') fetchNew() },   // catch up after a reconnect
  })
  return () => channel.close()
}
```

- `fetchNew` asks only for messages after the last one shown, so every event costs one small, indexed query.
- The query has `where room_id = $1` for speed, but security comes from the row rules: a user who isn't a member gets no rows even if they change the code.
- Rapid messages may arrive as several events; each `fetchNew` picks up everything since `lastId`, so nothing is shown twice.

## 4. Typing indicators (optional)

Typing indicators don't need to be stored. Publish them on a channel that only signed-in users can use, and include the room id. They carry nothing private, but keep them out of rooms you can't see by ignoring rooms you aren't showing.

Policy:
```bash
ALLOW IF ctx.resource.name == "typing" AND ctx.actor.is_anonymous == False
```

TypeScript:
```ts
const typing = sovaria.realtime.channel('typing', {
  onMessage: (m) => { if (m.room_id === currentRoom) showTyping(m.user_id) },
})
input.addEventListener('input', () => typing.send({ room_id: currentRoom, user_id: myId }))
```

## 5. Prove nobody else can read a room

Before you ship, run a simulation: it tries every table and channel as a visitor, a signed-in user and each role, changes nothing, and reports any access you didn't intend ([Simulate access before you ship](https://sovaria.in/docs/simulation)).

Terminal:
```bash
sov sim run --fail-on critical
```