Site administration
Set up analytics
You run the Source deploy, and you want to keep the usage events of production on your own server. Store them in ClickHouse on one Linux server in the EU, which the Worker reaches through a Cloudflare Tunnel.
10 min read
The server needs no open port except SSH. What the events hold is in What Source tracks, and how to turn it off. How it works shows the path of an event.
You need:
- The Source repository with its packages (
pnpm install), and wrangler signed in to the Cloudflare account of the deploy (pnpm -C apps/web exec wrangler login). - An SSH key.
- An account at a cloud provider in the EU, for example Hetzner Cloud.
Run the commands for your computer from the root of the Source repository.
1. Create the queues and the bucket
The deploy fails while these are missing, so create them before you merge:
pnpm -C apps/web exec wrangler queues create gitflare-analytics-events --message-retention-period-secs 1209600
pnpm -C apps/web exec wrangler queues create gitflare-analytics-events-dlq --message-retention-period-secs 1209600
pnpm -C apps/web exec wrangler r2 bucket create gitflare-analytics --location weur
1209600seconds is 14 days. A queue cannot keep a message longer.gitflare-analytics-events-dlqgets the batches that fail every retry.- The bucket keeps those batches under
dead/, and the nightly backups underbackups/. --location weurasks R2 to keep the bucket in Western Europe.
2. Create the server
Create one Linux server in the EU. For example, use a Hetzner Cloud server with Ubuntu 24.04 and 8 GB of memory or more.
When you create the server, add your SSH key.
Give the server a firewall that lets in only SSH (TCP port 22).
Write down the IPv4 address of the server. The R2 token of the nightly backup accepts only this address.
Connect to the server as root:
ssh root@<server IPv4>
Important
The privacy page tells people that the server is in Germany. If you put the server in another country, change that page too.
3. Install ClickHouse
Run these commands on the server.
Add the official ClickHouse apt repository, then install ClickHouse:
apt-get install -y apt-transport-https ca-certificates curl gnupg curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=$(dpkg --print-architecture)] https://packages.clickhouse.com/deb stable main" > /etc/apt/sources.list.d/clickhouse.list apt-get update DEBIAN_FRONTEND=noninteractive apt-get install -y clickhouse-server clickhouse-clientWith
DEBIAN_FRONTEND=noninteractive, the installer does not ask for a password. You set the password in the next steps.Make the password of the admin user
default, in a file that only root can read:openssl rand -hex 24 > /root/.clickhouse-admin chmod 600 /root/.clickhouse-adminGive
defaultthis password, and let it sign in only from the server itself:rm -f /etc/clickhouse-server/users.d/default-password.xml hash=$(tr -d '\n' < /root/.clickhouse-admin | sha256sum | cut -d ' ' -f 1) cat > /etc/clickhouse-server/users.d/default-user.xml <<EOF <clickhouse> <users> <default> <password remove="1"/> <password_sha256_hex>$hash</password_sha256_hex> <networks replace="replace"> <ip>::1</ip> <ip>127.0.0.1</ip> </networks> <access_management>1</access_management> </default> </users> </clickhouse> chown root:clickhouse /etc/clickhouse-server/users.d/default-user.xml chmod 640 /etc/clickhouse-server/users.d/default-user.xml systemctl enable clickhouse-server systemctl restart clickhouse-serveraccess_managementletsdefaultcreate the usersingestandreaderin section 4. The installer can leave its owndefault-password.xmlwith an empty password. Two password files for one user stop ClickHouse from loading its users, so the first line deletes it.Keep the logs of ClickHouse itself for 7 days. ClickHouse writes its own logs into tables of the
systemdatabase, and keeps them forever if you do not set a limit. On the first server they grew by about 400 MB a day:f=/etc/clickhouse-server/config.d/system-log-ttl.xml { echo '<clickhouse>' for t in text_log metric_log trace_log part_log asynchronous_metric_log query_log processors_profile_log query_views_log background_schedule_pool_log error_log query_metric_log blob_storage_log backup_log; do echo " <$t><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></$t>" done echo '</clickhouse>' } > $f chown root:clickhouse $f chmod 644 $f systemctl restart clickhouse-serverOn a server that already has these tables, ClickHouse renames each old table to
<name>_0when it restarts, and makes a new one with the limit. The_0tables are read-only and do not grow. Delete them withDROP TABLE system.<name>_0when you no longer need their logs.Make sure that the password works. This command prints the version of ClickHouse:
clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "SELECT version()"
Warning
ClickHouse listens on 127.0.0.1 and ::1 only. Do not change this: cloudflared reaches ClickHouse on 127.0.0.1.
4. Create the users ingest and reader, and the tables
Create the users first. The schema gives reader access to the table of the rankings, so reader must exist before you apply the schema.
On your computer, copy the schema to the server:
scp apps/web/clickhouse/schema.sql root@<server IPv4>:/root/schema.sqlOn the server, create the user
ingest. At the end of this step, this user gets access to insert intoanalytics.events, and to do nothing else:ingest_password=$(openssl rand -hex 24) clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "CREATE USER ingest IDENTIFIED WITH sha256_password BY '$ingest_password' HOST LOCAL" echo "$ingest_password"Copy the password that the last command prints. The Worker gets it in step 7, and needs no other copy.
Create the user
reader. This user gets access to readanalytics.eventsandanalytics.rank_actions, and to do nothing else:reader_password=$(openssl rand -hex 24) clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "CREATE USER reader IDENTIFIED WITH sha256_password BY '$reader_password' HOST LOCAL SETTINGS readonly = 1, max_execution_time = 10, output_format_json_quote_64bit_integers = 0" echo "$reader_password"Copy this password too. The Worker gets it in step 7.
readonly = 1makes the user read-only, and also stops it from changing settings. For this reason, the command sets the time limit (10 seconds for each query) and the number format on the user.Apply the schema. It creates:
- The database
analytics, with the tableevents, which keeps each event for 2 years. - The table
rank_actions, with one row for each person, repository, day and signal of the rankings. - The view
rank_actions_mv, which fillsrank_actionsfrom each insert intoevents. - The access of
readertorank_actions.
clickhouse-client --password "$(cat /root/.clickhouse-admin)" --queries-file /root/schema.sql- The database
Give
ingestandreadertheir access toanalytics.events:clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "GRANT INSERT ON analytics.events TO ingest" clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "GRANT SELECT ON analytics.events TO reader"
The admin page /admin/analytics reads the events as reader. A separate user keeps ingest write-only, and reader read-only.
Note
HOST LOCAL lets ingest and reader sign in only from the server. The requests of the Worker arrive from cloudflared on the server, so they can sign in.
5. Create the tunnel and the VPC service
The wrangler login cannot create tunnels. For this step, make an account API token with these permissions:
- Cloudflare Tunnel > Edit, to create the tunnel.
- Connectivity Directory > Admin, to create the VPC service.
If you use a user API token, its user also needs the Connectivity Directory Admin role.
Then, on your computer:
Give the token to wrangler, in this terminal only:
export CLOUDFLARE_API_TOKEN=<API token>Create the tunnel. The command prints the id of the tunnel:
pnpm -C apps/web exec wrangler tunnel create gitflare-analyticsCreate the VPC service. It sends the requests of the Worker through the tunnel to port 8123 of the server, where ClickHouse answers HTTP. The command prints the id of the service:
pnpm -C apps/web exec wrangler vpc service create clickhouse --type http --tunnel-id <tunnel id> --ipv4 127.0.0.1 --http-port 8123Put the service id in the entry
CLICKHOUSEofvpc_services, inapps/web/wrangler.jsonc. Then commit this change:"vpc_services": [{ "binding": "CLICKHOUSE", "service_id": "<service id>" }]Get the tunnel token, and copy it.
cloudflaredneeds it in step 6. The token is theresultfield of the answer.<account id>is theaccount_idinapps/web/wrangler.jsonc:curl -s "https://api.cloudflare.com/client/v4/accounts/<account id>/cfd_tunnel/<tunnel id>/token" -H "Authorization: Bearer $CLOUDFLARE_API_TOKEN"
Important
Then close this terminal. In a new terminal, wrangler uses your wrangler login again, which the next steps need.
6. Run the tunnel on the server
On the server, install
cloudflaredfrom the Cloudflare apt repository:mkdir -p --mode=0755 /usr/share/keyrings curl -fsSL https://pkg.cloudflare.com/cloudflare-main.gpg | tee /usr/share/keyrings/cloudflare-main.gpg > /dev/null echo 'deb [signed-by=/usr/share/keyrings/cloudflare-main.gpg] https://pkg.cloudflare.com/cloudflared any main' > /etc/apt/sources.list.d/cloudflared.list apt-get update apt-get install -y cloudflaredSave the tunnel token of step 5 in a file that only root can read. Paste the token, then press Enter. The terminal does not show the token, and the shell history does not keep it:
install -d -m 700 /etc/cloudflared (umask 077 && read -rs TOKEN && printf '%s' "$TOKEN" > /etc/cloudflared/tunnel-token)Run the tunnel as a service. The service reads the token from the file, and starts after each reboot:
cat > /etc/systemd/system/cloudflared.service <<'EOF' [Unit] Description=cloudflared tunnel gitflare-analytics After=network-online.target Wants=network-online.target [Service] Type=notify TimeoutStartSec=15 ExecStart=/usr/bin/cloudflared --no-autoupdate tunnel run --token-file /etc/cloudflared/tunnel-token Restart=on-failure RestartSec=5s [Install] WantedBy=multi-user.target systemctl daemon-reload systemctl enable --now cloudflaredMake sure that the tunnel is connected. This command prints one line for each connection, usually four:
journalctl -u cloudflared --no-pager | grep 'Registered tunnel connection'The log also shows warnings about ICMP (ping). You can ignore them, because the Worker sends only HTTP requests.
7. Give the Worker the users and the passwords
On your computer:
Set the two secrets for the user
ingest:pnpm -C apps/web exec wrangler secret put CLICKHOUSE_USER pnpm -C apps/web exec wrangler secret put CLICKHOUSE_PASSWORDEach command asks for the value. For
CLICKHOUSE_USER, enteringest. ForCLICKHOUSE_PASSWORD, enter the password ofingestfrom step 4.Set the two secrets for the user
reader:pnpm -C apps/web exec wrangler secret put CLICKHOUSE_READ_USER pnpm -C apps/web exec wrangler secret put CLICKHOUSE_READ_PASSWORDFor
CLICKHOUSE_READ_USER, enterreader. ForCLICKHOUSE_READ_PASSWORD, enter the password ofreaderfrom step 4.
Note
Until CLICKHOUSE_USER and CLICKHOUSE_PASSWORD have a value, the consumer keeps each batch in the queue, and tries it again later. The two read secrets are optional. Without them, ClickHouse still stores the events, and /admin/analytics shows a notice that analytics are not available.
8. Deploy
Merge your change of apps/web/wrangler.jsonc to main. The ci/deploy job deploys it. See "Deploying" in README.md.
Note
If ci/deploy fails at the binding CLICKHOUSE, the deploy token needs the permission Connectivity Directory > Bind. Add it to CLOUDFLARE_API_TOKEN. If that is a user API token, also give its user the Connectivity Directory Bind role.
Local dev and the tests send nothing to your server:
- Analytics is off under
vite dev(analyticsOninapps/web/src/server/analytics.ts). - The tests use a queue of their own.
9. Make sure that the events arrive
Open your site, and open a few pages.
On the server, count the events:
clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "SELECT count() FROM analytics.events"Wait one minute, then count again. The number grows.
As a site admin, open
/admin/analytics. It shows totals and top lists of the events, not the notice.After a person who counts visits a public repository, count the rows of the rankings. Each signal with activity has a row:
clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "SELECT signal, count() FROM analytics.rank_actions GROUP BY signal"
If the count of events does not grow, your browser can be the cause:
- It sends no events while Do Not Track or Global Privacy Control is on.
- It sends no events while your Share usage analytics switch is off.
Done
The events of production now go to your own server. Next, set up the nightly backup.
How it works
How the events get to ClickHouse
- The browser sends its events to
/_eon your site. The Worker also makes events of its own, for examplerepo.created. - The Worker puts the events on the Cloudflare Queue
gitflare-analytics-events. - The consumer of the queue runs in the same Worker. It drops the events of people who turned analytics off, and inserts the other events into the ClickHouse table
analytics.events. - The insert goes through the Workers VPC binding
CLICKHOUSEand the tunnelgitflare-analyticsto the server. cloudflaredon the server connects out to Cloudflare and keeps the tunnel open. As a result, the server needs no open port for ClickHouse.
How the rankings read the events
The repository rankings and the monthly awards come from the same events:
- The server stamps each event that can earn rank points with the property
rank. It istruewhen the action counts. - The consumer sets
ranktofalsewhen the person that gets the credit does not count. That is an account younger than 7 days, a banned account, an account with a burst flag, or a person who turned analytics off. - The view
analytics.rank_actions_mvcopies each event withrank=trueintoanalytics.rank_actions. Signed-in page leaves with 30 seconds or more of visible time go there too, as reading time. - Every 15 minutes, the cron of the Worker reads
rank_actionsasreader, and writes the scores of the repositories to D1. On the 1st of the month, it also gives the awards. - Explore, REST, MCP and
srcread only D1. While ClickHouse is down, they show the last scores.
| Event | Signal | Properties for the rankings |
|---|---|---|
repo.visit | Visit | rank |
page.leave | Reading time | visibleMs of 30000 or more |
git.clone | Clone | rank |
repo.forked | Fork, for the parent repository | rank, parentRepoId |
issue.opened, pr.opened | Opened | rank, itemNumber |
comment.created, pr.review_submitted | Reply | rank, itemNumber, itemAuthorId |
community.post_created, community.answer_created | Community | rank |
pr.merged | Merged, for the author | rank, itemNumber, mergerKind, authorId, rankPerson |
mergerKind is person, agent or bot. rankPerson is the author of the pull request, who gets the credit of the merge.
If ClickHouse is down
The events wait while ClickHouse is down. The consumer does this:
- On a network error, or an answer other than 400 and 413 (for example a wrong password), it tries the batch again later. The wait doubles each time, up to 3 hours.
- After 100 retries (about 11 days), the batch goes to the dead-letter queue. Its consumer writes the batch to R2, under
dead/. - On a 400 or 413 answer, ClickHouse refused the rows, and a retry cannot help. The batch goes to
dead/at once. - Both queues keep a message for 14 days. As a result, the retries end before a message expires.
- Each failed try logs an error. If the deploy has Sentry, Sentry shows these errors.
To get the batches of dead/ into ClickHouse, see Load the batches that never got in.