Postgres and ClickHouse stores

Pick a store

Each store other than SQLite is a plugin package, loaded by its name (coding.sh-store-<scheme>). There is nothing to register. The Docker image ghcr.io/jackulau/coding.sh includes all three plugins. Outside Docker, install the one you need next to coding.sh:

The viewer, CLI, MCP tools, datasets and experiments all work on the same store URL. Tables are created and migrated on first start. One serve process writes at a time; the others wait to take over.

Postgres

npm i -g coding.sh coding.sh-store-postgres
export CODING_SH_STORE_URL='postgres://coding:secret@db.internal:5432/coding?sslmode=require'
coding.sh serve

With Compose, deploy/compose/compose.postgres.yml runs serve and Postgres 17 together. Postgres is reachable only from serve, on the stack's own network. Set POSTGRES_PASSWORD in .env before the first start; Postgres keeps the password it started with.

• Blobs are stored in the same database, as zstd-compressed rows. Set CODING_SH_BLOB_URL to an s3:// URL to keep them in object storage instead.
• The writer lock is a session advisory lock. serve needs a direct connection or a session-mode pooler; a transaction-mode pooler drops it.
• Search uses a GIN-indexed tsvector and needs no extensions. --where filters run in the database.
• Statements time out after 30 s.

People with database access can run the CLI against the team database. With --store, local spool files are not pushed into it:

coding.sh sessions --store 'postgres://coding:…@db-host:5432/coding' --project alice

The Postgres password grants full access to the data, so most of the team should use the viewer.

ClickHouse

There are two ways to use ClickHouse, and they can run together:

Run the server on ClickHouse

docker run -d --name coding-sh -p 4318:4318 -v coding-sh:/data --restart unless-stopped \
  -e CODING_SH_STORE_URL='clickhouses://coding:pass@ch.example.com:8443/coding' \
  -e CODING_SH_BLOB_URL='file:///data/blobs' \
  ghcr.io/jackulau/coding.sh

With Compose: deploy/compose/compose.clickhouse.yml runs serve and ClickHouse together, with blobs on the coding-sh-data volume. Set CLICKHOUSE_PASSWORD in .env before the first start.

• Blobs never go to ClickHouse. Keep them on file:// or on S3.
• Each table is a ReplacingMergeTree, and every read uses FINAL, so the newest row wins. coding.sh gc --compact runs OPTIMIZE TABLE … FINAL.
• serve takes a 30-second lease in the locks table, so one replica writes. If the writer dies, another takes over within 30 s.
• Queries time out after 30 s.

Export to ClickHouse through an OpenTelemetry Collector

If your traces already go through the Collector's clickhouse exporter, send coding.sh sessions there too. coding.sh keeps its own store, and an otlp sink copies each session's spans to the collector, which writes them to otel.otel_traces. You query that table; coding.sh does not read it.

deploy/clickhouse/ has a working pair, otel-collector.yaml and docker-compose.yml, with pinned images and ports on 127.0.0.1:

export CLICKHOUSE_PASSWORD=$(od -An -N24 -tx1 /dev/urandom | tr -d ' \n')
docker compose -f deploy/clickhouse/docker-compose.yml up -d --wait

The collector part of the config, to merge into your own:

receivers:
  otlp:
    protocols:
      http:
        endpoint: 0.0.0.0:4318
exporters:
  clickhouse:
    endpoint: tcp://clickhouse:9000?dial_timeout=10s
    database: otel
    username: ${env:CLICKHOUSE_USER}
    password: ${env:CLICKHOUSE_PASSWORD}
    ttl: 720h
    create_schema: true
    # json: true          # attributes as the JSON type; needs enable_json_type=1 on the server
    sending_queue:
      batch:
        flush_timeout: 5s
service:
  pipelines:
    traces:
      receivers: [otlp]
      exporters: [clickhouse]

Then add a sink on each machine that captures sessions. The Compose file publishes the collector on 4319, so it can share a host with serve on 4318:

[[sinks]]
kind = "otlp"
name = "clickhouse"
endpoint = "http://collector:4319"
mode = "metadata"

On a team, set CODING_SH_FORWARD_URL=http://collector:4319 on serve instead, and it forwards every trace it commits.

The span table

What lands in otel_traces, with ServiceName = 'coding.sh':

gen_ai.usage.* is input_tokens, output_tokens, cache_read.input_tokens and cache_write.input_tokens. input_tokens is the whole prompt, cache included; the uncached part is coding_sh.usage.uncached_input_tokens. A call with reasoning tokens also gets reasoning.output_tokens. A session's spans share one TraceId, subagents join their parent's trace, and Duration is in nanoseconds.

• Map columns store every attribute as a string: cast with toFloat64OrZero and toUInt64OrZero. coding_sh.session.flags is a JSON array string.
• Cost is on the invoke_agent span (the session) and on each chat span. Sum one, never both.
• mode = "full" adds prompts, code and tool output as span attributes.
• The invoke_agent span is sent when the session ends. coding.sh serve and coding.sh collector also send it while a session is open: after 10 quiet seconds, and every 60 seconds while busy.
• Each send is a new row with the same TraceId and SpanId and newer totals. A session keyed by a conversation id never ends, so those are its only rows.
• The queries below keep the row with the most events (coding_sh.stats.events) for each span.
• A laptop sink (coding.sh ingest) sends the span at the session's end only.
• ttl: 720h drops rows after 30 days. Set it to your retention.

Sample queries

Cost per agent and model per day:

SELECT
    toDate(Timestamp) AS day,
    SpanAttributes['gen_ai.agent.name'] AS agent,
    SpanAttributes['gen_ai.request.model'] AS model,
    count() AS sessions,
    round(sum(toFloat64OrZero(SpanAttributes['coding_sh.cost_usd'])), 2) AS cost_usd,
    sum(toUInt64OrZero(SpanAttributes['gen_ai.usage.input_tokens'])) AS input_tokens,
    sum(toUInt64OrZero(SpanAttributes['gen_ai.usage.output_tokens'])) AS output_tokens
FROM (
    SELECT Timestamp, SpanId, SpanAttributes
    FROM otel.otel_traces
    WHERE ServiceName = 'coding.sh'
      AND SpanAttributes['gen_ai.operation.name'] = 'invoke_agent'
      AND Timestamp >= now() - INTERVAL 30 DAY
    ORDER BY toUInt64OrZero(SpanAttributes['coding_sh.stats.events']) DESC
    LIMIT 1 BY SpanId
)
GROUP BY day, agent, model
ORDER BY day DESC, cost_usd DESC

Slow and failing tools:

SELECT
    SpanAttributes['gen_ai.tool.name'] AS tool,
    count() AS calls,
    countIf(StatusCode = 'Error') AS errors,
    round(errors / calls, 3) AS error_rate,
    round(quantile(0.95)(Duration) / 1e6) AS p95_ms,
    round(max(Duration) / 1e6) AS max_ms
FROM otel.otel_traces
WHERE ServiceName = 'coding.sh'
  AND SpanAttributes['gen_ai.operation.name'] = 'execute_tool'
  AND Timestamp >= now() - INTERVAL 7 DAY
GROUP BY tool
ORDER BY p95_ms DESC
LIMIT 20

Failed sessions: tests failed, or coding.sh flagged repeated tool failures or a premature finish:

SELECT
    Timestamp AS started,
    SpanAttributes['gen_ai.conversation.id'] AS session,
    SpanAttributes['gen_ai.agent.name'] AS agent,
    toUInt32OrZero(SpanAttributes['coding_sh.stats.tool_errors']) AS tool_errors,
    toUInt32OrZero(SpanAttributes['coding_sh.stats.tests_failed']) AS tests_failed,
    JSONExtract(SpanAttributes['coding_sh.session.flags'], 'Array(String)') AS flags,
    round(Duration / 1e9) AS duration_s
FROM (
    SELECT Timestamp, SpanId, SpanAttributes, Duration
    FROM otel.otel_traces
    WHERE ServiceName = 'coding.sh'
      AND SpanAttributes['gen_ai.operation.name'] = 'invoke_agent'
      AND Timestamp >= now() - INTERVAL 7 DAY
    ORDER BY toUInt64OrZero(SpanAttributes['coding_sh.stats.events']) DESC
    LIMIT 1 BY SpanId
)
WHERE tests_failed > 0 OR hasAny(flags, ['repeated-tool-failure', 'premature-completion'])
ORDER BY started DESC
LIMIT 50

One session's spans, in order (clickhouse-client --param_session=<session id>):

SELECT
    Timestamp,
    SpanName,
    round(Duration / 1e6) AS ms,
    StatusCode,
    SpanAttributes['coding_sh.cost_usd'] AS cost_usd
FROM (
    SELECT Timestamp, SpanId, SpanName, Duration, StatusCode, SpanAttributes
    FROM otel.otel_traces
    WHERE TraceId = (
        SELECT any(TraceId) FROM otel.otel_traces
        WHERE SpanAttributes['gen_ai.conversation.id'] = {session:String}
    )
    ORDER BY toUInt64OrZero(SpanAttributes['coding_sh.stats.events']) DESC
    LIMIT 1 BY SpanId
)
ORDER BY Timestamp

Pull from ClickHouse

If your agents already send traces to a Collector with the clickhouse exporter (your own, ClickStack or HyperDX), coding.sh collector import reads them into your local store:

CLICKHOUSE_USER=otel CLICKHOUSE_PASSWORD=… \
  coding.sh collector import --clickhouse "http://127.0.0.1:8123/?database=otel"

• Each row is mapped as a span posted to coding.sh collector would be: the same events, redaction and pricing.
• It reads only traces with an agent span unless you pass --all, and never stores a span twice.
• It keeps its place per table and row filter. The next run starts an hour before it. --since or --from-start reads from that time again; leave both out of a scheduled import.
• It stores 5,000 rows at a time and retries a dropped connection or a 502, 503 or 504 three times. A run that still fails prints the command to run again.
• Both the default Map(String, String) columns and json: true columns work.

Every flag is on coding.sh collector.

S3 blobs

coding.sh-store-s3 keeps blobs (large prompts, tool output and transcripts) in AWS S3, Cloudflare R2, MinIO or any S3-compatible endpoint:

export AWS_ACCESS_KEY_ID=… AWS_SECRET_ACCESS_KEY=…
export CODING_SH_BLOB_URL='s3://my-bucket/blobs?region=us-east-1'
# R2:    s3://bucket/blobs?region=auto&endpoint=https://<account>.r2.cloudflarestorage.com
# MinIO: s3://bucket/blobs?endpoint=http://127.0.0.1:9000

• Credentials come only from the environment. A URL with credentials in it is refused.
• Parameters: region (default AWS_REGION, then us-east-1), endpoint (default AWS_ENDPOINT_URL_S3, then AWS_ENDPOINT_URL) and style=path|virtual.
• The credentials need s3:GetObject, s3:PutObject, s3:DeleteObject and s3:ListBucket on the bucket and prefix.

Next

• Self-host for a team: one server for the team's agents.
• Deploy coding.sh serve: Compose, Fly.io, Railway, Render and serverless.
• Configuration: every [serve] key and its environment variable.