38 lines
1002 B
SQL
38 lines
1002 B
SQL
-- Initial schema migration to version 1
|
|
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
type TEXT NOT NULL CHECK (type IN ('human','system')),
|
|
pwd_hash TEXT NOT NULL,
|
|
totp_secret TEXT,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
name TEXT PRIMARY KEY
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_roles (
|
|
user_id TEXT NOT NULL,
|
|
role TEXT NOT NULL,
|
|
PRIMARY KEY (user_id, role),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (role) REFERENCES roles(name) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_tokens (
|
|
service TEXT PRIMARY KEY,
|
|
token TEXT NOT NULL,
|
|
issued_at INTEGER NOT NULL,
|
|
revoked_at INTEGER
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS schema_migrations (
|
|
version INTEGER PRIMARY KEY
|
|
);
|
|
|
|
INSERT OR IGNORE INTO schema_migrations(version) VALUES (1);
|