# Data architecture for custom fields of type "**hierarchy"**

## Context and Problem Statement

What is the data architecture for serving a hierarchy of labels with associated metadata to an OpenProject custom field implementation?

## Decision Drivers

* The data architecture needs to structure labels in a hierarchical way (like a tree), where each label has associated metadata.

* The structure can change at any point in time.

* Begin of the insertion~~The structure must be recreated at any historic point in time.~~

* apparently not wanted/needed

* effort too high for gained benefit

* End of the insertion Changes to the structure need to be recorded throughout the life-time.

* The data architecture must be capable to be used for filtering based on given labels.

* When the hierarchical structure changes, it must be possible to update pointers to it (the custom field).

* When the hierarchical structure changes, it must be possible to to let pointers point to "older" versions of the structure.

* Changes to the structure must be auditable.

* Labels of the same level in the structure must maintain an order, which is manually Begin of the insertionchangeable.End of the insertion Begin of the deletion changable.End of the deletion


## Considered Options

* Single Table with always extending tree (with soft-deletes)

* Single Table with out ID pointer

* Single Table with ID pointer

* Begin of the insertion~~ltreeEnd of the insertion Begin of the deletion ltreeEnd of the deletion in Begin of the insertionPostgreSQL~~End of the insertion Begin of the deletion PostgreSQLEnd of the deletion

* Begin of the insertion~~RealEnd of the insertion Begin of the deletion RealEnd of the deletion graph Begin of the insertiondatabase~~End of the insertion Begin of the deletion databaseEnd of the deletion

* Event Sourcing


## Decision Outcome

Chosen option: "{title of option 1}", because {justification. e.g., only option, which meets k.o. criterion decision driver | which resolves force {force} | … | comes out best (see below)}.

### Consequences

* Good, because {positive consequence, e.g., improvement of one or more desired qualities, …}

* Bad, because {negative consequence, e.g., compromising one or more desired qualities, …}

* …


### Confirmation

{Describe how the implementation of/compliance with the ADR is confirmed. E.g., by a review or an ArchUnit test. Although we classify this element as optional, it is included in most ADRs.}

## Pros and Cons of the Options

### Single Table with always extending tree (with soft-deletes)

Begin of the insertionIf a user deletes a node,End of the insertion Begin of the deletion Whenever things are changed inEnd of the deletion the Begin of the insertionnodeEnd of the insertion Begin of the deletion tree, the tree is appendedEnd of the deletion and Begin of the insertionit's children areEnd of the insertion Begin of the deletion the now out-of-date pate isEnd of the deletion marked Begin of the insertionas `deprecated`/`deleted` and not deleted.End of the insertion Begin of the deletion "deleted"/"deprecated".End of the deletion

Begin of the insertionIf a user updates a node (change label and short, change parent, change order), custom values keep references.

Changes are recorded in a logging table.

End of the insertion * Good, because Begin of the insertionstructure is relatively simple to create. (with gems)

* Good, becauseEnd of the insertion historic Begin of the insertionchanges are comprehensible.

* Neutral, logging mustEnd of the insertion Begin of the deletion trees can stillEnd of the deletion be Begin of the insertionimplemented manually. (papertrail doesn't work for whole tables)End of the insertion Begin of the deletion obtained.End of the deletion

* Begin of the insertionNeutral, readEnd of the insertion Begin of the deletion Bad, because size andEnd of the deletion performance of large trees Begin of the insertioncanEnd of the insertion Begin of the deletion needs toEnd of the deletion be Begin of the insertionpoor.

* Bad, because tree grows with every change made.End of the insertion Begin of the deletion performant (custom indexing or lookup tables might help to reduce tree)End of the deletion


### Single Table with out ID pointer

Begin of the insertionTable only shows current tree.

End of the insertion Whenever a custom Begin of the insertionvalueEnd of the insertion Begin of the deletion fieldEnd of the deletion is set on a work package, a distinctive string is set as it's value.

* Good, because "old" assigned labels are not changed when the tree is updated.

* Good, because performance is adequate.

* Bad, because filtering on old trees not easily doable. Begin of the insertion

* Bad, because losing ability to update custom values on tree changes.End of the insertion


### Single Table with ID pointer

`id` | `name` | `short` | `parent_id` | (`child_ids`)

Begin of the insertionTable only shows current tree.

End of the insertion Using a single table to hold the hierarchical structures. (closure tree Begin of the insertiongem).End of the insertion Begin of the deletion gem)End of the deletion

* Good, because simple implementation (Work packages and Project do this Begin of the insertionalready).End of the insertion Begin of the deletion already)End of the deletion

* Good, because speed is not a big Begin of the insertionconcern.End of the insertion Begin of the deletion concernEnd of the deletion

* Bad, because having historical hierarchies is very hard to do (maybe copies of whole table parts, or: [https://wiki.postgresql.org/wiki/Temporal\_Extensions](https://wiki.postgresql.org/wiki/Temporal_Extensions)) Begin of the insertion

* Bad, because custom values can not have persistent values (see current list custom field implementation).End of the insertion


### Begin of the insertion~~ltreeEnd of the insertion Begin of the deletion ltreeEnd of the deletion in Begin of the insertionPostgreSQL~~End of the insertion Begin of the deletion PostgreSQLEnd of the deletion

Begin of the insertion`~~ltree~~` ~~isEnd of the insertion Begin of the deletion `ltree` isEnd of the deletion a method to have some tooling in PostgresSQL to query hierarchical Begin of the insertionstructures:~~ [~~https://www.postgresql.org/docs/current/ltree.html~~](https://www.postgresql.org/docs/current/ltree.html)End of the insertion Begin of the deletion structures: [https://www.postgresql.org/docs/current/ltree.html](https://www.postgresql.org/docs/current/ltree.html)End of the deletion

Begin of the insertion`~~root.parent.child.*~~`End of the insertion Begin of the deletion `root.parent.child.*`End of the deletion

* Begin of the insertion~~Good,End of the insertion Begin of the deletion Good,End of the deletion because query language already Begin of the insertionthere~~End of the insertion Begin of the deletion thereEnd of the deletion

* Begin of the insertion~~Good,End of the insertion Begin of the deletion Good,End of the deletion becuase speed is not a Begin of the insertionconcern~~End of the insertion Begin of the deletion concernEnd of the deletion

* Begin of the insertion~~Bad,End of the insertion Begin of the deletion Bad,End of the deletion because metadata Begin of the insertionlike~~ `~~short~~` ~~needsEnd of the insertion Begin of the deletion like `short` needsEnd of the deletion to be encoded into the Begin of the insertionlabels~~End of the insertion Begin of the deletion labelsEnd of the deletion

* Begin of the insertion~~Bad,End of the insertion Begin of the deletion Bad,End of the deletion because no historic data per Begin of the insertiondefault~~End of the insertion Begin of the deletion defaultEnd of the deletion


### Begin of the insertion~~RealEnd of the insertion Begin of the deletion RealEnd of the deletion graph Begin of the insertiondatabase~~End of the insertion Begin of the deletion databaseEnd of the deletion

Begin of the insertion~~UsingEnd of the insertion Begin of the deletion UsingEnd of the deletion a real graph database would give us most the flexibilities needed: querying, Begin of the insertionmetadata~~End of the insertion Begin of the deletion metadataEnd of the deletion

* Begin of the insertion~~Good,End of the insertion Begin of the deletion Good,End of the deletion because it fits the tree as graph representation Begin of the insertionnaturally~~End of the insertion Begin of the deletion naturallyEnd of the deletion

* Begin of the insertion~~Good,End of the insertion Begin of the deletion Good,End of the deletion because Begin of the insertionperformance~~End of the insertion Begin of the deletion performanceEnd of the deletion

* Begin of the insertion~~Bad,End of the insertion Begin of the deletion Bad,End of the deletion because we would need another running database just for Begin of the insertionthis~~End of the insertion Begin of the deletion thisEnd of the deletion

* Begin of the insertion~~Bad,End of the insertion Begin of the deletion Bad,End of the deletion because no historic data per default (maybe with Begin of the insertionsnapshots)~~End of the insertion Begin of the deletion snapshots)End of the deletion


### Event sourced structure

With Event Sourcing we wouldn't store complete trees in a table but rather record events that Begin of the insertiondescribeEnd of the insertion Begin of the deletion discribeEnd of the deletion the changes made to a tree.

In PostgresSQL we would have a table having a Begin of the insertionstructureEnd of the insertion Begin of the deletion strcutureEnd of the deletion like: `id` | `tree_id` | `event_type` | `sequence_number` | `timestamp` | `data`.

From that table we could recreate any historical tree at any point in time. To speed things up, we would need to introduce certain read models.

* Good, Begin of the insertionbecauseEnd of the insertion Begin of the deletion becuaseEnd of the deletion it's the most flexible concept Begin of the insertionthat covers all decision drivers.End of the insertion

* Good, Begin of the insertionbecauseEnd of the insertion Begin of the deletion becuaseEnd of the deletion it has historic data Begin of the insertionbuilt-inEnd of the insertion Begin of the deletion build itEnd of the deletion by Begin of the insertiondefault.End of the insertion Begin of the deletion defaultEnd of the deletion

* Neutral, because performance might be a concern, but can be mitigated with the use of read and write Begin of the insertionmodels.End of the insertion Begin of the deletion modelsEnd of the deletion

* Bad, because it's very complex to Begin of the insertionimplement.End of the insertion Begin of the deletion implementEnd of the deletion


## More Information

{You might want to provide additional evidence/confidence for the decision outcome here and/or document the team agreement on the decision and/or define when/how this decision the decision should be realized and if/when it should be re-visited. Links to other decisions and resources might appear here as well.}