tests(e2e): fix missing data in database.json

Co-authored-by: james <james@goldfish.net>
Co-authored-by: Kyle Mendell <kmendell@ofkm.us>

backend/go.mod

@@ -66,8 +66,8 @@ require (
 require (
 	filippo.io/edwards25519 v1.2.0 // indirect
 	github.com/Azure/go-ntlmssp v0.1.1 // indirect
-	github.com/ClickHouse/ch-go v0.61.5 // indirect
-	github.com/ClickHouse/clickhouse-go/v2 v2.30.0 // indirect
+	github.com/ClickHouse/ch-go v0.65.0 // indirect
+	github.com/ClickHouse/clickhouse-go/v2 v2.32.0 // indirect
 	github.com/alphadose/haxmap v1.4.1 // indirect
 	github.com/andybalholm/brotli v1.1.1 // indirect
 	github.com/asaskevich/govalidator v0.0.0-20230301143203-a9d515a09cc2 // indirect
@@ -136,7 +136,7 @@ require (
 	github.com/h2non/filetype v1.1.3 // indirect
 	github.com/hashicorp/go-cleanhttp v0.5.2 // indirect
 	github.com/hashicorp/go-retryablehttp v0.7.8 // indirect
-	github.com/hashicorp/go-version v1.6.0 // indirect
+	github.com/hashicorp/go-version v1.7.0 // indirect
 	github.com/inconshreveable/mousetrap v1.1.0 // indirect
 	github.com/jackc/pgerrcode v0.0.0-20250907135507-afb5586c32a6 // indirect
 	github.com/jackc/pgpassfile v1.0.0 // indirect
@@ -172,7 +172,7 @@ require (
 	github.com/paulmach/orb v0.11.1 // indirect
 	github.com/pelletier/go-toml/v2 v2.3.1 // indirect
 	github.com/philhofer/fwd v1.2.0 // indirect
-	github.com/pierrec/lz4/v4 v4.1.21 // indirect
+	github.com/pierrec/lz4/v4 v4.1.22 // indirect
 	github.com/pkg/errors v0.9.1 // indirect
 	github.com/pmezard/go-difflib v1.0.1-0.20181226105442-5d4384ee4fb2 // indirect
 	github.com/prometheus/client_golang v1.23.2 // indirect

backend/go.sum

@@ -4,10 +4,10 @@ github.com/Azure/go-ansiterm v0.0.0-20250102033503-faa5f7b0171c h1:udKWzYgxTojEK
 github.com/Azure/go-ansiterm v0.0.0-20250102033503-faa5f7b0171c/go.mod h1:xomTg63KZ2rFqZQzSB4Vz2SUXa1BpHTVz9L5PTmPC4E=
 github.com/Azure/go-ntlmssp v0.1.1 h1:l+FM/EEMb0U9QZE7mKNEDw5Mu3mFiaa2GKOoTSsNDPw=
 github.com/Azure/go-ntlmssp v0.1.1/go.mod h1:NYqdhxd/8aAct/s4qSYZEerdPuH1liG2/X9DiVTbhpk=
-github.com/ClickHouse/ch-go v0.61.5 h1:zwR8QbYI0tsMiEcze/uIMK+Tz1D3XZXLdNrlaOpeEI4=
-github.com/ClickHouse/ch-go v0.61.5/go.mod h1:s1LJW/F/LcFs5HJnuogFMta50kKDO0lf9zzfrbl0RQg=
-github.com/ClickHouse/clickhouse-go/v2 v2.30.0 h1:AG4D/hW39qa58+JHQIFOSnxyL46H6h2lrmGGk17dhFo=
-github.com/ClickHouse/clickhouse-go/v2 v2.30.0/go.mod h1:i9ZQAojcayW3RsdCb3YR+n+wC2h65eJsZCscZ1Z1wyo=
+github.com/ClickHouse/ch-go v0.65.0 h1:vZAXfTQliuNNefqkPDewX3kgRxN6Q4vUENnnY+ynTRY=
+github.com/ClickHouse/ch-go v0.65.0/go.mod h1:tCM0XEH5oWngoi9Iu/8+tjPBo04I/FxNIffpdjtwx3k=
+github.com/ClickHouse/clickhouse-go/v2 v2.32.0 h1:zVWJUmUGdtCApM/vRfQhruGXIm1M643bk68B3IYbR1I=
+github.com/ClickHouse/clickhouse-go/v2 v2.32.0/go.mod h1:rGFIgeNbJVggBp2C+0FXOdfjsMlpsKx7FUYnHHyy2KE=
 github.com/Microsoft/go-winio v0.6.2 h1:F2VQgta7ecxGYO8k3ZZz3RS8fVIXVxONVUPlNERoyfY=
 github.com/Microsoft/go-winio v0.6.2/go.mod h1:yd8OoFMLzJbo9gZq8j5qaps8bJ9aShtEA8Ipt1oGCvU=
 github.com/alexbrainman/sspi v0.0.0-20250919150558-7d374ff0d59e h1:4dAU9FXIyQktpoUAgOJK3OTFc/xug0PCXYCqU0FgDKI=
@@ -257,8 +257,8 @@ github.com/hashicorp/go-retryablehttp v0.7.8 h1:ylXZWnqa7Lhqpk0L1P1LzDtGcCR0rPVU
 github.com/hashicorp/go-retryablehttp v0.7.8/go.mod h1:rjiScheydd+CxvumBsIrFKlx3iS0jrZ7LvzFGFmuKbw=
 github.com/hashicorp/go-uuid v1.0.3 h1:2gKiV6YVmrJ1i2CKKa9obLvRieoRGviZFL26PcT/Co8=
 github.com/hashicorp/go-uuid v1.0.3/go.mod h1:6SBZvOh/SIDV7/2o3Jml5SYk/TvGqwFJ/bN7x4byOro=
-github.com/hashicorp/go-version v1.6.0 h1:feTTfFNnjP967rlCxM/I9g701jU+RN74YKx2mOkIeek=
-github.com/hashicorp/go-version v1.6.0/go.mod h1:fltr4n8CU8Ke44wwGCBoEymUuxUHl09ZGVZPK5anwXA=
+github.com/hashicorp/go-version v1.7.0 h1:5tqGy27NaOTB8yJKUZELlFAS/LTKJkrmONwQKeRZfjY=
+github.com/hashicorp/go-version v1.7.0/go.mod h1:fltr4n8CU8Ke44wwGCBoEymUuxUHl09ZGVZPK5anwXA=
 github.com/hashicorp/golang-lru/v2 v2.0.7 h1:a+bsQ5rvGLjzHuww6tVxozPZFVghXaHOwFs4luLUK2k=
 github.com/hashicorp/golang-lru/v2 v2.0.7/go.mod h1:QeFd9opnmA6QUJc5vARoKUSoFhyfM2/ZepoAG6RGpeM=
 github.com/inconshreveable/mousetrap v1.1.0 h1:wN+x4NVGpMsO7ErUn/mUI3vEoE6Jt13X2s0bqwp9tc8=
@@ -419,8 +419,8 @@ github.com/pelletier/go-toml/v2 v2.3.1 h1:MYEvvGnQjeNkRF1qUuGolNtNExTDwct51yp7ol
 github.com/pelletier/go-toml/v2 v2.3.1/go.mod h1:2gIqNv+qfxSVS7cM2xJQKtLSTLUE9V8t9Stt+h56mCY=
 github.com/philhofer/fwd v1.2.0 h1:e6DnBTl7vGY+Gz322/ASL4Gyp1FspeMvx1RNDoToZuM=
 github.com/philhofer/fwd v1.2.0/go.mod h1:RqIHx9QI14HlwKwm98g9Re5prTQ6LdeRQn+gXJFxsJM=
-github.com/pierrec/lz4/v4 v4.1.21 h1:yOVMLb6qSIDP67pl/5F7RepeKYu/VmTyEXvuMI5d9mQ=
-github.com/pierrec/lz4/v4 v4.1.21/go.mod h1:gZWDp/Ze/IJXGXf23ltt2EXimqmTUXEy0GFuRQyBid4=
+github.com/pierrec/lz4/v4 v4.1.22 h1:cKFw6uJDK+/gfw5BcDL0JL5aBsAFdsIT18eRtLj7VIU=
+github.com/pierrec/lz4/v4 v4.1.22/go.mod h1:gZWDp/Ze/IJXGXf23ltt2EXimqmTUXEy0GFuRQyBid4=
 github.com/pires/go-proxyproto v0.15.0 h1:dTshmNbFm/D+0+sbrxUuddPOZ5Y0B7c5NhtsBkm6LqI=
 github.com/pires/go-proxyproto v0.15.0/go.mod h1:OXsCrKwrK2tXS9YrI5tkHx5xaQlO8FH3lFW76orFh24=
 github.com/pkg/errors v0.9.1 h1:FEBLx1zS214owpjy7qsBeixbURkuhQAwrK5UwLGTwt4=

backend/internal/service/e2etest_service.go

@@ -220,6 +220,9 @@ func (s *TestService) SeedDatabase(baseURL string) error {
 				LogoutCallbackURLs: model.UrlList{"http://tailscale.localhost/auth/logout/callback"},
 				IsGroupRestricted:  true,
 				CreatedByID:        new(users[0].ID),
+				AllowedUserGroups: []model.UserGroup{
+					userGroups[0],
+				},
 			},
 			{
 				Base: model.Base{
@@ -282,6 +285,61 @@ func (s *TestService) SeedDatabase(baseURL string) error {
 			}
 		}
 
+		farFuture := datatype.DateTime(time.Date(2099, 1, 1, 0, 0, 0, 0, time.UTC))
+		oauth2Session := oidc.OAuth2Session{
+			Base: model.Base{
+				ID: "551ab785-c830-47d3-8a07-60c9f3bb4859",
+			},
+			Kind:                 "access_token",
+			Key:                  "cross-database-test-session",
+			RequestID:            "cross-database-test-request",
+			AccessTokenSignature: "",
+			Active:               true,
+			RequestData:          `{"request":"value"}`,
+			ExpiresAt:            &farFuture,
+		}
+		if err := tx.Create(&oauth2Session).Error; err != nil {
+			return err
+		}
+
+		if err := tx.Table("oauth2_jtis").Create(map[string]any{
+			"id":         "bd0c8bf2-66ec-487a-9dd5-7d9d78d73543",
+			"created_at": datatype.DateTime(time.Now()),
+			"jti":        "cross-database-test-jti",
+			"expires_at": farFuture,
+		}).Error; err != nil {
+			return err
+		}
+
+		interactionSession := oidc.InteractionSession{
+			Base: model.Base{
+				ID: "aaf5dd23-cd1f-4748-a2aa-baa6af94d800",
+			},
+			Scopes:          datatype.StringList{"openid"},
+			ClientID:        oidcClients[0].ID,
+			UserID:          new(users[0].ID),
+			ConsentRequired: true,
+			RequestedAt:     farFuture,
+			Parameters: oidc.InteractionSessionParameters{
+				"client_id": oidcClients[0].ID,
+			},
+		}
+		if err := tx.Create(&interactionSession).Error; err != nil {
+			return err
+		}
+
+		reauthenticationToken := webauthn.ReauthenticationToken{
+			Base: model.Base{
+				ID: "71839ace-d978-4e6f-8fb1-b8648a21031b",
+			},
+			Token:     "cross-database-reauthentication-token",
+			ExpiresAt: farFuture,
+			UserID:    users[0].ID,
+		}
+		if err := tx.Create(&reauthenticationToken).Error; err != nil {
+			return err
+		}
+
 		accessToken := model.OneTimeAccessToken{
 			Token:     "one-time-token",
 			ExpiresAt: datatype.DateTime(time.Now().Add(1 * time.Hour)),

backend/internal/service/oidc_service.go

@@ -587,23 +587,11 @@ func (s *OidcService) ListAccessibleOidcClients(ctx context.Context, userID stri
 	query := tx.
 		WithContext(ctx).
 		Model(&model.OidcClient{}).
-		Preload("UserAuthorizedOidcClients", "user_id = ?", userID)
-
-	// If user has no groups, only return clients with no allowed user groups
-	if len(userGroupIDs) == 0 {
-		query = query.Where(`NOT EXISTS (
-        SELECT 1 FROM oidc_clients_allowed_user_groups
-        WHERE oidc_clients_allowed_user_groups.oidc_client_id = oidc_clients.id)`)
-	} else {
-		query = query.Where(`
-        NOT EXISTS (
-            SELECT 1 FROM oidc_clients_allowed_user_groups
-            WHERE oidc_clients_allowed_user_groups.oidc_client_id = oidc_clients.id
-        ) OR EXISTS (
-            SELECT 1 FROM oidc_clients_allowed_user_groups
-            WHERE oidc_clients_allowed_user_groups.oidc_client_id = oidc_clients.id
-            AND oidc_clients_allowed_user_groups.user_group_id IN (?))`, userGroupIDs)
-	}
+		Preload("UserAuthorizedOidcClients", "user_id = ?", userID).
+		Where(`oidc_clients.is_group_restricted = ? OR EXISTS (
+			SELECT 1 FROM oidc_clients_allowed_user_groups
+			WHERE oidc_clients_allowed_user_groups.oidc_client_id = oidc_clients.id
+			AND oidc_clients_allowed_user_groups.user_group_id IN (?))`, false, userGroupIDs)
 
 	var clients []model.OidcClient
 

backend/internal/service/oidc_service_test.go

@@ -14,6 +14,7 @@ import (
 	"github.com/pocket-id/pocket-id/backend/internal/dto"
 	"github.com/pocket-id/pocket-id/backend/internal/model"
 	"github.com/pocket-id/pocket-id/backend/internal/storage"
+	"github.com/pocket-id/pocket-id/backend/internal/utils"
 	testutils "github.com/pocket-id/pocket-id/backend/internal/utils/testing"
 )
 
@@ -538,3 +539,45 @@ func TestOidcService_UpdateClient_description(t *testing.T) {
 	require.NoError(t, err)
 	assert.Empty(t, fetched.Description)
 }
+
+func TestOidcService_ListAccessibleOidcClients_requiresExplicitGroupPermission(t *testing.T) {
+	db := testutils.NewDatabaseForTest(t)
+	s, err := NewOidcService(db, nil, nil, nil, nil, nil)
+	require.NoError(t, err)
+
+	allowedGroup := model.UserGroup{Name: "allowed", FriendlyName: "Allowed"}
+	otherGroup := model.UserGroup{Name: "other", FriendlyName: "Other"}
+	require.NoError(t, db.Create(&allowedGroup).Error)
+	require.NoError(t, db.Create(&otherGroup).Error)
+
+	userWithGroup := model.User{Username: "with-group", UserGroups: []model.UserGroup{allowedGroup}}
+	userWithoutGroup := model.User{Username: "without-group"}
+	require.NoError(t, db.Create(&userWithGroup).Error)
+	require.NoError(t, db.Create(&userWithoutGroup).Error)
+
+	clients := []model.OidcClient{
+		{Name: "Unrestricted", CallbackURLs: model.UrlList{"https://unrestricted.example.com/callback"}},
+		{Name: "Restricted without groups", CallbackURLs: model.UrlList{"https://empty.example.com/callback"}, IsGroupRestricted: true},
+		{Name: "Restricted to user group", CallbackURLs: model.UrlList{"https://allowed.example.com/callback"}, IsGroupRestricted: true, AllowedUserGroups: []model.UserGroup{allowedGroup}},
+		{Name: "Restricted to other group", CallbackURLs: model.UrlList{"https://other.example.com/callback"}, IsGroupRestricted: true, AllowedUserGroups: []model.UserGroup{otherGroup}},
+	}
+	for i := range clients {
+		require.NoError(t, db.Create(&clients[i]).Error)
+	}
+
+	groupClients, _, err := s.ListAccessibleOidcClients(t.Context(), userWithGroup.ID, utils.ListRequestOptions{})
+	require.NoError(t, err)
+	assert.ElementsMatch(t, []string{"Unrestricted", "Restricted to user group"}, accessibleClientNames(groupClients))
+
+	noGroupClients, _, err := s.ListAccessibleOidcClients(t.Context(), userWithoutGroup.ID, utils.ListRequestOptions{})
+	require.NoError(t, err)
+	assert.Equal(t, []string{"Unrestricted"}, accessibleClientNames(noGroupClients))
+}
+
+func accessibleClientNames(clients []dto.AccessibleOidcClientDto) []string {
+	names := make([]string, len(clients))
+	for i := range clients {
+		names[i] = clients[i].Name
+	}
+	return names
+}

backend/resources/migrations/postgres/20260722120000_oauth_storage_export_normalization.down.sql

@@ -0,0 +1 @@
+-- No-op on PostgreSQL

backend/resources/migrations/postgres/20260722120000_oauth_storage_export_normalization.up.sql

@@ -0,0 +1 @@
+-- No-op on PostgreSQL because its OAuth storage types already match the export format

backend/resources/migrations/sqlite/20260722120000_oauth_storage_export_normalization.down.sql

@@ -0,0 +1,151 @@
+PRAGMA foreign_keys = OFF;
+BEGIN;
+
+CREATE TABLE reauthentication_tokens_old (
+    id TEXT PRIMARY KEY,
+    created_at DATETIME NOT NULL,
+    token TEXT NOT NULL UNIQUE,
+    expires_at INTEGER NOT NULL,
+    user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE
+);
+
+INSERT INTO reauthentication_tokens_old (
+    id,
+    created_at,
+    token,
+    expires_at,
+    user_id
+)
+SELECT
+    id,
+    created_at,
+    token,
+    expires_at,
+    user_id
+FROM reauthentication_tokens;
+
+DROP TABLE reauthentication_tokens;
+ALTER TABLE reauthentication_tokens_old RENAME TO reauthentication_tokens;
+
+CREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens (token);
+CREATE INDEX idx_reauthentication_tokens_expires_at ON reauthentication_tokens (expires_at);
+
+CREATE TABLE oauth2_sessions_old (
+    id TEXT NOT NULL PRIMARY KEY,
+    created_at INTEGER NOT NULL,
+    kind TEXT NOT NULL,
+    key TEXT NOT NULL,
+    request_id TEXT NOT NULL,
+    access_token_signature TEXT NOT NULL DEFAULT '',
+    active BOOLEAN NOT NULL DEFAULT TRUE,
+    request_data TEXT NOT NULL,
+    expires_at INTEGER
+);
+
+INSERT INTO oauth2_sessions_old (
+    id,
+    created_at,
+    kind,
+    key,
+    request_id,
+    access_token_signature,
+    active,
+    request_data,
+    expires_at
+)
+SELECT
+    id,
+    created_at,
+    kind,
+    key,
+    request_id,
+    access_token_signature,
+    active,
+    CAST(request_data AS TEXT),
+    expires_at
+FROM oauth2_sessions;
+
+DROP TABLE oauth2_sessions;
+ALTER TABLE oauth2_sessions_old RENAME TO oauth2_sessions;
+
+CREATE UNIQUE INDEX idx_oauth2_sessions_kind_key ON oauth2_sessions (kind, key);
+CREATE INDEX idx_oauth2_sessions_kind_request ON oauth2_sessions (kind, request_id);
+CREATE INDEX idx_oauth2_sessions_expires_at ON oauth2_sessions (expires_at);
+
+CREATE TABLE oauth2_jtis_old (
+    id TEXT NOT NULL PRIMARY KEY,
+    created_at INTEGER NOT NULL,
+    jti TEXT NOT NULL UNIQUE,
+    expires_at INTEGER NOT NULL
+);
+
+INSERT INTO oauth2_jtis_old (
+    id,
+    created_at,
+    jti,
+    expires_at
+)
+SELECT
+    id,
+    created_at,
+    jti,
+    expires_at
+FROM oauth2_jtis;
+
+DROP TABLE oauth2_jtis;
+ALTER TABLE oauth2_jtis_old RENAME TO oauth2_jtis;
+
+CREATE INDEX idx_oauth2_jtis_expires_at ON oauth2_jtis (expires_at);
+
+CREATE TABLE interaction_sessions_old (
+    id TEXT NOT NULL PRIMARY KEY,
+    created_at INTEGER NOT NULL,
+    consent_required BOOLEAN NOT NULL DEFAULT FALSE,
+    reauthentication_required BOOLEAN NOT NULL DEFAULT FALSE,
+    authentication_required BOOLEAN NOT NULL DEFAULT FALSE,
+    account_selection_required BOOLEAN NOT NULL DEFAULT FALSE,
+    scopes TEXT NOT NULL DEFAULT '[]',
+    client_id TEXT NOT NULL REFERENCES oidc_clients(id) ON DELETE CASCADE,
+    user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
+    requested_at INTEGER NOT NULL,
+    reauthenticated_at INTEGER,
+    parameters TEXT NOT NULL DEFAULT '{}'
+);
+
+INSERT INTO interaction_sessions_old (
+    id,
+    created_at,
+    consent_required,
+    reauthentication_required,
+    authentication_required,
+    account_selection_required,
+    scopes,
+    client_id,
+    user_id,
+    requested_at,
+    reauthenticated_at,
+    parameters
+)
+SELECT
+    id,
+    created_at,
+    consent_required,
+    reauthentication_required,
+    authentication_required,
+    account_selection_required,
+    CAST(scopes AS TEXT),
+    client_id,
+    user_id,
+    requested_at,
+    reauthenticated_at,
+    CAST(parameters AS TEXT)
+FROM interaction_sessions;
+
+DROP TABLE interaction_sessions;
+ALTER TABLE interaction_sessions_old RENAME TO interaction_sessions;
+
+CREATE INDEX idx_interaction_sessions_client_id ON interaction_sessions (client_id);
+CREATE INDEX idx_interaction_sessions_user_id ON interaction_sessions (user_id);
+
+COMMIT;
+PRAGMA foreign_keys = ON;

backend/resources/migrations/sqlite/20260722120000_oauth_storage_export_normalization.up.sql

@@ -0,0 +1,152 @@
+PRAGMA foreign_keys = OFF;
+BEGIN;
+
+-- Align JSON and timestamp column types with the export format used for PostgreSQL
+CREATE TABLE reauthentication_tokens_new (
+    id TEXT PRIMARY KEY,
+    created_at DATETIME NOT NULL,
+    token TEXT NOT NULL UNIQUE,
+    expires_at DATETIME NOT NULL,
+    user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE
+);
+
+INSERT INTO reauthentication_tokens_new (
+    id,
+    created_at,
+    token,
+    expires_at,
+    user_id
+)
+SELECT
+    id,
+    created_at,
+    token,
+    expires_at,
+    user_id
+FROM reauthentication_tokens;
+
+DROP TABLE reauthentication_tokens;
+ALTER TABLE reauthentication_tokens_new RENAME TO reauthentication_tokens;
+
+CREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens (token);
+CREATE INDEX idx_reauthentication_tokens_expires_at ON reauthentication_tokens (expires_at);
+
+CREATE TABLE oauth2_sessions_new (
+    id TEXT NOT NULL PRIMARY KEY,
+    created_at DATETIME NOT NULL,
+    kind TEXT NOT NULL,
+    key TEXT NOT NULL,
+    request_id TEXT NOT NULL,
+    access_token_signature TEXT NOT NULL DEFAULT '',
+    active BOOLEAN NOT NULL DEFAULT TRUE,
+    request_data BLOB NOT NULL,
+    expires_at DATETIME
+);
+
+INSERT INTO oauth2_sessions_new (
+    id,
+    created_at,
+    kind,
+    key,
+    request_id,
+    access_token_signature,
+    active,
+    request_data,
+    expires_at
+)
+SELECT
+    id,
+    created_at,
+    kind,
+    key,
+    request_id,
+    access_token_signature,
+    active,
+    CAST(request_data AS BLOB),
+    expires_at
+FROM oauth2_sessions;
+
+DROP TABLE oauth2_sessions;
+ALTER TABLE oauth2_sessions_new RENAME TO oauth2_sessions;
+
+CREATE UNIQUE INDEX idx_oauth2_sessions_kind_key ON oauth2_sessions (kind, key);
+CREATE INDEX idx_oauth2_sessions_kind_request ON oauth2_sessions (kind, request_id);
+CREATE INDEX idx_oauth2_sessions_expires_at ON oauth2_sessions (expires_at);
+
+CREATE TABLE oauth2_jtis_new (
+    id TEXT NOT NULL PRIMARY KEY,
+    created_at DATETIME NOT NULL,
+    jti TEXT NOT NULL UNIQUE,
+    expires_at DATETIME NOT NULL
+);
+
+INSERT INTO oauth2_jtis_new (
+    id,
+    created_at,
+    jti,
+    expires_at
+)
+SELECT
+    id,
+    created_at,
+    jti,
+    expires_at
+FROM oauth2_jtis;
+
+DROP TABLE oauth2_jtis;
+ALTER TABLE oauth2_jtis_new RENAME TO oauth2_jtis;
+
+CREATE INDEX idx_oauth2_jtis_expires_at ON oauth2_jtis (expires_at);
+
+CREATE TABLE interaction_sessions_new (
+    id TEXT NOT NULL PRIMARY KEY,
+    created_at DATETIME NOT NULL,
+    consent_required BOOLEAN NOT NULL DEFAULT FALSE,
+    reauthentication_required BOOLEAN NOT NULL DEFAULT FALSE,
+    authentication_required BOOLEAN NOT NULL DEFAULT FALSE,
+    account_selection_required BOOLEAN NOT NULL DEFAULT FALSE,
+    scopes BLOB NOT NULL DEFAULT X'5B5D',
+    client_id TEXT NOT NULL REFERENCES oidc_clients(id) ON DELETE CASCADE,
+    user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
+    requested_at DATETIME NOT NULL,
+    reauthenticated_at DATETIME,
+    parameters BLOB NOT NULL DEFAULT X'7B7D'
+);
+
+INSERT INTO interaction_sessions_new (
+    id,
+    created_at,
+    consent_required,
+    reauthentication_required,
+    authentication_required,
+    account_selection_required,
+    scopes,
+    client_id,
+    user_id,
+    requested_at,
+    reauthenticated_at,
+    parameters
+)
+SELECT
+    id,
+    created_at,
+    consent_required,
+    reauthentication_required,
+    authentication_required,
+    account_selection_required,
+    CAST(scopes AS BLOB),
+    client_id,
+    user_id,
+    requested_at,
+    reauthenticated_at,
+    CAST(parameters AS BLOB)
+FROM interaction_sessions;
+
+DROP TABLE interaction_sessions;
+ALTER TABLE interaction_sessions_new RENAME TO interaction_sessions;
+
+CREATE INDEX idx_interaction_sessions_client_id ON interaction_sessions (client_id);
+CREATE INDEX idx_interaction_sessions_user_id ON interaction_sessions (user_id);
+
+COMMIT;
+PRAGMA foreign_keys = ON;

frontend/src/routes/login/alternative/code/+page.svelte

@@ -87,10 +87,11 @@
 					placeholder={m.code()}
 					aria-label={m.code()}
 					bind:value={code}
+					autofocus
 					type="text"
 				/>
 			{:else}
-				<InputOTP.Root maxlength={6} bind:value={code}>
+				<InputOTP.Root maxlength={6} bind:value={code} autofocus>
 					{#snippet children({ cells })}
 						<InputOTP.Group>
 							{#each cells as cell}

frontend/src/routes/settings/+layout.svelte

@@ -63,7 +63,7 @@
 				</div>
 			</div>
 
-			<div class="flex w-full flex-col gap-4 overflow-hidden pb-2">
+			<div class="flex w-full flex-col gap-4 overflow-hidden pb-2 px-2">
 				<FadeWrapper>
 					<EmailVerificationStateBox />
 					{@render children()}

pnpm-lock.yaml

@@ -211,8 +211,8 @@ importers:
   tests:
     dependencies:
       adm-zip:
-        specifier: ^0.5.17
-        version: 0.5.18
+        specifier: ^0.6.0
+        version: 0.6.0
     devDependencies:
       '@playwright/test':
         specifier: ^1.60.0
@@ -1476,9 +1476,9 @@ packages:
     engines: {node: '>=0.4.0'}
     hasBin: true
 
-  adm-zip@0.5.18:
-    resolution: {integrity: sha512-ufJnssQGbxzLNS1Ho9bCtX4rQKCCvoVuDLHoJyc3F9dOGDB4BkWs2Ci0kv53lqocAEQ/Cbi+I2XCsNYGqVYqng==}
-    engines: {node: '>=12.0'}
+  adm-zip@0.6.0:
+    resolution: {integrity: sha512-XleryMhbuksdKtofnWZ9Sk+4CUTbms4Mb/EU32SZwToAyZ5RgVos/ki8n+yr0LWHOGKuakbXTuuYNHLQjhddgg==}
+    engines: {node: '>=14.0'}
 
   agent-base@6.0.2:
     resolution: {integrity: sha512-RZNwNclF7+MS/8bDg70amg32dyeZGZxiDuQmZxKLAlQjr3jGyLx+4Kkk58UO7D2QdgFIQCovuSuZESne6RG6XQ==}
@@ -4146,7 +4146,7 @@ snapshots:
 
   acorn@8.17.0: {}
 
-  adm-zip@0.5.18: {}
+  adm-zip@0.6.0: {}
 
   agent-base@6.0.2:
     dependencies:

tests/package.json

@@ -15,6 +15,6 @@
 		"prettier": "^3.8.3"
 	},
 	"dependencies": {
-		"adm-zip": "^0.5.17"
+		"adm-zip": "^0.6.0"
 	}
 }

tests/resources/export/database.json

@@ -1,7 +1,15 @@
 {
 	"provider": "sqlite",
-	"version": 20260708130000,
-	"tableOrder": ["users", "user_groups", "oidc_clients", "signup_tokens", "apis", "api_permissions", "oidc_clients_allowed_api_permissions"],
+	"version": 20260722120000,
+	"tableOrder": [
+		"users",
+		"user_groups",
+		"oidc_clients",
+		"signup_tokens",
+		"apis",
+		"api_permissions",
+		"oidc_clients_allowed_api_permissions"
+	],
 	"tables": {
 		"apis": [
 			{
@@ -229,6 +237,10 @@
 				"oidc_client_id": "606c7782-f2b1-49e5-8ea9-26eb1b06d018",
 				"user_group_id": "adab18bf-f89d-4087-9ee1-70ff15b48211"
 			},
+			{
+				"oidc_client_id": "7c21a609-96b5-4011-9900-272b8d31a9d1",
+				"user_group_id": "c7ae7c01-28a3-4f3c-9572-1ee734ea8368"
+			},
 			{
 				"oidc_client_id": "c46d2090-37a0-4f2b-8748-6aa53b0c1afa",
 				"user_group_id": "adab18bf-f89d-4087-9ee1-70ff15b48211"
@@ -264,6 +276,52 @@
 				"user_id": "f4b89dc2-62fb-46bf-9f5f-c34f4eafe93e"
 			}
 		],
+		"oauth2_jtis": [
+			{
+				"id": "bd0c8bf2-66ec-487a-9dd5-7d9d78d73543",
+				"created_at": "2026-07-22T12:00:00Z",
+				"jti": "cross-database-test-jti",
+				"expires_at": "2099-01-01T00:00:00Z"
+			}
+		],
+		"oauth2_sessions": [
+			{
+				"id": "551ab785-c830-47d3-8a07-60c9f3bb4859",
+				"created_at": "2026-07-22T12:00:00Z",
+				"kind": "access_token",
+				"key": "cross-database-test-session",
+				"request_id": "cross-database-test-request",
+				"access_token_signature": "",
+				"active": true,
+				"request_data": "eyJyZXF1ZXN0IjoidmFsdWUifQ==",
+				"expires_at": "2099-01-01T00:00:00Z"
+			}
+		],
+		"interaction_sessions": [
+			{
+				"id": "aaf5dd23-cd1f-4748-a2aa-baa6af94d800",
+				"created_at": "2026-07-22T12:00:00Z",
+				"consent_required": true,
+				"reauthentication_required": false,
+				"authentication_required": false,
+				"account_selection_required": false,
+				"scopes": "WyJvcGVuaWQiXQ==",
+				"client_id": "3654a746-35d4-4321-ac61-0bdcff2b4055",
+				"user_id": "f4b89dc2-62fb-46bf-9f5f-c34f4eafe93e",
+				"requested_at": "2099-01-01T00:00:00Z",
+				"reauthenticated_at": null,
+				"parameters": "eyJjbGllbnRfaWQiOiIzNjU0YTc0Ni0zNWQ0LTQzMjEtYWM2MS0wYmRjZmYyYjQwNTUifQ=="
+			}
+		],
+		"reauthentication_tokens": [
+			{
+				"id": "71839ace-d978-4e6f-8fb1-b8648a21031b",
+				"created_at": "2026-07-22T12:00:00Z",
+				"token": "cross-database-reauthentication-token",
+				"expires_at": "2099-01-01T00:00:00Z",
+				"user_id": "f4b89dc2-62fb-46bf-9f5f-c34f4eafe93e"
+			}
+		],
 		"signup_tokens": [
 			{
 				"created_at": "2025-11-25T12:39:02Z",

tests/specs/cli.spec.ts

@@ -59,7 +59,7 @@ test('Export via stdout', async ({ baseURL }) => {
 	compareExports(exampleExportPath, stdoutExtractPath);
 });
 
-test('Import', async () => {
+test('Import SQLite export', async () => {
 	// Reset the backend without seeding
 	await cleanupBackend({ skipSeed: true });
 
@@ -83,7 +83,7 @@ test('Import', async () => {
 	compareExports(exampleExportPath, exportExtracted);
 });
 
-test('Import via stdin', async () => {
+test('Import SQLite export via stdin', async () => {
 	await cleanupBackend({ skipSeed: true });
 
 	const exampleExportArchivePath = path.join(tmpDir, 'example-export-stdin.zip');