Lessons Learned: Designing the Right Database and Flow for Payment Gateways

Initially, I thought integrating a payment gateway was a simple task. I assumed it only involved fetching a server key and client key from a dashboard, putting a checkout button on the frontend, and redirecting users to the gateway page to complete the payment. However, once I started implementation, I realized that the hardest part wasn't displaying the payment button. The hardest part was understanding that payment is merely a small piece of a much larger order lifecycle.

Why a Simple Single-Table Design Fails Fast

When starting out, it feels practical to build a single orders table and store all payment statuses and transactional metadata directly in it. However, as real-world production requirements emerge, this simplified design quickly feels incredibly restrictive. Requirements like transaction retries, logging complete provider responses, storing unique gateway references, handling automated callbacks, or showing detailed transaction histories to users cannot be cleanly managed with a single database table.

Decoupling the Concepts of Orders and Payments

Faced with these limitations, I learned to divide the problem domain into two distinct entities:

• Order: This represents the business intent of the purchase. It records who the buyer is, what items are being bought, the total amount due, and the overall business status of the purchase (e.g., pending payment, processing, or completed).
• Payment: This represents the actual transactional attempt to pay for that specific order. It stores technical details such as the provider used, the unique reference sent to the gateway, the transaction status from the gateway, the checkout URL, expiration timestamps, and the raw payload for audit trails.

This decoupling shifts your mental model. Under this architecture, an order can have one active payment, but it can easily accommodate multiple payment attempts. For instance, if the first attempt expires, the user can try paying again with a different method without the system having to cancel or re-create the entire business order.

Preserving Price Data Integrity

Another crucial lesson is how we store historical line-item data. It is highly discouraged to rely solely on product IDs linked to a dynamic products table. When a user purchases an item at a specific price, that snapshot price must be copied directly into the order_items table at the exact moment of checkout.

If the product price changes tomorrow due to a promotion or catalog update, the customer's historical order records must still display the exact price paid at the time of the transaction. This is essential for proper financial record-keeping, database integrity, and customer trust.

Separating Business Statuses from Payment Statuses

A common pitfall is using generic statuses like pending, success, or failed across the board. The reality is that order status and payment status represent different lifecycles and should remain decoupled.

An order status might progress through pending_payment, processing, completed, or cancelled. Meanwhile, a payment status has its own technical states like pending, paid, expired, failed, or refunded. Merging these two domains leads to messy conditional branches and unmaintainable logic in your controllers.

The Ideal Integration Flow and the Crucial Role of Webhooks

To build a resilient payment system, the integration flow should be broken down into structured, sequential steps:

1. Order Creation: Create a business order record in your database.
2. Payment Initialization: Create a corresponding payment attempt record.
3. Provider Request: Submit the transaction payload to the payment gateway to retrieve a transaction token or URL.
4. User Redirection: Guide the user to the gateway's checkout page or display payment instructions.
5. Webhook Receipt: Wait for an asynchronous callback notification directly from the payment gateway's server when the status changes.
6. Status Update: Safely update the payment and order status based on verified callback events.

Relying solely on the client-side redirect page to update transaction statuses is unsafe. Users can close their browsers early, experience network drops, or the application might crash before the redirection occurs. The ultimate source of truth for payment success must come through secure backend webhooks (server-to-server notifications) or direct API status polling.

A Database Architecture That Answers Business Questions

I realized that a great database design isn't about having the fewest tables; it's about how clearly those tables describe your real-world business processes. Each table must have a singular, clear responsibility:

• The Order table answers: Who is buying and what is the current business status?
• The Order Item table answers: What was purchased and what was the exact price at checkout?
• The Payment table answers: Which method was used and did the transaction succeed?
• The Payment Event/Log table answers: What did the payment gateway communicate to our servers and when?

Ultimately, when integrating a payment gateway, do not just aim for a successful redirect to the checkout page. Build a system that can accurately answer technical and business questions long after the transaction has concluded.