mirror of
https://github.com/anand34577/ferrum.git
synced 2026-09-18 16:45:16 +00:00
Anand
102 lines
3.0 KiB
SQL
102 lines
3.0 KiB
SQL
-- +goose Up
|
|
CREATE TABLE users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
is_admin INTEGER NOT NULL DEFAULT 0,
|
|
totp_secret TEXT,
|
|
totp_enabled INTEGER NOT NULL DEFAULT 0,
|
|
oidc_subject TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
ip TEXT,
|
|
user_agent TEXT,
|
|
created_at TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
|
|
|
|
CREATE TABLE connections (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
host TEXT NOT NULL,
|
|
port INTEGER NOT NULL DEFAULT 8006,
|
|
auth_type TEXT NOT NULL, -- 'token' | 'password'
|
|
token_id TEXT,
|
|
token_secret_enc TEXT,
|
|
username TEXT,
|
|
password_enc TEXT,
|
|
verify_tls INTEGER NOT NULL DEFAULT 1,
|
|
behind_reverse_proxy INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE rbac_permissions (
|
|
id TEXT PRIMARY KEY,
|
|
key TEXT NOT NULL UNIQUE,
|
|
category TEXT NOT NULL,
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE rbac_roles (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
color TEXT,
|
|
is_system INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE rbac_role_permissions (
|
|
role_id TEXT NOT NULL REFERENCES rbac_roles(id) ON DELETE CASCADE,
|
|
permission_id TEXT NOT NULL REFERENCES rbac_permissions(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (role_id, permission_id)
|
|
);
|
|
|
|
CREATE TABLE rbac_user_roles (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role_id TEXT NOT NULL REFERENCES rbac_roles(id) ON DELETE CASCADE,
|
|
scope_type TEXT NOT NULL DEFAULT 'global', -- 'global' | 'connection' | 'node' | 'vm'
|
|
scope_id TEXT,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX idx_rbac_user_roles_user_id ON rbac_user_roles(user_id);
|
|
|
|
CREATE TABLE audit_log (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
|
|
action TEXT NOT NULL,
|
|
category TEXT NOT NULL,
|
|
target TEXT,
|
|
detail TEXT,
|
|
ip TEXT,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX idx_audit_log_created_at ON audit_log(created_at);
|
|
|
|
-- +goose Down
|
|
DROP TABLE audit_log;
|
|
DROP TABLE rbac_user_roles;
|
|
DROP TABLE rbac_role_permissions;
|
|
DROP TABLE rbac_roles;
|
|
DROP TABLE rbac_permissions;
|
|
DROP TABLE settings;
|
|
DROP TABLE connections;
|
|
DROP TABLE sessions;
|
|
DROP TABLE users;
|