Database performance might be significantly impacted by current approach to indexing of foreign-keyed columns: currently the default is not to index the FK column on the declaring table.

Example: form_defs

Indexes:
    "form_defs_pkey" PRIMARY KEY, btree (id)
    "form_defs_formid_publishedat_index" btree ("formId", "publishedAt")
Foreign-key constraints:
    "form_defs_formid_foreign" FOREIGN KEY ("formId") REFERENCES forms(id) ON DELETE CASCADE
    "form_defs_keyid_foreign" FOREIGN KEY ("keyId") REFERENCES keys(id)
    "form_defs_schemaid_foreign" FOREIGN KEY ("schemaId") REFERENCES form_schemas(id)
    "form_defs_xlsblobid_foreign" FOREIGN KEY ("xlsBlobId") REFERENCES blobs(id)

Adding an index to "form_defs"."formId" may significantly improve performance on joins with the forms table.

Detecting missing indexes

Examples can be found at:

• https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys (identifies 38 missing indexes)
• https://arktekk.no/blogs/2025_foreign_keys_without_index (identifies 33 missing indexes)

Aims

1. determine if adding the identified indexes could improve query performance
2. decide if including such indexes would be a good default
3. add migration to add missing indexes (wip at db: default to declare-side indexes for foreign keys central-backend#1550)
4. add script to detect missing indexes in CI, and reject code which does not include them (examples linked above might be suitable, depending on licenses)

cc @brontolosone