What is this?

Orders DB is the primary database for the Orders domain. It is a PostgreSQL 14 database and acts as the system of record for orders and their line items. If you are looking for where an order lives, how to query it, or how order state changes are persisted, you are in the right place.

What does it store?

  • Orders: One row per customer order. Includes status, totals, currency, timestamps.
  • Order Lines: One row per item inside an order. Includes product, quantities, and pricing.
  • Relationships: order_lines are linked to orders by order_id (cascading deletes are enabled).

Schema and test data for Orders DB

High-level data model

  • An order has many order_lines.
  • Order lifecycle is tracked via the status column. Common statuses: PENDING, PAID, CANCELLED, FULFILLED, REFUNDED.
  • Monetary values are stored as integer cents; currency is 3-letter ISO (e.g. USD).

Common queries

-- Fetch a single order with its lines
SELECT o.*, l.*
FROM orders o
LEFT JOIN order_lines l USING (order_id)
WHERE o.order_id = $1;


-- List recent orders for a customer (paged)
SELECT *
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT $2 OFFSET $3;


-- Daily revenue (rounded to dollars) for the last 30 days
SELECT date_trunc('day', created_at) AS day,
       SUM(total_amount_cents)/100.0 AS revenue
FROM orders
WHERE created_at >= now() - interval '30 days'
  AND status IN ('PAID','FULFILLED','REFUNDED')
GROUP BY 1
ORDER BY 1;

How data gets here (lifecycle)

  1. A user places an order → OrdersService validates input and writes to orders and order_lines in a single transaction.
  2. Payment success updates the order status to PAID.
  3. Fulfillment updates the order status to FULFILLED.
  4. Cancellations and refunds update status accordingly.
  5. Each change emits a domain event that other services consume.

Access patterns and guidance

  • Use the order_id for point lookups; most queries should be indexed by this or customer_id.
  • Prefer reading from read replicas for analytics/reporting workloads when available.
  • Treat status as the source of truth for order lifecycle; avoid inferring state from timestamps alone.
  • Do not store PII beyond customer_id here; card data never resides in this database.

Retention and residency

  • Retention: 7 years (see frontmatter). Archival or partitioning may be used to keep hot data smaller.
  • Residency: eu-west-1 (see frontmatter). Ensure cross-region access complies with data policies.

Backups and recovery

  • Automated daily snapshots, plus point-in-time recovery retained per platform policy.
  • Test restores should be performed regularly to validate RPO/RTO.

Security

  • Access is role-based. OrdersService has read/write. Downstream services typically have read-only.
  • Enforce least privilege at the database role level. Avoid ad-hoc superuser connections.

Operational notes

  • Consider time-based partitioning on orders.created_at for very large datasets.
  • Monitor bloat and autovacuum; ensure vacuum_analyze runs regularly.
  • Keep indexes targeted; avoid over-indexing write-heavy columns.

Local development

  • Connection string: provided via ORDERS_DB_URL environment variable.
  • Seed data: use project seed scripts to create sample orders and lines.

Gotchas

  • Deleting an order cascades to order_lines due to ON DELETE CASCADE. Prefer status changes over deletes in most cases.
  • Monetary math should be done in integer cents to avoid float rounding issues.

If you’re unsure which table to use or how to fetch something, start with the orders table by order_id, then join order_lines as needed. For more, see the OrdersService documentation and related domain events.

Edit this page