jiang
c7947a7481
Remove local auth and user-management endpoints in favor of Keycloak-backed metadata users, project context, admin metadata APIs, and agent auth context.
56 lines
2.1 KiB
PL/PgSQL
56 lines
2.1 KiB
PL/PgSQL
-- Metadata project configuration schema patch.
|
|
-- Admin APIs write these tables; operators should not hand-edit encrypted values.
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
gs_workspace VARCHAR(100) UNIQUE NOT NULL,
|
|
map_extent JSONB,
|
|
status VARCHAR(20) DEFAULT 'active' NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
CONSTRAINT projects_status_check CHECK (status IN ('active', 'inactive', 'archived'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_code ON projects(code);
|
|
|
|
DROP TRIGGER IF EXISTS update_projects_updated_at ON projects;
|
|
CREATE TRIGGER update_projects_updated_at
|
|
BEFORE UPDATE ON projects
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TABLE IF NOT EXISTS project_databases (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
db_role VARCHAR(20) NOT NULL,
|
|
db_type VARCHAR(20) NOT NULL,
|
|
dsn_encrypted TEXT NOT NULL,
|
|
pool_min_size INTEGER DEFAULT 2 NOT NULL,
|
|
pool_max_size INTEGER DEFAULT 10 NOT NULL,
|
|
CONSTRAINT project_databases_unique_role UNIQUE (project_id, db_role),
|
|
CONSTRAINT project_databases_role_check CHECK (db_role IN ('biz_data', 'iot_data')),
|
|
CONSTRAINT project_databases_type_check CHECK (db_type IN ('postgresql', 'timescaledb')),
|
|
CONSTRAINT project_databases_pool_check CHECK (
|
|
pool_min_size >= 1 AND pool_max_size >= pool_min_size
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_project_databases_project_id
|
|
ON project_databases(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_databases_role
|
|
ON project_databases(db_role);
|