# DBX History Module Architecture: Tracking and Restoring Query States

> Explore the DBX history module architecture, a four-layer system in Rust, SQLite, Tauri, and Pinia for robust query tracking and one-click state restoration.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: architecture
- Published: 2026-07-04

---

**DBX implements a four-layer history architecture spanning a Rust `HistoryEntry` data model, SQLite persistence with automatic pruning, async Tauri commands, and a reactive Pinia store to enable comprehensive query tracking and one-click restoration.**

The DBX database client maintains a complete audit trail of every executed query through a sophisticated history subsystem. According to the t8y2/dbx source code, this architecture coordinates between Rust core libraries and a TypeScript front-end to provide millisecond-accurate execution tracking, filtered pagination, and seamless query restoration across database sessions.

## Data Model: The HistoryEntry Struct

At the foundation of the DBX history module architecture lies the `HistoryEntry` struct defined in **[[`crates/dbx-core/src/history.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/history.rs)](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/history.rs)**. This comprehensive data structure captures every aspect of query execution:

```rust
pub struct HistoryEntry {
    pub id: String,
    pub connection_id: String,
    pub connection_name: String,
    pub database: String,
    pub sql: String,
    pub executed_at: String,
    pub execution_time_ms: u128,
    pub success: bool,
    pub error: Option<String>,
    pub activity_kind: String,
    pub operation: String,
    pub target: String,
    pub affected_rows: Option<i64>,
    pub rollback_sql: Option<String>,
    pub details_json: Option<String>,
}

```

The `activity_kind` field enables categorical filtering, distinguishing between standard queries, AI analyses, and other operations. A `MAX_HISTORY` constant caps retention at **1,000 entries**, preventing unbounded database growth while maintaining recent context.

## Persistence Layer: SQLite Storage

The `Storage` implementation in **[[`crates/dbx-core/src/storage.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/storage.rs)](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/storage.rs)** handles all database interactions using a blocking thread pool to keep the Tauri UI responsive.

**Saving entries** occurs via `Storage::save_history_entry`, which inserts new records and immediately trims excess history using a `DELETE` statement that retains only the most recent 1,000 entries by timestamp.

**Loading entries** supports both pagination and filtering through `Storage::load_history_entries`. The method constructs parameterized queries using `LIMIT ? OFFSET ?` for pagination and optional `WHERE activity_kind = ?` filtering for categorized views.

**Maintenance operations** include `Storage::clear_history` for bulk deletion and `Storage::delete_history_entry` for removing specific records by ID. All methods execute within `with_conn`, which offloads SQLite operations to a dedicated thread pool.

## API Layer: Tauri Commands

The bridge between Rust back-end and TypeScript front-end resides in **[[`src-tauri/src/commands/history.rs`](https://github.com/t8y2/dbx/blob/main/src-tauri/src/commands/history.rs)](https://github.com/t8y2/dbx/blob/main/src-tauri/src/commands/history.rs)**. These async commands expose the storage layer to the UI:

```rust
#[tauri::command]
pub async fn save_history(state: State<'_, Arc<AppState>>, entry: HistoryEntry) -> Result<(), String> {
    state.storage.save_history_entry(&entry).await
}

```

The `load_history` command accepts `limit`, `offset`, and an optional `activity_kind` parameter, enabling the front-end to implement infinite-scroll lists and filtered views. All commands return `Result<..., String>` to propagate errors as plain-text messages to the UI.

## Front-End State Management: Pinia Store

The reactive state layer lives in **[[`apps/desktop/src/stores/historyStore.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/stores/historyStore.ts)](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/stores/historyStore.ts)** using Pinia. This store manages local caching and optimistic updates:

```typescript
export const useHistoryStore = defineStore("history", () => {
  const entries = ref<HistoryEntry[]>([]);
  const loading = ref(false);

  async function load() {
    loading.value = true;
    entries.value = await api.loadHistory(200, 0);
    loading.value = false;
  }

  async function add(entry: Omit<HistoryEntry, "id" | "executed_at">) {
    const full: HistoryEntry = {
      ...entry,
      id: uuid(),
      executed_at: new Date().toISOString(),
    };
    await api.saveHistory(full);
    entries.value.unshift(full);
    if (entries.value.length > 200) entries.value.pop();
  }

  async function remove(id: string) {
    await api.deleteHistoryEntry(id);
    entries.value = entries.value.filter((e) => e.id !== id);
  }

  async function clear() {
    await api.clearHistory();
    entries.value = [];
  }

  return { entries, loading, load, add, remove, clear };
});

```

The store maintains a client-side cache of 200 entries (distinct from the 1,000-entry SQLite limit), providing instantaneous UI feedback while the back-end handles persistent storage.

## Query Restoration Workflow

Restoring a previous query state follows a coordinated flow across all four architectural layers:

1. **Selection**: The user selects a history entry from the virtual list, accessing the full `HistoryEntry` object including `sql`, `connection_name`, and `database` fields.

2. **State Hydration**: The front-end extracts [`entry.sql`](https://github.com/t8y2/dbx/blob/main/entry.sql) and connection metadata to pre-fill the query editor and connection selector.

3. **Re-execution**: Upon triggering execution, the system creates a new `HistoryEntry` with a fresh UUID and timestamp, preserving the original query text while recording new execution metrics.

4. **Synchronization**: The new entry flows through `save_history` → `Storage::save_history_entry` → SQLite, while the Pinia store simultaneously updates its local `entries` array via the `add()` method.

## Summary

- **DBX history module architecture** consists of four coordinated layers: a Rust `HistoryEntry` struct, SQLite persistence via `Storage`, Tauri command bridges, and a Pinia front-end store.
- **Data retention** is capped at 1,000 entries in SQLite (`MAX_HISTORY`), with the front-end caching the most recent 200 for performance.
- **Query restoration** leverages the complete `HistoryEntry` metadata to pre-fill connection details and SQL text.
- **Filtering and pagination** are supported at the storage layer via `activity_kind` parameters and `LIMIT/OFFSET` clauses.
- **Thread safety** is maintained through `with_conn`, which isolates blocking SQLite operations from the async Tauri runtime.

## Frequently Asked Questions

### How does DBX prevent the history database from growing indefinitely?

The `Storage::save_history_entry` method in **[[`crates/dbx-core/src/storage.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/storage.rs)](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/storage.rs)** automatically enforces a hard limit of 1,000 entries defined by the `MAX_HISTORY` constant. After inserting a new record, it executes a `DELETE` statement that removes all entries except the most recent 1,000 based on the `executed_at` timestamp, ensuring the SQLite database never exceeds manageable size.

### Can I filter history entries by specific activity types?

Yes, the `load_history` Tauri command accepts an optional `activity_kind` parameter that filters entries at the database level. When provided, the `Storage::load_history_entries` method appends `WHERE activity_kind = ?` to the SQL query, allowing the UI to display only specific categories such as `"query"` or `"ai_analysis"` without loading irrelevant records.

### How does the front-end synchronize with the back-end history state?

The Pinia store in **[[`apps/desktop/src/stores/historyStore.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/stores/historyStore.ts)](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/stores/historyStore.ts)** uses optimistic synchronization. When adding an entry, it immediately updates the local `entries` array while simultaneously calling `api.saveHistory()` to persist the change. For deletions and clearing, it awaits the Tauri command completion before mutating the local state, ensuring consistency between the SQLite database and the reactive UI.

### What metadata is preserved when restoring a query from history?

When restoring, the system utilizes the `sql`, `connection_name`, `database`, and `connection_id` fields from the `HistoryEntry` struct. This allows the UI to automatically select the correct database connection and pre-populate the SQL editor with the original query text, though execution creates a new history entry with updated timestamps and performance metrics rather than modifying the original record.