Add repository methods for efficient bulk user purging
- Dominant language
- Python
- Stars
- 670
- Forks
- 183
- Avg merge
- 17h 7m
- Merged PRs (30d)
- 358
Description
## Story
As a developer, I want to implement repository methods for bulk user purging so that database operations are optimized for handling multiple users efficiently.
## Technical Details
Add repository methods to support bulk user purging with optimized database queries and batch operations.
## Implementation Requirements
### Repository Methods
```python
class UserRepository:
async def bulk_delete_users(
self,
db_sess: SASession,
user_emails: list[str]
) -> tuple[list[str], dict[str, str]]:
"""
Bulk delete users and return succeeded/failed lists
"""
pass
async def bulk_delete_keypairs(
self,
db_sess: SASession,
user_ids: list[UUID]
) -> None:
"""
Efficiently delete all keypairs for multiple users
"""
pass
async def bulk_transfer_vfolders(
self,
db_sess: SASession,
user_ids: list[UUID],
new_owner_id: UUID
) -> None:
"""
Transfer ownership of vfolders from multiple users
"""
pass
async def bulk_delete_vfolders(
self,
db_sess: SASession,
user_ids: list[UUID],
include_shared: bool = False
) -> None:
"""
Delete vfolders for multiple users
"""
pass
async def bulk_transfer_endpoints(
self,
db_sess: SASession,
user_ids: list[UUID],
new_owner_id: UUID
) -> None:
"""
Transfer endpoint ownership from multiple users
"""
pass
```
### Implementation Details
1. **Batch Query Optimization**
- Use `IN` clauses for filtering multiple users
- Minimize round trips to database
- Use bulk insert/update/delete operations
1. **Efficient Data Loading**
- Load all necessary data in minimal queries
- Use joins where appropriate
- Avoid N+1 query problems
1. **Transaction Management**
- All operations within single transaction context
- Proper savepoint handling for partial rollbacks
- Clear transaction boundaries
1. **Performance Considerations**
{code:python}# Example: Bulk delete with single query
await db_sess.execute(
sa.delete(users)
.where(users.c.email.in_(user_emails))
.returning(users.c.email)
)
1. Example: Bulk update for vfolder ownership
await db_sess.execute(
sa.update(vfolders)
.where(vfolders.c.user.in_(user_ids))
.values(user=new_owner_id)
){code}
1. **Error Handling**
- Collect constraint violations
- Handle foreign key dependencies
- Provide detailed error information per user
## Acceptance Criteria
- [ ] All bulk repository methods implemented
- [ ] Optimized queries using batch operations
- [ ] No N+1 query problems
- [ ] Proper transaction handling
- [ ] Comprehensive error collection and reporting
- [ ] Unit tests for all repository methods
- [ ] Performance tests for large batch operations
- [ ] Documentation of query optimization strategies
## Performance Requirements
- Should handle 100+ users in a single batch efficiently
- Database round trips minimized (target: < 10 queries for full purge)
- Transaction time < 5 seconds for 100 users
## Dependencies
- Existing user, keypair, vfolder, and endpoint models
- SQLAlchemy batch operation support
## Parent Epic
BA-2510
JIRA Issue: BA-2513
Contributor guide
Assessment
This issue has not been assessed yet.