# How the History and Telemetry Database Works in Destructive Command Guard (dcg)

> Explore how Destructive Command Guard's history and telemetry database works. Learn about sub-millisecond latency, full-text search, and analytics with this SQLite solution.

- Repository: [Jeff Emanuel/destructive_command_guard](https://github.com/Dicklesworthstone/destructive_command_guard)
- Tags: internals
- Published: 2026-07-14

---

**TLDR:** Destructive Command Guard (dcg) records every shell command evaluation to a local SQLite database using a background writer thread, enabling sub-millisecond hook latency while providing full-text search, block-rate analytics, and graduated response capabilities.

At the core of Destructive Command Guard’s observability pipeline sits the **history and telemetry database**, a compact SQLite file that persists every command intercepted by the hook. This database powers real-time statistics, automated pruning, and intelligent blocking decisions without blocking the interactive shell.

## Database Schema and Core Tables

The schema is defined in [`src/history/schema.rs`](https://github.com/Dicklesworthstone/destructive_command_guard/blob/main/src/history/schema.rs) and initializes lazily via `HistoryDb::initialize_schema()` (lines 1445‑1475). When a database is opened, this function also executes any pending migrations via `run_migrations` to bring older databases up to the current schema version **6** (`CURRENT_SCHEMA_VERSION`).

### The commands Table

The primary `commands` table stores one row per evaluated command with columns for:

- `timestamp` – ISO‑8601 evaluation time.
- `agent_type` – The AI agent that produced the command (e.g., `claude_code`, `codex`).
- `working_dir` – Current working directory at evaluation time.
- `command` – Raw command line string.
- `outcome` – One of `allow`, `deny`, `warn`, or `bypass` (defined in the `Outcome` enum, lines 667‑689).
- `pack_id`, `pattern_name`, `rule_id` – Optional identifiers for the matching security rule.
- `eval_duration_us` – Microseconds spent evaluating the command.
- `session_id`, `hostname`, `exit_code`, `allowlist_layer`, `bypass_code` – Optional metadata fields.

### Full‑Text Search and Migration Tracking

Beyond the core table, the schema includes:

- **`commands_fts`** – A virtual FTS5 table indexing the `command` column for fast full‑text search.
- **`schema_version`** – Tracks migration history with `version`, `applied_at`, and `description` columns.
- **`stats_cache`** and **`suggestion_audit`** – Auxiliary tables added in later migrations for real‑time statistics and bypass auditing.

## Opening and Configuring the Database

Call `HistoryDb::open` (lines 656‑688 in [`src/history/schema.rs`](https://github.com/Dicklesworthstone/destructive_command_guard/blob/main/src/history/schema.rs)) to open or create the database:

```rust
use dcg::history::HistoryDb;

let db = HistoryDb::open(None)?;  // Default path: $HOME/.config/dcg/history.db

```

The method resolves the path, creates parent directories if needed, and invokes `initialize_schema`. You can override the default location by setting the **`DCG_HISTORY_DB`** environment variable (`ENV_HISTORY_DB_PATH`). To disable all history collection, set **`DCG_HISTORY_DISABLED=1`**, which causes `HistoryDb::open` to return `HistoryError::Disabled`.

For testing, use `HistoryDb::open_in_memory()` to create a transient database.

## Asynchronous Write Architecture

To maintain hook latency under one millisecond, dcg performs all database writes off the main thread using a dedicated writer.

### The HistoryWriter Thread

`HistoryWriter::new` (lines 152‑180 in [`src/history/mod.rs`](https://github.com/Dicklesworthstone/destructive_command_guard/blob/main/src/history/mod.rs)) creates a `std::sync::mpsc` channel and spawns a worker thread named `dcg-history-writer`. The writer maintains a **session ID** (generated via `generate_session_id()`) that attaches to every `CommandEntry` for correlation.

### Batching and Flush Handles

The worker receives `HistoryMessage::Entry(Box<CommandEntry>)` values and buffers them according to `WorkerConfig` settings (`batch_size` and `flush_interval`). When the batch limit is reached or the interval expires, the worker calls `HistoryDb::log_command` (lines 1286‑1302) for each entry.

For testing or graceful shutdown, the `HistoryFlushHandle::flush_sync` method blocks until all pending writes complete.

## Command Entry Structure

The `CommandEntry` struct (lines 320‑380 in [`src/history/schema.rs`](https://github.com/Dicklesworthstone/destructive_command_guard/blob/main/src/history/schema.rs)) captures everything required for later analysis. Key fields include:

- **`command_hash`** – A SHA‑256 hash computed by `CommandEntry::command_hash` (lines 830‑846) used for fast deduplication and indexing.
- **`outcome`** – An enum variant indicating whether the command was allowed, denied, warned, or bypassed.
- **`eval_duration_us`** – Performance telemetry for the evaluation itself.

Entries are inserted via `HistoryDb::log_command`, which returns the SQLite row ID.

## Indexing for Performance

During initialization, `HistoryDb::initialize_schema` creates several indexes (lines 1485‑1498) to keep analytics queries sub‑millisecond:

- **`idx_commands_command_hash_outcome_timestamp`** – Accelerates `count_command_blocks_in_window` for repeat-block detection.
- **`idx_commands_rule_outcome_timestamp`** – Enables fast lookups by `rule_id`.
- **`idx_commands_timestamp`**, **`idx_commands_outcome`**, **`idx_commands_working_dir`** – Support statistical queries and filtering.

These indexes ensure the database remains responsive even with large historical datasets.

## Analytics and Telemetry APIs

The `HistoryDb` type in [`src/history/schema.rs`](https://github.com/Dicklesworthstone/destructive_command_guard/blob/main/src/history/schema.rs) exposes methods for telemetry and maintenance:

- **`compute_stats`** and **`compute_stats_with_trends`** – Return `HistoryStats` containing total commands, block rates, top patterns, per‑project stats, and performance percentiles.
- **`count_command_blocks_in_window`** – Counts recent blocks for a specific command within a time window (used for graduated responses).
- **`get_history_count_by_pattern`** – Counts blocks for a specific rule ID.
- **`prune_older_than_days`** – Deletes rows older than a configurable retention period and rebuilds the FTS table.
- **`vacuum`** – Reclaims disk space after pruning.

All query methods use the `inline_params` helper (lines 161‑205) for safe parameter binding.

### Graduated Response Queries

Before denying a command, the hook queries `get_history_count` to check how many times the same command was blocked recently. If the count exceeds a threshold within a configurable window (e.g., 10 minutes), dcg emits a stricter response or escalates the alert.

### Background Pruning

When `auto_prune` is enabled in `WorkerConfig`, the writer thread periodically invokes `prune_older_than_days`. This routine:

1. Calculates a cutoff timestamp (`Utc::now() - Duration::days(retention_days)`).
2. Deletes matching rows.
3. Rebuilds the FTS virtual table (`rebuild_fts`) because the FTS5 implementation does not support incremental deletions.

## Practical Usage Examples

### Logging a Command Synchronously

```rust
use dcg::history::{HistoryDb, CommandEntry, Outcome};

fn example() -> Result<(), dcg::history::HistoryError> {
    let db = HistoryDb::open(None)?;

    let entry = CommandEntry {
        timestamp: chrono::Utc::now(),
        agent_type: "claude_code".into(),
        working_dir: std::env::current_dir()?.to_string_lossy().into(),
        command: "rm -rf /important".into(),
        outcome: Outcome::Deny,
        pack_id: Some("core.filesystem".into()),
        pattern_name: Some("rm-rf".into()),
        ..Default::default()
    };

    let row_id = db.log_command(&entry)?;
    println!("Logged command as row {}", row_id);
    Ok(())
}

```

### Using the Asynchronous Writer

```rust
use dcg::history::{HistoryWriter, HistoryConfig, HistoryMessage};

fn init_writer() -> HistoryWriter {
    let cfg = HistoryConfig::default();
    HistoryWriter::new(None, &cfg)  // None uses default DB path
}

fn record_command(writer: &HistoryWriter, entry: CommandEntry) {
    if let Some(sender) = &writer.sender {
        let _ = sender.send(HistoryMessage::Entry(Box::new(entry)));
    }
}

```

### Querying Recent Blocks for Graduated Response

```rust
use dcg::history::HistoryDb;
use chrono::Duration;

fn check_recent_blocks(db: &HistoryDb, cmd: &str) -> Result<u32, dcg::history::HistoryError> {
    let window = Duration::minutes(10);
    db.count_command_blocks_in_window(cmd, window)
}

```

### Generating Period Statistics

```rust
use dcg::history::HistoryDb;

fn print_stats(db: &HistoryDb) -> Result<(), dcg::history::HistoryError> {
    let stats = db.compute_stats_with_trends(7)?;
    println!("Total commands: {}", stats.total_commands);
    println!("Block rate: {:.2}%", stats.block_rate * 100.0);
    
    for pattern in &stats.top_patterns {
        println!("  {}: {}", pattern.name, pattern.count);
    }
    Ok(())
}

```

## Summary

- **Storage**: A single SQLite file (default: `$HOME/.config/dcg/history.db`) with schema version 6, featuring a `commands` table and FTS5 full‑text search.
- **Performance**: An asynchronous `HistoryWriter` thread batches inserts to keep hook latency under one millisecond.
- **Schema**: Includes `CommandEntry` with SHA‑256 hashing, outcome tracking, and optional AI agent metadata.
- **Analytics**: Indexed queries support block‑rate calculations, graduated responses, and trend analysis via `compute_stats`.
- **Maintenance**: Automated pruning and `VACUUM` operations reclaim space while preserving recent telemetry.

## Frequently Asked Questions

### Where does dcg store the history database?

By default, the database is stored at `$HOME/.config/dcg/history.db` (or equivalent on Windows). You can override this path by setting the `DCG_HISTORY_DB` environment variable before starting dcg.

### How does dcg write to the database without blocking the shell?

The hook uses `HistoryWriter::new` to spawn a dedicated background thread named `dcg-history-writer`. Commands are sent to this thread via a channel and batched according to `WorkerConfig` settings, ensuring the main hook path completes in under one millisecond.

### Can I disable telemetry collection entirely?

Yes. Set the environment variable `DCG_HISTORY_DISABLED=1`. When this is set, `HistoryDb::open` returns `HistoryError::Disabled`, and dcg operates without persisting command history.

### How does dcg use historical data to make blocking decisions?

Before denying a command, dcg calls `count_command_blocks_in_window` to check how many times the same command was blocked recently. If the count exceeds a threshold within a configurable time window, the system triggers a graduated response, potentially requiring additional confirmation or notifying administrators.