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;
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
- Privacy Policy:
../docs/security/PRIVACY_CONSENT_VERSIONING.md
For database operational procedures, see operations.md