Skip to content

Data Model

Jump to bottom
Alessandro Siniscalchi edited this page May 3, 2026 · 4 revisions

Data Model

Froid uses SQLite for all structured data and sqlite-vec for vector embeddings.

Entity Relationship Diagram

erDiagram
    journal_entries ||--o| journal_entry_extractions : "has"
    journal_entries ||--o| journal_entry_embedding_metadata : "has"
    journal_entry_embedding_metadata ||--|| journal_entry_embedding_vec : "contains"
    daily_reviews ||--o{ daily_review_signals : "has"
    daily_reviews ||--o| weekly_reviews : "aggregated into"

    journal_entries {
        int id PK
        text user_id
        text source
        text source_conversation_id
        text source_message_id
        text raw_text
        text received_at
        text created_at
    }

    journal_entry_extractions {
        int id PK
        int journal_entry_id FK
        text extraction_json
        text model
        text prompt_version
        text status
        text error_message
        text created_at
        text updated_at
    }

    journal_entry_embedding_metadata {
        int id PK
        int journal_entry_id FK
        text embedding_model
        int embedding_dim
        text status
        text error_message
        text created_at
    }

    daily_reviews {
        int id PK
        text user_id
        text review_date
        text review_text
        text model
        text prompt_version
        text status
        text error_message
        text signals_status
        text signals_error
        text signals_model
        text signals_prompt_version
        text delivered_at
        text delivery_error
        text created_at
        text updated_at
    }

    daily_review_signals {
        int id PK
        int daily_review_id FK
        text user_id
        text review_date
        text signal_type
        text label
        text status
        text valence
        float strength
        float confidence
        text evidence
        text model
        text prompt_version
        text created_at
        text updated_at
    }

    weekly_reviews {
        int id PK
        text user_id
        text week_start_date
        text review_text
        text model
        text prompt_version
        text status
        text error_message
        text delivered_at
        text delivery_error
        text inputs_snapshot
        text created_at
        text updated_at
    }
Loading

daily_reviews is keyed by (user_id, review_date). weekly_reviews is keyed by (user_id, week_start_date). Reviews are linked to entries and each other implicitly through the user and date/week.

Tables

journal_entries

The primary record for every user message. Every message sent to the bot is stored here immediately, before any asynchronous processing begins.

journal_entry_extractions

Structured data extracted from an entry by an LLM. The extraction includes emotions, behaviors, needs, and possible patterns.

journal_entry_embedding_metadata and journal_entry_embedding_vec

Together these store the vector embedding for an entry. journal_entry_embedding_vec is a sqlite-vec virtual table.

daily_reviews

One record per (user_id, review_date). Generated on a schedule and delivered via Telegram. Tracks both generation status and signal extraction status.

daily_review_signals

Granular signals extracted from a daily review and its associated journal entries.

weekly_reviews

One record per (user_id, week_start_date) (typically a Monday). Generated weekly and delivered via Telegram. The inputs_snapshot field stores a JSON representation of the daily reviews and signals used to generate the review, ensuring the review remains grounded even if source data changes.

Clone this wiki locally