# How Goose Session Storage Works with SQLite and Migrates from JSONL

> Explore how Goose session storage uses SQLite, automatically migrating legacy JSONL data with incremental schema updates on startup. Learn more.

- Repository: [Block Open Source/goose](https://github.com/block/goose)
- Tags: internals
- Published: 2026-04-05

---

**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`](https://github.com/block/goose/blob/main/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`](https://github.com/block/goose/blob/main/crates/goose/src/session/session_manager.rs). When the application initializes, it creates a `SqlitePool` connection to a file-based database located at:

```text
<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`](https://github.com/block/goose/blob/main/session_manager.rs)) executes raw SQL to establish three core tables:

```sql
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`](https://github.com/block/goose/blob/main/session_manager.rs), utilizing helpers from [`crates/goose/src/session/legacy.rs`](https://github.com/block/goose/blob/main/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`](https://github.com/block/goose/blob/main/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

```rust
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

```rust
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:

```rust
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:

```rust
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`](https://github.com/block/goose/blob/main/session_manager.rs) while legacy parsing utilities reside in [`legacy.rs`](https://github.com/block/goose/blob/main/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`](https://github.com/block/goose/blob/main/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`](https://github.com/block/goose/blob/main/session_manager.rs) lines 35–57) automatically runs any pending migrations before returning the pool handle, ensuring the schema is current before subsequent operations.