Collections & Schemas

In Moul, a Collection (internally called a Moul) represents a dynamic SQLite database table together with its schema, field validation constraints, and access control rules.

Collections can be created, updated, inspected, or deleted dynamically at runtime via the HTTP API, the Web Admin Console (/_moul_/), the TUI (moul), or AI assistants via MCP without restarting the server.

Collection Types

Moul supports four distinct collection types tailored for different application architectures:

Supported Dynamic Field Types

Moul supports 11 dynamic field types with automatic SQLite column mapping, runtime validation, and OpenAPI 3.0 type generation:

Encrypted Fields (cloak) & Blind Indexing

The cloak field type provides application-layer authenticated encryption at rest for sensitive data (PII, SSNs, access tokens, credit cards):

• Encryption Standard: AES-256-GCM with a 12-byte random nonce and 16-byte authentication tag ([0x01][nonce][ciphertext + tag]). Keys are derived via HKDF-SHA256 from the MOUL_ENCRYPTION_KEY environment variable.
• Fail-Safe Startup: If any collection in the database defines a cloak field and MOUL_ENCRYPTION_KEY is not provided, the engine refuses to boot to prevent unrecoverable data loss or unauthorized reads.
• Masking by Default: Record reads mask cloak values automatically (•••• + last 4 characters, or •••• if 4 or fewer characters). Authenticated administrators always see decrypted values. Other authenticated consumers can pass ?reveal=fieldName (or ?reveal=all) to request unmasked plaintext.
• Searchable Blind Indexing: Because ciphertext is encrypted nondeterministically with unique nonces, direct search on encrypted binary is impossible. Setting "searchable": true instructs Moul to maintain an automatic, indexed companion column (<fieldName>Hash TEXT) populated with an HMAC-SHA256 blind index.
  • Query Restrictions: Exact equality queries (field = 'value' and field != 'value') are seamlessly translated into companion hash lookups. Pattern matches (~, !~) and range comparisons (>, <) are strictly rejected with HTTP 400 Bad Request to prevent security misconfigurations.
  • Security Assurance: Companion hash columns are internal SQLite storage details and are completely stripped from all API outputs.

Field Naming & System Timestamps

Strict camelCase Naming

All custom field names in a collection must strictly follow camelCase (matching regex ^[a-z][a-zA-Z0-9]*$, e.g. authorId, viewsCount, isFeatured). Field names containing underscores (e.g. author_id), dashes, spaces, uppercase initial letters, or reserved keywords are rejected with HTTP 400 Bad Request.

Universal Timestamps

Every collection table automatically creates and maintains createdAt and updatedAt ISO 8601 UTC timestamp columns. These system fields are automatically stamped on record creation and update:

• createdAt — RFC 3339 UTC timestamp of initial record creation.
• updatedAt — RFC 3339 UTC timestamp of most recent update.

Reserved field names (id, createdAt, updatedAt, createdat, updatedat) cannot be used as custom field names.

Schema Management API

Create a Collection (POST /api/moul)

Schema creation and management endpoints require administrative authorization via either:

• _rootUsers Admin JWT Bearer Token: Authorization: Bearer <root_user_token> (obtained via POST /api/moul/_rootUsers/auth-with-password).
• Administrative Master Key: X-Admin-Key: <MOUL_ADMIN_KEY> (or Authorization: Bearer <MOUL_ADMIN_KEY>).

curl -X POST "http://localhost:8090/api/moul" \
  -H "Authorization: Bearer <root_user_token>" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "articles",
    "type": "base",
    "fields": [
      { "name": "title", "type": "text", "required": true, "options": { "min": 3, "max": 200 } },
      { "name": "slug", "type": "text", "required": true, "unique": true },
      { "name": "content", "type": "text", "required": false },
      { "name": "views", "type": "number", "options": { "min": 0 } },
      { "name": "published", "type": "bool" }
    ],
    "rules": {
      "listRule": "published = true || @request.auth.id != \"\"",
      "viewRule": "published = true || @request.auth.id != \"\"",
      "createRule": "@request.auth.id != \"\"",
      "updateRule": "@request.auth.id != \"\"",
      "deleteRule": "@request.auth.role = \"admin\""
    }
  }'

curl -X POST "http://localhost:8090/api/moul" \
  -H "X-Admin-Key: test-admin-key-1234" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "articles",
    "type": "base",
    "fields": [
      { "name": "title", "type": "text", "required": true, "options": { "min": 3, "max": 200 } },
      { "name": "slug", "type": "text", "required": true, "unique": true },
      { "name": "content", "type": "text", "required": false },
      { "name": "views", "type": "number", "options": { "min": 0 } },
      { "name": "published", "type": "bool" }
    ]
  }'

const response = await fetch('http://localhost:8090/api/moul', {
  method: 'POST',
  headers: {
    'X-Admin-Key': 'test-admin-key-1234',
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    name: 'articles',
    type: 'base',
    fields: [
      { name: 'title', type: 'text', required: true },
      { name: 'slug', type: 'text', required: true, unique: true },
      { name: 'content', type: 'text' },
      { name: 'published', type: 'bool' },
    ],
    rules: {
      listRule: 'published = true',
    },
  }),
});
const collection = await response.json();
console.log('Collection created:', collection);

package main

import (
	"bytes"
	"encoding/json"
	"net/http"
)

func main() {
	payload := map[string]any{
		"name": "articles",
		"type": "base",
		"fields": []map[string]any{
			{"name": "title", "type": "text", "required": true},
			{"name": "published", "type": "bool"},
		},
	}
	body, _ := json.Marshal(payload)
	req, _ := http.NewRequest("POST", "http://localhost:8090/api/moul", bytes.NewReader(body))
	req.Header.Set("X-Admin-Key", "test-admin-key-1234")
	req.Header.Set("Content-Type", "application/json")

	client := &http.Client{}
	resp, err := client.Do(req)
	if err != nil {
		panic(err)
	}
	defer resp.Body.Close()
}

Collection Management Endpoints

• GET /api/moul - List all dynamic collections.
• GET /api/moul/:name - Get schema definition, field types, and rules for a collection.
• PATCH /api/moul/:name - Update fields, schema, or access rules.
• DELETE /api/moul/:name - Delete a collection and drop its SQLite table.