Skip to content

Airtable Usage and Notes

Jump to bottom
Judson Stevens edited this page Jan 24, 2022 · 5 revisions

Introduction

We currently use Airtable as our "database" for Pybot. This allows us to make quick and easy changes to various aspects of our system, including the messaging interface. The current list of tables on the base (Airtable speak for the "database") is below.

Notes

Every table in the base will have the columns Last Modified - the date/time when the row was last modified, in UTC; Last Modified By - the user who last modified the row; and Valid - a per table formula row that determines if the row is completely valid, as in has all of the required information.

Each table has at least 3 views (views are typically tables with filters applied, like only selecting rows with Valid = True). The base view, the Valid view, and the Fields view. The last view is used for the application to collect the fields for that table without having to know an actual data row. The Fields view is only populated with one row - this row is invalid and has entries for each column with something along the lines of testing_fields_row.

All timestamps are in Unix Epoch Time, which is what Slack uses.

Tables

  • Affiliations

    • Columns
      • Name - name of the affiliation; ex - US Veteran
      • Slug - snake_cased name; ex - us_veteran; is a formula that snake cases the Name column
      • Description
    • Views
      • Grid
      • Valid
      • Fields

  • Daily Programmer

    • Columns
      • Name - name of the affiliation; ex - US Veteran
      • Slug - snake_cased name; ex - us_veteran; is a formula that snake cases the Name column
      • Text - the text of the problem
      • Category - category of the problem, currently unused
      • Initial Slack TS - the Slack timestamp of when the message was first posted
      • Blocks - if the message was created with things like Rich Text, it may have blocks and these are stored here
      • Initially Posted On - the date/time the message was initially posted, in UTC
      • Last Posted On - the date/time the message was last posted, in UTC
      • Posted Count - the number of times the problem has been posted
      • Row ID - auto-incremented integer used to identify specific rows in this table
    • Views
      • Grid
      • Valid
      • Fields

  • Mentors

    • Columns
      • Slack Name - the mentor's Slack username, without the @ symbol
      • Full Name
      • Email
      • Active - whether or not the mentor is considered active
      • Skills - these are links to the Skills table and used to match mentee requests and mentors
      • Max Mentees - the total number of mentees a mentor would like to have; currently not used
      • Desired Mentorship Hours Per Week - the total number of hours the mentor can dedicate per week; currently not used
      • Bio - optional biography provided by the mentors; currently not used
      • Notes - any notes about this mentor; currently not used
      • Time Zone - the time zone of the mentor, in the format "America/Chicago" or "Pacific/Fiji"
      • Mentees Worked With - links back to the Mentor Requests table to designate which mentees the mentor has worked with; currently not used
      • Code of Conduct Accepted
      • Guidebook Read
      • Row ID - auto-incremented integer used to identify specific rows in this table
    • Views
      • Main/Grid View
      • Valid
      • Fields

  • Mentor Requests

    • Columns
      • Slack Name - the mentor's Slack username, without the @ symbol
      • Email
      • Service - what service the mentor is requesting; currently not linked to the Services table but that may change
      • Affiliation - link to the Affiliations table with the category the mentee is affiliated with; i.e. US Veteran
      • Additional Details - filled out by the mentee when they request a mentor
      • Skillsets Requested - links to the Skillsets table, selected by the mentor when they fill out their request
      • Slack Message TS - Slack timestamp of when the request was made
      • Claimed - checkbox, whether or not the request has been claimed
      • Claimed By - link to Mentors table
      • Claimed On - date/time the request was last claimed, in UTC
      • Reset By - Slack username of the user who last reset the claim
      • Reset On - date/time the request was last reset, in UTC
      • Reset Count - number of times the claim has been reset
    • Views
      • Grid
      • Last Month - rows that have a Claimed On column within the last month; used to ensure that specific mentors aren't getting too many requests
      • Fields

  • Message Text

    • Columns
      • Name - name of the message; i.e. Report Received
      • Slug - snake_cased name; i.e. report_received; is a formula that snake cases the Name column
      • Text - the text of the message
      • Category - the category of the message
    • Views
      • Grid
      • Valid
      • Valid Mentorship Requests - rows filtered by Valid and Category: mentorship_request
      • Valid User Reports - rows filtered by Valid and Category: user_report
      • Fields

  • Scheduled Messages

    • Columns
      • Name - name of the message; i.e. Scholarship Links
      • Slug - snake_cased name; i.e. scholarship_links; is a formula that snake cases the Name column
      • Channel - the name of the channel the message should be sent to
      • Message Text
      • Initial Date-Time To Send - date/time to first send the message, in UTC; used to set the When To Send initially
      • Frequency - selection of Daily, Weekly, or Monthly
      • Last Sent - date/time when the message was last sent, in UTC; set this initially to the Initial Date-Time To Send
      • When To Send - date/time to next send the message, calculated using the Frequency and the Last Sent date/time
    • Views
      • Grid
      • Valid
      • Fields

  • Services

    • Columns
      • Name - name of the affiliation; i.e. Resume Review
      • Slug - snake_cased name; i.e. resume_review; is a formula that snake cases the Name column
      • Description
    • Views
      • Grid
      • Valid
      • Fields

  • Skillsets

    • Columns
      • Name - name of the affiliation; i.e. Resume Review
      • Slug - snake_cased name; i.e. resume_review; is a formula that snake cases the Name column
      • Mentors - link to the Mentors table with the mentors who have associated themselves with this skill
      • Mentor Requests - link to the Mentor Requests table with the mentor requests that have requested this skillset
    • Views
      • Grid
      • Valid
      • Fields
Clone this wiki locally