26 lines
1.3 KiB
MySQL
26 lines
1.3 KiB
MySQL
|
-- Per-user interface preferences.
|
||
|
|
--
|
||
|
|
-- These live in their own table rather than as columns on users, because users is
|
||
|
|
-- a projection of the identity provider's claims: UpsertUser rewrites it on every
|
||
|
|
-- login. A row appears the first time somebody saves a preference and is keyed by
|
||
|
|
-- the same subject, so the choice survives re-login and follows the account to
|
||
|
|
-- another browser.
|
||
|
|
--
|
||
|
|
-- An empty selected_theme means the person has not chosen a theme, which the UI
|
||
|
|
-- renders as its own default rather than as a stored value.
|
||
|
|
--
|
||
|
|
-- There is deliberately no foreign key to users. That table is a projection of the
|
||
|
|
-- identity provider's claims and can be rebuilt from a backup, so a cascade from it
|
||
|
|
-- would silently discard people's choices, and requiring the row to exist would make
|
||
|
|
-- saving a preference depend on projection state that has nothing to do with the
|
||
|
|
-- preference. A row whose subject never logs in again is inert and costs nothing.
|
||
|
|
CREATE TABLE IF NOT EXISTS user_preferences (
|
||
|
|
subject TEXT NOT NULL PRIMARY KEY,
|
||
|
|
selected_theme TEXT NOT NULL DEFAULT '',
|
||
|
|
selected_appearance TEXT NOT NULL DEFAULT '',
|
||
|
|
updated_at TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Lets an operator answer "who is using this theme?" without scanning every row.
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_preferences_theme
|
||
|
|
ON user_preferences (selected_theme);
|