Step 1: List every entity in the system
  - User, Order, OrderItem, Product, Category, Review, Shipment

Step 2: For each entity, write every query in plain English
  - Get user by userId
  - Get all orders for a userId
  - Get order detail by orderId
  - Get all items in an order
  - Get all orders in PENDING status (for the fulfillment queue)
  - Get all orders placed in the last 24 hours (for the dashboard)
  - Get all reviews for a productId
  - Get the 10 most recent reviews for a productId

Step 3: Assign key slot to each query
  - "Get user by userId" → PK: USER#userId, SK: PROFILE
  - "Get all orders for a userId" → PK: USER#userId, SK: begins_with ORDER#
  - "Get order detail by orderId" → PK: ORDER#orderId, SK: METADATA
  - "Get all items in an order" → PK: ORDER#orderId, SK: begins_with ITEM#
  - "Get all orders in PENDING status" → GSI1: PK: STATUS#pending, SK: orderId
  - "Get all reviews for a productId" → PK: PRODUCT#productId, SK: begins_with REVIEW#

Step 4: Add entities to the key assignments
  - New query cannot be served by existing key slots → revise the schema
  - New query can be served by adding a sparse GSI → add carefully (write amplification cost)
  - New query requires a full table scan → acceptable only for infrequent admin/batch use

// ❌ Entity-first modeling — three months of naive GSI accumulation
// Table design after modeling entities independently:
//   users:   PK = USER#userId,    SK = USER#userId
//   orders:  PK = ORDER#ordId,    SK = ORDER#ordId

// "Need orders by userId" → add GSI1: userId-index
// "Need orders by status" → add GSI2: status-index
// "Need open orders sorted by created date" → add GSI3: status-date-index
// "Need orders by region" → add GSI4: region-index
// Six months: 5 GSIs
//   Write amplification: 1 base table + 5 GSIs = 6× WCU per order write
//   Bill at 10K orders/day: 60K WCU-writes/day just for write amplification
//   Redesign migration: 4M existing records × full-table-scan + rewrite

// ✅ Access-pattern-first — 1 table, 1 GSI, all queries served

// All entity types share the same PK/SK attribute names
// The value prefix encodes the entity type and enables sort key range queries

// Key schema (TypeScript DynamoDB Document Client types):
interface BaseItem {
  PK: string    // Partition key — entity type + identifier (e.g., "USER#u42")
  SK: string    // Sort key — entity type or relationship (e.g., "PROFILE" or "ORDER#2026-01-15#o99")
  // Optional GSI keys (set only on items that should appear in that GSI)
  GSI1PK?: string
  GSI1SK?: string
  entityType: string  // Always set — enables filtering by entity type within a partition
}

// User profile item
// Query: "Get user by userId" → GetItem(PK=USER#u42, SK=PROFILE)
const userItem = {
  PK: 'USER#u42',
  SK: 'PROFILE',
  entityType: 'USER',
  email: 'alice@example.com',
  name: 'Alice',
  tier: 'PRO',
  createdAt: '2026-01-10T12:00:00Z',
}

// Order item — stored under user's partition
// Query: "Get all orders for userId" → Query(PK=USER#u42, SK begins_with ORDER#)
const orderUnderUser = {
  PK: 'USER#u42',
  SK: 'ORDER#2026-01-15#o99',  // ISO date prefix enables chronological sort
  entityType: 'ORDER',
  orderId: 'o99',
  status: 'PENDING',
  total: 149.99,
}

// Order item — also stored under its own partition for order-direct access
// Query: "Get order detail by orderId" → GetItem(PK=ORDER#o99, SK=METADATA)
const orderDirect = {
  PK: 'ORDER#o99',
  SK: 'METADATA',
  entityType: 'ORDER',
  userId: 'u42',
  status: 'PENDING',
  total: 149.99,
  // GSI key: enables "Get all orders in PENDING status" query
  GSI1PK: 'STATUS#PENDING',
  GSI1SK: '2026-01-15T12:00:00Z#o99', // ISO timestamp + orderId for chronological sort
}

// Sort key design with date prefix enables time-range queries without a GSI
// Query: "Get orders for user in January 2026"
//   → Query(PK=USER#u42, SK between ORDER#2026-01 and ORDER#2026-02)

const jan15Order = {
  PK: 'USER#u42',
  SK: 'ORDER#2026-01-15#o99', // ISO date → lexicographic sort = chronological sort
}
const jan22Order = {
  PK: 'USER#u42',
  SK: 'ORDER#2026-01-22#o103',
}
const feb01Order = {
  PK: 'USER#u42',
  SK: 'ORDER#2026-02-01#o108',
}

// KeyConditionExpression: SK between 'ORDER#2026-01' and 'ORDER#2026-02'
// Returns jan15Order and jan22Order — feb01Order excluded without a filter expression

// One-to-one: same PK, different SK discriminator
// User profile and user settings live in the same partition → single-round-trip fetch with BatchGet
const profile = { PK: 'USER#u42', SK: 'PROFILE', entityType: 'USER', name: 'Alice' }
const settings = { PK: 'USER#u42', SK: 'SETTINGS', entityType: 'USER_SETTINGS', theme: 'dark', locale: 'en-US' }
const billingInfo = { PK: 'USER#u42', SK: 'BILLING', entityType: 'USER_BILLING', stripeId: 'cus_abc' }

// Fetch all three with one TransactGetItems call:
// [GetItem(USER#u42/PROFILE), GetItem(USER#u42/SETTINGS), GetItem(USER#u42/BILLING)]

// One-to-many: parent PK, children prefixed under same partition
// Query: "Get all items in order o99" → Query(PK=ORDER#o99, SK begins_with ITEM#)
const orderMeta = { PK: 'ORDER#o99', SK: 'METADATA', entityType: 'ORDER', total: 149.99 }
const orderItem1 = { PK: 'ORDER#o99', SK: 'ITEM#prod-1', entityType: 'ORDER_ITEM', quantity: 2, unitPrice: 49.99 }
const orderItem2 = { PK: 'ORDER#o99', SK: 'ITEM#prod-2', entityType: 'ORDER_ITEM', quantity: 1, unitPrice: 49.99 }
// One Query call returns metadata + all line items — no joins, no second round trip

// Use case: User ↔ Group membership (many-to-many)
// Query 1: "Get all groups that user u42 belongs to"
//   → Query(PK=USER#u42, SK begins_with GROUP#)
// Query 2: "Get all members of group g7"
//   → Query(GSI1, GSI1PK=GROUP#g7, GSI1SK begins_with USER#)

// Item direction A: user → group (in user's partition)
const membershipA = {
  PK: 'USER#u42',      // User's partition
  SK: 'GROUP#g7',      // Sort key encodes the group
  entityType: 'MEMBERSHIP',
  joinedAt: '2026-01-10T12:00:00Z',
  role: 'member',
}

// Item direction B: group → user (in group's "partition" via GSI)
const membershipB = {
  PK: 'GROUP#g7',      // ← Different from item A: group's partition
  SK: 'USER#u42',      // ← Inverted: user is now the sort key
  entityType: 'MEMBERSHIP',
  joinedAt: '2026-01-10T12:00:00Z',
  role: 'member',
  // GSI key — enables query by group
  GSI1PK: 'GROUP#g7',
  GSI1SK: 'USER#u42',
}

// Write: both items written atomically with TransactWriteItems
await docClient.send(new TransactWriteCommand({
  TransactItems: [
    { Put: { TableName: 'AppTable', Item: membershipA } },
    { Put: { TableName: 'AppTable', Item: membershipB } },
  ]
}))

// Query 1: all groups for user u42
const userGroups = await docClient.send(new QueryCommand({
  TableName: 'AppTable',
  KeyConditionExpression: 'PK = :pk AND begins_with(SK, :prefix)',
  ExpressionAttributeValues: { ':pk': 'USER#u42', ':prefix': 'GROUP#' },
}))

// Query 2: all members of group g7 (via GSI)
const groupMembers = await docClient.send(new QueryCommand({
  TableName: 'AppTable',
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :pk AND begins_with(GSI1SK, :prefix)',
  ExpressionAttributeValues: { ':pk': 'GROUP#g7', ':prefix': 'USER#' },
}))

// Use case: "Get all orders in PENDING status"
// Only PENDING orders carry GSI1PK — other statuses do not → sparse index on status

// PENDING order → GSI1PK set → appears in GSI → findable via "pending orders" query
const pendingOrder = {
  PK: 'ORDER#o99',
  SK: 'METADATA',
  status: 'PENDING',
  GSI1PK: 'STATUS#PENDING', // ← set for PENDING orders only
  GSI1SK: '2026-01-15T12:00:00Z#o99',
}

// DELIVERED order → GSI1PK NOT set → does NOT appear in GSI → no write amplification
const deliveredOrder = {
  PK: 'ORDER#o88',
  SK: 'METADATA',
  status: 'DELIVERED',
  // GSI1PK and GSI1SK intentionally absent
  // This item is invisible to GSI1 and costs 0 additional WCU for GSI writes
}

// Query: "Get all pending orders, sorted by date"
const pendingOrders = await docClient.send(new QueryCommand({
  TableName: 'AppTable',
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :status',
  ExpressionAttributeValues: { ':status': 'STATUS#PENDING' },
  ScanIndexForward: true, // chronological order via GSI1SK
}))

// GSI1 serves TWO completely different queries:
// Query A: "Get all reviews for a productId"
// Query B: "Get all orders in PENDING status"

// Review item — uses GSI1 for product review listing
const review = {
  PK: 'USER#u42',
  SK: 'REVIEW#prod-1#2026-01-15',
  entityType: 'REVIEW',
  rating: 5,
  text: 'Excellent product',
  GSI1PK: 'PRODUCT#prod-1',    // ← Route this entity type through GSI1
  GSI1SK: 'REVIEW#2026-01-15T12:00:00Z',
}

// Order item — also uses GSI1 but with different key semantics
const order = {
  PK: 'ORDER#o99',
  SK: 'METADATA',
  entityType: 'ORDER',
  status: 'PENDING',
  GSI1PK: 'STATUS#PENDING',    // ← Same GSI1, different prefix
  GSI1SK: '2026-01-15T12:00:00Z#o99',
}

// Both queries served by one GSI — not two:
// Reviews:  Query(GSI1, GSI1PK=PRODUCT#prod-1)
// Orders:   Query(GSI1, GSI1PK=STATUS#PENDING)
// FilterExpression: entityType = 'REVIEW' or 'ORDER' if mixed results are a concern