DEV Community

undersilentskies
undersilentskies

Posted on

Creating a QuantifiedSelf Dashboard - Part 1

#quantifiedself #dataengineering #duckdb #evidencedev

Hello DEV.to! I've been lurking this site for years but I hadn't felt like I had anything to share until now. You see...

The problem

I've recently realized I have a problem we're all very familiar with: years of disconnected data with no central hub. What brought this to my attention was trying to visualize my health data in one place. I have years of phone step counts, months of data from a previous fitness band, lab reports, etc...

Most people don't think this is a problem but I would like it normalized.

Most of us change phones and simply throw away the data we didn't think was important. Once I noticed this problem I started grieving for all the information I lost every time I bought a new phone and only backed up pictures when I could have a very clear historical timeline of my physical activity. And it's not just health data, but music, books, screentime, coding stats, etc. This matters to me more than I was expecting.

So I came across the quantifiedself community on reddit and let's just say the rest is history. I've spent weeks trying to bring my data from all my sources into one hub, which led me to finally learning SQL properly, to learning about different databases I hadn't heard of, to trying out different visualization apps. All of this hoping to figure out whether there's a correlation between screentime, food intake, exercise, sleep and the chronic pain condition I've been trying to get diagnosed for almost 20 years. I think I'm finally onto something.

So far I've tried the following

  • Using Apple Health as a wearables hub, then syncing to Guava for lab reports and symptoms. this provides no integration for other types of data and ignores my previous wearables' raw data because AH can't pull it.
  • Parsing the GDPR exports for my Xiaomi Fit band myself and then adding to AH. Xiaomi exports are notoriously difficult to use.
  • Using DuckDB as a final database source. This became more promising, as DuckDB is perfect for taking data from different sources and pulling it into one final database.
  • Asking Gemini to parse the Fit data. Gemini isn't very smart but it did successfully help me turn it into a DuckDB table! We're getting somewhere.
  • Visualizing it through Grafana. This was the first time I actually felt like I was accomplishing something because I could visualize the data I had pulled with DuckDB. I don't like Grafana though and would rather code the logic and graphics myself.
  • Using Datasette with Dogsheep. I'm not too sure what I hoped to achieve with this, but I'm in the process of pulling my deleted Twitter account data through WaybackTweets which I found through a Dogsheep-related site.
  • Using Evidence.dev for visualization. This is what I've settled for. I'm an Obsidian lover, so the idea of using Markdown to design the dashboard was immediately appealing to me. Furthermore, I think my favorite part about it is how scalable the system is. I can have different connectors from different data sources, use source queries to organize my data and then graph it with ECharts which gives me full control over it. And it uses my beloved DuckDB dialect!

I also realized I've been doing everything wrong. I spent weeks trying to convert my data into the format I wanted, molding my final structure around the data I have instead of doing the opposite. I read an O'Reilly book about data engineering a few years ago and it's been in the back of my mind ever since. Isn't it better to create an ideal schema first and then convert your data to fit that? So this is what I'm doing from now on, and I'll be documenting it here to keep myself accountable!

Creating a plan

Before thinking about how I'll be pulling and transforming data, I'd like to take a data-engineering approach. First I'll be designing the system I want to store my data, which means I have to choose what data is important enough to visualize. To keep things simple, I'll only be selecting very basic information to work with for now. But the good thing about this approach is that we can always add more data later. I have my data sources, but I'll be designing the structure first, including creating the charts I'll use to visualize later. This means I'll just generate some mock data to graph, and once I have a solid schema, I'll bring in my own data and figure out how to transform each data source.

Health & Fitness

Sources I'm pulling from: Guava, Apple Health, MiFit, Honor Health, DailyNomie (for symptom tracking & sleep quality perception)

What I'll be working with:

  • Heart rate (raw)
  • Steps (raw)
  • Calories burned (raw)
  • Sleep data (sleep stages, sleep totals, perceived quality)
  • Lab results
  • Mood (from nomie)
  • Exercise minutes
  • Times stood

Nutrition

Sources I'm pulling from: Cronometer, Google Health, personal food log I plan on feeding to NotebookLM with the USDA food list to pull nutrient info.

What I'll be working with:

  • Name of meal
  • Macros per meal (Calories, fat, protein, carbs)

Media consumption

Sources I'm pulling from: Spotify, Goodreads, Letterboxd, RateYourMusic

What I'll be working with:

  • Name of book/song/movie
  • Times listened (for songs)
  • Most read/listened/watched author/artist/director etc.

Time tracking / Productivity

Sources I'm pulling from: Toggl Track, Google Sheets, Google Calendar/Apple Calendar

What I'll be working with:

  • Date & Time of event
  • What I did/What happened
  • Category
  • Who I did it with

Study

Sources I'm pulling from: Canvas, Google Sheets

What I'll be working with:

  • Courses/deep dives I've done
  • Grades
  • Subjects

Weather

Sources I'm pulling from: Have not decided yet

What I'll be working with:

  • Weather details for a particular time/date selected

Tables!

The next step is to design the tables we'll be working with. Since I want to start small, I think I'll start working with the Apple Activity trio: calories burned, exercise minutes and times stood. Instead of pulling my data and working with it, I'll design what the table should look like. This is a very important step, because it ensures that once we bring over our own data, we transform the data to work with OUR system, instead of the other way around. I want to keep things simple but still retain granularity.

Important things to decide here are what time format we'll use, as this must be consistent across all data. Furthermore, do we want to have three tables, one for calories, one for exercise and one for times stood, or one table that fits all of this data? We must keep scalability in mind. Once we bring other factors in such as steps or mood, will we keep it in the same table or create a different one?

Whatever you choose for this, I think one of the best things about Evidence.dev is that instead of having to worry about how to store data, you can simply design tables to get the data you want. For this reason, I think it'd be more efficient to have separate tables for each value, because although this would usually be extremely inefficient, who wants to match a giant table against itself? Evidence.dev treats queries as tables, so we can separate queries per data type to graph data more efficiently.

Hence, for this I'll make three queries with the following fields:

  • calories.sql: (log_date TIMESTAMP, value NUMERIC, unit STRING 'kcal')
  • exercise.sql: (log_date TIMESTAMP, value NUMERIC, unit STRING 'min')
  • stood.sql: (log_date TIMESTAMP, value NUERIC, unit STRING 'stood')

We don't need to think about how to store the data yet.

I asked Gemini to generate some data for us to test:

log_date,metric_type,value,unit
2026-07-20 00:15:00,calories,1.2,kcal
2026-07-20 06:30:00,calories,1.5,kcal
2026-07-20 06:32:00,stood,1.0,stood
2026-07-20 07:15:00,stood,1.0,stood
2026-07-20 08:00:00,calories,12.4,kcal
2026-07-20 08:00:00,exercise,1.0,min
2026-07-20 08:00:00,stood,1.0,stood
2026-07-20 08:01:00,calories,15.1,kcal
2026-07-20 08:01:00,exercise,1.0,min
2026-07-20 08:02:00,calories,14.8,kcal
2026-07-20 08:02:00,exercise,1.0,min
2026-07-20 08:03:00,calories,11.2,kcal
2026-07-20 08:03:00,exercise,1.0,min
2026-07-20 09:05:00,stood,1.0,stood
2026-07-20 10:12:00,stood,1.0,stood
2026-07-20 11:00:00,stood,1.0,stood
2026-07-20 12:30:00,stood,1.0,stood
2026-07-20 12:45:00,calories,2.1,kcal
2026-07-20 14:45:00,stood,1.0,stood
2026-07-20 16:20:00,stood,1.0,stood
2026-07-20 17:05:00,stood,1.0,stood
2026-07-20 18:30:00,calories,8.5,kcal
2026-07-20 18:30:00,exercise,1.0,min
2026-07-20 18:30:00,stood,1.0,stood
2026-07-20 18:31:00,calories,9.1,kcal
2026-07-20 18:31:00,exercise,1.0,min
2026-07-20 19:15:00,stood,1.0,stood
2026-07-20 22:10:00,calories,1.3,kcal
Enter fullscreen mode Exit fullscreen mode

I created a new data connector in my project called mock_csv. Then I added the data in the source folder.

Create the following files in a new /queries folder:

calories.sql

SELECT
    log_date::TIMESTAMP as log_date,
    value::DOUBLE as calories_burned,
    unit as unit
FROM mock_csv.mock_activity_data
WHERE metric_type = 'calories'
Enter fullscreen mode Exit fullscreen mode

exercise.sql

SELECT
    log_date::TIMESTAMP as log_date,
    value::DOUBLE as exercise_min,
    unit as unit
FROM mock_csv.mock_activity_data
WHERE metric_type = 'exercise'
Enter fullscreen mode Exit fullscreen mode

stood.sql

SELECT
    log_date::TIMESTAMP as log_date,
    value::DOUBLE as stood,
    unit as unit
FROM mock_csv.mock_activity_data
WHERE metric_type = 'stood'
Enter fullscreen mode Exit fullscreen mode

And a page on /pages:

---
title: "testing"
queries:
    - exercise: ../../../queries/exercise.sql
    - calories: ../../../queries/calories.sql
    - stood: ../../../queries/stood.sql
---

\`\`\`sql total_exercise
SELECT 
    date_trunc('day', log_date) as day,
    SUM(exercise_min) as total_min 
FROM ${exercise}
GROUP BY 1
ORDER BY 1;
\`\`\`

\`\`\`sql total_calories
SELECT 
    date_trunc('day', log_date) as day,
    SUM(calories_burned) as total_calories_burned
FROM ${calories}
GROUP BY 1
ORDER BY 1;
\`\`\`

\`\`\`sql total_times_stood
SELECT 
    date_trunc('day', log_date) as day,
    SUM(stood) as total_times_stood
FROM ${stood}
GROUP BY 1
ORDER BY 1;
\`\`\`
Enter fullscreen mode Exit fullscreen mode

(Remove the backslashes)

This should load tables with the averages per day. To visualize them in a line chart, we should have one table with the averages per day. Add this to the end of the page:

\`\`\`sql daily_summary
SELECT * 
FROM ${total_exercise}
JOIN ${total_calories} USING (day)
JOIN ${total_times_stood} USING (day)
\`\`\`
<LineChart 
    data={daily_summary}
    x=day
    y={['total_exercise_min', 'total_times_stood']}
    y2=total_calories_burned
    yAxisTitle="Stats"
/>
Enter fullscreen mode Exit fullscreen mode

This should give you a line chart showing the stats for calories burned, times stood and total exercise in minutes!

Next post, I'll talk about other charts for visualizing other types of information and begin designing the pipeline to get Apple Health data into the dashboard.

Here's the github for the project: https://github.com/undersilentskies/quant-self-dashboard

Happy coding!

Top comments (0)