JAYSON DERULO

1 files changed, 158 insertions, 0 deletions

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 |