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

```sql
-- 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
```