# Table: `hosts`

Canonical host registry. Hosts are identified by full DNS name in `fqdn`.

`legacy_id` and `hosts_ip` exist for compatibility with existing runtime databases and Work Orders, but they are not the product contract. UI and exports identify hosts by `fqdn` and expose one canonical `ip`.

## Columns

| Column | Type | Null | Default | Notes |
|--------|------|------|---------|-------|
| `fqdn` | `TEXT` | no | none | Canonical full host name. Primary key. |
| `legacy_id` | `TEXT` | no | none | Internal compatibility ID. Unique. |
| `status` | `TEXT` | no | `'active'` | Host lifecycle state, currently `active`, `planned`, or `retired`. |
| `hosts_ip` | `TEXT` | no | `''` | Legacy compatibility column. The current app model keeps one canonical routable IP and mirrors it here. |
| `dns_ip` | `TEXT` | no | `''` | Canonical routable IP for the host. The current UI/API expose this as a single `ip` field. |
| `monitoring` | `TEXT` | no | `'pending'` | Monitoring state. |
| `notes` | `TEXT` | no | `''` | Operator notes. |
| `created_at` | `TEXT` | no | none | ISO UTC creation timestamp. |
| `updated_at` | `TEXT` | no | none | ISO UTC update timestamp. |

## Keys And Indexes

- Primary key: `fqdn`
- Unique key: `legacy_id`

## Relationships

Referenced by:

- `host_aliases.host_fqdn`
- `host_roles.host_fqdn`
- `host_sources.host_fqdn`
- `host_flags.host_fqdn`
- `host_tags.host_fqdn`
- `host_ssh.host_fqdn`
- `vhosts.host_fqdn`
- `dhcp_leases.host_fqdn`
- `mdns_observations.host_fqdn`
- `certificates.host_fqdn`
- `work_order_actions.host_fqdn`

## Definition

```sql
CREATE TABLE IF NOT EXISTS hosts (
    fqdn TEXT PRIMARY KEY,
    legacy_id TEXT NOT NULL UNIQUE,
    status TEXT NOT NULL DEFAULT 'active',
    hosts_ip TEXT NOT NULL DEFAULT '',
    dns_ip TEXT NOT NULL DEFAULT '',
    monitoring TEXT NOT NULL DEFAULT 'pending',
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
```