[Sprint 6c]: Deleting/Modifying Data for a Year & Gateway Schools 🗓️ - Anne & Zander

[danglorioso]

💻 Summary

In this sprint, you will modify the settings page and upload flows to allow for the viewing and deletion of data from previous years. You will also introduce gateway Schools into the project by associating a school with a boolean indicating if it is a gateway or not and making this configurable via the settings page.

Goals of this sprint:

• Allow data to be deleted from the settings page.
• If data exists for a given year when uploading, delete all of it and then overwrite it.
• Annotate each school in the database with a field indicating whether it is a gateway school or not.

⚙️ Setup

Before starting:

1. PLEASE: **Pull the latest changes from main: git pull
2. Create a new branch for this sprint.
3. Install dependencies.
4. Run the dev server and verify the site builds.
5. Confirm everything runs.
   If you experience problems, check your .env is up to date!

🧭 Implementation

Step 1: Modify the settings page according to the Figma

• Create a new section on the settings page that displays years and whether or not they have data.
• The range of years should be from the first year data was uploaded to the most recent year it was uploaded. All years in this range should be shown along with an indicator of whether or not they have data. A nice way to test this is to add data for a year from a while ago and ensure that all years in the range show up. This would also be a good test for responsiveness.
• There should also be an option for each year to delete data. When this button is clicked, a confirmation popup (implemented as a Dialog box from shadcn) should appear that ensures that you actually want to delete the data. More on what data to delete below.

Step 2: What data to delete?

• We have 5 tables in the database for our schema: schools, teachers, projects, yearly_school_participation, and yearly_teacher_participation.
• schools and teachers contain metadata that does not change year to year. This data should not be modified.
• projects, yearly_school_participation, and yearly_teacher_participation contain yearly data. Each table has a year field. You will want to delete the entries for the year that is selected on the settings page.
• To delete data, create a new backend API endpoint (HTTP DELETE function) that accepts a year as a parameter and executes a DELETE query on the three aforementioned tables for data matching that year.

Step 3: Modifying upload flow to include data override

• As of right now, we do not handle the case in which data is re-uploaded for a given year, so it likely causes problems in our database and invalidates the accuracy of our data.
• To handle this, we will override data from the previous year by deleting it if it exists in the database before uploading the new data.
• Thus, in the upload flow, once the user confirms that they would like to upload data in the confirmation page, you should first check if data exists for that year. If it does, you can use the same API endpoint that you made in the previous step to delete that yearly data. Then, the remaining upload flow should be unchanged.
• Ensure that you modularize your code well for this step since there are many teams working on different parts of the upload flow.

Step 4: Test your deletion logic

• You will certainly need to test your deletion logic to ensure that it works properly. Make sure that you do this on a separate Neon branch.
• Ensure that, after deleting data for a year, the corresponding indicators update and there is no data in the database for that year. This can be confirmed on Neon.
• You will want to test your data override on upload in depth as well. Ensure that data for the given year is actually being deleted and then replaced. As long as you use the same API endpoint as step 2, this should not be too difficult. One way to confirm this is that if you upload a spreadsheet for a given year, uploading the same spreadsheet should cause no change in the data. The only change that should occur is changes to the row ID’s in the database. The rest of the data should be intact.

Step 5: Add gateway schools to database

• The last steps of this ticket are separate from the previous parts.
• Currently, there is no field in the schools table of the database that stores whether a school is a gateway school (a gateway school is a school that receives additional support to act as a gateway to the MHD competition).
• In this part, you will modify the schema.ts file to have an additional boolean field that indicates whether a school is a gateway school (true) or not (false).
• You will also modify the upload flow such that, when a school is added to the database, its boolean field is given the value of false (no school is a gateway school on upload). This should be very easy and not much code to implement.
• Ensure that each school has a value of false by checking the data tables on Neon

Step 6: Allow users to make schools gateway schools via settings page

• Currently, the settings page has a dummy dropdown for gateway schools that does not write any information to the database when enabling/disabling a school as a gateway.
• In this part of the ticket, you will replace this dropdown with a dropdown of all the schools in the database. Make this list of schools in the dropdown searchable (see filter by section of charts page and click on schools filter to see what we are looking for)
• When one of these schools is clicked, it should be added to the data table that appears after the dropdown. For the data table, make sure to use shadcn’s data table. This update must also be written to the database for that school by setting the gateway school field to true.
• In the data table, when the button to remove a gateway school is pressed, it should be removed from the table and the corresponding entry in the database should be changed to false.

Step 7: Filter by and Group by gateway schools on charts

• Lastly, you should update the filters on the chart to allow for grouping by and filtering by gateway schools.
• This should be implemented in a very similar way to the existing group by and filter by options, so study their implementations to ensure that you understand what is being asked of you.

🙌 Acceptance Criteria

• Schools are not a gateway schools (FALSE) by default
• User can select and search for gateway schools in the dropdown of all schools, database is updated accordingly
• User can delete gateway school from data table by clicking delete button
• Filter by gateway schools displays just data relevant to the gateway schools
• Group by gateway schools displays data across gateway schools vs non-gateway schools
• UI matches Figma exactly
• Code runs locally without errors and builds successfully
• Merge via PR

⁉️ Questions

If you get stuck or have questions:

• DM Dan or Shayne on Slack
• Come to office hours or ask during a hack night
• Ask questions during team meetings – we’re here to help!

📝 Resources

• Figma:
• Tailwind CSS
• shadcn/ui
• npm package manager