Wipe deleted Workspace (+ their WorkspaceInstances) at some point

[geropl]

Motivation

• getting rid of old shapes/variance: our code is by now riddled with optional fields (like this: someField?: string) just because we once in 2018 had a period where we would not set those field. This is an attempt to get rid of those variance to at least have a chance to streamline the code
• dead weight: This is about removing old deleted workspaces only: It seems there is no sense in keeping them around
• reduce table size:
  • reduce migration times: running a migration on one of the big tables takes ~50mins by now, which does not go well with CD
  • reduce storage: This has become a concern, although not a problem, yet
  • query time: This is a nice benefit, although not a driver as we don't have problems here for now

Solutions

Add 3rd "purged" state

Currently our Workspace deletion model has two phases:

• soft deleted: A workspace goes into this state either when a) a user deletes it or b) it is garbage collected after a # of days of inactivity. The effect is that is "hidden" from our API, and can easily be reversed e.g. on user request, or inspected in case of abuse, for instance.
• content deleted: A workspace goes into this mode after a certain time being in "soft deleted" state. All workspace contents (backups, logs, etc.) is deleted, and the workspace record is scrubbed from all PII, but the skeleton and main data model stays intact. This is helpful for being able to trace back issues or incidents even after the actual workspace content has been deleted.

This issue proposes to introduce a 3rd state:

• purged: A workspace moves into this state after a certain amount of time being in "content deleted" state. All workspace entries (workspace, instance, prebuild) are completely wiped from the DB.

Remarks

Beyond the pure technical aspect, this affects all of downstream systems as well: We need to make sure that they can handle not getting the total view over all workspaces. 🧘

[atduarte]

Is the goal to reduce storage cost or optimize query times? If the latter, did we consider partitioning the table by year?

[geropl]

Is the goal to reduce storage cost or optimize query times? If the latter, did we consider partitioning the table by year?

There are several motivations. I updated and listed them above. ☝️ 🙏

[geropl]

Dropping assignment as I'm not actively working on this besides talks with downstream clients on how this might affect them and how to mitigate that.

[geropl]

Assigned to Milan after discussion here.

[easyCZ]

Re-opening as we'll want to follow-up with other db records, as well as lower the retention period.

[easyCZ]

We're currently not deleting Prebuilds for Workspaces which we're purging, this leaves zombie records.

We'll need to do 2 things:

• Start deleting the prebuild records in the GC loop
• Retrospectively find all prebuilds which don't have a WS and delete these

To actually start deleting prebuild records - PR, we will need the following:

• Add deleted column to d_b_prebuilt_workspace, d_b_prebuilt_workspace_updatable
• Enable db-sync for the above tables - Enable db-sync for d_b_prebuilt_workspace and d_b_prebuilt_workspace_updatable #13160
• Actually land the PR to start deleting them

[easyCZ]

We've now purged all Workspaces and Workspace Instances from 2 years ago and we've cought up against the current rate workspaces entering this purging window - dashboard.

#13234 switched purging to after 1 year from content deletion.

We're also now purging Prebuilds and related records. However, we'll need to identify records which did not get automatically cleaned up and delete these manually as well.