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).

The browser-redirect variant of login (/login/?return_url=...) also backs a second account type as of 2026-09-07 — see "Site Clients" below.

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.

Roles & role-based dashboard access (updated 2026-09-06/07)

Every staff account has one role string plus an is_admin flag. The shared default role list is a 5-tier model — Super Admin, Admin, Developer, Support Lead, User — controlling QDBAuth's own dashboard access:

• Super Admin: every dashboard page, every site (checked via the account's real is_admin DB flag, not the role string — so pre-2026-09-06 accounts keep working with no migration).
• Admin: Users/Create-user/Site Config/Site Clients, scoped to the account's own approved_sites.
• Developer / Support Lead / User: no admin screens — redirected to a self-service My Account page instead (own password, authenticator, profile picture only).

Role lists are now database-backed, not hardcoded: apps/auth/site_roles.py

• the site_roles table let any site define its own role vocabulary (each with a flagged default and admin_equivalent role) through a dashboard page (/dashboard/site-roles/), replacing the ROLE_GROUPS Python dict this section used to describe. A site with no roles of its own falls back to the shared default list. TD (Troyen Data) still has its own five roles (Developer, Support, Dev Lead, Support Lead, Super Admin), now stored as data instead of code. See Roles, Permissions & Multi-Tenancy for the full model.

Site Clients (new, 2026-09-07)

Each registered site's own end-customers — previously managed outside QDBAuth — can now get a username/password/TOTP login created and managed directly from the dashboard (apps/auth/site_clients.py + db/create_site_clients_table.sql), scoped to exactly one site each. A client logs in through the same browser SSO flow as staff (login_view falls back to site_clients.authenticate() once a users-table lookup misses) and is always redirected back to their own site — never the QDBAuth dashboard. See Authentication, MFA & Token Internals for the mechanics and Administrator User Guide for day-to-day use.

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.
• Each registered site's own end-customers — via Site Clients (2026-09-07), for sites that have their own client base to log in (e.g. MTC, TD).

Documentation map

• API Reference — every endpoint, request/response shapes, integration steps for a new consuming app.
• Authentication, MFA & Token Internals — how login actually completes, TOTP mechanics, session vs. token model, how tokens are generated and validated, security requirements for consumers, and how Site Client login differs from staff.
• Roles, Permissions & Multi-Tenancy — the 5-tier role model, per-site role management, Site Clients, and an explicit comparison against Keycloak-style tenancy.
• Administrator User Guide — day-to-day dashboard operation: creating users and site clients, managing per-site roles, bulk upload, password resets, site management, troubleshooting.
• Known Limitations & Security Notes — open issues, resolved issues, and architectural limitations worth knowing about.
• Changelog — dated, reverse-chronological record of delivered changes.