bkend-data: Database Expert Skill

Complete database management for bkend.ai projects using MongoDB Atlas

1. Overview

bkend.ai provides a fully managed database layer built on MongoDB Atlas. Each project operates in complete data isolation, with built-in schema validation and Row-Level Security (RLS) policies.

Key characteristics:

• MongoDB Atlas backend with project-level isolation
• Schema validation enforced at the database level
• Row-Level Security (RLS) for fine-grained access control
• Automatic system fields on every record
• REST API and MCP tools for full database management

2. Data Model

2.1 Column Types (7 Types)

bkend.ai supports exactly 7 column types. There is no generic "number" type.

IMPORTANT: Do NOT use "number" as a column type. Use int for integers or double for decimals.

2.2 System Fields (Auto-Generated)

Every record automatically includes these system fields. Do NOT define them manually.

2.3 Constraints

Apply constraints to columns for data integrity:

2.4 Default Indexes

Every table is created with these indexes by default:

3. CRUD REST API

All data endpoints require authentication via the Authorization: Bearer <token> header and the x-project-id header.

3.1 Create Record

Single record:

POST /v1/data/:tableName
Content-Type: application/json

{
  "name": "John Doe",
  "email": "john@example.com",
  "age": 30,
  "role": "user"
}

Batch create (multiple records):

POST /v1/data/:tableName
Content-Type: application/json

{
  "records": [
    { "name": "Alice", "email": "alice@example.com", "age": 25 },
    { "name": "Bob", "email": "bob@example.com", "age": 28 }
  ]
}

Response:

{
  "success": true,
  "data": {
    "id": "rec_abc123",
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30,
    "role": "user",
    "createdBy": "usr_xyz",
    "createdAt": "2025-01-15T09:30:00Z",
    "updatedAt": "2025-01-15T09:30:00Z"
  }
}

3.2 Read One Record

GET /v1/data/:tableName/:id

Response:

{
  "success": true,
  "data": {
    "id": "rec_abc123",
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30,
    "role": "user",
    "createdBy": "usr_xyz",
    "createdAt": "2025-01-15T09:30:00Z",
    "updatedAt": "2025-01-15T09:30:00Z"
  }
}

3.3 List Records

GET /v1/data/:tableName?filter={...}&sort={...}&limit=20&cursor=last_id&search=keyword&searchType=partial

Query Parameters:

Response:

{
  "success": true,
  "data": [
    { "id": "rec_abc123", "name": "John Doe", "age": 30 },
    { "id": "rec_def456", "name": "Jane Smith", "age": 25 }
  ],
  "meta": {
    "total": 150,
    "limit": 20,
    "nextCursor": "rec_def456"
  }
}

3.4 Update Record

PUT /v1/data/:tableName/:id
Content-Type: application/json

{
  "name": "John Updated",
  "age": 31
}

Response:

{
  "success": true,
  "data": {
    "id": "rec_abc123",
    "name": "John Updated",
    "age": 31,
    "updatedAt": "2025-01-16T10:00:00Z"
  }
}

3.5 Delete Record

DELETE /v1/data/:tableName/:id

Response:

{
  "success": true,
  "data": {
    "id": "rec_abc123",
    "deleted": true
  }
}

3.6 Table Specification

Retrieve the full schema definition for a table:

GET /v1/data/:tableName/spec

Response:

{
  "success": true,
  "data": {
    "tableName": "users",
    "fields": [
      { "name": "name", "type": "string", "required": true },
      { "name": "email", "type": "string", "required": true, "unique": true },
      { "name": "age", "type": "int", "min": 0, "max": 150 },
      { "name": "role", "type": "string", "enum": ["user", "admin"], "default": "user" }
    ],
    "indexes": [
      { "name": "_id_", "fields": ["id"] },
      { "name": "idx_createdAt_desc", "fields": [{ "createdAt": -1 }] }
    ]
  }
}

4. Filtering

4.1 AND Filtering (Default)

Multiple conditions in the same filter object are combined with AND logic:

{
  "filter": {
    "status": { "$eq": "active" },
    "age": { "$gte": 18 }
  }
}

This returns records where status equals "active" AND age is greater than or equal to 18.

4.2 OR Filtering

Use the $or operator to combine conditions with OR logic:

{
  "filter": {
    "$or": [
      { "status": "active" },
      { "role": "admin" }
    ]
  }
}

This returns records where status equals "active" OR role equals "admin".

4.3 Filter Operators (10 Operators)

4.4 Search

Use query parameters for text search:

GET /v1/data/users?search=john&searchType=partial

• search: The keyword to search for
• searchType: "exact" for exact match, "partial" for contains match

5. Sorting & Pagination

5.1 Sorting

Specify sort order with field name and direction (1 for ascending, -1 for descending):

{
  "sort": { "createdAt": -1 }
}

Multiple sort fields:

{
  "sort": { "role": 1, "createdAt": -1 }
}

5.2 Pagination

bkend.ai uses cursor-based pagination for optimal performance:

GET /v1/data/users?limit=20&cursor=rec_last_id_value

• limit: Number of records per page (max 100, default 20)
• cursor: The id of the last record from the previous page

The response includes meta.nextCursor for fetching the next page. When nextCursor is null, there are no more pages.

Example pagination flow:

# First page
GET /v1/data/users?limit=20

# Next page (use nextCursor from previous response)
GET /v1/data/users?limit=20&cursor=rec_def456

# Continue until nextCursor is null

6. Relations

6.1 One-to-Many (1:N)

Store a reference ID in the child table:

Table: users
  - id (system)
  - name (string)
  - email (string)

Table: posts
  - id (system)
  - title (string)
  - content (string)
  - authorId (string)  ← references users.id

Join query to include related data:

GET /v1/data/posts?join=authorId

Response with joined data:

{
  "success": true,
  "data": [
    {
      "id": "post_001",
      "title": "My First Post",
      "content": "Hello world",
      "authorId": "usr_abc",
      "author": {
        "id": "usr_abc",
        "name": "John Doe",
        "email": "john@example.com"
      }
    }
  ]
}

6.2 Many-to-Many (N:M)

Use a junction table to model many-to-many relationships:

Table: posts
  - id (system)
  - title (string)

Table: tags
  - id (system)
  - name (string)

Table: post_tags (junction)
  - id (system)
  - postId (string)  ← references posts.id
  - tagId (string)   ← references tags.id

Query posts with their tags:

GET /v1/data/post_tags?filter={"postId":{"$eq":"post_001"}}&join=tagId

7. MCP Table Management Tools

Use these MCP tools for schema and table management operations:

7.1 Table Operations

Example: Create a table

Tool: backend_table_create
Arguments:
  tableName: "users"
  fields:
    - name: "name"
      type: "string"
      required: true
    - name: "email"
      type: "string"
      required: true
      unique: true
    - name: "age"
      type: "int"
      min: 0
      max: 150
    - name: "role"
      type: "string"
      enum: ["user", "admin"]
      default: "user"
    - name: "isActive"
      type: "bool"
      default: true

7.2 Field Management

Example: Add a field

Tool: backend_field_manage
Arguments:
  tableName: "users"
  action: "add"
  field:
    name: "bio"
    type: "string"
    max: 500

7.3 Index Management

Example: Create a compound index

Tool: backend_index_manage
Arguments:
  tableName: "users"
  action: "create"
  index:
    name: "idx_role_active"
    fields:
      - field: "role"
        direction: 1
      - field: "isActive"
        direction: 1

7.4 Schema Versioning

8. Frontend CRUD Pattern (TanStack Query)

Use the Query Key Factory pattern for consistent cache management:

// lib/queries/users.ts
import { useQuery, useMutation, useQueryClient } from '@tanstack/react-query';
import { bkendFetch } from '@/lib/bkend';

// Query Key Factory
export const userKeys = {
  all: ['users'] as const,
  lists: () => [...userKeys.all, 'list'] as const,
  list: (filters: Record<string, unknown>) =>
    [...userKeys.lists(), filters] as const,
  details: () => [...userKeys.all, 'detail'] as const,
  detail: (id: string) => [...userKeys.details(), id] as const,
};

// List users with filters
export function useUsers(filters: Record<string, unknown> = {}) {
  return useQuery({
    queryKey: userKeys.list(filters),
    queryFn: async () => {
      const params = new URLSearchParams();
      if (filters.filter) params.set('filter', JSON.stringify(filters.filter));
      if (filters.sort) params.set('sort', JSON.stringify(filters.sort));
      if (filters.limit) params.set('limit', String(filters.limit));
      if (filters.cursor) params.set('cursor', String(filters.cursor));

      const res = await bkendFetch(`/v1/data/users?${params.toString()}`);
      return res.json();
    },
  });
}

// Get single user
export function useUser(id: string) {
  return useQuery({
    queryKey: userKeys.detail(id),
    queryFn: async () => {
      const res = await bkendFetch(`/v1/data/users/${id}`);
      return res.json();
    },
    enabled: !!id,
  });
}

// Create user mutation
export function useCreateUser() {
  const queryClient = useQueryClient();
  return useMutation({
    mutationFn: async (data: Record<string, unknown>) => {
      const res = await bkendFetch('/v1/data/users', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(data),
      });
      return res.json();
    },
    onSuccess: () => {
      queryClient.invalidateQueries({ queryKey: userKeys.lists() });
    },
  });
}

// Update user mutation
export function useUpdateUser() {
  const queryClient = useQueryClient();
  return useMutation({
    mutationFn: async ({ id, data }: { id: string; data: Record<string, unknown> }) => {
      const res = await bkendFetch(`/v1/data/users/${id}`, {
        method: 'PUT',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(data),
      });
      return res.json();
    },
    onSuccess: (_, variables) => {
      queryClient.invalidateQueries({ queryKey: userKeys.detail(variables.id) });
      queryClient.invalidateQueries({ queryKey: userKeys.lists() });
    },
  });
}

// Delete user mutation
export function useDeleteUser() {
  const queryClient = useQueryClient();
  return useMutation({
    mutationFn: async (id: string) => {
      const res = await bkendFetch(`/v1/data/users/${id}`, {
        method: 'DELETE',
      });
      return res.json();
    },
    onSuccess: () => {
      queryClient.invalidateQueries({ queryKey: userKeys.lists() });
    },
  });
}

9. Error Codes

Error response format:

{
  "success": false,
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Field 'email' is required",
    "details": [
      { "field": "email", "message": "This field is required" }
    ]
  }
}

Quick Reference

Common Workflows

1. Create a table -> Use backend_table_create MCP tool
2. Add fields later -> Use backend_field_manage MCP tool
3. Insert data -> POST /v1/data/:tableName
4. Query with filters -> GET /v1/data/:tableName?filter={...}&sort={...}&limit=20
5. Join related data -> GET /v1/data/:tableName?join=fieldName
6. Paginate results -> Use cursor from meta.nextCursor
7. Search records -> GET /v1/data/:tableName?search=keyword&searchType=partial
8. Manage indexes -> Use backend_index_manage MCP tool