Building a Chrome Extension to highlight my Things tasks

I've been really into doing live-coding threads on Twitter and turning them into articles lately. After doing it two days in a row, building VAT handling into Sitesauce first and a referral system the day after, I figured out I'd continue and do it for the entire week.

Instead of continuing to work on Sitesauce though, I decided to build something I had been wanting to build for a while: a chrome extension to showcase my current task on each tab I open.

This may sound simple, but the software I use to log my tasks, Things, is notoriously difficult to integrate with. They don't provide any kind of API for pulling data out except for some AppleScript macros (which we can't execute from our Chrome extension). I also couldn't find any documentation online on how to do this, so I had to get creative

Part 1: The Backend

Things is a macOS app. Tasks need to be stored somewhere, and even though it syncs with iCloud (and their proprietary cloud service), the app also works offline, which means they needs to store data somewhere, even temporally. Indeed, after some digging through my filesystem, I found a sqlite database located at ~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application Support/Cultured Code/Things/Things.sqlite3. Let's open it up and see if we can use it as a data source.

Here's the structure of the TMTask table, which seems to contain our tasks (along with Projects for some reason).

CREATE TABLE 'TMTask' (
    'uuid', 'userModificationDate', 'creationDate', 'trashed', 'type', 'title', 'notes',
    'dueDate', 'dueDateOffset', 'status', 'stopDate', 'start', 'startDate', 'index',
    'todayIndex', 'area', 'project', 'repeatingTemplate', 'delegate', 'recurrenceRule',
    'instanceCreationStartDate', 'instanceCreationPaused', 'instanceCreationCount',
    'afterCompletionReferenceDate', 'actionGroup', 'untrashedLeafActionsCount',
    'openUntrashedLeafActionsCount', 'checklistItemsCount', 'openChecklistItemsCount',
    'startBucket', 'alarmTimeOffset', 'lastAlarmInteractionDate', 'leavesTombstone',
    'todayIndexReferenceDate', 'nextInstanceStartDate', 'dueDateSupressionDate'
);

It's a lot of data, but after playing around with the data, I managed to craft a query that gets all the items in our Today list. The status field represents the status of the task (0 seems to stand for unfinished task) and getting all columns where startDate is set does the trick!

select * from "TMTask" where "status" = "0" and "startDate" IS NOT NULL;

Now that we've got the query we want to run, we need a server that can execute it when we request it from our extension. I would normally create a new Laravel app, but it seems overkill for a single endpoint which makes a bunch of database calls.

Instead, we can use Laravel's amazing query builder on regular PHP apps by installing it with composer. We can get the required boilerplate fromMatt Stauffer's Torch project, which provides examples of Illuminate components outside of Laravel. According to their database example, we need to create a new instance of the query builder and connect it to Things' database. After that, we can use DB:: methods as we're used to.

<?php

require_once 'vendor/autoload.php'

use Illuminate\Database\Capsule\Manager as DB;

$connectionManager = new DB;

$connectionManager->addConnection([
    'driver' => 'sqlite',
    'database' => '~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application Support/Cultured Code/Things/Things.sqlite3',
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
]);

$connectionManager->setAsGlobal();

Let's reconstruct our query on the Query Builder, and see what we get.

<?php

require_once 'vendor/autoload.php'

use Illuminate\Database\Capsule\Manager as DB;

// connection is added here, removed for brevity

$tasks = DB::table('TMTask')
    ->where('status', 0)
    ->whereNotNull('startDate')
    ->orderByDesc('todayIndex')
    ->get();

var_dump($tasks);
exit();

/* Here's the output */
object(Illuminate\Support\Collection)#1058 (1) {
    ["items":protected]=> array(1) {
        [0]=> object(stdClass)#1056 (36) {
            ["uuid"]=> string(36) "5E966A73-19D1-4AA9-B4B9-A2C5541AB021"
            ["userModificationDate"]=> string(16) "1589845969.23817"
            ["creationDate"]=> string(16) "1589447418.40047"
            ["trashed"]=> string(1) "0"
            ["type"]=> string(1) "0"
            ["title"]=> string(29) "Write article about extension"
            ["notes"]=> string(0) ""
            ["dueDate"]=> NULL
            ["dueDateOffset"]=> string(1) "0"
            ["status"]=> string(1) "0"
            ["stopDate"]=> NULL
            ["start"]=> string(1) "1"
            ["startDate"]=> string(12) "1589414400.0"
            ["index"]=> string(4) "-343"
            ["todayIndex"]=> string(3) "334"
            ["area"]=> NULL
            ["project"]=> NULL
            ["repeatingTemplate"]=> NULL
            ["delegate"]=> NULL
            ["recurrenceRule"]=> NULL
            ["instanceCreationStartDate"]=> NULL
            ["instanceCreationPaused"]=> string(1) "0"
            ["instanceCreationCount"]=> string(1) "0"
            ["afterCompletionReferenceDate"]=> NULL
            ["actionGroup"]=> NULL
            ["untrashedLeafActionsCount"]=> string(2) "-1"
            ["openUntrashedLeafActionsCount"]=> string(2) "-1"
            ["checklistItemsCount"]=> string(1) "0"
            ["openChecklistItemsCount"]=> string(1) "0"
            ["startBucket"]=> string(1) "0"
            ["alarmTimeOffset"]=> NULL
            ["lastAlarmInteractionDate"]=> NULL
            ["todayIndexReferenceDate"]=> string(12) "1589414400.0"
            ["nextInstanceStartDate"]=> string(14) "-62135769600.0"
            ["dueDateSuppressionDate"]=> NULL
            ["leavesTombstone"]=> string(1) "0"
        }
    }
}

That's a lot of data we don't need (and some I don't even know what it stands for). Let's make sure we're only asking for what we need.

<?php

require_once 'vendor/autoload.php'

use Illuminate\Database\Capsule\Manager as DB;

// connection is added here, removed for brevity

$tasks = DB::table('TMTask')
    ->select('title', 'notes')
    ->where('status', 0)
    ->whereNotNull('startDate')
    ->orderByDesc('todayIndex')
    ->get()
    ->map(fn ($task) => ['title' => $task->title, 'description' => $task->notes])
    ->reverse()
    ->values()
    ->toArray();

var_dump($tasks);
exit();

/* Here's what we get now: */
object(Illuminate\Support\Collection)#1061 (1) {
    ["items":protected]=> array(4) {
        [0]=> array(2) {
            ["title"]=> string(29) "Sixth module of the Go course"
            ["description"]=> string(66) "https://www.educative.io/courses/introduction-to-programming-in-go"
        }
        [1]=> array(2) {
            ["title"]=> string(18) "Write Vercel Guide"
            ["description"]=> NULL
        }
        [2]=> array(2) {
            ["title"]=> string(41) "A new-tab extension for your Things tasks"
            ["description"]=> string(0) ""
        }
        [3]=> array(2) {
          ["title"]=> string(54) "Get Vercel login working on static site with functions"
          ["description"]=> string(0) ""
        }
    }
}

That's much better. Let's now turn our script into an API with the oldest method known to the PHP developer, echo.

<?php

require_once 'vendor/autoload.php'

use Illuminate\Database\Capsule\Manager as DB;

// connection is added here, removed for brevity

header('content-type: application/json');

echo DB::table('TMTask')
    ->select('title', 'notes')
    ->where('status', 0)
    ->whereNotNull('startDate')
    ->orderByDesc('todayIndex')
    ->get()
    ->map(fn ($task) => ['title' => $task->title, 'description' => $task->notes])
    ->reverse()
    ->values()
    ->toJson();

One Things feature I use a lot is tagging, and it'd be cool to show the tasks attached to our task on our extension. After a few failed tries, I managed to get everything working using an additional query to the pivot table.

<?php

require_once 'vendor/autoload.php'

use Illuminate\Database\Capsule\Manager as DB;

// connection is added here, removed for brevity

$tasks = DB::table('TMTask')
    ->select('uuid', 'title', 'notes')
    ->where('status', 0)
    ->whereNotNull('startDate')
    ->orderByDesc('todayIndex')
    ->get();

$relationship = DB::table('TMTaskTag')
    ->select('tasks as taskId', 'title as tag')
    ->whereIn('tasks', $tasks->map->uuid)
    ->join('TMTag', 'TMTag.uuid', 'TMTaskTag.tags')
    ->get();

header('content-type: application/json');

echo $tasks->map(fn ($task) => [
    'id' => $task->uuid,
    'title' => $task->title,
    'description' => $task->notes,
    'tags' => $relationship->where('taskId', $task->uuid)->map(fn ($rel) => $rel->tag),
])->reverse()->values()->toJson();

And, after a few hours of digging through Things' database and playing around with queries, we now have our API ready to be consumed by the extension. Let's work on that next!

Building a Chrome Extension to highlight my Things tasks — A screenshot of the Insomnia HTTP client showing the results of an example API call

Part 2: The Extension

The first thing we need to create is our manifest file, which will tell Chrome (or Brave in my case) what our extension does. For our purpose, we can use the chrome_url_overrides property to have our HTML file load each time we open a new tab.

{
    "name": "Things New Tab",
    "version": "1.0",
    "description": "A new tab extension for personal use.",
    "chrome_url_overrides": {
        "newtab": "newTab/index.html"
    },
    "manifest_version": 2,
    "icons": {
        "16": "icon.png",
        "128": "icon.png"
    }
}

Before we start working on the frontend though, we need to load the extension into Brave. We can do this by going to brave://extensions and loading the extension folder with the Load Unpacked option.

Using Tailwind and Vue, I got a basic version to pull my current task from our backend and displaying it along with the tag and optionally the description. Here's a timelapse of the process.

Once I got the basic functionality working (and after taking a break), I started working in making it look good. Here's another timelapse of the process.

Here's the design I ended up going with. It uses multiple shadows to give the illusion of stacked layers, which gives a fun look. The background was taken from this awesome collection of SVG patterns. Really like how it ended up looking.

After digging a bit more into the Things documentation, I found their URL protocol, which allows you to visit specially-crafted things:// links to perform actions, like editing tasks. With this functionality, I made it so you could mark your current task as completed right from your browser. Here's how that looks.

I've extracted all required configuration (a URL to the backend and the Things token, required for marking a task as complete) to an .env file and open-sourced the extension and the server in case someone else finds it useful.

As with the two others, this article started as a Twitter thread, you may want to follow me there for more live-coding threads. If you'd like to see me do something like this in video format, you can subscribe to my YouTube channel, where I'm planning to produce some video content this summer. Have a great day!

Blog

Building a Chrome Extension to highlight my Things tasks

Miguel Piedrafita

Part 1: The Backend

Part 2: The Extension

Join Our Newsletter. No Spam, Only the good stuff.

Related