Transactions in Next.js route with supabase

[is-dev-98]

Supabase does not provide an sql transaction mechanism, so i decided to use slonik as I don't want to deal with ORMs.
I do not want to write an RPC for everything that requires transactions so here I am.

I am not sure if I implemented the library correctly the code below shows my latest implementation.
Feel free to provide any constructive criticism.

I am mainly concerned about:

1. If the connection is stopped automatically?
2. Is there a better way to handle the transaction operations (unsafe)?
3. How should I properly initialise the pool createPool

export const POST = async (req: NextRequest) => {
  const body = await req.json();
  const { amount, fromCurrency, toCurrency } = body;

  const validCurrencies = ['EUR', 'USD'];

  if (!Number.isInteger(amount) || amount <= 0) {
    return NextResponse.json(
      {
        data: null,
        error: 'Invalid amount. Amount must be a positive integer in cents.',
      },
      { status: 400 }
    );
  }

  if (
    !validCurrencies.includes(fromCurrency) ||
    !validCurrencies.includes(toCurrency) ||
    fromCurrency === toCurrency
  ) {
    return NextResponse.json(
      { data: null, error: 'Invalid currency selection.' },
      { status: 400 }
    );
  }

  const supabaseUser = await createClient<Database>();
  const {
    data: { user },
    error: userError,
  } = await supabaseUser.auth.getUser();

  if (userError || isNil(user)) {
    return NextResponse.json(
      { data: null, error: 'User is not authenticated' },
      { status: 401 }
    );
  }

  const verified = await checkIfUserIsVerified(user.id);
  if (!verified.allowed) {
    return NextResponse.json(
      { data: null, error: verified.error ?? 'User is not verified' },
      { status: 403 }
    );
  }

  const pool = await createPool(process.env.DATABASE_URL!, {
    driverFactory: createPgDriverFactory(),
  });

  try {
    await pool.connect(async connection => {
      const [conversionFee, fromWalletRes, toWalletRes, platformWalletRes] =
        await Promise.all([
          getConversionFee(connection),
          getWalletByCurrency(connection, user.id, toCurrency),
          getWalletByCurrency(connection, user.id, fromCurrency),
          getPlatformWalletByCurrency(connection, toCurrency),
        ]);

      const fromWallet = fromWalletRes.row_to_json;
      const toWallet = toWalletRes.row_to_json;
      const platformWallet = platformWalletRes.row_to_json;

      if (!fromWallet || !toWallet || !conversionFee || !platformWallet) {
        return NextResponse.json(
          {
            data: null,
            error: 'Could not retrieve wallet or fee information.',
          },
          { status: 404 }
        );
      }

      if (fromWallet.balance < amount) {
        return NextResponse.json(
          { data: null, error: 'Insufficient funds in source wallet.' },
          { status: 400 }
        );
      }

      const freecurrencyapi = new Freecurrencyapi(
        process.env.FREE_CURRENCY_API_KEY
      );
      const response = await freecurrencyapi.latest({
        base_currency: fromCurrency,
        currencies: toCurrency,
      });

      const rate = response?.data?.[toCurrency];
      if (!rate) {
        return NextResponse.json(
          { error: 'Failed to fetch currency exchange rate.' },
          { status: 500 }
        );
      }

      const {
        newFromWalletBalanceInCents,
        newToWalletBalanceInCents,
        platformFeeInCents,
      } = calculateWalletMoveAmounts({
        amountInCents: amount,
        conversionRate: rate.toString(),
        fromWalletBalanceInCents: fromWallet.balance,
        toWalletBalanceInCents: toWallet.balance,
        platformFeePercentage: conversionFee.fee.toString(),
      });

      const netAmountCredited = newToWalletBalanceInCents - toWallet.balance;

      await connection.transaction(async transactionConnection => {
        await transactionConnection.query(sql.unsafe`
              UPDATE user_wallets
              SET balance = ${newFromWalletBalanceInCents}
              WHERE id = ${fromWallet.id}
            `);

        await transactionConnection.query(sql.unsafe`
              UPDATE user_wallets
              SET balance = ${newToWalletBalanceInCents}
              WHERE id = ${toWallet.id}
            `);

        await transactionConnection.query(sql.unsafe`
              UPDATE platform_wallet
              SET balance = balance + ${platformFeeInCents}
              WHERE currency = ${toCurrency}
            `);

        await transactionConnection.query(sql.unsafe`
              INSERT INTO transactions_new (
                amount, currency, destination_wallet_id, target_wallet_u_id,
                source_wallet_id, source_wallet_u_id, payment_type, exchange_rate,
                process_type, status, transaction_type
              ) VALUES
                (
                  ${netAmountCredited}, ${toCurrency}, ${toWallet.id}, ${toWallet.u_id},
                  ${fromWallet.id}, ${fromWallet.u_id}, 'wallet_to_wallet_conversion', ${rate},
                  'automatic', 'completed', 'credit'
                ),
                (
                  ${platformFeeInCents}, ${toCurrency}, ${platformWallet.id}, ${platformWallet.u_id},
                  ${toWallet.id}, ${toWallet.u_id}, 'currency_conversion_fee', ${rate},
                  'automatic', 'completed', 'debit'
                )
            `);
      });
    });
  } catch (error) {
    return NextResponse.json({ data: null, error: error }, { status: 500 });
  }
  return NextResponse.json(
    { data: 'Funds transfer successful', error: null },
    { status: 200 }
  );
};

functions used:

export const getWalletByCurrency = async (
  connection: DatabaseConnection,
  userId: string,
  currency: string
) => {
  return await connection.one(sql.typeAlias('userWallet')`
        SELECT row_to_json(user_wallets) 
        FROM user_wallets 
        WHERE user_id = ${userId} 
        AND currency = ${currency}
    `);
};
export const getPlatformWalletByCurrency = async (
  connection: DatabaseConnection,
  currency: string
) => {
  return await connection.one(
    sql.typeAlias('platformWallet')`
            SELECT row_to_json(platform_wallet) 
            FROM platform_wallet 
            WHERE currency = ${currency}`
  );
};
export const getConversionFee = async (connection: DatabaseConnection) => {
  return await connection.one(
    sql.typeAlias('conversionFee')`
            SELECT other_investor_fees.fee 
            FROM other_investor_fees WHERE id = 6`
  );
};

sql type aliases:

export const sql = createSqlTag({
  typeAliases: {
    conversionFee: z.object({ fee: z.number() }),
    userWallet: z.object({
      row_to_json: z.object({
        id: z.number(),
        u_id: z.string(),
        created_at: z.string(),
        currency: z.string(),
        balance: z.number(),
        user_id: z.string(),
      }),
    }),
    platformWallet: z.object({
      row_to_json: z.object({
        id: z.number(),
        u_id: z.string(),
        created_at: z.string(),
        currency: z.string(),
        balance: z.number(),
      }),
    }),
  },
});