Database Manager

const db = client.database(); // Uses appId from client config
// or
const db = client.database('specific-app-id');

const indexes = await db.listIndexes();

await db.createIndex(indexDefinition: Index);

interface Index {
  name: string;
  collection: string;
  field_name: string;
  index_type: 'btree' | 'hash' | 'fulltext' | 'composite' | 'price';
  fields?: string[];              // For composite indexes
  store_values?: boolean;
  unique_constraint?: boolean;
  sort_enabled?: boolean;
  price_config?: WriteIndexConfig;       // Write-time payment (price indexes)
  read_price_config?: ReadIndexConfig;   // Read-time payment
  creator_premium_config?: CreatorPremiumConfig; // Creator revenue sharing
}

// Hash index for equality lookups
await db.createIndex({
  name: 'idx_users_email',
  collection: 'users',
  field_name: 'email',
  index_type: 'hash',
  unique_constraint: true,
  store_values: true
});

// BTree index for range queries
await db.createIndex({
  name: 'idx_users_createdAt',
  collection: 'users',
  field_name: 'createdAt',
  index_type: 'btree'
});

// Fulltext index for search
await db.createIndex({
  name: 'idx_posts_content',
  collection: 'posts',
  field_name: 'content',
  index_type: 'fulltext'
});

await db.createIndexes('users', [
  { name: 'idx_email', field_name: 'email', index_type: 'hash', unique_constraint: true, store_values: true },
  { name: 'idx_created', field_name: 'createdAt', index_type: 'btree', store_values: true }
]);

await db.createWriteIndex('posts', 'author', {
  pricing_model: 'per_record',
  price_per_record: 500_000  // 0.50 USDC per write
});

await db.createReadIndex('articles', 'content', {
  pricing_model: 'per_access',
  price_per_access: 100_000  // 0.10 USDC per matched record
}, { indexType: 'fulltext' });

await db.createCreatorPremiumIndex('tracks', 'title', {
  creator_address_resolution: 'metadata.artist_address',
  creator_cut_model: 'per_value_percent',
  creator_cut_value: 10,          // 10% of the price field
  value_field_name: 'price_usdc'
});

await db.dropIndex('index-id');

await db.createView(
  'completed_orders',
  ['orders'],
  {
    find: { status: { is: 'completed' } },
    select: { id: true, total: true, customer: true }
  }
);

await db.createViewSql(
  'CREATE VIEW customer_revenue AS SELECT customer, SUM(total) as revenue FROM app::orders GROUP BY customer',
  'live'  // 'live' (auto-refresh) or 'lazy' (manual refresh)
);
// Note: createViewSql takes 2 parameters: (sql, refreshMode?)
// The view name is derived from the SQL statement

const result = await db.queryView<OrderSummary>('completed_orders', {
  find: { total: { greaterThan: 100 } },
  select: { id: true, total: true },
  sort_by: ['total'],
  limit: 50,
  offset: 0
});
// result: { success, view_name, data, count, query_time_ms }

const count = await db.countView('completed_orders', { total: { greaterThan: 100 } });

const views = await db.listViews();
// views: [{ name, source_collections, created_at }]

const view = await db.getView('completed_orders');

await db.refreshView('completed_orders');

await db.deleteView('completed_orders');

await db.createApiCollection({
  name: 'external_users',
  description: 'Users from external API',
  api_config: {
    base_url: 'https://api.example.com',
    authentication: { type: 'bearer', token: '...' },
    query_mapping: { /* ... */ },
    response_mapping: { /* ... */ }
  }
});

const collections = await db.listApiCollections();
const collection = await db.getApiCollection('external_users');
await db.deleteApiCollection('external_users');

const aggregatedView = {
  find: {},
  select: {},
  group_by: ['category'],
  aggregate: {
    total_sales: { '$sum': 'amount' },
    order_count: { '$count': '*' },
    avg_order: { '$avg': 'amount' }
  },
  sort_by: ['total_sales'],
  limit: 100
};

await db.createView('sales_by_category', ['orders'], aggregatedView);