BEGIN;
CREATE TABLE IF NOT EXISTS mcp_routing_observations (
 id uuid PRIMARY KEY, src_server_id bigint NOT NULL, dst_server_id bigint NOT NULL,
 src_interface text NOT NULL, dst_interface text NOT NULL, family text NOT NULL CHECK(family IN ('ipv4','ipv6')),
 routing_table text NOT NULL DEFAULT 'main', ping_ms double precision, loss_pct double precision,
 dl_mbps double precision, ul_mbps double precision, ping_at timestamptz, bandwidth_at timestamptz,
 context_verified boolean NOT NULL DEFAULT false, source text NOT NULL,
 created_at timestamptz NOT NULL DEFAULT now(), CHECK(ping_ms IS NULL OR ping_ms>=0),
 CHECK(loss_pct IS NULL OR loss_pct BETWEEN 0 AND 100), CHECK(dl_mbps IS NULL OR dl_mbps>=0), CHECK(ul_mbps IS NULL OR ul_mbps>=0)
);
CREATE INDEX IF NOT EXISTS mcp_routing_observations_leg ON mcp_routing_observations(src_server_id,dst_server_id,src_interface,family,routing_table,created_at DESC);
COMMIT;