Sync User Data with Database

Introduction

Synchronizing user data ensures users have a consistent view of their information, preferences, and progress across all platforms. There are many ways to synchronize data from ROQ to another application, essentially synchronizing necessary data in the third-party application database with the ROQ application.

There are various methods for synchronizing data, such as polling, scheduled tasks, webhooks, serverless functions, and event sourcing, each with its own set of advantages and disadvantages.

ROQ provides APIs for easy data query and management and webhook feature to notify the application when an event happens in the account. We can use this feature to sync the user data with third-party applications. Webhooks are more real-time than periodic API polling, but they also require third-party apps to have an endpoint to receive the data.

Sync Case

Let's consider an example of an application created by ROQ that collects figurines. Users typically add and remove images of their collectible figurines in this application. If we connect this application with a third-party application, any updates made to the files in the ROQ application will also be reflected in the third-party application. For this to be possible, we need to synchronize the data between the two applications.

We will use ROQ's webhook to notify third-party applications of events. For instance, when a user adds a new item to their collection, the webhook can notify the third-party app to get the new data from the ROQ platform or application.

ROQ Webhook

To use ROQ Webhook. We should enable the webhook feature. Go to the ROQ Console and Application Details, select Settings, and find the Webhook section. Tick mark both Create and Update for Files.

Set the webhook URL to the webhook endpoint. For example, if you want to create your webhook server, read this section.

ROQ webhook setting

Ensure that the webhook URL provided is both publicly accessible and secure since notifications may contain sensitive information.

For this tutorial, we will only be using the webhook for file updates and creations. If you wish to configure it for user and chat activity, you can do so. With this setting, any user activity related to files will trigger the system to send a message to the specified webhook.

ROQ Webhook will send post data to the webhook endpoint we havve set earlier. The webhook payload data for file updates in JSON is shown below:

{
	"name": "ROQ_FILE_UPDATE",
	"id": "6c64b15f-30f4-4340-8269-6e01038cb610",
	"object": "file",
	"data": {
		"current": {
			"id": "6c64b15f-30f4-4340-8269-6e01038cb610",
			"name": "Default_3D_character_Has_black_hairee0c-33b0_1.jpg",
			"status": "ready",
			"createdByUserId": "8d18aea8-65a4-4539-b202-8cf44688fa86",
			"isPublic": true,
			"contentType": "image/jpeg",
			"fileCategoryId": "1ebfb78e-5b26-40b2-95a5-b0e001aab658",
			"createdAt": "2023-09-19T11:31:45.537Z",
			"updatedAt": "2023-09-19T11:31:47.394Z"
		},
		"previous": {
			"id": "6c64b15f-30f4-4340-8269-6e01038cb610",
			"name": "3D_character_Has_black_hair_ca_1.jpg",
			"status": "upload_pending",
			"createdByUserId": "8d18aea8-65a4-4539-b202-8cf44688fa86",
			"isPublic": true,
			"contentType": "image/jpeg",
			"fileCategoryId": "1ebfb78e-5b26-40b2-95a5-b0e001aab658",
			"createdAt": "2023-09-19T11:31:45.537Z",
			"updatedAt": "2023-09-19T11:31:45.537Z",
		}
	},
	"environmentId": "051528ea-fcfc-4c6f-a749-e3d21af37127"
}

For more information about webhook events, please read here.

This JSON payload gives us a lot of information about which user updates the file and the changing file information. With this information, we can query necessary data from ROQ and update data in the third-party application database.

Webhook Server

It's important to note that numerous solutions are available for webhook automation, including building your own or utilizing automation services. However, we will create a basic webhook server using Node.js for this tutorial.

Please keep in mind that the webhook endpoint on this server needs to be publicly accessible and have a secure connection in order for the payload data to be delivered successfully.

To make the URL publicly available for development purposes, you can use a tunneling service like Ngrok (opens in a new tab) and then set the webhook URL in the ROQ Console.

The webhook server uses the SQLite database to synchronize the updated files information from ROQ's generated application. Of course, you can use another database in production to suit the business needs.

import 'dotenv/config'
import express from 'express'
import sqlite3 from 'sqlite3'
import { fileURLToPath } from 'url'
import { dirname } from 'path'
 
const __filename = fileURLToPath(import.meta.url)
const __dirname = dirname(__filename)
const app = express()
 
// Initialize SQLite database
const db = new sqlite3.Database(`${__dirname}/myfigurine.sqlite`)
 
db.serialize(() => {
	const createTableQuery = `
	  CREATE TABLE IF NOT EXISTS figurine (
		id TEXT PRIMARY KEY,
		name TEXT,
		status TEXT,
		createdByUserId TEXT,
		isPublic INTEGER,
		contentType TEXT,
		fileCategoryId TEXT,
		createdAt TEXT,
		updatedAt TEXT
	  );`
 
	db.run(createTableQuery, (err) => {
		if (err) {
			console.log('Table creation failed:', err)
		} else {
			console.log('Table created successfully')
		}
	})
})
 
app.use(express.json())
 
const handleRoqWebhook = (req, res) => {
	const payload = req.body
	const eventName = payload.name
 
	if (eventName === 'ROQ_FILE_UPDATE') {
		const fileData = payload.data.current
		const previousFileData = payload.data.previous
		// Update the database
		upsertDatabase(fileData, previousFileData)
	}
 
	console.log('Received ROQ payload:', payload)
	res.status(200).send('OK')
}
 
const upsertDatabase = (currentData) => {
	const query = `
		INSERT INTO figurine (
			id, name, status, createdByUserId, isPublic, 
			contentType, fileCategoryId, createdAt, updatedAt
		)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(id) DO UPDATE SET
			name = excluded.name,
			status = excluded.status,
			createdByUserId = excluded.createdByUserId,
			isPublic = excluded.isPublic,
			contentType = excluded.contentType,
			fileCategoryId = excluded.fileCategoryId,
			createdAt = excluded.createdAt,
			updatedAt = excluded.updatedAt;`
 
	const values = [
		currentData.id,
		currentData.name,
		currentData.status,
		currentData.createdByUserId,
		currentData.isPublic ? 1 : 0,
		currentData.contentType,
		currentData.fileCategoryId,
		currentData.createdAt,
		currentData.updatedAt
	]
 
	db.run(query, values, function (err) {
		if (err) {
			console.log('Database upsert failed:', err)
			return
		}
		console.log(`Database upserted, row(s) affected: ${this.changes}`)
	})
}
 
app.get('/', (req, res) => {
	res.status(200).send({ server: "webhook" })
})
app.post('/roq-webhook', handleRoqWebhook)
 
app.listen(process.env.PORT, () => {
	console.log(`Server listening at http://localhost:${process.env.PORT}`)
})

This webhook server provides the webhook URL:

https://[based_url]/roq-webhook

To optimize the handling of file creation and update activity. It is imperative to filter data using eventName parameter with a value of ROQ_FILE_UPDATE. This approach ensures that the webhook server processes only the relevant data.

The payload data from the ROQ system can then be used to update the existing record on the database or create a new record. Hence, this is the same as making a synchronization process.

const query = `
		INSERT INTO figurine (
			id, name, status, createdByUserId, isPublic, 
			contentType, fileCategoryId, createdAt, updatedAt
		)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(id) DO UPDATE SET
			name = excluded.name,
			status = excluded.status,
			createdByUserId = excluded.createdByUserId,
			isPublic = excluded.isPublic,
			contentType = excluded.contentType,
			fileCategoryId = excluded.fileCategoryId,
			createdAt = excluded.createdAt,
			updatedAt = excluded.updatedAt;`

Based on the payload, we can also query another data from ROQ platform or application, such as query the URL for the updated file based on the file ID. Fort this purposes, we can use File API from ROQ or using GraphQL query.

query files(
  $filter: FileFilterArgType = {
    id: { equalTo: "6c64b15f-30f4-4340-8269-6e01038cb610" }
  }
) {
  files(filter: $filter) {
    totalCount
    data {
      id
      name
      url
    }
  }
}

By querying the image file URL, we can download and synchronize the image file with the third-party application by either updating it or adding a new one.

{
  "data": {
    "files": {
      "totalCount": 1,
      "data": [
        {
          "id": "6c64b15f-30f4-4340-8269-6e01038cb610",
          "name": "3D_character_Has_black-33b0.jpg",
          "url": "https://s3.fr-par.scw.cloud/051a-environment-bucket/3D1_3fe0c.jpg"
        }
      ]
    }
  }
}

There are no strict rules for synchronizing data. The best method depends on the application architecture, integration support, and business needs.

To query data with GraphQL, use the graphqlRequest() API. Read about it here and for more information about our File API, see our documentation here.

Export User Data Application Deployment Vercel