List purchase orders with their lines
Go · Go · intermediate · modification
Adds the purchase orders list to the buyer API: newest first, 50 per page with a `before` cursor, every order with its lines in `line_no` order. The page query is keyset-paginated on the existing `(org_id, id)` index, so a deep page costs about the same as the first one, and orders without lines serialise as `[]` rather than `null` so the dashboard does not need a null check. A bad cursor is a 400, database errors are logged and answered with a generic 500.
Go 1.22 procurement service on the standard library (`net/http` method-pattern `ServeMux`, `log/slog`, `database/sql` with the pgx v5 stdlib driver, which passes a Go `[]int64` argument through as a PostgreSQL `bigint[]`). `main` wraps the mux in the auth middleware, so every handler runs for a signed-in org member; `auth.FromContext(ctx)` returns the signed-in `auth.Member` (with `UserID` and `OrgID`) and an `ok` flag. `httpx.WriteJSON(w, status, v)` (existing) sets the JSON content type, writes the status and encodes `v`. Tables (existing): `purchase_orders(id bigserial primary key, org_id bigint not null, number text not null, status text not null, created_at timestamptz not null)` with an index on `(org_id, id)`, and `purchase_order_lines(order_id bigint not null references purchase_orders, line_no int not null, sku text not null, description text not null, quantity bigint not null, unit_price_cents bigint not null, primary key (order_id, line_no))`. An order has 1 to 200 lines once submitted (a draft can have none). The pool's statement timeout is 5 seconds. A page does not need a single snapshot across its queries: the dashboard re-polls every minute.
Requirements
- `GET /api/purchase-orders` returns the caller's org's purchase orders, newest first (by `id`, descending), at most 50 per page, to any signed-in org member. An optional `before` query parameter (a positive integer, normally the previous page's `nextBefore`) restricts the page to orders with an `id` below it; a `before` that is present but empty, not an integer or not positive is 400 `{"error": "invalid_cursor"}`.
- The response is `{"orders": [...], "nextBefore": ...}`. Each order carries `id`, `number`, `status`, `createdAt` (RFC 3339) and `lines`: all of its lines in `line_no` order, each with `sku`, `description`, `quantity` and `unitPriceCents`. An order without lines has `"lines": []`, never `null`.
- `nextBefore` is the `id` of the last order on the page when the page holds 50 orders, and `null` otherwise. A client pages until it gets `null`, so a full last page may be followed by one empty page; that is accepted.
- The endpoint backs the buyer dashboard, which polls it, so a request makes at most two database queries whatever the page holds: one for the page of orders and one for the lines of all of them.
- Database errors answer 500 `{"error": "internal"}` and are logged; no internals reach the client.
Files touched
- cmd/api/routes.go
- internal/purchaseorders/store.go
- internal/purchaseorders/handlers.go
--- cmd/api/routes.go
"example.com/procure/internal/catalog"
+ "example.com/procure/internal/purchaseorders"
"example.com/procure/internal/suppliers"
)
func registerRoutes(mux *http.ServeMux, db *sql.DB) {
catalog.Register(mux, db)
+ purchaseorders.Register(mux, db)
suppliers.Register(mux, db)
}
--- internal/purchaseorders/store.go
+package purchaseorders
+
+import (
+ "context"
+ "database/sql"
+)
+
+type Store struct {
+ DB *sql.DB
+}
+
+// ListPage returns up to limit of the org's orders with an id below before,
+// newest first. Lines are not loaded.
+func (s *Store) ListPage(ctx context.Context, orgID, before int64, limit int) ([]Order, error) {
+ rows, err := s.DB.QueryContext(ctx,
+ `SELECT id, number, status, created_at
+ FROM purchase_orders
+ WHERE org_id = $1 AND id < $2