What Database Technology Does Shadowbroker Utilize for Its Backend?

Shadowbroker uses SQLite as its backend database technology, storing CCTV camera metadata in a local file at backend/data/cctv.db using Python's built‑in sqlite3 module.

Shadowbroker is an open-source intelligence platform that aggregates CCTV camera feeds. According to the source code in the BigBodyCobain/Shadowbroker repository, the application persists camera metadata using SQLite, an embedded relational database that requires no separate server installation. This file-based approach keeps the backend lightweight and self-contained while providing full SQL query capabilities.

SQLite Implementation in Shadowbroker

Unlike client-server databases such as PostgreSQL or MySQL, Shadowbroker utilizes SQLite, a serverless, embedded database engine. The backend stores all persistent data in a single file located at backend/data/cctv.db, which the application accesses through Python's standard sqlite3 library. This architecture eliminates external dependencies and simplifies deployment scenarios.

Database File Location and Connection Management

The database path is defined centrally in backend/services/cctv_pipeline.py using Python's pathlib module:

DB_PATH = Path(__file__).resolve().parent.parent / "data" / "cctv.db"

The code ensures the parent directory exists before attempting to write data, creating the backend/data/ directory automatically if it is missing.

Database Schema and Initialization

The init_db() function in backend/services/cctv_pipeline.py handles schema creation and migration. It creates the cameras table with a comprehensive schema designed for geospatial and media metadata:

def init_db():
    # Ensure the data directory exists

    DB_PATH.parent.mkdir(parents=True, exist_ok=True)

    # Open (or create) the SQLite database file

    conn = sqlite3.connect(str(DB_PATH))
    cursor = conn.cursor()

    # Create the `cameras` table if it does not already exist

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS cameras (
            id TEXT PRIMARY KEY,
            source_agency TEXT,
            lat REAL,
            lon REAL,
            direction_facing TEXT,
            media_url TEXT,
            media_type TEXT,
            refresh_rate_seconds INTEGER,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    # Add a column if the schema evolves

    cursor.execute("PRAGMA table_info(cameras)")
    columns = {str(row[1]) for row in cursor.fetchall()}
    if "media_type" not in columns:
        cursor.execute("ALTER TABLE cameras ADD COLUMN media_type TEXT")

    conn.commit()
    conn.close()

This implementation uses CREATE TABLE IF NOT EXISTS to prevent errors if the database is already initialized, and includes migration logic via PRAGMA table_info to add the media_type column to legacy databases.

Querying Camera Metadata

Data retrieval is handled by the get_all_cameras() function, which converts SQLite rows into Python dictionaries for downstream processing:

def get_all_cameras() -> List[Dict[str, Any]]:
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM cameras")
    rows = cursor.fetchall()
    conn.close()

    cameras = []
    for row in rows:
        cam = dict(row)
        # Determine media type if missing

        cam["media_type"] = cam.get("media_type") or _detect_media_type(cam.get("media_url", "")) or "image"
        cameras.append(cam)
    return cameras

The function sets conn.row_factory = sqlite3.Row to enable column-based access, then transforms each row into a standard Python dictionary. It also implements fallback logic for records missing the media_type field, ensuring backward compatibility.

Key Files in the Database Architecture

The SQLite integration spans several critical files in the repository:

• backend/services/cctv_pipeline.py – Contains the core database logic, including init_db() for schema management and get_all_cameras() for data retrieval.
• backend/main.py – The FastAPI entry point that initializes the application environment and indirectly utilizes the SQLite database through the ingestion pipeline.
• backend/tests/test_sigint_cctv_accuracy.py – Unit tests that verify SQLite-based data ingestion functions correctly.

Summary

• Shadowbroker utilizes SQLite as its backend database technology, implemented through Python's sqlite3 module.
• The database file resides at backend/data/cctv.db and is created automatically on first run.
• The schema is defined in backend/services/cctv_pipeline.py with a cameras table supporting geolocation, media URLs, and refresh rates.
• Connection handling is explicit, with each function opening and closing database connections to ensure data integrity.

Frequently Asked Questions

What database technology does Shadowbroker use for its backend?

Shadowbroker uses SQLite, an embedded, file-based relational database. According to the source code, it stores CCTV metadata in a local .db file rather than connecting to a remote database server, which simplifies deployment and reduces infrastructure requirements.

Where is the Shadowbroker database file located?

The database file is located at backend/data/cctv.db relative to the project root. The init_db() function in backend/services/cctv_pipeline.py automatically creates both the directory structure and the database file using DB_PATH.parent.mkdir(parents=True, exist_ok=True) if they do not already exist.

How does Shadowbroker handle database schema changes?

The application includes basic schema migration logic. When initializing the database, the code executes PRAGMA table_info(cameras) to inspect existing columns. If the media_type column is missing, it automatically executes ALTER TABLE cameras ADD COLUMN media_type TEXT to update the schema without requiring manual migration scripts.

What data does the Shadowbroker SQLite database store?

The cameras table stores comprehensive metadata including unique identifiers (id), source agencies, geographic coordinates (lat, lon), facing direction, media URLs, media types, refresh rates, and timestamps. This schema supports the aggregation and querying of CCTV camera data across different jurisdictions.

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