Best way to have an effective "relationship" field to individual items of an array field? (PostgreSQL)

[cpolish]

Hi there,

I was looking to see whether something like this was possible for a current project I am working on, but haven't yet found an ideal way to do this in Payload CMS.

The project I am currently working on essentially allows admins to define individual "exercises" – almost acting as if they are surveys, in order for users to respond to. To accomplish this, I define a collection for each exercise type. Each exercise may be composed of various fields – but of course, the data required for each field depends on the exercise. I also need to be able to store responses to these exercises in my database – naturally, being in the Payload ecosystem, defining a collection type for responses to particular exercise types seemed like the natural way to approach this. I need to be able to define user responses to each individual field of the exercise – ideally with referential integrity for each field, so that we store a response for every particular field of the exercise.

If I was to define such a schema in SQL for an example exercise and corresponding response type, I would probably do so like this:

CREATE TABLE free_text_exercise (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

CREATE TABLE free_text_exercise_field (
    id INTEGER PRIMARY KEY,
    exercise_id INTEGER NOT NULL REFERENCES free_text_exercise(id),
    order INTEGER NOT NULL,
    label VARCHAR(255) NOT NULL,
    description TEXT,
    CONSTRAINT unique_exercise_item_order UNIQUE (exercise_id, order)
);

CREATE TABLE free_text_response (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES user(id), -- Assume there's a `user` table
    exercise_id INTEGER NOT NULL REFERENCES free_text_exercise(id),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE free_text_response_item (
    id INTEGER PRIMARY KEY,
    response_id INTEGER NOT NULL REFERENCES free_text_response(id),
    field_id INTEGER NOT NULL REFERENCES free_text_exercise_field(id),
    text_response TEXT NOT NULL
);

The trouble is: I don't know how to best map such a schema to Payload's ecosystem (when using the Postgres adapter):

1. Ideally: I would be able to use an array field in my exercise collection types to define the data necessary for each field (in the example above, a label and optional description), then in my response collection type, I could use a relationship field to directly relate to an array field item.
   • The trouble is: Payload's relationship fields are only used for relationships to collections – you can't relate them to array fields, even though array fields, with the Postgres adapter, would define their own tables in the underlying database to support array field items.
2. I found this example repo from Payload themselves: payloadcms/ordered-join, however:
   • by default, I was unable to contain a brand new collection with the "ordered join" field with items defined in the ordered join field (resulted in some errors) – I would have to make some tweaks to some of the hook logic to support this.
     • this probably would be OK for a small number of collections, but I have quite a few variants of exercise collections I would need to define, which could mean a lot of duplicate logic I would have to write, or I would have to write some complex code to support DRY and such an "ordered join" feature across various collections.
   • the repo is a bit older and hasn't been updated (but perhaps it is not an issue?)
3. Potentially, I could define the entire schema for the app from scratch using Drizzle. I was thinking something along the lines of this discussion: Custom Fields with custom data types #6922, where a table is defined with Drizzle, and the Payload collection sits on top of this to allow for editing its underlying data via an admin interface:
   • This would probably be great for me – since I could control the schema how I'd want, however...
   • ...I would lose out on automatic pushes to the database schema (push: false), which would be a shame, since this is one of the reasons I really would like to use Payload – simplifying the development process of a schema which can change a lot during development without needing to fall back too much on complex migrations.

So as of now, I'm a bit unsure of how to use Payload to achieve this use case that I would like (with the Postgres adapter). Would anyone potentially have some ideas? Or has anyone had any similar scenarios when using Payload, and if so, how did you go about solving it?

Thanks so much!