Design proposed for standup database schema

[BartoszJarocki]

Create a standup database schema while keeping in mind that, standup is only one type of the possible use of the new meeting type. team prompt might be a more appropriate name for internal use.

This issue involves designing and architecting data structures for a new meeting type. 1 . how the data structure should look like?

2. how do we store responses?
3. how can we design the schema with recurrence in mind, does it change anything?
4. how do we support multiple days/weeks/whatever?
5. what are the phases of the meeting?
6. what are the stages?

Before writing any code, it is required to outline the solution and discuss it with the maintainer.

AC:

• [ ] data structure proposed and discussed with maintainer
• [ ] data structure implemented via migrations

[BartoszJarocki]

There are at least two ways to accomplish the goal of having team prompt activity with recurrence in mind.

Part 1 - initial scope

New meeting type

1. Add a new meeting type teamPrompt
2. Meeting stages, I propose 1 stage per meeting participant, a single meeting phase

Storing team prompt responses

1. Similarly to storing retro reflections, we need a new TeamPromptResponse table which would store the team prompt responses

• fields

  • id
  • createdAt
  • updatedAt
  • creatorId
  • meetingId
  • content
  • reactjis

• questions a. should we store it in rethink or pg? any potential issues with storing that in pg?

2. extend Discussion table [postgres]
   • add new enum field teamPromptResponse to discussionTopicType enum

---

Part 2 - recurrence, later stage

At this point, we need to figure out how to handle recurrence

First way - each recurrence is a new meeting phase

For example, we have a team prompt meeting, interval set to daily. In this case, each day will be a new meeting phase.

Changes needed

1. Extend the NewMeeting table [rethinkdb]
   • add new meeting type - teamPrompt
   • add new fields related to recurrence, isRecurring, interval [daily, weekly, monthly, yearly?], startDate (with tz), endDate (with tz)
   • depending on the further specification we can have either always a single stage or create a stage for each meeting participant

Example JSON structure of a meeting

{
  "id": "...",
  "createdBy": "...",
  "createdAt": "...",
  "endedAt": "...",
  "meetingNumber": 1,
  "meetingType": "teamPrompt", // new type
  "name": "Async Standup #1",
  "isRecurring": true, // new field
  "interval": "daily", // new field
  "startDate": "...", // new field
  "endDate": "...", // new field
  "summarySentAt": {
    "$reql_type$": "TIME",
    "epoch_time": 1644211686.138,
    "timezone": "+00:00"
  },
  "teamId": "...",
  "templateId": "...",
  "updatedAt": {
    "$reql_type$": "TIME",
    "epoch_time": 1644211683.29,
    "timezone": "+00:00"
  },
  "phases": [
    {
      "id": "...",
      "phaseType": "responses", // new type
      "stages": [
        {
          "id": "...",
          "phaseType": "responses",
          "startAt": {
            "$reql_type$": "TIME",
            "epoch_time": 1644211647.127,
            "timezone": "+00:00"
          },
          "endAt": {
            "$reql_type$": "TIME",
            "epoch_time": 1644211650.609,
            "timezone": "+00:00"
          },
          "isComplete": true,
          "isNavigable": true,
          "isNavigableByFacilitator": true
        }
      ]
    },
    {
      "id": "...",
      "phaseType": "responses",
      "stages": [
        {
          "id": "...",
          "phaseType": "responses",
          "startAt": {
            "$reql_type$": "TIME",
            "epoch_time": 1644211647.127,
            "timezone": "+00:00"
          },
          "endAt": {
            "$reql_type$": "TIME",
            "epoch_time": 1644211650.609,
            "timezone": "+00:00"
          },
          "isComplete": false,
          "isNavigable": true,
          "isNavigableByFacilitator": true
        }
      ]
    }
  ]
}

Pros

• we can model the new phases as we wish without touching too much NewMeeting data model

Cons

• new data model for phases will most probably have some of the fields exactly like NewMeetin, for example summarySentAt, endedAt, startDate, endDate

Second way - each recurrence is a new meeting

For example, we have a team prompt meeting, interval set to daily. In this case, each day will be a new meeting.

1. Extend the New Meeting table [rethink]
   • add a new parentMeetingId field which will store the meeting id that groups all the sub meetings
   • show only meetings with empty meetingParentId on the dashboard
   • new meeting gql type would need to have another field something like, submeetings?
   • everything else is just like in the case with phases

Root meeting

{
  "id": "parentId",
  "parentMeetingId" : null, // new field
  "createdBy": "...",
  "createdAt": "...",
  "endedAt": "...",
  "meetingNumber": 1,
  "meetingType": "teamPrompt",
  "name": "Async Standup #1",
  "isRecurring": true,
  "interval": "daily",
  "startDate": "...",
  "endDate": "...",
  "summarySentAt": {
    "$reql_type$": "TIME",
    "epoch_time": 1644211686.138,
    "timezone": "+00:00"
  },
  "teamId": "...",
  "templateId": "...",
  "updatedAt": {
    "$reql_type$": "TIME",
    "epoch_time": 1644211683.29,
    "timezone": "+00:00"
  },
  "phases": [] // do we need anything here?
}

Child meeting, representing an instance of the given interval, ie. day in the daily standup

{
  "id": "...",
  "parentMeetingId" : "parentId",
  "createdBy": "...",
  "createdAt": "...",
  "endedAt": "...",
  "meetingNumber": 1,
  "meetingType": "teamPrompt", // do we need a special type for a child meeting?
  "name": "Async Standup #1", 
  "startDate": "...",
  "endDate": "...",
  "summarySentAt": {
    "$reql_type$": "TIME",
    "epoch_time": 1644211686.138,
    "timezone": "+00:00"
  },
  "teamId": "...",
  "templateId": "...",
  "updatedAt": {
    "$reql_type$": "TIME",
    "epoch_time": 1644211683.29,
    "timezone": "+00:00"
  },
  "phases": [
    {
      "id": "...",
      "phaseType": "responses",
      "stages": [
        {
          "id": "...",
          "phaseType": "responses",
          "startAt": {
            "$reql_type$": "TIME",
            "epoch_time": 1644211647.127,
            "timezone": "+00:00"
          },
          "endAt": {
            "$reql_type$": "TIME",
            "epoch_time": 1644211650.609,
            "timezone": "+00:00"
          },
          "isComplete": true,
          "isNavigable": true,
          "isNavigableByFacilitator": true
        }
      ]
    }
  ]
}

Pros

• feels more natural, each interval instance feels like a new meeting

Cons

• add something specific to team prompt activity will affect NewMeeting data model

Handling recurring jobs should be handled using the ScheduledJobs table

• add a new ScheduledJobType, something like MEETING_NEW_PHASE_START, MEETING_END and MEETING_START, tbd
• add another meeting phase/new meeting depending on the interval a. how to handle meetings with no defined end date? should we schedule another job to do that when a previous one runs?
• handle start date
• handle end date

[Dschoordsch]

@BartoszJarocki Didn't look into the details yet if it would work, but since you want to discuss this with Matt I just wanted to put the idea out there: How about having 1 meeting for standups and add multiple phases for the recurrenses.

[BartoszJarocki]

@Dschoordsch I think that's exactly what I proposed as a first solution in First way - each interval instance is a new meeting phase. ie. one root meeting, then each recurrence is a phase

edit: I improved wording, recurrence sounds way better than interval instance, thank you!

[BartoszJarocki]

@mattkrick just a thought, in solution one where each day is a new phase, we wouldn't have to fetch everything if we store data in PG (we could fetch +/- X days of the current date and paginate if needed). Creating all the tables related to the meeting in pg is a pretty big lift though, so I'm not sure if we should do that now

[BartoszJarocki]

I probably should've been more clear about what's needed for the current scope and what's for the future. The initial scope can be found here: https://www.notion.so/parabol/Stand-up-MVP-27ebad512e754eb0ae740cdb7326a080#b570682f1fa04c8eb48ef93eced37ffa

To actually implement this, we don't need recurrence, thus we don't need any changes to the NewMeeting table, for now. Changes needed:

1. New meeting type teamPrompt
2. Storing team prompt responses, ie. new table
3. Meeting stages, I propose 1 stage per meeting participant, a single meeting phase

Once we have that, we can figure out the recurrence (it'll be at the same time when we'll be adding the day picker to the meeting). It'd be nice to have that discussed already, but I don't want to slow down the development. The reason I also proposed some solutions to recurrence is I believe it'll require some changes to the NewMeeting table and wanted us to be prepared.

TLDR

1. Let's start work by adding the three things I've mentioned above. Everything that's done here will be hidden under the feature flag so it's safe to try™
2. Let's discuss the recurrence in the meantime and agree on the best solution. By the time we get to this part, we'll have the architecture draft ready

Let me know what do you think CC @mattkrick @Dschoordsch

Edit: I updated initial comment to make it clear

[Dschoordsch]

I think you''re right with the safe to try™ part. Reading through all this, I think it might be useful to have a separate Recurrance table with a 1:n relationship to NewMeeting. This way we wouldn't bend the NewMeeting meeting type too much which in the long term would understanding the logic difficult. Use case dashboard: We show open meetings as we do now. If Recurrance is linked we can show history of meetings. If the meeting is recurring, there should be one open instance. We might want to have a 'meeting did not start yet' stage, but it's probably best to just open the next one straight after the last one was closed.

[tianrunhe]

As your Metrics Representative, I'd like to invite us think about from metrics perspective too. I have following teaser questions:

1. How do we define "number of meetings held" for stand-up? In a recurrence setting, say a daily activity, we probably would like each day to be counted as a distinct meeting?
2. What's the definition of "meeting completed" in this sense? Again, for a daily activity, do we mark the activity/meeting as completed at midnight each day?
3. Thinking out of box, should we still track the same old set of metrics or we could introduce new metrics for async stand-up?

Also CC @tghanken for insights

[tghanken]

From a data modeling perspective, I prefer the method outlined by @Dschoordsch where we create a Recurrence table. I have concerns about the approach outlined in First way - each recurrence is a new meeting phase as it would make it more difficult to extend recurrence to existing and future multi-phase meeting types. If we implemented recurrence as phases, would we then need to nest phases within phases if we wanted to implement recurrence for retros?

add new fields related to recurrence, isRecurring, interval [daily, weekly, monthly, yearly?]

Would it make sense to store the data internally in a crontab style (or something similar) format? We could start by implementing simple cases (daily, weekly, etc.) in the product, but this would allow us to open up more options for users in the future without backend data manipulation.

1. How do we define "number of meetings held" for stand-up? In a recurrence setting, say a daily activity, we probably would like each day to be counted as a distinct meeting?
2. What's the definition of "meeting completed" in this sense? Again, for a daily activity, do we mark the activity/meeting as completed at midnight each day?

I agree with what you've outlined here. Each "instance" of a recurrence should count as a full meeting from a metrics perspective. We likely want some sort of participation check in place so that we don't inflate numbers if someone sets a recurring meeting and never uses it. Should a meeting be marked as complete at all if no one participates?

3. Thinking out of box, should we still track the same old set of metrics or we could introduce new metrics for async stand-up?

For the most part, I think using the same metrics works, as we should treat each instance of a recurrence as an individual meeting. However, I think it would also be helpful to monitor usage at the "Recurrence" level as well:

• Usage rate: If X meetings are automatically scheduled by Parabol's recurrence feature, how many meetings are actively participated in by users?

I'll keep thinking through this and see if there's any additional metrics that I think would be helpful.

[BartoszJarocki]

@Dschoordsch @tianrunhe @tghanken this is awesome, thank you for the feedback!

Seems like the best option is to treat each recurrence as a separate meeting. Having another table with 1:n relation will do the job and it won't require NewMeeting table modification (very little, at most). At te beginning we won't be restricting standup to have only one active meeting (timezone differences etc) so we'll have to figure out how to show only the parent on the dashboard. Having recurrence as a separate meeting also means that day/week/whaverver navigator could simply change the URL to a different meeting id (so we can link to the proper day/meeting), I like that direction!

How do we define "number of meetings held" for stand-up? In a recurrence setting, say a daily activity, we probably would like each day to be counted as a distinct meeting?

I guess the right answer is to count each day/week etc as a separate meeting

What's the definition of "meeting completed" in this sense? Again, for a daily activity, do we mark the activity/meeting as completed at midnight each day?

For the start, there will be no restrictions and we won't be closing meetings automatically (it's not in the current scope so it's subject to change anytime)

[mattkrick]

I haven't forgotten about this! will dive in this weekend. don't let me hold anything up if you're waiting on something. you have my full support moving forward!

[mattkrick]

Extra questions (some discussed during our 1:1):

• What does an ended meeting look like? Is it immutable? Can a team member still add their updates? Depending on the definition of ended, can multiple child meetings be open at the same time? How do we encourage facilitators to close meetings?
• Is there a 1:1 relationship between Recurrence & Template? If so, could we merge them into the same table? Pros/cons?

Thoughts:

• Perhaps rename Recurrence to MeetingSeries (a little more specific since we might use "recurrence" in other places)
• If we can guarantee that a meeting series only has 1 open meeting at a time we can keep the /meetings query simple (no need to group on recurrence) and use a LIMIT 1 in the DB queries. If we don't restrict this now it may be extra work later
• Cronjobs can fail, how can we build the system in a way that a failed cronjob doesn't ruin the meeting series? E.g. perhaps the startStandupMeeting mutation closes the previous meeting so if the cronjob fails they can just manually open the next?
• Can we build in PG? Laying the groundwork here would make the migration much easier later

[BartoszJarocki]

What does an ended meeting look like? Is it immutable? Can a team member still add their updates? Depending on the definition of ended, can multiple child meetings be open at the same time? How do we encourage facilitators to close meetings?

This is more a UX question and I don't think we have an answer yet. CC @ackernaut

Is there a 1:1 relationship between Recurrence & Template? If so, could we merge them into the same table? Pros/cons?

I don't think it's 1:1. IMO users should be able to create as many team prompt meetings with different templates as they wish. Same as regular meetings.

Perhaps rename Recurrence to MeetingSeries (a little more specific since we might use "recurrence" in other places)

Sounds good!

If we can guarantee that a meeting series only has 1 open meeting at a time we can keep the /meetings query simple (no need to group on recurrence) and use a LIMIT 1 in the DB queries. If we don't restrict this now it may be extra work later

I don't think there will be any restrictions at the beginning as we don't want to make too many assumptions about how our users would like it to work. Timezones are a big issue there (when to close the meeting?) but that's just my personal opinion. CC @ackernaut

Cronjobs can fail, how can we build the system in a way that a failed cronjob doesn't ruin the meeting series? E.g. perhaps the startStandupMeeting mutation closes the previous meeting so if the cronjob fails they can just manually open the next?

IMO there should be a way to close/open the next meeting manually but that's also my personal take. CC @ackernaut

Can we build in PG? Laying the groundwork here would make the migration much easier later

I think all new tables should go to PG

@mattkrick These are all really good questions about recurrence and I hope it'll allow us to shape this feature nicely. If you think it'd be beneficial we can have some sync time with @ackernaut to discuss something that'd be safe to try with recurrence, I'm all for it.

---

Regarding the current phase, let's summarize implementation steps

1. Add new type teamPrompt to export type MeetingTypeEnum = 'poker' | 'retrospective' | 'action'
2. Add new table in PG TeamPromptResponse with fields id, createdAt, updatedAt, creatorId, meetingId, content, reactjis
3. Change the Discussion table add new enum field teamPromptResponse to discussionTopicType enum
4. Meeting structure - 1 stage per meeting participant, a single meeting phase
5. New meeting template Async standup related to teamPrompt meeting (from UI perspective the meeting will show as Async Standup (hardcoded), design details here: https://github.com/ParabolInc/parabol/issues/5918

@mattkrick please let me know if that sounds good. I'll start the implementation of the ☝🏻 while we continue to shape the recurrence with @ackernaut.

[ackernaut]

Here are my thoughts:

• A team could start any number of one-time or recurring meetings at the same time
• In the future they can pick how their prompt is supplied: same prompt from a template, random prompt from a templated list (I can clone and edit my list, e.g. a random social prompt every Friday), etc.
• Folks can edit responses and comments any time, and threads can continue, even after a meeting has ‘closed’
  • If we have a recurring meeting and the previous day or instance is technically closed, folks could go back if they can still continue their conversations. I agree timezones are going to be tricky so locking things down seems counterintuitive
  • I want to re-engage folks with the conversations via in-app or chat notifications. They see a comment (a question, a reaction, etc.) they can go answer even if they were out 1 day but wanted to get back to it
• I think we should open one instance in advance, too, in case folks want to share early. E.g. I leave my Friday Ship response on Wednesday because I’m going to be out Thursday and Friday — I shouldn’t have to wait until the next one automatically opens, unless, that is we keep them open 1 in advance
• Opening and closing of recurring meetings should be automatic. The only thing to ‘close’ is to end the whole series. ‘End this recurring activity’

My real opinion is that a ‘recurring meeting’ is just one activity (i.e. meeting) and each ‘day’ instance is a phase, not a separate meeting. This is our standup, we answer e.g. M-T-T-F because Wed is focus day. We open 1 phase in advance of the current day, but I’m routed to the current day initially. Past days are completed phases.

I can go back to those conversations. We can pull stats at any time. If I end the recurring meeting, I think threads are threads and should stay alive, meaning I can edit responses, comments, and add new comments. If I do, folks should receive at least an in-app notification. If the conversations go stale naturally, we’re fine.

[mattkrick]

Terry & I discussed during our 1:1

Things we both agree on:

• The discussion thread should stay open forever
• Do we allow 1 meeting series to have 0 open meetings? e.g. a friday ship series open on thursday & closes EOD friday & then you can't add to the next one until next thursday. We say no! a Meeting series needs at least 1 instance

Remaining questions for @ackernaut

• Do we allow 1 meeting series to have 2+ open meetings? I say no, Terry may say differently. For me, this comes down to wording-- e.g. we ask when should a meeting close vs. when should it open. if a friday ship meeting ends at 5PM on friday, then the next opens at 5:01PM until the next friday

[ackernaut]

My notes after discussing with Matt:

• I think we were closer to being on the same page than I realized, it’s just sometimes we are describing these nuances in different ways
• Yes, discussion threads should stay alive, even for a closed, past instance — I had added that the initial response card should remain editable but Matt thinks that should not be editable, but more of a fixed artifact in time, like an Instagram photo. I’m okay making it not editable after the meeting is closed if it’s not too hard to change our mind later.
  • Question: I assume the response card should be editable as long as the meeting is open? To fix a typo, to go back and link something for clarity, etc.
• I agree that there should always be an instance open, and to help the lag time between instances we should open the next one early. E.g. We hold Mon, Tue, Thu, Fri async standups. When Tue closes, Thu opens. When Thu close, Fri opens. When Fri close, Mon opens.
  • I think this is my answer to the question about having 2+ meetings open. That we have 1 at a time. I think if we open the next one as soon as possible and we build the logic of when things close (you marked Friday Ship runs through Friday, the next one opens Saturday. This allows folks to answer up until when the next one closes, be it e.g. Wed ‘I’m out early’, Thu, or Fri)
  • I think for recurrence we just close by day (not a time) and pick a timezone (e.g. Fri, CT) to manage what a team across timezones considers the sort of center of their day (e.g. we meet on Tuesdays, what is Tuesday?) Does time help? Friday ship closes 5p PT?) I guess we could explore that. I’ll revisit this closer to designing recurrence.
  • I think notifications could be decoupled from when instances open and close. I mark Friday ship ends at the end of Friday. However, I add a reminder notification on Thursday morning and Friday morning.

[mattkrick]

I assume the response card should be editable as long as the meeting is open? To fix a typo, to go back and link something for clarity, etc.

👍

I think for recurrence we just close by day (not a time) and pick a timezone (e.g. Fri, CT)

Could we reuse the pattern for the meeting phase complete date/time picker? That pattern avoids timezones by just using local time & may look simpler to the user. Plus time may matter, e.g. if it's a standup, it closes by 10AM. if it's a friday ship, it closes by 4PM.

[ackernaut]

Re: time picker for ending each instance

Could we reuse the pattern for the meeting phase complete date/time picker?

Yeah, I’m simmering on that idea. I think it’s probably a good pattern, and will have an upcoming design task to further refine the details of recurrence. I can explore more there.