Add OIDC sign-in (Google, Microsoft)

[martsokha]

Context

The cloud deployment needs Google and Microsoft sign-in. Both are OIDC, so one
implementation covers them — and most other identity providers — with only the
issuer URL, client credentials, and scopes differing per provider.

This belongs here rather than downstream. accounts, the authentication
handlers, and the session keys all live in this repository, so a downstream
implementation would need its own identity table and its own way to mint
sessions: a second auth system beside a working one, with two sources of truth
for identity.

docs/ already lists "SSO (SAML/OIDC), SCIM provisioning" as a capability, so
this may already be planned. Worth confirming before either side starts.

The blocker

accounts.password_hash is NOT NULL with a non-empty check:

password_hash         TEXT        NOT NULL,
CONSTRAINT accounts_password_hash_not_empty CHECK (password_hash <> ''),

An SSO-only account has no password. The options are to store a dummy hash — a
fake credential in a column that means "this user can sign in with a password",
which lies about the security model — or to relax the constraint. Only the
second is honest, and it is a schema change this repository owns.

Suggested shape

Schema. Make password_hash nullable, with a check that an account has
either a password or a linked identity. Add an identity table:

CREATE TABLE account_identities (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts (id) ON DELETE CASCADE,
    provider            IDENTITY_PROVIDER NOT NULL,
    -- The provider's stable subject claim, not the email: an address can be
    -- reassigned within a directory, `sub` cannot.
    provider_subject    TEXT NOT NULL,
    ...
);

CREATE UNIQUE INDEX account_identities_provider_subject_unique_idx
    ON account_identities (provider, provider_subject);

Handlers. /auth/{provider}/start and /auth/{provider}/callback, issuing
the same AuthToken the password login already returns, so everything
downstream sees one session model.

Config. Client ID and secret per provider, behind the existing cli
feature like the rest of ServiceArgs.

Notes

OIDC, not bare OAuth. The ID token's aud claim is what binds a token to
this client; validating an access token instead accepts one issued to a
different application. openidconnect
handles discovery and token validation rather than hand-rolling the JWT checks.

Self-hosted wants this too. A self-hosted deployment is usually one
organization that already runs Google Workspace or Entra, and central control
over access and deprovisioning is a common procurement requirement in the
regulated markets this targets. Single-IdP configuration covers that case.

Multi-tenancy is the cloud-only part. Many organizations, each with its own
provider, needs per-workspace configuration and domain-based routing, plus an
answer for a user belonging to two customers. That layer can live downstream on
top of this.

Microsoft tenancy. Single-tenant versus common changes who can sign in;
worth deciding deliberately rather than defaulting.

SAML is out of scope here. Some enterprise buyers still require it, but OIDC
covers Google, Microsoft, Okta, and Auth0, and is the smaller first step.

🤖 Generated with Claude Code

https://claude.ai/code/session_01RFDC1sxnDWHEqiLZGZuLJr