R2D2-MERIDIAN/migrations/AGENTS.md

migrations/ — Postgres Schema

Purpose

The relay's SQL schema, as an ordered sequence of forward-only migrations. These files are compiled into the relay binary and applied automatically at startup.

Ownership

NNNN_snake_case.sql, zero-padded four-digit sequence, currently 0001 → 0032. 0001_initial_schema.sql is the consolidated multi-tenant baseline; every later file is an incremental change. This upper bound is hand-maintained and nothing checks it — it read 0026 for five migrations, including the two brand-transition files carrying the fence GUC compatibility, in the document that owns the fence-critical partition-trigger contract (meridian-5riu).

The migrator lives in crates/meridian-db/src/migration.rs:

• static MIGRATOR = sqlx::migrate!("../../migrations") — files are embedded at compile time, so adding a file requires a rebuild of meridian-db.
• run_migrations() runs a pre-flight legacy guard, applies pending migrations, then calls replica_fence::verify_floor_guard_catalog().

Operator-only SQL that is deliberately not startup state lives in scripts/ (attach-schema-partitions.sql, backfill-d-tag.sql, scripts/cutover/).

Local Contracts

• Never edit an applied migration. sqlx checksums each file; changing one breaks startup on every existing deployment. Add a new numbered file instead.
• Forward-only, no down migrations. A revert is a new migration.
• Never renumber or reorder. Take the next free number; if two branches claim the same number, the later one renumbers before merge, never after release.
• Cutover and backfill are operator scripts, not migrations. The multi-tenant rewrite owns a clean 0001; legacy single-tenant data movement stays out of startup state.
• Migration 0021 installs the commit-time created_at floor trigger on the events parent and every partition. The replica-fence proof depends on it and verify_floor_guard_catalog fails closed if any partition lacks it. CREATE TABLE .. PARTITION OF clones parent triggers; ATTACH PARTITION does not. Any migration that creates or attaches a partition must ensure the trigger.
• Migration 0007 is checksum-frozen and predates exact NIP-RS tag-cardinality enforcement. The pre-flight guard refuses to run on a populated database still on 0001–0006 with ambiguous duplicate-tag rows, so an operator can repair them first. Do not remove that guard.
• Respect the store invariants enforced upstream in crates/meridian-db: events is partitioned by month on created_at, and no foreign key may reference a partitioned table.
• New partitions come from crates/meridian-db/src/partition.rs (ensure_future_partitions), not from hand-written DDL elsewhere.
• *_p_future is a temporary right-edge catch-all, not a permanent month. Baseline 0001 still creates events_p_future / delivery_log_p_future FROM ('2026-07-01') TO (MAXVALUE) so writes never fall into a hole before the manager runs. ensure_future_partitions must split that catch-all (DETACH → CREATE TABLE … PARTITION OF for each bounded month → move rows → ATTACH the remainder) rather than treating overlap as success. A sibling month covering the range stays info; catch-all cover must warn and carve. Do not replace this with hand-written monthly DDL in a migration — the manager is the sole carver, and ATTACH alone skips parent-trigger cloning.

Work Guidance

• Write the migration together with the meridian-db access module that uses it — a column with no typed accessor is dead schema.
• Prefer additive changes (new nullable column, new index, new table) so a rolling deploy where old and new relay versions coexist stays correct.
• Index creation on large tables should be CONCURRENTLY where the migration framework allows it; otherwise state the expected lock cost in a comment at the top of the file.
• Every migration starts with a comment: what it changes and why.

Verification

just test-unit    # meridian-db migrator/lint tests parse these files with no infra
just migrate      # apply against the local database
just test         # integration — proves the schema against real queries

A migration is not verified until just test passes against a database that applied it from scratch and one that upgraded into it.

Child STELLAR Index

None — migrations/ is governed by this file. The migrator itself is documented in crates/meridian-db/AGENTS.md.