Database & Data Model

The primary store is PostgreSQL 16, with Valkey as the cache. Grasshopper (the claim worker) has its own separate Cloudflare D1 (SQLite) database — don't conflate the two.

Migrations — database/migration/

• Engine: Goose v3, wired in database/migration/migration.go. Migration .sql files are embedded into the binary (embed.FS), so they ship with the compiled app.
• Format: YYYYMMDDHHMMSS_description.sql with -- +goose Up / -- +goose Down, statements wrapped in -- +goose StatementBegin/End.
• Count: ~244 migrations, from 20250624132859_add_countries_table.sql to 20260708000001_add_topup_product_unit.sql.
• Both postgres and sqlite dialects supported (chosen by DATABASE env).

go run main.go migrate create <name>   # generate a timestamped stub
go run main.go migrate up               # apply all pending (allows out-of-order team migrations)
go run main.go migrate down             # roll back one
go run main.go migrate status           # applied vs pending
go run main.go migrate fresh            # DROP + CREATE the DB, then up  (destructive)

fresh connects to the postgres maintenance DB, terminates connections, drops and recreates using the PG_* env vars.

Models — database/models/

~97 files. Models are plain Go structs. Fields carry json:"..." tags; db:"..." tags exist on some models (e.g. wallet.go) but are not universal — core models like Order and Product are scanned manually via per-repo getXScanValues() helpers, not tag reflection. Nullable columns use sql.NullString/Int64/Time and types.Decimal/types.NullDecimal. Relations are embedded pointer/slice fields loaded on demand.

enum.go holds domain enums as string types implementing sql.Scanner/driver.Valuer (they validate on read/write). Key state machines:

• OrderStatus: PLACED / PENDING / DELIVERED / PARTIALLY_DELIVERED / CANCELLED / FAILED
• OrderSubStatus (~15): VENDOR_ORDER_PENDING → VENDOR_ORDER_CREATED → VENDOR_VOUCHER_FETCHED, plus VENDOR_ORDER_FAILED, and link states GV_LINK_PENDING/CLAIMED/UNCLAIMED
• RechargeStatus (topups): PENDING / DELIVERED / FAILED / CANCELLED / RECHARGED
• OrderSource: API / SHOPIFY / DASHBOARD

QueryFilters (models.go) is the shared query-shaping struct: SelectFields, SortBy, Relations, Conditions map[string]string, ItemsPerPage, CurrentPage. GenerateHash() produces a deterministic SHA-256 cache key; ApplyDefaults() sets page 1 / 10 per page. FilterOperator constants support eq/gt/in/is_null/contains/….

The core data model

The catalog and order flow, read top to bottom:

Entity relationships (core)

Parallel product families (same shape, keyed off vendors):

• Top-up (20260204100002_create_topup_tables): topup_products → topup_variants → client_topup_variants; variant metadata in topup_variant_fields/_input_fields/_input_options/_regions; orders land in recharges (one per top-up, no quantity).
• eSIM (20260414000001_create_esim_tables): esim_products → esim_variants → client_esim_variants; orders in esim_orders + esim_order_responses.

Other core tables: tenancy — clients (self-referential parent_id hierarchy, plus encrypted Shopify fields), wallets (PREPAID/POSTPAID), ledgers, transactions; reference — countries, currencies, timezones, categories, sub_categories, forex_values; vendor config — vendor_attributes (credentials K/V), vendor_wallet, vendor_catalog, vendor_webhook_log; channels — g2a_credentials, g2a_reservation, g2a_order, g2a_product_mapping, shopify_*.

Seeding — database/seeder/

go run main.go seed all      # every seeder (idempotent via ShouldRun)
go run main.go seed fresh    # reference data + wallets only; PRESERVES clients & vendors
go run main.go seed list     # names
go run main.go seed <name>   # one seeder

~40 seeders. Each implements Name(), Run(), ShouldRun() (idempotency). Registration order matters and is explicit in runner.go (blacklists after vendors+clients, vendor_products after products, orders last). Seeding flushes the cache first (repo reads are cache-first, so stale keys would cause phantom "already exists" skips). Notable seeders: reference (Timezone/Country/Currency/Category/Forex), admin/client (AdminUser/Client/ClientUser/Wallet), products/vendors (Product/Vendor/SEAGM/DTOne/Runa/Neo/Octopus…/VendorProduct), details/orders (ClientProductDetail/Inventory/Order), payouts (Merit/Ledig/Beneficiary/Payout).

Repositories — database/repo/

One Repository struct holds dbClient + cacheClient (from the singleton); per-entity methods live in ~95 files, each with a matching _test.go.

• Query building: Squirrel with $N placeholders; columns/scan targets from getXTableColumns/getXScanValues.
• Multi-tenancy: IsClientContext(ctx) reads types.TenantIDKey; when present, queries auto-append WHERE client_id = ?. Soft-delete aware.
• Caching: cache-first reads via Retrieve/Store(key,val,ttl)/Remove/DeleteByPattern. Keys often derive from QueryFilters.GenerateHash().
• Retry: ExecuteQueryWithRetry with exponential backoff on transient errors.

Testing

Three complementary test doubles (in package-internal *_internal_test.go files, so they can inject fakes):

1. sqlmock — mockDB satisfies database.DatabaseClient over a mock *sql.DB; tests pre-program expected queries and rows. Uses the regex matcher (Squirrel whitespace varies).
2. faultDB — wraps a real client but flips per-op fail flags (failExecuteQuery, failStartTransaction, scanFailMode, …) to cover DB-failure branches integration tests can't reach.
3. cachedCache — a fake cache.Cache to drive cache-hit vs cache-miss branches.

Test helpers (test/testhelpers/): NewTestDB(t), ContextWithClient(clientID) (sets tenant), SkipIfShort(t), RequirePostgres(t) (skips without Postgres; resets sequences once/run), plus mocky.go/mockychaos/ for external-service mocking and factory.go/seeddata.go for fixtures.

go test ./database/repo                    # repository tests
go test ./database/repo -run TestOrderRepository
go test ./database/repo -short             # skip long integration tests
go test ./... -race                        # race detector
./automation/scripts/run-go-tests.sh       # interactive runner (option 1 = pre-deploy check)

Integration isolation = transaction rollback: tests run inside a transaction with defer tx.Rollback(), leaving the DB clean and making tests order-independent.

Postgres replication

A physical read-replica runs on a separate machine (deploy/replica/docker-compose.yaml, container pg_replica). The primary's docker-compose.yaml is WAL-tuned (wal_level=replica, max_wal_senders=10, slot replica_slot, hot_standby=on). scripts/postgres/init/02-create-replication-user.sh creates the replicator role + slot on the primary's first boot; scripts/postgres/bootstrap-replica.sh runs pg_basebackup on the replica. Verify with SELECT * FROM pg_stat_replication; (primary) and SELECT pg_is_in_recovery(); (replica). The app can point reads at the replica via PG_READ_* env.