# Schema Map — Data Source Agent (DSA)

> **Documentation only** — no executable SQL.  
> Canonical LLD: `docs/phase-2/llds/data-source-agent.md`

---

## Legend

| Symbol | Meaning |
|--------|---------|
| `PK` | Primary key (UUID, auto-generated unless noted) |
| `FK` | Foreign key |
| `CASCADE` | ON DELETE CASCADE applied |
| `UNIQUE` | Unique constraint |
| `JSONB` | Flexible schema stored as PostgreSQL JSONB |
| `ivfflat` | Vector similarity index (pgvector, approximate nearest-neighbour) |
| `{source_name}` | Per-DSA schema name — instantiated as `gmail`, `jira`, `salesforce`, etc. |

---

## Entity Relationship Diagrams

### `{source_name}` — Knowledge Base: Concepts & World Models

```mermaid
erDiagram
    concepts {
        UUID id PK
        TEXT concept_name "UNIQUE NOT NULL"
        JSONB schema_definition "NOT NULL — field definitions"
        TEXT description
        TIMESTAMP updated_at
        TIMESTAMPTZ created_at "NOT NULL"
        TEXT created_by
    }

    world_models {
        UUID id PK
        TEXT business_term "UNIQUE NOT NULL"
        TEXT business_context "NOT NULL"
        TEXT business_rules
        TEXT domain "delivery | sales | communication | hr"
        TEXT created_by
        TIMESTAMPTZ created_at "NOT NULL"
        TIMESTAMPTZ updated_at
    }

    world_model_concept_map {
        UUID id PK
        UUID world_model_id FK
        UUID concept_id FK
        JSONB mapping_rules
        TIMESTAMP created_at
    }

    world_models ||--o{ world_model_concept_map : "world_model_id"
    concepts ||--o{ world_model_concept_map : "concept_id"
```

---

### `{source_name}` — Knowledge Base: 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`.  
> The only DB-level FK is at **execution time**: when a runbook runs 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 "NOT NULL"
        TEXT description
        TIMESTAMP created_at
        TEXT created_by
    }

    skill_versions {
        UUID id PK
        UUID skill_id FK
        INT version_number "NOT NULL — UNIQUE with skill_id"
        TEXT execution_type "api | python_function | sql"
        TEXT execution_endpoint "NOT NULL"
        JSONB input_schema
        JSONB output_schema
        BOOLEAN is_active "DEFAULT TRUE"
        TIMESTAMP created_at
        TEXT created_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 "NOT NULL — success | failed | timeout"
        INT input_token_count "LLM tokens in (NFR-8)"
        INT output_token_count "LLM tokens out"
        NUMERIC cost_usd "NUMERIC(10,6)"
        INT duration_ms
        TIMESTAMP executed_at "DEFAULT now()"
    }

    runbooks {
        UUID id PK
        TEXT runbook_name "NOT NULL"
        TEXT description
        TEXT trigger_type "alert | scheduled | user_query"
        TIMESTAMP created_at
        TEXT created_by
    }

    runbook_versions {
        UUID id PK
        UUID runbook_id FK
        INT version_number "NOT NULL — UNIQUE with runbook_id"
        JSONB workflow_definition "NOT NULL — steps array with skill_id per step"
        BOOLEAN is_active "DEFAULT TRUE"
        TIMESTAMP created_at
        TEXT created_by
    }

    runbook_execution_logs {
        UUID id PK
        UUID runbook_version_id FK
        TEXT triggered_by
        TEXT status "NOT NULL — running | completed | failed"
        TIMESTAMP started_at "DEFAULT now()"
        TIMESTAMP completed_at
    }

    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)"
```

---

### `{source_name}` — Knowledge Base: Escalation Rules & Plan Cache

```mermaid
erDiagram
    escalation_rules {
        UUID id PK
        INT version_id "NOT NULL DEFAULT 1"
        TEXT rule_name "NOT NULL — UNIQUE with version_id"
        TEXT description
        VARCHAR entity_type "NOT NULL — bullet | thread | record"
        TEXT trigger_condition "NOT NULL — e.g. sentiment_score < 3"
        VARCHAR priority "NOT NULL — P0 | P1 | P2 | P3"
        VARCHAR action_type "NOT NULL DEFAULT email — email | slack | webhook"
        VARCHAR recipient_role "NOT NULL — e.g. project-manager"
        VARCHAR severity "optional alias for priority"
        TEXT alert_message "NOT NULL — template string"
        JSONB rule_metadata "NOT NULL DEFAULT '{}'"
        BOOLEAN active_flag "NOT NULL DEFAULT TRUE"
        TIMESTAMPTZ created_at "NOT NULL"
        TEXT created_by
    }

    plan_cache {
        UUID id PK
        TEXT execution_target "NOT NULL — local_db | live_source | resource_rag"
        TEXT intent_phrase "NOT NULL — canonical question"
        VECTOR intent_embedding "VECTOR(1536) NOT NULL — ivfflat index"
        JSONB parameterized_query "NOT NULL"
        INT hit_count "NOT NULL DEFAULT 0"
        TIMESTAMPTZ last_hit_at
        TIMESTAMPTZ expires_at "NOT NULL — TTL"
        TIMESTAMPTZ created_at "NOT NULL"
        TEXT created_by "DEFAULT llm-cache-agent"
    }
```

> `escalation_rules` has no relationships in this group — it is referenced by `alerts` in the Sync section below.  
> `plan_cache` is a standalone lookup table; no FK relationships.

---

### `{source_name}` — Knowledge Base: Resources

```mermaid
erDiagram
    resources {
        UUID id PK
        TEXT resource_name "NOT NULL"
        TEXT resource_type "NOT NULL — sop | manual | guide | handbook | policy"
        TEXT description
        TEXT source "origin path or URL of the document"
        TIMESTAMPTZ created_at "NOT NULL"
        TEXT created_by
    }

    resource_chunks {
        UUID id PK
        UUID resource_id FK
        INT chunk_index "NOT NULL — 0, 1, 2 …"
        TEXT chunk_text "NOT NULL"
        VECTOR embedding "VECTOR(1536) — ivfflat index"
        TIMESTAMPTZ created_at "NOT NULL"
    }

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

---

### `{source_name}` — Sync & Operational State

```mermaid
erDiagram
    refresh_cursors {
        UUID id PK
        TEXT agent_id "UNIQUE NOT NULL — e.g. gmail-dsa"
        TIMESTAMPTZ last_synced_at "NOT NULL — S1 cursor"
        TEXT run_status "NOT NULL DEFAULT idle — idle | running | failed"
        TIMESTAMPTZ updated_at "NOT NULL"
    }

    refresh_logs {
        UUID id PK
        TEXT agent_id "NOT NULL"
        TIMESTAMPTZ window_start "NOT NULL — S1"
        TIMESTAMPTZ window_end "NOT NULL — S2"
        INT rows_fetched "NOT NULL DEFAULT 0"
        INT rows_enriched "NOT NULL DEFAULT 0"
        INT alerts_raised "NOT NULL DEFAULT 0"
        TEXT status "NOT NULL — success | partial | failed"
        TEXT error_detail
        TIMESTAMPTZ started_at "NOT NULL"
        TIMESTAMPTZ completed_at
    }

    escalation_rules {
        UUID id PK
        TEXT rule_name "NOT NULL"
        VARCHAR priority "P0 | P1 | P2 | P3"
        BOOLEAN active_flag "NOT NULL"
    }

    alerts {
        UUID id PK
        TEXT agent_id "NOT NULL"
        TEXT project_id FK
        UUID rule_id FK
        TEXT entity_id "NOT NULL — ID of triggered record"
        TEXT entity_type "NOT NULL — email_bullet | task | deal"
        TEXT severity "NOT NULL — P0 | P1 | P2 | P3"
        TEXT summary "NOT NULL"
        TEXT evidence_url
        JSONB payload "NOT NULL DEFAULT '{}'"
        ENUM status "NOT NULL DEFAULT pending"
        TIMESTAMPTZ raised_at "NOT NULL"
        TIMESTAMPTZ dispatched_at
        TIMESTAMPTZ acknowledged_at
    }

    a2a_dispatch_logs {
        UUID id PK
        UUID alert_id FK
        TEXT agent_id "NOT NULL — sending DSA"
        TEXT target_agent "NOT NULL DEFAULT chanakya"
        TEXT task_id "A2A task ID from message/send"
        JSONB message_payload "NOT NULL"
        TEXT send_status "NOT NULL — sent | failed"
        TEXT ack_status "acknowledged | timeout | error"
        TIMESTAMPTZ send_at "NOT NULL"
        TIMESTAMPTZ ack_at
        TEXT error_detail
    }

    projects {
        TEXT project_id PK "short key e.g. EB, IIRM"
        TEXT project_name "NOT NULL"
        TEXT status "NOT NULL DEFAULT active"
    }

    escalation_rules ||--o{ alerts : "rule_id"
    projects ||--o{ alerts : "project_id"
    alerts ||--o{ a2a_dispatch_logs : "alert_id"
```

---

### Full Cross-Table Relationship Map

```mermaid
erDiagram
    concepts ||--o{ world_model_concept_map : "concept_id"
    world_models ||--o{ world_model_concept_map : "world_model_id"

    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"

    resources ||--o{ resource_chunks : "resource_id"

    escalation_rules ||--o{ alerts : "rule_id"
    alerts ||--o{ a2a_dispatch_logs : "alert_id"
    projects ||--o{ alerts : "project_id"
```

---

## Schema: `{source_name}` — Knowledge Base

> One schema per DSA — instantiated as `gmail`, `jira`, `salesforce`, etc.  
> All KB tables share the same structure across every source instance.

---

### `{source_name}.concepts`
One row per entity type in the source system. Describes queryable fields and their types. Loaded into agent memory at boot for NL2SQL generation.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | Auto-generated |
| `concept_name` | `TEXT` UNIQUE NOT NULL | e.g. `"Thread"`, `"Issue"`, `"Lead"`, `"Epic"` |
| `schema_definition` | `JSONB` NOT NULL | `{"fields": [{"name": "status", "type": "text"}, …]}` |
| `description` | `TEXT` | |
| `updated_at` | `TIMESTAMP` | |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `created_by` | `TEXT` | |

---

### `{source_name}.world_models`
One row per business-language term. Translates domain vocabulary into source-specific field mappings. Loaded into agent memory at boot alongside Concepts.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | Auto-generated |
| `business_term` | `TEXT` UNIQUE NOT NULL | e.g. `"Velocity"`, `"Escalation"`, `"Negative Communication"` |
| `business_context` | `TEXT` NOT NULL | What this term means in business language |
| `business_rules` | `TEXT` | Plain-English rules for interpreting this term |
| `domain` | `TEXT` | `"delivery"` \| `"sales"` \| `"communication"` \| `"hr"` |
| `created_by` | `TEXT` | |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `updated_at` | `TIMESTAMPTZ` | |

---

### `{source_name}.world_model_concept_map`
Junction table — maps each world model term to one or more Concepts within the same source.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `world_model_id` | `UUID` FK → `world_models.id` CASCADE | |
| `concept_id` | `UUID` FK → `concepts.id` CASCADE | |
| `mapping_rules` | `JSONB` | `{"relevant_fields": ["sentiment_score", "urgency_score"]}` |
| `created_at` | `TIMESTAMP` DEFAULT `now()` | |
| | UNIQUE `(world_model_id, concept_id)` | One mapping per term–concept pair |

---

### `{source_name}.skills`
Identity record for a callable enrichment function. Execution logic lives in `skill_versions`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `skill_name` | `TEXT` NOT NULL | e.g. `"get_sentiment"`, `"classify_type"`, `"score_urgency"` |
| `description` | `TEXT` | |
| `created_at` | `TIMESTAMP` DEFAULT `now()` | |
| `created_by` | `TEXT` | |

---

### `{source_name}.skill_versions`
Versioned execution config for a skill. Immutable after creation — updates create a new version.

| 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` |
| `execution_type` | `TEXT` NOT NULL | `"api"` \| `"python_function"` \| `"sql"` |
| `execution_endpoint` | `TEXT` NOT NULL | e.g. `"/gmail/sentiment"` or function path |
| `input_schema` | `JSONB` | Expected input shape |
| `output_schema` | `JSONB` | Expected output shape |
| `is_active` | `BOOLEAN` DEFAULT `TRUE` | Only one version active per skill (app-layer enforced) |
| `created_at` | `TIMESTAMP` DEFAULT `now()` | |
| `created_by` | `TEXT` | |
| | UNIQUE `(skill_id, version_number)` | |

---

### `{source_name}.skill_execution_logs`
Immutable runtime log. One row per skill invocation. Used for audit and cost tracking (NFR-8).

| 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 when called from a runbook step |
| `input_payload` | `JSONB` | |
| `output_payload` | `JSONB` | |
| `status` | `TEXT` NOT NULL | `"success"` \| `"failed"` \| `"timeout"` |
| `input_token_count` | `INT` | LLM input tokens consumed |
| `output_token_count` | `INT` | LLM output tokens consumed |
| `cost_usd` | `NUMERIC(10,6)` | Approximate USD cost |
| `duration_ms` | `INT` | Wall-clock execution time |
| `executed_at` | `TIMESTAMP` DEFAULT `now()` | |

---

### `{source_name}.runbooks`
Identity record for a multi-step enrichment workflow. Steps live in `runbook_versions`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `runbook_name` | `TEXT` NOT NULL | e.g. `"enrich_email_thread"`, `"classify_jira_issue"` |
| `description` | `TEXT` | |
| `trigger_type` | `TEXT` | `"alert"` \| `"scheduled"` \| `"user_query"` |
| `created_at` | `TIMESTAMP` DEFAULT `now()` | |
| `created_by` | `TEXT` | |

---

### `{source_name}.runbook_versions`
Versioned workflow definition. Steps stored as JSONB. Old versions kept for audit — never deleted.

| 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 | `{"steps": [{"step": 1, "skill_id": "<uuid>", "description": "…"}, …]}` |
| `is_active` | `BOOLEAN` DEFAULT `TRUE` | Only one version active per runbook |
| `created_at` | `TIMESTAMP` DEFAULT `now()` | |
| `created_by` | `TEXT` | |
| | UNIQUE `(runbook_id, version_number)` | |

---

### `{source_name}.runbook_execution_logs`
Immutable runtime log. One row per runbook execution. Parent record for `skill_execution_logs`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `runbook_version_id` | `UUID` FK → `runbook_versions.id` | Records which version ran |
| `triggered_by` | `TEXT` | `"scheduler"` \| `"alert_engine"` \| `"user"` |
| `status` | `TEXT` NOT NULL | `"running"` \| `"completed"` \| `"failed"` |
| `started_at` | `TIMESTAMP` DEFAULT `now()` | |
| `completed_at` | `TIMESTAMP` | |

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

---

### `{source_name}.escalation_rules`
Behavioral Red Flag triggers. Loaded into agent memory at boot. Immutable — updates insert a new version row; old rows get `active_flag = FALSE`.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `version_id` | `INT` NOT NULL DEFAULT `1` | Increments on each update |
| `entity_type` | `VARCHAR(50)` NOT NULL | `"bullet"` \| `"thread"` \| `"record"` |
| `trigger_condition` | `TEXT` NOT NULL | Evaluated expression e.g. `"sentiment_score < 3"` |
| `priority` | `VARCHAR(10)` NOT NULL | `"P0"` \| `"P1"` \| `"P2"` \| `"P3"` |
| `action_type` | `VARCHAR(50)` NOT NULL DEFAULT `'email'` | `"email"` \| `"slack"` \| `"webhook"` |
| `recipient_role` | `VARCHAR(100)` NOT NULL | e.g. `"project-manager"`, `"engineering-lead"` |
| `severity_level` | `VARCHAR(10)` | Optional alias for `priority` |
| `rule_metadata` | `JSONB` NOT NULL DEFAULT `'{}'` | Source-specific config e.g. `{"keyword_list": ["furious"]}` |
| `active_flag` | `BOOLEAN` NOT NULL DEFAULT `TRUE` | Only active rules loaded at boot |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `created_by` | `TEXT` | |
| | UNIQUE `(rule_name, version_id)` | |

---

### `{source_name}.plan_cache`
Parameterized query plan store keyed by semantic intent embedding. Not an answer cache — the source is always re-queried at answer time; only the query logic is reused.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `execution_target` | `TEXT` NOT NULL | `"local_db"` \| `"live_source"` \| `"resource_rag"` |
| `intent_phrase` | `TEXT` NOT NULL | Canonical human-readable question |
| `intent_embedding` | `VECTOR(1536)` NOT NULL | Embedding of `intent_phrase`; cosine lookup at query time |
| `parameterized_query` | `JSONB` NOT NULL | `{"query_type": "sql", "template": "…", "params": […]}` |
| `hit_count` | `INT` NOT NULL DEFAULT `0` | Times this plan was reused |
| `last_hit_at` | `TIMESTAMPTZ` | |
| `expires_at` | `TIMESTAMPTZ` NOT NULL | TTL-based expiry |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `created_by` | `TEXT` NOT NULL DEFAULT `'llm-cache-agent'` | |

---

### `{source_name}.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. `"Email Communication Policy"` |
| `resource_type` | `TEXT` NOT NULL | `"sop"` \| `"manual"` \| `"guide"` \| `"handbook"` \| `"policy"` |
| `description` | `TEXT` | |
| `source` | `TEXT` | Origin path or URL of the document |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `created_by` | `TEXT` | |

---

### `{source_name}.resource_chunks`
Auto-generated chunks with embeddings. N rows per document. Used for Stage 3C RAG lookup via cosine similarity.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `resource_id` | `UUID` FK → `resources.id` CASCADE | Deleting a resource removes all its chunks |
| `chunk_index` | `INT` NOT NULL | Position in document: 0, 1, 2 … |
| `chunk_text` | `TEXT` NOT NULL | Text content of this chunk |
| `embedding` | `VECTOR(1536)` | Generated by embedding model at upload time |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |

> Indexed with `ivfflat` using `vector_cosine_ops`.

---

## Schema: `{source_name}` — Delta Tables (Enriched Data)

> Column shapes are defined per-source by the integrating team. The structure below is the **minimum footprint** every parent delta table must follow. Source-specific payload columns are inserted between `enriched_at` and `summary`.

---

### Generalized Parent Record (`{source_name}.{entity_name}`)

```mermaid
erDiagram
    entity_name {
        TEXT id PK "source-native record ID"
        TIMESTAMPTZ created_at "NOT NULL — source timestamp"
        TEXT project_id FK "optional — REFERENCES shared.projects"
        TIMESTAMPTZ enriched_at "NOT NULL DEFAULT now()"
        TEXT summary "LLM-generated overall summary"
        VECTOR embeddings "VECTOR(1536) — record-level semantic embedding"
    }

```

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `TEXT` PK | Source-native record ID (e.g. `message_id`, `issue_key`, `deal_id`) — enables idempotent upsert on conflict |
| `created_at` | `TIMESTAMPTZ` NOT NULL | Record creation timestamp from the source system (not the pipeline write time) |
| `project_id` | `TEXT` FK → `shared.projects(project_id)` **(optional)** | Only include when the source supports project-level scoping; resolved by LLM inference in Stage 2 |
| `enriched_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | When this row was last written/updated by the DSA pipeline |
| *(source-specific columns)* | varies | e.g. `attachments JSONB`, `subject TEXT`, `status TEXT`, `priority TEXT` |
| `summary` | `TEXT` | LLM-generated plain-text summary of the record |
| `embeddings` | `VECTOR(1536)` | Generated from `summary`; record-level semantic embedding for similarity search |

> **Child intelligence tables** (e.g. `{source_name}.{entity_name}_bullets`) are optional. They follow the same FK pattern — one row per extracted signal unit, back-referencing the parent via `entity_id CASCADE`, with their own per-unit `embedding` column.


---

## Schema: `{source_name}` — Sync & Operational State

> These tables live in the same `{source_name}` schema as the KB and delta tables.  
> Each DSA fully owns its own sync state — no shared operational tables across agents.

---

### `{source_name}.refresh_cursors`
One row per agent. Tracks the S1 cursor (last successful sync end) and acts as an idempotency lock preventing concurrent refresh runs.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `agent_id` | `TEXT` UNIQUE NOT NULL | e.g. `"gmail-dsa"`, `"jira-dsa"` |
| `last_synced_at` | `TIMESTAMPTZ` NOT NULL | S1: end of last successful sync window |
| `run_status` | `TEXT` NOT NULL DEFAULT `'idle'` | `"idle"` \| `"running"` \| `"failed"` |
| `updated_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |

---

### `{source_name}.refresh_logs`
Full audit log of every refresh run — row counts, timing, outcome.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `agent_id` | `TEXT` NOT NULL | |
| `window_start` | `TIMESTAMPTZ` NOT NULL | S1 cursor value at start of this run |
| `window_end` | `TIMESTAMPTZ` NOT NULL | `now()` at start of this run (S2) |
| `rows_fetched` | `INT` NOT NULL DEFAULT `0` | Records pulled from source API |
| `rows_enriched` | `INT` NOT NULL DEFAULT `0` | Records successfully enriched |
| `alerts_raised` | `INT` NOT NULL DEFAULT `0` | Escalation rules matched this run |
| `status` | `TEXT` NOT NULL | `"success"` \| `"partial"` \| `"failed"` |
| `error_detail` | `TEXT` | Set on `partial` or `failed` |
| `started_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `completed_at` | `TIMESTAMPTZ` | |

---

### `{source_name}.alerts`
Outbox table for Red Flags. One row per escalation rule match. Rows are consumed by the A2A dispatch process and forwarded to the RT Agent (Chanakya).

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `agent_id` | `TEXT` NOT NULL | DSA that raised this alert |
| `project_id` | `TEXT` FK → `shared.projects(project_id)` | Project context (nullable) |
| `rule_id` | `UUID` FK → `{source_name}.escalation_rules(id)` NOT NULL | Rule that fired — DB-level FK (same schema) |
| `entity_id` | `TEXT` NOT NULL | ID of the record that triggered this alert |
| `entity_type` | `TEXT` NOT NULL | `"email_bullet"` \| `"task"` \| `"deal"` |
| `severity` | `TEXT` NOT NULL | `"P0"` \| `"P1"` \| `"P2"` \| `"P3"` |
| `summary` | `TEXT` NOT NULL | Human-readable alert message |
| `evidence_url` | `TEXT` | Deep link back to source record |
| `payload` | `JSONB` NOT NULL DEFAULT `'{}'` | Full structured alert payload |
| `status` | `ENUM(alert_status)` NOT NULL DEFAULT `'pending'` | `pending` \| `dispatched` \| `acknowledged` \| `failed` |
| `raised_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `dispatched_at` | `TIMESTAMPTZ` | Set when A2A send succeeds |
| `acknowledged_at` | `TIMESTAMPTZ` | Set when Chanakya ACKs |

---

### `{source_name}.a2a_dispatch_logs`
Immutable audit log of every A2A message sent to the RT Agent — including the async poll outcome.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `id` | `UUID` PK | |
| `alert_id` | `UUID` FK → `{source_name}.alerts(id)` | Alert this dispatch corresponds to |
| `agent_id` | `TEXT` NOT NULL | Sending DSA |
| `target_agent` | `TEXT` NOT NULL DEFAULT `'chanakya'` | Receiving RT agent |
| `task_id` | `TEXT` | A2A task ID returned by `message/send` |
| `message_payload` | `JSONB` NOT NULL | Full payload sent |
| `send_status` | `TEXT` NOT NULL | `"sent"` \| `"failed"` |
| `ack_status` | `TEXT` | `"acknowledged"` \| `"timeout"` \| `"error"` |
| `send_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `ack_at` | `TIMESTAMPTZ` | |
| `error_detail` | `TEXT` | |

---

## Schema: `shared`

> Platform-wide reference data. Owned by the platform, not by any individual DSA.

---

### `shared.projects`
Canonical project registry. `project_id` is the short key used as a FK by every DSA's delta tables and alerts.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `project_id` | `TEXT` PK | Short key e.g. `"EB"`, `"IIRM"`, `"Optinn"` |
| `project_name` | `TEXT` NOT NULL | |
| `description` | `TEXT` | |
| `status` | `TEXT` NOT NULL DEFAULT `'active'` | `"active"` \| `"inactive"` \| `"archived"` |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `updated_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |

---

## Indexes

### `{source_name}` — Knowledge Base

| Table | Column(s) | Type | Purpose |
|-------|-----------|------|---------|
| `world_models` | `domain` | btree | Filter terms by domain |
| `plan_cache` | `execution_target` | btree | Filter by query target type |
| `plan_cache` | `expires_at` | btree | TTL sweep / expiry queries |
| `plan_cache` | `intent_embedding` | `ivfflat` (cosine) | Semantic plan lookup at query time |
| `resource_chunks` | `resource_id` | btree | All chunks for a document |
| `resource_chunks` | `embedding` | `ivfflat` (cosine) | Stage 3C RAG cosine similarity |
| `escalation_rules` | `active_flag` (partial) | btree | Boot-time load: WHERE active_flag = TRUE |
| `escalation_rules` | `version_id DESC` | btree | Latest version lookup |
| `escalation_rules` | `priority` | btree | Filter by P0/P1/P2/P3 |
| `escalation_rules` | `entity_type` | btree | Filter rules by entity |

### `{source_name}` — Skills & Runbooks

| Table | Column(s) | Type | Purpose |
|-------|-----------|------|---------|
| `skill_versions` | `(skill_id, is_active)` | btree | Active version lookup |
| `runbook_versions` | `(runbook_id, is_active)` | btree | Active version lookup |
| `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 |

### `{source_name}` — Sync & Operational

| Table | Column(s) | Type | Purpose |
|-------|-----------|------|---------|
| `refresh_logs` | `agent_id` | btree | All runs for this agent |
| `refresh_logs` | `started_at DESC` | btree | Recent runs |
| `refresh_logs` | `status` | btree | Filter failures |
| `alerts` | `agent_id` | btree | Alerts per agent |
| `alerts` | `project_id` | btree | Alerts per project |
| `alerts` | `severity` | btree | Filter by priority |
| `alerts` | `status` | btree | Pending dispatch queue |
| `alerts` | `raised_at DESC` | btree | Recent alerts |
| `a2a_dispatch_logs` | `alert_id` | btree | Dispatches per alert |
| `a2a_dispatch_logs` | `send_status` | btree | Failed dispatch audit |
| `a2a_dispatch_logs` | `send_at DESC` | btree | Recent dispatches |

---

## Why the tables are structured this way

| Pattern | Tables | Reason |
|---------|--------|--------|
| **Identity + Versions** | `skills`/`skill_versions`, `runbooks`/`runbook_versions`, `escalation_rules` (version_id) | Execution logic and trigger conditions change over time; old versions are kept for audit. Identity never changes. |
| **Execution logs are immutable** | `skill_execution_logs`, `runbook_execution_logs` | Runtime record of every invocation — never updated, never deleted. Source of truth for cost, latency, and audit. |
| **KB loaded at boot, not at runtime** | `concepts`, `world_models`, `escalation_rules` | These are small, stable, behavioural tables. Loading them into agent memory at startup avoids per-request DB round-trips. |
| **Plan cache is not an answer cache** | `plan_cache` | The parameterized query template is reused; the underlying data source is always re-queried. Saves LLM tokens on repeated questions. |
| **Outbox pattern for alerts** | `alerts` + `a2a_dispatch_logs` | Decouples rule evaluation from A2A delivery. If Chanakya is temporarily unavailable, alerts remain `pending` and can be retried. |
| **Everything per-source** | All tables in `{source_name}` | Each DSA owns and operates its entire data boundary. No cross-schema joins for normal operation. Migrations and access control apply to one schema. |
| **`projects` in `shared`** | `shared.projects` | Cross-cutting FK used by every DSA's delta tables. One canonical truth — not duplicated per source. |