Building TCTF's DynamoDB Query Framework, Part 1: Single-Table Design Patterns

01Why Build a Query Framework?

When you have 34 microservices all talking to DynamoDB, the naive approach is to write queries inline — directly in each Lambda handler, using the AWS SDK's DocumentClient. It works at first. But it does not scale.

The first problem is scattered queries. Every handler constructs its own PK/SK values, its own query parameters, its own error handling. The same query pattern — get user by ID, check rate limit, fetch session — gets written dozens of times across dozens of services. When you need to change how a query works (add a filter, change a projection, handle a new error code), you change it in dozens of places. Miss one and you have a bug.

The second problem is readability. Raw DynamoDB queries are verbose. A simple get-by-ID operation requires constructing a params object with TableName, Key (with PK and SK objects), and optionally ProjectionExpression and ExpressionAttributeNames. Multiply that by every query in every handler and the business logic drowns in boilerplate.

The third problem is reuse. Without a shared layer, there is no reuse. Each service reinvents the same patterns — pagination, batch operations, conditional writes, transaction coordination. The code grows linearly with the number of services instead of logarithmically.

The fourth problem is testability. When queries are inline, testing a handler means mocking the DynamoDB client. When queries are in a shared framework, you test the framework once and the handlers test their business logic against a clean interface.

The fifth problem is maturity. Code that is written once and used everywhere gets battle-tested. Bugs are found and fixed in one place. Edge cases are handled once. Performance optimizations benefit every service. A query framework that serves 34 services is inherently more mature and reliable than 34 independent query implementations.

The sixth problem is cross-project portability. A well-designed query framework is not tied to one platform. Other projects — internal tools, partner integrations, new products — can adopt the same framework and immediately benefit from the patterns, the error handling, the type safety, and the test coverage that took months to build.

These are the reasons we built the TCTF DynamoDB query framework. The rest of this article covers the schema foundation it is built on. Part 2 covers the fluent query builder API. Part 3 covers transactions and advanced patterns.

🔧

Without a shared query framework, 34 services means 34 independent query implementations — scattered, duplicated, untested, and impossible to maintain. The framework turns that into one implementation, used everywhere, tested once, and battle-hardened by every service that depends on it.

02Why Single-Table Design

The traditional relational approach is one table per entity: a Users table, a Sessions table, a Posts table, a Connections table. In DynamoDB, this means one table per entity per service. With 34 services and 5-10 entity types per service, that is 170-340 tables to manage. Each table needs its own capacity settings, its own CloudWatch alarms, its own backup configuration, and its own CDK definition.

Single-table design collapses all entity types into one table per service. Users, sessions, achievements, and settings all live in the same table, differentiated by key prefixes. The user management service has one table. The billing service has one table. The social network service has one table.

The benefits are operational. Fewer tables means fewer things to monitor, fewer capacity configurations to tune, and fewer CDK resources to manage. It also means fewer DynamoDB API calls — a single query can fetch a user and their sessions in one round trip, because they share the same partition key.

The trade-off is complexity. The key schema must be designed carefully to support all access patterns. Adding a new access pattern might require a new GSI. And the table structure is not self-documenting — you need to understand the key prefixing convention to read the data. This article explains the conventions we chose and why.

📊

34 services × 5-10 entity types = 170-340 tables with one-table-per-entity. Single-table design: 34 tables total. Fewer tables, fewer ops, fewer round trips.

03PK/SK: The Generic Key Schema

Every TCTF DynamoDB table uses the same key schema: a string partition key named PK and a string sort key named SK. Both are generic — they do not represent a specific entity attribute. Instead, they use prefixed values that encode the entity type and the identifier.

A user profile has PK=USER#user123 and SK=PROFILE. A user session has PK=USER#user123 and SK=SESSION#sess-abc. An achievement has PK=USER#user123 and SK=ACHIEVEMENT#badge-1. A connection has PK=CONNECTION#user123 and SK=TARGET#user456.

The prefix (USER#, SESSION#, ACHIEVEMENT#, CONNECTION#, POST#) identifies the entity type. The value after the prefix is the entity's unique identifier. This convention means you can query all items for a user with PK=USER#user123 — the result includes their profile, sessions, achievements, and any other user-scoped entities.

The sort key enables range queries. SK begins_with SESSION# returns all sessions. SK begins_with ACHIEVEMENT# returns all achievements. SK=PROFILE returns exactly the profile. The sort key is the access pattern selector within a partition.

This PK/SK pattern is the foundation. Every entity in every service follows it. The consistency means any developer can look at any table and immediately understand the key structure.

🔑

PK=USER#user123, SK=PROFILE for the profile. SK=SESSION#sess-abc for a session. SK begins_with SESSION# for all sessions. The sort key is the access pattern selector.

04Entity Prefixing: The Convention That Makes It Work

Entity prefixing is the convention that makes single-table design readable. Without it, a table full of generic PK/SK values is impossible to navigate. With it, every item self-describes its type.

The prefix convention at TCTF follows a simple rule: the prefix is the entity type in uppercase, followed by a hash. USER#, SESSION#, POST#, CONNECTION#, ACHIEVEMENT#, RATELIMIT#, CIRCUIT#, CONFIG#. The hash separator makes prefixes unambiguous — USER#123 is clearly different from USERNAME#123.

Some entities use compound prefixes for hierarchical relationships. A comment on a post has PK=POST#post-789 and SK=COMMENT#comment-456. A reaction on a post has PK=POST#post-789 and SK=REACTION#user123. This groups all post-related items under the same partition key, enabling a single query to fetch a post with all its comments and reactions.

The prefix convention is documented and enforced. New entity types must follow the pattern. The repository layer (covered in Framework Series #12) uses the prefixes internally — the caller never constructs PK/SK values directly. They call getUserById(id) and the repository builds PK=USER#{id}, SK=PROFILE.

05GSI Strategies: Secondary Access Patterns

The primary key (PK/SK) serves the main access pattern — fetch entities by their owner or parent. But many access patterns need a different entry point. Find a user by email. List posts by date. Get a leaderboard by tier. Find reverse connections.

Global Secondary Indexes (GSIs) provide these secondary access patterns. Each TCTF table has 1-3 GSIs, each with its own PK/SK pair named GSI1PK/GSI1SK, GSI2PK/GSI2SK, etc.

GSI overloading is the key technique. The same GSI serves multiple access patterns by using different prefixes in different items. For the user entity, GSI1PK=EMAIL#sam@tctf.org enables lookup by email. For the achievement entity, GSI1PK=TIER#gold with GSI1SK=SCORE#0850 enables leaderboard queries sorted by score. For the connection entity, GSI1PK=CONNECTION#user456 enables reverse connection lookups. One GSI, three access patterns, three entity types.

Sparse indexes are a natural consequence. Not every item has GSI1PK set. A session item has no GSI1PK — it does not need a secondary access pattern. DynamoDB only indexes items that have the GSI key attributes, so sessions do not consume GSI storage or write capacity. This keeps the GSI lean and focused on the items that need secondary access.

🔄

GSI overloading: one GSI serves multiple access patterns. Email lookup, leaderboard by tier, and reverse connections — all through GSI1, using different prefixes on different entity types.

06TTL: Automatic Cleanup Per Entity Type

DynamoDB TTL (Time-to-Live) automatically deletes items when their TTL timestamp expires. This is essential for entities that have a natural lifespan — sessions, rate limit counters, cache entries, temporary tokens.

Every TCTF table has a TTL attribute named ttl (or expiresAt). Not every item uses it. User profiles do not expire — they have no TTL attribute. Sessions expire after 24 hours (or longer for stay-signed-in). Rate limit counters expire at the end of their time window. Cache entries expire based on their configured TTL. Circuit breaker state expires after the reset timeout.

The beauty of TTL in single-table design is that different entity types in the same table can have different expiration policies. Sessions expire in hours. Rate limit counters expire in minutes. Cache entries expire in seconds. User profiles never expire. DynamoDB handles all of this automatically — no cleanup jobs, no scheduled Lambda functions, no table scans.

TTL deletion is eventually consistent — items may persist for up to 48 hours after their TTL expires. For most use cases, this is fine. For security-sensitive items (sessions, tokens), the application checks the TTL on read and treats expired items as non-existent, even if DynamoDB has not deleted them yet.

07Access Pattern Design: Start with the Queries

The most important lesson of single-table design: design the keys around the access patterns, not around the entities. In relational databases, you model the entities first and figure out the queries later. In DynamoDB, you list the queries first and design the keys to serve them.

For the user management service, the access patterns are: get user by ID, get user by email, list user sessions, get session by ID, list user achievements, get leaderboard by tier. The PK/SK and GSI design falls directly from these patterns.

Get user by ID: PK=USER#id, SK=PROFILE. Get user by email: GSI1PK=EMAIL#addr. List sessions: PK=USER#id, SK begins_with SESSION#. Get session by ID: PK=USER#id, SK=SESSION#sessId. List achievements: PK=USER#id, SK begins_with ACHIEVEMENT#. Leaderboard by tier: GSI1PK=TIER#gold, sort by GSI1SK (score).

Every access pattern maps to either a primary key query or a GSI query. If an access pattern cannot be served by the existing keys or GSIs, we add a GSI. If it still cannot be served efficiently, we consider whether the access pattern belongs in DynamoDB at all — some patterns are better served by OpenSearch (full-text search) or Neptune (graph traversal).

📐

Design keys around access patterns, not entities. List the queries first. Design the PK/SK to serve them. If a pattern does not fit, add a GSI. If it still does not fit, use a different database.

08Lessons and Trade-Offs

Single-table design is not free. Here are the trade-offs we accepted and the lessons we learned.

The table is not self-documenting. A relational database has table names and column names that describe the data. A single-table DynamoDB table has PK, SK, GSI1PK, GSI1SK, and a mix of entity-specific attributes. New developers need to learn the prefixing convention before they can read the data. We mitigate this with documentation and the repository layer that hides the key construction.

Adding access patterns can require new GSIs. DynamoDB limits tables to 20 GSIs. With 1-3 GSIs per table, we are well within the limit. But each new GSI adds write amplification — every write to an item with GSI attributes also writes to the GSI. We are deliberate about adding GSIs and prefer to solve access patterns with the existing keys when possible.

Hot partitions are a risk. If one partition key receives disproportionate traffic — a viral post, a popular user, a rate limit counter for a busy IP — that partition can throttle. We mitigate this with write sharding for high-traffic counters and by designing partition keys that distribute traffic evenly.

Despite these trade-offs, single-table design has been the right choice for TCTF. The operational simplicity of 34 tables instead of 340, the query efficiency of fetching related entities in one round trip, and the consistency of a shared key convention across all services — these benefits compound as the platform grows. Part 2 covers the fluent query builder that makes working with these patterns practical at scale.