SMRT Collections

SmrtCollection provides a standardized interface for managing sets of persistent SmrtObject instances. It handles database setup, schema generation, and provides a fluent API for querying objects.

Creating Collections

Static Factory (Required)

Collections must be created using the static create() factory method.

import { SmrtCollection, SmrtObject, smrt } from '@happyvertical/smrt-core';

@smrt()
class Product extends SmrtObject {
  name: string = '';
  price: number = 0.0;
}

class ProductCollection extends SmrtCollection<Product> {
  static readonly _itemClass = Product;
}

// Create collection via static factory
const products = await ProductCollection.create({
  db: { type: 'sqlite', url: 'products.db' },
  ai: { provider: 'openai', apiKey: process.env.OPENAI_API_KEY }
});

// Wrong - constructor is protected
const collection = new ProductCollection(options); // Error

CRUD Operations

Create

Creates a new object instance, generates an ID, and saves to database.

const product = await products.create({
  name: 'Widget',
  price: 29.99
});
// product.id is now a UUID
// product is saved to database

For STI (Single Table Inheritance) classes, specify _meta_type:

const meeting = await events.create({
  _meta_type: 'Meeting',
  title: 'Team Standup',
  roomNumber: '101'
});

Get

Retrieves a single object by ID, slug, or filter criteria.

// By UUID
const product = await products.get('uuid-123-456');

// By slug
const product = await products.get('widget-pro');

// By filter
const product = await products.get({ name: 'Widget' });

Update

Objects are updated via the save() method after modification:

const product = await products.get('uuid-123');
product.price = 39.99;
await product.save();

Delete

const product = await products.get('uuid-123');
await product.delete();

getOrUpsert

Get existing object or create if not found:

const product = await products.getOrUpsert(
  { slug: 'widget-pro', context: '/products' },
  { name: 'Widget Pro', price: 49.99 }  // defaults for new object
);

Querying

list()

Query objects with filtering, sorting, and pagination.

const results = await products.list({
  where: { status: 'active' },
  orderBy: 'created_at DESC',
  limit: 20,
  offset: 0
});

WHERE Operators

Operators are specified in the field name:

Sorting

// Single field
await products.list({ orderBy: 'price DESC' });

// Multiple fields
await products.list({ orderBy: ['category ASC', 'price DESC'] });

Pagination

await products.list({
  limit: 20,
  offset: 40  // Skip first 40 records
});

count()

const total = await products.count({});
const active = await products.count({ where: { status: 'active' } });

Batch Operations

listByIds()

Fetch multiple objects by ID in a single query:

// Single query, not N queries
const profiles = await profileCollection.listByIds([
  'id-1', 'id-2', 'id-3', 'id-4', 'id-5'
]);

N+1 Query Prevention

Eager Loading

Use include to pre-load relationships in a single query:

// N+1 problem: 1 + 100 queries
const orders = await orderCollection.list({ limit: 100 });
for (const order of orders) {
  await order.loadRelated('customerId'); // 100 separate queries
}

// Solution: 1 query with JOINs
const orders = await orderCollection.list({
  limit: 100,
  include: ['customerId', 'productId']
});

for (const order of orders) {
  const customer = order.getRelated('customerId'); // Already loaded
  const product = order.getRelated('productId');   // Already loaded
}

listByIds Pattern

When you have a list of IDs from another query:

// Get all customer IDs from orders
const customerIds = orders.map(o => o.customerId);

// Single batched query
const customers = await customerCollection.listByIds(customerIds);

// Build lookup map
const customerMap = new Map(customers.map(c => [c.id, c]));

Raw SQL Queries

For complex queries that the standard API cannot express:

// NOT EXISTS pattern
const meetingsWithoutRecaps = await meetingCollection.query(`
  SELECT m.* FROM meetings m
  WHERE m.start_date < datetime('now')
  AND NOT EXISTS (
    SELECT 1 FROM contents c
    WHERE c.meeting_id = m.id
    AND c._meta_type = 'MeetingRecap'
  )
  ORDER BY m.start_date DESC
  LIMIT ?
`, [10]);

// JOIN query
const products = await productCollection.query(`
  SELECT p.* FROM products p
  INNER JOIN categories c ON p.category_id = c.id
  WHERE c.name = ? AND p.price > ?
  ORDER BY p.price ASC
`, ['Electronics', 100]);

Database Adapters

SMRT supports three database backends:

SQLite

Default adapter. Uses LibSQL client.

const collection = await ProductCollection.create({
  db: { type: 'sqlite', url: 'products.db' }
});

// In-memory
const collection = await ProductCollection.create({
  db: { type: 'sqlite', url: ':memory:' }
});

// Remote LibSQL/Turso
const collection = await ProductCollection.create({
  db: {
    type: 'sqlite',
    url: 'libsql://your-db.turso.io',
    authToken: process.env.TURSO_AUTH_TOKEN
  }
});

PostgreSQL

const collection = await ProductCollection.create({
  db: { type: 'postgres', url: 'postgres://user:pass@host:5432/db' }
});

DuckDB / JSON

const collection = await ProductCollection.create({
  db: {
    type: 'json',
    url: './data',
    writeStrategy: 'immediate'
  }
});

Transactions

Callback-based Transactions

await db.transaction(async (tx) => {
  await tx.insert('orders', { id: 'ord-1', total: 100 });
  await tx.insert('order_items', { orderId: 'ord-1', product: 'Widget' });
  // Commits on success, rolls back on error
});

Manual Transaction Control

const tx = await db.beginTransaction();
try {
  await tx.insert('orders', { id: 'ord-1', total: 100 });
  await tx.insert('order_items', { orderId: 'ord-1', product: 'Widget' });
  await tx.commit();
} catch (error) {
  await tx.rollback();
  throw error;
}

Context Memory

Collections can store learned patterns for reuse:

// Remember a parsing strategy
await documentCollection.remember({
  scope: 'parser/default',
  key: 'selector',
  value: { pattern: '.content article' },
  confidence: 0.8
});

// Recall with confidence threshold
const strategy = await documentCollection.recall({
  scope: 'parser/default',
  key: 'selector',
  minConfidence: 0.5
});

// Recall with ancestor fallback
const strategy = await documentCollection.recall({
  scope: 'parser/specific',
  key: 'selector',
  includeAncestors: true  // Falls back to parser/, then global
});

Semantic Search

For collections with embedding configuration:

@smrt({
  embeddings: {
    fields: ['content', 'title'],
    dimensions: 1536,
    provider: 'openai'
  }
})
class Article extends SmrtObject {
  title: string = '';
  content: string = '';
}

// Search by text
const results = await articles.semanticSearch('machine learning trends', {
  limit: 10,
  minSimilarity: 0.7
});

// Find similar objects
const similar = await articles.findSimilar(article, {
  limit: 5,
  excludeSelf: true
});

Best Practices

1. Always Use Static Factory

// Correct
const collection = await MyCollection.create(options);

// Wrong
const collection = new MyCollection(options);

2. Define _itemClass

class MyCollection extends SmrtCollection<MyObject> {
  static readonly _itemClass = MyObject; // Required
}

3. Use listByIds for Batch Lookups

// Good - single query
const profiles = await profileCollection.listByIds(ids);

// Bad - N queries
for (const id of ids) {
  const profile = await profileCollection.get(id);
}

4. Use include for Relationships

// Good - pre-loaded
const orders = await orderCollection.list({
  include: ['customerId']
});

// Bad - N+1
const orders = await orderCollection.list({});
for (const order of orders) {
  await order.loadRelated('customerId');
}

5. Share Database Connections

// Parent creates database
const parent = await ParentCollection.create({ db: 'app.db' });

// Child shares connection
const child = await ChildCollection.create({ db: parent.options.db });