Health Auto Export Server

A server application for ingesting and retrieving health data.

Architecture

This application uses:

• Fastify for the API server (handling data ingestion)
• PostgREST for automatic REST API generation from the PostgreSQL database
• PostgreSQL for data storage

API Endpoints

• POST /api/data - Ingest health data (metrics and workouts)
• PostgREST API - All data retrieval is handled by PostgREST (available on port 3000 by default)

Running the application

Using Docker Compose

# Start all services
npm run docker:up

# Start only PostgreSQL
npm run docker:postgres

# View logs
npm run docker:logs

# Stop all services
npm run docker:down

Development mode

# Install dependencies
npm install

# Start in development mode
npm run dev

PostgREST

PostgREST automatically exposes a RESTful API based on your PostgreSQL schema. For example:

• GET /base_metrics - Retrieve all base metrics
• GET /blood_pressure - Retrieve all blood pressure measurements
• GET /heart_rate - Retrieve all heart rate measurements
• GET /sleep_analysis - Retrieve all sleep analysis data

For more information on how to use PostgREST, refer to the official documentation.

OpenAPI Specification

PostgREST automatically generates an OpenAPI specification, which can be accessed at:

http://localhost:3000/

Features

• API endpoints for ingesting health metrics and workout data
• PostgreSQL database for data storage
• PostgREST for direct database access
• Docker configuration for easy deployment
• Dynamic table creation - Only core tables are created at initialization, with metric-specific tables created on demand when data is first received

Dynamic Table Creation

The server uses a dynamic approach to database schema management:

1. Only core tables (sources, workouts, sleep, blood pressure) are created during server initialization and defined in schema.sql
2. Standard metric-specific tables are created on-demand using tableTemplate.sql when data for a new metric type is first received
3. When new tables are created, PostgREST and Hasura are automatically signaled to reload their schema definitions

This approach has several advantages:

• The database only contains tables for metrics that are actually used
• All standard metric tables follow a consistent structure
• New metric types can be supported without manual database migrations

Metric Table Structure

All dynamically created metric tables follow this consistent structure:

CREATE TABLE {metric_name} (
  id SERIAL PRIMARY KEY,
  value DOUBLE PRECISION NOT NULL,
  units VARCHAR(50) NOT NULL,  -- No defaults, value comes from client
  date TIMESTAMPTZ NOT NULL,
  source VARCHAR(255),
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Special-case Tables

Some metrics have non-standard structures and are included in the schema.sql file:

• blood_pressure - Has systolic and diastolic values instead of a single value
• sleep_sessions and sleep_phases - Have specialized fields for sleep analysis

Admin Endpoints

• POST /admin/reload-services - Manually trigger service reloads for PostgREST and Hasura

Tech Stack

• Node.js with TypeScript
• Express.js for API routing
• PostgreSQL for data storage
• PostgREST for database API access
• Docker and Docker Compose for containerization

Setup and Installation

Prerequisites

• Docker and Docker Compose
• Node.js (for local development)

Environment Variables

Copy the example environment file:

cp .env.example .env

Modify the values in the .env file if needed.

Database Schema

Base Metrics

Stores generic health metrics like step count, heart rate, etc.

Special Metrics Tables

• blood_pressure - Blood pressure measurements
• heart_rate - Heart rate measurements
• sleep_analysis - Sleep data

Workouts

• workouts - Workout records
• workout_routes - GPS routes for workouts

License

MIT