PostgreSQL Database CRUD Query

Requirements

Write a Drizzle or Prisma transactional database script that manages a transaction. It must create a new invoice record, verify client balance scopes, deduct costs from users accounts, and write audits logs, rolling back all queries if any step fails.

Expected Output

A transactional query block executing ACID compliant database updates.

Hints & Tips

1Utilize Prisma `$transaction()` API calls.
2Perform index updates to deduct balances: ensure final totals do not fall below zero.
3Wrap step anomalies in exceptions to trigger database rollbacks.

Senior Code Review Checklist

0 / 4 Checked

Common Architectural Mistakes

1Executing database updates sequentially outside transactions, leading to orphaned records on failures.
2Not implementing balance verification checks, letting database totals drop below zero.
3Failing to close connection pools.

Developer Solution

Click to toggle the recommended code solution

Architectural Explanation

This database script calls transaction managers, verifies user state balances, decrementing pools, creates records, and logs logs. It guarantees ACID properties.

Code Snippet

typescript

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

const prisma = new PrismaClient();

export async function processInvoice(userId: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    // 1. Fetch user balance
    const user = await tx.user.findUnique({ where: { id: userId } });
    if (!user || user.balance < amount) {
      throw new Error("Insufficient account balance.");
    }

    // 2. Deduct balance
    const updatedUser = await tx.user.update({
      where: { id: userId },
      data: { balance: { decrement: amount } }
    });

    // 3. Create invoice
    const invoice = await tx.invoice.create({
      data: { userId, amount, status: "PAID" }
    });

    // 4. Log audit log
    await tx.auditLog.create({
      data: { userId, action: "INVOICE_PAYMENT", metadata: JSON.stringify({ invoiceId: invoice.id }) }
    });

    return { updatedUser, invoice };
  });
}

Explain in Interview

How to pitch this solution during technical interviews

I maintain database integrity by wrapping associated operations in transactions. If database queries fail or balance checks fail, the transaction throws an error, prompting database rollback actions.

Loading content…

Requirements

Expected Output

A transactional query block executing ACID compliant database updates.

import { PrismaClient } from "@prisma/client"; const prisma = new PrismaClient(); export async function processInvoice(userId: string, amount: number) { return prisma.$transaction(async (tx) => { // 1. Fetch user balance const user = await tx.user.findUnique({ where: { id: userId } }); if (!user || user.balance < amount) { throw new Error("Insufficient account balance."); } // 2. Deduct balance const updatedUser = await tx.user.update({ where: { id: userId }, data: { balance: { decrement: amount } } }); // 3. Create invoice const invoice = await tx.invoice.create({ data: { userId, amount, status: "PAID" } }); // 4. Log audit log await tx.auditLog.create({ data: { userId, action: "INVOICE_PAYMENT", metadata: JSON.stringify({ invoiceId: invoice.id }) } }); return { updatedUser, invoice }; }); }