schema/system_columns.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


System Columns
back to schema /// home
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


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:
[system_column_overrides.my_changelog_table]
all = false  # skip ALL system columns on this table


Or selectively:
[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