MacroDB Database Architecture and Migration Management in the Macro Platform

MacroDB is a PostgreSQL relational database accessed through the dedicated macro_db_client crate, with migrations managed via SQLx's integrated migration system and automated through Just recipes.

The Macro platform stores all persistent data—users, projects, documents, chats, email threads, and permissions—in MacroDB. This article examines the database architecture and migration workflow as implemented in the macro-inc/macro repository, based on source code analysis of the macro_db_client crate and related infrastructure.

MacroDB Core Architecture

PostgreSQL with SQLx Compile-Time Validation

MacroDB runs on PostgreSQL, deployed locally via Docker Compose for development and on managed RDS clusters in production. The database is never accessed directly by services; instead, all interaction flows through the macro_db_client crate located at crates/macro_db_client/.

The crate provides a typed, compile-time-checked API using SQLx with offline mode enabled (SQLX_OFFLINE=true). This guarantees that every query in the codebase matches the actual schema without requiring a live database during builds.

Key architectural components in crates/macro_db_client/src/lib.rs:

• DbPool — A sqlx::Pool<Postgres> exported for service use, built once at startup with DbPool::new().await
• Query macros — query!, query_as!, and related macros provide schema-level safety
• Environment integration — Credentials are loaded exclusively through macro_env_var macros, never via std::env::var

Connection Management Pattern

Services obtain the pool through Axum's State system:

// Pool creation at service startup
let db = DbPool::new().await?;

// Pool passed to handlers via Axum State
let app = Router::new()
    .route("/users", get(get_user_handler))
    .with_state(db);

This centralizes connection pooling and enables graceful shutdown handling.

SQLx Offline Cache

The repository maintains a .sqlx/ directory under crates/macro_db_client/.sqlx/ containing cached query metadata. This allows the project to compile in CI environments without database connectivity. After any schema change, developers regenerate the cache using just prepare_db.

Migration Management Workflow

MacroDB migrations follow SQLx's standard conventions, fully integrated into the Macro development workflow through Just recipes.

Creating and Applying Migrations

Migrations are timestamped SQL files stored in crates/macro_db_client/migrations/. Each file contains paired up and down statements:

# Create a new migration

cd crates/macro_db_client
sqlx migrate add add_user_created_at

# Result: crates/macro_db_client/migrations/YYYYMMDDHHMMSS_add_user_created_at.sql

The standard development workflow:

1. Write migration — Edit the generated file with CREATE TABLE/DROP TABLE or analogous statements
2. Test locally — just test spins up PostgreSQL and applies migrations via just migrate_db
3. Update offline cache — just prepare_db regenerates .sqlx metadata for all crates
4. Deploy — CI runs just migrate_db against production RDS before service startup

Example Migration File

From crates/macro_db_client/migrations/20260728143113_add_user_created_at.sql:

-- Up
ALTER TABLE "User" ADD COLUMN "created_at" TIMESTAMP WITH TIME ZONE DEFAULT now();

-- Down
ALTER TABLE "User" DROP COLUMN "created_at";

This pattern appears throughout the migration directory, with files like 20260702014623_create_favorites_table.sql introducing the favorites table and 20260715154212_add_document_live_id_owner_index.sql adding performance indexes.

Using the Database Client

Services interact with MacroDB through typed functions exported by macro_db_client. Here's a complete example:

use macro_db_client::{DbPool, get_user_by_email};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Build the connection pool (environment variables via macro_env_var)
    let db = DbPool::new().await?;

    // Fetch a user by email
    let user = get_user_by_email(&db, "alice@example.com").await?;
    println!("Found user: {} (id={})", user.name, user.id);

    Ok(())
}

The query implementation resides in crates/macro_db_client/src/user/get/get_user_by_email.rs, with similar patterns for all CRUD operations across the schema.

Key Files and Commands Reference

Path	Purpose
crates/macro_db_client/src/lib.rs	DbPool definition and connection setup
crates/macro_db_client/migrations/	All timestamped migration SQL files
crates/macro_db_client/.sqlx/	Offline query cache for compile-time validation
justfile (workspace root)	migrate_db and prepare_db recipes
docs/STYLE_GUIDE.md	DB credential loading requirements

Essential commands:

sqlx migrate add <name>     # Create new migration

just migrate_db             # Apply pending migrations

just prepare_db             # Regenerate SQLx offline cache

just test                   # Full test run with ephemeral database

Summary

• MacroDB is PostgreSQL, accessed exclusively through the macro_db_client crate using SQLx with compile-time query validation
• Offline mode (SQLX_OFFLINE=true) enables builds without live database connectivity via cached metadata in .sqlx/
• Migrations are timestamped SQL files in crates/macro_db_client/migrations/, managed through standard SQLx commands wrapped by Just recipes
• Environment security mandates macro_env_var macros for all credential access, keeping secrets out of source code
• Deployment safety requires just migrate_db to run against production RDS before service binaries start

Frequently Asked Questions

How does MacroDB ensure queries match the schema at compile time?

SQLx's query! and query_as! macros connect to the database during development (or use cached metadata from .sqlx/ in CI) to verify that each query's columns, types, and tables exist. If a query references a non-existent column or mismatched type, compilation fails. The just prepare_db command updates this cache after migrations.

What happens if a migration fails in production?

The sqlx migrate run command used in just migrate_db applies migrations transactionally. If any migration fails, the transaction rolls back and the deployment halts before services start. This prevents partial schema changes. CI pipelines should test migrations against staging environments that mirror production.

Can I build the Macro project without PostgreSQL installed?

Yes. With SQLX_OFFLINE=true (set by default in the repository), the compiler uses the checked-in .sqlx/ cache instead of connecting to a database. You only need PostgreSQL when: (1) writing new queries, (2) adding migrations, or (3) running just prepare_db to update the cache.

Why doesn't macro_db_client use an ORM like Diesel or SeaORM?

The Macro team chose SQLx for its zero-cost compile-time checking without the abstraction overhead of traditional ORMs. The query_as! macro maps rows to Rust structs with full type safety, while raw SQL provides complete control over query plans and PostgreSQL-specific features. This matches the project's preference for explicit, inspectable database access.

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