| # 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. |
|
|
|
|
|
|
|  |
