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**. | Type | Description | Example | |------|-------------|---------| | `string` | Text data, UTF-8 encoded | `"Hello World"` | | `int` | Integer numbers (no decimals) | `42` | | `double` | Floating-point numbers | `3.14` | | `bool` | Boolean true/false | `true` | | `date` | ISO 8601 date-time string | `"2025-01-15T09:30:00Z"` | | `object` | Nested JSON object | `{ "city": "Seoul", "zip": "06000" }` | | `array` | Array of values | `["tag1", "tag2", "tag3"]` | > **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. | Field | Type | Description | |-------|------|-------------| | `id` | string | Unique record identifier (auto-generated) | | `createdBy` | string | User ID of the creator (auto-set) | | `createdAt` | date | Creation timestamp (auto-set) | | `updatedAt` | date | Last update timestamp (auto-set) | ### 2.3 Constraints Apply constraints to columns for data integrity: | Constraint | Description | Example | |------------|-------------|---------| | `required` | Field must have a value | `required: true` | | `unique` | Value must be unique across all records | `unique: true` | | `default` | Default value when not provided | `default: "active"` | | `min` | Minimum value (int/double) or length (string) | `min: 0` | | `max` | Maximum value (int/double) or length (string) | `max: 100` | | `enum` | Restrict to a set of allowed values | `enum: ["active", "inactive", "pending"]` | ### 2.4 Default Indexes Every table is created with these indexes by default: | Index Name | Fields | Purpose | |------------|--------|---------| | `_id_` | `id` | Primary key lookup | | `idx_createdAt_desc` | `createdAt` descending | Sort by creation date | | `idx_updatedAt_desc` | `updatedAt` descending | Sort by update date | | `idx_createdBy` | `createdBy` | Filter by owner | ## 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:** ```http POST /v1/data/:tableName Content-Type: application/json { "name": "John Doe", "email": "john@example.com", "age": 30, "role": "user" } ``` **Batch create (multiple records):** ```http 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: ```json { "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 ```http GET /v1/data/:tableName/:id ``` Response: ```json { "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 ```http GET /v1/data/:tableName?filter={...}&sort={...}&limit=20&cursor=last_id&search=keyword&searchType=partial ``` **Query Parameters:** | Parameter | Type | Description | |-----------|------|-------------| | `filter` | JSON | Filter conditions (see Section 4) | | `sort` | JSON | Sort order (see Section 5) | | `limit` | int | Number of records to return (max 100, default 20) | | `cursor` | string | Cursor for pagination (last record ID) | | `search` | string | Full-text or partial search keyword | | `searchType` | string | Search mode: `"exact"` or `"partial"` | Response: ```json { "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 ```http PUT /v1/data/:tableName/:id Content-Type: application/json { "name": "John Updated", "age": 31 } ``` Response: ```json { "success": true, "data": { "id": "rec_abc123", "name": "John Updated", "age": 31, "updatedAt": "2025-01-16T10:00:00Z" } } ``` ### 3.5 Delete Record ```http DELETE /v1/data/:tableName/:id ``` Response: ```json { "success": true, "data": { "id": "rec_abc123", "deleted": true } } ``` ### 3.6 Table Specification Retrieve the full schema definition for a table: ```http GET /v1/data/:tableName/spec ``` Response: ```json { "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: ```json { "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: ```json { "filter": { "$or": [ { "status": "active" }, { "role": "admin" } ] } } ``` This returns records where `status` equals "active" **OR** `role` equals "admin". ### 4.3 Filter Operators (10 Operators) | Operator | Description | Example | |----------|-------------|---------| | `$eq` | Equal to | `{ "status": { "$eq": "active" } }` | | `$ne` | Not equal to | `{ "status": { "$ne": "deleted" } }` | | `$gt` | Greater than | `{ "age": { "$gt": 18 } }` | | `$gte` | Greater than or equal | `{ "age": { "$gte": 18 } }` | | `$lt` | Less than | `{ "price": { "$lt": 100 } }` | | `$lte` | Less than or equal | `{ "price": { "$lte": 99.99 } }` | | `$in` | In array of values | `{ "role": { "$in": ["admin", "editor"] } }` | | `$nin` | Not in array | `{ "status": { "$nin": ["deleted", "banned"] } }` | | `$regex` | Regular expression match | `{ "name": { "$regex": "^John" } }` | | `$exists` | Field exists or not | `{ "profileImage": { "$exists": true } }` | ### 4.4 Search Use query parameters for text search: ```