Explicit encrypted SQLite session storage

quadkit.auth.storage.SQLSessionStore implements the existing SessionStore interface through DatabaseProviderProtocol. It has no SQL-driver import and adds no automatic schema creation, key generation, routes, or cookie policy. Its parameter syntax and acceptance tests currently target SQLite only.

Composition and migration

The host must explicitly apply SQLITE_SESSION_SCHEMA and SQLITE_SESSION_INDEX from quadkit.auth.storage.sql_sessions in its own reviewed migration. They create auth_sessions and its owner/activity/expiry index. Construction and reads never initialize an absent table or fall back to memory. Do not run these DDL statements on every startup or against an existing table.

Create a Fernet key once through your secret-provisioning system. Store it separately from the database, with restrictive permissions, and retain it across restarts. Never use a JWT string as the key, regenerate the key on boot, or put it in argv, logs, source control, or an API response. Losing the key denies access to existing sessions. Wrong-key and corrupt-record reads fail closed.

from quadkit.auth.storage import SQLSessionStore
from quadkit.auth.session.manager import SessionManagerImpl

# db is a connected host-owned DatabaseProviderProtocol.
# key_bytes is an existing, securely provisioned Fernet key.
store = SQLSessionStore(db, encryption_key=key_bytes)
manager = SessionManagerImpl(session_store=store)

For a DI host, the preferred composition is explicit:

from quadkit.auth.module import AuthModule

module = AuthModule.configure(auth_config, user_store=users, session_store=store)

The store survives eager/deferred bundle composition and is passed to the shared session manager. An explicit store takes precedence over an optional SessionStore DI binding. Empty/falsey stores are retained (only None means absent); invalid backends fail instead of silently selecting memory. The manager is built once under concurrent concrete/protocol resolution, even when optional store lookup awaits I/O. Failed lookup is not cached as an in-memory success and may be retried. A host that supplies neither an explicit store nor a binding still gets the existing development-only in-memory default.

Assert the resolved manager uses the exact store. Supplying this option creates neither schema nor public session issuance routes, and does not replace a manager that has already been constructed in some other application. The host retains database lifecycle and migration ownership. Key rotation/re-encryption is not automated by this store.

The real-app composition test uses persisted SQL users and the encrypted store, creates a session through the trusted service API, verifies cookie-authenticated GET access to the protected operator API across app restart, and rejects the same cookie immediately after revocation through a separate connection. This is not an exposed cookie-login/logout flow or a CSRF acceptance claim. The named QuadKit Fixtures persistent bearer host still does not pass this option.

Stored data and retention

• A SHA-256 session-ID digest is the lookup primary key. Session identifiers are never stored in plaintext.
• Fernet authenticated encryption protects the session record, including its raw identifier, device details, IP, user-agent, fingerprints, and associated JTI.
• Owner ID, expiry and activity state remain plaintext indexed metadata.
• Record size is bounded at 64 KiB before encryption. Expiry must be explicit and timezone-aware; naive/missing expiry is rejected.
• The session manager now uses sha256:<digest> correlation identifiers in session logs and audit resource IDs, not bearer-capable session identifiers. Audit consumers must account for this identifier-format change. This is not a claim that arbitrary downstream middleware or host logs are sanitized.

Revocation sets is_active=0 and retains the row as an ID-reuse tombstone. A stale activity/MFA update cannot recreate it or reactivate it. create is insert-only, not an upsert. Expired rows are denied and cannot be revived by update. There is deliberately no automatic purge: deployments must define retention, backup access and tombstone removal policy before enabling this store. Backups still contain sensitive data and must be encrypted/access-controlled. Restoring an older backup can restore revoked sessions; reconcile revocations or invalidate all sessions during recovery. This change is not power-loss or rollback-proof.

Concurrency and logout scope

Updates compare the previous encrypted payload in a conditional SQL update. On a competing write they reread and retry up to eight times, retaining concurrent MFA/activity changes. The update also requires the row still be active and unexpired. Exhausted contention or database failure raises, never silently succeeds using in-memory state.

delete_all_for_user revokes existing rows for that owner in one statement. It does not block a fresh login or implement refresh-token family lineage. Already-authorized requests may finish. The manager’s configured maximum-session count uses a separate read/evict/create sequence; it is not a distributed, atomic login quota. The encrypted store does not change that guarantee.

Acceptance and non-goals

Real SQLite tests cover separate connections, a fresh child process reading persisted sessions/revocations, framework manager create/validate/revoke, stale update after logout, concurrent MFA/activity merge, owner-scoped logout, expiry, wrong key/corrupt metadata, missing schema and encrypted-at-rest sentinel checks.

The QuadKit Fixtures persistent bearer profile does not register this store or change its v2 schema. Cookie authentication, CSRF/session fixation handling, public refresh/client retry policy, family-scoped recovery and fleet acceptance remain separate work. A durable store alone is not a complete browser login flow.