Non-atomic household mutation causes data inconsistency

[Polqt]

Summary

The current household mutation flow is not atomic, leading to potential data inconsistency when updating household and member records.

Problem

In @packages/api/src/routers/households.ts (lines 108–172), the mutation performs multiple separate Supabase calls:

Load existing household

Insert/update household

Delete all household members

Insert new members

If any step fails (e.g., insert after delete), the system can end up in an inconsistent state:

Household saved but members wiped

Concurrent requests may interleave and reintroduce stale data

Expected Behavior

All operations (household + members) should execute atomically within a single transaction.

Proposed Solution

Replace the multi-step logic with a PostgreSQL RPC (transaction):

Create a Postgres function:

save_household_with_members(
household_payload JSONB,
household_id UUID,
members JSONB
)

This function should:

Insert/update the household

Replace household members

Run entirely inside a single transaction

Update the API layer to:

Call ctx.supabase.rpc(...)

Pass:

basePayload

existingHousehold?.id

memberPayload

Use getSupabaseDataOrThrow on the RPC result

Remove all separate .from("households") and .from("household_members") calls