DBX History Module Architecture: Tracking and Restoring Query States

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). This comprehensive data structure captures every aspect of query execution:

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) 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). These async commands expose the storage layer to the UI:

#[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) using Pinia. This store manages local caching and optimistic updates:

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 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) 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) 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.

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