# 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.

```sql
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:**
```javascript
fingerprint = SHA256(hostname + "|" + username + "|" + platform + "|" + home_directory)
// Example: SHA256("ACG-M-L5090|MikeSwanson|win32|C:\Users\MikeSwanson")
```

**Auto-Detection on Session Start:**
```javascript
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):**
```json
{
  "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):**
```json
{
  "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):**
```json
{
  "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.

```sql
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.

```sql
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).

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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](SCHEMA_MSP.md)
- **Infrastructure Details:** See [SCHEMA_INFRASTRUCTURE.md](SCHEMA_INFRASTRUCTURE.md)
- **Credentials & Security:** See [SCHEMA_CREDENTIALS.md](SCHEMA_CREDENTIALS.md)
- **Environmental Learning:** See [SCHEMA_CONTEXT.md](SCHEMA_CONTEXT.md)
- **External Integrations:** See [SCHEMA_INTEGRATIONS.md](SCHEMA_INTEGRATIONS.md)
- **API Endpoints:** See [API_SPEC.md](API_SPEC.md)
- **Architecture Overview:** See [ARCHITECTURE_OVERVIEW.md](ARCHITECTURE_OVERVIEW.md)