Timescaledb

TimescaleDB 2.14.2 on PostgreSQL 16 MCP tools: 4 status: examples included

The question this exists to answer

"What happened to the freezer in bay 3 last week?"

Asked of a metrics dashboard, that takes a human five minutes of panning and zooming. Asked of a model with SQL access to a TimescaleDB hypertable, it takes one query:

SELECT time_bucket('1 hour', reading_at) AS hour,
       avg(temp_c)                       AS avg_c,
       min(temp_c)                       AS min_c,
       max(temp_c)                       AS max_c,
       count(*) FILTER (WHERE temp_c > -15.0) AS excursion_samples
FROM readings
WHERE unit_id = 'FRZ-B3'
  AND reading_at >= now() - INTERVAL '7 days'
GROUP BY hour
ORDER BY hour;

The interesting part is not the query. It is that a language model can write that query correctly, against your actual schema, because time_bucket() is ordinary SQL and the hypertable looks like an ordinary table. No query DSL to learn, no vendor-specific JSON body, no PromQL-to-English translation layer.

This repository is the wiring: how to attach an MCP client to a free TimescaleDB instance on freebase.cloud, a worked cold-chain schema, and the query patterns that actually pay off.

---

1. What you are connecting to

Engine	TimescaleDB 2.14.2, running as an extension on PostgreSQL 16
Dialect	PostgreSQL SQL, plus the TimescaleDB function set
Reachable over	freebase.cloud's HTTP query API and MCP (Streamable HTTP)
Endpoint	https://freebase.cloud/api/mcp/YOUR_TOKEN
Dialect reference	psql syntax, PostgreSQL port 5432 conventions
Cost	free tier — no card, no cluster provisioning step

TimescaleDB is not a separate database that resembles Postgres. It is Postgres with an extension loaded, which is why joins between a hypertable and a plain relational table work, why WITH RECURSIVE works, why JSONB works, and why every Postgres aggregate is available inside a time_bucket grouping.

The extension is pre-installed on freebase.cloud instances; there is no CREATE EXTENSION step to run.

---

2. Four concepts, in the order you will need them

Hypertable. A table that TimescaleDB transparently partitions into time-ordered chunks. You create it in two statements:

CREATE TABLE readings (
  reading_at TIMESTAMPTZ NOT NULL,
  unit_id    TEXT        NOT NULL,
  temp_c     DOUBLE PRECISION,
  humidity   REAL,
  door_open  BOOLEAN
);
SELECT create_hypertable('readings', 'reading_at', chunk_time_interval => INTERVAL '1 day');

From that point on it behaves like a table: INSERT, SELECT, JOIN, indexes, constraints. The chunking is a planner concern, not an application one.

time_bucket(). Floor a timestamp to a fixed-width window. time_bucket('15 minutes', t) is date_trunc with an arbitrary width, and it is the workhorse of every downsampling query. Its offset and origin arguments matter when your buckets need to align to something other than the epoch — e.g. shift boundaries at 06:00.

Continuous aggregate. A materialised view that refreshes incrementally as data lands, rather than recomputing from scratch:

CREATE MATERIALIZED VIEW readings_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', reading_at) AS bucket,
       unit_id,
       avg(temp_c)  AS avg_c,
       max(temp_c)  AS max_c,
       count(*)     AS samples
FROM readings
GROUP BY bucket, unit_id;

Query it like any view. Add a refresh policy and it maintains itself.

Policies. add_compression_policy() and add_retention_policy() are background jobs that compress old chunks and drop older ones. On a free-tier database these are how you stop a sensor fleet from filling your quota with data nobody will read again.

---

3. Setup

1. Create a free account at freebase.cloud and start a session with the TimescaleDB engine.
2. Settings → MCP → New Token, select the connection, copy the URL. The token is a path segment — no Authorization header is required by any client below, and auth is always set to None.
3. Register it. The examples assume a connection named metrics, so the tools appear as metrics_query, metrics_store, metrics_list_tables, metrics_annotate_table.

# Claude Code — writes .mcp.json with --scope project
claude mcp add --transport http metrics https://freebase.cloud/api/mcp/YOUR_TOKEN

// Cursor — .cursor/mcp.json
{ "mcpServers": { "metrics": { "url": "https://freebase.cloud/api/mcp/YOUR_TOKEN" } } }

// VS Code / Copilot Chat — .vscode/mcp.json ("servers", not "mcpServers")
{
  "inputs": [{ "type": "promptString", "id": "tsdb", "description": "freebase.cloud token", "password": true }],
  "servers": { "metrics": { "type": "http", "url": "https://freebase.cloud/api/mcp/${input:tsdb}" } }
}

# Gemini CLI. In settings.json the streamable-HTTP key is httpUrl; plain url means SSE.
gemini mcp add --transport http metrics https://freebase.cloud/api/mcp/YOUR_TOKEN

Claude Desktop, Claude web and Cowork are configured through the UI only — Settings → Connectors → Add custom connector → paste → Add (step-by-step); the desktop config file has no support for remote HTTP servers. On ChatGPT, use Settings → Apps → Advanced settings → developer mode → Apps → Create, auth None, Scan Tools. Write access there is still rolling out to Business, Enterprise and Edu workspaces, so metrics_store may not be callable on every plan even when metrics_query is.

---

4. Tools

The connection exposes four tools — the same four every engine gets, prefixed with the connection name:

metrics_query           SELECT / analytical SQL, returns rows
metrics_store           INSERT — the ingest path for readings
metrics_list_tables     lists tables, including hypertables and continuous aggregates
metrics_annotate_table  schema description the model reads before writing SQL

Annotate the hypertable. Seriously.

Time-series schemas are the ones models get wrong most often, because units are invisible in column names and sample intervals are invisible everywhere:

metrics_annotate_table(
  table: "readings",
  description: "Hypertable partitioned on reading_at (1-day chunks). One row per sensor
                sample, nominally every 60s per unit. temp_c is degrees Celsius; a frozen
                unit's target band is -22 to -16. humidity is % RH. Use time_bucket() for
                any aggregation over more than an hour — do not GROUP BY reading_at."
)

That last sentence alone removes the most common failure mode: a model grouping by raw timestamp and returning ten thousand one-row buckets.

---

5. Query patterns worth memorising

Downsample. Never return raw samples to a model when a bucketed summary answers the question. Fewer tokens, better answers.

SELECT time_bucket('30 minutes', reading_at) AS bucket, unit_id, avg(temp_c)
FROM readings WHERE reading_at > now() - INTERVAL '12 hours'
GROUP BY bucket, unit_id ORDER BY bucket;

First and last within a bucket. first() and last() are TimescaleDB aggregates that take a value column and an ordering column — much cheaper than a window function for "state at end of hour".

SELECT time_bucket('1 hour', reading_at) AS bucket,
       last(temp_c, reading_at)  AS temp_at_close,
       first(temp_c, reading_at) AS temp_at_open
FROM readings WHERE unit_id = 'FRZ-B3' GROUP BY bucket ORDER BY bucket DESC LIMIT 24;

Gap filling. time_bucket_gapfill() emits empty buckets so a chart — or a model counting outages — sees the hole instead of silently skipping it. Pair it with locf() or interpolate().

Excursion detection. The cold-chain question is rarely "what was the average"; it is "how long was it out of band, and did that breach the threshold":

SELECT unit_id,
       count(*)                                              AS samples,
       count(*) FILTER (WHERE temp_c > -15.0)                AS above_band,
       round(100.0 * count(*) FILTER (WHERE temp_c > -15.0) / count(*), 2) AS pct_out
FROM readings
WHERE reading_at >= now() - INTERVAL '7 days'
GROUP BY unit_id
HAVING count(*) FILTER (WHERE temp_c > -15.0) > 0
ORDER BY pct_out DESC;

Read the continuous aggregate, not the hypertable, for anything spanning more than a day. It is the same numbers, precomputed. Both live in the same TimescaleDB connection, so metrics_list_tables shows them side by side.

---

6. Examples

A cold-chain monitoring fleet: refrigeration units across depots, each reporting temperature, humidity and door state.

File	Language	Role
examples/hypertable_setup.sql	SQL	Units and depots, the readings hypertable, two continuous aggregates, compression and retention policies, an excursion view
examples/ingest_readings.py	Python 3	Generates and writes a realistic fleet backfill through metrics_store, in batches, with a deliberate excursion
examples/coldchain_report.py	Python 3	Seven reports: bucketed trends, gap-filled availability, excursion ledger, chunk and compression status

Both Python files use the standard library only, and talk to the MCP endpoint over HTTP.

export FREEBASE_MCP_URL="https://freebase.cloud/api/mcp/YOUR_TOKEN"
export FREEBASE_CONN="metrics"

python3 examples/ingest_readings.py --schema examples/hypertable_setup.sql
python3 examples/ingest_readings.py --days 3 --interval 300
python3 examples/coldchain_report.py

---

7. Operating notes and limits

• Free tier scope. Development, prototyping and small production workloads on the free TimescaleDB tier. No uptime figure, backup policy or storage ceiling is claimed here — the dashboard is the source of truth for current quotas.
• Set a retention policy on day one. A 60-second sample interval across 20 units is about 29 million rows a year. add_retention_policy('readings', INTERVAL '90 days') costs one statement and saves a lot of grief.
• Compression changes what you can do to a chunk. Compressed chunks are effectively read-optimised; updating rows inside one is restricted. Compress data you have stopped correcting.
• Continuous aggregates lag by design. A refresh policy has a start_offset / end_offset window; the most recent minutes may not be in the aggregate yet. For "right now" questions, query the hypertable.
• No raw TCP listener. The dialect is Postgres and the tooling conventions are Postgres, but access here is over HTTPS — the query API and MCP — rather than a 5432 socket. If you need a native libpq connection for Grafana or an ORM, freebase.cloud's plain PostgreSQL engine exposes one; TimescaleDB does not.
• One statement per tool call. No multi-call transactions.
• Claude's free tier allows one custom connector.

---

8. Reference

• TimescaleDB instance page
• Connecting Claude to TimescaleDB
• TimescaleDB documentation
• time_bucket reference
• PostgreSQL 16 manual
• MCP specification 2026-07-28

---

freebase.cloud is an independent service and is not affiliated with Timescale, Inc., the PostgreSQL Global Development Group, Anthropic, OpenAI, Google, Microsoft or Cursor.