Category system needs foreign keys to avoid label comparisons

[jjmata]

The reason we have problems like the one below is due to the current category feature missing this key aspect!

/cc @bittoby

Reuse existing category across legacy locale mismatches

This lookup now only searches Category.investment_contributions_name in family.locale, so families that already have an Investment Contributions category created under the old request-locale behavior (for example, existing English name while family.locale is French) will get a second category created on the next call. In that scenario, historical and new transfer transactions end up split across two category IDs again, so the duplication bug persists for migrated data unless this method first resolves legacy names (or migrates existing records) before creating.

Useful? React with 👍 / 👎.

Originally posted by @chatgpt-codex-connector[bot] in #956

[bittoby]

@jjmata Can I pick this up?

[jjmata]

Sure, but can you explain your implementation plan first? This has UI implications since just adding IDs is not enough. Accomplishing this work means we need to enforce some specific categories to exist by default and get mapped to be used as "special" categories. Not as simple as it seems.

[jjmata]

It could actually be broken in 2-3 PRs.

[bittoby]

Thanks for providing feedback in advance @jjmata, Here is my plan:

PR 1: Stable identity for system categories
Add a system_key column.
Migrate existing data: look for any “Investment Contributions” category (any language), merge duplicates, and set the right key on the one we keep.
Then the app will look up system categories by system_key, not by name.

PR 2: System categories should always exist
Currently we create them only when they’re first needed. If a user deletes one, we recreate it with a new ID and that breaks old transaction links.
Fix: seed system categories when a family is created.
Also block deletion in code.
Result: system categories always exist, keep the same ID, and can’t be deleted.

PR 3: UI changes to match
Right now every category shows Edit/Delete and there’s no difference.
Remove Delete for system categories, add a lock (or similar), and limit what can be edited for them.
Result: UI makes it obvious what’s system-managed vs user-created.

=============

Please take a look and let me know your feedback. If it looks good, I'll open prs.

[bittoby]

@jjmata could you review this? If it is okay, I will work

[jjmata]

Let's actually have @sokie and @luckyPipewrench look it over as well. I'll provide some thoughts shortly.

[bittoby]

Thank you