feature-crew-chat-lifecycle

Summary

A crew exists to agree on one night out. Its chat lasts exactly as long: it is created with the crew, it follows who joins and leaves, it closes when the crew expires, and it disappears the next day — offering, before it goes, the people who were in it.

The Chat Is Born With the Crew

It used to exist only from the moment somebody tapped “open chat”, and joining a crew never put you in it.

Now two triggers, not client code:

Trigger	When	What it does
trg_crews_create_chat	BEFORE INSERT ON crews	creates the chat, sets chat_id, puts the creator in
trg_crew_members_sync_chat	AFTER INSERT OR DELETE ON crew_members	joining the crew joins the chat, leaving leaves

Why in the database and not the client

Crew membership already changes through at least three paths (creation wizard, join button, accept-invite in chat) and more will come. Each would have to remember to touch chat_participants too, and the one that forgets produces a member who cannot see the conversation — exactly the bug just fixed, in a new place. Putting it next to the data means no caller can forget.

metadata.crew_id

The chat carries the crew’s id. Until now, everything that needed to find a crew’s chat matched on the name, which is how the first backfill picked the wrong rows.

Note

A name is a label; this is a key.

The Chat Closes When the Crew Expires

chats.closed_at. Closed ≠ deleted: still readable, no longer writable.

The block is a RESTRICTIVE policy, and the distinction is load-bearing: Postgres ORs permissive policies together, so an extra permissive policy would have widened access rather than narrowed it — the existing Users can insert messages in their chats would still have let the write through. Restrictive policies are ANDed, which is what “and also not closed” actually means.

The sweep

sweep_crew_chats(), every 15 minutes via pg_cron:

1. expired crews become is_active = false;
2. their chats get closed_at = now() (found via metadata.crew_id, never by name);
3. chats closed more than 24 hours ago are deleted. Participants and messages cascade, crews.chat_id is ON DELETE SET NULL: nothing left dangling.

Not every minute (nothing here is urgent enough to pay for 1440 wake-ups a day) and not hourly (an hour of writing into a night that ended is an hour of messages nobody will read back).

The window

6 hours after the event ends, 12 for crews with no event — the values the wizard already computed, not new ones. Then 24 hours from closed to deleted.

Resurrection guard

get_or_create_crew_chat now refuses expired crews. Without it, opening a finished crew after the purge would find chat_id NULL and helpfully build a brand new room for a night that is over.

In Place of the Composer, the People

CrewWrapUpCard — lib/features/messaging/widgets/crew_wrap_up_card.dart.

The composer is not disabled, it is removed: the database refuses the write anyway, and a text field that silently discards what you type is worse than no text field.

What takes its place is everyone who was there, with one tap to keep them: Add / Requested / Friends.

It sits at the bottom of the conversation rather than on its own screen because that is where you already are when you think about those people. A summary screen would have to interrupt app launch to be seen, and an interruption is a worse offer than a card sitting exactly where the conversation stopped.

No optimism about state: a request shows as sent only if the server confirmed it. Claiming it was sent when it was not means that person is never added and nobody finds out.

UserApiService.getConnectionStatuses(List<String>) — a batched version beside the single one, so the logic stays in one place and the card makes one round trip instead of one per person.

Visibility: Everyone or Friends Only

crews.visibility — 'public' | 'friends', text with a CHECK rather than an enum: adding a third value later is one ALTER on the constraint, where a Postgres enum would need a type change and a redeploy of everything reading it.

Two values, not three. “Invite only” was considered and dropped: the chat invite already works on any crew regardless of visibility, so it would be a third choice to make every time that buys no power you do not already have.

The policy

The old one was using (true) — every crew visible to everyone. Replaced, not added to, for the same reason as above: permissive policies are ORed and a second would have narrowed nothing.

visibility = 'public'
  or creator_id = auth.uid()
  or  <caller is a member of this crew>
  or  are_friends(auth.uid(), creator_id)

Membership beats visibility. Someone invited into a friends-only crew who is not a friend of the creator must still see the crew they are standing in, or the app would show them a chat for something that appears not to exist.

are_friends checks both directions: connections stores one row per pair with a direction, and asking one way only would make the rule depend on who happened to send the request.

In the wizard

The selector sits on the size step because it is the only step both flows visit: a crew linked to an event skips the vibe step entirely.

Two rows you read, not a switch you guess at. A toggle labelled “private” makes the user infer what it excludes; spelling out the consequence of each choice costs a line and removes the guess.

The “Friends only” badge appears on the crew detail — a setting you cannot see afterwards is a setting you cannot trust — although what enforces it is the policy, not the client.

Verification

All in rolled-back transactions; the policy tests under role authenticated (as superuser RLS does not apply and the test would prove nothing).

Check
chat created with the crew, carrying crew_id in metadata	✅
creator inside from the first instant	✅
joining the crew joins the chat	✅
leaving the crew leaves the chat	✅
the sweep closes an expired crew’s chat	✅
messages stay readable after closing	✅
writing blocked in a closed chat	✅
deleted 24h after closing, no dangling references	✅
friend sees the friends-only crew, stranger does not	✅
stranger who is a member sees it anyway	✅
public crew stays visible to all	✅

Open Items

• The visibility badge is only on the detail, not on list cards.
• No notification before the chat closes. Anyone who does not open the app in those 24 hours loses the friend suggestion along with the chat.

Related

fix-crew-event-link — the bugs that led here refactor-unify-crew-model — the duplicate model this work forced out feature-crew-chat — earlier version of crew chat localization — keys added