Database Schema

Overview

Chatty the Lab uses a single Supabase PostgreSQL database with the pgvector extension for semantic search. The schema is defined in two migration files:

• 20260323000001_initial_schema.sql — Core tables (profiles, sessions, chats, messages)
• 20260323000002_pgvector.sql — Context and query items with vector embeddings

Entity relationship

erDiagram
    auth_users ||--o| profiles : "trigger creates"
    profiles ||--o{ sessions : "has many"
    sessions ||--o{ chats : "has many"
    sessions ||--o{ session_prompts : "has many"
    chats ||--o{ messages : "has many"
    context_items }o--o{ chats : "attached via context_text"

Tables

profiles

Automatically created when a user signs up via the handle_new_user() trigger on auth.users.

Column	Type	Description
id	UUID PK	References auth.users(id), cascade delete
display_name	TEXT	User’s full name from Google
email	TEXT UNIQUE	Email address
avatar_url	TEXT	Google profile picture URL
created_at	TIMESTAMPTZ	Defaults to now()

sessions

A session groups multiple chats for comparison.

Column	Type	Description
id	UUID PK	Auto-generated
user_id	UUID FK	References profiles(id), cascade delete
title	TEXT	Session name, defaults to 'New Session'
master_chat_index	INTEGER	Position of the master chat (default 0)
created_at	TIMESTAMPTZ	Auto-set
updated_at	TIMESTAMPTZ	Auto-updated via trigger

chats

Each chat represents one conversation with a specific model within a session.

Column	Type	Description
id	UUID PK	Auto-generated
session_id	UUID FK	References sessions(id), cascade delete
position	INTEGER	Order within the session (unique per session)
model_name	TEXT	Name of the LLM model
context_text	TEXT	System prompt / injected context (default '')
title	TEXT	Display title for the chat tab
created_at	TIMESTAMPTZ	Auto-set

messages

Individual messages within a chat conversation.

Column	Type	Description
id	UUID PK	Auto-generated
chat_id	UUID FK	References chats(id), cascade delete
position	INTEGER	Order within the chat (unique per chat)
role	TEXT	One of 'system', 'user', 'assistant'
content	TEXT	Message content
created_at	TIMESTAMPTZ	Auto-set

session_prompts

Tracks prompts sent via the master chat for session-level history.

Column	Type	Description
id	UUID PK	Auto-generated
session_id	UUID FK	References sessions(id), cascade delete
position	INTEGER	Order within the session (unique per session)
content	TEXT	The prompt text
created_at	TIMESTAMPTZ	Auto-set

context_items

Reusable context snippets with optional vector embeddings for semantic search.

Column	Type	Description
id	UUID PK	Auto-generated
title	TEXT	Display title
category	TEXT	Category label
tags	TEXT[]	Array of tags (default '{}')
owner	TEXT	User ID or 'all' for shared items
content	TEXT	The context content
embedding	vector(768)	Embedding from nomic-embed-text
created_at	TIMESTAMPTZ	Auto-set

query_items

Pre-populated query suggestions with embeddings for semantic matching.

Column	Type	Description
id	UUID PK	Auto-generated
query	TEXT	The suggested query text
embedding	vector(768)	Embedding vector
created_at	TIMESTAMPTZ	Auto-set

llm_models

Optional registry of LLM models (not actively used by the backend, which uses the in-memory plugin registry).

Column	Type	Description
id	UUID PK	Auto-generated
name	TEXT UNIQUE	Display name
provider	TEXT	Provider identifier
model_id	TEXT	API model identifier
enabled	BOOLEAN	Default true
created_at	TIMESTAMPTZ	Auto-set

pgvector

The vector extension is enabled in migration 20260323000002_pgvector.sql:

CREATE EXTENSION IF NOT EXISTS vector;

Embeddings are 768-dimensional vectors generated by Ollama’s nomic-embed-text model. Two IVFFlat indexes are created for cosine distance search:

-- Context items index
CREATE INDEX idx_context_items_embedding ON public.context_items
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Query items index
CREATE INDEX idx_query_items_embedding ON public.query_items
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 50);

Similarity is computed as 1 - (embedding <=> query_embedding) where <=> is the cosine distance operator.

Row Level Security

All tables have RLS enabled. Policies ensure users can only access their own data:

Table	Policy	Rule
profiles	SELECT, UPDATE own	id = auth.uid()
sessions	ALL own	user_id = auth.uid()
chats	ALL own	session_id belongs to user’s session
messages	ALL own	chat_id belongs to user’s chat via session
session_prompts	ALL own	session_id belongs to user
context_items	SELECT shared + own	owner = 'all' OR owner = auth.uid()::text
context_items	INSERT, UPDATE, DELETE own	owner = auth.uid()::text

Caution

The backend connects to PostgreSQL with the postgres superuser, bypassing RLS. RLS policies protect data when accessed directly through the Supabase client (e.g., from the frontend). The backend enforces ownership checks in application code via SQL WHERE clauses.

Database functions

Two SQL functions are defined for semantic search:

• search_context_items(query_embedding, match_limit, owner_filter) — Returns context items ordered by cosine similarity
• search_query_items(query_embedding, match_limit) — Returns query suggestions ordered by cosine similarity

These can be called via Supabase’s RPC interface, though the backend currently uses direct SQL queries with pgvector operators instead.