> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prisme.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Collection

> Simplified database access and management for your Prisme.ai workflows

The Collection app is a powerful infrastructure component in the Prisme.ai ecosystem that provides simplified access to a document database. It enables you to store, retrieve, and manage structured data without the complexity of setting up and maintaining a separate database system.

## Overview

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

<CardGroup cols={2}>
  <Card title="Data Storage" icon="database">
    Store structured data in document collections
  </Card>

  <Card title="Query Capabilities" icon="magnifying-glass">
    Retrieve and filter data with powerful query options
  </Card>

  <Card title="Data Management" icon="pen-to-square">
    Create, update, and delete records with simple operations
  </Card>

  <Card title="Integration Ready" icon="puzzle-piece">
    Seamlessly connect with automations and workflows
  </Card>
</CardGroup>

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.

***

<CardGroup cols={2}>
  <Card title="Schema Configuration" icon="gear">
    Collections now require explicit schema definitions in the application config.
  </Card>

  <Card title="Cross-Database Compatibility" icon="arrows-left-right">
    Collections can now work across **MongoDB** and **PostgreSQL**, with some differences detailed below.
  </Card>

  <Card title="Breaking Changes" icon="triangle-exclamation">
    Some MongoDB options were deprecated or modified. See below for full details.
  </Card>

  <Card title="New Features" icon="sparkles">
    Support for new **aggregate** instruction across both MongoDB and PostgreSQL. Existing **distinct** improved with new features
  </Card>
</CardGroup>

***

### Breaking Changes

<Warning>
  These breaking changes may affect your existing MongoDB-based Collections.\
  Please update your configuration and code accordingly.
</Warning>

<Steps>
  <Step title="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.
  </Step>

  <Step title="Required permissions">
    * The new collection schemas enforcement now requires 3 more mongodb user permissions (& their equivalent SQL) :
    * **listCollections**, **listIndexes**, **dropIndexes**
  </Step>

  <Step title="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`.
  </Step>

  <Step title="Allowed Update Operators">
    Only the following MongoDB update operators are supported: **\$push, \$set, \$inc, \$addToSet, \$pull**
  </Step>

  <Step title="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.
  </Step>

  <Step title="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)
  </Step>
</Steps>

***

### 🆕 New Features

<Tabs>
  <Tab title="Aggregate">
    Use **aggregate** instruction to easily calculate sums, average or count optionally groupped by a column, on both MongoDB and PostgreSQL collections.
  </Tab>

  <Tab title="Distinct">
    Use **distinct** to return unique values for a field.\
    Supports additional options to return each value with their `count`, and sort by these counts.
  </Tab>
</Tabs>

***

## Key Features

<Tabs>
  <Tab title="Document Storage">
    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.
  </Tab>

  <Tab title="Query Capabilities">
    Find exactly the data you need:

    * **Equality Queries**: Match specific field values
    * **Comparison Operators**: Greater than, less than, etc.
    * **Logical Operators**: AND, OR, NOT conditions
    * **Array Operations**: Query array contents and properties
    * **Text Search**: Search within text fields
    * **Regular Expressions**: Pattern matching in strings

    These query capabilities make it easy to retrieve precisely the data you need.
  </Tab>

  <Tab title="Data Manipulation">
    Manage data with comprehensive operations:

    * **Insert**: Add new documents
    * **Find**: Retrieve documents with filtering
    * **Update**: Modify existing documents
    * **Delete**: Remove documents
    * **Count**: Get document counts
    * **Distinct**: Find unique values
    * **Aggregation**: Group and analyze data

    These operations provide complete control over your data.
  </Tab>

  <Tab title="Advanced Features">
    Leverage powerful database capabilities:

    * **Indexing**: Create indexes for better performance
    * **Transactions**: Ensure data consistency
    * **Pagination**: Manage large result sets
    * **Sorting**: Order results by specified fields
    * **Update Operators**: Specialized field updates

    These advanced features enable sophisticated data management.
  </Tab>
</Tabs>

## How Collection Works

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

<Steps>
  <Step title="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
  </Step>

  <Step title="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
  </Step>

  <Step title="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
  </Step>

  <Step title="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
  </Step>
</Steps>

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

## ⚖️ MongoDB vs PostgreSQL Differences

<Tabs>
  <Tab title="$in Operator">
    * MongoDB: `$in: []` matches nothing.
    * PostgreSQL: `$in: []` matches everything!\\

          <Danger>
            Always check your input array before running `$in` queries on PostgreSQL.
          </Danger>
  </Tab>

  <Tab title="NULL vs $ne">
    * PostgreSQL does not consider NULL values when using `$ne`.
    * Initialize values explicitly if you expect `$ne` to work.
  </Tab>

  <Tab title="Array Queries">
    * MongoDB allows `{ "myArrayCol": "value" }` to search for "value" in the text array column `myArrayCol`.
    * In PostgreSQL, use `{ "myArrayCol": { $in: ["value"] } }`.
  </Tab>

  <Tab title="Array order on updates">
    * PostgreSQL array columns may lose value ordering when using `$push`, `$pull`, or `$addToSet`.
    * In MongoDB, order is preserved.
  </Tab>

  <Tab title="Nested JSON Array Queries ">
    * 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 :

      ```json theme={null}
      { "someObject": { "$elemMatch": { "field": "foo" } } }
      ```
  </Tab>

  <Tab title="Upserts">
    * Upserts with PostgreSQL require a **unique** index matching all **onConflictFields**
    * Upserts cannot target a document with any query operator other than the single field list **onConflictFields**.
  </Tab>

  <Tab title="$addToSet">
    * 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
  </Tab>
</Tabs>

***

# 🛠️ Configuring a Collection Schema

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

```yaml theme={null}
slug: Messages
config:
  collectionName: Messages
  indexes:
    - properties: children
    - properties:
        - conversationId
        - from.id
  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

<Note>
  Available property types include: `string, text, date, time, datetime, number, double, float, integer, decimal, boolean, uint8array, array, enum, enumArray, json, blob, time`
</Note>

## Basic Operations

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

<AccordionGroup>
  <Accordion title="Inserting Data">
    Add documents to a collection:

    ```yaml theme={null}
    # 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.
  </Accordion>

  <Accordion title="Finding Data">
    Retrieve documents from a collection:

    ```yaml theme={null}
    # 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.
  </Accordion>

  <Accordion title="Updating Data">
    By default, **updates** use mongo `$set` operator to only update given fields without removing other fields already existing in the matched record :

    ```yaml theme={null}
    # 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 :

    ```yaml theme={null}
    # 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.
  </Accordion>

  <Accordion title="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 :

    ```yaml theme={null}
    - 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.

    <Note>
      With **PostgresSQL**, a **compound unique** index is required for all **onConflictFields**.
    </Note>
  </Accordion>

  <Accordion title="Deleting Data">
    Remove documents from a collection:

    ```yaml theme={null}
    # 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.
  </Accordion>

  <Accordion title="Distinct values">
    Easily retrieve all distinct values for a column with their counts and sorting :

    ```yaml theme={null}
    # 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    
    ```
  </Accordion>

  <Accordion title="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).

    ```yaml theme={null}
    # 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`):

    ```yaml theme={null}
    - 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
    ```
  </Accordion>

  <Accordion title="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 :

    ```yaml theme={null}
    # 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 :

    ```yaml theme={null}
    # 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 :

    ```yaml theme={null}
    - Collection.find:
        query: {}
        options:
          fields:
            - projectId
            - userDocument
            - createdAt
    ```
  </Accordion>
</AccordionGroup>

## Advanced Features

Collection includes several advanced features that enable sophisticated data management:

<CardGroup cols={2}>
  <Card title="Indexing" icon="table-columns">
    Create indexes to optimize query performance:

    * Single-field indexes
    * Compound indexes
    * Text indexes for full-text search
    * Unique indexes for constraint enforcement
  </Card>

  <Card title="Transactions" icon="arrows-rotate">
    Ensure data consistency with multi-document transactions:

    * Atomic operations across multiple documents
    * Rollback on error
    * Consistent reads within a transaction
    * Isolation levels
  </Card>

  <Card title="Geospatial" icon="location-dot">
    Store and query location data:

    * GeoJSON format support
    * Proximity queries
    * Geospatial indexing
    * Area containment queries
  </Card>

  <Card title="Schema Validation" icon="check-to-slot">
    Optional schema validation for data consistency:

    * JSON Schema validation
    * Custom validation rules
    * Validation actions (error or warning)
    * Field restriction
  </Card>
</CardGroup>

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

## Common Use Cases

Collection enables a wide range of use cases:

<CardGroup cols={2}>
  <Card title="User Management" icon="users">
    Store and manage user information:

    * User profiles
    * Preferences
    * Activity history
    * Authentication data
  </Card>

  <Card title="Content Management" icon="newspaper">
    Manage structured content:

    * Articles and posts
    * Product information
    * Media metadata
    * Categorization and tagging
  </Card>

  <Card title="Workflow State" icon="diagram-project">
    Track process and workflow state:

    * Status tracking
    * Approval flows
    * Stage information
    * Audit history
  </Card>

  <Card title="Data Collection" icon="table-list">
    Collect and store form submissions:

    * Survey responses
    * Application data
    * Contact requests
    * Registration information
  </Card>
</CardGroup>

## Integration with Prisme.ai Products

Collection works seamlessly with other Prisme.ai products:

<Tabs>
  <Tab title="Knowledges">
    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.
  </Tab>

  <Tab title="Builder">
    Use Collection in your automations:

    * Store and retrieve workflow state
    * Maintain configuration settings
    * Log events and activities
    * Implement data-driven decision processes

    This enables sophisticated, data-driven automations.
  </Tab>

  <Tab title="Custom Code">
    Combine Collection with Custom Code for advanced operations:

    * Implement complex query logic
    * Process and transform data
    * Perform batch operations
    * Create specialized aggregations

    This combination provides maximum flexibility for data operations.
  </Tab>

  <Tab title="API Integrations">
    Connect Collection with external systems:

    * Store data retrieved from APIs
    * Buffer information for external systems
    * Maintain synchronization state
    * Cache external data for performance

    This integration bridges internal and external data sources.
  </Tab>
</Tabs>

## Example: Contact Management System

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

<Steps>
  <Step title="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
  </Step>

  <Step title="Create Storage Operations">
    Implement data storage automations:

    ```yaml theme={null}
    # 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}}"
    ```
  </Step>

  <Step title="Implement Query Operations">
    Create data retrieval automations:

    ```yaml theme={null}
    # 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}}"
    ```
  </Step>

  <Step title="Create User Interface">
    Build a UI to interact with your data:

    * Contact list view
    * Contact detail view
    * Add/edit contact forms
    * Search and filtering
  </Step>

  <Step title="Implement Business Logic">
    Add specialized functionality:

    * Duplicate detection
    * Contact merging
    * Import/export capabilities
    * Notification system
  </Step>
</Steps>

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:

<AccordionGroup>
  <Accordion title="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.
  </Accordion>

  <Accordion title="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.
  </Accordion>

  <Accordion title="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.
  </Accordion>

  <Accordion title="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.
  </Accordion>
</AccordionGroup>

## 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

<CardGroup cols={2}>
  <Card title="API Integrations" icon="plug" href="/apps-store/marketplace/api">
    Learn about connecting to external APIs
  </Card>

  <Card title="Crawler" icon="spider" href="/apps-store/marketplace/crawler">
    Discover web content extraction capabilities
  </Card>

  <Card title="Custom Code" icon="code" href="/apps-store/marketplace/custom-code">
    Execute custom logic within your workflows
  </Card>

  <Card title="Extending Apps" icon="puzzle-piece" href="/apps-store/marketplace/extending-apps">
    Create your own custom integrations
  </Card>
</CardGroup>