Overview
Several operations introduced in PR #414 (feat: implement habit stocks and occurrence expenses) perform multi-step database mutations from the client side without any transactional guarantee. If any intermediate step fails, the database is left in a partially-written or corrupted state. This issue tracks consolidating all such flows into atomic server-side RPCs or Postgres functions.
Related PR: #414
Requested by: @domhhv
Affected Flows
1. Occurrence creation + stock usage persistence
Files: src/components/occurrence/OccurrenceCreateFormContainer.tsx, src/services/occurrence-stock-usages.service.ts
addOccurrence commits the occurrence row first. createOccurrenceStockUsages (and the subsequent updateOccurrence cost/currency patch) run as separate requests. If any of these fail, the occurrence is already persisted but the usage rows and cost fields are missing.
Resolution: Create a single server-side RPC (e.g. create_occurrence_with_stock_usages) that performs the occurrence insert, stock usage inserts, stock remaining_items/is_depleted adjustments, and cost/currency update atomically within one transaction.
2. Occurrence update + stock usage reconciliation
Files: src/components/occurrence/OccurrenceUpdateFormContainer.tsx
The update flow performs delete/update/create of stock usage rows followed by updateOccurrence in separate requests. A failure mid-way leaves mismatched stock usages and incorrect inventory counts.
Resolution: Create a server-side RPC (e.g. update_occurrence_with_stock_usages) that reconciles usage rows and updates the occurrence atomically.
3. Stock creation + metric defaults creation
Files: src/stores/stocks.store.ts (around lines 52–82)
addStock calls createStock and then createStockMetricDefaults as two separate requests. If createStockMetricDefaults fails, a stock record exists in the DB with no defaults — leaving a dangling row.
Resolution: Create a server-side RPC (e.g. create_stock_with_defaults) that inserts both the stock and its metric defaults within one transaction.
4. Stock metric defaults update (delete-then-recreate)
Files: src/stores/stocks.store.ts (around lines 115–188), src/components/stock/StockListItem.tsx (around lines 129–137)
updateStockMetricDefaults calls destroyStockMetricDefaults (deletes all existing defaults) and then createStockMetricDefaults (recreates them). If the recreation step fails, all defaults for that stock are permanently lost.
Resolution: Replace the delete-then-recreate pattern with a single atomic RPC or an upsert strategy (e.g. upsert_stock_metric_defaults) that either uses ON CONFLICT DO UPDATE or wraps the delete+insert in a single Postgres function/transaction.
5. Race condition in stock decrement trigger
Files: supabase/schemas/02_functions.sql, supabase/migrations/20260316165838_add_habit_stocks_and_expenses.sql
The update_stock_on_usage_insert and update_stock_on_usage_delete trigger functions perform unprotected read-modify-write operations on habit_stocks.remaining_items. Concurrent inserts on the same habit_stock_id can read a stale remaining_items value, potentially producing an invalid negative count or violating the remaining_items >= 0 constraint.
Resolution: Add SELECT ... FOR UPDATE to lock the target habit_stocks row before computing and writing the new remaining_items value inside each trigger function.
Acceptance Criteria
Overview
Several operations introduced in PR #414 (feat: implement habit stocks and occurrence expenses) perform multi-step database mutations from the client side without any transactional guarantee. If any intermediate step fails, the database is left in a partially-written or corrupted state. This issue tracks consolidating all such flows into atomic server-side RPCs or Postgres functions.
Related PR: #414
Requested by: @domhhv
Affected Flows
1. Occurrence creation + stock usage persistence
Files:
src/components/occurrence/OccurrenceCreateFormContainer.tsx,src/services/occurrence-stock-usages.service.tsaddOccurrencecommits the occurrence row first.createOccurrenceStockUsages(and the subsequentupdateOccurrencecost/currency patch) run as separate requests. If any of these fail, the occurrence is already persisted but the usage rows and cost fields are missing.Resolution: Create a single server-side RPC (e.g.
create_occurrence_with_stock_usages) that performs the occurrence insert, stock usage inserts, stockremaining_items/is_depletedadjustments, and cost/currency update atomically within one transaction.2. Occurrence update + stock usage reconciliation
Files:
src/components/occurrence/OccurrenceUpdateFormContainer.tsxThe update flow performs delete/update/create of stock usage rows followed by
updateOccurrencein separate requests. A failure mid-way leaves mismatched stock usages and incorrect inventory counts.Resolution: Create a server-side RPC (e.g.
update_occurrence_with_stock_usages) that reconciles usage rows and updates the occurrence atomically.3. Stock creation + metric defaults creation
Files:
src/stores/stocks.store.ts(around lines 52–82)addStockcallscreateStockand thencreateStockMetricDefaultsas two separate requests. IfcreateStockMetricDefaultsfails, a stock record exists in the DB with no defaults — leaving a dangling row.Resolution: Create a server-side RPC (e.g.
create_stock_with_defaults) that inserts both the stock and its metric defaults within one transaction.4. Stock metric defaults update (delete-then-recreate)
Files:
src/stores/stocks.store.ts(around lines 115–188),src/components/stock/StockListItem.tsx(around lines 129–137)updateStockMetricDefaultscallsdestroyStockMetricDefaults(deletes all existing defaults) and thencreateStockMetricDefaults(recreates them). If the recreation step fails, all defaults for that stock are permanently lost.Resolution: Replace the delete-then-recreate pattern with a single atomic RPC or an upsert strategy (e.g.
upsert_stock_metric_defaults) that either usesON CONFLICT DO UPDATEor wraps the delete+insert in a single Postgres function/transaction.5. Race condition in stock decrement trigger
Files:
supabase/schemas/02_functions.sql,supabase/migrations/20260316165838_add_habit_stocks_and_expenses.sqlThe
update_stock_on_usage_insertandupdate_stock_on_usage_deletetrigger functions perform unprotected read-modify-write operations onhabit_stocks.remaining_items. Concurrent inserts on the samehabit_stock_idcan read a staleremaining_itemsvalue, potentially producing an invalid negative count or violating theremaining_items >= 0constraint.Resolution: Add
SELECT ... FOR UPDATEto lock the targethabit_stocksrow before computing and writing the newremaining_itemsvalue inside each trigger function.Acceptance Criteria
create_occurrence_with_stock_usagesRPC (or equivalent) created and wired intoOccurrenceCreateFormContainerupdate_occurrence_with_stock_usagesRPC (or equivalent) created and wired intoOccurrenceUpdateFormContainercreate_stock_with_defaultsRPC (or equivalent) created and wired into the stock storeaddStockactionupsert_stock_metric_defaultsRPC (or equivalent) created and wired intoupdateStockMetricDefaults/StockListItemupdate_stock_on_usage_insertandupdate_stock_on_usage_deleteupdated to use row-level locking (SELECT ... FOR UPDATE)