whisper-money/database/migrations
Víctor Falcón f4c21147f1
feat(budgets): count shared accounts at the owner's percentage (#786)
## Why

#750 made a shared account count only your share of every transaction —
on the
dashboard and on the cashflow screen. Budgets were left out and shipped
as a
known gap: `budget_transactions.amount` is a snapshot written when a
transaction
is assigned, so a 50% joint account still spent **100%** of every
expense against
its budget. The same category could read €400 on the dashboard and €800
in
Budgets.

This closes that gap.

## What

A €100 expense on an account you own 50% of now counts €50 towards your
budgets.

Because every budget reader funnels through
`BudgetPeriod::spentAmount()` — a sum
of those snapshots — weighing the snapshot covers the budget cards, the
detail
page, the spending chart, carry-over, the limit alert emails and the MCP
tools in
one move.

- **Assignment writes the owner's share.** Both paths (the
per-transaction
listener and the historical backfill) go through one `recordSnapshot()`.
- **Changing an account's share rewrites its history.** A single SQL
`UPDATE`
  re-weighs every budget row of that account, in every period, past ones
  included.
- **A migration re-weighs the rows written before this**, so existing
shared
  accounts are correct on deploy instead of on the next edit.

## How

The share is computed at the same two choke points #750 established:

- **PHP** — `Transaction::ownerShareOf()` (extracted from
`ConvertsTransactionCurrency`, which was doing the same null dance
inline)
  feeds `BudgetTransactionService::recordSnapshot()`.
- **SQL** — `BudgetTransactionService::reweighAccountSnapshots()` reuses
`Transaction::OWNED_AMOUNT_SQL`, so the rounding matches what PHP would
have
  written. A test pins both to the same answer on an uneven share.

The re-weigh hangs off `Account::booted()` rather than
`AccountController`, so a
seeder, an artisan command or a future MCP write tool cannot silently
skip it.
It also clears the period's `close_to_limit_notified` /
`over_limit_notified`
flags, the same way a refund that drops a budget back under its limit
does —
otherwise a budget that fell out of "over limit" would stay claimed and
never
alert on the next real crossing.

The owning account is eager loaded **withTrashed** everywhere the
snapshot is
written, because `OWNED_AMOUNT_SQL` joins `accounts` without the
soft-delete
scope; without it a transaction whose account was deleted would snapshot
at 100%
in PHP and at the real share in SQL.

## Deliberate boundaries

- **Transaction rows still show the real bank amount** — #750's rule.
The budget
detail page is the one screen where a weighted total sits directly above
its
own itemised list, so it now says so in a line under the chart. The
alert email
  had the same mismatch inside one message and now quotes your share.
- **`carried_over_amount` is not re-derived.** It is a second snapshot
taken when
a period closes. `remainingAmount()` deliberately ignores it and the UI
only
types the field; it surfaces solely through MCP. Left alone rather than
adding
  a second re-derivation path.
- The migration's `down()` restores the full transaction amount, which
is what
  those rows held — it cannot know a share an account no longer has.

## Testing

`tests/Feature/SharedAccountOwnershipTest.php` gains the budget cases:
assignment,
the historical backfill, the re-weigh through the settings screen,
PHP/SQL
rounding parity on 33% of 3333, and the alert flags being cleared.
`tests/Feature/WeighBudgetTransactionsMigrationTest.php` covers the
backfill,
including that it is idempotent and that it keeps refunds negative (the
fix from
`2026_02_24_193117`). Fixing that test needed the `BudgetTransaction`
factory,
which had been pointing at a `BudgetPeriodAllocation` model that no
longer exists.

Manual QA on real local data — budget "Miami Flight", account "Daily"
set to 50%:

| | Before | At 50% | Back at 100% |
|---|---|---|---|
| Miami Flight spent | €3,229.40 | **€1,955.79** | €3,229.40 |
| Yearly Padel spent | €1,514.91 | €785.49 | €1,514.91 |

€1,955.79 is €3,229.40 − €1,273.61, exactly half of the €2,547.24 that
account
had in the budget. The round trip lands back on the original figure to
the cent,
so the re-weigh is idempotent on real data too.

## Demo

<!-- PLACEHOLDER: drag the QA video here -->


https://github.com/user-attachments/assets/1fa5b98a-2a11-4cad-b927-496b434d3295
2026-08-12 12:47:43 +02:00
..
0001_01_01_000000_create_users_table.php
0001_01_01_000001_create_cache_table.php
0001_01_01_000002_create_jobs_table.php
2025_08_26_100418_add_two_factor_columns_to_users_table.php
2025_11_07_135255_add_encryption_salt_to_users_table.php
2025_11_07_135256_create_encrypted_messages_table.php
2025_11_07_150038_create_banks_table.php
2025_11_07_150122_create_accounts_table.php
2025_11_07_150613_create_categories_table.php
2025_11_07_150659_create_transactions_table.php
2025_11_07_184056_modify_banks_table_add_user_id_and_simplify_name.php
2025_11_07_185018_change_banks_logo_to_text.php
2025_11_08_140230_change_transactions_id_to_uuid.php
2025_11_08_141344_make_category_id_nullable_in_transactions_table.php
2025_11_08_144530_add_soft_deletes_to_transactions_table.php
2025_11_10_110100_create_automation_rules_table.php
2025_11_15_172640_create_account_balances_table.php
2025_11_15_175636_change_transactions_amount_to_bigint.php
2025_11_15_195739_convert_all_ids_to_uuid.php
2025_11_18_184409_create_user_leads_table.php
2025_11_28_104227_add_source_to_transactions_table.php
2025_11_29_170353_add_type_to_categories_table.php
2025_11_29_170955_fix_account_balances_unique_constraint.php
2025_12_01_101443_add_unique_constraint_to_categories_table.php
2025_12_06_112515_create_customer_columns.php
2025_12_06_112516_create_subscriptions_table.php
2025_12_06_112517_create_subscription_items_table.php
2025_12_06_112518_add_meter_id_to_subscription_items_table.php
2025_12_06_112519_add_meter_event_name_to_subscription_items_table.php
2025_12_10_142006_add_onboarded_at_to_users_table.php
2025_12_12_092647_create_labels_table.php
2025_12_12_092650_create_label_transaction_table.php
2025_12_12_092651_create_automation_rule_labels_table.php
2025_12_12_141955_update_labels_unique_constraint_include_deleted_at.php
2025_12_16_151952_create_user_mail_logs_table.php
2025_12_19_092437_create_budgets_table.php
2025_12_19_092443_create_budget_periods_table.php
2025_12_19_092446_create_budget_transactions_table.php
2025_12_20_154221_create_features_table.php
2025_12_29_063338_add_currency_code_to_users_table.php
2026_01_08_192757_add_email_identifier_to_user_mail_logs.php
2026_01_08_204223_update_user_mail_logs_unique_constraint.php
2026_01_21_151353_add_indexes_for_budget_transaction_assignment.php
2026_01_22_071325_add_processing_historical_to_budget_periods.php
2026_01_22_143232_add_locale_to_users_table.php
2026_02_08_103953_create_user_settings_table.php
2026_02_09_110624_add_encrypted_to_accounts_table.php
2026_02_09_131403_make_description_iv_nullable_on_transactions.php
2026_02_09_144915_create_banking_connections_table.php
2026_02_09_144959_add_banking_connection_fields_to_accounts_table.php
2026_02_09_144959_add_external_transaction_id_to_transactions_table.php
2026_02_10_085759_add_aspsp_logo_to_banking_connections_table.php
2026_02_10_095639_add_raw_data_to_transactions_table.php
2026_02_11_113115_add_pending_accounts_data_to_banking_connections_table.php
2026_02_11_113119_add_linked_at_to_accounts_table.php
2026_02_13_083716_add_original_description_to_transactions_table.php
2026_02_18_084241_add_api_token_to_banking_connections_table.php
2026_02_18_110556_add_api_secret_to_banking_connections_table.php
2026_02_20_074846_create_exchange_rates_table.php
2026_02_22_180317_add_invested_amount_to_account_balances_table.php
2026_02_24_193117_fix_budget_transaction_refund_amounts.php
2026_03_03_114620_add_paywall_seen_at_to_users_table.php
2026_03_04_085843_add_waitlist_columns_to_user_leads_table.php
2026_03_04_094926_add_locale_to_user_leads_table.php
2026_03_12_095411_add_iban_to_accounts_table.php
2026_03_18_112307_add_cashflow_direction_to_categories_table.php
2026_03_19_151039_add_include_loans_in_net_worth_chart_to_user_settings_table.php
2026_03_20_110659_create_real_estate_details_table.php
2026_03_20_132058_make_bank_id_nullable_on_accounts_table.php
2026_03_20_154755_add_include_real_estate_in_net_worth_chart_to_user_settings_table.php
2026_03_25_125717_add_revaluation_percentage_to_real_estate_details_table.php
2026_03_25_132244_create_loan_details_table.php
2026_03_30_123011_add_consecutive_sync_failures_to_banking_connections_table.php
2026_03_30_123011_create_banking_sync_logs_table.php
2026_04_14_120000_add_email_verification_to_user_leads_table.php
2026_04_16_063515_add_bank_transactions_email_cutoff_at_to_banking_connections_table.php
2026_04_16_092644_add_timezone_to_users_table.php
2026_04_22_084113_add_deleted_at_to_users_table.php
2026_04_25_092824_add_launch_columns_to_user_leads_table.php
2026_05_05_070442_add_rate_limited_until_to_banking_connections_table.php
2026_05_05_132023_convert_custom_budgets_to_monthly.php
2026_05_13_085027_add_dedup_fingerprint_to_transactions_table.php
2026_05_25_115100_update_default_saving_and_investment_category_types.php
2026_05_26_061232_add_reinvitation_columns_to_user_leads_table.php
2026_05_27_061513_add_counterparty_names_to_transactions_table.php
2026_05_28_073203_update_categories_unique_constraint_for_soft_deletes.php
2026_05_29_085835_update_saving_and_investment_category_cashflow_direction.php
2026_06_01_085554_create_budget_category_table.php
2026_06_01_085554_create_budget_label_table.php
2026_06_01_085554_migrate_budget_category_label_to_pivots.php
2026_06_02_090000_add_parent_id_to_categories_table.php
2026_06_02_094823_add_notify_on_bank_transactions_synced_to_user_settings_table.php
2026_06_05_120000_create_saved_filters_table.php
2026_06_05_185212_add_state_token_to_banking_connections_table.php
2026_06_08_114044_add_analysis_days_to_saved_filters_table.php
2026_06_09_124010_add_analysis_mode_to_saved_filters_table.php
2026_06_10_083644_add_last_logged_in_at_to_users_table.php
2026_06_10_084055_add_last_active_at_to_users_table.php
2026_06_12_134225_create_ai_consents_table.php
2026_06_12_134523_create_suggestion_runs_table.php
2026_06_12_134524_create_rule_suggestions_table.php
2026_06_13_000000_add_is_catch_all_to_budgets_table.php
2026_06_15_120911_add_ai_categorization_fields_to_transactions_table.php
2026_06_15_120911_add_origin_to_automation_rules_table.php
2026_06_15_120911_create_category_corrections_table.php
2026_06_17_081319_add_ai_suggested_category_to_transactions_table.php
2026_06_17_114601_create_integration_requests_table.php
2026_06_17_114602_create_integration_request_votes_table.php
2026_06_17_120850_add_status_to_integration_requests_table.php
2026_06_17_122001_seed_initial_integration_requests.php
2026_06_17_141827_add_comment_to_integration_requests_table.php
2026_06_18_092830_drop_unique_user_vote_from_integration_request_votes_table.php
2026_06_19_134854_create_stuck_cohort_snapshots_table.php
2026_06_20_105609_align_accounts_encrypted_flag_with_plaintext_names.php
2026_06_20_165235_add_position_to_accounts_table.php
2026_06_26_124609_add_ai_model_to_transactions_table.php
2026_06_27_000000_backfill_xxx_account_currencies.php
2026_06_27_114559_add_refunded_at_to_subscriptions_table.php feat(subscriptions): end the trial experiment and make the trial length per plan (#762) 2026-08-12 10:59:55 +02:00
2026_06_27_154041_add_hidden_on_dashboard_to_accounts_table.php
2026_06_29_161916_add_transactions_last_visited_at_to_users_table.php
2026_07_01_064324_add_ai_consent_prompt_dismissed_at_to_users_table.php
2026_07_02_133321_add_user_source_created_at_index_to_transactions_table.php
2026_07_06_120000_create_spaces_table.php
2026_07_06_120001_create_space_user_table.php
2026_07_06_120002_create_space_invitations_table.php
2026_07_06_120003_add_current_space_id_to_users_table.php
2026_07_06_120004_add_space_id_to_owned_tables.php
2026_07_06_120005_backfill_spaces.php
2026_07_17_125642_create_personal_access_tokens_table.php
2026_07_17_154027_create_oauth_auth_codes_table.php
2026_07_17_154028_create_oauth_access_tokens_table.php
2026_07_17_154029_create_oauth_refresh_tokens_table.php
2026_07_17_154030_create_oauth_clients_table.php
2026_07_17_154031_create_oauth_device_codes_table.php
2026_07_18_104433_create_account_import_configs_table.php
2026_07_18_111320_add_upsell_source_to_subscriptions_table.php
2026_07_24_083854_add_notification_toggles_to_budgets_table.php
2026_07_24_083855_add_budget_notification_defaults_to_user_settings_table.php
2026_07_24_083855_add_limit_notified_flags_to_budget_periods_table.php
2026_07_24_100000_enable_budget_notifications_by_default.php
2026_07_25_102200_disable_budget_new_transaction_notifications.php fix(budgets): keep per-transaction email notifications opt-in (#735) 2026-07-25 12:36:13 +02:00
2026_08_09_150908_retry_enable_banking_connections_stuck_after_auth_failures.php fix(banking): stop unclassified bank responses from silently killing a connection (#742) 2026-08-09 17:26:34 +02:00
2026_08_10_090000_add_ownership_to_accounts_table.php feat(accounts): count shared accounts at the owner's percentage (#750) 2026-08-11 13:26:54 +00:00
2026_08_10_144811_create_mcp_tool_calls_table.php feat(mcp): record MCP tool usage and report it with stats:mcp-usage (#760) 2026-08-11 10:27:32 +02:00
2026_08_11_101501_purge_subscription_experiment_feature_assignments.php feat(subscriptions): end the trial experiment and make the trial length per plan (#762) 2026-08-12 10:59:55 +02:00
2026_08_12_070623_retry_banking_connections_stranded_before_transient_fix.php fix(banking): stop bank connections from silently dropping out of scheduled syncing (#782) 2026-08-12 11:32:36 +02:00
2026_08_12_100000_weigh_budget_transactions_by_account_ownership.php feat(budgets): count shared accounts at the owner's percentage (#786) 2026-08-12 12:47:43 +02:00