feature-crew-merge

Summary

Four crews titled “puo darsi” existed on the same event, created within 47 seconds, and the user could not leave any of them. Three had no membership row at all.

Why Four Crews Existed

CrewService.createCrew did three things in sequence:

final response = await _client.from('crews').insert({…}).select().single();
final crew = Crew.fromJson(response);            // ← threw
await _client.from('crew_members').insert({…});  // ← never reached

The misplaced @JsonKey fixed the day before threw exactly between the crew insert and the membership insert. The crew existed, trg_crews_create_chat gave it a chat with the creator inside, and the membership was never written. The user retried four times.

Warning

They had never joined those crews — which is also why they could not leave them. There was nothing to leave, and the chat participant came from a different trigger.

Three Invariants, Moved Into the Database

Trigger	Rule
trg_crews_add_creator	the creator is a member, written on the same insert as the crew
trg_crew_members_one_per_event	you cannot be in two crews for the same event
trg_crew_members_cleanup_empty	the last member out deletes the crew and its chat

Two statements the client must both complete is a window, and windows get hit. Membership is now written by a trigger, so there is no gap to fall through.

The one-per-event rule cannot be a unique index — it spans crew_members and crews. Being in two crews for one night is not a state the app can render: you cannot meet two groups at two meeting points.

crew_members_cleanup_empty guards with pg_trigger_depth(): deleting the crew cascades back into crew_members and would re-enter the trigger.

Cleanup

8 memberless crews and 3 chats removed in production. One chat held a single emoji.

Crew Merge

Two half-empty crews going to the same event are one crew that has not met yet.

The vote

propose_crew_merge(from, to) checks membership, same event, both still active, and that everyone fits. It records the proposer’s own yes — making them vote again for a thing they just proposed is a step that teaches nothing — and announces the proposal in both chats, so neither side finds out by accident.

vote_crew_merge(proposal, accept) records the vote and re-counts.

Note

Majority of the people in each crew, not of the votes cast. Silence is not consent when the result dissolves your group.

The tally runs under FOR UPDATE on the proposal: two people accepting at the same instant must not both conclude they were the last vote and merge twice.

The fit is re-checked at execution, not only at proposal time — people can have joined either side while the vote was open, and a merge that overflows would have to leave someone behind.

The bug worth recording

The first version read the movers from crew_merge_votes after deleting the old memberships. Removing the last member fires the empty-crew cleanup, which deletes the crew — and both crew_merge_votes.crew_id and the proposal itself cascade from crews. The insert then selected from a table the delete had just emptied, and one of three people never arrived.

The movers are now captured into an array before anything is deleted.

Warning

Caught by a rollback test asserting the final member count, not by reading the code. The code looked right.

RLS

Both tables are readable only by members of either crew, and have no INSERT policy at all: writes go exclusively through the two SECURITY DEFINER functions. A vote that could be written directly could be written for someone else, or for a crew the voter is not in.

Client

• CrewMergeCard — renders the proposal in chat with yes/no. Reads metadata.crew_merge; returns null on anything malformed so an unreadable message falls back to plain text instead of breaking the conversation.
• CrewMergeSuggestions — the entry point, inside your own crew’s detail. Candidates are filtered on the fit up front: proposing to a crew that cannot take you produces a vote that can only end in refusal, and that refusal would read as “they said no” rather than “it never fit”.
• Joining a second crew for the same event surfaces as a sentence, not as Postgres error 23505.

Verification

Rolled-back transactions throughout.

Check
creator is a member on creation	✅
second crew for the same event refused	✅
last member out deletes crew and chat	✅
crew with members left intact	✅
proposal announced in both chats	✅
half the votes is not enough	✅
majority merges	✅
absorbed crew and its chat disappear	✅
everyone lands in the surviving crew and chat	✅
degenerate self-proposal refused	✅

Open Items

• No expiry on proposals. A pending one blocks new proposals for that pair until it resolves; status = 'expired' exists in the CHECK but nothing sets it. sweep_crew_chats would be the natural place.
• Four crews titled [MOCK] … remain in production, all expired since March.

Related

feature-crew-chat-lifecycle — the chat triggers this builds on fix-crew-event-link — the parsing bug that caused the four crews localization — keys added