Cross-DB User FK Pattern

Problem

Hardhat Flow uses per-tenant MySQL databases for all business data (bids, jobs, photos, activity, etc.) and a single platform database (default) for accounts and companies.

accounts.User lives only in the platform DB.

Every tenant model that references a user (e.g. Photo.uploaded_by, Job.project_manager, ActivityLog.performed_by) uses db_constraint=False on the FK because MySQL cannot enforce a FK across databases. However, db_constraint=False does not prevent Django from emitting a SQL JOIN when you call select_related("uploaded_by") — Django generates the join against whichever database the outer queryset was sent to (the tenant DB), and MySQL raises 1146 Table '<tenant>.accounts_user' doesn't exist.

The same error happens when code reads obj.uploaded_by lazily (or obj.user, obj.performed_by, etc.) if the instance was loaded from a tenant DB — Django issues a SELECT … FROM accounts_user WHERE id = ? against the tenant DB alias.

Rule

Never use select_related or lazy descriptor access for a cross-DB FK.

Platform app labels (those in PLATFORM_APP_LABELS in apps/companies/router.py): accounts, companies, admin, auth, contenttypes, sessions.

Any FK from a non-platform model to one of these models is cross-DB.

Canonical Fix

Use fetch_users_by_ids from apps.accounts.db_utils:

from apps.accounts.db_utils import fetch_users_by_ids

# 1. Collect FK column values (no descriptor traversal)
user_ids = [obj.uploaded_by_id for obj in photo_list]

# 2. Batch-fetch from the platform DB
user_map = fetch_users_by_ids(user_ids)  # dict[UUID, User]

# 3. Pass the map through serializer context
PhotoSerializer(photo_list, many=True, context={"user_map": user_map, ...})

Inside a serializer field:

def get_uploaded_by_name(self, obj):
    user = (self.context.get("user_map") or {}).get(obj.uploaded_by_id)
    if not user:
        return None
    return f"{user.first_name} {user.last_name}".strip() or user.email

For accessing the ID without a join, use the FK column directly (obj.uploaded_by_id), not obj.uploaded_by.id — the latter touches the descriptor and fires a cross-DB lookup.

What NOT to do

# BAD — emits JOIN against tenant DB
Photo.objects.filter(...).select_related("uploaded_by")

# BAD — lazy cross-DB lookup when photo is from tenant DB
name = photo.uploaded_by.first_name

# BAD — also triggers descriptor
id_field = serializers.UUIDField(source="uploaded_by.id")

Affected models

Tenant model	Cross-DB FK
Photo	uploaded_by → accounts.User
Job	project_manager, supervisor → accounts.User
Bid	project_manager, supervisor → accounts.User
JobUpdate	created_by, archived_by → accounts.User
JobWorkerAssignment	user → accounts.User
ActivityLog	performed_by → accounts.User
Update	user → accounts.User