33 lines
1.4 KiB
SQL
33 lines
1.4 KiB
SQL
CREATE TABLE IF NOT EXISTS system_users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(64) NOT NULL UNIQUE,
|
|
full_name VARCHAR(255) NOT NULL DEFAULT '',
|
|
role VARCHAR(16) NOT NULL DEFAULT 'user' CONSTRAINT ck_system_users_role CHECK (role IN ('admin', 'user')),
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
password_hash TEXT NOT NULL,
|
|
must_change_password BOOLEAN NOT NULL DEFAULT TRUE,
|
|
session_version INTEGER NOT NULL DEFAULT 0,
|
|
first_login_at TIMESTAMPTZ,
|
|
last_login_at TIMESTAMPTZ,
|
|
password_changed_at TIMESTAMPTZ,
|
|
password_reset_at TIMESTAMPTZ,
|
|
failed_login_attempts INTEGER NOT NULL DEFAULT 0,
|
|
locked_until TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS user_actions (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES system_users(id),
|
|
action VARCHAR(32) NOT NULL,
|
|
target_user_id INTEGER REFERENCES system_users(id),
|
|
employee_id INTEGER,
|
|
employee_name TEXT,
|
|
publication_year INTEGER,
|
|
publication_key VARCHAR(64),
|
|
article_title TEXT,
|
|
changes JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_user_actions_user_created ON user_actions(user_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS ix_user_actions_target_created ON user_actions(target_user_id, created_at);
|