claudetools/.claude/SCHEMA_CORE.md

SCHEMA_CORE.md

Source: MSP-MODE-SPEC.md Section: Core MSP Tracking Tables Date: 2026-01-15

Overview

Core tables for MSP Mode tracking system: machines, clients, projects, sessions, and tasks. These tables form the foundation of the MSP tracking database and are referenced by most other tables in the system.

---

Core MSP Tracking Tables (6 tables)

machines

Technician's machines (laptops, desktops) used for MSP work.

CREATE TABLE machines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Machine identification (auto-detected)
    hostname VARCHAR(255) NOT NULL UNIQUE, -- from `hostname` command
    machine_fingerprint VARCHAR(500) UNIQUE, -- hostname + username + platform hash

    -- Environment details
    friendly_name VARCHAR(255), -- "Main Laptop", "Home Desktop", "Travel Laptop"
    machine_type VARCHAR(50) CHECK(machine_type IN ('laptop', 'desktop', 'workstation', 'vm')),
    platform VARCHAR(50), -- "win32", "darwin", "linux"
    os_version VARCHAR(100),
    username VARCHAR(255), -- from `whoami`
    home_directory VARCHAR(500), -- user home path

    -- Capabilities
    has_vpn_access BOOLEAN DEFAULT false, -- can connect to client networks
    vpn_profiles TEXT, -- JSON array: ["dataforth", "grabb", "internal"]
    has_docker BOOLEAN DEFAULT false,
    has_powershell BOOLEAN DEFAULT false,
    powershell_version VARCHAR(20),
    has_ssh BOOLEAN DEFAULT true,
    has_git BOOLEAN DEFAULT true,

    -- Network context
    typical_network_location VARCHAR(100), -- "home", "office", "mobile"
    static_ip VARCHAR(45), -- if has static IP

    -- Claude Code context
    claude_working_directory VARCHAR(500), -- primary working dir
    additional_working_dirs TEXT, -- JSON array

    -- Tool versions
    installed_tools TEXT, -- JSON: {"git": "2.40", "docker": "24.0", "python": "3.11"}

    -- MCP Servers & Skills (NEW)
    available_mcps TEXT, -- JSON array: ["claude-in-chrome", "filesystem", "custom-mcp"]
    mcp_capabilities TEXT, -- JSON: {"chrome": {"version": "1.0", "features": ["screenshots"]}}
    available_skills TEXT, -- JSON array: ["pdf", "commit", "review-pr", "custom-skill"]
    skill_paths TEXT, -- JSON: {"/pdf": "/path/to/pdf-skill", ...}

    -- OS-Specific Commands
    preferred_shell VARCHAR(50), -- "powershell", "bash", "zsh", "cmd"
    package_manager_commands TEXT, -- JSON: {"install": "choco install", "update": "choco upgrade"}

    -- Status
    is_primary BOOLEAN DEFAULT false, -- primary machine
    is_active BOOLEAN DEFAULT true,
    last_seen TIMESTAMP,
    last_session_id UUID, -- last session from this machine

    -- Notes
    notes TEXT, -- "Travel laptop - limited tools, no VPN"

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_machines_hostname (hostname),
    INDEX idx_machines_fingerprint (machine_fingerprint),
    INDEX idx_machines_is_active (is_active),
    INDEX idx_machines_platform (platform)
);

Machine Fingerprint Generation:

fingerprint = SHA256(hostname + "|" + username + "|" + platform + "|" + home_directory)
// Example: SHA256("ACG-M-L5090|MikeSwanson|win32|C:\Users\MikeSwanson")

Auto-Detection on Session Start:

hostname = exec("hostname")          // "ACG-M-L5090"
username = exec("whoami")            // "MikeSwanson" or "AzureAD+MikeSwanson"
platform = process.platform          // "win32", "darwin", "linux"
home_dir = process.env.HOME || process.env.USERPROFILE

fingerprint = SHA256(`${hostname}|${username}|${platform}|${home_dir}`)

// Query database: SELECT * FROM machines WHERE machine_fingerprint = ?
// If not found: Create new machine record
// If found: Update last_seen, return machine_id

Examples:

ACG-M-L5090 (Main Laptop):

{
  "hostname": "ACG-M-L5090",
  "friendly_name": "Main Laptop",
  "platform": "win32",
  "os_version": "Windows 11 Pro",
  "has_vpn_access": true,
  "vpn_profiles": ["dataforth", "grabb", "internal"],
  "has_docker": true,
  "powershell_version": "7.4",
  "preferred_shell": "powershell",
  "available_mcps": ["claude-in-chrome", "filesystem"],
  "available_skills": ["pdf", "commit", "review-pr", "frontend-design"],
  "package_manager_commands": {
    "install": "choco install {package}",
    "update": "choco upgrade {package}",
    "list": "choco list --local-only"
  }
}

Mike-MacBook (Development Machine):

{
  "hostname": "Mikes-MacBook-Pro",
  "friendly_name": "MacBook Pro",
  "platform": "darwin",
  "os_version": "macOS 14.2",
  "has_vpn_access": false,
  "has_docker": true,
  "powershell_version": null,
  "preferred_shell": "zsh",
  "available_mcps": ["filesystem"],
  "available_skills": ["commit", "review-pr"],
  "package_manager_commands": {
    "install": "brew install {package}",
    "update": "brew upgrade {package}",
    "list": "brew list"
  }
}

Travel-Laptop (Limited):

{
  "hostname": "TRAVEL-WIN",
  "friendly_name": "Travel Laptop",
  "platform": "win32",
  "os_version": "Windows 10 Home",
  "has_vpn_access": false,
  "vpn_profiles": [],
  "has_docker": false,
  "powershell_version": "5.1",
  "preferred_shell": "powershell",
  "available_mcps": [],
  "available_skills": [],
  "notes": "Minimal toolset, no Docker, no VPN - use for light work only"
}

---

clients

Master table for all client organizations.

CREATE TABLE clients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL UNIQUE,
    type VARCHAR(50) NOT NULL CHECK(type IN ('msp_client', 'internal', 'project')),
    network_subnet VARCHAR(100), -- e.g., "192.168.0.0/24"
    domain_name VARCHAR(255), -- AD domain or primary domain
    m365_tenant_id UUID, -- Microsoft 365 tenant ID
    primary_contact VARCHAR(255),
    notes TEXT,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_clients_type (type),
    INDEX idx_clients_name (name)
);

Examples: Dataforth, Grabb & Durando, Valley Wide Plastering, AZ Computer Guru (internal)

---

projects

Individual projects/engagements for clients.

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE, -- directory name: "dataforth-dos"
    category VARCHAR(50) CHECK(category IN (
        'client_project', 'internal_product', 'infrastructure',
        'website', 'development_tool', 'documentation'
    )),
    status VARCHAR(50) DEFAULT 'working' CHECK(status IN (
        'complete', 'working', 'blocked', 'pending', 'critical', 'deferred'
    )),
    priority VARCHAR(20) CHECK(priority IN ('critical', 'high', 'medium', 'low')),
    description TEXT,
    started_date DATE,
    target_completion_date DATE,
    completed_date DATE,
    estimated_hours DECIMAL(10,2),
    actual_hours DECIMAL(10,2),
    gitea_repo_url VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_projects_client (client_id),
    INDEX idx_projects_status (status),
    INDEX idx_projects_slug (slug)
);

Examples: dataforth-dos, gururmm, grabb-website-move

---

sessions

Work sessions with time tracking (enhanced with machine tracking).

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE SET NULL,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    machine_id UUID REFERENCES machines(id) ON DELETE SET NULL, -- NEW: which machine
    session_date DATE NOT NULL,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    duration_minutes INTEGER, -- auto-calculated or manual
    status VARCHAR(50) DEFAULT 'completed' CHECK(status IN (
        'completed', 'in_progress', 'blocked', 'pending'
    )),
    session_title VARCHAR(500) NOT NULL,
    summary TEXT, -- markdown summary
    is_billable BOOLEAN DEFAULT false,
    billable_hours DECIMAL(10,2),
    technician VARCHAR(255), -- "Mike Swanson", etc.
    session_log_file VARCHAR(500), -- path to .md file
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_sessions_client (client_id),
    INDEX idx_sessions_project (project_id),
    INDEX idx_sessions_date (session_date),
    INDEX idx_sessions_billable (is_billable),
    INDEX idx_sessions_machine (machine_id)
);

---

pending_tasks

Open items across all clients/projects.

CREATE TABLE pending_tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    work_item_id UUID REFERENCES work_items(id) ON DELETE SET NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    priority VARCHAR(20) CHECK(priority IN ('critical', 'high', 'medium', 'low')),
    blocked_by TEXT, -- what's blocking this
    assigned_to VARCHAR(255),
    due_date DATE,
    status VARCHAR(50) DEFAULT 'pending' CHECK(status IN (
        'pending', 'in_progress', 'blocked', 'completed', 'cancelled'
    )),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,

    INDEX idx_pending_tasks_client (client_id),
    INDEX idx_pending_tasks_status (status),
    INDEX idx_pending_tasks_priority (priority)
);

---

tasks

Task/checklist management for tracking implementation steps, analysis work, and other agent activities.

CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Task hierarchy
    parent_task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
    task_order INTEGER NOT NULL,

    -- Task details
    title VARCHAR(500) NOT NULL,
    description TEXT,
    task_type VARCHAR(100) CHECK(task_type IN (
        'implementation', 'research', 'review', 'deployment',
        'testing', 'documentation', 'bugfix', 'analysis'
    )),

    -- Status tracking
    status VARCHAR(50) NOT NULL CHECK(status IN (
        'pending', 'in_progress', 'blocked', 'completed', 'cancelled'
    )),
    blocking_reason TEXT, -- Why blocked (if status='blocked')

    -- Context
    session_id UUID REFERENCES sessions(id) ON DELETE CASCADE,
    client_id UUID REFERENCES clients(id) ON DELETE SET NULL,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    assigned_agent VARCHAR(100), -- Which agent is handling this

    -- Timing
    estimated_complexity VARCHAR(20) CHECK(estimated_complexity IN (
        'trivial', 'simple', 'moderate', 'complex', 'very_complex'
    )),
    started_at TIMESTAMP,
    completed_at TIMESTAMP,

    -- Context data (JSON)
    task_context TEXT, -- Detailed context for this task
    dependencies TEXT, -- JSON array of dependency task_ids

    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_tasks_session (session_id),
    INDEX idx_tasks_status (status),
    INDEX idx_tasks_parent (parent_task_id),
    INDEX idx_tasks_client (client_id),
    INDEX idx_tasks_project (project_id)
);

---

Tagging System Tables (3 tables)

tags

Flexible tagging system for work items and sessions.

CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    category VARCHAR(50) CHECK(category IN (
        'technology', 'client', 'infrastructure',
        'problem_type', 'action', 'service'
    )),
    description TEXT,
    usage_count INTEGER DEFAULT 0, -- auto-increment on use
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_tags_category (category),
    INDEX idx_tags_name (name)
);

Pre-populated tags: 157+ tags identified from analysis

• 58 technology tags (docker, postgresql, apache, etc.)
• 24 infrastructure tags (jupiter, saturn, pfsense, etc.)
• 20+ client tags
• 30 problem type tags (connection-timeout, ssl-error, etc.)
• 25 action tags (migration, upgrade, cleanup, etc.)

---

work_item_tags (Junction Table)

Many-to-many relationship: work items ↔ tags.

CREATE TABLE work_item_tags (
    work_item_id UUID NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (work_item_id, tag_id),

    INDEX idx_wit_work_item (work_item_id),
    INDEX idx_wit_tag (tag_id)
);

---

session_tags (Junction Table)

Many-to-many relationship: sessions ↔ tags.

CREATE TABLE session_tags (
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (session_id, tag_id),

    INDEX idx_st_session (session_id),
    INDEX idx_st_tag (tag_id)
);

---

Relationships

• machines → sessions (one-to-many): Track which machine was used for each session
• clients → projects (one-to-many): Each client can have multiple projects
• clients → sessions (one-to-many): Track all work sessions for a client
• projects → sessions (one-to-many): Sessions belong to specific projects
• sessions → work_items (one-to-many): Each session contains multiple work items
• sessions → pending_tasks (one-to-many): Tasks can be created from sessions
• sessions → tasks (one-to-many): Task checklists linked to sessions
• tags ↔ sessions (many-to-many via session_tags)
• tags ↔ work_items (many-to-many via work_item_tags)

---

Cross-References

• Work Items & Time Tracking: See SCHEMA_MSP.md
• Infrastructure Details: See SCHEMA_INFRASTRUCTURE.md
• Credentials & Security: See SCHEMA_CREDENTIALS.md
• Environmental Learning: See SCHEMA_CONTEXT.md
• External Integrations: See SCHEMA_INTEGRATIONS.md
• API Endpoints: See API_SPEC.md
• Architecture Overview: See ARCHITECTURE_OVERVIEW.md