Epic: Database-per-Club Multi-Tenancy Split & Data Migration #105

New issue

Open

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

fastie81 commented

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

Owner

Epic: Database-per-Club Multi-Tenancy Split & Data Migration

Type: Refactoring / Architecture
Priority: High
Status: Planning / Ready for Implementation

1. Goal & Objectives

To improve tenant data isolation, compliance (GDPR/privacy), database performance, and security, we are migrating the Honbu Manager application from a shared-database multi-tenancy model to a database-per-club multi-tenant model.

Central Database (honbu_central): Contains system-level configuration, users, OAuth configurations, and club-to-database routing metadata.
Club Databases (honbu_club_<id>): Contains club-scoped data (members, attendance, notes, gradings, schedules, etc.), completely isolated physically from other clubs.
Content Isolation: Remove cross-club content sharing. All content tables (content_items and content_club_approvals) reside locally in each club database.
User Storage (Option A): Global user credentials reside in the Central database. This avoids modifying core authentication routing, OAuth callback parameters, and JWT verification logic.

2. Dynamic Routing Architecture

We will implement dynamic connection routing in Flask & SQLAlchemy using Python's contextvars to set the active database on a per-request basis.

                          ┌──────────────────────────┐
                          │    Incoming API Request  │
                          └─────────────┬────────────┘
                                        │
                                        ▼
                       ┌────────────────────────────────┐
                       │  Auth Decorators               │
                       │  - Verifies JWT / Club Access  │
                       │  - Sets active_club_id         │
                       └─────────────┬──────────────────┘
                                     │
                                     ▼
                       ┌────────────────────────────────┐
                       │  DynamicRoutingSQLAlchemy      │
                       │  - Intercepts get_engine()     │
                       │  - Returns tenant DB engine    │
                       └─────────────┬──────────────────┘
                                     │
             ┌───────────────────────┴───────────────────────┐
             ▼                                               ▼
┌─────────────────────────┐                     ┌──────────────────────────┐
│    Central Database     │                     │     Club Database        │
│  (users, assignments)   │                     │  (members, gradings...)  │
└─────────────────────────┘                     └──────────────────────────┘

3. Implementation Phases & Issues

We have structured the implementation into 6 distinct, sequential, and testable phases to ensure maximum stability and zero-downtime path:

Phase 1 — Dynamic Routing Infrastructure
Build thread-safe routing context (contextvars) and dynamic SQLAlchemy engine switching.
Phase 2 — Schema & Migration Split
Organize migrations into central tables vs club-specific tables.
Phase 3 — Repository Refactoring & Gaps
Refactor cross-DB JOINs (e.g. get_users_by_club), adapt dashboard aggregation, adjust club deletion, and add logical FK constraints.
Phase 4 — Backup & Restore Multi-DB Support
Implement backup and recovery for multiple SQL databases.
Phase 5 — ETL Migration Script & Isolation Tests
Create production migration scripts and E2E security verification tests.
Phase 6 — Staging Validation & Production Cutover
Validation on staging, production data migration checklist, and rollback protocols.

4. Security & Tenant Isolation Controls

Physical Isolation: Clubs will be physically blocked from querying each other's databases. Since SQLAlchemy binds requests by active request routing contexts, a bug in Python code cannot fetch cross-club records unless context parameters are explicitly modified.
Logical Validation: Check that the context variable matches JWT validation results strictly. The middleware should check:
- System admins can set any club routing context.
- Instructors and Club Admins can only set the routing context to the club(s) they are assigned to.
- Members/Guardians can only route to their registered club.

# Epic: Database-per-Club Multi-Tenancy Split & Data Migration **Type:** Refactoring / Architecture **Priority:** High **Status:** Planning / Ready for Implementation --- ## 1. Goal & Objectives To improve tenant data isolation, compliance (GDPR/privacy), database performance, and security, we are migrating the Honbu Manager application from a shared-database multi-tenancy model to a **database-per-club multi-tenant model**. - **Central Database (`honbu_central`)**: Contains system-level configuration, users, OAuth configurations, and club-to-database routing metadata. - **Club Databases (`honbu_club_<id>`)**: Contains club-scoped data (members, attendance, notes, gradings, schedules, etc.), completely isolated physically from other clubs. - **Content Isolation**: Remove cross-club content sharing. All content tables (`content_items` and `content_club_approvals`) reside locally in each club database. - **User Storage (Option A)**: Global user credentials reside in the Central database. This avoids modifying core authentication routing, OAuth callback parameters, and JWT verification logic. --- ## 2. Dynamic Routing Architecture We will implement dynamic connection routing in Flask & SQLAlchemy using Python's `contextvars` to set the active database on a per-request basis. ``` ┌──────────────────────────┐ │ Incoming API Request │ └─────────────┬────────────┘ │ ▼ ┌────────────────────────────────┐ │ Auth Decorators │ │ - Verifies JWT / Club Access │ │ - Sets active_club_id │ └─────────────┬──────────────────┘ │ ▼ ┌────────────────────────────────┐ │ DynamicRoutingSQLAlchemy │ │ - Intercepts get_engine() │ │ - Returns tenant DB engine │ └─────────────┬──────────────────┘ │ ┌───────────────────────┴───────────────────────┐ ▼ ▼ ┌─────────────────────────┐ ┌──────────────────────────┐ │ Central Database │ │ Club Database │ │ (users, assignments) │ │ (members, gradings...) │ └─────────────────────────┘ └──────────────────────────┘ ``` --- ## 3. Implementation Phases & Issues We have structured the implementation into 6 distinct, sequential, and testable phases to ensure maximum stability and zero-downtime path: 1. **[Phase 1 — Dynamic Routing Infrastructure](#106)** *Build thread-safe routing context (`contextvars`) and dynamic SQLAlchemy engine switching.* 2. **[Phase 2 — Schema & Migration Split](#107)** *Organize migrations into central tables vs club-specific tables.* 3. **[Phase 3 — Repository Refactoring & Gaps](#108)** *Refactor cross-DB JOINs (e.g. `get_users_by_club`), adapt dashboard aggregation, adjust club deletion, and add logical FK constraints.* 4. **[Phase 4 — Backup & Restore Multi-DB Support](#109)** *Implement backup and recovery for multiple SQL databases.* 5. **[Phase 5 — ETL Migration Script & Isolation Tests](#110)** *Create production migration scripts and E2E security verification tests.* 6. **[Phase 6 — Staging Validation & Production Cutover](#111)** *Validation on staging, production data migration checklist, and rollback protocols.* --- ## 4. Security & Tenant Isolation Controls 1. **Physical Isolation**: Clubs will be physically blocked from querying each other's databases. Since SQLAlchemy binds requests by active request routing contexts, a bug in Python code cannot fetch cross-club records unless context parameters are explicitly modified. 2. **Logical Validation**: Check that the context variable matches JWT validation results strictly. The middleware should check: - System admins can set any club routing context. - Instructors and Club Admins can only set the routing context to the club(s) they are assigned to. - Members/Guardians can only route to their registered club.

fastie81 referenced this issue

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

Phase 1 — Dynamic Routing Infrastructure #106

fastie81 referenced this issue

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

Phase 2 — Schema & Migration Split #107

fastie81 referenced this issue

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

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

fastie81 referenced this issue

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

Phase 4 — Backup & Restore Multi-DB Support #109