twofauth-rs/compatibility/database-portability.md

Database portability audit

The current runtime supports SQLite. PostgreSQL, MySQL/MariaDB and SQL Server are upstream deployment options, but are not implemented by this Rust backend. Changing the connection URL or enabling SQLx features does not establish compatibility.

Implementation boundary

AppState stores SqlitePool; handlers expose SqliteConnection and SqliteRow, and use direct SQLx queries throughout. Introduce a backend-neutral pool/transaction/query boundary with typed bound values and normalized results. Keep native SQLx SQLite/PostgreSQL/MySQL drivers; SQLx 0.8 has no SQL Server driver, so SQL Server needs a separate driver such as Tiberius. Cargo.lock contains SQLx PostgreSQL and MySQL 0.8.6 packages; Cargo.toml currently enables only SQLite. A lockfile entry does not mean a driver is enabled or tested.

Use explicit backend implementations for insert-and-return-ID, upsert, atomic consume-once records and rate-limit increments. Generic placeholder replacement cannot translate these semantics or normalize returned native types. Preserve transaction affinity when retrieving generated IDs.

Current form	PostgreSQL	MySQL/MariaDB	SQL Server
? binds	$1, $2, etc.	?	@P1, @P2, etc.
last_insert_rowid()	INSERT ... RETURNING id	insert result ID	OUTPUT INSERTED.id
ON CONFLICT ...	supported with compatible targets	explicit duplicate-key update	transactional update/insert with appropriate locking
DELETE ... RETURNING	supported	locked read/delete transaction as needed by server/version	DELETE ... OUTPUT DELETED...
datetime('now', ...)	typed UTC timestamp comparisons	typed UTC timestamp comparisons	typed UTC timestamp comparisons
json_each(grant_types)	native JSON conversion or application decoding	JSON expression or application decoding	JSON expression or application decoding
INSERT OR IGNORE	conflict-targeted DO NOTHING	conflict-specific handling; avoid suppressing unrelated errors	constraint-aware transaction
nullable ip_address IS ?	IS NOT DISTINCT FROM	<=>	equality plus explicit both-null condition
LIMIT	supported	supported	TOP or ordered OFFSET/FETCH
CAST(id AS TEXT)	supported	compatible character cast	compatible character cast

Quote identifiers consistently: key, groups and window can collide with dialect keywords. SQLite PRAGMA introspection must become PostgreSQL/MySQL information-schema and SQL Server catalog queries. Rust metadata DDL also needs per-backend generated IDs, bounded indexed strings, timestamp representations and index-existence checks; SQLite TEXT PRIMARY KEY and CREATE INDEX IF NOT EXISTS are not portable.

Schema and value normalization

Keep pinned PHP migrations authoritative for application tables. Validate an offline migrated database before creating only Rust-owned metadata tables. Do not attempt to recreate production schemas from the simplified development DDL.

Laravel columns are not uniformly signed 64-bit integers. Users use big IDs; account IDs originate as unsigned integer; digits use unsigned small integer; periods use unsigned integer; counters may be unsigned big integer. PostgreSQL smallint/integer cannot simply be decoded with SQLx's i64 implementation. MySQL unsigned-bigint counters can exceed i64::MAX; reject unsupported ranges explicitly or retain u64 through the data path. SQL Server uses its own numeric and bit representations. Real PostgreSQL booleans also require boolean predicates instead of =0 and =1.

Timestamps are native database timestamp columns, although SQLite handlers currently decode them as strings. WebAuthn additionally contains UUID and JSON fields on databases that support those types. Normalize timestamps to UTC, booleans to Rust bool, integer widths explicitly, and UUID/JSON values without losing nullability. Do not convert arbitrary database errors into conflict success.

schema_validation.rs checks required SQLite application columns, primary keys, non-partial uniqueness and delete actions against the materialized pinned migration DDL. Obsolete twofaccounts.group_id/order_column and framework cache/migration tables are excluded. Laravel sessions are required only with the database session driver. This validator is a structural prerequisite, not proof of cross-database behavior.

Required interoperability evidence

For each supported engine, run pinned PHP migrations in a disposable database and seed identical synthetic fixtures. Run the differential HTTP suite against Rust, then reopen records written by Rust with PHP. Include:

• encrypted accounts and key rotation; nullable strings, booleans, timestamps and numeric boundary values;
• Passport clients, tokens and revocation; Laravel session payloads; WebAuthn UUID/JSON/key fields;
• unique usernames, group assignments, per-user account ordering and share targets;
• account/user deletion and foreign-key cascade/set-null behavior;
• simultaneous consume-once challenges, rate-limit increments, registration and HOTP generation;
• failed statements, transaction rollback and connection reuse.

A successful connection or compilation does not demonstrate migrated-schema compatibility. Each engine needs a recorded migrated schema, driver/server versions, HTTP comparison results and PHP read-back results before its support claim can change.