Phase 3 — Repository Refactoring & Cross-DB Gaps #108

New issue

Closed

opened 2026-07-09 09:56:56 +12:00 by fastie81 · 1 comment

fastie81 commented

2026-07-09 09:56:56 +12:00

Owner

Issue: Phase 3 — Repository Refactoring & Cross-DB Gaps

Type: Architecture / Refactoring
Priority: High
Epic: Epic: Database-per-Club Multi-Tenancy Split & Data Migration
Status: Backlog

1. Context & Purpose

Because tables will be split across separate databases, direct SQL JOINs between central-only tables (users, clubs) and club-specific tables (members, families) are physically impossible. Similarly, code using raw db connection wrappers bypassing operations/repositories will fail. This issue refactors all affected queries and modules to resolve these architectural gaps.

2. Technical Specification

2.1. Refactor `DashboardOperations`

Modify app/core/operations/dashboard_ops.py:

Remove __init__(self, db_wrapper):
- Inject both club_repo and system_repo instead: __init__(self, club_repo, system_repo).
Modify all queries within DashboardOperations to use the appropriate repository:
- Club-scoped queries (e.g. member count, upcoming birthdays, attendance, gradings, class schedules) must use standard routing-bound methods on club_repo.
- System-scoped queries (e.g. users, clubs info) must use system_repo.

Update app/container.py dependency wiring to match:

dashboard_ops = DashboardOperations(club_repo=club_repo, system_repo=system_repo)

2.2. Fix `get_system_stats()`

Modify get_system_stats() in DashboardOperations:

Instead of running a single SELECT COUNT(*) FROM members against a single database (which would return 0 on the Central database), it must:
1. Retrieve all registered clubs from system_repo.get_all_clubs().
2. For each club, temporarily set the routing context using with route_to_club(club.id):.
3. Query SELECT COUNT(*) FROM members inside that context.
4. Aggregate and sum the counts in Python.
5. Fetch user count from Central database.

2.3. Eliminate Cross-DB JOIN in `get_users_by_club()`

Modify get_users_by_club(club_id) in app/core/repositories/postgres_system_repo.py:

This method previously joined users, user_club_assignments, family_guardians, families, and members in a single query. Update it to be a two-phase query:
1. Run a query against the Club Database (using club_repo or setting routing context):
  - Fetch all distinct user_id values from the members table.
  - Fetch all distinct user_id values from the family_guardians table.
  - Merge these into a single set of user_ids.
2. Query the Central Database:
  - Fetch all distinct user_id values from user_club_assignments where club_id = :cid.
  - Combine with the list of user IDs from the club database.
  - Fetch user account details from users table for all found IDs using WHERE id = ANY(:ids).
  - Exclude role System Admin.
  - Return the combined user structures.

2.4. Refactor `delete_club()`

Modify delete_club(club_id) in postgres_system_repo.py:

Cross-database foreign keys are not supported. Clean up dependencies across connections:
1. Construct the connection URI for honbu_club_<club_id>.
2. Connect to the PostgreSQL instance using administrative credentials (postgres default database) and execute DROP DATABASE IF EXISTS honbu_club_<club_id> to delete all member, family, attendance, grading, and note records cleanly and atomically.
3. Query Central DB: Delete all assignments in user_club_assignments and api_tokens associated with this club.
4. Delete the club's record from clubs.

2.5. Implement Application-level Foreign Key Validation

Modify create_member(member_data) in MemberOperations:
- If a user_id is supplied in member_data, verify it exists in the Central Database by querying system_repo.get_user(user_id). Raise a validation error if not found.
Modify add_guardian(family_id, user_id) in FamilyOperations:
- Verify that user_id exists in the Central Database before inserting the association in family_guardians table.

2.6. Birthday Timezone Fix (Co-resolved from Issue #96 / #82)

Integrate a fix for the dashboard birthday display timezone shifting issue during the refactoring of DashboardOperations.get_club_dashboard_data.
Ensure date serialization maps to local timezone correctly, or ensure dates are formatted cleanly for local timezone instantiation in the frontend client.

3. Verification Plan

3.1. Automated Unit Tests

Write a unit test in tests/unit/test_dashboard_split.py to ensure DashboardOperations correctly routes statistics calls and aggregates member counts across multiple mock database engines.
Write tests in tests/unit/test_user_lookups.py ensuring the two-phase query in get_users_by_club returns exact matches identical to the old joined query.
Write a unit test ensuring that creating a member or guardian with a non-existent user_id correctly throws a validation error.

3.2. Integration Verification

Execute:

docker exec club-manager_honbu-manager_1 python3 -m pytest tests/unit/ -v

Confirm all tests pass.

Part of Epic #105

# Issue: Phase 3 — Repository Refactoring & Cross-DB Gaps **Type:** Architecture / Refactoring **Priority:** High **Epic:** [Epic: Database-per-Club Multi-Tenancy Split & Data Migration](file:///mnt/f/forgejo-git/club-manager/docs/database_split_issue.md) **Status:** Backlog --- ## 1. Context & Purpose Because tables will be split across separate databases, direct SQL `JOIN`s between central-only tables (`users`, `clubs`) and club-specific tables (`members`, `families`) are physically impossible. Similarly, code using raw db connection wrappers bypassing operations/repositories will fail. This issue refactors all affected queries and modules to resolve these architectural gaps. --- ## 2. Technical Specification ### 2.1. Refactor `DashboardOperations` Modify [app/core/operations/dashboard_ops.py](file:///mnt/f/forgejo-git/club-manager/app/core/operations/dashboard_ops.py): - Remove `__init__(self, db_wrapper)`: - Inject both `club_repo` and `system_repo` instead: `__init__(self, club_repo, system_repo)`. - Modify all queries within `DashboardOperations` to use the appropriate repository: - Club-scoped queries (e.g. member count, upcoming birthdays, attendance, gradings, class schedules) must use standard routing-bound methods on `club_repo`. - System-scoped queries (e.g. users, clubs info) must use `system_repo`. - Update `app/container.py` dependency wiring to match: ```python dashboard_ops = DashboardOperations(club_repo=club_repo, system_repo=system_repo) ``` ### 2.2. Fix `get_system_stats()` Modify `get_system_stats()` in `DashboardOperations`: - Instead of running a single `SELECT COUNT(*) FROM members` against a single database (which would return 0 on the Central database), it must: 1. Retrieve all registered clubs from `system_repo.get_all_clubs()`. 2. For each club, temporarily set the routing context using `with route_to_club(club.id):`. 3. Query `SELECT COUNT(*) FROM members` inside that context. 4. Aggregate and sum the counts in Python. 5. Fetch user count from Central database. ### 2.3. Eliminate Cross-DB JOIN in `get_users_by_club()` Modify `get_users_by_club(club_id)` in [app/core/repositories/postgres_system_repo.py](file:///mnt/f/forgejo-git/club-manager/app/core/repositories/postgres_system_repo.py): - This method previously joined `users`, `user_club_assignments`, `family_guardians`, `families`, and `members` in a single query. Update it to be a two-phase query: 1. Run a query against the Club Database (using `club_repo` or setting routing context): - Fetch all distinct `user_id` values from the `members` table. - Fetch all distinct `user_id` values from the `family_guardians` table. - Merge these into a single set of `user_ids`. 2. Query the Central Database: - Fetch all distinct `user_id` values from `user_club_assignments` where `club_id = :cid`. - Combine with the list of user IDs from the club database. - Fetch user account details from `users` table for all found IDs using `WHERE id = ANY(:ids)`. - Exclude role `System Admin`. - Return the combined user structures. ### 2.4. Refactor `delete_club()` Modify `delete_club(club_id)` in `postgres_system_repo.py`: - Cross-database foreign keys are not supported. Clean up dependencies across connections: 1. Construct the connection URI for `honbu_club_<club_id>`. 2. Connect to the PostgreSQL instance using administrative credentials (`postgres` default database) and execute `DROP DATABASE IF EXISTS honbu_club_<club_id>` to delete all member, family, attendance, grading, and note records cleanly and atomically. 3. Query Central DB: Delete all assignments in `user_club_assignments` and `api_tokens` associated with this club. 4. Delete the club's record from `clubs`. ### 2.5. Implement Application-level Foreign Key Validation - Modify `create_member(member_data)` in `MemberOperations`: - If a `user_id` is supplied in `member_data`, verify it exists in the Central Database by querying `system_repo.get_user(user_id)`. Raise a validation error if not found. - Modify `add_guardian(family_id, user_id)` in `FamilyOperations`: - Verify that `user_id` exists in the Central Database before inserting the association in `family_guardians` table. --- ### 2.6. Birthday Timezone Fix (Co-resolved from Issue #96 / #82) - Integrate a fix for the dashboard birthday display timezone shifting issue during the refactoring of `DashboardOperations.get_club_dashboard_data`. - Ensure date serialization maps to local timezone correctly, or ensure dates are formatted cleanly for local timezone instantiation in the frontend client. ## 3. Verification Plan ### 3.1. Automated Unit Tests - Write a unit test in `tests/unit/test_dashboard_split.py` to ensure `DashboardOperations` correctly routes statistics calls and aggregates member counts across multiple mock database engines. - Write tests in `tests/unit/test_user_lookups.py` ensuring the two-phase query in `get_users_by_club` returns exact matches identical to the old joined query. - Write a unit test ensuring that creating a member or guardian with a non-existent `user_id` correctly throws a validation error. ### 3.2. Integration Verification - Execute: ```bash docker exec club-manager_honbu-manager_1 python3 -m pytest tests/unit/ -v ``` - Confirm all tests pass. --- **Part of Epic #105**

fastie81 referenced this issue from a commit

2026-07-16 16:06:21 +12:00

feat(db-split): Phase 3 — Repository Refactoring & Cross-DB Gaps (#108)

fastie81 referenced this issue

2026-07-16 16:17:29 +12:00

feat(db-split): Phase 3 — Repository Refactoring & Cross-DB Gaps (#108) #113

fastie81 commented

2026-07-16 16:17:33 +12:00

Author

Owner

Phase 3 has been fully implemented, verified, and tested. Staged unit tests and E2E tests are 100% green.

Opened Pull Request: #113.

Phase 3 has been fully implemented, verified, and tested. Staged unit tests and E2E tests are 100% green. Opened Pull Request: https://forgejo.scrat.co.nz/fastie81/honbu-manager/pulls/113.

fastie81 closed this issue

2026-07-16 16:17:33 +12:00