DynamoDB Single-Table Design Deep Dive: Access Patterns, Key Design, and Query Optimization

WardenAuth runs entirely on a single DynamoDB table. Not one table per entity — one table for scopes, permissions, roles, policies, API keys, audit logs, webhook subscriptions, SoD constraints, approval requests, and SCIM tokens. This post is a deep technical dive into the key design, GSI overloading, query patterns, and operational lessons from running a multi-tenant authorization system on a single DynamoDB table.

Why single-table?

DynamoDB charges per request, not per table. Multiple tables mean multiple requests to serve a single authorization check — the access check alone would need to query the policy table, the role table, and the permission table. With single-table design, related items share the same partition key and can be fetched in a single query.

Key design: PK and SK patterns

Every item has a composite primary key: PK (partition key) and SK (sort key). Items that are accessed together share the same PK. The SK determines the item type and identity:

Entity              PK                  SK
───────────────────────────────────────────────────
Scope               SCOPE#acme-corp     SCOPE#acme-corp
Permission          SCOPE#acme-corp     PERMISSION#invoice-read
Role                SCOPE#acme-corp     ROLE#billing-admin
Access Policy       SCOPE#acme-corp     POLICY#user_123
SoD Constraint      SCOPE#acme-corp     SOD#constraint-1
API Key             ORG#org_abc         API_KEY#sk_xyz
Audit Event         SCOPE#acme-corp     AUDIT#2026-07-19T14:31:22.123Z#evt_001
Webhook Subscription SCOPE#acme-corp    WEBHOOK#endpoint_1

The key insight: all entity types that are scope-scoped share the same PK prefix (SCOPE#). A single Query operation with PK = SCOPE#acme-corp retrieves all of a tenant's permissions, roles, policies, and constraints.

Access check: three operations, one partition

An access check (POST /v1/access/check) answers: "can this subject perform this action on this resource in this scope?" The evaluation needs:

1. The subject's access policy (which roles are assigned)
2. Each assigned role's permission set
3. Any applicable SoD constraints

With single-table design, we serve these in two queries against the same partition:

async function evaluateAccess(scopeId: string, subjectId: string, resource: string, action: string) {
  // Query 1: get the subject's policy + all roles + all permissions
  const items = await dynamodb.query({
    KeyConditionExpression: 'PK = :pk',
    ExpressionAttributeValues: { ':pk': `SCOPE#${scopeId}` },
  })

  const policy = items.find(i => i.SK === `POLICY#${subjectId}`)
  if (!policy) return { allowed: false }

  const allRoles = items.filter(i => i.SK.startsWith('ROLE#'))
  const allPermissions = items.filter(i => i.SK.startsWith('PERMISSION#'))

  // Resolve: policy → roles → permissions → match
  const assignedRoles = policy.Data.roles
  const matchedPermissions = assignedRoles.flatMap(roleId => {
    const role = allRoles.find(r => r.Data.id === roleId)
    return (role?.Data.permissions || []).map(permId =>
      allPermissions.find(p => p.Data.id === permId)
    ).filter(Boolean)
  })

  // Deny-wins evaluation
  const matching = matchedPermissions.filter(p => matches(p, resource, action))
  if (matching.some(p => p.Data.effect === 'deny')) return { allowed: false }
  if (matching.some(p => p.Data.effect === 'allow')) return { allowed: true }
  return { allowed: false }
}

GSI strategy: overloading for multiple access patterns

Four GSIs serve different query patterns from the same underlying data:

GSI overloading — using the same GSI for multiple entity types — works because the SK pattern disambiguates. A GSI3 query PK = SCOPE#acme-corp AND SK between AUDIT#2026-07-01 and AUDIT#2026-07-31 returns only audit events, even though the GSI also contains other entity types.

Hot partition mitigation

DynamoDB partitions have a 3,000 RCU / 1,000 WCU throughput limit. A single tenant with millions of access checks per minute could create a hot partition — all their data lives under one PK. Mitigation strategies we use:

• DynamoDB adaptive capacity: automatically redistributes throughput when a partition exceeds its allocated capacity. Handles most spikes transparently.
• Request-scoped caching: deduplication within a single API request eliminates repeated queries to the same partition.
• Burst capacity: DynamoDB reserves a portion of unused capacity for bursts. Spikes up to 5 minutes are absorbed before throttling occurs.
• Architectural ceiling: for tenants exceeding 3,000 consistent reads/second on a single partition, we recommend a dedicated table or partition-sharding — but at that scale, the conversation is about custom architecture, not a SaaS authorization platform.

What we would do differently

Single-table design is elegant but has a sharp edge: the key schema is hard to change once you have production data. Adding a new entity type requires fitting it into the existing PK/SK pattern without breaking existing queries. If we were starting over, we would:

• Define the complete entity catalog upfront — every entity type, its access patterns, its GSI requirements — before writing the first PutItem
• Use a type-safe query builder that validates PK/SK patterns at compile time, preventing runtime errors from malformed keys
• Pre-compute and denormalize access check results for high-traffic tenants rather than resolving the policy → roles → permissions chain on every request