# sqlc + Atlas Migration — Analysis & Implementation Guide

Status: **COMPLETE** — Phases 0-6 verified on 2026-08-17.
Written 2026-08-17 as the working context for the sqlc/Atlas migration of the Go ERP backend.

## 1. Objective & decisions

Goal: migrate the Go backend (repo `github.com/TaruLab/keluargamart-backend`) to
**sqlc-generated persistence** + **Atlas-managed migrations**, replacing the 13
hand-written `internal/*/repository.go` files (raw SQL) with generated queries
end-to-end.

Confirmed user decisions (Q&A):
1. **Full migration**: both the 13 repositories AND the golang-migrate `migrations/`.
2. **Full replacement** of module models with sqlc-generated types — the Store
   interfaces, services, handlers, AND test fakes all move to `db.*` types
   (not just the repository layer).
3. Dates: the `time.Time` switch was deferred. DATE columns are kept as strings
   via `::text` casts in sqlc queries (e.g. `invoice_date::text`) to preserve the
   Laravel-parity API. TIMESTAMPTZ columns generate as `pgtype.Timestamptz`.
   DATE/TIMESTAMPTZ → `pgtype.Date`/`pgtype.Timestamptz` also marshal to clean
   JSON (see §4), so handlers can return `db.*` rows directly.
4. Re-baseline from existing SQL (Atlas import of the old migrations).

## 2. Toolchain & environment facts

- OS: Windows, shell `pwsh`. Docker NOT available.
- PostgreSQL 18 local: psql/createdb at `C:\Program Files\PostgreSQL\18\bin\`,
  DB `erp`, user/pass `erp`/`erp` (URL `postgres://erp:erp@localhost:5432/erp?sslmode=disable`).
- `sqlc v1.31.1` and `atlas v1.3.1-canary` at `C:\Users\Shima\go\bin\`.
- Server binary: `bin/server.exe` at repo root; base `http://localhost:8080/api/v1`.
- Buyer seed: `buyer@keluargamart.local` / `Buyer@1234`, user id **36**.
- `erp` role has **no CREATEDB**; `postgres` superuser password unknown/forgotten
  (user: "aku lupa passwordnya"). Common passwords tried → failed. Shell is not
  admin (`isAdmin: False`), pg_hba is all `scram-sha-256`. So the `erp_dev` dev
  DB (needed for `atlas migrate diff`) **cannot be created right now** — deferred.
- `gofmt -l` flags untouched files for pre-existing CRLF; modified files are
  gofmt-clean; git normalizes on commit.
- `bin/migrate.exe` is a **precompiled binary with no source in this repo**
  (cmd/ contains only cmd/server/main.go).

## 3. What is DONE (verified working)

### Phase 0 — clean Atlas state
- `DELETE FROM atlas_schema_revisions;` → removed stale row.

### Phase 1 — migrations converted to Atlas
- `atlas migrate import` from golang-migrate produced Atlas-format single-file
  migrations. The old format had **two `000002_*` files** (duplicate version);
  Atlas rejects duplicates, so they were renumbered:
  - `000001_init_schema.sql`
  - `000002_add_cart.sql`
  - `000003_payment_methods_qris_kartu.sql`  (was `000002_payment_methods_qris_kartu`)
  - `000004_add_ecommerce.sql`               (was `000002_add_ecommerce`)
  - `atlas.sum`
- Old `.up.sql`/`.down.sql` files removed (backed up to
  `C:\Users\Shima\AppData\Local\Temp\opencode\golang-migrate-backup`).
  `migrations/README.md` kept.
- Live DB baselined: `atlas migrate apply --dir file://E:/Website/b-keluargamart/migrations
  --url postgres://erp:erp@localhost:5432/erp?sslmode=disable --baseline 000004`
  → "No migration files to execute", Executed Files: 1, Pending: 0.
  **Baseline version must be the numeric prefix only (`000004`)** — passing
  `000004_add_ecommerce` fails with "baseline version ... not found".
- `atlas.hcl` created (env `local`): src `file://migrations`, dev
  `postgres://erp:erp@localhost:5432/erp_dev?sslmode=disable` (placeholder —
  DB does not exist yet), url = live `erp` DB.

### Phase 2 — sqlc schema + queries authored
- `db/schema.sql` generated via:
  `atlas schema inspect --url postgres://erp:erp@localhost:5432/erp?sslmode=disable
   --schema public --exclude "*.atlas_schema_revisions" --format '{{ sql . }}'`
  (821 lines, 44 tables; exclude pattern MUST be `*.atlas_schema_revisions`).
- `sqlc.yaml` (v2, engine postgresql): schema `db/schema.sql`, queries `db/query`,
  out `internal/db`, package `db`, `sql_package: pgx/v5`, `emit_json_tags`,
  `emit_interface`; overrides: `pg_catalog.numeric` → `shopspring.decimal.Decimal`,
  nullable numeric → `decimal.NullDecimal`.
- Query files authored: `db/query/{users,warehouses,cart,catalog,partners,hr,
  inventory,finance,purchasing,sales,salestracking,reports,system}.sql`
  (12 files covering all 13 modules; users.sql covers auth+system users;
  system.sql covers roles+activity_logs). ~200 generated methods.

### Phase 3 — sqlc generate (green)
- `sqlc generate` exits 0. `internal/db/` contains 16 files:
  `db.go`, `models.go`, `querier.go`, and 13 `*.sql.go`.
- `go build ./internal/db/` passes.

### Phase 4 — repository and transaction rewiring (green)
- All domain repositories now call generated `internal/db` queries. No raw SQL
  remains in `internal/*/repository.go`.
- Transactional Store seams use `db.DBTX`; services build generated params and
  convert generated rows to stable API DTOs.
- Finance, purchasing, sales, and inventory transaction fakes and the shared
  parity harness use the same generated contracts.
- `cmd/server/main.go` POS ledger wiring derives queries with `db.New(q)` and no
  longer asserts a module-local query interface.
- The pgx simple-protocol workaround was removed; default extended protocol is
  verified against the local PostgreSQL database.

### Phase 5 — verification (green)
- `go build ./...`, `go vet ./...`, and `go test ./...` pass.
- Real-database smoke test: `/health` is `ok`, `/ready` reports database
  `connected`, and admin login succeeds. Readiness is degraded without Redis,
  which is expected for the local smoke environment.

### Phase 6 — source, docs, and CI (green)
- `db/schema.sql` and all `db/query/*.sql` files are versioned source files.
- CI validates Atlas checksums, regenerates sqlc output and rejects drift before
  build, vet, and tests.
- README and deployment docs use Atlas commands. The orphaned `bin/migrate.exe`
  is not tracked and is no longer part of the workflow.

## 4. CRITICAL FINDING: JSON contract survives pgtype

Empirically verified: `pgtype.*` value types implement `MarshalJSON` cleanly,
so handlers CAN return `db.*` rows directly without DTO conversion:

```
Int8 valid   -> 5           Int8 null  -> null
Text valid   -> "abc"       Text null  -> null
Date valid   -> "2026-08-17" Date null -> null
Timestamptz  -> "2026-08-17T01:20:03+07:00"
Int8 ptr     -> 5
```

This means the Laravel-parity JSON contract (numbers, nulls, `YYYY-MM-DD` dates)
is preserved when returning sqlc models/rows. `decimal.Decimal` marshals as a
number. This is the key enabler for "full replacement".

## 5. Generated API surface (internal/db/querier.go, 235 lines)

Generated `Querier` interface has ~200 methods across all modules. For the
per-module mapping, group the generated methods by module (name prefixes are
largely self-describing). **Notable quirks:**

- SUM aggregates return `interface{}`: `SumDebts`, `SumMonthPurchase`,
  `SumMonthSales`, `SumPayablesBalance`, `SumReceivables`,
  `SumReceivablesBalance` → cast with `COALESCE(x,0)::numeric(15,2)` at the repo
  layer.
- Param names `dollar_1`/`dollar_2` in `CountUsers`, `CountWarehouses` (sqlc
  couldn't infer a name for the `$1` search param; defined as `interface{}`).
- Filters use `pgtype.Text`/`pgtype.Int8`/`pgtype.Bool` optional args
  (`sqlc.narg(...)`) — pass `pgtype.X{}` for NULL.
- `Create*` methods returning IDs only (`CreateWarehouse`, `CreateCustomer`, ...)
  vs `*Row` types that RETURNING computed columns (`CreateSalesOrderRow`,
  `CreateInvoiceRow`, `CreateProductRow`, `CreateMovementRow`,
  `CreateTransferRow`, `CreateUserRow`, `CreateGoodsReceiptRow`,
  `CreatePurchaseOrderRow`, `CreateEmployeeRow`).
- Generated row types are module-specific (`GetSalesOrderByIDRow`,
  `ListStockLinesRow`, ...) — richer than the base models (include JOINed columns
  like `product_code`, `customer_name`).
- `GetSalesStockRow`/`GetStockRowForUpdate`/`EnsureStockRow` back the stock
  tx methods (`FOR UPDATE` lock variant exists for `LockStockRow`).

## 6. Module contracts → rewrite mapping

### 6.1 Repo pattern (unchanged seam): `Querier`/`Tx` per module

Modules with transaction flows define their own:
```go
type Querier interface { QueryRow(ctx, sql, args...) pgx.Row; Exec(ctx, sql, args...) (pgconn.CommandTag, error) }
type Tx interface { Querier; Commit(ctx) error; Rollback(ctx) error }
```
Implemented by `*pgxpool.Pool` and `pgx.Tx`. Repository `Begin(ctx) (Tx, error)`
returns `r.pool.Begin(ctx)`. **These module-local Querier/Tx are REPLACED by
`db.DBTX` / `pgx.Tx`** under full replacement. Store methods that take `q Querier`
become `q db.DBTX` (or drop q entirely when the repo holds the pool).

### 6.2 Per-module Store interface digest

| Module | Store methods (key) | Model types | tx methods | Query mapping |
|---|---|---|---|---|
| **auth** (8) | UserByEmail/ByID, TokenByHash, InsertToken, TouchLastUsed, DeleteToken, UpdateLastLogin, UpdateProfile | User, Token | none | users.sql + system.sql (tokens=personal_access_tokens) |
| **cart** (7) | CartByUser/GetOrCreate, Item ops, Clear, Add, UpdateQty, Remove, List | Cart, Item, ProductInfo | none | cart.sql |
| **catalog** (17) | Category/Unit CRUD, ListCategories/Units, NameExists, Product CRUD+List, ProductByCode, CountDeletedAndActive | Category, Unit, Product | none | catalog.sql |
| **partners** (12) | Supplier CRUD+List+ByCode, Customer CRUD+List+ByCode | Supplier, Customer | none | partners.sql |
| **warehouses** (5) | List, ByID, Create, Update, Delete | db.Warehouse (now) | none | warehouses.sql |
| **sales** (~23) | Begin; Order CRUD+List+ByID, Item ops, SetOrderStatus, Reserve/Release/AdjustStock, CreateInvoice/Payment/Cashflow, lookups, CheckStockAvailable, CustomerByUserID, CreateCustomerForUser | SalesOrder, SOItem, Invoice, Payment, Cashflow, Stock | yes | sales.sql |
| **finance** (13) | Begin; Invoice CRUD+List+ByID, SOByID, SetSOStatus, ListReceivables, Payment ops, Cashflow CRUD+List+Summary | Invoice, SOHeader, ReceivableRow, Payment, Cashflow, CashflowSummary | yes | finance.sql |
| **purchasing** (~25) | Begin; PO CRUD+List+ByID, Item ops, AddReceived, GR CRUD+Items+List, RawStockAdd, ListDebts, Voucher ops, CreateCashflow, lookups | PurchaseOrder, POItem, GoodsReceipt, GRItem, DebtRow, Voucher, Cashflow | yes | purchasing.sql |
| **inventory** (~20) | Begin; StockRow/Lock/Upsert/Update, CreateMovement, ListStock/Movements, Product/Warehouse Exists, Opname CRUD+Items+Physical+Status, Transfer CRUD+Items | Stock, Movement, StockLine, Opname, OpnameItem, Transfer, TransferItem | yes | inventory.sql |
| **hr** (~24) | Employee CRUD+List+ByID+ByUserID+Active, Attendance ops, Leave ops, Payroll ops, Performance ops | Employee, Attendance, Leave, Payroll, Performance | no | hr.sql |
| **salestracking** (9) | ListVisits, VisitByID/Create/Update, CustomerExists, CreateActivity, ListRoutes, CreateRoute, InsertLocations, LatestLocations | SalesVisit, SalesActivity, SalesRoute, SalesLocation | no | salestracking.sql |
| **reports** (10) | MonthSales/Purchase, Receivables/Payables Balance, LowStockCount, RecentActivity, SalesChart, Receivable/Debt/Cashflow Rows | DashboardKpis, SalesChartPoint, RecentActivity, CSVRow | no | reports.sql |
| **system** (16) | users CRUD+List, roles CRUD+List, activity logs List/Clear/Count, SetUserActive, UpdateUser, CreateUser | Role, ActivityLog (+ uses auth.User) | no | system.sql |

### 6.3 main.go injection seams (must keep working)

- `salesSvc.WithPOSLedger(...)` — asserts `q.(pgx.Tx)`, calls
  `salesRepo.StockRow(ctx, pgq, ...)` then `inventorySvc.MovementInTx(ctx, pgq, m)`
  building an `*inventory.Movement`. **Rewrite: pass `*db.Queries` derived from
  the tx** (`db.New(tx)` or `q.WithTx(tx)`), and Movement model becomes `db.StockMovement`.
- `cartSvc.WithOrderPlacer(...)` — delegates to
  `salesSvc.PlaceCheckout(ctx, sales.CheckoutInput{...}, userID, userName, userEmail)`;
  `mapPlacedItems`/`toPlacedOrder` bridge cart↔sales types. **Types change to db.\*.**
- `stSvc.WithEmployeeLookup(...)` — calls `hrRepo.EmployeeByUserID`.
- `reports.NewService(...).WithCache(cacheRedis(rdb), 60s)` — `cacheRedis` returns
  `reports.Cache` from `pkg/cache`.
- `system.ActivityLogMiddleware(sysRepo, ...)` router middleware records
  mutations; skips `/dashboard` + `/sales-tracking/locations`.
- `seqAdapter := &kvSeq{pool}` implements per-module `Seq` interfaces
  (`Sequence(ctx, prefix, date, tx any)`).

## 7. Implementation plan (Phase 4+, per module)

Order suggestion: **warehouses (done-ish) → cart → auth → catalog → partners →
system → reports → hr → salestracking → sales → finance → purchasing → inventory**.
Do smaller read-only modules first to lock the pattern, then the tx-heavy ones.

Per module, in this order:
1. **model.go** — replace structs with `db.*` types in the Store interface; drop
   module-local `Querier`/`Tx` in favor of `db.DBTX`/`pgx.Tx`; keep Input/Filter
   DTO structs. Keep sentinel errors.
2. **repository.go** — implement Store against `db.New(pool)` (pool level) and
   `db.New(tx)` (tx level). Map rows→`db.*` (many methods return generated
   `*Row` types already). Map `ErrNoRows`→module `ErrNotFound`. Cast `interface{}`
   SUMs to decimal. Delete every raw SQL string.
3. **service.go** — construct `db.*` values (params structs); read `db.*` back.
4. **handler.go** — return `db.*` directly (JSON contract holds, §4); update any
   explicit field access.
5. **Tests** — rewrite `fakeStore` implementations to the new `db.*` signatures
   (auth 8, catalog ~15, hr ~25, inventory ~22, purchasing ~30, sales ~30,
   system 16, reports 10+2, plus cart/finance/partners/salestracking).
6. Verify: `go build ./...`, `gofmt -l` on changed files, `go vet ./...`,
   `go test ./internal/<module>/...`.

Transaction handling pattern:
- Repo holds `pool *pgxpool.Pool`; build `base := db.New(pool)`.
- Store methods with `q db.DBTX`: `qx := db.New(q)` inside the method.
- Repository `Begin` stays `r.pool.Begin(ctx)` returning `pgx.Tx` (satisfies DBTX).

Final wiring and verification steps are complete. Future schema changes must add
an Atlas migration, refresh `db/schema.sql`, run `sqlc generate`, and commit both
the query sources and generated Go output.

## 8. Known traps

- `atlas migrate diff` needs the `erp_dev` dev DB (blocked, §2). Until then,
  schema changes go through hand-edited migrations + `atlas schema inspect`
  to refresh `db/schema.sql`, or wait for superuser access.
- `db/query/inventory.sql` count queries previously failed with "could not
  determine data type of parameter" — fixed by using `$1::bigint` casts on the
  sole positional arg of the count variants (list variants keep `$1/$2` LIMIT/OFFSET).
- `sqlc.narg` optional filters: `WHERE (sqlc.narg('search')::text IS NULL OR ...)`
  — pass typed NULL (`pgtype.Text{}`, `pgtype.Int8{}`).
- DATE columns: keep `::text` casts in queries to preserve the string API
  contract (decision §1.3). TIMESTAMPTZ columns stay `pgtype.Timestamptz`.
- `interface{}` SUM returns → cast `COALESCE(sum, 0)::numeric(15,2)` at repo layer.
- Generated `Create*Params` have field names derived from SQL args — check each
  (e.g. `dollar_1`, snake_case→PascalCase).
- Atlas is the documented migration path; the orphaned `bin/migrate.exe` is not
  tracked or required.