Summary
This is a record of a desktop tool I built myself that turns the time spent working and the time spent resting in development with AI into numbers. I call this tool the visualization tool. The visualization tool reads the records of dialogues with AI and the GitHub commit history, and saves daily values in the daily table. Under the default settings, the dialogue records are deleted, oldest first, after about 30 days. When an ingestion runs after part of the records has been deleted, the visualization tool can calculate a value smaller than the saved value.
The visualization tool counts time in units of intervals that divide the clock into 5-minute segments. I call these intervals time slots. The visualization tool uses time slots only inside the ingestion process and does not save them. Activity time is the time that the visualization tool counts as time in which a person and AI are working together. A dialogue session that the visualization tool classifies as started by an automated process, such as a scheduled job, is called an automated session. Automated time is the part of the time of automated sessions that does not overlap with activity time.
On 2026-08-03, the AI agent that fixed the daily table corrected the overwrite check of the daily table twice. The overwrite check is the condition that decides whether to overwrite the saved value with the value from the current ingestion. In the version just before the first fix, whether to overwrite automated time was also decided by the comparison of activity time. This comparison checks whether the current activity time is greater than or equal to the saved activity time. On a day with no human activity, when only automated sessions ran, the current activity time and the saved activity time are both 0. Because 0 is greater than or equal to 0, the comparison always passed. According to the commit record of the first fix, the saved automated time was being overwritten with smaller values calculated in incomplete ingestions. The records do not show on which days or how many times the overwrite happened with real data. In the first fix, the AI agent that fixed the daily table changed the automated time column and the human message time column so that each is compared with its own saved value. In the second defect, because each column was compared with its own saved value, values calculated in separate ingestions were mixed in one row. The second fix came 11 minutes after the first fix.
The final version compares the sum of activity time and automated time only once per row. Only when the current sum is greater than or equal to the saved sum does it overwrite all the columns calculated from time slots together. The count columns are overwritten every time. Even the final version cannot distinguish between a case where records from an ingestion source are missing and a case where the classification of a dialogue session has changed. The specification document lists three approximations that remain in this check. An approximation is a situation in which the check is known to leave incorrect values, and which was written into the specification and accepted.
In the body, I first explain why a check for overwriting saved values is needed and what the final form of the check is. Next, I compare in tables the five versions of the overwrite check and the three candidates not adopted in adversarial reviews. Then I show, with a code example that was run, how to write an SQLite UPSERT that overwrites only the count columns every time. On that basis, I explain the first defect, in which automated time decreased on days with only automated sessions. I then explain the second defect, in which values from separate ingestions were mixed in one row. After that, I explain the three approximations that remain in a design that does not save times of day. Finally, I write about tests that fail on the implementation before the fix and four rules for not losing saved values.
What you can take away
This article is for people building mechanisms that protect saved values in aggregations whose source records are deleted over time. You will learn the following four things.
- The five versions of the check that decides whether a saved value may be overwritten, and the reason the check was changed in each version
- How a check always passes on an input where both sides of the comparison are initial values
- How to write an SQLite UPSERT that keeps saved values in only some of the columns
- The practice of first confirming a test that fails on the implementation before the fix, and the names that external sources give to this practice and to the test
This article is a discussion of design based on the records of developing a tool I built myself.
The rest of this article is paid
You can read the rest by buying this article on its own, or with a subscription that covers every paid article.
The paid part is about 27,400 characters, roughly a 55-minute read.
Read just this article
From 300 JPY
Buy this article on its own. The exact price is shown at checkout. Purchased articles stay readable whenever you sign in with the email used at purchase.
Read every paid article
Standard is 490 JPY / month
Four new paid articles ship every month. For yearly billing and the full comparison, see the plans.
Add dialogue and columns
Premium is 980 JPY / month
Premium adds the subscriber-only columns and dialogue with matsumotory-kun on top of every paid article. See the plans for details.
Prices include tax. Purchases and subscriptions start after you sign in. Subscribers and readers who already bought this article can sign in and read the full article; for the plans, see the plans page.
Sales are available in supported regions only; the Terms list where we sell. All charges are in Japanese yen.