diff options
Diffstat (limited to 'schema')
| -rw-r--r-- | schema/README.md | 11 | ||||
| -rw-r--r-- | schema/core_tables.md | 158 | ||||
| -rw-r--r-- | schema/sessions.md | 57 | ||||
| -rw-r--r-- | schema/system_columns.md | 56 |
4 files changed, 282 insertions, 0 deletions
diff --git a/schema/README.md b/schema/README.md new file mode 100644 index 0000000..dc82f01 --- /dev/null +++ b/schema/README.md @@ -0,0 +1,11 @@ +# Schema + +[back to index](../README.md) + +The core database tables that every jsonderulo compliant server needs and how the server should auto manage certain columns. + +## Pages + +- [Core Tables](core_tables.md) the required tables (users, groups, settings, associations, sessions) +- [System Columns](system_columns.md) auto managed columns that jde adds to your tables +- [Sessions](sessions.md) how persistent sessions work under the hood diff --git a/schema/core_tables.md b/schema/core_tables.md new file mode 100644 index 0000000..789d2a1 --- /dev/null +++ b/schema/core_tables.md @@ -0,0 +1,158 @@ +# Core Tables + +[back to schema](README.md) /// [home](../README.md) + +These tables are required for the API to function. They handle user management, permissions, configuration and session persistence how you wish to implement them upstream wise idc just gotta be somewhat sql compatible obviously lol. + +## jde_settings + +Database driven configuration. Values here can override config file settings (application depends on [config preference](../admin/config_preference.md)). + +| Column | Type | Notes | +|---------------|---------------------|----------------------| +| `id` | INT AUTO_INCREMENT | primary key | +| `setting` | VARCHAR(255) UNIQUE | setting name | +| `value` | TEXT | setting value | +| `description` | TEXT NULL | optional description | + +### Overridable settings + +These are the settings a compliant server should support being overridden from the database. The `setting` column is the key and `value` is parsed to the appropriate type. + +#### Session settings + +| Setting | Type | What it controls | +|---------|------|-----------------| +| `session_timeout_minutes` | number | default session timeout | +| `refresh_session_on_activity` | bool | whether sessions extend on use | +| `max_concurrent_sessions` | number | default max sessions per user | +| `session_update_interval_seconds` | number | how often cached sessions flush to DB | +| `min_clear_sessions_power` | number | min power level to clear another users sessions | + +#### Query limits + +| Setting | Type | What it controls | +|---------|------|-----------------| +| `default_max_limit` | number | global default max rows per query | +| `default_max_where_conditions` | number | global default max WHERE conditions | + +#### User preferences + +| Setting | Type | What it controls | +|---------|------|-----------------| +| `default_user_settings_access` | string | default access level (`read-own-only`, `read-write-own`, `read-write-all`) | + +#### Security and IP whitelisting + +| Setting | Type | What it controls | +|---------|------|-----------------| +| `whitelisted_pin_ips` | JSON array | IPs/ranges allowed for PIN auth | +| `whitelisted_string_ips` | JSON array | IPs/ranges allowed for token/badge auth | + +#### Rate limiting (may. require restart to take effect) + +| Setting | Type | What it controls | +|---------|------|-----------------| +| `enable_rate_limiting` | bool | master switch for rate limiting | +| `auth_rate_limit_per_second` | number | auth burst limit per IP per second | +| `auth_rate_limit_per_minute` | number | auth limit per IP per minute | +| `api_rate_limit_per_second` | number | API burst limit per user per second | +| `api_rate_limit_per_minute` | number | API limit per user per minute | + +For SeckelAPI specifically : +Rate limit settings get merged into config on reload but the actual rate limiter middleware is only rebuilt on full restart. So changing these requires either a manual restart via `/reload` or waiting for an automatic restart if thats enabled. + +#### Application level settings + +You can also store arbitrary application level stuff like `company_name`, `date_format`, `datetime_format` etc. These dont get merged into the server config but can be read by toolkit endpoints via `inject_db_context` or queried by clients directly. Basically use the table as a key value store for whatever you need for shared tk settings if u want. + +### Settings that SHOULDNOT be overWRITABLE from the database + +Some things are config file only for good reasons: + +- **`config_preference`** controls how DB settings are merged in the first place. overriding it from the DB would be IDIOTIC +- **server bind address** (`host`, `port`) no need to explain id say +- **database connection** (`host`, `port`, `database`, `username`, `password`) cant change DB creds from inside the DB are you mad or smth? +- **`min_reload_power`** and **`min_manual_restart_power`** security critical, dont want these changeable from DB duh +- **`persistent_sessions`**, **`cache_recent_sessions`** fundamental fkn session architecture choices +- **`hash_pins`**, **`hash_tokens`** would literrally break existing credentials +- **logging config** file paths, log levels etc are infrastructure level +- **toolkit definitions** managed via their own config files not jde_settings +- **system column config** structural stuff thats set at startup + +## jde_groups + +Group/role definitions with power levels and permissions. + +| Column | Type | Notes | +|------------------------|---------------------|-------------------------------------------------------| +| `id` | INT AUTO_INCREMENT | primary key | +| `name` | VARCHAR(100) UNIQUE | group name | +| `power` | INT | 1 (lowest) to 100 (highest) | +| `permissions` | JSON NULL | table and column permission rules | +| `max_limit` | INT NULL | max rows per query (NULL = use default) | +| `max_where` | INT NULL | max WHERE conditions (NULL = use default) | +| `timeout_session` | INT NULL | session timeout in minutes | +| `max_sessions` | INT NULL | max concurrent sessions | +| `rollback_on_error` | BOOL | default TRUE | +| `allow_batch` | BOOL | default FALSE | +| `user_settings_access` | ENUM | `read-write-own`, `read-write-all` or `read-own-only` | + +The `permissions` column holds a JSON array of permission rules like `["*:rw", "logs:r"]`. See [table permissions](../permissions/table_permissions.md). + +## jde_users + +User accounts for authentication. + +| Column | Type | Notes | +|---------------------|---------------------|------------------------------------------------------------| +| `id` | INT AUTO_INCREMENT | primary key | +| `name` | VARCHAR(200) | display name | +| `username` | VARCHAR(100) UNIQUE | login name | +| `password` | VARCHAR(255) | bcrypt hashed | +| `pin_code` | VARCHAR(8) NULL | short PIN for kiosk auth | +| `login_string` | VARCHAR(255) NULL | badge/NFC card identifier | +| `core_group_id` | INT FK | references jde_groups.id | +| `email` | VARCHAR(255) NULL | | +| `phone` | VARCHAR(50) NULL | | +| `notes` | TEXT NULL | | +| `active` | BOOLEAN | default TRUE | +| `last_login_date` | DATETIME NULL | | +| `preferences` | JSON NULL | user settings (see [preferences](../admin/preferences.md)) | +| `toolkit_overrides` | JSON NULL | per user toolkit group overrides | + +### toolkit_overrides + +Allows overriding which toolkit group a user belongs to without changing the junction table: + +```json +[ + { "toolkit": "beepzone", "group": "manager" } +] +``` + +NULL means inherit from the core groups associations in `jde_associations`. + +## jde_associations + +Links core groups to toolkit specific groups. Each core group can have one membership per toolkit. + +| Column | Type | Notes | +|----------------------|--------------------|--------------------------------------| +| `id` | INT AUTO_INCREMENT | primary key | +| `core_group_id` | INT FK | references jde_groups.id | +| `toolkit_name` | VARCHAR(100) | toolkit identifier (like `beepzone`) | +| `toolkit_group_name` | VARCHAR(100) | group name within the toolkit | + +Unique on `(core_group_id, toolkit_name)` so a group can only be in one toolkit group per toolkit. + +## jde_sessions + +Internal table for persistent session storage. **Not accessible via the /query endpoint.** See [sessions](sessions.md) for details. + +| Column | Type | Notes | +|-----------------|----------------|--------------------------------| +| `token_hash` | VARCHAR(64) PK | SHA 256 hash of session token | +| `user_id` | INT FK | references jde_users.id | +| `created_at` | TIMESTAMP | when the session was created | +| `last_accessed` | TIMESTAMP | when the session was last used | diff --git a/schema/sessions.md b/schema/sessions.md new file mode 100644 index 0000000..2eb89d3 --- /dev/null +++ b/schema/sessions.md @@ -0,0 +1,57 @@ +# Sessions + +[back to schema](README.md) /// [home](../README.md) + +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](../auth/README.md). + +## 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 | diff --git a/schema/system_columns.md b/schema/system_columns.md new file mode 100644 index 0000000..14f2a7d --- /dev/null +++ b/schema/system_columns.md @@ -0,0 +1,56 @@ +# System Columns + +[back to schema](README.md) /// [home](../README.md) + +The server should be able to automatically add and manage certain columns on your tables. These are called system columns. + +## The columns + +| Column | Type | When populated | Notes | +|--------------------|----------------|----------------|-------------------------------------------------------------------------------| +| `created_at` | TIMESTAMP | on insert | defaults to CURRENT_TIMESTAMP | +| `created_by` | INT NULL | on insert | FK to jde_users.id, set to the authenticated users ID | +| `last_modified_at` | TIMESTAMP NULL | on update | uses ON UPDATE CURRENT_TIMESTAMP | +| `last_modified_by` | INT NULL | on update | FK to jde_users.id, set to the authenticated users ID | +| `pinned_to` | INT NULL | on insert | FK to jde_users.id, used for [ownership scoping](../permissions/ownership.md) | + +The FK columns (`created_by`, `last_modified_by`, `pinned_to`) reference jde_users with ON DELETE SET NULL and ON UPDATE CASCADE. + +## Auto management + +On startup the server should check every enabled tables columns and automatically add any missing system columns. This means you dont have to define them in your CREATE TABLE statements schema wise, Json Derulo will take care of them for you if you want <3 +TO let Jays Son Handle it a compliant server should support : +- master switch to enable/disable auto management +- separate switch for core tables (jde_users etc) + +Fully internal tables like reference servers `jde_sessions` should always be skipped regardless of these settings. + +## Per table overrides + +You can disable specific system columns for specific tables. In toolkit config: + +```toml +[system_column_overrides.my_changelog_table] +all = false # skip ALL system columns on this table +``` + +Or selectively: + +```toml +[system_column_overrides.my_table] +pinned_to = false # no ownership column +created_by = false # dont track who created rows +``` + +Same thing works for core tables via `core_system_column_overrides` in the columns config section. + +## Runtime repair + +If a query fails because of a missing system column (MySQL error 1054 "unknown column") the server should try to auto fix it by adding the missing column at runtime and retrying the query. This should have a cooldown so it doesnt spam ALTER TABLE on every request if something is actually wrong. + +## Write protection + +System columns are write protected by default. Only users with `rwa` table permission can write to them directly. For everyone else the server manages them automatically: +- `created_by` and `pinned_to` are set on insert +- `last_modified_by` is set on update +- the timestamp columns are handled by MySQL |