How Macro Creates Bidirectional Links When Generating Tasks from Emails or Messages

Macro turns incoming emails and chat messages into tasks by parsing payload data, inserting a new task record, and atomically writing paired rows to a task_properties table to create queryable parent-child relationships in both directions.

The Macro platform (available at macro-inc/macro on GitHub) treats communication channels as first-class task sources. When a user forwards an email or mentions a task in a message, the system doesn't just create an isolated to-do item—it automatically wires that new task into the existing hierarchy through bidirectional linking. This design enables efficient traversal from parent to children and vice versa without expensive self-joins.

Overview of the Task Generation Pipeline

The end-to-end flow spans three architectural layers:

Stage	Component	Responsibility
Ingestion	email_service	Normalizes webhooks into Message structs and extracts link hints
Orchestration	TaskProperties service	Manages transactions and coordinates persistence
Persistence	task_property_queries	Executes low-level SQL for forward and reverse links

All three stages participate in a single database transaction, guaranteeing that a task and its relationships are created together—or not at all.

Step 1: Extracting Link Hints from Incoming Messages

The ingestion layer lives in the email service utility code. When a Gmail webhook arrives, process_pre_insert normalizes the payload and scans for task-related tokens.

Parsing Parent Markers in sfs_map.rs

The file services/email_service/src/util/process_pre_insert/sfs_map.rs handles detection. It scans message bodies for patterns like #task-123 or @parent:456, collecting candidate relationships before the task exists.

// services/email_service/src/util/process_pre_insert/sfs_map.rs
if let Some(parent_id) = parse_parent_marker(&body) {
    pending_links.push((new_task_id, parent_id));
}

This preprocessing step decouples content analysis from database operations. The pending_links vector accumulates (child_id, parent_id) tuples that will be passed to the persistence layer once the task row is ready.

Step 2: Atomic Task Creation with Bidirectional Links

The core orchestration happens in crates/properties/src/domain/service_impl/task_properties.rs. The create_task method accepts the new task payload plus any pre-discovered links, then executes everything inside a transaction.

Transactional Insert Pattern

// crates/properties/src/domain/service_impl/task_properties.rs
pub async fn create_task(
    db: &Pool<Postgres>,
    payload: NewTask,
    links: Vec<(Uuid, Uuid)>, // (child, parent)
) -> Result<Task, ServiceError> {
    let mut tx = db.begin().await?;
    let task = query::insert_task(&mut tx, payload).await?;

    for (child, parent) in links {
        // Forward link: parent → child
        query::insert_task_parent(&mut tx, parent, child).await?;
        // Reverse link: child → parent
        query::insert_task_child(&mut tx, child, parent).await?;
    }

    tx.commit().await?;
    Ok(task)
}

Key design decisions in this implementation:

• Single transaction: All inserts succeed or fail together, preventing orphaned tasks or partial links
• Explicit pair ordering: The links vector uses (child, parent) ordering consistently, with the query helpers handling directionality
• Async/await: Full non-blocking I/O via sqlx for high-throughput message processing

Step 3: Low-Level SQL for Bidirectional Storage

The actual SQL execution resides in crates/properties/src/outbound/task_property_queries.rs. Two nearly identical helpers write the complementary rows:

// crates/properties/src/outbound/task_property_queries.rs
pub async fn insert_task_parent(
    tx: &mut Transaction<'_, Postgres>,
    parent_id: Uuid,
    child_id: Uuid,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        INSERT INTO task_properties (source_task_id, target_task_id, kind)
        VALUES ($1, $2, 'parent')
        "#,
        parent_id,
        child_id
    )
    .execute(&mut *tx)
    .await?;
    Ok(())
}

pub async fn insert_task_child(
    tx: &mut Transaction<'_, Postgres>,
    child_id: Uuid,
    parent_id: Uuid,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        INSERT INTO task_properties (source_task_id, target_task_id, kind)
        VALUES ($1, $2, 'subtask')
        "#,
        child_id,
        parent_id
    )
    .execute(&mut *tx)
    .await?;
    Ok(())
}

Schema Design Implications

The task_properties table uses a generic triple-store pattern:

• source_task_id: The task that owns this relationship
• target_task_id: The related task
• kind: Relationship semantics ('parent' or 'subtask')

By storing both directions explicitly:

• Parent → Children: Query where source_task_id = X AND kind = 'parent'
• Child → Parent: Query where source_task_id = Y AND kind = 'subtask' (or equivalently target_task_id = Y AND kind = 'parent')

This eliminates the need for recursive CTEs or self-joins for one-hop relationship queries.

Query Patterns Enabled by Bidirectional Storage

Downstream consumers benefit from O(1) lookups in either direction:

Use Case	Query Pattern
List subtasks of a project	source_task_id = ? AND kind = 'parent'
Find parent of a subtask	source_task_id = ? AND kind = 'subtask'
Full ancestry traversal	Alternate between the two patterns
Graph visualization	Union of both directions, filtered by kind

The search indexer and UI layer rely on these consistent query shapes to render task hierarchies without N+1 problems.

Comparison with Alternative Approaches

Approach	Pros	Cons	Macro's Choice
Single-row parent pointer	Simple writes, minimal storage	Expensive child lookups, no reverse traversal	Rejected
Adjacency list with CTEs	Standard SQL, flexible	Recursive queries don't scale at depth	Rejected
Bidirectional explicit storage	O(1) both directions, simple queries	Double writes, requires transaction coordination	Adopted
Closure table	Fast arbitrary-depth queries	More complex maintenance, larger storage	Not needed for Macro's use case

Macro's design optimizes for the common case of immediate parent-child relationships while keeping the schema simple enough for application-level maintenance.

Summary

• Ingestion: sfs_map.rs scans emails/messages for task references before database insertion
• Orchestration: task_properties.rs wraps task creation and linking in a single transaction
• Persistence: task_property_queries.rs writes paired rows with kind = 'parent' and kind = 'subtask' for bidirectional access
• Benefit: O(1) relationship queries in both directions without recursive SQL

Frequently Asked Questions

What happens if the transaction fails during link creation?

The entire operation rolls back. Because create_task uses db.begin().await? and only calls tx.commit().await? after all inserts succeed, any failure—whether in the primary task insert or either directional link—leaves the database unchanged. Callers receive a ServiceError and can retry or surface the failure appropriately.

Why does Macro store both directions explicitly instead of using a single parent pointer?

A single parent pointer makes "find all children of this task" require a full table scan or index on the parent column. By storing both parent and subtask rows, Macro creates covering indexes for both query patterns. This trades write amplification (two inserts instead of one) for read performance, which favors the query-heavy workload of a task management UI.

How does the email service distinguish task mentions from regular text?

The parse_parent_marker function in sfs_map.rs implements pattern matching for structured tokens like #task-UUID or @parent:UUID. These markers are either user-generated or injected by Macro's browser extension and email integration. Unmatched text is ignored, so casual mentions don't create spurious relationships.

Can this linking system handle many-to-many relationships?

The current schema supports one parent per task through the TaskProperties service API, but the underlying task_properties table is generic. Multiple (source, target, kind) rows with the same source_task_id and kind = 'parent' would technically work at the database level. The service layer enforces tree semantics for now, but the storage layer doesn't inherently restrict graph structures.

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