Schema API

The Schema API lets you read and modify the database schema at runtime. It covers collections (tables/views), fields (columns), relations (foreign keys), interfaces, and schema migration tools (snapshot, diff, apply).

All schema endpoints — including the read-only GET /api/schema/snapshot — are restricted to users with admin access. Schema changes run DDL statements directly on the connected PostgreSQL database.

Endpoint Summary

Method	Path	Status	Admin	Description
`GET`	`/api/collections`	200	No	List all collections
`GET`	`/api/collections/:name`	200	No	Get collection metadata
`GET`	`/api/collections/:name/fields`	200	No	List raw columns for a collection
`POST`	`/api/collections`	201	Yes	Create a table or virtual folder
`PATCH`	`/api/collections`	200	Yes	Batch update collection metadata
`PATCH`	`/api/collections/:name`	200	Yes	Update collection metadata
`DELETE`	`/api/collections/:name`	204	Yes	Drop table
`GET`	`/api/fields`	200	No	List all fields
`GET`	`/api/fields/:collection`	200	No	List fields for a collection
`GET`	`/api/fields/:collection/:field`	200	No	Get single field metadata
`POST`	`/api/fields/:collection`	201	Yes	Add a field
`PATCH`	`/api/fields/:collection/:field`	200	Yes	Update a field
`DELETE`	`/api/fields/:collection/:field`	204	Yes	Remove a field
`GET`	`/api/relations`	200	No	List all relations
`GET`	`/api/relations/:collection`	200	No	List relations for a collection
`GET`	`/api/relations/:collection/:field`	200	No	Get single relation
`POST`	`/api/relations`	201	Yes	Create a relation
`PATCH`	`/api/relations/:collection/:field`	200	Yes	Update a relation
`DELETE`	`/api/relations/:collection/:field`	204	Yes	Delete a relation
`GET`	`/api/interfaces`	200	No	List available field interfaces
`GET`	`/api/schema/snapshot`	200	Yes	Export schema snapshot
`POST`	`/api/schema/diff`	200	Yes	Diff a snapshot against current schema
`POST`	`/api/schema/apply`	200	Yes	Apply DDL operations from a diff

Common error codes:

Status	Meaning
201	Created
204	Deleted (no content)
400	Bad request (missing fields, invalid payload, duplicate, cannot delete primary key)
401	Unauthenticated
403	Forbidden (admin-only endpoint)
404	Not found
409	Conflict (duplicate collection/resources)

Collections

Get all collections


curl https://your-domain.com/api/collections \
  -H "Authorization: Bearer <token>"


{
  "data": [
    {
      "collection": "posts",
      "meta": { "icon": "article", "note": "Blog posts", "sort_field": "sort" },
      "schema": { "name": "posts", "schema": "public" },
      "system": false,
      "type": "table"
    },
    {
      "collection": "daas_users",
      "schema": { "name": "daas_users", "schema": "public" },
      "meta": null,
      "system": true,
      "type": "table"
    }
  ]
}

Field	Type	Description
`collection`	`string`	Collection/table name
`schema`	`object`	Schema info — `name` (table name) and `schema` (PostgreSQL schema, typically `"public"`)
`meta`	`object \| null`	Collection metadata (icon, note, sort_field, etc.)
`system`	`boolean`	Whether the collection is a system collection
`type`	`string`	Collection type — `"table"` or `"view"`

Get a single collection


GET /api/collections/:name

Returns a single collection object in the same shape as the list response.

Create a collection


curl -X POST https://your-domain.com/api/collections \
  -H "Authorization: Bearer <admin_token>" \
  -H "Content-Type: application/json" \
  -d '{
    "collection": "products",
    "meta": { "icon": "shopping_cart" },
    "schema": {},
    "fields": [
      { "field": "id",    "type": "integer", "schema": { "is_primary_key": true, "has_auto_increment": true } },
      { "field": "name",  "type": "string",  "schema": { "is_nullable": false } },
      { "field": "price", "type": "float",   "schema": { "is_nullable": true } }
    ]
  }'

Returns 201 Created with the collection object:


{
  "data": {
    "collection": "products",
    "schema": { "name": "products", "schema": "public" },
    "meta": { "icon": "shopping_cart" },
    "system": false,
    "type": "table"
  }
}

Virtual folders

Pass "schema": null to create a virtual folder — a collection with no physical database table and no required fields. Useful for organizing other collections into groups:


curl -X POST https://your-domain.com/api/collections \
  -H "Authorization: Bearer <admin_token>" \
  -H "Content-Type: application/json" \
  -d '{
    "collection": "Blog Content",
    "schema": null,
    "meta": { "icon": "folder" }
  }'

Batch update collections

PATCH /api/collections updates metadata (sort order, grouping) for multiple collections at once. The body must be an array:


curl -X PATCH https://your-domain.com/api/collections \
  -H "Authorization: Bearer <admin_token>" \
  -H "Content-Type: application/json" \
  -d '[
    { "collection": "articles", "meta": { "sort": 1, "group": "content" } },
    { "collection": "pages",    "meta": { "sort": 2, "group": "content" } }
  ]'

Returns 200 with the updated collection objects.

Update a single collection


PATCH /api/collections/:name

Updates metadata for a single collection. Response is 200 with the updated collection object.

Delete a collection


DELETE /api/collections/:name

Drops the table and returns 204 No Content.

Fields

All fields endpoints support ?limit=N to cap the number of returned items.

Get all fields


GET /api/fields
GET /api/fields?limit=10

Returns all field definitions across all collections in daas-compatible format:


{
  "data": [
    {
      "collection": "posts",
      "field": "title",
      "type": "string",
      "schema": {
        "name": "title",
        "table": "posts",
        "data_type": "character varying",
        "default_value": null,
        "max_length": 255,
        "numeric_precision": null,
        "numeric_scale": null,
        "is_nullable": false,
        "is_unique": false,
        "is_primary_key": false,
        "has_auto_increment": false,
        "has_index": false,
        "foreign_key_table": null,
        "foreign_key_column": null,
        "comment": null
      },
      "meta": {
        "collection": "posts",
        "field": "title",
        "interface": "input",
        "required": true,
        "hidden": false,
        "readonly": false,
        "sort": null,
        "width": "full"
      }
    }
  ]
}

Get fields for a collection


GET /api/fields/posts
GET /api/fields/posts?limit=5

Same response format as above, filtered to a single collection.

Get a single field


GET /api/fields/posts/title

Returns a single field object.

Add a field


curl -X POST https://your-domain.com/api/fields/posts \
  -H "Authorization: Bearer <admin_token>" \
  -H "Content-Type: application/json" \
  -d '{
    "field": "summary",
    "type": "text",
    "meta": { "interface": "input-multiline", "note": "Short description" },
    "schema": { "is_nullable": true, "default_value": null }
  }'

Returns 201 Created with the new field object.

Update a field


PATCH /api/fields/posts/summary

Updates field metadata and returns 200 with the updated field.

Delete a field


DELETE /api/fields/posts/summary

Returns 204 No Content on success.

Primary key columns cannot be deleted. Attempting to delete a primary key field returns a 400 error: Cannot delete primary key.

Raw column listing

GET /api/collections/:name/fields returns raw column information directly from PostgreSQL, in a different format than the daas-style /api/fields/:collection endpoint:


curl https://your-domain.com/api/collections/posts/fields \
  -H "Authorization: Bearer <token>"


{
  "data": [
    {
      "field": "id",
      "name": "Id",
      "type": "integer",
      "nullable": false,
      "default_value": "nextval('posts_id_seq'::regclass)",
      "is_primary_key": true,
      "schema": "public",
      "table": "posts"
    },
    {
      "field": "title",
      "name": "Title",
      "type": "string",
      "nullable": false,
      "default_value": null,
      "is_primary_key": false,
      "schema": "public",
      "table": "posts"
    }
  ]
}

Use this endpoint for lightweight introspection; use /api/fields/:collection for full daas-compatible field metadata (with meta and schema sub-objects).

Relations

Relations are discovered from foreign key constraints in PostgreSQL and from virtual M2M/O2M metadata stored in daas_relations.

All relations endpoints support ?limit=N to cap the number of returned items.

Get all relations


GET /api/relations
GET /api/relations?limit=5


{
  "data": [
    {
      "collection": "posts",
      "field": "author",
      "related_collection": "daas_users",
      "meta": {
        "many_collection": "posts",
        "many_field": "author",
        "one_collection": "daas_users",
        "one_field": null,
        "junction_field": null,
        "sort_field": null
      },
      "schema": {
        "table": "posts",
        "column": "author",
        "foreign_key_table": "daas_users",
        "foreign_key_column": "id",
        "constraint_name": "posts_author_foreign",
        "on_update": "NO ACTION",
        "on_delete": "SET NULL"
      }
    }
  ]
}

Create a relation

POST /api/relations requires three fields: collection, field, and related_collection.


curl -X POST https://your-domain.com/api/relations \
  -H "Authorization: Bearer <admin_token>" \
  -H "Content-Type: application/json" \
  -d '{
    "collection": "comments",
    "field": "post_id",
    "related_collection": "posts",
    "schema": { "on_delete": "CASCADE" }
  }'

Returns 201 Created with the relation object.

Get relations for a collection


GET /api/relations/posts

Returns all relations where the collection is the source.

Get a single relation


GET /api/relations/posts/author

Returns a single relation object.

Update a relation


PATCH /api/relations/posts/author

Updates relation metadata and schema (on_delete, on_update). Returns 200.

Delete a relation


DELETE /api/relations/posts/author

Returns 204 No Content.

Interfaces

GET /api/interfaces returns available field interface definitions. Used by the Data Model UI to populate interface selection dropdowns.

Query parameters:

Param	Type	Default	Description
`grouped`	`boolean`	`false`	Group interfaces by category
`type`	`string`	—	Filter by field type (e.g. `string`, `integer`, `boolean`)
`localType`	`string`	—	Filter by local type (e.g. `m2o`, `m2m`, `file`, `presentation`, `group`, `workflow`)
`supported`	`boolean`	`true`	Set `false` to include unsupported interfaces

Ungrouped response (default):


GET /api/interfaces


{
  "data": [
    { "id": "input",              "name": "Input",               "icon": "IconForms",         "types": ["string","text","integer","bigInteger","float","decimal"], "group": "standard",      "order": 1,  "supported": true, "recommended": true },
    { "id": "input-multiline",    "name": "Textarea",            "icon": "IconAlignLeft",     "types": ["string","text"],                                             "group": "standard",      "order": 2,  "supported": true },
    { "id": "datetime",           "name": "DateTime",            "icon": "IconCalendar",      "types": ["dateTime","date","time","timestamp"],                       "group": "standard",      "order": 5,  "supported": true, "recommended": true },
    { "id": "collection-item-dropdown", "name": "Collection Item", "icon": "IconDatabase",  "types": ["string","text","integer","uuid"], "localTypes": ["m2o"],    "group": "relational",    "order": 1,  "supported": true, "relational": true },
    { "id": "file-image",         "name": "Image",               "icon": "IconPhoto",         "types": ["uuid"],          "localTypes": ["file"],    "group": "relational",    "order": 7,  "supported": true }
  ]
}

Grouped response:


GET /api/interfaces?grouped=true


{
  "data": {
    "interfaces": [ ... ],
    "groups": [
      {
        "key": "standard",
        "name": "Text & Numbers",
        "interfaces": [
          { "id": "input", "name": "Input", "types": ["string","text","integer","bigInteger","float","decimal"], "group": "standard", "order": 1 },
          { "id": "datetime", "name": "DateTime", "types": ["dateTime","date","time","timestamp"], "group": "standard", "order": 5 }
        ]
      },
      {
        "key": "selection",
        "name": "Selection",
        "interfaces": [ ... ]
      },
      {
        "key": "relational",
        "name": "Relational",
        "interfaces": [ ... ]
      },
      {
        "key": "presentation",
        "name": "Presentation",
        "interfaces": [ ... ]
      },
      {
        "key": "group",
        "name": "Groups",
        "interfaces": [ ... ]
      },
      {
        "key": "workflow",
        "name": "Workflow",
        "interfaces": [ ... ]
      }
    ]
  }
}

Filtered:


GET /api/interfaces?type=string&localType=standard

Returns only interfaces that support string fields and have a local type of standard.

Schema Migration

The platform supports schema snapshot, diffing, and application for migrating schema changes between environments.

Snapshot

Export the full database schema as a JSON snapshot for later comparison:


GET /api/schema/snapshot


{
  "data": {
    "version": 1,
    "timestamp": "2024-06-15T10:30:00.000Z",
    "schema": "public",
    "tables": [
      { "table_name": "posts", "table_type": "BASE TABLE" }
    ],
    "columns": [
      { "table_name": "posts", "column_name": "id", "data_type": "integer", "is_nullable": "NO", "column_default": "nextval('posts_id_seq'::regclass)", "is_primary_key": true }
    ],
    "foreign_keys": [
      { "constraint_name": "posts_author_foreign", "table_name": "posts", "column_name": "author", "foreign_table_name": "daas_users", "foreign_column_name": "id" }
    ],
    "daas_fields": [
      { "collection": "posts", "field": "title", "interface": "input" }
    ],
    "daas_relations": [
      { "collection": "posts", "field": "author", "related_collection": "daas_users" }
    ],
    "daas_collections": [
      { "collection": "posts", "meta": { "icon": "article" } }
    ]
  }
}

Diff

Compare a previously captured snapshot against the current database schema. Detects added, removed, and changed tables, columns, and foreign keys:


POST /api/schema/diff

Request body:


{
  "snapshot": {
    "tables": [
      { "table_name": "posts", "table_type": "BASE TABLE" }
    ],
    "columns": [
      { "table_name": "posts", "column_name": "id", "data_type": "integer", "is_nullable": "NO", "column_default": "nextval('posts_id_seq'::regclass)" }
    ],
    "foreign_keys": [
      { "constraint_name": "posts_author_foreign", "table_name": "posts", "column_name": "author", "foreign_table_name": "daas_users", "foreign_column_name": "id" }
    ]
  }
}

Response:


{
  "data": {
    "tables": {
      "added": [
        { "table_name": "comments", "table_type": "BASE TABLE" }
      ],
      "removed": []
    },
    "columns": {
      "added": [
        { "table_name": "comments", "column_name": "body", "data_type": "text", "is_nullable": "YES" }
      ],
      "removed": [],
      "changed": [
        {
          "key": "posts.title",
          "current": { "data_type": "text", "is_nullable": "NO", "column_default": null },
          "snapshot": { "data_type": "character varying", "is_nullable": "NO", "column_default": null }
        }
      ]
    },
    "foreign_keys": {
      "added": [
        { "constraint_name": "comments_post_foreign", "table_name": "comments", "column_name": "post_id", "foreign_table_name": "posts", "foreign_column_name": "id" }
      ],
      "removed": []
    }
  }
}

Apply

Execute the DDL operations produced by a diff. Each operation is a separate SQL statement:


POST /api/schema/apply

Request body:


{
  "operations": [
    { "type": "sql", "sql": "CREATE TABLE comments (id SERIAL PRIMARY KEY, body TEXT, post_id INTEGER)" },
    { "type": "sql", "sql": "ALTER TABLE comments ADD CONSTRAINT comments_post_foreign FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE" }
  ]
}

Each operation must have "type": "sql" and a non-empty sql string.

Response (all operations succeed):


{
  "data": {
    "applied": 2,
    "results": [
      { "index": 0, "sql": "CREATE TABLE comments ...", "success": true },
      { "index": 1, "sql": "ALTER TABLE comments ...", "success": true }
    ]
  }
}

Operations are applied sequentially and stop on the first failure. Partial applies are rejected.

Partial failure (500):


{
  "errors": [{ "message": "Operation 1 failed: relation already exists", "extensions": { "code": "INTERNAL_SERVER_ERROR" } }],
  "data": { "applied": 1, "results": [ { "index": 0, "sql": "...", "success": true }, { "index": 1, "sql": "...", "success": false } ] }
}

Migration workflow

Snapshot the source environment: GET /api/schema/snapshot → save the JSON
Take the snapshot to your target environment
Diff the snapshot against the target schema: POST /api/schema/diff with { "snapshot": { "tables": [...], "columns": [...], "foreign_keys": [...] } } → generates a diff
Generate operations by converting the diff into SQL ALTER TABLE / CREATE TABLE / DROP TABLE statements wrapped in { "type": "sql", "sql": "..." } objects
Apply the operations to the target: POST /api/schema/apply with { "operations": [...] }

This flow enables predictable, declarative schema migrations across dev → staging → production environments.