Give each endpoint an owner
In this reference design, I expose selected datasets through an application API. Consumers need stable fields and predictable behavior while storage systems evolve. Each endpoint has a catalog entry naming its owner, field meanings, identifier scope, freshness expectations, supported filters, and response version.
The API accepts structured requests against that contract. It does not accept arbitrary SQL, table names, or caller-supplied connection details. Assume authenticated clients, enforceable dataset permissions, and separate budgets for interactive reads and bulk exports.
Authorize the request and the response
The backend derives identity and tenant from verified credentials. It validates filter fields, operators, value types, sort keys, and requested columns, then applies mandatory row and field policies. Counts and metadata require the same care as returned records.
Recheck current access on every page, job-status request, and download. Cached data never bypasses that check. If policy changes invalidate an export's scope, deny delivery and regenerate an authorized result. Authenticated handling enforces revocation on subsequent download requests. Already delivered copies cannot be recalled; stopping an open stream needs a separate control. Previously issued bearer links would weaken this request-time check.
Keep backend differences visible
An adapter translates the validated request into a parameterized or structured backend operation using scoped credentials. Literal binding prevents values from becoming query syntax; endpoint policy still controls identifiers and operations. Contract checks cover nulls, timestamps, decimal precision, and filtering behavior across adapters.
Bound concurrency, runtime, scanned work where supported, and returned bytes. A small page can still require an expensive scan. Unsupported operations fail explicitly. A timeout requests cancellation and reports uncertainty if remote work cannot be confirmed stopped.
Define what a page continues
Use a documented page limit and deterministic ordering with a unique tie-breaker. An opaque continuation token refers to server-controlled query state, including filters, ordering, and any retained snapshot. Changed arguments require a new query.
A token indicates position, not permission. Stable ordering alone cannot freeze changing data: document whether pagination uses a retained snapshot or may observe intervening updates. Expired state produces a restart requirement. Do not represent a partial page or an unavailable count as proof that the collection is complete.
Make bulk work a durable resource
A validated export request creates a durable queued job before returning an accepted response and a status location. Workers poll and atomically claim queued jobs from that same store. Expired claims can be retried; publishing completion requires the current claim. Store immutable parameters, contract version, caller, and requested source interval. Workers reauthorize before reading and record source versions where available. Label exports that cannot retain a consistent source snapshot.
Write output to a private staging location and publish it only after completeness checks pass. Status distinguishes queued, running, failed, canceled, and completed work. Cancellation does not imply finished files were erased. Downloads have explicit expiry and access checks; notifications contain a protected result link, not the dataset.
Operate the contract, not just the server
Track latency, rejected requests, source failures, queue age, scan cost, expired exports, and cancellation delay by endpoint version. Protect request logs because filters can contain sensitive values. Document deprecation periods and test consumer compatibility before replacing an adapter.
A shared API adds ownership and versioning work. Direct database access may suit a tightly controlled analytical team; a reusable application contract earns its cost when several clients need the same constrained behavior.