create table asset_observations
(
    id               bigint generated by default as identity primary key,
    asset_id         bigint references hosts (id) on update cascade on delete set null,
    scan_id          bigint references tasks (id) on update cascade on delete set null,
    observed_ip      inet        not null,
    fingerprint_hash text,
    confidence       smallint    not null check (confidence in (1, 2, 3)),
    evidence         jsonb       not null default '{}'::jsonb,
    observed_at      timestamptz not null default now()
);

create index asset_observations_asset_id_idx on asset_observations (asset_id);
create index asset_observations_scan_id_idx on asset_observations (scan_id);
create index asset_observations_observed_ip_idx on asset_observations (observed_ip);
create index asset_observations_fingerprint_hash_idx on asset_observations (fingerprint_hash);
create index asset_observations_confidence_idx on asset_observations (confidence);
