Historical Analytics (BigQuery)

Vartovii now leverages Google BigQuery to maintain a historical record of all project metrics. This enables trend analysis, predictive modeling, and deep dives into how Trust Scores evolve over time.

🏗️ Architecture

graph LR
    A[Cloud Scheduler] -->|Trigger Daily| B[Cloud Run Job]
    B -->|Fetch| C[PostgreSQL]
    B -->|Transform & Insert| D[BigQuery]
    D -->|Analyze| E[AI Reports]

📊 Data Schema

The data is stored in the vartovii_intelligence.daily_snapshots table.

Field	Type	Description
`snapshot_date`	DATE	Partitioning key (YYYY-MM-DD)
`slug`	STRING	Project ID (e.g., 'ethereum')
`trust_score`	INT	The final score (0-100)
`security_score`	FLOAT	Certik/Audit component
`community_score`	FLOAT	TweetScout component
`scam_alerted`	BOOL	Flag if marked as scam

🚀 Key Queries

1. Identify "Rising Stars" (Trust Score +20%)

WITH yesterday AS (
    SELECT slug, trust_score FROM `vartovii_intelligence.daily_snapshots`
    WHERE snapshot_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
),
today AS (
    SELECT slug, trust_score FROM `vartovii_intelligence.daily_snapshots`
    WHERE snapshot_date = CURRENT_DATE()
)
SELECT 
    t.slug, 
    y.trust_score as prev_score,
    t.trust_score as new_score,
    ((t.trust_score - y.trust_score) / y.trust_score) * 100 as pct_change
FROM today t
JOIN yesterday y ON t.slug = y.slug
WHERE t.trust_score > y.trust_score * 1.2

2. Spot "Rug Pulls" (Security Drops)

SELECT * FROM `vartovii_intelligence.daily_snapshots`
WHERE security_score < 40 
AND snapshot_date = CURRENT_DATE()
ORDER BY trust_score DESC

🤖 AI Integration

The Vartovii AI Assistant has access to this data via the get_historical_trends tool (planned), allowing users to ask questions like:

"How has Arbitrum's developer activity changed over last month?"

🏗️ Architecture​

📊 Data Schema​

🚀 Key Queries​

1. Identify "Rising Stars" (Trust Score +20%)​

2. Spot "Rug Pulls" (Security Drops)​