Data Model

Tomoda's persistent state lives in a single Postgres database (PostGIS-enabled). The schema is defined by goose migrations in backend/db/migrations/; db.Migrate applies them at boot. The structs in backend/internal/models/ are plain, hand-written Go types the app passes around, not schema declarations. The models package is shared: every vertical domain (see Domains) reads and writes these structs through its own Store, which runs sqlc-generated queries over pgx/v5 and maps rows to *models.X. This page groups the tables by the domain that owns them.

Source of truth

The goose migrations in backend/db/migrations/*.sql are the authoritative schema; backend/db/schema/ is a generated per-domain snapshot that sqlc targets. The structs in backend/internal/models/*.go are plain Go types with no schema tags. The tables below name the load-bearing entities and their role, not every join column, soft-delete flag, or audit timestamp. For exact fields, read the migration or the struct.

Where enums live

Every finite value-set is a named string type declared next to the struct it belongs to, not in a central file. EventStatus lives in event.go, ChatMessageType in chat_message.go, TravelLogSource in travel_log.go. Shared enums live with their canonical owner: Visibility (used by profile, moment, stamp) in user_profile.go, ContentStatus in tomoda_stamp.go. Each type carries constants and an IsValid() used at trust boundaries. The tables below name the enums each entity carries.

Identity & auth

Owned by the user and auth domains.

Entity	Role
User	Canonical account record: email, username, hashed password, AccountType, UserRole, OAuth linkage, Stripe fields, profile basics. Enums AccountType and UserRole live here.
UserProfile	Per-user display, preferences (ChatPreferences, MapPreferences), and denormalized visit stats materialized from Travel Log writes. Canonical home of the Visibility enum.
PublicUser	Read projection of a user for cross-domain briefs.
Session	Active login session, indexed by device; drives the "Active sessions" screen and per-session revocation.
RefreshToken	Long-lived opaque token used to mint new JWT access tokens; mirrored into Redis for fast validation.
LoginHistory	Per-login record exposed to users via the "Login history" screen.
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 / SafeAPIKey	Encrypted programmatic key with a scope list, authenticated via X-API-Key. SafeAPIKey is the redacted read shape.

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

Social graph

Owned by the friend domain.

Entity	Role
Friendship	Symmetric friendship with requester_id, addressee_id, and FriendshipStatus (pending, accepted, blocked). Source of truth for who sees whose live location and friends map.
CloseFriend	Owner-scoped inner-circle list backing the close_friends audience. Asymmetric: a row (alice, bob) means Alice added Bob. Presupposes an accepted friendship.

Events

Owned by the event domain.

Entity	Role
Event	Core social object: title, time range, category (a taxonomy string, not a foreign key), location, capacity, owner. Carries EventStatus, EventType, EventVisibility.
EventParticipant	Join row, user × event, with role (owner, host, guest) and EventParticipantStatus (pending, approved, rejected, left).
EventItem	Event-owned copy of a curated board card, snapshotted from a plan Item at promotion. Photos and links are JSONB (EventItemPhotos, EventItemLinks) so the board renders self-contained; links stay a promotion-time snapshot, while the host can replace an item's photos post-promotion via photo_keys on the event-item write path. Reuses ItemRole.

EventItem is fully owned by the event: editing it never touches the source plan, and it survives the source Item being edited or deleted. See Snapshot event items at plan promotion.

Plans

Owned by the plan domain. A plan is a date-free, collaborative intention that promotes into an Event.

Entity	Role
Plan	The canvas: a set of Items grouped by PlanLocation place-cards. Carries PlanVisibility, PlanStatus.
Item	One idea on a board. The board is the owner's stash (PlanID nil) or a plan's shared canvas (PlanID set); LocationID is the grouping key. Carries ItemRole, ItemStatus. content_key dedups re-shares against the owner's active stash (partial-unique on (owner_id, content_key)).
ItemLink	An item's read-only pointer at a shared parsed_links record (item_id, parsed_link_id, position); the API flattens the two.
parsed_links (shared)	One row per canonical_url: parsed content (Platform, ItemLinkStatus, ItemLinkSignals, LinkFacts, thumbnail, resolved place) + save_count. Shared across every item/user that saved the URL; re-parse propagates to all, GC'd when the last pointer is deleted.
ItemPhoto	Photo attached to an item. Carries ItemPhotoSource.
ItemNote	Free-text note on an item.
PlanLocation	A place-card on the canvas (PlanLocationStatus); PlanInterestVote records votes for the leading place.
PlanAvailabilityOption / PlanAvailabilityVote	The when-poll: proposed day/time options (PlanAvailabilityKind) and per-axis votes.
PlanCollaborator	Roster row with PlanCollaboratorRole, PlanCollaboratorStatus, PlanRSVP; PlanShareToken mints join links.

Chat

Owned by the chat domain.

Entity	Role
Chat	Either an event-scoped group room or a direct/group DM (ChatType = event / dm / group); holds title, avatar, ChatDisappearingMessages setting.
ChatParticipant	Membership row with read state (last_read_at), nickname, mute, last-seen.
ChatMessage	A message in a room: text, image, sticker, share, system, with optional expires_at. Carries ChatMessageType; JSONB MessageMetadata is the typed envelope (mention slots via Mention / MentionKind, tag notices via TagKind). MessageReaction holds reactions.
SystemMessage	Payload for system events (SystemMessageEvent), serialized into a ChatMessage and mapped 1:1 to frontend locale keys.

Every Event has a Chat, but Chat is independent enough to back direct messages and ad-hoc group chats that belong to no event.

Moments

Owned by the moment domain.

Entity	Role
Moment	Lightweight, ephemeral post anchored to a Location. Uses Visibility (public, friends, private), optional media, and TTL.
MomentLike	Like edge, user × moment, unique on the pair.
MomentTag	Tag edge (author tags friends at create-time). Each tag writes a UserTravelLog row so the tagged friend's visit is credited.

Presence & check-ins

Owned by the presence domain.

Entity	Role
ActiveLocationSession	The opt-in live-location broadcast a user shares so friends see where they are right now.
UserCheckin	Lowest-friction visit producer: no photo, no caption, anchored to a Location. Throttled 6h per (user, location). Its UserTravelLog row is the count-able fact; deleting the check-in leaves the log alone.
CheckinCompanion	Companion tag join, symmetric with MomentTag. Each row writes a UserTravelLog row for the tagged friend.

Locations & maps

Owned by the location and maps domains. Location is the shared place layer everything pins to.

Entity	Role
Location	Canonical place record: lat/lng, LocationProvenance (concrete enum: provider_api, serper_maps, osm_seed, wikidata_seed, geonames_seed, unesco_seed, bulk_import, promoted), and LocationTranslation per-language fields. Carries the LocationBusinessStatus enum (OPERATIONAL, CLOSED_PERMANENTLY, CLOSED_TEMPORARILY) and a RegionID FK (→ regions, nil until enrichment resolves it). Every external identity lives one-row-per-ref in location_providers ((provider, provider_id) composite unique); there is no primary provider column. Denormalized map fields (sub_category, save_count, moment_count, is_active) live on the same row, alongside a trigger-maintained search_text blob and an optional embedding (halfvec(1024)) for semantic search. Timezone is not stored (derived from coordinates).
location_providers	One row per external source ref for a location; its PK guarantees a (provider, provider_id) maps to exactly one location, and the dedup ladder resolves a place by any of its refs.
Region / regions	The administrative-area tree (ADM1/ADM2/ADM3 via AdminLevel) with self-referential parent_region_id; a location's region_id FK points into it. Localized names, identity (provider, provider_id) unique. city_id is a self-FK on locations (cities are locations), assigned in one set-based nearest-city pass at load.
Country / countries	Gazetteer reference row keyed by ISO 3166-1 alpha-2 code (PK): display name, localizations, continent, centroid lat/lng, prominence; (provider, provider_id) unique. Backs locations.country_code.
UserAffinity / UserSave / UserDismissal	Per-user map personalization: category affinity, saved places, dismissed markers.
RankingImpression / RankingSelection (ranking_impression / ranking_selection)	Durable search-ranking label sink. An impression (sampled) is the ordered candidates a search showed (JSONB, with per-candidate feature sub-scores); a selection (every one) is the location the user picked. Joined on ranking_query_id, they are the offline weight tuner's labeled cases. Pruned after 180d by the ranking_label_prune cron.
RankingWeights (ranking_weights)	The applied composite-ranking weight vectors (the six 0..1 blend weights + applied_by / note provenance). Exactly one row is active (partial unique index ... WHERE active); the ranker resolves it over env config and hot-refreshes it on an interval, so an approved retune applies without a redeploy.

Proximity dedupe and viewport clustering run on the PostGIS geometry index. See Backend → Locations service for the resolve, dedup, and provenance story. The ranking label sink feeds the offline weight tuner and is fed best-effort (a failed insert never blocks a search or selection).

Content catalog

Owned by the content domain: curated, admin-authored source-of-truth rows read by the game engine and profile surfaces.

Entity	Role
TomodaStamp	Curated, geographic stamp catalog. Earned by satisfying MinimumVisits guide entries. Carries StampScope, StampSource, and the canonical ContentStatus enum.
StampGuideEntry	One curated place/city/region/country in a stamp's guide, carrying travel-guide content.
Curio	Tomoda Collection artwork earned when its backing challenge completes. Carries HiddenTier.
Challenge	The engagement unit users progress through; awards a reward curio on completion. Carries ChallengeScopeKind, ChallengeDuration, ChallengeTzMode.
ContentTranslation	Per-language JSONB payload for TomodaStamp / Curio / Challenge.

Passport (earned state)

Owned by the passport domain: the per-user earned and progress rows the game engine writes and profile reads.

Entity	Role
UserTravelLog	Append-only factual ledger: "user X was at location Y at time T." One row per visit; immutable. Scope columns (district/city/region/country/continent) snapshotted on write. Carries TravelLogSource.
UserStamp	Passport row for an earned curated stamp, one per (user, tomoda_stamp). Carries UserStampKind.
UserStampProgress	Per-user progress toward a curated stamp; MatchedIDs accumulates satisfied guide-entry IDs.
FirstDiscovery	Records the user first to visit a location (one row per location), resolving first-arrival races atomically. Powers the discovery bonus.
UserCurioEarned / CurioEarnEvent	Curio dedup row plus the multi-row earn audit trail. CurioEarnSource names the origin.
ChallengeProgress	Per-(scope, challenge) progress, same shape for user- and circle-scoped challenges. Carries ChallengeProgressState.

Partners (multi-tenant scoping)

Owned by the partner domain.

Entity	Role
Partner	A non-user tenant (merchant, advertiser, organization), URL-scoped via :partner_id. Carries PartnerStatus; slug is unique and URL-safe.
PartnerMembership	Join row, user × partner, with PartnerMembershipRole (owner, admin, staff). Composite unique on (partner_id, user_id). PartnerMemberView is the roster read shape.

User.account_type = partner is a flag that hides the account from consumer surfaces; it grants no partner permissions on its own. Only PartnerMembership rows do. See Partners service for the access model and the "≥1 owner per partner" invariant.

Notifications

Owned by the notification domain.

Entity	Role
Notification	Inbox row per recipient with NotificationKind, group_key, actors (JSONB ActorPreview), payload, read_at, and NotificationResolution. Grouped at write time on (user_id, group_key) while unread. Purged after 90d.
PushToken	Per-device APNs / FCM / Web Push credential. Carries PushPlatform; unique on (user_id, platform, token).

See Notifications for the hybrid seen/resolved grouping model.

Ops & audit

Entity	Role
AuditLog	Append-only record of privileged or sensitive actions (admin changes, account deletions). Carries AuditAction.

Naming and lifecycle conventions

• UUIDs are primary keys throughout (uuid.UUID, gen_random_uuid()).
• Timestamps are stored UTC; created_at / updated_at are standard.
• Soft delete applies to high-value records (User, Moment, ChatMessage) via a deleted_at timestamp column, filtered explicitly with WHERE deleted_at IS NULL in the queries; the daily purge and moment_purge jobs hard-delete after a grace window. See System Overview.
• Spatial columns use PostGIS (geography(Point, 4326)); the shared Spatial mixin adds denormalized lat/lng floats alongside for reads that don't need PostGIS.
• Enums are named string types next to their struct, each with IsValid() at trust boundaries. There is no central enums file.