Fix api_keys time columns: BIGINT epoch, not TIMESTAMPTZ

#26 in Riparion/arium — merged 2026-06-10

Problem

api_keys (migration 0007) declared created_at / last_used_at / revoked_at as TIMESTAMPTZ (Postgres) / TIMESTAMP (SQLite), but the token code treats them as i64 unix-epoch seconds everywhere — unix_now() on write, i64 columns + format_unix_date on read — the same convention every other arium *_at column already uses (expires_at, occurred_at, mfa_enabled_at, deleted_at, …).

On Postgres this broke the entire token lifecycle at runtime:

• create_for_user bound a bigint into a timestamptz column → column "created_at" is of type timestamp with time zone but expression is of type bigint
• list_for_user couldn't decode timestamptz into i64

SQLite slipped through because its loose column affinity stored the bound i64 as-is, and CI never exercises the Postgres query path (migrations apply, but no queries run), so it went unnoticed until a token mint was attempted against a real Postgres deployment.

Fix

• New migration 0009_api_keys_epoch converts the three columns to BIGINT. Postgres uses ALTER … TYPE BIGINT USING EXTRACT(EPOCH FROM …)::BIGINT, preserving each existing row's instant; SQLite normalizes any legacy TEXT timestamps (written by the old CURRENT_TIMESTAMP bump) to epoch integers. Both are no-ops on fresh DBs.
• authenticate_token now writes last_used_at = unix_now() instead of SQL CURRENT_TIMESTAMP, so writes match the i64 reads on both backends (this was the one remaining non-epoch write).
• unix_now() made pub(crate) so api_key can share it.
• Regression test used_token_lists_with_populated_last_used_at: authenticates a token (triggering the last_used_at bump) then asserts list_for_user returns it with a populated last_used_at — the read path that previously failed to decode.

Validation

• cargo test -p arium --features sqlite,tokens,mfa --test api_tokens → 7/7 pass.
• Compiles clean on both sqlite and postgres; cargo fmt --check and lib clippy clean.
• Migration 0009 applied against a live QA Postgres (real rows) inside a rolled-back transaction: columns become bigint, existing rows convert to correct epochs.

🤖 Generated with Claude Code