Enforce that the HA mode setting is immutable per cluster

HAEnabled changes how replicas coordinate against the shared database, so
it cannot be flipped under an existing cluster. On startup we now record
the current value in the "kv" table on first run (via an atomic
INSERT ... ON CONFLICT ... RETURNING, the same portable pattern used for
the instance ID) and, on every later startup, compare the stored value
with the configured one. On a mismatch we stop with an error explaining
that the setting can only be changed by exporting and re-importing the
data into a new database.

The export omits the "ha_enabled" kv row so an imported cluster picks up
the mode from its own configuration rather than inheriting the exporter's.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01XHxJtQi2jBGv16GZTyMtSa

backend/go.mod

@@ -24,7 +24,7 @@ require (
 	github.com/go-webauthn/webauthn v0.17.4
 	github.com/golang-migrate/migrate/v4 v4.19.1
 	github.com/google/uuid v1.6.0
-	github.com/italypaleale/francis v0.1.0-beta.11
+	github.com/italypaleale/francis v0.1.0-beta.12
 	github.com/italypaleale/go-kit v0.0.0-20260708054611-e276b65dd3be
 	github.com/italypaleale/go-sql-utils v0.2.4
 	github.com/jackc/pgx/v5 v5.10.0
@@ -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,14 +257,16 @@ 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=
 github.com/inconshreveable/mousetrap v1.1.0/go.mod h1:vpF70FUmC8bwa3OWnCshd2FqLfsEA9PFc4w1p2J65bw=
 github.com/italypaleale/francis v0.1.0-beta.11 h1:FurXV2vMkRzJRFldQ6Z/bhLSJz8YXHm84uiASGeyWWU=
 github.com/italypaleale/francis v0.1.0-beta.11/go.mod h1:vqKhwdLs5Sx+n6JCNknEKAODtEU51E9/LC1q9JAG3zk=
+github.com/italypaleale/francis v0.1.0-beta.12 h1:v3D2w2uH7O0TKY5guCULMbpNH/Uo/kJNPJ9QMLGwFXU=
+github.com/italypaleale/francis v0.1.0-beta.12/go.mod h1:vqKhwdLs5Sx+n6JCNknEKAODtEU51E9/LC1q9JAG3zk=
 github.com/italypaleale/go-kit v0.0.0-20260708054611-e276b65dd3be h1:jgu+Mdsda++LqPxz8cj8vvgiFINQ8PhFB4Q1VZpyPjs=
 github.com/italypaleale/go-kit v0.0.0-20260708054611-e276b65dd3be/go.mod h1:pl0r3F+thZIyDsyDo8aOUsAIVcsRuAeP1bB4GuAHLoY=
 github.com/italypaleale/go-sql-utils v0.2.4 h1:6CN8y3qEdNzvYlS/JK6N65E8cL9F8a6OBCJjzaQIv3c=
@@ -419,8 +421,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/bootstrap/actors_bootstrap.go

@@ -10,6 +10,7 @@ import (
 	"time"
 
 	"github.com/italypaleale/francis/builtin/ratelimit"
+	"github.com/italypaleale/francis/components"
 	"github.com/italypaleale/francis/components/postgres"
 	"github.com/italypaleale/francis/host/local"
 	"github.com/jackc/pgx/v5/pgxpool"
@@ -43,22 +44,34 @@ func NewActors(o NewActorsOpts) (*local.Host, map[string]*ratelimit.RateLimitSer
 		return nil, nil, fmt.Errorf("failed to derive PSK: %w", err)
 	}
 
+	// Derive the cluster host limit from the HA setting
+	// With HA disabled the cluster is capped at a single replica; enabling HA lifts the cap
+	maxHosts := 1
+	if o.EnvConfig.HAEnabled {
+		maxHosts = 0
+	}
+
 	// Options for the host
 	opts := []local.HostOption{
 		local.WithAddress(net.JoinHostPort(o.EnvConfig.ActorsHost, o.EnvConfig.ActorsPort)),
 		local.WithLogger(log.With("scope", "actor-host")),
 		local.WithRuntimePSKs(psk),
 		local.WithShutdownGracePeriod(10 * time.Second),
-		// TODO: Tweak these values once Pocket ID fully supports horizontal scaling.
-		// The relaxed intervals are appropriate for a single active host, but should be
-		// tuned for lower latency and better distribution across a multi-host cluster.
-		local.WithHostHealthCheckDeadline(90 * time.Second),
-		local.WithAlarmsPollInterval(5 * time.Minute),
-		local.WithAlarmsFetchAheadInterval(5 * time.Minute),
+		local.WithMaxHosts(maxHosts),
+		local.WithHostHealthCheckDeadline(ActorsHostHealthCheckDeadline(o.EnvConfig.HAEnabled)),
+	}
+
+	// With a single active host the relaxed alarm intervals reduce database load
+	// When HA is enabled they are dropped so Francis uses its tighter defaults, which distribute alarm work and fail over faster across multiple hosts
+	if !o.EnvConfig.HAEnabled {
+		opts = append(opts,
+			local.WithAlarmsPollInterval(5*time.Minute),
+			local.WithAlarmsFetchAheadInterval(5*time.Minute),
+		)
 	}
 
 	// Add the database connection
-	providerOpt, err := o.getProvider()
+	providerOpt, err := o.getProviderOption()
 	if err != nil {
 		return nil, nil, err
 	}
@@ -98,23 +111,50 @@ func (o *NewActorsOpts) getPSK() ([]byte, error) {
 	return crypto.DeriveKey(o.EnvConfig.EncryptionKey, "pocketid/actors-psk/"+o.InstanceID)
 }
 
-func (o *NewActorsOpts) getProvider() (local.HostOption, error) {
+// nonHAHostHealthCheckDeadline is the relaxed host health-check deadline used when HA is disabled
+// A single active host does not need aggressive health checks, so a longer deadline reduces database load
+const nonHAHostHealthCheckDeadline = 90 * time.Second
+
+// ActorsHostHealthCheckDeadline returns the health-check deadline the actor host uses for the given HA setting
+// The cluster admin used during import must pass the same value so it waits the right amount of time for hosts to drain
+func ActorsHostHealthCheckDeadline(haEnabled bool) time.Duration {
+	if haEnabled {
+		return components.DefaultHostHealthCheckDeadline
+	}
+	return nonHAHostHealthCheckDeadline
+}
+
+// ActorsProviderOptions builds the Francis provider options for the given database handles
+// The actor host and the cluster admin must use the same options so they address the same cluster
+func ActorsProviderOptions(pg *pgxpool.Pool, sqliteDB *sql.DB) (components.ProviderOptions, error) {
 	switch {
-	case o.Postgres != nil && o.SQLite != nil:
+	case pg != nil && sqliteDB != nil:
 		return nil, errors.New("cannot have both Postgres and SQLite connections")
-	case o.Postgres != nil:
-		return local.WithPostgresProvider(postgres.PostgresProviderOptions{
-			DB: o.Postgres,
-		}), nil
-	case o.SQLite != nil:
-		return local.WithSQLiteProvider(local.SQLiteProviderOptions{
-			DB: o.SQLite,
-		}), nil
+	case pg != nil:
+		return postgres.PostgresProviderOptions{DB: pg}, nil
+	case sqliteDB != nil:
+		return local.SQLiteProviderOptions{DB: sqliteDB}, nil
 	default:
 		return nil, errors.New("one of Postgres and SQLite must be set")
 	}
 }
 
+// getProviderOption wraps the shared provider options in the host option the local host expects
+func (o *NewActorsOpts) getProviderOption() (local.HostOption, error) {
+	providerOpts, err := ActorsProviderOptions(o.Postgres, o.SQLite)
+	if err != nil {
+		return nil, err
+	}
+	switch v := providerOpts.(type) {
+	case postgres.PostgresProviderOptions:
+		return local.WithPostgresProvider(v), nil
+	case local.SQLiteProviderOptions:
+		return local.WithSQLiteProvider(v), nil
+	default:
+		return nil, fmt.Errorf("unsupported provider options type: %T", providerOpts)
+	}
+}
+
 func (o *NewActorsOpts) registerCronJobs(host *local.Host) (err error) {
 	// In test mode, we do not register anything
 	if common.EnvConfig.AppEnv == "test" {

backend/internal/bootstrap/bootstrap.go

@@ -9,14 +9,15 @@ import (
 
 	_ "github.com/golang-migrate/migrate/v4/source/file"
 
+	"github.com/italypaleale/francis/components"
 	"github.com/italypaleale/francis/host/local"
 	"github.com/italypaleale/go-kit/servicerunner"
 	"gorm.io/gorm"
 
 	"github.com/pocket-id/pocket-id/backend/internal/common"
+	"github.com/pocket-id/pocket-id/backend/internal/haconfig"
 	"github.com/pocket-id/pocket-id/backend/internal/instanceid"
 	"github.com/pocket-id/pocket-id/backend/internal/job"
-	"github.com/pocket-id/pocket-id/backend/internal/service"
 	"github.com/pocket-id/pocket-id/backend/internal/storage"
 )
 
@@ -43,7 +44,7 @@ func Bootstrap(ctx context.Context) error {
 	}
 	if pg != nil {
 		defer func() {
-			// Close the database connection pool only after the shutdown functions have run: some of them (e.g. releasing the application lock) still need to query the database.
+			// Close the database connection pool only after the shutdown functions have run: some of them (e.g. the actor host deregistering itself from the cluster) still need to query the database.
 			pg.Close()
 		}()
 	}
@@ -55,6 +56,13 @@ func Bootstrap(ctx context.Context) error {
 		return fmt.Errorf("failed to initialize instance ID: %w", err)
 	}
 
+	// Enforce that the HA mode setting has not changed since the cluster was created
+	// The value is fixed on first startup and stored in the "kv" table; a mismatch means the operator changed it against an existing database, which is unsupported, so we stop here rather than run with an inconsistent configuration
+	err = haconfig.Check(ctx, db, common.EnvConfig.HAEnabled)
+	if err != nil {
+		return err
+	}
+
 	// Init storage
 	fileStorage, err := InitStorage(ctx, db)
 	if err != nil {
@@ -104,29 +112,8 @@ func Bootstrap(ctx context.Context) error {
 	if err != nil {
 		return fmt.Errorf("failed to initialize services: %w", err)
 	}
-	services = append(services, svc.appLockService.RunRenewal)
-
-	// Acquire the lock from the app lock service
-	waitUntil, err := svc.appLockService.Acquire(ctx, false)
-	if errors.Is(err, service.ErrLockUnavailable) {
-		return errors.New("it appears that there's already one instance of Pocket ID running; running multiple replicas of Pocket ID is currently not supported")
-	} else if err != nil {
-		return fmt.Errorf("failed to acquire application lock: %w", err)
-	}
-
-	select {
-	case <-ctx.Done():
-		return ctx.Err()
-	case <-time.After(time.Until(waitUntil)):
-	}
-
-	shutdowns.Add(func(shutdownCtx context.Context) error {
-		sErr := svc.appLockService.Release(shutdownCtx)
-		if sErr != nil {
-			return fmt.Errorf("failed to release application lock: %w", sErr)
-		}
-		return nil
-	})
+	// Single-replica enforcement now comes from the actor host itself
+	// With HA disabled it registers with a host limit of 1, so a second replica fails to register and the process exits with ErrClusterFull, which is translated to a friendly message where the services run below
 
 	// Register scheduled jobs, only in non-test mode
 	if common.EnvConfig.AppEnv != "test" {
@@ -151,7 +138,9 @@ func Bootstrap(ctx context.Context) error {
 	// Run all background services
 	// This call blocks until the context is canceled
 	err = servicerunner.NewServiceRunner(services...).Run(ctx)
-	if err != nil {
+	if errors.Is(err, components.ErrClusterFull) {
+		return errors.New("it appears that there's already one instance of Pocket ID running; running multiple replicas requires enabling HA mode, which is not yet supported")
+	} else if err != nil {
 		return fmt.Errorf("failed to run services: %w", err)
 	}
 

backend/internal/bootstrap/e2etest_router_bootstrap.go

@@ -17,7 +17,7 @@ import (
 func init() {
 	registerTestControllers = []func(apiGroup *gin.RouterGroup, db *gorm.DB, svc *services){
 		func(apiGroup *gin.RouterGroup, db *gorm.DB, svc *services) {
-			testService, err := service.NewTestService(db, svc.appConfigService, svc.jwtService, svc.ldapService, svc.appLockService, svc.fileStorage)
+			testService, err := service.NewTestService(db, svc.appConfigService, svc.jwtService, svc.ldapService, svc.fileStorage)
 			if err != nil {
 				slog.Error("Failed to initialize test service", slog.Any("error", err))
 				os.Exit(1)

backend/internal/bootstrap/services_bootstrap.go

@@ -35,7 +35,6 @@ type services struct {
 	ldapService          *service.LdapService
 	versionService       *service.VersionService
 	fileStorage          storage.FileStorage
-	appLockService       *service.AppLockService
 	oneTimeAccessService *service.OneTimeAccessService
 
 	apiKeyModule     *apikey.Module
@@ -66,7 +65,6 @@ func initServices(
 
 	svc.fileStorage = fileStorage
 	svc.appImagesService = service.NewAppImagesService(imageExtensions, fileStorage)
-	svc.appLockService = service.NewAppLockService(db)
 
 	svc.emailService, err = service.NewEmailService(db)
 	if err != nil {

backend/internal/cmds/import.go

@@ -3,15 +3,18 @@ package cmds
 import (
 	"archive/zip"
 	"context"
+	"database/sql"
 	"errors"
 	"fmt"
 	"io"
+	"log/slog"
 	"os"
 	"path/filepath"
 	"time"
 
+	"github.com/italypaleale/francis/clusteradmin"
+	"github.com/italypaleale/francis/components"
 	"github.com/spf13/cobra"
-	"gorm.io/gorm"
 
 	"github.com/pocket-id/pocket-id/backend/internal/bootstrap"
 	"github.com/pocket-id/pocket-id/backend/internal/common"
@@ -38,7 +41,7 @@ func init() {
 
 	importCmd.Flags().StringVarP(&flags.Path, "path", "p", "pocket-id-export.zip", "Path to the ZIP file to import the data from, or '-' to read from stdin")
 	importCmd.Flags().BoolVarP(&flags.Yes, "yes", "y", false, "Skip confirmation prompts")
-	importCmd.Flags().BoolVarP(&flags.ForcefullyAcquireLock, "forcefully-acquire-lock", "", false, "Forcefully acquire the application lock by terminating the Pocket ID instance")
+	importCmd.Flags().BoolVarP(&flags.ForcefullyAcquireLock, "forcefully-acquire-lock", "", false, "Forcefully acquire exclusive access by terminating any running Pocket ID instance")
 
 	rootCmd.AddCommand(importCmd)
 }
@@ -73,23 +76,50 @@ func runImport(ctx context.Context, flags importFlags) error {
 	}
 	defer zipReader.Close()
 
-	db, _, err := bootstrap.ConnectDatabase(ctx)
+	// Connect to the database without running migrations: the import re-creates the Pocket ID schema itself
+	db, pg, err := bootstrap.ConnectDatabase(ctx)
 	if err != nil {
 		return err
 	}
 
-	err = acquireImportLock(ctx, db, flags.ForcefullyAcquireLock)
+	// The cluster admin talks to the same database as the actor host, so build its provider options the same way the host does
+	var sqliteDB *sql.DB
+	if pg == nil {
+		sqliteDB, err = db.DB()
+		if err != nil {
+			return fmt.Errorf("failed to get sql.DB connection: %w", err)
+		}
+	}
+	providerOpts, err := bootstrap.ActorsProviderOptions(pg, sqliteDB)
 	if err != nil {
 		return err
 	}
 
-	storage, err := bootstrap.InitStorage(ctx, db)
+	// Take exclusive access to the cluster so no Pocket ID replica is running while we overwrite the database
+	release, lost, err := acquireExclusiveAccess(ctx, providerOpts, flags.ForcefullyAcquireLock)
+	if err != nil {
+		return err
+	}
+	defer release()
+
+	// Abort the import if exclusive access is lost partway through (for example if the lease can no longer be renewed)
+	importCtx, cancel := context.WithCancel(ctx)
+	defer cancel()
+	go func() {
+		select {
+		case <-lost:
+			cancel()
+		case <-importCtx.Done():
+		}
+	}()
+
+	storage, err := bootstrap.InitStorage(importCtx, db)
 	if err != nil {
 		return fmt.Errorf("failed to initialize storage: %w", err)
 	}
 
 	importService := service.NewImportService(db, storage)
-	err = importService.ImportFromZip(ctx, &zipReader.Reader)
+	err = importService.ImportFromZip(importCtx, &zipReader.Reader)
 	if err != nil {
 		return fmt.Errorf("failed to import data from zip: %w", err)
 	}
@@ -98,41 +128,45 @@ func runImport(ctx context.Context, flags importFlags) error {
 	return nil
 }
 
-func acquireImportLock(ctx context.Context, db *gorm.DB, force bool) error {
-	// Check if the kv table exists, in case we are starting from an empty database
-	exists, err := utils.DBTableExists(db, "kv")
+// acquireExclusiveAccess takes an exclusive-access lease on the cluster so the import can safely overwrite the database.
+//
+// It returns a release function that must be called once the import is done, and a channel that is closed if the lease is lost while it is held.
+func acquireExclusiveAccess(ctx context.Context, providerOpts components.ProviderOptions, force bool) (release func(), lost <-chan struct{}, err error) {
+	// New initializes the provider, applying the actor host's schema migrations, so this also works against a brand-new (empty) database
+	admin, err := clusteradmin.New(ctx, providerOpts, clusteradmin.Options{
+		// Match the actor host so the admin waits the right amount of time for hosts to drain
+		HostHealthCheckDeadline: bootstrap.ActorsHostHealthCheckDeadline(common.EnvConfig.HAEnabled),
+		Logger:                  slog.Default(),
+	})
 	if err != nil {
-		return fmt.Errorf("failed to check if kv table exists: %w", err)
-	}
-	if !exists {
-		// This either means the database is empty, or the import is into an old version of PocketID that doesn't support locks
-		// In either case, there's no lock to acquire
-		fmt.Println("Could not acquire a lock because the 'kv' table does not exist. This is fine if you're importing into a new database, but make sure that there isn't an instance of Pocket ID currently running and using the same database.")
-		return nil
+		return nil, nil, fmt.Errorf("failed to create cluster admin: %w", err)
 	}
 
-	// Note that we do not call a deferred Release if the data was imported
-	// This is because we are overriding the contents of the database, so the lock is automatically lost
-	appLockService := service.NewAppLockService(db)
-
-	opCtx, cancel := context.WithTimeout(ctx, 30*time.Second)
-	defer cancel()
-
-	waitUntil, err := appLockService.Acquire(opCtx, force)
-	if errors.Is(err, service.ErrLockUnavailable) {
-		//nolint:staticcheck
-		return errors.New("Pocket ID must be stopped before importing data; please stop the running instance or run with --forcefully-acquire-lock to terminate the other instance")
-	} else if err != nil {
-		return fmt.Errorf("failed to acquire application lock: %w", err)
+	lost, err = admin.AcquireExclusive(ctx, clusteradmin.AcquireOptions{Force: force})
+	if err != nil {
+		_ = admin.Close()
+		switch {
+		case errors.Is(err, components.ErrHostsConnected):
+			//nolint:staticcheck
+			return nil, nil, errors.New("Pocket ID must be stopped before importing data; please stop the running instance or run with --forcefully-acquire-lock to terminate the other instance")
+		case errors.Is(err, components.ErrExclusiveHeld):
+			return nil, nil, errors.New("another exclusive operation, such as another import, is already in progress; please wait for it to complete and try again")
+		default:
+			return nil, nil, fmt.Errorf("failed to acquire exclusive access: %w", err)
+		}
 	}
 
-	select {
-	case <-ctx.Done():
-		return ctx.Err()
-	case <-time.After(time.Until(waitUntil)):
+	release = func() {
+		// The import preserves the actor host's "francis_" tables, including the lease row, so the lease must be released explicitly
+		// Detach from ctx so the release still runs even if the import was canceled
+		releaseCtx, cancelRelease := context.WithTimeout(context.WithoutCancel(ctx), 30*time.Second)
+		defer cancelRelease()
+		if rerr := admin.ReleaseExclusive(releaseCtx); rerr != nil {
+			slog.WarnContext(ctx, "Failed to release exclusive access", slog.Any("error", rerr))
+		}
+		_ = admin.Close()
 	}
-
-	return nil
+	return release, lost, nil
 }
 
 func askForConfirmation() (bool, error) {

backend/internal/common/env_config.go

@@ -85,6 +85,10 @@ type EnvConfigSchema struct {
 	ActorsPort string `env:"ACTORS_PORT"`
 	ActorsHost string `env:"ACTORS_HOST" options:"toLower"`
 
+	// HAEnabled turns on high-availability mode, allowing more than one replica of Pocket ID to run against the same database at once
+	// It is intentionally not bound to an environment variable while HA support is still being completed, so it stays false and the actor host enforces a single replica
+	HAEnabled bool
+
 	LogLevel string `env:"LOG_LEVEL" options:"toLower"`
 	LogJSON  bool   `env:"LOG_JSON"`
 }

backend/internal/controller/e2etest_controller.go

@@ -41,11 +41,6 @@ func (tc *TestController) resetAndSeedHandler(c *gin.Context) {
 		return
 	}
 
-	if err := tc.TestService.ResetLock(c.Request.Context()); err != nil {
-		_ = c.Error(err)
-		return
-	}
-
 	if err := tc.TestService.ResetApplicationImages(c.Request.Context()); err != nil {
 		_ = c.Error(err)
 		return

backend/internal/haconfig/haconfig.go

@@ -0,0 +1,52 @@
+// Package haconfig enforces that the HA mode setting is fixed for the lifetime of a Pocket ID cluster.
+package haconfig
+
+import (
+	"context"
+	"fmt"
+	"strconv"
+	"time"
+
+	"gorm.io/gorm"
+)
+
+// KVKey is the key in the "kv" table under which the cluster's HA mode setting is recorded.
+const KVKey = "ha_enabled"
+
+// Check verifies that the HA mode setting has not changed since the Pocket ID cluster was created.
+//
+// On the first startup it atomically records the current value in the "kv" table; on every subsequent startup it reads back the stored value and compares it with the configured one, returning an error if they differ.
+//
+// The setting is immutable because it changes how replicas coordinate against the shared database, so switching it under an existing cluster would be unsafe. To change it, the data must be exported and re-imported into a new database.
+func Check(parentCtx context.Context, db *gorm.DB, haEnabled bool) error {
+	desired := strconv.FormatBool(haEnabled)
+
+	ctx, cancel := context.WithTimeout(parentCtx, 10*time.Second)
+	defer cancel()
+
+	// Atomically insert the current value if the row does not exist yet, then read back whatever is stored.
+	// On a conflict the existing value is kept: the no-op self-update (value = kv.value) lets RETURNING return the current value, so the value fixed when the cluster was created is never overwritten here.
+	// This is the same portable "INSERT ... ON CONFLICT ... RETURNING" pattern used for the instance ID, and is valid for both SQLite and Postgres.
+	var stored string
+	err := db.
+		WithContext(ctx).
+		Raw(
+			`INSERT INTO kv (key, value)
+			VALUES ('ha_enabled', ?)
+			ON CONFLICT (key) DO UPDATE SET
+				value = kv.value
+			RETURNING value`,
+			desired,
+		).
+		Scan(&stored).
+		Error
+	if err != nil {
+		return fmt.Errorf("failed to load the HA mode setting from the database: %w", err)
+	}
+
+	if stored != desired {
+		return fmt.Errorf("the HA mode setting cannot be changed after a Pocket ID cluster has been created: the database was created with HAEnabled=%s, but the current configuration has HAEnabled=%s; to change this setting, export your data from Pocket ID and re-import it into a new database with the new value", stored, desired)
+	}
+
+	return nil
+}

backend/internal/haconfig/haconfig_test.go

@@ -0,0 +1,111 @@
+package haconfig
+
+import (
+	"sync"
+	"testing"
+
+	"github.com/stretchr/testify/require"
+	"gorm.io/gorm"
+
+	"github.com/pocket-id/pocket-id/backend/internal/model"
+	testutils "github.com/pocket-id/pocket-id/backend/internal/utils/testing"
+)
+
+// readHAEnabled returns the value stored in the kv table under the "ha_enabled" key, together with the number of rows that match (to detect duplicates)
+func readHAEnabled(t *testing.T, db *gorm.DB) (value string, count int) {
+	t.Helper()
+	var rows []model.KV
+	err := db.Where("key = ?", KVKey).Find(&rows).Error
+	require.NoError(t, err)
+	if len(rows) == 0 {
+		return "", 0
+	}
+	require.NotNil(t, rows[0].Value, "ha_enabled value should not be NULL")
+	return *rows[0].Value, len(rows)
+}
+
+func TestCheck(t *testing.T) {
+	t.Run("records the value on first startup", func(t *testing.T) {
+		for _, haEnabled := range []bool{false, true} {
+			t.Run("HAEnabled="+boolStr(haEnabled), func(t *testing.T) {
+				db := testutils.NewDatabaseForTest(t)
+
+				err := Check(t.Context(), db, haEnabled)
+				require.NoError(t, err)
+
+				stored, count := readHAEnabled(t, db)
+				require.Equal(t, 1, count)
+				require.Equal(t, boolStr(haEnabled), stored)
+			})
+		}
+	})
+
+	t.Run("succeeds when the value is unchanged on a later startup", func(t *testing.T) {
+		db := testutils.NewDatabaseForTest(t)
+
+		require.NoError(t, Check(t.Context(), db, true))
+
+		// A second startup with the same value must succeed and not create a duplicate row
+		require.NoError(t, Check(t.Context(), db, true))
+
+		stored, count := readHAEnabled(t, db)
+		require.Equal(t, 1, count)
+		require.Equal(t, "true", stored)
+	})
+
+	t.Run("fails when the value changed, without overwriting the stored value", func(t *testing.T) {
+		db := testutils.NewDatabaseForTest(t)
+
+		// The cluster was created with HA disabled
+		require.NoError(t, Check(t.Context(), db, false))
+
+		// Starting up with HA enabled must be rejected
+		err := Check(t.Context(), db, true)
+		require.Error(t, err)
+		require.Contains(t, err.Error(), "cannot be changed")
+
+		// The stored value must not have been changed by the rejected startup
+		stored, count := readHAEnabled(t, db)
+		require.Equal(t, 1, count)
+		require.Equal(t, "false", stored)
+
+		// Starting up again with the original value still works
+		require.NoError(t, Check(t.Context(), db, false))
+	})
+
+	t.Run("concurrent first startups converge on a single value", func(t *testing.T) {
+		db := testutils.NewConcurrentDatabaseForTest(t)
+
+		const parallel = 25
+		errs := make([]error, parallel)
+
+		start := make(chan struct{})
+		var wg sync.WaitGroup
+		wg.Add(parallel)
+		for i := range parallel {
+			go func() {
+				defer wg.Done()
+				<-start
+				errs[i] = Check(t.Context(), db, true)
+			}()
+		}
+		close(start)
+		wg.Wait()
+
+		for i, err := range errs {
+			require.NoErrorf(t, err, "goroutine %d errored", i)
+		}
+
+		// Only a single row must have been persisted
+		stored, count := readHAEnabled(t, db)
+		require.Equal(t, 1, count)
+		require.Equal(t, "true", stored)
+	})
+}
+
+func boolStr(b bool) string {
+	if b {
+		return "true"
+	}
+	return "false"
+}

backend/internal/service/app_lock_service.go

@@ -1,363 +0,0 @@
-package service
-
-import (
-	"context"
-	"encoding/json"
-	"errors"
-	"fmt"
-	"log/slog"
-	"os"
-	"time"
-
-	"github.com/google/uuid"
-	"go.opentelemetry.io/otel/trace"
-	"gorm.io/gorm"
-	"gorm.io/gorm/clause"
-
-	"github.com/pocket-id/pocket-id/backend/internal/model"
-	"github.com/pocket-id/pocket-id/backend/internal/tracing"
-)
-
-var (
-	ErrLockUnavailable = errors.New("lock is already held by another process")
-	ErrLockLost        = errors.New("lock ownership lost")
-)
-
-const (
-	ttl           = 30 * time.Second
-	renewInterval = 20 * time.Second
-	renewRetries  = 3
-	lockKey       = "application_lock"
-)
-
-type AppLockService struct {
-	db        *gorm.DB
-	lockID    string
-	processID int64
-	hostID    string
-}
-
-func NewAppLockService(db *gorm.DB) *AppLockService {
-	host, err := os.Hostname()
-	if err != nil || host == "" {
-		host = "unknown-host"
-	}
-
-	return &AppLockService{
-		db:        db,
-		processID: int64(os.Getpid()),
-		hostID:    host,
-		lockID:    uuid.NewString(),
-	}
-}
-
-type lockValue struct {
-	ProcessID int64  `json:"process_id"`
-	HostID    string `json:"host_id"`
-	LockID    string `json:"lock_id"`
-	ExpiresAt int64  `json:"expires_at"`
-}
-
-func (lv *lockValue) Marshal() (string, error) {
-	data, err := json.Marshal(lv)
-	if err != nil {
-		return "", err
-	}
-	return string(data), nil
-}
-
-func (lv *lockValue) Unmarshal(raw string) error {
-	if raw == "" {
-		return nil
-	}
-	return json.Unmarshal([]byte(raw), lv)
-}
-
-// Acquire obtains the lock. When force is true, the lock is stolen from any existing owner.
-// If the lock is forcefully acquired, it blocks until the previous lock has expired.
-func (s *AppLockService) Acquire(ctx context.Context, force bool) (waitUntil time.Time, err error) {
-	ctx, span := tracing.Start(ctx, "pocketid.applock.acquire", trace.WithSpanKind(trace.SpanKindInternal))
-	waitUntil, err = s.acquire(ctx, force)
-	if err != nil {
-		tracing.End(span, err)
-		return waitUntil, err
-	}
-
-	span.End()
-	return waitUntil, nil
-}
-
-func (s *AppLockService) acquire(ctx context.Context, force bool) (waitUntil time.Time, err error) {
-	tx := s.db.WithContext(ctx).Begin()
-	err = tx.Error
-	if err != nil {
-		return time.Time{}, fmt.Errorf("begin lock transaction: %w", err)
-	}
-	defer tx.Rollback()
-
-	var prevLockRaw string
-	err = tx.
-		WithContext(ctx).
-		Model(&model.KV{}).
-		Where("key = ?", lockKey).
-		Clauses(clause.Locking{Strength: "UPDATE"}).
-		Select("value").
-		Scan(&prevLockRaw).
-		Error
-	if err != nil {
-		return time.Time{}, fmt.Errorf("query existing lock: %w", err)
-	}
-
-	var prevLock lockValue
-	if prevLockRaw != "" {
-		err = prevLock.Unmarshal(prevLockRaw)
-		if err != nil {
-			return time.Time{}, fmt.Errorf("decode existing lock value: %w", err)
-		}
-	}
-
-	now := time.Now()
-	nowUnix := now.Unix()
-
-	value := lockValue{
-		ProcessID: s.processID,
-		HostID:    s.hostID,
-		LockID:    s.lockID,
-		ExpiresAt: now.Add(ttl).Unix(),
-	}
-	raw, err := value.Marshal()
-	if err != nil {
-		return time.Time{}, fmt.Errorf("encode lock value: %w", err)
-	}
-
-	var query string
-	switch s.db.Name() {
-	case "sqlite":
-		query = `
-			INSERT INTO kv (key, value)
-			VALUES (?, ?)
-			ON CONFLICT(key) DO UPDATE SET 
-				value = excluded.value
-			WHERE (json_extract(kv.value, '$.expires_at') < ?) OR ?
-		`
-	case "postgres":
-		query = `
-			INSERT INTO kv (key, value)
-			VALUES ($1, $2)
-			ON CONFLICT(key) DO UPDATE SET 
-				value = excluded.value
-			WHERE ((kv.value::json->>'expires_at')::bigint < $3) OR ($4::boolean IS TRUE)
-		`
-	default:
-		return time.Time{}, fmt.Errorf("unsupported database dialect: %s", s.db.Name())
-	}
-
-	res := tx.WithContext(ctx).Exec(query, lockKey, raw, nowUnix, force)
-	if res.Error != nil {
-		return time.Time{}, fmt.Errorf("lock acquisition failed: %w", res.Error)
-	}
-
-	err = tx.Commit().Error
-	if err != nil {
-		return time.Time{}, fmt.Errorf("commit lock acquisition: %w", err)
-	}
-
-	// If there is a lock that is not expired and force is false, no rows will be affected
-	if res.RowsAffected == 0 {
-		err = ErrLockUnavailable
-		return time.Time{}, err
-	}
-
-	if force && prevLock.ExpiresAt > nowUnix && prevLock.LockID != s.lockID {
-		waitUntil = time.Unix(prevLock.ExpiresAt, 0)
-	}
-
-	attrs := []any{
-		slog.Int64("process_id", s.processID),
-		slog.String("host_id", s.hostID),
-	}
-	wait := time.Until(waitUntil)
-	if wait > 0 {
-		attrs = append(attrs, slog.Duration("wait_before_proceeding", wait))
-	}
-	slog.InfoContext(ctx, "Acquired application lock", attrs...)
-
-	return waitUntil, nil
-}
-
-// RunRenewal keeps renewing the lock until the context is canceled.
-func (s *AppLockService) RunRenewal(ctx context.Context) error {
-	ticker := time.NewTicker(renewInterval)
-	defer ticker.Stop()
-
-	for {
-		select {
-		case <-ctx.Done():
-			return nil
-		case <-ticker.C:
-			renewCtx, span := tracing.Start(ctx, "pocketid.applock.renew", trace.WithSpanKind(trace.SpanKindInternal))
-			err := s.renew(renewCtx)
-			tracing.End(span, err)
-			if err != nil {
-				return fmt.Errorf("renew lock: %w", err)
-			}
-		}
-	}
-}
-
-// Release releases the lock if it is held by this process.
-func (s *AppLockService) Release(ctx context.Context) (err error) {
-	ctx, span := tracing.Start(ctx, "pocketid.applock.release", trace.WithSpanKind(trace.SpanKindInternal))
-	err = s.release(ctx)
-	if err != nil {
-		tracing.End(span, err)
-		return err
-	}
-
-	span.End()
-	return nil
-}
-
-func (s *AppLockService) release(ctx context.Context) error {
-	db, err := s.db.DB()
-	if err != nil {
-		return fmt.Errorf("failed to get DB connection: %w", err)
-	}
-
-	var query string
-	switch s.db.Name() {
-	case "sqlite":
-		query = `
-DELETE FROM kv
-WHERE key = ?
-  AND json_extract(value, '$.lock_id') = ?
-`
-	case "postgres":
-		query = `
-DELETE FROM kv
-WHERE key = $1
-  AND value::json->>'lock_id' = $2
-`
-	default:
-		return fmt.Errorf("unsupported database dialect: %s", s.db.Name())
-	}
-
-	opCtx, cancel := context.WithTimeout(ctx, 3*time.Second)
-	defer cancel()
-
-	res, err := db.ExecContext(opCtx, query, lockKey, s.lockID)
-	if err != nil {
-		return fmt.Errorf("release lock failed: %w", err)
-	}
-
-	count, err := res.RowsAffected()
-	if err != nil {
-		return fmt.Errorf("failed to count affected rows: %w", err)
-	}
-
-	if count == 0 {
-		// Treat this as a warning, not an error
-		slog.WarnContext(ctx, "Application lock not held by this process, cannot release",
-			slog.Int64("process_id", s.processID),
-			slog.String("host_id", s.hostID),
-		)
-		return nil
-	}
-
-	slog.InfoContext(ctx, "Released application lock",
-		slog.Int64("process_id", s.processID),
-		slog.String("host_id", s.hostID),
-	)
-	return nil
-}
-
-// renew tries to renew the lock, retrying up to renewRetries times (sleeping 1s between attempts).
-func (s *AppLockService) renew(ctx context.Context) error {
-	db, err := s.db.DB()
-	if err != nil {
-		return fmt.Errorf("failed to get DB connection: %w", err)
-	}
-
-	var lastErr error
-	for attempt := 1; attempt <= renewRetries; attempt++ {
-		now := time.Now()
-		nowUnix := now.Unix()
-		expiresAt := now.Add(ttl).Unix()
-
-		value := lockValue{
-			LockID:    s.lockID,
-			ProcessID: s.processID,
-			HostID:    s.hostID,
-			ExpiresAt: expiresAt,
-		}
-		raw, err := value.Marshal()
-		if err != nil {
-			return fmt.Errorf("encode lock value: %w", err)
-		}
-
-		var query string
-		switch s.db.Name() {
-		case "sqlite":
-			query = `
-UPDATE kv
-SET value = ?
-WHERE key = ?
-  AND json_extract(value, '$.lock_id') = ?
-  AND json_extract(value, '$.expires_at') > ?
-`
-		case "postgres":
-			query = `
-UPDATE kv
-SET value = $1
-WHERE key = $2
-  AND value::json->>'lock_id' = $3
-  AND ((value::json->>'expires_at')::bigint > $4)
-`
-		default:
-			return fmt.Errorf("unsupported database dialect: %s", s.db.Name())
-		}
-
-		opCtx, cancel := context.WithTimeout(ctx, 3*time.Second)
-		res, err := db.ExecContext(opCtx, query, raw, lockKey, s.lockID, nowUnix)
-		cancel()
-
-		// Query succeeded, but may have updated 0 rows
-		if err == nil {
-			count, err := res.RowsAffected()
-			if err != nil {
-				return fmt.Errorf("failed to count affected rows: %w", err)
-			}
-
-			// If no rows were updated, we lost the lock
-			if count == 0 {
-				return ErrLockLost
-			}
-
-			// All good
-			slog.DebugContext(ctx, "Renewed application lock",
-				slog.Int64("process_id", s.processID),
-				slog.String("host_id", s.hostID),
-				slog.Duration("duration", time.Since(now)),
-			)
-			return nil
-		}
-
-		// If we're here, we have an error that can be retried
-		slog.DebugContext(ctx, "Application lock renewal attempt failed",
-			slog.Any("error", err),
-			slog.Duration("duration", time.Since(now)),
-		)
-		lastErr = fmt.Errorf("lock renewal failed: %w", err)
-
-		// Wait before next attempt or cancel if context is done
-		if attempt < renewRetries {
-			select {
-			case <-ctx.Done():
-				return ctx.Err()
-			case <-time.After(1 * time.Second):
-			}
-		}
-	}
-
-	return lastErr
-}

backend/internal/service/app_lock_service_test.go

@@ -1,300 +0,0 @@
-package service
-
-import (
-	"context"
-	"testing"
-	"time"
-
-	"github.com/stretchr/testify/require"
-	"gorm.io/gorm"
-
-	"github.com/pocket-id/pocket-id/backend/internal/model"
-	testutils "github.com/pocket-id/pocket-id/backend/internal/utils/testing"
-)
-
-func newTestAppLockService(t *testing.T, db *gorm.DB) *AppLockService {
-	t.Helper()
-
-	return &AppLockService{
-		db:        db,
-		processID: 1,
-		hostID:    "test-host",
-		lockID:    "a13c7673-c7ae-49f1-9112-2cd2d0d4b0c1",
-	}
-}
-
-func insertLock(t *testing.T, db *gorm.DB, value lockValue) {
-	t.Helper()
-
-	raw, err := value.Marshal()
-	require.NoError(t, err)
-
-	err = db.Create(&model.KV{Key: lockKey, Value: &raw}).Error
-	require.NoError(t, err)
-}
-
-func readLockValue(t *testing.T, db *gorm.DB) lockValue {
-	t.Helper()
-
-	var row model.KV
-	err := db.Take(&row, "key = ?", lockKey).Error
-	require.NoError(t, err)
-
-	require.NotNil(t, row.Value)
-
-	var value lockValue
-	err = value.Unmarshal(*row.Value)
-	require.NoError(t, err)
-
-	return value
-}
-
-func lockDatabaseForWrite(t *testing.T, db *gorm.DB) *gorm.DB {
-	t.Helper()
-
-	tx := db.Begin()
-	require.NoError(t, tx.Error)
-
-	// Keep a write transaction open to block other queries.
-	err := tx.Exec(
-		`INSERT INTO kv (key, value) VALUES (?, ?) ON CONFLICT(key) DO NOTHING`,
-		lockKey,
-		`{"expires_at":0}`,
-	).Error
-	require.NoError(t, err)
-
-	return tx
-}
-
-func TestAppLockServiceAcquire(t *testing.T) {
-	t.Run("creates new lock when none exists", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		_, err := service.Acquire(context.Background(), false)
-		require.NoError(t, err)
-
-		stored := readLockValue(t, db)
-		require.Equal(t, service.processID, stored.ProcessID)
-		require.Equal(t, service.hostID, stored.HostID)
-		require.Greater(t, stored.ExpiresAt, time.Now().Unix())
-	})
-
-	t.Run("returns ErrLockUnavailable when lock held by another process", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		existing := lockValue{
-			ProcessID: 99,
-			HostID:    "other-host",
-			ExpiresAt: time.Now().Add(ttl).Unix(),
-		}
-		insertLock(t, db, existing)
-
-		_, err := service.Acquire(context.Background(), false)
-		require.ErrorIs(t, err, ErrLockUnavailable)
-
-		current := readLockValue(t, db)
-		require.Equal(t, existing, current)
-	})
-
-	t.Run("force acquisition steals lock", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		insertLock(t, db, lockValue{
-			ProcessID: 99,
-			HostID:    "other-host",
-			ExpiresAt: time.Now().Unix(),
-		})
-
-		_, err := service.Acquire(context.Background(), true)
-		require.NoError(t, err)
-
-		stored := readLockValue(t, db)
-		require.Equal(t, service.processID, stored.ProcessID)
-		require.Equal(t, service.hostID, stored.HostID)
-		require.Greater(t, stored.ExpiresAt, time.Now().Unix())
-	})
-
-	t.Run("force acquisition returns wait duration when stealing active lock", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		existing := lockValue{
-			ProcessID: 99,
-			HostID:    "other-host",
-			LockID:    "other-lock-id",
-			ExpiresAt: time.Now().Add(ttl).Unix(),
-		}
-		insertLock(t, db, existing)
-
-		waitUntil, err := service.Acquire(context.Background(), true)
-		require.NoError(t, err)
-		require.WithinDuration(t, time.Unix(existing.ExpiresAt, 0), waitUntil, time.Second)
-	})
-
-	t.Run("force acquisition does not wait when lock id is unchanged", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		insertLock(t, db, lockValue{
-			ProcessID: 99,
-			HostID:    "other-host",
-			LockID:    service.lockID,
-			ExpiresAt: time.Now().Add(ttl).Unix(),
-		})
-
-		waitUntil, err := service.Acquire(context.Background(), true)
-		require.NoError(t, err)
-		require.True(t, waitUntil.IsZero())
-	})
-
-	t.Run("returns error when existing lock value is invalid JSON", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		err := db.Create(&model.KV{Key: lockKey, Value: new("this-is-not-json")}).Error
-		require.NoError(t, err)
-
-		_, err = service.Acquire(context.Background(), false)
-		require.ErrorContains(t, err, "decode existing lock value")
-	})
-
-	t.Run("returns context deadline exceeded when database is locked", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		tx := lockDatabaseForWrite(t, db)
-		defer tx.Rollback()
-
-		ctx, cancel := context.WithTimeout(context.Background(), 150*time.Millisecond)
-		defer cancel()
-
-		_, err := service.Acquire(ctx, false)
-		require.ErrorIs(t, err, context.DeadlineExceeded)
-		require.ErrorContains(t, err, "begin lock transaction")
-	})
-}
-
-func TestAppLockServiceRelease(t *testing.T) {
-	t.Run("removes owned lock", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		_, err := service.Acquire(context.Background(), false)
-		require.NoError(t, err)
-
-		err = service.Release(context.Background())
-		require.NoError(t, err)
-
-		var row model.KV
-		err = db.Take(&row, "key = ?", lockKey).Error
-		require.ErrorIs(t, err, gorm.ErrRecordNotFound)
-	})
-
-	t.Run("ignores lock held by another owner", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		existing := lockValue{
-			ProcessID: 2,
-			HostID:    "other-host",
-			ExpiresAt: time.Now().Add(ttl).Unix(),
-		}
-		insertLock(t, db, existing)
-
-		err := service.Release(context.Background())
-		require.NoError(t, err)
-
-		stored := readLockValue(t, db)
-		require.Equal(t, existing, stored)
-	})
-
-	t.Run("returns context deadline exceeded when database is locked", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		_, err := service.Acquire(context.Background(), false)
-		require.NoError(t, err)
-
-		tx := lockDatabaseForWrite(t, db)
-		defer tx.Rollback()
-
-		ctx, cancel := context.WithTimeout(context.Background(), 150*time.Millisecond)
-		defer cancel()
-
-		err = service.Release(ctx)
-		require.ErrorIs(t, err, context.DeadlineExceeded)
-		require.ErrorContains(t, err, "release lock failed")
-	})
-}
-
-func TestAppLockServiceRenew(t *testing.T) {
-	t.Run("extends expiration when lock is still owned", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		_, err := service.Acquire(context.Background(), false)
-		require.NoError(t, err)
-
-		before := readLockValue(t, db)
-
-		err = service.renew(context.Background())
-		require.NoError(t, err)
-
-		after := readLockValue(t, db)
-		require.Equal(t, service.processID, after.ProcessID)
-		require.Equal(t, service.hostID, after.HostID)
-		require.GreaterOrEqual(t, after.ExpiresAt, before.ExpiresAt)
-	})
-
-	t.Run("returns ErrLockLost when lock is missing", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		err := service.renew(context.Background())
-		require.ErrorIs(t, err, ErrLockLost)
-	})
-
-	t.Run("returns ErrLockLost when ownership changed", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		_, err := service.Acquire(context.Background(), false)
-		require.NoError(t, err)
-
-		// Simulate a different process taking the lock.
-		newOwner := lockValue{
-			ProcessID: 9,
-			HostID:    "stolen-host",
-			ExpiresAt: time.Now().Add(ttl).Unix(),
-		}
-		raw, marshalErr := newOwner.Marshal()
-		require.NoError(t, marshalErr)
-		updateErr := db.Model(&model.KV{}).
-			Where("key = ?", lockKey).
-			Update("value", raw).Error
-		require.NoError(t, updateErr)
-
-		err = service.renew(context.Background())
-		require.ErrorIs(t, err, ErrLockLost)
-	})
-
-	t.Run("returns context deadline exceeded when database is locked", func(t *testing.T) {
-		db := testutils.NewDatabaseForTest(t)
-		service := newTestAppLockService(t, db)
-
-		_, err := service.Acquire(context.Background(), false)
-		require.NoError(t, err)
-
-		tx := lockDatabaseForWrite(t, db)
-		defer tx.Rollback()
-
-		ctx, cancel := context.WithTimeout(context.Background(), 150*time.Millisecond)
-		defer cancel()
-
-		err = service.renew(ctx)
-		require.ErrorIs(t, err, context.DeadlineExceeded)
-	})
-}

backend/internal/service/e2etest_service.go

@@ -45,7 +45,6 @@ type TestService struct {
 	appConfigService *appconfig.AppConfigService
 	ldapService      *LdapService
 	fileStorage      storage.FileStorage
-	appLockService   *AppLockService
 	externalIdPKey   jwk.Key
 }
 
@@ -56,13 +55,12 @@ const (
 	e2eRefreshTokenExpiredFixtureToken = "X4vqwtRyCUaq51UafHea4Fsg8Km6CAns6vp3tuX4"
 )
 
-func NewTestService(db *gorm.DB, appConfigService *appconfig.AppConfigService, jwtService *JwtService, ldapService *LdapService, appLockService *AppLockService, fileStorage storage.FileStorage) (*TestService, error) {
+func NewTestService(db *gorm.DB, appConfigService *appconfig.AppConfigService, jwtService *JwtService, ldapService *LdapService, fileStorage storage.FileStorage) (*TestService, error) {
 	s := &TestService{
 		db:               db,
 		appConfigService: appConfigService,
 		jwtService:       jwtService,
 		ldapService:      ldapService,
-		appLockService:   appLockService,
 		fileStorage:      fileStorage,
 	}
 	err := s.initExternalIdP()
@@ -220,6 +218,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 +283,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)),
@@ -656,11 +712,6 @@ func (s *TestService) ResetAppConfig(ctx context.Context) error {
 	return nil
 }
 
-func (s *TestService) ResetLock(ctx context.Context) error {
-	_, err := s.appLockService.Acquire(ctx, true)
-	return err
-}
-
 // SyncLdap triggers an LDAP synchronization
 func (s *TestService) SyncLdap(ctx context.Context) error {
 	dbConfig, err := s.appConfigService.GetConfig(ctx)

backend/internal/service/export_service.go

@@ -11,6 +11,7 @@ import (
 
 	"gorm.io/gorm"
 
+	"github.com/pocket-id/pocket-id/backend/internal/haconfig"
 	datatype "github.com/pocket-id/pocket-id/backend/internal/model/types"
 	"github.com/pocket-id/pocket-id/backend/internal/storage"
 	"github.com/pocket-id/pocket-id/backend/internal/utils"
@@ -109,10 +110,10 @@ func (s *ExportService) dumpTable(table string, types utils.DBSchemaTableTypes,
 			rowMap[col] = vals[i]
 		}
 
-		// Skip the app lock row in the kv table
+		// Skip the HA mode row in the kv table: it records the mode the cluster was created with, and is re-established from config (not the export) when the data is imported into a new database
 		if table == "kv" {
 			keyPtr, ok := rowMap["key"].(*string)
-			if ok && keyPtr != nil && *keyPtr == lockKey {
+			if ok && keyPtr != nil && *keyPtr == haconfig.KVKey {
 				continue
 			}
 		}

backend/internal/service/export_service_test.go

@@ -7,7 +7,10 @@ import (
 
 	"github.com/stretchr/testify/require"
 
+	"github.com/pocket-id/pocket-id/backend/internal/haconfig"
+	"github.com/pocket-id/pocket-id/backend/internal/model"
 	"github.com/pocket-id/pocket-id/backend/internal/utils"
+	testutils "github.com/pocket-id/pocket-id/backend/internal/utils/testing"
 )
 
 // TestExportExcludesActorHostTables verifies that an export does not dump the actor host's own "francis_" tables
@@ -28,3 +31,29 @@ func TestExportExcludesActorHostTables(t *testing.T) {
 		require.Falsef(t, strings.HasPrefix(table, "francis_"), "export must not include actor host table %q", table)
 	}
 }
+
+// TestExportOmitsHAModeKV verifies that an export omits the "ha_enabled" row from the kv table
+// The HA mode is a per-cluster setting re-established from config on import, so carrying it in the export would pin an imported cluster to the exporter's mode
+func TestExportOmitsHAModeKV(t *testing.T) {
+	db := testutils.NewDatabaseForTest(t)
+
+	// Seed the kv table with the HA mode row (must be omitted) and another row (must be kept)
+	haValue := "true"
+	instanceValue := "instance-123"
+	require.NoError(t, db.Create(&model.KV{Key: haconfig.KVKey, Value: &haValue}).Error)
+	require.NoError(t, db.Create(&model.KV{Key: "instance_id", Value: &instanceValue}).Error)
+
+	export, err := NewExportService(db, nil).extractDatabase()
+	require.NoError(t, err)
+
+	keys := make(map[string]bool)
+	for _, row := range export.Tables["kv"] {
+		keyPtr, ok := row["key"].(*string)
+		require.True(t, ok)
+		require.NotNil(t, keyPtr)
+		keys[*keyPtr] = true
+	}
+
+	require.NotContains(t, keys, haconfig.KVKey, "export must omit the HA mode row")
+	require.Contains(t, keys, "instance_id", "export must keep other kv rows")
+}

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');