← 中文

Multi-Tenant Database Isolation and AI-Assisted Migration Architecture

Version v0.1 · Design draft · Covers player identity, standard clubs, private clubs, and tournament data

Modular monolith first, evolvable to dedicated databases · AI-assisted, deterministic execution

1. Objectives and Scope

POFA's global identity domain stores player login accounts, external identity references (e.g., DUPR ID links), the organization directory, and tenant data routing. Standard clubs may use a shared database; clubs with higher privacy or deployment requirements may later migrate to a dedicated database or a supported customer-hosted environment.

A private club's membership roster, contact details, match records, and files must be stored and accessed within its authorization boundary. Files are typically kept in isolated object storage rather than placed directly in the relational database.

2. Proposed Logical Structure

POFA Web / API
    │
    ├── Identity & Authorization (global identity & authorization)
    ├── Tenant Router (club_id → data location)
    │     ├── Shared PostgreSQL (shared schema + RLS)
    │     └── Dedicated PostgreSQL (on-demand dedicated DB)
    ├── Object Storage (tenant-scoped file access)
    ├── Migration Runner (versioned migrations, validation, rollback)
    └── Audit & Event Outbox (auditing & reliable sync)

3. Data Ownership

4. Constraints Required in the First Release

1. Globally stable IDs: prefer UUIDs for business entities; UUIDs reduce collision risk but do not automatically resolve duplicate identities, business conflicts, or version divergence.
2. Tenant boundaries: all club-scoped tables include club_id; global user and global routing tables need not be forced to carry it. Composite unique constraints and foreign keys must match tenant boundaries.
3. Shared-database security: use PostgreSQL RLS together with application-layer authorization, and test cross-tenant privilege escalation, background jobs, and admin bypass paths.
4. Unified business contracts: shared and dedicated databases maintain compatible schema / API versions for the corresponding business modules, rather than requiring every database to contain exactly the same tables.
5. Migration versioning: record migration versions and results per database; support batch execution, failure retry, and per-tenant pausing. Do not assume every database can always be upgraded in lockstep.
6. Independent keys: dedicated deployments use tenant-level key management; key rotation, backup, and destruction must be auditable.

5. Database Move-Out, Merge, and Deletion

Move-Out to a Dedicated Database

Freeze or restrict tenant writes → take a consistent snapshot → import into the target database → validate record counts, relationships, permissions, and business invariants → apply incremental changes → atomically switch routing → monitoring and rollback window → purge old copies per retention policy.

Merge into a Shared Database

Pre-check UUIDs, tenant IDs, duplicate accounts, and business conflicts → import into a staging area → transform using reviewed mappings → validate foreign keys and business rules such as scores → switch routing → monitor → clean up the old database and replicas. Copying data and editing routing alone is not sufficient for a successful migration.

Deletion

First verify the requester's identity and processing authority, identify applicable legal retention obligations, then carry out access revocation, primary database deletion, key destruction (where applicable), and backup expiry cleanup. Copies in caches, object storage, logs, and authorized shared locations must be handled separately. Whether crypto-erasure is effective depends on key isolation and how all copies are managed.

6. Cross-Database Identity and Tournament Synchronization

A player may belong to multiple clubs. The global identity layer provides only a stable reference; private club data must not automatically enter public rankings merely because it shares the same player ID. Cross-database rankings and results aggregation require explicit authorization, data minimization, and withdrawal mechanisms.

Authoritative data such as scores remains in the owning tournament database. External synchronization uses Outbox events, idempotent consumers, and replayable processing records, avoiding any assumption that multiple databases can commit atomically together.

7. AI Field Calibration and Migration Auditing

AI can read de-identified schemas, field comments, and permitted samples to propose field mappings, type conversions, duplicate identity candidates, and anomaly checks. It cannot infer from field names alone that club_id and organization_id are equivalent, and confidence scores cannot serve as migration acceptance criteria.

Schema / de-identified samples → AI proposes mappings
    → human review of ambiguous & sensitive mappings
    → versioned mapping rules
    → deterministic migration program (dry-run first)
    → record counts / foreign keys / hashes / score rules / permission checks
    → approved cutover + audit log

Sample data involving personal information must follow minimization principles, with the model service's data handling scope made explicit; high-risk fields should default to synthetic or de-identified samples.

8. Iteration Roadmap

9. Open Decisions

1. Whether private clubs may publish tournament results, and which fields may be exposed.
2. Whether tournaments always belong to exactly one club; how the authoritative data source is determined for joint tournaments spanning clubs.
3. Policies for binding, unbinding, synchronizing, and authorizing DUPR external identifiers.
4. Pricing thresholds and service levels for dedicated databases and customer self-hosting.
5. Regional data retention periods, deletion exceptions, and division of responsibility for payment data.

Status: design draft, not yet validated in POFA production. Recommend revalidating against real tournament workflows during the web implementation phase, once exam preparation concludes.