Skip to content
andriiemail
2025Architecture & lead

ChaterBox

A control plane for a network of physical Raspberry Pi devices across a tree of companies — schedule audio, push messages, watch live device telemetry.

Physical devices reporting live telemetry to one dashboard.

Role
Tech lead + PM — 1 backend, 1 frontend, plus design
Timeline
Multi-month contract
Stack
ReactTypeScriptNodePostgreSQLPostGIS

The business problem

An Amsterdam entrepreneur wanted to sell a product: a network of small physical devices (Raspberry Pi units, "ChaterBox") installed at business locations that play scheduled audio — announcements, messages, music. The pain it solved: a company with many locations and many devices had no central way to organize them and push the right message to the right places on a schedule. You'd be walking device to device. The product was a control plane for all of it — a tree of companies and subsidiaries, with devices hanging off each, driven from one dashboard.

The constraints

  • Hardware in the loop. This wasn't just a web app — real Raspberry Pi devices had to report in and receive messages. That constrains everything: a device can be offline, slow, or on bad Wi-Fi.
  • A small team I had to run, not just code in. One backend, one frontend, plus design — I was tech lead and PM: I reviewed and fixed their code, set scope, and paid them.
  • A client who kept moving. The business idea — and the Raspberry Pi direction — wasn't settled. That's the constraint that eventually ended the project.

What I built and why

A React/Redux dashboard over a Node + PostgreSQL backend, with Google OAuth for sign-in. The company hierarchy is stored as an adjacency list — companies.parent_id points at the parent company — so a company can nest arbitrarily deep and devices attach to any node. Access is scoped through user_company_access (a user's rights follow the company subtree), and every mutation is written to audit_logs. Internationalization is a polymorphic translations table: any field of any entity can be translated by (entity_type, entity_id, field_name, lang_id), so adding a language never touches the schema — exactly what "support future languages" required. Devices, schedules (with recurrence_pattern), and message targeting (message_assignments with target_type / target_id and per-target delivery status) are all modeled.

ChaterBox company tree An adjacency-list hierarchy: companies nest arbitrarily deep via parent_id, and devices attach as leaves to a node at any level. Root company Subsidiary A Subsidiary B Branch B1 Branch B2 device device device device parent_id = null dashed → device.company_id (attaches to any node)
Company hierarchy as an adjacency list — companies nest via parent_id, and devices attach as leaves to a node at any level.

The device layer actually worked end to end: a physical Pi reports live telemetry to the server — uptime, CPU load, disk, temperature, Wi-Fi SSID and signal. This was never a mockup.

Technical decisions, with the cost of each

Decision 1 — the tree as an adjacency list in PostgreSQL, not a graph database.

The brief floated a graph DB (Neo4j) for the hierarchy. I kept it in Postgres with a self-referencing parent_id. Reason: the whole system already needed relational integrity, transactions, RBAC, and — critically — PostGIS for geolocation, all in one engine. Adding a second database for the tree alone would have split the source of truth and doubled the ops surface for a two-person team.

The cost: reading a full subtree needs a recursive CTE rather than a native graph traversal, and very deep trees would eventually make those queries something to watch. At this scale, a fair trade for keeping one database.

ChaterBox database schema One PostgreSQL schema: companies self-reference via parent_id, devices and user access attach by foreign key, and translations are polymorphic across any entity. parent_id → self has scopes auth_uid entity_type + entity_id → any table companies company_id PK parent_id FK name is_active devices device_id PK company_id FK status users id PK auth_uid role lat lon user_company_access auth_uid FK company_id FK access_level translations translation_id PK entity_type entity_id lang_id field_name
One PostgreSQL schema: companies self-reference through parent_id, access is scoped via user_company_access, and translations are polymorphic across any entity.

Decision 2 — two-tier device status: live poll versus cached state.

Two endpoints, on purpose. One reaches out to the actual Pi and returns full live telemetry — authoritative, but slow and dependent on the device being reachable. The other returns the server's cached online/offline — instant and cheap. The dashboard uses the cheap one to render lists; the live poll is on-demand, when you actually open a device.

The cost: the cached status can be briefly stale — a device that just dropped still shows online until the next heartbeat. The alternative, live-polling every device on every page load, would fall over the moment a company has a hundred devices. I chose staleness over a system that doesn't scale.

The result — honestly

  • No metrics — the product never reached real users. The client pivoted before launch. I won't dress that up.
  • What shipped: the backend and data model, Google-auth login, the main dashboard with the full interaction surface (selection, filter controls, message composition), role scaffolding, and — the part I'm proudest of — real devices reporting live telemetry to the server.
  • Where it stopped: the device tree and geographic filtering. The honest reason is boring and correct — all the devices sat in one physical location, so there was no geographic spread to filter, and the admin/superadmin panel to expose it all wasn't built yet. No point finishing a feature with no data to justify it.
  • What I take from it: I designed a hardware-backed distributed system and led a team to build most of it. The tree and status decisions are ones I'd defend in any review.
Back to workUpdated May 14, 2025