Phase 2 — Schema & Migration Split #107

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 2 — Schema & Migration Split

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

1. Context & Purpose

We need to separate the schema migrations list into system-wide central migrations and club-specific tenant migrations. This allows the system to initialize the Central Database first, and then dynamically create and migrate each club's dedicated database.

2. Technical Specification

2.1. Migration Definitions Split (`migrations.py`)

Modify app/infrastructure/database/migrations.py:

Partition the list of migrations into two distinct tuples/lists:
- CENTRAL_MIGRATIONS: Contains migrations that apply to central tables:
  - migration_ensure_clubs_table (if defined, or other club-list tables)
  - migration_add_timezone_field (on clubs)
  - migration_add_created_updated_timestamps (on clubs)
  - migration_add_user_club_assignments_table
  - migration_add_api_tokens_table (since tokens are checked centrally)
  - oauth2_config tables
  - users table
- CLUB_MIGRATIONS: Contains migrations that apply to club-specific tables:
  - members table creation
  - families table creation
  - attendance table creation
  - belt_levels table creation
  - gradings table creation
  - class_schedules table creation
  - notes_history table creation
  - form_configurations table creation
  - content_items and content_club_approvals (CMS content is local to each club)
  - family_guardians table creation

2.2. Clubs Database Routing Metadata

Create a migration in CENTRAL_MIGRATIONS to add a new database_name column to the clubs table if it does not already exist:

ALTER TABLE clubs ADD COLUMN IF NOT EXISTS database_name TEXT;

2.3. Dynamic Migration Execution Flow

Update run_all_migrations(engine) in migrations.py:

Initialize the migration tracking table in the database pointed to by the provided engine.
Run all CENTRAL_MIGRATIONS against that central database engine.
Query the clubs table for all clubs with a non-null database_name.
For each club discovered:
- Construct the connection URI for honbu_club_<id> using the default database URL parameters but swapping the database name to the club's specific database.
- Create a SQLAlchemy engine for that club database.
- Apply CLUB_MIGRATIONS on that club database engine.
If running inside tests, handle the migration execution for in-memory or temporary test databases safely (only running central migrations or applying both as needed by the test environment).

2.4. New Club Registration Flow (`club_ops.py`)

Update ClubOperations.create_club in app/core/operations/club_ops.py:

When a new club is created, insert its record into the Central clubs table.
Set its database_name to honbu_club_<id>.
Using an administrative database connection (e.g., connected to the postgres default database), execute CREATE DATABASE honbu_club_<id>.
Instantiate an engine for the new database, run run_migrations with CLUB_MIGRATIONS, and commit.

3. Verification Plan

3.1. Automated Unit Tests

Write tests in tests/unit/test_migrations_split.py:

Test that executing run_all_migrations with no clubs registered only creates Central tables.
Test that registering a club with a custom database_name causes run_all_migrations to attempt connection and schema creation on that database.

3.2. Integration Verification

Verify that running migrations against a clean local database setup constructs the tables correctly.
All 192 tests must pass successfully.

Part of Epic #105

# Issue: Phase 2 — Schema & Migration Split **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 separate the schema migrations list into system-wide central migrations and club-specific tenant migrations. This allows the system to initialize the Central Database first, and then dynamically create and migrate each club's dedicated database. --- ## 2. Technical Specification ### 2.1. Migration Definitions Split (`migrations.py`) Modify [app/infrastructure/database/migrations.py](file:///mnt/f/forgejo-git/club-manager/app/infrastructure/database/migrations.py): - Partition the list of migrations into two distinct tuples/lists: - `CENTRAL_MIGRATIONS`: Contains migrations that apply to central tables: - `migration_ensure_clubs_table` (if defined, or other club-list tables) - `migration_add_timezone_field` (on `clubs`) - `migration_add_created_updated_timestamps` (on `clubs`) - `migration_add_user_club_assignments_table` - `migration_add_api_tokens_table` (since tokens are checked centrally) - `oauth2_config` tables - `users` table - `CLUB_MIGRATIONS`: Contains migrations that apply to club-specific tables: - `members` table creation - `families` table creation - `attendance` table creation - `belt_levels` table creation - `gradings` table creation - `class_schedules` table creation - `notes_history` table creation - `form_configurations` table creation - `content_items` and `content_club_approvals` (CMS content is local to each club) - `family_guardians` table creation ### 2.2. Clubs Database Routing Metadata - Create a migration in `CENTRAL_MIGRATIONS` to add a new `database_name` column to the `clubs` table if it does not already exist: ```sql ALTER TABLE clubs ADD COLUMN IF NOT EXISTS database_name TEXT; ``` ### 2.3. Dynamic Migration Execution Flow Update `run_all_migrations(engine)` in `migrations.py`: 1. Initialize the migration tracking table in the database pointed to by the provided `engine`. 2. Run all `CENTRAL_MIGRATIONS` against that central database engine. 3. Query the `clubs` table for all clubs with a non-null `database_name`. 4. For each club discovered: - Construct the connection URI for `honbu_club_<id>` using the default database URL parameters but swapping the database name to the club's specific database. - Create a SQLAlchemy engine for that club database. - Apply `CLUB_MIGRATIONS` on that club database engine. 5. If running inside tests, handle the migration execution for in-memory or temporary test databases safely (only running central migrations or applying both as needed by the test environment). ### 2.4. New Club Registration Flow (`club_ops.py`) Update `ClubOperations.create_club` in [app/core/operations/club_ops.py](file:///mnt/f/forgejo-git/club-manager/app/core/operations/club_ops.py): - When a new club is created, insert its record into the Central `clubs` table. - Set its `database_name` to `honbu_club_<id>`. - Using an administrative database connection (e.g., connected to the `postgres` default database), execute `CREATE DATABASE honbu_club_<id>`. - Instantiate an engine for the new database, run `run_migrations` with `CLUB_MIGRATIONS`, and commit. --- ## 3. Verification Plan ### 3.1. Automated Unit Tests Write tests in `tests/unit/test_migrations_split.py`: - Test that executing `run_all_migrations` with no clubs registered only creates Central tables. - Test that registering a club with a custom `database_name` causes `run_all_migrations` to attempt connection and schema creation on that database. ### 3.2. Integration Verification - Verify that running migrations against a clean local database setup constructs the tables correctly. - All 192 tests must pass successfully. --- **Part of Epic #105**

fastie81 added reference feature/db-split-phase1-routing

2026-07-13 20:01:03 +12:00

fastie81 closed this issue

2026-07-13 20:01:10 +12:00