JAYSON DERULO

1 files changed, 56 insertions, 0 deletions

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