Hitsnag connects your mail client like Gmail with services like Google Docs or Trello. By sending mails to a dediciated @hitsnag.com mail address, you can create e.g. new Google Docs documents or add a new task in a Trello board.
After signing up with your email address, you can connect your email address with one of the provided services (Google Docs, Trello, Notion etc.). For each service you want to connect to, you have to configure the required OAuth credentials (consumer key, consumer secret). Those connections can have additional options, based upon the service type.
To let Hitsnag handle emails coming from your email address, you have to define actions:
You have to specify the local part of the email (e.g. trello) which Hitsnag should be listen to. The local part is the part before the @ sign and must not exceed 64 characters. Also note that the local part must be unique for your account but may exist for other accounts: The sender address is used to lookup the configured action.
By selecting a previous configured service, you are then provided with the available action types. For Trello this can be Attach a new task in board. Depending upon the selected action type, you can also specify in which board the new task should be created. If you skip that field, you can send an email to trello+trello-test-board and Hitsnag automatically finds the correct board.
Incoming events are logged, so that the owner of an email address can see which mails could be handled and which failed.
Hints
The challenge for this exercise is the way, in which additional optional options and settings for the action can be stored. Model a database schema which does not have a a dedicated table for each service as additional services should be integrated in the future.
Solution
Hints
For this solution, jsonb is used as way to store options and settings. This allows the SaaS provider to easily add new settings, based upon the selected action type.
Another way woul be to store options and settings in dedicated database tables.
Schema
Table user {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
email char(255) [not null, unique]
}
Table service {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
name char(255) [not null, unique]
}
Table connection {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
user_id integer [not null, ref: > user.id]
service_id integer [not null, ref: > service.id]
// we are keeping OAuth simple
consumer_key char(255) [not null]
consumer_secret char(255) [not null]
// save additional connection options, e.g. defaults as jsonb
options jsonb [null]
}
Table service_action_type {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
service_id integer [not null, ref: > service.id]
name char(255) [not null, unique]
// additional UI options like validation when a service has been selected
options jsonb [null]
}
Table action {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
email_local_part char(64) [not null]
connection_id integer [not null, ref: > connection.id]
// selected type of action. The application restricts the available service_action_type's by looking up the available types of the connection.service_id relationship
service_action_type_id integer [not null, ref: > service_action_type.id]
// user defined settings, like a reference to the selected Trello board or Google Drive directory
settings jsonb [null]
}
// store events
Table event {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
// the application has atleast to identify the user's sender address. if the sender address is unknown it must be discarded.
user_id integer [not null, ref: > user.id]
// a connection can be null if the user's sender address is known but the email_local_part is unknown
connection_id integer [null, ref: > connection.id]
// status of the action
status action_status [not null, default: 'unknown']
// subject and body are both "not null" but can be empty
subject text [not null]
body text [not null]
// provide additional context information, e.g. if the status == 'unknown', save the local part of the email
context text [null]
// timestamp is relevant
created_at datetime [not null]
}
enum action_status {
// email_local_part is unknown
unknown
// action has failed, e.g. if OAuth credentials are invalid
failed
// action has succeeded
success
}