# Repo PostgreSQL Map — ground truth

How PostgreSQL is actually used in **our** codebase, focused on conversationmgmt,
notification (`internal/notification`), spike (`internal/spike`), the shared
`internal/golibs/database` layer, and `migrations/`. This is the factual backbone every
lesson is anchored to. All paths are repo-relative; `file:line` refs are from this branch.

> Verified by a code scan on 2026-07-11. Where the code contradicts a common assumption,
> it's flagged ⚠️. If a lesson and this file disagree, trust the code.

---

## Corrections up front (learned from the scan)

1. ⚠️ **Services don't own the DBs they use.** conversationmgmt → **`tom`** DB; spike →
   **`notificationmgmt`** (no DB of its own); notification → spans **`bob`** (legacy
   `info_notifications`, `announcement*`, roles) **+ `notificationmgmt`** (`bulk_job`,
   emails). There is **no** `migrations/conversationmgmt` or `migrations/spike` dir — their
   tables live in `migrations/tom` and `migrations/notificationmgmt`.
2. ⚠️ **Driver is pgx v4** (`v4.18.3`) dominant, with a few v5 files (`v5.9.2`). `pgtype`
   v1.14, `pgxpool` v4. The `database` layer maintains parallel `*5.go` files.
3. ⚠️ **Migrations are forward-only** — `find migrations -name '*.down.sql'` = **0 files**.
   Naming `migrations/<db>/<NNNN>_migrate.up.sql`; runner calls only `m.Up()`.
4. ⚠️ **No sqlc** in these three services — hand-written raw SQL + `database.*` helpers.
   (sqlc is Eureka-only.)
5. ⚠️ **RLS tenant is set via the pool's `BeforeAcquire` hook**, per connection acquire —
   not per query.

---

## Driver & pool

- `github.com/jackc/pgx/v4 v4.18.3` (primary) + `pgx/v5 v5.9.2` (a few files);
  `pgtype v1.14`, `pgxpool` (v4), `golang-migrate/migrate/v4`.
- **Pool**: `database.NewPool(ctx, l, cfg)` (`internal/golibs/database/pool_gcp.go:33`) →
  `pgxpool.ParseConfig` + `ConnectConfig` with retry. Knobs: `MaxConns`,
  `MaxConnIdleTime` (`pool_gcp.go:58-67`). Non-local uses the **Cloud SQL Go Connector**
  (IAM auth). `poolconfig.BeforeAcquire = setPostgres` (`pool_gcp.go:63`) — the RLS hook.
- Config: `configs.PostgresDatabaseConfig` (`internal/golibs/configs/postgres.go:56`),
  per-DB, YAML-driven.

## The `database` abstraction layer

Core interfaces (`internal/golibs/database/db.go:17-36`):
```go
type QueryExecer interface {          // pure query/exec — accepts pool OR tx
    Query(ctx, sql, args...) (pgx.Rows, error)
    QueryRow(ctx, sql, args...) pgx.Row
    Exec(ctx, sql, args...) (pgconn.CommandTag, error)
    SendBatch(ctx, *pgx.Batch) pgx.BatchResults
}
type Ext interface { QueryExecer; Begin(ctx) (pgx.Tx, error) }   // can start a tx
```
Convention (`.claude/rules/go-code-style.md`): repo methods take `(ctx, db database.Ext|QueryExecer, …)`.

- **Transactions**: `database.ExecInTx(ctx, db, func(ctx, tx pgx.Tx) error {…})`
  (`db.go:41`) — begin → deferred rollback-on-error / commit. Variants: `ExecInTxV2`
  (`db.go:150`; the code even notes `ExecInTx` "has a potential bug"),
  `ExecInTxWithRetry` (`db.go:84`, retries `SerializationFailure` 40001). Lock hints
  `WithUpdateLock()`/`WithShareLock()` → `FOR UPDATE`/`FOR SHARE` (`db.go:136`). Default
  isolation = Postgres default **READ COMMITTED** (not set explicitly).
- **Select/scan**: `database.Select(ctx, db, sql, args...).ScanAll(&ents)`
  (`scanner.go:100,74`); `ScanOne`.
- **Entity pattern** (`entity.go:21`): `FieldMap() ([]string, []interface{})` +
  `TableName() string`; `Entities.Add()` powers `ScanAll`. Helpers `GetFieldNames`,
  `GetScanFields`, `AllNullEntity` (`.Set(nil)` on every field).
- **CRUD builders** (`repo.go`): `GeneratePlaceholders(n)` → `$1,…,$n` (`:14`);
  `UpsertExcept` builds `ON CONFLICT (keys) DO UPDATE SET col = EXCLUDED.col` (`:505`);
  `InsertMany*`, `Update*`. **Batch**: `db.SendBatch(ctx, b)` (spike
  `email.go:102-122`).

## pgtype & NULL

Entities use `jackc/pgtype`: `Text`, `Int8`, `Bool`, `Timestamptz`, `JSONB`, `TextArray`,
`Numeric`. Helpers in `type.go`. **NULL via the `Status` field** — `pgtype.Present` /
`Null` / `Undefined`; set NULL at runtime with `field.Set(nil)` (`spike/.../email.go:26`);
`AllNullEntity` sets everything `Null` before insert to avoid `Undefined`.

Representative entity — `internal/spike/modules/email/domain/model/email.go:9`:
```go
type Email struct {
    EmailID   pgtype.Text          // TEXT PK
    Content   pgtype.JSONB         // JSONB
    EmailRecipients pgtype.TextArray  // TEXT[]
    CreatedAt pgtype.Timestamptz
    DeletedAt pgtype.Timestamptz   // soft delete
}
func (*Email) TableName() string { return "emails" }
func (e *Email) FieldMap() (fields []string, values []interface{}) { ... }
```

## Writing queries (hand-written SQL)

Values ALWAYS go through `$1,$2,…`; `fmt.Sprintf` only for structural parts (table name,
joined field list). Enforced by `.claude/rules/security.md`.

- **SELECT** (`spike/.../email.go:196`):
  ```go
  query := fmt.Sprintf(`SELECT %s FROM emails e WHERE e.email_id = ANY($1) AND e.deleted_at IS NULL;`, fields)
  database.Select(ctx, db, query, database.TextArray(emailIDs)).ScanAll(&emails)
  ```
- **UPSERT** (`spike/.../email.go:37`):
  ```sql
  INSERT INTO emails as e (...) VALUES (...)
  ON CONFLICT ON CONSTRAINT pk__emails
  DO UPDATE SET sg_message_id = EXCLUDED.sg_message_id, ... WHERE e.deleted_at IS NULL;
  ```
- **Dynamic filter builders**: `notification/repositories/info_notification_sql_builder.go:18`
  (grows `whereQuery` + `*argsQuery`, JSONB path filters `target_groups->'…'->>'type'`);
  conversationmgmt `support_conversation.go` builder + `SELECT COUNT(*) FROM (…) tmp_count`.
- ⚠️ pgx **v5** files use `pgx.NamedArgs` with `@name` placeholders (announcement
  `query_builder.go:27`) — a v5-only API; don't mix v4/v5 `Tx`/`Row` across a boundary.

## Migrations

`golang-migrate/migrate/v4` + pgx driver (`internal/golibs/database/migrate.go`). **Up-only**
(`m.Up()` only, no `.down.sql`). Files `migrations/<db>/<NNNN>_migrate.up.sql`. Run locally:
`./local/run.bash start -m migration -s <service>`.

CREATE TABLE example — `migrations/notificationmgmt/1004_migrate.up.sql` (the `emails`
table): `email_id text`, `content jsonb`, `email_recipients text[]`,
`created_at/updated_at timestamptz DEFAULT (now() at time zone 'utc')`, `deleted_at`,
`resource_path text NOT NULL DEFAULT autofillresourcepath()`,
`CONSTRAINT pk__emails PRIMARY KEY (email_id)`, `CHECK (status = ANY(ARRAY[...]))`.

Real index definitions:
| Kind | Example | File |
|---|---|---|
| Composite B-tree | `(email_id, recipient_address, recipient_type)` | `notificationmgmt/1026_migrate.up.sql:1` |
| GIN trigram (text search) | `USING gin (name gin_trgm_ops)` | `tom/1064_migrate.up.sql:17` |
| GIN on expression | `gin (nospace(full_name_phonetic) gin_trgm_ops)` | `tom/1059_migrate.up.sql:40` |
| GIN on array | `gin (inferred_location_ids)` | `bob/1706_migrate.up.sql:1` |
| B-tree on resource_path | `btree (resource_path)` | `tom/1059_migrate.up.sql:43` |
| Unique constraint | `conversations__student_id__coach_id UNIQUE (student_id, coach_id)` | `tom/1001_migrate.up.sql:238` |
Created `CONCURRENTLY IF NOT EXISTS` to avoid write locks. ⚠️ No partial (`WHERE`) indexes
found in tom/notificationmgmt.

## Row-Level Security & multi-tenancy (the central pattern)

Every tenant table has `resource_path text NOT NULL DEFAULT autofillresourcepath()` + RLS.

RLS functions (`migrations/auth/1001_migrate.up.sql`):
```sql
autofillresourcepath()  -- returns current_setting('permission.resource_path', 't')
permission_check(rp, tbl) -- returns (rp = current_setting('permission.resource_path'))::bool
```
Per-table policy (e.g. `migrations/tom/1050_migrate.up.sql:16`): a permissive
`CREATE POLICY … USING(permission_check(...)) WITH CHECK(...)`, an
`AS RESTRICTIVE TO public` twin, then `ENABLE` + `FORCE ROW LEVEL security`.

**How the tenant reaches the DB (trace):**
1. gRPC auth interceptor puts claims on ctx (`interceptors.ContextWithJWTClaims`); never
   read `resource_path` from the request.
2. Pool **`BeforeAcquire` hook** `setPostgres` (`pool_gcp.go:161`) runs on **every** checkout
   and executes `SELECT set_config('permission.resource_path', $1, false), set_config('permission.user_group', …), set_config('app.user_id', …)`, verifying the echo (rejects the conn on mismatch). `false` = session-level (re-set each acquire, not `SET LOCAL`).
3. A `tx` from that conn inherits the session's `permission.resource_path`, so
   `permission_check()` filters every row. Works for both v4 and v5 pools.

The `query-db` skill mirrors this manually with `SET permission.resource_path='…'`.

## Query performance

⚠️ **No `EXPLAIN`/`EXPLAIN ANALYZE` in application code** — not a codified practice here.
Slow-query investigation goes through the `/query-db` skill (read-only, IAM, RLS set for
you); endpoint latency/activity logs live in the **`zeus`** DB. No pgbouncer / read-replica
in the `database` layer. Every repo method is wrapped in `interceptors.StartSpan` (OTel), so
query timing shows per-repo-method in traces. DB primer:
`docs/tech-stack-learning/03-data-persistence/README.md`.

## Hasura (a second read path)

Hasura runs alongside the Go services against the **same** DBs, as a GraphQL **read** path
for frontends (writes stay in Go). It does **not** use Postgres RLS — it re-implements the
tenant filter in metadata: `resource_path: { _eq: X-Hasura-Resource-Path }` + `deleted_at
_is_null: true` per role
(`deployments/helm/backend/notificationmgmt/files/hasura/metadata/tables.yaml`). Add tables
via the `hasura-track-table` skill.

## Testing

Primary = unit tests with `mock_database.Ext`/`Tx`/`BatchResults` + generated repo mocks
(spike `email_test.go`). BDD real-DB tests under `features/`. Local stack:
`localhost:5432`, `postgres`/`example`, container `local-db-1`, `psql -U postgres -d <db>`.

## Gotchas worth teaching

- **Soft deletes everywhere** — reads filter `deleted_at IS NULL`; writes set it, never hard
  delete (even upserts guard it).
- **Timestamps set in Go** (`.Set(now)`) despite DDL `DEFAULT now() at time zone 'utc'`;
  bulk helpers pass literal `"NOW()"`.
- **ULID `TEXT` primary keys** (`idutil.ULIDNow()`), named `pk__<table>`.
- **`ON CONFLICT` upserts**; `TEXT[]` queried with `= ANY($1)` / `&&` overlap; **JSONB**
  with `->`/`->>` and `jsonb_each`.
- **Advisory locks** (`pg_try_advisory_lock(hashtext($1))`,
  `database/pg_advisory_lock.go`); CTEs / `array_agg` / ltree-ish `access_path ~~`.
- **`CHECK (col = ANY(ARRAY[...]))` as enums**; the pgx **v4↔v5 split**; the notification
  **wrong-DB gotcha** (bob vs notificationmgmt).