1-- PostgreSQL schema with production-grade patterns
2 
3-- Users table: always use UUID primary keys for distributed systems
4CREATE TABLE users (
UUID over auto-increment: no coordination needed across database replicas
5  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
6  email       TEXT UNIQUE NOT NULL,
7  name        TEXT NOT NULL,
TIMESTAMPTZ stores timezone — use it. TIMESTAMP without TZ causes daylight saving bugs
8  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),  -- timezone-aware!
9  deleted_at  TIMESTAMPTZ                           -- soft delete, not hard delete
10);
11 
12-- Orders table: proper foreign keys with cascading
13CREATE TABLE orders (
14  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ON DELETE RESTRICT prevents orphaned orders. ON DELETE CASCADE would delete orders with users
15  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
Store money as integer cents, never as FLOAT. 0.1 + 0.2 = 0.30000000000000004 in floating point
16  status      TEXT NOT NULL CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
17  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),  -- store money as integers!
18  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
19);
20 
Composite index on (user_id, created_at) supports "get user orders sorted by date" in one index scan
21-- Index for common query pattern: user's orders
22CREATE INDEX idx_orders_user_id_created ON orders(user_id, created_at DESC);
23 
24-- ACID transaction: transfer money atomically
25BEGIN;
26  UPDATE accounts SET balance = balance - 10000 WHERE id = 'account-A';
27  UPDATE accounts SET balance = balance + 10000 WHERE id = 'account-B';
28  -- If any error here, entire transaction rolls back
29COMMIT;
30 
31-- Window functions: FAANG interview favorite
32SELECT
33  user_id,
34  order_id,
35  total_cents,
36  SUM(total_cents) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
37  RANK() OVER (PARTITION BY user_id ORDER BY total_cents DESC) AS order_rank
38FROM orders;

1// Read replica routing with Prisma
2import { PrismaClient } from '@prisma/client';
3 
4// Primary: all writes go here
5const primary = new PrismaClient({
Never mix connection pools — primary is for writes, replica for reads
6  datasources: { db: { url: process.env.DATABASE_PRIMARY_URL } }
7});
8 
9// Replica: reads only
10const replica = new PrismaClient({
11  datasources: { db: { url: process.env.DATABASE_REPLICA_URL } }
12});
13 
14// Route based on operation type
15class DatabaseRouter {
Replica reads are eventually consistent. Replication lag is typically 10-100ms
16  async findUser(id: string) {
17    // READ → replica (eventual consistency acceptable)
18    return replica.user.findUnique({ where: { id } });
19  }
20 
21  async createOrder(data: OrderCreateInput) {
22    // WRITE → primary (strong consistency required)
23    return primary.order.create({ data });
Post-write reads should go to primary to avoid read-after-write inconsistency
24  }
25 
26  async getOrderForDisplay(id: string) {
27    // Reads that MUST be fresh (after a write) → primary
28    return primary.order.findUnique({
29      where: { id },
30      include: { user: true, items: true }
31    });
32  }
33}
34 
35// ⚠️ Read-after-write problem:
36// User creates order → replica may not have it yet
37// Solution: For 1 second after writes, route reads to primary
38// Or: use a cache to store "just created" IDs