Data Model Reference

Complete reference for all database tables, fields, and relationships.

Entity Relationship

┌──────────────┐
│ Organization │
└──────┬───────┘
       │ 1:many
       ├──────────────────┐
       │                  │
┌──────▼───────┐   ┌──────▼──────┐
│   API Key    │   │ Conversation │
└──────────────┘   └──────┬──────┘
                          │ 1:many
                    ┌─────┴─────┐
                    │           │
             ┌──────▼──┐  ┌────▼────────┐
             │ Message  │  │    Chunk    │
             └─────────┘  └─────────────┘

Organizations

The top-level tenant. All data is scoped to an organization.

Example:

{
  "id": "org_V1StGXR8_Z5jdHi6B-myT",
  "name": "Acme Corp",
  "created_at": "2026-03-23T10:00:00Z",
  "updated_at": "2026-03-23T10:00:00Z"
}

API Keys

Authentication credentials tied to an organization.

Raw key format: engram_sk_live_ + 32 random characters (47 chars total)

The raw key is shown once at creation and never stored. Only the SHA-256 hash is persisted.

Indexes:

• idx_api_keys_hash on key_hash — fast lookup during authentication
• idx_api_keys_org on organization_id — list keys per org

Example:

{
  "id": "key_K2RtHYS9_A6keFj7C-nzU",
  "organization_id": "org_V1StGXR8_Z5jdHi6B-myT",
  "key_hash": "a1b2c3d4e5f6...64 hex chars",
  "key_prefix": "engram_sk_live_aBcD",
  "name": "Production key",
  "expires_at": null,
  "revoked_at": null,
  "last_used_at": "2026-03-23T15:42:00Z",
  "created_at": "2026-03-23T10:00:00Z"
}

Conversations

A container for an ordered sequence of messages.

Indexes:

• idx_conversations_org on organization_id — list conversations per org
• idx_conversations_agent on agent_id — filter by agent
• idx_conversations_updated on updated_at — sort by recency

Notes:

• tags is stored as a JSON string (e.g., '["support","billing"]') and parsed to string[] in application code
• metadata is stored as a JSON string and parsed to Record<string, unknown> in application code
• message_count is incremented atomically on each append_messages call

Example:

{
  "id": "conv_V1StGXR8_Z5jdHi6B-myT",
  "organization_id": "org_V1StGXR8_Z5jdHi6B-myT",
  "title": "Debugging the auth flow",
  "agent_id": "support-bot-v2",
  "tags": ["engineering", "auth"],
  "metadata": { "sprint": "2026-Q1-W12" },
  "message_count": 24,
  "created_at": "2026-03-23T10:00:00Z",
  "updated_at": "2026-03-23T10:45:00Z"
}

Messages

Individual messages within a conversation, stored verbatim.

Indexes:

• idx_messages_conv_seq on (conversation_id, sequence) — ordered retrieval
• idx_messages_org on organization_id — tenant-scoped queries

Key design decisions:

• organization_id is denormalized — it’s redundant with the conversation’s org, but avoids JOINs for tenant-scoped queries
• sequence is an integer, not a timestamp — deterministic ordering regardless of insertion time
• content is stored as-is — no truncation, no summarization, no HTML encoding
• Cascade delete: when a conversation is deleted, all its messages are automatically removed

Example:

{
  "id": "msg_K2RtHYS9_A6keFj7C-nzU",
  "conversation_id": "conv_V1StGXR8_Z5jdHi6B-myT",
  "organization_id": "org_V1StGXR8_Z5jdHi6B-myT",
  "role": "tool",
  "content": "{\"invoices\": [{\"id\": \"inv_123\", \"amount\": 99.00}]}",
  "tool_call_id": "call_abc",
  "tool_name": "lookup_billing",
  "sequence": 3,
  "metadata": {},
  "created_at": "2026-03-23T10:00:03Z"
}

Conversation Chunks

Sliding-window text fragments of messages, used for embedding and vector search.

Indexes:

• idx_chunks_conv on conversation_id — list chunks per conversation
• idx_chunks_org on organization_id — tenant-scoped queries
• idx_chunks_vectorize on vectorize_id — lookup by vector ID after search

Chunk text format:

[user]: Can you check the logs?
[assistant]: Sure, looking now.
[tool]: {"errors": [{"level": "ERROR"}]}
[assistant]: I see an error in the logs.
[user]: When did it start?

Chunking algorithm:

• Window size: 5 messages
• Stride: 3 messages
• Overlap: 2 messages between consecutive chunks
• Messages sorted by sequence before chunking

Example:

{
  "id": "chk_M3SuIZT0_B7lfGk8D-ozV",
  "conversation_id": "conv_V1StGXR8_Z5jdHi6B-myT",
  "organization_id": "org_V1StGXR8_Z5jdHi6B-myT",
  "chunk_text": "[user]: Can you check...\n[assistant]: Sure...",
  "start_sequence": 1,
  "end_sequence": 5,
  "vectorize_id": "chk_M3SuIZT0_B7lfGk8D-ozV",
  "created_at": "2026-03-23T10:00:00Z"
}

ID Format

All IDs use prefixed nanoids for human readability and type safety.

IDs are generated using nanoid with a default size of 21 characters (plus the prefix). They are URL-safe and contain characters from A-Za-z0-9_-.

Vector Metadata

Each vector stored in Vectorize includes metadata for filtering: