Postgres schema owned by wordloop-core, including tables, relationships, and migration workflow.

Database Schema

The Postgres database is owned exclusively by wordloop-core. The canonical schema is the target-state SQL file at services/wordloop-core/db/schema.sql.

Important

Wordloop Core does not use timestamped up/down migration files. To change the schema, edit services/wordloop-core/db/schema.sql, review the generated pg-schema-diff plan with ./dev db dry-run, then apply it with ./dev db migrate. Do not manually alter local, staging, or production databases.

Schema Workflow

wordloop-core uses a declarative schema workflow:

services/wordloop-core/db/schema.sql describes the desired final database state.
services/wordloop-core/cmd/migrate connects using DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME, and DB_SSLMODE.
The migrator ensures the vector extension exists, builds a temporary database, applies the target schema there, and asks pg-schema-diff to compare the live database to the target schema.
./dev db dry-run prints the planned DDL and hazards without applying changes.
./dev db migrate applies the generated plan to the configured database.
./dev db dump applies the target schema locally, then writes a reference-only pg_dump snapshot to .dev/schema.sql.

The Docker Compose core profile builds the same migrator binary and runs the wordloop-core-migrate container before starting the API container.

ER Diagram

Rendering architecture map...

Enums

Enum	Values
`public.task_source_enum`	`user`, `system`
`public.meeting_source_enum`	`recording`, `upload`, `text`, `anecdotal`

Tables

`users`

Primary user account linked to Clerk and optionally to a people row for the user's own person record.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`username`	TEXT	Required
`email`	TEXT UNIQUE	Required
`clerk_id`	TEXT UNIQUE	External identity
`person_id`	UUID FK -> `people.id`	Nullable, `ON DELETE SET NULL`
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

Indexes: idx_users_person_id on person_id.

`people`

Contacts and meeting participants scoped to a user.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`user_id`	UUID FK -> `users.id`	Required, `ON DELETE CASCADE`
`display_name`	TEXT	Required
`full_name`	TEXT	Optional
`title`	TEXT	Optional
`role`	TEXT	Optional
`email`	TEXT	Optional
`company`	TEXT	Optional
`tags`	JSONB	Optional
`voice_model_status`	TEXT	Defaults to `untrained`; check: `untrained`, `training`, `ready`
`voice_confidence`	DECIMAL	Optional
`voice_vector`	vector(192)	Optional ECAPA-TDNN speaker embedding. Locked to 192 dimensions in lockstep with ML's embedding model — see ADR 0008.
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

Constraints and indexes: unique (user_id, display_name), idx_people_voice_vector_hnsw on voice_vector using HNSW cosine ops.

`person_summaries`

Summaries of people authored by users.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`person_id`	UUID FK -> `people.id`	Required, `ON DELETE CASCADE`
`author_id`	UUID FK -> `users.id`	Required
`summary`	TEXT	Required
`context`	JSONB	Optional supporting context
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

`tags`

User-owned tag names.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`user_id`	UUID FK -> `users.id`	Required, `ON DELETE CASCADE`
`name`	TEXT	Required
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

`meetings`

Recorded, uploaded, text, or anecdotal conversations.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`user_id`	UUID FK -> `users.id`	Required, `ON DELETE CASCADE`
`title`	TEXT	Required
`source_type`	`public.meeting_source_enum`	Required
`start_time`	TIMESTAMPTZ	Required
`end_time`	TIMESTAMPTZ	Optional
`summary`	TEXT	Optional AI-generated summary
`key_points`	JSONB	Optional
`headline`	TEXT	Optional
`deleted_at`	TIMESTAMPTZ	Soft-delete marker
`created_at`	TIMESTAMPTZ	Defaults to `now()`

`meeting_audio_files`

Audio object attached to a meeting.

Column	Type	Notes
`meeting_id`	UUID PK/FK -> `meetings.id`	Required, `ON DELETE CASCADE`
`storage_path`	TEXT	Required GCS/emulator storage path
`created_at`	TIMESTAMPTZ	Defaults to `now()`

`meeting_attendees`

Join table between meetings and people.

Column	Type	Notes
`meeting_id`	UUID FK -> `meetings.id`	Required, `ON DELETE CASCADE`
`person_id`	UUID FK -> `people.id`	Required, `ON DELETE CASCADE`

Constraints and indexes: primary key (meeting_id, person_id), idx_meeting_attendees_meeting_id, idx_meeting_attendees_person_id.

`transcriptions`

Transcription job state for a meeting.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`meeting_id`	UUID FK -> `meetings.id`	Required, `ON DELETE CASCADE`
`status`	TEXT	Required; check: `pending`, `transcribing`, `synthesizing`, `completed`, `failed`
`status_message`	TEXT	Optional error or status detail
`is_degraded`	BOOLEAN	Required, defaults to `false`
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

`transcription_status_history`

Audit log of transcription status changes.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`transcription_id`	UUID FK -> `transcriptions.id`	Required, `ON DELETE CASCADE`
`status`	TEXT	Required
`status_message`	TEXT	Optional
`created_at`	TIMESTAMPTZ	Defaults to `now()`

`transcript_segments`

Timestamped chunks of transcript text.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`transcription_id`	UUID FK -> `transcriptions.id`	Nullable, `ON DELETE CASCADE`
`person_id`	UUID FK -> `people.id`	Nullable, `ON DELETE SET NULL`
`speaker_label`	TEXT	Temporary label before identification
`text`	TEXT	Required
`start_ms`	DECIMAL	Required start offset in milliseconds
`end_ms`	DECIMAL	Required, defaults to `0`
`confidence`	DECIMAL	Required, defaults to `0`
`is_final`	BOOLEAN	Required, defaults to `true`
`is_highlighted`	BOOLEAN	Required, defaults to `false`
`feature_vector`	vector(192)	Optional segment embedding — ECAPA-TDNN, matches `people.voice_vector`. See ADR 0008.

Indexes: idx_transcript_time on (transcription_id, start_ms), idx_transcript_segments_feature_vector_hnsw on feature_vector using HNSW cosine ops, idx_transcript_segments_person_id on person_id.

`segment_manifest`

One row per GCS storage segment — an aggregated object covering a contiguous, inclusive range of 100ms audio frame sequences for a live recording. The authoritative highest_contiguous_sequence / missing-range computation for a recording is derived from this table.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`meeting_id`	UUID FK -> `meetings.id`	Required, `ON DELETE CASCADE`
`audio_version`	INTEGER	Required, defaults to `1`. Partitions takes — re-recording a meeting starts a new version, so a new take's segments never collide with the previous take's manifest rows.
`start_sequence`	BIGINT	Required
`end_sequence`	BIGINT	Required
`started_at_ms`	BIGINT	Required
`duration_ms`	BIGINT	Required
`object_name`	TEXT	Required GCS object name
`object_generation`	BIGINT	Optional
`byte_count`	BIGINT	Required
`crc32c`	TEXT	Required
`sha256`	TEXT	Optional; NULL for live segments, populated for gap-recovery segments
`created_at`	TIMESTAMPTZ	Defaults to `now()`

Constraints and indexes: unique (meeting_id, audio_version, start_sequence, end_sequence), idx_segment_manifest_meeting_version_seq on (meeting_id, audio_version, start_sequence).

`meeting_speaker_labels`

User-owned speaker_label → person assignments for a meeting. Re-applied to transcript_segments whenever segments are appended (live) or replaced (final batch rebuild), so assignments survive transcript rebuilds.

Column	Type	Notes
`meeting_id`	UUID PK/FK -> `meetings.id`	Required, `ON DELETE CASCADE`
`speaker_label`	TEXT PK	Required
`person_id`	UUID FK -> `people.id`	Required, `ON DELETE CASCADE`
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

Constraints and indexes: primary key (meeting_id, speaker_label), idx_meeting_speaker_labels_person_id on person_id.

`recording_event_history`

Append-only diagnostics trail for a meeting's live recording: every lifecycle transition, health event, reconnect/resume, duration warning, and stop (with reason). Served newest-first by GET /meetings/{id}/recording/events.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`seq`	BIGSERIAL	Append order; `created_at` alone is not unique inside a transaction
`meeting_id`	UUID FK -> `meetings.id`	Required, `ON DELETE CASCADE`
`event_type`	TEXT	Required
`payload`	JSONB	Optional
`created_at`	TIMESTAMPTZ	Defaults to `clock_timestamp()`

Indexes: idx_recording_event_history_meeting on (meeting_id, seq DESC).

`topics`

Topic clusters for a meeting.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`meeting_id`	UUID FK -> `meetings.id`	Required, `ON DELETE CASCADE`
`name`	TEXT	Required
`summary`	TEXT	Required
`is_final`	BOOLEAN	Required, defaults to `false`
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

Indexes: idx_topics_meeting_id on meeting_id.

`topic_segments`

Segments associated with a topic.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`topic_id`	UUID FK -> `topics.id`	Required, `ON DELETE CASCADE`
`segment_id`	UUID	Required segment identifier
`created_at`	TIMESTAMPTZ	Defaults to `now()`

Indexes: idx_topic_segments_topic_id on topic_id.

`talking_points`

Meeting talking points, optionally linked to a topic.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`meeting_id`	UUID FK -> `meetings.id`	Required, `ON DELETE CASCADE`
`topic_id`	UUID FK -> `topics.id`	Nullable, `ON DELETE SET NULL`
`content`	TEXT	Required
`is_final`	BOOLEAN	Required, defaults to `false`
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`deleted_at`	TIMESTAMPTZ	Soft-delete marker

`talking_point_segments`

Join table between talking points and segment identifiers.

Column	Type	Notes
`talking_point_id`	UUID FK -> `talking_points.id`	Required, `ON DELETE CASCADE`
`segment_id`	UUID	Required segment identifier

Constraints: primary key (talking_point_id, segment_id).

`tasks`

Actionable items owned by users and optionally sourced from meetings.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`user_id`	UUID FK -> `users.id`	Required, `ON DELETE CASCADE`
`content`	TEXT	Required
`status`	TEXT	Required, defaults to `pending`; check: `pending`, `completed`
`source`	`public.task_source_enum`	Required, defaults to `system`
`due_date`	DATE	Optional
`assigned_to`	UUID FK -> `people.id`	Nullable, `ON DELETE SET NULL`
`meeting_id`	UUID FK -> `meetings.id`	Nullable, `ON DELETE SET NULL`
`parent_task_id`	UUID FK -> `tasks.id`	Nullable, `ON DELETE CASCADE`
`deleted_at`	TIMESTAMPTZ	Soft-delete marker
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

Indexes: idx_tasks_status_due on (user_id, status, due_date).

`sub_tasks`

Legacy subtask rows associated with tasks.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`task_id`	UUID FK -> `tasks.id`	Required, `ON DELETE CASCADE`
`content`	TEXT	Required
`status`	TEXT	Required, defaults to `pending`; check: `pending`, `completed`
`assigned_to`	UUID FK -> `people.id`	Nullable, `ON DELETE SET NULL`
`due_date`	DATE	Optional
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

Indexes: idx_sub_tasks_task_id on task_id.

`notes`

Free-form notes attached to people or meetings.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`user_id`	UUID FK -> `users.id`	Required, `ON DELETE CASCADE`
`content`	TEXT	Required
`subject_type`	TEXT	Optional; check: `PERSON`, `MEETING`
`subject_id`	UUID	Optional polymorphic subject identifier
`tags`	JSONB	Optional
`created_at`	TIMESTAMPTZ	Defaults to `now()`
`updated_at`	TIMESTAMPTZ	Defaults to `now()`

Indexes: idx_notes_subject on (subject_type, subject_id).

`ai_threads`

Contextual AI conversation containers.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`user_id`	UUID FK -> `users.id`	Required, `ON DELETE CASCADE`
`context_type`	TEXT	Optional; check: `PERSON`, `MEETING`
`context_id`	UUID	Optional polymorphic context identifier
`deleted_at`	TIMESTAMPTZ	Soft-delete marker
`created_at`	TIMESTAMPTZ	Defaults to `now()`

`chat_messages`

Individual messages within an AI thread.

Column	Type	Notes
`id`	UUID PK	Defaults to `gen_random_uuid()`
`thread_id`	UUID FK -> `ai_threads.id`	Required, `ON DELETE CASCADE`
`role`	TEXT	Required; check: `user`, `assistant`, `system`, `tool`
`content`	TEXT	Required
`tool_calls`	JSONB	Optional
`deleted_at`	TIMESTAMPTZ	Soft-delete marker
`created_at`	TIMESTAMPTZ	Defaults to `now()`

Indexes: idx_chat_messages_thread_id on thread_id.

Database Schema

On this page