Identity table and membership sign-in foundation

## Epic ask (verbatim) > "Allow user to be a member of several orgs, including Operator. At any given moment user acts on behalf of the currently selected org, no permissions mix or leak. User can switch between orgs by clicking on his icon and selecting a switch icon next to the org. Same model as in Stripe and other multitenant systems" ## Requirements this child delivers (PRD, verbatim) - **R1 — Many memberships per identity (A1).** One person = one identity: email, name, password, sign-in methods, MFA, Slack link and display preferences. That identity holds memberships in one or more orgs. Every org-owned thing stays in its org and is visible only while acting as that org. The Operator org is a valid membership. A user may hold Operator plus any number of tenant orgs. (Architecture amendment Q-N: the Slack link and ALL notification settings stay per org, on the membership row.) - **R4 (sign-in half).** "The last active org is remembered per user, and the next sign-in opens it. If that membership no longer exists or the org is disabled, the session falls back to another active membership." - **R7 (sign-in half).** "Removing a user's LAST membership deactivates the account: they cannot sign in." - **R11 — Migration.** "Every existing user becomes a member of exactly the org they belong to today, with the same role bindings and the same `is_superadmin` effect. Superadmin power is honoured only while acting as Operator (R2). Nobody gains or loses access on the day of deploy." ## Approach (architecture §1, §2.1, §2.2, §2.3a, §2.4, §2.6, §3.0, §3.1, §3.5, §5.1 — read /tmp/cockpit-task-inputs/task-5065/architecture.md if present; the essentials are here) An `admin_users` row becomes ONE membership of ONE person in ONE org; a new platform-plane `identities` table holds the person. Do NOT introduce a global-user + memberships table (rejected: the tenancy manifest's composite keys `(user_id, org_id) → admin_users(id, org_id)` must stay byte-identical). 1. **`identities` table** (uuidv7 pk; `email` UNIQUE NOT NULL; `email_verified_at`; `password_hash`; `google_sub` UNIQUE, `github_user_id` UNIQUE, `microsoft_tenant_id`+`microsoft_object_id` pair-unique with both-or-neither check; `first_name`, `last_name`; `preferences` jsonb display keys; `session_epoch` int 0; `last_active_admin_id` FK→admin_users ON DELETE SET NULL; standard entity shape). Read only under `seam.identity_scope()`; add to the tenancy manifest's non-tenant classification, the bypass census (`apps/api/test_tenancy_bypass_sites.py`) with derivation prose, and `last_active_admin_id` to the composite-keys gap register (`test_tenancy_composite_keys.py`). 2. **Revision-1 migration (additive only):** create `identities`; backfill ONE identity per existing `admin_users` row (1:1, `identities.session_epoch = 0`); add nullable `admin_users.identity_id` FK ON DELETE RESTRICT + `UNIQUE(identity_id, org_id)`; a `BEFORE INSERT` trigger find-or-creates the identity from the legacy columns (rolling-deploy safety); drop global `UNIQUE(admin_users.email)`. Legacy columns KEPT and dual-written. The system actor gets its own (non-loginable) identity. CONCURRENTLY index builds per `tenancy/contraction.py` conventions. 3. **Model:** `AdminUser.email/.first_name/.last_name` become read-only `hybrid_property`s via the identity (class level: correlated scalar subquery); setters RAISE; legacy physical columns remapped to `_legacy_*` attrs dual-written by one identity-setter helper. 4. **Email-lookup audit:** rewrite every `AdminUser.email ==` lookup (`password_reset.py:92`, `email_signup.py:162`, `signup.py:221`, `login_oauth.py:292,342`, `notify_targets`, `routes/users.py`, `routes/auth.py`, `routes/access.py`, `routes/groups.py`, `routes/projects.py`, `billing/notices.py`, `seed*.py`, …) to identity → `pick_membership` (sign-in doors) or an explicit `org_id` filter. Add a census test failing any `AdminUser.email ==` comparison not also constrained by `org_id` (allowlist with reasons). 5. **Two-epoch liveness (§3.0):** `auth.session_row_is_live` = row exists, `disabled_at IS NULL`, cookie `epoch == row.session_epoch` AND cookie `iepoch == identity.session_epoch` (missing `iepoch` reads 0 so every pre-deploy cookie survives). Disable/enable of one membership bumps only the row epoch; sign-out and password change bump only the identity epoch. `iepoch` is a REQUIRED keyword of `auth.set_session_cookie` (`auth.py:293`); thread it through every caller — `complete_first_factor`, `_mint_without_code`, `complete_second_factor`, `reissue_session` (`login_funnel.py:102,158,174,261`). 6. **`login_funnel.pick_membership(session, identity, door)` (§3.1):** order `identity.last_active_admin_id` first, then other rows by `last_seen_at desc`; eligible = `disabled_at IS NULL`, org passes `org_gate.sign_in_refusal`, every `POLICY_HOOKS` entry (incl. `sso_policy`, superadmin-no-password) admits the door. First eligible → `complete_first_factor` unchanged. None eligible → the first candidate's refusal (single-membership users see exactly today's refusal); zero live rows → refused `disabled` (derived, no new flag). Write `identity.last_active_admin_id` on every mint. All four doors (`routes/auth.py::login`, Google, `login_oauth.py` GitHub/Microsoft) resolve email/IdP subject → identity → `pick_membership`. Failure/lockout records before a pick use `UNATTRIBUTED_SIGN_IN_ORG_ID` (`tenancy/ids.py:35`). 7. **`GET /api/auth/me`** (`AdminOut`, `routes/auth.py:175`) gains exactly: `memberships: [ { org_id, org_slug, org_name, state: "active" | "pending_approval" | "disabled", is_current: bool, is_operator_plane: bool } ]` ordered current first then name, and `pending_invitations: []` (a later child fills it). `org`, `is_operator`, `org_role` keep meaning the active membership. Only the caller's own identity's rows. 8. **Preferences split (§2.4):** one tuple `IDENTITY_PREFERENCE_KEYS` in `app/preferences.py` = (`theme`, `wide_mode`, `nav_collapsed_groups`) stored on `identities.preferences`; everything else (incl. `notifications`, rail pins, onboarding flags) stays on `admin_users.preferences`. GET/PATCH response shape unchanged. 9. Pin the invariant: a row created by invitation-accept or self-create is never `is_superadmin` (helper later children use). `Principal.is_operator` (`authz.py:785-791`) unchanged. Land it as ORDERED commits (migration+backfill → model/hybrids → email audit → epochs/iepoch → pick_membership + doors → me + preferences) so review can follow it. Security surface: auth/authz + data migration — this child is under the security-surface gate (branching-policy; apply the repo's ADR-0028 carve-out only as your build rules allow). Out of scope: Do NOT build the switch endpoint, `X-Jaah-Org` guard or SSE close (separate child); do NOT re-key MFA/recovery/trusted-device/password-reset/legal-acceptance tables (separate child); do NOT create `org_invitations` or fill `pending_invitations`; no web UI. Revision-2 contraction (dropping legacy columns, NOT NULL on `identity_id`, dropping the trigger) is a deferred follow-up — do NOT do it. Epic out-of-scope (PRD §5, verbatim): "No per-tab simultaneous orgs. Org is per session. Do NOT put the org in the URL path as an addressing scheme (a deep-link interstitial is allowed)." "No audit event for a switch (operator decision Q8). Do not add one." "No self-service 'leave org'." "No operator 'view as / impersonate' of an org the operator is not a member of." "No change to ADR-0118 admission gates or the approval flow, and no change to the node/org immutability or the placement org floor." "Do not weaken the read/write seams (`tenancy/seam.py`, `tenancy/write_seam.py`): one unit of work = one org stays the rule. The active org REPLACES `user.org_id` as the bound scope; it never widens it." "No change to billing/plans/seat pricing semantics." "No change to email-domain auto-join beyond what is noted in §6 D-B." "No mobile-native screens." "No redesign of the account menu's existing items." Done means: - AC15 — "After migration, every pre-existing user has exactly one membership equal to their old `org_id`, with identical effective permissions." — migration test comparing the Principal (org_id, permissions, org_keys, is_operator) before and after for a seeded set: operator superadmin, org owner, project-role user, zero-grant user; all identical. - A pre-deploy cookie (no `iepoch`) stays live after the migration — API test. - R4 sign-in half — API test with two fixture membership rows: next sign-in lands in `last_active_admin_id`'s org; when that row is deleted or its org disabled, sign-in lands in the other live membership. - AC11 sign-in side — deleting a user's last live membership row makes sign-in refuse `disabled`; with another membership left, sign-in succeeds into it — API test. - Disabling one membership does not end the session of the person's other membership; sign-out ends all — API test. - `GET /api/auth/me` returns `memberships` (exact shape above) with one `is_current` entry and `pending_invitations: []` — API test. - Email-lookup census test passes and fails on an injected unconstrained `AdminUser.email ==`. - Setting `AdminUser.email` directly raises — unit test. - AC17 — `./dc check full` green, including the tenancy bypass/scope censuses and the authz policy suite. Required checks: gate

Built the identities table (rev 0360, 1:1 backfill), person-field hybrids, the email census, two-epoch sessions and pick_membership on all 4 doors, plus /me memberships. Full gate green; review findings fixed; merged via #4500 with operator approval. Deferred: rev-2 contraction, trusted devices→identity (C2).