starred/bambuddy-maziggy-1

Fork 0

mirror of https://github.com/maziggy/bambuddy.git synced 2026-05-09 08:25:54 +02:00

[PR #1126] [MERGED] feat(oidc): Azure Entra ID support — configurable email claim & verification + Remember Me persistent login #1168

New issue

Closed

opened 2026-05-07 00:16:25 +02:00 by BreizhHardware · 0 comments

BreizhHardware commented

2026-05-07 00:16:25 +02:00

Owner

📋 Pull Request Information

Original PR: https://github.com/maziggy/bambuddy/pull/1126
Author: @netscout2001
Created: 4/25/2026
Status: ✅ Merged
Merged: 4/25/2026
Merged by: @maziggy

Base: dev ← Head: feature/2fa-oidc-authentication

📝 Commits (10+)

13967e8 feat(oidc): add Azure Entra ID support with configurable email claim resolution
9fb0a3d fix(oidc): harden email claim resolution, guards, and test coverage
0dfb5c5 fix(oidc): apply maintainer review fixes for Azure Entra ID support
dacf003 feat(auth): add Remember Me option to login page
9e3f09d fix(db): swallow 'no such column' in _safe_execute for idempotent RENAME COLUMN migrations
1864bef fix(oidc): harden _safe_execute, extract email/autolink helpers, add test coverage
2c32fd7 fix(db): restore 'no such column' and 'duplicate column name' to _safe_execute swallow list
f99e31b fix(db): swallow PostgreSQL 'does not exist' error for idempotent RENAME COLUMN migrations
18ff085 fix(db,auth): move DML backfills out of _safe_execute; split setAuthToken storage writes
3eaa2b5 fix(oidc,db): address PR #1118 maintainer review — migration safety, email shape checks, dialect branching

📊 Changes

22 files changed (+2289 additions, -74 deletions)

View changed files

📝 backend/app/api/routes/mfa.py (+136 -18)
📝 backend/app/core/database.py (+139 -19)
📝 backend/app/models/oidc_provider.py (+24 -1)
📝 backend/app/schemas/auth.py (+56 -3)
📝 backend/tests/integration/test_mfa_api.py (+777 -0)
📝 backend/tests/unit/test_db_dialect.py (+358 -4)
➕ docs/authentication/entra-id.md (+82 -0)
📝 frontend/src/__tests__/api/client.test.ts (+56 -0)
➕ frontend/src/__tests__/components/OIDCProviderSettings.test.tsx (+125 -0)
📝 frontend/src/__tests__/pages/LoginPage.test.tsx (+328 -1)
📝 frontend/src/api/client.ts (+25 -11)
📝 frontend/src/components/OIDCProviderSettings.tsx (+50 -1)
📝 frontend/src/contexts/AuthContext.tsx (+9 -9)
📝 frontend/src/i18n/locales/de.ts (+8 -0)
📝 frontend/src/i18n/locales/en.ts (+8 -0)
📝 frontend/src/i18n/locales/fr.ts (+8 -0)
📝 frontend/src/i18n/locales/it.ts (+8 -0)
📝 frontend/src/i18n/locales/ja.ts (+8 -0)
📝 frontend/src/i18n/locales/pt-BR.ts (+8 -0)
📝 frontend/src/i18n/locales/zh-CN.ts (+8 -0)

...and 2 more files

📄 Description

Description

This PR combines two improvements to the OIDC / authentication flow:

1 — Azure Entra ID support + hardening (maintainer review fixes)

Adds Azure Entra ID support to the OIDC provider configuration by introducing two new fields: email_claim and require_email_verified.

Azure Entra ID never includes the email_verified claim in its ID tokens. The previous code dropped the email whenever email_verified was absent (fail-closed), breaking Azure logins. This PR introduces three configurable email resolution paths:

Case A (default): email_claim="email" + require_email_verified=true — standard behaviour, unchanged
Case B: email_claim="email" + require_email_verified=false — permissive mode, accepts email when email_verified is absent
Case C: email_claim != "email" — custom claim (preferred_username, upn), no email_verified check; recommended for Azure Entra ID

auto_link_existing_accounts is blocked at schema level, route level, and DB level when either new field would make auto-linking unsafe (SEC-1/SEC-6).

This PR also addresses the subsequent maintainer review feedback:

Regex hardening: _EMAIL_SHAPE_RE is now precompiled at module level and uses fullmatch() with a stricter pattern that requires a dot in the domain (local@domain.tld) — rejects x@nodot, @domain, x@, etc.
PII removed from logs: The raw email value is no longer logged when the shape check fails; only provider_id, email_claim name, and sub are emitted.
PostgreSQL-compatible CheckConstraint: Literals changed from = 0 / = 1 to = FALSE / = TRUE so the constraint compiles correctly on PostgreSQL.
_safe_execute re-raises on real errors: Non-idempotency errors are now logged with logger.error and re-raised so a failed migration aborts startup loudly instead of silently continuing.
_safe_execute swallows PostgreSQL RENAME COLUMN idempotency errors: column "xyz" does not exist (raised by PostgreSQL when re-running an already-applied RENAME COLUMN migration) is now swallowed correctly, fixing a startup failure on existing PostgreSQL installations.
DML backfills moved out of _safe_execute: The three data-backfill statements (UPDATE api_keys, UPDATE users SET password_changed_at, DELETE FROM settings) now use direct conn.execute() inside a savepoint instead of going through _safe_execute. This ensures any column-reference failure is always fatal and never silently swallowed — most importantly for the security-critical password_changed_at backfill.
provider_email lowercasing migration: Normalises existing mixed-case UPN values (common with Azure Entra ID) to lowercase for consistent matching.
Azure Entra ID setup guide: New docs/authentication/entra-id.md covering app registration, client secret, issuer URL, BamBuddy provider config, preferred_username vs email claim, and a troubleshooting table.
New tests: Real in-memory SQLite integration tests for _safe_execute reraise/swallow behaviour, provider_email lowercasing migration, PostgreSQL RENAME COLUMN idempotency, and FALSE/TRUE CheckConstraint enforcement.

"Remember Me" checkbox added to the login form (unchecked by default).
When checked, the JWT is written to both sessionStorage and localStorage, so the session survives a tab close / browser restart.
Introduces TokenPersistence = 'session' | 'persistent' type, replacing the previous persist?: boolean parameter throughout client.ts, AuthContext, and LoginPage.
OIDC round-trip support: The preference is saved to sessionStorage as auth_remember_me=1 before the provider redirect and consumed (read + removed atomically) in the OIDC callback useEffect, so the setting carries through the external redirect where React state is lost.
Persistence also threads through the OIDC + 2FA combined flow.
Logout storage safety: sessionStorage and localStorage writes/removals now happen in independent try/catch blocks so a failure on one storage (e.g. localStorage blocked in private mode) cannot prevent the other from being cleared — a previously possible scenario where a user could believe they were logged out while their localStorage token remained active.
All storage writes are wrapped in try-catch with console.warn to handle private browsing / quota errors gracefully (in-memory token still works for the current tab).
rememberMe i18n key added to all 8 locale files (en, de, fr, it, pt-BR, zh-CN, ja, zh-TW).
Full test coverage: persistence mode tests in client.test.ts; Remember Me checkbox, session/persistent storage, OIDC carry-through, OIDC+2FA carry-through, error cleanup, and negative cases in LoginPage.test.tsx.

Fixes #902 #1088

Documentation

https://github.com/maziggy/bambuddy-wiki/pull/19

Type of Change

Bug fix (non-breaking change that fixes an issue)
New feature (non-breaking change that adds functionality)
Documentation update
Test addition or update

Changes Made

Backend

backend/app/api/routes/mfa.py — precompiled _EMAIL_SHAPE_RE, PII-free log, three-case email resolution (Case A/B/C), _is_valid_email_shaped helper, Combined-State-Guard in PUT handler
backend/app/models/oidc_provider.py — email_claim and require_email_verified ORM fields, CheckConstraint uses FALSE/TRUE
backend/app/core/database.py — column migrations for new fields, PostgreSQL ADD CONSTRAINT migration, _safe_execute logs + re-raises non-idempotency errors + swallows PostgreSQL RENAME COLUMN idempotency errors, DML backfills moved to direct conn.execute(), provider_email lowercasing migration
backend/app/schemas/auth.py — new fields on OIDCProviderCreate / OIDCProviderUpdate / OIDCProviderResponse, model_validator for SEC-1/SEC-6 on create and update
backend/tests/integration/test_mfa_api.py — 19 new tests: 13-case parametrized matrix (Case A/B/C), SEC-1/SEC-6 guard tests, Combined-State-Guard tests, response field coverage, no-dot-in-domain matrix case
backend/tests/unit/test_db_dialect.py — real SQLite integration tests for _safe_execute and migrations, PostgreSQL RENAME COLUMN idempotency mock test
docs/authentication/entra-id.md — new Azure Entra ID setup guide

Frontend

frontend/src/api/client.ts — TokenPersistence type, updated setAuthToken with split try/catch for independent storage failure handling, new fields on OIDCProvider and OIDCProviderCreate interfaces
frontend/src/contexts/AuthContext.tsx — login/loginWithToken accept TokenPersistence
frontend/src/pages/LoginPage.tsx — Remember Me checkbox, consumeSavedRememberMe(), toPersistence() helper, OIDC sessionStorage flag
frontend/src/components/OIDCProviderSettings.tsx — toggle for require_email_verified, input for email_claim, both shown in provider info view
frontend/src/i18n/locales/*.ts — rememberMe key + OIDC settings keys in all 8 locale files
frontend/src/__tests__/api/client.test.ts — 5 new persistence mode tests (including independent storage-failure paths on logout)
frontend/src/__tests__/pages/LoginPage.test.tsx — 9 new Remember Me / OIDC carry-through tests

Post-review fixes (follow-up after revert)

PostgreSQL startup crash — printer_ids JSONB comparison: UPDATE api_keys … WHERE printer_ids = '[]'
failed on PostgreSQL with "operator does not exist: jsonb = unknown" because printer_ids is a JSONB column.
Fixed with a dialect branch ('[]'::jsonb on PostgreSQL, plain '[]' on SQLite).
Extracted into _migrate_normalize_printer_ids() for isolated testability.
_safe_execute compound guard: The "does not exist" swallow was previously too broad —
it could silently suppress real errors from ADD COLUMN or CREATE INDEX statements.
The guard now additionally requires the SQL to be a RENAME COLUMN statement, so only
the intended idempotency case is swallowed.
SEC-1 backfill order: The UPDATE oidc_providers … auto_link … = FALSE backfill now runs
before ADD CONSTRAINT, not after. PostgreSQL validates CHECK constraints against existing
rows on ADD CONSTRAINT; running the backfill first ensures legacy unsafe rows are reset before
the constraint is applied (mirrors maintainer fix 7ebe152a).
Email shape check in Falls A and B: _is_valid_email_shaped() is now applied in all three
email resolution paths (A, B, C), not only in C. Malformed values are rejected before any
email_verified evaluation. Log level raised from INFO to WARNING for all shape-check
rejections.

New tests added:

test_normalize_printer_ids_sqlite_uses_plain_comparison — real SQLite engine, verifies DML
normalises '[]' → NULL
test_normalize_printer_ids_postgres_uses_jsonb_cast — mock verifies ::jsonb cast in SQL
test_auto_link_sec1_backfill_resets_unsafe_rows — SEC-1 backfill resets unsafe rows correctly
test_safe_execute_reraises_does_not_exist_without_column — compound guard re-raises non-RENAME errors
test_oidc_boolean_default_migrations_sqlite_defaults — SQLite BOOLEAN DEFAULT dialect branch
test_safe_execute_column_not_exists_only_swallowed_for_rename — guard restricted to RENAME COLUMN
3 new parametrize cases in test_email_resolution_matrix: fall-a-malformed-email,
fall-b-malformed-email, fall-b-malformed-email-ev-false

Total: 158 backend tests pass (27 unit + 131 integration).

Screenshots

Testing

I have tested this on my local machine

149 backend tests pass (pytest tests/integration/test_mfa_api.py tests/unit/test_db_dialect.py). 1479 frontend tests pass (vitest run). No ruff or TypeScript warnings.

Checklist

My code follows the project's coding style
My changes generate no new warnings
I have tested my changes thoroughly

Additional Notes

Existing OIDC providers and login flows are fully backwards-compatible. Both new features are opt-in by default: require_email_verified=true / email_claim='email' preserves the previous behaviour; the Remember Me checkbox defaults to unchecked.

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/maziggy/bambuddy/pull/1126 **Author:** [@netscout2001](https://github.com/netscout2001) **Created:** 4/25/2026 **Status:** ✅ Merged **Merged:** 4/25/2026 **Merged by:** [@maziggy](https://github.com/maziggy) **Base:** `dev` ← **Head:** `feature/2fa-oidc-authentication` --- ### 📝 Commits (10+) - [`13967e8`](https://github.com/maziggy/bambuddy/commit/13967e83fc22a841c2bdbd8cc113f61b91e6ebc7) feat(oidc): add Azure Entra ID support with configurable email claim resolution - [`9fb0a3d`](https://github.com/maziggy/bambuddy/commit/9fb0a3d05df1ee25cc7290b2be5a9401f20ca494) fix(oidc): harden email claim resolution, guards, and test coverage - [`0dfb5c5`](https://github.com/maziggy/bambuddy/commit/0dfb5c50262557dfffcce537e9980e95fc287fd6) fix(oidc): apply maintainer review fixes for Azure Entra ID support - [`dacf003`](https://github.com/maziggy/bambuddy/commit/dacf00306817f3015ff4a6e91682101c1e1acb5f) feat(auth): add Remember Me option to login page - [`9e3f09d`](https://github.com/maziggy/bambuddy/commit/9e3f09d477c771ee5f4c90a152b9b61d70eb718d) fix(db): swallow 'no such column' in _safe_execute for idempotent RENAME COLUMN migrations - [`1864bef`](https://github.com/maziggy/bambuddy/commit/1864befd2282303ab7ca6741db06dca1d9925155) fix(oidc): harden _safe_execute, extract email/autolink helpers, add test coverage - [`2c32fd7`](https://github.com/maziggy/bambuddy/commit/2c32fd709257d4ba5bb21425ce01f8d56998dc2f) fix(db): restore 'no such column' and 'duplicate column name' to _safe_execute swallow list - [`f99e31b`](https://github.com/maziggy/bambuddy/commit/f99e31bb30d1bff4655fbe5f8a52dcd9d4986fb1) fix(db): swallow PostgreSQL 'does not exist' error for idempotent RENAME COLUMN migrations - [`18ff085`](https://github.com/maziggy/bambuddy/commit/18ff085a4f91613f0a99e000df1cda32b5cb23e1) fix(db,auth): move DML backfills out of _safe_execute; split setAuthToken storage writes - [`3eaa2b5`](https://github.com/maziggy/bambuddy/commit/3eaa2b50c5be796103715c7ffb23c6f5524c9fba) fix(oidc,db): address PR #1118 maintainer review — migration safety, email shape checks, dialect branching ### 📊 Changes **22 files changed** (+2289 additions, -74 deletions) <details> <summary>View changed files</summary> 📝 `backend/app/api/routes/mfa.py` (+136 -18) 📝 `backend/app/core/database.py` (+139 -19) 📝 `backend/app/models/oidc_provider.py` (+24 -1) 📝 `backend/app/schemas/auth.py` (+56 -3) 📝 `backend/tests/integration/test_mfa_api.py` (+777 -0) 📝 `backend/tests/unit/test_db_dialect.py` (+358 -4) ➕ `docs/authentication/entra-id.md` (+82 -0) 📝 `frontend/src/__tests__/api/client.test.ts` (+56 -0) ➕ `frontend/src/__tests__/components/OIDCProviderSettings.test.tsx` (+125 -0) 📝 `frontend/src/__tests__/pages/LoginPage.test.tsx` (+328 -1) 📝 `frontend/src/api/client.ts` (+25 -11) 📝 `frontend/src/components/OIDCProviderSettings.tsx` (+50 -1) 📝 `frontend/src/contexts/AuthContext.tsx` (+9 -9) 📝 `frontend/src/i18n/locales/de.ts` (+8 -0) 📝 `frontend/src/i18n/locales/en.ts` (+8 -0) 📝 `frontend/src/i18n/locales/fr.ts` (+8 -0) 📝 `frontend/src/i18n/locales/it.ts` (+8 -0) 📝 `frontend/src/i18n/locales/ja.ts` (+8 -0) 📝 `frontend/src/i18n/locales/pt-BR.ts` (+8 -0) 📝 `frontend/src/i18n/locales/zh-CN.ts` (+8 -0) _...and 2 more files_ </details> ### 📄 Description ## Description This PR combines two improvements to the OIDC / authentication flow: ### 1 — Azure Entra ID support + hardening (maintainer review fixes) Adds Azure Entra ID support to the OIDC provider configuration by introducing two new fields: `email_claim` and `require_email_verified`. Azure Entra ID never includes the `email_verified` claim in its ID tokens. The previous code dropped the email whenever `email_verified` was absent (fail-closed), breaking Azure logins. This PR introduces three configurable email resolution paths: - **Case A** *(default)*: `email_claim="email"` + `require_email_verified=true` — standard behaviour, unchanged - **Case B**: `email_claim="email"` + `require_email_verified=false` — permissive mode, accepts email when `email_verified` is absent - **Case C**: `email_claim != "email"` — custom claim (`preferred_username`, `upn`), no `email_verified` check; recommended for Azure Entra ID `auto_link_existing_accounts` is blocked at schema level, route level, and DB level when either new field would make auto-linking unsafe (SEC-1/SEC-6). This PR also addresses the subsequent maintainer review feedback: - **Regex hardening**: `_EMAIL_SHAPE_RE` is now precompiled at module level and uses `fullmatch()` with a stricter pattern that requires a dot in the domain (`local@domain.tld`) — rejects `x@nodot`, `@domain`, `x@`, etc. - **PII removed from logs**: The raw email value is no longer logged when the shape check fails; only `provider_id`, `email_claim` name, and `sub` are emitted. - **PostgreSQL-compatible CheckConstraint**: Literals changed from `= 0` / `= 1` to `= FALSE` / `= TRUE` so the constraint compiles correctly on PostgreSQL. - **`_safe_execute` re-raises on real errors**: Non-idempotency errors are now logged with `logger.error` and re-raised so a failed migration aborts startup loudly instead of silently continuing. - **`_safe_execute` swallows PostgreSQL RENAME COLUMN idempotency errors**: `column "xyz" does not exist` (raised by PostgreSQL when re-running an already-applied `RENAME COLUMN` migration) is now swallowed correctly, fixing a startup failure on existing PostgreSQL installations. - **DML backfills moved out of `_safe_execute`**: The three data-backfill statements (`UPDATE api_keys`, `UPDATE users SET password_changed_at`, `DELETE FROM settings`) now use direct `conn.execute()` inside a savepoint instead of going through `_safe_execute`. This ensures any column-reference failure is always fatal and never silently swallowed — most importantly for the security-critical `password_changed_at` backfill. - **`provider_email` lowercasing migration**: Normalises existing mixed-case UPN values (common with Azure Entra ID) to lowercase for consistent matching. - **Azure Entra ID setup guide**: New `docs/authentication/entra-id.md` covering app registration, client secret, issuer URL, BamBuddy provider config, `preferred_username` vs `email` claim, and a troubleshooting table. - **New tests**: Real in-memory SQLite integration tests for `_safe_execute` reraise/swallow behaviour, `provider_email` lowercasing migration, PostgreSQL RENAME COLUMN idempotency, and `FALSE`/`TRUE` CheckConstraint enforcement. ### 2 — Remember Me persistent login - **"Remember Me" checkbox** added to the login form (unchecked by default). - When checked, the JWT is written to both `sessionStorage` **and** `localStorage`, so the session survives a tab close / browser restart. - Introduces `TokenPersistence = 'session' | 'persistent'` type, replacing the previous `persist?: boolean` parameter throughout `client.ts`, `AuthContext`, and `LoginPage`. - **OIDC round-trip support**: The preference is saved to `sessionStorage` as `auth_remember_me=1` before the provider redirect and consumed (read + removed atomically) in the OIDC callback `useEffect`, so the setting carries through the external redirect where React state is lost. - Persistence also threads through the **OIDC + 2FA** combined flow. - **Logout storage safety**: `sessionStorage` and `localStorage` writes/removals now happen in independent `try/catch` blocks so a failure on one storage (e.g. `localStorage` blocked in private mode) cannot prevent the other from being cleared — a previously possible scenario where a user could believe they were logged out while their `localStorage` token remained active. - All storage writes are wrapped in `try-catch` with `console.warn` to handle private browsing / quota errors gracefully (in-memory token still works for the current tab). - `rememberMe` i18n key added to all 8 locale files (en, de, fr, it, pt-BR, zh-CN, ja, zh-TW). - Full test coverage: persistence mode tests in `client.test.ts`; Remember Me checkbox, session/persistent storage, OIDC carry-through, OIDC+2FA carry-through, error cleanup, and negative cases in `LoginPage.test.tsx`. ## Related Issues Fixes #902 #1088 ## Documentation [https://github.com/maziggy/bambuddy-wiki/pull/19](https://github.com/maziggy/bambuddy-wiki/pull/19) ## Type of Change - [x] Bug fix (non-breaking change that fixes an issue) - [x] New feature (non-breaking change that adds functionality) - [x] Documentation update - [x] Test addition or update ## Changes Made **Backend** - `backend/app/api/routes/mfa.py` — precompiled `_EMAIL_SHAPE_RE`, PII-free log, three-case email resolution (Case A/B/C), `_is_valid_email_shaped` helper, Combined-State-Guard in PUT handler - `backend/app/models/oidc_provider.py` — `email_claim` and `require_email_verified` ORM fields, `CheckConstraint` uses `FALSE`/`TRUE` - `backend/app/core/database.py` — column migrations for new fields, PostgreSQL `ADD CONSTRAINT` migration, `_safe_execute` logs + re-raises non-idempotency errors + swallows PostgreSQL RENAME COLUMN idempotency errors, DML backfills moved to direct `conn.execute()`, `provider_email` lowercasing migration - `backend/app/schemas/auth.py` — new fields on `OIDCProviderCreate` / `OIDCProviderUpdate` / `OIDCProviderResponse`, `model_validator` for SEC-1/SEC-6 on create and update - `backend/tests/integration/test_mfa_api.py` — 19 new tests: 13-case parametrized matrix (Case A/B/C), SEC-1/SEC-6 guard tests, Combined-State-Guard tests, response field coverage, `no-dot-in-domain` matrix case - `backend/tests/unit/test_db_dialect.py` — real SQLite integration tests for `_safe_execute` and migrations, PostgreSQL RENAME COLUMN idempotency mock test - `docs/authentication/entra-id.md` — new Azure Entra ID setup guide **Frontend** - `frontend/src/api/client.ts` — `TokenPersistence` type, updated `setAuthToken` with split try/catch for independent storage failure handling, new fields on `OIDCProvider` and `OIDCProviderCreate` interfaces - `frontend/src/contexts/AuthContext.tsx` — `login`/`loginWithToken` accept `TokenPersistence` - `frontend/src/pages/LoginPage.tsx` — Remember Me checkbox, `consumeSavedRememberMe()`, `toPersistence()` helper, OIDC sessionStorage flag - `frontend/src/components/OIDCProviderSettings.tsx` — toggle for `require_email_verified`, input for `email_claim`, both shown in provider info view - `frontend/src/i18n/locales/*.ts` — `rememberMe` key + OIDC settings keys in all 8 locale files - `frontend/src/__tests__/api/client.test.ts` — 5 new persistence mode tests (including independent storage-failure paths on logout) - `frontend/src/__tests__/pages/LoginPage.test.tsx` — 9 new Remember Me / OIDC carry-through tests ### Post-review fixes (follow-up after revert) - **PostgreSQL startup crash — `printer_ids` JSONB comparison**: `UPDATE api_keys … WHERE printer_ids = '[]'` failed on PostgreSQL with *"operator does not exist: jsonb = unknown"* because `printer_ids` is a JSONB column. Fixed with a dialect branch (`'[]'::jsonb` on PostgreSQL, plain `'[]'` on SQLite). Extracted into `_migrate_normalize_printer_ids()` for isolated testability. - **`_safe_execute` compound guard**: The `"does not exist"` swallow was previously too broad — it could silently suppress real errors from `ADD COLUMN` or `CREATE INDEX` statements. The guard now additionally requires the SQL to be a `RENAME COLUMN` statement, so only the intended idempotency case is swallowed. - **SEC-1 backfill order**: The `UPDATE oidc_providers … auto_link … = FALSE` backfill now runs **before** `ADD CONSTRAINT`, not after. PostgreSQL validates `CHECK` constraints against existing rows on `ADD CONSTRAINT`; running the backfill first ensures legacy unsafe rows are reset before the constraint is applied (mirrors maintainer fix `7ebe152a`). - **Email shape check in Falls A and B**: `_is_valid_email_shaped()` is now applied in all three email resolution paths (A, B, C), not only in C. Malformed values are rejected before any `email_verified` evaluation. Log level raised from `INFO` to `WARNING` for all shape-check rejections. **New tests added:** - `test_normalize_printer_ids_sqlite_uses_plain_comparison` — real SQLite engine, verifies DML normalises `'[]'` → `NULL` - `test_normalize_printer_ids_postgres_uses_jsonb_cast` — mock verifies `::jsonb` cast in SQL - `test_auto_link_sec1_backfill_resets_unsafe_rows` — SEC-1 backfill resets unsafe rows correctly - `test_safe_execute_reraises_does_not_exist_without_column` — compound guard re-raises non-RENAME errors - `test_oidc_boolean_default_migrations_sqlite_defaults` — SQLite BOOLEAN DEFAULT dialect branch - `test_safe_execute_column_not_exists_only_swallowed_for_rename` — guard restricted to RENAME COLUMN - 3 new parametrize cases in `test_email_resolution_matrix`: `fall-a-malformed-email`, `fall-b-malformed-email`, `fall-b-malformed-email-ev-false` Total: **158 backend tests pass** (27 unit + 131 integration). ## Screenshots <img width="1120" height="880" alt="image" src="https://github.com/user-attachments/assets/7aa12bfb-6ed7-4d2c-b803-06da6f01b217" /> <img width="460" height="716" alt="image" src="https://github.com/user-attachments/assets/68437d8f-887f-4841-9860-a69b7c2fcec1" /> ## Testing - [x] I have tested this on my local machine 149 backend tests pass (`pytest tests/integration/test_mfa_api.py tests/unit/test_db_dialect.py`). 1479 frontend tests pass (`vitest run`). No ruff or TypeScript warnings. ## Checklist - [x] My code follows the project's coding style - [x] My changes generate no new warnings - [x] I have tested my changes thoroughly ## Additional Notes Existing OIDC providers and login flows are fully backwards-compatible. Both new features are opt-in by default: `require_email_verified=true` / `email_claim='email'` preserves the previous behaviour; the Remember Me checkbox defaults to unchecked. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>