The shape of Cue’s data
Sixty-three tables, one spine, zero ORM guesswork. Scroll down and watch the schema assemble itself — domain by domain.
It all starts with one table
Every other table is, directly or a hop or two away, hanging off a user. Here are the ten domains that branch out from it — tap any one to jump straight to it.
Everything hangs off a person
Every row in Cue traces back to a person. A user appears the first time someone signs in — Google, Apple, or a six-digit email code — and is never asked to invent a username. Provider logins live beside the row, not inside it, so one human can link several and stay a single account.
linked_accountsOAuth identities, one humanauth_sessionsrotating refresh tokensdevice_tokensAPNs / FCM push targetsprofile_picturesuploaded avatars (BYTEA)Two names, on purpose. provider_name follows your Google or Apple display name; name is an optional custom handle, unique case-insensitively through a LOWER(name) index.
Heavy bytes live apart. Uploaded avatars sit in profile_pictures as BYTEA so the hot users row stays small and quick to read.
One row per rotation. auth_sessions stores only a SHA-256 digest of each refresh token, and every rotation descended from one sign-in shares a family_id — so a replayed token kills the whole chain in a single UPDATE.
The original idea, still at the core
An organizer proposes a few times; invitees answer in plain language. That whole exchange is a handful of tables — the event, its candidate slots, one invitation per guest holding their free-text reply, and the notifications that nudge everyone along.
proposed_timescandidate slotsinvitationsone per guest + reply + roleinvite_linksshareable join tokenpending_email_invitesinvite a non-usernotificationsin-app nudgesOne reply per guest. A unique (event_id, user_id) on invitations makes “exactly one response” the database’s job, not the application’s.
Invite a stranger. pending_email_invites parks an invitation against an email address and attaches it the moment that person signs up.
More than one organizer. A role column on that same invitation row lets the creator hand the organizing powers to anyone already invited — no second table, because one row per person already exists.
Gemini’s work, written down
dashed: events is borrowed from Scheduling — the AI tables hang off it
Every AI feature re-runs as the conversation changes and saves its output as a fresh row — the suggested time, a persona-flavored comment, a practical weather read. They all anchor to the event, with token usage and budget tracked right alongside.
resolutionsAI suggested timeai_commentspersona blurbweather_reportsyr.no + Geminiai_participantschat personalitiesai_usage_logstokens & costai_usage_limitsbudget (singleton)Latest wins, history stays. Each re-run writes a new row. The newest is shown; older ones remain for transparency and debugging. Per-slot scores ride in JSONB, so their shape can change without a migration.
No one torches the budget. Every Gemini call lands in ai_usage_logs with token counts, checked against a single ai_usage_limits config row.
A live room on every event
Switch chat on and an event grows a message stream. People and AI participants post into the same table, with read cursors and emoji reactions kept in their own companions.
chat_read_stateper-user cursorchat_message_reactionsemoji, unique per userai_participantsAI authors (shared)Human or AI, never both. A CHECK (chk_chat_messages_one_sender) guarantees each message has exactly one author — a user or an AI participant.
Built to page. The (event_id, id DESC) index lets “older messages” scroll back with index-only scans. ai_participants is shared with the Intelligence domain — the bridge between the two.
Two people, one canonical row
DMs mirror event chat almost exactly — thread, messages, read cursor, reactions — but a thread isn’t tied to any event. The clever part is the ordering that keeps every pair to a single row.
dm_messagesthe thread bodydm_read_stateper-user cursordm_message_reactionsemoji, unique per useruser_a_id < user_b_id, always. A CHECK forces canonical pair ordering, so a thread lookup never has to try the pair both ways — every two people map to exactly one row.
A quiet decline. A thread carries a status; declining simply stops the fan-out — no notification, no leak — and flips back to pending if the other person writes first.
A room that belongs to no event
Standalone group chats like #oslo and #fotball, with their own membership, roles, and three visibility tiers. Seven tables that borrow every lesson the chat and DM tables learned first.
channel_membersowner / admin / memberchannel_messageshuman-only (v1)channel_read_statecursor + mute / unreadchannel_message_reactionsemoji, unique per userchannel_join_requestsprivate-channel askschannel_blocksremoved = blockedExactly one owner. A partial unique index (WHERE role = 'owner') makes “never adminless, no coups” a database invariant, not a code convention.
Removed means blocked. A side channel_blocks table keeps ejected users out of the hot fan-out path entirely — no WHERE removed_at IS NULL smeared across every query.
One library, photos, video and files
One library per person holds photos, short videos and uploaded documents as a single kind of row. Variants fan out per size and format on Cloudflare R2, and a usage row records every place each item is shown.
photo_variantssize × format on R2photo_usageswhere-used trackingSame table, three media. A media_kind column — image, video or document — is all that separates them at the row level; the bytes live in R2, never in Postgres.
Where-used, tracked. photo_usages records every place an item appears — chats, DMs, covers — so a delete detaches it cleanly everywhere it was shown.
The third pillar: a group picks what
Scheduling settles when; Decisions settles what — where to travel, what to name the band. A decision holds candidate options and one free-text preference per person, and Gemini weighs them into a recommended pick with per-option fit scores and a written rationale. It borrows the whole event playbook — invites, an optional AI commentator, a full chat — so thirteen tables come along for the ride.
decision_optionsthe candidate choicesdecision_participantsmember + preference_textrecommendationsthe AI's pick (like resolutions)option_insightsgrounded pros / cons per optiondecision_ai_commentsFunny / Serious persona takedecision_messagesa decision grows a chatThe Events pillar, mirrored. recommendations is to a decision what resolutions is to an event — the model's pick, re-run whenever the options or preferences change, with fit scores in JSONB.
Everything else, reused. The rest — decision_context_items (AI background), decision_message_reactions, decision_chat_read_state, decision_ai_participants, decision_invite_links and decision_pending_email_invites — mirror the chat and scheduling tables one-for-one.
A container, not a fourth pillar
Events settle when and decisions settle what; a trip settles nothing — it organizes them. It is a container: a dated thing with its own roster, chat, notes, documents and album, gathering events and decisions into one timeline. Dates are optional and may be in the past, so the same row is equally a plan for next month and a memory from years ago.
trip_membersguide / travelercontainer_linksthe soft pillar↔container tietrip_notesmarkdown, optionally datedtrip_documentsPDFs and imagestrip_mediathe album, from the librarytrip_briefsthe Travel AI brieftrip_messageshuman-only chatOne table, both container kinds. container_links carries typed-nullable FKs with two CHECKs — exactly one pillar (event xor decision) against exactly one container (trip xor channel) — and UNIQUE NULLS NOT DISTINCT so a pair can't repeat. Adding a kind is one nullable column.
A link is a tag, never a grant. ON DELETE CASCADE on all four sides means deleting either end drops only the link row. Container membership never grants pillar access — authorization stays per-resource, and the chips are access-filtered per viewer.
The rest, borrowed wholesale. trip_invite_links, trip_pending_email_invites, trip_message_reactions, trip_chat_read_state and trip_ai_comments are the scheduling and chat tables again, one-for-one.
How much room a person gets
Two things cost real money as Cue grows: stored bytes, and the fan-out every created event, decision or trip drags behind it. So each user carries an effective level with an allowance on both. Room grows with tenure on its own, and admins can pin someone to any rung — up as a gift, down as an abuse lever.
user_level_grantsappend-only who / when / whyuser_creationsthe 30-day ledgerRows, not an enum. Levels are catalog rows precisely because the set is open — adding a tier or retuning an allowance is an UPDATE, never a wire-contract change shipped clients must be checked against.
Earned is computed, never stored. Each earnable rung carries a threshold in days since users.created_at, so the earned level is just the highest rung an account's age clears. No promotion job, no drift, nothing to fall out of sync.
A ledger, because live rows are gameable. user_creations appends a row per create and is never trimmed by deletes — create, fan out invites and AI, delete, repeat would otherwise be free. The window rolling is what returns a slot.
Patterns that repeat
Ten domains, but a small set of conventions runs through all of them. Learn these once and the whole schema reads the same way.
Flyway, never Hibernate DDL
Every schema change ships as a numbered V<n>__*.sql migration. Hibernate runs in validate mode — it checks that the entities match, but never touches the DDL.
One enum pattern, everywhere
Enum-shaped columns are VARCHAR + CHECK, persisted lowercase through a custom AttributeConverter — never @Enumerated. The wire format and the stored value can’t drift.
JSONB where the shape flexes
Per-slot AI scores, weather payloads, and cover-crop coordinates live in JSONB, so their structure can evolve without a migration.
Composite keys for read state
Every read cursor is keyed on (user, conversation) — a natural primary key that needs no surrogate id and saves a lookup.
The V28 lesson
Read-state rows point at a last-read message with ON DELETE SET NULL, so deleting a message can never drop someone’s place. Every table built since starts this way.
Partial indexes earn their keep
Active-only uniques — one AI name per event, one owner per channel — and a processing-only sweep index do exactly as much work as needed, and not a row more.
Soft delete with a window
Photos get a 30-day trash; removed AI participants are kept so their past messages still attribute. Nothing a user might miss vanishes on the spot.
Search indexes the row itself
Searchable tables carry a generated tsvector column and a pg_trgm index — never a denormalized search table. A generated column cannot drift from the row it summarizes, so search can’t go quietly stale.
That’s the tour
This is the shape of it. The full written reference — every column, type, constraint and index — lives beside the code, and the whole system that wraps this database is its own story.