# Lilith Data Model

## Overview

Lilith uses a polyglot persistence strategy with multiple specialized databases
for different data types:

- **PostgreSQL**: Primary relational data (users, content, conversations)
- **Milvus/Weaviate**: Vector embeddings for semantic search
- **Neo4j**: Knowledge graph for relationships and ontologies
- **S3/Object Storage**: Media assets and documents
- **Redis**: Caching and session storage
- **Kafka**: Event streaming
- **TimescaleDB**: Time-series biometric data

## Entity Relationship Diagram

```
┌─────────────────────────────────────────────────────────────────────────────┐
│                              USER DOMAIN                                     │
│                                                                             │
│  ┌──────────────┐       ┌──────────────┐       ┌──────────────┐           │
│  │    users     │───────│   profiles   │───────│ preferences  │           │
│  │              │  1:1  │              │  1:1  │              │           │
│  │ • id         │       │ • user_id    │       │ • user_id    │           │
│  │ • email_hash │       │ • display_nm │       │ • personas   │           │
│  │ • created_at │       │ • timezone   │       │ • voices     │           │
│  │ • consent    │       │ • avatar_url │       │ • locale     │           │
│  └──────────────┘       └──────────────┘       └──────────────┘           │
│         │                                                                   │
│         │ 1:N                                                               │
│         ▼                                                                   │
│  ┌──────────────┐       ┌──────────────┐                                   │
│  │   sessions   │       │    events    │                                   │
│  │              │       │              │                                   │
│  │ • user_id    │       │ • user_id    │                                   │
│  │ • device_id  │       │ • type       │                                   │
│  │ • token_hash │       │ • payload    │                                   │
│  │ • expires_at │       │ • timestamp  │                                   │
│  └──────────────┘       └──────────────┘                                   │
└─────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────┐
│                            CONTENT DOMAIN                                    │
│                                                                             │
│  ┌──────────────┐       ┌──────────────┐       ┌──────────────┐           │
│  │   personas   │       │   content    │───────│content_links │           │
│  │              │       │              │  1:N  │              │           │
│  │ • id         │       │ • id         │       │ • content_id │           │
│  │ • key        │       │ • type       │       │ • source_uri │           │
│  │ • title      │       │ • title      │       │ • license    │           │
│  │ • style_cfg  │       │ • lang       │       │ • confidence │           │
│  │ • policy_tag │       │ • body_ref   │       │ • span       │           │
│  └──────────────┘       │ • status     │       └──────────────┘           │
│         │               │ • version    │                                   │
│         │               └──────────────┘                                   │
│         │                      │                                           │
│         │                      │ N:M                                       │
│         │                      ▼                                           │
│         │               ┌──────────────┐       ┌──────────────┐           │
│         │               │   courses    │───────│ course_items │           │
│         │               │              │  1:N  │              │           │
│         │               │ • id         │       │ • course_id  │           │
│         │               │ • title      │       │ • content_id │           │
│         │               │ • syllabus   │       │ • order_idx  │           │
│         │               └──────────────┘       └──────────────┘           │
│         │                                                                  │
│         └──────────────────────────────────────────────────────────────────│
│                                                                            │
└────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────┐
│                         CONVERSATION DOMAIN                                  │
│                                                                             │
│  ┌──────────────┐       ┌──────────────┐       ┌──────────────┐           │
│  │conversations │───────│   messages   │───────│  citations   │           │
│  │              │  1:N  │              │  1:N  │              │           │
│  │ • id         │       │ • id         │       │ • message_id │           │
│  │ • user_id    │       │ • conv_id    │       │ • source_uri │           │
│  │ • persona_key│       │ • role       │       │ • text_span  │           │
│  │ • created_at │       │ • text_ref   │       │ • confidence │           │
│  │ • metadata   │       │ • metrics    │       │ • license    │           │
│  └──────────────┘       └──────────────┘       └──────────────┘           │
│                                │                                           │
│                                │ 1:1                                       │
│                                ▼                                           │
│                         ┌──────────────┐                                   │
│                         │ msg_metrics  │                                   │
│                         │              │                                   │
│                         │ • latency_ms │                                   │
│                         │ • tokens_in  │                                   │
│                         │ • tokens_out │                                   │
│                         │ • rag_hit    │                                   │
│                         └──────────────┘                                   │
└─────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────┐
│                            MEDIA DOMAIN                                      │
│                                                                             │
│  ┌──────────────┐       ┌──────────────┐       ┌──────────────┐           │
│  │ media_assets │───────│  transcripts │───────│  audio_segs  │           │
│  │              │  1:1  │              │  1:N  │              │           │
│  │ • id         │       │ • asset_id   │       │ • trans_id   │           │
│  │ • uri        │       │ • text       │       │ • start_ms   │           │
│  │ • kind       │       │ • lang       │       │ • end_ms     │           │
│  │ • duration   │       │ • timings    │       │ • text       │           │
│  │ • hash       │       └──────────────┘       └──────────────┘           │
│  │ • drm_key_id │                                                          │
│  └──────────────┘                                                          │
└─────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────┐
│                          INGESTION DOMAIN                                    │
│                                                                             │
│  ┌──────────────┐       ┌──────────────┐       ┌──────────────┐           │
│  │ ingest_docs  │───────│   chunks     │───────│  embeddings  │           │
│  │              │  1:N  │              │  1:1  │              │           │
│  │ • id         │       │ • doc_id     │       │ • chunk_id   │           │
│  │ • uri        │       │ • text       │       │ • vector     │           │
│  │ • license    │       │ • position   │       │ • model      │           │
│  │ • source_typ │       │ • metadata   │       │ • dimension  │           │
│  │ • status     │       └──────────────┘       └──────────────┘           │
│  │ • kg_node_id │                                                          │
│  └──────────────┘                                                          │
└─────────────────────────────────────────────────────────────────────────────┘
```

## PostgreSQL Schema

### Users Table

```sql
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email_hash      VARCHAR(64) NOT NULL UNIQUE,  -- SHA-256 of email
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    consent_flags   JSONB NOT NULL DEFAULT '{}',
    status          VARCHAR(20) NOT NULL DEFAULT 'active',

    CONSTRAINT valid_status CHECK (status IN ('active', 'suspended', 'deleted'))
);

CREATE INDEX idx_users_email_hash ON users(email_hash);
CREATE INDEX idx_users_created_at ON users(created_at);
```

### Profiles Table

```sql
CREATE TABLE profiles (
    user_id         UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    display_name    VARCHAR(100),
    locale          VARCHAR(10) NOT NULL DEFAULT 'en-US',
    timezone        VARCHAR(50) NOT NULL DEFAULT 'UTC',
    avatar_url      TEXT,
    bio             TEXT,
    preferred_voices JSONB DEFAULT '[]',

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
```

### Personas Table

```sql
CREATE TABLE personas (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key             VARCHAR(50) NOT NULL UNIQUE,
    title           VARCHAR(100) NOT NULL,
    description     TEXT,
    tradition       VARCHAR(50),
    style_config    JSONB NOT NULL DEFAULT '{}',
    policy_tags     TEXT[] DEFAULT '{}',
    voice_id        VARCHAR(100),
    avatar_config   JSONB DEFAULT '{}',

    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_personas_key ON personas(key);
CREATE INDEX idx_personas_tradition ON personas(tradition);
```

### Content Table

```sql
CREATE TABLE content (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type            VARCHAR(30) NOT NULL,
    title           VARCHAR(500) NOT NULL,
    lang            VARCHAR(10) NOT NULL DEFAULT 'en',
    body_ref        TEXT,                          -- S3 URI or text
    metadata        JSONB NOT NULL DEFAULT '{}',
    status          VARCHAR(20) NOT NULL DEFAULT 'draft',
    version         INTEGER NOT NULL DEFAULT 1,
    parent_id       UUID REFERENCES content(id),   -- For versioning

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    published_at    TIMESTAMPTZ,

    CONSTRAINT valid_type CHECK (type IN (
        'lecture', 'meditation', 'article', 'course',
        'lesson', 'path', 'collection', 'daily_reflection'
    )),
    CONSTRAINT valid_status CHECK (status IN ('draft', 'review', 'live', 'archived'))
);

CREATE INDEX idx_content_type ON content(type);
CREATE INDEX idx_content_status ON content(status);
CREATE INDEX idx_content_lang ON content(lang);
CREATE INDEX idx_content_published ON content(published_at) WHERE published_at IS NOT NULL;
```

### Content Links (Citations) Table

```sql
CREATE TABLE content_links (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content_id      UUID NOT NULL REFERENCES content(id) ON DELETE CASCADE,
    source_uri      TEXT NOT NULL,
    license         VARCHAR(100),
    span            JSONB,                         -- {"start": 0, "end": 100}
    confidence      DECIMAL(3,2),                  -- 0.00 to 1.00
    attribution     TEXT,

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_content_links_content ON content_links(content_id);
```

### Conversations Table

```sql
CREATE TABLE conversations (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID REFERENCES users(id) ON DELETE SET NULL,
    persona_key     VARCHAR(50) NOT NULL REFERENCES personas(key),
    title           VARCHAR(200),
    metadata        JSONB NOT NULL DEFAULT '{}',

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_message_at TIMESTAMPTZ
);

CREATE INDEX idx_conversations_user ON conversations(user_id);
CREATE INDEX idx_conversations_persona ON conversations(persona_key);
CREATE INDEX idx_conversations_updated ON conversations(updated_at);
```

### Messages Table

```sql
CREATE TABLE messages (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    role            VARCHAR(20) NOT NULL,
    text_ref        TEXT,                          -- Message content or S3 URI
    citations       JSONB DEFAULT '[]',
    metrics         JSONB DEFAULT '{}',
    safety_flags    TEXT[] DEFAULT '{}',

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT valid_role CHECK (role IN ('user', 'assistant', 'system', 'tool'))
);

CREATE INDEX idx_messages_conversation ON messages(conversation_id);
CREATE INDEX idx_messages_created ON messages(conversation_id, created_at);
```

### Media Assets Table

```sql
CREATE TABLE media_assets (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    uri             TEXT NOT NULL,
    kind            VARCHAR(30) NOT NULL,
    lang            VARCHAR(10),
    duration_ms     INTEGER,
    file_size       BIGINT,
    hash            VARCHAR(64),                   -- SHA-256
    drm_key_id      VARCHAR(100),
    metadata        JSONB DEFAULT '{}',

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT valid_kind CHECK (kind IN (
        'audio', 'video', 'image', 'document', 'transcript', 'subtitle'
    ))
);

CREATE INDEX idx_media_kind ON media_assets(kind);
CREATE INDEX idx_media_created ON media_assets(created_at);
```

### Ingestion Documents Table

```sql
CREATE TABLE ingest_docs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    uri             TEXT NOT NULL UNIQUE,
    license         VARCHAR(100) NOT NULL,
    source_type     VARCHAR(50) NOT NULL,
    lang            VARCHAR(10),
    checksum        VARCHAR(64),
    status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    kg_node_id      VARCHAR(100),                  -- Neo4j node reference
    error_message   TEXT,

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    processed_at    TIMESTAMPTZ,

    CONSTRAINT valid_status CHECK (status IN (
        'pending', 'processing', 'completed', 'failed', 'archived'
    ))
);

CREATE INDEX idx_ingest_status ON ingest_docs(status);
CREATE INDEX idx_ingest_source ON ingest_docs(source_type);
```

### Events Table

```sql
CREATE TABLE events (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID REFERENCES users(id) ON DELETE SET NULL,
    type            VARCHAR(100) NOT NULL,
    payload         JSONB NOT NULL DEFAULT '{}',
    ts              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Partitioning key
    partition_key   DATE NOT NULL DEFAULT CURRENT_DATE
) PARTITION BY RANGE (partition_key);

CREATE INDEX idx_events_user ON events(user_id, ts);
CREATE INDEX idx_events_type ON events(type, ts);
```

## Vector Database Schema (Milvus)

### Embeddings Collection

```python
# Collection schema for semantic search
collection_schema = {
    "collection_name": "content_embeddings",
    "fields": [
        {"name": "id", "type": "VARCHAR", "max_length": 36, "is_primary": True},
        {"name": "doc_id", "type": "VARCHAR", "max_length": 36},
        {"name": "chunk_id", "type": "VARCHAR", "max_length": 36},
        {"name": "text", "type": "VARCHAR", "max_length": 65535},
        {"name": "lang", "type": "VARCHAR", "max_length": 10},
        {"name": "tradition", "type": "ARRAY", "element_type": "VARCHAR", "max_length": 50},
        {"name": "entities", "type": "ARRAY", "element_type": "VARCHAR", "max_length": 100},
        {"name": "year_range", "type": "ARRAY", "element_type": "INT32"},
        {"name": "license", "type": "VARCHAR", "max_length": 100},
        {"name": "embedding", "type": "FLOAT_VECTOR", "dim": 1536},
        {"name": "created_at", "type": "INT64"}
    ],
    "indexes": [
        {
            "field_name": "embedding",
            "index_type": "HNSW",
            "metric_type": "COSINE",
            "params": {"M": 16, "efConstruction": 256}
        }
    ]
}
```

### Persona Embeddings Collection

```python
persona_schema = {
    "collection_name": "persona_embeddings",
    "fields": [
        {"name": "id", "type": "VARCHAR", "max_length": 36, "is_primary": True},
        {"name": "persona_key", "type": "VARCHAR", "max_length": 50},
        {"name": "style_vector", "type": "FLOAT_VECTOR", "dim": 128},
        {"name": "teaching_style", "type": "VARCHAR", "max_length": 50},
        {"name": "tradition", "type": "VARCHAR", "max_length": 50}
    ]
}
```

## Knowledge Graph Schema (Neo4j)

### Node Types

```cypher
// Tradition Node
CREATE (t:Tradition {
    id: $id,
    name: $name,
    description: $description,
    origin_region: $region,
    origin_period: $period
})

// Figure Node (Teachers, Authors, Historical Figures)
CREATE (f:Figure {
    id: $id,
    name: $name,
    birth_year: $birth,
    death_year: $death,
    tradition: $tradition,
    biography: $bio
})

// Text Node (Primary Sources)
CREATE (tx:Text {
    id: $id,
    title: $title,
    lang: $lang,
    year: $year,
    license: $license
})

// Concept Node
CREATE (c:Concept {
    id: $id,
    name: $name,
    definition: $def,
    tradition: $tradition
})

// Practice Node
CREATE (p:Practice {
    id: $id,
    name: $name,
    description: $desc,
    type: $type,
    duration_range: $duration
})

// Place Node
CREATE (pl:Place {
    id: $id,
    name: $name,
    type: $type,
    coordinates: point({latitude: $lat, longitude: $lon})
})
```

### Relationship Types

```cypher
// Teaching relationships
(f1:Figure)-[:TAUGHT]->(f2:Figure)
(f:Figure)-[:WROTE]->(t:Text)
(f:Figure)-[:FOUNDED]->(tr:Tradition)
(f:Figure)-[:LIVED_IN]->(p:Place)

// Concept relationships
(c1:Concept)-[:ALIGNS_WITH]->(c2:Concept)
(c1:Concept)-[:CONTRADICTS]->(c2:Concept)
(c:Concept)-[:BELONGS_TO]->(t:Tradition)
(c:Concept)-[:MENTIONED_IN]->(tx:Text)

// Practice relationships
(p:Practice)-[:BELONGS_TO]->(t:Tradition)
(p:Practice)-[:EMBODIES]->(c:Concept)
(p:Practice)-[:TAUGHT_BY]->(f:Figure)

// Place relationships
(pl:Place)-[:ASSOCIATED_WITH]->(f:Figure)
(pl:Place)-[:SACRED_TO]->(t:Tradition)
```

### Example Queries

```cypher
// Find all concepts in Zen Buddhism
MATCH (c:Concept)-[:BELONGS_TO]->(t:Tradition {name: 'Zen Buddhism'})
RETURN c.name, c.definition

// Find lineage of teachers
MATCH path = (f1:Figure)-[:TAUGHT*1..5]->(f2:Figure)
WHERE f1.name = 'Bodhidharma'
RETURN path

// Find cross-tradition concept alignments
MATCH (c1:Concept)-[:ALIGNS_WITH]->(c2:Concept)
WHERE c1.tradition <> c2.tradition
RETURN c1.name, c1.tradition, c2.name, c2.tradition
```

## Event Schemas (Kafka)

### ConversationStarted Event

```json
{
  "event_type": "conversation.started",
  "version": "1.0",
  "timestamp": "2025-01-15T10:30:00Z",
  "correlation_id": "uuid",
  "payload": {
    "conversation_id": "uuid",
    "user_id": "uuid",
    "persona": "zen_master",
    "device_type": "mobile"
  }
}
```

### MessageCompleted Event

```json
{
  "event_type": "message.completed",
  "version": "1.0",
  "timestamp": "2025-01-15T10:30:05Z",
  "correlation_id": "uuid",
  "payload": {
    "conversation_id": "uuid",
    "message_id": "uuid",
    "latency_ms": 1500,
    "tokens_in": 50,
    "tokens_out": 200,
    "rag_hit_rate": 0.85,
    "citations_count": 3,
    "safety_flags": []
  }
}
```

### TTSCompleted Event

```json
{
  "event_type": "tts.completed",
  "version": "1.0",
  "timestamp": "2025-01-15T10:30:10Z",
  "correlation_id": "uuid",
  "payload": {
    "request_id": "uuid",
    "voice_id": "calm_female_1",
    "duration_ms": 15000,
    "first_byte_ms": 450,
    "format": "opus",
    "cached": false
  }
}
```

### IngestionJob Event

```json
{
  "event_type": "ingestion.completed",
  "version": "1.0",
  "timestamp": "2025-01-15T11:00:00Z",
  "correlation_id": "uuid",
  "payload": {
    "job_id": "uuid",
    "source_uri": "s3://bucket/document.pdf",
    "license": "CC-BY-4.0",
    "status": "completed",
    "docs_ingested": 1,
    "chunks_created": 45,
    "embeddings_generated": 45,
    "errors": []
  }
}
```

## Data Retention Policies

| Data Type        | Retention Period          | Archive Policy               |
| ---------------- | ------------------------- | ---------------------------- |
| User Accounts    | Until deletion request    | Soft delete, 30-day grace    |
| Chat Messages    | 2 years                   | Archive to cold storage      |
| Events/Analytics | 90 days hot, 2 years cold | Aggregate after 90 days      |
| Media Assets     | Indefinite                | Tier to Glacier after 1 year |
| Audit Logs       | 7 years                   | Immutable storage            |
| Session Data     | 30 days                   | Auto-expire                  |

## GDPR/Privacy Considerations

### PII Fields

| Table         | Field        | Classification | Notes                |
| ------------- | ------------ | -------------- | -------------------- |
| users         | email_hash   | Pseudonymous   | One-way hash, salted |
| profiles      | display_name | PII            | User-controlled      |
| events        | user_id      | Identifier     | Linkable to user     |
| conversations | user_id      | Identifier     | Can be nullified     |

### Data Subject Rights

- **Right to Access**: Export via `/v1/users/{id}/export`
- **Right to Erasure**: Cascade delete with 30-day retention
- **Right to Portability**: JSON/CSV export format
- **Right to Rectification**: Profile update endpoints

## Related Documentation

- **PostgreSQL Migrations**: `../services/*/migrations/`
- **API Reference**: [api/http-api.md](./api/http-api.md)
- **Privacy Policy**: `../docs/security/PRIVACY_CONSENT_VERSIONING.md`

---

_For database operational procedures, see [operations.md](./operations.md)_
