Designing the RSVP-and-guest data model (and the 3 things I got wrong)

The RSVP system is the heart of Pickleloonies. People show up because RSVPs work. They leave when RSVPs don't. This is the data model I shipped, the three mistakes I made along the way, and how the schema looks now.

The wrong first attempt: pod-scoped RSVPs

The first version had a single rsvp row per (user, pod). The thinking was: you're either "in" for the pod this week or you're not. Simple. Done.

It broke instantly. Real crews don't work that way. People are "in" for Tuesday but "out" for Thursday this week. People say "I'm in if Jake's in." People drop in mid-week. A pod-scoped RSVP couldn't express any of that, and the workarounds (storing per-date overrides as JSON, etc.) were worse than just refactoring.

The fix was obvious in hindsight: RSVPs live on the session, not on the pod. Every recurring session generates its own session row, and RSVPs attach to that row.

Why guests had to become real users

The second mistake was treating guests as text columns on the RSVP row — guest_name, guest_phone. It felt lighter. It saved a join.

Two weeks in, I needed to track which guest had come three weeks running. I needed to convert them to a full member without losing their attendance history. I needed to count them toward host's bring-rate stats.

None of that worked with a text column. The data was trapped.

Guests are now first-class users. They have a user row with a flag, is_guest: true, reduced RLS permissions (read-only on chat, no payments, no admin), and the same identity surface as full members. Promoting a guest to a member is a single flag flip — no migration, no data loss.

Session, RSVP, Guest — the three-table answer

The current schema is three tables:

• sessions — one row per scheduled play date. Belongs to a pod. Has a capacity, a status, a court count.
• rsvps — one row per (user, session). Status is in, out, maybe, or waitlisted. The unique constraint is (user_id, session_id).
• guest_invites — one row per guest brought to a session. Links the guest user to the host user and the session. A guest can be invited to multiple sessions but only one invite per (guest, session).

The unique constraints matter. Without them you'd get duplicate RSVPs from race conditions on double-tap, and that ruins waitlist ordering.

Waitlist promotion belongs in the database

The third mistake: trying to handle waitlist promotion in the application layer. When someone dropped, the client would query the waitlist, pick the oldest entry, and update it. Race conditions everywhere. Two people drop within a second and the same waitlisted user gets promoted twice.

The fix is a Postgres trigger on rsvps. When a row goes from in to anything else (or is deleted), the trigger queries the session's waitlisted RSVPs, picks the earliest by created_at, and updates it to in — atomically, in the same transaction. The client doesn't have to know waitlists exist.

This is one of those problems where the Supabase RLS and trigger docs save you. Don't fight the database.

What I'd still change

The current model handles 95% of real cases. The 5% that still annoys me: late cancellations don't currently penalize attendance stats, only confirmed no-shows do. That feels like a soft incentive problem. I'm going to add a cancelled_at timestamp and a "cancelled <60 min before start" flag, but I haven't done it yet because no crew has actually asked for it.

That's the data model. If you want to see how it plays out for real users, try the demo — or read the glossary for the in-app terms. And if you want to peek at how RSVPs handle paddle data, the paddle directory is its own little sub-system.

Frequently asked questions

+−Why are guests first-class users instead of a free-text name field?

So they can be re-invited, tracked in attendance stats, and converted to full members later without a data migration. A string field traps the data and can't be linked to anything.

+−How does waitlist promotion happen?

A Postgres trigger fires on every RSVP delete or status change. The earliest 'waitlisted' row for that session, ordered by created_at, is updated to 'in' atomically. The client never has to coordinate the promotion.

+−Can any pod member invite a guest?

Yes by default. The pod admin can disable guest invites per session if needed (for example, a packed playoff night with 4 courts and a fixed roster).

+−Do guests show up in attendance stats?

Yes. Guests count as attendees of the host who invited them, which keeps the host's stats honest and lets you see who's always bringing one more friend.