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 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:

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 to try this example. New accounts start with $300 in free credits for a 30-day trial; see the current trial offer. 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:

1git clone https://github.com/ClickHouse/examples.git
2cd examples/applications/report-history
3npm 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 if needed:

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

2. Authenticate from the example directory #

Create a Cloud API key with the Admin role for your organization using the API-key guide. 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:

1clickhousectl cloud auth login --interactive
2clickhousectl cloud auth status
3clickhousectl 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:

1REPORT_DEMO_IP="$(curl -4fsS https://api.ipify.org)" npm run cloud:create
2npm 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:

1const report = prepareReport(input);
2await store.writeResults(report);
3await 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

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 and read-consistency guidance

Query across runs, not only by report ID #

The result columns support aggregate queries over many completed reports:

1SELECT
2    toDate(r.completed_at) AS day,
3    d.region,
4    d.category,
5    uniqExact(d.run_id) AS reports,
6    sum(d.revenue_cents) AS revenue_cents
7FROM report_results AS d FINAL
8INNER JOIN report_runs AS r FINAL USING (tenant_id, run_id)
9WHERE d.tenant_id = {tenant:String}
10GROUP BY day, d.region, d.category
11ORDER 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:

1await store.status(tenant, runId, 3, 'completed', completedAt);
2await store.status(tenant, runId, 2, 'running', startedAt);
3const 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 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 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 and idling guidance 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.