# SCHEMA_INFRASTRUCTURE.md

**Source:** MSP-MODE-SPEC.md
**Section:** Client & Infrastructure Tables
**Date:** 2026-01-15

## Overview

Infrastructure tracking tables for client sites, servers, network devices, services, and Microsoft 365 tenants. These tables provide comprehensive infrastructure inventory and relationship tracking.

---

## Client & Infrastructure Tables (7 tables)

### `sites`

Physical/logical locations for clients.

```sql
CREATE TABLE sites (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL, -- "Main Office", "SLC - Salt Lake City"
    network_subnet VARCHAR(100), -- "172.16.9.0/24"
    vpn_required BOOLEAN DEFAULT false,
    vpn_subnet VARCHAR(100), -- "192.168.1.0/24"
    gateway_ip VARCHAR(45), -- IPv4/IPv6
    dns_servers TEXT, -- JSON array
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_sites_client (client_id)
);
```

---

### `infrastructure`

Servers, network devices, NAS, workstations (enhanced with environmental constraints).

```sql
CREATE TABLE infrastructure (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    site_id UUID REFERENCES sites(id) ON DELETE SET NULL,
    asset_type VARCHAR(50) NOT NULL CHECK(asset_type IN (
        'physical_server', 'virtual_machine', 'container',
        'network_device', 'nas_storage', 'workstation',
        'firewall', 'domain_controller'
    )),
    hostname VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45),
    mac_address VARCHAR(17),
    os VARCHAR(255), -- "Ubuntu 22.04", "Windows Server 2022", "Unraid"
    os_version VARCHAR(100), -- "6.22", "2008 R2", "22.04"
    role_description TEXT, -- "Primary DC, NPS/RADIUS server"
    parent_host_id UUID REFERENCES infrastructure(id) ON DELETE SET NULL, -- for VMs/containers
    status VARCHAR(50) DEFAULT 'active' CHECK(status IN (
        'active', 'migration_source', 'migration_destination', 'decommissioned'
    )),

    -- Environmental constraints (new)
    environmental_notes TEXT, -- "Manual WINS install, no native service. ReadyNAS OS, SMB1 only."
    powershell_version VARCHAR(20), -- "2.0", "5.1", "7.4"
    shell_type VARCHAR(50), -- "bash", "cmd", "powershell", "sh"
    package_manager VARCHAR(50), -- "apt", "yum", "chocolatey", "none"
    has_gui BOOLEAN DEFAULT true, -- false for headless/DOS
    limitations TEXT, -- JSON array: ["no_ps7", "smb1_only", "dos_6.22_commands"]

    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_infrastructure_client (client_id),
    INDEX idx_infrastructure_type (asset_type),
    INDEX idx_infrastructure_hostname (hostname),
    INDEX idx_infrastructure_parent (parent_host_id),
    INDEX idx_infrastructure_os (os)
);
```

**Examples:**
- Jupiter (Ubuntu 22.04, PS7, GUI)
- AD2/Dataforth (Server 2022, PS5.1, GUI)
- D2TESTNAS (ReadyNAS OS, manual WINS, no GUI service manager, SMB1)
- TS-27 (MS-DOS 6.22, no GUI, batch only)

---

### `services`

Applications/services running on infrastructure.

```sql
CREATE TABLE services (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    infrastructure_id UUID REFERENCES infrastructure(id) ON DELETE CASCADE,
    service_name VARCHAR(255) NOT NULL, -- "Gitea", "PostgreSQL", "Apache"
    service_type VARCHAR(100), -- "git_hosting", "database", "web_server"
    external_url VARCHAR(500), -- "https://git.azcomputerguru.com"
    internal_url VARCHAR(500), -- "http://172.16.3.20:3000"
    port INTEGER,
    protocol VARCHAR(50), -- "https", "ssh", "smb"
    status VARCHAR(50) DEFAULT 'running' CHECK(status IN (
        'running', 'stopped', 'error', 'maintenance'
    )),
    version VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_services_infrastructure (infrastructure_id),
    INDEX idx_services_name (service_name),
    INDEX idx_services_type (service_type)
);
```

---

### `service_relationships`

Dependencies and relationships between services.

```sql
CREATE TABLE service_relationships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE,
    to_service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE,
    relationship_type VARCHAR(50) NOT NULL CHECK(relationship_type IN (
        'hosted_on', 'proxied_by', 'authenticates_via',
        'backend_for', 'depends_on', 'replicates_to'
    )),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(from_service_id, to_service_id, relationship_type),
    INDEX idx_service_rel_from (from_service_id),
    INDEX idx_service_rel_to (to_service_id)
);
```

**Examples:**
- Gitea (proxied_by) NPM
- GuruRMM API (hosted_on) Jupiter container

---

### `networks`

Network segments, VLANs, VPN networks.

```sql
CREATE TABLE networks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
    network_name VARCHAR(255) NOT NULL,
    network_type VARCHAR(50) CHECK(network_type IN (
        'lan', 'vpn', 'vlan', 'isolated', 'dmz'
    )),
    cidr VARCHAR(100) NOT NULL, -- "192.168.0.0/24"
    gateway_ip VARCHAR(45),
    vlan_id INTEGER,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_networks_client (client_id),
    INDEX idx_networks_site (site_id)
);
```

---

### `firewall_rules`

Network security rules (for documentation/audit trail).

```sql
CREATE TABLE firewall_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    infrastructure_id UUID REFERENCES infrastructure(id) ON DELETE CASCADE,
    rule_name VARCHAR(255),
    source_cidr VARCHAR(100),
    destination_cidr VARCHAR(100),
    port INTEGER,
    protocol VARCHAR(20), -- "tcp", "udp", "icmp"
    action VARCHAR(20) CHECK(action IN ('allow', 'deny', 'drop')),
    rule_order INTEGER,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255),

    INDEX idx_firewall_infra (infrastructure_id)
);
```

---

### `m365_tenants`

Microsoft 365 tenant tracking.

```sql
CREATE TABLE m365_tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL UNIQUE, -- Microsoft tenant ID
    tenant_name VARCHAR(255), -- "dataforth.com"
    default_domain VARCHAR(255), -- "dataforthcorp.onmicrosoft.com"
    admin_email VARCHAR(255),
    cipp_name VARCHAR(255), -- name in CIPP portal
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_m365_client (client_id),
    INDEX idx_m365_tenant_id (tenant_id)
);
```

---

## Environmental Constraints System

### Purpose

The infrastructure table includes environmental constraint fields to track system-specific limitations and capabilities. This prevents failures by recording what works and what doesn't on each system.

### Key Fields

**`environmental_notes`**: Free-form text describing quirks, limitations, custom installations
- Example: "Manual WINS install, no native service. ReadyNAS OS, SMB1 only."

**`powershell_version`**: Specific PowerShell version available
- Enables command compatibility checks
- Example: "2.0" (Server 2008), "5.1" (Server 2022), "7.4" (Ubuntu with PS)

**`shell_type`**: Primary shell interface
- "bash", "cmd", "powershell", "sh", "zsh"
- Determines command syntax to use

**`package_manager`**: Package management system
- "apt", "yum", "chocolatey", "brew", "none"
- Enables automated software installation

**`has_gui`**: Whether system has graphical interface
- `false` for headless servers, DOS systems
- Prevents suggestions like "use Services GUI"

**`limitations`**: JSON array of specific constraints
- Example: `["no_ps7", "smb1_only", "dos_6.22_commands", "no_long_filenames"]`

### Real-World Examples

**D2TESTNAS (192.168.0.9)**
```sql
{
    "hostname": "D2TESTNAS",
    "os": "ReadyNAS OS",
    "environmental_notes": "Manual WINS installation (Samba nmbd). No native service GUI. SMB1/CORE protocol only for DOS compatibility.",
    "powershell_version": null,
    "shell_type": "bash",
    "package_manager": "none",
    "has_gui": false,
    "limitations": ["smb1_only", "no_service_manager_gui", "manual_wins"]
}
```

**AD2 (192.168.0.6 - Server 2022)**
```sql
{
    "hostname": "AD2",
    "os": "Windows Server 2022",
    "environmental_notes": "Primary domain controller. PowerShell 5.1 default.",
    "powershell_version": "5.1",
    "shell_type": "powershell",
    "package_manager": "none",
    "has_gui": true,
    "limitations": []
}
```

**TS-XX Machines (DOS)**
```sql
{
    "hostname": "TS-27",
    "os": "MS-DOS 6.22",
    "environmental_notes": "DOS 6.22. No IF /I, no long filenames (8.3 only), no modern batch features.",
    "powershell_version": null,
    "shell_type": "cmd",
    "package_manager": "none",
    "has_gui": false,
    "limitations": ["dos_6.22", "no_if_i", "8.3_filenames_only", "no_unicode"]
}
```

---

## Relationships

- `clients` → `sites` (one-to-many): Clients can have multiple physical locations
- `clients` → `infrastructure` (one-to-many): Clients own infrastructure assets
- `clients` → `networks` (one-to-many): Clients have network segments
- `clients` → `m365_tenants` (one-to-many): Clients can have M365 tenants
- `sites` → `infrastructure` (one-to-many): Infrastructure located at sites
- `sites` → `networks` (one-to-many): Networks belong to sites
- `infrastructure` → `infrastructure` (self-referencing): Parent-child for VMs/containers
- `infrastructure` → `services` (one-to-many): Infrastructure hosts services
- `infrastructure` → `firewall_rules` (one-to-many): Firewall rules applied to infrastructure
- `services` ↔ `services` (many-to-many via service_relationships): Service dependencies

---

## Cross-References

- **Core Tables:** See [SCHEMA_CORE.md](SCHEMA_CORE.md)
- **Credentials:** See [SCHEMA_CREDENTIALS.md](SCHEMA_CREDENTIALS.md)
- **Environmental Learning:** See [SCHEMA_CONTEXT.md](SCHEMA_CONTEXT.md) for failure patterns and insights
- **MSP Work Tracking:** See [SCHEMA_MSP.md](SCHEMA_MSP.md)
- **External Integrations:** See [SCHEMA_INTEGRATIONS.md](SCHEMA_INTEGRATIONS.md)
- **API Endpoints:** See [API_SPEC.md](API_SPEC.md)