Investigation API: Understanding the Shared Foundation Before Implementing Endpoints

This guide is for contributors implementing Investigation API endpoints. It explains the shared foundation introduced in PR #263 for issue #196, why it exists, and how its different parts work together.

If you haven't read Watchdog Investigation History: What We're Building and How to Contribute, start there.

1. Why we have a shared foundation

The Investigation API has 41 endpoints, and multiple contributors are implementing them.

Although each endpoint returns different data, they all need to handle many of the same things:

• Authentication and request validation.
• Reading and validating query parameters.
• Querying PostgreSQL.
• Handling database errors.
• Returning consistent JSON representations.
• Pagination and including related resources.
• Caching and HTTP response headers.

If every endpoint implemented these independently, we would end up with different conventions across the API. For example, one endpoint might return 500 for a missing resource while another returns 404, or different endpoints might format timestamps differently.

This would also make OBACloud more complicated because it would need to understand the differences between endpoints.

The foundation puts these shared responsibilities in internal/investigation. This lets contributors focus on what makes their endpoint different: the data it retrieves and the response it returns.

2. How the foundation is organized

There are two types of shared functionality.

Automatic request handling

When an endpoint is registered through s.get, it automatically receives the common request checks:

• Metrics collection.
• Token authentication.
• Database availability checks.
• Accept header negotiation.

The endpoint handler does not need to implement these checks.

Helpers used by endpoint handlers

Other responsibilities are handled by reusable functions and service methods.

For example, handlers use shared helpers to:

• Read and validate parameters.
• Execute database reads.
• Handle errors.
• Write JSON responses.
• Paginate results.
• Load included resources.

The important distinction is that contributors should reuse these helpers instead of implementing the same rules again.

3. What happens when a request arrives?

The following diagram shows how a request moves through the foundation and the endpoint handler.

flowchart TD
    A["HTTP request"] --> B["Metrics: count and time the request, including rejected ones"]
    B --> C{"Token valid?"}
    C -->|No| C1["401 Unauthorized"]
    C -->|Yes| D{"Database on?"}
    D -->|No| D1["503 Investigation storage disabled"]
    D -->|Yes| E{"Accepts JSON?"}
    E -->|No| E1["406 Not Acceptable"]
    E -->|Yes| F["Endpoint handler"]

    F --> G["Read and validate the path ID and query parameters"]
    G -->|"Malformed path ID"| G1["404 Not Found"]
    G -->|"Invalid parameters"| G2["400 listing every parameter problem"]
    G -->|"Valid request"| J["s.read starts one read-only transaction with a 5-second timeout"]

    subgraph TX["Inside the same transaction"]
        K["Run the endpoint's queries"] --> L["Load included resources, if requested"]
    end

    J --> K
    K -->|Error| N["s.fail: 404, 503, 499 or 500"]
    L -->|Error| N
    L --> O["s.writeJSON: encode the body, compute the ETag, set cache headers"]
    O --> P{"If-None-Match matches?"}
    P -->|Yes| Q["304 Not Modified, no body"]
    P -->|No| R["200 with the JSON body"]

Loading

The first four checks are handled automatically by s.get.

Everything after the handler receives the request is the endpoint's responsibility, using the shared helpers.

4. How the endpoint components fit together

An endpoint is not just a handler. It is a group of components with separate responsibilities.

Component	Responsibility
SQL queries	Retrieve the required data from PostgreSQL.
Representation	Define the structure returned to API clients.
Representation builder	Convert database results into that structure.
Handler	Coordinate parameter validation, queries, and response creation.
Loader	Retrieve a resource when another endpoint wants to include it.
Route registration	Expose the endpoint through the shared request pipeline.

A useful way to think about the relationship is:

flowchart TD
    subgraph REG["registerRoutes"]
        R1["s.get registers the route"]
        R2["s.registerLoader registers the loader"]
    end

    R1 --> H["Handler: one resource for a GET request"]
    R2 --> L["Loader: many resources in one call"]
    X["Another endpoint's s.loadIncluded"] -->|"Distinct IDs"| L

    H --> Q1["SQL query for one ID"]
    L --> Q2["SQL query for many IDs"]

    Q1 --> B["Shared representation builder"]
    Q2 --> B

    B -->|"Response body"| W["s.writeJSON"]
    B -->|"Entries keyed by URL"| I["The other endpoint's included object"]

Loading

Notice that the handler and loader share the same representation builder. This is important: a resource should have the same representation whether a client requests it directly or receives it through include.

5. The main implementation patterns

Most endpoints follow one of two patterns: returning a single resource or returning a collection of resources.

A. Single-resource endpoint

Consider GET /v1/oba-metrics-responses/{response_id} (issue #234).

This endpoint returns one OBA metrics response. Other resources, such as unmatched-stop episodes, can link to it through opening_response or closing_response.

Its implementation follows this flow:

1. Retrieve the resource. The SQL query finds the requested response using its ID. A second query retrieves multiple responses, which is needed by the loader.
2. Build its representation. The representation builder converts the database row into the API response, including its fields, timestamps, and links.
3. Handle the request. The handler validates the path ID, checks that no unsupported query parameters were supplied, retrieves the row, and passes the result to the shared response writer.
4. Register a loader. The loader uses the same representation builder but accepts multiple IDs, allowing other endpoints to include these responses efficiently.
5. Register the route and loader. The route makes the endpoint available, while the loader makes its resource available to other endpoints.

The foundation handles the common request checks, database transaction, error mapping, and response headers. The endpoint supplies the resource-specific behavior.

B. Paginated collection endpoint

Consider GET /v1/unmatched-stop-episodes (issue #228).

This endpoint is more involved because it supports filters, time windows, pagination, and included resources. It reads from the episode view introduced in issue #198.

Its flow is:

1. Validate the request. Read the supported filters, time windows, pagination parameters, and requested includes.
2. Retrieve the page. The SQL query applies the filters and returns the newest matching records first.
3. Build the representations. Convert each database row into the API representation and construct its links.
4. Build pagination links. Determine whether another page exists and provide a next link when necessary.
5. Load included resources. If the client requested include, collect the related resources and load them through the shared include mechanism.
6. Write the response. Return the page, links, and included resources using the shared response writer.

The following sections explain the patterns involved in more detail.

6. Parameter validation

The foundation uses a shared validation approach: collect all parameter problems first, then return one response.

For example, imagine a request contains an invalid agency_id and an invalid page_size.

Instead of returning an error for the first problem and making the client send another request to discover the second, the validation helpers collect both problems and return them together in one 400 response.

This is the responsibility of parseQuery, the param* helpers, and invalidParams.

Some important rules are already built in:

• parseQuery rejects unknown parameter names and parameters supplied more than once. A typo such as agnecy_id must not silently produce an unfiltered result.
• Each param* helper returns a pointer. An absent parameter becomes nil, which works directly with nullable sqlc query parameters.
• An explicitly empty value, such as ?agency_id=, is invalid rather than absent.
• IDs must use their canonical form. For example, 42 is valid, while +42 and 042 are not.
• paramTime requires an explicit timezone or Z, so a timestamp has an unambiguous meaning.
• notFuture rejects future timestamps for parameters where asking about the future would not make sense.

Path IDs are handled separately through pathID. A malformed path ID returns 404, because the URL does not identify a valid resource.

What this means for contributors: declare every supported query parameter, use the shared parsing helpers, and check the collected errors before executing queries.

7. Time windows

Many collection endpoints support time filters. Their rules can differ: some require a window, some have a default, and some have a maximum duration.

Instead of implementing these rules separately, the foundation provides window constructors. The endpoint selects the appropriate constructor, and parseWindow applies the corresponding rules.

Window constructor	Used by	Behavior
activeWindowOptions(statusOpen, replaced)	Bounded episode lists	Defaults to the last 24 hours in applicable cases; maximum 31 days.
openedWindowOptions()	opened_from and opened_to on episode lists	No default; maximum 31 days.
unboundedWindowOptions(from, to)	Individual stop, trip, or vehicle episode lists, and static GTFS versions	No default and no maximum.
countsWindowOptions()	Episode counts	A window is required; maximum 31 days.

All window types enforce the same basic rules:

• Both ends must be provided, or neither.
• The start must be before the end.

When no window is supplied, the resulting From and To values are both nil.

Some defaults depend on the current time. The service therefore has a replaceable clock, s.now. Use it instead of calling time.Now() directly, so tests can control the current time.

8. Database reads with s.read

All database reads for one API request should happen inside one s.read call.

It runs the queries inside a REPEATABLE READ READ ONLY transaction with a five-second timeout.

This gives us three guarantees.

One consistent snapshot

Watchdog continues collecting data while API requests are being handled. Without a shared snapshot, two queries in the same response could see different database states.

For example, an episode might appear open in the list query but closed in the query that loads its related data.

The transaction ensures all queries in the request see the same snapshot.

Read-only access

PostgreSQL prevents writes inside this transaction. An endpoint bug therefore cannot accidentally modify investigation history.

A query timeout

The transaction uses a five-second timeout. The context passed to the transaction callback carries this timeout, so every query must use that context.

The important rule is that the endpoint's queries and its included-resource queries must all run inside the same s.read call. Do not query through s.Pool directly.

9. Error handling with s.fail

The foundation also centralizes how database and request errors become HTTP responses.

Instead of writing status codes or reporting errors independently, endpoint handlers return errors to the shared error handler.

Error	HTTP response	Behavior
pgx.ErrNoRows	404	The requested resource does not exist. This can also happen when history has been removed by the 90-day retention policy.
Query timeout	503	Logged and reported to Sentry as a warning. A slow query may indicate a missing index or an unbounded filter.
Client disconnect	499	No response body. This separates client disconnects from server errors in metrics.
Any other error	500	Logged and reported to Sentry. Internal error details are not exposed to the client.

For example, if a single-resource query finds no row, it should return pgx.ErrNoRows. The handler then passes that error to s.fail, which produces the correct response.

This keeps error responses and reporting consistent across endpoints.

10. Writing responses and caching

All successful endpoint responses should go through s.writeJSON.

It handles JSON encoding and the common HTTP headers, including:

• Content-Type and Content-Length.
• ETag.
• Cache headers.

It also supports conditional requests. If a client sends an If-None-Match header containing the current ETag, the server returns 304 Not Modified without sending the response body again.

This is useful for clients such as dashboards that refresh frequently but often receive unchanged data.

The endpoint must choose the appropriate cache policy:

Policy	When to use it	Cache duration
cacheShort()	Data that can change, such as open episodes, lists, clusters, and counts	30 seconds
cacheImmutable(t)	Data that will never change, such as closed episodes, their static contexts and evidence, OBA responses, static versions, rule versions, and bounding boxes	24 hours

A single-episode endpoint should use short caching while the episode is open, then immutable caching once it is closed.

The foundation also removes the no-store and Pragma headers set by the security middleware for these successful responses, and disables HTML escaping so links remain readable.

11. Consistent API representations

The representation is the contract between the API and its clients. It should follow the same conventions across endpoints.

Timestamps and dates

Use apiTime for timestamps and apiDate for calendar dates, such as a trip's service date.

They ensure consistent formatting regardless of the timezone of the original database value:

• Timestamps use UTC with RFC3339Nano.
• Calendar dates use YYYY-MM-DD.

The conversion helpers handle sqlc's database types.

Nullable fields

A field that can be null should remain present in the response.

For example, an open episode should contain:

"closed_at": null

It should not omit the field entirely. Use pointer fields without omitempty so clients can rely on a consistent response structure.

Links

Links are represented as a map of link names to paths.

If a related resource does not exist, leave its link out rather than returning a null link.

For links that carry query parameters, use linkWithQuery. It sorts the parameters so identical filters always produce the same URL.

This matters because included resources are keyed by URL, and the response ETag is based on the response body. Stable URLs help keep both consistent.

12. Pagination

The collection endpoints use cursor-based pagination.

The client does not need to understand how the cursor works. It follows the next link returned by the API.

The process works as follows:

1. parsePage reads page_size and cursor. The default page size is 50, and the allowed range is 1 to 200.
2. The query retrieves records with IDs below the last ID seen by the client, ordered by ID descending.
3. The query fetches one extra row beyond the requested page size. This tells us whether another page exists.
4. trimPage removes that extra row and reports whether more results are available.
5. pageLinks builds the self and, when applicable, next links.

The next link preserves the original filters and changes only the cursor.

Using IDs this way also avoids duplicates when new episodes are created while a client is paging. New records appear at the top of the first page on the next refresh rather than shifting into an already-read page.

13. Including related resources

The include mechanism allows a client to request related resources in the same response.

For example:

GET /v1/unmatched-stop-episodes?include=opening_response,closing_response,static_contexts

Without includes, a client might need to request every episode first, then make additional requests for its related responses and contexts.

With includes, the API loads those resources and returns them alongside the main response.

The important part is that loading is organized by resource type, not by individual link.

How the pieces work together

There are three main responsibilities.

1. The target resource registers a loader

A loader belongs to the endpoint that owns the resource.

For example, the OBA metrics response endpoint registers a loader for the oba_metrics_response kind.

The loader accepts multiple IDs and retrieves their representations in one database query. It must use the same representation builder as the normal GET endpoint.

Some resources represent collections rather than individual records. For example, static_contexts and evidence are loaded using the episode ID, with the loader returning the entire collection for each episode.

2. The linking endpoint declares what can be included

An endpoint declares a map connecting its link names to resource kinds.

For example, the unmatched-stop episode endpoint can declare that:

• opening_response points to an OBA metrics response.
• closing_response points to an OBA metrics response.
• static_contexts points to the static-context collection.

The endpoint passes this map to s.parseInclude, which validates the client's requested include names.

A resource can be declared before its target loader is registered, but requesting it before the loader exists returns 400.

3. s.loadIncluded collects and loads everything

The endpoint provides the links for all items in the response to s.loadIncluded.

The function then:

1. Collects the requested related resources.
2. Removes duplicate URLs.
3. Groups resources by kind.
4. Collects the distinct IDs needed by each loader.
5. Calls each loader once with all its IDs.
6. Builds the included object from the results.

This means that if 50 episodes all refer to OBA metrics responses, the API does not execute 50 separate queries. It calls the OBA metrics response loader once with the distinct IDs.

Similarly, opening_response and closing_response share the same loader because both point to the same resource kind.

The include flow

flowchart TD
    A["Client sends ?include=opening_response,static_contexts"] --> B["s.parseInclude checks each name against the endpoint's includable map"]
    B -->|"Unknown, repeated or empty name, or no loader yet"| B1["400, together with any other parameter problems"]
    B -->|"Valid names"| C

    subgraph TX["Inside the same s.read transaction"]
        C["Handler builds each item and its links"] --> D["s.loadIncluded picks the requested links from every item and skips absent ones"]
        D --> E["Drop repeated URLs"]
        E --> F["Group the distinct IDs by resource kind"]
        F --> G["Call each kind's loader once"]
        G --> H["Build included: URL to representation"]
    end

    H --> I["s.writeJSON returns the items, their links and included"]

Loading

Important include rules

• The included key appears when the client requested include, even if there was nothing to include. In that case it is an empty object.
• If the client did not request include, the key is omitted.
• Includes support one level only. Included resources do not contain their own included resources.
• The representation returned through a loader must match the representation returned by the resource's normal GET endpoint.

The design is described in the Including related resources section of the Investigation API design.

14. Testing an endpoint

Tests belong next to the endpoint code in package investigation, which allows them to call unexported handlers and helpers.

Database tests

dbtest.NewMigratedPool(t) creates a fresh, migrated database schema for each test and removes it when the test finishes.

Tests require WATCHDOG_TEST_DATABASE_URL and are skipped when it is not set. CI configures this automatically.

Use the shared database test helpers to insert the rows needed by your test.

To run database tests locally, start PostgreSQL and run:

WATCHDOG_TEST_DATABASE_URL=postgres://watchdog:watchdog@localhost:5432/watchdog_test?sslmode=disable make test-db

Testing handlers

Until issue #197 adds the token check, requests through the router are intentionally rejected with 401.

For now, call the handler directly in tests. After #197 is merged, newTestService(t) and doGet can be used to test through the router with the test token.

Other useful testing helpers

• Set s.now to a fixed time when testing time-window defaults.
• Use report.CaptureSentry(t) to check what was reported to Sentry.
• Test missing resources, malformed path IDs, unknown parameters, invalid values, and pagination through a second page.

Tests should cover both successful requests and the behavior of the shared rules.

15. Before opening your endpoint PR

Use this checklist to verify that the endpoint follows the foundation:

• The route is registered with s.get.
• A loader is registered if other resources need to include this resource.
• Every accepted query parameter is declared in parseQuery.
• Invalid parameters are collected and returned together.
• All database reads, including included-resource loading, happen inside one s.read call and use its context.
• Errors are passed to s.fail.
• Successful responses go through s.writeJSON with the correct cache policy.
• Timestamps and dates use apiTime and apiDate.
• Nullable fields remain present, and links to nonexistent resources are omitted.
• The GET handler and loader use the same representation builder.
• Tests cover the expected behavior and error cases.
• make fmt-check vet sqlc-check test passes.

16. Where to find the implementation

The following files contain the foundation's main components.

File	Responsibility
internal/investigation/routes.go	Route registration and s.get.
internal/investigation/pipeline.go	Request checks and metrics status recording.
internal/investigation/params.go	Query parameter parsing and validation.
internal/investigation/window.go	Time-window parsing and rules.
internal/investigation/read.go	Shared read-only database transaction.
internal/investigation/fail.go	Error-to-response mapping.
internal/investigation/response.go	JSON responses, ETags, and caching.
internal/investigation/types.go	API timestamp and date types.
internal/investigation/links.go	Link construction.
internal/investigation/page.go	Cursor pagination.
internal/investigation/include.go	Resource loaders and include handling.
internal/investigation/problem.go	Problem JSON error responses.
internal/db/dbtest/dbtest.go	Database testing helpers.

Each file and function has comments explaining its responsibility. If this guide disagrees with the implementation, trust the code and let us know so the guide can be corrected.

Further reading

• Investigation API design: API conventions, endpoint specifications, and implementation decisions.
• Foundation issue #196: the shared foundation requirements.
• Foundation PR #263: the actual implementation and tests.
• Historical investigation schema: the database tables and views used by endpoint queries.