Technical Story: PT1-170

Context and Problem Statement¶

In Adding links to T&A, we accepted a quick, simple solution we all understood in order to keep things moving. We've not had a full discussion about the pros/cons of that decision in the medium term, so it's possible (and indeed likey) that this solution is not everyone's preference.

The problem we are solving here, therefore, is reaching agreement on the best way to model links which we can follow up in Headroom. This may also set a pattern for polymorphic joins in general (re: closed PR) which could for useful for future projects where some given entity (here Link) can relate to multiple other entities (here Engagement and Accommodation); for instance, the forthcoming comments project.

Decision Drivers ¶

• Increased space to reflect and update the current model (this is the opportunity to get it what we think is "right")
• Growing need to set a pattern for future polymorphic joins to reduce overhead in following projects

Considered Options¶

For the sake of brevity, I won't cover every previous option in detail again, as they're all available in the previous T&A Link ADR.

Options for DB modelling are:

• Status quo (i.e. table for every Linkable entity
• One table of links with a polymorphic join
• One table of links with join table(s)
• One table of links with a 'grouping' table

Options for API model are:

• Status quo (i.e. specific add/delete mutation per entity and one Link type with list on each linkable entity)
• One add/delete mutation for all links
• Add links as field in other linkable update mutations (i.e. editEngagement)

As always, we set a higher threshold for changing the status quo than sticking with it: i.e. the cost/benefit analysis is generally weighted towards the status quo as not making a decision, not learning something new and not refactoring massively reduces the cost. However, it's important to keep in mind that the status quo was decided very, very quickly and purely for the sake of shipping easily. One could also argue that being the solution that allowed us to get alignment and ship most easily is a great benefit. 🤷‍♀️

Decision Outcome¶

API¶

Maintain status quo

Positive Consequences ¶

• We do not yet have a decision on mutation return types and client-side caching (PT1-270) on which all other options make assumptions
• Does not require any complicated or exotic return type (e.g. if we add delete something to a Linkable do we get returned a Linkable? Is that union of Engagement and Accommodation?)
• Mutations are already inconsistent and need rationalising (i.e. Accommodation edits need brining into line with Engagement edits)
• As mentioned in previous ADR, this is (and remains) the easiest decision to reverse

Negative Consequences ¶

• Miss out on a change to rationalise schema and reduce overall number of mutations
• Creating a Linkable GraphQL type (in above rationalisation) would create a nice reflection of our mental model

DB¶

Change to one Link table with a single grouping table

i.e. one Link table, one Linkable (or similar) grouping table and linkableId foreign key on Accommodation + Engagement (the linkables) and Link

 Positive Consequences¶

• Reduce overall number of tables and simplify schema
  • There is clearly no difference between the links on different entities (they share UI components etc.)
  • We have enough perspective now to feel confident about this
• Feels like the best fit for our mental model, as we think of the Links problem (in terms of polymorphism) as composition rather than inheritance i.e. engagements and accommodations don't share a common ancestor: rather they both exhibit this Linkable feature which should be added as a mixin
  • Here there's a clear mixin (single Linkable table)
  • Foreign key on the linked entity's table makes explicit that it's demonstrating this behaviour
• Plays relatively nicely with Prisma (which doesn't understand a polymorphic join)
• Simple migration (insert links from current AccommodationLink table to Link table and grouping table; same for EngagementLink)
  • There will be some small changes required for the resolvers as demonstrated in the MVP
• Extensible solution as the grouping table has no foreign key constraints so multiple types of 'metadata' (e.g. links or comments) can join onto it

Negative Consequences¶

• Allows orphan links
• Some refactoring required (as noted above) in the resolvers
• Need to migrate data (although this is limited as we're making this decision before launch)
• Less familiar SQL pattern (engineers used to seeing typical join tables with multiple foreign keys rather than tables to be joined foreign keying to grouping table)
  • Possible that some ORMs might not play so nicely with this so could restrict choices a little in the future

Pros and Cons of the Options ¶

Most options are already discussed in Adding links to T&A.

Further comments and feedback can be found in its PR and that of the polymorphic joins (closed).

 MVPs¶

I've made three MVPs (cunningly avoiding collision with the word Link) showing how we can achieve certain candidate solutions in Prisma and our resolvers.

Two are for using multiple join tables which can be modeled either explicitly (declaring the join table) or implicitly (allowing Prisma to infer and create the join table) in a Prisma schema.

The third covers the new solution from below: using a single grouping table.

• Multiple join tables:
  • Explicit in Prisma schema
  • Implicit in Prisma schema
• One grouping table

DB: One grouping table¶

Add one e.g. Linkable table with just an id; all entities that can have Links will have a one-to-one relationship with that table with the foreign key on the linkable entity (e.g. engagement); the Links table has a standard many-to-one relationship with the Linkable table.

Distinct from using join table(s) as a join table connects tables by having a foreign key to each: in this model, the tables to be connected have a foreign key to the so called 'grouping' table. N.B. We refer to a grouping table rather than a join table, as engineers will likely assume 'join table' means the former.

An MVP of this solution is up on GitHub. An example Prisma model is below. Note that Prisma shows relationships it knows about reciprocally, so the Linkable model below has an Engagement? and and Accommodation?; nevertheless the only field on Linkable this model creates is id (the rest is what will be available in the client) as can be seen in the MVP.

model Link {
  id         String     @id @default(uuid()) @db.Uuid()
  url        String
  title      String
  Linkable   Linkable   @relation(fields: [linkableId], references: [id])
  linkableId String     @db.Uuid()
}

model Engagement {
  ...
  Linkable   Linkable   @relation(fields: [linkableId], references: [id])
  linkableId String     @db.Uuid()
}

model Accommodation {
  ...
  Linkable   Linkable   @relation(fields: [linkableId], references: [id])
  linkableId String     @db.Uuid()
}

model Linkable {
  id            String  @id @default(uuid()) @db.Uuid()
  links         Link[]
  Engagement    Engagement?
  Accommodation Accommodation?
}

• Good, because lowest number of tables without doing a polymorphic join
• Good, because by avoiding foreign key constraints on the grouping table it's extensible (e.g. could also join Comments onto it)
  • There's an argument this is also a bad as we might end up grouping lots of functionality together inappropriately or overuse this model and add it in situations where more familiar patterns (e.g. standard many-to-many) are more appropriate
• Good, because fits our mental model fairly well (there are things that can have links on them, so they have a linkableId; there is a table of things that are linkable including accommodations and engagements)
• Good, because doesn't totally blow Prisma's mind
• Bad, because less familiar pattern
• Bad, because allows orphan links

Links ¶

• Supersedes Adding links to T&A