# How SpiderFoot Handles Data Storage and Database Schema in SQLite

> Discover how SpiderFoot manages data storage using SQLite and its database schema. Learn about thread-safe wrappers, schema creation, event logging, and scan results.

- Repository: [Steve Micallef/spiderfoot](https://github.com/smicallef/spiderfoot)
- Tags: internals
- Published: 2026-08-15

---

**SpiderFoot uses a single SQLite file with a thread-safe wrapper class (`SpiderFootDb`) that manages schema creation, event logging, scan results, and correlation data through a re-entrant locking mechanism.**

SpiderFoot's data persistence layer revolves around one central component: the **`SpiderFootDb`** class in [`spiderfoot/db.py`](https://github.com/smicallef/spiderfoot/blob/main/spiderfoot/db.py). This lightweight, file-based approach eliminates external database dependencies while providing robust storage for reconnaissance data. Understanding how SpiderFoot handles data storage and its database schema is essential for developers extending the platform or administrators managing large-scale scans.

## SQLite as the Core Storage Engine

SpiderFoot chooses **SQLite** deliberately for its portability and zero-configuration deployment. When you initialize a `SpiderFootDb` instance, you provide a path via the `__database` key in the options dictionary. The constructor then ensures the directory exists, establishes the connection, and automatically provisions the schema if tables are missing or outdated.

```python
from spiderfoot.db import SpiderFootDb

opts = {'__database': '/tmp/spiderfoot.db'}
db = SpiderFootDb(opts, init=True)  # Creates schema on first run

```

The schema is defined in `createSchemaQueries` (lines 37–109 in [`spiderfoot/db.py`](https://github.com/smicallef/spiderfoot/blob/main/spiderfoot/db.py)) and applied through the `create()` method when `init=True` is passed or when a pre-4.0 database is detected.

## Thread-Safe Database Access

SpiderFoot's scanning engine runs multiple modules concurrently, making thread safety critical. The `SpiderFootDb` class implements this through:

- **`dbhLock`**: A class-level `threading.RLock()` (re-entrant lock) declared at lines 34–36
- **Context manager pattern**: Every database method uses `with self.dbhLock:` before cursor operations

This design prevents race conditions without sacrificing performance for single-threaded operations.

```python

# Pattern used throughout spiderfoot/db.py

with self.dbhLock:
    self.dbh.execute("SELECT ...")
    self.dbh.commit()

```

## Custom REGEXP Function for SQLite

SQLite lacks native regular expression support, so `SpiderFootDb` registers a Python callback at initialization (lines 30–48). This enables regex-based searches through the `search()` method:

```python
def regexp(self, expression, data):
    return bool(re.compile(expression, re.IGNORECASE).search(data))

```

The function is registered via `self.dbh.create_function("REGEXP", 2, self.regexp)`, allowing SQL queries like `WHERE data REGEXP ?`.

## SpiderFoot Database Schema: Complete Table Reference

The schema spans seven core tables plus supporting indexes. Here's the authoritative breakdown:

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `tbl_event_types` | Catalog of all reconnaissance data types | `event`, `event_descr`, `event_raw`, `event_type` |
| `tbl_config` | Global and module-specific settings | `scope`, `opt`, `val` |
| `tbl_scan_instance` | Per-scan metadata and status | `guid`, `name`, `seed_target`, `status`, timestamps |
| `tbl_scan_log` | Structured logging output | `scan_instance_id`, `generated`, `component`, `type`, `message` |
| `tbl_scan_config` | Scan-level configuration overrides | `scan_instance_id`, `component`, `opt`, `val` |
| `tbl_scan_results` | All discovered intelligence | `scan_instance_id`, `hash`, `type`, `confidence`, `risk`, `module`, `data`, `source_event_hash` |
| `tbl_scan_correlation_results` | Triggered correlation rules | `title`, `rule_risk`, `rule_id`, `rule_name`, `rule_logic` |
| `tbl_scan_correlation_results_events` | Junction table linking correlations to events | `correlation_id`, `event_hash` |

### Event Type Catalog (`tbl_event_types`)

SpiderFoot hard-codes 175+ event types in `eventDetails` (lines 111–285), ranging from `IP_ADDRESS` and `DOMAIN_NAME` to `VULNERABILITY_CVE_HIGH` and `DARKNET_MENTION`. The constructor automatically populates `tbl_event_types` on first run, ensuring referential integrity with `tbl_scan_results`.

### Performance-Oriented Indexes

Lines 101–108 in [`spiderfoot/db.py`](https://github.com/smicallef/spiderfoot/blob/main/spiderfoot/db.py) create targeted indexes:

- **`idx_scan_results_type`**: Accelerates filtering by event type
- **`idx_scan_logs`**: Speeds log retrieval during scan monitoring
- Additional indexes on `hash`, `scan_instance_id`, and `generated` columns

## High-Level Database API Methods

The `SpiderFootDb` class abstracts raw SQL through purpose-built methods:

### Scan Lifecycle Management

```python

# Create a new scan instance

instance_id = "123e4567-e89b-12d3-a456-426614174000"
db.scanInstanceCreate(instance_id, "Demo Scan", "example.com")

# Generate structured log entries

db.scanLogEvent(
    instance_id,
    classification="INFO",
    message="Started scanning example.com",
    component="SpiderFoot"
)

```

### Result Storage and Retrieval

```python

# Query scan result summaries (count by type, module, or risk)

summary = db.scanResultSummary(instance_id, by="type")

# Full-text search across all scan data

criteria = {'type': 'IP_ADDRESS', 'value': '192.168.1.1'}
matches = db.search(criteria, filterFp=True)  # Excludes false positives

```

### Configuration Management

```python

# Global configuration retrieval

cfg = db.configGet()
print(cfg['GLOBAL:max_threads'])

# Per-scan overrides stored in tbl_scan_config

```

## Correlation Engine Storage

SpiderFoot's correlation system (analyzing relationships between discovered data) uses two dedicated tables:

- **`tbl_scan_correlation_results`**: Stores triggered rules with risk scores and logic details
- **`tbl_scan_correlation_results_events`**: Maps each correlation back to its constituent events via `event_hash`

This design enables efficient retrieval of "why this matters" context for any discovered intelligence.

## Key Source Files for Data Storage

| File | Responsibility |
|------|---------------|
| [`spiderfoot/db.py`](https://github.com/smicallef/spiderfoot/blob/main/spiderfoot/db.py) | **Core storage layer**: `SpiderFootDb` class, schema definition, all DB operations |
| [`sf.py`](https://github.com/smicallef/spiderfoot/blob/main/sf.py) | CLI entry point: creates `SpiderFootDb` instance, manages scan orchestration |
| [`sfcli.py`](https://github.com/smicallef/spiderfoot/blob/main/sfcli.py) | Interactive shell: database initialization for command-line workflows |
| `modules/*.py` | Individual scanners: call `db.scanResult*` methods to persist findings |

## Summary

- SpiderFoot uses **single-file SQLite storage** via the `SpiderFootDb` class in [`spiderfoot/db.py`](https://github.com/smicallef/spiderfoot/blob/main/spiderfoot/db.py)
- **Re-entrant locking (`dbhLock`)** ensures thread safety across concurrent module execution
- **Custom REGEXP function** enables regex searches in SQL where native support is absent
- **Seven core tables** handle event types, configuration, scan metadata, logs, results, and correlations
- **Automatic schema migration** occurs on initialization for new or pre-4.0 databases
- **High-level API methods** (`scanInstanceCreate`, `scanLogEvent`, `search`, `configGet`) provide clean Python interfaces

## Frequently Asked Questions

### What database does SpiderFoot use?

SpiderFoot uses **SQLite** as its exclusive database engine. All data resides in a single file specified by the `__database` configuration option, making backups trivial (file copy) and eliminating external database dependencies.

### Is SpiderFoot's database access thread-safe?

Yes. The `SpiderFootDb` class implements thread safety through a **class-level re-entrant lock (`dbhLock`)** that wraps every database operation. This allows multiple scanning modules to write results concurrently without corruption.

### How does SpiderFoot enable regex searches in SQLite?

Since SQLite lacks native regex support, `SpiderFootDb` registers a **custom Python function named `REGEXP`** during initialization. This function compiles patterns using Python's `re` module and executes matches against column data, exposed to SQL as `WHERE column REGEXP ?`.

### Can I query SpiderFoot's database directly?

Yes. The SQLite file is standard and queryable with any SQLite client. However, schema knowledge is essential—key tables include `tbl_scan_results` for discovered data, `tbl_scan_instance` for scan metadata, and `tbl_event_types` for the data type catalog. Direct writes are discouraged as they bypass the locking mechanism.