# Table: `dhcp_leases`

Stores observed DHCP lease/reservation data.

## Columns

| Column | Type | Null | Default | Notes |
|--------|------|------|---------|-------|
| `lease_key` | `TEXT` | no | none | Stable lease observation key. Primary key. |
| `worker_id` | `TEXT` | no | none | Collector worker. References `data_workers(worker_id)`. |
| `host_fqdn` | `TEXT` | yes | `NULL` | Matched host if known. References `hosts(fqdn)`. |
| `observed_name` | `TEXT` | no | `''` | Name reported by DHCP. |
| `ip_address` | `TEXT` | no | none | Observed IP address. |
| `mac_address` | `TEXT` | no | `''` | Observed MAC address. |
| `lease_state` | `TEXT` | no | `''` | DHCP state, if known. |
| `first_seen` | `TEXT` | no | none | First observation timestamp. |
| `last_seen` | `TEXT` | no | none | Last observation timestamp. |
| `raw` | `TEXT` | no | `''` | Raw source payload or diagnostic text. |

## Keys And Indexes

- Primary key: `lease_key`
- `idx_dhcp_leases_ip` on `ip_address`
- `idx_dhcp_leases_mac` on `mac_address`
- `idx_dhcp_leases_worker_last_seen` on `(worker_id, last_seen)`

## Relationships

- `worker_id` references `data_workers(worker_id)` with `ON UPDATE CASCADE ON DELETE RESTRICT`
- `host_fqdn` references `hosts(fqdn)` with `ON UPDATE CASCADE ON DELETE SET NULL`

## Definition

```sql
CREATE TABLE IF NOT EXISTS dhcp_leases (
    lease_key TEXT PRIMARY KEY,
    worker_id TEXT NOT NULL,
    host_fqdn TEXT,
    observed_name TEXT NOT NULL DEFAULT '',
    ip_address TEXT NOT NULL,
    mac_address TEXT NOT NULL DEFAULT '',
    lease_state TEXT NOT NULL DEFAULT '',
    first_seen TEXT NOT NULL,
    last_seen TEXT NOT NULL,
    raw TEXT NOT NULL DEFAULT '',
    FOREIGN KEY (worker_id) REFERENCES data_workers(worker_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_dhcp_leases_ip ON dhcp_leases(ip_address);
CREATE INDEX IF NOT EXISTS idx_dhcp_leases_mac ON dhcp_leases(mac_address);
CREATE INDEX IF NOT EXISTS idx_dhcp_leases_worker_last_seen
ON dhcp_leases(worker_id, last_seen);
```