superidou
5759c1862e
M0-M7 已完成:核心网关(身份/RBAC/TOTP/OIDC/SAML/Provider/配额/路由/内容策略/审计/定价)+ 资源市场(MCP/Skills/数字员工)。 含 22 个 PostgreSQL 迁移、管理端/门户端前端源码、OpenAPI 契约、部署 compose。 Co-Authored-By: Claude <noreply@anthropic.com>
50 lines
2.4 KiB
SQL
50 lines
2.4 KiB
SQL
ALTER TABLE gateway.api_keys
|
|
ADD COLUMN IF NOT EXISTS application_id uuid REFERENCES gateway.applications(id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS api_keys_application_idx
|
|
ON gateway.api_keys (application_id, enabled)
|
|
WHERE application_id IS NOT NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS gateway.application_runtime_credentials (
|
|
application_id uuid NOT NULL REFERENCES gateway.applications(id) ON DELETE CASCADE,
|
|
department_id uuid REFERENCES gateway.departments(id) ON DELETE CASCADE,
|
|
api_key_id uuid NOT NULL UNIQUE REFERENCES gateway.api_keys(id) ON DELETE CASCADE,
|
|
encrypted_key bytea NOT NULL,
|
|
key_kek_version integer NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
UNIQUE NULLS NOT DISTINCT (application_id, department_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS gateway.portal_conversations (
|
|
id uuid PRIMARY KEY,
|
|
portal_user_id uuid NOT NULL REFERENCES gateway.portal_users(id) ON DELETE CASCADE,
|
|
application_id uuid NOT NULL REFERENCES gateway.applications(id) ON DELETE CASCADE,
|
|
title text NOT NULL DEFAULT '新会话' CHECK (length(title) BETWEEN 1 AND 160),
|
|
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
|
|
busy boolean NOT NULL DEFAULT false,
|
|
busy_token uuid,
|
|
busy_since timestamptz,
|
|
next_sequence integer NOT NULL DEFAULT 1 CHECK (next_sequence BETWEEN 1 AND 202),
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
updated_at timestamptz NOT NULL DEFAULT clock_timestamp()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS portal_conversations_user_time_idx
|
|
ON gateway.portal_conversations (portal_user_id, updated_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS gateway.portal_conversation_messages (
|
|
id uuid PRIMARY KEY,
|
|
conversation_id uuid NOT NULL REFERENCES gateway.portal_conversations(id) ON DELETE CASCADE,
|
|
sequence integer NOT NULL CHECK (sequence BETWEEN 1 AND 200),
|
|
role text NOT NULL CHECK (role IN ('user','assistant')),
|
|
content text NOT NULL CHECK (length(content) BETWEEN 1 AND 100000),
|
|
previous_hash char(64) NOT NULL,
|
|
message_hash char(64) NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
UNIQUE (conversation_id, sequence)
|
|
);
|
|
|
|
COMMENT ON TABLE gateway.portal_conversation_messages IS
|
|
'Server-managed immutable conversation history. A SHA-256 hash chain detects database-side sequence/content corruption before replay.';
|