Data Model

Tomoda's persistent state lives in a single Postgres database (PostGIS-enabled). GORM models in backend/internal/models/ declare the schema; in non-production environments db.AutoMigrate keeps it in sync at boot. This page groups the core tables by domain and shows how they relate.

Source of truth

The GORM struct tags in backend/internal/models/*.go are the authoritative schema. The diagram below shows logical relationships; not every join column, soft-delete flag, or audit timestamp is rendered.

Entity-relationship diagram

erDiagram
  User ||--o{ Session : "has"
  User ||--o{ RefreshToken : "has"
  User ||--o{ WebAuthnCredential : "registers"
  User ||--o{ APIKey : "owns"
  User ||--o{ Event : "creates"
  User ||--o{ EventParticipant : "joins"
  Event ||--o{ EventParticipant : "has"
  Event ||--|| ChatRoom : "spawns"
  Event ||--o{ ChatMessage : "scopes"
  ChatRoom ||--o{ ChatRoomParticipant : "members"
  ChatRoom ||--o{ ChatMessage : "contains"
  User ||--o{ ChatRoomParticipant : "is_in"
  User ||--o{ ChatMessage : "authors"
  User ||--o{ Friendship : "requester"
  User ||--o{ Friendship : "addressee"
  User ||--o{ Moment : "posts"
  Moment ||--o{ MomentLike : "receives"
  User ||--o{ MomentLike : "gives"
  Event }o--|| Category : "tagged"
  Event }o--o| Location : "located_at"
  Moment }o--o| Location : "located_at"
  User ||--o{ OTP : "verifies_via"
  User ||--o{ AuditLog : "subject_of"
  User ||--o{ UserActivityLog : "produces"

Auth & identity

Entity	Role
User	Canonical account record — email, username, hashed password, role, OAuth linkage flags, profile fields, map preferences.
Session	Active login session, indexed by device. Drives the "Active sessions" screen and supports per-session revocation.
RefreshToken	Long-lived opaque token used to mint new JWT access tokens. Mirrored into Redis for fast validation.
WebAuthnCredential	Stored passkey public key + counter for one user. Multiple credentials per user are supported.
OTP	Short-lived one-time codes (signup, password reset, email change, phone verification). Purpose-tagged.
APIKey	Encrypted programmatic key with a scope list. Authenticated via the X-API-Key header.

See Authentication for how these compose into login and session flows.

Events & chat

Entity	Role
Event	Core social object — title, time range, category, location, capacity, visibility, owner. Roughly a "plan to meet up".
EventParticipant	Join table — user × event with role (owner, host, guest) and status (pending, approved, rejected, left).
ChatRoom	Either an event-scoped group room or a direct/group DM (type = event | dm | group). Holds metadata: title, avatar, settings.
ChatRoomParticipant	Membership row — user × room with read state (last_read_at), nickname, mute, and last-seen.
ChatMessage	A message in a room. Supports text, image, sticker/GIF, share, system, with optional expires_at for disappearing messages.

The relationship is "every Event has a ChatRoom", but ChatRoom is independent enough to back direct messages and ad-hoc group chats that don't belong to any event.

Social graph

Entity	Role
Friendship	Symmetric friendship modelled with requester_id, addressee_id, and status (pending, accepted, blocked).

The friendship row is the source of truth for who can see whose live location and who shows up on the friends map.

Moments & locations

Entity	Role
Moment	Lightweight, ephemeral post anchored to a location. Has visibility (public, friends, private), optional media, and TTL.
MomentLike	Like edge — user × moment, unique constraint on the pair.
Location	Canonical place record. Carries lat/lng, H3 cell index (resolution 12, ~5m), Photon/Google IDs, and timezone derived via tzf.

Location is shared by events and moments — it is the "place layer" the app pins everything to. H3 indices enable fast proximity dedupe and clustering for the discovery map.

Ops & audit

Entity	Role
Category	Curated set of event tags; seeded at boot from CategoryHandler.SeedCategories.
AuditLog	Append-only record of privileged or sensitive actions (admin changes, account deletions, etc.).
UserActivityLog	Higher-volume behavioural log — login, logout, profile edits — used for security insights and admin tooling.
LoginHistory	Per-login record exposed to users via the "Login history" screen.

Naming and lifecycle conventions

• UUIDs are used as primary keys throughout (uuid.UUID, gen_random_uuid()).
• Timestamps are stored UTC; created_at / updated_at are standard.
• Soft delete is applied to high-value records (User, Moment, ChatMessage) via GORM's DeletedAt; the daily purge and moment_purge jobs hard-delete after a grace window. See System Overview.
• Spatial columns use PostGIS (geography(Point, 4326)); H3 indices live alongside for cell-level operations that don't need PostGIS.