Snapshotting Tax at Checkout

Snapshotting Tax at Checkout | CodeSignal Learn

Orders Must Not Change

Congratulations on making it to the final lesson of this course 🎉 You’ve built a full tax configuration system, wired it into carts, and you can now see cart totals change as the tax country changes. That’s a huge backend milestone. Previously, in “Applying Cart Taxes”, you made carts tax-aware by storing tax_country, resolving a tax_rate_bps (configured or default), and computing totals every time a cart is fetched. That was perfect for a dynamic cart. But orders aren’t dynamic—orders are receipts. This lesson is about making that receipt immutable and trustworthy . Tax rates change. Sometimes frequently. If your system recalculates tax for old orders using “today’s” rate, you’ll corrupt your financial history. The key behavior we want is: Updating a tax rate affects future carts and future checkouts. Updating a tax rate must not change existing orders. That’s what “snapshotting tax at checkout” means: at the moment checkout happens, we store tax_country, tax_rate_bps, tax_cents, and total_cents on the order. After that, reads (listOrders, getOrderById) must simply return those stored values—no recomputation. All of the work in this lesson lives in src/lib/repositories/ordersRepo.ts .

The Order Mapping Must Include Snapshot Fields

Before we even talk checkout, the simplest way to see “snapshotting” is in the order mapping: if a field exists in the DB row and we want it to be part of the domain order object, mapOrderRow(...) must include it. This function is the foundation for both listing and fetching orders, because both functions map rows through it. TypeScript// src/lib/repositories/ordersRepo.ts function mapOrderRow(r: OrderRow): Order { return { id: r.id, cart_id: r.cart_id, status: ["pending", "paid", "shipped", "cancelled"].includes(r.status) ? (r.status as Order["status"]) : "pending", subtotal_cents: r.subtotal_cents, tax_cents: r.tax_cents, total_cents: r.total_cents, currency: r.currency === "USD" ? "USD" : "USD", tax_country: r.tax_country ?? null, tax_rate_bps: r.tax_rate_bps, created_at: r.created_at, updated_at: r.updated_at, }; } tax_country and tax_rate_bps are the “snapshot metadata” that explain why an order’s tax looks the way it does. Without them, the order would have numbers but no context. tax_cents and total_cents are mapped directly from the row and should be treated as final values once stored. This mapping function is intentionally “dumb”: it doesn’t compute totals or call out to other tables. That’s a critical theme of this lesson—reads should not recompute.

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

List and Fetch Must Return the Same Snapshot Fields

Both list and fetch call mapOrderRow(...), which means once mapping is correct, both endpoints become consistent automatically. TypeScript// src/lib/repositories/ordersRepo.ts export async function listOrders( page: number, pageSize: number, ): Promise<Order[]> { const offset = (page - 1) * pageSize; const rows = await query<OrderRow>( `SELECT * FROM orders ORDER BY created_at DESC LIMIT $1 OFFSET $2`, [pageSize, offset], ); return rows.map(mapOrderRow); } Listing orders returns the snapshot fields because the SQL selects * and the mapping includes tax_country and tax_rate_bps. This is exactly what you want for an orders list UI: it can show totals and the rate used without additional DB lookups. Nothing in listOrders queries tax_rates, which preserves the snapshot guarantee. Fetching one order by ID should behave the same way—plus items. TypeScript// src/lib/repositories/ordersRepo.ts export async function getOrderById(id: string): Promise<Order | null> { const rows = await query<OrderRow>("SELECT * FROM orders WHERE id = $1", [ id, ]); if (!rows[0]) return null; const order = mapOrderRow(rows[0]); const items = await query<OrderItemRow>( `SELECT * FROM order_items WHERE order_id = $1 ORDER BY created_at ASC`, [id], ); order.items = items.map(mapItemRow); return order; } The order-level amounts (subtotal_cents, tax_cents, total_cents) come straight from the orders table and should remain unchanged. Items are loaded from order_items, which is also snapshot-based (unit_price_cents is stored at checkout). The critical constraint for this lesson: do not query tax_rates in getOrderById and do not “fix up” tax/total during reads. Orders are receipts.

Checkout Must Snapshot Tax Inside the Transaction

The heart of this lesson is createOrderFromCart(...). Checkout is the one moment where it’s valid to look at current state (cart, items, inventory, tax configuration) and convert it into a permanent order snapshot. This function already uses withTransaction, and that’s non-negotiable: inventory checks + inventory decrement + totals + inserts must be consistent. TypeScript// src/lib/repositories/ordersRepo.ts export async function createOrderFromCart(params: { orderId: string; cartId: string; }): Promise<Order> { const { orderId, cartId } = params; return withTransaction(async (client: DbClient) => { const cartRows = await client.query<CartSnapshotRow>( "SELECT id, status, tax_country FROM carts WHERE id = $1 FOR UPDATE", [cartId], ); const cart = cartRows.rows[0]; if (!cart) { throw new CheckoutError("CART_NOT_FOUND"); } if (cart.status !== "open") { throw new CheckoutError("CART_NOT_OPEN"); } // ... }); }// src/lib/repositories/ordersRepo.ts export async function createOrderFromCart(params: { orderId: string; cartId: string; }): Promise<Order> { const { orderId, cartId } = params; return withTransaction(async (client: DbClient) => { const cartRows = await client.query<CartSnapshotRow>( "SELECT id, status, tax_country FROM carts WHERE id = $1 FOR UPDATE", [cartId], ); const cart = cartRows.rows[0]; if (!cart) { throw new CheckoutError("CART_NOT_FOUND"); } if (cart.status !== "open") { throw new CheckoutError("CART_NOT_OPEN"); } // ... }); } The cart row is locked with FOR UPDATE, and importantly it selects tax_country. That ensures the checkout sees a stable tax country during the transaction. If the cart doesn’t exist, checkout throws a CheckoutError("CART_NOT_FOUND"). This is a business outcome handled upstream (service layer maps it). If the cart isn’t open, we throw CART_NOT_OPEN. That prevents checkout from running twice or from modifying a finalized cart.

Lock Items and Products So Inventory and Totals Are Stable

Next, checkout locks cart items and the relevant product rows. This prevents race conditions like two checkouts trying to buy the last unit at the same time. TypeScript// src/lib/repositories/ordersRepo.ts const itemsResult = await client.query<CartItemSnapshotRow>( `SELECT product_id, quantity, unit_price_cents FROM cart_items WHERE cart_id = $1 ORDER BY created_at ASC FOR UPDATE`, [cartId], ); if (itemsResult.rows.length === 0) { throw new CheckoutError("EMPTY_CART"); } const productIds = [...new Set(itemsResult.rows.map((r) => r.product_id))]; const productsResult = await client.query<ProductInventoryRow>( `SELECT id, currency, inventory_count FROM products WHERE id = ANY($1::uuid[]) FOR UPDATE`, [productIds], ); const productMap = new Map(productsResult.rows.map((p) => [p.id, p]));// src/lib/repositories/ordersRepo.ts const itemsResult = await client.query<CartItemSnapshotRow>( `SELECT product_id, quantity, unit_price_cents FROM cart_items WHERE cart_id = $1 ORDER BY created_at ASC FOR UPDATE`, [cartId], ); if (itemsResult.rows.length === 0) { throw new CheckoutError("EMPTY_CART"); } const productIds = [...new Set(itemsResult.rows.map((r) => r.product_id))]; const productsResult = await client.query<ProductInventoryRow>( `SELECT id, currency, inventory_count FROM products WHERE id = ANY($1::uuid[]) FOR UPDATE`, [productIds], ); const productMap = new Map(productsResult.rows.map((p) => [p.id, p])); Cart items are selected with FOR UPDATE, which ensures the item snapshot used for subtotal (quantity × unit_price_cents) cannot change mid-checkout. Products are also locked with FOR UPDATE so inventory checks and decrements are safe from race conditions. Notice that item rows include unit_price_cents. That’s the same snapshot principle you used earlier for carts: checkout uses snapshot item prices, not “current product price.”

Inventory Checks and Decrement Are Part of the Same Atomic Flow

Checkout gathers required quantities per product, verifies availability, then decrements inventory. All of this happens inside the same transaction so it’s “all or nothing”. TypeScript// src/lib/repositories/ordersRepo.ts const productQuantities = new Map<string, number>(); for (const item of itemsResult.rows) { const currentQty = productQuantities.get(item.product_id) ?? 0; productQuantities.set(item.product_id, currentQty + item.quantity); } for (const [productId, requiredQty] of productQuantities.entries()) { const product = productMap.get(productId); if (!product || product.inventory_count < requiredQty) { throw new CheckoutError("INSUFFICIENT_INVENTORY", { productId, available: product?.inventory_count ?? 0, }); } } for (const [productId, requiredQty] of productQuantities.entries()) { await client.query( "UPDATE products SET inventory_count = inventory_count - $1, updated_at = now() WHERE id = $2", [requiredQty, productId], ); } The first loop aggregates quantities per product before performing inventory checks. In this codebase, the cart_items table enforces UNIQUE (cart_id, product_id), and the add-to-cart logic increments the existing row rather than inserting duplicates. That means a cart normally has only one row per product. The aggregation loop is still a useful defensive pattern: it ensures the inventory check operates per product ID even if the query shape changes in the future or if multiple rows somehow appear (for example, after a schema change or a bulk import). It keeps the checkout logic correct without relying too heavily on assumptions about the cart structure. INSUFFICIENT_INVENTORY is thrown if the product is missing or doesn’t have enough stock. Because this is inside a transaction, nothing is decremented if the check fails. The decrement is done after all checks pass, which reduces the chance of partial state changes. Combined with the transaction, it ensures consistency.

Snapshotting Tax Rate at Checkout (The Core of the Lesson)

Now we compute the subtotal, resolve the tax rate basis points once, compute tax_cents, and compute total_cents. These values are then inserted into the orders table as permanent snapshot fields. TypeScript// src/lib/repositories/ordersRepo.ts const subtotal = itemsResult.rows.reduce( (acc, r) => acc + r.quantity * r.unit_price_cents, 0, ); const taxCountry = cart.tax_country ?? null; let taxRateBps = DEFAULT_TAX_RATE_BPS; if (taxCountry) { const rateRows = await client.query<{ rate_bps: number }>( "SELECT rate_bps FROM tax_rates WHERE country_code = $1", [taxCountry], ); taxRateBps = rateRows.rows[0]?.rate_bps ?? DEFAULT_TAX_RATE_BPS; } const tax = computeTaxCents(subtotal, taxRateBps); const total = subtotal + tax; const currency = (productMap.get(itemsResult.rows[0].product_id) ?.currency ?? "USD") as Order["currency"];// src/lib/repositories/ordersRepo.ts const subtotal = itemsResult.rows.reduce( (acc, r) => acc + r.quantity * r.unit_price_cents, 0, ); const taxCountry = cart.tax_country ?? null; let taxRateBps = DEFAULT_TAX_RATE_BPS; if (taxCountry) { const rateRows = await client.query<{ rate_bps: number }>( "SELECT rate_bps FROM tax_rates WHERE country_code = $1", [taxCountry], ); taxRateBps = rateRows.rows[0]?.rate_bps ?? DEFAULT_TAX_RATE_BPS; } const tax = computeTaxCents(subtotal, taxRateBps); const total = subtotal + tax; const currency = (productMap.get(itemsResult.rows[0].product_id) ?.currency ?? "USD") as Order["currency"]; Subtotal uses item snapshot prices (unit_price_cents), so the receipt reflects what was actually in the cart at checkout time. Resolving taxRateBps follows the exact rule your practices will enforce: If tax_country is set → attempt to load rate_bps from tax_rates. If no row exists (or tax_country is unset) → fall back to DEFAULT_TAX_RATE_BPS. computeTaxCents(subtotal, taxRateBps) ensures consistent basis-point math and rounding behavior across the codebase. Currency is derived from one of the products, with "USD" as the fallback. This is consistent with how the project normalizes currency elsewhere.

Inserting the Order With Snapshot Fields

This is where “snapshotting” becomes real: we store tax metadata and computed totals directly in the orders row. TypeScript // src/lib/repositories/ordersRepo.ts const orderRows = await client.query<OrderRow>(`INSERT INTO orders (id, cart_id, status, subtotal_cents, tax_cents, total_cents, currency, tax_country, tax_rate_bps) VALUES ($1,$2,'pending',$3,$4,$5,$6,$7,$8) RETURNING *`, [orderId, cartId, subtotal, tax, total, currency, taxCountry, taxRateBps],); const order = mapOrderRow(orderRows.rows[0]); tax_country and tax_rate_bps are stored on the order so future reads can show “what rate was applied” without looking anywhere else. tax_cents and total_cents are stored as snapshot amounts. Once written, they must not be recomputed during reads. RETURNING * lets us immediately map the inserted order row into the domain object using mapOrderRow, ensuring a consistent response shape. The order status is set to'pending' at creation, matching the lifecycle you implemented earlier.

Snapshotting Line Items and Finalizing the Cart

The order isn’t complete without snapshotting purchased line items and marking the cart as checked out—still inside the same transaction. TypeScript// src/lib/repositories/ordersRepo.ts for (const r of itemsResult.rows) { await client.query<OrderItemRow>( `INSERT INTO order_items (id, order_id, product_id, quantity, unit_price_cents) VALUES ($1,$2,$3,$4,$5)`, [randomUUID(), order.id, r.product_id, r.quantity, r.unit_price_cents], ); } await client.query( "UPDATE carts SET status = $1, updated_at = now() WHERE id = $2", ["checked_out", cartId], ); return order; Each order item stores unit_price_cents, which is the item-level snapshot that prevents price changes from affecting past orders. The cart status update to "checked_out" is the final “commit” of checkout. Since we’re still in the transaction, we only reach this line if all prior steps succeed. Returning the order at the end gives the caller the snapshot totals and metadata immediately.

Orders Must Not Change

The Order Mapping Must Include Snapshot Fields

Join the 1M+ learners on CodeSignal

List and Fetch Must Return the Same Snapshot Fields

Checkout Must Snapshot Tax Inside the Transaction

Lock Items and Products So Inventory and Totals Are Stable

Inventory Checks and Decrement Are Part of the Same Atomic Flow

Snapshotting Tax Rate at Checkout (The Core of the Lesson)

Inserting the Order With Snapshot Fields

Snapshotting Line Items and Finalizing the Cart

The Most Important Rule: Never Recompute Tax When Reading Orders

Recap and Course Finish