57 lines
2.7 KiB
PL/PgSQL
57 lines
2.7 KiB
PL/PgSQL
-- Task-scoped, reviewable data access requests for dynamic investigations.
|
|
-- This migration is deliberately restricted to the verified tjwater_v2 BizDB.
|
|
BEGIN;
|
|
SET LOCAL lock_timeout = '5s';
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF current_database() <> 'tjwater_v2' THEN
|
|
RAISE EXCEPTION 'Operational access-request migration is restricted to tjwater_v2';
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE TABLE IF NOT EXISTS workbench.operational_task_access_requests (
|
|
project_id uuid NOT NULL,
|
|
task_id uuid NOT NULL,
|
|
access_request_id uuid NOT NULL,
|
|
node_id text NOT NULL CHECK (length(node_id) > 0),
|
|
status text NOT NULL CHECK (status IN ('pending','approved','rejected')),
|
|
version bigint NOT NULL CHECK (version >= 1),
|
|
document jsonb NOT NULL,
|
|
requested_at timestamptz NOT NULL,
|
|
updated_at timestamptz NOT NULL,
|
|
PRIMARY KEY (project_id,task_id,access_request_id),
|
|
FOREIGN KEY (project_id,task_id)
|
|
REFERENCES workbench.operational_tasks(project_id,task_id) ON DELETE CASCADE,
|
|
CHECK ((document->>'access_request_id'=access_request_id::text
|
|
AND document->>'status'=status
|
|
AND (document->>'version')::bigint=version) IS TRUE)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS operational_task_access_pending_idx
|
|
ON workbench.operational_task_access_requests(project_id,status,requested_at)
|
|
WHERE status='pending';
|
|
|
|
COMMENT ON TABLE workbench.operational_task_access_requests IS
|
|
'动态调查中的任务级数据访问申请；与动作审批分离，保存申请范围及人工审核结果。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.project_id IS
|
|
'所属项目标识；当前仍要求一个项目独占一个 BizDB。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.task_id IS
|
|
'所属工况任务标识。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.access_request_id IS
|
|
'访问申请的稳定标识，同时用于计划审核节点引用。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.node_id IS
|
|
'发起申请的执行计划节点标识。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.status IS
|
|
'人工审核状态：pending、approved 或 rejected。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.version IS
|
|
'用于乐观并发控制的申请修订号。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.document IS
|
|
'经过接口模型校验的完整访问申请文档。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.requested_at IS
|
|
'Agent 发起访问申请的时间。';
|
|
COMMENT ON COLUMN workbench.operational_task_access_requests.updated_at IS
|
|
'申请最近一次状态变更时间。';
|
|
|
|
COMMIT;
|