Architecting a Hybrid Database System: PostgreSQL for Billing vs. MongoDB for Dynamic AI Memory

In modern software engineering, one size rarely fits all—especially when it comes to databases. Developers often get caught in endless debates about SQL vs. NoSQL, when the real solution is often polyglot persistence. When designing the backend for PrepWai, an AI mock-interview platform, we decided to leverage the strengths of both PostgreSQL and MongoDB to build a billing, credit ledgering, and heavy AI conversation log pipeline.

The Problem: Rigid Transactions vs. Unstructured Chats

PrepWai has two primary data streams with completely different operational requirements: 1. Billing & Token Ledgers: Needs strict transaction controls, mathematical integrity, and relational references (users, invoices, credit balances). Every transaction must be ACID-compliant. 2. AI Interview Sessions: Needs flexible schemas. An interview consists of an array of dialog messages, evaluation results, sentiment metrics, and dynamic feedback trees. The structure changes constantly as our models evolve.

The Solution: Split-Storage Polyglot Architecture

We decoupled these streams and mapped them to their ideal database engines:

1. PostgreSQL: Relational Consistency & Billing

For user management, token balances, and Razorpay payment records, we use PostgreSQL. It provides strict validation. A user cannot purchase credits if the transaction is pending, and we can run database-level atomic operations to decrement tokens safely, preventing double-spend exploits.

2. MongoDB: Flexible Document Storage for AI Conversations

Once an interview session begins, the conversation logs are recorded as highly flexible BSON documents in MongoDB. Storing nested dialogue loops, chat tokens used, and complex feedback trees in SQL tables would require 4 or 5 complex tables with foreign keys. In MongoDB, it fits into a single, highly readable document, allowing fast reads and writes during real-time mock interviews.

// Start a PG transaction to decrement user tokens safely
const pgClient = await pgPool.connect();
try {
  await pgClient.query("BEGIN");
  
  // Decrement token balance atomically
  const { rows } = await pgClient.query(
    "UPDATE users SET tokens = tokens - 1 WHERE id = $1 AND tokens > 0 RETURNING tokens",
    [userId]
  );
  if (rows.length === 0) throw new Error("Insufficient tokens");

  await pgClient.query("COMMIT");

  // Fire-and-forget: Initialize the heavy document session in MongoDB
  await mongoDb.collection("interview_sessions").insertOne({
    userId,
    status: "active",
    messages: [],
    metadata: { model: "claude-3-5-sonnet", startedAt: new Date() }
  });

} catch (err) {
  await pgClient.query("ROLLBACK");
  throw err;
} finally {
  pgClient.release();
}

Key Takeaway

By dividing database responsibilities, we achieved transaction safety for payments alongside rapid schema iteration for AI models. Don't fight database limitations. Use SQL for what must be structured, and NoSQL for what is inherently dynamic.

Thanks for reading.