# Database migrations and SQLx cache

> Operations: MacroDB migrations, local database just recipes, SQLx offline cache, fixture data, and migration validation workflow.

- Repository: macro-inc/macro
- GitHub: https://github.com/macro-inc/macro
- Human docs: https://grok-wiki.com/public/docs/macro-inc-macro-bb988e1a448e
- Complete Markdown: https://grok-wiki.com/public/docs/macro-inc-macro-bb988e1a448e/llms-full.txt

## Source Files

- `rust/cloud-storage/macro_db_client/README.md`
- `rust/cloud-storage/macro_db_client/migrations/0001_baseline.sql`
- `rust/cloud-storage/database.just`
- `rust/cloud-storage/sqlx.just`
- `rust/cloud-storage/justfile`
- `rust/cloud-storage/chat/.sqlx/query-57fc603adf83fd7c07968425c98f9a57924573692cf0529ad021b6eef00ce99e.json`
- `.github/workflows/migrate-macro-db.yml`

---

---
title: "Database migrations and SQLx cache"
description: "Operations: MacroDB migrations, local database just recipes, SQLx offline cache, fixture data, and migration validation workflow."
---

MacroDB migrations live in `rust/cloud-storage/macro_db_client/migrations`, are executed with SQLx CLI recipes, and feed both local PostgreSQL setup and compile-time SQLx query metadata used by the Rust cloud-storage workspace.

## Migration ownership

| Surface | Path | Purpose |
| --- | --- | --- |
| Migration files | `rust/cloud-storage/macro_db_client/migrations/` | SQLx migration history for MacroDB. |
| Migration runner recipes | `rust/cloud-storage/macro_db_client/justfile` | Database create, migrate, reset, check, and remote dev/prod helpers. |
| Shared SQLx recipes | `rust/cloud-storage/sqlx.just` | Low-level `sqlx database`, `sqlx migrate`, and `cargo sqlx prepare` commands. |
| Local database URL | `rust/cloud-storage/database.just` | Default local MacroDB URL: `postgres://user:password@localhost:5432/macrodb`. |
| Workspace recipes | `rust/cloud-storage/justfile` | `setup_macrodb`, `initialize_dbs`, `prepare_db`, checks, and test `.env` setup. |
| Test migrator crate | `rust/cloud-storage/macro_db_migrator` | Lightweight static import of MacroDB migrations for crates that need migrations in tests without depending on `macro_db_client`. |
| GitHub migration action | `.github/actions/migrate-cloud-storage-db/action.yml` | CI/CD migration runner for dev and prod databases. |

## Baseline migration behavior

`0001_baseline.sql` is a guarded baseline from the Prisma-to-SQLx migration. It counts existing public tables except `_sqlx_migrations` and only creates the baseline schema when the database is empty.

<Warning>
Do not treat the baseline as a normal production bootstrap script. Its header states that dev and prod already had the declared objects when the project moved to SQLx. The guard prevents it from applying over an existing schema, but new migrations should still be additive SQLx migrations after the baseline.
</Warning>

Typical later migrations are timestamped files such as:

```text
20260511131821_enable_pgvector.sql
20260416135258_scheduled_agent.up.sql
20260416135258_scheduled_agent.down.sql
20260529164720_crm_domain_directory_apollo_fields.up.sql
20260529164720_crm_domain_directory_apollo_fields.down.sql
```

The migrations directory contains both single-file migrations and paired `.up.sql` / `.down.sql` migrations where rollback SQL is maintained.

## Local database recipes

The local MacroDB URL is imported from `database.just`:

```bash
postgres://user:password@localhost:5432/macrodb
```

Start local database services from the repository root:

```bash
just run_dbs -d
```

Initialize MacroDB from the repository root:

```bash
just rust/cloud-storage/initialize_dbs
```

Equivalent cloud-storage workflow:

```bash
cd rust/cloud-storage
just setup_macrodb
```

`setup_macrodb` runs:

```text
just macro_db_client/create_db
just macro_db_client/migrate_db
```

Root `setup_local_dbs` starts the database containers, creates and migrates MacroDB, prints `Local databases initialized`, then stops the database compose file.

### MacroDB just commands

Run these from `rust/cloud-storage/macro_db_client` unless invoking through the root justfile path.

| Command | Effect |
| --- | --- |
| `just create_db` | Runs `sqlx database create` against the local `DATABASE_URL`. |
| `just migrate_db` | Runs `sqlx migrate run` against the local `DATABASE_URL`. |
| `just check_db` through `sqlx.just` | Runs `sqlx migrate info`. |
| `just setup` | Runs `sqlx database setup`. |
| `just reset_db` | Drops and sets up the database. |
| `just force_drop_db` | Pipes `y` into the drop command for local force drops. |
| `just prepare_db` | Runs SQLx workspace prepare for offline query metadata. |
| `just prepare_db_dev` | Prepares query metadata against the dev RDS URL returned by `../scripts/rds_database_url.sh`. |
| `just check_db_dev` / `just check_db_prod` | Checks pending migrations against remote dev/prod. |
| `just migrate_db_dev` | Runs migrations against remote dev. |
| `just migrate_db_prod` | Prompts before running migrations against remote prod. |

## SQLx offline cache

The workspace uses SQLx compile-time query checking. Prepared query metadata is committed as JSON files under `.sqlx` directories, for example:

```json
{
  "db_name": "PostgreSQL",
  "query": "INSERT INTO \"ChatMessage\" ... RETURNING id",
  "describe": {
    "columns": [{ "ordinal": 0, "name": "id", "type_info": "Text" }],
    "parameters": { "Left": ["Text", "Text", "Jsonb", "Text", "Text", "Timestamp", "Timestamp"] },
    "nullable": [false]
  },
  "hash": "57fc603adf83fd7c07968425c98f9a57924573692cf0529ad021b6eef00ce99e"
}
```

Refresh the cache after adding or changing SQLx queries:

```bash
cd rust/cloud-storage
just prepare_db
```

The shared recipe expands to:

```bash
cargo sqlx prepare --workspace -- --all-features
```

Additional flags can be passed through the shared `sqlx.just` recipe. The cloud-storage `check`, `clippy`, `format`, and `build` recipes set `SQLX_OFFLINE=true`, so stale or missing `.sqlx` metadata can surface as compile-time failures before runtime.

<Note>
`macro_db_client/README.md` documents the expected test workflow: run local MacroDB in Docker, run `just setup_macrodb` from `cloud-storage`, then run `just prepare_db` before `cargo test` for code that depends on prepared SQLx metadata.
</Note>

## Test migrations and fixtures

Most database tests use `#[sqlx::test]` and fixture SQL files. Fixtures are stored near the crate that owns the behavior, for example:

```text
rust/cloud-storage/macro_db_client/fixtures/basic_user_with_document.sql
rust/cloud-storage/name_search/fixtures/chat.sql
rust/cloud-storage/channels/fixtures/channels_repo.sql
```

`macro_db_client` tests can use the crate-local migration directory directly. Other crates import migrations through `macro_db_migrator`:

```rust
pub static MACRO_DB_MIGRATIONS: sqlx::migrate::Migrator =
    sqlx::migrate!("../macro_db_client/migrations");
```

A typical cross-crate test shape is:

```rust
#[sqlx::test(
    migrator = "MACRO_DB_MIGRATIONS",
    fixtures(path = "../../fixtures", scripts("chat"))
)]
async fn test_search_chat_names_empty_term(pool: Pool<Postgres>) -> anyhow::Result<()> {
    // test body
}
```

The cloud-storage CI test job prepares test `.env` files and initializes MacroDB before running `cargo nextest`:

```bash
just rust/cloud-storage/setup_test_envs
just rust/cloud-storage/initialize_dbs
cd rust/cloud-storage
cargo nextest run --all-features --lib --bins --tests
```

`setup_test_envs` appends the same `DATABASE_URL` to many crate-local `.env` files so SQLx tests and service tests resolve MacroDB consistently.

## Fixture and seed data

There are two fixture paths with different purposes:

| Data type | Location | Used by |
| --- | --- | --- |
| SQL test fixtures | Per-crate `fixtures/*.sql` directories | `#[sqlx::test(fixtures(...))]` database tests. |
| Local E2E seed data | `rust/cloud-storage/seed_cli/seed/` | Deterministic local Playwright and Rust local E2E smoke data. |

The root `local-e2e-seed` recipe resets MacroDB and applies deterministic seed data:

```bash
just local-e2e-seed
```

It runs local databases, drops MacroDB, initializes migrations, then calls:

```bash
just rust/cloud-storage/seed_cli/local-e2e-smoke
```

The `local-e2e-smoke` seed command sets `LOCAL_E2E_SEED=true`, the local MacroDB URL, local AWS/FusionAuth settings, and `SQLX_OFFLINE=true`, then executes:

```bash
cargo r -- scenario local-e2e-smoke
```

The seed scenario validates that it is running against a local compose-style MacroDB URL before applying destructive reset SQL. Accepted hosts are `localhost`, `127.0.0.1`, `::1`, or `postgres`, with user `user`, database `macrodb`, and port `5432`.

## Remote migration workflow

Manual migration dispatch is defined in `.github/workflows/migrate-macro-db.yml` with an `environment` input of `dev` or `prod`.

The composite action:

1. Checks out `.github/` and `rust/cloud-storage/macro_db_client`.
2. Reads either `macro-db-dev` or `macro-db-prod` from AWS Secrets Manager.
3. Adds `sslmode=require` to the database URL when missing.
4. Masks the database URL in GitHub logs.
5. Runs SQLx migrations from `rust/cloud-storage/macro_db_client`.

Dev migrations run with:

```bash
cargo sqlx migrate run --ignore-missing
```

Prod migrations run with:

```bash
cargo sqlx migrate run
```

The main-branch cloud-storage deploy workflow runs the same migration action for `dev` before service deployment. The production release workflow runs the same action for `prod` before deploying cloud-storage services.

## Migration validation checklist

<Steps>
  <Step title="Start or select the target database">
    For local work, start Docker databases with `just run_dbs -d`. For remote checks, use the `check_db_dev` or `check_db_prod` recipes from `rust/cloud-storage/macro_db_client`.
  </Step>

  <Step title="Apply migrations locally">
    Run `cd rust/cloud-storage && just setup_macrodb` for a fresh local database, or `cd rust/cloud-storage/macro_db_client && just migrate_db` when the database already exists.
  </Step>

  <Step title="Check migration status">
    Run `just sqlx::check_db postgres://user:password@localhost:5432/macrodb` from `rust/cloud-storage`, or the crate-specific remote check recipes for dev/prod.
  </Step>

  <Step title="Refresh SQLx metadata">
    Run `cd rust/cloud-storage && just prepare_db` after schema or query changes. Commit the changed `.sqlx/query-*.json` files with the migration and Rust query changes.
  </Step>

  <Step title="Run compile and test checks">
    Use `just check`, `just clippy`, and the relevant `cargo test` or `cargo nextest` command. Database-backed tests require local MacroDB and the crate `.env` setup when they read `DATABASE_URL`.
  </Step>
</Steps>

## Troubleshooting

| Symptom | Likely cause | Fix |
| --- | --- | --- |
| SQLx macro compile error for a changed query | Offline cache is missing or stale. | Run `cd rust/cloud-storage && just prepare_db` against an up-to-date MacroDB. |
| Migration appears pending locally after pulling changes | Local database has not applied new files in `macro_db_client/migrations`. | Run `cd rust/cloud-storage/macro_db_client && just migrate_db`. |
| Test crate cannot find `DATABASE_URL` | Crate-local `.env` was not populated. | Run `just rust/cloud-storage/setup_test_envs` from the repository root. |
| Local E2E seed refuses to run | Safety guard did not detect `LOCAL_E2E_SEED=true` or the URL is not the local compose MacroDB. | Use `just local-e2e-seed` or match `postgres://user:password@localhost:5432/macrodb`. |
| Remote prod migration needs confirmation locally | `migrate_db_prod` is intentionally interactive. | Use it from a terminal and confirm only after reviewing pending migrations with `check_db_prod`. |
| GitHub dev migration ignores missing migrations but prod does not | The composite action passes `--ignore-missing` only for `dev`. | Keep prod migration history complete and avoid deleting applied prod migration files. |

## Related pages

<CardGroup>
  <Card title="Cloud-storage Rust workspace" href="/rust-cloud-storage">
    Build, check, clippy, test, and service-level Rust workflows.
  </Card>
  <Card title="Local development stack" href="/local-development">
    Docker compose databases, local services, and seeded E2E workflows.
  </Card>
</CardGroup>