Databases & Webhooks

Relational Databases (PostgreSQL)

In industrial software, data integrity is paramount. Relational SQL databases enforce strict schemas, relationships, and ACID transactions. PostgreSQL is widely considered the gold standard open-source database.

• PostGIS: A massive extension for Postgres that handles geometric and spatial data perfectly (e.g., mapping exactly where a robot arm is on a grid).
• JSONB: Postgres allows you to store and query native JSON documents alongside strict columns, giving you the flexibility of NoSQL (like MongoDB) with the unmatched safety of SQL.

The Push vs. Pull Problem

Imagine your Edge AI detects a scratched product and inserts a row into the Postgres defects table. How does the React Dashboard know it happened?

1. Polling (Pull): The React app sends a GET request every 3 seconds asking, "Any new defects?" This wastes immense network bandwidth and battery.
2. Webhooks & PubSub (Push): The system notifies the React app instantly only when it actually happens.

What is a Webhook?

A Webhook is essentially a "Reverse API". Instead of a client sending an HTTP GET to a server to request data, the server sends an HTTP POST to the client (or another service) the exact second an event occurs.

Postgres Database Webhooks (e.g., Supabase)

Modern Backend-as-a-Service platforms like Supabase (built on Postgres) introduced powerful native Database Webhooks.

1. The Trigger: You configure Postgres so that ON INSERT into the defects table, a trigger fires.
2. The Webhook: The database automatically fires an HTTP POST payload containing the new defect data to a Serverless Function or Edge Function.
3. The Broadcast: That Serverless Function instantly broadcasts a WebSocket message down to the React clients watching the dashboard.

sequenceDiagram
    participant Edge AI Camera
    participant PostgreSQL DB
    participant Supabase Edge Function
    participant React Dashboard
    
    Edge AI Camera->>PostgreSQL DB: SQL INSERT INTO defects
    Note over PostgreSQL DB: Database Trigger Fires!
    PostgreSQL DB->>Supabase Edge Function: Webhook (HTTP POST payload)
    Supabase Edge Function->>React Dashboard: WebSocket Broadcast ("New Defect Alert")
    Note over React Dashboard: UI updates in < 50ms without polling

This architecture means the Edge Camera only ever has to worry about simple database inserts, and the database acts as the central nervous system driving entire reactivity chains invisibly!