CoconutDB Client SDK: AI-Friendly System Reference & Integration Guide

A lightweight, high-performance client library and pre-built React UI component suite for integrating with CoconutDB database engines.

This documentation is structured explicitly to be parsed and understood by LLMs, developer agents, and AI tools assisting in database integration.

🚀 Getting Started & Authentication

1. Installation

Install the SDK directly from your local project filesystem:

npm install /Users/princepatel/COCONUTSDK/COCONUTDB-SDK

2. Initialization

Instantiate the CoconutDB client. The client can authorize requests using a dynamic session token OR a persistent apiKey.

• Authorization Token: Dispatched inside the standard Authorization: Bearer <token> header. Used for standard user sessions (retrieved via db.auth.login or db.auth.login2FA).
• Developer API Key: Dispatched inside the custom x-api-key: <apiKey> header. If apiKey is provided, the SDK automatically skips adding the Authorization header.

import { CoconutDB } from 'coconutdb-sdk';

// Option A: Using User Session Token
const db = new CoconutDB({
  backendUrl: 'http://localhost:3000',
  clientId: 'tenant-client-id',
  projectId: 'active-project-id',
  token: 'jwt-session-token'
});

// Option B: Using Developer API Key (Authorization header is omitted)
const dbWithKey = new CoconutDB({
  backendUrl: 'http://localhost:3000',
  clientId: 'tenant-client-id',
  projectId: 'active-project-id',
  apiKey: 'your-developer-api-key'
});

📂 Database Schema & Table Registry

CoconutDB supports building schemas dynamically. AI tools should verify existing tables and schemas before attempting lookups or insertion tasks.

Supported Column Datatypes

When creating tables or adding columns via db.registry.createTable or db.registry.addColumn, use these exact string tags for data_type:

| Type Tag | Description | Database Type | Expected Payload Format | | :--- | :--- | :--- | :--- | | text | Alphanumeric strings, descriptions, names. | TEXT | string (e.g. "Luke Skywalker") | | number | Whole integer numbers. | INTEGER | number (e.g. 42) | | decimal | Floating point fractional numbers. | DOUBLE PRECISION | number (e.g. 12.99) | | Yes/No | Boolean binary state flag. | BOOLEAN | boolean (e.g. true or false) | | Datetime | Timestamps. | TIMESTAMP WITH TIME ZONE| ISO 8601 string (e.g. "2026-06-06T07:30:00Z") | | Lookup relation | Foreign key referencing another table. | UUID (ForeignKey) | string UUID (e.g. "019e962f-216f-7817-826d...") | | filepicker | Secure vault attachment object. | JSONB / TEXT | object with { id: string, filename: string } |

🔗 Lookup Relations Creation Flow

To create lookup relations between tables, the AI tool must follow these chronological steps:

graph TD
    A[Fetch Table List: listTables] --> B{Parent Table Exists?}
    B -->|No| C[Create Parent Table: createTable]
    B -->|Yes| D[Identify Parent ID column]
    C --> D
    D --> E[Create Child Table specifying lookup column data_type: Lookup relation]

1. Query Existing Tables: Call await db.registry.listTables() to fetch schemas.
2. Ensure Parent Table Exists: Lookups require a parent table to exist first. If missing, create the parent table via db.registry.createTable(...).
3. Register Child Lookup: Define the lookup column in the child table. Include data_type: "Lookup relation" and target referenced_table_name pointing to the parent.

🛡️ Row-Level Security: "RLS by Created By"

When a table is created, the configuration payload accepts a boolean property: rls_by_created_by.

• Mechanism: If rls_by_created_by: true is set, non-admin users (Editors and Viewers) are strictly filtered. They can only query, read, edit, or delete rows where the record's created_by column matches their active user ID.
• Evaluation: The backend automatically appends AND created_by = req.user.id to database transactions for these tables. Administrators bypass this restriction automatically.

📊 Dynamic Record Endpoints (15 Core Operations)

The SDK wraps these 15 operations within the db.records module. All operations handle optimistic concurrency checks and audit log tracking.

1. List Dynamic Records

Fetches records. Supports standard pagination and field equality filters.

• Method: db.records.list(tableName, params)
• Request Parameters:
  • page: Page index (default 1).
  • limit: Records per page (default 10).
  • filter: Object defining column constraints (e.g., { filter: { status: 'active' } }).
  • include_deleted: Set true to include soft-deleted records.
• Response: { success: true, data: [...], meta: { total: 100, page: 1, limit: 10 } }

2. Retrieve One Record

Fetches a single record by primary key.

• Method: db.records.get(tableName, recordId)

3. Create Record

Inserts a single row. Payload types must match the declared schema.

• Method: db.records.create(tableName, recordData)
• Payload Example:

  {
    "title": "Coconut Water",
    "stock": 45,
    "price": 3.99,
    "in_stock": true,
    "created_at": "2026-06-06T12:00:00Z",
    "vendor_id": "019e88d1-a09f-791e-bd0f-9e96d4a16962"
  }

4. Partial Update (PATCH)

Updates specific columns on a row. To prevent write conflicts, send the record's current version inside headers or body.

• Method: db.records.update(tableName, recordId, recordData, currentVersion)
• Optimistic Concurrency: If the record has been modified by another process since it was fetched, this throws a VERSION_MISMATCH (409) conflict.

5. Soft Delete Record

Marks a record as deleted by setting is_deleted: true.

• Method: db.records.delete(tableName, recordId, false)

6. Restore Soft-Deleted Record

Recovers a soft-deleted row by resetting is_deleted: false.

• Method: db.records.restore(tableName, recordId)

7. Hard Delete Record

Permanently purges a record from the SQL table. Requires Administrator permissions.

• Method: db.records.delete(tableName, recordId, true)

8. Bulk Create / Upsert

Creates multiple records inside a single transaction database commit.

• Method: db.records.bulkCreate(tableName, items)
• Batch Cap: Max 100 items.
• Response: Details on succeeded/failed inserts and primary key listings.

9. Bulk Update

Updates multiple records sequentially inside a single transaction. Each record object in the array must include its unique id and its current version.

• Method: db.records.bulkUpdate(tableName, items)
• Batch Cap: Max 100 items.

10. Bulk Soft Delete

Moves a batch of records to the recycle bin inside a single transaction.

• Method: db.records.bulkDelete(tableName, ids) (where ids is an array of strings e.g. ['uuid1', 'uuid2']).

11. Advanced Search

Performs complex queries, full-text searches, or matching against tables.

• Method: db.records.search(tableName, params)

12. Count Matching

Returns a simple numeric count of records matching filters.

• Method: db.records.count(tableName, params)
• Response: { count: 42 }

13. Export CSV / JSON

Extracts table data. JSON is standard; CSV returns formatted columns.

• Method: db.records.export(tableName, format, params)
• Format Options: 'csv' or 'json' (Capped at 500 rows for performance stability).

14. Version History Timeline

Lists version audit logs for a row, including who changed the item.

• Method: db.records.getHistory(tableName, recordId)

15. Snapshot Time-Machine

Retrieves the complete data snapshot of a record at a specific version index.

• Method: db.records.getHistoryVersion(tableName, recordId, versionNumber)

☁️ Document Vault (Secure File Vault)

The Document Vault manages secure binary files (e.g. images, PDFs). It integrates with Cloud Storage and Backblaze B2.

| Operation | SDK Method | Access Scope | Details | | :--- | :--- | :--- | :--- | | List Documents | db.projects.listDocuments(projId, recycle) | Members (Viewer+) | Lists active or recycle-bin (recycle: true) files. | | Upload Document | db.projects.uploadDocument(projId, docData) | Editor / Admin | Uploads base64 data. 100MB limit. | | Download Document| db.projects.downloadDocument(projId, docId)| Members (Viewer+) | Streams the binary document content as a Blob. | | Soft Delete | db.projects.deleteDocument(projId, docId) | Editor / Admin | Moves active files to the Recycle Bin. | | Restore File | db.projects.restoreDocument(projId, docId) | Editor / Admin | Recovers a document from the Recycle Bin. | | Hard Delete | db.projects.deleteDocument(projId, docId) | Admin Only | Deletes a soft-deleted document permanently. |

👥 Access & Permission Control

CoconutDB operates on strict project-level access tokens:

Role Permissions Matrix

| Role | Table Schema | Records DML (CRUD) | Document Vault | Site Workflows / Webhooks / RLS | | :--- | :--- | :--- | :--- | :--- | | Viewer | Read Only | Read Only | Download Only | Denied | | Editor | Read Only | Read, Write, Update, Soft-Delete | Upload, Soft-Delete, Restore | Denied | | Admin | Read, Write, Alter | Read, Write, Update, Hard-Delete | Full Access | Create, Update, Delete, Pause |

Access APIs (db.projects.*)

• List Members: db.projects.listUsers(projectId)
• Add Member: db.projects.addUser(projectId, { email, role_id }) (Role IDs: 1 = Admin, 2 = Editor, 3 = Viewer).
• Remove Member: db.projects.removeUser(projectId, userId)

⚡ Site Workflows (Serverless Automations)

Workflows are high-performance sandboxed Node.js engines. They can be triggered by either HTTP Requests or Scheduled Cron Jobs.

• Note: Only Administrators can create, update, pause, or delete workflows.

Workflow APIs (db.workflows.*)

• List Workflows: await db.workflows.list(projectId)
• Create Workflow: await db.workflows.create(projectId, { name, trigger_type, code })
  • trigger_type: 'http' or 'scheduled'.
  • code: Sandboxed JS execution script string.
• Update Workflow: await db.workflows.update(projectId, workflowId, { code, active })
• Pause/Resume: await db.workflows.toggle(projectId, workflowId)
• Trigger Manually: await db.workflows.trigger(projectId, workflowId, payload)
• Audit Logs: await db.workflows.getHistory(projectId, workflowId)

🛡️ RLS Policies (Advanced Security)

RLS policies evaluate Boolean expressions to filter rows during CRUD queries.

• Note: Only Administrators can configure policies.

RLS APIs (db.policies.*)

• List Policies: await db.policies.list(projectId)
• Create Policy: await db.policies.create(projectId, { name, table_id, action, expression })
  • action: 'SELECT', 'INSERT', 'UPDATE', 'DELETE', or 'ALL'.
  • expression: Logic string (e.g. record.created_by == user.id or user.role_name == 'Administrator').
• Delete Policy: await db.policies.delete(projectId, policyId)

🔗 Webhooks

Webhooks notify external HTTP services during database mutations. Webhooks can be combined with Site Workflows by pasting a workflow's HTTP trigger URL into the webhook endpoint!

• Supported Targets: Tables (Insert, Update, Delete), Document Vault (Uploads, Deletions), Workspace logs, Email Dispatcher, Member registration, Rate limit blocks.
• Webhook APIs (db.admin.*):
  • listWebhooks(projectId)
  • createWebhook(projectId, { name, target_url, events }) (e.g. events: ['INSERT', 'UPDATE'])
  • deleteWebhook(projectId, webhookId)

📧 SMTP Email Configs

Register custom SMTP credentials to configure transactional email services.

• Email APIs (db.admin.*):
  • listEmailConfigs(projectId)
  • createEmailConfig(projectId, { email, host, port, encryption, display_name })
  • updateEmailConfig(projectId, configId, configDef)
  • deleteEmailConfig(projectId, configId)
  • testEmailConfig(projectId, configId) (Verifies port connection)
  • testSendEmailConfig(projectId, configId, { to, subject, body }) (Dispatches test email)
  • setDefaultEmailConfig(projectId, configId)

🔑 Developer API Keys

Generate API credentials with specific authorization limits (Viewer, Editor, Admin scope).

• Execution Logs Audit: When a developer key is utilized, CoconutDB log systems record the action under the name of the user who registered the API key, ensuring full audit traceability.
• Key APIs (db.apiKeys.*):
  • list(projectId)
  • create(projectId, { name, role_id })
  • toggle(projectId, keyId) (Enables or disables key)
  • delete(projectId, keyId)