# Knowledge Base — Org Snapshot Schema Mapping

> **Migration file:** [`backend/database/migrations/001_create_eb_schema.sql`](../../backend/database/migrations/001_create_eb_schema.sql)
> **Related LLD:** [`docs/phase-2/llds/gmail-data-pipeline-lld.md`](../llds/gmail-data-pipeline-lld.md)
> **PostgreSQL:** 15+ | **Extensions:** `pgvector`, `pgcrypto`

---

## Schema Layout

Three PostgreSQL schemas. Each has a distinct ownership and lifecycle.

| Schema | Purpose | Tables |
|---|---|---|
| `core` | **Global / Core** — project registry; referenced by any source schema | `core.projects` |
| `gmail` | **Gmail source layer** — raw emails enriched to intelligence units | `gmail.emails`, `gmail.email_bullets` |
| `snapshot` | **RT Agent output** — cross-source org snapshot | `snapshot.org_project_snapshot` |

Adding a new data source (e.g. Jira, GChat) means creating a new source schema (`jira`, `gchat`) following the same `emails` + `email_bullets` pattern, with a cross-schema FK to `core.projects`.


---

## Entity Relationship Diagram

```mermaid
erDiagram
    core_projects {
        TEXT project_id PK
        TEXT project_name
        TEXT description
        BOOLEAN is_active
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
    }

    gmail_emails {
        TEXT id PK
        TEXT thread_id
        TEXT email_link
        TEXT user_id
        TEXT from
        TEXT_ARRAY recipients
        TEXT subject
        TEXT thread_position
        TIMESTAMPTZ created_at
        JSONB attachments
        TEXT project_id FK
        TEXT summary
        TIMESTAMPTZ enriched_at
        VECTOR embeddings
    }

    gmail_email_bullets {
        UUID bullet_id PK
        TEXT email_id FK
        TEXT text
        TEXT sentiment_value
        NUMERIC sentiment_score
        TEXT type
        TEXT category
        TEXT role
        SMALLINT urgency_score
        TEXT project_id FK
        TEXT user_id
        TEXT_ARRAY recipients
        TIMESTAMPTZ created_at
        VECTOR embeddings
    }

    snapshot_org_project_snapshot {
        UUID id PK
        TEXT project_id FK
        TEXT source
        TIMESTAMPTZ last_updated
        TIMESTAMPTZ previous_snapshot_at
        JSONB content
        JSONB meta_data
        TIMESTAMPTZ deleted_at
        TEXT deleted_by
    }

    core_projects ||--o{ gmail_emails : project_id
    gmail_emails ||--o{ gmail_email_bullets : email_id
    core_projects ||--o{ gmail_email_bullets : project_id
    core_projects ||--o{ snapshot_org_project_snapshot : project_id
```


---

## ENUM Types

Defined in the `core` schema. Shared across all source schemas and the snapshot schema.


| ENUM | Values |
|---|---|
| `core.thread_position` | `original`, `reply`, `forward` |
| `core.bullet_type` | `information`, `alert`, `insight`, `escalation` |
| `core.bullet_category` | `bug-report`, `release-update`, `meeting-notes`, `status-update`, `announcement`, `general` |
| `core.sentiment_value` | `positive`, `neutral`, `negative` |

---

## Schema: `core`

### `core.projects`

**Purpose:** Global canonical project registry. `project_id` is the FK anchor for all tables across all schemas.


| Column | Type | Constraints | Default | Description |
|---|---|---|---|---|
| `project_id` | `TEXT` | `PRIMARY KEY` | — | Short canonical key. e.g. `EB`, `IIRM`, `Optinn` |
| `project_name` | `TEXT` | `NOT NULL` | — | Full display name. e.g. `Enterprise Brain` |
| `description` | `TEXT` | — | `NULL` | Optional project description |
| `is_active` | `BOOLEAN` | `NOT NULL` | `TRUE` | Soft-disable without breaking FK rows |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | `now()` | Row creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | `now()` | Last modification timestamp |

**Indexes:**


| Index | Type | Column(s) |
|---|---|---|
| `idx_projects_name` | B-tree | `project_name` |
| `idx_projects_active` | Partial B-tree | `is_active` WHERE `TRUE` |

---

## Schema: `gmail`

### `gmail.emails`

**Purpose:** One row per unique email. Stores Gmail headers (direct from API) + LLM summary + project mapping + email-level embedding. Parent record for all bullet rows.

| Column | Type | Constraints | Default | Description |
|---|---|---|---|---|
| `id` | `TEXT` | `PRIMARY KEY` | — | Gmail `message_id`; natural dedup key |
| `thread_id` | `TEXT` | — | `NULL` | Gmail thread ID |
| `email_link` | `TEXT` | — | `NULL` | Deep link to message in Gmail UI |
| `user_id` | `TEXT` | `NOT NULL` | — | Org user whose inbox provided this email |
| `from` | `TEXT` | `NOT NULL` | — | Sender email address |
| `recipients` | `TEXT[]` | `NOT NULL` | `{}` | Merged To + CC + BCC (deduplicated) |
| `subject` | `TEXT` | — | `NULL` | Email subject line |
| `thread_position` | `core.thread_position` | — | `NULL` | `original / reply / forward` |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | — | Send timestamp from Gmail `Date` header |
| `attachments` | `JSONB` | `NOT NULL` | `[]` | `[{ filename, mime_type, size_bytes }]` — no file content |
| `project_id` | `TEXT` | `NOT NULL`, FK → `core.projects` | — | LLM-inferred against controlled vocabulary |
| `summary` | `TEXT` | — | `NULL` | LLM-generated plain-text email summary |
| `enriched_at` | `TIMESTAMPTZ` | `NOT NULL` | `now()` | Timestamp of last LLM enrichment |
| `embeddings` | `vector(1536)` | — | `NULL` | Email-level semantic embedding (from summary) |

**Indexes:**

| Index | Type | Column(s) | Notes |
|---|---|---|---|
| `idx_emails_project_id` | B-tree | `project_id` | Hot filter for RT Agent |
| `idx_emails_user_id` | B-tree | `user_id` | Per-user lookups |
| `idx_emails_thread_id` | B-tree | `thread_id` | Thread grouping |
| `idx_emails_created_at` | B-tree DESC | `created_at` | Time-range queries |
| `idx_emails_enriched_at` | B-tree DESC | `enriched_at` | Re-enrichment scheduling |
| `idx_emails_recipients_gin` | GIN | `recipients` | Array containment queries |
| `idx_emails_embeddings_ivf` | IVFFlat | `embeddings` | ANN cosine — tune `lists` after volume known |

---

### `gmail.email_bullets`

**Purpose:** One row per bullet point per email. Each row is a fully independent intelligence unit. Cascade-deleted when the parent email is deleted.

| Column | Type | Constraints | Default | Description |
|---|---|---|---|---|
| `bullet_id` | `UUID` | `PRIMARY KEY` | `gen_random_uuid()` | Synthetic bullet identifier |
| `email_id` | `TEXT` | `NOT NULL`, FK → `gmail.emails(id)` ON DELETE CASCADE | — | Parent email reference |
| `text` | `TEXT` | `NOT NULL` | — | Extracted bullet text (one key insight) |
| `hashtags` | `TEXT[]` | `NOT NULL` | `{}` | Content hashtags. e.g. `['#bug', '#finance']` |
| `sentiment_value` | `core.sentiment_value` | `NOT NULL` | — | `positive / neutral / negative` |
| `sentiment_score` | `NUMERIC(4,2)` | `NOT NULL`, CHECK `[-1.00, 1.00]` | — | Float polarity score |
| `type` | `core.bullet_type` | `NOT NULL` | — | `information / alert / insight / escalation` |
| `category` | `core.bullet_category` | `NOT NULL` | — | `bug-report / release-update / …` |
| `role` | `TEXT` | `NOT NULL` | — | Target audience. e.g. `project-manager` |
| `urgency_score` | `SMALLINT` | `NOT NULL`, CHECK `[0, 5]` | `0` | 0 = no urgency, 5 = critical |
| `project_id` | `TEXT` | `NOT NULL`, FK → `core.projects` | — | Denormalised from parent; avoids JOIN on hot paths |
| `user_id` | `TEXT` | `NOT NULL` | — | Inherited from parent email |
| `recipients` | `TEXT[]` | `NOT NULL` | `{}` | Inherited from parent email |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | `now()` | Row creation timestamp |
| `embeddings` | `vector(1536)` | — | `NULL` | Per-bullet embedding for fine-grained RAG |

**Indexes:**

| Index | Type | Column(s) | Notes |
|---|---|---|---|
| `idx_bullets_email_id` | B-tree | `email_id` | Parent lookup |
| `idx_bullets_project_id` | B-tree | `project_id` | RT Agent hot query |
| `idx_bullets_user_id` | B-tree | `user_id` | Per-user bullet views |
| `idx_bullets_type` | B-tree | `type` | Dashboard type filters |
| `idx_bullets_category` | B-tree | `category` | Role-filtered views |
| `idx_bullets_sentiment` | B-tree | `sentiment_value` | Sentiment-filtered queries |
| `idx_bullets_urgency` | B-tree DESC | `urgency_score` | High-urgency alert surfacing |
| `idx_bullets_created_at` | B-tree DESC | `created_at` | Time-range sorting |
| `idx_bullets_hashtags_gin` | GIN | `hashtags` | Tag-based RAG filters |
| `idx_bullets_recipients_gin` | GIN | `recipients` | Participant-based routing |
| `idx_bullets_embeddings_ivf` | IVFFlat | `embeddings` | ANN cosine — tune `lists` after volume |

**`core.bullet_type` severity order (highest → lowest):**

| Value | Meaning |
|---|---|
| `escalation` | Critical; immediate action needed |
| `alert` | Requires attention; not yet critical |
| `insight` | Analytical or trend signal |
| `information` | General informational update |

---

## Schema: `snapshot`

### `snapshot.org_project_snapshot`

**Purpose:** One row per `(project_id, source)`. Written by the RT Agent after cross-source merge and diff. Stores only bullet UUID references and computed scalar signals — **no bullet text**. Read by the Distribution Engine and CXO dashboards.

| Column | Type | Constraints | Default | Description |
|---|---|---|---|---|
| `id` | `UUID` | `PRIMARY KEY` | `gen_random_uuid()` | Synthetic row identifier |
| `project_id` | `TEXT` | `NOT NULL`, FK → `core.projects` | — | The project this snapshot covers |
| `source` | `TEXT` | `NOT NULL` | — | Data source. e.g. `gmail`, `jira`, `gchat` |
| `last_updated` | `TIMESTAMPTZ` | `NOT NULL` | `now()` | Timestamp of last RT Agent write |
| `previous_snapshot_at` | `TIMESTAMPTZ` | — | `NULL` | `NULL` on first run; prior `last_updated` value before overwrite |
| `content` | `JSONB` | `NOT NULL` | `{}` | Bullet ID references + diff signals (see shape below) |
| `meta_data` | `JSONB` | — | `NULL` | Extensible metadata |
| `deleted_at` | `TIMESTAMPTZ` | — | `NULL` | Soft deletion timestamp |
| `deleted_by` | `TEXT` | — | `NULL` | User who deleted the snapshot |

**`content` JSONB shape:**

```json
{
  "bullet_ids":          ["uuid1", "uuid2", "uuid3"],
  "new_bullet_ids":      ["uuid3"],
  "resolved_bullet_ids": ["uuid-old-1"]
}
```

| Key | Description |
|---|---|
| `bullet_ids` | Full active set of bullet UUIDs at `last_updated` |
| `new_bullet_ids` | Subset new since `previous_snapshot_at` |
| `resolved_bullet_ids` | In previous snapshot; absent from current window |

**Indexes:**

| Index | Type | Column(s) |
|---|---|---|
| `idx_ops_project_id` | B-tree | `project_id` |
| `idx_ops_source` | B-tree | `source` |
| `idx_ops_last_updated` | B-tree DESC | `last_updated` |
| `idx_ops_deleted_at` | B-tree | `deleted_at` |

---

## Foreign Key Map

```
core.projects.project_id  (global — cross-schema references)
    ← gmail.emails.project_id
    ← gmail.email_bullets.project_id
    ← snapshot.org_project_snapshot.project_id
    ← (future) jira.tickets.project_id
    ← (future) gchat.messages.project_id

gmail.emails.id
    ← gmail.email_bullets.email_id  (ON DELETE CASCADE)
```

---

> **Note:** This schema was derived from the LLD authored while understanding the Gmail → Org Snapshot data flow. The tables, columns, and relationships reflect the current best understanding of the pipeline. **If any structure needs to change during implementation — make those changes here and update the LLD accordingly. The schema should reflect what is correct, not be frozen to the initial LLD.**