71 lines
3.3 KiB
SQL
71 lines
3.3 KiB
SQL
|
|
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.';
|