The Collection app is an infrastructure module: a database abstraction that gives your workspaces a real database without provisioning one, and with no UI of its own. Depending on your platform configuration it is backed by MongoDB or PostgreSQL, and it can use a mutualized database on your enterprise cluster or a dedicated database when a use case needs isolation. In Builder, you import Collection and read, write, and query structured data from your automations.

Collection (this app) vs the Collections product. Collection is the low-level database module, used from Builder in code, with no interface. The Collections product is built on top of it and adds a no-code, spreadsheet-like interface for business users. Same data layer, two levels of abstraction.

Overview

Collection serves as a managed database service integrated directly into the Prisme.ai platform:

Data Storage

Store structured data in document collections

Query Capabilities

Retrieve and filter data with powerful query options

Data Management

Create, update, and delete records with simple operations

Integration Ready

Seamlessly connect with automations and workflows

This infrastructure app is particularly valuable for persistently storing information, managing application state, and building data-driven automations without external database dependencies.

Release Note: Collection with PostgreSQL

With this new release, Collection instructions become compatible with postgresql database and the same MongoDB queries / updates syntax as usual.
This also brings performance improvements and new features, but with a few breaking changes and mongodb/postgres subtle differences to note.

Schema Configuration

Collections now require explicit schema definitions in the application config.

Cross-Database Compatibility

Collections can now work across MongoDB and PostgreSQL, with some differences detailed below.

Breaking Changes

Some MongoDB options were deprecated or modified. See below for full details.

New Features

Support for new aggregate instruction across both MongoDB and PostgreSQL. Existing distinct improved with new features

Breaking Changes

These breaking changes may affect your existing MongoDB-based Collections.
Please update your configuration and code accordingly.

Connection Pooling

Since mongodb requests were previously executed by prismeai-functions microservice, the total number of mongodb clients opened were directly tied to the prismeai-functions replicas number & scaling
Now that collections clients are only opened from prismeai-runtime (which supports multithreading), minPoolSize and maxPoolSize must be scaled relative to RUNNER_MAX_THREADS.
Example: if RUNNER_MAX_THREADS=2, then divide your pool sizes by 2.

Required permissions

The new collection schemas enforcement now requires 3 more mongodb user permissions (& their equivalent SQL) :
listCollections, listIndexes, dropIndexes

Update Instructions

Collection.updateMany no longer supports options.upsert
Collection.updateOne deprecates options.upsert : Use the dedicated Collection.upsert instruction instead.
Collection.updateOne and Collection.updateMany no longer supports options.replace.

Allowed Update Operators

Only the following MongoDB update operators are supported: $push, $set, $inc, $addToSet, $pull

Safer Deletes and Updates

updateOne, updateMany, deleteOne and deleteMany now raise an error if the query is empty/undefined.
To allow matching all documents, use the overrideSecurity: true parameter.

Schema Enforcement

All collections must now define collectionName and properties inside their app config, see an example config below.
Queries referencing unknown fields will raise errors.
Insert/update operations with unknown fields will also fail.
All properties defined in the schema will now be initialized to null and returned as-is by find if they were not initialized (and nullable)

🆕 New Features

Aggregate
Distinct

Use aggregate instruction to easily calculate sums, average or count optionally groupped by a column, on both MongoDB and PostgreSQL collections.

Use distinct to return unique values for a field.
Supports additional options to return each value with their count, and sort by these counts.

Key Features

Document Storage
Query Capabilities
Data Manipulation
Advanced Features

Store flexible document structures:

Schema enforcement: Ensure your data respect a predefined schema
Nested Data Support: Store complex, hierarchical data
Data Types: Support for strings, numbers, booleans, arrays, objects, dates
Automatic Indexing: Optimized for fast retrieval

This flexible storage model accommodates a wide range of data needs.

How Collection Works

Collection provides a MongoDB-compatible interface integrated directly into the Prisme.ai platform:

Collections Organization

Data is organized into collections, similar to tables in relational databases:

Each collection contains related documents
Collections are created automatically when used
No schema definition is required
Each workspace has its own collection namespace

Document Structure

Documents are stored as JSON-like objects:

Each document has a unique _id field
Documents can have any structure
Fields can contain various data types
Nested objects and arrays are supported
Documents in the same collection can have different structures

Data Operations

Operations are performed through simple, intuitive methods:

Commands follow MongoDB syntax and patterns
Results are returned in standard formats
Operations are executed in a secure environment
Performance is optimized for common use cases

Integration

Collection integrates with the rest of the Prisme.ai ecosystem:

Direct usage in automations
Connection to AI agents through tools
Data exchange with other platform components
Role-based access control

This approach provides the power of a document database with the simplicity of a fully managed service.

⚖️ MongoDB vs PostgreSQL Differences

MongoDB: $in: [] matches nothing.
PostgreSQL: $in: [] matches everything!\
Always check your input array before running $in queries on PostgreSQL.

PostgreSQL does not consider NULL values when using $ne.
Initialize values explicitly if you expect $ne to work.

MongoDB allows { "myArrayCol": "value" } to search for “value” in the text array column myArrayCol.
In PostgreSQL, use { "myArrayCol": { $in: ["value"] } }.

PostgreSQL array columns may lose value ordering when using $push, $pull, or $addToSet.
In MongoDB, order is preserved.

MongoDB allows { "someObject.field": "foo" }, even if someObject is an array.
The literal postgres translation would only work if someObject is an object
If someObject is an array, PostgreSQL requires the more explicit $elemMatch operator :
{ "someObject": { "$elemMatch": { "field": "foo" } } }

Nullable fields that are not initialized during insertion are always initialized to null with both MongoDB and PostgreSQL
MongoDB cannot process $addToSet to null values, so these columns must be initialized to `[]` before using$ addToSet
PostgreSQL graciously handles this situation and does not need these arrays to be set to [] for using $addToSet

🛠️ Configuring a Collection Schema

Defining a schema is now mandatory for collections in order to enforce validation and ensure cross-database compatibility.

slug: Messages
config:
  collectionName: Messages
  indexes:
    - properties: children
    - properties:
        - conversationId
        - from.id
    - name: msg_conv_from_tags_idx
      properties:
        - conversationId
        - from.id
        - tags
  uniques:
    - properties: conversationId
  properties:
    conversationId:
      type: text
      nullable: false
    children:
      type: text
      nullable: false
    content:
      type: text
      nullable: true
    from:
      type: json
      nullable: false
    tags: 
      type: array
      nullable: false
    messagesCount:
      type: number

The array type only supports text arrays
Use json type for both objects and arrays of objects
Non-nullable properties must be set on inserts and upserts. Otherwise the given query will fail

The name field on indexes is optional: by default it is generated automatically from the list of properties.
However, some databases enforce a maximum length on index names (PostgreSQL truncates names above 63 characters), which can cause collisions or errors on compound indexes with many/long property names.
In those cases, it is imperative to explicitly set a shorter name to avoid conflicts and ensure the index is created reliably.

Available property types include:

string, text, date, time, datetime, number, double, float, integer, decimal, boolean, uint8array, array, enum, enumArray, json, blob, time

🔄 Changing a Property Type

Changing the type of a property that already holds data means converting every existing row. The default conversion fails as soon as one value does not fit the new type: a text column switched to json chokes on the first row that is not valid JSON.

When that conversion fails, the whole schema update fails with it: every other column added in the same change is silently missing from the database. The symptom appears much later, and somewhere else: writing to one of those columns returns Invalid update on unknown field <collection>.<column>.

Text converted to json automatically

On PostgreSQL, a property now declared json whose column is still text is converted without declaring anything, when the platform knows how its rows were written. It decides from what the collection’s schema said before the update:

The property was	What happens to existing values
`string` or `text`	JSON objects and arrays are parsed; everything else stays the string it was (`123` stays `"123"`)
`json` already	every value the platform serialized comes back as written (`"abc"` → `abc`, `123` → `123`, objects and arrays parsed); text that is not valid JSON stays a string. See below for collections moved from MongoDB
unknown (no previous schema, not declared, or another type)	nothing is converted, see below

A column that is neither text nor json (a number, a boolean or a date column, for instance) is never converted automatically either: see below. A column that used to be an array (text array) is always converted, each array becoming the same JSON array.

The conversion changes what you read: a string holding {"en":"Hello"} is read as the object { en: 'Hello' } from then on. Until it is converted, a json property stored in a text column is read as the raw text.

Collections moved from MongoDB to PostgreSQL. The migration creates a field that held only strings as a text column, with the strings as they were. If the property is declared json, a string such as 123, true or null in that column is read as a number, a boolean or null once the column is converted, like the values the platform serialized itself. The workspaces.collections.migrated event counts these values in retyped, so you can check whether a collection is affected.

When the platform cannot tell, or cannot convert the column type, it leaves the column alone and emits a workspaces.collections.notMigrated event in the activity feed: the column, the reason, and the migration to declare (skipped[].howTo). The collection keeps working on its previous schema until you declare it, as described below.

Declaring a migration

Declare a migration next to your properties to choose how the existing values are converted, or to convert a column the platform left alone. Add it to the configuration of the Collection instance:

config:
  collectionName: agents
  properties:
    description:
      type: json      # was `text` until now
  migrations:
    - op: alterColumnType
      column: description
      from: [text, character varying]
      to: jsonb
      cast: auto
      description: Localized descriptions, 2026-07-21

A declared migration takes precedence over the automatic one, for the column types listed in its from. The migration runs before the schema is synchronized, and in the same transaction: if the synchronization then fails (a new unique index on duplicate values, for instance), the conversion is rolled back with it and the column keeps its previous type.

It only runs once, by itself

The from list is a guard checked against the column’s current type in the database. Once the column has been converted, from no longer matches and the migration is skipped.

Leave the declaration in place once it has run. There is no migration history to maintain: the database itself is the record, so the same declaration is safe on an instance that has already migrated, on a fresh one where the column was created with the right type, and on every later update.

Never list the target type in from. With from: [text, jsonb] and to: jsonb, the guard keeps matching after the conversion and the table is rewritten on every single schema update.

The conversion rewrites the table while locking it. If the table stays locked by something else for more than 5 seconds (COLLECTIONS_MIGRATION_LOCK_TIMEOUT_MS on a self-hosted runtime), the conversion gives up rather than block every request on the collection, and runs again at the next update.

Converting to json

`cast`	What happens to existing values
`auto` (default)	JSON objects and arrays are parsed, anything else is wrapped as a JSON string
`lenient`	every valid JSON value is parsed, scalars included (`123` becomes a number); anything else is wrapped
`wrap`	every value is wrapped as a JSON string, even valid JSON
`parse`	every value is parsed, and the migration fails on the first one that is not valid JSON

auto is what you want for a column that accumulated both plain text written before the type change and JSON written after it. Use lenient only for a column whose values were all written as JSON.

auto and lenient work on every supported PostgreSQL version; they are faster from PostgreSQL 16.

Following what happened

A migration that actually converts data emits a workspaces.collections.migrated event in the activity feed, reporting the column, the type it had, the type it became, the number of rows rewritten (rows, an estimate when rowsEstimated is true), how many of them now read as a number, a boolean or null instead of a string (retyped, with lenient only) and how long it took. A migration that matches nothing emits nothing. If a declared migration fails, the collection keeps working on its previous schema and the workspaces.collections.notMigrated event tells you which column refused and why (failed), so the failure is visible immediately instead of surfacing weeks later. A conversion undone because the rest of the schema update failed after it is listed in rolledBack.

Checking and converting from an automation

Without access to the database, two functions of the collections module let an automation of your workspace do the same, on its own collections only:

getSchemaDrift lists the collections whose table no longer matches their configuration: a column of the wrong type, a column that was never created.
runSchemaMigrations converts the columns of one collection with the migrations you give it, then synchronizes the collection with its configuration, exactly like an update of the Collection instance. With dryRun: true, it only tells you what it would convert, and how.

- run:
    module: collections
    function: getSchemaDrift
    output: drift
# drift.collections[0]: { collection: agents, columns: [{ column: description, from: text, to: jsonb, changes: [type] }, ...] }
- run:
    module: collections
    function: runSchemaMigrations
    parameters:
      collection: agents
      dryRun: true
      migrations:
        - op: alterColumnType
          column: description
          from: [text, character varying]
          to: jsonb
          cast: auto
    output: plan

Check plan.planned (the cast, the number of rows, retyped), then run the same call without dryRun: the conversion emits the same workspaces.collections.migrated event, and getSchemaDrift no longer lists the collection. To keep the migration for the instances you have not converted yet, declare it in the configuration as well. A few rules:

declare the property json in the configuration first: only conversions to jsonb of json properties are accepted, since the synchronization brings every other column back to its declared type;
run it once, from a manual or one-shot automation: each call synchronizes the collection, even with nothing to convert, and never take the migrations from a request payload;
PostgreSQL only: on MongoDB there are no column types, so no drift.

Basic Operations

Let’s explore the core operations you can perform with Collection:

Inserting Data

Add documents to a collection:

# Insert a single document
- Collection.insert:
    data:
      name: "John Doe"
      email: "john.doe@example.com"
      age: 30
      active: true
    output: result

# Insert multiple documents
- Collection.insertMany:
    data:
      - name: "Jane Smith"
        email: "jane.smith@example.com"
        age: 28
      - name: "Bob Johnson"
        email: "bob.johnson@example.com"
        age: 35
    output: result

The operation returns information about the inserted documents, including their assigned _id values.

Finding Data

Retrieve documents from a collection:

# Find documents matching criteria
- Collection.find:
    query:
      age: { $gt: 25 }
    output: users

# Find a single document
- Collection.find:
    query:
      email: "john.doe@example.com"
    output: user

# Find + sort + pagination
- Collection.find:
    query:
      active: true
    sort:
      age: -1  # Descending
    options:
      limit: 10
      skip: 0
    output: activeUsers

These operations allow you to retrieve documents with precise filtering and control over the results.

Updating Data

By default, updates use mongo $set operator to only update given fields without removing other fields already existing in the matched record :

# Update a single document
- Collection.updateOne:
    query:
      email: "john.doe@example.com"
    data:
      age: 31
      lastUpdated: "{{run.date}}"
    output: updateResult

But you can also use the MongoDB operators you want :

# Update a single document
- Collection.updateOne:
    query:
      email: "john.doe@example.com"
    data:
      $set:
        age: 31
        lastUpdated: "{% now() %}"
    output: updateResult

# Update multiple documents
- Collection.updateMany:
    query:
      active: false
    data:
      $set:
        status: "inactive"
        lastChecked: "{{run.date}}"
    output: updateResult

# Update with advanced operators
- Collection.updateOne:
    query:
      _id: "{{userId}}"
    data:
      $inc:
        loginCount: 1
      $push:
        loginHistory:
          date: "{{run.date}}"
          ip: "{{userIp}}"
    output: updateResult

These operations enable precise updates to documents, including field modifications, additions, and array operations.

Upserts

Upserts allow you to create or update a document if it already exists, based on a list of properties (onConflictFields) that must stay unique accros the collection :

- Collection.upsert:
    data:
      type: city
      name: Toulouse
    options:
      onConflictFields:
        - name
      onInsertValues:
        createdAt: '...'
    output: upsert

onInsertValues is optional and let you specify data that will be only included upon document creation but not on update.

With PostgresSQL, a compound unique index is required for all onConflictFields.

Deleting Data

Remove documents from a collection:

# Delete a single document
- Collection.deleteOne:
    query:
      email: "john.doe@example.com"
    output: deleteResult

# Delete multiple documents
- Collection.deleteMany:
    query:
      active: false
      lastLogin: { $lt: "{% dateAdd('now', -90, 'days') %}" }
    output: deleteResult

These operations allow you to remove documents based on specific criteria.

Distinct values

Easily retrieve all distinct values for a column with their counts and sorting :

# Aggregate data
- Collection.distinct:
    query:
      projectId: '{{projectId}}'
      userDocument:
        $ne: true
      createdAt:
        $gt: '{{dateStart}}'
        $lt: '{{dateEnd}}'
    field: 'tags'
    opts:
      count: true
      valueField: distinctTags
      sort:
        count: -1
    output: res    

Aggregation Operations

Perform complex data analysis. Supported aggregate types: sum, avg, count, max, min.max and min work on any orderable field type: numbers, datetimes (BSON dates and ISO 8601 strings), and strings (lexicographic order).

# Aggregate data
- Collection.aggregate:
    query:
      projectId: '{{projectId}}'
      isArchived:
        $ne: true
      createdAt:
        $gt: '{{dateStart}}'
        $lt: '{{dateEnd}}'
    opts:
      groupBy: department
      groupField: departmentGroup
      sort:
        count: -1
    steps:
      - inputField: size
        type: sum
        outputField: totalSize
      - inputField: age
        type: avg
        outputField: avgAge
      - inputField: age
        type: max
        outputField: oldestAge
      - inputField: age
        type: min
        outputField: youngestAge
      - inputField: _id
        type: count
        outputField: count
    output: res

Example: latest activity timestamp per agent (single pass, no find + sort + limit):

- Collection.aggregate:
    query:
      agent_id: '{{agent_id}}'
    steps:
      - inputField: last_message_at
        type: max
        outputField: latest_message_at
    output: res
# res[0].latest_message_at → ISO string of the most recent message

Find pagination & fields selection

Pagination is enforced by returning only the first 50 matching entries by default. This number is configurable with options.limit.
You can then choose which page you are interested in using options.page, starting at 1 for the first page :

# First page :  
- Collection.find:
    query: {}
    options:
      limit: 50
      page: 1
    output: firstPage
- Collection.find:
    query: {}
    options:
      limit: 50
      page: 2
    output: secondPage        

Alternatively, you can use options.skip to finely select the matching page :

# Retrieve from the 11th to the 51th  record
- Collection.find:
    query: {}
    options:
      limit: 50
      skip: 10
    output: firstPage

You can also limit the document properties you will have in return :

- Collection.find:
    query: {}
    options:
      fields:
        - projectId
        - userDocument
        - createdAt

Advanced Features

Collection includes several advanced features that enable sophisticated data management:

Indexing

Create indexes to optimize query performance:

Single-field indexes
Compound indexes
Text indexes for full-text search
Unique indexes for constraint enforcement

Transactions

Ensure data consistency with multi-document transactions:

Atomic operations across multiple documents
Rollback on error
Consistent reads within a transaction
Isolation levels

Geospatial

Store and query location data:

GeoJSON format support
Proximity queries
Geospatial indexing
Area containment queries

Schema Validation

Optional schema validation for data consistency:

JSON Schema validation
Custom validation rules
Validation actions (error or warning)
Field restriction

These advanced features provide additional capabilities for specific use cases and requirements.

Common Use Cases

Collection enables a wide range of use cases:

User Management

Store and manage user information:

User profiles
Preferences
Activity history
Authentication data

Content Management

Manage structured content:

Articles and posts
Product information
Media metadata
Categorization and tagging

Workflow State

Track process and workflow state:

Status tracking
Approval flows
Stage information
Audit history

Data Collection

Collect and store form submissions:

Survey responses
Application data
Contact requests
Registration information

Integration with Prisme.ai Products

Collection works seamlessly with other Prisme.ai products:

Knowledges
Builder
Custom Code
API Integrations

Enhance knowledge bases with Collection:

Store metadata about knowledge base documents
Track usage patterns and popular queries
Maintain user feedback on responses
Save and manage test results

This integration improves knowledge management and quality assurance.

Example: Contact Management System

Here’s an example of using Collection to build a contact management system:

Define Data Structure

Plan your data organization:

Contacts collection for individual contacts
Companies collection for organization information
Interactions collection for communication history
Tags collection for categorization

Create Storage Operations

Implement data storage automations:

# Add a new contact
slug: add-contact
name: Add Contact
do:
  - Collection.insert:
      data:
        firstName: "{{payload.firstName}}"
        lastName: "{{payload.lastName}}"
        email: "{{payload.email}}"
        phone: "{{payload.phone}}"
        company: "{{payload.company}}"
        title: "{{payload.title}}"
        tags: "{{payload.tags}}"
        createdAt: "{% now() %}"
      output: result
  - emit:
      event: contact-added
      payload:
        contact: "{{result}}"

Implement Query Operations

Create data retrieval automations:

# Search for contacts
slug: search-contacts
name: Search Contacts
do:
  - set:
      name: query
      value: {}
  - conditions:
      '{{payload.searchTerm}}':
        - set:
            name: query
            value:
              $or:
                - firstName: { $regex: "{{payload.searchTerm}}", $options: "i" }
                - lastName: { $regex: "{{payload.searchTerm}}", $options: "i" }
                - email: { $regex: "{{payload.searchTerm}}", $options: "i" }
                - company: { $regex: "{{payload.searchTerm}}", $options: "i" }
      '{{payload.tags}}':
        - set:
            name: query.tags
            value: { $in: "{{payload.tags}}" }
      default: []
  - Collection.find:
      query: "{{query}}"
      sort:
        lastName: 1
        firstName: 1
      options:
        limit: "{{payload.limit || 20}}"
        skip: "{{payload.skip || 0}}"
      output: contacts
  - emit:
      event: search-results
      payload:
        contacts: "{{contacts}}"
        query: "{{query}}"

Create User Interface

Build a UI to interact with your data:

Contact list view
Contact detail view
Add/edit contact forms
Search and filtering

Implement Business Logic

Add specialized functionality:

Duplicate detection
Contact merging
Import/export capabilities
Notification system

This example demonstrates how Collection can serve as the data layer for a complete application.

Best Practices

Follow these recommendations to get the most from Collection:

Data Modeling

Design your data structure effectively:

Use descriptive collection names
Choose between embedding and referencing based on access patterns
Keep document size reasonable (under 1MB when possible)
Normalize data when it changes frequently
Denormalize data to optimize common queries
Use consistent field names across collections

Effective data modeling improves performance and maintainability.

Query Optimization

Optimize your queries for better performance:

Create indexes for frequently queried fields
Write specific queries that use indexes
Limit the number of documents returned
Avoid complex regex patterns when possible
Use aggregation for data processing, not application code

These practices ensure efficient data retrieval.

Data Validation

Ensure data quality and consistency:

Validate input data before storage
Consider using schema validation for critical collections
Implement application-level validation for complex rules
Use unique indexes to prevent duplicates
Include creation and update timestamps
Maintain audit trails for sensitive data

Validation helps maintain data integrity.

Security Considerations

Protect your data with proper security practices:

Apply proper access controls
Validate input to prevent injection attacks
Don’t store sensitive data without encryption
Implement appropriate backup strategies
Audit access to sensitive collections
Follow the principle of least privilege

Security should be a fundamental consideration in all data operations.

Limitations and Considerations

When using Collection, be aware of these considerations:

Document Size: Individual documents are limited to 16MB
Nested Depth: Deep nesting of objects can impact performance
Query Complexity: Very complex queries may have performance implications
Transaction Limits: Transactions have time and size limitations
Indexing Overhead: Indexes improve query performance but increase storage requirements and write overhead
Consistency Model: Collection uses an eventually consistent model in some scenarios

Next Steps

API Integrations

Learn about connecting to external APIs

Crawler

Discover web content extraction capabilities

Custom Code

Execute custom logic within your workflows

Extending Apps

Create your own custom integrations

​Overview

Data Storage

Query Capabilities

Data Management

Integration Ready

​Release Note: Collection with PostgreSQL

Schema Configuration

Cross-Database Compatibility

Breaking Changes

New Features

​Breaking Changes

​🆕 New Features

​Key Features

​How Collection Works

​⚖️ MongoDB vs PostgreSQL Differences

​🛠️ Configuring a Collection Schema

​🔄 Changing a Property Type

​Text converted to json automatically

​Declaring a migration

​It only runs once, by itself

​Converting to json

​Following what happened

​Checking and converting from an automation

​Basic Operations

​Advanced Features

Indexing

Transactions

Geospatial

Schema Validation

​Common Use Cases

User Management

Content Management

Workflow State

Data Collection

​Integration with Prisme.ai Products

​Example: Contact Management System

​Best Practices

​Limitations and Considerations

​Next Steps

API Integrations

Crawler

Custom Code

Extending Apps

Overview

Release Note: Collection with PostgreSQL

Breaking Changes

🆕 New Features

Key Features

How Collection Works

⚖️ MongoDB vs PostgreSQL Differences

🛠️ Configuring a Collection Schema

🔄 Changing a Property Type

Text converted to json automatically

Declaring a migration

It only runs once, by itself

Converting to json

Following what happened

Checking and converting from an automation

Basic Operations

Advanced Features

Common Use Cases

Integration with Prisme.ai Products

Example: Contact Management System

Best Practices

Limitations and Considerations

Next Steps