ADR: Soft deletes should cascade from namespaces -> attribute definitions -> attribute values
Taken from comment below #108 (comment)
Background
In our Policy Config table schema, we have a Foreign Key (FK) relationship from namespaces
to attribute definitions
, and another FK relationship from attribute definitions
to attribute values
. We have decided that due to the scenario above in the description of this issue, we want to rely on soft-deletes to avoid accidental or malicious creations of attributes/values in the place of their deleted counterparts.
If we were relying on hard deletes, we would be given certain benefits by the relational FK constraint when deleting so that we could either:
- cascade a delete from an attribute definition to its values, OR
- prevent deleting an attribute unless its associated values had been deleted first
These benefits of our schema and chosen DB would prevent unintended side effects and require thoughtful behavior on the part of platform admins. However, now that we are restricting hard deletes to dangerous/special rpc's and specific "superadmin-esque" functionalities for known dangerous mutations by adding active/inactive state to these three tables, we need to decide the cascading nature of soft deletes with inactive state.
Chosen Option:
Considered Options: Rely on PostgreSQL triggers on UPDATEs to state to cascade down
- Rely on PostgreSQL triggers on UPDATEs to state to cascade down
- Rely on the server's db layer to make DB queries that cascade the soft deletion down
- Allow INACTIVE namespaces with active attribute definitions/values, and INACTIVE definitions with ACTIVE namespaces and values
Option 1: Rely on PostgreSQL triggers on UPDATEs to state to cascade down
Postgres triggers allow us to define the cascade behavior as the platform maintainers. Keeping the functionality within Postgres and not the server has additional benefits.
- ๐ฉ Good, because cascading behavior of inactive state makes the most sense when the user intention is to delete (which is still going to be a relatively dangerous mutation)
- ๐ฉ Good, because keeping the cascade in the DB is always going to be more optimal than multiple queries
- ๐ฉ Good, because we are indexing on the
state
column in the three tables for speed of lookup/update
- ๐ฉ Good, because it has already been proven out with an integration test for repeatability in this branch
-
- ๐ฉ Good, because this does not block any superadmin/dangerous/special deletion capability and will be fully distinct from any cascade/constraint handling there
- ๐จ Neutral, because triggers are a Postgres feature, but we haven't made any firm decisions yet about what other SQL databases/versions we'll support or if we'll require customers to use the latest PostgreSQL
- ๐ฅ Bad, because it's a less well-known feature of Postgres
- ๐ฅ Bad, because we will only be able to ALWAYS cascade the INACTIVE UPDATE down the tree and will not get the foreign key constraint of a one-off deletion if that's what the user really intended. We'll need to make it clear to them what their change will do.
Option 2: Rely on the server's db layer to make DB queries that cascade the soft deletion down
The same as option 1, but with the cascading logic put into server-driven queries and not Postgres triggers.
- ๐ฉ Good, because it does not tie us to any Postgres-specific feature and can be reused across SQL db's
- ๐ฉ Good, because of all the other good benefits of option 1
- ๐ฅ Bad, because performance: anything being soft deleted will mean multiple round trips
- ๐ฅ Bad, because more room for bugs: anything being soft deleted will mean multiple queries
- ๐ฅ Bad, because we can more easily end up in a bad state where the server fails or a secondary/tertiary query fails but the first succeeded
Option 3. Allow INACTIVE namespaces with active attribute definitions/values, and INACTIVE definitions with ACTIVE namespaces and values
- ๐ฉ Good, because it gives maximum control to the user
- ๐ฅ Bad, because that maximized control is actually more confusing
- ๐ฅ Bad, because is most likely to cause a bad state where access is not allowed but an unknown reason
- ๐ฅ Bad, because it is unintuitive from an Engineering/maintenance perspective
As a platform maintainer, I want to make sure that data which is deleted is soft-deleted so that I can prevent dangerous side effects and restore accidental deletes.
There are situations where the side effect of a delete could result in data leak if two admins are maintaining the platform. Example:
- Admin A adds attribute
demo.com/attr/Classification/value/TopTopSecret
- Creates subject mapping with Deep Secret Spy
- User A creates TDF
SecretSpy-SecretSantas-MailingList.csv.tdf
with demo.com/attr/Classification/value/TopTopSecret
- Admin A deletes attribute
demo.com/attr/Classification/value/TopTopSecret
- Admin B add attribute
demo.com/attr/Classification/value/TopTopSecret
- and creates subject mapping with Top Secret Toy Inventor of Tops
- User B with Top Secret Toy Inventor of Tops subject attribute accesses
SecretSpy-SecretSantas-MailingList.csv.tdf
The soft-delete feature will prevent the recreation of the attribute with the same name on the same namespace.
Acceptance Criteria