Phase 5 — ETL Migration Script & Isolation Tests #110

New issue

Open

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

fastie81 commented

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

Owner

Issue: Phase 5 — ETL Migration Script & Isolation Tests

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

1. Context & Purpose

To migrate running production data safely without loss, we need a robust ETL (Extract, Transform, Load) script. We also require a suite of E2E isolation tests to guarantee that a user authenticated to Club A cannot access data in Club B.

2. Technical Specification

2.1. Standalone ETL Script (`etl_migrate.py`)

Create scripts/etl_migrate.py:

Connect to the monolithic production database (the source) and the new Central database.
Execute migrations on the Central database (run_migrations(CENTRAL_MIGRATIONS)).
Copy Central tables:
- users, clubs, user_club_assignments, oauth2_config, api_tokens.
- Use parameterized queries; do not log password_hash values.
For each club in the clubs table:
1. Retrieve its ID.
2. Connect to the PG host and run: CREATE DATABASE honbu_club_<id>.
3. Swap the active connection target to honbu_club_<id> and apply run_migrations(CLUB_MIGRATIONS).
4. Query the monolithic database for all table rows belonging to this club and insert them into the new club database.
  - Tables to copy: members, families, family_guardians, attendance, belt_levels, gradings, class_schedules, notes_history, form_configurations, content_items, content_club_approvals.
  - Order of copy: Respect foreign key dependencies (e.g. families before members before attendance).
5. Run sequence synchronization queries:
  - For each table, determine the maximum ID value and execute:
    SELECT setval(pg_get_serial_sequence('table_name', 'id'), coalesce(max(id), 1), max(id) IS NOT NULL) FROM table_name;
    This prevents subsequent primary key collision errors on insert.
6. Update the club's database_name column in the Central database to honbu_club_<id>.
Perform Post-migration Verification:
- Count rows in the source table for each club and verify they match the row counts in the newly migrated target club database.
- Report any mismatches and block final execution.
Implement command line flags:
- --dry-run: Performs queries and shows count reports but executes no database creation or insert commands.
- --verify-only: Executes verification check on existing databases.
- --club-id <id>: Migrates a single club (useful for quick testing on staging).

2.2. Tenant Isolation Tests (`test_tenant_isolation.py`)

Create tests/e2e/test_tenant_isolation.py:

Setup:
- Create two test clubs and corresponding databases.
- Populate both with distinct member and class records.
Tests:
- Verify that setting context to Club 1 (with route_to_club(1):) returns exactly zero members from Club 2.
- Verify that an update command executed on Club 1 cannot modify records in Club 2.
- Verify that an authenticated API request using a JWT for Club 1 returns a 403 Forbidden if it attempts to query/modify endpoints with parameters referencing Club 2.
- Validate that get_users_by_club doesn't leak any user records across clubs.

2.3. complete docker-compose.test.yml Setup (Issue #93)

Fully populate docker-compose.test.yml by adding the PostgreSQL database service configuration matching the CI pipeline setup.
Define proper test database environment variables and health checks so integration/isolation tests can run successfully in local and remote test cycles.

3. Verification Plan

3.1. Automated Verification

Run the tenant isolation E2E tests:

docker exec club-manager_honbu-manager_1 python3 -m pytest tests/e2e/test_tenant_isolation.py -v

Run the ETL migration test using a mock database:

docker exec club-manager_honbu-manager_1 python3 -m pytest tests/e2e/test_etl_migration.py -v

3.2. Manual Verification

Run python scripts/etl_migrate.py --dry-run on staging. Check that the console logs print a detailed row-count projection and verify that no new databases are created.
Run the script fully on staging with two clubs and manually verify that login, member rosters, and dashboards function perfectly.

Part of Epic #105

# Issue: Phase 5 — ETL Migration Script & Isolation Tests **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 To migrate running production data safely without loss, we need a robust ETL (Extract, Transform, Load) script. We also require a suite of E2E isolation tests to guarantee that a user authenticated to Club A cannot access data in Club B. --- ## 2. Technical Specification ### 2.1. Standalone ETL Script (`etl_migrate.py`) Create [scripts/etl_migrate.py](file:///mnt/f/forgejo-git/club-manager/scripts/etl_migrate.py): - Connect to the monolithic production database (the source) and the new Central database. - Execute migrations on the Central database (`run_migrations(CENTRAL_MIGRATIONS)`). - Copy Central tables: - `users`, `clubs`, `user_club_assignments`, `oauth2_config`, `api_tokens`. - Use parameterized queries; do not log `password_hash` values. - For each club in the `clubs` table: 1. Retrieve its ID. 2. Connect to the PG host and run: `CREATE DATABASE honbu_club_<id>`. 3. Swap the active connection target to `honbu_club_<id>` and apply `run_migrations(CLUB_MIGRATIONS)`. 4. Query the monolithic database for all table rows belonging to this club and insert them into the new club database. - Tables to copy: `members`, `families`, `family_guardians`, `attendance`, `belt_levels`, `gradings`, `class_schedules`, `notes_history`, `form_configurations`, `content_items`, `content_club_approvals`. - Order of copy: Respect foreign key dependencies (e.g. `families` before `members` before `attendance`). 5. Run sequence synchronization queries: - For each table, determine the maximum ID value and execute: `SELECT setval(pg_get_serial_sequence('table_name', 'id'), coalesce(max(id), 1), max(id) IS NOT NULL) FROM table_name;` This prevents subsequent primary key collision errors on insert. 6. Update the club's `database_name` column in the Central database to `honbu_club_<id>`. - Perform Post-migration Verification: - Count rows in the source table for each club and verify they match the row counts in the newly migrated target club database. - Report any mismatches and block final execution. - Implement command line flags: - `--dry-run`: Performs queries and shows count reports but executes no database creation or insert commands. - `--verify-only`: Executes verification check on existing databases. - `--club-id <id>`: Migrates a single club (useful for quick testing on staging). ### 2.2. Tenant Isolation Tests (`test_tenant_isolation.py`) Create [tests/e2e/test_tenant_isolation.py](file:///mnt/f/forgejo-git/club-manager/tests/e2e/test_tenant_isolation.py): - Setup: - Create two test clubs and corresponding databases. - Populate both with distinct member and class records. - Tests: - Verify that setting context to Club 1 (`with route_to_club(1):`) returns exactly zero members from Club 2. - Verify that an update command executed on Club 1 cannot modify records in Club 2. - Verify that an authenticated API request using a JWT for Club 1 returns a `403 Forbidden` if it attempts to query/modify endpoints with parameters referencing Club 2. - Validate that `get_users_by_club` doesn't leak any user records across clubs. --- ### 2.3. complete docker-compose.test.yml Setup (Issue #93) - Fully populate `docker-compose.test.yml` by adding the PostgreSQL database service configuration matching the CI pipeline setup. - Define proper test database environment variables and health checks so integration/isolation tests can run successfully in local and remote test cycles. ## 3. Verification Plan ### 3.1. Automated Verification - Run the tenant isolation E2E tests: ```bash docker exec club-manager_honbu-manager_1 python3 -m pytest tests/e2e/test_tenant_isolation.py -v ``` - Run the ETL migration test using a mock database: ```bash docker exec club-manager_honbu-manager_1 python3 -m pytest tests/e2e/test_etl_migration.py -v ``` ### 3.2. Manual Verification - Run `python scripts/etl_migrate.py --dry-run` on staging. Check that the console logs print a detailed row-count projection and verify that no new databases are created. - Run the script fully on staging with two clubs and manually verify that login, member rosters, and dashboards function perfectly. --- **Part of Epic #105**