23 lines
814 B
SQL
23 lines
814 B
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role VARCHAR(20) NOT NULL DEFAULT 'blocked',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_one_admin ON users(role) WHERE role = 'admin';
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash CHAR(64) UNIQUE NOT NULL,
|
|
user_agent VARCHAR(500),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_token_hash ON sessions(token_hash);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
|