---
title: "Building report results and run history with ClickHouse Cloud"
date: "2026-09-05"
author: "Al Brown"
category: "Engineering"
excerpt: "Build report results and run history with ClickHouse, provision a service from the CLI, and learn when to use Managed Postgres within ClickHouse Cloud."
---

# Building report results and run history with ClickHouse Cloud

This walkthrough builds a small command-line sales-reporting application. It
reads sales CSV files, writes human-readable Markdown reports, and stores the
result rows and completed-run history in ClickHouse. You can find an earlier
run or compare revenue across reports without reopening each input file.

The design question is: **can an application keep analytical results and the
metadata needed to find completed reports in one ClickHouse service?** For
this append-oriented workflow, the example shows how to do that, including
retries and incomplete writes. A run-history table does not, by itself, require
a separate transactional database.

ClickHouse Cloud is the platform for both ClickHouse and ClickHouse Managed
Postgres services. This example provisions only the ClickHouse analytical
service; the transactional alternative discussed below is also part of
ClickHouse Cloud.

The [runnable TypeScript example](https://github.com/ClickHouse/examples/tree/main/applications/report-history)
implements that lifecycle, including interruption tests. It uses the official
JavaScript client and provisions infrastructure with `clickhousectl`; no
console-clicking walkthrough is required after account authentication.

## Store report results and run history

Each report has two parts: the result rows you want to analyze and a record
describing the completed run. They go into two tables:

| Table | Purpose | Logical identity |
| --- | --- | --- |
| `report_results` | Typed analytical rows, such as region, category, units and revenue | Tenant, immutable run ID, row number |
| `report_runs` | Completion time, report type, file URIs, expected row count and summary | Tenant, immutable run ID |

Files remain in the filesystem for the demo, or in object storage in a deployed
application. ClickHouse stores their references, not uploaded file bytes. A
shared application needs shared object storage; a local `file://` URI is not a
public download link.

The report ID hashes the canonical input. An exact replay has the same ID; a
changed report is a new immutable run. That rule avoids an ambiguous “update the
old report but perhaps keep its old result rows” operation.

## Provision from the terminal

[Sign up for ClickHouse Cloud](https://console.clickhouse.cloud/signUp) to try
this example. New accounts start with **$300 in free credits for a 30-day trial**;
see the [current trial offer](https://clickhouse.com/cloud). If you already have
an account, use it for the steps below.

### 1. Clone the repository and enter the example

You need Git, Node.js 22 or newer, and npm. Start by cloning the repository and
entering the example directory:


```sh
git clone https://github.com/ClickHouse/examples.git
cd examples/applications/report-history
npm ci
```

Stay in `examples/applications/report-history` for authentication and all
subsequent commands. Return to this directory if you open a new terminal.

Install the [ClickHouse CLI](https://clickhouse.com/docs/products/cloud/features/cli)
if needed:


```sh
curl -fsSL https://clickhouse.com/cli | sh
export PATH="$HOME/.local/bin:$PATH"
clickhousectl --version
```

### 2. Authenticate from the example directory

Create a Cloud API key with the **Admin** role for your organization using the
[API-key guide](https://clickhouse.com/docs/products/cloud/features/admin-features/api/openapi).
Keep both the Key ID and Key Secret. This example needs permission to create a
service and provision a per-service Query API key during schema setup.

In a private terminal, still in `examples/applications/report-history`, run:


```sh
clickhousectl cloud auth login --interactive
clickhousectl cloud auth status
clickhousectl cloud org list
```

Enter the Key ID and Key Secret at the prompts. Check that status shows API-key
authentication and the organization list contains your intended organization
before continuing. Browser-only OAuth login is read-only and cannot perform
this setup. Do not paste secrets into an agent conversation or commit credential
files. If an agent is running the remaining steps, complete the interactive login
yourself in a terminal in this same directory first.

### 3. Create the service and schema

From the same directory, fetch your public IPv4 address and provision the
service. The helper allows connections from that address only:


```sh
REPORT_DEMO_IP="$(curl -4fsS https://api.ipify.org)" npm run cloud:create
npm run cloud:setup
```

The helper creates one IP-restricted AWS `eu-west-1` service with one fixed
8 GiB replica and idle scaling enabled. This configuration is for a demo, not
a high-availability recommendation. Usage draws down available trial credits;
paid accounts incur normal Cloud charges. Check your credit balance and stop
the service when you finish.

Setup applies the SQL files and creates a dedicated application user with only
`SELECT` and `INSERT` on the three example tables. It writes credentials to a
gitignored private `.env`; the application never uses the default administrator.
If creation times out, the helper records that an attempt occurred so a retry
does not silently create another service.

## Show a report only after all its rows are stored

A report should not appear in the run history while some of its rows are still
being uploaded. The application first inserts all the rows into `report_results`.
Only after those inserts succeed does it add the report to `report_runs`:


```ts
const report = prepareReport(input);
await store.writeResults(report);
await store.writeCompletion(report);
```

These are two separate database operations, not one transaction. If the process
stops after storing the result rows but before adding the history record, the
report stays out of the history. Retrying the same input finishes saving it.

Queries also check that the number of stored result rows matches the row count
recorded in `report_runs`. A report with missing rows is excluded from the
history and from queries across reports.

Stable batch contents, ordering, and deduplication tokens handle ordinary
retries. ClickHouse's insert-deduplication history is finite, so the design does
not call it permanent exactly-once delivery. Both tables use
`ReplacingMergeTree`, and queries use `FINAL` to deduplicate visible logical
rows even when repeated physical rows remain. The integration test deliberately
disables insert deduplication for one replay and checks that totals do not
double. [Retry deduplication reference](https://clickhouse.com/docs/concepts/features/operations/insert/deduplicating-inserts-on-retries)

`FINAL` does not mean “wait for the background merge.” It applies the table's
replacement rules during the query. It also does not make another replica
current by itself: this Cloud example enables `select_sequential_consistency`
for acknowledged-write reads through the HTTPS endpoint. That has coordination
cost and does not create a transaction across the tables.
[ReplacingMergeTree reference](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/replacingmergetree)
and [read-consistency guidance](https://clickhouse.com/docs/resources/support-center/knowledge-base/data-management/read-consistency)

## Query across runs, not only by report ID

The result columns support aggregate queries over many completed reports:


```sql
SELECT
    toDate(r.completed_at) AS day,
    d.region,
    d.category,
    uniqExact(d.run_id) AS reports,
    sum(d.revenue_cents) AS revenue_cents
FROM report_results AS d FINAL
INNER JOIN report_runs AS r FINAL USING (tenant_id, run_id)
WHERE d.tenant_id = {tenant:String}
GROUP BY day, d.region, d.category
ORDER BY day, d.region, d.category
```

The query above totals revenue by day, region and category. In your application,
use the example's `analytics()` method. It also excludes reports with missing
rows, so an interrupted upload cannot contribute a partial total.

Revenue is stored in whole cents to avoid rounding errors. Totals are returned
as strings so JavaScript can read large values without losing precision.

Run `npm run demo`, `npm run history`, and `npm run analytics`. The demo generates
CSV inputs, parses them, writes separate Markdown reports, and stores 2,000
analytical rows for each report. No LLM key is needed: the deterministic generator
isolates the storage lifecycle. It deliberately republishes each report. The
logical result remains three reports, 6,000 rows and 7,197,000 cents of revenue.

## Track progress without using the status table as a job queue

If users also want progress observations, the example includes a separate
`run_status` table with `ReplacingMergeTree(version)`. A single workflow owner
assigns monotonically increasing versions. `FINAL` returns version 3 even if
version 2 arrives afterward:


```ts
await store.status(tenant, runId, 3, 'completed', completedAt);
await store.status(tenant, runId, 2, 'running', startedAt);
const current = await store.currentStatus(tenant, runId);
```

Repeated versions must have identical values. This does not implement
compare-and-swap, concurrent job claiming, or atomic state transitions. Use a
transactional service such as [ClickHouse Managed Postgres](https://clickhouse.com/cloud/postgres)
within ClickHouse Cloud if those requirements are central. A workflow ID assigned before execution can
identify progress observations; the immutable report ID is only available once
the completed payload is known.

## What the example demonstrates

The example was tested on ClickHouse 26.2.1.641 in ClickHouse Cloud. Tests cover
CLI setup, interrupted uploads, retries, empty reports, duplicate rows,
cross-report totals, and status updates arriving out of order. These checks
verify that the workflow produces the expected results; they do not measure
performance at scale. The [README](https://github.com/ClickHouse/examples/tree/main/applications/report-history#when-this-design-fits)
documents the sample code's implementation limits.

The application still owns authorization, file storage, retries, batching,
orphan cleanup and workload sizing. Tenant filtering in example queries is not
database-enforced isolation: the shared server-side user can access every
example tenant. Do not expose it directly to a browser or an untrusted client.

Finish with `npm run cloud:stop`. Stopping preserves the service and data;
storage, backups, or other applicable charges can remain. Consult current
[pricing](https://clickhouse.com/pricing) and [idling guidance](https://clickhouse.com/docs/products/cloud/features/autoscaling/idling)
instead of treating a small test as a cost or resume-latency guarantee.

The practical distinction is straightforward: completed-report history and
analytical results can be one analytical application. Transactional invariants
still deserve a transactional design. The runnable repository makes both the
working path and its boundary explicit.