# Database Schema — Voice Chat (STT/TTS) System

## 1. `users`

| Column | Type | Notes |
|---|---|---|
| id | PK | |

---

## 2. `threads`

Represents a conversation session — groups multiple messages together.

| Column | Type | Notes |
|---|---|---|
| id | PK | internal DB primary key |
| client_thread_id | UUID | client-generated thread identifier — `Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True, index=True, nullable=False)`. This is what the client sends and what's used to build the `thread_abc`-equivalent folder name in the S3 path below — decoupled from `id` so the internal PK never has to be exposed to or generated by the client. |
| user_id | FK → users.id | |
| created_at | timestamp | session start |

---

## 3. `messages`

One row per audio event (STT input turn or TTS output turn). The `type` column determines which direction the conversion runs.

| Column | Type | Notes |
|---|---|---|
| id | PK | internal DB primary key |
| client_msg_id | UUID | client-generated message identifier — `Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True, index=True, nullable=False)`. The client generates this *before* uploading, uses it as the S3 filename (`msg_<client_msg_id>.wav`), and sends it along with the DB write — this is what enables direct client → S3 presigned-URL uploads, bypassing your backend for the audio bytes entirely. |
| thread_id | FK → threads.id | internal join — resolved server-side by looking up the row matching the client's `client_thread_id` |
| user_id | FK → users.id | denormalized — avoids a join for ownership checks |
| type | enum: `stt` \| `tts` | direction of conversion |
| audio_storage_path | text | S3/Supabase path — built from `client_thread_id` + `client_msg_id`, see layout below |
| text_content | text | transcript (STT output) or source text (TTS input) |
| created_at | timestamp | for ordering turns within a thread |

**How `type` works:**
- `type = stt` → `audio_storage_path` is the input, `text_content` is the model output (transcript)
- `type = tts` → `text_content` is the input, `audio_storage_path` is the model output (generated audio)

**Why two IDs per row (`id` vs. `client_*_id`):**
`id` is the internal relational PK — small, sequential, used for all server-side joins and never exposed to or generated by the client. `client_thread_id` / `client_msg_id` are what the client actually generates, sends, and uses to name S3 objects — this lets the client build a valid, unique S3 path and request a presigned upload URL *before* your backend has even created a DB row, without ever needing to know or influence the internal PK.

---

## S3 / Supabase Bucket Layout

```
bucket/
└── user_id_123/
    ├── stt/
    │   └── {client_thread_id}/
    │       └── msg_{client_msg_id}.wav      ← input (spoken query)
    │
    └── tts/
        └── {client_thread_id}/
            └── msg_{client_msg_id}.wav      ← output (spoken response)
```

- The folder/file names in this path are the client-generated `client_thread_id` and `client_msg_id` — not the internal `id` columns.
- `audio_storage_path` in the `messages` table stores this exact path, so it can be used directly for S3 reads/writes without reconstructing it.
- Split by `stt/` and `tts/` at the top level (not by thread) so each pipeline can have independent lifecycle/retention rules.
- `{client_thread_id}/` nested underneath keeps all audio for one conversation together for easy per-thread cleanup or export.



![alt text](image-1.png)