DynamoDB Account Management Module - Table Design

Key Requirements for AM Module (Role/Permission System)

• Multi-tenant isolation (client_id)
• Role definitions per tenant (plus optional global/system roles)
• Permission definitions (system-wide)
• Assignment of roles to users at specific scopes (client, project, building)
• Efficient permission resolution for each user
• Fast GSI-based queries (user-role, user-permission, role-permission)

---

Table Name: AccountManagement

Entity Patterns

1. Client （等同于Tenant）

**PK**: `CLIENT#{client_id}`  
**SK**: `METADATA`  
**GSI1PK**: `CLIENT#{client_id}`  
**GSI1SK**: `CLIENT#{client_id}`  
**GSI4PK**: `CLIENT#{client_id}`  
**GSI4SK**: `CLIENT#{timestamp}`

2. Project

**PK**: `CLIENT#{client_id}`  
**SK**: `PROJECT#{project_id}`  
**GSI1PK**: `CLIENT#{client_id}`  
**GSI1SK**: `PROJECT#{project_id}`  
**GSI4PK**: `CLIENT#{client_id}`  
**GSI4SK**: `PROJECT#{timestamp}`

3. Building

**PK**: `PROJECT#{project_id}`  
**SK**: `BUILDING#{building_id}`  
**GSI1PK**: `CLIENT#{client_id}`  
**GSI1SK**: `BUILDING#{building_id}`  
**GSI4PK**: `CLIENT#{client_id}`  
**GSI4SK**: `BUILDING#{timestamp}`

4. User

**PK**: `USER#{user_id}`  
**SK**: `METADATA`  
**GSI1PK**: `CLIENT#{client_id}`  
**GSI1SK**: `USER#{user_id}`  
**GSI2PK**: `USER#{user_id}`  
**GSI2SK**: `USER#{user_id}`  
**GSI3PK**: `EMAIL#{email}`  
**GSI3SK**: `USER#{user_id}`  
**GSI4PK**: `CLIENT#{client_id}`  
**GSI4SK**: `USER#{timestamp}`

5. Role

**PK**: `CLIENT#{client_id}`  
**SK**: `ROLE#{role_id}`  
**GSI1PK**: `CLIENT#{client_id}`  
**GSI1SK**: `ROLE#{role_id}`  
**GSI4PK**: `CLIENT#{client_id}`  
**GSI4SK**: `ROLE#{timestamp}`

6. Permission (System-wide)

**PK**: `SYSTEM`  
**SK**: `PERMISSION#{permission_id}`

7. User Role Assignment

**PK**: `USER#{user_id}`  
**SK**: `ROLE#{scope_type}#{scope_id}#{role_id}`  
**GSI2PK**: `USER#{user_id}`  
**GSI2SK**: `ACCESS#{scope_type}#{scope_id}#{role_id}`

8. Service Subscription

**PK**: `SCOPE#{scope_type}#{scope_id}`  
**SK**: `SUBSCRIPTION#{module_id}`  
**GSI5PK**: `MODULE#{module_id}`  
**GSI5SK**: `SUBSCRIPTION#{scope_type}#{scope_id}`

9. Settings

**PK**: `SCOPE#{scope_type}#{scope_id}`  
**SK**: `SETTING#{key}`

10. Device(暂时不需要)

**PK**: `BUILDING#{building_id}`  
**SK**: `DEVICE#{device_id}`  
**GSI1PK**: `CLIENT#{client_id}`  
**GSI1SK**: `DEVICE#{device_id}`

11. Zone(暂时不需要)

**PK**: `BUILDING#{building_id}`  
**SK**: `ZONE#{zone_id}`  
**GSI1PK**: `CLIENT#{client_id}`  
**GSI1SK**: `ZONE#{zone_id}`

12. Audit Log(不需要)

**PK**: `CLIENT#{client_id}`  
**SK**: `AUDIT#{timestamp}#{log_id}`  
**GSI4PK**: `CLIENT#{client_id}`  
**GSI4SK**: `AUDIT#{timestamp}`  
**TTL Attribute**: `expires_at` (1 year)

13. Authentication Token(不需要)

**PK**: `USER#{user_id}`  
**SK**: `TOKEN#{token_id}`  
**TTL Attribute**: `expires_at` (30 days)

14. Login Session (不需要)

**PK**: `USER#{user_id}`  
**SK**: `SESSION#{session_start}#{session_id}`  
**TTL Attribute**: `expires_at` (90 days)

Main Table Structure

---

Main Table: AccountManagement

Primary Key Structure (Single Table Design)

Attribute Name	Description
PK (Partition Key)	Entity identifier with pattern {ENTITY_TYPE}#{ID}
SK (Sort Key)	Hierarchical identifier with entity details

Global Secondary Indexes (GSIs)

Index Name	Partition Key	Sort Key	Purpose
GSI1 (Client-Entity)	GSI1PK = CLIENT#{client_id}	GSI1SK = {entity_type}#{entity_id}	Client-scoped entity queries
GSI2 (User Access)	GSI2PK = USER#{user_id}	GSI2SK = ACCESS#{scope_type}#{scope_id}#{role_id}	User permission resolution
GSI3 (Email Lookup)	GSI3PK = EMAIL#{email}	GSI3SK = USER#{user_id}	User authentication
GSI4 (Time-based)	GSI4PK = CLIENT#{client_id}	GSI4SK = {entity_type}#{timestamp}	Time-series queries
GSI5 (Module Subscription)	GSI5PK = MODULE#{module_id}	GSI5SK = SUBSCRIPTION#{scope_type}#{scope_id}	Module subscriptions

PK (Partition Key)	SK (Sort Key)	Type
CLIENT#{client_id}	ROLE#{role_id}	Role
USER#{user_id}	ROLE#{scope_type}#{scope_id}#{role_id}	UserRoleAssignment
SYSTEM	PERMISSION#{permission_id}	Permission
EMAIL#{email}	USER#{user_id}	(via GSI3)

---

Role Entity

PK: CLIENT#{client_id}
SK: ROLE#{role_id}
Type: Role
Attributes:
- id: UUID
- client_id: UUID (null for system roles)
- name: String
- description: String
- is_system: Boolean
- created_by: UUID
- created_at, updated_at: ISO timestamps
- parent_role_id: UUID (for hierarchy)
- permissions: [ { module, action, resource } ]

---

Permission Entity

PK: SYSTEM
SK: PERMISSION#{permission_id}
Type: Permission
Attributes:
- id: UUID
- module: String
- action: String
- resource: String
- description: String
- is_dangerous: Boolean
- created_at, updated_at: ISO timestamps

Use this to manage canonical permission definitions.

---

User Role Assignment Entity

PK: USER#{user_id}
SK: ROLE#{scope_type}#{scope_id}#{role_id}
Type: UserRoleAssignment
Attributes:
- id: UUID
- user_id: UUID
- role_id: UUID
- scope_type: String (client, project, building)
- scope_id: UUID
- created_by: UUID
- created_at, updated_at: ISO timestamps
- start_at: ISO timestamp
- expires_at: ISO timestamp

Use this to query all roles a user has and in which scopes.

---

Global Secondary Indexes (GSIs)

GSI2: User Access Index

GSI2 PK	GSI2 SK	Purpose
USER#{user_id}	ACCESS#{scope_type}#{scope_id}#{role_id}	Lookup all access scopes for a user

This supports permission resolution and access scoping per user.

---

GSI3: Email Lookup Index (Maybe not necessary)

GSI3 PK	GSI3 SK	Purpose
EMAIL#{email}	USER#{user_id}	Login and account find

---

##Role-Permission Linkage Table (if not embedded in Role)(need Jiaxu confirma this design)

If you choose not to embed permissions directly in Role, you can model:

PK: ROLE#{role_id}
SK: PERMISSION#{permission_id}
Type: RolePermissionLink
Attributes:
- module: String
- action: String
- resource: String
- granted_by: UUID
- granted_at: ISO timestamp

However, this will increase read complexity and item counts. Embedding is often preferred in DynamoDB for performance.

---

Samples: Access Patterns and Queries

Access Pattern	Query Description	Method
Get all roles for a tenant	PK = CLIENT#{client_id} AND SK begins_with ROLE#	Main Table
Get all permissions for a role	If embedded: read from Role; else query PK = ROLE#{role_id}	Main Table or GSI
Get all roles a user has	PK = USER#{user_id} AND SK begins_with ROLE#	Main Table
Get all scopes (project/building) user has access to	GSI2: PK = USER#{user_id}	GSI2
Get permission definitions	PK = SYSTEM AND SK begins_with PERMISSION#	Main Table
Lookup user by email	GSI3: PK = EMAIL#{email}	GSI3

---