Phase 1 — Dynamic Routing Infrastructure #106

New issue

Closed

opened 2026-07-09 09:56:56 +12:00 by fastie81 · 0 comments

fastie81 commented

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

Owner

Issue: Phase 1 — Dynamic Routing Infrastructure

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

1. Context & Purpose

We need to swap database connections dynamically on a per-request basis. This issue implements the foundation: a thread-safe and async-safe routing context (contextvars) and a custom SQLAlchemy class that routes queries based on that context.

2. Technical Specification

2.1. Routing Context (`context.py`)

Create a new file app/infrastructure/database/context.py:

Define a contextvars.ContextVar named routing_club_id with a default of None.
Implement set_routing_club_id(club_id: int): Sets the current club ID.
Implement get_routing_club_id() -> Optional[int]: Returns the current club ID or None.
Implement clear_routing_club_id(): Resets the context var to None.
Implement a context manager route_to_club(club_id: int) using @contextmanager to temporarily swap club contexts and safely restore the old value in a finally block:

import contextvars
from contextlib import contextmanager

_routing_club_id = contextvars.ContextVar('routing_club_id', default=None)

def set_routing_club_id(club_id: int):
    _routing_club_id.set(club_id)

def get_routing_club_id():
    return _routing_club_id.get()

def clear_routing_club_id():
    _routing_club_id.set(None)

@contextmanager
def route_to_club(club_id: int):
    token = _routing_club_id.set(club_id)
    try:
        yield
    finally:
        _routing_club_id.reset(token)

2.2. Dynamic DB Router (`extensions.py`)

Modify app/infrastructure/flask/extensions.py:

Subclass SQLAlchemy into DynamicRoutingSQLAlchemy.
Override get_engine(self, bind=None):
1. Retrieve club_id = get_routing_club_id().
2. If club_id is None or 0, return the default engine (super().get_engine(bind=bind)).
3. If a club_id is set, check the cache self._club_engines.
4. If not cached, connect to the Central database, lookup the club's database_name or database_uri from the clubs table:
  - If database_uri exists, use it.
  - Else, parse the Central connection URI (using sqlalchemy.engine.url.make_url) and substitute the database name with honbu_club_<club_id>.
5. Cache and return the engine.
Instantiate db_orm = DynamicRoutingSQLAlchemy().

2.3. Request Lifecycle Integration (`app_factory.py`)

Modify the Flask app factory to automatically clear the routing context after every request:

@app.teardown_request
def teardown_routing_context(exception=None):
    from app.infrastructure.database.context import clear_routing_club_id
    clear_routing_club_id()

2.4. Auth Decorator Integration (`authentication.py`)

Modify app/core/auth/authentication.py:

Update require_auth to call set_routing_club_id(user.get('club_id')) after successfully extracting the JWT token.
Update require_club_access to call set_routing_club_id(club_id).

3. Verification Plan

3.1. Automated Unit Tests

Write tests in tests/unit/test_routing_infra.py:

Test that set_routing_club_id and get_routing_club_id work across threads/coroutines.
Mock SQLAlchemy.get_engine and verify that calling a query inside a with route_to_club(1): context switches engines correctly, and returns to the default engine afterwards.

3.2. Integration Verification

Run the full existing test suite (pytest):
```
docker exec club-manager_honbu-manager_1 python3 -m pytest tests/unit/ -v
```
All tests must pass. The default routing behavior must remain unchanged because no club database configurations have been set.

Part of Epic #105

# Issue: Phase 1 — Dynamic Routing Infrastructure **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 We need to swap database connections dynamically on a per-request basis. This issue implements the foundation: a thread-safe and async-safe routing context (`contextvars`) and a custom `SQLAlchemy` class that routes queries based on that context. --- ## 2. Technical Specification ### 2.1. Routing Context (`context.py`) Create a new file [app/infrastructure/database/context.py](file:///mnt/f/forgejo-git/club-manager/app/infrastructure/database/context.py): - Define a `contextvars.ContextVar` named `routing_club_id` with a default of `None`. - Implement `set_routing_club_id(club_id: int)`: Sets the current club ID. - Implement `get_routing_club_id() -> Optional[int]`: Returns the current club ID or `None`. - Implement `clear_routing_club_id()`: Resets the context var to `None`. - Implement a context manager `route_to_club(club_id: int)` using `@contextmanager` to temporarily swap club contexts and safely restore the old value in a `finally` block: ```python import contextvars from contextlib import contextmanager _routing_club_id = contextvars.ContextVar('routing_club_id', default=None) def set_routing_club_id(club_id: int): _routing_club_id.set(club_id) def get_routing_club_id(): return _routing_club_id.get() def clear_routing_club_id(): _routing_club_id.set(None) @contextmanager def route_to_club(club_id: int): token = _routing_club_id.set(club_id) try: yield finally: _routing_club_id.reset(token) ``` ### 2.2. Dynamic DB Router (`extensions.py`) Modify [app/infrastructure/flask/extensions.py](file:///mnt/f/forgejo-git/club-manager/app/infrastructure/flask/extensions.py): - Subclass `SQLAlchemy` into `DynamicRoutingSQLAlchemy`. - Override `get_engine(self, bind=None)`: 1. Retrieve `club_id = get_routing_club_id()`. 2. If `club_id` is `None` or `0`, return the default engine (`super().get_engine(bind=bind)`). 3. If a `club_id` is set, check the cache `self._club_engines`. 4. If not cached, connect to the Central database, lookup the club's `database_name` or `database_uri` from the `clubs` table: - If `database_uri` exists, use it. - Else, parse the Central connection URI (using `sqlalchemy.engine.url.make_url`) and substitute the database name with `honbu_club_<club_id>`. 5. Cache and return the engine. - Instantiate `db_orm = DynamicRoutingSQLAlchemy()`. ### 2.3. Request Lifecycle Integration (`app_factory.py`) Modify the Flask app factory to automatically clear the routing context after every request: ```python @app.teardown_request def teardown_routing_context(exception=None): from app.infrastructure.database.context import clear_routing_club_id clear_routing_club_id() ``` ### 2.4. Auth Decorator Integration (`authentication.py`) Modify [app/core/auth/authentication.py](file:///mnt/f/forgejo-git/club-manager/app/core/auth/authentication.py): - Update `require_auth` to call `set_routing_club_id(user.get('club_id'))` after successfully extracting the JWT token. - Update `require_club_access` to call `set_routing_club_id(club_id)`. --- ## 3. Verification Plan ### 3.1. Automated Unit Tests Write tests in `tests/unit/test_routing_infra.py`: - Test that `set_routing_club_id` and `get_routing_club_id` work across threads/coroutines. - Mock `SQLAlchemy.get_engine` and verify that calling a query inside a `with route_to_club(1):` context switches engines correctly, and returns to the default engine afterwards. ### 3.2. Integration Verification - Run the full existing test suite (`pytest`): ```bash docker exec club-manager_honbu-manager_1 python3 -m pytest tests/unit/ -v ``` All tests must pass. The default routing behavior must remain unchanged because no club database configurations have been set. --- **Part of Epic #105**

fastie81 referenced this issue from a commit

2026-07-09 11:21:45 +12:00

Phase 1: Dynamic Routing Infrastructure (#106)

fastie81 closed this issue

2026-07-09 11:21:54 +12:00