hyper-calendar

Appearance

Data Model ​

One SQLite database, defined with Drizzle in core/src/db/schema.ts. Migrations live in core/ and run automatically on server boot, before it starts listening — so first boot creates the schema and upgrades are idempotent.

Conventions

  • Primary keys are text — nanoid-generated, not autoincrement.
  • All timestamps are integer epoch milliseconds, UTC. There are no SQLite date types anywhere.
  • Mutable user data carries a monotonic version integer, used for optimistic concurrency and offline conflict detection.
  • Deletes of user content are soft (deleted_at) so trash and undo work.

Identity and credentials ​

users ​

id, username, email, display_name, password_hash (scrypt), is_admin, avatar_data (blob) + avatar_mime_type + avatar_version, discovery_disabled, created_at, updated_at.

The first user to register gets is_admin = true. discovery_disabled removes the user from the /api/users directory used for invites.

sessions ​

id (sha256 of the opaque token), user_id, created_at, expires_at. The cookie carries the token; only its hash is stored.

webauthn_credentials ​

id, user_id, public_key, counter, transports, name, created_at. One row per passkey.

webauthn_challenges ​

Short-lived ceremony state: id, user_id (nullable — registration happens before a user exists in some flows), challenge, expires_at.

user_totp ​

user_id (PK), secret, enabled, created_at, confirmed_at. enabled only flips after a code is confirmed, so a half-finished enrollment can't lock anyone out.

totp_recovery_codes ​

id, user_id, code_hash (scrypt), used_at. Single use.

pending_logins ​

id, user_id, expires_at. The short-lived bridge between the two steps of a 2FA sign-in — password verified, second factor not yet.

app_passwords ​

id, user_id, name, token_hash (sha256), scope (default read-write), last_used_at, created_at. These are the CalDAV Basic-auth credentials.

user_encryption ​

user_id (PK), bundle (JSON text), created_at, updated_at. The wrapped-key bundle for client-side encryption. The server stores and serves it verbatim and can't unwrap it.

Instance settings ​

app_settings ​

key (PK), value. Key/value bag for instance-wide config — most importantly whether registration is still open.

Calendars and events ​

calendars ​

id, user_id, name, color, description, timezone, default_reminders (JSON), is_default, hidden, encrypted, caldav_enabled, sort_order, created_at, updated_at.

Plus two feature clusters on the same row:

  • ICS subscription — subscription_url, refresh_interval_minutes, last_fetched_at, last_fetch_error, etag, last_modified.
  • CalDAV link — caldav_url, caldav_username, caldav_password (AES-256-GCM sealed with CALDAV_SECRET), caldav_sync_token, caldav_ctag.

A subscription and a CalDAV link are therefore both just a calendar with extra columns populated, which is why there's no separate subscriptions listing endpoint.

events ​

id, user_id, title, description, location, start_utc, end_utc, timezone (IANA), all_day, color, encrypted, calendar_id, ical_uid, rrule, rdate, reminders (JSON), version, caldav_href, caldav_etag, caldav_base (JSON snapshot of the last-synced field values, for three-way merge), created_at, updated_at, deleted_at.

This is the table the whole architecture turns on: an instant pair plus a timezone, stored once. Per-day segments are derived in the frontend at render time and are never written back. See Architecture.

A row is a recurring master when it has an rrule, an rdate, or both. rdate is the RFC 5545 RDATE: extra occurrence starts the rule can't express, as a comma-separated list of UTC milliseconds. Nothing in the app authors them — the repeat editor only writes RRULEs — but CalDAV clients and .ics files do, and keeping them means a series doesn't silently lose dates on the way back out.

event_overrides ​

id, master_id, user_id, occurrence_start_utc, cancelled, encrypted, then the overridable fields (title, description, location, start_utc, end_utc, all_day, color), version, timestamps.

One row per modified occurrence of a recurring event. cancelled is how a single occurrence is deleted without breaking the series.

event_attachments ​

id, event_id, user_id, filename, mime_type, size, data (blob), encrypted, created_at. Attachments live in the database, so they're covered by the same backup as everything else.

tasks ​

id, user_id, title, description, color, due_utc, all_day, timezone, done, completed_at, sort_order, ical_uid, version, timestamps.

ical_uid is the task's identity over CalDAV, where the list is published as a VTODO collection. It's null on rows created in the app until one is minted the first time CalDAV serves them; a UID sent by a client is kept as-is so their copy and ours stay the same object.

Sharing ​

calendar_shares ​

id, calendar_id, owner_id, grantee_id, role, hidden_by_grantee, timestamps. hidden_by_grantee is why hiding a shared calendar is a separate endpoint from hiding your own — the grantee's preference must not touch the owner's row.

event_shares ​

id, event_id, owner_id, grantee_id, role, timestamps. Share a single event without sharing its calendar.

event_attendees ​

id, event_id, owner_id, user_id, status (default needs-action), comment, invited_at, responded_at, updated_at. Invitations and RSVPs.

Authorization decisions over all three of these are centralized in core/src/lib/access.ts — the security modules only establish identity.

Notifications ​

user_settings ​

user_id (PK), notifications_enabled, default_reminders (JSON), all_day_reminder_minute, updated_at.

push_subscriptions ​

id, user_id, endpoint, auth, user_agent, created_at. One row per device that opted in.

reminder_log ​

id, user_id, event_id, occurrence_start_utc, minutes_before, fired_at. The idempotency ledger for the reminder scheduler — it's what stops a reminder firing twice across restarts or repeated scheduler ticks.

event_search_tokens / override_search_tokens ​

(event_id | override_id + master_id), user_id, token. Denormalized token index backing GET /api/events/search. Maintained on write; encrypted rows contribute nothing, since the server has no plaintext to tokenize.

Backup ​

The database file is the backup — everything above lives in it, attachments and avatars included. See Deployment and Operations for the snapshot and restore runbook.