Export User Data From ROQ

Exporting user data is essential for businesses to adapt to operational needs. Export data provides flexibility and control over data.

User Data

The user data is divided into two categories: platform data in the ROQ platform and project data in the generated application. The platform data is configurable through the ROQ Console and can be configured and accessed via ROQ API. On the other hand, the project data is specific to the generated application and cannot be accessed from the ROQ Console.

In order to collect user data, we can use ROQ's API with the help of the User Management API and other APIs, such as the File and Chat API. Additionally, we can also collect user data that is related to the project entities. To export data from ROQ platform and the generated application, we need to gather all the user-related data by their IDs.

Platform Data

We can use the Users API to get the user data. For example, for the user with ID c095e436-6904-49d0-8b7e-c0ba916ecdad. We can get the user basic information, roles, user groups and tenant.

const userID = "c095e436-6904-49d0-8b7e-c0ba916ecdad"
 
const userByID = await roqClient.asSuperAdmin().user({
	id: userID,
	withTenant: true,
	withUserGroups: true,
	withRoles: true
})
 
console.log(JSON.stringify(userByID))

The response from the ROQ API is an object that can be processed easily or converted to JSON format:

{
	"user": {
		"id": "c095e436-6904-49d0-8b7e-c0ba916ecdad",
		"reference": "c095e436-6904-49d0-8b7e-c0ba916ecdad",
		"firstName": "Anna",
		"lastName": "W.",
		"active": true,
		"email": "anna.w@gmail.com",
		"phone": null,
		"locale": "en-US",
		"isOptedIn": true,
		"synced": false,
		"tenantId": "a750fb55-2b94-4b94-8c87-ddc4deb941ed",
		"customData": {},
		"timezone": "Asia/Jakarta",
		"avatarUrl": "https://s3.fr-par.scw.cloud/a35df408-ee00-4703-environment-bucket/Fs-CC4maAAEf5xU.jpg",
		"createdAt": "2023-09-11T13:37:59.076Z",
		"updatedAt": "2023-09-14T09:56:02.470Z",
		"tenant": {
			"id": "a750fb55-2b94-4b94-8c87-ddc4deb941ed",
			"reference": null,
			"isDefault": false,
			"name": "Otaku HQ",
			"createdAt": "2023-09-11T13:37:58.807Z",
			"updatedAt": "2023-09-11T13:37:58.807Z"
		},
		"userGroups": {
			"data": [],
			"totalCount": 0
		},
		"roles": {
			"data": [
				{
					"id": "e5a65365-3f88-4f47-90d7-15e581004a65",
					"reference": null,
					"description": "",
					"isSystemManaged": false,
					"key": "store-owner",
					"name": "Store Owner"
				}
 			],
			"totalCount": 1
		}
	}
}

We can add additional data, such as files. We will use the Files API to get all users the files uploaded into the ROQ platform.

const userID = "c095e436-6904-49d0-8b7e-c0ba916ecdad"
const myFiles = await roqClient.asUser(userID).files()
 
console.log(JSON.stringify(myFiles))

Running files() as a user will make the output is automatically filtered. The output represents all the file data related to the user-specific ID:

	"files": {
		"data": [
			{
				"id": "302726f3-621e-4c7a-afa5-76ba92c32b94",
				"createdAt": "2023-09-14T09:56:55.980Z",
				"updatedAt": "2023-09-14T09:56:57.696Z",
				"contentType": "text/plain",
				"createdByUserId": "c095e436-6904-49d0-8b7e-c0ba916ecdad",
				"fileCategoryId": "a26ab5e0-2554-46da-822c-8ac2b77f0bc3",
				"isPublic": true,
				"name": "CPD-3080.txt",
				"status": "ready",
				"url": "https://s3.fr-par.scw.cloud/a35df408-ee00-4703-environment-bucket/CPD-3080.txt"
			},
			{
				"id": "799c8355-6e76-48c6-b3e3-3412fc00de90",
				"createdAt": "2023-09-14T09:56:00.018Z",
				"updatedAt": "2023-09-14T09:56:02.031Z",
				"contentType": "image/jpeg",
				"createdByUserId": "c095e436-6904-49d0-8b7e-c0ba916ecdad",
				"fileCategoryId": "9591e33d-725b-48b8-a2d3-e5c826bd5b45",
				"isPublic": true,
				"name": "Fs-CC4maAAEf5xU.jpg",
				"status": "ready",
				"url": "https://s3.fr-par.scw.cloud/a35df408-ee00-4703-environment-bucket/Fs-CC4maAAEf5xU.jpg"
			}
		],
		"totalCount": 2
	}

We can also add chat messages data using the conversations() and messages() method from the Chat API.

const chats = await roqClient.asUser(userID).conversations({
	withUsers: true,
	withTags: true
}

The previous code retrieves all conversation data for the current user ID and permissions. Then from each conversation ID, we can get the conversation messages.

const chatMessages = await roqClient.asUser(userID).messages({
	withFile: true,
	filter: {
		conversationId: {
			equalTo: userConversationId
		}
	}
})
 
console.log(JSON.stringify(chatMessages))

After calling this ROQ API, chat messages can be easily processed as JSON:

{
	"messages": {
		"data": [
			{
				"id": "af3d3394-8200-460c-9a9e-2bff9ed2dc40",
				"body": "heroku-add-payment.png",
				"conversationId": "7540d60f-d4a4-4724-927d-792078909455",
				"fileId": "aea705d9-4071-4b55-b699-d982935f909c",
				"messageStatusId": null,
				"authorId": "b555663c-1917-48b7-8513-ddf0ab4d1df9",
				"createdAt": "2023-09-17T02:52:35.190Z",
				"updatedAt": "2023-09-17T02:52:35.190Z",
				"file": {
					"id": "aea705d9-4071-4b55-b699-d982935f909c",
					"createdAt": "2023-09-17T02:52:31.867Z",
					"updatedAt": "2023-09-17T02:52:34.857Z",
					"contentType": "image/png",
					"createdByUserId": "b555663c-1917-48b7-8513-ddf0ab4d1df9",
					"fileCategoryId": "a26ab5e0-2554-46da-822c-8ac2b77f0bc3",
					"isPublic": true,
					"name": "heroku-add-payment.png",
					"status": "ready",
					"url": "https://s3.fr-par.scw.cloud/a35df408-ee00-environment-bucket/heroku-add-payment.png"
				}
			},
			{
				"id": "659bba7e-cafd-4727-8fd9-a668350a4c4a",
				"body": "Screenshot for Heroku setup",
				"conversationId": "7540d60f-d4a4-4724-927d-792078909455",
				"fileId": null,
				"messageStatusId": null,
				"authorId": "b555663c-1917-48b7-8513-ddf0ab4d1df9",
				"createdAt": "2023-09-17T02:52:31.706Z",
				"updatedAt": "2023-09-17T02:52:31.706Z",
				"file": null
			}
		],
		"totalCount": 2
	}
}

We can use the JavaScript object spread syntax to merge user data, files, and chat messages:

const userData = {
	...userByID,
	...myFiles,
	...chatMessages
}

Determining the data that requires retrieval from the ROQ platform is heavily dependent on the relevant business needs. However, the ROQ API provides a range of APIs to address the specific requirements of your application.

Project Data

To retrieve user data from the generated ROQ application, which is linked to the project entities, we will use GraphQL query using graphqlRequest() API.

We can run GraphQL query in ROQ using the graphqlRequest() method. Please read this documentation.

const entitiesQuery = `
query projectEntities($limit: Int = 10) {
	projectEntities(limit: $limit) {
	  totalCount
	  data {
		id
		name
	  }
	}
  }  
`
 
const entitiesList = await roqClient.asSuperAdmin().graphqlRequest(entitiesQuery)
console.log(JSON.stringify(entitiesList))

Upon identification of the project entities, it becomes feasible to retrieve any pertinent user data that is required to meet the application specifications.

Export Route

To simplify the export process, we will create a new endpoint in the application that retrieves user data from project entities.

Depending on which environment the generated application is running, for example, for a local development environment with the base URL localhost and the export endpoint is /api/export, the URL will be:

http://localhost:3000/api/export

For more information, you can read this documentation about how to add API endpoint in the ROQ's generated application.

Create a file in the pages/api directory to add a new API route. Let us name it export.ts. This code will retrieve all project entities' user data, roles, and files.

import type { NextApiRequest, NextApiResponse } from 'next';
import { withAuth, getServerSession } from '@roq/nextjs';
import { convertQueryToPrismaUtil, parseQueryParams } from 'server/utils';
import { roqClient } from 'server/roq';
 
export async function handler(req: NextApiRequest, res: NextApiResponse) {
	const session = getServerSession(req, res);
	const { roqUserId, user } = session;
 
	if (req.method !== 'GET') {
		return res.status(405).json({ message: `Method ${req.method} not allowed` });
	}
 
	const userByID = await roqClient.asSuperAdmin().user({
		id: roqUserId,
		withTenant: true,
		withUserGroups: true,
		withRoles: true
	});
 
	const myFiles = await roqClient.asUser(roqUserId).files();
 
	const entitiesQuery = `
		query projectEntities($limit: Int = 10) {
			projectEntities(limit: $limit) {
				totalCount
				data {
					id
					name
				}
			}
		}
	`;
 
	const entitiesList = await roqClient.asUser(roqUserId).graphqlRequest(entitiesQuery);
	const entitiesArray = entitiesList?.data?.projectEntities?.data.map(item => item.name) || [];
 
	const query = parseQueryParams(req.query);
	const allData = {};
 
	for (let entity of entitiesArray) {
		if (!prisma[entity]) {
			throw new Error(`Entity "${entity}" does not exist in Prisma client.`);
		}
		allData[entity] = await prisma[entity].withAuthorization({
			roqUserId,
			tenantId: user.tenantId,
			roles: user.roles,
		}).findManyPaginated(convertQueryToPrismaUtil(query, entity));
	}
 
	const userData = {
		userdata: userByID,
		...myFiles,
		...allData
	};
 
	return res.status(200).json({ data: userData });
}
 
export default function helloHandler(req: NextApiRequest, res: NextApiResponse) {
	return withAuth(req, res)(handler);
}

The code above filters data according to the user's ID, roles, and permissions. For additional information about filtering data by user ID please read this documentation.

This export data solution depends on the application's needs and ROQ provides easy APIs to query user data in the platform and its generated applications.

Edit Data Models Sync User Data with Database