crowd.dev/services/libs/tinybird/datasources/project_insights_copy_ds.datasource at a20d1bc214e7db6da628c8d5fb67d80b61a9c1bc · linuxfoundation/crowd.dev · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
DESCRIPTION >
    - `project_insights_copy_ds` contains materialized project insights data.
    - Populated by `project_insights_copy.pipe` copy pipe.
    - Includes project metadata, health score, first commit, and activity metrics for last 365 days and previous 365 days.
    - `id` column is the primary key identifier for the project or repository.
    - `type` column indicates the record type: 'project' for project insights or 'repo' for repository insights.
    - `repoUrl` column is the full repository URL for repo type records (empty string for project type).
    - `name` column is the human-readable project name.
    - `slug` column is the URL-friendly identifier used in routing and filtering.
    - `logoUrl` column is the URL to the project's logo image.
    - `isLF` column indicates whether the project is a Linux Foundation project (1 = true, 0 = false).
    - `contributorCount` column is the total number of contributors for the project.
    - `organizationCount` column is the total number of organizations for the project.
    - `softwareValue` column is the estimated economic value of the software.
    - `contributorDependencyCount` column is the number of contributors making up 51% of contributions (bus factor).
    - `contributorDependencyPercentage` column is the combined contribution percentage of dependent contributors.
    - `organizationDependencyCount` column is the number of organizations making up 51% of contributions.
    - `organizationDependencyPercentage` column is the combined contribution percentage of dependent organizations.
    - `achievements` column is an array of tuples (leaderboardType, rank, totalCount) representing project achievements.
    - `healthScore` column is the overall health score for the project (0-100).
    - `contributorHealthScore` column is the health score percentage for the contributors category (0-100).
    - `popularityHealthScore` column is the health score percentage for the popularity category (0-100).
    - `developmentHealthScore` column is the health score percentage for the development category (0-100).
    - `securityHealthScore` column is the health score percentage for the security category (0-100).
    - `firstCommit` column is the timestamp of the first commit in the project.
    - `starsLast365Days` column is the count of stars in the last 365 days.
    - `forksLast365Days` column is the count of forks in the last 365 days.
    - `activeContributorsLast365Days` column is the unique count of active contributors in the last 365 days.
    - `activeOrganizationsLast365Days` column is the unique count of active organizations in the last 365 days.
    - `starsPrevious365Days` column is the count of stars in the previous 365 days (365-730 days ago).
    - `forksPrevious365Days` column is the count of forks in the previous 365 days (365-730 days ago).
    - `activeContributorsPrevious365Days` column is the unique count of active contributors in the previous 365 days (365-730 days ago).
    - `activeOrganizationsPrevious365Days` column is the unique count of active organizations in the previous 365 days (365-730 days ago).
    - `status` column is the current status of the project (e.g., 'active', 'archived').
    - `healthScoreV2` column is the Akrites-methodology composite health score (0-100), averaged from `ossPackages_enriched_ds.healthScore` across the project's linked packages. Distinct from the legacy `healthScore` column (community/contributor-based); null when the project has no linked package data.
    - `healthLabel` column buckets `healthScoreV2`: 'excellent' (85+), 'healthy' (70-84), 'fair' (50-69), 'concerning' (30-49), 'critical' (<30). Lowercase, matches `ossPackages_enriched_ds.healthLabel` convention.
    - `lifecycleLabel` column is the project's aggregated maintenance state across its packages, using best-state-wins precedence (active > stable > declining > abandoned > archived) over `ossPackages_enriched_ds.lifecycleLabel`.
    - `impactScore` column is the average Osprey criticality `impact` (0-100) across the project's packages. Null when the project has no linked package data.
    - `impactLabel` column buckets `impactScore`: 'foundational' (85-100), 'major' (60-84), 'moderate' (30-59), 'minor' (0-29). Null when `impactScore` is null.

TAGS "Project insights", "Metrics"

SCHEMA >
    `id` String,
    `type` String,
    `repoUrl` String,
    `name` String,
    `slug` String,
    `logoUrl` String,
    `isLF` UInt8,
    `status` String,
    `contributorCount` UInt64,
    `organizationCount` UInt64,
    `softwareValue` UInt64,
    `contributorDependencyCount` UInt64,
    `contributorDependencyPercentage` Float64,
    `organizationDependencyCount` UInt64,
    `organizationDependencyPercentage` Float64,
    `achievements` Array(Tuple(String, UInt64, UInt64)),
    `healthScore` Nullable(Float64),
    `contributorHealthScore` Nullable(Float64),
    `popularityHealthScore` Nullable(Float64),
    `developmentHealthScore` Nullable(Float64),
    `securityHealthScore` Nullable(Float64),
    `firstCommit` Nullable(DateTime64(3)),
    `starsLast365Days` UInt64,
    `forksLast365Days` UInt64,
    `activeContributorsLast365Days` UInt64,
    `activeOrganizationsLast365Days` UInt64,
    `starsPrevious365Days` UInt64,
    `forksPrevious365Days` UInt64,
    `activeContributorsPrevious365Days` UInt64,
    `activeOrganizationsPrevious365Days` UInt64,
    `healthScoreV2` Nullable(UInt8),
    `healthLabel` Nullable(String),
    `lifecycleLabel` Nullable(String),
    `impactScore` Nullable(UInt8),
    `impactLabel` Nullable(String)

ENGINE MergeTree
ENGINE_SORTING_KEY type, id