Being able to cache tasks from Todoist in a database will allow us to do more complex things with them (reports, further automations, etc). This post will cover how to set up the relevant tables and how to cache tasks from Todoist in it.

Tools Used

• n8n - Workflow automation tool
• Todoist - Task management application
• PostgreSQL - Our datastore

Note: Can be adapted to other tools

Setting up Todoist application and creating a credential in n8n

Go to the App Console and create a new app. You need to give it a name and service URL.

Once it is created, you should create a new credential in n8n of Todoist OAuth2 API, copy the OAuth Redirect URL from the n8n credential and put it in the new application in todoist and save.

You can then copy the client id and client secret from the todoist application into the n8n credential and and click Connect My Account, you will be redirected to todoist to login and authorize the application. Once you have authorized the application, you can click Save and the credential will be ready to use in a workflow.

Caching todo tasks

Keep a local copy of tasks in order to query things to reduce the number of queries to todoist to retrieve information. You need to determine what pieces of information you wish to cache. The fields that I have chosen to cache are:

• task_id
• content
• description
• added
• completed_at
• due
• labels
• checked
• url
• parent_id

The database can be created with the following sql:

CREATE TABLE todoist_tasks (
    task_id bigint NOT NULL,
    content text,
    description text,
    added timestamp with time zone,
    completed_at timestamp with time zone,
    due timestamp with time zone,
    labels character varying(1000)[],
    checked boolean,
    url text,
    parent_id bigint
);

Once the database is created, create a new workflow in n8n and add a manual trigger (for now)

.

Then add a Todoist - Get many tasks node

Then add a Code node to format the data into the format we want to cache, select Run Once for All Items and add the following code:

const items = [];

for (const item of $input.all()) {
  const labels = item.json.labels.join(",") || "";
  const vars = {json: {
    id: item.json.id,
    content: item.json.content,
    description: item.json.description,
    added: item.json.created_at,
    due: item.json.due,
    completed_at: item.json.completed_at,
    checked: item.json.is_completed,
    labels: `{${labels}}`,
    url: item.json.url,
    parent: item.json.parent_id
  }};
  items.push(vars)
}
return items;

Then add a Postgres - Execute a SQL Query node

We will be doing an upsert query, so set the query to the following:

INSERT INTO todoist_tasks (
  task_id,
  content,
  description,
  added,
  completed_at,
  labels,
  checked,
  url,
  parent_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
ON CONFLICT (task_id)  do update SET content = EXCLUDED.content, description = EXCLUDED.description, added = EXCLUDED.added, completed_at = EXCLUDED.completed_at, parent_id = EXCLUDED.parent_id, labels = EXCLUDED.labels;

and then select "Add field" under "Additional fields and select "Query Parameters"

and set it to id, content, description, added, completed_at, labels, checked,url, parent.

Execute the workflow and ensure there are no errors.

You can then add an Schedule Trigger node to run the workflow on a schedule. I have mine run 2 times a day to pickup any changes that may have been missed by the webhook receiver. You will need to enable the workflow for the interval node to work. You can set it up as follows:

Trigger Rules:

• Trigger Interval: Hours
• Hours Between Trigger: 12
• Trigger at Minute: 0

Setting up webhook receiver in n8n transfer into the database

Create a new workflow in n8n and add a webhook trigger node and set the HTTP method to POST and Respond to immediately. Add a Code node to format the data into the same format as above and add the following code:

const labels = $input.item.json.body.event_data.labels.join(",") || "";
const vars = {json: {
  id: $input.item.json.body.event_data.id,
  content: $input.item.json.body.event_data.content,
  description: $input.item.json.body.event_data.description,
  added: $input.item.json.body.event_data.added_at,
  due: $input.item.json.body.event_data.due,
  completed_at: $input.item.json.body.event_data.completed_at,
  checked: $input.item.json.body.event_data.checked,
  labels: `{${labels}}`,
  url: $input.item.json.body.event_data.url,
  parent: $input.item.json.body.event_data.parent_id,
  delete: $input.item.json.body.event_name == "item:deleted"
}};

return vars;

Then I added an IF node to check if the task was deleted.

Then add two Postgres - Execute a SQL Query nodes, one for insert/update from the true and another for delete from the false.

For the update/insert node, set the query to the following:

INSERT INTO todoist_tasks (
  task_id,
  content,
  description,
  added,
  completed_at,
  labels,
  checked,
  url,
  parent_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
ON CONFLICT (task_id)  do update SET content = EXCLUDED.content, description = EXCLUDED.description, added = EXCLUDED.added, completed_at = EXCLUDED.completed_at, parent_id = EXCLUDED.parent_id, labels = EXCLUDED.labels;

and Query Parameters to id, content, description, added, completed_at, labels, checked, url, parent

For the delete node, set the query to the following:

DELETE FROM todoist_tasks WHERE task_id = $1

and Query Parameters to id.

Setting up webhook in Todoist

Open the Webhook trigger node and copy the Test URL. Go to the App Console and set the webhook URL to the copied URL on your application and select the following events:

• item:added
• item:updated
• item:deleted
• item:completed
• item:uncompleted

You can then go back to n8n and click the Execute Workflow button to test the webhook. Once it is waiting, make a change or add an item to your Todoist. You should see the data come through and be inserted into the database.

After you have confirmed that the workflow is working, you need to copy the Production URL from the webhook trigger node and paste it into the application console. You will also need to enable the workflow in n8n for the webhook to work.

The next post in the series will go over creating a reusable workflow for creating tasks in Todoist.