CREATE TABLE application_sessions (
    session_id SERIAL NOT NULL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(user_id),
    application_id TEXT NOT NULL,

    started_at_utc TIMESTAMP NOT NULL,
    last_seen_at_utc TIMESTAMP NOT NULL
);

CREATE INDEX ON application_sessions (user_id);

COMMENT ON TABLE application_sessions IS 'Stores the sessions that involve an OIDC application and a user.';

COMMENT ON COLUMN application_sessions.user_id IS 'UUID of the user.';

COMMENT ON COLUMN application_sessions.application_id IS 'client_id of the OpenID Connect application for which this session is issued.';

COMMENT ON COLUMN application_sessions.started_at_utc IS 'Time when this session was started.';

COMMENT ON COLUMN application_sessions.last_seen_at_utc IS 'Time when we last heard of activity in this session. Does not mean the user has not been using the application since.';


CREATE TABLE application_access_tokens (
    access_token_hash BYTEA NOT NULL PRIMARY KEY,
    session_id INTEGER NOT NULL REFERENCES application_sessions(session_id) ON DELETE CASCADE,
    issued_at_utc TIMESTAMP NOT NULL,

    expires_at_utc TIMESTAMP NOT NULL,

    created_from_refresh_token_hash BYTEA UNIQUE -- intentionally nullable
);

CREATE INDEX ON application_access_tokens (session_id);

COMMENT ON TABLE application_access_tokens IS 'Stores the hash of access tokens that have been issued to applications on the user''s behalf.';

COMMENT ON COLUMN application_access_tokens.access_token_hash IS 'BLAKE2s-256 hash of the refresh token.';

COMMENT ON COLUMN application_access_tokens.session_id IS 'ID of the session. This access token will cease to exist if the session is deleted.';

COMMENT ON COLUMN application_access_tokens.created_from_refresh_token_hash IS 'If set, this is the hash of the refresh token that was used to create this access token. This lets us track acknowledgement of a refresh.';

COMMENT ON COLUMN application_access_tokens.issued_at_utc IS 'Time when this access token was issued.';

COMMENT ON COLUMN application_access_tokens.expires_at_utc IS 'Time when this access token expires.';


CREATE TABLE application_refresh_tokens (
    refresh_token_hash BYTEA NOT NULL PRIMARY KEY,
    session_id INTEGER NOT NULL REFERENCES application_sessions(session_id) ON DELETE CASCADE,
    created_from_refresh_token_hash BYTEA UNIQUE, -- intentionally nullable

    issued_at_utc TIMESTAMP NOT NULL,
    expires_at_utc TIMESTAMP NOT NULL
);

CREATE INDEX ON application_refresh_tokens (session_id);

COMMENT ON TABLE application_refresh_tokens IS 'Stores the hash of refresh tokens that have been issued to applications on the user''s behalf.';

COMMENT ON COLUMN application_refresh_tokens.refresh_token_hash IS 'BLAKE2s-256 hash of the refresh token.';

COMMENT ON COLUMN application_refresh_tokens.session_id IS 'ID of the session. This refresh token will cease to exist if the session is deleted.';

COMMENT ON COLUMN application_refresh_tokens.created_from_refresh_token_hash IS 'If set, this is the hash of the refresh token that was used to create this refresh token. This lets us track acknowledgement of a refresh.';

COMMENT ON COLUMN application_refresh_tokens.issued_at_utc IS 'Time when this refresh token was issued.';

COMMENT ON COLUMN application_refresh_tokens.expires_at_utc IS 'Time when this refresh token expires.';