A Cloudflare Worker that syncs Oura Ring health data to a D1 database and serves it to Grafana for visualization and analytics.

• Complete Data Coverage: Syncs all Oura Ring v2 API endpoints (18+ resources)
• Automated Sync: Every 2 hours via cron, plus webhook-driven near-real-time updates
• Durable Backfill: Cloudflare Workflows for reliable, retryable historical data sync
• Grafana Integration: Pre-built dashboard JSON included in repo
• Enterprise Security: Multi-token auth, timing-safe comparison, 3-tier rate limiting, SQL injection prevention, query timeouts
• SQL Query Caching: KV-backed cache with SHA-256 keys, automatic invalidation after sync
• Analytics Engine: Query and auth metrics via Cloudflare Analytics Engine
• Status Page: /status page showing pipeline health, last sync time, and per-table record counts (requires auth)
• Sync Health Tracking: Last successful sync metadata written to KV after every cron run
• Cost-Efficient: Runs within Cloudflare's free tier limits
• Test Coverage: Automated tests covering auth, SQL safety, webhook verification, and queue ingestion
• Production-Ready: Comprehensive logging, error handling, observability, and monitoring

• Security hardening: SQL injection via quoted identifiers, error detail leakage, CORS localhost defaults, webhook replay window, OpenAPI domain allowlist, concurrent token refresh dedup, cron overlap protection.
• Performance: incremental heart rate stats, DB growth monitoring (/api/db/info), webhook delivery freshness tracking.
• Repo cleanup: removed tutorial docs, stale scripts, and .gitattributes binary JSON misconfiguration.
• See CHANGELOG.md for full history.

• Architecture
• Quick Start
• Data Model
• API Endpoints
• Backfill Workflows
• Deployment
• Configuration
• Cloudflare Access (Recommended)
• Grafana Setup
• Development
• Contributing
• License

• Split src/index.ts into route/services/modules to reduce risk and improve maintainability.
• Introduce a router framework (Hono) for cleaner middleware composition and route ownership.
• Expand automated coverage for sync engine, OAuth refresh lifecycle, queue consumer, and D1 writers.
• Add scheduled D1 backups (export + artifact retention) via GitHub Actions.
• Add optional data export endpoint (/api/export) for CSV pulls outside Grafana.
• Consider Cloudflare Secrets Store and dashboard provisioning automation as follow-up ops hardening.

Diagram source: docs/architecture.excalidraw (also see architecture.svg)

flowchart LR
  Oura["Oura Cloud API<br/>OAuth + Webhooks"]
  Worker["Cloudflare Worker<br/>oura-cf"]
  Webhook["/webhook/oura<br/>public"]
  Admin["/api/admin/oura/webhooks*<br/>Access + admin token"]
  Queue["Cloudflare Queue<br/>oura-webhook-events"]
  D1[("D1: oura_db")]
  KV[("KV: OURA_CACHE")]
  AE[("Analytics Engine")]
  WF["Backfill Workflow"]
  Cron["Cron<br/>0 */2 * * *"]
  Grafana["Grafana<br/>Infinity datasource"]

  Oura -->|webhook delivery| Webhook
  Oura -->|OAuth token exchange| Worker
  Webhook -->|verify + enqueue| Queue
  Queue -->|single-document ingest| Worker
  Worker --> D1
  Worker --> KV
  Worker --> AE
  Cron --> Worker
  WF --> Worker
  Admin --> Worker
  Grafana -->|POST /api/sql<br/>GET /api/stats| Worker

Three ingestion paths write data into D1, and one query path serves it to Grafana:

Oura Ring ──> Oura Cloud API
                  │
     ┌────────────┼────────────┐
     ▼            ▼            ▼
  WEBHOOK       CRON       BACKFILL
  (real-time)  (2h poll)  (manual, durable)
     │            │            │
     ▼            ▼            ▼
  Queue ──┐   syncData()   Workflows
          │       │            │
          └───────┴────────────┘
                  │
                  ▼
              saveToD1()
                  │
                  ▼
           D1 (9 tables)
                  │
                  ▼
         /api/sql (Grafana)

Webhook (primary, real-time): Oura pushes create/update/delete events to /webhook/oura. The handler validates the HMAC signature, deduplicates via KV (24h TTL), and enqueues to a Cloudflare Queue. The queue consumer fetches the full document from the Oura API and writes to D1.

Cron (reconciliation): Every 2 hours, discovers available endpoints from the Oura OpenAPI spec (cached 24h in KV), fetches the last 3 days of data with bounded concurrency (4 workers), and upserts into D1. Protected by a KV lock to prevent overlap.

Backfill (manual, durable): Admin triggers via /backfill. Uses Cloudflare Workflows for durable, step-by-step execution where each resource sync is independently retryable.

Query path: Grafana uses the Infinity datasource to POST /api/sql with raw SQL. The handler authenticates the Bearer token, validates the SQL is read-only, rejects overly large UNION ALL compound queries (>5 terms), checks the KV cache (6h TTL), executes against D1 with a 7s timeout and 50K row cap, then caches the result.

• Cloudflare account (free tier works)
• Oura Ring with active subscription
• Node.js 22+ installed
• Wrangler CLI

Two deployment modes are supported:

• Maintainer mode (fastest for this repo owner): use wrangler.jsonc as-is for production.
• Starter mode (new deployers/forks): use wrangler.starter.jsonc and replace placeholder IDs.

Before deploying, you need Oura API credentials:

1. Go to cloud.ouraring.com and sign in
2. Create a new application
3. Note your Client ID and Client Secret
4. Set the Redirect URI to https://your-host/oauth/callback (use your actual domain)

# Clone repository
git clone https://github.com/xxKeith20xx/oura-cf.git
cd oura-cf

# Install dependencies
npm install

# If deploying your own copy, use starter config
cp wrangler.starter.jsonc wrangler.local.jsonc

# Create D1 database
npx wrangler d1 create oura-db
# Note the database_id from the output — update wrangler.local.jsonc

# Create KV namespace
npx wrangler kv namespace create OURA_CACHE
# Note the id from the output — update wrangler.local.jsonc

# Create Queue
npx wrangler queues create oura-webhook-events

npx wrangler secret put GRAFANA_SECRET      # Token for Grafana datasource auth
npx wrangler secret put ADMIN_SECRET        # Token for manual admin operations
npx wrangler secret put OURA_CLIENT_ID      # From Oura developer portal
npx wrangler secret put OURA_CLIENT_SECRET  # From Oura developer portal
npx wrangler secret put OURA_WEBHOOK_CALLBACK_URL  # e.g. https://your-host/webhook/oura
npx wrangler secret put OURA_WEBHOOK_VERIFICATION_TOKEN  # Random string for webhook verification

# Apply database migrations
npx wrangler d1 migrations apply oura-db --remote

# Set your Cloudflare account ID (get from: npx wrangler whoami)
export CLOUDFLARE_ACCOUNT_ID=your-account-id

# Deploy to Cloudflare
npx wrangler deploy --config wrangler.local.jsonc

# Authorize Oura OAuth (visit URL in browser).
# If Access protects /oauth/start, also include CF-Access service token headers.
curl https://your-host/oauth/start \
  -H "Authorization: Bearer YOUR_ADMIN_SECRET"

# Complete OAuth flow, then backfill historical data
curl https://your-host/backfill?days=730 \
  -H "Authorization: Bearer YOUR_ADMIN_SECRET"

# Ensure webhook subscriptions are configured
curl -X POST https://your-host/api/admin/oura/webhooks/sync \
  -H "Authorization: Bearer YOUR_ADMIN_SECRET"

# Poll backfill status
curl "https://your-host/backfill/status?id=INSTANCE_ID" \
  -H "Authorization: Bearer YOUR_ADMIN_SECRET"

daily_summaries - Aggregated daily metrics

• Readiness Score (activity balance, HRV, temperature, etc.)
• Sleep Score (efficiency, latency, deep/REM/light sleep)
• Activity Score (steps, calories, training volume)
• Health Metrics (stress, resilience, SpO2, VO2 max, cardiovascular age)
• Sleep Timing (optimal bedtime, recommendation, status)

sleep_episodes - Detailed sleep sessions

• Sleep stages (deep, REM, light, awake durations)
• Heart rate (average, lowest)
• HRV, breathing rate, temperature deviation
• Sleep type (long sleep, nap, rest)

heart_rate_samples - 5-minute resolution HR data

• Timestamp, BPM, source (ring, workout, etc.)
• High-resolution data for detailed analysis

activity_logs - Workouts and sessions

• Exercise type, duration, intensity
• Calories burned, distance, average heart rate
• Meditation and breathing sessions

enhanced_tags - User-created tags and annotations

• Tag type, custom name, freeform comments
• Start/end dates with optional duration support

rest_mode_periods - Rest mode tracking

• Start/end dates and times
• Episode data (tags during rest mode)

table_stats - Pre-computed statistics (cache)

• Row counts, date ranges, last update time
• Reduces database load for dashboard queries

Dynamic Endpoint Discovery: The Worker auto-discovers available Oura API endpoints by fetching the OpenAPI spec URL from the Oura docs page, with fallback to a known version. This makes the system resilient to Oura API version bumps.

Oura Docs Page → Discover Spec URL → Fetch OpenAPI Spec → KV Cache (24hr)
                                            ↓
                                     18 API Endpoints
                                            ↓
                                   Worker (parallel fetch)
                                            ↓
                                    D1 Database (upsert)

Upsert Strategy: Multiple Oura endpoints write to the same daily_summaries row (keyed by day), allowing data from daily_readiness, daily_sleep, daily_activity, and sleep_time to merge into a single denormalized record.

Resource Aliases: When Oura renames API endpoints across versions (e.g., vo2_max → vO2_max), the RESOURCE_ALIASES map normalizes names before D1 storage.

Rate limit: 3000 requests per minute per IP (applies to all authenticated endpoints)

# Start a backfill (returns immediately with workflow instance ID)
curl "https://your-host/backfill?days=730" \
  -H "Authorization: Bearer YOUR_ADMIN_SECRET"
# → 202 { "instanceId": "backfill-730d-offset0-...", "statusUrl": "/backfill/status?id=..." }

# Poll status until complete
curl "https://your-host/backfill/status?id=backfill-730d-offset0-..." \
  -H "Authorization: Bearer YOUR_ADMIN_SECRET"
# → { "status": "running" }  ... then eventually:
# → { "status": "complete", "output": { "successful": 18, "failed": 0, "totalRequests": 42 } }

# Backfill specific resources only
curl "https://your-host/backfill?days=365&resources=heartrate,sleep" \
  -H "Authorization: Bearer YOUR_ADMIN_SECRET"

# Backfill with offset (skip recent days)
curl "https://your-host/backfill?days=365&offset_days=30" \
  -H "Authorization: Bearer YOUR_ADMIN_SECRET"

curl -X POST https://your-host/api/sql \
  -H "Authorization: Bearer YOUR_GRAFANA_SECRET" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT day, readiness_score, sleep_score FROM daily_summaries WHERE day >= date(\"now\", \"-30 days\") ORDER BY day",
    "params": []
  }'

Large backfills use Cloudflare Workflows for durable, retryable execution. This solves the fundamental limitation of Workers: CPU time and subrequest limits that made large inline backfills unreliable.

1. /backfill dispatches a BackfillWorkflow instance and returns 202 Accepted immediately
2. The Workflow runs as a series of durable steps:
   • discover-resources — Loads available resources from the Oura OpenAPI spec (3 retries)
   • sync:{resource} — One step per resource (e.g., sync:heartrate, sync:sleep), each with 3 retries, 5-minute timeout
   • update-stats — Refreshes the table_stats table
   • flush-cache — Invalidates all cached SQL query results in KV
3. /backfill/status?id=<instanceId> polls the workflow for progress

The every-2-hours cron sync (syncData) remains inline — it only syncs 3 days of data, well within Workers limits. The Workflow is only used for /backfill.

npm run deploy:cf

# Run database migrations (only when schema changes)
npm run db:migrate

# Deploy Worker
npx wrangler deploy

# Verify deployment
curl https://your-host/health

# Use starter config with your own IDs
npx wrangler deploy --config wrangler.local.jsonc

# Add custom domain via Cloudflare Dashboard
# Or update wrangler.jsonc:
{
  "routes": [
    {
      "pattern": "oura.yourdomain.com",
      "custom_domain": true
    }
  ]
}

# Deploy with custom domain
npx wrangler deploy

Key settings in wrangler.jsonc:

{
	"compatibility_date": "2026-02-24",
	"triggers": {
		"crons": ["0 */2 * * *"], // Sync every 2 hours
	},
	"queues": {
		"producers": [{ "binding": "OURA_WEBHOOK_QUEUE", "queue": "oura-webhook-events" }],
		"consumers": [
			{
				"queue": "oura-webhook-events",
				"max_batch_size": 10,
				"max_batch_timeout": 5,
				"max_retries": 8,
			},
		],
	},
	"workflows": [
		{
			"name": "backfill-workflow",
			"binding": "BACKFILL_WORKFLOW",
			"class_name": "BackfillWorkflow",
		},
	],
	"d1_databases": [
		{
			"binding": "oura_db",
			"database_id": "YOUR_D1_DATABASE_ID",
		},
	],
	"kv_namespaces": [
		{
			"binding": "OURA_CACHE",
			"id": "YOUR_KV_NAMESPACE_ID",
		},
	],
	"analytics_engine_datasets": [
		{
			"binding": "OURA_ANALYTICS",
			"dataset": "oura_metrics",
		},
	],
}

For stronger perimeter auth and centralized audit logs, protect admin/API endpoints with Cloudflare Access.

1. Create Service Token in Zero Trust Dashboard (Access → Service Auth → Service Tokens)
2. Create Access Application protecting admin/API paths
3. Configure Policies:
   • service token policy for machine clients (Grafana/automation)
   • optional human IdP policy (e.g. GitHub) for browser access
4. Add Headers to Grafana datasource configuration:
   • CF-Access-Client-Id: <token-id>
   • CF-Access-Client-Secret: <token-secret>

• Observability: Centralized access logs and analytics
• Defense in Depth: Multiple authentication layers
• Token Rotation: Supports graceful credential updates

Path guidance:

• Keep protected: /api/*, /backfill, /oauth/start
• Keep public: /webhook/oura, /oauth/callback, /health, /favicon.ico

• Grafana Cloud account (free tier: 10k series, 50GB logs)
• Infinity datasource plugin installed

1. Install Infinity Plugin

   Grafana → Plugins → Search "Infinity" → Install
   

2. Create Datasource

   • Name: Oura API
   • URL: https://your-host/api/sql
   • Method: POST
   • Custom HTTP Headers (see below)

3. Import Dashboard

   # Use provided dashboard JSON (contains https://YOUR_HOST placeholders)
   # These URLs are overridden by your datasource config — just import as-is
   cat grafana-dashboard-structured.json
   # Import via Grafana UI -> Dashboards -> Import

Custom HTTP Headers Configuration:

Without Cloudflare Access:

• Authorization: Bearer YOUR_GRAFANA_SECRET

With Cloudflare Access (recommended):

• Authorization: Bearer YOUR_GRAFANA_SECRET
• CF-Access-Client-Id: <service-token-client-id>
• CF-Access-Client-Secret: <service-token-client-secret>

• Comprehensive visualizations across readiness, sleep, activity, stress, tags, and data quality sections
• Time-series panels: Readiness, sleep, activity trends
• Stat panels: Current scores, latest metrics, sleep timing
• Bar charts: Sleep stages, workout distribution, tag frequency
• Tables: Recent tags, rest mode periods, data coverage
• Correlation analysis: Sleep quality vs readiness, training load vs recovery

Readiness Trend (7-day rolling average):

WITH d AS (
  SELECT day, readiness_score AS score
  FROM daily_summaries
  WHERE readiness_score IS NOT NULL AND day >= date('now', '-2 years')
)
SELECT
  day||'T00:00:00Z' AS time,
  score,
  AVG(score) OVER (
    ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS score_7d
FROM d
ORDER BY day

• Multi-token auth: Separate GRAFANA_SECRET and ADMIN_SECRET for role separation
  • GRAFANA_SECRET — read-only access for Grafana datasource
  • ADMIN_SECRET — elevated access: enables debug output on /health, required for /backfill, /oauth/start, and /api/admin/oura/webhooks*
• Timing-safe comparison: Uses crypto.subtle.timingSafeEqual (SHA-256 hash both sides first) to prevent timing attacks
• OAuth state validation: 24-hour expiry with automatic cleanup via cron
• Debug header protection: /health request headers (including auth tokens) are never returned to non-admin callers

• Read-only enforcement: Blocks INSERT, UPDATE, DELETE, DROP, ALTER, PRAGMA, VACUUM, ATTACH, REPLACE INTO
• Sensitive table filtering: Queries against oura_oauth_tokens and oura_oauth_states are blocked (including quoted identifier variants like "oura_oauth_tokens")
• Comment stripping: Removes -- and /* */ comments before validation
• Compound SELECT guardrail: Rejects queries with more than 5 UNION ALL terms before they hit D1 limits
• Multi-statement blocking: Rejects queries containing semicolons
• Leading wildcard blocking: Rejects LIKE '%...' patterns that force full table scans
• LIMIT capping: Injects/caps LIMIT to prevent unbounded result sets
• Parameter validation: Rejects objects/arrays in SQL params (only primitives allowed)

All responses include: X-Content-Type-Options: nosniff, X-Frame-Options: DENY, X-XSS-Protection: 1; mode=block, Referrer-Policy: strict-origin-when-cross-origin, Content-Security-Policy: default-src 'none'

# Start local dev server (binds to D1, KV, secrets)
npx wrangler dev

# Access local worker
curl http://localhost:8787/health
curl http://localhost:8787/status   # Pipeline status page (HTML)

# Run tests
npm test

# Run tests once
npm run test:run

# Type checking (clean — no generated types file needed)
npx tsc --noEmit

The project uses Vitest with @cloudflare/vitest-pool-workers for testing against real Miniflare bindings:

npm test          # Watch mode
npm run test:run  # Single run

# 62 tests (60 passing, 2 skipped)
# Coverage: auth, SQL injection (including quoted identifier bypass), param validation, LIMIT capping,
#           CORS origins, daily_summaries, 404 handling, root endpoint

oura-cf/
├── src/
│   └── index.ts              # Main Worker + BackfillWorkflow (3,000+ lines)
├── test/
│   └── index.spec.ts         # 57 tests
├── migrations/
│   ├── 0001_init.sql         # Core tables
│   ├── 0002_oauth_tokens.sql # OAuth token storage
│   ├── 0003_placeholder.sql  # Numbering placeholder
│   ├── 0004_table_stats.sql  # Pre-computed statistics cache
│   ├── 0005_add_indexes.sql  # Performance indexes
│   ├── 0006_new_endpoints.sql # v1.28 tables (enhanced_tags, rest_mode_periods)
│   ├── 0007_optimize_indexes.sql # Drop redundant indexes
│   ├── 0008_covering_indexes.sql # Covering indexes for Grafana queries
│   ├── 0009_drop_unused_tables.sql # Drop unused tables
│   └── 0010_drop_user_tags.sql # Drop superseded user_tags table
├── scripts/
│   ├── smoke-test.sh        # Post-deploy endpoint smoke test
│   └── sync-version.sh      # Syncs version from package.json → wrangler.jsonc + vitest config
├── wrangler.jsonc            # Cloudflare configuration
├── wrangler.starter.jsonc    # Starter config for independent deployments
├── vitest.config.mts         # Test configuration
├── package.json              # Dependencies
├── tsconfig.json             # TypeScript config
├── grafana-dashboard-structured.json  # Grafana dashboard definition
├── CHANGELOG.md              # Version history
├── docs/
│   ├── architecture.excalidraw  # Editable architecture diagram
│   ├── architecture.svg         # Rendered architecture diagram
│   ├── RUNBOOK.md            # Ops runbook (OAuth/webhook/queue incidents)
│   ├── ENVIRONMENT.md        # Secrets, bindings, and Access path policy
│   ├── RELEASE_CHECKLIST.md  # Release process and post-deploy checks
│   ├── DECISIONS.md          # ADR-lite architecture decisions
│   └── KNOWN_ISSUES.md       # Active caveats and tradeoffs
├── AGENTS.md                 # AI contributor quick context
├── CONTRIBUTING.md           # Contribution guidelines
└── README.md                 # This file

The system automatically detects new Oura API endpoints via OpenAPI spec. To add a new table:

1. Create D1 migration in migrations/
2. Add mapping logic in saveToD1() function
3. Deploy migration and Worker

Contributions are welcome! See CONTRIBUTING.md for guidelines.

Quick summary:

• Use conventional commit messages (feat:, fix:, docs:)
• Update CHANGELOG.md for notable changes
• Test locally with npm run test:run before submitting
• Use npm version patch|minor|major to bump versions — the version hook auto-syncs wrangler.jsonc and vitest.config.mts
• Open an issue for major changes before starting work

• Repo context for AI contributors: AGENTS.md
• Operational docs for AI sessions: docs/RUNBOOK.md, docs/ENVIRONMENT.md, docs/DECISIONS.md, docs/KNOWN_ISSUES.md
• Cloudflare Workers docs (LLM format): https://developers.cloudflare.com/workers/llms-full.txt
• Cloudflare D1 docs (LLM format): https://developers.cloudflare.com/d1/llms-full.txt
• Cloudflare MCP server docs: https://developers.cloudflare.com/agents/model-context-protocol/mcp-servers-for-cloudflare/

This project is licensed under the MIT License - see the LICENSE file for details.

TL;DR: You can use, modify, and distribute this code freely. Attribution appreciated but not required.

Project Repository: github.com/xxKeith20xx/oura-cf