Merge pull request #6 from IDTS-LAB/normalized-user-domain-architectu… · IDTS-LAB/python-codebase@91f707c · GitHub
Skip to content

Commit 91f707c

Browse files
fiqrikm18fiqrikm18
authored
Merge pull request #6 from IDTS-LAB/normalized-user-domain-architecture-0d379
Update from task d9094924-b67f-44f0-bb30-c9ebeaa0d379
2 parents 4ec78ec + 5fefb72 commit 91f707c

22 files changed

Lines changed: 1162 additions & 105 deletions

‎.gitignore‎

Lines changed: 28 additions & 7 deletions

‎docs/NORMALIZED_USER_DOMAIN.md‎

Lines changed: 266 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,266 @@
1+
"""Normalized User Domain Schema Design
2+
3+
This module implements a fully normalized user domain following:
4+
- PostgreSQL 17 features
5+
- Third Normal Form (3NF)
6+
- Domain-Driven Design (DDD) principles
7+
- Modular Monolith Architecture
8+
- CQRS compatibility
9+
- Multi-tenancy readiness
10+
- Audit-friendly design
11+
12+
## Domain Analysis: Why Monolithic Users Table is Bad
13+
14+
1. **Single Responsibility Principle Violation**: A monolithic users table mixes identity,
15+
profile, security, preferences, and contact information in one place. This makes it
16+
difficult to reason about and maintain.
17+
18+
2. **Scalability Bottlenecks**: As the table grows wide with many columns, every query
19+
loads unnecessary data. Index efficiency decreases, and vacuum operations become slower.
20+
21+
3. **Security Concerns**: Sensitive data like password hashes and security settings should
22+
be isolated from frequently accessed profile data to minimize exposure surface.
23+
24+
4. **Multi-Tenancy Complexity**: Adding tenant isolation to a wide table requires careful
25+
consideration of which fields need tenant scoping.
26+
27+
5. **CQRS Incompatibility**: Command and Query Responsibility Segregation becomes difficult
28+
when read models need different projections than write models from the same table.
29+
30+
6. **Microservice Extraction**: When extracting services, a monolithic table creates tight
31+
coupling. Separated tables allow clean bounded context boundaries.
32+
33+
7. **Audit Trail Gaps**: Tracking changes across many unrelated fields in one table is
34+
complex and error-prone.
35+
36+
8. **Performance Contention**: Hot spots form when unrelated operations compete for locks
37+
on the same row.
38+
39+
## Recommended Normalized Structure
40+
41+
### Identity Bounded Context
42+
- **users**: Core identity and authentication credentials
43+
- **user_security**: Security state, lockouts, MFA configuration
44+
- **user_verifications**: Verification status per communication channel
45+
- **user_sessions**: Active sessions and refresh tokens
46+
47+
### Profile Bounded Context
48+
- **user_profiles**: Personal information (names, bio, avatar)
49+
- **user_contacts**: Multiple contact methods with types
50+
- **user_addresses**: Multiple addresses with labels
51+
- **user_settings**: User preferences in flexible JSONB format
52+
53+
### Access Control Bounded Context
54+
- **roles**: Role definitions
55+
- **permissions**: Permission definitions
56+
- **role_permissions**: Role-to-permission assignments
57+
- **user_has_roles**: User-to-role assignments
58+
59+
### Audit Bounded Context
60+
- **audit_logs**: Immutable event log for compliance
61+
- **error_traces**: Error tracking for debugging
62+
63+
## ERD Diagram (Mermaid)
64+
65+
```mermaid
66+
erDiagram
67+
users ||--o| user_profiles : "has"
68+
users ||--o| user_security : "has"
69+
users ||--o| user_contacts : "has multiple"
70+
users ||--o| user_addresses : "has multiple"
71+
users ||--o| user_settings : "has"
72+
users ||--o| user_verifications : "has multiple"
73+
users ||--o| user_sessions : "has multiple"
74+
users ||--o| user_has_roles : "assigned"
75+
76+
roles ||--o{ role_permissions : "contains"
77+
permissions ||--o{ role_permissions : "contained in"
78+
roles ||--o{ user_has_roles : "assigned to"
79+
users ||--o{ user_has_roles : "has roles"
80+
81+
authorization_resources ||--o{ permissions : "defines"
82+
83+
users {
84+
uuid id PK
85+
varchar email UK
86+
varchar username UK
87+
varchar password_hash
88+
varchar auth_provider
89+
varchar status
90+
timestamptz created_at
91+
timestamptz updated_at
92+
}
93+
94+
user_profiles {
95+
uuid id PK
96+
uuid user_id FK UK
97+
varchar first_name
98+
varchar last_name
99+
varchar display_name
100+
varchar avatar_url
101+
text bio
102+
date birth_date
103+
}
104+
105+
user_security {
106+
uuid id PK
107+
uuid user_id FK UK
108+
int failed_login_attempts
109+
timestamptz locked_until
110+
timestamptz password_changed_at
111+
boolean two_factor_enabled
112+
varchar two_factor_secret
113+
}
114+
115+
user_contacts {
116+
uuid id PK
117+
uuid user_id FK
118+
varchar type
119+
varchar value
120+
boolean is_primary
121+
boolean is_verified
122+
}
123+
124+
user_addresses {
125+
uuid id PK
126+
uuid user_id FK
127+
varchar label
128+
varchar line1
129+
varchar line2
130+
varchar city
131+
varchar state
132+
varchar postal_code
133+
varchar country
134+
boolean is_default
135+
}
136+
137+
user_settings {
138+
uuid id PK
139+
uuid user_id FK UK
140+
jsonb preferences
141+
}
142+
143+
user_verifications {
144+
uuid id PK
145+
uuid user_id FK
146+
varchar channel
147+
boolean is_verified
148+
timestamptz verified_at
149+
varchar verification_token
150+
}
151+
152+
user_sessions {
153+
uuid id PK
154+
uuid user_id FK
155+
varchar refresh_token_hash
156+
timestamptz expires_at
157+
varchar device_info
158+
varchar ip_address
159+
boolean is_revoked
160+
}
161+
162+
roles {
163+
uuid id PK
164+
varchar name UK
165+
varchar description
166+
}
167+
168+
permissions {
169+
uuid id PK
170+
uuid resource_id FK
171+
varchar key UK
172+
varchar resource
173+
varchar action
174+
varchar description
175+
}
176+
177+
authorization_resources {
178+
uuid id PK
179+
varchar key UK
180+
varchar name
181+
varchar description
182+
}
183+
184+
role_permissions {
185+
uuid id PK
186+
uuid role_id FK
187+
uuid permission_id FK
188+
}
189+
190+
user_has_roles {
191+
uuid id PK
192+
uuid user_id FK
193+
uuid role_id FK
194+
}
195+
196+
audit_logs {
197+
uuid id PK
198+
varchar action
199+
uuid actor_id
200+
varchar resource_type
201+
uuid resource_id
202+
varchar request_id
203+
jsonb meta
204+
timestamptz created_at
205+
}
206+
```
207+
208+
## DDD Mapping
209+
210+
### Aggregates
211+
- **UserAggregate**: Root entity `users` with entities `user_security`, `user_profiles`
212+
- **SessionAggregate**: Root entity `user_sessions`
213+
- **RoleAggregate**: Root entity `roles` with `role_permissions`
214+
215+
### Entities
216+
- `users`: Identity aggregate root
217+
- `user_security`: Security configuration entity
218+
- `user_profiles`: Profile entity
219+
- `user_contacts`: Contact method entity
220+
- `user_addresses`: Address entity
221+
- `user_sessions`: Session entity
222+
- `roles`: Role aggregate root
223+
- `permissions`: Permission entity
224+
225+
### Value Objects
226+
- `Email`: Email address with validation
227+
- `PhoneNumber`: Phone number with formatting
228+
- `Address`: Structured address components
229+
- `Preferences`: JSONB settings object
230+
231+
### Domain Services
232+
- `AuthenticationService`: Login/logout/password management
233+
- `AuthorizationService`: RBAC evaluation
234+
- `VerificationService`: Email/phone verification
235+
- `SessionService`: Session lifecycle management
236+
- `AuditService`: Audit log creation
237+
238+
## Future Scalability Considerations
239+
240+
### Millions of Users
241+
- Partition `audit_logs` and `user_sessions` by date
242+
- Use connection pooling efficiently
243+
- Implement read replicas for query separation
244+
- Cache frequently accessed profiles
245+
246+
### Multi-Tenancy
247+
- Add `tenant_id` column to all tables
248+
- Implement Row Level Security (RLS) policies
249+
- Use schema-per-tenant for high isolation needs
250+
251+
### OAuth/SSO Support
252+
- `auth_provider` field supports external identity providers
253+
- `external_id` can be added for provider-specific IDs
254+
- `user_verifications` tracks OAuth account linking
255+
256+
### Microservice Extraction
257+
- Each bounded context can become a separate service
258+
- Clear foreign key boundaries enable database splitting
259+
- Event sourcing ready via `audit_logs`
260+
261+
### Event-Driven Architecture
262+
- `audit_logs` serves as event store
263+
- Can integrate with message brokers (Kafka, RabbitMQ)
264+
- Supports CQRS read model rebuilding
265+
266+
"""

‎src/modules/authorization/infrastructure/models/permission_model.py‎

Lines changed: 25 additions & 6 deletions

0 commit comments

Comments
 (0)