35 lines
980 B
SQL
35 lines
980 B
SQL
CREATE TABLE system_state (
|
|
singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
|
|
bootstrap_completed_at TEXT
|
|
);
|
|
|
|
INSERT INTO system_state (singleton, bootstrap_completed_at) VALUES (1, NULL);
|
|
|
|
CREATE TABLE users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE COLLATE NOCASE,
|
|
password_hash TEXT NOT NULL,
|
|
global_role TEXT NOT NULL CHECK (global_role IN ('admin', 'user')),
|
|
disabled_at TEXT,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
id_hash BLOB PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
csrf_hash BLOB NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL,
|
|
last_seen_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX sessions_user_id_idx ON sessions(user_id);
|
|
CREATE INDEX sessions_expires_at_idx ON sessions(expires_at);
|
|
|
|
CREATE TABLE authentication_attempts (
|
|
attempt_key TEXT PRIMARY KEY,
|
|
failures INTEGER NOT NULL,
|
|
blocked_until TEXT,
|
|
updated_at TEXT NOT NULL
|
|
);
|