ERD_INDEXERS_CHECKLIST.md still had migration and stored-procedure verification unchecked even though most runtime behavior was already implemented.
The repository already had broad module-level stored-procedure tests in revaer-data, but it lacked a catalog-level integration suite proving the migrated database actually contains the full ERD table, enum, seed, and wrapper-procedure surface.
AGENTS.md requires a task record with motivation, design notes, test coverage summary, observability updates, risk and rollback guidance, and dependency rationale.
Decision:
Add crates/revaer-data/tests/indexers_schema.rs as a live Postgres integration suite that runs migrations and verifies:
all ERD indexer tables exist,
all ERD enums match the specified value sets,
all required stable and _v1 stored procedures are registered,
core schema invariants hold (public_id boundaries, deleted_at soft-delete columns, JSON/JSONB prohibition, key lower-case checks, and representative varchar caps),
seeded catalog rows exist for trust tiers, media domains, Torznab categories, default rate-limit policies, job schedules, and the system sentinel user.
Treat the new catalog inventory tests plus the existing module-level stored-procedure tests as the acceptance basis for closing the migration/procedure verification checklist items.
Alternatives considered:
Add many more per-procedure behavioral duplicates in integration tests: rejected because that would repeat existing module coverage and add runtime without improving catalog verification.
Rely on migration file review only: rejected because it does not prove the live migrated schema matches the ERD.
Consequences:
Positive outcomes:
The database surface now has an executable ERD conformance check at migration time, not just code review.
Missing tables, enum drift, missing wrappers, or seed regressions will fail just test quickly.
The checklist can advance without inventing duplicate stored-procedure tests where behavior is already covered.
Risks or trade-offs:
The schema suite is intentionally catalog-oriented, so future behavioral changes still require focused module tests.
The test maintains a long explicit inventory of ERD objects, which must be updated whenever the ERD evolves.
Follow-up:
Implementation tasks:
Extend the catalog suite if new ERD tables, enums, or procedures are added.
Add additional DML-based constraint tests if future schema changes introduce higher-risk invariants not well represented by catalog inspection.
Review checkpoints:
Keep ERD_INDEXERS_CHECKLIST.md aligned with the live test inventory.
Revisit unchecked UI, migration-parity, and origin-only logging items in the next implementation passes.
Motivation:
Close the remaining ERD verification gap with the smallest high-signal change that exercises the real database surface.
Design notes:
The suite uses the existing Postgres test harness shape and keeps assertions at the schema catalog layer rather than duplicating service logic.
Test coverage summary:
Added crates/revaer-data/tests/indexers_schema.rs with six integration tests covering tables, enums, procedures, seeds, and representative constraints.
Observability updates:
No telemetry changes; this pass is verification-only.
Risk & rollback plan:
Roll back by reverting crates/revaer-data/tests/indexers_schema.rs, the checklist update, and this ADR if the test strategy needs to change.
Dependency rationale:
No new dependencies were added.
Alternatives considered: parsing migration SQL directly or adding a dedicated schema-test crate. Both were rejected in favor of sqlx catalog queries inside the existing revaer-data test setup.