How Goose Session Storage Works with SQLite and Migrates from JSONL

Goose stores session data in an embedded SQLite database at <data_dir>/sessions/sessions.db, automatically importing legacy JSONL files and applying incremental schema migrations (versions 1–9) on application startup.

Goose, the open-source AI agent framework from Block, persists conversation history using an embedded SQLite database rather than the flat JSONL files used in earlier versions. The SessionStorage component in session_manager.rs manages connection pooling, schema versioning, and seamless migration of legacy data when the application initializes.

SQLite Database Architecture and Connection Pool

The session storage subsystem centers on the SessionStorage struct defined in crates/goose/src/session/session_manager.rs. When the application initializes, it creates a SqlitePool connection to a file-based database located at:

<data_dir>/sessions/sessions.db

The initialization logic in SessionStorage::pool() (lines 35–57) checks for the existence of the schema_version table to determine whether to create a fresh schema or run pending migrations.

• If schema_version is missing: The system calls create_schema() to build the initial tables and then triggers import_legacy() to scan for JSONL files.
• If schema exists: The system executes run_migrations() to apply any incremental updates between the current version and CURRENT_SCHEMA_VERSION (version 9).

Schema Creation and Initialization

The create_schema function (lines 66–108 in session_manager.rs) executes raw SQL to establish three core tables:

CREATE TABLE schema_version ( … );
INSERT INTO schema_version (version) VALUES (9);
CREATE TABLE sessions ( … );
CREATE TABLE messages ( … );

This initialization sets the schema version to 9 immediately, ensuring new installations skip legacy migration logic while maintaining compatibility with the current table structure.

Versioned Migration System

Goose employs an incremental migration strategy tracked by the schema_version table. The run_migrations function reads the current version, then iterates from current_version + 1 to CURRENT_SCHEMA_VERSION (9), applying each transformation via apply_migration (lines 154–240).

Key migrations include:

• Migration 4: Adds name and user_set_name columns to the sessions table
• Migration 5: Introduces the session_type column
• Migration 9: Renames the special "ACP Session" entry

Each migration runs within the same connection pool transaction, updating the schema atomically before incrementing the version counter in schema_version.

Legacy JSONL Migration Process

When Goose detects a fresh database (no schema_version table), it automatically imports historical data from the legacy JSONL format. The process flows through import_legacy() (lines 49–91) and import_legacy_session() (lines 93–145) in session_manager.rs, utilizing helpers from crates/goose/src/session/legacy.rs.

The import sequence:

1. Enumeration: legacy::list_sessions (lines 13–26) scans <data_dir>/sessions for *.jsonl files
2. Parsing: legacy::load_session (lines 31–105) reads the first line as session metadata and subsequent lines as individual messages, injecting a default metadata object when missing
3. Insertion: import_legacy_session writes the session row to the sessions table and, if conversation data exists, populates the messages table

After import, the original JSONL files remain untouched in the directory, though they are no longer referenced by the application.

Runtime Session Operations

All high-level session APIs are thin async wrappers around SQL statements using the same SqlitePool. The SessionManager façade (defined in session_manager.rs) operates as a singleton accessed via SessionManager::instance().

Core methods include:

• create_session: Persists new session metadata (lines 445–482)
• add_message: Appends conversation history to the messages table (lines 788–811)
• export_session: Serializes session data to JSON for backup (lines 666–670)
• import_session: Rehydrates JSON exports into the SQLite store (lines 671–724)

Working with Session Storage Programmatically

Creating a New Session

use goose::session::SessionManager;
use goose::session::SessionType;
use goose::config::GooseMode;
use std::path::PathBuf;

let manager = SessionManager::instance();

let session = manager
    .create_session(
        PathBuf::from("/my/project"),
        "My First Session".into(),
        SessionType::User,
        GooseMode::Auto,
    )
    .await
    .expect("failed to create session");

Adding Messages to a Session

use goose::conversation::message::{Message, MessageContent};

let msg = Message::new(
    rmcp::model::Role::User,
    chrono::Utc::now().timestamp(),
    MessageContent::Text("Hello, Goose!".into()),
);

manager
    .add_message(&session.id, &msg)
    .await
    .expect("failed to store message");

Exporting and Importing Sessions

Export sessions to JSON for backup or transfer:

let json = manager
    .export_session(&session.id)
    .await
    .expect("export failed");

std::fs::write("session_backup.json", json).unwrap();

Import JSON data into a fresh SQLite store:

let json = std::fs::read_to_string("session_backup.json").unwrap();

let imported = manager
    .import_session(&manager, &json, None)
    .await
    .expect("import failed");

Summary

• Goose uses an embedded SQLite database at <data_dir>/sessions/sessions.db for all session persistence
• The schema version table tracks incremental migrations from version 1 through 9
• On first launch, Goose automatically imports legacy JSONL files via import_legacy() before entering normal operation
• The SessionManager singleton provides async CRUD operations wrapped around a SqlitePool connection
• Migration logic lives in session_manager.rs while legacy parsing utilities reside in legacy.rs
• Sessions can be exported to JSON for backup and re-imported without data loss

Frequently Asked Questions

Where is the SQLite database file located?

Goose stores the database at <data_dir>/sessions/sessions.db, where <data_dir> resolves to the platform-specific data directory determined by crates/goose/src/config/paths.rs. On first startup, the sessions subdirectory and database file are created automatically if they do not exist.

What happens to old JSONL files after migration?

The legacy JSONL files remain in the <data_dir>/sessions directory after import but are no longer read by the application. Goose leaves them untouched as a backup precaution, though they can be manually deleted once you verify the SQLite migration succeeded.

How does Goose handle schema updates when upgrading versions?

When the application starts, SessionStorage::pool() checks the schema_version table. If the stored version is lower than CURRENT_SCHEMA_VERSION (9), run_migrations() iterates through each missing version number and applies the corresponding SQL transformations via apply_migration, updating the schema atomically before marking the new version complete.

Can I programmatically trigger a migration check?

Yes. Simply accessing the connection pool triggers the migration logic. Calling manager.pool().await (as implemented in session_manager.rs lines 35–57) automatically runs any pending migrations before returning the pool handle, ensuring the schema is current before subsequent operations.

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