schema/sessions.md


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57


Sessions
back to schema /// home
How session persistence and management works under the hood. This is the internal guts of json derulo, for the client facing auth flow see auth.
Architecture
Of note : Below is partially optional in regards to persistant sessions and insane hashing idea i had at 3am, dont keep your session tokens in plaintext or unencrypted on anything else but maybe RAM (hashmaps) and you should be fine.
Token handling
When you login the server generates a UUID v4 token. The raw token is returned to you and never stored anywhere on the server. The server only keeps a SHA 256 hash of it.
When you send a request with your token to json derulo he hashes it and looks up the hash in the cache. This means even if someone gets access to the database or the servers memory they only see hashes not usable tokens.
Persistence
When persistent_sessions is enabled the server stores session hashes in the jde_sessions table. This has two parts:
Periodic flushing your hashes down the toilet (onto mariadb)
A background task should run periodically (with preferably support for custom intervals). It:
1. writes new sessions to the database
2. updates last_accessed for sessions that had activity since the last flush
3. deletes sessions that were logged out or expired
4. cleans up any really old rows as a safety net
Changes should preferably be batched so the server isnt hitting the database on every single request a billion extra times.
Startup load
On server start (including after a restart) the server should load all valid sessions from jde_sessions back into memory by JOINing with jde_users and jde_groups to reconstruct full session objects. Expired sessions should be discarded.
Pre restart flushing of toilet doo doo
When a forced restart is requested via /reload the server should flush all pending session changes to the database before re execing. Should have a timeout so a dead database doesnt  block the restart though lmao.
Session timeouts
Each session should have a timeout based on the users power level (configurable per power level). If session refresh on activity is enabled (should be by default) the timeout resets on every request.
Expired sessions should be cleaned up by the periodic background task.
Concurrent session limits
Each user has a maximum number of concurrent sessions (max_concurrent_sessions configurable per power level with a global default). When a user logs in and is at the limit the oldest session (earliest created_at) is automatically evicted.
The jde_sessions table
This table is internal only. It should never never never EVER be accessible via the /query endpoint and the server is hardcoded to skip it for system column management (no pinned_to, created_by etc on this table neederd).


Column
Type
Notes


token_hash
VARCHAR(64)
primary key, SHA 256 hash


user_id
INT
FK to jde_users.id, ON DELETE CASCADE


created_at
TIMESTAMP
when the session was created


last_accessed
TIMESTAMP
when the session was last used