Bottle Counter

Production counter for a bottling line, running on a Raspberry Pi 3 Model B.

An optical sensor wired to a GPIO pin counts bottles as they pass. The Pi keeps the live count and throughput statistics in memory, drives the line's door / piston through a relay, persists periodic snapshots to SQL Server, and exposes everything over an HTTPS JSON API consumed by the operator panel.

How it works

  optical sensor ──► GPIO in  ┐
                              ├──► counting thread ──► in-memory state ──► HTTP API
  door relay     ◄── GPIO out ┘                              │
                                                             ▼
                                              statistics thread ──► SQL Server

Three worker threads run alongside the Flask server:

Thread	Job
count	Polls the sensor pin and counts rising edges. Buffers pulses and flushes them every 10 counts or once per second, so the shared state is not touched on every edge.
stats	Every 10 s, derives throughput (units/hour) and a 10-sample rolling average, and writes a count snapshot to the database.
pause	Pauses the counter at the configured times of day. Only started when AUTO_PAUSE_TIMES is set.

A separate watchdog service restarts the counter if it dies or if the Pi runs short on CPU or memory.

A run finishes automatically once current_count reaches target_count + rejects; rejected units raise the number of bottles that still have to pass the sensor.

Data model

Two databases are used, both configured by environment variable:

• an application database (read/write) with a runs table and a readings table: one row per production run, plus one row per periodic snapshot;
• an ERP database (read-only), queried for the product code, description and nominal line speed of a production order.

If the ERP is unreachable, history requests still succeed and fall back to DEFAULT_RATED_SPEED. If the database is unreachable, partial counts are dropped with a log entry rather than interrupting the count.

Installation

On a fresh Raspberry Pi OS image:

sudo ./setup.sh

This installs the system packages, creates the virtual environment, writes a .env from .env.example, and installs the two systemd units. Then fill in .env (database host, credentials, table names) and start:

sudo ./start.sh

Other commands:

sudo ./start.sh status
sudo ./start.sh logs
sudo ./manage_services.sh --uninstall

TLS is optional: place a certificate and key at TLS_CERT_PATH / TLS_KEY_PATH and the server uses HTTPS, otherwise it falls back to plain HTTP with a warning. No certificate is shipped with this repository.

Configuration

Every deployment-specific value lives in .env. See .env.example for the full list: server host/port, CORS origins, TLS paths, GPIO pins and relay polarity, automatic pause times, database connection, and the database/table names for both the application and the ERP schema.

API

Control endpoints accept POST (preferred) and GET, so the operator panel can drive them from a plain link.

Endpoint	Description
/setup/<order>/<quantity>	Look the order up in the ERP and configure a run
/counting/start	Start counting
/counting/stop	Stop the run and write the final data
/counting/pause, /counting/resume	Pause and resume
/rejects/<quantity>	Register rejected units
/door/open, /door/close	Drive the door relay
/reset	Clear the state and deactivate open runs
GET /status	Compact snapshot of the current state
GET /api/info/<points>/<order>	Full state with time series; reads history from the database when the order is not the active one
GET /debug/gpio	Live pin levels, for wiring checks

Security notes

The API has no authentication and drives physical equipment. It is meant to run on an isolated plant network, reachable only from the operator panel, with CORS_ORIGINS restricted to that panel. Do not expose it to an untrusted network as-is.

Table and database names from the environment are interpolated into SQL as identifiers, since they cannot be bound as parameters. They come from the deployment's own configuration file and never from request data; all request values are bound parameters.

Repository layout

main.py                 entry point: wiring and TLS
src/config.py           environment-driven configuration
src/counter.py          counting engine and worker threads
src/gpio_handler.py     RPi.GPIO wrapper (sensor input, door relay)
src/database.py         SQL Server access, retries, SQL statements
src/api.py              Flask routes
src/logger.py           rotating log setup
src/watchdog.py         service and resource watchdog
setup.sh                one-time installation
start.sh                start / stop / status / logs
manage_services.sh      systemd unit management

Requirements

Raspberry Pi OS, Python 3.11+, FreeTDS (freetds-dev, for pymssql), and a reachable SQL Server instance.

License

MIT