Frigate MQTT Counter Service

This service monitors Frigate NVR MQTT events to count "karung" objects after detecting both "pintu-kiri-buka" and "pintu-kanan-buka" objects.

Features

• Monitor MQTT events from Frigate NVR on topic frigate/events
• Detect both "pintu-kiri-buka" and "pintu-kanan-buka" objects
• Start 30-minute timer when both objects detected
• Count "karung" objects during timer period
• Ignore "pintu-kiri-buka" and "pintu-kanan-buka" during timer
• Save results to SQLite database with columns: camera_name, date, counter_value
• Republish counter result to MQTT topic {TOPIC}/counter/{SITE_NAME}
• Reset counter every midnight
• If timer expires (more than 30 minutes), restart detection sequence

Requirements

• Python 3.6+
• paho-mqtt
• schedule
• sqlite3 (built-in with Python)

Installation

1. Create a virtual environment:

python3 -m venv venv
source venv/bin/activate

2. Install dependencies:

pip install -r requirements.txt

Configuration

Set the following environment variables:

• FRIGATE_MQTT_HOST: Host for Frigate MQTT server (default: localhost)
• FRIGATE_MQTT_PORT: Port for Frigate MQTT server (default: 1883)
• REPORT_MQTT_HOST: Host for reporting MQTT server (default: localhost)
• REPORT_MQTT_PORT: Port for reporting MQTT server (default: 1883)
• TOPIC: Base topic for reporting MQTT server (default: frigate)
• SITE_NAME: Site name for reporting MQTT server (default: default)

Usage

Run the service:

python frigate_counter.py

Systemd Service

To run as a systemd service, copy the service file to /etc/systemd/system/:

sudo cp frigate-counter.service /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable frigate-counter.service
sudo systemctl start frigate-counter.service

Database Schema

The service creates an SQLite database (karung_counts.db) with the following table:

CREATE TABLE IF NOT EXISTS karung_counts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    camera_name TEXT NOT NULL,
    date DATE NOT NULL,
    counter_value INTEGER NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

MQTT Topics

• Subscribe to: frigate/events
• Publish to: {TOPIC}/counter/{SITE_NAME}

Architecture

The service implements a state machine with the following states:

1. Waiting for "pintu-kiri-buka" detection
2. Waiting for "pintu-kanan-buka" detection
3. Timer active - counting "karung" objects
4. Timer expired - publishing results and resetting

Error Handling

The service includes comprehensive error handling and logging for:

• MQTT connection issues
• Database errors
• Message parsing errors
• Timer expiration handling