DynamoDB Account Management Module Requirements Document

Overview

This document outlines the DynamoDB database design requirements for the Account Management System, converting from the original SQL-based design to a NoSQL structure optimized for DynamoDB's capabilities.

Core Design Principles

Single Table Design Pattern

Following DynamoDB best practices, we will implement a single table design with composite keys to enable efficient querying while maintaining data relationships.

Partition Key Strategy

Primary Pattern: CLIENT#{client_id} for tenant isolation
Secondary Patterns: USER#{user_id}, PROJECT#{project_id}, BUILDING#{building_id}

Sort Key Strategy

Entity Type Prefix: METADATA, USER, PROJECT, BUILDING, ROLE, PERMISSION, etc.
Hierarchical Structure: Enable range queries for related entities

Table Structure

Main Table: `AccountManagement`

Primary Key Design

Partition Key (PK): Varies by entity type for optimal distribution
Sort Key (SK): Hierarchical structure for efficient querying

Global Secondary Indexes (GSIs)

GSI1: Client-Entity Index

PK: CLIENT#{client_id}
SK: {entity_type}#{entity_id}
Purpose: Query all entities belonging to a client

GSI2: User Access Index

PK: USER#{user_id}
SK: ACCESS#{scope_type}#{scope_id}#{role_id}
Purpose: Fast permission resolution for users

GSI3: Email Lookup Index

PK: EMAIL#{email}
SK: USER#{user_id}
Purpose: User login and lookup

GSI4: Time-based Queries Index

PK: CLIENT#{client_id}
SK: {entity_type}#{timestamp}
Purpose: Time-based queries (audit logs, sessions)

GSI5: Module Subscription Index

PK: MODULE#{module_id}
SK: SUBSCRIPTION#{scope_type}#{scope_id}
Purpose: Module subscription management

Entity Patterns

1. Client Entity (Tenant)

PK: CLIENT#{client_id}
SK: METADATA
Type: Client
Attributes:
- id: UUID
- name: String
- status: String (active, suspended)
- created_at: ISO timestamp
- updated_at: ISO timestamp
- logo_url: String
- primary_contact_email: String
- primary_contact_name: String
- timezone: String (default: UTC)
- billing_email: String
- address: String
- phone: String

2. Project Entity

PK: CLIENT#{client_id}
SK: PROJECT#{project_id}
Type: Project
Attributes:
- id: UUID
- client_id: UUID
- name: String
- description: String
- status: String (active, completed, on-hold)
- created_at: ISO timestamp
- updated_at: ISO timestamp
- start_date: ISO date
- end_date: ISO date
- region: String
- owner_user_id: UUID

3. Building Entity

PK: PROJECT#{project_id}
SK: BUILDING#{building_id}
Type: Building
Attributes:
- id: UUID
- project_id: UUID
- name: String
- address: String
- city: String
- state: String
- country: String
- postal_code: String
- timezone: String
- square_footage: Number
- floors: Number
- year_built: Number
- created_at: ISO timestamp
- updated_at: ISO timestamp
- status: String (active, inactive)
- geo_latitude: Number
- geo_longitude: Number

4. User Entity

PK: USER#{user_id}
SK: METADATA
Type: User
Attributes:
- id: UUID
- client_id: UUID
- email: String
- status: String (active, disabled)
- first_name: String
- last_name: String
- password_hash: String
- phone: String
- title: String
- department: String
- created_at: ISO timestamp
- updated_at: ISO timestamp
- last_login_at: ISO timestamp
- user_type: String (internal, client_admin, external_customer, external_tenant)
- invited_by: UUID
- invited_at: ISO timestamp
- mfa_enabled: Boolean
- mfa_type: String
- mfa_secret: String (encrypted)
- password_last_changed_at: ISO timestamp

5. 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
- custom_template_id: UUID
- created_at: ISO timestamp
- updated_at: ISO timestamp
- created_by: UUID
- parent_role_id: UUID
- permissions: List of permission objects

6. 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: ISO timestamp
- updated_at: ISO timestamp

7. User Role Assignment

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_at: ISO timestamp
- updated_at: ISO timestamp
- created_by: UUID
- expires_at: ISO timestamp
- start_at: ISO timestamp

8. Service Subscription

PK: SCOPE#{scope_type}#{scope_id}
SK: SUBSCRIPTION#{module_id}
Type: ServiceSubscription
Attributes:
- id: UUID
- client_id: UUID
- scope_type: String (building, project)
- scope_id: UUID
- module_id: UUID
- status: String (active, expired, trial)
- tier: String (standard, premium)
- start_date: ISO timestamp
- end_date: ISO timestamp
- created_at: ISO timestamp
- created_by: UUID
- auto_renew: Boolean
- usage_quota: Map

9. Settings

PK: SCOPE#{scope_type}#{scope_id}
SK: SETTING#{key}
Type: Setting
Attributes:
- id: UUID
- scope_type: String (client, project, building, user)
- scope_id: UUID
- key: String
- value: String
- value_type: String (string, number, boolean, json)
- last_updated: ISO timestamp
- updated_by: UUID
- is_overridable: Boolean
- description: String

10. Device Entity

PK: BUILDING#{building_id}
SK: DEVICE#{device_id}
Type: Device
Attributes:
- id: UUID
- building_id: UUID
- name: String
- type: String (hvac, camera, robot)
- manufacturer: String
- model: String
- serial_number: String
- location_description: String
- zone_id: UUID
- status: String (active, inactive)
- created_at: ISO timestamp
- updated_at: ISO timestamp
- configuration: Map
- last_seen_at: ISO timestamp

11. Zone Entity

PK: BUILDING#{building_id}
SK: ZONE#{zone_id}
Type: Zone
Attributes:
- id: UUID
- building_id: UUID
- name: String
- description: String
- floor: String
- area_sqft: Number
- type: String (office, lobby, utility)
- parent_zone_id: UUID
- created_at: ISO timestamp
- updated_at: ISO timestamp
- geo_json: Map

12. Audit Log Entity

PK: CLIENT#{client_id}
SK: AUDIT#{timestamp}#{log_id}
Type: AuditLog
Attributes:
- id: UUID
- user_id: UUID
- action: String
- entity_type: String
- entity_id: UUID
- timestamp: ISO timestamp
- client_id: UUID
- ip_address: String
- user_agent: String
- metadata: Map
- status: String (success, failure, error)
- session_id: UUID
- severity: String (info, warning, critical)
- related_entities: Map
TTL: 31536000 (1 year)

13. Authentication Token

PK: USER#{user_id}
SK: TOKEN#{token_id}
Type: AuthToken
Attributes:
- id: UUID
- user_id: UUID
- client_id: UUID
- token_type: String (JWT, refresh, api)
- issued_at: ISO timestamp
- expires_at: ISO timestamp
- payload: Map
- revoked: Boolean
- revoked_at: ISO timestamp
- revoked_by: UUID
- device_id: String
- ip_address: String
TTL: 2592000 (30 days)

PK: USER#{user_id}
SK: SESSION#{session_start}#{session_id}
Type: LoginSession
Attributes:
- id: UUID
- user_id: UUID
- client_id: UUID
- sso_provider: String
- session_start: ISO timestamp
- session_end: ISO timestamp
- ip_address: String
- user_agent: String
- device_info: Map
- mfa_verified: Boolean
- location: String
- status: String (active, expired, revoked)
TTL: 7776000 (90 days)

Access Patterns and Query Requirements

1. User Authentication and Authorization

Pattern: Get user by email
Query: GSI3 where PK = EMAIL#{email}
Pattern: Get user permissions for scope
Query: GSI2 where PK = USER#{user_id} and SK begins with ACCESS#{scope_type}#{scope_id}

2. Client Management

Pattern: Get all entities for a client
Query: GSI1 where PK = CLIENT#{client_id}
Pattern: Get client metadata
Query: Main table where PK = CLIENT#{client_id} and SK = METADATA

3. Project and Building Hierarchy

Pattern: Get all projects for a client
Query: GSI1 where PK = CLIENT#{client_id} and SK begins with PROJECT#
Pattern: Get all buildings for a project
Query: Main table where PK = PROJECT#{project_id} and SK begins with BUILDING#

4. Device and Zone Management

Pattern: Get all devices in a building
Query: Main table where PK = BUILDING#{building_id} and SK begins with DEVICE#
Pattern: Get all zones in a building
Query: Main table where PK = BUILDING#{building_id} and SK begins with ZONE#

5. Subscription and Module Management

Pattern: Get active subscriptions for a scope
Query: Main table where PK = SCOPE#{scope_type}#{scope_id} and SK begins with SUBSCRIPTION#
Pattern: Get all subscriptions for a module
Query: GSI5 where PK = MODULE#{module_id}

6. Audit and Compliance

Pattern: Get recent audit logs for a client
Query: GSI4 where PK = CLIENT#{client_id} and SK begins with AUDIT# (with date range)
Pattern: Get user activity logs
Query: Filter on user_id attribute after fetching client audit logs

DynamoDB Account Management Module Requirements Document ​

Overview ​

Core Design Principles ​

Single Table Design Pattern ​

Partition Key Strategy ​

Sort Key Strategy ​

Table Structure ​

Main Table: AccountManagement ​

Primary Key Design ​

Global Secondary Indexes (GSIs) ​

Entity Patterns ​

1. Client Entity (Tenant) ​

2. Project Entity ​

3. Building Entity ​

4. User Entity ​

5. Role Entity ​

6. Permission Entity ​

7. User Role Assignment ​

8. Service Subscription ​

9. Settings ​

10. Device Entity ​

11. Zone Entity ​

12. Audit Log Entity ​

13. Authentication Token ​

14. Login Session ​

Access Patterns and Query Requirements ​

1. User Authentication and Authorization ​

2. Client Management ​

3. Project and Building Hierarchy ​

4. Device and Zone Management ​

5. Subscription and Module Management ​

6. Audit and Compliance ​