Shesha

Shesha — The Endless Serpent (Chat & AI Data)

Shesha holds every world that ever was without dropping one. Chat history is the product's memory: append-only, accounted, retrievable in the right order, and deletable when its owner asks.

Core schema

• Two tables carry the product:
  • conversations: id uuid pk default gen_random_uuid(), user_id text not null (the Supabase auth.uid() — see the boundary rules below), title, model text not null, system_prompt_version text, created_at/updated_at timestamptz not null default now(), deleted_at timestamptz.
  • messages: id uuid pk, conversation_id uuid not null references conversations on delete cascade, role text not null check (role in ('user','assistant','system','tool')), content text not null, status text not null default 'complete' check (status in ('complete','interrupted','failed')), model text, prompt_tokens int, completion_tokens int, created_at timestamptz not null default now().
• timestamptz always, UTC always. Bare timestamp is a bug you'll meet at your first DST boundary (see varuna).
• IDs are server-generated UUIDs; the optimistic frontend reconciles on ack (see maya), it never invents the canonical ID.

Append-only history

• Messages are immutable: no UPDATE on content, ever. Edits create a new message referencing parent_message_id; regenerations create siblings. The transcript is an audit log of what the user and model actually said (see chitragupta).
• Deletion is soft (deleted_at) in the product flow; hard deletion exists only in the privacy-erasure path below.
• Interrupted generations are persisted with status='interrupted' and whatever content streamed — silently vanishing output breaks user trust and your debugging (see vayu).

Indexes & query patterns

• The one index that matters: (conversation_id, created_at) on messages — every transcript read walks it. (user_id, updated_at desc) on conversations for the sidebar list.
• Keyset pagination only (where created_at < $last order by created_at desc limit 50); OFFSET over a long history re-reads everything it skips (see varuna).
• Context-window assembly is a query, not a full load: last N messages by recency plus the system prompt, with a token budget enforced in code — never select an entire year of history to build one prompt (see lakshmi).
• Every query in the API path is written to be explainable: if you can't say which index serves it, run EXPLAIN ANALYZE before merging.

Embeddings — pgvector

• One embeddings table per embedded thing, not vectors inline on messages: id, message_id references messages on delete cascade, embedding vector(1536), model text not null, created_at.
• The vector dimension is pinned to the embedding model; the model column exists because you will change models — mixed-model vectors in one similarity search return confident nonsense.
• Re-embedding is a versioned backfill job (see hanuman): new column/table, embed, verify, cut over — never in-place overwrite while search is live.
• Index with HNSW (using hnsw (embedding vector_cosine_ops)) once rows exceed a few thousand; measure recall before and after — an ANN index is a quality tradeoff, not free speed.
• Filter by owner in SQL before similarity ordering: retrieval that searches all users' vectors and filters after is a data leak dressed as a ranking bug (see muruka).

Token & cost accounting

• Every assistant message stores prompt_tokens, completion_tokens, and model from the provider's usage block — per-user and per-day cost is then one GROUP BY, not an archaeology project (see lakshmi).
• Store the system prompt version, not a guess: when quality shifts, the first question is "what changed in the prompt" (see durga).

Retention, privacy, erasure

• Chat content is user PII: it never appears in logs (log message IDs and token counts, not content — see chitragupta), and it stays out of analytics events.
• One erasure path, tested: given a user_id, hard-delete their conversations (cascade covers messages and embeddings) within your stated SLA. Run it against staging on a schedule so it can't rot.
• Write the retention policy down (e.g. soft-deleted rows purged after 30 days by a cron job — see airavata) and enforce it with a job, not intentions.

The boundary, restated

• user_id here is the Supabase auth.uid() string — same identifier, different database, no foreign key across stores and no copying of profile data into Render Postgres (see yama). Display names join client-side.
• Only FastAPI reads or writes this database (see vayu); if the frontend needs it, that's an endpoint, not a connection string.

Backups

• Render Postgres point-in-time recovery verified on the current plan; a quarterly restore drill into a scratch instance proves the backup is real (see matsya). Chat history is the one dataset users will not forgive losing.

Before merging data work — checklist

• timestamptz, server-side UUIDs, checked enums on role/status
• Messages append-only; edits/regens as new rows; interruptions persisted
• (conversation_id, created_at) index present; keyset pagination only
• Embeddings dimension + model pinned; owner filter inside the similarity query
• Usage tokens + model + prompt version stored per generation
• Erasure path exists and is exercised; retention job scheduled; restore drill done