📋 Create DynamoDB schema pattern validator agent

[sodre]

Summary

Create a Claude Code agent (or rule/skill) to automatically detect and warn about DynamoDB schema patterns that will fail at runtime. This would have prevented issues #168 and #179.

Motivation

We've hit the same DynamoDB limitation twice:

1. 🐛 fix(aggregator): DynamoDB UpdateExpression path overlap in update_snapshot() #168: Attempted SET #data = if_not_exists(...) ADD #data.counter :delta for snapshots
2. 🐛 Snapshot aggregator fails to capture usage with high rate limits (10M+ TPM) #179: Attempted same pattern for consumption counters

Both times, the code looked correct but failed at runtime with "overlapping document paths" error. An automated validator could catch these patterns during code review or development.

Proposed Solution

Option 1: Claude Code Agent (.claude/agents/dynamodb-validator.md)

An agent that can be invoked to validate DynamoDB UpdateExpression patterns:

---
name: dynamodb-validator
description: "Validate DynamoDB UpdateExpression patterns for common pitfalls"
tools: Grep, Read
---

You are a DynamoDB schema validator. Check for these anti-patterns:

1. **Overlapping SET + ADD paths**
   - Pattern: `SET #path = ... ADD #path.subpath :value`
   - Error: ValidationException - overlapping document paths
   - Fix: Use flat schema or separate operations

2. **Missing if_not_exists for atomic upserts**
   - Pattern: `ADD #path :value` without initialization
   - Error: Path doesn't exist on first write
   - Fix: Use flat schema with `SET attr = if_not_exists(attr, :zero) ADD attr :delta`

3. **Nested map initialization races**
   - Pattern: Assuming map exists for nested ADD
   - Error: Path doesn't exist
   - Fix: Use Put for creation, Update for modification

Option 2: Claude Code Rule (.claude/rules/dynamodb-patterns.md)

A passive rule that's always loaded and provides context:

# DynamoDB Schema Patterns

## Known Limitations

### SET + ADD Overlapping Paths
DynamoDB throws "overlapping document paths" when you:
- SET a map path AND ADD to paths within that map in the same expression

**This FAILS:**
```python
UpdateExpression="SET #data = if_not_exists(#data, :map) ADD #data.counter :delta"

This WORKS (flat schema):

UpdateExpression="SET attr = if_not_exists(attr, :zero) ADD counter :delta"

When Implementing DynamoDB Operations

Always ask: "Does this UpdateExpression have SET and ADD targeting overlapping paths?"

### Option 3: Pre-commit Skill

A skill that runs before commits to scan for dangerous patterns:

```bash
/validate-dynamodb

Scans repository for UpdateExpression strings and validates them.

Acceptance Criteria

• Validator catches SET #path ... ADD #path.subpath pattern
• Validator explains the issue and suggests fix
• Validator is discoverable (documented in CLAUDE.md)
• Test cases cover both valid and invalid patterns
• Integration with existing design-validator agent (if applicable)

Implementation Notes

Consider integrating with the existing design-validator agent since both deal with "patterns that look correct but fail at runtime under certain conditions."

Related Issues

• 🐛 fix(aggregator): DynamoDB UpdateExpression path overlap in update_snapshot() #168 - Snapshot overlapping paths issue
• 🐛 Snapshot aggregator fails to capture usage with high rate limits (10M+ TPM) #179 - Consumption counter blocked by same issue
• 📋 Revalidate nested data.M schema design before v1.0.0 #180 - Schema revalidation task

[sodre]

Resolved by creating .claude/rules/dynamodb-patterns.md which covers:

• Flat schema mandate (ADR-111) — prevents nested data.M anti-patterns entirely
• Reserved words — ExpressionAttributeNames requirements
• Overlapping SET + ADD paths — anti-pattern from 🐛 fix(aggregator): DynamoDB UpdateExpression path overlap in update_snapshot() #168 with correct alternative
• Missing if_not_exists guards — first-write safety
• Derivation from token deltas — anti-pattern from 🐛 Snapshot aggregator fails to capture usage with high rate limits (10M+ TPM) #179, references design-validator agent

The rule is auto-loaded by Claude Code for all DynamoDB-related development. Combined with the DynamoDB plugin (dynamodb@zeroae.com) for schema-level auditing, this provides coverage at both the schema design and code-level expression layers.