Tickets project

[danieljames-dj]

Tickets project is a project that is aimed to automate/simplify works done by WCA staffs. The following is the database architecture of the MVP:

This Github issue is to discuss regarding this design and the plan of implementation.

[gregorbg]

Some initial thoughts:

• Stakeholders should have an is_active flag. It might be that someone initially takes charge of a ticket and then later a second person comes in and reviews the work. Or maybe some T/C initially received the ticket, but then noticed that another T/C is more suitable to handle it, so the stakeholder "responsibility" gets transfered.
• comments should have both a stakeholder ID and an actual user ID. Since only a user can make a comment, it is important to record that user and then display it as "comment by user ID 1234 on behalf of stakeholder gorup WCAT" or something
• status should probably have more entries. Things that come to mind immediately are rejected and no_action. But this can be easily implemented later down the road and is not an inherent design flaw.
  • On that note, I'm not sure how much we can even standardise the status field at all? If we want to use a ticketing system throughout the website, then various teams will have various workflows with wildly different statuses. Some tickets for some workflows might need a pending_review status, others might not.
  • In line with this thought, what purpose does the general status column serve at all? If different workflows for different tickets can have wildly different status requirements, why even try to unify them?
    • Maybe the status flag should be moved to the metadata objects like ticket_wst_contacts or tickets_edit_person_name?
    • Maybe we should consider introducing different terminology for the "raw, technical status" of a ticket (is it open? is it closed?) and the "internal business logic status" (i.e. steps that depend on the actual type of the ticket, where WRT anonymizations have different "status" steps from WCAT competition announcements). Calling both of them "status" will be hugely confusing
• We definitely need a separate table keeping track of changes to tickets. Something along the lines of ticket_history. The MVP would be to record changes in ticket status field -- whenever the status changes, there has to be an entry to match this status change with information on who made this status change and why
  • This will end up being a very similar structure to ticket_comments. Essentially, a change log entry is just a "glorified comment". So maybe think about a clever way of reusing these tables. Maybe just have the change log entry point to a comment instead of storing its own comments?
  • How can we log changes to "custom status" changes (i.e. team-specific status flags) that I mentioned in the last paragraph?

[danieljames-dj]

Thanks a lot, @gregorbg for your detailed review. I'll answer to your questions/thoughts by quoting them:

1. is_active flag

Stakeholders should have an is_active flag [...]

I agree with this change. I actually thought of doing this with connection. The active stakeholder will have this value as assigned and once somebody new gets this assigned, then the previous assignee's status will get moved to cc. I really don't remember why I had hidden. But if I decide not to have hidden, then there will be just two - assigned and cc, so instead of this enum, as you said is_active will be better. I'll make that change.

2. General status field or status field within metadata table

status should probably have more entries. [...]

If there is a status field within each metadata table, then definitely that is better as each ticket types can have their own custom tickets. I'll make that change.

3. "internal business logic status"

[...] and the "internal business logic status" (i.e. steps that depend on the actual type of the ticket [...]

This may not be applicable to all ticket types, but for whichever ticket types it is needed, we can add them under a stage field.

4. Comments and logs

comments should have both a stakeholder ID and an actual user ID [...]

I already had this in my mind and was planning to reuse tickets_comments for the same. My plan was to add a wcabot dummy user as a stakeholder and use that stakeholder to comment the logs. I hope this is fine.

5. Logs for status change

How can we log changes to "custom status" changes (i.e. team-specific status flags) that I mentioned in the last paragraph?

My plan is to add a comment by bot saying "Status is changed from x to y".

6. Stakeholder ID and user ID in comments

comments should have both a stakeholder ID and an actual user ID [...]

Agreed with this. My initial plan was that whenever a user adds a comment, add them as a stakeholder. But it is more complex, instead having comment_by_user_id field in comments table is more better.

Updated design

As an MVP, I'm planning to start with tickets_anonymizations and following updated design considers just that subtable for now.

Following are the plan of anonymization:

1. Whenever somebody requests for anonymization through contact form, send the following email to the user with WRT in cc:

Subject: WCA Ticket #123
Body:

Hi,

We received anonymization request for your details in WCA profile. WCA Results Team will work on this request soon.

@WCA Results Team: You can proceed this request in https://...../ticket/123.

2. Along with sending the above email, a ticket will also get automatically created and first comment will be the following by wcabot: "Received anonymization request from user with user ID x. The anonymization request is "account only" or "account & profile".

If the request is "account only", there will be a button "Click here to anonymize the account" which when clicked will anonymize the account.

If the request contains profile, provide the link to anonymize from the existing script.

3. Once the requested actions are completed, click the "mark as completed" button which will change the status to marked_as_completed.

Note that this is an MVP and this ticket will definitely have more features and more status. Please let me know if the MVP and other comments looks good to you.

[gregorbg]

Re is_active: I don't think this should be merged with the connection enum. The latter enum in my mind is something like "Is this person the main responsible? Should this person only be in the loop passively? Is this person a witness in the case? Is this person a reviewer?". The is_active flag should express whether that connection, no matter what value you choose, is still active or not. It may be interesting to see that this person was at some point in time the main responsible, but isn't anymore.
Now I'm thinking whether we should even go a step further, and add a previous_stakeholder_id column, which is nullable but may optionally point to which stakeholder you "inherited" from...?

[gregorbg]

Re comments and logs: No, definitely no dummy user. Absolutely not. Only logged in users can make comments, and these users have IDs. Store them please.

In most cases, the stakeholder will be a user, and it may seem "redundant" when there is an additional "comment written by user ID" column. But if the stakeholder is a group, suddenly it's very interesting which user ID specifically wrote the comment.

[danieljames-dj]

Thanks @gregorbg for your review. I agree with all your points except previous_stakeholder_id which I didn't completely understand, or rather I couldn't find any good use-case where that can be helpful. I might have understood it wrongly, but I feel not having it can make the design simple and straight forward. What do you think?

So finally there are mainly 4 tables for tables:

1. tickets
2. ticket_comments
3. ticket_logs
4. ticket_stakeholders
   Apart from above 4 there will be one table for each ticket type like tickets_edit_person_name, tickets_anonymize_profile, etc.

The 4 main tables' structure will be as follows (since there is no major change from the previous schema diagram (other than adding new table ticket_logs and few new columns), I'm writing it down now):

1. tickets
   • id (int)
   • name (string)
   • type (enum)
   • created_by (ticket_stakeholder)
2. ticket_comments
   • id (int)
   • ticket_id (tickets)
   • comment (string)
   • comment_by_stakeholder_id (ticket_stakeholder) - the stakeholder can either be the user directly or can be a user_group like WRT, WST, etc.
   • comment_by_user_id (users) - the comment can be sent only by logged in user, so this field will store the user ID
3. ticket_logs
   • id (int)
   • ticket_id (tickets)
   • log (string)
4. ticket_stakeholders
   • id (int)
   • ticket_id (tickets)
   • stakeholder_id (int) - user ID or user_group ID
   • stakeholder_type (enum) - user or user_group
   • connection (enum) - initially only 'assigned' and 'cc', later maybe we can add new types of connections if needed
   • is_active (bool) - whether the stakeholder is still relevant to the ticket

Do you agree with this design? If so, can we consider this as final design? If yes, I can share my action plan next.

[danieljames-dj]

Added the architecture diagram for easy review.

[gregorbg]

Thank you! Please turn around the arrow between tickets and anonymizations. In other words, the "general" ticket should be the one that points to its metadata, just the same way that groups currently point to their metadata. Otherwise LGTM :)

[danieljames-dj]

Thanks @gregorbg, I'm attaching the updated diagram. My plan is to start with tickets for edit_person, so have added table for that:

I'm planning to do this in different phases and in the first phase, I'm targeting to build an MVP which won't harm WRT in any way (like I won't give any extra work for WRT with this change)

Also, in the first phase, I'll be skipping ticket_comments, I'll be doing it in phase 2.

The structure of tickets page will be something like:

Except ticket playground, everything should be straightforward. By ticket playground I mean the area where the stakeholders can do some action. There can be cases where the playground can be empty as well. For example, in case of 'edit person tickets', for WRT members, the playground will have the form to make the change and the changes requested, while if the requester opens the ticket page, they won't have any playground so that area will be empty.

User journey:

1. User requests for editing their profile details using contact form
2. In the backend, along with sending the usual email, a ticket will also be created.
3. In the mail that is sent, we will have a line like "you can edit the details using this link" and the link will be of the ticket page.
4. In the ticket page, the playground for WRT will be having two components: first component to show what are the changes requested, and second component to show the form where they can make the change there itself.
5. Once the WRT makes the change, the ticket will get closed.

(Passively, whenever some action is made in tickets, a log entry will also be created)

There are many more features that is related to tickets which are not covered in phase 1, some of them are:

1. When a ticket is created, "ticket created" email will be sent, similarly "ticket closed" email will be sent when query is resolved.
2. A listing page will be there where WRT can see open tickets. Similarly users will also have a page where they can see their tickets.
3. Currently the ticket page has the contact form, but it will be replaced with proper one-button click actions making it more comfortable for WRT.
4. There will be option to add new stakeholders and maybe even remove stakeholders.