# Shesha

> Chat and AI data standards for Render Postgres — conversation/message schema, append-only history, pgvector embeddings, token accounting, retention, and context-window queries. Use when designing or querying chat history, messages, conversations, embeddings, pgvector, or any AI-generated data storage.

- Skill: `arjuncrevathi/shesha` (Agent Skill)
- Install (CLI): `npx skillmds@latest add arjuncrevathi/shesha`
- Raw SKILL.md: https://api.skillmd.com/api/skills/arjuncrevathi/shesha/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Finance & Business
- Author: arjuncrevathi (https://skillmd.com/u/arjuncrevathi)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/arjuncrevathi/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