Session-based Auth System | Dataford Interview Questions

Implement a session-based authentication system supporting "generate", "renew" and "count" queries

Asked in the Coding stage. The task is open-ended and evaluates clean API design, boundary conditions, tests, and performance. Create a PostgreSQL function that handles all three operations using the supplied authentication tables.

Output

Return operation, token, and active_sessions.
Return one row per function call. token is populated for a successful generation or renewal, and otherwise is NULL.
Count only sessions for the requested user that are not revoked and expire after the supplied timestamp.
Treat a session expiring exactly at the supplied timestamp as inactive.

Schema

auth_users

Column	Type	Description
user_idPK	INTEGER	Unique application user identifier
email	VARCHAR(255)	User email address

auth_sessions

Column	Type	Description
session_idPK	BIGINT	Unique session identifier
token	VARCHAR(64)	Opaque authentication token
user_id	INTEGER	Referenced application user
issued_at	TIMESTAMPTZ	Session creation timestamp
expires_at	TIMESTAMPTZ	Timestamp after which the session is inactive
revoked_at	TIMESTAMPTZ	Revocation timestamp, or NULL when not revoked

Your question is Session-based Auth System. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem