Private, real-time chat storage for AI apps

1-- chat.messages is a per-user table.
2-- Every user sees their own isolated data automatically (no user_id column needed).
3CREATE TABLE chat.messages (
4  id         BIGINT DEFAULT SNOWFLAKE_ID(),
5  thread_id  TEXT,
6  role       TEXT,         -- 'user' | 'assistant' | 'tool'
7  content    TEXT,
8  created_at TIMESTAMP
9) WITH (TYPE='USER');
10
11-- Topic routing and consumption
12CREATE TOPIC chat.message_events PARTITIONS 2;
13ALTER TOPIC chat.message_events
14ADD SOURCE chat.messages
15ON INSERT
16WITH (payload = 'full');
17
18-- User sends a message (written into their own private space)
19INSERT INTO chat.messages (thread_id, role, content, created_at)
20VALUES ('thread_42', 'user', 'Can you check my invoice?', NOW());
21
22-- Agent worker consumes message events to trigger processing (e.g. tool calls/thinking/replying/streaming)
23CONSUME FROM chat.message_events GROUP 'chat-worker-1' FROM LATEST LIMIT 50;
24  -- Agent writes back impersonation (same user scope; still isolated)
25  EXECUTE AS USER 'service_bot' (
26    INSERT INTO chat.messages (conversation_id, role_id, content)
27    VALUES (1, 'assistant', 'I processed your request')
28  );
29ACK chat.message_events GROUP 'chat-worker-1' UPTO OFFSET 50;
30
31-- User live subscribes to new messages in a thread.
32-- NOTE: No `user_id = ...` — the connection is already scoped to the user.
33SUBSCRIBE TO chat.messages
34WHERE thread_id = 'thread_42'
35OPTIONS (last_rows = 20);
36