The Shape of the Data

Cue’s data lives in PostgreSQL 18, and the schema changes only through Flyway: every change is a numbered SQL migration, reviewed like code and never edited once shipped. Hibernate runs in validate mode — it checks that the Kotlin entities match the tables at startup, and never writes DDL of its own.

The 83 tables group into thirteen domains. Every domain that holds people’s data has at least one foreign key into users. The last two belong to no one: Link previews, a cache of public pages every chat shares, and Operations, the backup ledger. Each section below draws one domain as an entity-relationship diagram: the columns that carry its rules, the relations between its tables, and the constraints Postgres enforces on them. A crow’s foot marks the many side of a relation, a bar the one side.

Every row in Cue traces back to a person. A user appears the first time someone signs in — with Google, Apple, or a six-digit email code — and is never asked to invent a username. Sign-in identities live beside the row rather than inside it, so one person can link several providers and stay a single account.

• Two names, on purpose. provider_name follows the Google or Apple display name and refreshes on every sign-in; name is an optional name of your own, unique case-insensitively through a LOWER(name) index. The public handle gets the same treatment.
• Heavy bytes live apart. Uploaded avatars sit in profile_pictures as BYTEA, so the hot users row stays small. Apart from Cue Notes’ documents, every other byte lives in R2.
• One row per rotation. auth_sessions stores only a SHA-256 digest of each refresh token. Every rotation descended from one sign-in shares a family_id, so a token replayed after its successor was spent revokes the whole chain in a single UPDATE.
• A sign-in takes its devices with it. device_tokens and widget_credentials carry the family that registered them, so a logout, a replayed token or a lapsed session ends that phone’s pushes and its widget’s credential along with the session.

An organizer proposes a few times and invitees answer in plain language. That whole exchange fits in 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.

• One reply per guest. A unique (event_id, user_id) on invitations makes “exactly one response each” 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 the same invitation row lets the creator share the organizing powers with anyone already invited — no extra table, because every guest already has a row.
• A nudge points at one thing. notifications carries three nullable foreign keys and a CHECK that exactly one is set: an event, a decision or a trip.

Every AI feature re-runs as the conversation changes and saves its output as a fresh row: the suggested time, a persona’s comment, a practical read of the weather. All of it hangs off the event, with token usage and the budget tracked alongside. A cover Cue paints is the exception: one person asks for it, so its session hangs off them.

• Latest wins, history stays. Each re-run writes a new row. Only the newest is shown; older ones stay for transparency and debugging. Per-slot scores ride in JSONB, so their shape can change without a migration. A status marks each row ok, rate_limited or failed, and a fingerprint of the inputs lets an unchanged request skip the call.
• Nobody torches the budget. Every Gemini call lands in ai_usage_logs with its model, token counts and cost — priced once, when it is logged, at its model’s row in ai_model_prices — and the caps in the single ai_usage_limits row are checked before the next one runs. A usage row is the bill, so it outlives the event, decision or trip it was charged to.
• A cover is a session. image_sessions holds one person painting one image, a cover or a preview of their look: what Cue read, its idea and the look it froze at the start. image_takes holds each picture, its bytes in R2 under a storage_key. The image prompt stays server-side. A kept take becomes an ordinary photos row; the rest are swept with their session. image_looks keeps one row per person, and no row means Cue decides.
• One name per room. A partial unique index allows one active AI participant per name on each event. A removed participant keeps its row, so its old messages still say who wrote them.

Switch chat on and an event grows a message stream. People and AI participants post into the same table; read cursors and emoji reactions live in companions of their own.

• Human or AI, never both. A CHECK guarantees each message has exactly one author: a user or an AI participant.
• Built to page back. An index on (event_id, id DESC) lets “load older messages” walk the history in index order, newest first.
• A delete never takes your read state with it. last_read_message_id is ON DELETE SET NULL: deleting the message a cursor points at clears the cursor instead of failing, and the row keeps its mute and marked-unread flags.

A direct-message thread mirrors event chat almost exactly — messages, read cursors, reactions — but it belongs to a pair of people instead of an event. The neat part is the ordering that keeps every pair to one row.

• user_a_id < user_b_id, always. Whoever writes first, the pair is stored in the same order, so every two people map to at most one row.
• A quiet decline. A thread carries a status. Declining silently stops the fan-out — no notification, no leak — and the thread flips back to pending only if the person who declined writes back.
• Archived, not deleted. archived_at hides a thread from the inbox until a new message brings it back. Only direct messages have it; other kinds mute or leave.

Standalone group chats like #oslo and #fotball, with their own membership, roles and three visibility tiers: public, private and secret. Seven tables that borrow every lesson the chat and DM tables learned first.

• Never two owners. A partial unique index (WHERE role = 'owner') makes a second owner impossible — a database invariant, not a code convention.
• Removed means blocked. A separate channel_blocks table keeps removed members off the hot fan-out path, instead of a WHERE removed_at IS NULL smeared across every query.
• Requests keep their history. Decided join requests stay behind for audit and for the 24-hour cooldown after a denial; only a pending one is unique.

Everyone has one library holding photos, short videos and documents as a single kind of row. Each rendition — every size, every format — is a variant stored in Cloudflare R2, and a usage row records every place an item is shown.

• One table, three media. A media_kind column — image, video or document — is all that separates them at the row level.
• Upload a photo twice, store it once. A unique (owner_id, content_hash) deduplicates each person’s photos and documents; uploading a trashed photo again brings the original back — unless an admin took it down, when removed_at keeps the row past the purge as a tombstone that refuses the same bytes.
• Read once, kept with the item. media_ai_texts holds at most one text per kind — a photo’s AI description, or the digest of a shared file — so later prompts carry the text instead of the bytes. It goes with its item, and a photo’s description goes when its uploader opts out.
• Thirty days in the trash. deleted_at soft-deletes, and a partial index over trashed rows lets the purge job find expired ones without scanning anyone’s library.

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 reuses the whole event playbook — invites, an AI commentator, a full chat — so thirteen tables come along.

• The Events pillar, mirrored. recommendations is to a decision what resolutions is to an event: the model’s pick, re-run whenever options or preferences change, with fit scores in JSONB.
• Grounded, with receipts. option_insights stores a cited brief per option, with its sources and search queries — only when the organizer switches insights on, because grounded calls cost money.
• Everything else, reused. The AI commentator and participants, the chat trio and the invite pair mirror their event counterparts one for one. decision_context_items, the background a recommendation reads, is the one table events don’t have.

Events settle when and decisions settle what; a trip settles nothing — it organizes them. A trip is a container 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.

• One table, both container kinds. Trips and channels share container_links, and UNIQUE NULLS NOT DISTINCT stops a pair from repeating. A new container kind is one more nullable column and a wider CHECK, not a new table.
• A link is a tag, never a grant. ON DELETE CASCADE on all four sides means deleting either end drops only the link. Membership never grants access to a linked event or decision; authorization stays per resource.
• Built to outlive its creator. created_by is ON DELETE SET NULL: a shared memory survives its creator’s account, where events and decisions leave with theirs.

Three things cost real money as Cue grows: stored bytes, the fan-out every new event, decision or trip drags behind it, and the images Cue paints on demand. So each person has an effective level with an allowance on each. Room grows with tenure on its own, and an admin can pin anyone to any rung — up as a gift, down as a brake on abuse.

• Rows, not an enum. Levels are catalog rows because the set is open: adding a tier is an INSERT and retuning an allowance an UPDATE — never a change to the wire contract shipped apps depend on.
• Earned is computed, never stored. Each earnable rung carries a threshold in days since users.created_at, so the earned level is simply the highest rung an account’s age clears. No promotion job, nothing to drift.
• A ledger, because live rows are gameable. user_creations gains a row per create and never loses one to a delete — otherwise create, fan out invites and AI, delete, repeat would be free. image_take_ledger does the same for every take Cue paints. Only the rolling window returns a slot.

A note is markdown several people write at once. Its truth isn’t the markdown column but an Automerge document — a CRDT — so a week offline, a save from a script and an AI rewrite all merge instead of colliding. Postgres keeps that document the way a log-structured store would: every change appended before anyone is told it is safe, folded into a snapshot now and then.

• Stored before it’s acknowledged. A change reaches note_log and commits before the writer hears it is safe. Compaction folds the log into note_docs straight from those rows, and its version column lets exactly one of two racing replicas win.
• Every author, bound. note_actors ties each CRDT actor to the one person who first wrote with it, and keeps the binding — its user_id set null — after they leave, so nobody can later write as a departed author.
• History as copies. A note_versions row copies the markdown at a set of heads, with who wrote since the last one and why it was cut — an editing burst, a restore or a Clean up — so scrubbing back reads no document at all.
• Images stay the uploader’s. note_images points at an ordinary photo in its uploader’s library, deliberately not a photo_usages row, so no Cue app’s where-used chips ever name a note.

Post a link in any chat and it gets a card: a title, a thumbnail, the domain it lands on. cue-core fetches the page once, on the server, and keeps what it found as an immutable snapshot every chat can share — so no reader’s device ever contacts the linked site, and a card never changes under its message.

• Immutable, newest wins. Every fetch writes a new row, and the newest row for a canonical_url is the cache entry, found through an index on (canonical_url, fetched_at DESC). A message keeps the snapshot it posted with when a refresh adds a newer one.
• A failure is a row too. A failed row has no content and stays the answer for ten minutes, so a dead link isn’t fetched again on every pause in typing.
• Decided at post. link_preview_state takes one of four values. snapshot points at a snapshot; cue is a link to Cue itself, drawn for each viewer at read time and never stored; none has no card, though the post’s own fetch may still make it a snapshot within seconds; off was suppressed or removed, and no later edit undoes it. NULL marks a message from before previews, which never gets one.
• Swept, never orphaned. A nightly job deletes snapshots no message points at once they are a week old, with their images in R2. A partial index on each message table’s link_snapshot_id keeps that question cheap.

Every other table is written by cue-core. This one is written by the nightly backup job, from another machine, once each run ends — a failed run too, whenever production can still be reached.

• A failure names its step. A CHECK ties a failed_step — the dump, reading it back, or the restore check — to every failed run, and to nothing else.
• Restored, not just saved. Each run restores its dump into a scratch database and records what came back: the tables, their rows, and the newest migration the dump carried.
• Silence is a signal. The ledger lives in the database it describes, so a run that can’t reach production can’t report either. The dashboard reads the gap as a late backup. Rows are kept for good, one a night.

Thirteen domains, but over a quarter of all tables come in one of two shapes. Every conversation — event chat, direct messages, channels, decision chat, trip chat — is the same trio of tables under its own prefix, and a new chat kind copies it verbatim.

The second shape is the invite pair. Events, decisions, trips and notes — everything a person can be invited to — each carry a reusable join link and a table of invitations to people who aren’t on Cue yet:

A handful of conventions run through the whole schema. Learn them once and every table reads the same way.