Building CLI time tracker with Python
Tags: python, programming, opensource
Published: 2022-10-19 14:49:58

###So, why another time tracker?
There are plenty of time tracking apps and services, however it doesn't mean there is no room for a new app in this niche. Like there are established clothing brands with mass production capabilities doesn't eliminate custom made clothing brands. Making time tracker app as a personal project was both a personal challenge and opportunity to create an app that is tailored to specific time management techniques like a tailor can make a suit that's made by your specific requirements. 

However custom made doesn't mean "only for yourself". Like they say "_If you had an idea it's likely someone came up with the same idea before_". Same situation here  —  if you developed particular way for doing things might mean that you simply discovered an approach that has been adopted by some people for a long time. It might be a niche approach but nevertheless your are not likely to be alone. This way of thinking was encouraging for me since it's more motivating when you build something that can be useful not only for yourself but for other people as well.

My first approach to time tracking was quite straightforward: jot down on a piece of paper or a sticker when task started, then put finish time and a comment what task was about. Initially time tracking was purely manual.

Though I enjoy taking notes manually and handwriting in general (especially with a good fountain pen) it's hard to do much with these data after it was written. Next step was tracking time in digital form but still manually using sticky notes app:

![Digital time tracking](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/11rrta8ahv8k45u6mubv.png)

Looks neater and now it's possible to do something with these data. So idea to write a script that going to show amount of time spent on a specific task by supplying time slots as parameter was originated.

To automate time spent on each activity a [ruby script](https://github.com/dmikhr/time_management) was written that took a string with time ranges when task was active:

`ruby st.rb "10:30 - 11:25 11:45 - 12:30"`

Now it's possible to do something with data about time spent on different tasks, but it was still a semi-manual approach. Track time manually, then pass it in a specific format to the script didn't feel convenient and was more like a compromise. So the idea to build an app to track time was born.

###Prototyping phase
Having all these in place the question was what features should be included in the app. The first obvious step was to get rid of manually typing time ranges, so the idea was that user evoke the app with a specific flag and task name that indicates whether task is active or finished, while app tracks time itself.

There were lots of ideas what features such application might have. But first thing first: before building full featured app let's make a prototype, a proof of concept. Then it's going to be clear whether it's gonna stick  —  am I going to use it consistently? If app proves to be handy further development efforts are justified.

Overall the following minimal functionality must be in an app:
* add new task
* remove task
* start tracking time for a given task
* finish time tracking

Also feature that shows time stats (how much time has been spent on each task during the day) was added.
The idea was to build a console app. CLI apps have something in common with writing on paper - it's basic, nothing extra, but this is an appeal for both. Also doing software development projects made me get used to command line interfaces, so CLI app was a no brainer option.

Further app architecture was built in the same manner - identify app component and separate them:
* console interface
* app logic (time tracking, statistics)
* database management (creating database, add, remove data)

###Console interface
Console interface was built with **argparse**. However it worth noting that alternatives were considered and one that stands out is **click**. This module takes a different approach to building command line interfaces - instead of building methods for processing incoming arguments it offers a way to build CLI interface by decorating functions that will be evoked by a given flag ([code example](https://palletsprojects.com/p/click/)). While **click** seems like a promising library, for initial app version I decided to stay with tried and tested argparse. However in the future when CLI interface will comprise more features decorator approach that is used by click seems to be more scalable.

The following commands were included in the initial app version:

```
options:
-h, - help show this help message and exit
-s START, - start START
Start task by providing its name
-f, - finish Finish current task. Task also can be finished by
starting a different task
-a ADD, - add ADD Add new task
-r REMOVE, - remove REMOVE Remove the task
-l, - list List of all tasks
-st, - stats Stats about tasks
```

###Database
With database design  —  let's keep it simple, one table for storing tasks and another for time tracking. Each working session spent on a specific task will be stored as a record in `work_blocks` table. Each task may have any number of work blocks, including none if task hasn't been tracked yet. ER-diagram of the app database is presented on the following picture:

![diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nof2i2cv4v0a2nmvetjl.png)

As a database engine **SQLite** has been chosen — lightweight, data is stored in a single file, overall good option for a first version of an app. For database management an **SQLAlchemy ORM** was chosen to separate data manipulation from specific database implementation. Since it's a utility with local database and large amounts of data are not expected, so moving to distributed databases was not in a plan, auto incremented id was chosen for primary keys instead of [uuid](https://blog.boot.dev/clean-code/what-are-uuids-and-should-you-use-them/).

**Storing timestamps**
One issue with SQLite — it doesn't have `datetime` data type and this is a time tracking app! The solution was to store time as a Unix timestamp using `Integer` data type. This approach is recommended by SQLite documentation, since it can support storing numbers in up to 8 bytes which corresponds to `bigint` in database engines such as PostgreSQL. Storing time in Unix timestamp might bring some inconveniences down the road with features like obtaining daily stats, however it doesn't seem like a real roadblock for further development.

**Database location and schema**
To store database user files directory was used. The issue here is that each OS has it's own path for this directory. But there is no problem, Python is known for having libraries almost for everything and identifying user directory is no exception: for this job [appdirs](https://github.com/ActiveState/appdirs) has been chosen. When app starts for the first time it will check whether database exists and if no database file was found it will create an empty database with the following schema:

```python
class Task(Base):
   __tablename__ = 'tasks'

   id = Column(Integer, primary_key=True)
   name = Column(String)
   description = Column(String)
   children = relationship("WorkBlock",
                           cascade="all,delete",
                           backref="tasks")

class WorkBlock(Base):
   __tablename__ = 'work_blocks'

   id = Column(Integer, primary_key=True)
   task_id = Column(Integer, ForeignKey("tasks.id"))
   start_time = Column(Integer)
   finish_time = Column(Integer)
```

###App logic
App logic consists of methods that evoked through CLI interface and interact with database as well as a collection of private methods that serve auxiliary purpose.

Start task - app creates a record in `work_blocks` table related to a given task that is stored in tasks table with a unix timestamp of a current time in `start_time` field while `finish_time` field remains empty.

**Finish task**  —  no arguments are required for this options. If there is an active task (`finish_time==NULL`) current time in Unix timestamp format will be recorded in the finish_time time, thus session for a current task will be ended.

**Add new task**  —  in order to track tasks there should be a way to store them. This options requires task name as input and record data in tasks table.

**Remove task**  —  if task is no more needed it can be removed. In the current version removing the task from tasks table triggers 'delete on cascade' and all corresponding work_block will be removed as well. In the future versions of an app an additional option might be added that's going to archive tasks instead of complete deletion. The motivation behind this is that even if task is no more needed, storing data about it can be useful for further data analysis. For example for understanding performance bottlenecks that can occur while working on similar tasks.

**List all tasks**  —  show all tasks that has been added to an app. If one of these tasks is active there will be (in progress) status near it. For example:

```
Project_work
Exercising (in progress)
Flask_API_project
Some_task
```

**Statistics**  —  shows only tasks that were active today. For each task time spent on a it is shown in two formats: hr:min and in decimal format. For example: 1:20 (1.33). The reason behind presenting time in decimal format is that it can be useful if you track time in spreadsheets and it's easier to use time in such format for further analysis like counting total time spent on a task during the last week, month, etc. I've been using this approach for identifying issues with prioritisation - track time on each task/project and at the end of the week see how much time was spend on each. If there is an outlier (task that consumed way more time that others) and there is no clear reason why it has happened (close deadline, high importance of the task) that it's the reason to change priorities and dedicate more time to other responsibilities.

###Testing
Testing was implemented using PyTest framework. Test data was generated with Faker. In order to isolate test data for each test fixtures were used. Fixtures are convenient way to generate test data and ensure its consistency among different tests. If you're not familiar with them you may check [this example](https://docs.pytest.org/en/7.1.x/how-to/fixtures.html) to get an idea how it works.

Since application features mostly use database it was important to use a test database. In the simplest case requests to database can be stubbed, but then most test won't be useful in this particular case. 

First idea was to create temporary SQLite database file each time tests run and delete it after they're finished, however this approach didn't feel right. Thankfully it's possible to create SQLite database in memory. This approach doesn't require any read/write operations with file system, no need to think about deleting database file after testing. In case of in-memory database memory will be cleared automatically after connection is closed.

While choosing in-memory database for testing it's important to consider how much test data is planned to put into database. In this case it was about dozens of records at most, so exceeding memory limit was not an issue.
To establish connection and create in-memory sqlite database:

```python
from sqlalchemy import create_engine
engine = create_engine(f"sqlite+pysqlite:///:memory:")
```

Testing approach was to cover with tests all public methods. Mostly I tried to follow [TDD approach](https://testdriven.io/test-driven-development/) by writing tests first and then implementing a feature, followed by several iterations of test and implementation improvements and code refactoring.

###Giving a proper name to project
When you're working on a personal project it might seem that project name is not a big deal. However giving a project a proper name achieves at least two important goals. First, when personal project has a name it feels subconsciously that I began to take it more seriously, not just an exercise for improving programming skills but more like I'm building a tool or a product that might be useful not only for personal purpose but for other people too. Secondly, if you decide to share your project with community via repository such as _pypi_ or _npm_ you will need a unique name for a project. In case of this project I gave it a name **tiempo-tracker** (tiempo means time in Spanish).


###Conclusions
So far I found myself using this app consistently during my work process and new ideas were born on how to improve an app and what features might be useful to add. However it's important not to be overwhelmed with implementing all ideas at once but instead keeping track of them and planning on implementing them when they're truly needed.

Pet-projects is something that can be useful and educational. Using a self-developed app tailored to your own requirements is an interesting experience, however personal projects take time and the more features app has the more maintenance it requires: more tests, bug-fixes. It's important not to be overly consumed in this activity since a lot of other things requires our attention both professional responsibilities and personal. However it's something that easier to be said than done especially when you're passionate about your project.

A short demo is available on [Asciinema](https://asciinema.org/a/7RFZiBHamLTBqOuwvsIYyp71Z)

You can check the source code of [tiempo-tracker on Github](https://github.com/dmikhr/tiempo-tracker).