Database Schema
Postgres schema — tables, relationships, and ER diagram.
Database Schema
The Postgres database is owned exclusively by wordloop-core.
Important
Schema changes must be managed through versioned SQL migrations in services/wordloop-core/scripts/migrations/. Do not apply manual schema alterations.
ER diagram
Tables
users
Primary user account linked to Auth0.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
auth0_id | TEXT UNIQUE | External identity |
email | TEXT | |
name | TEXT | |
person_id | UUID FK → people | Optional self-link |
created_at | TIMESTAMPTZ |
people
Contacts and meeting participants.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
name | TEXT | |
role | TEXT | Job title / role description |
email | TEXT | |
company | TEXT | |
tags | JSONB | |
voice_model_status | TEXT | untrained / training / ready |
voice_confidence | DECIMAL | |
voice_vector | vector(512) | Optional SpeechBrain embedding |
meetings
Recorded or uploaded conversations.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
user_id | UUID FK → users | |
title | TEXT | |
start_time | TIMESTAMPTZ | |
end_time | TIMESTAMPTZ | |
summary | TEXT | AI-generated summary |
key_points | JSONB | |
source_type | TEXT | recording / upload / text / anecdotal |
created_at | TIMESTAMPTZ |
meeting_audio_files
Audio files attached to meetings.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
meeting_id | UUID FK → meetings | |
storage_path | TEXT | GCS path |
file_name | TEXT | |
content_type | TEXT | MIME type |
file_size | BIGINT | Bytes |
created_at | TIMESTAMPTZ |
transcriptions
Records the transcription job details connected to a meeting.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
meeting_id | UUID FK → meetings | |
status | TEXT | enum: pending, transcribing, diarizing, extracting_features, completed, failed |
status_message | TEXT | Optional error details |
created_at | TIMESTAMPTZ | |
updated_at | TIMESTAMPTZ |
transcription_status_history
Audit log of transcription status changes.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
transcription_id | UUID FK → transcriptions | |
status | TEXT | |
status_message | TEXT | |
created_at | TIMESTAMPTZ |
transcript_segments
Timestamped chunks of transcribed speech.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
transcription_id | UUID FK → transcriptions | |
person_id | UUID FK → people | Nullable |
speaker_label | TEXT | Temporary label before identification |
text | TEXT | |
start_time | DECIMAL | Seconds from start |
end_time | DECIMAL | Seconds from start |
confidence | DECIMAL | Transcription confidence |
is_final | BOOLEAN | Indicates if segment is finalized (streaming) |
feature_vector | vector(512) | SpeechBrain embedding |
tasks (formerly action_items)
Actionable items extracted from meetings.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
user_id | UUID FK → users | |
content | TEXT | |
status | TEXT | pending / completed |
due_date | DATE | |
assigned_to | UUID FK → people | |
meeting_id | UUID FK → meetings | |
sub_tasks | JSONB | |
created_at | TIMESTAMPTZ |
notes
Free-form notes attached to people or meetings.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
user_id | UUID FK → users | |
content | TEXT | |
subject_type | TEXT | PERSON / MEETING |
subject_id | UUID | Polymorphic FK |
tags | JSONB | |
created_at | TIMESTAMPTZ | |
updated_at | TIMESTAMPTZ |
ai_threads
Contextual AI conversation containers.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
user_id | UUID FK → users | |
context_type | TEXT | PERSON / MEETING |
context_id | UUID | Polymorphic FK |
created_at | TIMESTAMPTZ |
chat_messages
Individual messages within an AI thread.
| Column | Type | Notes |
|---|---|---|
id | UUID PK | |
thread_id | UUID FK → ai_threads | |
role | TEXT | user / assistant / system / tool |
content | TEXT | |
tool_calls | JSONB | |
created_at | TIMESTAMPTZ |
Migration history
Migrations are applied via ./dev db migrate and live in services/wordloop-core/scripts/migrations/.
| Version | Description |
|---|---|
20250709123530 | Initial schema (users, people) |
20260309152000 | Meetings, transcripts, tasks, notes, AI threads |
20260313204400 | Add person_id to users |
20260315213000 | Rename action_items → tasks |
20260324204621 | Add meeting_audio_files |
20260324211500 | Add meeting.status |
20260326090621 | Add meeting.status_message |
20260327200316 | Update transcript segment fields |
20260329060000 | Add is_final to transcript_segments |
20260329204000 | Add meeting_status_history (later dropped) |
20260330203000 | Add pgvector extension, transcriptions table, and voice_vector |