Macro Properties System Architecture: How Custom Fields Work on Entities

Macro's properties system implements a three-layer database architecture—definitions, options, and values—that enables any entity to store custom fields through JSONB columns in PostgreSQL, exposed via a unified Rust crate and REST API.

The macro-inc/macro repository contains a sophisticated properties subsystem that powers custom fields across documents, CRM companies, tasks, and chats. This architecture separates schema definition from data storage, allowing organizations to attach typed metadata to any entity while maintaining query performance and data integrity.

The Three-Layer Database Architecture

The Macro properties system architecture centers on three tightly-coupled PostgreSQL tables managed by the properties crate. Each layer handles a distinct responsibility in the custom field lifecycle.

Definition Layer: property_definitions

The definition layer stores the schema for each custom field. The property_definitions table contains columns such as id, organization_id, user_id, display_name, data_type, and is_system. This layer determines what custom fields exist, what data types they accept, and which entity types they apply to.

Creation flows through POST /properties via the CreatePropertyDefinition SDK operation, implemented in crates/properties/src/outbound/property_definition_queries.rs. This module handles SQL-based CRUD operations, ensuring that schema changes propagate correctly across the organization.

Option Layer: property_options

For Select, Multi-Select, and Tag data types, the property_options table stores the allowed values. Columns include id, property_definition_id, string_value, number_value, and color. This layer enforces uniqueness through constraints like unique_property_options_string_value and unique_property_options_number_value.

Options are created via CreatePropertyOption (POST /properties/{definition_id}/options), with core logic residing in crates/properties/src/outbound/property_option_queries.rs. When entities store values for these types, they reference option UUIDs rather than raw strings, ensuring data consistency.

Value Layer: entity_properties

The value layer persists actual assignments to concrete entities in the entity_properties table. Columns include id, entity_id, entity_type, property_definition_id, and a jsonb column named values that encodes the data.

The JSON structure varies by data type:

// Example for a text property
{ "type": "String", "value": "Customer priority: High" }

// Example for a multi-select/tag property
{ "type": "SelectOption", "value": ["opt-uuid-1","opt-uuid-2"] }

This flexible storage format allows the same table structure to support strings, numbers, dates, and complex option references without schema migrations.

Core API Operations and Implementation

The properties crate exposes a unified HTTP API consumed by services across the platform. Key operations are implemented in specific outbound query modules.

Setting and Retrieving Values

The SetEntityProperty operation (POST /entity-properties) upserts rows in entity_properties, handling single values, multi-select arrays via add_option_ids/remove_option_ids, and tag semantics. This logic lives in crates/properties/src/outbound/entity_properties_upsert.rs.

Reading values occurs through GetEntityProperties (GET /entity-properties?entityId=&entityType=), implemented in crates/properties/src/outbound/entity_properties_get_query.rs. This module also provides entity_properties_get_query::get_bulk_entity_properties_values_filtered for high-performance batch retrieval used by search indexing.

Clearing and Cleanup

When entities are deleted, the ClearEntityProperties operation (DELETE /entity-properties) removes all associated value rows. Deletion of a property definition cascades to its options, ensuring referential integrity across the three layers.

Integration Points Across the Platform

The properties system serves as a single source of truth for custom fields throughout Macro.

• Search Processing Service calls get_bulk_entity_properties_values_filtered to enrich documents with searchable property metadata.
• Document Storage Service invokes SetEntityProperty when initializing starter documents or processing user updates.
• CRM and Task Services reuse the same tables through the system_properties crate, demonstrating the architecture's flexibility across domains.

All interactions route through the Properties Service (crates/properties_service), with HTTP definitions in crates/properties/src/inbound/axum_router.rs and public SDK contracts exported via packages/sdk/specs/properties.json.

Key Source Files and Their Roles

Path	Responsibility
crates/properties/src/outbound/property_definition_queries.rs	CRUD operations for property_definitions table
crates/properties/src/outbound/property_option_queries.rs	Option insertion, updates, and uniqueness enforcement
crates/properties/src/outbound/entity_properties_get_query.rs	Single and bulk reading of property values
crates/properties/src/outbound/entity_properties_upsert.rs	Value persistence and multi-select delta handling
crates/properties/src/outbound/properties_pg_repo.rs	High-level repository combining all query types
crates/properties/src/inbound/axum_router.rs	HTTP route definitions for the REST API
crates/properties_service/src/api/swagger.rs	OpenAPI specification export for SDK generation
packages/sdk/specs/properties.json	Public API contract for external integrations
crates/properties/src/outbound/tag_promotion_queries.rs	Specialized logic for tag-to-entity promotion

Summary

• The Macro properties system architecture separates concerns into three layers: schema definitions (property_definitions), allowed values (property_options), and instance data (entity_properties).
• Values are stored as JSONB in PostgreSQL, enabling flexible typing without table alterations.
• The system supports complex data types like Multi-Select and Tag through immutable option references rather than string duplication.
• All database access is centralized in the properties crate, with specific modules handling definitions, options, and entity values separately.
• The architecture powers features across Search, Document Storage, CRM, and Tasks through a unified service layer and public SDK.

Frequently Asked Questions

How does Macro handle different data types in the same entity_properties table?

The entity_properties table uses a jsonb column named values to store type-specific payloads. For text fields, it stores {"type": "String", "value": "..."}; for multi-select fields, it stores {"type": "SelectOption", "value": ["uuid-1", "uuid-2"]}. This polymorphic JSON approach allows the same table schema to support strings, numbers, dates, and option references without requiring separate columns or migrations for new data types.

What happens when a property definition is deleted?

Deleting a property definition via DELETE /properties/{id} cascades to the property_options table, removing all associated allowed values. However, this does not automatically delete historical values from entity_properties; those rows typically remain until the entity itself is deleted or a cleanup job runs, depending on the organization's retention policies.

How do select and multi-select fields maintain data integrity?

Instead of storing human-readable strings directly in entity_properties, the system stores UUID references to rows in property_options. The property_option_queries.rs module enforces uniqueness constraints (unique_property_options_string_value, unique_property_options_number_value) and validates that referenced options exist for their parent definition. This ensures that renaming an option updates its display everywhere, and that deletions are handled atomically at the schema level.

Can system properties coexist with user-created custom fields?

Yes. The property_definitions table includes an is_system boolean column that distinguishes between platform-created fields (like "Status" or "Priority") and user-defined custom fields. Both types share the same three-layer architecture and API surface, though system properties may have restricted deletion capabilities enforced at the application layer in property_definition_queries.rs.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}