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

> Discover how orx stores its local SQLite database in orx.db. Learn about file paths, WAL mode, and schema for efficient data management with the alphaXiv OpenResearch CLI.

- Repository: [alphaXiv/OpenResearch](https://github.com/alphaXiv/OpenResearch)
- Tags: internals
- Published: 2026-09-13

---

**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](https://github.com/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)](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`](https://github.com/alphaXiv/OpenResearch/blob/main/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`](https://github.com/alphaXiv/OpenResearch/blob/main/src/store.rs):

```rust
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`](https://github.com/alphaXiv/OpenResearch/blob/main/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`](https://github.com/alphaXiv/OpenResearch/blob/main/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:

```rust
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`](https://github.com/alphaXiv/OpenResearch/blob/main/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`](https://github.com/alphaXiv/OpenResearch/blob/main/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)](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`](https://github.com/alphaXiv/OpenResearch/blob/main/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

```rust
use crate::store::Store;

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

```

### Opening at a Custom Path

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

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

```

### Relocating Project Paths

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

```rust
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`](https://github.com/alphaXiv/OpenResearch/blob/main/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`](https://github.com/alphaXiv/OpenResearch/blob/main/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`](https://github.com/alphaXiv/OpenResearch/blob/main/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`](https://github.com/alphaXiv/OpenResearch/blob/main/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.