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. A source = 'moment' photo also carries moment_id (FK to moments, ON DELETE CASCADE), referencing a moment's photo onto a saved place-card.
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. An optional iso_3166_2 (e.g. "US-CA") carries the ADM1 subdivision code for the passport map. city_id is a self-FK on locations (cities are locations), assigned in one set-based nearest-city pass at load. An optional boundary (geometry(MultiPolygon,4326), GiST-indexed) holds the area polygon for point-in-polygon coords-to-admin resolution; ADM1 boundaries + iso_3166_2 come from the Natural Earth 10m admin_1 source at build time. Never SELECTed (only ST_Contains).
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.
ActivityEvent (activity_events)	Append-only cross-surface interaction firehose (verb, object, surface, jsonb context). Monthly RANGE-partitioned on created_at; partitions older than 30 days are dropped. Online serving never scans it; the activity_rollup cron folds it into interaction_edges.
InteractionEdge (interaction_edges)	Rolled-up per-(actor, edge_type, target) affinity the ranker reads at request time. Weight accumulates in the rollup and is decayed at read by age (last_at). Bounded per user; persists as the durable memory the raw firehose feeds.
activity_rollup_state	One high-water-mark row per rollup job so the incremental rollup only reads events newer than it has already folded.
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, plus stable ids region_id (FK into regions, ON DELETE SET NULL, indexed (user_id, region_id)) and country_code, both copied from the location so admin-1 grouping keys on a stable id rather than the region name. 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.

Account deletion is a schema property

Every column holding a user id references users(id), so erasing an account is one DELETE FROM users rather than a hand-maintained list of deletes that drifts as tables are added. The default action is ON DELETE CASCADE. A new column holding a user id is expected to declare the FK in the same migration that creates it, and to be typed uuid: a varchar user id cannot carry a foreign key, which is how a column silently falls out of the cascade.

Group	Tables
Identity & credentials	sessions, refresh_tokens, login_histories, api_keys, web_authn_credentials, user_profiles
Social & content	friendships, close_friends, moments, moment_likes, moment_tags, events, event_participants, plans, plan_collaborators, plan_poll_votes, items, item_notes
Chat	chat_participants, message_reactions
Passport & rewards	user_stamps, user_stamp_progress, user_curios_earned, curio_earn_events, first_discoveries, user_travel_logs
Presence & discovery	user_checkins, checkin_companions, active_location_sessions, user_saves, user_affinity, user_dismissals
Ops & delivery	audit_logs, notifications, push_tokens, partner_memberships

Deliberate exceptions, all ON DELETE SET NULL:

• chat_messages.user_id. Group-chat history survives its author's departure with the sender anonymized, so the conversation stays readable for the remaining members. Direct rooms are deleted outright by the service before the cascade runs.
• plan_share_tokens.created_by, plan_poll_options.created_by, plan_collaborators.invited_by. These are creator attribution on a shared plan, not personal data owned by one account: a share link, a day/time candidate, and an invite record all stay meaningful to the remaining collaborators. The row survives, the identifier is erased. This is why the columns are nullable.

One case that reads like an exception but is not:

• items.owner_id cascades even though items can sit in a shared plan. The column is NOT NULL, so SET NULL is not expressible, and an item is single-owner personal data.

Object storage has no equivalent guarantee: keys are enumerated by the service before the cascade, since the cascade destroys the only record of them. See Storage → Purge ordering.

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.
• Privacy toggles default off. A boolean that shares something about the user (user_profiles.chat_pref_read_receipts, chats.read_receipts_enabled, is_location_shared, marketing_consent) carries DEFAULT false, so a row created with no user action sits at the most private setting. Clients gate on === true, never !== false, so an absent value on a cold cache reads as off.