[new] Sub-collections

Overview

Sub-collections allow you to define nested collections of items within deal documents. Each sub-collection can have its own custom fields, similar to how custom fields work for entities (Account, Contact, Deal, Activity). This feature enables you to track related items like milestones, deliverables, quotes, documents or any other structured data associated with a deal.

Key Concepts

• Sub-collections are defined in datamodel.json under deal.subCollections

• Each sub-collection can have multiple fields with different types (text, number, date, boolean, select, etc.)

• Sub-collection items are stored as Firestore sub-collections: deals/{dealId}/{subCollectionName}/{itemId}

• Items are displayed as a related list in the deal details view, similar to activities

• Sub-collections are automatically synced to BigQuery as separate tables (one table per sub-collection)

Configuration

Defining Sub-Collections in datamodel.json

(via Admin Panel > Team > Custom fields & sub-collections)

Sub-collections are defined in the datamodel.json file for each team, under the deal entity:

{
"deal": {
"subCollections": {
"milestones": {
"name": "Milestones",
"fields": {
"title": {
"name": "Title",
"type": "text",
"required": true
 },
"dueDate": {
"name": "Due Date",
"type": "date",
"required": false
 },
"status": {
"name": "Status",
"type": "select",
"options": ["Not Started", "In Progress", "Completed"],
"required": false
 }
 }
 },
"deliverables": {
"name": "Deliverables",
"fields": {
"name": {
"name": "Name",
"type": "text",
"required": true
 },
"description": {
"name": "Description",
"type": "textarea",
"required": false
 },
"completed": {
"name": "Completed",
"type": "boolean",
"defaultValue": false
 }
 }
 }
 },
"probability": {
"name": "Probability",
"type": "number"
 }
 }
}

Field Types

Sub-collection fields support the same types as custom fields:

• text - Single-line text input

• textarea - Multi-line text input

• number - Numeric value

• date - Date picker

• boolean - Checkbox

• select - Dropdown with options

• email - Email input with validation

• url - URL input with validation

• phone - Phone number input

Field Properties

Each field can have the following properties:

• name (required) - Display name for the field

• type (required) - Field type (see above)

• required (optional) - Whether the field is required (default: false)

• defaultValue (optional) - Default value for the field

• options (optional) - Array of options for select type fields

• minValue / maxValue (optional) - Min/max values for number type fields

• maxLength (optional) - Maximum length for text fields

UI Display

Field Priority in List View

Sub-collection items are displayed in a list view with fields prioritized by their order in the JSON definition:

1. First field → Displayed as title (bold, larger font)

2. Second field → Displayed as subtitle (smaller, gray text)

3. Third field → Displayed as secondary info (right-aligned, similar to activity date)

Example:

• If fields are defined as: title, dueDate, status

• The list will show:

• Title (bold)

• Due Date (subtitle)

• Status (right-aligned)

Location in UI

Sub-collections appear in the deal details view:

• Below the activities list

• In the same column as contacts and activities

Actions

• Click on item → Opens edit dialog

• Add button → Opens create dialog

• Delete → Available in the form dialog (with confirmation)

Data Structure

Firestore Structure

Sub-collection items are stored as Firestore sub-collections:

deals/
 {dealId}/
 {subCollectionName}/
 {itemId}/
 - title: "Milestone 1"
 - dueDate: Timestamp(...)
 - status: "In Progress"
 - createdAt: Timestamp(...)
 - updatedAt: Timestamp(...)

Note: Fields are stored flat at the root level (no fields wrapper). The dealId and subCollectionName are implicit from the Firestore path.

Data Model

class SubCollectionItem {
final String id;
final Map<String, dynamic> data; // All fields stored flat
final DateTime createdAt;
final DateTime? updatedAt;
}

BigQuery Integration

Table Structure

Each sub-collection gets its own BigQuery table (table name = sub-collection name):

stood_deals.milestones
stood_deals.deliverables

Table Schema

Base columns:

• id (STRING, REQUIRED) - Item ID

• deal_id (STRING, REQUIRED) - Parent deal ID

• account_id (STRING, NULLABLE) - Account ID for lookups

• team_id (STRING, REQUIRED) - Team ID

• team_name (STRING, REQUIRED) - Team name

• created_at (DATE, NULLABLE) - Creation date

• updated_at (DATE, NULLABLE) - Last update date

• synced_at (TIMESTAMP, REQUIRED) - Sync timestamp

Query Pattern

To get the latest state of sub-collection items:

SELECT * FROM `stood_deals.milestones` m1
WHERE m1.deal_id = 'deal123'
AND m1.synced_at = (
SELECT MAX(synced_at)
FROM `stood_deals.milestones` m2
WHERE m2.id = m1.id
 )

Or using window function:

SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY synced_at DESC) as rn
FROM `stood_deals.milestones`
WHERE deal_id = 'deal123'
) WHERE rn = 1

CRUD Operations

Create

1. Click the Add button in the sub-collection list

2. Fill in the form fields

3. Click Create

4. Item is saved to Firestore and synced to BigQuery

Read

• Items are automatically displayed in the list

• Real-time updates via Firestore streams

• Fields are displayed based on priority (title, subtitle, secondary info)

Update

1. Click on an item in the list (or use edit button if visible)

2. Modify fields in the form dialog

3. Click Update

4. Changes are saved to Firestore and synced to BigQuery

Delete

1. Open the item in edit mode

2. Click the Delete button (bottom left)

3. Confirm deletion

4. Item is removed from Firestore and a new snapshot is created in BigQuery

Limitations

• Sub-collections are currently only available for deals (not accounts, contacts, or activities)

• Sub-collections are not included in webhook payloads (only deal data is sent)

• BigQuery tables use INSERT streaming, so historical data accumulates (use queries to get latest state)