Querying Data
The query command reads JSONL files directly using DuckDB and exposes these views:
| View | Description |
|---|---|
metrics |
Unified time-series: all sources including aggregated events |
metrics_filled |
Like metrics, with every total_* series carried forward to every day |
indicators |
Derived growth rate and trend for daily metrics (28-day windows) |
events |
Raw events with timestamp, user and issue or PR number (e.g., GitHub forks, issues, PRs, comments) |
content |
Entity data from content providers (e.g., issues, YouTube videos, feed items) |
projects |
Project metadata from your config |
targets |
Every project, source and target in your config, with canonical saying which copy of a shared target counts |
fetch_log |
One row per fetch, with its records and errors |
Examples
Daily star counts
velocirepo query "
SELECT project, date, value AS stars
FROM metrics
WHERE source = 'github' AND metric = 'daily_stars'
ORDER BY date DESC
LIMIT 5
"┌──────────────────┬────────────┬───────┐
│ project │ date │ stars │
├──────────────────┼────────────┼───────┤
│ ggsql │ 2026-06-22 │ 1 │
│ gt │ 2026-06-22 │ 1 │
│ images-workbench │ 2026-06-22 │ 1 │
│ rmarkdown │ 2026-06-22 │ 1 │
│ pointblank │ 2026-06-22 │ 1 │
└──────────────────┴────────────┴───────┘Total stars per project
Summing daily_stars gives the stars a repository received in a period. For the current total, use total_stars, the star count GitHub reports on each fetch. Like other total_* metrics it’s stored only when it changes, so take the latest value of each repository:
velocirepo query "
SELECT p.name, SUM(arg_max_value) AS stars
FROM (
SELECT project, target, arg_max(value, date) AS arg_max_value
FROM metrics
WHERE source = 'github' AND metric = 'total_stars'
GROUP BY project, target
) m
JOIN projects p ON m.project = p.id
GROUP BY p.name
ORDER BY stars DESC
LIMIT 5
"Monthly merged pull requests using raw events
The events view gives you access to individual events when you need per-user or per-timestamp detail:
velocirepo query "
SELECT date_trunc('month', datetime)::DATE AS month, COUNT(*) AS merged, COUNT(DISTINCT \"user\") AS authors
FROM events
WHERE project = 'quarto' AND type = 'pr_merge'
GROUP BY month
ORDER BY month DESC
LIMIT 5
"Top YouTube videos by views
velocirepo query "
SELECT c.title, m.value AS views
FROM metrics m
JOIN content c ON m.tags->>'video_id' = c.id
WHERE m.source = 'youtube' AND m.metric = 'total_views'
ORDER BY m.value DESC
LIMIT 5
"Latest metrics across sources
velocirepo query "
SELECT project, source, metric, date, value
FROM metrics
WHERE source != 'github'
ORDER BY date DESC
LIMIT 5
"┌───────────────────┬─────────┬─────────────────┬────────────┬─────────┐
│ project │ source │ metric │ date │ value │
├───────────────────┼─────────┼─────────────────┼────────────┼─────────┤
│ shiny-r │ openvsx │ total_downloads │ 2026-06-22 │ 2404755 │
│ shiny-r │ openvsx │ rating │ 2026-06-22 │ 5 │
│ shiny-r │ openvsx │ reviews │ 2026-06-22 │ 2 │
│ pointblank-python │ pypi │ daily_downloads │ 2026-06-22 │ 604 │
│ orbital-r │ cran │ daily_downloads │ 2026-06-22 │ 37 │
└───────────────────┴─────────┴─────────────────┴────────────┴─────────┘Output formats
By default, results are printed as a table. Use --json, --csv, or --parquet for machine-readable output:
velocirepo query --csv "SELECT project, metric, value FROM metrics LIMIT 3"
velocirepo query --json "SELECT project, metric, value FROM metrics LIMIT 3"
velocirepo query --parquet "SELECT * FROM metrics" > metrics.parquetSchema
The schema command shows all available columns:
velocirepo schema┌────────────────┬──────────────┬───────────┐
│ TABLE │ COLUMN │ TYPE │
├────────────────┼──────────────┼───────────┤
│ content │ project │ VARCHAR │
│ content │ source │ VARCHAR │
│ content │ target │ VARCHAR │
│ content │ id │ VARCHAR │
│ content │ ref │ INTEGER │
│ content │ title │ VARCHAR │
│ content │ description │ VARCHAR │
│ content │ content │ VARCHAR │
│ content │ published_at │ TIMESTAMP │
│ content │ updated_at │ TIMESTAMP │
│ content │ url │ VARCHAR │
│ content │ duration │ BIGINT │
│ content │ tags │ JSON │
│ content │ type │ VARCHAR │
│ content │ extra │ JSON │
│ events │ project │ VARCHAR │
│ events │ source │ VARCHAR │
│ events │ type │ VARCHAR │
│ events │ target │ VARCHAR │
│ events │ datetime │ TIMESTAMP │
│ events │ ref │ INTEGER │
│ events │ user │ VARCHAR │
│ events │ extra │ JSON │
│ fetch_log │ run_id │ VARCHAR │
│ fetch_log │ timestamp │ TIMESTAMP │
│ fetch_log │ version │ VARCHAR │
│ fetch_log │ command │ VARCHAR │
│ fetch_log │ source │ VARCHAR │
│ fetch_log │ project │ VARCHAR │
│ fetch_log │ records │ INTEGER │
│ fetch_log │ files │ INTEGER │
│ fetch_log │ start_date │ VARCHAR │
│ fetch_log │ end_date │ VARCHAR │
│ fetch_log │ duration_ms │ BIGINT │
│ fetch_log │ status_code │ INTEGER │
│ fetch_log │ skipped │ VARCHAR │
│ fetch_log │ error │ VARCHAR │
│ indicators │ project │ VARCHAR │
│ indicators │ source │ VARCHAR │
│ indicators │ target │ VARCHAR │
│ indicators │ metric │ VARCHAR │
│ indicators │ indicator │ VARCHAR │
│ indicators │ date │ DATE │
│ indicators │ value │ DOUBLE │
│ indicators │ extra │ JSON │
│ metrics │ project │ VARCHAR │
│ metrics │ source │ VARCHAR │
│ metrics │ target │ VARCHAR │
│ metrics │ metric │ VARCHAR │
│ metrics │ date │ DATE │
│ metrics │ value │ BIGINT │
│ metrics │ extra │ JSON │
│ metrics_filled │ project │ VARCHAR │
│ metrics_filled │ source │ VARCHAR │
│ metrics_filled │ target │ VARCHAR │
│ metrics_filled │ metric │ VARCHAR │
│ metrics_filled │ date │ DATE │
│ metrics_filled │ value │ BIGINT │
│ metrics_filled │ extra │ JSON │
│ projects │ id │ VARCHAR │
│ projects │ name │ VARCHAR │
│ projects │ description │ VARCHAR │
│ projects │ color │ VARCHAR │
│ projects │ tags │ VARCHAR[] │
│ projects │ website │ VARCHAR │
│ projects │ logo │ VARCHAR │
│ targets │ project │ VARCHAR │
│ targets │ source │ VARCHAR │
│ targets │ target │ VARCHAR │
│ targets │ canonical │ BOOLEAN │
└────────────────┴──────────────┴───────────┘