# Macro Properties System Architecture: How Custom Fields Work on Entities

> Explore Macro's properties system architecture. Discover how custom fields leverage a three-layer PostgreSQL database and Rust crate for seamless entity integration.

- Repository: [Macro/macro](https://github.com/macro-inc/macro)
- Tags: architecture
- Published: 2026-08-18

---

**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`](https://github.com/macro-inc/macro/blob/main/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`](https://github.com/macro-inc/macro/blob/main/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:

```json
// 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`](https://github.com/macro-inc/macro/blob/main/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`](https://github.com/macro-inc/macro/blob/main/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`](https://github.com/macro-inc/macro/blob/main/crates/properties/src/inbound/axum_router.rs) and public SDK contracts exported via [`packages/sdk/specs/properties.json`](https://github.com/macro-inc/macro/blob/main/packages/sdk/specs/properties.json).

## Key Source Files and Their Roles

| Path | Responsibility |
|------|----------------|
| [`crates/properties/src/outbound/property_definition_queries.rs`](https://github.com/macro-inc/macro/blob/main/crates/properties/src/outbound/property_definition_queries.rs) | CRUD operations for `property_definitions` table |
| [`crates/properties/src/outbound/property_option_queries.rs`](https://github.com/macro-inc/macro/blob/main/crates/properties/src/outbound/property_option_queries.rs) | Option insertion, updates, and uniqueness enforcement |
| [`crates/properties/src/outbound/entity_properties_get_query.rs`](https://github.com/macro-inc/macro/blob/main/crates/properties/src/outbound/entity_properties_get_query.rs) | Single and bulk reading of property values |
| [`crates/properties/src/outbound/entity_properties_upsert.rs`](https://github.com/macro-inc/macro/blob/main/crates/properties/src/outbound/entity_properties_upsert.rs) | Value persistence and multi-select delta handling |
| [`crates/properties/src/outbound/properties_pg_repo.rs`](https://github.com/macro-inc/macro/blob/main/crates/properties/src/outbound/properties_pg_repo.rs) | High-level repository combining all query types |
| [`crates/properties/src/inbound/axum_router.rs`](https://github.com/macro-inc/macro/blob/main/crates/properties/src/inbound/axum_router.rs) | HTTP route definitions for the REST API |
| [`crates/properties_service/src/api/swagger.rs`](https://github.com/macro-inc/macro/blob/main/crates/properties_service/src/api/swagger.rs) | OpenAPI specification export for SDK generation |
| [`packages/sdk/specs/properties.json`](https://github.com/macro-inc/macro/blob/main/packages/sdk/specs/properties.json) | Public API contract for external integrations |
| [`crates/properties/src/outbound/tag_promotion_queries.rs`](https://github.com/macro-inc/macro/blob/main/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`](https://github.com/macro-inc/macro/blob/main/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`](https://github.com/macro-inc/macro/blob/main/property_definition_queries.rs).