How to store and retrieve data from Slack hosted tables

1Set-up Slack CLI

Use the new Slack CLI to create and deploy Slack apps. With the Slack CLI, developers can create an app and generate some boilerplate code in seconds.

This section walks through setting up your environment for next gen platform development.

  • Install the Slack CLI

    We've built a command line interface to make the process of creating new apps, running them, and deploying them easier. Binaries are available for macOS and Windows.

    Deno is currently a pre-requisite for using the CLI — if you do not have Deno installed, please do so first.

    Additionally, the experience of writing an app will be greatly enhanced if your IDE includes support for Deno. Here's a plugin to add Deno capabilities to VS Code.

    To install the Slack CLI:

    1. Download the latest binary for your platform
    2. Decompress the downloaded file. On macOS, copy the slack binary to a destination in your path (we recommend /usr/local/bin). For Windows, copy the .exe file into any location accessible in your path.
    3. Test the app is properly installed by running slack version and verifying the version matches the one you downloaded.

    Now you can now use the Slack CLI to authenticate and create a new app.

  • Authenticate the Slack CLI

    Before you can create an app, you must authenticate.

    1. Run slack login — this will display some instruction text and a string to copy and paste in any channel in the test workspace.

          /hermesauthticket ABC123DEF...XYZ
      
    2. Copy the entire string, including the leading /, and paste it into any channel in your Slack workspace. (You are essentially calling a special slash command that will authenticate your app with Slack).

    3. Run slack auth info for details on your active workspace authorization.

    Now that you've authenticated, you can create a new app.

  • Create a new app

    Now you can create an app! cd to a directory where you have write access and want your app to live, most likely in your home. Then, run

    $ slack create [optional-project-name]
    

    This will create a new project called with a random name like priceless-lemur-123.

    If you want to name your project something specific, include an additional parameter

    $ slack create my-new-project
    

    Once the app has finished running, you'll have a new directory that contains a skeleton app.

    $ cd my-new-project
    

    The structure of these next-generation apps aims to favor convention over configuration. Directories are named logically and the files within those directories map to specific functionality with an app. For example, the functions directory contains a list of an app's functions, with a single file for each function an app defines.

2Getting Started

  • What to expect

    In this track you will learn how to store and retrieve data from a Slack hosted database using time tracking as a use-case. You will be creating an app that enables users to:

    • log time spent on a project or task AND
    • retrieve all time logged by them

    By the end of this article you will be able to create:

    • a table to store time entries
    • a time entry function that enables users to log time against a project or task
    • a time retrieval function that enable a user to retrieve all time blocks logged by them

3Create Table

Create a table to store time-blocks entered by users.

  • "Timelog" table

    Now that you have created your app, the next step is to create a table to hold the time entries. We will call this table “Timelog” and create it with the following columns:

    Column Description
    id Primary key
    project Name of project or task
    time_duration Amount of time spent on the project or task
    date Date on which time was spent
    user_id User
    created_at Timestamp for row creation
  • Define table

    You will be using Slack's built-in "DefineTable" function to create the "Timelog" table. By convention, tables are declared in the tables directory at the root of a project. You can use a single .ts file to hold multiple table definitions.

    import { DefineTable, Schema } from "slack-cloud-sdk/mod.ts"
    /*  This is the table definition for the table that 
        holds the time entries. Primary key: "id"
     */
    export const Timelog = DefineTable("timelog", {
      primary_key: "id",
      columns: {
        id: {
          type: Schema.types.string,
        },
        project: {
          type: Schema.types.string,
        },
        time_duration: {
          type: Schema.types.string,
        },
        date: {
          type: Schema.types.string,
        },
        user_id: {
          type: Schema.types.string,
        },
        created_at: {
          type: Schema.types.string,
        }
      },
    });
    

4Add Record to Table

Create a function to write a time-block to the table.

  • Create a function to add time entries

    To add a time entry to the table you will need to create a function that takes time logged as inputs from the user. The function will then add these inputs as a new row to the Timelog table. The Timelog table has six columns. Of these:

    • project, time_duration and date are entered by the user when calling this function
    • id is the primary key and you can use any method you like to generate a unique value, such as: const primarykey = crypto.randomUUID();
    • created_at is the timestamp at which the row was created and is set to use Date.now()
    • Note that channel_id and user_id are set to Schema type values from the SDK

    Here is the function to add an entry to the table. The function is defined in its own file in the functions directory ../functions/create_timeblock.ts.

    import { DefineFunction, Schema } from "slack-cloud-sdk/mod.ts";
    import { Timelog } from "../tables/timelog.ts";
    export const LogTime = DefineFunction(
      "create_timeblock",
      {
        title: "Create timeblock",
        description: "Add a new timeblock",
        input_parameters: {
          project: {
            type: Schema.types.string,
            description: "Project or task to log time against",
          },
         time_duration: {
            type: Schema.types.string,
            description: "Time spent on task",
          },
          date: {
            type: Schema.types.string,
            description: "Optional: date, if not provided today's date will be used",
          },
          channel: {
            type: Schema.types.string,
            description: "Channel",
          },
          user_id: {
            type: Schema.slack.types.user_id,
            description: "User Id",
          },
        },
        output_parameters: {
          id: {
            type: Schema.types.string,
            description: "Time block ID",
          },
        },
      },
      //Add time block to timelog table
      async ({ inputs, client, env }) => {
      //Create primary key for table
      const primarykey = crypto.randomUUID();
      if (inputs.date == ""){
        // get today's date in `MM/DD/YYYY` format
        const now = new Date();
        const today = now.toLocaleDateString('en-US');
        inputs.date = today.toString();
      };
      await client.call("apps.hosted.tables.putRow", {
          table: "timelog",
          row: {
            id: primarykey,
            project: inputs.project,
            time_duration: inputs.time_duration,
            date: inputs.date,
            user_id: inputs.user_id,
            created_at: Date.now(),
          },
        });
        // Log message to console
        console.log(`Timelog entry added to table for project:${inputs.project}`);
        // Return unique ID for the row that was added
        return await {
          outputs: { id: primarykey },
        };
      });
    

    Here we're returning the primary key of the time block entered. You could also choose to return a confirmation message to the user for whom the time block was added.

    // Post confirmation message to user
      return await client.call("chat.postEphemeral", {
        text: `Time logged for project:${inputs.project}`,
        channel: inputs.channel,
        user: inputs.user_id,
      });
    });
    

    By convention, functions are declared in the functions directory at the root of a project, with each function declared as its own TypeScript file. In the next step, you will be creating a function to list all time entered by a user.

5Retrieve Records from Table

Create a function to read time-blocks recorded in the table.

  • Create a function to list time entries

    It’s timesheet time and hence time for the user to retrieve all time-blocks logged! You will do so by querying the Timelog table for all entries that match the user_id of the user invoking the function.

    import { DefineFunction, Schema } from "slack-cloud-sdk/mod.ts";
    import { Timelog } from "../tables/timelog.ts";
    export const GetTimelog = DefineFunction(
      "get_timeblocks",
      {
        title: "Get time logged",
        description: "Get time blocks logged for the past week",
        input_parameters: {
            channel: {
                type: Schema.slack.types.channel_id,
                description: "Channel",
                },
            user_id: {
                type: Schema.slack.types.user_id,
                description: "User Id",
                },
        },
        output_parameters: {},
      },
      async ({ inputs, client }) => {
        const timesheet = Timelog.api(client);
        const timesheet_resp = await timesheet.query({
          expression: "#user_id = :user_id",
          expression_columns: { "#user_id": "user_id"},
          expression_values: { ":user_id": inputs.user_id},
          limit:20,
      });
      if (!timesheet_resp.ok) {
        return {
          error: timesheet_resp.error || "Error finding timeblocks for user",
        };
      }
    
      const timesheet_rows = timesheet_resp.rows || [];
      console.log(`Found ${timesheet_rows.length} time entries`);
    
      var timesheet_output = [];
      for (var timeblock of timesheet_rows) {
          timesheet_output.push({
          "type": "section",
          "text": {
            "type": "mrkdwn",
            "text": `${timeblock.date}` + " " +
            `${timeblock.project}` + " " + 
            `${timeblock.time_duration}`,
          },
        });
      }
    //Post time blocks to channel
        const resp = await client.call("chat.postMessage", {
          text: ``,
          channel: inputs.channel,
          blocks: timesheet_output,
        });
        return await {
          outputs: {},
        };
      });
    

    You can apply any logic you like to query the table. Use the limit property to limit number of rows returned. The logic in the function above looks for rows where user_id matches the user ID of the user who invoked the trigger:

    expression: "#user_id = :user_id",
    expression_columns: { "#user_id": "user_id"},
    expression_values: { ":user_id": inputs.user_id},
    

    You could also query a list of all entries that were logged in the past seven days as follows:

    expression: "#created_at BETWEEN :lastWeek AND :today",
    

    Or maybe you want to query entries logged within a date span and for a specific user. More details on how to use apps.hosted.tables.query with DynamoDB syntax and operators are here.

6Tie Loose Ends

Tie up loose ends and where to go from here.

  • Complete app development

    Now that you’ve created your table and functions, complete your app by:

    • adding a workflow each for time-entry and time-block-retrieval
    • adding triggers

    Update references:

    • Add functions, table, triggers and workflows to the project metadata file, located at project.ts in the project root.
    • Add the exported constant names to the respective arrays.
  • Future considerations

    There are a number of directions you can go from here to augment your app with additional functionality.

    • write a function to delete time entries once they've been entered into a timesheet/ served their ultimate purpose
    • explore functionality to retrieve total time logged against a given project over a span of time

Was this page helpful?