Database

LIS 3 stores all persistent state in a single PostgreSQL database (Neon). Three services — the gateway, the conversation engine, and the memory service — share this database through hand-written, per-service data-access layers. There is no ORM and no migration code in the service paths.

Overview

Each service ships its own db/ package: an engine.py that owns a module-global psycopg_pool.AsyncConnectionPool (opened at app startup, min_size=0), Pydantic row models validated from psycopg dict_row output, and per-table repository classes issuing raw parameterized SQL.

Five tables are used. Four live in the lis schema — sessions, conversation_history, conversation_attachments, and memory_facts. The fifth, avatars, lives in the ml schema (ml.avatars) and is read-only from the LIS services.

warning

There is no users table. User identity is a UUID taken from the X-User-ID header (HTTP) or the user_id query parameter (WebSocket) and is trusted as-is — it is never validated against a table. Every query scopes rows by this UUID. See the Gateway Service auth model for how identity is resolved.

Only the gateway registers no pgvector adapter. The memory service registers pgvector per connection (register_vector_async), so it reads embedding columns as native vectors; the gateway reads the same column back as a JSON string and parses it in a Pydantic field_validator.

Connection parameters come from a shared DBConfig (env vars PGHOST, PGDATABASE, PGUSER, PGPASSWORD, PGSSLMODE, PGCHANNELBINDING, DB_POOL_MAX). See Configuration for defaults.

Ownership summary

Each table has exactly one writer of record. Other services touch a table only in the limited ways listed below.

Table	Owner (writer)	Other accessors
ml.avatars	External / ML side	gateway_service — read-only, by uid
lis.sessions	gateway_service — sole writer	—
lis.conversation_history	conversation_engine — sole writer	gateway_service — read-only
lis.conversation_attachments	conversation_engine — sole reader + writer	—
lis.memory_facts	memory_service — full lifecycle	gateway_service — reads + soft-deletes

note

Only the gateway repositories wrap DB exceptions (via a pg_to_repo_errors decorator that maps psycopg errors to a RepositoryError family surfaced as HTTP 500). The conversation_engine and memory_service repositories have no such wrapper — DB exceptions propagate raw and are surfaced upstream as fatal DB_ERROR turns in the pipeline.

ml.avatars

Avatar definitions (persona, voice-clone assets, LoRA adapter). Written by the ML/back-office side; the gateway only reads a single row by its UUID uid.

Column	Type	Notes
uid	uuid	Primary key. Lookup key used by the gateway; its value is stored into lis.sessions.avatar_id.
avatar_id	text	Human-readable slug. Not the FK target; passed to the engine as the avatar_id field.
creator_id	text
name	text | null
system_prompt	text	Default "".
lora_adapter_path	text | null	Basis for the lora_name injected into the engine.
lora_quality_passed	bool	Default false.
collection_name	text | null	RAG collection for this avatar.
voice_clone_path	text | null	TTS reference audio.
voice_clone_text	text | null	TTS reference text.
description	text | null
tags	text[] | null
preview_url	text | null
elevenlabs_voice_id	text | null	Not injected into the engine session.start.
customization	jsonb	Default {}.
date	timestamptz
updated_at	timestamptz

Reads: gateway AvatarRepository.get_by_id, which runs SELECT * FROM ml.avatars WHERE uid = %s. The lookup key is uid, not avatar_id.

lis.sessions

A conversation session between a user and an avatar. Owned exclusively by the gateway.

Column	Type	Notes
id	uuid	Primary key.
user_id	uuid	Owner; trusted X-User-ID. Scopes every read and write.
avatar_id	uuid	References ml.avatars.uid (the avatar's uid, not its slug).
name	text | null	Mutable via PATCH.
description	text | null	Mutable via PATCH.
status	text	'active' (default) or 'deleted'.
created_at	timestamptz	DB default.
updated_at	timestamptz	DB default; not written by repository code.
deleted_at	timestamptz | null	Not written by repository code.

Reads / writes: gateway SessionRepository — create_session (INSERT (user_id, avatar_id) ... RETURNING *, storing avatar.uid in avatar_id), get_session / list_sessions (both filter status = 'active'), update (dynamic SET built from provided fields only), and soft_delete_session.

note

Soft-delete sets only status = 'deleted' — deleted_at is not written. All mutating queries include user_id in the WHERE clause, so a user can only change their own rows.

lis.conversation_history

One row per chat turn (user or assistant). Written only by the conversation engine; read by both the engine and the gateway.

Column	Type	Notes
id	serial int	Primary key.
session_id	uuid	The owning session.
role	text	'user' or 'assistant'.
content	text	Turn text. For image-only turns, this is the resolved default question.
created_at	timestamptz	DB default.

Writes: conversation_engine ConversationRepository.append_turn (INSERT (session_id, role, content) ... RETURNING *). This is the only writer; see the Conversation Pipeline for when persistence happens (blocked/unsafe turns are never persisted).

Reads: the engine loads recent turns via get_recent_turns (below); the gateway reads via MessageRepository.get_last_n_turns (ORDER BY created_at DESC, id DESC LIMIT n, then reversed to chronological order by SessionService). The gateway read does not join attachments.

lis.conversation_attachments

Metadata for image attachments on a message. Owned entirely by the conversation engine — no other service touches it. The image bytes live on the filesystem; only metadata is stored here.

Column	Type	Notes
id	serial int	Primary key.
message_id	int	References lis.conversation_history.id.
storage_key	text	Relative filesystem path (pattern below).
mime_type	text	image/jpeg, image/png, or image/webp.
filename	text
size_bytes	int	Decoded byte size.
description	text | null	VLM-generated; NULL until back-filled.
created_at	timestamptz	DB default.

Storage key

AttachmentStorage.save builds the storage_key and writes the file under ATTACHMENT_STORAGE_BASE_PATH:

users/{user_id}/sessions/{session_id}/{message_id}.{ext}

{ext} comes from a MIME map (image/jpeg and image/jpg → jpg, image/png → png, image/webp → webp), falling back to bin for an unknown MIME. When user_id is absent, session_id is used in its place.

Description back-fill

The description is written in two steps. AttachmentRepository.insert stores the row with description = NULL. After the VLM analyzes the image, update_description back-fills it. If analysis fails or is dropped, the column stays NULL.

Join into history

get_recent_turns reads history with attachments in one query: a LIMIT-bounded inner subquery over lis.conversation_history is LEFT JOIN-ed to lis.conversation_attachments:

FROM (
    SELECT * FROM lis.conversation_history
    WHERE session_id = %s
    ORDER BY created_at DESC, id DESC LIMIT %s
) m
LEFT JOIN lis.conversation_attachments a ON a.message_id = m.id
ORDER BY m.created_at DESC, m.id DESC, a.id

A helper (_group_messages) collapses the fan-out rows into one history entry per message, appending an attachment only when its id is non-NULL, and the result is reversed to chronological order. Each attachment description then surfaces in the prompt as an [Image: {description}] line beneath the turn text, giving the model context about past images without re-sending bytes. An optional since argument adds created_at >= %s to the inner query.

lis.memory_facts

Long-term user memory. Owned by the memory service, which handles the full lifecycle (similarity search, dedup, supersede). The gateway additionally reads and soft-deletes rows directly.

Column	Type	Notes
fact_id	uuid	Primary key (app-generated uuid4).
user_id	uuid	Fact owner.
avatar_id	uuid	Avatar the fact was learned with.
category	text
fact_key	text	Normalized snake_case key.
fact_value	text
confidence	float	Default 1.0.
status	text	'active', 'superseded', or 'deleted'.
source_session_id	uuid | null
source_excerpt	text | null
dedup_hash	text	SHA-256 of {avatar_id}:{fact_key}:{fact_value}.
embedding	vector | null	pgvector; used for cosine-similarity dedup.
created_at	timestamptz	DB default.
updated_at	timestamptz	DB default.
last_seen_at	timestamptz	Stamped on injection.
deleted_at	timestamptz | null	Not written by any service.

Writes (memory service): MemoryRepository inserts fresh facts (_insert sets everything except the three timestamps, which are DB-defaulted and returned via RETURNING), stamps last_seen_at on injected facts (touch_last_seen), and supersedes near-duplicates in a single transaction (supersede_fact marks the old row status = 'superseded', then inserts the merged replacement). Similarity search uses the pgvector cosine operator embedding <=> %s::vector with a distance threshold. See the Memory Service for the full extraction and dedup flow.

Reads / writes (gateway): MemoryRepository.get_facts (SELECT * filtered by user_id, optional avatar_id, and status = ANY(%s), default ['active'], ORDER BY last_seen_at DESC) and soft_delete_fact (SET status = 'deleted' WHERE ... AND status <> 'deleted'). The gateway soft-delete sets only status; it does not write deleted_at.

note

The gateway currently reads and soft-deletes lis.memory_facts directly, bypassing the owning service. The intended direction is for these gateway operations to migrate to the Memory Service API, leaving the memory service as the sole accessor of the table.