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)

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:

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.

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 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:

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:

-- 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:

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:

-- 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)

[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 for every key.

Environment Variables

# 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

{
  "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); 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:

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

Lifecycle:

Open the data directory
Load schema and indexes
Execute queries
Flush data on shutdown

Concurrency: Single process with multi-threaded internal handling

Pattern 2: Server Mode

Use Case: Network accessible database

# 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:

Start server process
Accept client connections
Maintain connection pool
Replicate transactions to disk

Concurrency: Multiple client connections (configurable max)

Pattern 3: Vector Search Integration

Use Case: Semantic search in applications

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

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

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

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

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

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

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: pass an http.Client that adds the bearer token.

12. Schema Examples

Example 1: E-commerce

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

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:

-- 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:

-- 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:

-- 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;