How orx Stores Its Local SQLite Database: File Paths, WAL Mode, and Schema

The orx CLI stores all persistent state in a single SQLite file named orx.db located in a user-specific data directory, configured with Write-Ahead Logging (WAL) mode for concurrent read/write access.

The orx command-line tool from the alphaXiv/OpenResearch repository manages experiments, projects, and chat sessions through a robust local SQLite database. Understanding how this database is stored, configured, and migrated is essential for troubleshooting, backup strategies, and advanced customization of your research workflow.

Database Location and Resolution Order

The physical location of the orx.db file is determined by the Store::data_dir() method defined in [src/store.rs](https://github.com/alphaXiv/OpenResearch/blob/main/src/store.rs#L20-L30). This method implements a precedence-based resolution strategy to locate the appropriate data directory, following XDG Base Directory specifications where possible.

The resolution follows this strict order of precedence:

1. $ORX_DATA_DIR environment variable – Forces an explicit path when set, overriding all other sources via env_path("ORX_DATA_DIR").
2. settings.json configuration – Uses the dataDir key from persisted UI choices via crate::config::settings_data_dir().
3. $XDG_DATA_HOME/openresearch – Respects the XDG Base Directory specification if the environment variable is present.
4. Fallback to ~/.local/share/openresearch – The default location when no other configuration is found, resolved by xdg_default_data_dir().

Once the directory is resolved, the database connection is established at line 335 of src/store.rs:

let conn = Connection::open(dir.join("orx.db"))?;

Environment Variable Override

Setting the $ORX_DATA_DIR environment variable allows you to relocate the entire database and associated files to a custom location, such as an external drive or network storage. This is useful for managing large datasets or sharing state between machines without modifying configuration files.

Settings-Based Configuration

When users select a data directory through the orx UI, the choice persists in settings.json and takes precedence over XDG defaults. This provides a user-friendly way to manage storage without manual environment configuration.

SQLite Configuration and Write-Ahead Logging

According to the source code in src/store.rs, the database is configured for Write-Ahead Logging (WAL) mode. This is set within the Store::open_at_with_move_lock method (lines 335-338).

WAL mode is critical for orx's architecture because it allows the CLI (orx serve) to read the database while background processes (orx up) write updates without contention. The SQLite connection is explicitly configured with:

journal_mode = "WAL"

This configuration prevents read locks from blocking writers and ensures that long-running experiments can log data without freezing the interactive UI.

Database Schema and Core Tables

The initial schema is created automatically when orx.db is first opened. The table definitions reside in the CREATE TABLE block within Store::open_at_with_move_lock (lines 339-480 of src/store.rs).

The database stores the following key entities:

• runs – Records every local execution with IDs, experiment references, status timestamps, and metadata.
• local_projects – Contains project metadata including the absolute filesystem path to the repository (repo_path).
• local_experiments – Describes experiments belonging to specific projects.
• chat_sessions, chat_messages, chat_turns – Persist interactive chat state for AI agent harnesses.
• ui_state – Stores user interface preferences, onboarding flags, and window geometries.

Note that while metadata resides in SQLite, actual run logs are stored outside the database in a run-logs directory within the data folder. The database only maintains pointers and metadata for these files.

Data Integrity and Directory Migration

Before moving the data directory, orx ensures data consistency through the Store::checkpoint() method (lines 52-56 of src/store.rs). This method forces the WAL to merge back into the main .db file, ensuring that a simple file copy of orx.db captures the complete state without loose WAL segments.

The migration logic in [src/local/storage.rs](https://github.com/alphaXiv/OpenResearch/blob/main/src/local/storage.rs) handles moving the entire data directory (including orx.db) to a new location. It operates in two phases:

1. Read-only analysis – Opens the database with rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY to locate stored absolute paths.
2. Path updating – Issues UPDATE statements via Store::relocate_project_paths (referenced at lines 5-13 of src/store.rs) to rewrite filesystem references to the new location.

Programmatic Access Examples

You can interact with the storage layer directly using the Store API.

Opening the Default Database

use crate::store::Store;

// Opens or creates orx.db in the resolved data directory
let store = Store::open()?;

Opening at a Custom Path

use std::path::PathBuf;
use crate::store::Store;

let custom_dir = PathBuf::from("/tmp/my-orx-data");
let store = Store::open_at(custom_dir)?; // Opens /tmp/my-orx-data/orx.db

Checkpointing Before Backup

// Merges WAL into the main database file
store.checkpoint()?;

Relocating Project Paths

let old_path = "/old/cache/repos/owner/project".to_string();
let new_path = "/new/data/repos/owner/project".to_string();
store.relocate_project_paths(&[(old_path, new_path)])?;

Reading Workspace State

let ws = store.project_workspace_state("proj-id")?;
if let Some(state) = ws {
    println!("Workspace: {}", serde_json::to_string_pretty(&state).unwrap());
}

Summary

• orx stores all persistent data in a single SQLite file named orx.db within a user-specific data directory resolved by Store::data_dir().
• The location follows a precedence chain: $ORX_DATA_DIR > settings.json > $XDG_DATA_HOME/openresearch > ~/.local/share/openresearch.
• The database runs in WAL (Write-Ahead Logging) mode to enable concurrent reads during background writes.
• Key tables include runs, local_projects, local_experiments, and chat-related tables for AI agent state.
• The Store::checkpoint() method ensures the database is portable by merging WAL files before directory moves.
• Absolute paths stored in the database are automatically updated when migrating data directories using Store::relocate_project_paths().

Frequently Asked Questions

Where is the orx SQLite database file located on my system?

The database file is located at {data_dir}/orx.db, where data_dir is resolved by checking (in order) the $ORX_DATA_DIR environment variable, the dataDir setting in settings.json, the $XDG_DATA_HOME/openresearch directory, or finally falling back to ~/.local/share/openresearch. You can programmatically retrieve this path using Store::data_dir() in src/store.rs.

Why does orx use Write-Ahead Logging (WAL) mode for SQLite?

orx configures the SQLite database with journal_mode = "WAL" (set in Store::open_at_with_move_lock) to allow the orx serve CLI command to read the database while background processes like orx up simultaneously write experiment data. This prevents read operations from being blocked by active writes, which is essential for real-time log streaming and interactive chat sessions.

How do I safely back up or move my orx database?

First call store.checkpoint() to merge the WAL segments into the main orx.db file. Then copy or move the entire data directory. If you're changing the base path, use Store::relocate_project_paths() to update any absolute repository paths stored in the local_projects table. The migration logic in src/local/storage.rs demonstrates this safe relocation pattern.

What happens if I delete the orx.db file?

Deleting orx.db removes all metadata about your experiments, projects, and chat history, though raw log files in the run-logs/ directory will remain. The database will be recreated automatically with an empty schema the next time you run orx, but you will lose all run tracking and project associations unless you have a backup.

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