June 27, 2023 - Database Synchronization
Synchronizing WatermelonDB Local Database with Cloud-Based Database
Today, my focus was on adapting the Logbook mobile app's offline database to a cloud-based solution. This transformation aimed to bring the concept of collaboration into the user experience, allowing for multiple users to operate concurrently within the same platform. Moreover, this change would also enable integration with our Civalgo SAAS, thereby enhancing operations management, and offer a provision for data backup.
To realize this synchronization between offline and online databases, we employed the methods offered by WatermelonDB (watermelondb.dev/docs/Sync/Intro), a resource designed from the ground up to facilitate seamless syncing with remote databases and to keep multiple data replicas synchronized. For the remote database, we utilized a Postgres database through Supabase, a service providing a range of tools conducive to the development of robust and scalable applications.
The choice of Supabase was driven by its provision for real-time subscriptions. This functionality allowed me to monitor changes in both the remote and local databases, triggering the synchronization function accordingly. As a result, it enabled a real-time collaborative experience for multiple users on the same platform.
To ensure the synchronization worked effectively with our backend, which uses the Prisma ORM, I had to make certain assumptions. One key presumption was that the Prisma table models mirrored the WatermelonDB table schema exactly. Given this, I was able to develop a loop to dynamically sync all the tables that I inputted into an array.
Here's a glimpse at the code under development for this technique:
import { omit } from 'lodash'
import { db } from 'src/lib/db'
import { logger } from 'src/lib/logger'
import { MutationResolvers, QueryResolvers } from 'types/graphql'
const COLLECTIONS_TO_SYNC = ['projects'] //, 'projectProperties', 'projectPropertyValues']
type Changes = {
[key: string]: CollectionChanges
}
type CollectionChanges = {
// eslint-disable-next-line @typescript-eslint/no-explicit-any
created: any[]
// eslint-disable-next-line @typescript-eslint/no-explicit-any
updated: any[]
deleted: string[]
}
export const pullChanges: QueryResolvers['pullChanges'] = async ({
lastPulledAt,
}) => {
const timestamp = Date.now()
const lastPulledAtDate = new Date(lastPulledAt).toISOString()
const changes: Changes = {}
try {
await db.$transaction(async (tx) => {
for (const collection of COLLECTIONS_TO_SYNC) {
if (!lastPulledAt) {
changes[collection] = {
created: await tx[collection].findMany(),
updated: [],
deleted: [],
}
} else {
const created = await tx[collection].findMany({
where: { created_at: { gt: lastPulledAtDate } },
})
const updated = await tx[collection].findMany({
where: {
created_at: { lte: lastPulledAtDate },
updated_at: { gt: lastPulledAtDate },
},
})
const deleted = (
await tx[collection].findMany({
where: {
created_at: { lte: lastPulledAtDate },
deleted_at: { gt: lastPulledAtDate },
},
})
).map((row) => row.id)
changes[collection] = {
created: created,
updated: updated,
deleted: deleted,
}
}
}
})
} catch (error) {
return {
changes: {},
timestamp,
error: error,
}
}
return {
changes,
timestamp,
error: null,
}
}
export const pushChanges: MutationResolvers['pushChanges'] = async ({
// lastPulledAt, // TODO: do something with this
changes,
}: {
lastPulledAt: number
changes: Changes
}) => {
// const lastPulledAtDate = new Date(lastPulledAt).toISOString()
try {
for (const collection in changes) {
if (!COLLECTIONS_TO_SYNC.includes(collection)) {
continue
}
const { created, updated, deleted } = changes[collection]
for (const row of created) {
const created_at = new Date(row.created_at).toISOString()
const updated_at = new Date(row.updated_at).toISOString()
await db[collection].create({
data: {
...omit(row, ['_status', '_changed']),
created_at,
updated_at,
},
})
}
for (const row of updated) {
const updated_at = new Date(row.updated_at).toISOString()
await db[collection].update({
where: {
id: row.id,
},
data: {
...omit(row, ['_status', '_changed', 'created_at']),
updated_at,
},
})
}
await db[collection].deleteMany({
where: {
id: {
in: deleted,
},
},
})
}
return {
code: 200,
}
} catch (error) {
logger.info(error)
return {
error: error,
code: 500,
}
}
}
Email Validation Message
Today, I also undertook the task of revamping our sign-up email. Initially, the default email sent via our authentication technology, Supabase, was rather simple and lacked the professional touch that aligns with our brand image. To rectify this, I introduced custom HTML inline styling to create a more polished and engaging design.
Prior to this, our system didn't have a dedicated website to confirm the successful validation of an email address. Recognizing the necessity of this, I designed a confirmation page using Redwood.js. I chose Render.com for hosting the new website due to how quick and simple this service is.
To boost the credibility of our transactional emails and improve their deliverability, I decided to switch the SMTP system in Supabase to our own domain. This change ensures that our emails are recognized as legitimate by the recipients, thus improving their open rates and our overall email performance.
As for the email delivery service, we adopted Postmark for its reputation of high deliverability rates and comprehensive analytics. These improvements, taken together, will improve our email communication system and enhance the overall user experience.
Learning Three.js
Lately, I've started devoting a few hours of my day to explore Three.js, a highly intriguing and robust technology, via online courses. This unique tool is known for its ability to craft and exhibit 3D graphics directly in a web browser, completely doing away with the requirement of any external software.
As I continue to learn more about Three.js, I'm excited about its potential. I hope to uncover all it can do, and I'm especially interested in how it could inspire new product ideas. Ultimately, I believe using Three.js could enhance our user potential, and I'm excited to explore these untapped creative possibilities.
