Summary
Several flows introduced in #414 perform multiple sequential Supabase mutations without a wrapping transaction. A failure at any intermediate step leaves the database in an inconsistent state, and a retry can produce duplicate records.
The following operations should each be consolidated into a single Supabase RPC (PostgreSQL function with BEGIN … COMMIT) or a compensating rollback strategy.
1. Occurrence create flow — OccurrenceCreateFormContainer.tsx
Current sequential chain:
addOccurrence — inserts the occurrence (committed immediately)addNote — inserts an optional notesaveMetricValues — inserts metric value rowscreateOccurrenceStockUsages — inserts stock usage rowsupdateOccurrence — patches cost/currency back onto the already-committed occurrenceupdateStock (×N) — marks stocks as depleted
Risk: If step 4–6 fail, the occurrence exists without stock usages, with a null cost/currency, and stocks are not marked depleted. A retry duplicates the occurrence.
Proposed fix: A single create_occurrence RPC that atomically handles all inserts and the cost/currency derivation inside one transaction.
2. Occurrence update flow — OccurrenceUpdateFormContainer.tsx
Current sequential chain:
deleteOccurrenceStockUsages — removes removed usages (triggers restore remaining_items)updateOccurrenceStockUsage (×N, Promise.all) — updates changed quantitiescreateOccurrenceStockUsages — inserts newly added usagesupdateStock (×N) — marks stocks as depletedupdateOccurrence — patches the occurrence itself (cost, currency, etc.)addNote / updateNote / deleteNote — note managementsaveMetricValues — metric valuesrefreshHabitStocks — store refresh
Risk: Partial failure between any of steps 1–5 leaves stock remaining-item counts and occurrence cost/currency in an inconsistent state.
Proposed fix: A single update_occurrence RPC that wraps stock usage mutations and the occurrence patch in one transaction, with the store refresh happening client-side only after success.
3. Stock creation — stocks.store.ts → addStock
Current sequential chain:
createStock — inserts the habit_stocks rowcreateStockMetricDefaults — inserts metric default rows for the new stock
Risk: If step 2 fails, the stock exists with no metric defaults, silently breaking metric compound behaviour for that stock.
Proposed fix: A create_stock_with_defaults RPC that inserts both the stock and its metric defaults atomically.
4. Stock metric-defaults update — stocks.store.ts → updateStockMetricDefaults
Current sequential chain:
destroyStockMetricDefaults — deletes all existing defaults for the stockcreateStockMetricDefaults — inserts the new set of defaults
Risk: If step 2 fails after step 1 completes, the stock is left with no metric defaults at all.
Proposed fix: A replace_stock_metric_defaults RPC (or use an upsert + targeted delete inside a transaction) so the delete and re-insert are atomic.
References
Summary
Several flows introduced in #414 perform multiple sequential Supabase mutations without a wrapping transaction. A failure at any intermediate step leaves the database in an inconsistent state, and a retry can produce duplicate records.
The following operations should each be consolidated into a single Supabase RPC (PostgreSQL function with
BEGIN … COMMIT) or a compensating rollback strategy.1. Occurrence create flow —
OccurrenceCreateFormContainer.tsxCurrent sequential chain:
addOccurrence— inserts the occurrence (committed immediately)addNote— inserts an optional notesaveMetricValues— inserts metric value rowscreateOccurrenceStockUsages— inserts stock usage rowsupdateOccurrence— patches cost/currency back onto the already-committed occurrenceupdateStock(×N) — marks stocks as depletedRisk: If step 4–6 fail, the occurrence exists without stock usages, with a
nullcost/currency, and stocks are not marked depleted. A retry duplicates the occurrence.Proposed fix: A single
create_occurrenceRPC that atomically handles all inserts and the cost/currency derivation inside one transaction.2. Occurrence update flow —
OccurrenceUpdateFormContainer.tsxCurrent sequential chain:
deleteOccurrenceStockUsages— removes removed usages (triggers restoreremaining_items)updateOccurrenceStockUsage(×N, Promise.all) — updates changed quantitiescreateOccurrenceStockUsages— inserts newly added usagesupdateStock(×N) — marks stocks as depletedupdateOccurrence— patches the occurrence itself (cost, currency, etc.)addNote / updateNote / deleteNote— note managementsaveMetricValues— metric valuesrefreshHabitStocks— store refreshRisk: Partial failure between any of steps 1–5 leaves stock remaining-item counts and occurrence cost/currency in an inconsistent state.
Proposed fix: A single
update_occurrenceRPC that wraps stock usage mutations and the occurrence patch in one transaction, with the store refresh happening client-side only after success.3. Stock creation —
stocks.store.ts→addStockCurrent sequential chain:
createStock— inserts thehabit_stocksrowcreateStockMetricDefaults— inserts metric default rows for the new stockRisk: If step 2 fails, the stock exists with no metric defaults, silently breaking metric compound behaviour for that stock.
Proposed fix: A
create_stock_with_defaultsRPC that inserts both the stock and its metric defaults atomically.4. Stock metric-defaults update —
stocks.store.ts→updateStockMetricDefaultsCurrent sequential chain:
destroyStockMetricDefaults— deletes all existing defaults for the stockcreateStockMetricDefaults— inserts the new set of defaultsRisk: If step 2 fails after step 1 completes, the stock is left with no metric defaults at all.
Proposed fix: A
replace_stock_metric_defaultsRPC (or use anupsert+ targeted delete inside a transaction) so the delete and re-insert are atomic.References