NAV
Shell

Introduction

Welcome to the TinyDevCRM API! TinyDevCRM serves as your data layer for lightweight, event-driven clients.

In my experience, developing clients (CLIs, GUIs, SDKs) is pretty easy, and it takes a few hours to get them up and running. Even developing APIs is fairly straightforward, on the order of days to weeks. But developing and maintaining enterprise-grade data management solutions is very expensive, on the order of months. It's expensive not just in pure hosting costs, but also in the operational overhead of migrations, backups, failover / replication / high availability, logging, and analytics. Those time sinks are what kill small projects, and prevent small bits of useful work from being routinely shipped and positively impacting people.

So I built TinyDevCRM to amortize the cost of managing, hosting, and understanding your own data, so that you can focus on building and shipping event-driven side projects that stick with you and improve your life. I hope you find this project useful!


Since this API is defined using HTTP, no SDK is necessary! Your favorite language's HTTP library should be able to communicate with this API, no problem. You can view code examples in the dark area to the right, and you can switch the programming language of the examples with the tabs in the top right.

All project code is open-source, and can be viewed in this GitHub organization page.

This API documentation page was created with DocuAPI, a multilingual documentation theme for the static site generator Hugo.

A Sample App

Let's say you're having a difficult time keeping in touch with your friends and/or professional network, and you want your own digital contacts management solution.

Your data model may look something like this:

First Name Last Name Relation Date Notify Note
Melanie Barron Friend 1979-05-03 ANNUALLY Birthday
Cynthia Ward Coworker @ Company X 2009-07-01 ANNUALLY Work Anniversary
Mary Gil Coworker @ Company Y 1993-01-16 ANNUALLY Annual Happy Hour
Christopher Hoffman Fishing Buddy 2003-08-26 MONTHLY Fishing Gala

You may want to reach out to friends or coworkers on an annual basis to congratulate them on that special occasion. One way to do this is to filter your contacts for when Date is the present day, and if Notify is set to ANNUALLY.

Let's build the data management layer for this app in TinyDevCRM, using only a few curl commands.

Identity and Access Management

TinyDevCRM packages a tiny identity and access management (IAM) solution for fine-grained permissioning to your data resources. This means each request only has access to the resources you specifically asked for. This helps keep your data secure.

TinyDevCRM's IAM solution is based on JSON Web Tokens (JWT) and token-based authentication. Each resource request to the service expects a valid JWT access token present in the HTTP header.

TinyDevCRM does not support usernames. Instead, TinyDevCRM uses your email address as your identifier. To create multiple apps with the same email, use plus addressing.

For example, if my email address is me@yingw787.com, my plus-addressed email for app rolodex may be me+rolodex@yingw787.com.

Register a New User

Register a new user:

curl \
  --header "Content-Type: application/json" \
  --request POST \
  --data '{"primary_email": "me+rolodex@yingw787.com", "password": "test1234"}' \
  https://api.tinydevcrm.com/auth/users/register/

To register a new user, you must pass 'primary_email' and 'password' in the HTTP payload.

Obtain a JSON Web Token

Obtain a JSON Web Token:

curl \
    --header "Content-Type: application/json" \
    --request POST \
    --data '{"primary_email": "me+rolodex@yingw787.com", "password": "test1234"}' \
    https://api.tinydevcrm.com/auth/tokens/obtain/

Response:

{"refresh":"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ0b2tlbl90eXBlIjoicmVmcmVzaCIsImV4cCI6MTU5MjUyNjY0MywianRpIjoiNTliOTJjMDdiNDNlNDZlMDk5YjY1ZjhjOWEyMWI1MGMiLCJ1c2VyX2lkIjoxfQ.IkXnAMgIXENQac8t87hAJpzS_nGYdtwDBr04UG8ErwE","access":"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ0b2tlbl90eXBlIjoiYWNjZXNzIiwiZXhwIjoxNTkxMzE3MzQzLCJqdGkiOiIyZGU5YjE1NWRjZGI0YzU5YWRmZTVkZmM2Y2FjNWMxYSIsInVzZXJfaWQiOjF9.a3ii2l7QgJhDD0527hA5nxDV9EtmbNWcYU8ijDh7wFk"}

Get a JSON Web Token in order to access other API endpoints from TinyDevCRM. A JSON Web Token consists of two components: a “refresh” token, which is a long-lived token meant to store session state on the client, and an “access” token, a short-lived token sent over the wire to access resources.

Since each token is simply a hash, any kind of data can be stored within the token. This enables TinyDevCRM to decrypt the token and figure out what resources the token has permission to access.

Read more about JSON Web Tokens on the industry standard page.

Refresh a JSON Web Token

Refresh a JSON Web Token:

curl \
    --header "Content-Type: application/json" \
    --request POST \
    --data '{"refresh": "$YOUR_JWT_REFRESH_TOKEN"}' \
    https://api.tinydevcrm.com/auth/tokens/refresh/

Response:

{"access":"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ0b2tlbl90eXBlIjoiYWNjZXNzIiwiZXhwIjoxNTkxNzIyNDA5LCJqdGkiOiJhZjcxZTRjYmQxMjU0N2M2YjA2NGYzNWJkYjUwZGIyMyIsInVzZXJfaWQiOjF9.hPsU9jj8zimIkB3LBiMuzcGDNgHQFJ5DS6oK61Oc6E0","refresh":"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ0b2tlbl90eXBlIjoicmVmcmVzaCIsImV4cCI6MTU5MjkzMTcwOSwianRpIjoiNzJlNGZkOGE0NWIxNDRmYWEzOGQ0NTU0OTY0NjQwMzciLCJ1c2VyX2lkIjoxfQ.7lWBJTX9cAmEZMJx0XsLMkf4VDcpHIGeSoLy7UuHyck"}

Access tokens expire after 5 minutes. Requests made to TinyDevCRM with an expired access token will result in an HTTP 401 Unauthorized error. Request a new access token using your refresh token in order to make further requests and continue your session.

By default, TinyDevCRM does not rotate refresh tokens upon refresh.

Blacklist a JSON Web Token

Blacklist a JSON Web Token:

curl \
    --header "Content-Type: application/json" \
    --request POST \
    --data '{"refresh": "$YOUR_JWT_REFRESH_TOKEN"}' \
    https://api.tinydevcrm.com/auth/tokens/blacklist/

When your session is complete and you want to log the client out, blacklist the refresh token to prevent further accesses to TinyDevCRM using this token or access tokens generated from it.

Upon successful blacklisting, TinyDevCRM should return an HTTP 205 Reset Content response.

Native Tables

TinyDevCRM leverages PostgreSQL's ability to create tables to store raw data. These tables act as a source of truth for the rest of your workflow.

Native tables are differentiated from foreign tables. Foreign tables are imported from a remote database, such as another PostgreSQL instance or a separate type of database entirely. See this wiki page for a full list of different types of data imports supported by the PostgreSQL community.

Support for foreign tables may be added in a future TinyDevCRM release.

Preparing Data

Let's take our digital contacts management data and import it into TinyDevCRM. For data integrity reasons, TinyDevCRM supports Apache Parquet.

A more streamlined means of data upload will be added in a future TinyDevCRM release.

If we converted it into a CSV file, it may look something like this:

$ cat /path/to/file.csv

FirstName,LastName,Relation,Date,Notify,Note
Melanie,Barron,Friend,1979-05-03,ANNUALLY,Birthday
Cynthia,Ward,Coworker @ Company X,2009-07-01,ANNUALLY,Work Anniversary
Mary,Gil,Coworker @ Company Y,1993-01-16,ANNUALLY,Annual Happy Hour
Christopher,Hoffman,Fishing Buddy,2003-08-26,MONTHLY,Fishing Gala

TinyDevCRM currently accepts Apache Parquet files only. To convert a CSV file into a Parquet file locally, install the Python dependencies pandas and pyarrow. You can do so by downloading Anaconda and running the following script:

$ conda create myenv python=3.8
$ conda activate myenv
(myenv) $ conda install -c anaconda pandas
(myenv) $ conda install -c conda-forge pyarrow

Next, open a Python shell using ipython, and run the following:

import pandas as pd
pd.read_csv('/path/to/file.csv').to_parquet('/path/to/file.parquet')

At this point, you should now have a Parquet file representing your data at /path/to/file.parquet.

Creating the Table

With your Parquet file, you can define and create the table using one API request. TinyDevCRM creates the table using a set of column name and type definitions passed into the request header. These type definitions are any supported by PostgreSQL. See this documentation page for a full list of PostgreSQL data types.

Create table as sample_table:

curl \
    --header "Content-Type: multipart/form-data" \
    --header "Authorization: $YOUR_JWT_ACCESS_TOKEN" \
    --request POST \
    --form "file=@/path/to/file.parquet" \
    --form "table_name=sample_table" \
    --form columns='[{"column_name": "FirstName", "column_type": "varchar(256)"}, {"column_name": "LastName", "column_type": "varchar(256)"}, {"column_name": "Relation", "column_type": "varchar(256)"}, {"column_name": "Date", "column_type": "date"}, {"column_name": "Notify", "column_type": "varchar(256)"}, {"column_name": "Note", "column_type": "varchar(256)"}]' \
    https://api.tinydevcrm.com/tables/create/

Note that TinyDevCRM preserves case sensitivity of column names and column values. Also note that the column names must be ordered, and match the names of the CSV headers exactly.

This HTTP request would be translated to the following SQL:

CREATE FOREIGN TABLE "temp" (
    "FirstName" VARCHAR(256),
    "LastName" VARCHAR(256),
    "Relation" VARCHAR(256),
    "Date" DATE,
    "Notify" VARCHAR(256),
    "Note": VARCHAR(256)
) SERVER parquet_server OPTIONS (filename '/path/to/file.parquet');
CREATE TABLE "sample_table" AS TABLE "temp" WITH DATA;
DROP FOREIGN TABLE "temp";

A successful request should return an HTTP 201 Created response, with the following data:

{"id":$TABLE_ID,"table_name":"sample_table","user":$USER_ID}

Materialized Views

Materialized views are query results cached as views by PostgreSQL for future reference. Since materialized views cache the original query for refreshing at a later point in time, and cannot be directly updated, they are a lightweight and safe alternative for data manipulation, as opposed to creating direct views or additional tables. They can also be referenced by external applications just as tables can be.

TinyDevCRM supports arbitrary view creation, by passing the direct SQL command to create a materialized view within the HTTP request body. In order to prevent unsafe usage of the API, TinyDevCRM restricts SQL commands to only one statement, and enforces keyword prefix of SELECT, TABLES, or VALUES, as per the PostgreSQL documentation on materialized views.

Creating a Materialized View

To create a view:

curl \
    --header "Content-Type: application/json" \
    --header "Authorization: JWT $YOUR_JWT_ACCESS_TOKEN" \
    --request POST \
    --data "{\"view_name\": \"sample_view\", \"sql_query\": \"SELECT * FROM \"sample_table\" WHERE date_part('month', \"Date\") = date_part('month', CURRENT_DATE) AND date_part('day', \"Date\") = date_part('day', CURRENT_DATE) AND \"Notify\" = 'ANNUALLY'\"}" \
    https://api.tinydevcrm.com/views/create/

This translates to the following SQL:

CREATE MATERIALIZED VIEW "sample_view" AS
    SELECT * FROM "sample_table" WHERE
    date_part('month', "Date") = date_part('month', CURRENT_DATE) AND
    date_part('day', "Date") = date_part('day', CURRENT_DATE) AND
    "Notify" = 'ANNUALLY'
WITH DATA;

This should result in a successful HTTP 201 Created response:

{"id":$VIEW_ID,"view_name":"sample_view","user":$USER_ID}

Cron Jobs

A cron job is a time-based job scheduler. cron enables servers to run functions, procedures, and other assorted tasks at specific and fixed times.

TinyDevCRM leverages pg_cron to migrate cron jobs from your /etc/crontab file into a PostgreSQL table and the PostgreSQL directory /var/lib/postgresql/data. This is important to scale cron jobs across multiple servers, and to efficiently separate the compute and data layers of the database. It's also important to keep costs down and fault models tight. cron jobs will only run if the database is active, and many cron jobs can be run on a single server, as opposed to using a serverless development model. Since cron is native to all Unix-like systems, no additional dependencies need to be installed and audited, which helps TinyDevCRM pass any compliance/certification needs faster and keeps build sizes small, which means you can run TinyDevCRM from many more machines.

Cron jobs work well in refreshing materialized views, which is important in getting an up-to-date understanding of your data. Since refreshing materialized views is natively supported by PostgreSQL, you don't need to worry about data consistency issues.

Creating a Cron Job

Pass in a crontab definition. Common crontab expressions are supported (i.e. no intervals or special keywords). Validate your crontab expressions using this online cron expression editor.

To create a cron job:

# Create a cron job to refresh materialized view "sample_view" every minute.
curl \
    --header "Content-Type: application/json" \
    --header "Authorization: JWT $YOUR_JWT_ACCESS_TOKEN" \
    --request POST \
    --data '{"view_name": "sample_view", "crontab_def": "* * * * *"}' \
    https://api.tinydevcrm.com/jobs/create/

This creates two cron jobs underneath the hood: one to refresh the materialized view, and one to send a notification.

This should return an HTTP 201 Created response with the following data:

{"id":$JOB_ID,"job_ids":[$CRON_JOB_ID_1,$CRON_JOB_ID_2],"user":$USER_ID,"view":$VIEW_ID}

Streaming Channels

TinyDevCRM uses HTTP/2 and Server-Sent Events in order to stream materialized view refresh events to your clients. Compared to WebSockets, Server-Sent Sockets need no SDKs to install/audit/maintain/upgrade, pass through packet inspection and firewalls more easily, and remain unidirectional to enforce data consistency.

TinyDevCRM currently supports one channel listening to one specific cron job, refreshing one specific materialized view. Listening to refresh events from different materialized views will be supported in a future TinyDevCRM release.

Create a streaming channel

To create a streaming channel:

curl \
    --header "Content-Type: application/json" \
    --header "Authorization: JWT $YOUR_JWT_ACCESS_TOKEN" \
    --request POST \
    --data '{"job_id": $YOUR_JOB_ID}' \
    https://api.tinydevcrm.com/channels/create/

This should result in an HTTP 201 Created response, and the following response data:

{"id":$CHANNEL_ID,"public_identifier":$CHANNEL_UUID,"job":$JOB_ID,"user":$USER_ID}

Using a tool like jq, you can retrieve the channel public identifier from the response data:

$ echo $RESPONSE | jq -r ".public_identifier"

61951b33-dbc6-4fab-b1c9-339c730a4eb0

Listen to a streaming channel

To listen to an existing streaming channel:

curl \
    --header "Content-Type: application/json" \
    --header "Authorization: JWT $YOUR_JWT_ACCESS_TOKEN" \
    --request GET \
    https://api.tinydevcrm.com/channels/$YOUR_CHANNEL_PUBLIC_IDENTIFIER/listen/

Using the public identifier taken from the previous response as 61951b33-dbc6-4fab-b1c9-339c730a4eb0, the final API request would be:

curl \
    --header "Content-Type: application/json" \
    --header "Authorization: JWT $YOUR_JWT_ACCESS_TOKEN" \
    --request GET \
    https://api.tinydevcrm.com/channels/61951b33-dbc6-4fab-b1c9-339c730a4eb0/listen/

The HTTP response should be a never-ending event stream:

event: message
id: 61951b33-dbc6-4fab-b1c9-339c730a4eb0:1
data: {"update_available": "true", "view_name": "sample_view"}

event: keep-alive
data:

event: keep-alive
data:

event: keep-alive
data:

event: message
id: 61951b33-dbc6-4fab-b1c9-339c730a4eb0:2
data: {"update_available": "true", "view_name": "sample_view"}
...
event: message
id: 61951b33-dbc6-4fab-b1c9-339c730a4eb0:6
data: {"update_available": "true", "view_name": "sample_view"}

event: keep-alive
data:

event: keep-alive
data:

event: keep-alive
data:

event: message
id: 61951b33-dbc6-4fab-b1c9-339c730a4eb0:7
data: {"update_available": "true", "view_name": "sample_view"}

Create Your Own!

I hope you've found this tutorial and documentation useful. The special thing about TinyDevCRM is how any app built on top of it isn't special. All tiny automation-driven apps can be built from these basic fundamentals.


Say you want to build out a habit tracker in order to automate your daily routines.

Your data model may look something like this:

Name Frequency Time of Day Notes Reminders
Eat breakfast DAILY MORNING
Brush and floss DAILY MORNING Brush your teeth in the morning if you want to keep your friends [8:00AM,]
Meditate DAILY MORNING
Update financial ledger EVERY TWO WEEKS MORNING
Eat lunch DAILY AFTERNOON [1:00PM,]
Check bank account DAILY EVENING
Brush and floss DAILY EVENING Brush your teeth in the evening if you want to keep your teeth [8:00PM,]
Eat dinner DAILY EVENING [6:00PM,6:30PM,]
Disinfect frequently touched surfaces DAILY ALLDAY

You may want to send an iPhone notification, or have your Google Home say something, when the present time matches the time of a reminder.


Maybe you want to create an internal tool in order to keep track of organizational processes your direct reports come up with in improving the division. The more processes a report submits, the faster he or she is promoted, with the added benefit of making everybody's lives easier and “hero work” more visible.

Your data model may look something like this:

First Name Last Name Process Notes GitHub PR Priority Item? Compensation Paid?
Debbie Gonzalez .gitattributes for tabs/spaces #45 NO $20 NO
Jacob Richards linting over landing page repo #67 NO $20 NO
Michele Wilkerson improving test coverage to 80% #34 NO $20 NO
Nancy Ford no-code adaptor for marketing analytics #12 YES $100 NO

Create a view filtering for all unpaid work, and refresh that view on the 5th day of the week (or Friday) for general awards and claps, to increase morale and improve retention.


Lastly, consider a password rotation tool, where you'd like a basic app to remind yourself to rotate a password in your password manager every six months.

Your data model may look something like this:

Type Name Username URL Password Last Rotated
Login GitHub Login yingw787 https://github.com/login 2018-07-22
Login AWS IAM $IAM_USER Login $IAM_USER $ACCT.signin.aws.amazon.com 2019-05-25
Login Amtrak Login scrap@yingw787.com amtrak.com 2007-01-02

You may want to filter all logins older than three months, and rotate those passwords + sign out of all devices, so that any password leaks don't compromise your account. If you have thousands of passwords, this would be impossible to do by hand.

You could pay for solutions to all these. But managed solutions release jarring UI/UX changes (because only shipping new product makes money), might not be compatible with your device or devices, and become more and more expensive the more apps you purchase. Worst of all, they might lock down your data, export your data in its own special format, or crash and lose your data entirely. For certain classes of these life management applications, you may want stability, transparency, and utility, over the latest and greatest. That's why data forms the least common denominator for all these apps, and why you might want to separate out the data layer from the rest of your stack.