Ensure indexes present

[gvt]

Since there was some concern mentioned this weekend (at the RfG Event 2025) lets do a pass to ensure that indexes are present in the database and creatd them if not.

• any column that ends in _id
• any FK column
• any other column that is joined on
• columns that are commonly used in WHERE clauses

If no action is needed that would be fine, and it closes this issue.

[github-actions]

This issue has been inactive for 246 hours (10.25 days) and will be unassigned after 114 more hours (4.75 days). If you have questions, please

If you are still working on this, comment here to tell the bot to give you more time

[github-actions]

This issue has been inactive for 366 hours (15.25 days) and is past the limit of 360 hours (15.00 days) so is being unassigned.You’ve just been unassigned from this ticket due to inactivity – but feel free to pick it back up (or a new one!) in the future! Thank you again for your contribution to this project.

[Raushan998]

@gvt Can you assign me this issue?

[acrosman]

I did a quick visual review of the db/schema.rb file since the original description includes this comment:

If no action is needed that would be fine, and it closes this issue.

There are definitely at least a few places that don't have indexes on _id columns (missing Id example – one of several).
There are also places where an id column is in an index, but not one for just that column (example).
There are another set that have id columns indexed, but not following consistent patterns: casa_case_emancipation_categories vs casa_cases_emancipation_options

So there is definitely work to do here.

I'm happy to help work on this, or at least chip away and help create some sub-tasks. That said, I'd like a slightly more refined spec to push for consistency on some of the details before launching into updates. I'm happy to discuss on Slack to help think through those details.

Basically I think the guidance I'd like to see would address the basic questions:

• When to add an index for an Id on it's own?
• When should something be part of this issue vs a new/sub-issue to resolve in a separate PR?
• Do we ever add a new index to enforce uniqueness for Id pairs in this issue? There are places it could improve performance and data integrity but also could create breaking changes for production instances. And without knowing the design intention of a feature it could make sense at the data layer, but not in practice.

[gvt]

I began working on this at the RubyForGood event (RfG) this past September 2025. I started a new job the following week and have been preoccupied with all that, sorry!

Here is the work in progress on this branch that I just pushed now. If someone wants to take this one over that would be fine with me. The branch: ensure_indexes_present_6505. Note that I'm using algorithm: :concurrently which creates an index without locking the table from writes, with the intention of having less impact on Production at deploy time. Feel free to double check my work.

For some additional context:

For this rails app, casa, one of the main things the project sponsors asked us to work on and improve during the RfG event was to improve performance. I created this issue because I saw some places where queries were doing joins across foreign key columns that didn't have indexes. That would def hinder performance. Yes having more numerous indices will make inserts and updates slower, but writing to the DB has not been a bottleneck. But reading from the DB doing many joins for pages that show a lot of data from many different tables using joins has been a bottleneck. So lets make indices!

[gvt]

I assigned this issue to you, @Raushan998, as requested. Note my previous comment and my WIP branch ensure_indexes_present_6505.