Establishing and manage relations within lifecycles and controllers in strapi js

Question

I'm working on implementing a feature in Strapi where, upon creating a message, message statuses are automatically created for all users in the system. However, I'm facing difficulties establishing the necessary relationships in the lifecycle methods, which results in internal server errors when attempting to create messages in the admin panel.

I have two collection types: Message and MessageStatus. The schema definitions and lifecycle methods for these collections are attached. My goal is to ensure that when a message is created, corresponding message statuses are generated for each user. However, I'm encountering issues with setting up these relationships correctly, leading to the errors mentioned.

Could you please help me understand how to properly configure these relationships when creating the message statuses in the afterCreate lifecycle method in the message collection type.

Below I share the collection types:

1. MessageStatus schema

{
  "kind": "collectionType",
  "collectionName": "message_statuses",
  "info": {
    "singularName": "message-status",
    "pluralName": "message-statuses",
    "displayName": "Message Status",
    "description": ""
  },
  "options": {
    "draftAndPublish": false
  },
  "pluginOptions": {},
  "attributes": {
    "status": {
      "type": "enumeration",
      "enum": [
        "pending",
        "sent",
        "read",
        "delivered",
        "failed",
        "edited",
        "deleted"
      ],
      "default": "pending"
    },
    "message": {
      "type": "relation",
      "relation": "manyToOne",
      "target": "api::message.message",
      "inversedBy": "receiptDetails"
    },
    "user": {
      "type": "relation",
      "relation": "oneToOne",
      "target": "plugin::users-permissions.user"
    }
  }
}

2. Message schema

{
  "kind": "collectionType",
  "collectionName": "messages",
  "info": {
    "singularName": "message",
    "pluralName": "messages",
    "displayName": "Message",
    "description": ""
  },
  "options": {
    "draftAndPublish": false
  },
  "pluginOptions": {},
  "attributes": {
    "content": {
      "type": "text"
    },
    "sender": {
      "type": "relation",
      "relation": "oneToOne",
      "target": "plugin::users-permissions.user"
    },
    "replyTo": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::message.message",
      "mappedBy": "replies"
    },
    "replies": {
      "type": "relation",
      "relation": "manyToOne",
      "target": "api::message.message",
      "inversedBy": "replyTo"
    },
    "receiptDetails": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::message-status.message-status",
      "mappedBy": "message"
    },
    "group": {
      "type": "relation",
      "relation": "manyToOne",
      "target": "api::group.group",
      "inversedBy": "messages"
    },
    "status": {
      "type": "enumeration",
      "enum": [
        "pending",
        "sent",
        "read",
        "delivered",
        "edited",
        "failed"
      ],
      "default": "pending"
    },
    "messageReactions": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::message-reaction.message-reaction",
      "mappedBy": "message"
    }
  }
}

3. Message lifecycle.js method

module.exports = {
  async afterCreate(event) {
    const { result } = event;

    const BATCH_SIZE = 100;
    let offset = 0;
    let usersBatch;

    do {
      usersBatch = await strapi.db
        .query("plugin::users-permissions.user")
        .findMany({
          where: {
            id: {
              $ne: result.sender.id,
            },
          },
          limit: BATCH_SIZE,
          offset: offset,
        });

      if (usersBatch.length > 0) {
        await Promise.all(
          usersBatch.map(async (user) => {
            return await strapi
              .service("api::message-status.message-status")
              .create({ data: { message: result.id, user: user.id } });
          })
        );

        offset += BATCH_SIZE;
      }
    } while (usersBatch.length === BATCH_SIZE);
  },
};

I have tried the code snippets above but when I create the message in the admin panel it creates returns the toast notification with the message below

warning: internal server error

And the following information is returned in my terminal

e=1 (33 ms) 200
[2024-08-01 14:44:43.579] error: Undefined binding(s) detected when compiling WHERE. Undefined column(s): [t0.id] query: where `t0`.`id` <> ?
Error: Undefined binding(s) detected when compiling WHERE. Undefined column(s): [t0.id] query: where `t0`.`id` <> ?
    at QueryCompiler_SQLite3.toSQL (C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\query\querycompiler.js:112:13)
    at compileCallback (C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\formatter\formatterUtils.js:13:19)
    at rawOrFn (C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\formatter\wrappingFormatter.js:225:7)
    at QueryCompiler_SQLite3.whereWrapped (C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\query\querycompiler.js:1095:17)
    at QueryCompiler_SQLite3.where (C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\query\querycompiler.js:584:34)
    at C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\query\querycompiler.js:135:40
    at Array.forEach (<anonymous>)
    at QueryCompiler_SQLite3.select (C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\query\querycompiler.js:134:16)
    at QueryCompiler_SQLite3.toSQL (C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\query\querycompiler.js:75:29)
    at QueryBuilder_SQLite3.toSQL (C:\Users\user\Documents\workspace\ablestate\insightify-admin\node_modules\knex\lib\query\querybuilder.js:84:44)