多 Agent 共享记忆与项目协作

CREATE SCHEMA core;

-- 租户表（企业 / 团队）
CREATE TABLE core.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    plan TEXT DEFAULT 'free',
    config JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Agent 注册表
CREATE TABLE core.agents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core.tenants(id),
    name TEXT NOT NULL,
    role TEXT NOT NULL,          -- planner / researcher / coder / reviewer
    capabilities TEXT[],         -- 能力标签
    config JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 项目表
CREATE TABLE core.projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core.tenants(id),
    name TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 项目成员（Agent 参与项目）
CREATE TABLE core.project_members (
    project_id UUID REFERENCES core.projects(id),
    agent_id UUID REFERENCES core.agents(id),
    role TEXT DEFAULT 'member',  -- owner / member / readonly
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (project_id, agent_id)
);

-- 项目级共享记忆
CREATE TABLE memory.shared_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    project_id UUID NOT NULL,
    author_agent_id UUID NOT NULL,   -- 记忆创建者
    content TEXT NOT NULL,
    embedding vector(1024),
    record_type TEXT DEFAULT 'finding',  -- finding / decision / context / artifact
    visibility TEXT DEFAULT 'project',   -- project / team / private
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- RLS：同项目的 Agent 可以互相访问记忆
ALTER TABLE memory.shared_records ENABLE ROW LEVEL SECURITY;

CREATE POLICY project_access ON memory.shared_records
    USING (
        project_id IN (
            SELECT project_id FROM core.project_members
            WHERE agent_id = current_setting('app.agent_id')::uuid
        )
    );

-- Researcher Agent 写入调研结论
SET app.agent_id = 'researcher-001';

INSERT INTO memory.shared_records
    (tenant_id, project_id, author_agent_id, content, embedding, record_type)
VALUES (
    :tenant_id,
    :project_id,
    'researcher-001',
    '竞品 PolarDB 的向量检索性能在 100 万级数据下 QPS 约 2000',
    tencentdb_ai.get_embedding('bge-m3', '竞品 PolarDB 向量检索性能'),
    'finding'
);

-- Planner Agent 检索 Researcher 的发现
SET app.agent_id = 'planner-001';

SELECT content, author_agent_id, created_at
FROM memory.shared_records
WHERE project_id = :project_id
ORDER BY embedding <=> tencentdb_ai.get_embedding('bge-m3', '竞品性能数据')
LIMIT 5;

-- 任务表
CREATE TABLE core.tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    assigner_id UUID NOT NULL,   -- 分配者
    assignee_id UUID NOT NULL,   -- 执行者
    title TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'pending',  -- pending / in_progress / completed / failed
    result TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);

-- Planner 分配任务给 Researcher
INSERT INTO core.tasks (project_id, assigner_id, assignee_id, title, description)
VALUES (:project_id, 'planner-001', 'researcher-001',
        '调研 DiskANN 索引性能', '对比 HNSW 和 DiskANN 在 1000 万向量下的性能');

-- Researcher 完成后更新
UPDATE core.tasks
SET status = 'completed',
    result = 'DiskANN 在 1000 万向量下 QPS 提升 40%，内存节省 60%',
    completed_at = NOW()
WHERE id = :task_id;

-- 记录团队决策
INSERT INTO memory.shared_records
    (tenant_id, project_id, author_agent_id, content, record_type, metadata)
VALUES (
    :tenant_id, :project_id, 'planner-001',
    '决定采用 pgvectorscale DiskANN 作为向量索引方案',
    'decision',
    '{"reason": "性能更优，内存更省", "alternatives": ["HNSW", "IVFFlat"]}'
);

-- readonly 成员只能读取，不能写入
CREATE POLICY readonly_member ON memory.shared_records
    FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1 FROM core.project_members
            WHERE project_id = memory.shared_records.project_id
              AND agent_id = current_setting('app.agent_id')::uuid
              AND role IN ('owner', 'member')
        )
    );