Bkend Data

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:

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

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

Batch create (multiple records):

httpCopy code
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:

jsonCopy code
{
  "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

httpCopy code
GET /v1/data/:tableName/:id

Response:

jsonCopy code
{
  "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

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

Query Parameters:

Response:

jsonCopy code
{
  "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

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

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

Response:

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

3.5 Delete Record

httpCopy code
DELETE /v1/data/:tableName/:id

Response:

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

3.6 Table Specification

Retrieve the full schema definition for a table:

httpCopy code
GET /v1/data/:tableName/spec

Response:

jsonCopy code
{
  "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:

jsonCopy code
{
  "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:

jsonCopy code
{
  "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:

Copy code
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):

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

Multiple sort fields:

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

5.2 Pagination

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

Copy code
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:

Copy code
# 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:

Copy code
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:

httpCopy code
GET /v1/data/posts?join=authorId

Response with joined data:

jsonCopy code
{
  "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:

Copy code
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:

httpCopy code
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

Copy code
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

Copy code
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

Copy code
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:

typescriptCopy code
// 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:

jsonCopy code
{
  "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