---
title: "HeliosDB-Lite AI Agent & Automation Technical Guide"
description: "Structured reference for AI agents and automation working with HeliosDB Lite: SQL surface, PostgreSQL wire, REST, configuration, errors and operations."
---

# HeliosDB-Lite AI Agent & Automation Technical Guide

## Overview

This guide is designed for AI agents, automation systems, and programmatic access to HeliosDB-Lite. It provides structured information for parsing and integration by automated systems.

---

## 1. System Specifications

### Database Engine
- **Type**: PostgreSQL-compatible embedded database
- **Storage**: native storage engine with a write-ahead log
- **Transaction Model**: MVCC with snapshot isolation
- **Concurrency**: ACID transactions with row-level locking
- **Query Language**: SQL (95%+ PostgreSQL compatibility)

### Supported Deployment Modes

| Mode | Location | Concurrency | Use Case |
|------|----------|-------------|----------|
| `embedded` | In-process (licensed source distribution, or the `heliosdb-lite repl` shell) | Single process | Desktop apps, services |
| `server` | Network | Multiple clients | Web apps, APIs |
| `in_memory` | RAM only | Single process | Tests, analytics |
| `hybrid` | Disk + cache | Multiple clients | Production systems |

### Resource Requirements

| Component | Minimum | Recommended |
|-----------|---------|-------------|
| Memory | 50 MB | 512 MB - 4 GB |
| Disk | 10 MB | 100 MB - 10 GB |
| CPU | 1 core | 2+ cores |

---

## 2. API Integration Points

### Client Library APIs

#### REPL (Embedded)

```bash
heliosdb-lite repl --data-dir ./mydb    # or --memory
```

#### REST API

`heliosdb-lite start` serves the REST API on port 8080 under `/v1` and `/api/v1`. Every `/v1` route
needs `Authorization: Bearer <JWT>`, signed with `HELIOSDB_JWT_SECRET`:

```bash
curl -s -X POST http://127.0.0.1:8080/v1/branches/main/query \
  -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
  -d '{"sql": "SELECT 1"}'
```

See the [REST API Reference](https://www.heliosdb.com/docs/lite/api/rest-api/).

#### PostgreSQL Wire Protocol

Supported for server mode (port 5432 by default):

```
postgresql://[user[:password]@][host][:port]/[database]

Example:
postgresql://localhost:5432/mydb
postgresql://user:pass@db.example.com:5432/production
```

Compatible clients:
- psql (PostgreSQL CLI)
- Python: `psycopg2`, `asyncpg`, `sqlalchemy`
- JavaScript/Node.js: `pg`, `node-postgres`
- Rust: `sqlx`, `tokio-postgres`, `diesel`
- Java: `jdbc`
- Go: `database/sql`, `pgx`
-.NET: `Npgsql`

### SDK Integrations

The Apache-2.0 client SDKs (REST clients for the `/v1` API) are installed from the public
[HeliosDatabase/HeliosDB-SDKs](https://github.com/HeliosDatabase/HeliosDB-SDKs) repository:

| Language | Status |
|----------|--------|
| Python | Available from source |
| TypeScript | Available from source |
| Go | Available from source |
| Rust | Available from source |

---

## 3. Query Specification

### SQL Dialect

**Supported Features:**
- DML: SELECT, INSERT, UPDATE, DELETE
- DDL: CREATE/DROP/ALTER TABLE, INDEX, VIEW
- Transactions: BEGIN, COMMIT, ROLLBACK
- Temporal: AS OF TIMESTAMP/TRANSACTION/SCN
- Vector operations: Vector types, HNSW indexes
- Functions: Aggregate, scalar, window functions
- Joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS

**Parameterized Query Format:**

```sql
SELECT * FROM table WHERE column = $1 AND other = $2;
-- Parameter indices: $1, $2, $3, ... (1-indexed)
```

### Data Types

| Type | Size | Example |
|------|------|---------|
| BOOLEAN | 1 byte | true, false |
| INT2 | 2 bytes | -32768 to 32767 |
| INT4 | 4 bytes | -2147483648 to 2147483647 |
| INT8 | 8 bytes | -2^63 to 2^63-1 |
| FLOAT4 | 4 bytes | Single precision |
| FLOAT8 | 8 bytes | Double precision |
| DECIMAL(p,s) | Variable | 123.45 |
| TEXT | Variable | 'string' |
| VARCHAR(n) | Variable | 'string' |
| BYTEA | Variable | Binary data |
| TIMESTAMP | 8 bytes | 2025-01-15 12:00:00 |
| DATE | 4 bytes | 2025-01-15 |
| JSON | Variable | {"key": "value"} |
| JSONB | Variable | {"key": "value"} |
| VECTOR(n) | Variable | [0.1, 0.2,...] |
| UUID | 16 bytes | 550e8400-e29b-41d4-a716-446655440000 |
| ARRAY[T] | Variable | ARRAY[1,2,3] |

### Vector Operations

**Vector Type Definition:**
```sql
-- Define columns with vector type
column_name VECTOR(dimensions)

-- Example: 1536-dimensional embedding (OpenAI)
embedding VECTOR(1536)
```

**Distance Operators:**
| Operator | Metric | Distance |
|----------|--------|----------|
| `<=>` | Cosine | 0 to 2 |
| `<#>` | Euclidean | ≥ 0 |
| `<+>` | Manhattan | ≥ 0 |
| `<~>` | Dot Product | -∞ to ∞ |

**Index Creation:**
```sql
CREATE INDEX idx_name ON table USING hnsw (vector_column distance_ops)
WITH (
    m = 16,                    -- HNSW connection count
    ef_construction = 200,     -- Build quality
    quantization = 'product',  -- Optional Product Quantization
    pq_subquantizers = 8       -- PQ settings
);
```

---

## 4. System Views & Monitoring

### Information Schema Views

**Available system views for introspection:**

```sql
-- List all tables
SELECT * FROM information_schema.tables WHERE table_schema = 'public';

-- List all columns
SELECT * FROM information_schema.columns WHERE table_name = 'table_name';

-- List all indexes
SELECT * FROM pg_indexes;

-- List all views
SELECT * FROM information_schema.views WHERE table_schema = 'public';

-- Database statistics
SELECT * FROM pg_stat_user_tables;

-- Index statistics
SELECT * FROM pg_stat_user_indexes;

-- Vector index statistics
SELECT * FROM pg_vector_index_stats;

-- Branch information
SELECT * FROM pg_database_branches();

-- Materialized view staleness
SELECT * FROM pg_mv_staleness();

-- Current database size
SELECT pg_database_size(current_database());
```

---

## 5. Configuration Parameters

### Configuration File (heliosdb.toml)

```toml
[storage]
path = "./heliosdb-data"            # Data directory
cache_size = 536870912              # 512 MB
compression = "Zstd"                # Zstd, Lz4, None
memory_only = false                 # true = no persistence
time_travel_enabled = true          # Time-travel queries

[server]
listen_addr = "127.0.0.1"           # Listen address
port = 5432                         # PostgreSQL port
max_connections = 100               # Maximum client connections

[performance]
simd_enabled = true                 # SIMD acceleration
worker_threads = 4                  # Query worker threads
parallel_query = true               # Parallel query execution

[encryption]
enabled = false                     # AES-256-GCM encryption at rest
key_source = { Environment = "HELIOSDB_ENCRYPTION_KEY" }

[vector]
enable_pq = true                    # Product Quantization
```

The REST port is a command-line flag: `heliosdb-lite start --http-port 8080`. See the
[Configuration reference](https://www.heliosdb.com/docs/lite/user-guide/configuration/) for every key.

### Environment Variables

```bash
# Server mode (required by heliosdb-lite start: REST token signing)
HELIOSDB_JWT_SECRET=...

# Encryption (read when [encryption] is enabled with the default key source)
HELIOSDB_ENCRYPTION_KEY=...

# Logging: trace, debug, info, warn, error (or start with --verbose)
RUST_LOG=debug
```

---

## 6. Error Handling & Response Codes

### PostgreSQL Error Codes

Standard PostgreSQL SQLSTATE codes are used:

| Code | Class | Meaning |
|------|-------|---------|
| 00000 | SUCCESS | Command successful |
| 01000 | WARNING | Warning (non-fatal) |
| 08000 | CONNECTION | Connection failure |
| 42P01 | UNDEFINED_TABLE | Table does not exist |
| 42P09 | UNDEFINED_OBJECT | Object does not exist |
| 23505 | UNIQUE_VIOLATION | Unique constraint violation |
| 23503 | FOREIGN_KEY | Foreign key constraint violation |
| 25001 | IN_FAILED_SQL_TRANSACTION | Transaction in failed state |

### Structured Error Response

```json
{
  "error": {
    "code": "42P01",
    "message": "relation \"table_name\" does not exist",
    "detail": "Table 'table_name' was not found in schema 'public'",
    "severity": "ERROR",
    "sqlstate": "42P01"
  }
}
```

---

## 7. Performance Characteristics

Figures below come from internal tests (HeliosDB Lite, embedded SQL path, representative fixtures; see [benchmarks](https://www.heliosdb.com/benchmarks.html)); reproduce on your own hardware before relying on them.

### Query Performance

Measured on representative fixtures; reproduce on your own hardware.

| Operation | Throughput | Latency |
|-----------|-----------|---------|
| Single row lookup | 100K+ QPS | <1ms |
| Full table scan | 500K-1M rows/sec | Variable |
| Vector search (k=10) | 1K-5K QPS | <100ms |
| Aggregation | 500K rows/sec | Variable |
| Join | 100K-500K rows/sec | Variable |

### Compression Ratios

| Data Type | Method | Ratio |
|-----------|--------|-------|
| Numeric | ALP | 2-10x |
| String | FSST | 2-5x |
| Vector index | Product Quantization (HNSW) | about 16x less resident RAM at recall-safe defaults (internal tests, Nano 3.32.1 vs an unquantized index; no Lite-specific measurement) |

### Memory Usage

```
Base system: ~50 MB
Per connection: ~1-2 MB
Cache (configurable): 50 MB to 4 GB
Index overhead: ~10-20% of data size
```

---

## 8. Integration Patterns

### Pattern 1: Embedded Database

**Use Case**: Local application storage

The engine runs inside one process and reads a local data directory. Try it with the REPL:

```bash
heliosdb-lite repl --data-dir ./app-data
```

**Lifecycle**:
1. Open the data directory
2. Load schema and indexes
3. Execute queries
4. Flush data on shutdown

**Concurrency**: Single process with multi-threaded internal handling

### Pattern 2: Server Mode

**Use Case**: Network accessible database

```bash
# Start server (refuses to start without a REST token-signing secret)
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --port 5432 --data-dir ./mydb

# Client connection (any language): PostgreSQL wire protocol on port 5432
psql -h 127.0.0.1 -p 5432 -U postgres
```

**Lifecycle**:
1. Start server process
2. Accept client connections
3. Maintain connection pool
4. Replicate transactions to disk

**Concurrency**: Multiple client connections (configurable max)

### Pattern 3: Vector Search Integration

**Use Case**: Semantic search in applications

```sql
-- Storage
CREATE TABLE documents (
    id SERIAL,
    content TEXT,
    embedding VECTOR(1536)
);

CREATE INDEX idx_embedding ON documents
USING hnsw (embedding vector_cosine_ops);

-- Insert with embedding
INSERT INTO documents VALUES
    (1, 'text', array[0.1, 0.2, ...]);

-- Search
SELECT id, content,
       embedding <=> ARRAY[0.15, 0.25, ...] AS distance
FROM documents
ORDER BY distance
LIMIT 10;
```

### Pattern 4: Time-Travel Queries

**Use Case**: Historical data analysis

```sql
-- Current data
SELECT * FROM table;

-- Historical data
SELECT * FROM table AS OF TIMESTAMP '2025-01-01 00:00:00';

-- Audit trail
SELECT id, action, timestamp
FROM audit_log
WHERE occurred_at >= '2025-01-01';
```

---

## 9. Compatibility Matrix

### PostgreSQL Feature Support

| Feature | Support | Notes |
|---------|---------|-------|
| SQL (DML/DDL) | 95%+ | Full support for common operations |
| Transactions | Yes | MVCC, snapshot isolation |
| Indexes | Yes | B-tree, HNSW |
| Extensions | Partial | Extensions are not loadable (no PostGIS); vector search is built in |
| Replication | Partial | WAL streaming replication is experimental |
| Functions and procedures | Partial | SQL and PL/pgSQL |
| Triggers | Yes | PL/pgSQL trigger functions |
| Foreign data wrappers | No | |

### ORM Compatibility

| Framework | Language | Result |
|-----------|----------|--------|
| SQLAlchemy | Python | Supported |
| Diesel | Rust | Supported |
| Prisma | TypeScript | Supported |
| SQLx | Rust | Supported |
| Entity Framework | .NET | Supported |

---

## 10. Operational Parameters

### Health Checks

```sql
-- Basic connectivity
SELECT 1;

-- Database availability
SELECT pg_database_size(current_database());

-- Index health
SELECT * FROM pg_stat_user_indexes;

-- Transaction activity
SELECT * FROM pg_stat_activity;
```

### Maintenance Operations

```sql
-- VACUUM (cleanup dead tuples)
VACUUM ANALYZE;

-- REINDEX (rebuild indexes)
REINDEX DATABASE;

-- Backup
BACKUP DATABASE TO '/backups/full_001.hldb';

-- Restore (backup id from the BACKUP output)
RESTORE DATABASE FROM 'bk_1711400000';
```

### Diagnostic Queries

```sql
-- Slow queries (>1 second)
SELECT query, mean_time FROM pg_stat_statements
WHERE mean_time > 1000
ORDER BY mean_time DESC;

-- Table bloat
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Connection status
SELECT * FROM pg_stat_activity;
```

---

## 11. SDK Usage Patterns

The SDKs call the REST API with a JWT signed with `HELIOSDB_JWT_SECRET`. Vector-store and
agent-memory helpers call REST endpoint groups that are planned on Lite; run vector search in SQL.

### Python

```python
import os
from heliosdb import HeliosDB

db = HeliosDB.connect("http://localhost:8080", jwt_token=os.environ["HELIOSDB_TOKEN"])
result = db.query("SELECT * FROM users WHERE id = $1", [1])
```

### TypeScript

```typescript
import { HeliosDB } from '@heliosdb/client';

const db = new HeliosDB({ url: 'http://localhost:8080', jwtToken: process.env.HELIOSDB_TOKEN });
const result = await db.query('SELECT * FROM users WHERE id = $1', [1]);
```

### Go

See the [Go SDK page](https://www.heliosdb.com/docs/lite/api/go-sdk/): pass an `http.Client` that adds the bearer token.

---

## 12. Schema Examples

### Example 1: E-commerce

```sql
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE,
    name TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    price DECIMAL(10,2),
    embedding VECTOR(1536)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers,
    total DECIMAL(10,2),
    status TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_customer_email ON customers(email);
CREATE INDEX idx_order_customer ON orders(customer_id);
CREATE INDEX idx_product_embedding ON products
    USING hnsw (embedding vector_cosine_ops);

-- Materialized view
CREATE MATERIALIZED VIEW customer_stats AS
SELECT customer_id, COUNT(*) as order_count, SUM(total) as lifetime_value
FROM orders GROUP BY customer_id;
```

### Example 2: Time-Series Analytics

```sql
CREATE TABLE metrics (
    id SERIAL PRIMARY KEY,
    sensor_id INT,
    metric_name TEXT,
    value FLOAT8,
    timestamp TIMESTAMP DEFAULT NOW(),
    metadata JSONB
);

CREATE INDEX idx_sensor_time ON metrics(sensor_id, timestamp DESC);

CREATE MATERIALIZED VIEW hourly_avg AS
SELECT
    sensor_id,
    metric_name,
    DATE_TRUNC('hour', timestamp) as hour,
    AVG(value) as avg_value,
    MAX(value) as max_value,
    MIN(value) as min_value
FROM metrics
GROUP BY sensor_id, metric_name, DATE_TRUNC('hour', timestamp);

-- Refresh on a schedule (automatic background refresh is planned)
REFRESH MATERIALIZED VIEW hourly_avg INCREMENTALLY;
```

---

## 13. Troubleshooting Guide for Automation

### Connectivity Issues

**Problem**: Cannot connect to database

**Diagnostic Steps**:
```sql
-- 1. Test local connectivity
SELECT 1;

-- 2. Check server status
SELECT version();

-- 3. Check active connections
SELECT count(*) FROM pg_stat_activity;

-- 4. Check configuration
SHOW listen_addresses;
SHOW port;
```

### Performance Issues

**Problem**: Slow queries

**Analysis**:
```sql
-- Enable query logging
SET log_min_duration_statement = 1000;

-- Analyze query plan
EXPLAIN ANALYZE SELECT ...;

-- Check statistics
ANALYZE table_name;

-- Check missing indexes
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
```

### Resource Issues

**Problem**: Out of memory or disk space

**Diagnosis**:
```sql
-- Database size
SELECT pg_database_size(current_database());

-- Table sizes
SELECT tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Clear old data
DELETE FROM table_name WHERE created_at < '2024-01-01';
VACUUM ANALYZE;
```

---

## References

- [REST API Reference](/docs/lite/api/rest-api/)
- [SQL Reference](/docs/lite/user-guide/sql-reference/)
- [Deployment Modes](/docs/lite/getting-started/deployment/)