Eve library

Lilith Data Model

Guide

Revision 1

On this page

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#

text
┌─────────────────────────────────────────────────────────────────────────────┐
│                              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;
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
  • PostgreSQL Migrations: ../services/*/migrations/
  • API Reference: api/http-api.md
  • Privacy Policy: ../docs/security/PRIVACY_CONSENT_VERSIONING.md

For database operational procedures, see operations.md