# Exercise 3: Single-Table Design for a Multi-Tenant Ticketing App

## Scenario

`HelpDesk` is a SaaS support-ticket system. Entities: **tenants** (companies), **users** (agents and customers, belong to one tenant), **tickets** (belong to a tenant, created by a customer, assigned to an agent, have a status and priority), **comments** (on a ticket, ordered by time), and **tags** (many-to-many with tickets).

Access patterns (all scoped to a tenant):

1. Get a ticket with all its comments (newest last).
2. List a tenant's open tickets, sorted by priority then creation time.
3. List tickets assigned to an agent, newest first.
4. List tickets created by a customer.
5. Find a user by email (login).
6. List all tickets with a given tag.
7. Count open tickets per tenant (dashboard), updated in near real time.
8. Get tenant settings.

Volumes: 2,000 tenants; the largest tenant has 3 million tickets and 40 agents; peak 500 writes/s overall, but one big tenant can generate 300 writes/s during incidents.

## Deliverables

1. **Key design table**: for each entity, its `PK`, `SK`, and any `GSI1PK/GSI1SK`, `GSI2PK/GSI2SK`… attributes, with example values.
2. For each access pattern (1–8), the exact operation (GetItem/Query on table or GSI, key conditions, sort direction) — no scans.
3. **Hot partition analysis**: which keys could get hot (consider pattern 2 for the big tenant), and how you'd shard or restructure them.
4. **Pattern 7**: how to maintain counts without scanning (streams + aggregator, or transactional counters) and the consistency trade-off.
5. **Tags (pattern 6)**: model the many-to-many relationship (adjacency list) and describe what a tag rename costs.
6. **Tenant isolation**: how keys and IAM (`dynamodb:LeadingKeys` condition) prevent one tenant's code path from reading another tenant's items.
7. **What you'd keep outside DynamoDB** (full-text search over ticket bodies, analytics) and how data gets there.

## Requirements Checklist

- [ ] Every key starts with the tenant (`TENANT#<id>#…`) or the design otherwise guarantees tenant scoping.
- [ ] Ticket + comments share a partition (`PK=TENANT#t#TICKET#id`, `SK=META` / `COMMENT#<timestamp>`), satisfying pattern 1 with one Query.
- [ ] Open tickets use a sparse GSI with a sort key encoding priority and time (e.g. `GSI1SK = P1#2025-06-12T10:00Z#ticketId`); closed tickets drop out of the index.
- [ ] Agent and customer listings use GSIs keyed by assignee/creator.
- [ ] Email lookup uses a GSI or a uniqueness item (`EMAIL#…`) written in a transaction with the user.
- [ ] Hot-partition mitigation for the big tenant (e.g. shard suffix on `GSI1PK` with scatter-gather reads of N shards).
- [ ] Counts via DynamoDB Streams → Lambda → counter items (eventually consistent) or transactional updates (cost/contention trade-off discussed).
- [ ] Tenant isolation via IAM condition `dynamodb:LeadingKeys` on the tenant prefix.
- [ ] Full-text search via OpenSearch fed by streams; analytics via export to S3 + Athena.