53 lines
2.6 KiB
SQL
53 lines
2.6 KiB
SQL
-- Sessions. Parameters are NAMED with sqlc.arg rather than left as $n, so that the generated params
|
|
-- struct carries field names: two adjacent time.Time arguments at a call site are exactly the shape
|
|
-- that transposes silently, and a named field is the cheapest defence against it.
|
|
|
|
-- name: LookupSession :one
|
|
-- Expiry and revocation are clauses of THIS query, not checks a caller could forget: a row that
|
|
-- comes back is live by construction.
|
|
select user_id, idle_expires_at, absolute_expires_at
|
|
from sessions
|
|
where token_sha256 = sqlc.arg(token_sha256)
|
|
and revoked_at is null
|
|
and idle_expires_at > sqlc.arg(now)
|
|
and absolute_expires_at > sqlc.arg(now);
|
|
|
|
-- name: TouchSession :exec
|
|
-- Slides the idle window and never moves the absolute expiry — that is the point of having two
|
|
-- clocks. Its WHERE matches LookupSession's, idle clause included (PD-4).
|
|
update sessions
|
|
set last_used_at = sqlc.arg(now),
|
|
idle_expires_at = least(sqlc.arg(idle_deadline)::timestamptz, absolute_expires_at)
|
|
where token_sha256 = sqlc.arg(token_sha256)
|
|
and revoked_at is null
|
|
and idle_expires_at > sqlc.arg(now)
|
|
and absolute_expires_at > sqlc.arg(now);
|
|
|
|
-- name: SessionStillLive :one
|
|
-- ⚠ THE IDLE CLAUSE IS DELIBERATELY ABSENT. The idle window slides on a REQUEST, and the event
|
|
-- stream is ONE request that lives for hours, so a stream cannot slide its own window and asking the
|
|
-- idle question here would end the stream of a user who is sitting and watching it.
|
|
select 1
|
|
from sessions
|
|
where token_sha256 = sqlc.arg(token_sha256)
|
|
and revoked_at is null
|
|
and absolute_expires_at > sqlc.arg(now);
|
|
|
|
-- name: CreateSession :exec
|
|
insert into sessions (token_sha256, user_id, created_at, last_used_at, idle_expires_at, absolute_expires_at)
|
|
values (sqlc.arg(token_sha256), sqlc.arg(user_id), sqlc.arg(now), sqlc.arg(now),
|
|
sqlc.arg(idle_expires_at), sqlc.arg(absolute_expires_at));
|
|
|
|
-- name: RevokeSession :exec
|
|
-- The cast is not decoration: revoked_at is NULLABLE, so without it sqlc types the parameter from
|
|
-- the column and hands the caller a *time.Time for a value that is never absent.
|
|
update sessions set revoked_at = sqlc.arg(now)::timestamptz
|
|
where token_sha256 = sqlc.arg(token_sha256) and revoked_at is null;
|
|
|
|
-- name: SweepSessions :execrows
|
|
-- Deletes rows nothing can authenticate with again: past either expiry, or revoked. A revoked row is
|
|
-- the one a compromised account most wants gone. The audit lives in the login journal, not here.
|
|
delete from sessions
|
|
where absolute_expires_at <= sqlc.arg(now)
|
|
or idle_expires_at <= sqlc.arg(now)
|
|
or revoked_at is not null;
|