The Dual-Write Dilemma in Transactional Systems
Consider what happens during user registration: your application inserts a new row into PostgreSQL, and then immediately calls an external email API. What happens if the email API times out? Or what happens if your application container is terminated by an autoscaler between the database commit and the email call?
This is the classic dual-write distributed systems problem. You either end up with a registered user who never receives their verification link, or—if you send the email first—an email sent for a user whose database transaction subsequently rolled back. The Transactional Outbox pattern guarantees 100% consistency by committing the email record to an outbox table in the exact same atomic transaction.
1. Prisma Interactive Transaction Implementation
Using prisma.$transaction, insert the business entity and the pending email payload in a single atomic database operation.
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
export async function createUserWithVerification(email: string, name: string) {
return await prisma.$transaction(async (tx) => {
// 1. Create the user entity
const user = await tx.user.create({
data: { email, name, isVerified: false },
});
// 2. Commit the outbound email payload into the outbox table
await tx.outboxEmail.create({
data: {
to: user.email,
subject: 'Verify your email address',
text: `Hello ${name}, please click here to verify: https://example.com/verify?token=${user.id}`,
status: 'PENDING',
idempotencyKey: `verify-${user.id}`,
},
});
return user;
});
}
// Background poller consuming outbox records using FOR UPDATE SKIP LOCKED
export async function processOutboxBatch() {
const pending = await prisma.$queryRaw<Array<{ id: string; to: string; subject: string; text: string; idempotencyKey: string }>>`
SELECT id, "to", subject, text, "idempotencyKey"
FROM "OutboxEmail"
WHERE status = 'PENDING'
LIMIT 20
FOR UPDATE SKIP LOCKED;
`;
for (const item of pending) {
const res = await fetch('https://api.sadasend.com/emails', {
method: 'POST',
headers: {
Authorization: `Bearer ${process.env.SADASEND_API_KEY}`,
'Content-Type': 'application/json',
'Idempotency-Key': item.idempotencyKey,
},
body: JSON.stringify({ to: item.to, subject: item.subject, text: item.text }),
});
if (res.ok) {
await prisma.outboxEmail.update({
where: { id: item.id },
data: { status: 'DELIVERED', processedAt: new Date() },
});
}
}
}Outbox Pattern vs Direct Dispatch Tradeoff Matrix
| Dimension | Direct API Dispatch in Controller | Transactional Outbox Pattern |
|---|---|---|
| Data Consistency | At-risk (Dual write failure) | 100% Guaranteed Atomic Consistency |
| HTTP Latency | Slow (Awaits remote email API) | immediate (Single local DB transaction) |
| API Outage Resilience | Lost emails / 500 error to user | Zero lost emails (Queued safely in DB) |
| Implementation Complexity | Very low | Requires background polling or Debezium CDC |
Building AI agents that send email?
Scoped API keys, per-key recipient allowlists, approval mode and a hosted MCP server with ten tools — on the free plan, without a card.