[peterldowns]

I agree with you. What I've done in the past (and continue to do with new projects) is write a database-backed test that:

- applies migrations

- parses the resulting schema

- finds all foreign key references: ref{tableA columnA -> tableB columnB}

- finds all the indexes: index{tableA columnA [columnB...]}

- checks that there is an explicit index for every reference column: index{tableA columnA} must exist

So basically, by default when you add a new foreign key, the tests fail until you either explicitly add an exception OR create the necessary index. Easy.

This strategy is also really nice for linting other relational properties in your database. For instance, for GDPR/CCPA/correctness purposes, you probably want to prevent deletion of certain rows unless it's done by specialized code with an audit log. These kinds of lints can check to make sure that there are no ON DELETE CASCADE foreign keys from those tables that would result in a surprise deletion. You can also check to make sure that foreign keys are either ON DELETE CASCADE, ON DELETE SET NULL, or explicitly covered by custom deletion code.