Here is the properly formatted and organized version of your AI Model Hub Database Schema document:

Database Schema for the AI Model Hub

This document provides a detailed overview of the PostgreSQL database schema for the AI Model Hub service, based on the SQLAlchemy models defined in app/db/model.py. The schema supports core functionalities like:

• Conversational history
• Document storage
• Tracking RAG-related metadata

Schema Diagram

The following diagram illustrates the relationships between the four main tables: sessions, messages, documents, and vector_metadata.

+-------------+         +-------------+
|  sessions   | <------ |  messages   |
+-------------+         +-------------+
| id          | 1       | id          |
| user_id     |         | session_id  |
| title       |         | sender      |
| model_name  |         | content     |
| created_at  |         | created_at  |
| is_archived |         | model_response_time |
+-------------+         | token_count |
                        | metadata    |
                        +-------------+
        ^
        | 1
+------------------+         +-------------+
| vector_metadata  | ---1:1--|  documents  |
+------------------+         +-------------+
| id               |         | id          |
| document_id      |         | title       |
| faiss_index      |         | text        |
| session_id       |         | source_url  |
| embedding_model  |         | author      |
+------------------+         | status      |
                             | created_at  |
                             | user_id     |
                             +-------------+

Table Descriptions

1. sessions Table

Stores metadata for each conversation session. Each row represents a single chat conversation.

• id (Integer, Primary Key): Unique session identifier
• user_id (String): ID of the session's owner. Indexed for quick lookups
• title (String): Human-readable title for the session
• model_name (String): LLM used (e.g., 'gemini-1.5-pro', 'deepseek-chat')
• created_at (DateTime): Timestamp of session creation
• is_archived (Boolean): Soft delete flag

2. messages Table

Stores individual messages within a session.

• id (Integer, Primary Key): Unique message identifier
• session_id (Integer, Foreign Key): Links to sessions.id
• sender (String): Either 'user' or 'assistant'
• content (Text): Message text
• created_at (DateTime): Timestamp of message
• model_response_time (Integer): Time (in seconds) to generate response
• token_count (Integer): Tokens used for the message
• metadata (JSON): Flexible field for model/tool-specific data

3. documents Table

Stores original text and metadata of documents ingested into the system.

• id (Integer, Primary Key): Unique document identifier
• title (String): Document title
• text (Text): Full content of the document
• source_url (String): URL or file path of origin
• author (String): Author of the document
• status (String): 'processing', 'ready', or 'failed'
• created_at (DateTime): Timestamp of upload
• user_id (String): ID of the uploading user

4. vector_metadata Table

Links documents to their vector representations and session context for RAG.

• id (Integer, Primary Key): Unique metadata ID
• document_id (Integer, Foreign Key): Links to documents.id
• faiss_index (Integer): Index in the FAISS vector store
• session_id (Integer, Foreign Key): Session where this vector was used
• embedding_model (String): Embedding model used (e.g., 'text-embedding-004')

Key Relationships

• One-to-Many: sessions → messages A session contains multiple messages; each message belongs to one session.

• One-to-One: documents → vector_metadata Each document has a single vector metadata record.

• Many-to-One: vector_metadata → sessions Multiple vector metadata entries can reference the same session if used for RAG.

Let me know if you’d like this in Markdown, PDF, or any specific format.