Database¶

PostgreSQL is the system of record for all durable state. The data layer is sqlc-generated type-safe queries over pgx/v5 (no ORM), with goose migrations and the PostGIS extension for geospatial queries.

backend/internal/database is boot / driver infrastructure: it opens the pgx pool and owns Migrate (which applies the goose migrations + idempotent runtime post-steps). Each domain under backend/internal/services/<domain>/ gets the *pgxpool.Pool (via wiring.ProvidePool) and runs SQL through its Store using generated <domain>db query code.

Source files:

File	Role
`backend/internal/database/database.go`	`New`, pgx pool + connect retry, `Migrate`
`backend/internal/database/pgx.go`	`NewPool` (pgxpool config)
`backend/internal/database/pgconv/pgconv.go`	pgtype ↔ Go converters used by every store mapper
`backend/internal/migrate/goose.go`	`ApplySchema` — applies embedded goose migrations
`backend/internal/migrate/manual.go`	`RunManual` idempotent post-steps (seeds, FK coverage)
`backend/db/migrations/`	goose migrations (embedded via `db/embed.go`)
`backend/db/schema/`	per-domain schema snapshot sqlc generates against (generated)

The four artifacts¶

Artifact	Role	Source
`db/migrations/*.sql`	goose migrations — the runtime schema	hand-written (`task db:migrate:create`)
`db/schema/*.sql`	schema snapshot sqlc generates against	generated from a migrated DB (`task db:schema`)
`internal/services/<d>/<d>db/`	typed pgx query code	generated (`task db:sqlc`)
`internal/models/*.go`	domain structs	hand-written; store mappers convert rows ↔ models

db/schema is derived from db/migrations. A drift guard (task db:schema:verify, run in CI and the pre-commit hook) applies the migrations to a throwaway DB and diffs the dump against db/schema/, failing if they diverge. Commit the migration, db/schema/, queries.sql, and generated code together.

Connection pool¶

database.New opens a single *pgxpool.Pool (NewPool in pgx.go), retrying connection refused for up to 60s so the backend survives the Postgres + PostGIS container still booting. MaxConns is 20. Services never hold *sql.DB or a raw connection — they take the pool and use their generated <domain>db.Queries.

The DSN is built by (*DatabaseConfig).DSN(). Use sslmode=require for any non-local environment. Connection caps scale per instance, so tune MaxConns down past ~5 replicas.

Migrations¶

db.Migrate() = migrate.ApplySchema (applies the embedded goose migrations) + migrate.RunManual (idempotent post-steps: synthetic-user seed, FK merge-coverage assertion — safe on every boot).

cmd/server/main.go runs Migrate on boot only when cfg.Server.Env != "production". In production it's skipped; schema changes ship as goose migrations run explicitly.

Iterate on one migration

While a migration is uncommitted, edit the single file and re-apply against a fresh DB (task db:reset or task db:test:setup) rather than stacking a new migration per tweak — goose won't re-run an already-applied file. Ship one migration per change.

Changing the schema¶

task db:migrate:create -- add_users_handle   # new migration in db/migrations
task db:migrate                              # apply (+ runtime post-steps)
task db:schema                               # regenerate db/schema/ + sqlc
# edit the domain's queries.sql, then:
task db:sqlc                                 # regenerate <domain>db code
# update internal/models + the store mapper by hand if the shape changed

Task	Does
`task db:migrate`	apply all migrations + post-steps (same as boot)
`task db:migrate:create -- name`	new timestamped migration
`task db:migrate:down` / `:status`	roll back last / show status
`task db:schema`	regenerate `db/schema/` snapshot (+ sqlc)
`task db:sqlc` / `db:sqlc:verify`	regenerate typed code / fail if stale
`task db:sql:format` / `db:sql:lint`	format / lint the `queries.sql` files (sqlfluff)
`task db:schema:verify`	drift guard (migrations-fresh vs `db/schema/`)
`task db:reset` / `db:test:setup`	recreate volumes + migrate / wipe + migrate + seed

PostGIS¶

The Postgres image ships PostGIS. Geospatial queries live in the domain Store, never in models. Never SELECT the coordinates geometry column — pgx can't decode it. Read the scalar lat/lng columns, and derive the geometry on write:

-- write: separate float8 args (reusing one numeric arg mis-infers the type)
coordinates = ST_SetSRID(ST_MakePoint($lng::float8, $lat::float8), 4326)
-- read: bounding box / distance against the geometry or scalar columns
ST_DWithin(coordinates::geography, ST_SetSRID(ST_MakePoint($1::float8, $2::float8), 4326)::geography, $3)

models.GeoPoint is the shared coordinate type (embedded in models.Spatial); GeoPoint.PointArgs() returns (lng, lat) in ST_MakePoint order. geography casts give distances in metres. Location + event stores are the main consumers; discovery aggregates on top.

Timezone handling¶

Event scheduling needs the IANA timezone for an arbitrary lat/lng. We use github.com/ringsaturn/tzf, an offline finder with the polygon dataset compiled in — no network calls:

// backend/internal/utils/timezone.go
finder.GetTimezoneName(lng, lat)  // e.g. "Asia/Tokyo"

Soft delete¶

deleted_at columns carry lifecycle state (Moment, ChatMessage); queries filter WHERE deleted_at IS NULL explicitly in SQL. Hard-delete sweeps for expired rows run on the scheduler (cron:moment_purge, cron:message_expiry).

Operating notes¶

Backups live in the devops repo's Postgres runbook. Prod runs on in-cluster CNPG.
Connection limits. Tune MaxConns (pgx.go) down as replicas scale.