# What Database Technology Does Shadowbroker Utilize for Its Backend?

> Discover Shadowbroker's backend database technology. Learn how it uses SQLite and Python's sqlite3 module to store CCTV metadata locally.

- Repository: [Shadowbroker/Shadowbroker](https://github.com/BigBodyCobain/Shadowbroker)
- Tags: deep-dive
- Published: 2026-05-07

---

**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`](https://github.com/BigBodyCobain/Shadowbroker/blob/main/backend/services/cctv_pipeline.py) using Python's `pathlib` module:

```python
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`](https://github.com/BigBodyCobain/Shadowbroker/blob/main/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:

```python
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:

```python
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`](https://github.com/BigBodyCobain/Shadowbroker/blob/main/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`](https://github.com/BigBodyCobain/Shadowbroker/blob/main/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`](https://github.com/BigBodyCobain/Shadowbroker/blob/main/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`](https://github.com/BigBodyCobain/Shadowbroker/blob/main/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`](https://github.com/BigBodyCobain/Shadowbroker/blob/main/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.