• SQL Recorder — Transparently captures all SQL queries across web requests and background jobs with zero application changes
• Index Manager — Analyzes recorded queries, generates index candidates, backtests them against real workloads, and drops indexes that don't help
• DocType Flow Tracer — Traces document lifecycle chains to visualize which DocTypes trigger creation of other DocTypes
• Dashboards — Built-in dashboards for index management and site overview
• Unused Indexes Report — Identifies indexes that aren't being used by your workload
• CLI Tools — Full CLI for recording, processing, optimizing, and managing indexes

Record  →  Analyze  →  Optimize

1. Record — When enabled, the SQL Recorder monkey-patches frappe.db.sql to capture every query executed during web requests and background jobs. Queries are aggregated in Redis with occurrence counts.

2. Analyze — A scheduled job processes recorded queries: runs EXPLAIN EXTENDED on each, extracts table access patterns, and stores structured results as MariaDB Query / Table / Index documents.

3. Optimize — The Index Manager parses WHERE clauses and ORDER BY expressions to generate index candidates, creates them, backtests with ANALYZE to measure actual improvement, and drops indexes that don't reduce rows scanned.

bench get-app https://github.com/gavindsouza/toolbox.git
bench --site your-site install-app toolbox

Note: Toolbox currently supports MariaDB only.

1. Navigate to ToolBox Settings
2. Enable SQL Recorder — starts capturing queries immediately
3. Enable Index Manager — enables automatic index optimization
4. Set processing intervals (Hourly or Daily)
5. Save — scheduled jobs are created automatically

Toolbox will begin recording queries and periodically process them to generate optimized indexes.

# Start/stop recording
bench --site your-site sql-recorder start
bench --site your-site sql-recorder stop

# Discard recorded data
bench --site your-site sql-recorder drop

# Process recorded queries (run EXPLAIN, store metadata)
bench --site your-site sql-manager process

# Deduplicate stored query records
bench --site your-site sql-manager cleanup

# Generate and apply optimized indexes (with backtesting)
bench --site your-site index-manager optimize

# Optimize a specific table
bench --site your-site index-manager optimize --table tabSales\ Invoice

# Skip backtesting (faster, but keeps all generated indexes)
bench --site your-site index-manager optimize --skip-backtest

# Filter by minimum query occurrence
bench --site your-site index-manager optimize --sql-occurrence 100

# Show all toolbox-generated indexes
bench --site your-site index-manager show-toolbox-indexes

# Remove all toolbox-generated indexes
bench --site your-site index-manager drop-toolbox-indexes

# Start tracing specific DocTypes
bench --site your-site doctype-manager trace on -d "Sales Invoice,Payment Entry"

# Stop tracing
bench --site your-site doctype-manager trace off -d "Sales Invoice"

# View traced flows
bench --site your-site doctype-manager trace draw

# Check tracing status
bench --site your-site doctype-manager trace status

# Purge trace data
bench --site your-site doctype-manager trace purge -d "Sales Invoice"

• Table health checks — ghost data, dangling columns (via after_migrate hooks + on-demand)
• Backup quality auditing
• Resource utilization analysis — queue utilization, CPU usage, configuration recommendations
• Security auditing — semgrep rules, whitelisted API checks, guest-accessible endpoints
• Suspicious activity tracking — permission errors, access log summaries
• Permission gap detection — data access vs. list view permission mismatches

1. Fork the repository
2. Create a feature branch (git checkout -b feature/your-feature)
3. Write tests for new functionality
4. Submit a pull request

# Run tests locally
bench --site your-site run-tests --app toolbox

GNU Affero General Public License v3.0 — see LICENSE for details.

Copyright © 2023, Gavin D'souza