29d0310f03
Add new frontend pages for the multi-tenant OCDP platform: - Charts page (/charts): Browse Harbor OCI registries to list Helm chart repositories and versions, with deploy modal to launch charts on selected clusters - Monitoring page (/monitoring): Display cluster metrics (CPU/Memory/GPU usage) and per-node details with resource utilization bars - Chart References page (/chart-references): CRUD for chart metadata references - Values Templates page (/templates): CRUD for Helm values templates with version history and rollback support - Sidebar: Add Charts navigation, update Storage and Templates links - api.ts: Add all API client functions (clusterApi, registryApi, instanceApi, monitoringApi, storageApi, chartReferenceApi, valuesTemplateApi, workspaceApi, userApi) with full TypeScript types Note: deploy flow and values template rollback not yet end-to-end tested.
190 lines
7.8 KiB
SQL
190 lines
7.8 KiB
SQL
-- OCDP Multi-Tenant Migration Script
|
|
-- Adds multi-tenant fields and new tables for workspace isolation
|
|
|
|
-- ===== Phase 1: Add new columns to existing tables =====
|
|
|
|
-- Add multi-tenant fields to users table
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS role VARCHAR(20) NOT NULL DEFAULT 'user';
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS workspace_id VARCHAR(36);
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT TRUE;
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS must_change_password BOOLEAN NOT NULL DEFAULT FALSE;
|
|
|
|
-- Add indexes for new user fields
|
|
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
|
|
CREATE INDEX IF NOT EXISTS idx_users_workspace_id ON users(workspace_id);
|
|
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
|
|
|
|
-- Add multi-tenant fields to clusters table
|
|
ALTER TABLE clusters ADD COLUMN IF NOT EXISTS workspace_id VARCHAR(36);
|
|
ALTER TABLE clusters ADD COLUMN IF NOT EXISTS owner_id VARCHAR(36);
|
|
ALTER TABLE clusters ADD COLUMN IF NOT EXISTS isolation_mode VARCHAR(20) NOT NULL DEFAULT 'namespace';
|
|
ALTER TABLE clusters ADD COLUMN IF NOT EXISTS default_namespace VARCHAR(255);
|
|
ALTER TABLE clusters ADD COLUMN IF NOT EXISTS is_shared BOOLEAN NOT NULL DEFAULT FALSE;
|
|
|
|
-- Add index for cluster workspace
|
|
CREATE INDEX IF NOT EXISTS idx_clusters_workspace_id ON clusters(workspace_id);
|
|
|
|
-- Add multi-tenant fields to registries table
|
|
ALTER TABLE registries ADD COLUMN IF NOT EXISTS workspace_id VARCHAR(36);
|
|
ALTER TABLE registries ADD COLUMN IF NOT EXISTS owner_id VARCHAR(36);
|
|
ALTER TABLE registries ADD COLUMN IF NOT EXISTS is_shared BOOLEAN NOT NULL DEFAULT FALSE;
|
|
|
|
-- Add index for registry workspace
|
|
CREATE INDEX IF NOT EXISTS idx_registries_workspace_id ON registries(workspace_id);
|
|
|
|
-- Add multi-tenant fields to instances table
|
|
ALTER TABLE instances ADD COLUMN IF NOT EXISTS workspace_id VARCHAR(36);
|
|
ALTER TABLE instances ADD COLUMN IF NOT EXISTS owner_id VARCHAR(36);
|
|
ALTER TABLE instances ADD COLUMN IF NOT EXISTS values_template_id VARCHAR(36);
|
|
ALTER TABLE instances ADD COLUMN IF NOT EXISTS user_override_yaml TEXT;
|
|
ALTER TABLE instances ADD COLUMN IF NOT EXISTS cpu_requested DECIMAL(10,2) NOT NULL DEFAULT 0;
|
|
ALTER TABLE instances ADD COLUMN IF NOT EXISTS memory_requested VARCHAR(50) NOT NULL DEFAULT '0Mi';
|
|
ALTER TABLE instances ADD COLUMN IF NOT EXISTS gpu_requested DECIMAL(10,2) NOT NULL DEFAULT 0;
|
|
ALTER TABLE instances ADD COLUMN IF NOT EXISTS gpu_memory_requested VARCHAR(50) NOT NULL DEFAULT '0Mi';
|
|
|
|
-- Add index for instance workspace
|
|
CREATE INDEX IF NOT EXISTS idx_instances_workspace_id ON instances(workspace_id);
|
|
|
|
-- ===== Phase 2: Create new tables =====
|
|
|
|
-- Create workspaces table
|
|
CREATE TABLE IF NOT EXISTS workspaces (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL UNIQUE,
|
|
description TEXT,
|
|
created_by VARCHAR(36),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_workspaces_name ON workspaces(name);
|
|
|
|
-- Create workspace_quotas table
|
|
CREATE TABLE IF NOT EXISTS workspace_quotas (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
workspace_id VARCHAR(36) NOT NULL,
|
|
resource_type VARCHAR(50) NOT NULL,
|
|
hard_limit DECIMAL(10,2) NOT NULL,
|
|
soft_limit DECIMAL(10,2) NOT NULL,
|
|
used DECIMAL(10,2) NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(workspace_id, resource_type),
|
|
CONSTRAINT fk_workspace_quotas_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_workspace_quotas_workspace_id ON workspace_quotas(workspace_id);
|
|
|
|
-- Create storage_backends table
|
|
CREATE TABLE IF NOT EXISTS storage_backends (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
workspace_id VARCHAR(36),
|
|
owner_id VARCHAR(36),
|
|
name VARCHAR(255) NOT NULL,
|
|
type VARCHAR(50) NOT NULL,
|
|
config JSONB NOT NULL,
|
|
description TEXT,
|
|
is_default BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_shared BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(workspace_id, name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_storage_backends_workspace_id ON storage_backends(workspace_id);
|
|
|
|
-- Create chart_references table
|
|
CREATE TABLE IF NOT EXISTS chart_references (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
workspace_id VARCHAR(36),
|
|
registry_id VARCHAR(36),
|
|
repository VARCHAR(500) NOT NULL,
|
|
chart_name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(workspace_id, registry_id, repository)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chart_references_workspace_id ON chart_references(workspace_id);
|
|
CREATE INDEX IF NOT EXISTS idx_chart_references_registry_id ON chart_references(registry_id);
|
|
|
|
-- Create values_templates table
|
|
CREATE TABLE IF NOT EXISTS values_templates (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
workspace_id VARCHAR(36),
|
|
owner_id VARCHAR(36),
|
|
chart_reference_id VARCHAR(36),
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
values_yaml TEXT NOT NULL,
|
|
version INTEGER NOT NULL DEFAULT 1,
|
|
is_default BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(workspace_id, chart_reference_id, name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_values_templates_workspace_id ON values_templates(workspace_id);
|
|
CREATE INDEX IF NOT EXISTS idx_values_templates_chart_reference_id ON values_templates(chart_reference_id);
|
|
|
|
-- Create user_config_overrides table
|
|
CREATE TABLE IF NOT EXISTS user_config_overrides (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
workspace_id VARCHAR(36),
|
|
user_id VARCHAR(36),
|
|
target_type VARCHAR(50) NOT NULL,
|
|
target_id VARCHAR(36),
|
|
config JSONB NOT NULL,
|
|
priority INTEGER NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_config_overrides_workspace_id ON user_config_overrides(workspace_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_config_overrides_user_id ON user_config_overrides(user_id);
|
|
|
|
-- Create audit_logs table
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
workspace_id VARCHAR(36),
|
|
user_id VARCHAR(36),
|
|
action VARCHAR(100) NOT NULL,
|
|
resource_type VARCHAR(50) NOT NULL,
|
|
resource_id VARCHAR(36),
|
|
resource_name VARCHAR(255),
|
|
details JSONB,
|
|
ip_address VARCHAR(50),
|
|
user_agent TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_workspace_id ON audit_logs(workspace_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON audit_logs(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at);
|
|
|
|
-- ===== Phase 3: Create admin user =====
|
|
-- Note: Default password is 'admin123' (bcrypt hash will be set by application)
|
|
-- The admin user will have NULL workspace_id to indicate global access
|
|
|
|
INSERT INTO users (id, username, password_hash, email, role, workspace_id, is_active, must_change_password)
|
|
VALUES (
|
|
'00000000-0000-0000-0000-000000000001',
|
|
'admin',
|
|
'$2a$10$placeholder', -- Replace with actual bcrypt hash in production
|
|
'admin@ocdp.local',
|
|
'admin',
|
|
NULL,
|
|
TRUE,
|
|
TRUE
|
|
) ON CONFLICT (username) DO NOTHING;
|
|
|
|
-- Update schema version
|
|
INSERT INTO schema_migrations (version) VALUES ('v2.0.0-multi-tenant')
|
|
ON CONFLICT (version) DO NOTHING;
|
|
|
|
-- Grant permissions (adjust as needed for your setup)
|
|
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ocdp_user;
|
|
-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ocdp_user; |