---
name: dynamodb-table-design
description: "Designs a DynamoDB single-table structure from a documented set of access patterns. Use when the user has an access patterns .md file (from dynamodb-access-patterns skill) and wants to design partition keys, sort keys, GSIs, composite keys, and map every access pattern to a concrete DynamoDB query operation. This is Step 2 of a 3-step pipeline: access patterns -> table design -> query interfaces."
---

# DynamoDB Table Design

This skill takes a formalized access patterns document and produces a complete DynamoDB single-table design. It maps every access pattern to a concrete DynamoDB operation with explicit key conditions.

## When to Use

- The user has an access patterns `.md` file (from `dynamodb-access-patterns` or manually written)
- The user wants to design PK/SK structures for their entities
- The user wants to decide which GSIs they need
- The user wants to verify that every access pattern is covered by the table design
- The user wants to add a new entity to an existing table design

## Pipeline Position

```
  1. Access Patterns  -->  [2. Table Design]  -->  3. Query Interfaces
  (dynamodb-access-       (this skill)           (dynamodb-query-
   patterns)                                      interfaces)
```

**Input**: Access patterns `.md` file
**Output**: Table design `.md` file consumed by `dynamodb-query-interfaces`

---

## Prerequisites

Before starting, read the access patterns file. If it doesn't exist or is incomplete, tell the user to run the `dynamodb-access-patterns` skill first.

If the user already has a table in production and wants to add entities, also read their existing table design file (if any) to understand current keys and GSIs.

---

## Design Workflow

### Step 1: Read and Validate Access Patterns

Read the access patterns file and verify:

1. Every access pattern has an ID (AP-XXX)
2. Every pattern specifies input parameters and result type
3. Multi-entity patterns are explicitly marked
4. Write patterns specify transaction requirements

If anything is missing, ask the user to clarify before proceeding.

### Step 2: Group Entities into Item Collections

This is the core of single-table design. Analyze the access patterns to determine which entities should share a partition key.

**Rules for grouping:**

1. **Entities fetched together in a multi-entity read MUST share a PK.**
   - If AP-003 fetches User + Orders together, they must share a PK.

2. **1:N relationships where the parent is always known go under the parent's PK.**
   - Orders belong to a User -> `PK = USER#userId`

3. **Entities accessed only by their own ID get their own PK.**
   - A Product looked up by productId -> `PK = PRODUCT#productId`

4. **Global collections (list all, leaderboard) use a static PK.**
   - All products catalog -> `PK = PRODUCTS`

5. **N:N or deeply nested relationships use composite PKs.**
   - Items in a specific cart -> `PK = USER#userId#CART#cartId`

Present the proposed grouping to the user:

```
Based on your access patterns, here's how I'd group entities:

Item Collection 1: User scope
  PK = USER#<userId>
  Contains: User, Order, Balance, Preferences
  Supports: AP-001, AP-002, AP-003, AP-005

Item Collection 2: Product catalog
  PK = PRODUCTS
  Contains: Product (catalog entries)
  Supports: AP-006, AP-007

Item Collection 3: Product detail
  PK = PRODUCT#<productId>
  Contains: Product (full record), Review
  Supports: AP-008, AP-009, AP-010

Does this grouping make sense? Any patterns I'm missing?
```

### Step 3: Design Primary Keys

For each item type, define the PK and SK.

**Key Design Principles:**

#### Separator Convention
Use `#` as the separator between key segments. This is the universal DynamoDB convention.

```
PK = ENTITY_PREFIX#identifier
SK = ENTITY_PREFIX#identifier
```

#### PK Patterns

| Pattern | Format | Use Case |
|---|---|---|
| Entity-scoped | `ENTITY#<id>` | Self-lookup, entity owns its collection |
| Parent-scoped | `PARENT#<parentId>` | Children queried under a parent |
| Static collection | `COLLECTION_NAME` | Global lists, catalogs, leaderboards |
| Hierarchical | `PARENT#<id>#CHILD#<childId>` | Deep nesting (use sparingly) |

#### SK Patterns

| Pattern | Format | Use Case |
|---|---|---|
| Same as PK | `ENTITY#<id>` | 1:1 self-referencing record |
| Child entity | `CHILD_TYPE#<childId>` | Items in a collection |
| Composite | `TYPE#<sortField>#<uniqueId>` | Multi-field sorting |
| Metadata | `METADATA` or `COUNTER` | Singleton items in a collection |
| Timestamp-prefixed | `TYPE#<ISO-timestamp>#<id>` | Time-ordered collections |

#### Sort Key Design for Range Queries

If an access pattern needs sorting or range queries, the sort field MUST be encoded in the SK:

```
# Sort by date (newest first -- use ScanIndexForward=false)
SK = ORDER#2024-01-15T10:30:00Z#01HORDERID

# Sort by score (string-encoded numbers must be zero-padded)
SK = SCORE#000150#01HPLAYERID    (score=150, padded to 6 digits)

# Sort by status + date (hierarchical sort)
SK = ORDER#SHIPPED#2024-01-15T10:30:00Z#01HORDERID
```

**Zero-padding rule**: When numbers are used in string sort keys, they MUST be zero-padded to a fixed width so lexicographic order matches numeric order. Decide the maximum expected value and pad accordingly.

**Descending order trick**: To sort descending without `ScanIndexForward=false`, invert the value:
```
SK = SCORE#<(MAX_SCORE - actualScore) zero-padded>#<id>
```

### Step 4: Identify GSI Requirements

For each access pattern NOT covered by the table's PK/SK, determine if a GSI is needed.

**You need a GSI when:**

| Situation | Example |
|---|---|
| Query by a non-key attribute | "Find user by email" (table PK is userId) |
| Different sort order on same collection | "Orders by total" (table SK sorts by date) |
| Inverted relationship | "Find all orders containing product X" |
| Global sorted view | "Leaderboard sorted by score" |

**GSI Design Rules:**

1. **Reuse GSIs aggressively.** DynamoDB allows max 20 GSIs per table. Each GSI costs additional storage and write capacity. Design GSI keys to be overloaded across entity types when possible.

2. **Name GSIs generically.** Use `GSI1`, `GSI2`, `GSI3` for overloaded indexes. Use descriptive names only for single-purpose indexes (e.g., `email-index`).

3. **GSI key attributes follow the naming convention:**
   - Overloaded: `GSI1PK` (String), `GSI1SK` (String)
   - Single-purpose: The actual attribute name (e.g., `email` as PK, `createdAt` as SK)

4. **Choose the right projection:**
   - `ALL` -- projects all attributes (default, simplest, most expensive)
   - `KEYS_ONLY` -- only key attributes (cheapest, requires table fetch for other attributes)
   - `INCLUDE` -- specific attributes (middle ground)

5. **Sparse indexes are powerful.** If only some items have the GSI attributes, only those items appear in the index. Use this to create filtered views.

Present GSI decisions to the user:

```
I need the following GSIs:

GSI1 (overloaded, String/String):
  - User lookup by email: GSI1PK=EMAIL#<email>, GSI1SK=EMAIL#<email>
  - Order leaderboard: GSI1PK=ORDERS, GSI1SK=ORDER#<total-inverted>#<orderId>
  Projection: ALL

No additional GSIs needed. Total: 1 GSI.

Does this look right? Are there access patterns I should reconsider?
```

### Step 5: Map Every Access Pattern to a DynamoDB Operation

This is the accountability step. EVERY access pattern from the input file must be mapped to a concrete DynamoDB operation.

For each access pattern, define:

| Field | Description |
|---|---|
| **AP ID** | Reference to the access pattern |
| **DynamoDB Operation** | Query, GetItem, BatchGetItem, PutItem, TransactWriteItems, UpdateItem, DeleteItem |
| **Table or GSI** | Which index to use |
| **PK Value** | Exact partition key value (with placeholders) |
| **SK Condition** | `=`, `begins_with`, `>`, `<`, `>=`, `<=`, `BETWEEN` |
| **SK Value(s)** | The sort key value(s) |
| **Additional Parameters** | Limit, ScanIndexForward, FilterExpression, ProjectionExpression |
| **Condition Expression** | For writes: conditions that must be met |

Example:
```
AP-001: Get user by userId
  Operation: Query
  Table: Main
  PK: USER#<userId>
  SK: = USER#<userId>
  Params: Limit=1

AP-002: List orders for user, newest first
  Operation: Query
  Table: Main
  PK: USER#<userId>
  SK: begins_with(ORDER#)
  Params: ScanIndexForward=false, Limit=20, paginated

AP-010: Create order
  Operation: TransactWriteItems
  Items:
    1. Put: PK=USER#<userId>, SK=ORDER#<timestamp>#<orderId> (Condition: attribute_not_exists(pk))
    2. Put: PK=ORDER#<orderId>, SK=ORDER#<orderId> (Condition: attribute_not_exists(pk))
    3. Update: PK=USER#<userId>, SK=ORDER_COUNT (SET count = count + 1)
```

### Step 6: Validate the Design

**Read `reference/validation-checklist.md` and run through every check with the user before finalizing.**

---

## Output Format

Persist the output as a markdown file. Suggest `docs/dynamodb/table-design.md` or alongside the access patterns file.

**Read `reference/output-template.md` for the exact file structure to use.**

---

## Common Design Patterns

**Read `reference/design-patterns.md` for a catalog of reusable single-table design patterns.** Reference these when making key design decisions and present relevant patterns to the user.

---

## Rules for the Agent

1. **Read the access patterns file first.** Never design keys without documented access patterns.
2. **Map EVERY access pattern.** If a pattern can't be mapped, it's a design problem -- don't skip it.
3. **Prefer the base table over GSIs.** Only add a GSI when the base table genuinely can't serve the pattern.
4. **Be interactive.** Present groupings and key designs to the user for approval before finalizing.
5. **Show concrete examples.** For each entity, include a sample JSON item.
6. **Document decisions.** When there are trade-offs (e.g., duplication vs. extra query), explain both options and document the choice.
7. **Stay language-agnostic.** Use DynamoDB operation names (Query, GetItem, TransactWriteItems) not SDK-specific function names.
8. **Challenge hot partitions.** If a static PK like `PRODUCTS` will receive heavy traffic, discuss sharding strategies with the user.
9. **Consider write amplification.** If an entity is written to 3 item collections, that's 3x write cost. Make sure it's justified.
10. **Persist the file.** Always write the output to a `.md` file.

## Next Step

Once the table design file is complete, tell the user:

```
Table design is documented at [file path].

The next step is to generate the query interface -- method signatures
for every access pattern, ready to be implemented in your language of choice.

To continue, use the `dynamodb-query-interfaces` skill with this file as input.
```