Deduplicate and Import New Financial Transactions with Prisma

While working on my personal finance app, I ran into a common use case where I want to import new transaction after making sure it's unique.

Here's how I did it.

As for the tech stack, I'm using Prisma ORM and using PostgreSQL under the hood.

Dependencies

We'll be using day.js for easily working with date and Prisma ORM for working with the database.

npm i dayjs @prisma/client

Snippet

// prisma.js
import { PrismaClient } from "@prisma/client";

const prisma = global.prisma || new PrismaClient();

if (process.env.NODE_ENV === "development") global.prisma = prisma;

export default prisma;

// importDeduplicatedTransactions.js
import dayjs from "dayjs";
import prisma from "./prisma";

import utc from "dayjs/plugin/utc";
import timezone from "dayjs/plugin/timezone";
dayjs.extend(utc);
dayjs.extend(timezone);

const importDeduplicatedTransactions = async (transactions) => {
  const dbRecords = await prisma.transaction.findMany({
    where: {
      OR: transactions.map((transaction) => {
        const date = dayjs(transaction.date, "YYYY-MM-DD")
          .tz("Asia/Kolkata")
          .utc()
          .toDate();
        return {
          date: {
              gte: dayjs(date).subtract(1, 'day').startOf('day').utc().toDate(),
              lte: dayjs(date).add(1, 'day').endOf('day').utc().toDate(),
          },
          description: transaction.description,
          amount: transaction.amount,
        };
      }),
    },
  });

  const existingTransactionsMap = new Map();

  dbRecords.forEach(({ id, date, description, amount }) => {
    const parsedDate = dayjs(date).tz("Asia/Kolkata").format("YYYY-MM-DD")
    const key = `${parsedDate}-${description}-${amount}`;
    existingTransactionsMap.set(key, id);
  });

  const uniqueTransactions = [];

  transactions.forEach(({ date, description, amount }) => {
    const parsedDate = dayjs(date, "YYYY-MM-DD")
      .utcOffset("+5:30")
      .format("YYYY-MM-DD");
    const key = `${parsedDate}-${description}-${amount}`;
    if (!existingTransactionsMap.has(key)) {
      uniqueTransactions.push({
        date,
        description,
        amount: parseFloat(amount),
      });
    }
  });

  if (uniqueTransactions?.length) {
    const results = await prisma.transaction.createMany({
      data: uniqueTransactions,
    });
    return results;
  }

  return uniqueTransactions;
};

Happy deduplicating records!