Skip to content

Storage

Engine

SQLAlchemy Core over a URL you choose:

Environment VENKAI_DATABASE_URL
Development unset → local SQLite file (zero setup)
Production postgresql://user:pass@host/db

SQLite gets check_same_thread=False and pragma tuning at connection time. Nothing in the schema is Postgres-specific, so the two are interchangeable — which is why the test suite can run against SQLite and mean something.

Entities

erDiagram
  ORGANIZATION ||--o{ USER : has
  ORGANIZATION ||--o{ API_KEY : has
  ORGANIZATION ||--o{ PROJECT : owns
  PROJECT ||--o{ CONTEXT : contains
  CONTEXT ||--o{ VERSION : "has history"

  ORGANIZATION { string id PK  string name }
  PROJECT { string id PK  string org_id FK  string project_key  string name  string status }
  CONTEXT {
    string id PK
    string org_id FK
    string project_id FK
    string agent_key
    string type
    text content
    float confidence
    float importance
    int access_count
    float last_accessed_at
    float created_at
    float updated_at
    text metadata_json
    text embedding_json
  }
  VERSION { string id PK  string context_id FK  int version  text content }

The contexts table

Column Type Notes
id string ctx_ + 12 hex
org_id string Denormalised onto the row — this is what the second org check reads
project_id string Internal proj_… id, never exposed
agent_key string The agent_id from the write
type string One of six, validated at the API boundary
content text 1–8000 chars
confidence float Stored, unused in ranking
importance float 25 % of the retrieval score
access_count int Not incremented by retrieval
last_accessed_at float Nullable
created_at / updated_at float Epoch seconds
metadata_json text JSON
embedding_json text JSON float array

org_id being on the context row rather than reached through the project join is deliberate: it makes the per-candidate ownership check a field comparison instead of a second query.

Embeddings are JSON text

"[0.0, 0.0, 0.30151134457776363, -0.30151134457776363, ...]"

Not pgvector, not BYTEA, not a native array. Cosine similarity is computed in Python over the loaded candidate set.

This is a stated, reversible choice. The docstring in the retriever explains the exit: the column is a 1:1 match for a pgvector Vector column with identical cosine semantics, so migrating is a type change plus a backfill — not a redesign. pgvector was explicitly removed from the dependency set because nothing imported it and no migration declared CREATE EXTENSION vector; carrying an unused dependency that suggests an index exists is worse than not having it.

What it costs

hashing (128-d) semantic (384-d)
Per memory ~2.5 KB of JSON text ~7.5 KB
10 000 memories ~25 MB ~75 MB
100 000 memories ~250 MB ~750 MB

Several times a packed binary vector. Fine at current scale, and a real number to plan against beyond it.

Project identity

flowchart LR
  K["project_key<br/>'billing-service'<br/><i>client-chosen</i>"] -->|"resolved per request"| I["id<br/>'proj_878b0d7ab961'<br/><i>internal</i>"]
  I --> R["context rows"]
  R -->|"substituted on every response"| K

Two resolution modes:

Path Behaviour on unknown key
Write (POST /api/memory) Creates the project
Read (GET /api/context/…) 404

The internal id never leaves the API — every response substitutes your key back. A client therefore cannot couple to it, and the internal id stays free to change.

The asymmetry is also the source of the most common integration bug: a typo on the write path succeeds silently.

Isolation

flowchart TB
  R["request"] --> A["auth → org_id"]
  A --> Q["query WHERE project org-keyed"]
  Q --> C["candidate rows"]
  C --> V{"row.org_id == caller.org_id?"}
  V -->|no| D["discarded"]
  V -->|yes| K["scored"]

Two independent barriers. The second is redundant with the first by design — a stale project id, a caching mistake, or one missed WHERE clause cannot leak another tenant's data through it.

Migrations

Alembic, configured in venkai/alembic.ini.

cd venkai
alembic upgrade head

Run before starting a new version. There is no automatic migration on startup — a deployment that silently migrates is a deployment that can silently migrate the wrong way.

Operational reality
Backups Not provided. Your database, your policy.
Retention / TTL None. Memories persist until edited or the org is deleted.
Per-memory delete Does not exist. PATCH to supersede.
Bulk export GET /api/export
Bulk delete DELETE /api/organization — irreversible
Docker volume venkai-data → /data