# Smartbotic Database

A standalone key-value document database with gRPC API, designed for microservice architectures.

## Features

- **Document Storage** - JSON document store with collections
- **Version History** - Per-document version tracking with restore by version or date
- **Health Check** - gRPC `HealthCheck()` RPC for service status verification
- **Replication** - Multi-master replication via `DatabaseReplication` gRPC service
- **Persistence** - WAL (Write-Ahead Log) + snapshots with LZ4 compression
- **Encryption** - Field-level AES-256-GCM encryption for sensitive data
- **Events** - Pub/sub event subscription for real-time updates
- **File Storage** - Binary file management with streaming support
- **Vector Storage** - Embedding vector storage with SIMD-accelerated cosine similarity search
- **Migrations** - Declarative JSON-based schema migrations

## Architecture

```
smartbotic-database/
├── client/           # Client library (injectable into projects)
├── service/          # Database service (standalone deployable)
├── proto/            # gRPC protocol buffer definitions
└── packaging/        # Docker build and systemd files
```

### Component Separation

| Component | Target | Description |
|-----------|--------|-------------|
| **Client Library** | `smartbotic-db-client` | Lightweight gRPC client, injectable via submodule |
| **Database Service** | `smartbotic-database` | Full server with persistence, encryption, replication |
| **Proto Library** | `smartbotic_db_proto` | Generated protobuf/gRPC code |

## Building

### Prerequisites

```bash
# Debian/Ubuntu
sudo apt install cmake ninja-build \
    protobuf-compiler libprotobuf-dev \
    libgrpc++-dev protobuf-compiler-grpc \
    nlohmann-json3-dev libspdlog-dev libssl-dev liblz4-dev
```

### Standalone Build

```bash
cmake -B build -G Ninja
ninja -C build

# Run the database
./build/service/smartbotic-database --config config/database.json
```

### As Embedded Submodule

When used as a git submodule in another project:

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

Configure in your CMakeLists.txt:

```cmake
# Build options
set(SMARTBOTIC_DB_BUILD_CLIENT ON CACHE BOOL "" FORCE)   # Always need client
set(SMARTBOTIC_DB_BUILD_SERVICE ON CACHE BOOL "" FORCE)  # Optional: include service

add_subdirectory(external/smartbotic-database)

# Link to client library
target_link_libraries(your-service PRIVATE smartbotic-db-client)
```

## Client Library Usage

### Basic Operations

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

// Connect to database
smartbotic::database::Client client("localhost:9004");

// Create a document
nlohmann::json user = {
    {"name", "John Doe"},
    {"email", "john@example.com"}
};
auto id = client.put("users", user);

// Get a document
auto doc = client.get("users", id);

// Query documents
smartbotic::database::QueryOptions opts;
opts.filter = {{"name", "John Doe"}};
auto results = client.query("users", opts);

// Delete a document
client.remove("users", id);
```

### Working with Files

```cpp
// Upload a file
std::vector<uint8_t> audioData = loadAudioFile("recording.wav");
auto fileId = client.uploadFile("call-recordings", "recording.wav", "audio/wav", audioData);

// Download a file
auto [data, metadata] = client.downloadFile(fileId);

// Delete a file
client.deleteFile(fileId);
```

### Vector Storage & Similarity Search

Store embedding vectors alongside documents and perform cosine similarity search — no external dependencies required.

```cpp
// Create a vector-enabled collection (dimension must be fixed at creation)
client.createCollection("memories", 0, false, 0, 384);  // 384-dim embeddings

// Insert documents with _vector field
nlohmann::json doc = {
    {"id", "mem-1"},
    {"content", "The user prefers dark mode"},
    {"_vector", embedding}  // std::vector<float> of size 384
};
client.put("memories", doc);

// Similarity search — returns top-K results sorted by cosine similarity
auto results = client.similaritySearch("memories", queryEmbedding, /*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 — it is **not** returned in `Get()` or `Query()` responses
- Dimension is validated on every insert/update — mismatches are rejected
- Documents without `_vector` are stored normally but excluded from similarity search
- SIMD-accelerated (AVX2/SSE4.1) brute-force scan, suitable for up to ~100K vectors

### Subscribing to Events

```cpp
// Subscribe to collection events
client.subscribe("users", [](const smartbotic::database::Event& event) {
    if (event.type == smartbotic::database::EventType::Created) {
        std::cout << "New user created: " << event.documentId << std::endl;
    }
});
```

### Version History

Every update and delete saves the previous document state to version history. You can browse history, retrieve old versions, and restore documents — including deleted ones.

```cpp
// Get version history
auto history = client.getVersionHistory("users", docId);
for (const auto& ver : history.versions) {
    std::cout << "v" << ver.version << " at " << ver.timestamp
              << " by " << ver.updatedBy << std::endl;
}

// Get a specific old version
auto oldVer = client.getDocumentVersion("users", docId, 2);

// Restore to a specific version (creates new version with old data)
uint64_t newVer = client.restoreVersion("users", docId, 2, "admin");

// Restore to what the document looked like at a given time
auto [restoredFrom, newVersion] = client.restoreToDate("users", docId,
    1706000000000, "admin");  // timestamp in ms

// Restore a deleted document
client.remove("users", docId);
uint64_t restored = client.restoreVersion("users", docId, 1, "admin");
```

History depth is configurable per-collection via `max_versions` (0 = unlimited). History survives restarts through snapshots and WAL replay.

## gRPC API

### DatabaseService

| RPC | Description |
|-----|-------------|
| `HealthCheck` | Check service health and status |
| `Get` | Retrieve document by ID |
| `Put` | Create or update document |
| `Delete` | Remove document by ID |
| `Query` | Query documents with filters |
| `Watch` | Subscribe to document changes |
| `ListCollections` | List all collections |
| `GetVersionHistory` | List version history for a document |
| `GetDocumentVersion` | Get document data at a specific version |
| `RestoreVersion` | Restore document to a previous version (copy-forward) |
| `RestoreToDate` | Restore document to version active at a timestamp |
| `UploadFile` | Upload binary file (streaming) |
| `DownloadFile` | Download binary file (streaming) |
| `DeleteFile` | Delete file by ID |
| `SimilaritySearch` | Cosine similarity search over document vectors |
| `GetFileMetadata` | Get file metadata |

### DatabaseReplication

| RPC | Description |
|-----|-------------|
| `Sync` | Bidirectional sync stream |
| `GetChanges` | Get changes since sequence number |
| `ApplyChanges` | Apply changes from peer |

## Configuration

```json
{
  "log_level": "info",
  "database": {
    "bind_address": "0.0.0.0",
    "rpc_port": 9004,
    "node_id": "db-node-1",
    "data_directory": "/var/lib/smartbotic/database",
    "migrations": {
      "enabled": true,
      "directory": "/etc/smartbotic/migrations",
      "auto_apply": true,
      "fail_on_error": true
    },
    "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
    },
    "files": {
      "max_file_size_mb": 500,
      "allowed_types": ["audio/*", "application/octet-stream"],
      "cleanup_orphans_interval_sec": 3600
    },
    "replication": {
      "enabled": false,
      "peers": ["db-node-2.example.com:9004"],
      "conflict_resolution": "last_writer_wins"
    }
  }
}
```

### Configuration Reference

| Section | Option | Default | Description |
|---------|--------|---------|-------------|
| `database` | `bind_address` | `0.0.0.0` | Interface to bind gRPC server |
| `database` | `rpc_port` | `9004` | gRPC server port |
| `database` | `node_id` | `db-1` | Unique node identifier for replication |
| `database` | `data_directory` | `/var/lib/smartbotic/database` | Data storage path |
| `migrations` | `enabled` | `true` | Enable migration system |
| `migrations` | `directory` | - | Path to migration files |
| `migrations` | `auto_apply` | `true` | Auto-apply pending migrations on startup |
| `migrations` | `fail_on_error` | `true` | Stop if migration fails |
| `persistence` | `wal_sync_interval_ms` | `100` | WAL sync frequency |
| `persistence` | `snapshot_interval_sec` | `3600` | Snapshot creation interval |
| `persistence` | `compression` | `lz4` | Compression algorithm |
| `encryption` | `enabled` | `false` | Enable field-level encryption |
| `encryption` | `key_file` | - | Path to encryption key |
| `encryption` | `auto_generate_key` | `false` | Generate key if missing |
| `replication` | `enabled` | `false` | Enable multi-master replication |
| `replication` | `peers` | `[]` | List of peer node addresses |
| `replication` | `conflict_resolution` | `last_writer_wins` | Conflict resolution strategy |

## Migration System

### Creating Migrations

Create JSON migration files in the migrations directory. Files are processed in alphabetical order (use numeric prefixes).

**001_create_users.json:**
```json
{
  "version": "001",
  "name": "create_users",
  "description": "Create users collection with encryption",
  "operations": [
    {
      "type": "create_collection",
      "collection": "users",
      "options": {
        "encrypted": true,
        "sensitive_fields": ["password_hash", "api_key"],
        "max_versions": 50
      }
    }
  ]
}
```

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

**003_seed_admin.json:**
```json
{
  "version": "003",
  "name": "seed_admin",
  "description": "Create default admin user",
  "operations": [
    {
      "type": "insert_if_not_exists",
      "collection": "users",
      "id": "admin",
      "data": {
        "username": "admin",
        "role": "administrator",
        "created_at": "{{NOW}}"
      }
    }
  ]
}
```

### Migration Operations

| Operation | Description | Required Fields |
|-----------|-------------|-----------------|
| `create_collection` | Create collection if not exists | `collection`, `options` (optional) |
| `drop_collection` | Drop collection | `collection`, `confirm: true` |
| `insert` | Insert document (fails if exists) | `collection`, `id`, `data` |
| `insert_if_not_exists` | Insert only if ID doesn't exist | `collection`, `id`, `data` |
| `upsert` | Insert or update document | `collection`, `id`, `data` |
| `update` | Update existing document | `collection`, `id`, `data` |
| `delete` | Delete document | `collection`, `id` |

### Migration Tracking

Applied migrations are tracked in the `_migrations` collection:

```json
{
  "id": "001_create_users",
  "version": "001",
  "name": "create_users",
  "applied_at": "2024-01-15T10:30:00Z",
  "status": "applied",
  "checksum": "sha256:abc123..."
}
```

### Template Variables

| Variable | Description |
|----------|-------------|
| `{{NOW}}` | Current ISO 8601 timestamp |
| `{{NODE_ID}}` | Database node identifier |
| `{{VERSION}}` | Database version |

## Deployment Patterns

### Pattern 1: Standalone Database

Deploy as a separate service:

```
┌──────────────────┐         ┌──────────────────┐
│   Your App       │──gRPC──▶│ smartbotic-db    │
│   (client lib)   │         │ (separate node)  │
└──────────────────┘         └──────────────────┘
```

```bash
# Build and package database separately
cd packaging
./build.sh
# Deploy smartbotic-database-x.y.z.tar.gz to database servers
```

### Pattern 2: Bundled with Application

Include database binary in your application package:

```cmake
# In your CMakeLists.txt
set(SMARTBOTIC_DB_BUILD_SERVICE ON)
add_subdirectory(external/smartbotic-database)
```

Both binaries deployed together:
```
/opt/myapp/
├── bin/
│   ├── myapp
│   └── smartbotic-database
└── etc/
    └── database.json
```

### Pattern 3: High Availability

Multiple database nodes with replication:

```
┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│  db-node-1  │◄───▶│  db-node-2  │◄───▶│  db-node-3  │
│  (primary)  │     │  (replica)  │     │  (replica)  │
└─────────────┘     └─────────────┘     └─────────────┘
       ▲                  ▲                   ▲
       │                  │                   │
       └──────────────────┴───────────────────┘
                    Load Balancer
```

Enable replication in config:
```json
{
  "replication": {
    "enabled": true,
    "peers": ["db-node-2:9004", "db-node-3:9004"],
    "conflict_resolution": "last_writer_wins"
  }
}
```

## Health Checking

```bash
# Using grpcurl
grpcurl -plaintext localhost:9004 smartbotic.databasepb.DatabaseService/HealthCheck

# Response
{
  "healthy": true,
  "nodeId": "db-node-1",
  "uptime": 86400,
  "documentsCount": 15234,
  "collectionsCount": 8,
  "diskUsageBytes": 52428800
}
```

## Systemd Service

```ini
# /etc/systemd/system/smartbotic-database.service
[Unit]
Description=Smartbotic Database Service
After=network.target

[Service]
Type=simple
User=smartbotic
ExecStart=/opt/smartbotic/bin/smartbotic-database --config /etc/smartbotic/database.json
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target
```

## CMake Targets

| Target | Description | Build Option |
|--------|-------------|--------------|
| `smartbotic_db_proto` | Generated protobuf/gRPC code | Always |
| `smartbotic-db-client` | Client library | `SMARTBOTIC_DB_BUILD_CLIENT=ON` |
| `smartbotic-database` | Service executable | `SMARTBOTIC_DB_BUILD_SERVICE=ON` |

## Modifying from Consumer Project

When using as a submodule, you can modify the database code directly:

```bash
cd external/smartbotic-database
# Make changes
git add -A && git commit -m "Fix: description"
git push origin main

# Update submodule reference in parent project
cd ../..
git add external/smartbotic-database
git commit -m "Update smartbotic-database submodule"
```

## License

Proprietary - Smartbotics AI