# Table: `mdns_observations`

Stores observed mDNS records.

## Columns

| Column | Type | Null | Default | Notes |
|--------|------|------|---------|-------|
| `observation_key` | `TEXT` | no | none | Stable 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 | none | Observed mDNS name. |
| `ip_address` | `TEXT` | no | none | Observed IP address. |
| `rr_type` | `TEXT` | no | `'A'` | DNS record type. |
| `ttl` | `INTEGER` | no | `0` | Observed TTL. |
| `first_seen` | `TEXT` | no | none | First observation timestamp. |
| `last_seen` | `TEXT` | no | none | Last observation timestamp. |
| `seen_count` | `INTEGER` | no | `1` | Number of times observed. |
| `last_peer` | `TEXT` | no | `''` | Last sender peer. |
| `raw` | `TEXT` | no | `''` | Raw source payload or diagnostic text. |

## Keys And Indexes

- Primary key: `observation_key`
- `idx_mdns_observations_name` on `observed_name`
- `idx_mdns_observations_ip` on `ip_address`
- `idx_mdns_observations_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 mdns_observations (
    observation_key TEXT PRIMARY KEY,
    worker_id TEXT NOT NULL,
    host_fqdn TEXT,
    observed_name TEXT NOT NULL,
    ip_address TEXT NOT NULL,
    rr_type TEXT NOT NULL DEFAULT 'A',
    ttl INTEGER NOT NULL DEFAULT 0,
    first_seen TEXT NOT NULL,
    last_seen TEXT NOT NULL,
    seen_count INTEGER NOT NULL DEFAULT 1,
    last_peer TEXT NOT NULL DEFAULT '',
    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_mdns_observations_name ON mdns_observations(observed_name);
CREATE INDEX IF NOT EXISTS idx_mdns_observations_ip ON mdns_observations(ip_address);
CREATE INDEX IF NOT EXISTS idx_mdns_observations_worker_last_seen
ON mdns_observations(worker_id, last_seen);
```