Data Model Reference

Database schema, table structures, and JSONB contracts.

Schema Isolation

Epitome uses per-user PostgreSQL schemas for data isolation. When a new user signs up, the system clones the template_user schema into a new schema named user_<user_id>. Every query for that user runs within a transaction that sets the search path to their schema.

sql

-- How per-user schema isolation works in each transaction:
BEGIN;
SET LOCAL search_path = 'user_abc123', public;

-- All queries now resolve to tables in the user's schema
SELECT * FROM profile;           -- reads user_abc123.profile
SELECT * FROM vector_entries;    -- reads user_abc123.vector_entries

COMMIT;

There are two categories of schemas in the database:

Schema	Purpose	Tables
shared	Cross-user data	users, accounts, sessions, api_keys
template_user	Blueprint for new users	All per-user tables (cloned on signup)
user_*	One per user	profile, user_tables, table_records, vector_collections, vector_entries, entities, entity_edges, entity_mentions, activity_log, memory_review, agent_consent

Core Tables

These tables store the user's profile and custom structured data.

profile

The profile is a versioned JSONB document. Every update creates a new row with an incremented version number. The latest version is the current profile.

sql

CREATE TABLE profile (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  version     INTEGER NOT NULL DEFAULT 1,
  data        JSONB NOT NULL DEFAULT '{}',
  confidence  REAL NOT NULL DEFAULT 0.5,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by  TEXT  -- agent_id or 'user' for dashboard edits
);

CREATE INDEX idx_profile_version ON profile (version DESC);

user_tables

Metadata registry for user-defined tables. Each entry describes a logical table and its inferred schema.

sql

CREATE TABLE user_tables (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL UNIQUE,
  schema      JSONB NOT NULL DEFAULT '{}',  -- inferred column types
  record_count INTEGER NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

table_records

All user-table records are stored in a single table with the table name as a foreign key. Record data is stored as JSONB.

sql

CREATE TABLE table_records (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name  TEXT NOT NULL REFERENCES user_tables(name) ON DELETE CASCADE,
  data        JSONB NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_table_records_table ON table_records (table_name);
CREATE INDEX idx_table_records_data ON table_records USING GIN (data);

Vector Tables

The vector system stores semantic memories as text content paired with 1536-dimensional embeddings generated by text-embedding-3-small.

vector_collections

sql

CREATE TABLE vector_collections (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL UNIQUE,
  description TEXT,
  entry_count INTEGER NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

vector_entries

The main vector storage table. Each entry has content, an embedding vector, optional metadata, and a confidence score managed by the memory quality engine.

sql

CREATE TABLE vector_entries (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  collection   TEXT NOT NULL REFERENCES vector_collections(name) ON DELETE CASCADE,
  content      TEXT NOT NULL,
  embedding    vector(1536) NOT NULL,
  metadata     JSONB DEFAULT '{}',
  confidence   REAL NOT NULL DEFAULT 0.8,
  state        TEXT NOT NULL DEFAULT 'active'
                 CHECK (state IN ('active', 'flagged', 'archived', 'contradicted')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by   TEXT  -- agent_id
);

-- HNSW index for fast cosine similarity search
CREATE INDEX idx_vector_entries_embedding
  ON vector_entries USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

CREATE INDEX idx_vector_entries_collection ON vector_entries (collection);
CREATE INDEX idx_vector_entries_state ON vector_entries (state);

Graph Tables

The knowledge graph uses three tables: entities (nodes), entity_edges (relationships), and entity_mentions (links back to source memories).

entities

sql

CREATE TABLE entities (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT NOT NULL,
  type          TEXT NOT NULL
                  CHECK (type IN ('person', 'place', 'organization', 'concept', 'event')),
  properties    JSONB DEFAULT '{}',
  mention_count INTEGER NOT NULL DEFAULT 0,
  confidence    REAL NOT NULL DEFAULT 0.7,
  first_seen    TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen     TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_entities_type ON entities (type);
CREATE INDEX idx_entities_name_trgm ON entities USING GIN (name gin_trgm_ops);

entity_edges

sql

CREATE TABLE entity_edges (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source_id     UUID NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
  target_id     UUID NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
  type          TEXT NOT NULL,
  properties    JSONB DEFAULT '{}',
  weight        REAL NOT NULL DEFAULT 1.0,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (source_id, target_id, type)
);

CREATE INDEX idx_entity_edges_source ON entity_edges (source_id);
CREATE INDEX idx_entity_edges_target ON entity_edges (target_id);

entity_mentions

Links entities to the vector entries (memories) where they were mentioned. This allows tracing an entity back to its source data.

sql

CREATE TABLE entity_mentions (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_id     UUID NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
  vector_id     UUID NOT NULL REFERENCES vector_entries(id) ON DELETE CASCADE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (entity_id, vector_id)
);

CREATE INDEX idx_entity_mentions_entity ON entity_mentions (entity_id);
CREATE INDEX idx_entity_mentions_vector ON entity_mentions (vector_id);

System Tables

System tables handle audit logging, memory quality review, and agent consent management.

activity_log

sql

CREATE TABLE activity_log (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_id    TEXT,
  action      TEXT NOT NULL,
  resource    TEXT,
  details     JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_activity_log_created ON activity_log (created_at DESC);
CREATE INDEX idx_activity_log_agent ON activity_log (agent_id);
CREATE INDEX idx_activity_log_action ON activity_log (action);

memory_review

Items flagged by the memory quality engine for human review.

sql

CREATE TABLE memory_review (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type             TEXT NOT NULL CHECK (type IN ('contradiction', 'stale', 'low_confidence')),
  entry_ids        UUID[] NOT NULL,
  description      TEXT NOT NULL,
  suggested_action TEXT,
  status           TEXT NOT NULL DEFAULT 'pending'
                     CHECK (status IN ('pending', 'resolved', 'dismissed')),
  resolved_at      TIMESTAMPTZ,
  resolution_note  TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_memory_review_status ON memory_review (status);

agent_consent

Per-resource consent grants for each agent. Controls which resources an agent can read/write.

sql

CREATE TABLE agent_consent (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_id    TEXT NOT NULL,
  resource    TEXT NOT NULL,
  permission  TEXT NOT NULL CHECK (permission IN ('read', 'write', 'read_write')),
  granted_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at  TIMESTAMPTZ,
  UNIQUE (agent_id, resource)
);

CREATE INDEX idx_agent_consent_agent ON agent_consent (agent_id);

JSONB Contracts

Several tables use JSONB columns for flexible data. Here are the expected shapes for the most important JSONB documents.

Profile Data Schema

The profile.data column stores the user's personal information. All fields are optional. The schema evolves as agents add new data.

typescript

interface ProfileData {
  name?: string;
  timezone?: string;
  location?: {
    city?: string;
    state?: string;
    country?: string;
  };
  preferences?: {
    food?: {
      favorites?: string[];
      dietary_restrictions?: string[];
      regional_style?: string;
    };
    communication?: {
      style?: string;
      languages?: string[];
    };
  };
  family?: Array<{
    name?: string;
    relation?: string;
    birthday?: string;
  }>;
  career?: {
    primary_job?: {
      title?: string;
      company?: string;
      industry?: string;
    };
    skills?: string[];
  };
  health?: {
    conditions?: string[];
    dietary_goals?: string[];
  };
  [key: string]: unknown;  // open-ended for new fields
}

Entity Properties

The entities.properties JSONB column stores type-specific metadata about each entity.

typescript

// Person entity
{ relation?: string; age?: number; birthday?: string; occupation?: string }

// Place entity
{ address?: string; city?: string; country?: string; type?: string }

// Organization entity
{ industry?: string; website?: string; role?: string }

// Concept entity
{ category?: string; description?: string }

// Event entity
{ date?: string; location?: string; recurring?: boolean }

Edge Metadata

The entity_edges.properties column carries relationship-specific data.

typescript

// Common edge properties
{
  since?: string;       // when the relationship started
  until?: string;       // when it ended (if applicable)
  context?: string;     // additional context about the relationship
  source_memory?: string; // vector_entry ID where this was extracted
}

Vector Entry Metadata

The vector_entries.metadata column tracks provenance and context for each stored memory.

typescript

interface VectorMetadata {
  source?: string;          // agent name or "dashboard"
  conversation_id?: string; // originating conversation
  tags?: string[];          // user-defined tags
  extracted_entities?: string[]; // entity names found in this memory
  [key: string]: unknown;
}

Architecture Overview Security Model