How SpiderFoot Handles Data Storage and Database Schema in SQLite

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

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

# 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:

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

# 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

# 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

# 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	Core storage layer: SpiderFootDb class, schema definition, all DB operations
sf.py	CLI entry point: creates SpiderFootDb instance, manages scan orchestration
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
• 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.

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