46 lines
2.1 KiB
SQL
46 lines
2.1 KiB
SQL
CREATE TABLE IF NOT EXISTS fabric_service_channel_route_feedback_observations (
|
|
id UUID PRIMARY KEY,
|
|
cluster_id UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
|
|
reporter_node_id UUID NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
|
route_id TEXT NOT NULL,
|
|
service_class TEXT NOT NULL,
|
|
feedback_status TEXT NOT NULL,
|
|
score_adjustment INTEGER NOT NULL DEFAULT 0,
|
|
reasons TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
|
|
last_error TEXT NOT NULL DEFAULT '',
|
|
consecutive_failures INTEGER NOT NULL DEFAULT 0,
|
|
stall_count INTEGER NOT NULL DEFAULT 0,
|
|
last_send_duration_ms BIGINT NOT NULL DEFAULT 0,
|
|
payload JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
observed_at TIMESTAMPTZ NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_fsc_route_feedback_observed
|
|
ON fabric_service_channel_route_feedback_observations (cluster_id, reporter_node_id, service_class, observed_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_fsc_route_feedback_route
|
|
ON fabric_service_channel_route_feedback_observations (cluster_id, route_id, observed_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS fabric_service_channel_route_feedback_latest (
|
|
cluster_id UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
|
|
reporter_node_id UUID NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
|
route_id TEXT NOT NULL,
|
|
observation_id UUID NOT NULL REFERENCES fabric_service_channel_route_feedback_observations(id) ON DELETE CASCADE,
|
|
service_class TEXT NOT NULL,
|
|
feedback_status TEXT NOT NULL,
|
|
score_adjustment INTEGER NOT NULL DEFAULT 0,
|
|
reasons TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
|
|
last_error TEXT NOT NULL DEFAULT '',
|
|
consecutive_failures INTEGER NOT NULL DEFAULT 0,
|
|
stall_count INTEGER NOT NULL DEFAULT 0,
|
|
last_send_duration_ms BIGINT NOT NULL DEFAULT 0,
|
|
payload JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
observed_at TIMESTAMPTZ NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
PRIMARY KEY (cluster_id, reporter_node_id, route_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_fsc_route_feedback_latest_active
|
|
ON fabric_service_channel_route_feedback_latest (cluster_id, reporter_node_id, service_class, expires_at DESC);
|