Welcome back! 👋 Up to this point, you’ve built out the core order workflow—checkout creates orders, and pay/cancel moves them through a basic lifecycle. Now we’re going to add the missing piece that makes checkout totals feel “real”: configurable tax rates that can be managed through an API and stored in Postgres.

In this lesson, you’ll build a Tax Rates Configuration API that lets clients list all tax rates, fetch one country’s rate, save (create/update) a rate, and delete a rate. You’ll see how this codebase keeps routes thin, centralizes rules and validation in the service layer, and uses a repository layer for parameterized SQL.

Previously… you completed the Orders API and its action-based state transitions (pay/cancel). That gave us a stable order lifecycle; now we’re ready to support pricing rules that vary by location by adding a tax rate configuration system that other parts of the backend (like checkout) can rely on.

In e-commerce, tax rules vary by country (and often by region). Hardcoding a single percentage doesn’t scale—and it’s risky because tax rates change.

This project solves that by storing tax rates in a tax_rates table and exposing them through a small API:

• GET /api/tax/rates → list all configured rates (deterministic ordering)
• GET /api/tax/rates/:country → fetch one rate
• PUT /api/tax/rates/:country → create or update a rate (“save” behavior)
• DELETE /api/tax/rates/:country → delete a rate

A key detail: rates are stored as basis points (rate_bps)—an integer representation of a percentage—so we avoid floating point rounding issues in financial logic.

The repository is responsible for direct database access. It does not validate HTTP input or decide what response status to return—that’s the service layer’s job. In this codebase, repositories focus on: parameterized SQL, mapping DB rows to domain types, and returning “raw outcomes” like null or boolean.

The repository for tax rates lives in src/lib/repositories/taxRatesRepo.ts.

This first part defines the database row shape and converts it into the domain model TaxRate. The rest of the repository functions build on this mapping to keep everything consistent.

• TaxRateRow represents the exact column names coming back from Postgres (country_code, rate_bps, timestamps). Keeping this separate from the domain type makes it explicit what we get from SQL.
• mapTaxRateRow is a small but important abstraction: it centralizes how DB rows become a TaxRate, so every repository function returns a consistent shape.
• Even though the mapping looks “1:1” here, having a mapper pays off later if the DB schema changes or if you want to rename/transform fields at the boundary.

listTaxRates() returns all rows ordered by country_code ASC. That ordering is deliberate: it makes responses deterministic for UIs and tests.

• The ORDER BY country_code ASC ensures that two identical requests return results in the same order, which prevents flaky tests and inconsistent UI rendering.
• The repository returns a plain TaxRate[] and does not wrap it in HTTP responses or service result types—this keeps layers clean.
• Notice the generic: query<TaxRateRow>(...) tells the DB client what shape to expect, and the mapper turns those rows into domain objects.

getTaxRate() returns a single TaxRate if it exists, otherwise null. Returning null is intentional: the service layer converts that into a structured 404 error.

• $1 is a parameter placeholder, and [countryCode] is the value list. This is how we avoid SQL injection and keep queries safe.
• Returning null rather than throwing for “not found” keeps “missing data” as a normal business outcome, not an exception.
• The repository does not normalize or validate countryCode. It assumes the service layer gives it a clean value.

For configuration APIs, it’s common to want a single “save” call. This repo uses Postgres INSERT ... ON CONFLICT ... DO UPDATE so the same function handles both create and update.

• ON CONFLICT (country_code) means the country_code column is the unique key for determining conflicts. If the row exists, we update instead of failing.
• EXCLUDED.rate_bps is the “new attempted value” from the insert. Using it makes the update logic concise and avoids re-specifying parameters.
• updated_at = now() ensures updates are timestamped consistently by the database, rather than relying on app-side clocks.
• RETURNING * gives us the saved row (either inserted or updated), which is perfect for returning the canonical persisted value to callers.

Delete endpoints should be able to say “not found” when nothing was removed. This repo uses DELETE ... RETURNING * and then returns a boolean indicating whether a row actually existed.

• RETURNING * is doing the heavy lifting: if the delete removed a row, we get it back; if nothing matched, we get an empty array.
• Returning true/false is a clean repo-level signal. The service layer can turn false into a proper 404.
• This keeps the route/service logic simple and avoids a second query (“does it exist?”) before deleting.

The service layer is the “rulebook” for tax rates. It validates inputs (country codes and basis point bounds), calls repository functions, and returns a ServiceResult that routes can forward without re-implementing business logic.

This logic lives in src/lib/services/taxRatesService.ts.

Before any individual service function, this file defines shared error handling so all services behave consistently when Postgres throws.

• COUNTRY_CODE_RE and MAX_RATE_BPS are service-level rules: they define what inputs are allowed before we ever hit the database.
• handleException is crucial because it normalizes errors into a stable ServiceResult. Routes don’t need to guess whether to return 500 vs 409—they just forward the service’s structured result.
• isPostgresError + mapPostgresError ensures database failures become consistent API-safe error envelopes (instead of leaking raw SQL/driver details).
• The fallback branch returns INTERNAL_ERROR with a 500. That means truly unexpected errors still produce a consistent response shape rather than crashing the route handler.

Tax rates are keyed by country code, and the codebase enforces exactly two uppercase letters. Validation accepts unknown input and returns either { ok: true, value } or { ok: false, message }.

• Accepting unknown makes this validator reusable across contexts (route params, parsed JSON, future integrations) without assuming a type.
• trim().toUpperCase() ensures inputs like " us " normalize to "US". That prevents accidental duplicates and keeps the DB key consistent.
• The validation is intentionally strict: it checks both string length (min: 2, max: 2) and the regex. This redundancy makes it easier to return precise error messages and avoids edge-case inputs.
• Returning the normalized value means downstream code (services and repos) always receive the canonical country code.

Tax rates are stored as basis points, so this validator enforces an integer within 0..MAX_RATE_BPS.

• isInt ensures we never accept floats or strings like "725"—that matters because basis points should be represented as integers end-to-end.
• The upper cap (MAX_RATE_BPS = 5000) is a business constraint: it prevents unrealistic rates and protects the database from garbage values.
• Like country validation, this returns a clean value that’s safe to pass to the repository without further checks.

Each service function wraps repository calls in try/catch, returns ok(...) on success, and uses fail(...) for normal business errors like missing records.

• The list service is intentionally thin: listing tax rates has no special business branching beyond “did the DB call succeed?”
• Because the repository already orders results, the service doesn’t need to enforce ordering—it simply forwards the deterministic list.
• Catching exceptions here ensures DB errors are consistently mapped, even though the route also has a try/catch.

• This service converts null from the repository into a structured NOT_FOUND error with a 404. That’s why the repo returns null instead of throwing.
• Notice that countryCode is assumed to already be valid (normalized two-letter code). In this project, the route validates the param before calling the service.
• Returning ServiceResult<TaxRate> makes routes trivial: they can forward without interpreting business meaning.

Now we expose the tax rates to clients using Next.js route handlers. The collection route lives at src/app/api/tax/rates/route.ts and supports GET for listing all tax rates.

• This route is a textbook “thin handler”: it calls one service function, checks ok, and returns either success(...) or error(...).
• On failure, it forwards the service-provided code, message, optional details, and httpStatus. That keeps HTTP behavior consistent and avoids duplicating status logic in multiple routes.
• The outer try/catch is a final safety net. In normal conditions, DB exceptions should already be mapped in the service layer, but this guarantees the route never crashes ungracefully.

The dynamic route src/app/api/tax/rates/[country]/route.ts handles country-specific operations. A key pattern in this codebase is reading params using a Promise-based context.params.

This first code block shows the shared param extraction + validation approach used by all handlers in this file.

• RouteContext types params as a Promise, which is why handlers in this project read params via const { country } = await context.params;.
• Importing validateCountryCode and validateRateBps from the service layer reinforces the layering rule: routes validate request shape using shared validators, then delegate.
• parseJson is pulled in for PUT, where we need to safely parse request bodies and return consistent errors for invalid JSON.

This handler validates the :country param, calls the service, and forwards the result.

• The handler validates the route param before doing any work. That prevents unnecessary DB calls and guarantees consistent 400 responses for malformed input.
• validateCountryCode(country) returns a normalized two-letter code, and the handler passes validCountry.value into the service. That ensures every service call uses canonical keys like "US".
• The route does not implement “not found” logic directly. It simply forwards the service’s NOT_FOUND error (404) if the repository returns null.
• success(rate.value) returns the domain object in a consistent response envelope, making UI/client consumption predictable.

PUT supports both create and update because the underlying repo uses UPSERT. This handler has two validations: the :country param and the JSON body’s rate_bps.

• parseJson(req) is used instead of req.json() directly so invalid JSON can become a consistent, structured 400 error rather than an unhandled exception.
• The payload is treated as { rate_bps?: unknown } so validation remains robust: we validate the presence/type/range of rate_bps rather than trusting the client.
• validateRateBps enforces basis points as an integer and applies the cap (0..5000). This is the main “business constraint” for saving rates.
• The route delegates to , which delegates to the repo UPSERT. That’s how a single PUT endpoint supports “create if missing” and “update if exists” without branching in the route.

Delete follows the same shape: validate param, call service, forward result.

• The handler validates :country exactly the same way as GET/PUT. That consistency is intentional: it makes the API predictable and easier to test.
• deleteTaxRateService converts “nothing deleted” into a NOT_FOUND 404, so the route doesn’t need to interpret boolean results itself.
• Returning success({ deleted: true }) gives clients a clear confirmation payload, rather than returning an empty body that might be ambiguous.

You now have a clean, production-style Tax Rates Configuration API built on the same layering patterns as the rest of the backend:

• Repository (src/lib/repositories/taxRatesRepo.ts) uses parameterized SQL and returns raw outcomes (TaxRate[], TaxRate | null, boolean), including UPSERT for save and DELETE ... RETURNING for real deletion checks.
• Service (src/lib/services/taxRatesService.ts) validates inputs (country codes + basis points), converts “missing” into structured 404s, and maps Postgres exceptions into stable API errors with isPostgresError + mapPostgresError.
• Routes (src/app/api/tax/rates/...) stay thin: validate route params, parse and validate JSON for PUT, delegate to services, and forward success(...) / error(...) consistently.

In the practices, you’ll implement these same functions and handlers yourself—focusing on safe SQL, strict validation, and keeping HTTP handlers lightweight and consistent.