Meetily SQLite Database Schema for Storing Meetings, Transcripts, and Summaries

Meetily persists all meeting data in a normalized SQLite database with four core tables—meetings, transcripts, summary_processes, and transcript_chunks—linked by foreign keys with cascade deletion to maintain referential integrity.

The open-source meeting assistant Meetily (Zackriya-Solutions/meetily) uses an embedded SQLite database accessed via sqlx in its Rust-based Tauri backend. The schema is defined in the migration file frontend/src-tauri/migrations/20250916100000_initial_schema.sql and mapped to Rust structs in frontend/src-tauri/src/database/models.rs. This design supports real-time transcription segments, asynchronous AI summarization jobs, and chunked processing for long sessions.

Core Tables and Relationships

The database schema centers on four tables that separate meeting metadata from raw transcript data and background processing state.

meetings

The meetings table stores the top-level session record. It uses a TEXT PRIMARY KEY for the UUID-based id, with title, created_at, and updated_at columns tracking the session lifecycle. All downstream tables reference this primary key.

transcripts

Each row in transcripts represents a discrete speech segment generated by the Whisper integration. Key columns include:

• id (TEXT PRIMARY KEY)
• meeting_id (TEXT NOT NULL, FK to meetings)
• transcript (TEXT NOT NULL) — the raw transcribed text
• timestamp (TEXT NOT NULL) — RFC3339 timestamp
• Optional AI fields: summary, action_items, key_points (all TEXT)
• Audio sync fields: audio_start_time, audio_end_time, duration (REAL)

The foreign key constraint ON DELETE CASCADE ensures that deleting a meeting automatically removes its transcript segments.

summary_processes

This table tracks the lifecycle of background LLM summarization jobs. It uses meeting_id as both PRIMARY KEY and foreign key, ensuring one summary job per meeting. Notable columns include:

• status (TEXT) — e.g., "pending", "processing", "completed"
• result (TEXT) — JSON payload containing the final summary output
• error (TEXT) — error messages if processing fails
• Timing metrics: start_time, end_time, processing_time
• chunk_count (INTEGER) and metadata (TEXT JSON)

transcript_chunks

For large-scale processing, the transcript_chunks table stores chunk-level metadata. Columns include meeting_id (PK, FK), meeting_name, transcript_text, model/model_name, chunk_size, overlap, and created_at. This facilitates parallel processing of long meetings without loading entire transcripts into memory.

SQL Schema Definition

The migration file frontend/src-tauri/migrations/20250916100000_initial_schema.sql defines the table structures with explicit foreign key constraints:

CREATE TABLE meetings (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE transcripts (
    id TEXT PRIMARY KEY,
    meeting_id TEXT NOT NULL,
    transcript TEXT NOT NULL,
    timestamp TEXT NOT NULL,
    summary TEXT,
    action_items TEXT,
    key_points TEXT,
    audio_start_time REAL,
    audio_end_time REAL,
    duration REAL,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE
);

CREATE TABLE summary_processes (
    meeting_id TEXT PRIMARY KEY,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    error TEXT,
    result TEXT,
    start_time TEXT,
    end_time TEXT,
    chunk_count INTEGER DEFAULT 0,
    processing_time REAL DEFAULT 0.0,
    metadata TEXT,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE
);

CREATE TABLE transcript_chunks (
    meeting_id TEXT PRIMARY KEY,
    meeting_name TEXT,
    transcript_text TEXT,
    model TEXT,
    model_name TEXT,
    chunk_size INTEGER,
    overlap INTEGER,
    created_at TEXT NOT NULL,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE
);

Rust Model Mappings

The sqlx crate uses strongly-typed Rust structs to enforce schema compliance at compile time. These definitions live in frontend/src-tauri/src/database/models.rs and mirror the SQL columns exactly. For example, the Transcript struct maps to the transcripts table, while SummaryProcess and TranscriptChunk handle their respective tables, enabling type-safe queries throughout the repository layer in frontend/src-tauri/src/database/repositories/.

Practical Database Operations

Inserting a New Meeting

Use parameterized queries against the database pool exposed via DB::conn():

use uuid::Uuid;
use chrono::Utc;
use crate::database::manager::DB;

let meeting_id = Uuid::new_v4().to_string();
sqlx::query!(
    r#"
    INSERT INTO meetings (id, title, created_at, updated_at)
    VALUES (?, ?, ?, ?)
    "#,
    meeting_id,
    "Team Stand-up",
    Utc::now(),
    Utc::now()
)
.execute(&DB::conn())
.await?;

Saving Transcript Segments with AI Metadata

Store Whisper output alongside optional LLM-generated summaries and audio timestamps:

sqlx::query_as!(
    crate::database::models::Transcript,
    r#"
    INSERT INTO transcripts
        (id, meeting_id, transcript, timestamp, summary, action_items, key_points,
         audio_start_time, audio_end_time, duration)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    "#,
    Uuid::new_v4().to_string(),
    meeting_id,
    "Welcome everyone, today we will discuss the Q3 roadmap.",
    chrono::Utc::now().to_rfc3339(),
    Some("Introductory remarks."),
    None::<String>,
    Some("Key point: Q3 roadmap."),
    Some(0.0),
    Some(5.2),
    Some(5.2)
)
.execute(&DB::conn())
.await?;

Tracking Summary Job Lifecycle

Initialize a pending job and update it upon LLM completion:

// Create pending entry
sqlx::query!(
    r#"
    INSERT INTO summary_processes
        (meeting_id, status, created_at, updated_at)
    VALUES (?, 'pending', ?, ?)
    "#,
    meeting_id,
    Utc::now(),
    Utc::now()
)
.execute(&DB::conn())
.await?;

// Update with results
let final_summary = serde_json::json!({
    "summary": "We agreed on Q3 milestones...",
    "action_items": ["Prepare slide deck", "Assign owners"]
});

sqlx::query!(
    r#"
    UPDATE summary_processes
    SET status = 'completed',
        updated_at = ?,
        result = ?,
        end_time = ?
    WHERE meeting_id = ?
    "#,
    Utc::now(),
    final_summary.to_string(),
    Utc::now(),
    meeting_id
)
.execute(&DB::conn())
.await?;

Querying Transcripts by Meeting

Retrieve all segments for a specific meeting ordered chronologically:

let rows = sqlx::query_as!(
    crate::database::models::Transcript,
    r#"
    SELECT *
    FROM transcripts
    WHERE meeting_id = ?
    ORDER BY timestamp ASC
    "#,
    meeting_id
)
.fetch_all(&DB::conn())
.await?;

Summary

• Four normalized tables (meetings, transcripts, summary_processes, transcript_chunks) separate metadata, content, and processing state.
• Cascade deletion via ON DELETE CASCADE foreign keys ensures that removing a meeting cleans up all related transcripts and process tracking data.
• JSON storage in summary_processes.result and metadata columns allows flexible AI output schemas without schema migrations.
• Type-safe Rust mappings in frontend/src-tauri/src/database/models.rs enforce SQL schema compliance at compile time via sqlx.
• Chunking support via the transcript_chunks table enables scalable processing of long-form audio sessions.

Frequently Asked Questions

What SQLite engine does Meetily use?

Meetily uses an embedded SQLite database accessed through the sqlx Rust crate within its Tauri desktop application backend. The database file is created automatically when the application first runs, with migrations applied from frontend/src-tauri/migrations/.

How does the schema handle large meeting transcripts?

The transcript_chunks table stores partition metadata for long meetings, including chunk_size and overlap values. This allows the application to split extensive audio sessions into manageable segments for parallel processing while maintaining a reference to the parent meeting_id.

Can summaries be stored at both the segment and meeting level?

Yes. The transcripts table includes optional columns (summary, action_items, key_points) for per-segment AI annotations generated during real-time transcription. Additionally, the summary_processes table stores a complete meeting summary in its JSON result column after background processing finishes.

Where are the database connection and repository implementations located?

The connection pool wrapper is defined in frontend/src-tauri/src/database/manager.rs. Table-specific CRUD operations are implemented in frontend/src-tauri/src/database/repositories/transcript.rs for transcript operations and frontend/src-tauri/src/database/repositories/summary.rs for summary process management.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}