gistfile1.txt

## 🚀 Project Goal  
Build a **real-time collaborative chat platform** where teams can interact with each other **and** an AI agent in shared rooms. The UX should blend the speed and clarity of a SOC terminal with the fluidity of Discord and the conversational power of ChatGPT. Think **fast, minimal, API-first, and production-ready**.

---

## 🧱 Tech Stack Summary

### **Frontend (React + Next.js + Vite)**
- **Framework**: React + Vite + Next.js App Router  
- **Rendering**: SSR used exclusively for **AI streaming**  
- **Styling**: Tailwind CSS + `shadcn/ui` (modals, buttons, inputs, cards)  
- **State Management**: Zustand  
- **Transport**:  
  - **WebSocket**: Client → Server messages  
  - **SSE (EventSource)**: Tokenized AI responses from server  
- **Rendering**: `react-markdown` with syntax-highlighted code blocks  

### **Backend (FastAPI)**
- **Framework**: Async FastAPI  
- **Transport**:
  - **WebSocket**: User and agent relay in shared rooms  
  - **SSE**: `/agent/stream` endpoint using `StreamingResponse`  
- **AI Integration**: `httpx` with `stream=True` to OpenAI/Ollama  
- **Design**: Fully API-driven, no client-side DB logic  
- **State**: In-memory message queue (planned Redis swap)  
- **Auth**: Server-side JWT or session  
- **Roles**: `user`, `admin`, `agent`

---

## 🗃️ Database Design (PostgreSQL)

All data is persisted in a normalized schema:

### **users**
| Field      | Type     | Notes                  |
|------------|----------|------------------------|
| id         | UUID PK  |                        |
| email      | TEXT     | Optional / unique      |
| name       | TEXT     | Display name           |
| role       | TEXT     | `member`, `admin`      |
| created_at | TIMESTAMP|                        |

### **rooms**
| Field      | Type     | Notes                  |
|------------|----------|------------------------|
| id         | UUID PK  |                        |
| name       | TEXT     | e.g. `red-team`        |
| created_by | UUID FK  |                        |
| created_at | TIMESTAMP|                        |

### **room_members**
| Field      | Type     | Notes                  |
|------------|----------|------------------------|
| room_id    | UUID FK  |                        |
| user_id    | UUID FK  |                        |
| joined_at  | TIMESTAMP|                        |
| is_muted   | BOOLEAN  | Optional UX flag       |

### **messages**
| Field      | Type     | Notes                  |
|------------|----------|------------------------|
| id         | UUID PK  |                        |
| room_id    | UUID FK  |                        |
| user_id    | UUID FK  | Nullable if agent      |
| is_agent   | BOOLEAN  |                        |
| content    | TEXT     | Markdown-supported     |
| created_at | TIMESTAMP|                        |

### **agent_sessions** (optional but preferred)
| Field      | Type     | Notes                  |
|------------|----------|------------------------|
| id         | UUID PK  |                        |
| room_id    | UUID FK  |                        |
| user_id    | UUID FK  | Initiator              |
| prompt     | TEXT     | Raw input              |
| output     | TEXT     | Final AI output        |
| status     | TEXT     | `running`, `completed`, `error` |
| started_at | TIMESTAMP|                        |
| ended_at   | TIMESTAMP|                        |

**ER Diagram**:  
```
users ────────┐  
              ▼  
        room_members  
              ▲  
rooms ────────┘  
              ▼  
           messages  
              ▲  
              │  
      agent_sessions (optional)
```

---

## 📜 SQL Operation Logging

Every `INSERT`, `UPDATE`, and `DELETE` must be captured and appended to a real-time `.sql` backup log. This enables an auditable, replayable change log for disaster recovery.

---

## ✅ Deliverables

- `main.py`: FastAPI backend with WebSocket relay, SSE streaming, OpenAI integration, and strict API-first logic  
- `App.tsx`: SSR-enabled chat UI with markdown rendering, `shadcn/ui` components, WebSocket & SSE hooks  
- `schema.sql` + Alembic migrations  
- `sql_logger.py`: Appends every SQL operation into `backup.sql` in real time