Understanding Your Trello Schema

This tutorial is a basic explanation of the default Trello schema found in Stitch. A full summary of each schema attribute is provided to enable you to better understand and use Trello effectively for task management. A schema is a collection of objects within a database, typically consisting of tables (defined below) and the relationships between those tables.

Stitch’s Trello integration includes the following tables. Note- this may not be a full list of attributes. Refer to Trello’s documentation for a full list and description of each attribute. The colored attributes represent foreign key relationships which are explained in the last section of this tutorial. 

trello_actions

This table contains info about the actions related to cards, including the lists that a card belongs to.

Primary Key: 🔑id

AttributeDescription                                                                                                                                      
idThe ID of the action.
data
  • _board_id
  • _board_name
  • _card_id
  • _card_name
Relevant information regarding the action.
dateWhen the action occurred.
idMemberCreatorThe ID of the member that caused the action.
typeType of the action.

trello_boards

This table contains details about all the boards that the connection (authorizing user) is a member of.

This table contains nested structures. If you use a data warehouse that doesn’t natively support nested structures, some of the attributes listed below may be in a subtable. These items are marked with a ★

Primary Key: 🔑id

AttributeDescription
id

id

id

id

The ID of the board.
closedBoolean whether the board has been closed or not.
descdataIf the description includes custom emoji, this will contain the data necessary to display them.
idOrganizationMongoID of the organization to which the board belongs.
lableNames
  • _black
  • _blue
  • _green
  • _lime
  • _orange
  • _pink
  • _purple
  • _red
  • _sky
  • _yellow
Object containing color keys and the label names given for one label of each color on the board.
nameThe name of the board.
prefsShort for “preferences”, these are the settings for the board.
memberships ★Array of objects that represent the relationship of users to this board as memberships.
shortUrlURL for the board using only its shortMongoID.
starredWhether the board has been starred by the current request’s user.
urlPersistent URL for the board.

trello_cards

This table contains info about the latest state of all the cards that the connection (authorizing user) has access to. This table is updated based on the actions table.

This table contains nested structures. If you use a data warehouse that doesn’t natively support nested structures, some of the attributes listed below may be in a subtable. These items are marked with a ★

Primary Key: 🔑id

AttributeDescription
id

id

The ID of the card.
badges
  • _attachments
  • _checkitems
  • _checkitemschecked
  • _comments
  • _description
  • _due
  • _duecomplete
  • _subscribed
  • _viewingmembervoted
  • _votes
Pieces of information about the card that are displayed on the front of the card.
descThe description for the card. Up to 16384 chars.
idChecklists ★An array of checklist IDs that are on this card.
idLables ★An array of label IDs that are on this card.
idMembers ★An array of member IDs that are on this card.
labels ★Array of label objects on this card.
closedWhether the card is closed (archived).
dateLastActivityThe datetime of the last activity on the card.
dueThe due date on the card, if one exists.
dueCompleteWhether the due date has been marked complete.
idAttachmentCoverThe id of the attachment selected as the cover image, if one exists.
idBoardThe ID of the board the card is on.
idListThe ID of the list the card is in.
idShortNumeric ID for the card on this board. Only unique to the board, and subject to change as the card moves.
manualCoverAttachmentWhether the card cover image was selected automatically by Trello, or manually by the user.
nameName of the card.
posPosition of the card in the list.
shortUrlThe 8 character shortened ID for the card.
urlFull URL to the card, with the name slug.

trello_checklists

The trello_checklists table contains info about the latest state of all the checklists on cards that the connection (authorizing user) has access to.

Primary Key: 🔑id

Attribute        Description                                                                                                                                              
idThe ID of the checklist.
idBoardThe ID of the board the checklist is on.
idCardThe ID of the card the checklist is on.
nameThe name of the checklist.
posThe position of the checklist on the card (relative to any other checklists on the card).

trello_lists

This table contains info about all the lists the connection (authorizing user) has access to.

Primary Key: 🔑id

Attribute        Description                                                                                                                                              
idThe ID of the list.
closedThe name of the list.
idBoardThe ID of the board the list is on.
nameThe name of the list.
posThe position of the list on the board.
subscribedWhether the member is subscribed to this list.

trello_users

This table contains info about the users in your Trello account/organization.

Primary Key: 🔑id

Attribute       Description                                                                                                                                               
idThe ID the user.
fullNameThe full name of the user.
usernameThe username of the user.

Foreign Key Join

A foreign key is a column in a table that establishes an association with another table via shared values. To accomplish this, a foreign key is populated with values of the primary key from the other table. Foreign keys are also typically titled IDs that are attached to the beginning of the name of the referenced table. Below are some of the relations you could have when joining the different Trello tables.

Note: All foreign key relationships are color coordinated based on each primary key in the tables above for easy identification.

trello_actions with trello_boards

Join these tables when you want to link an action to a board.

  • Foreign key: trello_actions_board_id <–> trello_boards.id

trello_actions with trello_cards

Join these tables when you want to link an action to a card.

  • Foreign key: trello_actions_board_id <–> trello_cards.id

trello_actions with trello_users

Join these tables when you want to link an action to a user.

  • Foreign key: trello_actions.idMemberCreator <–> trello_users.id

trello_cards with trello_boards

Join these tables when you want to link a card to a board.

  • Foreign key: trello_cards.idBoard <–> trello_boards.id

trello_checklists with trello_boards

Join these tables when you want to link a checklist to a board.

  • Foreign key: trello_checklists.idBoard <–> trello_boards.id

trello_checklists with trello_cards

Join these tables when you want to link a checklist to a board.

  • Foreign key: trello_checklists.idCard <–> trello_cards.id

trello_lists with trello_boards

Join these tables when you want to link a list to a board.

  • Foreign key: trello_lists.idBoard <–> trello_boards.id

Resource

About Bryn Burns

Hi! I'm Bryn Burns. I am a current senior at Virginia Tech pursuing degrees in Statistics and Mathematics. Data science and visualization are two things I'm very passionate about, as well as working with numbers and helping people learn. I'm thrilled to share my knowledge here at The Data School!