IT Services
Portfolio

FieldDB

Overview

FieldDB is a platform for collecting and analyzing hyperlocal environmental sensor data. It ingests telemetry from weather stations and air-quality nodes, stores time series, and presents measurements to device owners and visitors to the public map.

Unlike satellite data or citywide averages, FieldDB works with readings from specific ground-level devices. The platform combines device registration, a data-ingestion API, analytical charts, and controlled station publishing.

Product Features

The system provides:

  • Device registration and individual API-key issuance
  • Batch uploads of weather and air-quality measurements
  • Protection against duplicate measurement records
  • Online, stale, and offline device statuses
  • Charts and tables covering periods from one hour to 30 days
  • Temperature, humidity, pressure, PM2.5, PM10, wind, rainfall, and dew-point metrics
  • An overview dashboard with device counts, last synchronization time, and alerts
  • A public map for stations whose owners have opted in to publishing
  • An interface in English, Spanish, German, and Russian

The public map never returns a device's exact coordinates. Before publication, the location is converted to a lower-precision geohash, and the external API returns the center of that area.

Architecture

FieldDB is built as a monorepo and includes:

  • A Next.js web application
  • A Hono API with OpenAPI documentation
  • PostgreSQL with the TimescaleDB extension for time-series data
  • Prisma for the data schema and migrations
  • Better Auth for web sessions
  • Keycloak as the OIDC provider
  • BFF routes in the web application for API access
  • Google Maps for the public station map
  • A shared package for structured logging and application constants

Measurements are stored in a TimescaleDB hypertable. The API can return both raw points and data aggregated into time intervals. A device key is shown to the user only once, while only its SHA-256 hash is stored in the database.

Infrastructure & SRE

The project has:

  • GitLab CI with linting, type checks, builds, tests, and dependency audits
  • Container builds with Kaniko
  • Separate images for the web application, API, migrator, and Keycloak theme
  • Health checks for the API and web application
  • SonarQube code-quality checks
  • Renovate for dependency updates
  • Structured JSON logs in production
  • A local TimescaleDB and Keycloak environment powered by Testcontainers
  • GitOps deployment with a separate database-migration task
  • Build-cache lifetime limits and cleanup of old registry images

Migrations do not run with the API. A separate image is designed to run as a Kubernetes Job before the application is updated. This arrangement reduces the risk of several API instances attempting the same migration simultaneously.

Engineering Challenges

FieldDB brings together time-series telemetry, authentication, and device-location privacy:

  • Idempotent ingestion of batches containing up to 500 measurements
  • Time-series storage and aggregation in TimescaleDB
  • Secure issuance and storage of device keys
  • Separation of private owner data from the public station representation
  • Reduced coordinate precision before locations are published on the map
  • Correct OAuth callback handling with localized routes
  • Supplying Google Maps keys at build time and configuring allowed referrers
  • Retrieving web sessions internally without calling the public TLS endpoint from the same pod
  • Running migrations before a new API version is deployed

The resulting architecture provides detailed measurements to device owners while exposing only explicitly published stations and geographically anonymized coordinates to the public.