Skip to content
Source
Sign inSign up

Set up analytics

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
  • 1209600 seconds is 14 days. A queue cannot keep a message longer.
  • gitflare-analytics-events-dlq gets the batches that fail every retry.
  • The bucket keeps those batches under dead/, and the nightly backups under backups/.
  • --location weur asks R2 to keep the bucket in Western Europe.

2. Create the server

  1. 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.

  2. When you create the server, add your SSH key.

  3. Give the server a firewall that lets in only SSH (TCP port 22).

  4. Write down the IPv4 address of the server. The R2 token of the nightly backup accepts only this address.

  5. 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.

  1. 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-client

    With DEBIAN_FRONTEND=noninteractive, the installer does not ask for a password. You set the password in the next steps.

  2. 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-admin
  3. Give default this 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>
    EOF
    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-server

    access_management lets default create the users ingest and reader in section 4. The installer can leave its own default-password.xml with an empty password. Two password files for one user stop ClickHouse from loading its users, so the first line deletes it.

  4. Keep the logs of ClickHouse itself for 7 days. ClickHouse writes its own logs into tables of the system database, 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-server

    On a server that already has these tables, ClickHouse renames each old table to <name>_0 when it restarts, and makes a new one with the limit. The _0 tables are read-only and do not grow. Delete them with DROP TABLE system.<name>_0 when you no longer need their logs.

  5. 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.

  1. On your computer, copy the schema to the server:

    scp apps/web/clickhouse/schema.sql root@<server IPv4>:/root/schema.sql
  2. On the server, create the user ingest. At the end of this step, this user gets access to insert into analytics.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.

  3. Create the user reader. This user gets access to read analytics.events and analytics.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 = 1 makes 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.

  4. Apply the schema. It creates:

    • The database analytics, with the table events, 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 fills rank_actions from each insert into events.
    • The access of reader to rank_actions.
    clickhouse-client --password "$(cat /root/.clickhouse-admin)" --queries-file /root/schema.sql
  5. Give ingest and reader their access to analytics.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:

  1. Give the token to wrangler, in this terminal only:

    export CLOUDFLARE_API_TOKEN=<API token>
  2. Create the tunnel. The command prints the id of the tunnel:

    pnpm -C apps/web exec wrangler tunnel create gitflare-analytics
  3. Create 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 8123
  4. Put the service id in the entry CLICKHOUSE of vpc_services, in apps/web/wrangler.jsonc. Then commit this change:

    "vpc_services": [{ "binding": "CLICKHOUSE", "service_id": "<service id>" }]
  5. Get the tunnel token, and copy it. cloudflared needs it in step 6. The token is the result field of the answer. <account id> is the account_id in apps/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

  1. On the server, install cloudflared from 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 cloudflared
  2. Save 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)
  3. 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
    EOF
    systemctl daemon-reload
    systemctl enable --now cloudflared
  4. Make 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:

  1. 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_PASSWORD

    Each command asks for the value. For CLICKHOUSE_USER, enter ingest. For CLICKHOUSE_PASSWORD, enter the password of ingest from step 4.

  2. 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_PASSWORD

    For CLICKHOUSE_READ_USER, enter reader. For CLICKHOUSE_READ_PASSWORD, enter the password of reader from 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 (analyticsOn in apps/web/src/server/analytics.ts).
  • The tests use a queue of their own.

9. Make sure that the events arrive

  1. Open your site, and open a few pages.

  2. On the server, count the events:

    clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "SELECT count() FROM analytics.events"
  3. Wait one minute, then count again. The number grows.

  4. As a site admin, open /admin/analytics. It shows totals and top lists of the events, not the notice.

  5. 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 /_e on your site. The Worker also makes events of its own, for example repo.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 CLICKHOUSE and the tunnel gitflare-analytics to the server.
  • cloudflared on 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 is true when the action counts.
  • The consumer sets rank to false when 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_mv copies each event with rank = true into analytics.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_actions as reader, and writes the scores of the repositories to D1. On the 1st of the month, it also gives the awards.
  • Explore, REST, MCP and src read only D1. While ClickHouse is down, they show the last scores.
EventSignalProperties for the rankings
repo.visitVisitrank
page.leaveReading timevisibleMs of 30000 or more
git.cloneClonerank
repo.forkedFork, for the parent repositoryrank, parentRepoId
issue.opened, pr.openedOpenedrank, itemNumber
comment.created, pr.review_submittedReplyrank, itemNumber, itemAuthorId
community.post_created, community.answer_createdCommunityrank
pr.mergedMerged, for the authorrank, 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.