Repository Public
1-- Claude Remote initial schema. Single-administrator system, but every row keeps ownership.2CREATE EXTENSION IF NOT EXISTS pgcrypto;34CREATE TABLE users (5 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),6 username text NOT NULL UNIQUE,7 password_hash text NOT NULL, -- argon2id / scrypt PHC string8 totp_secret_enc text, -- AES-256-GCM with CR_SECRET_KEY; null = not enrolled9 totp_last_counter bigint, -- replay protection10 failed_logins int NOT NULL DEFAULT 0,11 locked_until timestamptz,12 created_at timestamptz NOT NULL DEFAULT now(),13 updated_at timestamptz NOT NULL DEFAULT now()14);1516-- WebAuthn credentials (passkeys)17CREATE TABLE devices (18 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),19 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,20 label text NOT NULL,21 credential_id text NOT NULL UNIQUE, -- base64url22 public_key bytea NOT NULL,23 counter bigint NOT NULL DEFAULT 0,24 transports text[] NOT NULL DEFAULT '{}',25 created_at timestamptz NOT NULL DEFAULT now(),26 last_used_at timestamptz27);28CREATE INDEX devices_user_idx ON devices(user_id);2930CREATE TABLE auth_sessions (31 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),32 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,33 token_hash text NOT NULL UNIQUE, -- sha256(token); the token itself is never stored34 csrf_token text NOT NULL,35 second_factor boolean NOT NULL DEFAULT false,36 pending jsonb NOT NULL DEFAULT '{}', -- e.g. WebAuthn challenge (short lived)37 user_agent text,38 ip inet,39 created_at timestamptz NOT NULL DEFAULT now(),40 last_seen_at timestamptz NOT NULL DEFAULT now(),41 expires_at timestamptz NOT NULL,42 revoked_at timestamptz43);44CREATE INDEX auth_sessions_user_idx ON auth_sessions(user_id) WHERE revoked_at IS NULL;45CREATE INDEX auth_sessions_expiry_idx ON auth_sessions(expires_at);4647-- Brute-force throttling (per ip and per username), survives restarts.48CREATE TABLE login_attempts (49 id bigserial PRIMARY KEY,50 key text NOT NULL, -- "ip:<addr>" | "user:<name>" | "global"51 ok boolean NOT NULL,52 at timestamptz NOT NULL DEFAULT now()53);54CREATE INDEX login_attempts_key_at_idx ON login_attempts(key, at DESC);5556CREATE TABLE projects (57 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),58 owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,59 name text NOT NULL,60 description text NOT NULL DEFAULT '',61 root_label text NOT NULL,62 rel_path text NOT NULL, -- relative to the workspace root63 git_remote text,64 tags text[] NOT NULL DEFAULT '{}',65 pinned boolean NOT NULL DEFAULT false,66 settings jsonb NOT NULL DEFAULT '{}',67 last_activity_at timestamptz,68 created_at timestamptz NOT NULL DEFAULT now(),69 updated_at timestamptz NOT NULL DEFAULT now(),70 UNIQUE (root_label, rel_path)71);72CREATE INDEX projects_owner_idx ON projects(owner_id);7374CREATE TABLE terminal_sessions (75 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),76 owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,77 project_id uuid REFERENCES projects(id) ON DELETE SET NULL,78 kind text NOT NULL CHECK (kind IN ('claude','shell')),79 title text NOT NULL,80 state text NOT NULL,81 cwd_abs text NOT NULL, -- server-side only82 cwd_rel text NOT NULL DEFAULT '',83 pid int,84 claude_conversation_id text,85 launch_args jsonb NOT NULL DEFAULT '[]',86 pinned boolean NOT NULL DEFAULT false,87 archived boolean NOT NULL DEFAULT false,88 exit_code int,89 waiting_reason text,90 created_at timestamptz NOT NULL DEFAULT now(),91 last_activity_at timestamptz,92 ended_at timestamptz93);94CREATE INDEX terminal_sessions_owner_state_idx ON terminal_sessions(owner_id, state);95CREATE INDEX terminal_sessions_project_idx ON terminal_sessions(project_id);9697-- Lifecycle events only (state changes, attach/detach, hook signals). NOT terminal transcripts.98CREATE TABLE terminal_events (99 id bigserial PRIMARY KEY,100 session_id uuid NOT NULL REFERENCES terminal_sessions(id) ON DELETE CASCADE,101 at timestamptz NOT NULL DEFAULT now(),102 kind text NOT NULL,103 detail jsonb NOT NULL DEFAULT '{}'104);105CREATE INDEX terminal_events_session_at_idx ON terminal_events(session_id, at DESC);106107CREATE TABLE agent_tasks (108 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),109 owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,110 project_id uuid REFERENCES projects(id) ON DELETE SET NULL,111 title text NOT NULL,112 command text NOT NULL,113 host text NOT NULL DEFAULT 'local',114 cwd_abs text NOT NULL,115 cwd_rel text NOT NULL DEFAULT '',116 state text NOT NULL,117 exit_code int,118 timeout_sec int,119 idempotent boolean NOT NULL DEFAULT false,120 retry_of uuid REFERENCES agent_tasks(id) ON DELETE SET NULL,121 artifact_globs text[] NOT NULL DEFAULT '{}',122 log_bytes bigint NOT NULL DEFAULT 0,123 cpu_sec double precision,124 max_rss_mb double precision,125 created_at timestamptz NOT NULL DEFAULT now(),126 started_at timestamptz,127 ended_at timestamptz128);129CREATE INDEX agent_tasks_owner_state_idx ON agent_tasks(owner_id, state);130CREATE INDEX agent_tasks_created_idx ON agent_tasks(created_at DESC);131132CREATE TABLE task_artifacts (133 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),134 task_id uuid NOT NULL REFERENCES agent_tasks(id) ON DELETE CASCADE,135 name text NOT NULL,136 rel_path text NOT NULL, -- inside the task cwd137 size bigint NOT NULL,138 created_at timestamptz NOT NULL DEFAULT now()139);140CREATE INDEX task_artifacts_task_idx ON task_artifacts(task_id);141142CREATE TABLE cluster_nodes (143 id text PRIMARY KEY, -- alias144 model text, chip text, kind text NOT NULL DEFAULT 'mac', role text,145 cpu_cores int, gpu_cores int, memory_mb int, os text,146 exec_allowed boolean NOT NULL DEFAULT false,147 first_seen_at timestamptz NOT NULL DEFAULT now(),148 last_seen_at timestamptz149);150151CREATE TABLE node_metrics (152 id bigserial PRIMARY KEY,153 node_id text NOT NULL REFERENCES cluster_nodes(id) ON DELETE CASCADE,154 at timestamptz NOT NULL,155 online boolean NOT NULL,156 cpu_pct real, mem_used_mb real, disk_free_gb real, load1 real157);158CREATE INDEX node_metrics_node_at_idx ON node_metrics(node_id, at DESC);159160-- Cached view of Claude Code's own MCP / skills configuration (source of truth stays on disk).161CREATE TABLE mcp_servers (162 name text NOT NULL, scope text NOT NULL, transport text NOT NULL, target text NOT NULL,163 env_keys text[] NOT NULL DEFAULT '{}', status text NOT NULL DEFAULT 'unknown',164 last_error text, checked_at timestamptz,165 PRIMARY KEY (name, scope)166);167CREATE TABLE installed_skills (168 name text NOT NULL, kind text NOT NULL, source text NOT NULL, version text,169 location text NOT NULL, enabled boolean NOT NULL DEFAULT true, seen_at timestamptz NOT NULL DEFAULT now(),170 PRIMARY KEY (name, kind, source)171);172173CREATE TABLE notifications (174 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),175 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,176 kind text NOT NULL,177 title text NOT NULL,178 body text NOT NULL DEFAULT '',179 link text,180 read_at timestamptz,181 created_at timestamptz NOT NULL DEFAULT now()182);183CREATE INDEX notifications_user_created_idx ON notifications(user_id, created_at DESC);184185CREATE TABLE audit_events (186 id bigserial PRIMARY KEY,187 at timestamptz NOT NULL DEFAULT now(),188 actor text NOT NULL, -- username | "system" | "anonymous"189 action text NOT NULL, -- e.g. auth.login, session.create, fs.write, task.create190 target text,191 ip inet,192 ok boolean NOT NULL DEFAULT true,193 detail jsonb NOT NULL DEFAULT '{}' -- never secrets, never file/terminal content194);195CREATE INDEX audit_events_at_idx ON audit_events(at DESC);196CREATE INDEX audit_events_action_idx ON audit_events(action, at DESC);197198CREATE TABLE application_settings (199 user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,200 settings jsonb NOT NULL DEFAULT '{}',201 updated_at timestamptz NOT NULL DEFAULT now()202);203204-- Retention (enforced by the server's daily janitor; see docs/backup-recovery.md):205-- terminal_events: settings.sessionRetentionDays (default 90) · node_metrics: 14 d206-- login_attempts: 30 d · audit_events: 400 d · notifications: 90 d · expired auth_sessions: 7 d after expiry207