PostgreSQL

Calendar / events database schema

A calendar schema with events, RFC 5545 RRULE recurrence, invitees, RSVPs, conferencing links, and per-instance overrides for edited occurrences.

Start for free

Database schema previewCalendar / events database schema

Recreate this in Raltey

Copy this prompt

Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.

Prompt

Design a PostgreSQL database schema for: Calendar / events database schema.

A calendar schema with events, RFC 5545 RRULE recurrence, invitees, RSVPs, conferencing links, and per-instance overrides for edited occurrences.

Include these tables: event, event_override, invitee.

Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.

Context

- Recurrence stored as RFC 5545 RRULE strings, not exploded rows.
- Per-instance overrides live in event_override for edited recurrences.
- Time zone handled by storing UTC + IANA tz column.

Patterns

- Recurrence as a single RRULE string instead of exploded rows.
- Per-instance overrides linked by the original UTC start time.
- UTC + IANA time zone for correct DST handling.

Tables

The schema, table by table

event

DBML - event

Table event {
  id            uuid pk
  organizer_id  uuid [ref: > user_account.id, not null]
  title         text not null
  description   text
  starts_at     timestamptz not null
  ends_at       timestamptz not null
  time_zone     text not null   // IANA, America/Bogota
  rrule         text             // RFC 5545
  location      text
  conferencing_url text
  created_at    timestamptz default `now()`
}

event_override

Per-instance edits for a recurring event.

DBML - event_override

Table event_override {
  id             uuid pk
  event_id       uuid [ref: > event.id, not null]
  original_start timestamptz not null
  starts_at      timestamptz
  ends_at        timestamptz
  is_cancelled   boolean default false
}

invitee

DBML - invitee

Table invitee {
  event_id   uuid [ref: > event.id]
  user_id    uuid [ref: > user_account.id]
  status     text default `\'pending\'`   // pending, accepted, declined, tentative
  responded_at timestamptz
  pk (event_id, user_id)
}

Relationships

From	To	Type
user_account	event	1:N
event	event_override	1:N
event	invitee	1:N

Notes from the field

Use a recurrence-rule library (rrule.js) in the app layer to materialize occurrences.
Index event(organizer_id, starts_at) for the organizer view.
Cancel an instance by inserting an event_override with is_cancelled = true.

Frequently asked

Why not store every recurring instance as a row?

It does not scale. A weekly recurring meeting over 5 years is 260 rows; multiply by every recurring event. Storing one RRULE row plus per-instance overrides is far cheaper.

How do I support a guest accepting a single instance?

Add an event_override row for that original_start with a per-instance invitee response. The override carries the diverged state.

More templates

FlowchartSaaS onboarding email flow FlowchartCustomer support runbook flow FlowchartPayment webhook retry and error flow FlowchartGitHub PR review and merge flow FlowchartLogin with magic link flow ArchitectureThree-tier SaaS architecture on AWS ArchitectureMicroservices architecture on Kubernetes ArchitectureReal-time data pipeline on GCP

Open this template in Raltey

Edit it on the canvas, ask the AI agent to extend it, then share or export.