# DEB-340: Core: Knowledge Base Setup and CRUD operations on Knowledge Base for Org Info

> **Jira:** [DEB-340](https://divami.atlassian.net/browse/DEB-340)  
> **Source Report:** [20260315.md](../../reports/jira/20260315.md)

- **Status:** Start
- **Assignee:** Abhilash Adunuri
- **Priority:** Medium
- **Created:** 2026-03-13T15:37:32.712+0530
- **Updated:** 2026-03-13T16:36:29.343+0530

**Description:**

DB Setup and Tables creation for Org Info

Knowledge Base and Data Source Agents Data will be in the same database, with a schema separable grouped tables.

**Architecture Diagram:**

```mermaid
flowchart TD
  Admin["Admin / Trainer"]
  User["User"]
  RT["RT Agent"]
  OrgInfo["Org Info<br/>(org_info schema)"]
  KB["Knowledge Base<br/>(agent_kb schema)"]

  Admin -- "Direct CRUD" --> OrgInfo
  RT -- "Read + Write" --> OrgInfo
  RT -- "Read + Write" --> KB
  User -- "Interact" --> RT
```

> The RT Decision Agent is the primary path for all KB mutations. Admins and Trainers can also directly add data (e.g., seeding org structure or resources) through the CRUD endpoints.


---

## Functional Requirements

### FR-1: Schema Initialisation
- The system must create and maintain two isolated PostgreSQL schemas: `org_info` (org structure) and `agent_kb` (agent knowledge).
- All tables, foreign keys, constraints, and `ON DELETE CASCADE` rules must be defined via versioned migration scripts.
- A rollback script must exist to cleanly remove all created tables and schemas.

### FR-2: World Models & Concepts
- The system must allow creation, retrieval, partial update, and deletion of **Concepts** — each describing the technical schema of a connected data source entity (table, object, or API resource). Concepts store schema definitions only, not actual data records.
- The system must allow creation of **World Models** — business terminology that can span multiple data sources. Each world model is identified by a unique `business_term`.
- A world model can be mapped to multiple concepts across different sources via a junction table (`world_model_concept_mappings`).
- Deleting a world model must cascade-delete all its concept mappings.
- Deleting a concept must cascade-delete only its specific mappings, not the world model itself.
- Concepts must be filterable by `source_name` and have a unique constraint on `(source_name, concept_name)`.
- World models must be filterable by `domain` and `business_term` must be unique.
- Partial updates (`PATCH`) must only modify the specified fields, leaving all others unchanged.

### FR-3: Resources
- The system must allow uploading reference documents with metadata (name, type, source, description) and full text content.
- On upload, the document must be automatically split into chunks server-side, with vector embeddings generated and stored per chunk.
- The system must expose a semantic search endpoint (`GET /kb/resources/search?q=...`) that returns the top-k most relevant chunks via vector similarity.
- Deleting a resource must cascade-delete all its chunks.

### FR-4: Org Core Information
- The system must maintain the organisational hierarchy: **Roles**, **Teams**, **People**, and **Project Assignments**.
- People records must support a self-referencing `manager_id` for hierarchy.
- An `is_trainer` flag on People must be settable via a PATCH endpoint — this controls who can manage trainable KB entities.
- Project assignments must be linkable to a Person and support filtering by `person_id` and `project_name`.

### FR-5: Skills
- The system must allow creation and management of **Skills** — atomic agent actions identified by name and source system.
- Each Skill must support multiple **Skill Versions**, each with its own execution configuration (`execution_type`, `execution_endpoint`, `input_schema`, `output_schema`).
- Only one version of a skill can be active (`is_active = TRUE`) at a time.
- The active version is retrievable via `GET /kb/skills/{id}/versions/active`.
- Skill executions must be logged via `POST /kb/skills/logs`, storing input, output, status, and a reference to the version used.

### FR-6: Runbooks
- The system must allow creation and management of **Runbooks** — multi-step workflows that orchestrate one or more skills.
- Each Runbook must support multiple **Runbook Versions**, each with a full `workflow_definition` JSON describing the steps.
- The `workflow_definition` must reference skill IDs per step.
- Only one version of a runbook can be active at a time.
- Runbook executions must be logged via `POST /kb/runbooks/logs`, capturing trigger source, status, start time, and completion time.
- Each skill invocation within a runbook execution must be individually logged and linked back to the parent runbook execution log via `runbook_execution_id`.

### FR-7: Access Patterns
- The RT Decision Agent must be able to read all KB entities and write execution logs.
- Admins and Trainers must be able to write directly to `org_info` entities (org structure, resources).
- All write operations must record a `created_by` identifier so the source (Admin, Trainer, or RT Agent) is traceable.

---

## Non-Functional Requirements

### NFR-1: Data Integrity
- All relationships between tables must be enforced at the database level using foreign key constraints — not just in application code.
- `ON DELETE CASCADE` must be applied wherever child records have no meaning without their parent (e.g., world models without a concept, resource chunks without a resource, skill versions without a skill).
- Duplicate version numbers must be prevented at the DB level: `UNIQUE(skill_id, version_number)` and `UNIQUE(runbook_id, version_number)`.
- Tables for different data source connectors (Jira, Salesforce, Gmail, etc.) must be logically separated using PostgreSQL schemas so each connector can be scaled, maintained, or replaced independently without affecting others.

### NFR-2: Performance & Latency
- When the agent starts up, it must load all system prompts, world models, and skill definitions into memory. It must **not** make a database round-trip on every user query to fetch these — that would make every response slow.
- The vector similarity search on `resource_chunks` must use a vector index (`hnsw` or `ivfflat` via pgvector) so searches across large document libraries remain fast.
- Raw data query results must be capped at **50 rows by default** to keep response times predictable. Users can explicitly request more if needed.
- All list endpoints must support pagination.
- Columns that are frequently used for filtering (`source_name`, `domain`, `source_system`, `trigger_type`, `status`) must have database indexes.

### NFR-3: Extensibility
- No business rules, world model descriptions, or system instructions should be hardcoded in the application code. Everything must live in the database so a Trainer can update the agent's understanding without a code deployment.
- `schema_definition` on Concepts, `workflow_definition` on Runbook Versions, and `input_schema`/`output_schema` on Skill Versions are all JSONB — their internal shape is intentionally flexible so new fields can be added without a migration.

### NFR-4: Traceability & Observability
- Every table must have `created_at` and `created_by` columns so it is always clear who or what created a record.
- Execution logs for both skills and runbooks must be **immutable** — no update or delete endpoints are exposed for them.
- Every execution log must capture which `skill_version` was used at the time of execution. This ensures historical runs remain auditable even after the skill is upgraded.
- Every LLM interaction log must also record **input token count**, **output token count**, and the **estimated cost in USD** for that call. This lets the organisation track and budget AI spend over time.
- The system must track response latency per agent run and log error rates (e.g., how often `401` or `500` responses occur) so performance degradation can be detected early.

### NFR-5: Security & Governance
- All endpoints must require authentication — unauthenticated requests must be rejected with `401`.
- Write access to knowledge entities (skills, runbooks, world models) must be restricted by role — a regular user cannot modify them.
- The `is_trainer` flag must only be settable by an Admin. A user must not be able to grant themselves trainer access.
- Sensitive fields in data responses (e.g., salary figures, financial amounts) must be automatically removed or masked before the response is returned, if the requesting user's role does not permit access to those fields. This logic is driven by the governance rules stored in FR-9 — not hardcoded.
- System instructions, world model content, and other internal KB metadata must be **encrypted at rest**. They are only decrypted when the agent initialises, so that even someone with direct database access cannot read the organisation's AI logic in plain text.

### NFR-6: Reliability & Maintenance
- Migration scripts must be idempotent — running them twice must not cause errors or duplicate data.
- Seed scripts must use `INSERT ... ON CONFLICT DO NOTHING` (or equivalent) so they can be safely re-run in any environment.
- User preferences (FR-12) and other interactive patterns should use an `is_active` flag for deactivation rather than hard deletes, so historical context is preserved and changes can be easily reverted.

### NFR-7: In-Memory Configuration
- At agent startup, the system must load world models, skill definitions, and system prompts into an in-memory cache (e.g., Redis or application-level cache).
- The cache must be refreshable without a full restart — a config change by an Admin should propagate to the running agent within a configurable TTL (e.g., 5 minutes).
- This ensures the agent always has fast access to its "brain" without hitting the database on every query.

### NFR-8: Cost Visibility
- Every interaction with an LLM must log the number of input tokens, output tokens, and the calculated USD cost to the execution log.
- The system must expose a read endpoint to query aggregated cost data by date range, user, or data source, so the organisation can monitor and control AI spend.
- Cost data must never be deleted — it is append-only for budgeting and audit purposes.

### NFR-9: Response Sanitisation
- Before any data result is returned to a user, the system must check the governance rules (FR-9) and strip any fields the user's role is not permitted to see.
- This check must happen at the API layer — the underlying data is stored in full; only the response is trimmed.
- If an entire record is restricted (not just a field), the record must be excluded from the result set entirely rather than returned with blanks.

### NFR-10: Prompt Encryption
- All system instructions, world model descriptions, and internal KB text that forms part of the agent's "brain" must be stored encrypted at rest in the database.
- Encryption and decryption must happen at the application layer, not the DB layer, so the data is unreadable even to someone with direct Postgres access.
- The encryption keys must be managed separately (e.g., via a secrets manager) and must not be stored in the same database.

---

## Overview

This task covers the database setup and CRUD API layer for the **Core Agent Knowledge Base** — the `org_info` schema in the shared database.

The KB is organised into five entity groups:

| Entity Group | Purpose |
|---|---|
| World Models & Concepts | World models define business terminology across sources; concepts store technical schemas per data source |
| Resources | Chunked reference documents for semantic retrieval |
| Org Core Information | People, roles, teams, and project assignments |
| Skills | Versioned atomic agent capabilities |
| Runbooks | Versioned multi-step workflows that orchestrate skills |

> Guard Rails are excluded from this task scope.

---

## System Flow


```mermaid
flowchart TD
    Admin["Admin / Trainer"]
    User["User"]
    RT["RT Agent"]

    subgraph agent_kb["agent_kb schema"]
        WM["World Models & Concepts"]
        SK["Skills + Versions"]
        RB["Runbooks + Versions"]
        LOGS["Execution Logs"]
    end

    subgraph org_info["org_info schema"]
        RES["Resources + Chunks"]
        ORG["Org Core Info"]
    end

    Admin -->|"Configure KB via RT Agent"| RT
    Admin -->|"Direct data entry"| RES
    Admin -->|"Direct data entry"| ORG

    RT -->|"Read + Write"| WM
    RT -->|"Read + Write"| SK
    RT -->|"Read + Write"| RB
    RT -->|"Write logs"| LOGS
    RT -->|"Read + Write"| RES
    RT -->|"Read + Write"| ORG

    User -->|"Interact"| RT
```

> The RT Agent is the primary path for KB mutations. Admins and Trainers can also write directly — primarily for org structure and resource seeding.

---

## Database Schema

Tables are split across **two PostgreSQL schemas** in the shared database:

| Schema | Contains |
|---|---|
| `org_info` | Org structure + shared resources (roles, teams, people, assignments, resources, resource_chunks) |
| `agent_kb` | RT Agent knowledge (concepts, world models, skills, skill_execution_logs, runbooks, runbook_versions, runbook_execution_logs) |

```sql
CREATE SCHEMA IF NOT EXISTS org_info;
CREATE SCHEMA IF NOT EXISTS agent_kb;
```

---

## 1. World Models & Concepts

### Purpose

**Concepts** store the technical schema of each entity in a connected data source (tables, objects, fields). They define structure only — no actual data records.

**World Models** store business terminology that spans across multiple data sources. A world model like "Customer" can map to Salesforce Leads, Jira Customers, and CRM Contacts — all representing the same business concept.

The agent uses world models to understand business language and resolve it to the appropriate technical concepts across systems.

### Enterprise Brain Example

**Scenario:** A CXO asks "Show me our team velocity."

**Concept (Jira Sprint):** Schema-only definition
```json
{
  "source_name": "jira",
  "concept_name": "Sprint",
  "schema_definition": {
    "fields": ["id", "completedIssuesCount", "startDate", "endDate"]
  }
}
```

**Concept (GitHub Milestone):** Schema-only definition
```json
{
  "source_name": "github",
  "concept_name": "Milestone",
  "schema_definition": {
    "fields": ["id", "closed_issues", "due_on"]
  }
}
```

**World Model (Velocity):** Business terminology across sources
```json
{
  "business_term": "Velocity",
  "business_context": "Team productivity measured by work completed per sprint/milestone",
  "business_rules": "Calculate from closed items only",
  "mappings": [
    {"source": "jira", "concept": "Sprint", "field": "completedIssuesCount"},
    {"source": "github", "concept": "Milestone", "field": "closed_issues"}
  ]
}
```

**Result:** When the CXO says "velocity," the agent queries both Jira Sprints and GitHub Milestones, unifying the business concept across multiple systems.

### Flow

```mermaid
sequenceDiagram
    participant Trainer as Trainer (via RT Agent)
    participant RT as RT Decision Agent
    participant API as KB API
    participant DB as agent_kb DB

    Trainer->>RT: "Define 'Lead' as a business concept"
    RT->>API: POST /kb/world-models (business_term: "Lead")
    API->>DB: INSERT into data_source_world_models
    
    Trainer->>RT: "Map Lead to Salesforce Lead object"
    RT->>API: POST /kb/concepts (source: salesforce, schema)
    API->>DB: INSERT into data_source_concepts
    RT->>API: POST /kb/world-models/{id}/mappings
    API->>DB: INSERT into world_model_concept_mappings
    
    Trainer->>RT: "Also map Lead to HubSpot Contact"
    RT->>API: POST /kb/concepts (source: hubspot, schema)
    API->>DB: INSERT into data_source_concepts
    RT->>API: POST /kb/world-models/{id}/mappings
    API->>DB: INSERT into world_model_concept_mappings

    RT->>API: GET /kb/world-models?business_term=Lead
    API->>DB: SELECT world model + all mapped concepts
    DB-->>API: Business term + sources (Salesforce, HubSpot)
    API-->>RT: Unified business context across sources
```

### Tables

```sql
CREATE TABLE agent_kb.data_source_concepts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_name TEXT NOT NULL,
    concept_name TEXT NOT NULL,
    schema_definition JSONB NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT now(),
    created_by TEXT,
    updated_at TIMESTAMP,
    UNIQUE(source_name, concept_name)
);

CREATE TABLE agent_kb.data_source_world_models (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_term TEXT NOT NULL UNIQUE,
    business_context TEXT NOT NULL,
    business_rules TEXT,
    domain TEXT,
    created_at TIMESTAMP DEFAULT now(),
    created_by TEXT,
    updated_at TIMESTAMP
);

CREATE TABLE agent_kb.world_model_concept_mappings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    world_model_id UUID NOT NULL REFERENCES agent_kb.data_source_world_models(id) ON DELETE CASCADE,
    concept_id UUID NOT NULL REFERENCES agent_kb.data_source_concepts(id) ON DELETE CASCADE,
    mapping_rules JSONB,
    created_at TIMESTAMP DEFAULT now(),
    UNIQUE(world_model_id, concept_id)
);
```

> **Key Change:** World models are now independent entities identified by `business_term`. They map to multiple concepts across sources via the junction table `world_model_concept_mappings`.

### CRUD Endpoints

#### Concepts

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/concepts` | Create a new concept |
| `GET` | `/kb/concepts` | List all concepts (filter by `source_name`, `domain`) |
| `GET` | `/kb/concepts/{id}` | Get a concept by ID |
| `PATCH` | `/kb/concepts/{id}` | Partially update concept schema or description |
| `DELETE` | `/kb/concepts/{id}` | Delete a concept (cascades to world model) |

**POST `/kb/concepts` — Request**
```json
{
  "source_name": "salesforce",
  "concept_name": "Lead",
  "schema_definition": {
    "object_name": "Lead",
    "fields": ["Id", "Name", "Email", "Company", "Status", "CreatedDate"],
    "relationships": { "converted_contact": "ContactId" }
  },
  "description": "Salesforce object storing potential customer information"
}
```

#### World Models

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/world-models` | Create a world model for a business term |
| `GET` | `/kb/world-models` | List all world models (filter by `domain`) |
| `GET` | `/kb/world-models/{id}` | Get a world model by ID |
| `PATCH` | `/kb/world-models/{id}` | Partially update business context or rules |
| `DELETE` | `/kb/world-models/{id}` | Delete a world model |
| `POST` | `/kb/world-models/{id}/mappings` | Link a world model to a concept |
| `GET` | `/kb/world-models/{id}/mappings` | List all concept mappings for a world model |
| `DELETE` | `/kb/world-models/{id}/mappings/{mapping_id}` | Remove a concept mapping |

**POST `/kb/world-models` — Request**
```json
{
  "business_term": "Velocity",
  "business_context": "Team productivity measured by work completed per time period. Used by executives to track delivery capacity and trends.",
  "business_rules": "Calculate from completed work only. Exclude unestimated items and spikes.",
  "domain": "engineering_metrics"
}
```

**POST `/kb/world-models/{id}/mappings` — Request**
```json
{
  "concept_id": "<jira_sprint_concept_uuid>",
  "mapping_rules": {
    "aggregate": "completedIssuesCount",
    "group_by": "sprint",
    "filter": "state = 'closed'"
  }
}
```

**PATCH `/kb/concepts/{id}` — Request** (only send the fields you want to change)
```json
{
  "description": "Updated: Salesforce Lead object, including enrichment fields added in 2025"
}
```

---

## 2. Resources

### Purpose

Resources are reference documents (manuals, guides, SOPs) that the agent retrieves contextually using semantic search. Documents are chunked and embedded after upload so that only the most relevant sections are fetched at query time.

### Flow

```mermaid
sequenceDiagram
    participant Admin
    participant API as KB API
    participant DB as org_info DB
    participant Embed as Embedding Service
    participant RT as RT Decision Agent

    Admin->>API: POST /kb/resources (document metadata + content)
    API->>DB: INSERT into resources (metadata)
    API->>Embed: Generate embeddings for chunks
    Embed-->>API: Chunk vectors
    API->>DB: INSERT into resource_chunks (text + embedding)

    RT->>API: GET /kb/resources/search?q=lead time definition
    API->>DB: Vector similarity search on resource_chunks
    DB-->>API: Top-k matching chunks
    API-->>RT: Relevant document sections
```

### Tables

```sql
CREATE TABLE org_info.resources (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    resource_name TEXT NOT NULL,
    source TEXT,
    resource_type TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT now(),
    created_by TEXT
);

CREATE TABLE org_info.resource_chunks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    resource_id UUID NOT NULL REFERENCES org_info.resources(id) ON DELETE CASCADE,
    chunk_text TEXT NOT NULL,
    embedding VECTOR(1536),
    chunk_index INT,
    created_at TIMESTAMP DEFAULT now()
);
```

### CRUD Endpoints

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/resources` | Upload a new resource (triggers chunking + embedding) |
| `GET` | `/kb/resources` | List all resources |
| `GET` | `/kb/resources/{id}` | Get resource metadata |
| `DELETE` | `/kb/resources/{id}` | Delete resource and all its chunks |
| `GET` | `/kb/resources/search` | Semantic search across chunks (`?q=...&top_k=5`) |

**POST `/kb/resources` — Request**
```json
{
  "resource_name": "Supply Chain Operations Manual",
  "source": "internal_documentation",
  "resource_type": "operations_manual",
  "description": "Guidelines and procedures for supply chain operations",
  "content": "<full document text — chunked server-side>"
}
```

---

## 3. Org Core Information

### Purpose

Stores the organizational structure: roles, teams, people, and project assignments. The agent uses this to understand ownership, accountability, and routing — e.g., who owns a KPI, who to alert for a specific event.

`is_trainer` on `org_people` controls who can make changes to trainable KB entities (skills, runbooks, world models).

### Enterprise Brain Example

**Structure:**
- **Roles:** CTO, Engineering Manager, Product Manager
- **Teams:** Backend Engineering, Frontend Engineering
- **People:** Sarah Chen (CTO, is_trainer=true), Michael Kumar (Eng Manager, reports to Sarah)
- **Assignments:** Michael → Enterprise Brain project (Technical Lead)

**Usage:** When velocity drops, the agent knows to alert Michael (team owner) and Sarah (his manager). Since Sarah is a trainer, she can update the agent's skills and runbooks to improve future responses.

### Flow

```mermaid
flowchart TD
  Admin["Admin / Trainer"]
  RT["RT Agent"]
  OrgInfo["Org Info"]
  Dist["Distribution Engine"]

  Admin -- "Insert/Manage Org Data" --> OrgInfo
  RT -- "Read/Query Org Data" --> OrgInfo
  OrgInfo -- "Org Structure" --> Dist
  Dist -- "Identify alert recipients" --> OrgInfo
```

### Tables

```sql
CREATE TABLE org_info.org_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    role_name TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT now(),
    created_by TEXT
);

CREATE TABLE org_info.org_teams (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    team_name TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE org_info.org_people (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    role_id UUID REFERENCES org_info.org_roles(id),
    team_id UUID REFERENCES org_info.org_teams(id),
    manager_id UUID REFERENCES org_info.org_people(id),
    is_trainer BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE org_info.org_project_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    person_id UUID NOT NULL REFERENCES org_info.org_people(id) ON DELETE CASCADE,
    project_name TEXT NOT NULL,
    role_in_project TEXT,
    assigned_at TIMESTAMP DEFAULT now()
);
```

### CRUD Endpoints

#### Roles

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/org/roles` | Create a role |
| `GET` | `/kb/org/roles` | List all roles |
| `PATCH` | `/kb/org/roles/{id}` | Update a role |
| `DELETE` | `/kb/org/roles/{id}` | Delete a role |

#### Teams

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/org/teams` | Create a team |
| `GET` | `/kb/org/teams` | List all teams |
| `PATCH` | `/kb/org/teams/{id}` | Update a team |
| `DELETE` | `/kb/org/teams/{id}` | Delete a team |

#### People

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/org/people` | Add a person |
| `GET` | `/kb/org/people` | List people (filter by `team_id`, `role_id`) |
| `GET` | `/kb/org/people/{id}` | Get person details |
| `PATCH` | `/kb/org/people/{id}` | Update person (role, team, trainer flag) |
| `DELETE` | `/kb/org/people/{id}` | Remove a person |

#### Project Assignments

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/org/assignments` | Assign a person to a project |
| `GET` | `/kb/org/assignments` | List assignments (filter by `person_id`, `project_name`) |
| `DELETE` | `/kb/org/assignments/{id}` | Remove an assignment |

---

## 4. Skills

### Purpose

Skills are atomic actions the agent can execute — fetching data from a system, running a Python function, calling an API. Each skill has a **latest version** tracked automatically. When a skill is updated, the version is auto-bumped and the latest version pointer is updated. There is no branching — only one active version at a time.

The skill's Python code is stored as a text blob in the `code_body` column and executed by the agent runtime at invocation time.

### Enterprise Brain Example

**Skill:** `fetch_sprint_velocity`
- **What it does:** Calls Jira API to get completed story points per sprint
- **Input:** `board_id`, `start_date`, `end_date`
- **Output:** List of sprints with completed points + average velocity

**Usage:** When a CXO asks "What's our velocity?", the agent invokes this skill, gets the data, and shows a trend chart.

### Flow

```mermaid
sequenceDiagram
    participant Trainer as Trainer (via RT Agent)
    participant RT as RT Decision Agent
    participant API as KB API
    participant DB as agent_kb DB

    Trainer->>RT: "Teach: fetch sales pipeline from Salesforce"
    RT->>API: POST /kb/skills (name, system, description)
    API->>DB: INSERT into agent_kb.skills
    RT->>API: POST /kb/skills/{id}/versions (execution config)
    API->>DB: INSERT into agent_kb.skill_versions

    RT->>API: GET /kb/skills?source_system=salesforce
    API->>DB: SELECT skills + latest active version
    DB-->>API: Skill + version config
    RT->>RT: Invoke skill using execution_endpoint + input_schema
    RT->>API: POST /kb/skills/logs (result)
    API->>DB: INSERT into agent_kb.skill_execution_logs
```

### Tables

```sql
CREATE TABLE agent_kb.skills (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    skill_name TEXT NOT NULL,
    source_system TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT now(),
    created_by TEXT
);

CREATE TABLE agent_kb.skill_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    skill_id UUID NOT NULL REFERENCES agent_kb.skills(id) ON DELETE CASCADE,
    version_number INT NOT NULL,
    execution_type TEXT NOT NULL,
    execution_endpoint TEXT NOT NULL,
    input_schema JSONB,
    output_schema JSONB,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT now(),
    created_by TEXT,
    UNIQUE (skill_id, version_number)
);

CREATE TABLE agent_kb.skill_execution_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    skill_version_id UUID NOT NULL REFERENCES agent_kb.skill_versions(id),
    runbook_execution_id UUID,
    input_payload JSONB,
    output_payload JSONB,
    status TEXT NOT NULL,
    executed_at TIMESTAMP DEFAULT now()
);
```

### CRUD Endpoints

#### Skills

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/skills` | Create a new skill |
| `GET` | `/kb/skills` | List skills (filter by `source_system`) |
| `GET` | `/kb/skills/{id}` | Get skill with its versions |
| `PATCH` | `/kb/skills/{id}` | Update skill description |
| `DELETE` | `/kb/skills/{id}` | Delete skill and all versions |

#### Skill Versions

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/skills/{id}/versions` | Add a new version |
| `GET` | `/kb/skills/{id}/versions` | List all versions of a skill |
| `GET` | `/kb/skills/{id}/versions/active` | Get the currently active version |
| `PATCH` | `/kb/skills/{id}/versions/{version_id}` | Update a version (e.g., set `is_active`) |

**POST `/kb/skills` — Request**
```json
{
  "skill_name": "fetch_sales_pipeline",
  "source_system": "salesforce",
  "description": "Retrieve sales pipeline metrics from Salesforce"
}
```

**POST `/kb/skills/{id}/versions` — Request**
```json
{
  "version_number": 1,
  "execution_type": "api",
  "execution_endpoint": "/salesforce/pipeline",
  "input_schema": { "start_date": "date", "end_date": "date" },
  "output_schema": { "stages": "array", "total_value": "number" },
  "is_active": true
}
```

---

## 5. Runbooks

### Purpose

Runbooks are structured multi-step workflows that orchestrate one or more skills to resolve an operational scenario. The agent executes a runbook step-by-step when triggered by an event or a user query. All executions are logged for traceability.

### Enterprise Brain Example

**Runbook:** `investigate_velocity_drop`

**Trigger:** Velocity drops >20% below average

**Steps:**
1. Fetch last 4 sprints data (uses `fetch_sprint_velocity` skill)
2. Calculate trend and percentage drop
3. Check team capacity/attendance
4. Generate insight: "Velocity dropped 25% due to 2 team members on leave"
5. Notify Engineering Manager and CTO via email

**Result:** Automatic root-cause analysis and stakeholder notification — no manual investigation needed.

### Flow

```mermaid
sequenceDiagram
    participant Trainer as Trainer (via RT Agent)
    participant RT as RT Decision Agent
    participant API as KB API
    participant DB as agent_kb DB

    Trainer->>RT: "Define runbook: investigate pipeline drop"
    RT->>API: POST /kb/runbooks (name, trigger_type)
    API->>DB: INSERT into agent_kb.runbooks
    RT->>API: POST /kb/runbooks/{id}/versions (workflow_definition)
    API->>DB: INSERT into agent_kb.runbook_versions

    RT->>API: GET /kb/runbooks?trigger_type=alert
    API->>DB: SELECT runbooks + active version
    DB-->>API: Runbook + workflow steps
    RT->>RT: Execute steps sequentially, invoke skills per step
    RT->>API: POST /kb/runbooks/logs (execution result)
    API->>DB: INSERT into agent_kb.runbook_execution_logs
    RT->>API: POST /kb/skills/logs (per-step skill log)
    API->>DB: INSERT into agent_kb.skill_execution_logs
```

### Tables

```sql
CREATE TABLE agent_kb.runbooks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    runbook_name TEXT NOT NULL,
    description TEXT,
    trigger_type TEXT,
    created_at TIMESTAMP DEFAULT now(),
    created_by TEXT
);

CREATE TABLE agent_kb.runbook_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    runbook_id UUID NOT NULL REFERENCES agent_kb.runbooks(id) ON DELETE CASCADE,
    version_number INT NOT NULL,
    workflow_definition JSONB NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT now(),
    created_by TEXT,
    UNIQUE (runbook_id, version_number)
);

CREATE TABLE agent_kb.runbook_execution_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    runbook_version_id UUID NOT NULL REFERENCES agent_kb.runbook_versions(id),
    triggered_by TEXT,
    status TEXT NOT NULL,
    started_at TIMESTAMP DEFAULT now(),
    completed_at TIMESTAMP
);
```

> `skill_execution_logs.runbook_execution_id` references `runbook_execution_logs.id` for per-step traceability.

### Workflow Definition Format

```json
{
  "steps": [
    { "step": 1, "skill_id": "<uuid>", "description": "Fetch sales pipeline data" },
    { "step": 2, "skill_id": "<uuid>", "description": "Analyze pipeline drop against threshold" },
    { "step": 3, "action": "generate_alert", "description": "Raise alert if drop > 20%" }
  ]
}
```

### CRUD Endpoints

#### Runbooks

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/runbooks` | Create a new runbook |
| `GET` | `/kb/runbooks` | List runbooks (filter by `trigger_type`) |
| `GET` | `/kb/runbooks/{id}` | Get runbook with its versions |
| `PATCH` | `/kb/runbooks/{id}` | Update runbook description or trigger |
| `DELETE` | `/kb/runbooks/{id}` | Delete runbook and all versions |

#### Runbook Versions

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/runbooks/{id}/versions` | Add a new workflow version |
| `GET` | `/kb/runbooks/{id}/versions` | List all versions |
| `GET` | `/kb/runbooks/{id}/versions/active` | Get the currently active version |
| `PATCH` | `/kb/runbooks/{id}/versions/{version_id}` | Update version (e.g., set `is_active`) |

#### Execution Logs (Read-only — written by RT Agent)

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/kb/runbooks/logs` | Log a runbook execution (called by RT Agent) |
| `GET` | `/kb/runbooks/logs` | Query execution logs (filter by `status`, `triggered_by`) |
| `POST` | `/kb/skills/logs` | Log a skill execution within a runbook |
| `GET` | `/kb/skills/logs` | Query skill execution logs |

---

## Database Schema Summary

Tables are split across two PostgreSQL schemas:

```
org_info                              ← org structure + shared resources
├── resources
├── resource_chunks                   → FK: resources.id  [VECTOR(1536) embedding]
├── org_roles
├── org_teams
├── org_people                        → FK: org_roles.id, org_teams.id, org_people.id (self)
└── org_project_assignments           → FK: org_people.id

agent_kb                              ← RT Agent knowledge
├── data_source_concepts
├── data_source_world_models
├── world_model_concept_mappings      → FK: data_source_world_models.id, data_source_concepts.id
├── skills
├── skill_versions                    → FK: skills.id  [is_active, UNIQUE(skill_id, version_number)]
├── skill_execution_logs              → FK: skill_versions.id, runbook_execution_logs.id
├── runbooks
├── runbook_versions                  → FK: runbooks.id  [is_active, UNIQUE(runbook_id, version_number)]
└── runbook_execution_logs            → FK: runbook_versions.id
```

---

## What Needs to Be Built

The deliverable for this task is: **set up the two database schemas (`org_info`, `agent_kb`) and expose REST API endpoints so that the RT Agent can manage the Core Agent Knowledge Base.**

Concretely, this means:

1. **Run the migrations** — Create `org_info` and `agent_kb` schemas with all tables, foreign keys, constraints, and `ON DELETE CASCADE` rules.

2. **Expose the CRUD endpoints** — For each entity group (Concepts & World Models, Resources, Org Info, Skills, Runbooks), implement the API routes under `/kb/...`. All writes go through the RT Agent — no direct DB access by Admin/Trainer.

3. **Handle resource chunking** — When a resource is uploaded via `POST /kb/resources`, split the content into chunks, generate vector embeddings, and store them in `resource_chunks`. `GET /kb/resources/search` performs vector similarity search.

4. **Expose execution log endpoints** — `POST` log endpoints (`/kb/runbooks/logs`, `/kb/skills/logs`) are called by the RT Agent at runtime. `GET` endpoints are for observability.

5. **Skill versioning** — Skills have a base record plus versioned execution configs in `skill_versions`. `POST /kb/skills/{id}/versions` creates a new version; `PATCH /kb/skills/{id}/versions/{version_id}` switches the active version.

---

## Subtasks

### Subtask 1 — DB Schema: Write Migration Scripts
**Estimate: 6h**
- Migration scripts exist for both schemas: `org_info` and `agent_kb`
- `org_info` tables: `resources`, `resource_chunks`, `org_roles`, `org_teams`, `org_people`, `org_project_assignments`
- `agent_kb` tables: `data_source_concepts`, `data_source_world_models`, `world_model_concept_mappings`, `skills`, `skill_versions`, `skill_execution_logs`, `runbooks`, `runbook_versions`, `runbook_execution_logs`
- All foreign key constraints and `ON DELETE CASCADE` rules are defined
- Scripts are stored as versioned migration files under `migrations/`
- Scripts are tested locally against a clean database and confirmed error-free

### Subtask 2 — DB Schema: Apply & Verify Migrations
**Estimate: 2h**
- Migration scripts run successfully on dev database without errors
- All tables are visible and correctly structured in their respective schemas (`org_info`, `agent_kb`)
- FK relationships and constraints confirmed via schema inspection
- Migration applied and verified on the test environment
- A rollback script exists and tested to cleanly drop all created tables and schemas

### Subtask 3 — Initial Data Seeding
**Estimate: 16h**
- Identify and collect the initial data required for each KB entity — org roles, teams, people, concepts, world models, skills, runbooks, and resources
- Prepare seed data as SQL scripts or structured JSON/CSV files and store them in the repo under `migrations/seeds/`
- Seed data is applied to the dev database and all records are verified to be correctly inserted
- Seed data is applied to the test environment as well
- Any resources (documents) uploaded are chunked and embeddings are confirmed to be generated correctly

### Subtask 4 — CRUD API: World Models & Concepts
**Estimate: 6h**
- `POST /kb/concepts` creates a new concept record in `agent_kb`
- `GET /kb/concepts` returns a list of concepts, filterable by `source_name` and `domain`
- `GET /kb/concepts/{id}` returns a single concept by ID
- `PATCH /kb/concepts/{id}` partially updates concept description or schema fields
- `DELETE /kb/concepts/{id}` deletes the concept and cascades to its world model
- Same five operations implemented for `/kb/world-models`
- `PATCH /kb/world-models/{id}` accepts partial updates (e.g., only `business_context`)
- All endpoints return appropriate HTTP status codes and error responses
- Unit tests cover create, list, get, partial update, and delete for both concepts and world models, including invalid input and not-found cases

### Subtask 5 — CRUD API: Resources + Chunking Pipeline
**Estimate: 6h**
- `POST /kb/resources` accepts document metadata and full text content
- On upload, content is split into chunks server-side and stored in `resource_chunks`
- Vector embeddings are generated for each chunk and stored in the `embedding` column
- `GET /kb/resources` returns a list of all resources
- `GET /kb/resources/{id}` returns resource metadata
- `DELETE /kb/resources/{id}` deletes the resource and all its chunks
- `GET /kb/resources/search?q=...&top_k=5` performs vector similarity search and returns the top matching chunks
- Unit tests cover upload (verify chunks are created), delete (verify chunks are cascade deleted), and search (verify top-k results are returned)

### Subtask 6 — CRUD API: Org Core Information
**Estimate: 6h**
- Full CRUD implemented for `/kb/org/roles` (create, list, update, delete)
- Full CRUD implemented for `/kb/org/teams` (create, list, update, delete)
- Full CRUD implemented for `/kb/org/people` (create, list, get, update, delete), with filters by `team_id` and `role_id`
- `is_trainer` flag can be set via the update endpoint
- Create and delete implemented for `/kb/org/assignments`, with filters by `person_id` and `project_name`
- Unit tests cover CRUD for all four entities, including FK validation (e.g., assigning a non-existent person to a project should fail)

### Subtask 7 — CRUD API: Skills & Versions
**Estimate: 8h**
- Full CRUD implemented for `/kb/skills` (create, list, get, update, delete)
- `POST /kb/skills/{id}/versions` creates a new versioned config for a skill
- `GET /kb/skills/{id}/versions` returns all versions of a skill
- `GET /kb/skills/{id}/versions/active` returns the currently active version
- `PATCH /kb/skills/{id}/versions/{version_id}` allows setting `is_active` to switch active version
- `POST /kb/skills/logs` stores a skill execution log entry (called by RT Agent)
- `GET /kb/skills/logs` returns execution logs, filterable by `status`
- Unit tests cover skill CRUD, version creation, active version switching, and log entry creation

### Subtask 8 — CRUD API: Runbooks & Versions
**Estimate: 8h**
- Full CRUD implemented for `/kb/runbooks` (create, list, get, update, delete)
- `POST /kb/runbooks/{id}/versions` creates a new versioned workflow definition
- `GET /kb/runbooks/{id}/versions` returns all versions of a runbook
- `GET /kb/runbooks/{id}/versions/active` returns the currently active version
- `PUT /kb/runbooks/{id}/versions/{version_id}` allows setting `is_active` to switch active version
- `POST /kb/runbooks/logs` stores a runbook execution log entry (called by RT Agent)
- `GET /kb/runbooks/logs` returns execution logs, filterable by `status` and `triggered_by`
- Unit tests cover runbook CRUD, version creation, active version switching, and log entry creation

---

### Subtask 9 — Integration & API Testing
**Estimate: 8h**
- End-to-end test: RT Agent creates concepts from multiple sources → creates a world model → maps it to both concepts → queries business term successfully
- End-to-end test: Upload a resource → verify chunks created → verify `/kb/resources/search` returns correct top-k
- End-to-end test: Create a skill → update it via `PUT /kb/skills/{id}` → verify `latest_version` auto-increments
- End-to-end test: Create runbook → execute via RT Agent → verify `runbook_execution_logs` and `skill_execution_logs` both populated
- All CRUD endpoints tested with valid input, invalid input (missing required fields), and not-found (non-existent ID)
- `PATCH` endpoints tested with partial payloads — verify only the specified fields are updated

---

**Total Estimate: ~66 hours**

> Subtasks must be done in order: Schema (1→2) → Seeding (3) → CRUD APIs (4–8) → Integration Testing (9). CRUD subtasks 4–8 can be picked up in parallel once seeding is done.



## Test Cases

### TC-01: Create and Retrieve a Concept
| Step | Action | Expected Result |
|---|---|---|
| 1 | `POST /kb/concepts` with valid `source_name`, `concept_name`, `schema_definition` | `201` response with new `id` |
| 2 | `GET /kb/concepts/{id}` with the returned ID | `200` with the correct concept record |
| 3 | `GET /kb/concepts?source_name=salesforce` | Response list includes the created concept |

### TC-02: PATCH Concept — Partial Update Only
| Step | Action | Expected Result |
|---|---|---|
| 1 | `POST /kb/concepts` to create a concept | `201` |
| 2 | `PATCH /kb/concepts/{id}` with only `{ "description": "updated text" }` | `200`; `description` is updated, `schema_definition` and `concept_name` are unchanged |

### TC-03: World Model Maps to Multiple Concepts
| Step | Action | Expected Result |
|---|---|---|
| 1 | Create Jira Sprint concept and GitHub Milestone concept | Both concepts created |
| 2 | Create "Velocity" world model | `201`; world model created |
| 3 | `POST /kb/world-models/{id}/mappings` linking to Jira Sprint concept | `201`; mapping created |
| 4 | `POST /kb/world-models/{id}/mappings` linking to GitHub Milestone concept | `201`; second mapping created |
| 5 | `GET /kb/world-models/{id}/mappings` | Returns both Jira and GitHub mappings |
| 6 | `DELETE /kb/world-models/{id}` | `204`; world model and all mappings deleted |

### TC-04: Resource Upload and Chunking
| Step | Action | Expected Result |
|---|---|---|
| 1 | `POST /kb/resources` with document metadata and `content` text | `201`; resource record created |
| 2 | Query `resource_chunks` for the new `resource_id` | Multiple chunk rows exist with non-null `embedding` vectors |
| 3 | `DELETE /kb/resources/{id}` | `204`; all associated `resource_chunks` rows are deleted |

### TC-05: Semantic Search
| Step | Action | Expected Result |
|---|---|---|
| 1 | Upload a resource containing text about "quarterly sales targets" | Chunked and embedded |
| 2 | `GET /kb/resources/search?q=quarterly+sales&top_k=3` | Returns up to 3 chunks with the highest similarity scores; content is relevant to the query |

### TC-06: Org People — FK Validation
| Step | Action | Expected Result |
|---|---|---|
| 1 | `POST /kb/org/people` with a `role_id` that does not exist | `422` or `400` with a foreign key validation error |
| 2 | `POST /kb/org/people` with a valid `role_id` and `team_id` | `201`; person created |
| 3 | `PATCH /kb/org/people/{id}` to set `is_trainer: true` | `200`; `is_trainer` updated |

### TC-07: Skill Versioning
| Step | Action | Expected Result |
|---|---|---|
| 1 | `POST /kb/skills` to create a skill | `201` with skill `id` |
| 2 | `POST /kb/skills/{id}/versions` with `version_number: 1`, `is_active: true` | `201`; version created |
| 3 | `POST /kb/skills/{id}/versions` with `version_number: 2`, `is_active: false` | `201`; second version created; version 1 still active |
| 4 | `PATCH /kb/skills/{id}/versions/{v2_id}` with `{ "is_active": true }` | `200`; version 2 becomes active |
| 5 | `GET /kb/skills/{id}/versions/active` | Returns version 2 |

### TC-08: Skill Execution Log
| Step | Action | Expected Result |
|---|---|---|
| 1 | Create a skill with an active version | Skill and version exist |
| 2 | `POST /kb/skills/logs` with `skill_version_id`, `input_payload`, `output_payload`, `status: "success"` | `201`; log record created |
| 3 | `GET /kb/skills/logs?status=success` | Returns the created log entry |
| 4 | Attempt `DELETE /kb/skills/logs/{id}` | `405 Method Not Allowed` — logs are immutable |

### TC-09: Runbook Execution — End to End
| Step | Action | Expected Result |
|---|---|---|
| 1 | Create a runbook with a version containing a 2-step `workflow_definition` | Runbook and version created |
| 2 | `GET /kb/runbooks?trigger_type=alert` | Returns the runbook |
| 3 | `POST /kb/runbooks/logs` with `status: "completed"` and `completed_at` | `201`; execution log created |
| 4 | `POST /kb/skills/logs` with `runbook_execution_id` referencing the runbook log | `201`; skill log linked to runbook execution |
| 5 | `GET /kb/runbooks/logs?triggered_by=rt_agent` | Returns the runbook execution log |

### TC-10: Authentication — Unauthenticated Request
| Step | Action | Expected Result |
|---|---|---|
| 1 | `GET /kb/concepts` without an auth token | `401 Unauthorized` |
| 2 | `POST /kb/skills` without an auth token | `401 Unauthorized` |

### TC-11: Not-Found Handling
| Step | Action | Expected Result |
|---|---|---|
| 1 | `GET /kb/concepts/non-existent-uuid` | `404 Not Found` with descriptive error message |
| 2 | `PATCH /kb/skills/non-existent-uuid` | `404 Not Found` |
| 3 | `DELETE /kb/runbooks/non-existent-uuid` | `404 Not Found` |

### TC-12: Schema Isolation — org_info vs agent_kb
| Step | Action | Expected Result |
|---|---|---|
| 1 | Insert a record into `agent_kb.data_source_concepts` | Record appears in `agent_kb` schema only |
| 2 | Query `org_info` schema tables | No knowledge-base records are present in `org_info` |
| 3 | Drop `agent_kb` schema in a rollback test | `org_info` tables are unaffected |

### TC-13: Business Term Uniqueness
| Step | Action | Expected Result |
|---|---|---|
| 1 | `POST /kb/world-models` with `business_term: "Velocity"` | `201`; world model created |
| 2 | `POST /kb/world-models` with same `business_term: "Velocity"` | `409 Conflict` — business term must be unique |

### TC-14: Concepts Store Schema Only
| Step | Action | Expected Result |
|---|---|---|
| 1 | Create Jira Sprint concept with `schema_definition` | `201`; concept created with schema |
| 2 | Verify no actual sprint data (records) are stored in the concept table | Concept table contains only schema definitions, not data rows |
| 3 | Actual sprint data is queried from Jira API at runtime | Data is fetched live, not pre-stored |