Comment by pdimitar

Writing out the constraints. The starting point was an admin_notes table to start with, where each note initially had the same fields; we later added different categories of notes, where two of the categories had fields that were only meaningful for that category. What I did was add nullable columns for all of the category-specific fields to the table, with constraints to make the category-specific columns only have values for rows of the appropriate category. The type looked roughly like this (using Rust syntax) [1]:

  struct AdminNote {
      id: String,
      text: String,
      category: CategorySpecificData,
  }

  enum CategorySpecificData {
      GeneralRequest,
      InitialRequestForm {
          appliesToBasicRequestDetails: bool,
          appliesToSubjectAreas: bool,
          appliesToAttendees: bool,
      },
      SupportingDocuments,
      ConsultSession,
      AdviceLetter {
          appliesToMeetingSummary: bool,
          appliesToNextSteps: bool,
      },
  }

Setting up the constraints took about 30 lines of SQL just for those couple of boolean fields on some of the categories, you can see the details here [2].

As far as making this a Postgres extension - I'm not sure how useful it would be when the application language doesn't have a notion of sum types. Thinking about it, what might be more useful would be a language-specific library for data validation/constraints that sets up the database constraints as well. I'm not sure, though.

[1] We weren't using Rust, this was in Go, but I figured this was the most succinct way to summarize it. Our GraphQL schema for this type was basically the equivalent of this.

[2] https://github.com/CMSgov/easi-app/blob/main/migrations/V164...