# Schema Map — Shared (Global Tables)

> **Documentation only** — no executable SQL.  
> Alert lifecycle LLD: `docs/phase-2/llds/deb-366-alert-lifecycle-agent-to-understand-escallations.md`

---

## Legend

| Symbol | Meaning |
|--------|---------|
| `PK` | Primary key (UUID, auto-generated unless noted) |
| `FK` | Foreign key |
| `CASCADE` | ON DELETE CASCADE applied |
| `JSONB` | Flexible schema stored as PostgreSQL JSONB |
| `ENUM` | PostgreSQL enumerated type |
| `eb` | Central platform schema — the alert lifecycle domain. Multiple agents write here; schema reflects the data domain, not which agent owns the process |

---

## Overview

own schema. The `eb` schema is the **alert lifecycle data domain** — schema assignment reflects which bounded context the data belongs to, not which agent writes the rows.

| Table | Schema | Written by | Read by |
|-------|--------|-----------|--------|
| `alerts` | `eb` | RTDI Agent (Chanakya) — created after cross-source correlation | Distribution Engine (picks up `OPEN` alerts), Dashboard, Audit |
| `alert_notifications` | `eb` | Distribution Engine (User Aware Agent) — one row per delivery attempt | Audit, retry logic, delivery status tracking |

---

## Entity Relationship Diagrams

### Global Tables

```mermaid
erDiagram

    alerts {
        UUID alert_id PK
        TEXT type "NOT NULL — e.g. PROJECT_ESCALATION"
        TEXT recipient_role "NOT NULL — functional role e.g. Delivery Lead"
        TEXT message "NOT NULL — synthesised alert message"
        ENUM status "NOT NULL DEFAULT OPEN"
        TIMESTAMPTZ created_at "NOT NULL"
        TIMESTAMPTZ updated_at "NOT NULL"
    }

    alert_notifications {
        UUID notification_id PK
        UUID alert_id FK "REFERENCES eb.alerts CASCADE"
        TEXT user_id "NOT NULL — resolved person from org_info"
        TEXT recipient_email "NOT NULL"
        VARCHAR channel "NOT NULL DEFAULT email"
        TEXT status "NOT NULL — pending | sent | failed"
        TEXT error_detail
        TIMESTAMPTZ sent_at
        TIMESTAMPTZ created_at "NOT NULL"
    }

    alerts ||--o{ alert_notifications : "alert_id (CASCADE)"
```

---


### `eb.alerts`

One row per synthesised alert. Created by the RTDI Agent (Chanakya) after State 1 vs. State 2 comparison and cross-source correlation. Immutable after creation — only `status` and `updated_at` may change.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `alert_id` | `UUID` PK | Auto-generated |
| `type` | `TEXT` NOT NULL | Alert classification — e.g. `"PROJECT_ESCALATION"`, `"SENTIMENT_ALERT"`, `"DEAL_AT_RISK"` |
| `recipient_role` | `TEXT` NOT NULL | Functional role that should receive this alert — e.g. `"Delivery Lead"`. The Distribution Engine resolves this to a specific person at dispatch time |
| `message` | `TEXT` NOT NULL | Human-readable synthesised alert message |
| `status` | `ENUM(alert_status)` NOT NULL DEFAULT `'OPEN'` | `OPEN` → `DISPATCHED` → `ACKNOWLEDGED` → `CLOSED` |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | |
| `updated_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | Updated on status transitions only |

> **Immutability policy:** `type`, `message`, `recipient_role` are never modified after creation. Only `status` and `updated_at` may change (NFR-04).

---

### `eb.alert_notifications`

One row per delivery attempt per recipient per channel. A single alert may produce multiple rows — e.g. one email to `Delivery Lead` and one to `CEO` for a P0 compound alert.

Written by the Distribution Engine after it resolves `recipient_role` → specific person via the Org Snapshot.

| Column | Type / Constraint | Notes |
|--------|-------------------|-------|
| `notification_id` | `UUID` PK | Auto-generated |
| `alert_id` | `UUID` FK → `eb.alerts(alert_id)` CASCADE | Parent alert |
| `user_id` | `TEXT` NOT NULL | `org_info.org_people.id` of the resolved recipient |
| `recipient_email` | `TEXT` NOT NULL | Resolved email address at dispatch time — snapshot for audit correctness |
| `channel` | `VARCHAR(20)` NOT NULL DEFAULT `'email'` | `"email"` (Phase 1) \| `"slack"` \| `"whatsapp"` \| `"dashboard"` (Phase 2) |
| `status` | `TEXT` NOT NULL | `"pending"` \| `"sent"` \| `"failed"` |
| `error_detail` | `TEXT` | Populated on `"failed"` — e.g. SMTP error, invalid address |
| `sent_at` | `TIMESTAMPTZ` | Set when delivery succeeds |
| `created_at` | `TIMESTAMPTZ` NOT NULL DEFAULT `now()` | When the Distribution Engine created this delivery record |

> **Phase 1:** `channel` is always `"email"`. Slack, WhatsApp, and Dashboard are Phase 2.

---

## ENUM Types

```sql
CREATE TYPE eb.alert_status AS ENUM ('OPEN', 'DISPATCHED', 'ACKNOWLEDGED', 'CLOSED');
```

| Value | Meaning |
|-------|---------|
| `OPEN` | Alert created; not yet picked up by Distribution Engine |
| `DISPATCHED` | Distribution Engine has created at least one `alert_notifications` row |
| `ACKNOWLEDGED` | Recipient confirmed receipt (Phase 2) |
| `CLOSED` | Alert resolved or expired |

---

## Indexes

### `shared`

| Table | Column(s) | Type | Purpose |
|-------|-----------|------|---------|
| `projects` | `status` | btree | Filter active / archived projects |

### `eb`

| Table | Column(s) | Type | Purpose |
|-------|-----------|------|---------|
| `alerts` | `source_id` | btree | All alerts per originating DSA |
| `alerts` | `project_id` | btree | All alerts scoped to a project |
| `alerts` | `priority` | btree | Filter P0/P1 for critical bypass (FR-11) |
| `alerts` | `status` | btree | Open alert queue; dispatch polling |
| `alerts` | `created_at DESC` | btree | Recent alerts |
| `alert_notifications` | `alert_id` | btree | All notifications for an alert |
| `alert_notifications` | `user_id` | btree | All notifications sent to a person |
| `alert_notifications` | `status` | btree | Pending / failed delivery queue |
| `alert_notifications` | `channel` | btree | Filter by delivery channel |
| `alert_notifications` | `sent_at DESC` | btree | Recent dispatches |

---

## Why the tables are structured this way

| Pattern | Tables | Reason |
|---------|--------|--------|
| **`projects` in `shared`** | `shared.projects` | Cross-cutting FK used by every DSA's delta tables and by Chanakya. One canonical truth — not duplicated per source. |
| **Alerts in `eb`, not per-source** | `eb.alerts` | Compound/cross-source alerts do not belong to any single DSA. Centralising in `eb` lets Chanakya own the full post-correlation lifecycle. |
| **Soft reference to escalation rules** | `alerts.rule_id` (no FK) | Escalation rules live in `{source_name}.escalation_rules` — a different schema per source. A DB-level FK would break the plug-and-play isolation of each DSA. |
| **Notifications as child rows** | `alert_notifications` (1-to-many on `alert_id`) | A single alert can be dispatched to multiple roles, channels, and people. Separating delivery tracking from the alert body keeps `alerts` clean and enables per-channel retry. |
| **Snapshot recipient at send time** | `recipient_email` on `alert_notifications` | Org assignments change. Recording the resolved email at dispatch time ensures the audit log stays accurate after org restructuring. |
| **Immutable alert body** | `eb.alerts` | Every alert must be traceable to the raw signal that triggered it (NFR-04). Only `status` transitions are permitted post-creation. |