npm package: @onchaindb/sdk
Description: TypeScript SDK for OnChainDB - Decentralized database built on Celestia
Author: OnChainDB Team
Latest version: 4.0.1

# OnDB TypeScript SDK

A TypeScript SDK for OnDB — the decentralized database built on Celestia with x402 payment protocol integration.

## Installation

```bash
npm install @onchaindb/sdk
```

## Quick Start

```typescript
import { createClient } from '@onchaindb/sdk';

const db = createClient({
  endpoint: 'https://api.onchaindb.io',
  appId: 'my_app',
  appKey: 'your_app_key'
});

// Store data
await db.store({
  collection: 'messages',
  data: [{ message: 'Hello OnDB!', author: 'alice' }]
});

// Query data
const result = await db.queryBuilder()
  .collection('messages')
  .whereField('author').equals('alice')
  .selectAll()
  .limit(10)
  .execute();

console.log(result.records);
```

## Configuration

```typescript
interface OnDBConfig {
  endpoint: string;      // OnDB server endpoint
  appId?: string;        // Application ID
  appKey?: string;       // App key for write operations (X-App-Key header)
  agentKey?: string;     // Agent key with Pay permission (X-Agent-Key header)
  timeout?: number;      // Request timeout in ms (default: 30000)
  retryCount?: number;   // Retry attempts (default: 3)
  retryDelay?: number;   // Retry delay in ms (default: 1000)
}
```

### Agent Keys

Keys with the `Pay` permission are Agent Keys — they can pay other apps inline using USDC via EIP-3009.

```typescript
const db = createClient({
  endpoint: 'https://api.onchaindb.io',
  appId: 'my_app',
  agentKey: 'agent_key_with_pay_permission'
});
```

## Storing Data

```typescript
// Basic store
await db.store({
  collection: 'tweets',
  data: [
    { id: 'tweet_1', content: 'Hello!', author: 'alice' },
    { id: 'tweet_2', content: 'World!', author: 'bob' }
  ]
});

// Store with x402 payment callback
await db.store(
  { collection: 'tweets', data: [{ content: 'Paid tweet' }] },
  async (quote) => {
    // quote is an X402Quote — sign and broadcast payment via your wallet
    const txHash = await wallet.sendTransaction({
      to: quote.payTo,
      value: quote.maxAmountRequired
    });
    return {
      txHash,
      network: quote.network,
      sender: wallet.address,
      chainType: quote.chainType,
      paymentMethod: quote.paymentMethod
    };
  }
);
```

## Query Builder

The SDK provides a fluent query builder for constructing queries. See the [Query SDK Reference](./src/query-sdk/README.md) for the full operator and aggregation reference.

### Basic Queries

```typescript
const result = await db.queryBuilder()
  .collection('users')
  .whereField('status').equals('active')
  .selectFields(['id', 'name', 'email'])
  .limit(50)
  .execute();

result.records.forEach(user => console.log(user.name, user.email));
```

### All Field Operators

```typescript
// Comparison
.equals(value)           .notEquals(value)
.greaterThan(value)      .greaterThanOrEqual(value)
.lessThan(value)         .lessThanOrEqual(value)
.between(min, max)

// String
.contains(value)         .startsWith(value)        .endsWith(value)
.regExpMatches(pattern)
.includesCaseInsensitive(value)
.startsWithCaseInsensitive(value)
.endsWithCaseInsensitive(value)

// Array
.in(values)              .notIn(values)

// Existence
.exists()    .notExists()    .isNull()    .isNotNull()
.isTrue()    .isFalse()

// Network / security
.isLocalIp()    .isExternalIp()    .inCountry(code)    .cidr(range)
.b64(value)     .inDataset(name)
```

### Complex Queries with Logical Operators

```typescript
import { LogicalOperator } from '@onchaindb/sdk';

const result = await db.queryBuilder()
  .collection('posts')
  .find(builder =>
    LogicalOperator.And([
      builder.field('published').isTrue(),
      LogicalOperator.Or([
        builder.field('category').equals('tech'),
        builder.field('views').greaterThan(1000)
      ]),
      LogicalOperator.Not([
        builder.field('archived').isTrue()
      ])
    ])
  )
  .selectFields(['title', 'content', 'author'])
  .limit(20)
  .execute();
```

### Sorting & Pagination

```typescript
const result = await db.queryBuilder()
  .collection('posts')
  .whereField('published').isTrue()
  .orderBy('created_at', 'DESC')
  .limit(20)
  .offset(40)
  .selectAll()
  .execute();
```

### Server-Side Aggregations

```typescript
// Count
const total = await db.queryBuilder()
  .collection('users')
  .whereField('active').isTrue()
  .count();

// Sum / average
const revenue = await db.queryBuilder().collection('orders').sumBy('amount');
const avg     = await db.queryBuilder().collection('orders').avgBy('amount');

// Group by
const byCountry = await db.queryBuilder()
  .collection('users')
  .groupBy('country')
  .count();
// { "USA": 150, "UK": 75, ... }
```

## Server-Side JOINs

JOINs execute on the backend in a single request. Use `$data.fieldname` to reference parent record fields.

### joinOne (one-to-one)

```typescript
const result = await db.queryBuilder()
  .collection('tweets')
  .joinOne('author_info', 'users')
    .onField('address').equals('$data.author')
    .selectFields(['display_name', 'avatar_url', 'verified'])
    .build()
  .selectAll()
  .limit(20)
  .execute();

// result.records[0].author_info => { display_name, avatar_url, verified } | null
```

### joinMany (one-to-many)

```typescript
const result = await db.queryBuilder()
  .collection('users')
  .whereField('address').equals(userAddress)
  .joinMany('tweets', 'tweets')
    .onField('author').equals('$data.address')
    .selectFields(['id', 'content', 'created_at'])
    .build()
  .selectAll()
  .execute();

// result.records[0].tweets => [{ id, content, created_at }, ...]
```

### Nested JOINs

```typescript
const result = await db.queryBuilder()
  .collection('tweets')
  .joinMany('replies', 'tweets')
    .onField('reply_to_id').equals('$data.id')
    .selectAll()
    .joinOne('author_info', 'users')
      .onField('address').equals('$data.author')
      .selectFields(['display_name', 'avatar_url'])
      .build()
    .build()
  .selectAll()
  .execute();
```

## Model API

`db.model<T>(collection)` returns a typed delegate with structured query and write methods — a clean, declarative API on top of `QueryBuilder` and `store`.

```typescript
interface User {
  id: string;
  email: string;
  role: string;
  age: number;
  deletedAt?: number;
}

const users = db.model<User>('users');
```

### Reading

```typescript
// findMany — returns T[]
const active = await users.findMany({
  where: { role: 'admin', age: { gte: 18 } },
  select: { id: true, email: true },
  orderBy: { age: 'desc' },
  take: 50,
  skip: 0,
});

// findFirst — returns T | null
const user = await users.findFirst({ where: { email: 'alice@example.com' } });

// findUnique — returns T | null
const user = await users.findUnique({ where: { id: 'usr_1' } });
```

### Where clause

Top-level fields are implicitly ANDed. All field operators from `QueryBuilder` are available:

```typescript
const results = await users.findMany({
  where: {
    // shorthand equality
    role: 'admin',

    // field operators
    age:       { gte: 18, lt: 65 },
    email:     { endsWith: '@example.com' },
    status:    { in: ['active', 'pending'] },
    bio:       { containsCaseInsensitive: 'engineer' },
    ipAddress: { isLocalIp: true },
    score:     { between: { from: 10, to: 90 } },
    tags:      { keywords: ['rust', 'typescript'] },

    // logical operators
    AND: [{ age: { gte: 18 } }, { age: { lt: 65 } }],
    OR:  [{ role: 'admin' }, { role: 'moderator' }],
    NOT: [{ status: 'banned' }],
  }
});
```

#### Full FieldFilter reference

| Operator | Description |
|----------|-------------|
| `equals` | Exact equality |
| `not` | Not equal |
| `in` | Case-insensitive membership |
| `notIn` | Exclude values |
| `inDataset` | Case-sensitive membership (`string[]`) |
| `lt`, `lte`, `gt`, `gte` | Numeric comparisons |
| `between` | Exclusive range `{ from, to }` |
| `contains` | Substring (case-sensitive) |
| `startsWith` / `endsWith` | Prefix / suffix |
| `containsCaseInsensitive` / `startsWithCaseInsensitive` / `endsWithCaseInsensitive` | Case-insensitive string ops |
| `regex` | Regular expression match |
| `isNotNull` | Field is not null |
| `exists` | `true` → field exists, `false` → field absent |
| `isLocalIp` / `isExternalIp` | IP classification |
| `inCountry` | GeoIP country code |
| `cidr` | CIDR range check (`string` or `string[]`) |
| `keywords` | Any keyword matches (case-insensitive substring) |
| `b64` | Base64-encoded value match |

### Exclude soft-deleted records

```typescript
// true → filters out records where deletedAt exists
const live = await users.findMany({
  where: { role: 'admin' },
  excludeDeleted: true,
});

// string → use a custom delete marker field
const live = await users.findMany({
  excludeDeleted: 'archivedAt',
});
```

### Writing

OnDB is an append-only log with server-side deduplication on unique fields. "Update" and "delete" are implemented by writing a new version of the document.

```typescript
// Create one record
await users.create({ data: { id: 'usr_1', email: 'alice@example.com', role: 'user', age: 30 } });

// Create many records in a single request
await users.createMany({
  data: [
    { id: 'usr_2', email: 'bob@example.com', role: 'user', age: 25 },
    { id: 'usr_3', email: 'carol@example.com', role: 'admin', age: 40 },
  ]
});

// Update — finds the record, merges patch, stores the new version
await users.updateDocument(
  { id: 'usr_1' },          // identifier (WhereInput)
  { role: 'admin', age: 31 } // patch
);

// Soft-delete — sets deletedAt: Date.now() on the existing record
await users.deleteDocument({ id: 'usr_1' });

// Soft-delete with extra patch fields
await users.deleteDocument({ id: 'usr_1' }, { deletedBy: 'moderator' });
```

### Count & Aggregate

```typescript
const total = await users.count({ where: { role: 'admin' } });

const stats = await users.aggregate({
  where: { role: 'user' },
  _count: true,
  _avg:   { age: true },
  _sum:   { age: true },
  _min:   { age: true },
  _max:   { age: true },
});
// { _count: 42, _avg_age: 28.5, _sum_age: 1197, _min_age: 18, _max_age: 64 }
```

## Collection Management

### Create & Sync Collections

```typescript
import { SimpleCollectionSchema } from '@onchaindb/sdk';

const schema: SimpleCollectionSchema = {
  name: 'users',
  fields: {
    email:          { type: 'string', index: true },
    age:            { type: 'number', index: true },
    status:         { type: 'string', index: true, indexType: 'hash' },
    bio:            { type: 'string', index: true, indexType: 'fulltext' },
    'address.city': { type: 'string', index: true },
  },
  useBaseFields: true, // Auto-index id, createdAt, updatedAt, deletedAt
};

// Create collection with indexes
await db.createCollection(schema);

// Sync: creates new indexes, removes none (broker handles upsert)
await db.syncCollection(schema);
```

### Sharding

Partition large collections across multiple files for massive datasets.

```typescript
import { SimpleCollectionSchemaWithSharding, ShardingStrategy } from '@onchaindb/sdk';

const schema: SimpleCollectionSchemaWithSharding = {
  name: 'orderbook_snapshots',
  fields: {
    market:    { type: 'string', index: true },
    timestamp: { type: 'number', index: true },
    mid_price: { type: 'number' }
  },
  sharding: {
    keys: [
      { field: 'market',    type: 'discrete' },
      { field: 'timestamp', type: 'time_range', granularity: 'hour' }
    ],
    enforce_in_queries: true
  }
};

// syncCollection applies sharding automatically if present
await db.syncCollection(schema);

// Or configure sharding separately
await db.setupSharding('orderbook_snapshots', schema.sharding!);
```

### Update & Delete Collections

```typescript
// Update collection properties
await db.updateCollection('users', {
  public: true,
  description: 'User profiles',
  default_sort_column: 'created_at',
  collection_type: 'local' // 'local' | 'offline' | 'api' | 'hybrid'
});

// Delete a collection
await db.deleteCollection('old_collection');

// Get collection info by ID (ID format: "{appId}_{index}", from listCollections)
const info = await db.getCollectionInfo('myapp_0');
// { id, name, namespace, primary_column, sort_column, status, collection_type,
//   document_count, size_kb, created_at, last_modified }
```

## Data Retention

Configure per-collection retention policies. First 30 days are always free.

```typescript
// Get current retention config
const config = await db.getRetention('orders');
// { collection, retention_days, monthly_cost_per_kb, last_cleanup, status }

// Set retention (null = permanent storage)
const result = await db.setRetention('orders', { retention_days: 90 });
// { message, config: RetentionConfig }

// Get cost estimate for all collections
const costs = await db.getRetentionCost();
// { app_id, collections: CollectionRetentionCost[], total_monthly_cost_tia, projected_next_month_cost_tia }
```

## SQL Interface

Query and insert data using SQL syntax. Table references use `app::collection` format.

```typescript
// SQL query
const result = await db.sql(
  'SELECT * FROM myapp::orders WHERE amount > 100 ORDER BY created_at DESC LIMIT 50'
);
// { data: any[], count, query, app_id, collection }

// SQL query with history (includes all versions of records)
const withHistory = await db.sql(
  'SELECT * FROM myapp::orders',
  { includeHistory: true }
);

// SQL insert
await db.sqlInsert(
  "INSERT INTO myapp::orders (id, amount, status) VALUES ('ord_1', 250, 'pending')"
);
```

## Predefined Queries

Named, parameterized queries that can be executed publicly without authentication.

```typescript
import { CreateQueryRequest } from '@onchaindb/sdk';

// Create a predefined query
const created = await db.createQuery({
  name: 'active_markets',
  source_collection: 'orderbook_snapshots',
  base_query: { find: { status: { $eq: 'active' } } },
  parameters: [
    {
      name: 'market',
      field_path: 'market',   // maps to find.market.$eq
      required: true,
      description: 'Market symbol (e.g. BTC, ETH)'
    },
    {
      name: 'min_price',
      field_path: 'price.$gte',
      default: 0,
      description: 'Minimum price filter'
    }
  ],
  description: 'Query active orderbook snapshots by market'
});

// List all queries
const queries = await db.listQueries();
// QueryDefinition[]

// Get a specific query definition
const query = await db.getQuery('active_markets');

// Execute a predefined query (public endpoint — no auth required)
const data = await db.executeQuery(
  'active_markets',
  { market: 'BTC', min_price: '1000' }  // params as Record<string, string>
);
// { success, query_name, data, count, query_time_ms }

// Execute a specific version
const v2data = await db.executeQuery('active_markets', { market: 'ETH' }, 2);

// Delete a query
await db.deleteQuery('active_markets');
```

## Materialized Views

### Create via JSON Query

```typescript
const db_manager = db.database();

await db_manager.createView(
  'top_sellers',
  ['products', 'orders'],
  { find: { status: { $eq: 'completed' } }, sort_by: ['-total'], limit: 100 }
);
```

### Create via SQL

```typescript
await db_manager.createViewSql(
  'SELECT market, AVG(price) as avg_price FROM myapp::trades GROUP BY market',
  'live' // 'live' | 'lazy'
);
```

### Query & Manage Views

```typescript
// Query view data
const result = await db_manager.queryView<TradeStats>('top_sellers', {
  find: { avg_price: { $gte: 1000 } },
  sort_by: ['-avg_price'],
  limit: 20
});
// { success, view_name, data: T[], count, query_time_ms }

// Count records in a view
const count = await db_manager.countView('top_sellers', { market: 'BTC' });

await db_manager.listViews();
await db_manager.getView('top_sellers');
await db_manager.refreshView('top_sellers');
await db_manager.deleteView('top_sellers');
```

## API Collections

Collections backed by external APIs instead of on-chain data.

```typescript
const db_manager = db.database();

await db_manager.createApiCollection({
  name: 'live_prices',
  description: 'Real-time price feed from external API',
  api_config: {
    base_url: 'https://api.example.com',
    authentication: { type: 'bearer', token: 'xxx' },
    query_mapping: { path_template: '/v1/prices/{market}' },
    response_mapping: { records_path: 'data.prices' }
  }
});

const collections = await db_manager.listApiCollections();
const collection  = await db_manager.getApiCollection('live_prices');
await db_manager.deleteApiCollection('live_prices');
```

## Index Management

Access the `DatabaseManager` via `db.database()` for direct index control.

```typescript
const dbm = db.database();

// List all indexes
const indexes = await dbm.listIndexes();

// Create an index
await dbm.createIndex({
  name: 'users_email_idx',
  collection: 'users',
  field_name: 'email',
  index_type: 'btree'
});

// Create multiple indexes at once
await dbm.createIndexes('users', [
  { name: 'email_idx',    field_name: 'email',    index_type: 'btree' },
  { name: 'status_idx',   field_name: 'status',   index_type: 'hash' },
  { name: 'bio_idx',      field_name: 'bio',      index_type: 'fulltext' },
]);

// Create a price index (enables payment splits on write)
await dbm.createPriceIndex('orders', 'total_price', {
  app_owner_percentage: 0.80,
  platform_percentage: 0.20,
  app_owner_address: 'celestia1abc...'
});

// Drop an index by ID
await dbm.dropIndex('users_email_idx');
```

## Blob Storage

```typescript
// Upload
const upload = await db.uploadBlob({
  collection: 'images',
  blob: file,
  metadata: { user_address: userAddress }
});

// Wait for on-chain confirmation
const task = await db.waitForTaskCompletion(upload.ticket_id);
console.log('Blob ID:', upload.blob_id);

// Retrieve
const blob = await db.retrieveBlob({
  collection: 'images',
  blob_id: upload.blob_id
});
```

## Task Tracking

```typescript
const task = await db.getTaskStatus(ticketId);
// status: 'Pending' | 'PaymentBroadcast' | 'PaymentConfirming' |
//         'PaymentConfirmed' | 'StoringData' | 'Completed' | { Failed: { error } }

const completed = await db.waitForTaskCompletion(ticketId, 2000, 600000);
// Polls every 2s, times out after 600s
```

## Pricing Quotes

```typescript
const quote = await db.getPricingQuote({
  app_id: 'my_app',
  operation_type: 'write',
  size_kb: 10,
  collection: 'orders',
  data: { amount: 150 } // for price index calculation
});
// { total_cost, total_cost_utia, indexing_costs, ... }
```

## Error Handling

```typescript
import { OnDBError, ValidationError, PaymentRequiredError } from '@onchaindb/sdk';

try {
  await db.store({ collection: 'test', data: [{ value: 1 }] });
} catch (error) {
  if (error instanceof ValidationError)      console.log('Validation:', error.message);
  if (error instanceof PaymentRequiredError) console.log('Payment required:', error.paymentRequired);
  if (error instanceof OnDBError)            console.log(error.code, error.message);
}
```

## API Reference

### OnDBClient

| Method | Description |
|--------|-------------|
| `store(request, paymentCallback?)` | Store data on-chain |
| `model<T>(collection)` | Typed model delegate (structured queries & writes) |
| `queryBuilder()` | Fluent query builder |
| `createCollection(schema)` | Create collection with indexes |
| `syncCollection(schema)` | Sync indexes to schema (supports sharding) |
| `setupSharding(collection, sharding)` | Configure sharding on an existing collection |
| `deleteCollection(collection)` | Delete a collection |
| `updateCollection(collection, updates)` | Update collection properties |
| `getCollectionInfo(collectionId)` | Get collection details by ID |
| `getRetention(collection)` | Get retention config |
| `setRetention(collection, request)` | Set retention period |
| `getRetentionCost()` | Get retention cost estimate for all collections |
| `sql(query, options?)` | Execute SQL query |
| `sqlInsert(sql)` | Execute SQL insert |
| `listQueries()` | List predefined queries |
| `createQuery(request)` | Create a predefined query |
| `getQuery(name)` | Get query definition |
| `deleteQuery(name)` | Delete a predefined query |
| `executeQuery(name, params?, version?)` | Execute a predefined query |
| `createRelation(request)` | Create a collection relation |
| `createIndex(request)` | Create a single index |
| `getPricingQuote(request)` | Estimate storage cost |
| `uploadBlob(request, paymentCallback?)` | Upload binary file |
| `retrieveBlob(request)` | Download binary file |
| `getTaskStatus(ticketId)` | Get async task status |
| `waitForTaskCompletion(ticketId)` | Poll until task completes |
| `health()` | Server health check |
| `database(appId?)` | Access `DatabaseManager` |

### DatabaseManager (`db.database()`)

| Method | Description |
|--------|-------------|
| `listIndexes()` | List all indexes for the app |
| `createIndex(definition)` | Create an index |
| `createIndexes(collection, indexes)` | Bulk create indexes |
| `createPriceIndex(collection, field, config?)` | Create payment-split price index |
| `dropIndex(indexId)` | Delete an index by ID |
| `createView(name, sources, query)` | Create materialized view |
| `createViewSql(sql, refreshMode?)` | Create view from SQL |
| `queryView(viewName, query?)` | Query view data |
| `countView(viewName, filter?)` | Count view records |
| `listViews()` | List all views |
| `getView(name)` | Get view definition |
| `refreshView(name)` | Rebuild view |
| `deleteView(name)` | Delete a view |
| `createApiCollection(request)` | Create API-backed collection |
| `listApiCollections()` | List API collections |
| `getApiCollection(name)` | Get API collection details |
| `deleteApiCollection(name)` | Delete API collection |

### ModelDelegate (`db.model<T>(collection)`)

| Method | Description |
|--------|-------------|
| `findMany(args?)` | Query records, returns `T[]` |
| `findFirst(args?)` | First matching record or `null` |
| `findUnique(args)` | Alias for `findFirst` with a required `where` |
| `create(args)` | Store a single record |
| `createMany(args)` | Store multiple records in one request |
| `updateDocument(identifierFind, patch)` | Find, merge patch, re-store |
| `deleteDocument(identifierFind, patch?)` | Soft-delete by setting `deletedAt` |
| `count(args?)` | Count matching records |
| `aggregate(args)` | Server-side aggregations (`_count`, `_sum`, `_avg`, `_min`, `_max`) |

### QueryBuilder

| Method | Description |
|--------|-------------|
| `collection(name)` | Set target collection |
| `whereField(name)` | Start a field condition |
| `find(builderFn)` | Set complex logical conditions |
| `selectFields(fields)` | Select specific fields |
| `selectAll()` | Select all fields |
| `joinOne(alias, model)` | One-to-one server JOIN |
| `joinMany(alias, model)` | One-to-many server JOIN |
| `orderBy(field, dir?)` | Sort results |
| `limit(n)` | Limit result count |
| `offset(n)` | Skip results |
| `execute()` | Execute and return records |
| `executeUnique()` | Return latest single record |
| `count()` | Count matching records |
| `sumBy(field)` | Sum numeric field |
| `avgBy(field)` | Average numeric field |
| `maxBy(field)` | Maximum field value |
| `minBy(field)` | Minimum field value |
| `distinctBy(field)` | Distinct field values |
| `groupBy(field)` | Start grouped aggregation |
| `buildRawQuery()` | Inspect raw query object |
| `clone()` | Clone builder |

## License

MIT