Agent-System/deploy/postgres/goose/00017_2026-09-12-project-workflow-instances.sql

-- +goose Up
-- Phase 3: 项目工作流实例、规划态与激活确认

ALTER TABLE projects ADD COLUMN IF NOT EXISTS lifecycle_status TEXT NOT NULL DEFAULT 'planning';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS workflow_plan_revision INT NOT NULL DEFAULT 0;

-- 历史项目：已有激活工作流视为 active 生命周期
UPDATE projects
SET lifecycle_status = 'active'
WHERE COALESCE(active_workflow_key, '') <> ''
  AND active_workflow_key <> '__none__';

CREATE TABLE IF NOT EXISTS project_workflow_instances (
    id                  BIGSERIAL PRIMARY KEY,
    project_id          BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    workflow_version_id BIGINT NOT NULL REFERENCES workflow_versions(id),
    instance_no         INT NOT NULL DEFAULT 1,
    status              TEXT NOT NULL DEFAULT 'planning',
    config_revision     INT NOT NULL DEFAULT 1,
    plan_hash           TEXT NOT NULL DEFAULT '',
    config_snapshot     JSONB NOT NULL DEFAULT '{}'::jsonb,
    activated_by        BIGINT REFERENCES users(id) ON DELETE SET NULL,
    activated_at        TIMESTAMPTZ,
    created_by          BIGINT REFERENCES users(id) ON DELETE SET NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_project_workflow_instance_no UNIQUE (project_id, instance_no),
    CONSTRAINT chk_pwi_status CHECK (status IN ('planning', 'active', 'awaiting_stage_confirmation', 'completed', 'terminated', 'termination_requested'))
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_project_one_planning_instance
    ON project_workflow_instances(project_id)
    WHERE status = 'planning';

CREATE UNIQUE INDEX IF NOT EXISTS uq_project_one_live_instance
    ON project_workflow_instances(project_id)
    WHERE status IN ('active', 'awaiting_stage_confirmation', 'termination_requested');

CREATE INDEX IF NOT EXISTS idx_pwi_project ON project_workflow_instances(project_id);
CREATE INDEX IF NOT EXISTS idx_pwi_status ON project_workflow_instances(status);

CREATE TABLE IF NOT EXISTS project_stage_instances (
    id                   BIGSERIAL PRIMARY KEY,
    workflow_instance_id BIGINT NOT NULL REFERENCES project_workflow_instances(id) ON DELETE CASCADE,
    stage_key            TEXT NOT NULL,
    position             INT NOT NULL DEFAULT 0,
    enabled              BOOLEAN NOT NULL DEFAULT true,
    status               TEXT NOT NULL DEFAULT 'pending',
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_psi_stage UNIQUE (workflow_instance_id, stage_key),
    CONSTRAINT chk_psi_status CHECK (status IN ('pending', 'active', 'completed', 'skipped'))
);

CREATE INDEX IF NOT EXISTS idx_psi_wfi ON project_stage_instances(workflow_instance_id);

CREATE TABLE IF NOT EXISTS project_doc_task_instances (
    id                 BIGSERIAL PRIMARY KEY,
    stage_instance_id  BIGINT NOT NULL REFERENCES project_stage_instances(id) ON DELETE CASCADE,
    task_key           TEXT NOT NULL,
    enabled            BOOLEAN NOT NULL DEFAULT true,
    template_asset_key TEXT NOT NULL DEFAULT '',
    template_version   INT NOT NULL DEFAULT 0,
    output_doc_type    TEXT NOT NULL DEFAULT '',
    status             TEXT NOT NULL DEFAULT 'pending',
    created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_pdti_task UNIQUE (stage_instance_id, task_key),
    CONSTRAINT chk_pdti_status CHECK (status IN ('pending', 'in_progress', 'reviewing', 'approved', 'skipped'))
);

CREATE INDEX IF NOT EXISTS idx_pdti_stage ON project_doc_task_instances(stage_instance_id);

CREATE TABLE IF NOT EXISTS document_duty_assignments (
    id                   BIGSERIAL PRIMARY KEY,
    doc_task_instance_id BIGINT NOT NULL REFERENCES project_doc_task_instances(id) ON DELETE CASCADE,
    duty_role            TEXT NOT NULL,
    user_id              BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    assigned_by          BIGINT REFERENCES users(id) ON DELETE SET NULL,
    assigned_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_dda_role UNIQUE (doc_task_instance_id, duty_role),
    CONSTRAINT chk_dda_role CHECK (duty_role IN ('author', 'reviewer', 'approver'))
);

CREATE INDEX IF NOT EXISTS idx_dda_user ON document_duty_assignments(user_id);

CREATE TABLE IF NOT EXISTS workflow_activation_tokens (
    token                    TEXT PRIMARY KEY,
    project_id               BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    plan_hash                TEXT NOT NULL,
    expected_project_version INT NOT NULL,
    config_snapshot          JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_by               BIGINT REFERENCES users(id) ON DELETE SET NULL,
    expires_at               TIMESTAMPTZ NOT NULL,
    used_at                  TIMESTAMPTZ,
    created_at               TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_wat_project ON workflow_activation_tokens(project_id);

INSERT INTO system_permissions (perm_key, resource_type, action, risk_level) VALUES
    ('project.settings.view', 'project', 'settings_view', 'normal'),
    ('project.settings.edit', 'project', 'settings_edit', 'elevated'),
    ('project.workflow.activate', 'project', 'workflow_activate', 'critical')
ON CONFLICT (perm_key) DO NOTHING;

INSERT INTO system_role_permissions (role_id, permission_id, effect)
SELECT 'role-admin', p.id, 'allow'
FROM system_permissions p
WHERE p.perm_key IN ('project.settings.view', 'project.settings.edit', 'project.workflow.activate')
ON CONFLICT DO NOTHING;

-- +goose Down
DROP TABLE IF EXISTS workflow_activation_tokens;
DROP TABLE IF EXISTS document_duty_assignments;
DROP TABLE IF EXISTS project_doc_task_instances;
DROP TABLE IF EXISTS project_stage_instances;
DROP TABLE IF EXISTS project_workflow_instances;
ALTER TABLE projects DROP COLUMN IF EXISTS workflow_plan_revision;
ALTER TABLE projects DROP COLUMN IF EXISTS lifecycle_status;