Repository Public
1-- Agent OS: authentication, audit, notifications, settings (derived from Claude Remote).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);55565758596061626364CREATE TABLE notifications (65 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),66 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,67 kind text NOT NULL,68 title text NOT NULL,69 body text NOT NULL DEFAULT '',70 link text,71 read_at timestamptz,72 created_at timestamptz NOT NULL DEFAULT now()73);74CREATE INDEX notifications_user_created_idx ON notifications(user_id, created_at DESC);7576CREATE TABLE audit_events (77 id bigserial PRIMARY KEY,78 at timestamptz NOT NULL DEFAULT now(),79 actor text NOT NULL, -- username | "system" | "anonymous"80 action text NOT NULL, -- e.g. auth.login, session.create, fs.write, task.create81 target text,82 ip inet,83 ok boolean NOT NULL DEFAULT true,84 detail jsonb NOT NULL DEFAULT '{}' -- never secrets, never file/terminal content85);86CREATE INDEX audit_events_at_idx ON audit_events(at DESC);87CREATE INDEX audit_events_action_idx ON audit_events(action, at DESC);8889CREATE TABLE application_settings (90 user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,91 settings jsonb NOT NULL DEFAULT '{}',92 updated_at timestamptz NOT NULL DEFAULT now()93);9495-- WebAuthn login challenges issued BEFORE an auth session exists (passkey-first login).96-- The browser holds an opaque random token in the short-lived cookie `__Host-cr_pre`;97-- only its sha256 is stored. Rows are single-use (deleted on verification) and expire after 5 min.98CREATE TABLE preauth_challenges (99 token_hash text PRIMARY KEY,100 challenge text NOT NULL,101 ip inet,102 created_at timestamptz NOT NULL DEFAULT now(),103 expires_at timestamptz NOT NULL104);105CREATE INDEX preauth_challenges_expiry_idx ON preauth_challenges(expires_at);106