41 lines
1.9 KiB
SQL
41 lines
1.9 KiB
SQL
CREATE TABLE IF NOT EXISTS cluster_authority_states (
|
|
cluster_id UUID PRIMARY KEY REFERENCES clusters(id) ON DELETE CASCADE,
|
|
authority_state TEXT NOT NULL DEFAULT 'authoritative',
|
|
mutation_mode TEXT NOT NULL DEFAULT 'normal',
|
|
term BIGINT NOT NULL DEFAULT 1,
|
|
notes TEXT,
|
|
updated_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT cluster_authority_states_authority_check
|
|
CHECK (authority_state IN ('authoritative', 'minority', 'isolated', 'recovery')),
|
|
CONSTRAINT cluster_authority_states_mutation_check
|
|
CHECK (mutation_mode IN ('normal', 'read_only', 'recovery_override'))
|
|
);
|
|
|
|
INSERT INTO cluster_authority_states (cluster_id, authority_state, mutation_mode, term, notes)
|
|
SELECT id, 'authoritative', 'normal', 1, 'default authority state'
|
|
FROM clusters
|
|
ON CONFLICT (cluster_id) DO NOTHING;
|
|
|
|
CREATE OR REPLACE VIEW cluster_admin_summaries AS
|
|
SELECT
|
|
c.id AS cluster_id,
|
|
c.slug,
|
|
c.name,
|
|
c.status,
|
|
c.region,
|
|
COALESCE(cas.authority_state, 'authoritative') AS authority_state,
|
|
COALESCE(cas.mutation_mode, 'normal') AS mutation_mode,
|
|
COUNT(DISTINCT cm.node_id) AS node_count,
|
|
COUNT(DISTINCT CASE WHEN n.health_status = 'healthy' THEN n.id END) AS healthy_node_count,
|
|
COUNT(DISTINCT CASE WHEN njr.status = 'pending' THEN njr.id END) AS pending_join_count,
|
|
COUNT(DISTINCT nra.id) AS active_role_assignment_count,
|
|
MAX(n.last_seen_at) AS last_node_seen_at
|
|
FROM clusters c
|
|
LEFT JOIN cluster_authority_states cas ON cas.cluster_id = c.id
|
|
LEFT JOIN cluster_memberships cm ON cm.cluster_id = c.id
|
|
LEFT JOIN nodes n ON n.id = cm.node_id
|
|
LEFT JOIN node_join_requests njr ON njr.cluster_id = c.id
|
|
LEFT JOIN node_role_assignments nra ON nra.cluster_id = c.id AND nra.status = 'active'
|
|
GROUP BY c.id, c.slug, c.name, c.status, c.region, cas.authority_state, cas.mutation_mode;
|