database_schema.md

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.