74 lines
2.5 KiB
SQL
74 lines
2.5 KiB
SQL
-- RemoteLink initial schema
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
full_name TEXT,
|
|
company TEXT,
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS machines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
hostname TEXT,
|
|
os TEXT,
|
|
os_version TEXT,
|
|
agent_version TEXT,
|
|
ip_address TEXT,
|
|
access_key TEXT UNIQUE NOT NULL,
|
|
is_online BOOLEAN NOT NULL DEFAULT FALSE,
|
|
last_seen TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS session_codes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code TEXT NOT NULL,
|
|
machine_id UUID NOT NULL REFERENCES machines(id) ON DELETE CASCADE,
|
|
created_by UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
used_at TIMESTAMPTZ,
|
|
used_by UUID REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
machine_id UUID REFERENCES machines(id) ON DELETE SET NULL,
|
|
machine_name TEXT,
|
|
viewer_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
connection_type TEXT,
|
|
session_code TEXT,
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ended_at TIMESTAMPTZ,
|
|
duration_seconds INTEGER,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS invites (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
token UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
|
|
email TEXT NOT NULL,
|
|
created_by UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '7 days'),
|
|
used_at TIMESTAMPTZ,
|
|
used_by UUID REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS machines_user_id_idx ON machines(user_id);
|
|
CREATE INDEX IF NOT EXISTS machines_access_key_idx ON machines(access_key);
|
|
CREATE INDEX IF NOT EXISTS session_codes_code_idx ON session_codes(code);
|
|
CREATE INDEX IF NOT EXISTS session_codes_machine_id_idx ON session_codes(machine_id);
|
|
CREATE INDEX IF NOT EXISTS sessions_viewer_user_id_idx ON sessions(viewer_user_id);
|
|
CREATE INDEX IF NOT EXISTS invites_token_idx ON invites(token);
|