QDBAuth

QDBAuth is the central login/2FA service for the QDB ecosystem. It owns the ONLY username/password check and the ONLY TOTP 2FA check across every consuming site — QDBAdmin, the CMS (built in Appsmith), Troyen Data (TD), and the QDB data migration tool all authenticate through it instead of maintaining their own login logic.

Architecture

• Django, but no ORM anywhere — every table is accessed via raw SQL through django.db.connection (see apps/auth/users.py's module docstring). Schema changes are plain .sql files under db/, applied manually against the live database — there are no Django migrations.
• Sessions are signed cookies (SESSION_ENGINE = "django.contrib.sessions.backends.signed_cookies"), not server-side session storage — app servers are stateless and interchangeable, with no sticky-session requirement.
• Tokens (access token, pending-login token, signup token, SSO handoff token) are Django signing values (HMAC-signed, salted per purpose), not JWTs — see apps/auth/tokens.py. A generic JWT decoder cannot read them; verification requires Django's signing.loads() with the matching key and salt.

Login flow

Two-step and 2FA-mandatory for every real account:

1. POST /api/login/ (email + password) → pending_token
2. POST /api/verify-2fa/ (pending_token + TOTP code) → access_token + refresh_token

Both endpoints require an X-QDB-Client-Secret header matching the shared secret (QDB_SSO_SHARED_SECRET). Business-logic failures (wrong password, wrong code, not yet 2FA-enrolled) return HTTP 400; a missing/wrong client secret returns 401. Access tokens are short-lived and stateless (ACCESS_TOKEN_MAX_AGE, default 15 minutes); refresh tokens are the only credential actually stored server-side (auth_refresh_tokens, only a SHA-256 hash is kept).

Per-site credentials

Accounts created after this feature shipped (2026-07-28) get a separate password + TOTP secret per granted site (apps/auth/site_credentials.py + db/create_user_site_credentials_table.sql), instead of one shared credential across every site they're approved for. This fixed a real problem: an admin granting one person access to 2-3 sites used to send 2-3 verification emails that all pointed at the same shared password/authenticator, so completing setup via the first email made the others immediately say "already verified."

Accounts created before this feature exist keep working exactly as before, on purpose — they were not migrated. site_credentials.has_any(user_id) is the legacy/new-style switch: zero rows there means legacy behavior (shared credential, site-blind); at least one row means every login/signup/2FA/QR-code path resolves against that specific site's own credential row instead.

Site-specific roles

A site can define its own role vocabulary instead of the global admin/developer/user roles — see ROLE_GROUPS in apps/auth/users.py. TD (Troyen Data) uses its own five roles (Developer, Support, Dev Lead, Support Lead, Super Admin) instead. The group's admin_equivalent role sets is_admin=1 on the account, but this is unrelated to QDBAuth's own dashboard-admin access — that check (dashboard.require_admin) only ever matches the literal global "admin" role, so a TD "Super Admin" can never unlock QDBAuth's own admin dashboard. The Create/Edit User forms in the dashboard swap the Role dropdown's options dynamically based on which site checkbox is checked.

Reliability

QDBAuth is a single point of failure for every consuming site's login — if it's down, nobody can log in anywhere. Groundwork has been added to support fixing this without requiring the team to manually manage two servers:

• A /health/ endpoint (checks app + DB connectivity), for load-balancer health checks or uptime monitoring.
• A production WSGI launcher (serve_waitress.py), replacing manage.py runserver (Django's dev server — single-threaded, no auto-restart, never meant for unattended production use).

The recommended direction (pending management decision) is cloud hosting (AWS/Azure) using the provider's own auto-recovery for a single instance (Auto Scaling Group / VM Scale Set with min=1) plus a managed database with automatic failover (RDS Multi-AZ / Azure zone-redundant MySQL) — rather than the team operating two servers and MySQL replication by hand. Combined with locally-verified, longer-lived access tokens, an already-logged-in user on any site would not notice a brief failover at all.

Ecosystem consumers

• QDBAdmin — browser-based SSO redirect flow (login_view/verify_2fa_view).
• CMS (Appsmith) — JSON API flow (/api/login/ + /api/verify-2fa/), two-step UI (password screen, then TOTP code screen) since Appsmith's original login JS assumed a single-step, non-2FA API.
• TD (Troyen Data) — being migrated from its own local login/auth API to QDBAuth's centralized API; keeps managing its own roles/permissions locally (QDBAuth doesn't know about TD's role meanings beyond the role string itself).
• QDB data migration tool — planned integration, same pattern as CMS/TD.