Menu

1 / 5

Recording who was in the room: session history, two partial unique indexes, and the RLS hole it uncovered

DEV Community: postgres·Michael Banigo·18 days ago
#S1cKCw06
#devtopostgresql#devto#dev#null#unique
Reading 0:00
15s threshold

A live classroom that keeps one row is not a classroom with history. It is a classroom with a current state. The difference shows up the week a teacher asks the question that actually matters: who was in my session on the 14th, and for how long?

We added that. Getting it right turned up two bugs worth writing down — one in the schema, because of how Postgres treats NULL in a unique index, and one in a row-level security policy that had been quietly handing anyone the keys to somebody else's institution.

What "history" needs to answer

Three questions, and each one forces a different shape of data:

How long did the session run? — a session, with a start and an end.

Who was there? — one row per attendee per session.

Was the same person there twice, or two different people? — a stable identity for an attendee who never signed in.

The third question is the one that breaks the obvious design.

Two tables, not one

create table if not exists public .…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.