Skip to content

Database

PostgreSQL is the system of record for all durable state. We use the GORM ORM with the gorm.io/driver/postgres driver and the PostGIS extension for geospatial queries.

Source files referenced on this page:

Driver and connection pool

database.New opens a single GORM *gorm.DB, attaches our centralised Zap-backed GORM logger, and configures the underlying database/sql pool:

sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)

SetConnMaxLifetime is not set — connections live as long as the pool keeps them. If you put the backend behind a connection-killing proxy (PgBouncer in transaction mode, AWS RDS Proxy with a hard idle timeout), set a lifetime explicitly.

The DSN is built by (*DatabaseConfig).DSN() and is plain host=... port=... user=... password=... dbname=... sslmode=.... Configure sslmode=require for any non-local environment.

Models and AutoMigrate

All models live in backend/internal/models/. The canonical list is in Database.AutoMigrate:

&models.User{}, &models.APIKey{}, &models.WebAuthnCredential{},
&models.AuditLog{}, &models.Event{}, &models.EventParticipant{},
&models.ChatMessage{}, &models.Friendship{}, &models.ChatRoom{},
&models.ChatRoomParticipant{}, &models.Category{}, &models.RefreshToken{},
&models.Session{}, &models.LoginHistory{}, &models.UserActivityLog{},
&models.Location{}, &models.Moment{}, &models.MomentLike{}, &models.OTP{},

cmd/server/main.go calls db.AutoMigrate() on every boot only when cfg.Server.Env != "production". In production the call is skipped, and schema changes must ship through SQL migrations.

Production migrations are explicit

AutoMigrate cannot drop columns, narrow types, or rename safely. Always test destructive changes in a local DB before assuming the dev autoboot has applied them, and write a SQL migration for anything that must run in production.

Adding a migration

For controlled SQL migrations (the path used in dev and prod):

task db:migrate:create -- add_users_handle_column
# scaffolds backend/migrations/000NN_add_users_handle_column.{up,down}.sql

task db:migrate          # apply
task db:migrate:down     # roll back the most recent

cmd/migrate/main.go is a small wrapper that loads config, opens the DB, and runs AutoMigrate on demand — useful for one-off migrations outside the server lifecycle.

PostGIS

The Postgres image we use in docker-compose.dev.yml ships PostGIS preinstalled. Geospatial queries live in repositories rather than models:

// backend/internal/repository/location_repository.go
SELECT id,
       ST_Distance(coordinates::geography,
                   ST_SetSRID(ST_MakePoint(?, ?), 4326)::geography) AS dist
FROM locations
WHERE ST_DWithin(coordinates::geography,
                 ST_SetSRID(ST_MakePoint(?, ?), 4326)::geography, ?)
ORDER BY dist

We use the geography type (rather than geometry) so distances are returned in metres and great-circle calculations work out of the box. The EventRepository and LocationRepository are the main consumers; see backend/internal/repository/ for the full list. The map / discovery UI is powered by these queries combined with H3 hex indexing (see Services → Discovery).

PostGIS columns and GORM

PostGIS geometry columns are scanned via virtual columns (ST_Y, ST_X) in raw SQL. Models hold the lat/lng as ordinary float64 fields tagged gorm:"-" so GORM doesn't try to read or write them; the repository populates them after the query.

Timezone handling

Event scheduling and recurrence calculations need the IANA timezone for an arbitrary lat/lng. We use github.com/ringsaturn/tzf — an offline tz finder with the polygon dataset compiled in.

// backend/internal/utils/timezone.go
var finder tzf.F

func init() {
    finder, err = tzf.NewDefaultFinder()
}

Resolving a zone is finder.GetTimezoneName(lng, lat) and returns e.g. "Asia/Tokyo". Because the dataset is embedded, there are no external calls and no network failure modes.

Soft delete

GORM's gorm.DeletedAt soft-delete column is used on models where life-cycle state matters (e.g. Moment, ChatMessage) — these models are filtered automatically by GORM. Hard-delete sweeps for expired soft-deleted rows run on the scheduler (moment_purge, message_expiry).

Operating notes

  • Backups live in the devops repo's docs/postgres-operations.md runbook. The prod database is managed via Cloud SQL.
  • Connection limits. Cloud SQL has a hard max-connections cap per tier. The pool is sized for one backend instance; multiplying instances multiplies the pool. Tune SetMaxOpenConns down when running with >5 replicas.
  • Slow query logging is controlled by database.log_level in YAML — set to info for verbose query logs or silent for none. Wired through logger.NewGormLogger.

See also