Database Schema¶

Beyond Retrieval v2 uses PostgreSQL 15 via Supabase with three extensions: pgvector, pg_trgm, and pg_net.

Entity Relationship Diagram¶

erDiagram
    notebook {
        UUID notebook_id PK
        TEXT notebook_title
        TEXT notebook_description
        TEXT icon
        TEXT db_type
        TEXT storage_provider
        TEXT user_id
        INTEGER number_of_documents
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
    }

    notebook_settings {
        UUID notebook_id PK_FK
        TEXT system_prompt_retrieval
        VARCHAR embedding_model
        VARCHAR inference_provider
        VARCHAR inference_model
        DECIMAL inference_temperature
        VARCHAR active_strategy_id
        JSONB strategies_config
        BOOLEAN enable_multimodal_processing
        TEXT language_mode
        INTEGER enhancement_concurrency
        JSONB ingestion_settings
        BOOLEAN judge_enabled
        TIMESTAMPTZ created_at
    }

    notebook_file_jobs {
        UUID job_id PK
        UUID notebook_id FK
        TEXT file_id
        TEXT file_name
        TEXT status
        TEXT error_description
        JSONB ingestion_settings
        TIMESTAMPTZ created_at
    }

    documents {
        BIGSERIAL id PK
        TEXT content
        JSONB metadata
        VECTOR embedding
        TSVECTOR fts
    }

    document_records {
        UUID id PK
        TEXT file_id
        TEXT file_name
        UUID notebook_id FK
        TIMESTAMPTZ created_at
    }

    contextual_retrieval_table {
        TEXT chunk_id PK
        UUID job_id FK
        TEXT file_id
        TEXT original_chunk
        TEXT enhanced_chunk
        TEXT status
        UUID notebook_id FK
        JSONB original_metadata
    }

    chat_conversations {
        UUID conversation_id PK
        UUID notebook_id FK
        TEXT user_id
        TEXT title
        BOOLEAN is_pinned
        INTEGER message_count
        TIMESTAMPTZ last_message_at
    }

    chat_history {
        UUID id PK
        UUID conversation_id FK
        UUID notebook_id FK
        VARCHAR role
        TEXT content
        JSONB citations
        JSONB run_metadata
    }

    query_cache {
        UUID id PK
        UUID notebook_id FK
        TEXT query_text
        TEXT query_hash
        TEXT response
        VECTOR embedding
        TIMESTAMPTZ created_at
    }

    notebook_access {
        UUID id PK
        UUID notebook_id FK
        TEXT user_id
        TEXT access_type
    }

    notebook_invites {
        UUID id PK
        UUID notebook_id FK
        TEXT invite_code
        TIMESTAMPTZ expires_at
    }

    user_api_keys {
        UUID id PK
        TEXT user_id
        TEXT key_name
        TEXT key_value
    }

    notebook ||--|| notebook_settings : "1:1 config"
    notebook ||--o{ notebook_file_jobs : "has jobs"
    notebook ||--o{ document_records : "has files"
    notebook ||--o{ contextual_retrieval_table : "has chunks"
    notebook ||--o{ chat_conversations : "has conversations"
    notebook ||--o{ query_cache : "has cache"
    notebook ||--o{ notebook_access : "has access grants"
    notebook ||--o{ notebook_invites : "has invites"
    chat_conversations ||--o{ chat_history : "has messages"
    notebook_file_jobs ||--o{ contextual_retrieval_table : "produces chunks"

PostgreSQL Extensions¶

Key Indexes¶

• HNSW vector index on query_cache.embedding (vector_cosine_ops) for semantic cache lookups
• GIN index on documents.fts for full-text search
• GIN index on documents.metadata for JSONB containment queries
• Composite unique on query_cache(notebook_id, query_hash) for exact cache dedup
• Composite unique on notebook_access(notebook_id, user_id)
• Composite unique on user_api_keys(user_id, key_name)

Key Database Functions (30+)¶

Schema Migrations¶

The primary schema is at db/migrations/001_initial_schema.sql. Additional migrations:

Local mode: start_services.py copies the schema to docker/supabase/init-scripts/99-schema.sql for auto-execution on first container startup.

Cloud mode: Apply migrations manually via the Supabase SQL Editor.