Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

8.6 KB
Raw Blame

Database development

Read this for SQLx queries, migrations, database-backed tests, and cache errors. Commands run from the repository root inside nix develop unless shown with an explicit Nix invocation. See also Rust development.

Locate the schema and owner

  • MacroDB migrations live in crates/macro_db_client/migrations/. Check the owning crate's migrations/recipes before assuming another database uses the same setup.
  • Inspect migrations or use the dump-schema skill against the local database. Do not guess table or column spelling.
  • Quote camelCase identifiers and alias selected columns to their Rust field names: SELECT "userId" AS user_id FROM "Chat".
  • Use the owning domain's database adapter; follow the [db] rules (CS-01–09) in rust/db.md and ownership rule (CS-27) in rust/architecture.md.

Set up local test databases

On a local machine, from the root inside Nix:

just run_dbs -d
just setup_test_envs
just setup_macrodb

run_dbs creates the required networks/volumes and starts Postgres and Redis. setup_macrodb creates and migrates MacroDB; initialize_dbs is its alias. The default local URL is defined in database.just. These recipes target that default database, not a named local-stack instance.

On Cursor Cloud, installation already prepares the database and test envs. Start the infrastructure with bash .cursor/infra.sh; see Cursor Cloud. Apply any new migrations before testing.

Pre-migrated test template

CI runs just setup_test_template after initialize_dbs. It migrates template1, which Postgres clones for every new database, so each #[sqlx::test] database starts with the MacroDB schema instead of replaying every migration. That replay used to be nearly all of the Rust test suite's runtime. Run the recipe locally only against a Postgres dedicated to one checkout; every database created on that server afterwards gets the schema too.

Set max_locks_per_transaction = 8192 on that dedicated server, as in CI’s PostgreSQL setup, and restart PostgreSQL before testing; replacing a pre-migrated schema can exceed the default lock capacity.

Tests that build their own schema (#[sqlx::test(migrations = false)], or a crate's test-only schema fixture) must first run DROP SCHEMA public CASCADE; CREATE SCHEMA public;, since their database may arrive pre-migrated.

Safe database schema changes

Entity tables and reusable storage

New app entities use a plural <kind>_entities table for identity, display name, ownership, lifecycle timestamps, and trash state. When an entity uses a reusable storage resource, its entity table references that resource with a foreign key; storage must be usable without creating the app entity or its access grants.

This convention starts with database_entities; it is new for databases and is the convention for new entities going forward. It does not require renaming existing entity tables. database_entities.database_id is both the entity's key and a reference to databases.id. See the database backend for the storage boundary and the explicitly destructive database cutover.

Compatible migrations

Database migrations deploy before service changes. Every migration must remain compatible with the service code currently deployed, not just the code in its PR.

  • Never drop a column in the same PR that removes its usage. First remove all reads/writes in a service-only PR and keep the column in the schema. After that change is fully deployed to all consumers, drop the column in a separate PR. Merging the first PR is not enough; confirm deployment before removal.
  • Apply the same staged approach to table removals, column renames, and other incompatible changes: add compatible schema, migrate service usage, then remove obsolete schema in a later PR after deployment.
  • New columns and constraints must also support currently deployed code (for example, allow omitted values via nullability or defaults until writers migrate).
  • Consolidate unpublished branch migrations into the minimum deployment-safe final set; never rewrite migrations already deployed to a shared environment.

Change queries or schema

  1. If the schema changes, generate a migration with SQLx, never by hand. For MacroDB, from the root:

    sqlx migrate add --source crates/macro_db_client/migrations <descriptive_name>
    

    Edit the generated file. For another migration directory, use its --source or run sqlx migrate add from its owning crate. Never invent/copy timestamp prefixes. If SQLx is unavailable, use the pinned Nix shell or report the blocker; do not fabricate a migration filename.

  2. Use compile-time checked queries (query!, query_as!, query_scalar!, query_file!) by default. Dynamic SQL is only for genuinely dynamic queries; bind values and allowlist dynamic identifiers. Review indexes, bounded results, and access-control predicates with the SQLx query validator.

  3. Apply schema changes to the local database:

    just crates/macro_db_client/migrate_db
    

    Migrations deploy before services and must survive a rolling deploy: give new NOT NULL columns a default, and for large tables set lock_timeout and use NOT VALID + VALIDATE or CREATE INDEX CONCURRENTLY (CS-70).

  4. Update affected tests/fixtures and run package tests from the root with SQLX_OFFLINE unset. Test between batches of query changes, not just at the end.

  5. Refresh the workspace SQLx cache when non-test queries or schema change, or when required metadata is missing. From the repository root:

    nix develop --command just prepare_db
    

    Commit generated .sqlx changes with the query/schema change. Never manually create or edit .sqlx/query-*.json, and never generate a crate-local cache. Unrelated Rust-only DB-crate edits still need appropriate tests, but do not require cache preparation when queries/schema are unchanged and metadata is present.

  6. Rerun the affected tests after preparation or fixes. Cache preparation is not a substitute for cargo test -p <package> against the live local database.

Test queries are not cached

Tests compile against a live database with SQLX_OFFLINE unset, and the offline checks (just rust-check, just clippy) don't compile tests, so test-only queries never need .sqlx metadata. Prepare with the root prepare_db wrapper only. Never pass --tests to sqlx::prepare_db or cargo sqlx prepare, and don't commit cache entries for test queries.

Checking query plans

Local Postgres runs with default planner costs. Hosted MacroDB uses the parameter group in infra/stacks/macrodb/index.ts (random_page_cost = 1.1, work_mem = 16MB, jit = off). Plans can differ between the two, so SET those values in your session before reading EXPLAIN (ANALYZE, BUFFERS) output. Seed data at realistic volume: an empty table hides missing indexes. Local and dev run Postgres 18, but production runs the engine_version in infra/stacks/macrodb/Pulumi.prod.yaml, which is older and lacks skip scans. Check that the plans you rely on also hold on that version.

Troubleshooting

  • Connection or schema errors: confirm local Postgres is running, test envs point to the intended local database, and migrations have been applied.
  • Missing cached query data: run just prepare_db as above. If the missing query is test-only, the test is running offline: leave SQLX_OFFLINE unset for cargo test; enabling it can hide schema drift and produce misleading type-inference errors. Offline mode is only for builds/checks/lints.
  • Preparation still fails: fix in-scope query/schema issues; if the blocker is environmental, report it. Do not hand-edit the cache or wipe databases as an automatic workaround.

Destructive local reset — explicit approval required

Only after confirming the target is the disposable local database and the user approves losing its data, run from the repository root inside Nix:

just crates/macro_db_client/drop_db -y -f
just setup_macrodb

Do not use this on hosted dev/production databases or delete unrelated containers. For a named local stack, follow its instance-specific commands so you do not reset the default database by mistake.