Repository: logto-io/logto
File: packages/core/src/queries/oidc-model-instance.ts (TypeScript)

Code before:
  const findPayloadById = async (modelName: string, id: string) => {
    const result = await pool.maybeOne<QueryResult>(sql`
      ${findByModel(modelName)}
      and ${fields.id}=${id}
    `);

    return convertResult(result, modelName);
  };

  const findPayloadByPayloadField = async <
    T extends ValueExpression,
    Field extends keyof OidcModelInstancePayload,
  >(
    modelName: string,
    field: Field,
    value: T
  ) => {
    // Fetch all matching records
    const results = await pool.any<QueryResult>(sql`
    ${findByModel(modelName)}
    and ${fields.payload}->>${field}=${value}
  `);

    // In a rare case, when duplicate uid is created for different session instances.
    // This query may lead to DataIntegrityError.
    // To handle this case, we delete all duplicates and return null.
    if (results.length > 1) {
      // Delete all duplicates
      await pool.query(sql`
      delete from ${sql.identifier([modelName])}
      where ${fields.payload}->>${field}=${value}
    `);
      return;
    }

    // If there is only one record, return the result.
    return results[0] ? convertResult(results[0], modelName) : undefined;
  };

  const consumeInstanceById = async (modelName: string, id: string) => {
    await pool.query(sql`
      update ${table}
      set ${fields.consumedAt}=${convertToTimestamp()}
      where ${fields.modelName}=${modelName}
      and ${fields.id}=${id}
    `);
  };

  const destroyInstanceById = async (modelName: string, id: string) => {
    await pool.query(sql`
      delete from ${table}

Review comment (refactor):
```suggestion
    // Rarely, duplicate UIDs can exist for different sessions.
    // This query may throw `DataIntegrityError`.
    // If that happens, delete all duplicates and return `null`.
```

Code after:
  const findPayloadById = async (modelName: string, id: string) => {
    const result = await pool.maybeOne<QueryResult>(sql`
      ${findByModel(modelName)}
      and ${fields.id}=${id}
    `);

    return convertResult(result, modelName);
  };

  const findPayloadByPayloadField = async <
    T extends ValueExpression,
    Field extends keyof OidcModelInstancePayload,
  >(
    modelName: string,
    field: Field,
    value: T
  ) => {
    // Fetch all matching records
    const results = await pool.any<QueryResult>(sql`
    ${findByModel(modelName)}
    and ${fields.payload}->>${field}=${value}
  `);

    // Rarely, duplicate UIDs can exist for different sessions.
    // This query may throw `DataIntegrityError`.
    // If that happens, delete all duplicates and return `null`.
    if (results.length > 1) {
      // Delete all duplicates
      await pool.query(sql`
      delete from ${sql.identifier([modelName])}
      where ${fields.payload}->>${field}=${value}
    `);
      return;
    }

    // If there is only one record, return the result.
    return results[0] ? convertResult(results[0], modelName) : undefined;
  };

  const consumeInstanceById = async (modelName: string, id: string) => {
    await pool.query(sql`
      update ${table}
      set ${fields.consumedAt}=${convertToTimestamp()}
      where ${fields.modelName}=${modelName}
      and ${fields.id}=${id}
    `);
  };

  const destroyInstanceById = async (modelName: string, id: string) => {
    await pool.query(sql`
      delete from ${table}