Reviewing your history of public GitHub repositories using ClickHouse#

There’s a story going around at the moment that people have found code from their private GitHub repositories in the AI training data known as The Stack, using this search tool: https://huggingface.co/spaces/bigcode/in-the-stack

I’m very doubtful that private data has been included in that training set. I think it’s far more likely that the repositories in question were public at some point in the time, and were gathered up by the https://www.softwareheritage.org/ project when they archived code from GitHub.

But how can we tell if a private repository was public at some point in the past?

GitHub have a security audit log for logged in users, but sadly it appears to only cover the past six months.

For a longer history, we can look things up in the GitHub Archive project, which has been recording public events from the GitHub API since 2011.

TLDR: I built a tool for this here: https://observablehq.com/@simonw/github-public-repo-history

The ClickHouse team provide a public tool for querying that data using SQL as a demo of their software. We can use that to try and find out if a repository was public at some point in the past.

Access the tool here - no login required: https://play.clickhouse.com/play

Now execute the following SQL, replacing my username with yours in both places where it occurs:

1
with public_events as (
2
  select
3
    created_at as timestamp,
4
    'Private repo made public' as action,
5
    repo_name
6
  from github_events
7
  where lower(actor_login) = 'simonw'
8
  and event_type in ('PublicEvent')
9
),
10
most_recent_public_push as (
11
  select
12
    max(created_at) as timestamp,
13
    'Most recent public push' as action,
14
    repo_name
15
  from github_events
16
  where event_type = 'PushEvent'
17
  and lower(actor_login) = 'simonw'
18
  group by repo_name
19
),
20
combined as (
21
  select * from public_events
22
  union all select * from most_recent_public_push
23
)
24
select * from combined order by timestamp

The result is a combined timeline showing two things:

• PublicEvent events - which GitHub describes as “When a private repository is made public. Without a doubt: the best GitHub event.”
• The most recent PushEvent for each repository. Repositories which started life public won’t show up in the PublicEvent list, so this aims to capture them.

Here’s an extract from the data I get back when I run the query for myself:

1
viewof username = Inputs.text({
2
  placeholder: "Your GitHub username",
3
  submit: true
4
})

1
results = username.trim() &&
2
  (
3
    await fetch("https://play.clickhouse.com/?user=play", {
4
      method: "POST",
5
      body: `with public_events as (
6
  select
7
    created_at as timestamp,
8
    'Private repo made public' as action,
9
    repo_name
10
  from github_events
11
  where lower(actor_login) = '${username.trim().toLowerCase()}'
12
  and event_type in ('PublicEvent')
13
),
14
most_recent_public_push as (
15
  select
16
    max(created_at) as timestamp,
17
    'Most recent public push' as action,
18
    repo_name
19
  from github_events
20
  where event_type = 'PushEvent'
21
  and lower(actor_login) = '${username.trim()}.toLowerCase()'
22
  group by repo_name
23
),
24
combined as (
25
  select * from public_events
26
  union all select * from most_recent_public_push
27
)
28
select * from combined order by timestamp FORMAT JSON`
29
    })
30
  ).json()

1
table = {
2
  if (results && results.data) {
3
    return Inputs.table(results.data);
4
  } else {
5
    return null;
6
  }
7
}