Database Scope Is a Product Contract

Database scope defines which data an operation may read or change, under whose authority, and within which business and time boundaries.

Consider an endpoint that returns paid orders. Before writing the query, several decisions already exist: which tenant owns the orders, which sources the caller can access, what “paid” means, and which timestamp determines whether an order belongs to the reporting period.

Those decisions form a contract. The API, database queries, cache, and background jobs all need to preserve it. A missing boundary can produce an answer that looks reasonable while describing a different population.

I want that contract to be understandable before anyone opens the query builder. A practical way to get there is to follow one question through the system and make each boundary explicit.

A database or schema can provide a physical boundary. The operation still needs a definition of the data it owns, the actions it allows, and the period it describes. Even a separate database for each tenant cannot decide whether a particular user may read every source within that tenant.

For application design, I break the scope and boundaries of a database operation into four questions:

These boundaries overlap. A reporting service may be allowed to read a replicated order without being allowed to change its payment status. A user may belong to Tenant A while having permission to view only one of its stores.

Consistency is another decision that follows from the question: should a list and its summary describe one snapshot, or may they reflect changes committed between reads? Freshness also needs a definition when the report uses delayed analytical data. We will return to both when following the query path.

Naming these decisions gives the API and database a shared vocabulary. It also makes a review more specific: “the count query lost the source boundary” is an actionable explanation of a defect.

Our example question is:

Return paid orders for Tenant A, from Store 1, paid during the selected UTC day.

The response contains a page of orders, the total matching count, and the sum of their amounts. The summary describes the full matching population, while the list contains one page of it. All orders in this example use TRY, and amounts are stored in minor units. A report spanning currencies would need separate totals or an explicit conversion policy.

Before implementing the endpoint, write down what each input means:

The omitted-source rule deserves attention. Removing a source filter must not turn a user’s request into a query across every source or tenant. It expands the selection only to the actor’s permitted sources within the authenticated tenant.

An explicitly selected source that fails the access check produces the endpoint’s defined access error. An authenticated actor with no readable sources receives an empty report. Missing authentication or a malformed period is an invalid request. These outcomes should remain distinguishable.

The status rule also has a consequence: this report describes orders that are paid when read. If an order is later refunded and its status changes, an old reporting period may change. A historical “payments received” report needs a payment-event or ledger model with its own contract. System boundaries as design explores how ownership decisions shape that model.

The HTTP handler should translate client filters into a validated scope. Authentication establishes the tenant and actor; authorization resolves which sources that actor may read. A syntactically valid UUID is only an identifier, so parsing it cannot replace the access check.

The following TypeScript sketch assumes that parseOptionalSourceId validates an optional UUID and parseUtcPeriod rejects missing, invalid, reversed, or unsupported ranges. The source resolver checks both tenant membership and read permission, rejects a denied explicit selection, and returns permitted source IDs when the selection is omitted.

type PaidOrderScope = {
  readonly tenantId: string
  readonly sourceIds: readonly string[]
  readonly period: {
    readonly start: Date
    readonly endExclusive: Date
  }
  readonly status: "paid"
  readonly currency: "TRY"
}

const sourceIds = await sourceAccess.resolveForRead({
  tenantId: auth.tenantId,
  actorId: auth.actorId,
  requestedSourceId: parseOptionalSourceId(request.query.sourceId),
})

const scope: PaidOrderScope = {
  tenantId: auth.tenantId,
  sourceIds,
  period: parseUtcPeriod(request.query.start, request.query.end),
  status: "paid",
  currency: "TRY",
}

After this step, repositories receive the normalized source list rather than deciding what an absent HTTP filter means. The access decision belongs at a trusted boundary and must cover every entry point. OWASP’s authorization guidance recommends validating permissions on every request.

For one selected source, the SQL is easy to inspect. The parameters represent the authenticated tenant, the authorized source, and the normalized start and end:

SELECT id, amount_minor
FROM orders
WHERE tenant_id = $1
  AND source_id = $2
  AND paid_at >= $3
  AND paid_at < $4
  AND status = 'paid'
  AND currency = 'TRY'
ORDER BY paid_at, id;

For the general case, including an omitted source selection, use the resolved set. Here is a query-construction example for PostgreSQL UUID source IDs:

const where = [
  "tenant_id = $1",
  "source_id = ANY($2::uuid[])",
  "paid_at >= $3",
  "paid_at < $4",
  "status = 'paid'",
  "currency = $5",
].join(" AND ")

const values = [
  scope.tenantId,
  [...scope.sourceIds],
  scope.period.start,
  scope.period.endExclusive,
  scope.currency,
]

const listQuery = {
  text: `SELECT id, amount_minor FROM orders
    WHERE ${where}
    ORDER BY paid_at, id
    LIMIT $6 OFFSET $7`,
  values: [...values, page.limit, page.offset],
}

const summaryQuery = {
  text: `SELECT count(*) AS total,
    coalesce(sum(amount_minor), 0) AS revenue_minor
    FROM orders WHERE ${where}`,
  values,
}

The shared fragment contains application-owned SQL; request values remain parameters. An empty source array matches no records through PostgreSQL’s ANY array comparison. It must never trigger a fallback that removes the source predicate.

Pagination narrows the list without changing the population used by the summary. The ordering includes id as a tie-breaker for equal payment timestamps. This simple offset example assumes stable data while paging; a live dataset needs an explicit pagination consistency policy as well.

Identical filters establish agreement about the population. They do not guarantee that separately executed queries observe the same moment. PostgreSQL’s default Read Committed isolation can give successive statements different snapshots, even inside one transaction. If the response requires a coherent snapshot, execute the reads on the same connection in an appropriately configured transaction, such as Repeatable Read. PostgreSQL transaction isolation documents these guarantees.

That is a product choice with an operational cost. A report may accept small changes between reads; a reconciliation workflow may require a stable snapshot. API design that survives change discusses making such guarantees visible to consumers.

“Orders from yesterday” can refer to several different populations:

An order created on October 6 and paid on October 7 belongs to the October 7 paid-orders report. If an analytical copy arrives on October 8, that arrival does not change the payment day, although it may change when the report becomes complete.

For the UTC day October 7, 2026, the period is:

start:        2026-10-07T00:00:00.000Z
endExclusive: 2026-10-08T00:00:00.000Z

The start is included and the end is excluded. An order paid exactly at midnight on October 8 belongs to the next day. Adjacent periods meet without counting that record twice, and the query avoids guessing the last representable instant of a day.

If the product uses a reporting timezone, resolve the selected local calendar boundaries into UTC instants before querying. A timezone must be part of the request’s interpretation; a server’s local timezone should not silently decide which day the user meant.

Analytical reads need one more piece of context: how complete is the data for that period? An endpoint can return successfully while ingestion is delayed. The response should expose a documented freshness state or processing checkpoint, with a clear late-arrival policy. A recent ingestion timestamp alone cannot prove that every earlier event has arrived.

The current-status rule from our example should remain visible here too. A delayed payment event, a refund, and a status correction can change historical answers for different reasons. Treating each as “new data” hides the decision the reader needs to understand.

A correct database query can still serve the wrong result when a cache key omits part of its scope. Consider two users in Tenant A: one can read Store 1, while the other can read both Store 1 and Store 2. Their omitted-source requests have different populations.

Resolve current access before looking up cached data, then build the key from the effective scope. Include pagination and sorting for cached lists. A summary key can omit pagination because its result describes the whole matching population.

const summaryCacheKey = JSON.stringify([
  "paid-orders-summary-v1",
  scope.tenantId,
  [...scope.sourceIds].sort(),
  scope.period.start.toISOString(),
  scope.period.endExclusive.toISOString(),
  scope.status,
  scope.currency,
])

This key assumes that source access is the complete visibility rule for the example. If visibility also depends on row permissions or actor-specific fields, those distinctions must participate in the cache design. Access changes must affect cache eligibility immediately; data changes such as refunds need an invalidation or expiry policy appropriate to the promised freshness.

Background jobs introduce a delay between requesting an operation and performing it. A queued export should carry the tenant, requesting actor, selected sources, and reporting period. That payload records the requested scope; the worker must validate it against trusted records before executing the query.

Suppose access to Store 1 is revoked after an export is queued. For a user-requested export, I would reauthorize at execution and fail the job clearly if the original selection is no longer allowed. Silently broadening the selection would expose data, while silently narrowing it would produce a different export from the one requested. Result retrieval needs its own access check as well.

A scheduled administrative report may run under a service identity with a different policy. Give that identity explicit permissions and audit its actions. The queue message should never be the sole evidence that a tenant or source is authorized.

Lifecycle decisions also determine the write boundary. If order creation and inventory reservation must succeed together in the same database, they need an atomic transaction. External delivery can follow through a durable hand-off, such as an outbox. PostgreSQL transactions describes the database mechanism; the business contract determines which steps belong inside it.

Retries and migrations must preserve the same meaning. An export retry should not publish a second result unintentionally, and a deployment should not leave an old worker interpreting a changed scope field differently. Reversible migrations covers keeping those transitions manageable.

The contract becomes easier to verify with a small fixture where each record challenges a specific rule. Use the October 7 UTC period, Tenant A, and Store 1. All amounts below are in TRY minor units. Tenant and store labels stand in for UUIDs in this table.

The “Store 1” rows in different tenants represent distinct source UUIDs with the same display name. For a full-page request, the exact expected IDs are order-a1 and order-a2, the count is 2, and the total is 30000. These are fixture expectations, not production measurements.

The following assertion sketch assumes the fixture has been seeded and the response normalizes database numeric values into the shown application types:

const report = await getPaidOrdersReport(scope, {
  limit: 10,
  offset: 0,
})

expect(report.items.map((order) => order.id)).toEqual([
  "order-a1",
  "order-a2",
])
expect(report.total).toBe(2)
expect(report.revenueMinor).toBe(30000)

Checking the exact IDs matters. An assertion that every returned row belongs to Tenant A can also pass for an empty result. The fixture should detect missing data as well as data that crossed the wrong boundary.

Then test the transitions the happy-path fixture cannot cover on its own:

• Select a source outside the tenant or actor’s permissions and verify the access error.
• Omit the source for an actor who can read only Store 1; verify Store 2 stays excluded.
• Use an actor with no readable sources and verify an empty report without an unbounded fallback.
• Request a page size of one; verify the summary still covers both matching orders.
• Warm the cache with broader permissions, then request the report with a narrower source set.
• Revoke access after queuing an export and verify execution and result retrieval enforce the policy.
• Change the dataset between reads and check the response against its declared snapshot guarantee.

These cases belong at the boundaries where the behavior can actually be observed. A SQL fragment test can help, but database fixtures and API-level assertions establish the resulting population. Testing is a boundary design problem develops this approach in more detail.

For a practical PostgreSQL setup with isolated state and exact fixture assertions, see backend database testing.

Operational evidence should preserve the same distinctions. Track scope-validation failures, access-denied requests, cache behavior, and job authorization outcomes. Use request or job IDs to connect the stages, and record only the identifiers and metadata the team needs under its logging policy.

I find this model useful because it gives failures a name and a location. The team can tell whether the problem came from access resolution, a query predicate, a stale cache, or a worker acting on an outdated decision.

A database boundary becomes useful when another developer can explain it, a test can challenge it, and an operator can observe when it fails. That is what turns scope into a product contract.