zsjz-ai/sql/gateway.sql

-- ============================================================================
-- ai-gateway 网关表（按用户粘性路由，见 docs/design/ai-gateway-user-sticky-routing.md）
-- 目标库：PostgreSQL（与 ai-server 共享同一个库，网关表独立于业务表）
-- 执行方式：ai-gateway 启动时由 spring.sql.init 自动执行（本脚本可重复执行）
--
-- 注意：同 user.sql，这是**平台库**表，不登记到 table_info / table_field。
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 1. 服务：一组对外路径前缀 + 一组后端节点
--    首次启动若表为空，用 ai-gateway application.yaml 的 gateway.services 播种；
--    之后以数据库为准，YAML 不再覆盖（变更走 /gateway/admin/** 管理接口）。
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS gateway_service (
    id              VARCHAR(64)  PRIMARY KEY,
    name            VARCHAR(128) NOT NULL,
    -- 对外路径前缀，如 /js/a；全局唯一，不能以 / 结尾、不能是 /gateway
    path_prefix     VARCHAR(128) NOT NULL,
    -- 相对登录路径（相对 path_prefix），如 /auth/login；可空 = 无登录粘性
    login_path      VARCHAR(128),
    -- 完整健康检查路径，如 /js/a/sys/health
    health_path     VARCHAR(255) NOT NULL,
    -- 存量用户兜底节点；网关上线前已存在的用户若未绑定路由则落到该节点
    default_node_id VARCHAR(64),
    enabled         SMALLINT     NOT NULL DEFAULT 1,
    create_time     TIMESTAMP    NOT NULL DEFAULT now(),
    update_time     TIMESTAMP    NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS uk_gateway_service_path_prefix
    ON gateway_service (path_prefix);

COMMENT ON TABLE  gateway_service IS '网关服务定义（路径前缀 -> 节点组）';
COMMENT ON COLUMN gateway_service.login_path IS '相对路径前缀的登录路径，如 /auth/login；空 = 不做登录粘性';
COMMENT ON COLUMN gateway_service.default_node_id IS '存量用户兜底节点 id；无绑定的已登录用户会被绑定到该节点';

-- ---------------------------------------------------------------------------
-- 2. 节点：一台 ai-server 实例
--    base_url 不含路径（context-path 由转发的原始路径携带）；同一服务内地址唯一。
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS gateway_node (
    id          VARCHAR(64)  PRIMARY KEY,
    service_id  VARCHAR(64)  NOT NULL,
    name        VARCHAR(128) NOT NULL,
    base_url    VARCHAR(255) NOT NULL,
    enabled     SMALLINT     NOT NULL DEFAULT 1,
    remark      VARCHAR(512),
    create_time TIMESTAMP    NOT NULL DEFAULT now(),
    update_time TIMESTAMP    NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS uk_gateway_node_service_base_url
    ON gateway_node (service_id, base_url);

COMMENT ON TABLE  gateway_node IS '网关后端节点（一台 ai-server）';
COMMENT ON COLUMN gateway_node.base_url IS 'http(s)://host:port，不含路径';

ALTER TABLE gateway_node
    DROP CONSTRAINT IF EXISTS fk_gateway_node_service;
ALTER TABLE gateway_node
    ADD CONSTRAINT fk_gateway_node_service
    FOREIGN KEY (service_id) REFERENCES gateway_service (id) ON DELETE RESTRICT;

-- ---------------------------------------------------------------------------
-- 3. 用户路由：用户 -> 节点 的粘性绑定
--    user_id / username 至少一个非空；唯一性用「部分唯一索引」保证：
--      - (service_id, user_id)             user_id 非空时唯一
--      - (service_id, lower(username))     username 非空时唯一（用户名不区分大小写，
--                                          与 sys_user 的 uk_sys_user_username 对齐）
--    bindIfAbsent 用不带冲突目标的 ON CONFLICT DO NOTHING，由上面两个索引裁决并发。
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS gateway_user_route (
    id          BIGSERIAL    PRIMARY KEY,
    service_id  VARCHAR(64)  NOT NULL,
    -- sys_user.id；可空（允许只按用户名预绑定）
    user_id     BIGINT,
    username    VARCHAR(64),
    node_id     VARCHAR(64)  NOT NULL,
    create_time TIMESTAMP    NOT NULL DEFAULT now(),
    update_time TIMESTAMP    NOT NULL DEFAULT now(),
    -- user_id、username 至少一个非空
    CONSTRAINT ck_gateway_user_route_owner CHECK (user_id IS NOT NULL OR username IS NOT NULL)
);

CREATE UNIQUE INDEX IF NOT EXISTS uk_gateway_user_route_user
    ON gateway_user_route (service_id, user_id) WHERE user_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uk_gateway_user_route_name
    ON gateway_user_route (service_id, lower(username)) WHERE username IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_gateway_user_route_node
    ON gateway_user_route (node_id);

COMMENT ON TABLE  gateway_user_route IS '网关用户粘性路由（用户 -> 节点）';
COMMENT ON COLUMN gateway_user_route.node_id IS '绑定的目标节点；节点不可用时网关返回 503，不自动切换';

ALTER TABLE gateway_user_route
    DROP CONSTRAINT IF EXISTS fk_gateway_user_route_service;
ALTER TABLE gateway_user_route
    ADD CONSTRAINT fk_gateway_user_route_service
    FOREIGN KEY (service_id) REFERENCES gateway_service (id) ON DELETE RESTRICT;

ALTER TABLE gateway_user_route
    DROP CONSTRAINT IF EXISTS fk_gateway_user_route_node;
ALTER TABLE gateway_user_route
    ADD CONSTRAINT fk_gateway_user_route_node
    FOREIGN KEY (node_id) REFERENCES gateway_node (id) ON DELETE RESTRICT;