How I Built Serverless APIs for Real-Time Energy Analytics with Cloudflare Workers, TypeScript, and PostgreSQL

How I Built Serverless APIs for Real-Time Energy Analytics with Cloudflare Workers, TypeScript, and PostgreSQL

[August 11, 2026]—[7 min read]—[NOTE]

Real-time energy data processing using Cloudflare Workers, TypeScript, PostgreSQL, Kysely, Zod, and ETL. A practical case study on serverless architecture, analytics, caching, and report generation for energy consumption, demand, and savings systems.

Abstract

Over the past several months, I worked on building serverless APIs focused on processing and analyzing energy data. The goal was to transform large volumes of operational information into useful metrics for visualizing energy consumption, demand, costs, and savings in real time.

The solution was built using Cloudflare Workers, TypeScript, PostgreSQL, Kysely, Zod, ETL processes, and dynamic PDF report generation. This article describes the architectural decisions, challenges, and lessons learned while building an analytics platform focused on energy data.

The problem: processing energy data in real time

Energy systems generate information continuously.

Each meter can report periodic readings that must later be transformed into useful information to answer questions such as:

  • How much energy did a site consume during the last month?
  • What was the highest demand recorded?
  • How does current behavior compare with previous periods?
  • How much was saved thanks to an energy storage system?
  • What would the operating cost be under different scenarios?

Answering these questions requires more than simply storing readings.

It requires an analytics layer capable of:

  • Processing time series.
  • Calculating cumulative differences.
  • Aggregating information by period.
  • Generating comparative indicators.
  • Returning results ready for visualization.

System context

The platform was designed for energy analytics and included several information domains.

Energy consumption

Calculating energy used over different periods.

Examples:

  • Daily
  • Weekly
  • Monthly
  • 12-month historical view

Energy demand

Processing recorded maximums to identify consumption peaks and billable demand.

Energy costs

Calculating costs using tariff structures with multiple components:

  • Capacity
  • Distribution
  • Base energy
  • Intermediate energy
  • Peak energy
  • Semi-peak energy

Energy savings

Comparing scenarios:

  • Traditional operation
  • Operation with a battery energy storage system (BESS)

Reports and visualization

The data was not intended only for storage.

The primary goal was to feed:

  • Dashboards
  • Charts
  • Indicators
  • PDF reports

Why Cloudflare Workers?

The decision to use Cloudflare Workers was based on several factors.

Low latency

The APIs run close to users thanks to the edge-computing model.

Operational simplicity

There was no need to manage servers, containers, or traditional infrastructure.

Automatic scaling

The platform could respond to variable workloads without manually adjusting capacity.

Fast deployments

The deployment workflow was significantly simpler than traditional server-based solutions.

Trade-offs

Cloudflare Workers also introduces important constraints:

  • A runtime that differs from Node.js.
  • CPU and memory limitations.
  • Partial compatibility with some libraries.
  • Special considerations for database connections.

Designing within these constraints was an important part of the project.

Overall architecture

The solution was organized into multiple layers with clearly defined responsibilities.

Plain Text•••
Client
   │
   ▼
Cloudflare Workers API
   │
   ▼
Validation Layer (Zod)
   │
   ▼
Application Services
   │
   ▼
Kysely
   │
   ▼
PostgreSQL

API Layer

Workers acted as the entry point for all requests.

Each endpoint had clear responsibilities:

  • Validate input.
  • Delegate business logic.
  • Format responses.

Validation Layer with Zod

All external input was validated with Zod.

Example:

TypeScript•••
const schema = z.object({
  siteId: z.string().uuid(),
  startDate: z.string(),
  endDate: z.string(),
});

This provided:

  • Consistent validation.
  • Type safety.
  • Predictable error messages.

Application Services

Business logic was encapsulated in specialized services.

Examples:

Plain Text•••
operation-energy
cost-energy
savings-energy

This made it possible to decouple complex calculations from HTTP transport concerns.

PostgreSQL

PostgreSQL served as the primary engine for analytical queries.

Most calculations were executed directly in SQL to take advantage of:

  • Aggregations
  • Window functions
  • CTEs
  • Query-engine optimization

Kysely

Kysely was used as a typed query builder.

The primary motivation was to preserve:

  • Type safety
  • Reusability
  • Readability

without losing access to advanced SQL when needed.

ETL and normalization

Data arrived from different sources and formats.

ETL processes were responsible for:

  • Standardizing structures.
  • Cleaning inconsistencies.
  • Preparing information for later analysis.

Caching

Not every query needed to recalculate its result constantly.

Caching mechanisms were therefore introduced to reduce load and latency.

PDF reports

Document generation was implemented with React PDF.

This allowed us to reuse familiar React patterns to build dynamic reports.

Designing analytics endpoints

One important lesson was that endpoints should not return only raw data.

They should return information optimized for consumption.

Consumption

Plain Text•••
GET /energy/consumption

Simplified response:

JSON•••
{
  "labels": ["Jan", "Feb", "Mar"],
  "values": [1200, 1450, 1320]
}

Demand

Plain Text•••
GET /energy/demand

Returned historical and comparative maximum-demand values.

Costs

Plain Text•••
GET /energy/costs

Calculated energy costs while considering different tariff components.

Savings

Plain Text•••
GET /energy/savings

Compared scenarios with and without energy storage.

PostgreSQL and analytical queries

Most of the system's value lived in the SQL layer.

Time series

Energy readings are essentially time series.

SQL makes it possible to work with them efficiently.

LAG

One of the most frequently used patterns was:

SQL•••
LAG(value)
OVER (
  PARTITION BY meter_id
  ORDER BY timestamp
)

This makes it possible to compare a reading with the immediately previous one.

Delta calculation

A meter usually records cumulative values.

To obtain actual consumption:

SQL•••
current_reading - previous_reading

Using LAG:

SQL•••
value - LAG(value)

PARTITION BY

This is essential when processing multiple meters at the same time.

SQL•••
PARTITION BY meter_id

CTEs

Common Table Expressions helped organize complex queries.

SQL•••
WITH monthly_data AS (
  ...
)
SELECT *
FROM monthly_data;

Aggregations by period

Monthly grouping:

SQL•••
DATE_TRUNC('month', timestamp)

Daily grouping:

SQL•••
DATE_TRUNC('day', timestamp)

Migrating to Kysely

One interesting part of the project was migrating complex queries to Kysely.

Why migrate?

SQL queries kept growing over time.

We needed:

  • Better maintainability.
  • Lower risk of errors.
  • Consistent typing.

Preserving parity with SQL

The goal was never to hide SQL.

The goal was to preserve the same logic:

TypeScript•••
db.selectFrom("readings")
  .select(...)

without losing expressiveness.

Aliases and typing

One of the most common challenges involved alias handling.

Example:

TypeScript•••
eb.alias("base_energy")

or:

TypeScript•••
sql<number>`SUM(value)`.as("total")

Keeping aliases consistent was critical to preserving correct typing.

Window functions in Kysely

Some complex queries required embedded SQL.

Example:

TypeScript•••
sql<number>`
LAG(value)
OVER (
  PARTITION BY meter_id
  ORDER BY timestamp
)
`

This approach preserved the power of SQL without giving up type safety.

Challenges in serverless environments

Cold starts

They exist, even if they are small.

That made it important to minimize unnecessary work during initialization.

Database connections

Traditional database connections are not always ideal in serverless environments.

We had to design strategies compatible with the ephemeral nature of Workers.

Library compatibility

Some dependencies work perfectly in Node.js but not in Workers.

One example was adapting libraries used for PDF generation and WASM.

Error handling

Errors needed to be:

  • Consistent.
  • Observable.
  • Easy to trace.

Validation and reliability

Zod

All input was validated before it reached business logic.

Sanitization

Early validation prevented multiple classes of errors.

Consistent errors

The APIs returned homogeneous structures.

JSON•••
{
  "success": false,
  "message": "Invalid input"
}

Debugging

Analytics systems present particular challenges.

When a calculation appears incorrect, the problem may be in:

  • Source data.
  • ETL.
  • SQL.
  • Aggregations.
  • Visualization.

That made it important to build tools for validating results step by step.

Performance

Strategic caching

Not every query needed to recalculate results.

Aggregated results were natural candidates for caching.

Reducing latency

The combination of:

  • Edge execution
  • Optimized queries
  • Caching

made it possible to build fast responses even for complex calculations.

Visualization-oriented endpoints

One important lesson:

Do not force the frontend to transform large amounts of data.

The API should return structures that are ready to consume.

Lessons learned

When to use serverless

It is an excellent option when:

  • Workloads vary.
  • Operational simplicity is important.
  • Execution times are relatively short.

When to keep SQL explicit

Not every query should be abstracted.

Complex analytical queries often benefit from explicit, visible SQL.

How to design reliable analytical APIs

Three principles proved especially useful:

  1. Validate early.
  1. Keep business logic isolated.
  1. Execute calculations close to the data.

Conclusion

Building serverless APIs for energy analytics was an interesting experience because it combined multiple areas of engineering:

  • Backend
  • Serverless
  • Advanced SQL
  • Time-series processing
  • ETL
  • Validation
  • Reporting

Beyond the technologies used, the main challenge was transforming operational data into information that was useful, reliable, and ready for decision-making.

Signals for recruiters

  • Designed production serverless architectures using Cloudflare Workers.
  • Experience using PostgreSQL for analytical workloads and time-series processing.
  • Advanced SQL experience with CTEs, window functions, and time-based aggregations.
  • Built typed APIs using TypeScript, Kysely, and Zod.
  • Built data pipelines, ETL processes, and analytics-oriented systems.
←All notes