The GP Booking App uses a multi-tenant database architecture where each tenant (client VPS) has its own PostgreSQL database (sector_client), isolated from the shared registry database (default). The primary VPS hosts the default database with platform-level data; each client VPS hosts its own tenant database.
| Database | Purpose | Host |
|---|---|---|
default |
Global registry: platform admins, tenancy, auth, partner orgs | Primary VPS |
sector_gp |
GP tenant data (same physical DB as default currently) | Primary VPS |
sector_client |
Per-tenant data (one per client VPS) | Client VPS |
sector_client_{subdomain} |
Dynamic per-client aliases | Client VPS |
sector_dental |
Dental tenant data | Primary VPS |
tenancy/routers.py)| App Set | Apps | Routes To |
|---|---|---|
GLOBAL_APPS |
tenancy, auth, admin, partner, dashboards, etc. | default |
GP_APPS |
appointments, clinical_data, onboarding, users, integrations, etc. | sector_gp / sector_client* |
DENTAL_APPS |
dental | sector_dental |
Before: users was hardcoded to route to default regardless of tenant context.
After: Added 'users' to GP_APPS set. Removed if app_label == 'users': return 'default' from _sector_db() and allow_migrate().
Effect: Tenant users now route to sector_gp or sector_client depending on the tenant context (set by TenantMiddleware which runs before AuthenticationMiddleware).
Files changed:
tenancy/routers.py — removed users hard-route, added to GP_APPSScript: scripts/migrate_users_to_tenant.py
Action: Copied 32 tenant users from default to sector_client:
nichola_jorja)Key detail: Users were matched by username across databases (not PK, since PKs differ). The is_staff/is_superuser flags were set to False in sector_client — platform privileges stay in default.
| Old Role | New Role |
|---|---|
PARTNER_ADMIN |
ADMIN |
PARTNER_CLINICIAN |
CLINICIAN |
PARTNER_RECEPTIONIST |
RECEPTIONIST |
PARTNER_REGISTERED_MANAGER |
REGISTERED_MANAGER |
ORG_ADMIN |
ADMIN |
Added REGISTERED_MANAGER to the CustomUser.Roles enum in users/models.py.
Client VPS roles: PATIENT, CLINICIAN, RECEPTIONIST, REGISTERED_MANAGER, ADMIN
Primary VPS roles: ADMIN, SYSTEM_ADMIN, SIRO (platform-level only)
Problem: Appointments stored in sector_client had FK references to users (patient_id, clinician_id) using user PKs from sector_client. But the router sent users queries to default, causing FK lookup failures (appointment.patient returned wrong user or DoesNotExist).
Fix: With users now in GP_APPS, FK lookups resolve within the same database. Workarounds removed:
appointments/views.py — removed user resolution by username in AppointmentCreateView.form_validappointments/views.py — removed .using('default') patient lookup in get_initialappointments/views.py — removed AppointmentDetailView.get_object FK overridesappointments/views.py — added select_related('patient', 'clinician') to list/detail/cancel/checkin views (N+1 prevention, kept)Problem: All dashboards (/dashboard/receptionist/, /dashboard/clinician/, /dashboard/admin/) had:
target_db = 'default'get_practice_context() that failed because StaffRoleRecord data lived in default but queries went to sector_clientFix: Refactored dashboard views to:
target_db dynamically from tenant context via get_tenant_db_alias().using('default') with user__username filter for StaffRoleRecord queries (data not yet migrated)Files changed:
dashboards/views/dashboards.py — receptionist, clinician, admin dashboardsdashboards/views/base.py — get_practice_context() fallbackSeveral apps had migrations recorded as applied in sector_client's django_migrations table but the actual tables/columns were never created:
| App | Migrations Applied |
|---|---|
insurance_billing |
0001_initial — created privateinsuranceinvoice table |
integrations |
0008_user_imap_account, 0009_oauth2_migration — created integration_user_imap_account table |
clinical_data |
0013—0017 — added snomed_attributes column, SNOMED ICD-10 mapping, drug safety rules |
Fix: For each:
python manage.py migrate <app> --database=sector_client --fake 0001 (or appropriate initial)python manage.py migrate <app> --database=sector_clienttemplates/appointments/appointment_check_in.html — created for check-in workflow (was missing, causing 500 error)appointment_form.html (/clinical/api/snomed/search/ → /clinical/snomed/search/)The onboarding_staffrolerecord table has data only in default. The staff role records reference default user PKs. Dashboard views currently use a fallback to .using('default') with user__username filter.
To migrate: Copy staffrolerecord entries from default to sector_client, updating user_id references from default PKs to sector_client PKs (matched by username).
Practice and Clinic IDs differ between default and sector_client:
default: Practice(id=8, clinic_id=2) → Clinic(id=2, "Test Clinic")sector_client: Practice(id=1, clinic_id=1) → Clinic(id=1, "Test Clinic")The dashboard views resolve this by looking up practice by name in the target database, but a proper data migration would align the IDs.
The following apps may have similar migration gaps (recorded as applied but tables missing):
onboarding — check onboarding_staffrolerecord, onboarding_practiceslot_management — check slot_management_clinicreporting — check reporting_platformactivityeventThe reporting_platformactivityevent table has a FK constraint on practice_id that references onboarding_practice — the log shows repeated FK violations because practice_id=8 (from default) doesn't exist in sector_client.
| File | Changes |
|---|---|
tenancy/routers.py |
Removed users hard-route, added to GP_APPS |
users/models.py |
Added REGISTERED_MANAGER to Roles enum |
dashboards/views/dashboards.py |
Fix target_db, clinic resolution, StaffRoleRecord fallback |
dashboards/views/base.py |
StaffRoleRecord fallback by username |
appointments/views.py |
Removed user resolution code, fixed get_initial, select_related |
appointments/forms.py |
_post_clean FK handling (unchanged, works correctly) |
scripts/migrate_users_to_tenant.py |
One-time user migration script |
/partner/) → loads for manager user