NotAShelf
56d44e120a
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: I8567eec6980b2b5453687bcbd07a61206a6a6964
35 lines
1.1 KiB
SQL
35 lines
1.1 KiB
SQL
-- Users table
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role JSONB NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL
|
|
);
|
|
|
|
-- User profiles table
|
|
CREATE TABLE user_profiles (
|
|
user_id UUID PRIMARY KEY,
|
|
avatar_path TEXT,
|
|
bio TEXT,
|
|
preferences_json JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- User library access table
|
|
CREATE TABLE user_libraries (
|
|
user_id UUID NOT NULL,
|
|
root_path TEXT NOT NULL,
|
|
permission JSONB NOT NULL,
|
|
granted_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
PRIMARY KEY (user_id, root_path),
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- Indexes for efficient lookups
|
|
CREATE INDEX idx_users_username ON users(username);
|
|
CREATE INDEX idx_user_libraries_user_id ON user_libraries(user_id);
|
|
CREATE INDEX idx_user_libraries_root_path ON user_libraries(root_path);
|