Add `is_deleted` column to Moped <> Data Tracker sync

mddilley commented 1 month ago

When we synchronize project data between the Moped database and the Data Tracker projects table, we don't consider whether projects are deleted or not (is_deleted = true). This is limiting the ability of the Data Tracker projects table rows to populate tables and dropdowns in Data Tracker with active (non-deleted) projects only. It is also causing links to appear in Data Tracker that point to deleted projects.

To improve this, we should add the is_deleted column to the data sync so that deleted projects can be filtered out on the Knack builder side like we do in the Moped UI.

Examples of links to soft deleted projects can be found through this link.

We should:

Coordinate with the Apps team to add the boolean is_deleted column to the projects table of Data Tracker (object_201) AND the latest test copy of the app - Builder link - ask Christina
Update the Moped <> Data Tracker sync ETL to populate this new Knack column with the value from the Moped database
Backfill the values for projects in the projects table of Data Tracker (object_201)
- Here is the query in the ETL code that is used in combination with the Airflow last successful run date to determine what to update
- Or, ask someone on the Knack team to import a Moped export with the is_deleted values included

mddilley commented 1 month ago

@ChristinaTremel feel free to edit the issue description if there is anything that i missed. thanks for bringing this to the sync the other day! 🙏

ChristinaTremel commented 1 month ago

I just reminded myself about issue #18353 that was submitted by Ivonne a couple weeks ago and is directly tied to this issue! I'm going to close out 18353 and note that you researched and are going to address this issue with the is_deleted column. And I already let Ivonne know that we're working on it!

ChristinaTremel commented 2 weeks ago

Added Is Deleted boolean field to the projects object in Knack! ✨

mddilley commented 2 weeks ago

@ChristinaTremel Thank you!!!! could this be added to the latest test app as well?

ChristinaTremel commented 2 weeks ago

Added the field to the projects object in the TEST | Austin Transportation Data Tracker | 29 JUL 2024 app!

cityofaustin / atd-data-tech

Add `is_deleted` column to Moped <> Data Tracker sync #18577