# Schema Map — 20260324

> **Documentation only** — no executable SQL.  
> Migration: `20260324_001.sql` · Rollback: `20260324_rollback.sql`

---

## Legend

| Symbol | Meaning |
|--------|---------|
| `PK` | Primary key (UUID, auto-generated) |
| `FK` | Foreign key |
| `CASCADE` | ON DELETE CASCADE applied |
| `UNIQUE` | Unique constraint |
| `enc` | Application-layer encrypted at rest (NFR-10) |
| `JSONB` | Flexible schema stored as PostgreSQL JSONB |
| `hnsw` | Vector similarity index (pgvector) |

---

## Entity Relationship Diagrams

### `org_info` — Org Structure

```mermaid
erDiagram
    org_roles {
        UUID id PK
        TEXT role_name "UNIQUE NOT NULL"
        TEXT description
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    org_people {
        UUID id PK
        TEXT full_name "NOT NULL"
        TEXT email "UNIQUE NOT NULL"
        UUID role_id FK
        UUID team_id FK
        UUID manager_id FK
        BOOLEAN is_trainer
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    org_teams {
        UUID id PK
        TEXT team_name "UNIQUE NOT NULL"
        TEXT description
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    org_team_members {
        UUID id PK
        UUID team_id FK
        UUID person_id FK
        TEXT role_label "NOT NULL e.g. tech_lead, member"
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    org_projects {
        UUID id PK
        TEXT project_name "UNIQUE NOT NULL"
        TEXT description
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    org_project_members {
        UUID id PK
        UUID project_id FK
        UUID person_id FK
        TEXT role_label "NOT NULL e.g. tech_lead, developer"
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    org_roles ||--o{ org_people : "role_id (org title)"
    org_teams ||--o{ org_people : "team_id (primary team)"
    org_people ||--o| org_people : "manager_id (self-ref)"
    org_teams ||--o{ org_team_members : "team_id"
    org_people ||--o{ org_team_members : "person_id"
    org_projects ||--o{ org_project_members : "project_id"
    org_people ||--o{ org_project_members : "person_id"
```

---

### `org_info` — Resources (Knowledge Base)

```mermaid
erDiagram
    resources {
        UUID id PK
        TEXT resource_name "NOT NULL"
        TEXT resource_type "operations_manual | sop | guide"
        TEXT description
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    resource_chunks {
        UUID id PK
        UUID resource_id FK
        VECTOR embedding "VECTOR(1536) hnsw index"
        INT chunk_index "0, 1, 2 ..."
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    resources ||--o{ resource_chunks : "resource_id (CASCADE)"
```

---

### `agent_kb` — Source Models & Concepts

```mermaid
erDiagram
    data_source_models {
        UUID id PK
        TEXT source "UNIQUE NOT NULL e.g. jira, salesforce"
        TEXT content "enc - what this source is and covers"
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    data_source_concepts {
        UUID id PK
        TEXT source "NOT NULL e.g. jira"
        TEXT content "enc - entity fields, filters, aggregation rules"
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    model_concept_mappings {
        UUID id PK
        UUID model_id FK
        UUID concept_id FK
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    data_source_models ||--o{ model_concept_mappings : "model_id"
    data_source_concepts ||--o{ model_concept_mappings : "concept_id"
```

---

### `agent_kb` — Skills & Runbooks

> **Note on runbook → skill relationship:**  
> There is **no FK table** mapping runbooks to skills at design time. The relationship is encoded in `runbook_versions.workflow_definition` (JSONB), where each step holds a `skill_id`. This is intentional — it keeps the workflow flexible.  
> The only DB-level FK is at **execution time**: when a runbook runs and calls a skill, `skill_execution_logs.runbook_execution_id` points back to the `runbook_execution_logs` row that triggered it.

```mermaid
erDiagram
    skills {
        UUID id PK
        TEXT skill_name "UNIQUE NOT NULL"
        TEXT source_system "jira | salesforce | internal"
        TEXT description
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    skill_versions {
        UUID id PK
        UUID skill_id FK
        INT version_number "NOT NULL"
        TEXT code_body
        JSONB input_schema
        JSONB output_schema
        BOOLEAN is_active "NOT NULL"
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    skill_execution_logs {
        UUID id PK
        UUID skill_version_id FK
        UUID runbook_execution_id FK "nullable - set if called from runbook"
        JSONB input_payload
        JSONB output_payload
        TEXT status "success | failed | timeout"
        INT input_tokens
        INT output_tokens
        NUMERIC cost_usd
        INT duration_ms
        TIMESTAMP executed_at "NOT NULL"
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    runbooks {
        UUID id PK
        TEXT runbook_name "UNIQUE NOT NULL"
        TEXT description
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    runbook_versions {
        UUID id PK
        UUID runbook_id FK
        INT version_number "NOT NULL"
        JSONB workflow_definition "steps array with skill_id per step"
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    runbook_execution_logs {
        UUID id PK
        UUID runbook_version_id FK
        TEXT trigger_source "alert_engine | scheduler | user"
        TEXT triggered_by
        TEXT status "running | completed | failed"
        TIMESTAMP started_at "NOT NULL"
        TIMESTAMP completed_at
        TIMESTAMP created_at
        TEXT created_by
        TIMESTAMP updated_at
        TEXT updated_by
        TIMESTAMP deleted_at
        TEXT deleted_by
    }

    skills ||--o{ skill_versions : "skill_id"
    skill_versions ||--o{ skill_execution_logs : "skill_version_id"
    runbooks ||--o{ runbook_versions : "runbook_id"
    runbook_versions ||--o{ runbook_execution_logs : "runbook_version_id"
    runbook_execution_logs ||--o{ skill_execution_logs : "runbook_execution_id (execution-time back-ref)"
```

---

## Schema: `org_info`
> Org structure, people, teams, projects, and shared knowledge resources.

---

### `org_info.org_roles`
One row per organisation-wide designation.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | Auto-generated |
| `role_name` | `TEXT` UNIQUE NOT NULL | e.g. `"Senior Developer"`, `"QA Engineer"` |
| `description` | `TEXT` | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `org_info.org_people`
One row per person in the organisation. Source of truth for identity.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | Auto-generated |
| `full_name` | `TEXT` NOT NULL | |
| `email` | `TEXT` UNIQUE NOT NULL | |
| `role_id` | `UUID` FK → `org_roles.id` | Org-wide designation (e.g. Senior Developer) |
| `team_id` | `UUID` FK → `org_teams.id` | Primary team |
| `manager_id` | `UUID` FK → `org_people.id` (self-ref) | Reporting hierarchy |
| `is_trainer` | `BOOLEAN` DEFAULT `false` | Controls KB edit access; only Admin can flip |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

> **Key distinction:** `role_id` is the person's org-level title. Their *function* on a team or project is stored separately in `org_team_members` / `org_project_members` as `role_label`.

---

### `org_info.org_teams` *(optional)*
One row per team. Clean identity record — membership tracked in `org_team_members`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | Auto-generated |
| `team_name` | `TEXT` UNIQUE NOT NULL | e.g. `"Backend Engineering"` |
| `description` | `TEXT` | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `org_info.org_team_members` *(optional)*
One row per person per team. `role_label` captures their **function on that team**, independent of their org role.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `team_id` | `UUID` FK → `org_teams.id` CASCADE | |
| `person_id` | `UUID` FK → `org_people.id` CASCADE | |
| `role_label` | `TEXT` NOT NULL | `"tech_lead"` \| `"delivery_mgr"` \| `"team_lead"` \| `"member"` |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |
| | UNIQUE `(team_id, person_id)` | One role per person per team |

**Example — the same person across two teams:**

| person | team | role_label |
|--------|------|------------|
| John (Senior Developer) | Team A | `tech_lead` |
| John (Senior Developer) | Team B | `member` |

---

### `org_info.org_projects` *(optional)*
One row per project. Membership tracked in `org_project_members`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | Auto-generated |
| `project_name` | `TEXT` UNIQUE NOT NULL | e.g. `"Project Alpha"` |
| `description` | `TEXT` | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `org_info.org_project_members` *(optional)*
One row per person per project. `role_label` captures their **function on that project**.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `project_id` | `UUID` FK → `org_projects.id` CASCADE | |
| `person_id` | `UUID` FK → `org_people.id` CASCADE | |
| `role_label` | `TEXT` NOT NULL | `"tech_lead"` \| `"delivery_mgr"` \| `"developer"` \| `"qa_lead"` |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |
| | UNIQUE `(project_id, person_id)` | One role per person per project |

**Example — the same person across two projects:**

| person | project | role_label |
|--------|---------|------------|
| John (Senior Developer) | Project X | `tech_lead` |
| John (Senior Developer) | Project Y | `developer` |

> `role_label` is free-text — no DDL change needed to add `"scrum_master"`, `"product_owner"`, etc.

---

### `org_info.resources`
One row per uploaded document. Parent record — search is done via `resource_chunks`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `resource_name` | `TEXT` NOT NULL | e.g. `"Supply Chain Operations Manual"` |
| `resource_type` | `TEXT` | `"operations_manual"` \| `"sop"` \| `"guide"` \| `"policy"` |
| `description` | `TEXT` | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `org_info.resource_chunks`
Auto-generated chunks with embeddings. N rows per document. Use for vector similarity search.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `resource_id` | `UUID` FK → `resources.id` CASCADE | Deleting resource removes all chunks |
| `embedding` | `VECTOR(1536)` | Generated by embedding model (e.g. `text-embedding-3-small`) |
| `chunk_index` | `INT` | Position in document: 0, 1, 2, 3 … |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

> Indexed with `hnsw` using `vector_cosine_ops` for fast similarity search (NFR-2).

---

## Schema: `agent_kb`
> RT Agent knowledge: source models, concepts, skills, runbooks, execution logs.

---

### `agent_kb.data_source_models`
One row per connected source system. High-level context loaded by the agent at startup.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `source` | `TEXT` UNIQUE NOT NULL | `"jira"` \| `"salesforce"` \| `"github"` \| `"hubspot"` \| `"internal"` |
| `content` | `TEXT` NOT NULL `enc` | What this source is, what it covers, agent rules before querying |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `agent_kb.data_source_concepts`
One row per entity type within a source. Describes fields, filters, aggregation logic.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `source` | `TEXT` NOT NULL | `"jira"` \| `"salesforce"` \| `"github"` |
| `content` | `TEXT` NOT NULL `enc` | What this concept is, which fields to use, filter/aggregation rules |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `agent_kb.model_concept_mappings`
Junction table — links a source model to its concepts.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `model_id` | `UUID` FK → `data_source_models.id` CASCADE | |
| `concept_id` | `UUID` FK → `data_source_concepts.id` CASCADE | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |
| | UNIQUE `(model_id, concept_id)` | |

**How they work together — example: Jira with Sprint + Issue concepts**

`data_source_models` (1 row):

| source | content |
|--------|---------|
| `"jira"` | `"Jira is the primary project tracking tool. Used by engineering to manage sprints and tickets. Always filter closed/done tickets for metrics."` |

`data_source_concepts` (2 rows):

| source | content |
|--------|---------|
| `"jira"` *(Sprint)* | `"Sprint entity. Fields: id, name, completedIssuesCount, startDate, endDate. Use completedIssuesCount for velocity. Group by sprint name, order by startDate. Exclude spikes and unestimated items."` |
| `"jira"` *(Issue)* | `"Issue entity. Fields: id, summary, status, assignee, storyPoints, priority, sprint. Exclude epics from story point sums. Priority: Blocker > Critical > Major > Minor."` |

`model_concept_mappings` (2 rows):

| model_id | concept_id |
|----------|------------|
| `<jira_model_uuid>` | `<sprint_uuid>` |
| `<jira_model_uuid>` | `<issue_uuid>` |

**Agent flow:**
1. Load all `data_source_models` at startup → knows what each source is
2. Follow mappings → knows which concepts belong to each source
3. Read concept `content` → knows exactly which fields and rules to apply
4. Answer query with correct source + field logic

---

### `agent_kb.skills`
Identity record for a skill. Execution logic lives in `skill_versions`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `skill_name` | `TEXT` UNIQUE NOT NULL | e.g. `"fetch_sprint_velocity"` |
| `source_system` | `TEXT` | `"jira"` \| `"salesforce"` \| `"internal"` |
| `description` | `TEXT` | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `agent_kb.skill_versions`
Versioned execution config for a skill. Only one version is active at a time.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `skill_id` | `UUID` FK → `skills.id` CASCADE | |
| `version_number` | `INT` NOT NULL | 1, 2, 3 … UNIQUE with `skill_id` |
| `code_body` | `TEXT` | Python code blob executed by agent runtime |
| `input_schema` | `JSONB` | e.g. `{"board_id": "string", "start_date": "date"}` |
| `output_schema` | `JSONB` | e.g. `{"sprints": "array", "avg_velocity": "number"}` |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `agent_kb.skill_execution_logs`
Immutable runtime log. One row per skill invocation. Used for audit and cost tracking.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `skill_version_id` | `UUID` FK → `skill_versions.id` | Records which exact version ran |
| `runbook_execution_id` | `UUID` FK → `runbook_execution_logs.id` (nullable) | Set if called from a runbook |
| `input_payload` | `JSONB` | |
| `output_payload` | `JSONB` | |
| `status` | `TEXT` NOT NULL | `"success"` \| `"failed"` \| `"timeout"` |
| `input_tokens` | `INT` | LLM tokens consumed (if applicable) |
| `output_tokens` | `INT` | |
| `cost_usd` | `NUMERIC(10,6)` | |
| `duration_ms` | `INT` | |
| `executed_at` | `TIMESTAMP` NOT NULL | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `agent_kb.runbooks`
Identity record for a runbook. No workflow steps here — steps live in `runbook_versions`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `runbook_name` | `TEXT` UNIQUE NOT NULL | e.g. `"investigate_velocity_drop"` |
| `description` | `TEXT` | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

---

### `agent_kb.runbook_versions`
Versioned workflow definition. Steps stored as JSONB. Old versions kept for audit.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `runbook_id` | `UUID` FK → `runbooks.id` CASCADE | |
| `version_number` | `INT` NOT NULL | 1, 2, 3 … UNIQUE with `runbook_id` |
| `workflow_definition` | `JSONB` NOT NULL | Step array — each step references a `skill_id` |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

**Example `workflow_definition`:**
```json
{
  "steps": [
    { "step": 1, "skill_id": "<uuid>", "description": "Fetch last 4 sprints data" },
    { "step": 2, "skill_id": "<uuid>", "description": "Calculate trend & % drop" },
    { "step": 3, "action": "generate_alert", "description": "Notify Eng Manager + CTO" }
  ]
}
```

---

### `agent_kb.runbook_execution_logs`
Immutable runtime log. One row per runbook execution.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `runbook_version_id` | `UUID` FK → `runbook_versions.id` | Records which version ran |
| `trigger_source` | `TEXT` | `"alert_engine"` \| `"scheduler"` \| `"user"` |
| `triggered_by` | `TEXT` | `"rt_agent"` \| `"admin@company.com"` |
| `status` | `TEXT` NOT NULL | `"running"` \| `"completed"` \| `"failed"` |
| `started_at` | `TIMESTAMP` NOT NULL | |
| `completed_at` | `TIMESTAMP` | |
| `created_at` | `TIMESTAMP` NOT NULL | |
| `created_by` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `updated_by` | `TEXT` | |
| `deleted_at` | `TIMESTAMP` | Soft-delete timestamp |
| `deleted_by` | `TEXT` | |

> Each skill invoked within this run creates a row in `skill_execution_logs` with `runbook_execution_id` pointing back here.

---

### Why three tables for skills and runbooks?

| Table | Analogy |
|-------|---------|
| `skills` / `runbooks` | The tool / book on the shelf — name and identity never change |
| `skill_versions` / `runbook_versions` | Each edition — code and workflow steps change over time; old versions kept for audit |
| `skill_execution_logs` / `runbook_execution_logs` | The usage log — every run recorded with inputs, outputs, cost, latency |

---

## Indexes

### `org_info`

| Table | Column(s) | Type | Purpose |
|-------|-----------|------|---------|
| `resource_chunks` | `embedding` | `hnsw` | Vector similarity search (NFR-2) |
| `org_people` | `role_id` | btree | People by org role |
| `org_people` | `team_id` | btree | People by primary team |
| `org_team_members` | `team_id` | btree | All members of a team *(opt)* |
| `org_team_members` | `person_id` | btree | All teams a person is on *(opt)* |
| `org_project_members` | `project_id` | btree | All members of a project *(opt)* |
| `org_project_members` | `person_id` | btree | All projects a person is on *(opt)* |

### `agent_kb`

| Table | Column(s) | Type | Purpose |
|-------|-----------|------|---------|
| `data_source_models` | `source` | btree | Lookup by source name |
| `data_source_concepts` | `source` | btree | Concepts by source |
| `model_concept_mappings` | `model_id` | btree | All concepts for a model |
| `model_concept_mappings` | `concept_id` | btree | All models using a concept |
| `skills` | `source_system` | btree | Skills by source |
| `skill_versions` | `skill_id` | btree | All versions of a skill |
| `skill_versions` | `(skill_id, is_active)` | btree | Active version lookup |
| `runbook_versions` | `runbook_id` | btree | All versions of a runbook |
| `skill_execution_logs` | `status` | btree | Filter by outcome |
| `skill_execution_logs` | `executed_at` | btree | Time-range queries |
| `skill_execution_logs` | `(executed_at, cost_usd)` | partial btree | Cost aggregation (NFR-8) |
| `runbook_execution_logs` | `status` | btree | Filter by outcome |
| `runbook_execution_logs` | `triggered_by` | btree | Filter by trigger actor |