💡 Periodic Delta Sync Engine for Incremental Telemetry & Continuous Ingestion

[don-petry]

Context & Objective

As part of expanding broodminder-data beyond initial bulk historical exports, we need a lightweight, resilient Periodic Delta Sync Engine designed for regular, incremental data ingestion (e.g., hourly or daily cron jobs, home automation pollers, or background services).

Currently, scripts/extract_all.py walks large date ranges with 28-day windowing designed for deep historical backfills. While resumable, it is optimized for high-volume batch collection rather than low-latency, periodic catch-up syncs.

Problem Statement

1. API Rate Limit Conservation: BroodMinder caps external API calls at ~1,000 requests per day. Periodic pollers must minimize redundant API calls by fetching only new readings and events since the last high-water mark.
2. Late-Arriving Telemetry: BroodMinder hardware syncs over Bluetooth Low Energy (BLE) to mobile devices or cellular/Wi-Fi hubs. Readings may be buffered on physical devices for days before uploading to the cloud. A naive delta query based strictly on max(timestamp) risks missing backfilled readings that were synced late.
3. Atomic State & Checkpoints: Delta runs must record per-hive sync checkpoints atomically to prevent duplicate ingestion or data gaps across process restarts.

Proposed Architecture & Features

1. High-Water Mark with Lookback Buffer:

   • Maintain a local state store (e.g. sync_state.json or SQLite state table) tracking the last successful sync epoch per hive/device.
   • Support a configurable safety lookback window (e.g., --lookback-days 3, default 48-72 hours) during incremental syncs to capture late-arriving BLE uploads.
   • De-duplicate overlapping records against existing local datasets before appending.

2. Delta CLI Command:

   # Quick incremental catch-up sync across all active apiaries
   broodminder sync --delta
   
   # Sync with custom lookback window and append to destination
   broodminder sync --delta --lookback 48h --format ndjson,csv

3. Incremental Output Formats:

   • Append-only NDJSON / JSONL streams.
   • SQLite / DuckDB direct table upsert.
   • Webhook or event emission for downstream consumers (e.g., Home Assistant, MQTT, Prometheus export).

4. Health & Quota Monitoring:

   • Pre-flight quota check to ensure sufficient daily budget remains before starting the delta cycle.
   • Logging of delta statistics: new records ingested, late-arriving records merged, API calls consumed.

Acceptance Criteria

• Delta sync runs unattended via cron or systemd timer without human intervention.
• Safe ingestion of late-arriving readings without duplicating records.
• Zero API call waste when no new data is available.
• Integration with the unified Python SDK and CLI.