← Back to Docs

Technical Details

Database schema, API endpoints, cron schedule, and project structure.

Technology Stack

Frontend

  • Framework: Next.js 16 (App Router)
  • Language: TypeScript
  • Styling: Tailwind CSS v4
  • Charts: Recharts

Backend

  • API: Next.js API Routes
  • Database: PostgreSQL (Neon.tech)
  • Driver: Neon Serverless Driver
  • Hosting: Vercel

Database Schema

PostgreSQL with 7 core tables and 4 materialized views.

etf_flows

Daily Bitcoin ETF flow data

id              BIGSERIAL PRIMARY KEY
date            DATE NOT NULL UNIQUE
total_net       NUMERIC(12, 2)
ibit, fbtc, gbtc, arkb, bitb, others  NUMERIC(12, 2)
data_source     VARCHAR(50)
created_at      TIMESTAMPTZ
updated_at      TIMESTAMPTZ
stablecoin_flows

Daily stablecoin supply metrics

id                      BIGSERIAL PRIMARY KEY
date                    DATE NOT NULL UNIQUE
supply_change_percent   NUMERIC(8, 4)
market_cap_billions     NUMERIC(12, 2)
dominance_percent       NUMERIC(8, 4)
data_source             VARCHAR(50)
created_at, updated_at  TIMESTAMPTZ
derivatives_data

8-hourly derivatives snapshots. OI change % and liquidation values are derived at query time from raw OI columns and the mv_derivatives_7d_stats view.

id                    BIGSERIAL PRIMARY KEY
timestamp             TIMESTAMPTZ NOT NULL UNIQUE
funding_rate          NUMERIC(12, 8)
funding_rate_percent  NUMERIC(10, 6)
open_interest_btc     NUMERIC(16, 8)
open_interest_usd     NUMERIC(18, 2)
mark_price            NUMERIC(12, 2)
-- oi_change_24h_pct and avg_liquidations_7d are computed
-- from this table by mv_derivatives_7d_stats at query time
data_source           VARCHAR(50)
created_at, updated_at TIMESTAMPTZ
market_data

Daily market sentiment: source for the Price & Structure pillar

id                        BIGSERIAL PRIMARY KEY
date                      DATE NOT NULL UNIQUE
fear_greed_value          INT (0–100)
fear_greed_classification VARCHAR(50)
btc_dominance             NUMERIC(8, 4)
eth_btc_ratio             NUMERIC(10, 8)
btc_price                 NUMERIC(12, 2)
btc_24h_change            NUMERIC(8, 4)
data_source               VARCHAR(50)
created_at, updated_at    TIMESTAMPTZ
macro_data

Daily macro indicators: feeds the Macro Context overlay only

id          BIGSERIAL PRIMARY KEY
date        DATE NOT NULL UNIQUE
dxy         NUMERIC(10, 4)
us10y       NUMERIC(8, 4)
spx         NUMERIC(10, 2)
vix         NUMERIC(8, 4)
data_source VARCHAR(50)
created_at, updated_at TIMESTAMPTZ
price_action

Daily price & technical indicators (legacy: not used by regime score)

id                   BIGSERIAL PRIMARY KEY
date                 DATE NOT NULL UNIQUE
current_price        NUMERIC(12, 2)
sma20, sma50, sma200 NUMERIC(12, 2)
atr14                NUMERIC(12, 2)
volatility_percent   NUMERIC(8, 4)
data_source          VARCHAR(50)
created_at, updated_at TIMESTAMPTZ
data_sync_log

Audit trail for all data sync operations

id               BIGSERIAL PRIMARY KEY
table_name       VARCHAR(100)
sync_type        VARCHAR(50)
records_affected INT
data_source      VARCHAR(50)
status           VARCHAR(20)   -- success / error / partial
error_message    TEXT
started_at       TIMESTAMPTZ
completed_at     TIMESTAMPTZ
duration_ms      INT

Materialized Views

Pre-computed rolling aggregations refreshed after each sync. Eliminates expensive window functions at query time.

  • mv_etf_aggregates : 7D/30D rolling sums, cumulative totals
  • mv_stablecoin_aggregates : 7D/30D rolling averages of supply change
  • mv_derivatives_7d_stats : Funding stats, OI 7D moving averages
  • mv_macro_aggregates : 200D MAs, 30D changes for DXY/yields/SPX
Views are refreshed by calling refreshMaterializedViews() at the end of each sync job.

API Endpoints

Data Sync
POST/api/sync/dailyAutomated

Syncs daily data: Fear & Greed (Coinglass), ETF flows (Coinglass), stablecoins (DefiLlama + CoinGecko), macro (FMP + FRED), market data.

Schedule: Every 2 hours (0 */2 * * *)
POST/api/sync/derivativesAutomated

Syncs derivatives data: funding rate, open interest, and liquidations from Coinglass.

Schedule: Every 4 hours at :30 (30 */4 * * *)
POST/api/sync/exchange-flowsAutomated

Syncs exchange netflow data from Coinglass on-chain endpoints.

Schedule: Every 2 hours at :20 (20 */2 * * *)
POST/api/sync/liquidationsAutomated

Syncs liquidation history from Coinglass.

Schedule: Every 2 hours at :15 (15 */2 * * *)
POST/api/sync/market-dashboardAutomated

Refreshes market dashboard data.

Schedule: Every hour at :05 (5 */1 * * *)
POST/api/sync/whalesAutomated

Syncs whale wallet activity.

Schedule: Every hour at :10 (10 */1 * * *)
POST/api/sync/telegram-alertsAutomated

Evaluates and dispatches real-time Telegram alerts.

Schedule: Every hour at :25 (25 */1 * * *)
POST/api/sync/telegram-briefingAutomated

Sends daily macro briefing via Telegram.

Schedule: Daily at 00:00 UTC (0 0 * * *)
Health & Monitoring
GET/api/db/health

Returns database connection status, data freshness, and recent sync logs.

{
  "status": "healthy" | "degraded" | "down",
  "database": { "configured": true, "connected": true },
  "dataFreshness": [
    {
      "table_name": "etf_flows",
      "latest_date": "2026-02-20",
      "total_records": 265,
      "days_old": 0
    },
    ...
  ]
}

Automated Data Sync Schedule

Configured in vercel.json:

{
  "crons": [
    { "path": "/api/sync/daily",            "schedule": "0 */2 * * *"  },
    { "path": "/api/sync/derivatives",      "schedule": "30 */4 * * *" },
    { "path": "/api/sync/market-dashboard", "schedule": "5 */1 * * *"  },
    { "path": "/api/sync/whales",           "schedule": "10 */1 * * *" },
    { "path": "/api/sync/liquidations",     "schedule": "15 */2 * * *" },
    { "path": "/api/sync/exchange-flows",   "schedule": "20 */2 * * *" },
    { "path": "/api/sync/telegram-briefing","schedule": "0 0 * * *"    },
    { "path": "/api/sync/telegram-alerts",  "schedule": "25 */1 * * *" }
  ]
}
EndpointFrequencyData Synced
/api/sync/dailyEvery 2 hoursFear & Greed, ETF flows, stablecoins, macro, market data
/api/sync/derivativesEvery 4 hours (:30)Funding rate, open interest, liquidations (Coinglass)
/api/sync/exchange-flowsEvery 2 hours (:20)Exchange netflows (Coinglass on-chain)
/api/sync/liquidationsEvery 2 hours (:15)Liquidation history (Coinglass)
/api/sync/market-dashboardEvery hour (:05)Market dashboard refresh
/api/sync/whalesEvery hour (:10)Whale wallet activity
/api/sync/telegram-alertsEvery hour (:25)Telegram alert evaluation and dispatch
/api/sync/telegram-briefingDaily: 00:00 UTCDaily macro briefing via Telegram

Data Flow Architecture

1Ingestion

Fetcherslib/*_data_fetcher.ts files fetch data from external APIs
ValidationData validated against schema constraints before insert
Mutationslib/db/mutations.ts upserts records into PostgreSQL

2Aggregation

TriggersAuto-update updated_at timestamps on row changes
ViewsMaterialized views refresh after each sync cycle
LoggingAll sync runs recorded in data_sync_log for auditing

3Regime Calculation

Querieslib/db/queries.ts retrieves aggregated data from materialized views
Calculatorlib/regime_calculator_v3.ts applies all pillar scoring formulas
UIDashboard renders real-time regime classification and scores

Environment Variables

# Database
DATABASE_URL=postgresql://...

# Coinglass -- Fear & Greed, ETF flows, derivatives, exchange netflows
COINGLASS_API_KEY=...

# Financial Modeling Prep -- SPX, VIX, forex pairs (for DXY synthesis)
FMP_API_KEY=...

# FRED (Federal Reserve) -- US10Y yield, SEK/USD for DXY
FRED_API_KEY=...

# DefiLlama and CoinGecko are free -- no key required

# Telegram (optional -- for briefing and alerts)
TELEGRAM_BOT_TOKEN=...
TELEGRAM_CHAT_ID=...

Performance Optimizations

Database

  • · Materialized views for expensive aggregations
  • · Indexed columns (date DESC, timestamps)
  • · Partial indexes for time-range queries
  • · Serverless connection pooling (Neon)

Frontend

  • · Server-side rendering (Next.js)
  • · Client-side caching (React state)
  • · Automatic code splitting (Next.js)
  • · Edge deployment (Vercel)

Deployment

Vercel Deployment

  1. 1.Connect GitHub repository to Vercel
  2. 2.Configure environment variables in Vercel dashboard
  3. 3.Deploy automatically on git push to main
  4. 4.Cron jobs configured via vercel.json

Database Setup

  1. 1.Create Neon PostgreSQL database
  2. 2.Run lib/db/schema.sql in Neon console
  3. 3.Run scripts/setup-database.ts to create materialized views
  4. 4.Trigger initial sync: POST /api/sync/daily

Project Structure

btc-regime-dashboard/
├── app/
│   ├── page.tsx                    # Main dashboard
│   └── docs/                       # Documentation pages
│       ├── page.tsx
│       ├── data-sources/
│       ├── scoring/
│       ├── regime-classification/
│       ├── macro-context/
│       ├── timeframes/
│       └── technical/
├── component/
│   └── dashboard_v2.tsx            # Active dashboard component
├── lib/
│   ├── regime_calculator_v3.ts     # Regime scoring engine (v3)
│   ├── *_data_fetcher.ts           # External data fetchers
│   ├── db/
│   │   ├── schema.sql              # PostgreSQL schema
│   │   ├── neon.ts                 # Database connection
│   │   ├── queries.ts              # Read operations
│   │   └── mutations.ts            # Write operations
│   └── types_definition.ts
├── pages/
│   └── api/
│       ├── sync/
│       │   ├── daily.ts
│       │   └── derivatives.ts
│       └── db/
│           └── health.ts
├── scripts/
│   ├── setup-database.ts
│   └── test-alignment.ts
├── vercel.json                     # Cron configuration
└── package.json

Local Development

Start dev server
npm install
npm run dev
# Visit http://localhost:3000
Database scripts
npx tsx scripts/check-database.ts      # Check data status
npx tsx scripts/test-alignment.ts      # Test scoring alignment
npx tsx scripts/setup-database.ts      # Create materialized views
Manual data sync
curl -X POST http://localhost:3000/api/sync/daily
curl -X POST http://localhost:3000/api/sync/derivatives
← 7D vs 30D TimeframesBack to Docs →