Server Instructions

## Complete Schema Reference Tables live under `github.main.*` and `github.analytics.*`. `github.<table>` remains a DuckLake shortcut for main-schema tables. Prefer analytics tables for common search/profile/activity/comparison workflows; use raw main activity tables only when you need detail records. ## Schema: main ### repos (~404K rows) Qualified name: github.main.repos repo_id BIGINT, repo_node_id VARCHAR, name_with_owner VARCHAR, stars_count INTEGER, fork_count INTEGER, created_at TIMESTAMP, pushed_at TIMESTAMP, updated_at TIMESTAMP ### repo_metadata (~405K rows) Qualified name: github.main.repo_metadata repo_node_id VARCHAR, description VARCHAR, homepage VARCHAR, topics VARCHAR[], language VARCHAR, license_key VARCHAR, license_name VARCHAR, license_spdx_id VARCHAR, default_branch VARCHAR, size BIGINT, visibility VARCHAR, archived BOOLEAN, disabled BOOLEAN, is_fork BOOLEAN, is_template BOOLEAN, has_issues BOOLEAN, has_wiki BOOLEAN, has_pages BOOLEAN, has_discussions BOOLEAN, has_projects BOOLEAN, has_pull_requests BOOLEAN, has_downloads BOOLEAN, has_commit_comments BOOLEAN, watchers_count BIGINT, open_issues_count BIGINT, subscribers_count BIGINT, owner_type VARCHAR ### repo_scores (~404K rows) Qualified name: github.main.repo_scores repo_node_id VARCHAR, name_with_owner VARCHAR, age_cohort VARCHAR, star_cohort VARCHAR, cohort_group VARCHAR, cohort_size BIGINT, has_issues BOOLEAN, has_pull_requests BOOLEAN, has_star_data BOOLEAN, has_pr_data BOOLEAN, has_commit_data BOOLEAN, has_issue_data BOOLEAN, stars_count INTEGER, score_overall DOUBLE, score_star_count INTEGER, score_star_velocity INTEGER, score_issue_count INTEGER, score_issue_velocity INTEGER, score_issue_unique_creators INTEGER, score_issue_time_to_close INTEGER, score_fork_pr_count INTEGER, score_fork_pr_velocity INTEGER, score_pr_merge_count INTEGER, score_unique_contributors INTEGER, score_unique_pr_authors INTEGER, score_pr_time_to_merge INTEGER, star_velocity DOUBLE, issue_count BIGINT, issue_velocity DOUBLE, issue_unique_creators BIGINT, issue_median_days_to_close DOUBLE, fork_pr_count BIGINT, fork_pr_velocity DOUBLE, pr_merge_count BIGINT, unique_contributors BIGINT, unique_pr_authors BIGINT, pr_median_days_to_merge DOUBLE, pct_star_count DOUBLE, pct_star_velocity DOUBLE, pct_issue_count DOUBLE, pct_issue_velocity DOUBLE, pct_issue_unique_creators DOUBLE, pct_issue_time_to_close DOUBLE, pct_fork_pr_count DOUBLE, pct_fork_pr_velocity DOUBLE, pct_pr_merge_count DOUBLE, pct_unique_contributors DOUBLE, pct_unique_pr_authors DOUBLE, pct_pr_time_to_merge DOUBLE, pct_overall_star_count DOUBLE, pct_overall_star_velocity DOUBLE, pct_overall_issue_count DOUBLE, pct_overall_issue_velocity DOUBLE, pct_overall_issue_unique_creators DOUBLE, pct_overall_issue_time_to_close DOUBLE, pct_overall_fork_pr_count DOUBLE, pct_overall_fork_pr_velocity DOUBLE, pct_overall_pr_merge_count DOUBLE, pct_overall_unique_contributors DOUBLE, pct_overall_unique_pr_authors DOUBLE, pct_overall_pr_time_to_merge DOUBLE ### repo_categories (~403K rows) Qualified name: github.main.repo_categories repo_node_id VARCHAR, top_category_id BIGINT, top_category VARCHAR, similarity DOUBLE ### repo_category_similarities (~10.1M rows) Qualified name: github.main.repo_category_similarities repo_node_id VARCHAR, category_id BIGINT, similarity DOUBLE ### repo_contributors (~7.1M rows) Qualified name: github.main.repo_contributors repo_node_id VARCHAR, user_id BIGINT, login VARCHAR, login_lower VARCHAR, contributions BIGINT, type VARCHAR ### repo_readme_summaries (~432K rows) Qualified name: github.main.repo_readme_summaries repo_node_id VARCHAR, name_with_owner VARCHAR, summary VARCHAR, content_hash VARCHAR, generated_at TIMESTAMP, tier VARCHAR ### repo_readme_summary_embeddings (~403K rows) Qualified name: github.main.repo_readme_summary_embeddings repo_node_id VARCHAR, content_hash VARCHAR, embedding FLOAT[] ### github_users (~1.9M rows) Qualified name: github.main.github_users user_id BIGINT, login VARCHAR, name VARCHAR, email VARCHAR, bio VARCHAR, company VARCHAR, location VARCHAR, website_url VARCHAR, twitter_username VARCHAR, is_hireable BOOLEAN, followers_count BIGINT, following_count BIGINT, public_repos_count BIGINT, created_at TIMESTAMP, updated_at TIMESTAMP, fetched_at VARCHAR ### repo_pulls (~34.9M rows) Qualified name: github.main.repo_pulls pull_id BIGINT, pull_number BIGINT, node_id VARCHAR, title VARCHAR, state VARCHAR, draft BOOLEAN, locked BOOLEAN, user_id BIGINT, user_login VARCHAR, author_association VARCHAR, created_at TIMESTAMP, updated_at TIMESTAMP, closed_at TIMESTAMP, merged_at TIMESTAMP, merge_commit_sha VARCHAR, head_ref VARCHAR, head_sha VARCHAR, head_repo_id BIGINT, head_repo_node_id VARCHAR, head_repo_full_name VARCHAR, head_repo_owner_login VARCHAR, base_ref VARCHAR, base_sha VARCHAR, base_repo_id BIGINT, base_repo_node_id VARCHAR, base_repo_full_name VARCHAR, base_repo_owner_login VARCHAR, labels VARCHAR[], repo_node_id VARCHAR ### repo_issues (~47.5M rows) Qualified name: github.main.repo_issues issue_id BIGINT, issue_number BIGINT, node_id VARCHAR, title VARCHAR, state VARCHAR, state_reason VARCHAR, locked BOOLEAN, active_lock_reason JSON, user_id BIGINT, user_login VARCHAR, author_association VARCHAR, assignee_logins VARCHAR[], milestone_id BIGINT, milestone_number BIGINT, milestone_title VARCHAR, comments BIGINT, reactions_total BIGINT, created_at TIMESTAMP, updated_at TIMESTAMP, closed_at TIMESTAMP, is_pull_request BOOLEAN, labels VARCHAR[], html_url VARCHAR, repo_node_id VARCHAR ### repo_stars_daily (~61M rows) Qualified name: github.main.repo_stars_daily repo_node_id VARCHAR, starred_date DATE, stars_delta BIGINT ## Schema: analytics ### repo_profile (~404K rows) Qualified name: github.analytics.repo_profile repo_id BIGINT, repo_node_id VARCHAR, name_with_owner VARCHAR, name_with_owner_lower VARCHAR, owner_login VARCHAR, owner_login_lower VARCHAR, repo_name VARCHAR, repo_name_lower VARCHAR, created_at TIMESTAMP, pushed_at TIMESTAMP, updated_at TIMESTAMP, stars_count INTEGER, fork_count INTEGER, description VARCHAR, homepage VARCHAR, topics VARCHAR[], language VARCHAR, license_key VARCHAR, license_name VARCHAR, license_spdx_id VARCHAR, default_branch VARCHAR, size BIGINT, visibility VARCHAR, archived BOOLEAN, disabled BOOLEAN, is_fork BOOLEAN, is_template BOOLEAN, has_issues BOOLEAN, has_wiki BOOLEAN, has_pages BOOLEAN, has_discussions BOOLEAN, has_projects BOOLEAN, watchers_count BIGINT, open_issues_count BIGINT, subscribers_count BIGINT, owner_type VARCHAR, has_pull_requests BOOLEAN, top_category_id BIGINT, top_category VARCHAR, category_similarity DOUBLE, age_cohort VARCHAR, star_cohort VARCHAR, cohort_group VARCHAR, cohort_size BIGINT, has_star_data BOOLEAN, has_pr_data BOOLEAN, has_commit_data BOOLEAN, has_issue_data BOOLEAN, score_overall DOUBLE, score_star_count INTEGER, score_star_velocity INTEGER, score_issue_count INTEGER, score_issue_velocity INTEGER, score_issue_unique_creators INTEGER, score_issue_time_to_close INTEGER, score_fork_pr_count INTEGER, score_fork_pr_velocity INTEGER, score_pr_merge_count INTEGER, score_unique_contributors INTEGER, score_unique_pr_authors INTEGER, score_pr_time_to_merge INTEGER, star_velocity DOUBLE, issue_count BIGINT, issue_velocity DOUBLE, issue_unique_creators BIGINT, issue_median_days_to_close DOUBLE, fork_pr_count BIGINT, fork_pr_velocity DOUBLE, pr_merge_count BIGINT, unique_contributors BIGINT, unique_pr_authors BIGINT, pr_median_days_to_merge DOUBLE, pct_star_count DOUBLE, pct_star_velocity DOUBLE, pct_issue_count DOUBLE, pct_issue_velocity DOUBLE, pct_issue_unique_creators DOUBLE, pct_issue_time_to_close DOUBLE, pct_fork_pr_count DOUBLE, pct_fork_pr_velocity DOUBLE, pct_pr_merge_count DOUBLE, pct_unique_contributors DOUBLE, pct_unique_pr_authors DOUBLE, pct_pr_time_to_merge DOUBLE, pct_overall_star_count DOUBLE, pct_overall_star_velocity DOUBLE, pct_overall_issue_count DOUBLE, pct_overall_issue_velocity DOUBLE, pct_overall_issue_unique_creators DOUBLE, pct_overall_issue_time_to_close DOUBLE, pct_overall_fork_pr_count DOUBLE, pct_overall_fork_pr_velocity DOUBLE, pct_overall_pr_merge_count DOUBLE, pct_overall_unique_contributors DOUBLE, pct_overall_unique_pr_authors DOUBLE, pct_overall_pr_time_to_merge DOUBLE, readme_summary VARCHAR, readme_summary_generated_at TIMESTAMP ### repo_activity_summary (~404K rows) Qualified name: github.analytics.repo_activity_summary repo_node_id VARCHAR, stars_7d BIGINT, stars_30d BIGINT, stars_90d BIGINT, stars_180d BIGINT, stars_365d BIGINT, issues_opened_7d BIGINT, issues_opened_30d BIGINT, issues_opened_90d BIGINT, issues_opened_180d BIGINT, issues_opened_365d BIGINT, issues_closed_7d BIGINT, issues_closed_30d BIGINT, issues_closed_90d BIGINT, issues_closed_180d BIGINT, issues_closed_365d BIGINT, prs_opened_7d BIGINT, prs_opened_30d BIGINT, prs_opened_90d BIGINT, prs_opened_180d BIGINT, prs_opened_365d BIGINT, prs_merged_7d BIGINT, prs_merged_30d BIGINT, prs_merged_90d BIGINT, prs_merged_180d BIGINT, prs_merged_365d BIGINT, commits_7d BIGINT, commits_30d BIGINT, commits_90d BIGINT, commits_180d BIGINT, commits_365d BIGINT, activity_90d BIGINT, last_starred_at TIMESTAMP, last_issue_at TIMESTAMP, last_pr_at TIMESTAMP, last_commit_at TIMESTAMP, last_activity_at TIMESTAMP, issue_median_days_to_close_90d DOUBLE, pr_median_days_to_merge_90d DOUBLE ### repo_profile_with_activity (~404K rows) Qualified name: github.analytics.repo_profile_with_activity repo_id BIGINT, repo_node_id VARCHAR, name_with_owner VARCHAR, name_with_owner_lower VARCHAR, owner_login VARCHAR, owner_login_lower VARCHAR, repo_name VARCHAR, repo_name_lower VARCHAR, created_at TIMESTAMP, pushed_at TIMESTAMP, updated_at TIMESTAMP, stars_count INTEGER, fork_count INTEGER, description VARCHAR, homepage VARCHAR, topics VARCHAR[], language VARCHAR, license_key VARCHAR, license_name VARCHAR, license_spdx_id VARCHAR, default_branch VARCHAR, size BIGINT, visibility VARCHAR, archived BOOLEAN, disabled BOOLEAN, is_fork BOOLEAN, is_template BOOLEAN, has_issues BOOLEAN, has_wiki BOOLEAN, has_pages BOOLEAN, has_discussions BOOLEAN, has_projects BOOLEAN, watchers_count BIGINT, open_issues_count BIGINT, subscribers_count BIGINT, owner_type VARCHAR, has_pull_requests BOOLEAN, top_category_id BIGINT, top_category VARCHAR, category_similarity DOUBLE, age_cohort VARCHAR, star_cohort VARCHAR, cohort_group VARCHAR, cohort_size BIGINT, has_star_data BOOLEAN, has_pr_data BOOLEAN, has_commit_data BOOLEAN, has_issue_data BOOLEAN, score_overall DOUBLE, score_star_count INTEGER, score_star_velocity INTEGER, score_issue_count INTEGER, score_issue_velocity INTEGER, score_issue_unique_creators INTEGER, score_issue_time_to_close INTEGER, score_fork_pr_count INTEGER, score_fork_pr_velocity INTEGER, score_pr_merge_count INTEGER, score_unique_contributors INTEGER, score_unique_pr_authors INTEGER, score_pr_time_to_merge INTEGER, star_velocity DOUBLE, issue_count BIGINT, issue_velocity DOUBLE, issue_unique_creators BIGINT, issue_median_days_to_close DOUBLE, fork_pr_count BIGINT, fork_pr_velocity DOUBLE, pr_merge_count BIGINT, unique_contributors BIGINT, unique_pr_authors BIGINT, pr_median_days_to_merge DOUBLE, pct_star_count DOUBLE, pct_star_velocity DOUBLE, pct_issue_count DOUBLE, pct_issue_velocity DOUBLE, pct_issue_unique_creators DOUBLE, pct_issue_time_to_close DOUBLE, pct_fork_pr_count DOUBLE, pct_fork_pr_velocity DOUBLE, pct_pr_merge_count DOUBLE, pct_unique_contributors DOUBLE, pct_unique_pr_authors DOUBLE, pct_pr_time_to_merge DOUBLE, pct_overall_star_count DOUBLE, pct_overall_star_velocity DOUBLE, pct_overall_issue_count DOUBLE, pct_overall_issue_velocity DOUBLE, pct_overall_issue_unique_creators DOUBLE, pct_overall_issue_time_to_close DOUBLE, pct_overall_fork_pr_count DOUBLE, pct_overall_fork_pr_velocity DOUBLE, pct_overall_pr_merge_count DOUBLE, pct_overall_unique_contributors DOUBLE, pct_overall_unique_pr_authors DOUBLE, pct_overall_pr_time_to_merge DOUBLE, readme_summary VARCHAR, readme_summary_generated_at TIMESTAMP, stars_7d BIGINT, stars_30d BIGINT, stars_90d BIGINT, stars_180d BIGINT, stars_365d BIGINT, issues_opened_7d BIGINT, issues_opened_30d BIGINT, issues_opened_90d BIGINT, issues_opened_180d BIGINT, issues_opened_365d BIGINT, issues_closed_7d BIGINT, issues_closed_30d BIGINT, issues_closed_90d BIGINT, issues_closed_180d BIGINT, issues_closed_365d BIGINT, prs_opened_7d BIGINT, prs_opened_30d BIGINT, prs_opened_90d BIGINT, prs_opened_180d BIGINT, prs_opened_365d BIGINT, prs_merged_7d BIGINT, prs_merged_30d BIGINT, prs_merged_90d BIGINT, prs_merged_180d BIGINT, prs_merged_365d BIGINT, commits_7d BIGINT, commits_30d BIGINT, commits_90d BIGINT, commits_180d BIGINT, commits_365d BIGINT, activity_90d BIGINT, last_starred_at TIMESTAMP, last_issue_at TIMESTAMP, last_pr_at TIMESTAMP, last_commit_at TIMESTAMP, last_activity_at TIMESTAMP, issue_median_days_to_close_90d DOUBLE, pr_median_days_to_merge_90d DOUBLE ### repo_top_contributors (~4M rows) Qualified name: github.analytics.repo_top_contributors repo_node_id VARCHAR, contributor_rank BIGINT, user_id BIGINT, login VARCHAR, login_lower VARCHAR, contributions BIGINT, type VARCHAR, name VARCHAR, company VARCHAR, location VARCHAR, bio VARCHAR, website_url VARCHAR, twitter_username VARCHAR, followers_count BIGINT ### repo_activity_monthly (~10M rows) Qualified name: github.analytics.repo_activity_monthly repo_node_id VARCHAR, month DATE, stars BIGINT, issues_opened BIGINT, issues_closed BIGINT, prs_opened BIGINT, prs_merged BIGINT, commits BIGINT ### repo_readme_similar_repos (~39.2M rows) Qualified name: github.analytics.repo_readme_similar_repos source_repo_node_id VARCHAR, similar_repo_node_id VARCHAR, rank SMALLINT, similarity FLOAT ### category_stats (~25 rows) Qualified name: github.analytics.category_stats top_category_id BIGINT, top_category VARCHAR, repo_count BIGINT, total_stars BIGINT, stars_90d BIGINT, activity_90d BIGINT, top_languages VARCHAR[], example_repos VARCHAR[] ### language_category_stats (~2K rows) Qualified name: github.analytics.language_category_stats language VARCHAR, top_category_id BIGINT, top_category VARCHAR, repo_count BIGINT, total_stars BIGINT, stars_90d BIGINT, activity_90d BIGINT, example_repos VARCHAR[] ### repo_topic_index (~2M rows) Qualified name: github.analytics.repo_topic_index topic VARCHAR, repo_node_id VARCHAR, name_with_owner VARCHAR, name_with_owner_lower VARCHAR, owner_login_lower VARCHAR, repo_name_lower VARCHAR, language VARCHAR, top_category VARCHAR, stars_count INTEGER, score_overall DOUBLE, stars_90d BIGINT, activity_90d BIGINT Powerset Research public DuckLake tips - Query only the documented public tables and prefer selective filters before joins. The canonical repository join key across public tables is repo_node_id. - Write queries in DuckDB SQL. DuckDB functions, types, date syntax, CTEs, and list functions are available against the attached DuckLake. - Prefer `github.analytics.repo_profile_with_activity`, `github.analytics.repo_profile`, `github.analytics.repo_activity_summary`, and `github.analytics.repo_top_contributors` for repository search, profile, activity, and comparison workflows. Use the lower-cased lookup columns on repo_profile/repo_profile_with_activity for name matching. - Use `github.analytics.category_stats`, `github.analytics.language_category_stats`, and `github.analytics.repo_topic_index` for category, language/category, and topic discovery. - Use the structured `get_similar_repos` tool, or query `github.analytics.repo_readme_similar_repos`, for similar-repository recommendations, alternatives/competitors, adjacent projects, and seed-expansion tasks. - For direct similar-repository SQL, resolve the seed repo to `source_repo_node_id`, filter `repo_readme_similar_repos` by `source_repo_node_id` first, add `rank <= N` where N is at most 100, and join neighbors to `github.analytics.repo_profile` or `github.analytics.repo_profile_with_activity` for names, stars, language, category, README summaries, and optional activity columns. - Avoid scanning `github.main.repo_readme_summary_embeddings` for nearest neighbors to a repository. Use embeddings directly only for custom vector experiments or small filtered candidate sets not covered by the precomputed top-100 neighbors. - Use raw `github.main.repo_pulls`, `github.main.repo_issues`, and `github.main.repo_stars_daily` only when you need detail records or a custom aggregation not covered by analytics tables. - Large-ish tables such as repo_category_similarities, repo_contributors, repo_readme_summary_embeddings, github_users, repo_pulls, repo_issues, and repo_stars_daily should be filtered by repo_node_id, user_id, category_id, date ranges, or other selective columns where possible. - repo_pulls and repo_issues are large public activity tables; generally filter them by repo_node_id and created_at/closed_at/merged_at ranges, or join them to a small candidate repository set before aggregating. - repo_pulls and repo_issues only contain items created within the last ~2 years. Older PRs/issues are not present even if they were recently updated, merged, or closed. Do not use these tables to compute all-time PR/issue counts; they reflect recent activity only. - repo_issues includes pull requests (GitHub's Issues API returns both). Use the is_pull_request column to filter them out when counting issues only. - repo_stars_daily is the public star-history table with one row per repo/day. Raw repo_stars is intentionally not public; use repo_stars_daily for star history trends and repos.stars_count for current totals. - repo_stars_daily is derived from individual GitHub stargazer events, which are capped at ~40,000 per repository by the GitHub API. For repos with more than 40k stars, the star history is incomplete and SUM(stars_delta) will undercount the true total. Always use repos.stars_count for accurate current star counts, not aggregations from repo_stars_daily. - The public dataset covers repositories in the active universe: repos with at least 10 GitHub stars that have been pushed to within the last 90 days. - For total GitHub stars, prefer repos.stars_count. repo_scores.stars_count is the same value at scoring time and is included for scoring context; repo_metadata.watchers_count is metadata from a different source/update path and can be stale or differ. - Treat repo_scores metrics such as issue_count, open_issues_count-derived scores, and unique_contributors as scoring snapshot features, not canonical live counts. Use repo_metadata.open_issues_count for current GitHub metadata, and use repo_contributors for contributor-edge analysis. - repo_readme_summary_embeddings exposes README summary embeddings as a FLOAT[] column for custom vector experiments. Prefer filtering/materializing a small non-null candidate set first, then score with DuckDB list similarity functions such as list_cosine_similarity. - The MCP query tool passes SQL to DuckDB without parsing it; keep queries selective and bounded. - Results are capped by row count and encoded JSON response size; use LIMIT and selective predicates. - You can attach the same public frozen DuckLake directly from DuckDB: INSTALL ducklake; INSTALL httpfs; LOAD ducklake; LOAD httpfs; ATTACH 'ducklake:<url>' AS github (READ_ONLY); SELECT * FROM github.repo_metadata LIMIT 10; Replace <url> with the public DUCKLAKE_URL, for example an HTTPS URL ending in /latest/public.ducklake or /snapshots/<version>/public.ducklake.