CAD-SYS // ULTSQL-KERNEL v1.0.32
COORD: X: 0000 | Y: 0000
JITTER: 0.08ms
View on pub.dev GitHub Repository
VERSION 1.0.32 100% PURE DART ZERO C/C++ DEPENDENCIES

ULTSQL Documentation

Complete technical guide for ULTSQL: the converged multi-model database engine combining Relational SQL, NoSQL Dotted JSON, Redis-style KV caching, and AI Vector RAG Search on a unified slotted-page core.

1. System Architecture & Overview #

ULTSQL is an autonomous, converged database engine implemented in 100% pure Dart. Unlike traditional systems that require running separate database daemons for relational queries (PostgreSQL/SQLite), document storage (MongoDB), high-speed key-value caching (Redis), and vector embeddings (Milvus/Pinecone), ULTSQL unifies all four operational models into a single engine binary and single storage container.

πŸ’‘ Architecture & SDK Availability
The core ULTSQL storage engine, SQL parser, WAL, and HNSW graph are written in 100% pure Dart. When using client bindings in Python (pip install ultsql) or Node.js (npm install ultsql), a small bundled engine process starts automatically on a free port and manages lifecycle cleanlyβ€”no external services, background daemons, or C-compiler toolchains required. On pub.dev, package:ultsql currently bundles Flutter dev-tooling & UI services (compatible with all Flutter platforms). For standalone CLI usage, install the native binary via install.sh / install.ps1 or deploy the official Docker container.

Storage is structured around a slotted-page architecture with an ARIES-compliant Write-Ahead Log (WAL), CRC32 integrity checksums, bottom-up B+ Tree construction, and graph-based HNSW vector indexing.

2. Installation & SDKs #

Install ULTSQL in your language environment or install the standalone CLI globally.

# Add to Dart / Flutter pubspec.yaml
dart pub add ultsql
# Or Flutter:
flutter pub add ultsql

3. Quickstart Guide #

Initialize an embedded database instance, create a table with primary key constraints, insert records, and query with high-speed interpreted execution.

import 'package:ultsql/ultsql.dart';

void main() async {
  // 1. Open persistent disk database (or ':memory:' for RAM)
  final db = Database('./my_database');
  await db.init();
  final it = Interpreter(db);

  // 2. Create relational schema
  await it.executeScript('''
    CREATE TABLE users (
      id INT PRIMARY KEY,
      name TEXT,
      email TEXT,
      created_at TEXT
    );
  ''');

  // 3. Insert records
  await it.executeScript('''
    INSERT INTO users VALUES (1, 'Alice Smith', '[email protected]', '2026-10-01');
    INSERT INTO users VALUES (2, 'Bob Jones', '[email protected]', '2026-10-02');
  ''');

  // 4. Query with standard SQL
  final res = await it.executeScript("SELECT name, email FROM users WHERE id = 1;");
  for (final row in res.rows) {
    print('User: ${row[0]} <${row[1]}>');
  }

  // 5. Clean shutdown (flushes cache and closes WAL)
  await db.close();
}

4. Storage Modes #

ULTSQL offers four switchable persistence profiles depending on performance and durability requirements:

Storage Mode Initialization Throughput (Measured) Crash Safety Recommended Use Case
⚑ In-Memory Database(':memory:') ~140k–200k rows/s Ephemeral (RAM) Automated unit tests, fast ephemeral caches, session states
πŸ’Ύ Durable Disk Database('./path') ~670k–718k rows/s ACID WAL + CRC32 Production transactional OLTP, embedded applications, microservices
πŸ”„ Hybrid Snapshot Database('./path', syncWal: false) ~1.2M rows/s Periodic Snapshots Time-series ingestion, sensory IoT streams, high-volume event logs
⚑ Public Batch API insertBatchRecordsSync() ~2.08M rows/s Slotted-Page Direct Packing ETL bulk loading, initial data migrations, parquet conversions

5. SQL & Dialect Reference #

ULTSQL supports an ANSI-SQL 92/99 dialect with modern PostgreSQL-style extensions including JSON operators, vector types, and procedural scripting.

Data Types

SQL Type Dart Representation Storage Footprint Description
INT / INTEGER / BIGINT DbInt (int) 8 bytes 64-bit signed integer
DOUBLE / FLOAT / REAL DbDouble (double) 8 bytes 64-bit IEEE floating point
TEXT / VARCHAR DbText (String) Variable (UTF-8) Arbitrary-length text string
BOOLEAN / BOOL DbBool (bool) 1 byte True or false boolean
BLOB DbBlob (Uint8List) Variable Raw binary byte array
JSON DbJson (Map/List) Variable (Structured) Dotted schema-less JSON object
VECTOR DbVector (List<double>) 4 bytes/dimension Fixed-dimension float32 embedding vector
UUID DbUuid (String) 16 bytes packed Standard RFC 4122 UUID
DATETIME / TIMESTAMP DbDateTime (DateTime) 8 bytes (epoch ms) ISO 8601 UTC timestamp
DECIMAL(p, s) DbDecimal Variable Exact fixed-precision decimal arithmetic

DDL (Data Definition Language)

SQL DDL
-- Create table with constraints
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount DOUBLE DEFAULT 0.0,
  status TEXT,
  metadata JSON,
  embedding VECTOR,
  placed_at DATETIME
);

-- Modify schema
ALTER TABLE orders ADD COLUMN discount DOUBLE;
DROP TABLE IF EXISTS old_orders;

-- Inspect database metadata
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.columns WHERE table_name = 'orders';

DML & Query Capabilities

SQL DML
-- Multi-row batch insert
INSERT INTO orders VALUES 
  (101, 1, 149.99, 'completed', '{"tier": "gold"}', '[0.1, 0.2, 0.3]', '2026-10-01T12:00:00Z'),
  (102, 2, 89.50, 'pending', '{"tier": "silver"}', '[0.4, 0.5, 0.6]', '2026-10-02T14:30:00Z');

-- UPSERT / REPLACE
REPLACE INTO orders VALUES (101, 1, 139.99, 'refunded', '{"tier": "gold"}', '[0.1, 0.2, 0.3]', '2026-10-01T12:00:00Z');

-- Aggregations with GROUP BY and HAVING
SELECT 
  status, 
  COUNT(*) AS total_orders, 
  AVG(amount) AS avg_amount,
  SUM(amount) AS gross_revenue
FROM orders
WHERE amount > 50.0
GROUP BY status
HAVING COUNT(*) >= 1
ORDER BY gross_revenue DESC
LIMIT 10 OFFSET 0;

-- Query Planner Execution Plan
EXPLAIN SELECT * FROM orders WHERE order_id = 101;

6. Prepared Statements & Batch API #

Prepared statements compile the SQL query string into an execution plan once, allowing parameterized parameters to be executed repeatedly without re-parsing overhead.

Dart API
final stmt = db.prepare('INSERT INTO metrics VALUES (?, ?, ?);');

// 1. Single parameterized execution (sub-5Β΅s point write)
stmt.executeSync([DbInt(101), DbText('cpu_load'), DbDouble(0.42)]);

// 2. High-speed synchronized batch insertion (~670k rows/sec)
final batch = List<List<DbValue>>.generate(50000, (i) {
  return [DbInt(i), DbText('metric_$i'), DbDouble(i * 0.1)];
});
stmt.executeBatchSync(batch);

// 3. Query parameterized prepared statement
final queryStmt = db.prepare('SELECT val FROM metrics WHERE tag = ?;');
final result = queryStmt.executeSync([DbText('cpu_load')]);
print('Metric value: ${result.rows.first.first}');

7. B+ Tree Indexes & Bottom-Up Construction #

ULTSQL builds standard B+ Tree indexes on disk with bottom-up bulk index construction. Instead of performing N random top-down tree insertions, creating an index on existing tables sorts leaf keys sequentially and constructs upper index layers in a single cache-coherent pass.

SQL Indexing
-- Standard B+ Tree Index on single column
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Compound / Multi-column Index
CREATE INDEX idx_orders_status_amount ON orders(status, amount);

-- Unique Constraint Index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Trigram Substring & Wildcard Index (Accelerates LIKE '%pattern%')
CREATE INDEX idx_products_name ON products(name) USING TRIGRAM;
SELECT * FROM products WHERE name LIKE '%quantum%';

-- Drop Index
DROP INDEX idx_orders_customer;

8. ACID Transactions & MVCC #

Transactions in ULTSQL guarantee full ACID semantics:

  • Atomicity: Statements executed inside BEGIN...COMMIT either all commit to disk or are completely discarded on ROLLBACK.
  • Consistency: Schema constraints, unique keys, and primary key requirements are validated before slot assignment.
  • Isolation: Multi-Version Concurrency Control (MVCC) ensures read operations never block write operations and writes never block reads.
  • Durability: Changes are recorded sequentially in the Write-Ahead Log before acknowledging commit.
SQL Transaction
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100.0 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100.0 WHERE account_id = 2;
-- In case of failure: ROLLBACK;
COMMIT;

Multi-Process Concurrency & Shared Read Locks

In addition to in-process MVCC, ULTSQL provides OS-level shared file locks (FileLock.shared) when opening a database with readOnly: true. Multiple separate OS processes and background isolates can read and query the same database file simultaneously without lock collisions, with configurable lockTimeout retry escalation.

Dart Shared Read Lock
final readerDb = Database(
  './production.ultsql',
  readOnly: true,
  lockTimeout: Duration(seconds: 5),
);
await readerDb.init(); // Acquires shared lock, allowing other processes to read concurrently

9. ARIES WAL, Checkpoints & Crash Recovery #

ULTSQL utilizes an ARIES-style Write-Ahead Log (`wal.log`) fortified with CRC32 checksums on every log entry. If the host machine loses power, crashes, or terminates abruptly, the engine automatically engages recovery upon startup during db.init():

  1. Analysis Phase: Identifies unwritten dirty pages in the slotted-page cache and locates active uncommitted transactions.
  2. Redo Phase: Replays committed page updates from the log sequentially to recover database state.
  3. Undo Phase: Rolls back operations from transactions that were active during the crash.
Pragma Tuning
-- Checkpoint active WAL into main database table pages
SET engine_option wal_checkpoint = true;

-- Enable automated autovacuum to reclaim fragmented slots
SET engine_option enable_autovacuum = true;

10. Converged NoSQL Document Store #

Store and query schema-less JSON documents using MongoDB-compatible collection APIs directly inside ULTSQL with zero configuration:

Dart NoSQL API
final users = db.collection('customers');

// 1. Insert single document (auto-generates 24-char unique _id)
final doc = await users.insertOne({
  'name': 'Sarah Connor',
  'email': '[email protected]',
  'profile': {
    'score': 95000,
    'tags': ['vip', 'verified'],
    'location': {'city': 'Los Angeles', 'country': 'USA'}
  }
});

// 2. High-speed batch ingestion (~92k docs/sec)
await users.insertMany([
  {'name': 'John Connor', 'profile': {'score': 88000, 'tags': ['member']}},
  {'name': 'Kyle Reese', 'profile': {'score': 72000, 'tags': ['veteran']}},
]);

// 3. Fast point-read by _id (direct B+ Tree index lookup: ~4Β΅s)
final found = await users.findOne({'_id': doc.id});
print('Found user: ${found?.getByPath('name')}');

// 4. Deep nested dotted-path queries with pagination & sorting
final cursor = users.find({
  'profile.score': {r'$gte': 80000},
  'profile.location.country': 'USA',
})
.sort({'profile.score': -1})
.limit(10);

final vips = await cursor.toList();

// 5. In-place atomic mutations ($set, $inc, $unset, $push)
await users.updateOne(
  filter: {'_id': doc.id},
  update: {
    r'$set': {'profile.location.city': 'San Francisco'},
    r'$inc': {'profile.score': 500},
  },
);

// 6. Delete document
await users.deleteOne({'name': 'Kyle Reese'});

Supported MongoDB Filter Operators

Filter Operator Meaning Example Syntax
$eq / $ne Equality / Inequality {'role': {'$eq': 'admin'}}
$gt / $gte Greater than / Greater or equal {'profile.score': {'$gte': 90000}}
$lt / $lte Less than / Less or equal {'age': {'$lt': 30}}
$in / $nin Array membership {'role': {'$in': ['admin', 'dev']}}
$exists Key existence check {'profile.location': {'$exists': true}}
$regex Regular expression match {'email': {'$regex': r'@domain\.io$'}}

11. Redis-Style Embedded Key-Value Engine #

ULTSQL embeds an ultra-high-throughput Key-Value caching engine with in-memory hot lookups (876,000+ ops/sec), atomic WAL persistence, TTL expiration, and atomic counters:

Dart KV API
// 1. Set key with TTL expiration
await db.kv.set('session:user_101', 'token_xyz123', ttl: const Duration(hours: 2));

// 2. High-speed hot in-memory get (< 1.5 Β΅s latency)
final token = await db.kv.get('session:user_101');

// 3. Batch set in single WAL transaction (~342k ops/sec)
await db.kv.mset({
  'config:timeout': 30,
  'config:max_conns': 100,
  'config:endpoint': 'https://api.ultsql.io',
});

// 4. Multi-key read
final configs = await db.kv.mget(['config:timeout', 'config:max_conns']);

// 5. Atomic increment and decrement counters
final views = await db.kv.incr('stats:page_views');      // 1
final views5 = await db.kv.incr('stats:page_views', 5);   // 6
final active = await db.kv.decr('stats:page_views', 2);   // 4

// 6. Pattern wildcard key scanning
final keys = await db.kv.keys(pattern: 'config:*');

// 7. Delete key
await db.kv.delete('session:user_101');

12. Cross-Model SQL ↔ NoSQL Bridge #

Query NoSQL document collections directly from SQL using the collection('name') function and dotted navigation operators (-> and ->>), or join relational SQL tables with schema-less collections in the same query:

Cross-Model SQL
-- Query document collection using relational SQL with dotted navigation
SELECT 
  _id,
  doc->>'serial' AS dev_serial,
  (doc->'telemetry'->>'battery')::INT AS battery
FROM collection('devices')
WHERE (doc->'telemetry'->>'battery')::INT > 80;

-- Join a relational table with a NoSQL document collection
SELECT 
  o.order_id,
  o.amount,
  u.doc->>'name' AS customer_name,
  u.doc->>'email' AS customer_email
FROM orders o
JOIN collection('customers') u ON o.customer_id = u._id
WHERE o.amount > 100.0;

14. Security, Encryption & Envelopes #

ULTSQL provides enterprise-grade data security at rest and in transit:

  • AES-256-GCM Authenticated Encryption: Encrypt database files, pages, and WAL records with military-grade authenticated ciphers.
  • Dual Authenticated Envelopes: Choose between speed-optimized and security-hardened cryptographic envelope modes.
  • Tamper Traps: Any unauthorized bit modification on disk triggers active tamper trap detection, raising DatabaseIntegrityException.
  • Zeroization: Cryptographic keys in memory are automatically zeroized upon database closure.
Encrypted Storage
// Open encrypted database with secret passphrase
final db = Database(
  './secure_db',
  passphrase: 'super_secret_master_key_99',
);
await db.init();

15. PostgreSQL Wire Protocol Server #

ULTSQL can run as a standalone or embedded network database daemon implementing the PostgreSQL Wire Protocol v3.0 (port 5432). Connect using existing tools like psql, DBeaver, TablePlus, or native drivers across all languages:

Dart Server Daemon
import 'package:ultsql/ultsql.dart';

void main() async {
  final db = Database('./production_db');
  await db.init();

  // Start PGWire TCP server on port 5432
  final server = PgWireServer(db, port: 5432);
  await server.start();
  print('ULTSQL PostgreSQL Wire Server listening on port 5432...');
}
Connect with psql CLI
# Connect using standard PostgreSQL CLI
psql -h localhost -p 5432 -U postgres -d ultsql

# Output:
# Welcome to psql 16.2.
# Connected to: ULTSQL Engine v1.0.32 (Pure Dart)
# ultsql=> SELECT 1 + 1;

Distributed Raft Consensus & Delta-CRDT Replication

ULTSQL includes production-grade clustering and synchronization:

  • Raft Consensus Engine: Multi-node clustering with leader election, randomized election timers (150–300ms), term leases, heartbeat loops, and Quorum Log Replication with state machine callbacks.
  • Delta-CRDT with Hybrid Logical Clocks (HLC): Offline peer-to-peer state merging with causal ordering and tombstone deletion propagation.
Raft Cluster Setup
final cluster = RaftCluster('node-1', peerNodes: ['node-2', 'node-3']);
await cluster.start(); // Initiates leader election and quorum replication

16. Embedded REST Server #

Need HTTP access from web applications or serverless lambdas? Start the built-in REST server:

Dart REST Daemon
// Defaults to strictly 127.0.0.1 for local network security
final rest = RestServer(db, host: '127.0.0.1', port: 8080);
await rest.start();
print('REST API active on http://127.0.0.1:8080');

// Or launch from terminal via: ultsql dev --db ./app.ultsql
Method Route Payload Description
POST /api/query {"sql": "SELECT * FROM users"} Executes SQL query and returns JSON rows
GET /api/health None Returns engine health, uptime, and page cache stats
GET /api/metrics None Returns Prometheus-compatible telemetry metrics

17. CLI & REPL Commands #

The ultsql CLI provides an interactive REPL with syntax highlighting, autocomplete, dot-commands, and headless CI/CD batch execution:

Terminal Commands
# 1. Launch interactive REPL
ultsql ./app.ultsql

# 2. Local development server on 127.0.0.1:8080 with live logs
ultsql dev --db ./app.ultsql

# 3. Production REST & OpenAPI daemon
ultsql serve --host 127.0.0.1 --port 8080 --db ./app.ultsql

# 4. Interactive dot-commands inside REPL:
.tables                          # List all tables and row counts
.schema users                    # Display CREATE TABLE DDL
.collections                     # List all NoSQL document collections
.kv list config:*                # Scan KV keys matching pattern
.mode json                       # Switch output mode (box, json, csv, markdown)
.quit                            # Exit REPL

# 5. Headless CI/CD execution with JSON output piped to jq
ultsql ./app.ultsql -m json -c "SELECT * FROM users;" | jq .

# 6. Ingest via MongoDB syntax directly in CLI
db.users.insertOne({"name": "Diana", "role": "admin"})

18. Native Multi-Language SDK Bindings #

Native client SDKs connect seamlessly to embedded or remote ULTSQL instances. For Python and Node.js, the package operates as a Client SDK that connects to a server or manages an opt-in local engine helper process on loopback (127.0.0.1) with automatic process reuse and clean exit hooks:

Python / Node.js Application  β†’  UltSQL Client SDK  β†’  local UltSQL engine (127.0.0.1)  β†’  database file
const { UltSQL, UltSQLClient } = require('ultsql');

// Mode 1: Convenient Local Mode (auto-starts engine on 127.0.0.1 with dynamic port)
const db = await UltSQL.open('./app.ultsql', { auto_start: true });
console.log(await db.status()); // { status: 'online', host: '127.0.0.1', port: 54321, pid: ... }

await db.sql('CREATE TABLE users (id INT PRIMARY KEY, name TEXT);');
await db.insert('users', { id: 1, name: 'Alice' });
const res = await db.query('users');
console.log(res.rows);

// Clean shutdown (also auto-cleaned on process exit)
db.close();

// Mode 2: Client Mode (connecting to existing server)
const remote = new UltSQLClient('http://127.0.0.1:8080');

19. Engine Configuration & Pragmas #

Fine-tune runtime engine parameters via SQL pragmas or Dart constructors:

Option Default Values Description
page_size 4096 1024–65536 Storage page size in bytes
cache_capacity 10000 100–1000000 Maximum pages retained in hot LRU cache
wal_synchronous NORMAL OFF, NORMAL, FULL WAL fsync flush behavior on commit
enable_autovacuum true true, false Automatic background slot compaction
auto_create_indexes false true, false Autonomous index creation based on query telemetry

20. Errors, Troubleshooting & FAQ #

Frequently Encountered Exceptions

Exception Class Cause Resolution
DatabaseLockException Another process holds an exclusive lock on the database file. Ensure only one process opens the database in read-write mode, or use PGWire/REST server for multi-process concurrency.
DatabaseIntegrityException CRC32 checksum mismatch or disk tampering detected. Check disk hardware integrity; restore from WAL or backup snapshot.
TableNotFoundException The referenced table or collection does not exist in the catalog. Verify spelling or run CREATE TABLE / db.collection('name') before querying.

Frequently Asked Questions

Q: Does ULTSQL use SQLite underneath?
No. ULTSQL contains zero lines of SQLite code and zero native C libraries. The B+ Tree, slotted-page layout, ARIES WAL, query parser, JIT compiler, and HNSW vector index are written 100% in pure Dart.
Q: Can I use ULTSQL on iOS, Android, macOS, Linux, and Windows?
Yes! Because ULTSQL is pure Dart, it compiles natively to ARM64 and x86_64 across iOS, Android, macOS, Linux, and Windows without platform channel bridging or FFI configuration.

21. Model Context Protocol (MCP) Server & AI Agent Skills #

ULTSQL includes native, first-class support for AI agent integration via the standardized Model Context Protocol (MCP) (JSON-RPC over stdio) and official agent skills. This allows AI assistants such as Claude Desktop, Cursor, Antigravity, and VS Code Roo Code to execute embedded database operations autonomously.

Starting the MCP Server

Terminal
# Start via standalone CLI
ultsql mcp

# Or start via Dart SDK directly
dart run bin/ultsql_mcp_server.dart

Client Configuration (Claude Desktop / Cursor / Antigravity)

mcp.json / claude_desktop_config.json
{
  "mcpServers": {
    "ultsql": {
      "command": "dart",
      "args": ["run", "bin/ultsql_mcp_server.dart"]
    }
  }
}

Available MCP Tools

Tool Name Description
ultsql_query Executes ANSI SQL, DDL, DML, CTEs, Window functions, and PL/SQL procedural blocks.
ultsql_describe_schema Introspects tables, views, columns, types, primary keys, and real-time row counts.
ultsql_batch_insert Vector-fast batch ingestion (up to 4.07M+ rows/sec) directly into 4KB slotted pages.
ultsql_doc_insert Stores schema-less JSON documents with auto-generated UUIDs and secondary indexes.
ultsql_doc_find Queries documents with rich MongoDB-style filters ($gt, $in, dotted paths).
ultsql_kv_set / ultsql_kv_get Sub-microsecond key-value caching with optional TTL expiration.
ultsql_vector_search Sub-2ms HNSW k-NN nearest-neighbor vector similarity search (cosine, l2, dot).
ultsql_explain Generates physical Volcano iterator execution plan diagnostics (EXPLAIN).
🧠 Agent Skill Auto-Discovery
The repository contains an official Agent Skill at .agents/skills/ultsql/SKILL.md. AI agents automatically discover and activate this skill when working on local storage, data analytics, or embedding search tasks.
ESC
βœ“ Copied