> ## Documentation Index
> Fetch the complete documentation index at: https://dev-doc.dualmindlab.tech/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> Complete PostgreSQL database schema for DualMind Lab — all tables, columns, types, relationships, and indexes. Hosted on Supabase.

## Overview

DualMind Lab uses **PostgreSQL** hosted on **Supabase**. All tables live in the `public` schema. The backend accesses data via Supabase's PostgREST API (not an ORM).

<Info>
  **9 tables** in the `public` schema. All primary keys are UUID v4. The backend uses the Supabase **service role key** (bypasses RLS).
</Info>

## Entity relationship diagram

```mermaid theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
erDiagram
    users ||--o{ threads : "owns"
    users ||--o{ comparisons : "creates"
    users ||--o{ model_votes : "casts"
    threads ||--o{ thread_messages : "contains"
    comparisons ||--o{ model_votes : "receives"
    ai_models ||--o{ comparisons : "participates as model1"
    ai_models ||--o{ comparisons : "participates as model2"
    ai_models ||--o{ model_votes : "wins"
    providers ||--o{ provider_api_keys : "has"

    users {
        uuid user_id PK
        text full_name
        text email
        text role
        timestamp created_at
        timestamp last_login_at
    }

    ai_models {
        uuid model_id PK
        text model_name
        text provider_name
        text api_url
        text description
        text status
        uuid created_by FK
        timestamp created_at
        timestamp updated_at
    }

    threads {
        uuid thread_id PK
        uuid user_id FK
        text title
        text visibility
        timestamp created_at
    }

    thread_messages {
        uuid message_id PK
        uuid thread_id FK
        text prompt_text
        uuid model1_id FK
        uuid model2_id FK
        text model1_response
        text model2_response
        int model1_time_ms
        int model2_time_ms
        uuid comparison_id FK
        timestamp created_at
    }

    comparisons {
        uuid comparison_id PK
        uuid user_id FK
        text prompt_text
        uuid model1_id FK
        uuid model2_id FK
        text model1_response
        text model2_response
        int model1_time_ms
        int model2_time_ms
        timestamp created_at
    }

    model_votes {
        uuid vote_id PK
        uuid user_id FK
        uuid comparison_id FK
        uuid winner_model_id FK
        timestamp created_at
    }

    providers {
        text provider_name PK
        text display_name
        boolean is_enabled
        int priority
        timestamp created_at
        timestamp updated_at
    }

    provider_api_keys {
        uuid key_id PK
        text provider_name FK
        text api_key
        text display_mask
        boolean is_active
        int failure_count
        int total_calls
        timestamp last_used_at
        text last_error_type
        text last_error_category
        timestamp cooldown_until
        uuid created_by FK
        timestamp created_at
        timestamp updated_at
    }

    system_settings {
        text key PK
        text value
        timestamp created_at
        timestamp updated_at
    }
```

## Table definitions

### `users`

Stores all registered users. Synced from Supabase Auth via `UserSyncService.EnsureUserExistsAsync()`.

| Column          | Type          | Nullable | Default  | Description                                 |
| --------------- | ------------- | -------- | -------- | ------------------------------------------- |
| `user_id`       | `uuid`        | No       | —        | **Primary key**. Matches Supabase Auth `id` |
| `full_name`     | `text`        | Yes      | —        | Display name                                |
| `email`         | `text`        | Yes      | —        | Email address                               |
| `role`          | `text`        | Yes      | `'user'` | Role: `user`, `admin`                       |
| `created_at`    | `timestamptz` | Yes      | `now()`  | Account creation time                       |
| `last_login_at` | `timestamptz` | Yes      | —        | Last login timestamp                        |

### `ai_models`

Registry of all AI models available for comparison.

| Column          | Type          | Nullable | Default             | Description                                        |
| --------------- | ------------- | -------- | ------------------- | -------------------------------------------------- |
| `model_id`      | `uuid`        | No       | `gen_random_uuid()` | **Primary key**                                    |
| `model_name`    | `text`        | No       | —                   | Model identifier (e.g., `llama-3.3-70b-versatile`) |
| `provider_name` | `text`        | Yes      | —                   | Provider name (e.g., `groq`, `bytez`)              |
| `api_url`       | `text`        | Yes      | —                   | API endpoint URL                                   |
| `description`   | `text`        | Yes      | —                   | Human-readable display name                        |
| `status`        | `text`        | Yes      | `'active'`          | Status: `active`, `inactive`, `deprecated`         |
| `created_by`    | `uuid`        | Yes      | —                   | FK to `users.user_id`                              |
| `created_at`    | `timestamptz` | Yes      | `now()`             | Creation time                                      |
| `updated_at`    | `timestamptz` | Yes      | —                   | Last update time                                   |

### `threads`

Conversation threads owned by users.

| Column       | Type          | Nullable | Default             | Description                     |
| ------------ | ------------- | -------- | ------------------- | ------------------------------- |
| `thread_id`  | `uuid`        | No       | `gen_random_uuid()` | **Primary key**                 |
| `user_id`    | `uuid`        | Yes      | —                   | FK to `users.user_id`           |
| `title`      | `text`        | Yes      | —                   | Thread title                    |
| `visibility` | `text`        | Yes      | `'private'`         | `private`, `public`, `unlisted` |
| `created_at` | `timestamptz` | Yes      | `now()`             | Creation time                   |

### `thread_messages`

Individual messages within a thread. Supports both single-model and dual-model responses.

| Column            | Type          | Nullable | Default             | Description                                       |
| ----------------- | ------------- | -------- | ------------------- | ------------------------------------------------- |
| `message_id`      | `uuid`        | No       | `gen_random_uuid()` | **Primary key**                                   |
| `thread_id`       | `uuid`        | No       | —                   | FK to `threads.thread_id`                         |
| `prompt_text`     | `text`        | Yes      | —                   | User's prompt                                     |
| `model1_id`       | `uuid`        | Yes      | —                   | FK to `ai_models.model_id` (or model name stored) |
| `model2_id`       | `uuid`        | Yes      | —                   | FK to `ai_models.model_id` (null for single chat) |
| `model1_response` | `text`        | Yes      | —                   | Response from model 1                             |
| `model2_response` | `text`        | Yes      | —                   | Response from model 2 (null for single chat)      |
| `model1_time_ms`  | `integer`     | Yes      | —                   | Response time for model 1                         |
| `model2_time_ms`  | `integer`     | Yes      | —                   | Response time for model 2                         |
| `comparison_id`   | `uuid`        | Yes      | —                   | FK to `comparisons.comparison_id`                 |
| `created_at`      | `timestamptz` | Yes      | `now()`             | Message timestamp                                 |

### `comparisons`

Records of dual-chat arena comparisons.

| Column            | Type          | Nullable | Default             | Description                    |
| ----------------- | ------------- | -------- | ------------------- | ------------------------------ |
| `comparison_id`   | `uuid`        | No       | `gen_random_uuid()` | **Primary key**                |
| `user_id`         | `uuid`        | Yes      | —                   | FK to `users.user_id`          |
| `prompt_text`     | `text`        | Yes      | —                   | The prompt sent to both models |
| `model1_id`       | `uuid`        | Yes      | —                   | FK to `ai_models.model_id`     |
| `model2_id`       | `uuid`        | Yes      | —                   | FK to `ai_models.model_id`     |
| `model1_response` | `text`        | Yes      | —                   | Full response from model 1     |
| `model2_response` | `text`        | Yes      | —                   | Full response from model 2     |
| `model1_time_ms`  | `integer`     | Yes      | —                   | Response time (ms) for model 1 |
| `model2_time_ms`  | `integer`     | Yes      | —                   | Response time (ms) for model 2 |
| `created_at`      | `timestamptz` | Yes      | `now()`             | Comparison timestamp           |

### `model_votes`

User votes on comparison outcomes.

| Column            | Type          | Nullable | Default             | Description                       |
| ----------------- | ------------- | -------- | ------------------- | --------------------------------- |
| `vote_id`         | `uuid`        | No       | `gen_random_uuid()` | **Primary key**                   |
| `user_id`         | `uuid`        | Yes      | —                   | FK to `users.user_id`             |
| `comparison_id`   | `uuid`        | Yes      | —                   | FK to `comparisons.comparison_id` |
| `winner_model_id` | `uuid`        | Yes      | —                   | FK to `ai_models.model_id`        |
| `created_at`      | `timestamptz` | Yes      | `now()`             | Vote timestamp                    |

### `providers`

AI provider registry (Groq, Bytez, etc.).

| Column          | Type          | Nullable | Default | Description                             |
| --------------- | ------------- | -------- | ------- | --------------------------------------- |
| `provider_name` | `text`        | No       | —       | **Primary key** (e.g., `groq`)          |
| `display_name`  | `text`        | Yes      | —       | Human-readable name                     |
| `is_enabled`    | `boolean`     | No       | `true`  | Whether provider is active              |
| `priority`      | `integer`     | No       | `0`     | Selection priority (higher = preferred) |
| `created_at`    | `timestamptz` | Yes      | `now()` | Creation time                           |
| `updated_at`    | `timestamptz` | Yes      | —       | Last update time                        |

### `provider_api_keys`

API keys for each provider with rotation and health tracking.

| Column                | Type          | Nullable | Default             | Description                                |
| --------------------- | ------------- | -------- | ------------------- | ------------------------------------------ |
| `key_id`              | `uuid`        | No       | `gen_random_uuid()` | **Primary key**                            |
| `provider_name`       | `text`        | No       | —                   | FK to `providers.provider_name`            |
| `api_key`             | `text`        | No       | —                   | Encrypted API key                          |
| `display_mask`        | `text`        | Yes      | —                   | Masked key for display (e.g., `sk-...abc`) |
| `is_active`           | `boolean`     | No       | `true`              | Whether key is active                      |
| `failure_count`       | `integer`     | No       | `0`                 | Consecutive failure count                  |
| `total_calls`         | `integer`     | No       | `0`                 | Total API calls made with this key         |
| `last_used_at`        | `timestamptz` | Yes      | —                   | Last successful use                        |
| `last_error_type`     | `text`        | Yes      | —                   | Last error classification                  |
| `last_error_category` | `text`        | Yes      | —                   | Error category                             |
| `cooldown_until`      | `timestamptz` | Yes      | —                   | Key is in cooldown until this time         |
| `created_by`          | `uuid`        | Yes      | —                   | FK to `users.user_id`                      |
| `created_at`          | `timestamptz` | Yes      | `now()`             | Creation time                              |
| `updated_at`          | `timestamptz` | Yes      | —                   | Last update time                           |

### `system_settings`

Key-value store for system configuration and feature flags.

| Column       | Type          | Nullable | Default | Description                              |
| ------------ | ------------- | -------- | ------- | ---------------------------------------- |
| `key`        | `text`        | No       | —       | **Primary key** (e.g., `public_sharing`) |
| `value`      | `text`        | Yes      | —       | Setting value (e.g., `true`)             |
| `created_at` | `timestamptz` | Yes      | `now()` | Creation time                            |
| `updated_at` | `timestamptz` | Yes      | —       | Last update time                         |

## Key relationships

* `threads.user_id` → `users.user_id` (thread ownership)
* `thread_messages.thread_id` → `threads.thread_id` (message belongs to thread)
* `comparisons.user_id` → `users.user_id` (who created the comparison)
* `comparisons.model1_id` / `model2_id` → `ai_models.model_id`
* `model_votes.comparison_id` → `comparisons.comparison_id`
* `model_votes.winner_model_id` → `ai_models.model_id`
* `provider_api_keys.provider_name` → `providers.provider_name`

<Warning>
  The `UserSyncService.EnsureUserExistsAsync()` pattern exists because Supabase Auth creates users in `auth.users` but not in `public.users`. The backend must insert the `public.users` row before any operation that references `user_id` as a foreign key.
</Warning>