How to Access Historical Event Data from the Multi-Cam Face Tracker Database

Use the get_face_logs() method in core/database.py to query the SQLite database and retrieve filtered historical face-detection events as a list of FaceLogEntry objects.

The multi-cam face tracker persists every detection event to a local SQLite database, enabling full historical analysis and audit trails. This guide explains how to access historical event data from the database using the Python API exposed in the FaceDatabase class.

Understanding the Database Architecture

The persistence layer resides in core/database.py and consists of two primary components that handle data normalization and retrieval.

The FaceLogEntry Dataclass

The FaceLogEntry dataclass (lines 8-18) represents a single row from the face_logs table. It automatically normalizes raw SQLite values—such as converting byte-encoded timestamps to Python float types—ensuring consistent data types for downstream processing.

The FaceDatabase Class

The FaceDatabase class manages the SQLite connection lifecycle, creates required tables on first run, and exposes high-level CRUD helpers. It opens the database file (default: face_tracker.db) and provides the primary interface for accessing historical event data from the database.

Retrieving Historical Events with get_face_logs

The get_face_logs method (lines 100-165) is the canonical API for querying historical records. It constructs a dynamic SQL query based on optional filter arguments, executes parameterized queries to prevent injection, and returns a list of FaceLogEntry instances.

The method supports filtering by:

• camera_id: Integer ID of the source camera
• face_name: String label of the detected face
• start_time and end_time: Unix timestamps defining the temporal window
• limit: Maximum records to return (default 100)

Results are automatically ordered by timestamp DESC to present the most recent events first.

Basic Retrieval Script

from core.database import FaceDatabase

# Initialize connection (creates tables if missing)

db = FaceDatabase("data/face_tracker.db")

# Fetch the 50 most recent events across all cameras

events = db.get_face_logs(limit=50)

for entry in events:
    print(
        f"{entry.timestamp:.0f}: {entry.face_name} "
        f"on camera {entry.camera_id} (confidence={entry.confidence:.2f})"
    )

Filtering by Camera, Face, and Date Range

import time
from core.database import FaceDatabase

db = FaceDatabase("data/face_tracker.db")

# Define Unix timestamps for January 2024

start_ts = time.mktime(time.strptime("2024-01-01", "%Y-%m-%d"))
end_ts = time.mktime(time.strptime("2024-01-31", "%Y-%m-%d"))

# Query specific camera and face within time window

events = db.get_face_logs(
    camera_id=3,
    face_name="Alice",
    start_time=start_ts,
    end_time=end_ts,
    limit=200
)

for entry in events:
    # Each entry is a fully typed FaceLogEntry instance

    print(f"Detected {entry.face_name} at {entry.timestamp}")

Integrating Historical Data into the UI

The History Viewer (ui/history_viewer.py) demonstrates production-grade integration of the database API within a PyQt5 application. It collects filter parameters from UI widgets—date pickers, camera dropdowns, and face selection combos—and passes them to get_face_logs.

The viewer stores the raw FaceLogEntry objects as item data (Qt.UserRole) while displaying human-readable strings formatted as YYYY-MM-DD HH:MM:SS – <face> on <camera>. This pattern allows the UI to present summary information while retaining full object fidelity for detailed inspection when a user selects an item.

PyQt Integration Example

def load_history(self):
    """Slot triggered when user clicks 'Load History'."""
    # Retrieve filter values from UI controls

    camera_id = self.camera_combo.currentData()
    face_name = self.face_combo.currentData()
    start_ts = self.start_date_picker.dateTime().toSecsSinceEpoch()
    end_ts = self.end_date_picker.dateTime().toSecsSinceEpoch()
    
    # Query the database

    entries = self.database.get_face_logs(
        limit=1000,
        camera_id=camera_id,
        face_name=face_name,
        start_time=start_ts,
        end_time=end_ts
    )
    
    # Populate the QListWidget

    self.history_list.clear()
    for entry in entries:
        display_text = (
            f"{datetime.fromtimestamp(entry.timestamp):%Y-%m-%d %H:%M:%S} – "
            f"{entry.face_name} on Camera {entry.camera_id}"
        )
        item = QListWidgetItem(display_text)
        item.setData(Qt.UserRole, entry)  # Store full object for detail view

        self.history_list.addItem(item)

Summary

• The multi-cam face tracker uses SQLite for local persistence, with the default database file named face_tracker.db.
• Access historical event data from the database using the get_face_logs method in core/database.py, which returns a list of FaceLogEntry objects.
• The method supports optional filtering by camera ID, face name, time range, and result limit, using parameterized queries to prevent SQL injection.
• The FaceLogEntry dataclass normalizes raw database values into proper Python types, simplifying downstream data processing.
• The History Viewer (ui/history_viewer.py) provides a reference implementation for integrating database queries into PyQt5 applications.

Frequently Asked Questions

What database format does the multi-cam face tracker use?

The application uses SQLite, a serverless, file-based SQL database. By default, it creates a file named face_tracker.db in the working directory, though this path is configurable via config/config.yaml. SQLite was chosen for zero-configuration deployment and portability across operating systems.

How do I filter events by specific time ranges?

Pass Unix timestamps (seconds since epoch) to the start_time and end_time parameters of get_face_logs. For example, convert Python datetime objects using time.mktime() or datetime.timestamp(). The method automatically applies these as SQL BETWEEN clauses with proper parameter binding.

Can I access the database from external scripts?

Yes. The FaceDatabase class in core/database.py is designed to be imported independently of the main application. Instantiate it with the path to your face_tracker.db file, then call get_face_logs() or other CRUD methods. This allows data scientists to perform analytics, generate reports, or export data to other formats without modifying the core tracker code.

Where is the database file located?

By default, the database is created as face_tracker.db in the application's root directory. The location is controlled by the database path setting in config/config.yaml. When initializing FaceDatabase, you can override this default by passing a custom file path to the constructor, enabling centralized storage or network-mounted database files.

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