Core Tables

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).

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.

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)
`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:

[
  { "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 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