# Smartbotic Database Integration Guide

How to install, configure, and integrate smartbotic-database into your C++ project.

## Table of Contents

- [Installing the Database](#installing-the-database)
- [Installing the Client Library](#installing-the-client-library)
- [Integrating into a C++ Project](#integrating-into-a-c-project)
- [Client API Reference](#client-api-reference)
- [Configuration](#configuration)
- [Migrations](#migrations)
- [Docker Build Integration](#docker-build-integration)
- [CLI Administration](#cli-administration)
- [Upgrading from Legacy Packages](#upgrading-from-legacy-packages)

---

## Installing the Database

### From APT Repository

Add the Smartbotics repository to your system:

```bash
# Download GPG key
curl -fsSL https://repository.smartbotics.ai/smartbotics-repo.gpg | \
    sudo gpg --dearmor -o /usr/share/keyrings/smartbotics-repo.gpg

# Configure authentication
sudo tee /etc/apt/auth.conf.d/smartbotics.conf << EOF
machine repository.smartbotics.ai
login callerai
password <repo-password>
EOF
sudo chmod 600 /etc/apt/auth.conf.d/smartbotics.conf

# Add repository
echo "deb [signed-by=/usr/share/keyrings/smartbotics-repo.gpg] https://repository.smartbotics.ai trixie main" | \
    sudo tee /etc/apt/sources.list.d/smartbotics.list

# Install
sudo apt update
sudo apt install smartbotic-database
```

This installs:
- `/usr/bin/smartbotic-database` — the server binary
- `/etc/smartbotic-database/config.json` — configuration file
- `smartbotic-database.service` — systemd service (starts automatically)

The service listens on `localhost:9004` by default.

### Verify Installation

```bash
# Check service status
sudo systemctl status smartbotic-database

# Check health via CLI (if installed)
smartbotic-db-cli health
```

### Available Packages

| Package | What it provides | Install when... |
|---------|-----------------|-----------------|
| `smartbotic-database` | Server + systemd service | You need to run the database on this machine |
| `libsmartbotic-db-client` | Shared library (`.so`) | Your application links the client at runtime |
| `libsmartbotic-db-client-dev` | Headers + cmake config | You're building a C++ project against the client API |
| `smartbotic-db-cli` | CLI admin tool | You want to administer the database from the command line |

---

## Installing the Client Library

### On a Development Machine (for local builds)

```bash
sudo apt install libsmartbotic-db-client-dev
```

This pulls in `libsmartbotic-db-client` (the runtime `.so`) as a dependency, plus all development headers needed to compile.

### On a Production Machine (runtime only)

```bash
sudo apt install libsmartbotic-db-client
```

Only the shared library — no headers, no cmake config, no dev tooling.

---

## Integrating into a C++ Project

### Option A: System-installed package (recommended for Docker builds)

If `libsmartbotic-db-client-dev` is installed (e.g., in your Docker build image):

```cmake
# CMakeLists.txt
find_package(smartbotic-db-client REQUIRED)

add_executable(myapp main.cpp)
target_link_libraries(myapp PRIVATE smartbotic::db-client)
```

The `smartbotic::db-client` target automatically sets up include paths and links the shared library.

### Option B: Git submodule (for local development on non-Debian systems)

```bash
git submodule add ssh://git@git.smartbotics.ai:10022/fszontagh/smartbotic-database.git \
    external/smartbotic-database
```

```cmake
# CMakeLists.txt
add_subdirectory(external/smartbotic-database)

add_executable(myapp main.cpp)
target_link_libraries(myapp PRIVATE smartbotic-db-client)
```

When built as a submodule, the library is static (`.a`) and linked directly into your binary.

### Option C: Both (recommended)

Use the system package when available, fall back to submodule:

```cmake
# Try system-installed first (Docker builds, CI)
find_package(smartbotic-db-client QUIET)
if(smartbotic-db-client_FOUND)
    message(STATUS "Using system smartbotic-db-client")
    set(DB_CLIENT_TARGET smartbotic::db-client)
else()
    # Fall back to submodule (local development)
    if(EXISTS "${CMAKE_CURRENT_SOURCE_DIR}/external/smartbotic-database/CMakeLists.txt")
        add_subdirectory(external/smartbotic-database)
        set(DB_CLIENT_TARGET smartbotic-db-client)
    else()
        message(FATAL_ERROR
            "smartbotic-database not found. Either:\n"
            "  apt install libsmartbotic-db-client-dev\n"
            "  git submodule update --init external/smartbotic-database")
    endif()
endif()

add_executable(myapp main.cpp)
target_link_libraries(myapp PRIVATE ${DB_CLIENT_TARGET})
```

---

## Client API Reference

### Connecting

```cpp
#include <smartbotic/database/client.hpp>

smartbotic::database::Client::Config config;
config.address = "localhost:9004";   // gRPC endpoint
config.timeoutMs = 5000;            // per-RPC timeout
config.maxRetries = 3;              // auto-retry on transient failures

smartbotic::database::Client db(config);
if (!db.connect()) {
    // handle connection failure
}
```

### Document Operations

```cpp
// Insert
nlohmann::json doc = {{"name", "Alice"}, {"email", "alice@example.com"}};
std::string id = db.insert("users", doc);
// Or with explicit ID:
std::string id = db.insert("users", doc, "user-123");

// Get
auto result = db.get("users", "user-123");
if (result) {
    std::cout << (*result)["name"] << std::endl;
}

// Update (full document replacement, with automatic optimistic locking)
db.update("users", "user-123", {{"name", "Alice Smith"}, {"email", "alice@example.com"}});

// Update with explicit optimistic locking (fails if version changed)
db.updateIfVersion("users", "user-123", {{"name", "Alice"}}, /*expectedVersion=*/2);

// Patch — atomic partial update (only specified fields are modified)
uint64_t newVer = db.patch("users", "user-123", {{"name", "Alice Smith"}});

// Upsert (insert or update)
auto [uid, isNew] = db.upsert("users", doc, "user-123");

// Delete
db.remove("users", "user-123");

// Check existence
bool found = db.exists("users", "user-123");
```

### Concurrency: update() vs patch()

When multiple clients modify the same document concurrently, choose the right method:

**`patch()`** — use for most updates (recommended). Merges only the specified fields on the server, atomically. Two clients can safely modify different fields simultaneously:

```cpp
// Client A:                                    // Client B:
db.patch("users", "user-123",                   db.patch("users", "user-123",
    {{"name", "Alice Smith"}});                      {{"email", "alice@new.com"}});
// Result: both fields updated, nothing lost
```

**`update()`** — full document replacement with automatic retry. Uses optimistic locking internally (reads current version, writes with version check, retries on conflict). Use when you need to replace the entire document:

```cpp
auto doc = db.get("users", "user-123");
(*doc)["name"] = "Alice Smith";
(*doc)["role"] = "admin";
db.update("users", "user-123", *doc);  // replaces entire document
```

Note: `update()` is "last writer wins" for complete replacement — if two clients modify different fields via `update()`, the last one overwrites the other's changes. Use `patch()` to avoid this.

**`updateIfVersion()`** — explicit optimistic locking. Use when you need to detect conflicts and handle them yourself:

```cpp
auto doc = db.get("users", "user-123");
uint64_t version = (*doc)["_version"];
(*doc)["balance"] = (*doc)["balance"].get<int>() + 100;
if (!db.updateIfVersion("users", "user-123", *doc, version)) {
    // Version conflict — someone else modified the document
    // Re-read and retry, or report error to user
}
```

### Querying

```cpp
// Find with filters (equality is default)
smartbotic::database::Client::QueryOptions opts;
opts.filters = {
    {"status", "active"},   // EQ (default) — 2-arg Filter constructor
    {"role", "admin"},      // EQ
};
opts.sortField = "name";
opts.sortDescending = false;
opts.limit = 50;
opts.offset = 0;

auto docs = db.find("users", opts);
for (const auto& doc : docs) {
    std::cout << doc["name"] << std::endl;
}

// Find all documents in a collection
auto allDocs = db.find("users");

// Count
uint64_t total = db.count("users");
uint64_t admins = db.count("users", {{"role", "admin"}});
```

#### Filter operators

Filters support a range of operators via `Client::FilterOp`. The 2-argument
`Filter(field, value)` constructor defaults to `EQ` — existing brace-init code
continues to compile and behave the same. To use other operators, pass the
operator explicitly with the 3-argument constructor:

```cpp
using Op = smartbotic::database::Client::FilterOp;

smartbotic::database::Client::QueryOptions opts;
opts.filters = {
    {"age", Op::GTE, 18},                                             // age >= 18
    {"status", Op::NE, "deleted"},                                    // status != "deleted"
    {"tags", Op::CONTAINS, "admin"},                                  // tags array contains "admin"
    {"role", Op::IN, nlohmann::json::array({"admin", "moderator"})},  // role IN [...]
    {"email", Op::REGEX, ".*@example\\.com$"},                        // regex match
    {"name", Op::SEARCH, "alice"},                                    // full-text search
    {"deleted_at", Op::EXISTS, false},                                // field does NOT exist
};
auto docs = db.find("users", opts);
```

| Operator              | Semantics                                                      | Value type                  |
|-----------------------|----------------------------------------------------------------|-----------------------------|
| `FilterOp::EQ`        | field equals value                                             | any JSON                    |
| `FilterOp::NE`        | field does not equal value                                     | any JSON                    |
| `FilterOp::GT`        | field > value                                                  | number or string            |
| `FilterOp::GTE`       | field >= value                                                 | number or string            |
| `FilterOp::LT`        | field < value                                                  | number or string            |
| `FilterOp::LTE`       | field <= value                                                 | number or string            |
| `FilterOp::IN`        | field value appears in the supplied array                      | JSON array                  |
| `FilterOp::CONTAINS`  | field (array) contains the supplied value                      | any JSON                    |
| `FilterOp::EXISTS`    | field presence check (`true` = must exist, `false` = must not) | bool                        |
| `FilterOp::REGEX`     | field matches the supplied regex pattern                       | string                      |
| `FilterOp::SEARCH`    | full-text search across document ID and string fields          | string                      |

Note: the old `_search` magic field name that auto-selected SEARCH is no
longer special — use `FilterOp::SEARCH` explicitly instead.

### Collection Management

```cpp
// Create a basic collection
db.createCollection("users");

// Create with options
db.createCollection("sessions",
    /*defaultTtlSeconds=*/3600,    // auto-expire after 1 hour
    /*encrypted=*/false,
    /*maxVersions=*/5,             // keep last 5 versions
    /*vectorDimension=*/0);        // no vector support

// Create vector-enabled collection
db.createCollection("memories",
    /*defaultTtlSeconds=*/0,
    /*encrypted=*/false,
    /*maxVersions=*/0,
    /*vectorDimension=*/384);      // 384-dim embeddings

// List and inspect
auto collections = db.listCollections();
auto info = db.getCollectionInfo("users");
if (info) {
    std::cout << "Documents: " << info->documentCount << std::endl;
}

// Drop
db.dropCollection("old_data");
```

### Vector Similarity Search

Requires a collection created with `vectorDimension > 0`.

```cpp
// Insert documents with embeddings
nlohmann::json doc = {
    {"id", "mem-1"},
    {"content", "The user prefers dark mode"},
    {"_vector", embedding}   // std::vector<float>, size must match vectorDimension
};
db.insert("memories", doc, "mem-1");

// Search
std::vector<float> queryVec = getEmbedding("user preferences");
auto results = db.similaritySearch("memories", queryVec, /*topK=*/5, /*minScore=*/0.7f);

for (const auto& r : results) {
    std::cout << r.id << " (score=" << r.score << "): "
              << r.data["content"] << std::endl;
}
```

Notes:
- `_vector` is extracted and stored separately — not returned in `get()` or `find()` responses
- Dimension is validated on every insert — mismatches are rejected
- SIMD-accelerated (AVX2/SSE4.1) brute-force cosine similarity

### Version History

```cpp
// Get history
auto history = db.getVersionHistory("users", "user-123", /*limit=*/10);
for (const auto& v : history.versions) {
    std::cout << "v" << v.version << " by " << v.updatedBy
              << " at " << v.timestamp << std::endl;
}

// Get a specific version
auto oldVersion = db.getDocumentVersion("users", "user-123", /*version=*/2);

// Restore to a previous version
uint64_t newVer = db.restoreVersion("users", "user-123", /*version=*/2);

// Restore to a point in time
auto [fromVer, newVersion] = db.restoreToDate("users", "user-123", timestamp);
```

### File Storage

Two-level storage with content-addressed blob deduplication. Identical files are stored once.

```cpp
// Upload
std::vector<uint8_t> data = readFile("photo.jpg");
smartbotic::database::Client::FileUploadMeta meta;
meta.name = "photo.jpg";
meta.mime_type = "image/jpeg";
meta.file_type = "document";        // "plugin", "document", "generated"
meta.related_id = "conversation-1";
meta.is_public = false;

auto result = db.uploadFile(data, meta);
std::cout << "File ID: " << result.id
          << " Deduplicated: " << result.deduplicated << std::endl;

// Download
auto fileData = db.downloadFile(result.id);

// Get file info
auto fileInfo = db.getFileInfo(result.id);

// List files
auto files = db.listFiles(
    /*file_type=*/"document",
    /*related_id=*/"conversation-1",
    /*limit=*/100, /*offset=*/0,
    /*checksum=*/"",
    /*name=*/"photo.jpg");

// Delete
db.deleteFile(result.id);
```

### Event Subscription

```cpp
auto subscription = db.subscribe({"users", "sessions"}, [](
    const std::string& collection,
    const std::string& id,
    const std::string& eventType,
    const std::optional<nlohmann::json>& data) {

    std::cout << eventType << " on " << collection << "/" << id << std::endl;
});

// Unsubscribe by releasing the handle
subscription.reset();
```

### Set Operations

Redis-compatible set operations on top of documents.

```cpp
db.setAdd("tags", "user-123", "admin");
db.setAdd("tags", "user-123", "active");

auto members = db.setMembers("tags", "user-123");
bool isAdmin = db.setIsMember("tags", "user-123", "admin");

db.setRemove("tags", "user-123", "active");
```

### Health & Statistics

```cpp
// Quick health check
if (db.healthCheck()) {
    std::cout << "Database is healthy" << std::endl;
}

// Detailed health
auto health = db.getHealthInfo();
if (health) {
    std::cout << "Uptime: " << health->uptimeMs << "ms"
              << " Docs: " << health->documentCount << std::endl;
}

// Full statistics
auto stats = db.getStats();
if (stats) {
    std::cout << "Memory: " << stats->memoryUsedBytes << " bytes"
              << " Insert avg: " << stats->insertAvgMicros() << " us" << std::endl;
}
```

---

## Configuration

The server configuration file is at `/etc/smartbotic-database/config.json`.

### Default Configuration

```json
{
  "log_level": "info",
  "storage": {
    "bind_address": "localhost",
    "rpc_port": 9004,
    "node_id": "smartbotic-db",
    "data_directory": "/var/lib/smartbotic-database",
    "memory": {
      "max_memory_mb": 512,
      "eviction_threshold_percent": 80,
      "eviction_target_percent": 60,
      "eviction_check_interval_ms": 5000
    },
    "persistence": {
      "wal_sync_interval_ms": 100,
      "snapshot_interval_sec": 3600,
      "compression": "lz4"
    },
    "encryption": {
      "enabled": true,
      "key_file": "/var/lib/smartbotic-database/storage.key",
      "auto_generate_key": true
    },
    "migrations": {
      "enabled": false,
      "directory": "/etc/smartbotic-database/migrations",
      "auto_apply": false
    },
    "files": {
      "max_file_size_mb": 500,
      "cleanup_orphans_interval_sec": 3600
    },
    "replication": {
      "enabled": false
    }
  }
}
```

### Key Settings

| Setting | Default | Description |
|---------|---------|-------------|
| `bind_address` | `localhost` | Change to `0.0.0.0` to accept remote connections |
| `rpc_port` | `9004` | gRPC listen port |
| `data_directory` | `/var/lib/smartbotic-database` | WAL, snapshots, files stored here |
| `memory.max_memory_mb` | `512` | Maximum memory for document cache. Evicts LRU when exceeded |
| `persistence.snapshot_interval_sec` | `3600` | Full snapshot every N seconds (WAL truncated after) |
| `encryption.enabled` | `true` | Field-level AES-256-GCM encryption. Key auto-generated on first start |
| `migrations.enabled` | `false` | Enable to auto-apply JSON migrations on startup |
| `migrations.directory` | `/etc/smartbotic-database/migrations` | Path to migration JSON files |

### Configuring for Your Project

Consumer projects typically enable migrations by configuring the database via their own `postinst` script:

```bash
# Example: in your project's postinst
DB_CONFIG="/etc/smartbotic-database/config.json"
python3 -c "
import json
with open('$DB_CONFIG') as f:
    cfg = json.load(f)
s = cfg['storage']
s['migrations']['enabled'] = True
s['migrations']['directory'] = '/opt/myproject/migrations'
s['migrations']['auto_apply'] = True
with open('$DB_CONFIG', 'w') as f:
    json.dump(cfg, f, indent=2)
"
systemctl restart smartbotic-database
```

---

## Migrations

Declarative JSON-based schema migrations. Create numbered JSON files in the migrations directory.

### Migration File Format

```json
{
  "version": "001",
  "name": "create_users",
  "description": "Create users collection with encryption",
  "operations": [
    {
      "type": "create_collection",
      "collection": "users",
      "options": {
        "encrypted": true,
        "max_versions": 10
      }
    }
  ]
}
```

### Available Operations

| Operation | Fields | Description |
|-----------|--------|-------------|
| `create_collection` | `collection`, `options` | Create a new collection. Options: `encrypted`, `max_versions`, `default_ttl_seconds`, `vector_dimension` |
| `put` | `collection`, `document` | Insert/upsert a document |
| `delete` | `collection`, `id` | Delete a document |
| `delete_where_id_prefix` | `collection`, `prefix` | Bulk-delete documents whose ID starts with prefix |

### Vector-Enabled Collection Migration

```json
{
  "version": "002",
  "name": "create_memories",
  "description": "Create vector-enabled memories collection",
  "operations": [
    {
      "type": "create_collection",
      "collection": "memories",
      "options": {
        "vector_dimension": 384
      }
    }
  ]
}
```

Note: `vector_dimension` is immutable after collection creation.

---

## Docker Build Integration

When building your project in Docker for production, install the dev package in your base image instead of compiling the submodule from source.

### Dockerfile.base Example

```dockerfile
FROM debian:trixie

# Add Smartbotics APT repository
COPY smartbotics-repo.gpg /usr/share/keyrings/
RUN echo "deb [signed-by=/usr/share/keyrings/smartbotics-repo.gpg] \
    https://repository.smartbotics.ai trixie main" \
    > /etc/apt/sources.list.d/smartbotics.list

# Configure authentication
ARG REPO_USER=callerai
ARG REPO_PASS
RUN printf "machine repository.smartbotics.ai\nlogin %s\npassword %s\n" \
    "$REPO_USER" "$REPO_PASS" > /etc/apt/auth.conf.d/smartbotics.conf && \
    chmod 600 /etc/apt/auth.conf.d/smartbotics.conf

# Install client dev package (pulls shared lib as dependency)
RUN apt-get update && apt-get install -y \
    libsmartbotic-db-client-dev \
    && rm -rf /var/lib/apt/lists/*
```

Then your project's CMake uses `find_package(smartbotic-db-client REQUIRED)` and links against the pre-built shared library.

### Production Dockerfile

Your project's production `.deb` should declare:

```
Depends: smartbotic-database (>= 1.2.0), libsmartbotic-db-client (>= 1.2.0), ...
```

This ensures both the server and runtime library are installed on the target machine.

---

## CLI Administration

Install the CLI tool:

```bash
sudo apt install smartbotic-db-cli
```

The CLI connects to the database via gRPC. It does not require the server installed on the same machine — it can administer a remote instance.

```bash
# Connect to default (localhost:9004)
smartbotic-db-cli health

# Connect to a remote instance
smartbotic-db-cli --address 192.168.1.50:9004 health
```

---

## Upgrading from Legacy Packages

If your machine previously had `shadowman-database` or `callerai-storage` installed, the new `smartbotic-database` package handles the transition automatically:

```bash
sudo apt install smartbotic-database
```

What happens:
1. `dpkg` sees `Conflicts: shadowman-database` / `callerai-storage`
2. The old package is removed (its `prerm` stops the old service)
3. `smartbotic-database` is installed
4. The `postinst` script detects old data/config and migrates:
   - `/var/lib/shadowman/data/database/` → `/var/lib/smartbotic-database/`
   - `/etc/shadowman/database.json` → `/etc/smartbotic-database/config.json` (paths updated)
   - Same for callerai paths

The old data directory is preserved (copied, not moved) so rollback is safe.

### Rollback

If something goes wrong:

```bash
sudo apt remove smartbotic-database
sudo apt install shadowman-database=1.2.0-14   # or callerai-storage=0.5.18-1
```

Old packages remain in the repository.

---

## Proto File

The raw `.proto` file is available at:
- Package: `/usr/share/smartbotic-database/proto/database.proto`
- Source: `proto/database.proto`

This can be used to generate client stubs in other languages (Python, Go, etc.) if needed.