Weekly activity report from the read replica
Go · Go · beginner · modification
Adds the weekly activity report behind the project page's activity widget: `GET /api/projects/{id}/reports/weekly` returns how many of the project's tasks were created and how many were completed in the last seven days. Report queries go to the read replica, configured by a new required `REPLICA_DATABASE_URL`, so the report query never runs on the primary. Report queries reach the replica through the existing store layer, with the same pool settings as the primary (at most 20 open connections, a ping on open). The window is a rolling 7 x 24 hours ending now, in UTC, with the clock injected so it can be pinned in tests. Membership is checked by the same `auth.RequireMember` as the task list.
Go 1.22 service, standard library only (`net/http` method-pattern `ServeMux`, `log/slog`, `database/sql` with the pgx driver). `store.Open(url)` (existing, unchanged) opens a `*sql.DB` with `sql.Open`, caps it at 20 open and 5 idle connections, and pings once with a 5-second timeout; its errors never contain the URL. `auth.RequireMember` (existing, unchanged) answers 401 or 403 itself unless the signed-in user is a member of the project in the path, and passes a path id that is not a positive integer through to the handler, which answers 400; it checks membership on the primary. The `tasks` table has `created_at timestamptz NOT NULL` and `completed_at timestamptz NULL` (set when a task is marked done, cleared when it is reopened), an index on `project_id`, at most 500 rows per project, and rows are never deleted. The replica is a PostgreSQL 16 streaming replica in another zone; opening a connection to it (TCP, TLS, authentication) takes about 30 ms. The project page's activity widget calls this endpoint on every page open; at the Monday-morning peak one instance serves up to 60 of these requests at once. `httpserver.Serve(ctx, addr, h)` (unchanged) sets the server timeouts and drains in-flight requests on shutdown.
Requirements
- `GET /api/projects/{id}/reports/weekly` (new) returns the project's activity over the last seven days as JSON `{"from", "to", "created", "completed"}`: `to` is the current time in UTC from the injected clock (`Handler.Now`, `time.Now` in production), `from` is exactly 7 x 24 hours earlier, `created` counts the project's tasks whose `created_at` falls in `[from, to)`, and `completed` counts those whose `completed_at` falls in `[from, to)`. `from` and `to` encode as RFC 3339 timestamps in UTC.
- The report's query runs on the read replica, never on the primary. The replica's URL comes from a new `REPLICA_DATABASE_URL` variable, required at startup like `DATABASE_URL`. A replica lag of a few seconds is acceptable for this report.
- The replica's connection budget is binding: it accepts at most 100 connections, shared by the four API instances, so each instance keeps at most 20 connections open to the replica (the cap `store.Open` sets on a pool). When all 20 are busy, a report waits for one rather than opening another.
- Same access and errors as the task list: the route is behind `auth.RequireMember`; a path id that is not a positive integer answers 400; a database error is logged and answers 500 `internal error`.
Files touched
- cmd/api/main.go
- internal/config/config.go
- internal/reports/handler.go
--- cmd/api/main.go
"os/signal"
"syscall"
+ "time"
"example.com/taskboard/internal/auth"
"example.com/taskboard/internal/config"
"example.com/taskboard/internal/httpserver"
+ "example.com/taskboard/internal/reports"
"example.com/taskboard/internal/store"
"example.com/taskboard/internal/tasks"
th := &tasks.Handler{Store: store.NewTaskStore(db), Log: log}
+ rh := &reports.Handler{ReplicaURL: cfg.ReplicaDatabaseURL, Log: log, Now: time.Now}
mux := http.NewServeMux()
mux.Handle("GET /api/projects/{id}/tasks", auth.RequireMember(db, http.HandlerFunc(th.List)))
+ mux.Handle("GET /api/projects/{id}/reports/weekly", auth.RequireMember(db, http.HandlerFunc(rh.Weekly)))
return httpserver.Serve(ctx, cfg.Addr, mux)
--- internal/config/config.go
// Config is the API's settings, read from the environment at startup.
type Config struct {
- Addr string
- DatabaseURL string
+ Addr string
+ DatabaseURL string
+ ReplicaDatabaseURL string
}