docs/database.md

Database Architecture & Vector Storage

Overview

The platform supports two vector storage modes:

• Hosted Mode: PostgreSQL + pgvector extension for scalable multi-tenant cloud deployments.
• Standalone / Offline Mode: Lightweight SQLite (objects.db) with stored L2 normalized embedding vectors and in-memory cosine similarity search.

---

Schema Overview

-- Objects Table
CREATE TABLE objects (
    id TEXT PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    category TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'active',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

-- Reference Images Table
CREATE TABLE object_images (
    id TEXT PRIMARY KEY,
    object_id TEXT NOT NULL,
    file_path TEXT NOT NULL,
    image_hash TEXT,
    created_at TEXT NOT NULL,
    FOREIGN KEY (object_id) REFERENCES objects(id) ON DELETE CASCADE
);

-- Object Embeddings Table
CREATE TABLE object_embeddings (
    id TEXT PRIMARY KEY,
    object_id TEXT NOT NULL,
    model_name TEXT NOT NULL,
    model_version TEXT NOT NULL,
    embedding TEXT NOT NULL, -- JSON Array [f32 x 512]
    created_at TEXT NOT NULL,
    FOREIGN KEY (object_id) REFERENCES objects(id) ON DELETE CASCADE
);

---

Database Backup & Export

Users can export their custom taught object library as a ZIP archive via the web UI or API endpoint:

my-object-library.zip
├── database.db
└── manifest.json